Re: [sqlite] Date and age calculations

[Craig Smith]

On Sep 16, 2009, at 10:02 PM, sqlite-users-requ...@sqlite.org wrote:

> WHERE birth BETWEEN date('now','-24 years') AND date('now','-12
> years')

Thank you Igor and D. Richard for your explanations and assistance.  I  
understand better now how the date comparisons function.  Also, thank  
you Igor for pointing out my redundant nested SELECT statement.

Craig Smith
cr...@macscripter.net



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Running test after misc7-6.1.2

[Stephan Wehner]

I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed
to complete "make".

Now with  "make fulltest", there is no progress for over  almost two
hours. The present output is

misc6-1.4... Ok
Memory used:  now 16  max 361240  max-size 10
Page-cache used:  now  0  max  0  max-size   4096
Page-cache overflow:  now  0  max  59136
Scratch memory used:  now  0  max  0
Scratch overflow: now  0  max   9104  max-size   9104
misc7-1-misuse... Ok
misc7-2... Ok
misc7-3... Ok
misc7-4... Ok
misc7-5... Ok
misc7-6.1.1... Ok
misc7-6.1.2... Ok

There is still activity with "top" showing memory/CPU usage for the
"testfixture" process going up and down.
I am guessing the machine is working on misc7-6.1.3.

Is this a very demanding test? What does it test for?

How can I run the tests that come next in "fulltest"?

Stephan



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] full outer join questions

[Stef Mientki]

hello,

I'm trying to join 2 tables,
so I guess I need to perform a full outer join.

On wikipedia, I found this solution for sqlite3:
http://en.wikipedia.org/wiki/Join_%28SQL%29

select *
  from RT0
left join RT1 on RT1.PID = RT0.PID
  union
select RT0.*, RT1.*
  from RT1
left join RT0 on RT1.PID = RT0.PID
where RT0.PID IS NULL

Now the strange thing is that this query returns the correct number of rows,
but all the columns from the employee-table are empty.

If I change "UNION" to "UNION ALL" the join works as expected.
Is there an explanation for this behavior ?

Also when I add an order clause, the left columns are all made empty ?

Is it uberhaupt possible to order the resulting table on the column PID 
and preferable get just 1 PID column ?

thanks,
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Simon Slavin]

On 17 Sep 2009, at 5:56pm, Kelly Jones wrote:

> % 99+% of the time, there won't be two updates "at the same time". In
> other words, copy 1's change will almost always propagate to copy 2
> before copy 2 does another update.

Doesn't really matter as long as you have the other 1% of the time.

> % In my app, almost all the UPDATEs are of the form "WHERE rowid IN
> (...)". I don't do global updates like "WHERE foo='bar'".

If you can get rid of the 'almost' in that sentence, you have  
something worth talking about.  Then you just have the following  
problem:

Start with multiple copies of a table with 120 entries.

Client 1 inserts a new row in the database.  It is assigned id=121.
Client 2 inserts a new row in the database.  It is also assigned id=121.

Now you have two pieces of the data with the same id.  So it turns out  
you can't really use id (or rowid, or whatever you call it) either.   
You have to work out which records are new since the last synch which,  
once again, can only really be done by keeping some information  
centrally.  Which I understand you want to avoid.

There /are/ situations where you can safely implement multi-master  
systems.  One is if each record in your database belongs to a  
particular one of your sites.  For instance, a company has six  
branches, spread over the country.  Any branch can gain a new  
customer, modify customer details, or lose one of their existing  
customers.  But modifications and deletions for a customer in the  
database are always done by the branch that recruited that customer --  
one copy of the database.  So what you have in your tables is a field  
which says which of your sites can modify that record.  If any other  
site tries to modify information to do with those records, the system  
won't let them.

This places a restriction on how your database is used, but if your  
organisation can work within this restriction (and it sometimes fits  
well with how the organisation works anyway) then it might be a  
solution you can use.  And you can vary it, as long as all your sites  
cooperate.  For instance, you could have a rule that on the last day  
of every month Head Office can make any modification it likes to any  
record, and no other sites can modify anything.

> I read your other post as well, and still think this is worth
> pursuing. Is there a place I can read up on proposed solutions and why
> they fail?

There are entire books on the problems encountered when trying to  
synchronize copies of database.  Find anything called 'Designing multi- 
user database systems' or 'Distributed database systems'.  There is no  
satisfactory solution to the problem that doesn't require showing  
information to a human and getting the human to pick which  
interpretation of the data is best.  The programming for a proper  
solution gets more and more intricate and requires more and more  
locking and cross-checking until you realize it's simpler to implement  
a server/client architecture.  In fact there are patents granted on  
methods of synchronising databases, and if you try to implement the  
method you find it doesn't work.

You just walked into one of those problems that looks easy until you  
figure out it's impossible, like the travelling salesman problem.   
Don't worry about it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Force the use of a specified index?

[Bart Smissaert]

Have tried INDEXED BY and it does indeed work and force the use of the
specified index.
It didn't however make the query faster, so maybe the SQLite plan
generator is better than I thought!

RBS


On Thu, Sep 17, 2009 at 10:07 AM, Dan Kennedy  wrote:
>
> On Sep 17, 2009, at 5:02 AM, Bart Smissaert wrote:
>
>> Is it possible to tell SQLite to use a specified index?
>> I know you can use the + to excludes fields being used in an index,
>> but this doesn't help me in this particular case. I remember a
>> discussion about this and that this option might be added to SQLite,
>> but couldn't find it anywhere.
>
> There is the "INDEXED BY" clause. But many are of the opinion that
> this feature is prone to misuse.
>
>   http://www.sqlite.org/lang_indexedby.html
>
> Dan.
>
>>
>> RBS
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] replace extra carriage returns?

[Pavel Ivanov]

> Also, is there a more comprehensive function list other
> than http://www.sqlite.org/lang_corefunc.html ?

If you look closely at http://www.sqlite.org/lang.html you'll find these links:
http://www.sqlite.org/lang_corefunc.html
http://www.sqlite.org/lang_aggfunc.html
http://www.sqlite.org/lang_datefunc.html
And all these three links combined list all functions that SQLite
understands unless you (or you database manager application) register
some user-defined functions.

Pavel

