Re: [sqlite] Date and age calculations

2009-09-17 Thread 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

[sqlite] Running test after misc7-6.1.2

2009-09-17 Thread 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:

[sqlite] full outer join questions

2009-09-17 Thread 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

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

2009-09-17 Thread 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. >

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

2009-09-17 Thread 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

Re: [sqlite] replace extra carriage returns?

2009-09-17 Thread 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

Re: [sqlite] replace extra carriage returns?

2009-09-17 Thread 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

[sqlite] replace extra carriage returns?

2009-09-17 Thread 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

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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()

Re: [sqlite] cygwin and sqlite

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] cygwin and sqlite

2009-09-17 Thread 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

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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().

Re: [sqlite] Serious locking/blocking issue

2009-09-17 Thread 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,

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

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] cygwin and sqlite

2009-09-17 Thread 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.

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

2009-09-17 Thread 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

Re: [sqlite] cygwin and sqlite

2009-09-17 Thread 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

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

2009-09-17 Thread 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

[sqlite] cygwin and sqlite

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] [ANN] SQLJet 1.0.0 released

2009-09-17 Thread 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.

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

2009-09-17 Thread 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

Re: [sqlite] [ANN] SQLJet 1.0.0 released

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] [ANN] SQLJet 1.0.0 released

2009-09-17 Thread 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

Re: [sqlite] Select records by specific YEAR

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] Sqlite testing

2009-09-17 Thread 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

[sqlite] Force the use of a specified index?

2009-09-17 Thread 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] Performance in a case of big columns number

2009-09-17 Thread 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] Select records by specific YEAR

2009-09-17 Thread 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

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

2009-09-17 Thread 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

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

2009-09-17 Thread 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

Re: [sqlite] Two feature requests

2009-09-17 Thread 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