On Thu, Sep 17, 2009 at 2:52 PM, Matt Williamson  wrote:
> How does the replace function identify a windows CRLF? I've tried
> using \n, \r, \p, 0D0A, etc. I just want to remove multiple carriage
> returns from a text typed field. Something like: Select replace
> (field1, '\n\n','\n') from table; I've just started dabbling with
> SQLite. I mainly work with MSSQL at the moment but I'm liking what I
> see so far. Also, is there a more comprehensive function list other
> than http://www.sqlite.org/lang_corefunc.html ?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] replace extra carriage returns?

[Griggs, Donald]

Hi Matt,

Regarding:
"Is there a more comprehensive function list other than
http://www.sqlite.org/lang_corefunc.html;

Is there an sqlite-supported function that's not listed there, or are
you saying you want more functions?
If the latter, sqlite struggles to keep the "lite" on, but you can
define your own functions as desired.


Regarding:  "I just want to remove multiple carriage returns from a text
typed field."

I think you'll want to use 
  SELECT replace(field1, x'0d0a0d0a', x'0d0a');

Note that I'm assuming you already *have* the multiple cr/lf's in your
text field.   If you're importing data with the commandline utility, it
allows very flexible definition of *field* separators, but I don't think
you can redefine the *record* separator.  So to eliminate empty lines on
imported data, you could still do something like:
   -- Create a table with only one long field.  
   -- Use something very unlikely as field separator
   CREATE TABLE raw(line);
   .separator '#$%'
   .import 'myRawFile.txt'  raw
   -- delete any truly empty lines and output my clean data for later
re-import.
   DELETE FROM RAW WHERE STRLEN(line) < 1;
   .output 'myCleanFile.txt' 
   SELECT * FROM raw;
   .output stdout

   OR -- you could use a tiny program in awk, sed, perl, etc.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] replace extra carriage returns?

[Matt Williamson]

How does the replace function identify a windows CRLF? I've tried
using \n, \r, \p, 0D0A, etc. I just want to remove multiple carriage
returns from a text typed field. Something like: Select replace
(field1, '\n\n','\n') from table; I've just started dabbling with
SQLite. I mainly work with MSSQL at the moment but I'm liking what I
see so far. Also, is there a more comprehensive function list other
than http://www.sqlite.org/lang_corefunc.html ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Igor Tandetnik]

Angus March  wrote:
> Igor Tandetnik wrote:
>> Angus March  wrote:
>>> After the callback has finished, what will happen with that original
>>> call to sqlite3_reset()?
>>
>> Since sqlite3_reset doesn't take any locks, a busy callback would
>> never be invoked for it.
>
>Well someone should tell the library on my machine that, because
> it's returning locking errors

I didn't say that sqlite3_reset can't return SQLITE_BUSY - only that it 
doesn't take locks and doesn't trigger a call to a busy handler.

For historical reasons, for statements prepared with 
sqlite3_prepare[16], sqlite3_step reports all errors with a generic 
SQLITE_ERROR code. A subsequent call to sqlite3_reset or 
sqlite3_finalize returns the real error code (even though the reset or 
finalize itself doesn't fail). Use sqlite3_prepare[16]_v2 to avoid this 
behavior and have sqlite3_step return the real error code directly.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] About the "EnterCriticalSection" call in sample C code

[Kavita Raghunathan]

Marcus,
Question about the Sample C code you wrote on sqlite.org. Could you tell me if 
I need the
"EnterCriticalSection" over the fprintf for this to work in multiple "linux" 
not windows
threads ? Currently I am using your sample C code as reference for my C++ 
wrappers
around Sqlite. We use centos 5.3.

And thanks for the sample code, its made my life much easier.
Regards,
Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Marcus Grimm]

> Igor Tandetnik wrote:
>> Angus March  wrote:
>>> After the callback has finished, what will happen with that original
>>> call to sqlite3_reset()?
>>>
>>
>> Since sqlite3_reset doesn't take any locks, a busy callback would never
>> be invoked for it.
>>
>
> Well someone should tell the library on my machine that, because
> it's returning locking errors

yes, it may return that... but as a result of a previous
call to sqlite3_step which returned a lock error.
so it basically just passes the last error code throu

Marcus

> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Angus March]

Igor Tandetnik wrote:
> Angus March  wrote:
>   
>> What should
>> the callback that is passed to sqlite3_busy_handler() be doing?
>> 
>
> It should be deciding whether to continue waiting for the lock to clear, 
> or to allow SQLite to report an error to the calling application. It 
> should be conveying this decision via its return value.
>
>   
>> Let's
>> say that sqlite3_reset() is called, which fails to achieve the lock.
>> 
>
> sqlite3_reset doesn't take any locks.
>
>   
>> After the callback has finished, what will happen with that original
>> call to sqlite3_reset()?
>> 
>
> Since sqlite3_reset doesn't take any locks, a busy callback would never 
> be invoked for it.
>   

Well someone should tell the library on my machine that, because
it's returning locking errors
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Jean-Christophe Deschamps]

At 18:56 17/09/2009, you wrote:
´¯¯¯
>  % 99+% of the time, there won't be two updates "at the same time". In
>  other words, copy 1's change will almost always propagate to copy 2
>  before copy 2 does another update.
`---

The devil is in the 1% and the "almost" of course.

But what do you and other think of his:

Since the OP postulates that the chance of update clashes are low to 
very low, and given that he aims at a fixed number of only 4 updaters, 
isn't it possible to maintain, for each of 4 servers and _at the 
application level_ 4 distinct connections, one being local and the 
other 3 being remote.

Of course all read-only operation can be done on the local connection, 
while a easy encapsulation is needed for write operations taking care 
of return codes.  Avoiding all non-reproducible data (e.g. rowids 
generated by sqlite or timestamps, which will vary from system to 
system) isn't it possible for him to do it this way?

It is certainly a waste of resource (but this is a redundant system) 
and it won't be very fast due to added write contention, but at least 
he won't have to do surgery on each new SQLite release.  Since the OP 
had been thinking of a backup before each write, it shouldn't be a 
highly demanding environment.

That's too easy, there must be a catch somewhere...

Where is the thinko?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Igor Tandetnik]

Angus March  wrote:
> What should
> the callback that is passed to sqlite3_busy_handler() be doing?

It should be deciding whether to continue waiting for the lock to clear, 
or to allow SQLite to report an error to the calling application. It 
should be conveying this decision via its return value.

> Let's
> say that sqlite3_reset() is called, which fails to achieve the lock.

sqlite3_reset doesn't take any locks.

> After the callback has finished, what will happen with that original
> call to sqlite3_reset()?

Since sqlite3_reset doesn't take any locks, a busy callback would never 
be invoked for it.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Angus March]

Pavel Ivanov wrote:
>> Will sqlite3_unlock_notify() work for this, or do I need to be
>> doing something else?
>> 
>
> No, sqlite3_unlock_notify() doesn't work for multi-process
> applications. For them you should do some retries after delay by
> yourself (probably using sqlite3_busy_handler() ) or use
> sqlite3_busy_timeout().
>
>   
Yes, it looks like the answer involves sqlite3_busy_handler(). I
take it I don't need that shared cache stuff or whatever. I'm having a
hard time understanding how I'm supposed to use it though. What should
the callback that is passed to sqlite3_busy_handler() be doing? Let's
say that sqlite3_reset() is called, which fails to achieve the lock.
After the callback has finished, what will happen with that original
call to sqlite3_reset()?
I'd appreciate it if someone could give me some quick and dirty
pseudocode to illustrate what I should be doing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cygwin and sqlite

[Pavel Ivanov]

> At least I think that is what you suggest, and think it just
> may work! But I could be wrong!

Yes, that's exactly what I suggest.

Pavel

On Thu, Sep 17, 2009 at 1:18 PM, John  wrote:
> Pavel Ivanov wrote:
>>> I'd rather avoid building sqlite3 under cygwin. I would like
>>> to keep as much as possible in native code, compromising only
>>> on cygwin to run my scripts.
>>
>> And this is root of your problem. Using mix of cygwin-native
>> applications with windows-native applications will always have such
>> problem.
>>
>>> When installing cygwin, you it offers you the choice to switch
>>> to default text file type to DOS (\r\n). Should I try that?
>>
>> Don't do that. This mode of operation is not supported much and not
>> recommended by cygwin developers and it reportedly will significantly
>> slow down cygwin's operation.
>>
>>> So I guess my question here is, do any sqlite users here
>>> have experience fixing this on Windows for Unix cygwin
>>> script calls?
>>
>> The major suggestion here: write some "windows native code launcher"
>> that will be used for running all non-cygwin applications (this can be
>> just function in the script). It will do nothing on unix platforms
>> (select your own preferred way of distinguishing it) and it will
>> always strip off '\r' from output of running application on windows
>> (you can use sed for that). And there's nothing else you can do about
>> it.
>
> This sounds like a great idea. I can have all sqlite3.exe calls
> "intercepted" by another script call like:
>
> NumPar=`WINDOWSCALL Program Arguments`
>
> WINDOWSCALL is the launcher that calls Program sqlite3.exe
> with its arguments and strips off any trailing \r's
> and returns that string to the caller through stdout,
> as to NumPar here. WINDOWSCALL can do nothing on Unix/MacOS,
> and fix the string on Windows.
>
> At least I think that is what you suggest, and think it just
> may work! But I could be wrong!
>
> Thanks! I'll try coding it.
>
>> Pavel
>>
>> On Thu, Sep 17, 2009 at 12:26 PM, John  wrote:
>>> I am writing some Unix scripts on Mac OS X that use
>>> sqlite3. Since the program could be useful to those
>>> on Windows, I figured I'd see if they worked under
>>> cygwin.
>>>
>>> A lot of it works, but calling sqlite3.exe from
>>> cygwin and returning a string with the value
>>> returned from the database seems to attach a
>>> "\r" that expr doesn't remove.
>>>
>>> That is:
>>>
>>> NumPar=`sqlite3.exe ${DATABASE} "SELECT NumPar FROM citations WHERE
>>> X='Key' ;"`
>>>
>>> NumPar comes back as: "12\r"
>>>
>>> and
>>>
>>> NumPar=`expr ${NumPar}`
>>>
>>> doesn't convert it to integer, as the subsequent test fails because
>>> of NumPar being non-integer (it isn't complaining about N, that
>>> is integer in the code):
>>>
>>> if [ ${N} -le ${NumPar} ]
>>> ...
>>>
>>> I can fix this case by:
>>>
>>> NumPar=`printf '%s' "${NumPar}" | sed 's/[^0-9]//g'`
>>>
>>> but then other scripts fail later, presumably because of
>>> strings with \r on them. (I suppose I can use sed to
>>> always remove \r's on every one of these calls, but
>>> that seems pretty kludgy, especially since "clean"
>>> Mac OS X handles all this "properly" without that.
>>> I'm hoping to find an elegant solution.
>>>
>>> I'd rather avoid building sqlite3 under cygwin. I would like
>>> to keep as much as possible in native code, compromising only
>>> on cygwin to run my scripts.
>>>
>>> When installing cygwin, you it offers you the choice to switch
>>> to default text file type to DOS (\r\n). Should I try that?
>>> My pretty serious objection to that would be that any users
>>> already using cygwin with the "correct" default settings would
>>> not be able to use the scripts anyway.
>>>
>>> So I guess my question here is, do any sqlite users here
>>> have experience fixing this on Windows for Unix cygwin
>>> script calls?
>>>
>>> Thanks!
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Stephan Wehner]

On Thu, Sep 17, 2009 at 9:56 AM, Kelly Jones
 wrote:
> On 9/17/09, Simon Slavin  wrote:
>>
>> On 17 Sep 2009, at 4:54pm, Kelly Jones wrote:
>>
>>> I want to do multi-master sqlite3 replication by editing sqlite3.c to
>>> log UPDATE/INSERT queries with timestamps, and then using another
>>> program to run those queries on the other masters.

Quick comment; timestamps are not likely to be robust ... I would add
a "version" column and synchronize against that.

When it comes to backups, one likes reliability; I personally wouldn't
be confident with a  recompiled sqlite.
Plus how do you distribute the new binaries to the, presumably up and
running, replication
system without hick-ups? Most likely you are going to change your
implementation (schema changes not mentioned yet)

Stephan

>> Doesn't work.  Consider:  You have a database with three records:
>>
>> ID    object  colour
>> --    --  --
>> 1     car     red
>> 2     coat    black
>> 3     hat     green
>>
>> In database copy number 1 someone does
>>
>> UPDATE objects SET colour='green' WHERE id=1
>>
>> In database copy number 2 someone does
>>
>> UPDATE objects SET colour='purple' WHERE colour='green'
>>
>> Then you try to merge the databases by running everyone else's UPDATE
>> commands against each copy.  Under your system copy 1 will still end
>> up different copy 2.
>>
>> A better way is to hold just one 'master' copy of the database
>> centrally, use everyone's queries to update that, then distribute
>> copies of the master.  But even that won't have the results you might
>> expect at first glance.  First you need to work out what you'd want to
>> happen under those circumstances.  Then work out how to implement it.
>
> Hmmm, good point. I always thought this was how MySQL did replication,
> but maybe that's why MySQL's replication is unidirectional. Two comments:
>
>  % 99+% of the time, there won't be two updates "at the same time". In
>  other words, copy 1's change will almost always propagate to copy 2
>  before copy 2 does another update.
>
>  % In my app, almost all the UPDATEs are of the form "WHERE rowid IN
>  (...)". I don't do global updates like "WHERE foo='bar'".
>
> I read your other post as well, and still think this is worth
> pursuing. Is there a place I can read up on proposed solutions and why
> they fail?
>
> I've considered other solutions (eg, triggers and iwatch/rsync), but
> the query-logging one seems to have certain advantages.
>
> One thought: backup the db before each INSERT/UPDATE (too ugly?) +
> keep the backups for 5m (or whatever). If you get an update query from
> a remote master w/ a timestamp older than your own most-recent update
> query, restore the backup and re-run the queries in the correct order.
>
> I'm trying to implement a redundant system of 4 VPSs, and want to
> avoid having a single master.
>
> --
> We're just a Bunch Of Regular Guys, a collective group that's trying
> to understand and assimilate technology. We feel that resistance to
> new ideas and technology is unwise and ultimately futile.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cygwin and sqlite

[John]

Pavel Ivanov wrote:
>> I'd rather avoid building sqlite3 under cygwin. I would like
>> to keep as much as possible in native code, compromising only
>> on cygwin to run my scripts.
> 
> And this is root of your problem. Using mix of cygwin-native
> applications with windows-native applications will always have such
> problem.
> 
>> When installing cygwin, you it offers you the choice to switch
>> to default text file type to DOS (\r\n). Should I try that?
> 
> Don't do that. This mode of operation is not supported much and not
> recommended by cygwin developers and it reportedly will significantly
> slow down cygwin's operation.
> 
>> So I guess my question here is, do any sqlite users here
>> have experience fixing this on Windows for Unix cygwin
>> script calls?
> 
> The major suggestion here: write some "windows native code launcher"
> that will be used for running all non-cygwin applications (this can be
> just function in the script). It will do nothing on unix platforms
> (select your own preferred way of distinguishing it) and it will
> always strip off '\r' from output of running application on windows
> (you can use sed for that). And there's nothing else you can do about
> it.

This sounds like a great idea. I can have all sqlite3.exe calls
"intercepted" by another script call like:

NumPar=`WINDOWSCALL Program Arguments`

WINDOWSCALL is the launcher that calls Program sqlite3.exe
with its arguments and strips off any trailing \r's
and returns that string to the caller through stdout,
as to NumPar here. WINDOWSCALL can do nothing on Unix/MacOS,
and fix the string on Windows.

At least I think that is what you suggest, and think it just
may work! But I could be wrong!

Thanks! I'll try coding it.

> Pavel
> 
> On Thu, Sep 17, 2009 at 12:26 PM, John  wrote:
>> I am writing some Unix scripts on Mac OS X that use
>> sqlite3. Since the program could be useful to those
>> on Windows, I figured I'd see if they worked under
>> cygwin.
>>
>> A lot of it works, but calling sqlite3.exe from
>> cygwin and returning a string with the value
>> returned from the database seems to attach a
>> "\r" that expr doesn't remove.
>>
>> That is:
>>
>> NumPar=`sqlite3.exe ${DATABASE} "SELECT NumPar FROM citations WHERE
>> X='Key' ;"`
>>
>> NumPar comes back as: "12\r"
>>
>> and
>>
>> NumPar=`expr ${NumPar}`
>>
>> doesn't convert it to integer, as the subsequent test fails because
>> of NumPar being non-integer (it isn't complaining about N, that
>> is integer in the code):
>>
>> if [ ${N} -le ${NumPar} ]
>> ...
>>
>> I can fix this case by:
>>
>> NumPar=`printf '%s' "${NumPar}" | sed 's/[^0-9]//g'`
>>
>> but then other scripts fail later, presumably because of
>> strings with \r on them. (I suppose I can use sed to
>> always remove \r's on every one of these calls, but
>> that seems pretty kludgy, especially since "clean"
>> Mac OS X handles all this "properly" without that.
>> I'm hoping to find an elegant solution.
>>
>> I'd rather avoid building sqlite3 under cygwin. I would like
>> to keep as much as possible in native code, compromising only
>> on cygwin to run my scripts.
>>
>> When installing cygwin, you it offers you the choice to switch
>> to default text file type to DOS (\r\n). Should I try that?
>> My pretty serious objection to that would be that any users
>> already using cygwin with the "correct" default settings would
>> not be able to use the scripts anyway.
>>
>> So I guess my question here is, do any sqlite users here
>> have experience fixing this on Windows for Unix cygwin
>> script calls?
>>
>> Thanks!
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Pavel Ivanov]

> Will sqlite3_unlock_notify() work for this, or do I need to be
> doing something else?

No, sqlite3_unlock_notify() doesn't work for multi-process
applications. For them you should do some retries after delay by
yourself (probably using sqlite3_busy_handler() ) or use
sqlite3_busy_timeout().

Pavel

On Thu, Sep 17, 2009 at 1:04 PM, Angus March  wrote:
> I was under the impression that when a C API function attempts to get a
> lock on the db that it cannot get, it blocks until it can get the lock.
> Well it turns out that this isn't true.
>    Googling for 'sqlite locking block' has directed me to
> http://www.sqlite.org/unlock_notify.html, which suggest that I should be
> using the sqlite3_unlock_notify() function. However, it also states that
> this is for multi-threaded applications. My project is a multi-/process/
> one. Will sqlite3_unlock_notify() work for this, or do I need to be
> doing something else?
>    If someone has a better solution, I'd love to hear it, and soon,
> because I'm in crunch time.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serious locking/blocking issue

[Igor Tandetnik]

Angus March  wrote:
> I was under the impression that when a C API function attempts to get
> a lock on the db that it cannot get, it blocks until it can get the
> lock. Well it turns out that this isn't true.

What gave you this impression in the first place? If this were true, 
what would be the purpose of sqlite3_busy_handler and 
sqlite3_busy_timeout?

>Googling for 'sqlite locking block' has directed me to
> http://www.sqlite.org/unlock_notify.html, which suggest that I should
> be using the sqlite3_unlock_notify() function.

Are you using shared cache mode?

> However, it also
> states that this is for multi-threaded applications. My project is a
> multi-/process/ one.

Then you are not using shared cache mode, and the article doesn't apply 
to you in any way.

> Will sqlite3_unlock_notify() work for this

No

> or do I need to be doing something else?

Yes.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Kelly Jones]

On 9/17/09, Simon Slavin  wrote:
>
> On 17 Sep 2009, at 4:54pm, Kelly Jones wrote:
>
>> I want to do multi-master sqlite3 replication by editing sqlite3.c to
>> log UPDATE/INSERT queries with timestamps, and then using another
>> program to run those queries on the other masters.
>
> Doesn't work.  Consider:  You have a database with three records:
>
> IDobject  colour
> ----  --
> 1 car red
> 2 coatblack
> 3 hat green
>
> In database copy number 1 someone does
>
> UPDATE objects SET colour='green' WHERE id=1
>
> In database copy number 2 someone does
>
> UPDATE objects SET colour='purple' WHERE colour='green'
>
> Then you try to merge the databases by running everyone else's UPDATE
> commands against each copy.  Under your system copy 1 will still end
> up different copy 2.
>
> A better way is to hold just one 'master' copy of the database
> centrally, use everyone's queries to update that, then distribute
> copies of the master.  But even that won't have the results you might
> expect at first glance.  First you need to work out what you'd want to
> happen under those circumstances.  Then work out how to implement it.

Hmmm, good point. I always thought this was how MySQL did replication,
but maybe that's why MySQL's replication is unidirectional. Two comments:

 % 99+% of the time, there won't be two updates "at the same time". In
 other words, copy 1's change will almost always propagate to copy 2
 before copy 2 does another update.

 % In my app, almost all the UPDATEs are of the form "WHERE rowid IN
 (...)". I don't do global updates like "WHERE foo='bar'".

I read your other post as well, and still think this is worth
pursuing. Is there a place I can read up on proposed solutions and why
they fail?

I've considered other solutions (eg, triggers and iwatch/rsync), but
the query-logging one seems to have certain advantages.

One thought: backup the db before each INSERT/UPDATE (too ugly?) +
keep the backups for 5m (or whatever). If you get an update query from
a remote master w/ a timestamp older than your own most-recent update
query, restore the backup and re-run the queries in the correct order.

I'm trying to implement a redundant system of 4 VPSs, and want to
avoid having a single master.

-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Andreas Kupries]

Simon Slavin wrote:
> On 17 Sep 2009, at 5:29pm, Simon Slavin wrote:
>> [stuff]

> Sorry, I forgot to add: you are asking questions about a subject which  
> is frequently raised here: synchronising multiple copies of a  
> database.  This stuff is hard.

Note also

http://www.sqliteconcepts.org

and especially

http://www.sqliteconcepts.org/DCS_index.html

-- 
Sincerely,
 Andreas Kupries 
 Developer @
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cygwin and sqlite

[Pavel Ivanov]

> I'd rather avoid building sqlite3 under cygwin. I would like
> to keep as much as possible in native code, compromising only
> on cygwin to run my scripts.

And this is root of your problem. Using mix of cygwin-native
applications with windows-native applications will always have such
problem.

> When installing cygwin, you it offers you the choice to switch
> to default text file type to DOS (\r\n). Should I try that?

Don't do that. This mode of operation is not supported much and not
recommended by cygwin developers and it reportedly will significantly
slow down cygwin's operation.

> So I guess my question here is, do any sqlite users here
> have experience fixing this on Windows for Unix cygwin
> script calls?

The major suggestion here: write some "windows native code launcher"
that will be used for running all non-cygwin applications (this can be
just function in the script). It will do nothing on unix platforms
(select your own preferred way of distinguishing it) and it will
always strip off '\r' from output of running application on windows
(you can use sed for that). And there's nothing else you can do about
it.

Pavel

On Thu, Sep 17, 2009 at 12:26 PM, John  wrote:
> I am writing some Unix scripts on Mac OS X that use
> sqlite3. Since the program could be useful to those
> on Windows, I figured I'd see if they worked under
> cygwin.
>
> A lot of it works, but calling sqlite3.exe from
> cygwin and returning a string with the value
> returned from the database seems to attach a
> "\r" that expr doesn't remove.
>
> That is:
>
> NumPar=`sqlite3.exe ${DATABASE} "SELECT NumPar FROM citations WHERE
> X='Key' ;"`
>
> NumPar comes back as: "12\r"
>
> and
>
> NumPar=`expr ${NumPar}`
>
> doesn't convert it to integer, as the subsequent test fails because
> of NumPar being non-integer (it isn't complaining about N, that
> is integer in the code):
>
> if [ ${N} -le ${NumPar} ]
> ...
>
> I can fix this case by:
>
> NumPar=`printf '%s' "${NumPar}" | sed 's/[^0-9]//g'`
>
> but then other scripts fail later, presumably because of
> strings with \r on them. (I suppose I can use sed to
> always remove \r's on every one of these calls, but
> that seems pretty kludgy, especially since "clean"
> Mac OS X handles all this "properly" without that.
> I'm hoping to find an elegant solution.
>
> I'd rather avoid building sqlite3 under cygwin. I would like
> to keep as much as possible in native code, compromising only
> on cygwin to run my scripts.
>
> When installing cygwin, you it offers you the choice to switch
> to default text file type to DOS (\r\n). Should I try that?
> My pretty serious objection to that would be that any users
> already using cygwin with the "correct" default settings would
> not be able to use the scripts anyway.
>
> So I guess my question here is, do any sqlite users here
> have experience fixing this on Windows for Unix cygwin
> script calls?
>
> Thanks!
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Simon Slavin]

On 17 Sep 2009, at 5:29pm, Simon Slavin wrote:

> [stuff]

Sorry, I forgot to add: you are asking questions about a subject which  
is frequently raised here: synchronising multiple copies of a  
database.  This stuff is hard.  There is no obvious solution, and  
there are some very unobvious problems with most of the solutions that  
look like they would work.  So if we're not just reeling-off a pat  
solution to solve your problem please don't take it as a personal  
insult.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cygwin and sqlite

[Simon Slavin]

On 17 Sep 2009, at 5:26pm, John wrote:

> So I guess my question here is, do any sqlite users here
> have experience fixing this on Windows for Unix cygwin
> script calls?

Not me, but it occurs to me that your scripts might get different  
results for different shells, even if you're just executing on the Mac/ 
Unix side.  So you might try, for example, tcsh and bash and make sure  
you get identical results before you start singling out cygwin as a  
trouble-maker.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication through UPDATE and INSERT logging?

[Simon Slavin]

On 17 Sep 2009, at 4:54pm, Kelly Jones wrote:

> I want to do multi-master sqlite3 replication by editing sqlite3.c to
> log UPDATE/INSERT queries with timestamps, and then using another
> program to run those queries on the other masters.

Doesn't work.  Consider:  You have a database with three records:

ID  object  colour
--  --  --
1   car red
2   coatblack
3   hat green

In database copy number 1 someone does

UPDATE objects SET colour='green' WHERE id=1

In database copy number 2 someone does

UPDATE objects SET colour='purple' WHERE colour='green'

Then you try to merge the databases by running everyone else's UPDATE  
commands against each copy.  Under your system copy 1 will still end  
up different copy 2.

A better way is to hold just one 'master' copy of the database  
centrally, use everyone's queries to update that, then distribute  
copies of the master.  But even that won't have the results you might  
expect at first glance.  First you need to work out what you'd want to  
happen under those circumstances.  Then work out how to implement it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] cygwin and sqlite

[John]

I am writing some Unix scripts on Mac OS X that use
sqlite3. Since the program could be useful to those
on Windows, I figured I'd see if they worked under
cygwin.

A lot of it works, but calling sqlite3.exe from
cygwin and returning a string with the value
returned from the database seems to attach a
"\r" that expr doesn't remove.

That is:

NumPar=`sqlite3.exe ${DATABASE} "SELECT NumPar FROM citations WHERE 
X='Key' ;"`

NumPar comes back as: "12\r"

and

NumPar=`expr ${NumPar}`

doesn't convert it to integer, as the subsequent test fails because
of NumPar being non-integer (it isn't complaining about N, that
is integer in the code):

if [ ${N} -le ${NumPar} ]
...

I can fix this case by:

NumPar=`printf '%s' "${NumPar}" | sed 's/[^0-9]//g'`

but then other scripts fail later, presumably because of
strings with \r on them. (I suppose I can use sed to
always remove \r's on every one of these calls, but
that seems pretty kludgy, especially since "clean"
Mac OS X handles all this "properly" without that.
I'm hoping to find an elegant solution.

I'd rather avoid building sqlite3 under cygwin. I would like
to keep as much as possible in native code, compromising only
on cygwin to run my scripts.

When installing cygwin, you it offers you the choice to switch
to default text file type to DOS (\r\n). Should I try that?
My pretty serious objection to that would be that any users
already using cygwin with the "correct" default settings would
not be able to use the scripts anyway.

So I guess my question here is, do any sqlite users here
have experience fixing this on Windows for Unix cygwin
script calls?

Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance in a case of big columns number

[Simon Slavin]

On 16 Sep 2009, at 3:23pm, Dmitry Konishchev wrote:

> Data in my program has such format that there is useful to place it in
> the database in many (thousands) columns.

It'll work, but SQLite does not use a balanced tree to store the  
columns for a particular record.  So if you're seeking the 700th  
column of a particular row, it has to look through 699 others before  
it gets to it.  Unless you always handle all the columns of a row  
together, it'll be slow.

Because of speed, and the difficulty of correctly handling such a long  
INSERT line, it's usually better to break this down into properties.   
So instead of

ID  prop1   prop2   prop3   prop4
--  -   -   -   -
1   rec1p1  rec1p2  rec1p3  rec1p4
2   rec2p1  rec2p2  rec2p3  rec1p4

Do

ID  propNumber  propValue
--  --  -
1   1   rec1p1
1   2   rec1p2
1   3   rec1p3
1   4   rec1p4
2   1   rec2p1
2   2   rec2p2
2   3   rec2p3
2   4   rec2p4

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ANN] SQLJet 1.0.0 released

[Alexander Kitaev]

Hello Max,

> 1. Are there any real reasons for having dual-licensed commercial
> partial reimplementation of SQLite in Java? Any examples?
We're following licensing policy of another project we're working on
(SVNKit), plus BDB JE was an example for us in that area, ever since
SleepyCat times.

The reason for reimplementation itself is that in another project we
have (SVNKit) we need to work with SQLite databases with maximum
portability (which excludes JNI), relatively good performance (which
excludes NestedVM) and, preferably, with means to control the code.

Another reason is that having this project (SQLJet) opens us ways for
other projects that are not that tightly connected to SQLite and not
just to have sort of SQLite 'replacement'.

> 2. When do you expecting to have SQL API?
Now we face two options for the next major version: first one is to work
on and open BTree-level API (i.e. add custom comparators support, like
in BDB, provide examples, etc) and second one is SQL queries support.

I hope we'll find enough resources to work in both directions, but first
one (low level BTree API) has higher priority at the moment. I expect
next major version to be published early next year.

> 3. Are you targeting for having 100% SQLite unit-tests passes?
Yes, we'd like to be able to run SQLite tests and of course get 100%
coverage, but, as you know, to do that we need first to have SQL support.

> 4. Have you ever thought about collaborating with and contributing to
> http://code.google.com/p/csharp-sqlite/ since it's easier to port
> C#->Java than C->Java?
We wasn't aware of C# version at the moment we've started SQLJet -
otherwise you're right, it could save time. As for contributing - we're
not experts in C#, but in general we're open for collaboration :)

> 5. Any benchmarks comparing to native?
So far SQLJet is slower than JNI-based JDBC, but faster than
NestedVM-based version (more than twice). To run benchmarks one should
do the following:

$ svn export http://svn.sqljet.com/repos/sqljet/trunk sqljet
$ cd sqljet
$ ant benchmarks

On my Linux box output is:

[junit] Testsuite: org.tmatesoft.sqljet.benchmarks.SqlJetBenchmark
[junit] Tests run: 7, Failures: 0, Errors: 0, Time elapsed: 18.621 sec
[junit]
[junit] Testcase: clear took 1.505 sec
[junit] Testcase: nothing took 0.009 sec
[junit] Testcase: selectAll took 1.573 sec
[junit] Testcase: updateAll took 3.938 sec
[junit] Testcase: deleteAll took 0.846 sec
[junit] Testcase: insertRandoms took 4.278 sec
[junit] Testcase: locate took 6.322 sec

[junit] Testsuite: org.tmatesoft.sqljet.benchmarks.NestedVmBenchmark
[junit] Tests run: 7, Failures: 0, Errors: 0, Time elapsed: 44.009 sec
[junit]
[junit] Testcase: clear took 2.991 sec
[junit] Testcase: nothing took 0.011 sec
[junit] Testcase: selectAll took 6.362 sec
[junit] Testcase: updateAll took 7.96 sec
[junit] Testcase: deleteAll took 1.492 sec
[junit] Testcase: insertRandoms took 9.183 sec
[junit] Testcase: locate took 15.908 sec

This comparison is not of course absolutely 'fair', as for NEstedVM we
use SQL queries, while SQLJet is measured using API. Also, while SQLJet
outperform NestedVM it is still slower than native and performance
optimization is another task for the next major version of SQLJet.

Alexander Kitaev,
TMate Software,
http://svnkit.com/ - Java [Sub]Versioning Library!
http://sqljet.com/ - Java SQLite Library!

Kosenko Max wrote:
> 
> Alexander Kitaev-3 wrote:
>> We're glad to announce that SQLJet 1.0.0 has been released and available
>> for download at http://sqljet.com/ web site.
>>
> 
> Hi.
> 
> Several questions:
> 1. Are there any real reasons for having dual-licensed commercial partial
> reimplementation of SQLite in Java? Any examples?
> 2. When do you expecting to have SQL API?
> 3. Are you targeting for having 100% SQLite unit-tests passes?
> 4. Have you ever thought about collaborating with and contributing to
> http://code.google.com/p/csharp-sqlite/ since it's easier to port C#->Java
> than C->Java?
> 5. Any benchmarks comparing to native?
> 
> Thanks.
> 
> Max.
> 
> -
> Best Regards.
> Max Kosenko.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multi-master replication through UPDATE and INSERT logging?

[Kelly Jones]

I want to do multi-master sqlite3 replication by editing sqlite3.c to
log UPDATE/INSERT queries with timestamps, and then using another
program to run those queries on the other masters.

I looked at the sqlite3Insert() function in sqlite3.c, but couldn't
find a variable that holds the query itself. I could probably
reconstruct the query from pTabList, pList, pSelect, and pColumn, but
is there an easier way?

Same question for sqlite3Update().

I realize I could use triggers for this, but my goal is to move the
low-priority replication task outside of sqlite3.

-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ANN] SQLJet 1.0.0 released

[Virgilio Alexandre Fornazin]

Question 1 is a very good question, why pay for a partial copy if you can
use the full version for free?

Also, what is the sense of using SQLite database without SQL support? (this
remember Clipper/dBase GOTO LOCATE APPEND...)

I can't get the point... if you can't use a native SQLite in your platform,
why do not use another pure-java DB?

Just my 2 cents...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kosenko Max
Sent: quinta-feira, 17 de setembro de 2009 11:14
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] [ANN] SQLJet 1.0.0 released



Alexander Kitaev-3 wrote:
> 
> We're glad to announce that SQLJet 1.0.0 has been released and available
> for download at http://sqljet.com/ web site.
> 

Hi.

Several questions:
1. Are there any real reasons for having dual-licensed commercial partial
reimplementation of SQLite in Java? Any examples?
2. When do you expecting to have SQL API?
3. Are you targeting for having 100% SQLite unit-tests passes?
4. Have you ever thought about collaborating with and contributing to
http://code.google.com/p/csharp-sqlite/ since it's easier to port C#->Java
than C->Java?
5. Any benchmarks comparing to native?

Thanks.

Max.

-
Best Regards.
Max Kosenko.
-- 
View this message in context:
http://www.nabble.com/-ANN--SQLJet-1.0.0-released-tp25458690p25491910.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best approach for storing not-so-small BLOBs per record

[Pavel Ivanov]

If the majority of your queries don't need blobs and ask only meta
information then you definitely need to go with 2 or 3. Because it
will compact your meta data and SQLite will need to read fewer file
pages to reach more meta data. Also it will help your meta data to be
read more from database cache and do not touch disk unless you
actually need to read blob data. Though it will be true only if your
metadata is less than total amount of cache allowed. I'm not sure that
3 will be better in some ways than 2, maybe only somewhere on the OS
level. But you can try it.

Pavel

On Wed, Sep 16, 2009 at 12:13 PM, Itamar Syn-Hershko
 wrote:
> Hi all,
>
> I'm in the design phase of an application with SQLite backend. The SQLite
> file will hold a table of about 20K records initially, and a few several
> other small tables. About 75% of the records in the large table will have
> binary data associated with it. My main question is which one of the
> following options I'm better off with to store those BLOBs, in terms of DB
> efficiency, memory usage, media seeks (since this will most likely to reside
> on a CD) and file size. The storage options I see relevant are:
>
> 1. BLOBs in the original table in a per-record basis (records with no BLOBs
> NULLified). If separating the BLOBs from this table will help performance in
> any way, I see two further options:
> 2. BLOBs in a separate table, and having the unique ID of the record in the
> large table point at this. No indices necessary, and will never use JOINs in
> queries since that table will be accessed explicitly on-demand only.
> 3. Same as #2 above, except in a separated, joint SQLite file (to aid file
> seeks).
>
> As mentioned, the binaries I'll be storing will only be pulled on demand
> (most queries to the large table will return the accompanying meta-data
> WITHOUT the binary data); no JOINs or foreign indices necessary. The average
> BLOB size is a few 10s of KBs; anyway I do not expect to have a BLOB over
> 1-2MBs. In the shelf version writes to the DB (particularly the large table)
> will very rarely occur; mostly only read operations, so I'm willing to take
> any cost to write operations.
>
> Also, looking up on compression support with SQLite I found 2 solutions -
> CEROD [1] and per-field compression using zlib and extension functions to
> compress / decompress. Are there more options I might have missed?
>
> Thanks in advance for any advice on this.
>
> Itamar.
>
> [1] http://www.hwaci.com/sw/sqlite/cerod.html
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ANN] SQLJet 1.0.0 released

[Kosenko Max]

Alexander Kitaev-3 wrote:
> 
> We're glad to announce that SQLJet 1.0.0 has been released and available
> for download at http://sqljet.com/ web site.
> 

Hi.

Several questions:
1. Are there any real reasons for having dual-licensed commercial partial
reimplementation of SQLite in Java? Any examples?
2. When do you expecting to have SQL API?
3. Are you targeting for having 100% SQLite unit-tests passes?
4. Have you ever thought about collaborating with and contributing to
http://code.google.com/p/csharp-sqlite/ since it's easier to port C#->Java
than C->Java?
5. Any benchmarks comparing to native?

Thanks.

Max.

-
Best Regards.
Max Kosenko.
-- 
View this message in context: 
http://www.nabble.com/-ANN--SQLJet-1.0.0-released-tp25458690p25491910.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select records by specific YEAR

[P Kishor]

On Wed, Sep 16, 2009 at 9:16 AM, SHARMAQ Sistemas  wrote:
> Hi,
>
>
>
> I have a table called PEOPLE with 2 fiels:
>
>
>
> NAME --> TEXT 40
>
> BIRTH --> DATETIME
>
>
>
> With some records
>
>
>
> I want to filter all people with BIRTH = 1946, I'm trying this:
>
>
>
> SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)='1946';
>
>
>
> There are several people in this year, but recordset all time returns 0
> records. what's the problem?
>
>

The problem is that you probably haven't read the documentation of
datetime functions. Please do so at
http://www.sqlite.org/lang_datefunc.html



-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance in a case of big columns number

[P Kishor]

On Wed, Sep 16, 2009 at 9:23 AM, Dmitry Konishchev  wrote:
> Hello.
>
> Data in my program has such format that there is useful to place it in
> the database in many (thousands) columns. Please, answer me: does SQLite
> work more slowly when it has very big number of columns in the table
> (with the same total amount of data)?

How can anyone know the answer to that. Since you have provided so
little and so generic information, here is a short and generic answer
-- a database with thousands of columns is most likely wrong design.
Whether or not it works quicker or slower is something only you can
answer. Imagine a string with thousands of characters. Instead of
putting that string in one column, you break it up into thousands of
columns, each with a single character. Well, if you have to query the
999th character, probably querying just the 999th column will be
quicker than querying for a single string based on its 999th
character.

Nevertheless, your db design is most likely wrong.




-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite testing

[D . Richard Hipp]

On Sep 16, 2009, at 10:35 PM, James Cooper wrote:

> Your web page www.sqlite.org/testing.html mentions that each release  
> must pass an extensive set of tests " on multiple platforms and  
> under multiple compile-time configurations", but I have not been  
> abel to find out what platforms are tested.  In particular do you  
> test on
> · Solaris with the Sum compiler?
> · SCO OpenServer with its native compiler?
>


We test on linux (x86, amd64), mac (x86), win32, win64, and wince.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Force the use of a specified index?

[Bart Smissaert]

Is it possible to tell SQLite to use a specified index?
I know you can use the + to excludes fields being used in an index,
but this doesn't help me in this particular case. I remember a
discussion about this and that this option might be added to SQLite,
but couldn't find it anywhere.

RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance in a case of big columns number

[Dmitry Konishchev]

Hello.

Data in my program has such format that there is useful to place it in 
the database in many (thousands) columns. Please, answer me: does SQLite 
work more slowly when it has very big number of columns in the table 
(with the same total amount of data)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Select records by specific YEAR

[SHARMAQ Sistemas]

Hi,

 

I have a table called PEOPLE with 2 fiels:

 

NAME --> TEXT 40

BIRTH --> DATETIME

 

With some records

 

I want to filter all people with BIRTH = 1946, I'm trying this:

 

SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)='1946';

 

There are several people in this year, but recordset all time returns 0
records. what's the problem?

 

 

 

Paulo Cezar

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Force the use of a specified index?

[bartsmissaert]

Ah, thanks, that was the one and will give that a try.

RBS


>
> On Sep 17, 2009, at 5:02 AM, Bart Smissaert wrote:
>
>> Is it possible to tell SQLite to use a specified index?
>> I know you can use the + to excludes fields being used in an index,
>> but this doesn't help me in this particular case. I remember a
>> discussion about this and that this option might be added to SQLite,
>> but couldn't find it anywhere.
>
> There is the "INDEXED BY" clause. But many are of the opinion that
> this feature is prone to misuse.
>
>http://www.sqlite.org/lang_indexedby.html
>
> Dan.
>
>>
>> RBS
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Force the use of a specified index?

[Dan Kennedy]

On Sep 17, 2009, at 5:02 AM, Bart Smissaert wrote:

> Is it possible to tell SQLite to use a specified index?
> I know you can use the + to excludes fields being used in an index,
> but this doesn't help me in this particular case. I remember a
> discussion about this and that this option might be added to SQLite,
> but couldn't find it anywhere.

There is the "INDEXED BY" clause. But many are of the opinion that
this feature is prone to misuse.

   http://www.sqlite.org/lang_indexedby.html

Dan.

>
> RBS
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Two feature requests

[Itamar Syn-Hershko]

Tim,

In this context, you might find clucene useful. It's an IR lib written
completely in cross-platform C++. It could be used just for what you're
after using Queries and Filters. The git master HEAD is stable but still
work in progress, or you could download the latest official release (quite
outdated tho).

http://sourceforge.net/projects/clucene
http://clucene.git.sourceforge.net/git/gitweb.cgi?p=clucene/clucene;a=summar
y

Itamar. 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Thursday, September 17, 2009 12:16 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Two feature requests

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tim Romano wrote:
> Requesting these here, since I'm not quite sure how to go about it via 
> the WIKI (do you simply edit the request list there and prepend|append 
> your request to the list?)

Generally you should enter them as tickets setting the type as enhancement
request.  You can see the open feature requests using the new Fossil source
control system at:

  http://www.sqlite.org/src/info/084941461f

The old cvstrac system has its requests at:

  http://www.sqlite.org/cvstrac/rptview?rn=8

(Yes it would be nice if they were merged :)

> 1. An IFEMPTY(a,b) operator would be a convenience, analogous to
[...]
> 2. I would like to have a function that does a standard LIKE 
> comparison against a list of values:

First remember the 'Lite' part of SQLite.  Nothing is going to be added to
the core unless it is substantially useful and would be used by the vast
majority of users.  So far SQLite users have survived just fine without
these items :-)

There are separate contributed extensions where your requests are generally
more appropriate.  See the contrib page at http://www.sqlite.org/contrib and
in particular the last item - you'd find that author more receptive to your
requests.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqxVW4ACgkQmOOfHg372QSEFwCeLbJ9de0gOszqUgivMgWWdBRY
g04An2mY7/YDMjVa9KKbnh7uvFx4NYQo
=Ecf8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users