Re: [sqlite] cygwin and sqlite
Pavel Ivanov wrote:
>> 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
It worked! Fortunately I had already parameterized SQLITE3 as a
preference variable so I could have the same scripts run easily on Mac
OS and Windows. There are dozens of sqlite3 calls throughout the scripts.
My whole set of scripts that process raw data and load the database by
reading text files seem to work.
cygwin is as slow as I recall, however. I was writing expense scripts a
few years ago and abandoned it for MacOS Unix. I moved 100% to Mac OS.
(except for this project which I want to work on Mac, linux, and
Windows; my next goal is recoding it in Java with its Swing GUI, but I'm
just learning Java and Swing, but I'm on my way...).
Observed elapsed times on my two notebook computers for the same scripts
to load the database (using sqlite3 calls and lots of sed and awk
processing of thousands of lines of input data):
MacBook Mac OS X 10.5.8
2 GHz Intel Core Duo
1 GB memory:
17 minutes 46 seconds.
IBM ThinkPad
Windows XP (latest patches)
1.70 GHz, 512 MB memory:
6 hours 25 minutes 57 seconds
Fortunately, sqlite .dump and restoring from the resultant sql will be
able to be used for most of the heavy lifting when I'm done. Changes to
the data will come in small increments over time from then on. My dumpit
and restoreit scripts each take only seconds on both platforms for the
full set of current data.
Thanks!
> 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
[sqlite] New function request: sqlite3_busy_timeout_???(sqlite3*, int ms, int minSleepMs)
In my multithreaded applications I use sqlite3_busy_timeout(sqlite3*, int ms) to avoid failures on temporary locked databases. This works fine so far. The only problem is the very poor computer I have to use. I made a test to let 3 applications do nothing, but write to the database. When I use the function with ms=1 I get much poorer result, than I get when I program the behaviour of the function on my own. I think the reason are the sleep times between the write attempts. I get better results with longer sleep times, because I do not waste so much computing time on write attempts (Of course only valid on my poor machine). First Question: How long does sqlite3_busy_timeout(sqlite3*, int ms) sleep minimal and maximal? Second Questions: Would it be possible the add a function sqlite3_busy_timeout_???(sqlite3*, int ms, int minSleepMs) where the user can define the minimal sleeptime "minSleepMs" between two attempts? Regards, Stefan Best regards / Mit freundlichen Gruessen Stefan Breitholz --- Staeubli GmbH - Theodor-Schmidt-Str. 19 DE - 95448 Bayreuth Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126 mailto:s.breith...@staubli.com http://www.staubli.com Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl Kirschner --- This e-mail and any attachment (the 'message') are confidential and privileged and intended solely for the person or the entity to which it is adressed. If you have received it in error, please advise the sender by return e-mail and delete it immediately. Any use not in accordance with its purpose, any dissemination or reproduction, either whole or partial, by entities other than the intended recipient is strictly prohibited. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running test after misc7-6.1.2
On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote: > 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 > 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"? > The next test (misc7-7.0) is testing the sqlite3_busy_timeout() function. It is suppose to use usleep() (or the equivalent, depending on your OS) to sleep for short intervals and retry the connection until 2.0 seconds have elapsed, then it should give up and report an error. My first guess would be that usleep() is not working quite right on your system. What OS are you running? What compiler? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New function request: sqlite3_busy_timeout_???(sqlite3*, int ms, int minSleepMs)
On Sep 18, 2009, at 4:52 AM, s.breith...@staubli.com wrote: > In my multithreaded applications I use > sqlite3_busy_timeout(sqlite3*, int > ms) to avoid failures on temporary locked databases. > > This works fine so far. The only problem is the very poor computer I > have > to use. I made a test to let 3 applications do nothing, but write to > the > database. When I use the function with ms=1 I get much poorer > result, > than I get when I program the behaviour of the function on my own. > I think the reason are the sleep times between the write attempts. I > get > better results with longer sleep times, because I do not waste so much > computing time on write attempts (Of course only valid on my poor > machine). > > First Question: How long does sqlite3_busy_timeout(sqlite3*, int ms) > sleep minimal and maximal? The sqlite3_busy_timeout() function is implemented by using sqlite3_busy_handler() to make calls to sqlite3_sleep(). You can implement your on variation on this routine that will sleep as little or as much as you want. Look at the source code. The implementation of sqltie3_busy_timeout() is not that complicated. You should be able to see exactly what it is doing. Make a copy of the code and modify your copy to do whatever you want. > > Second Questions: Would it be possible the add a function > sqlite3_busy_timeout_???(sqlite3*, int ms, int minSleepMs) > where the user can define the minimal sleeptime "minSleepMs" between > two > attempts? > > Regards, > Stefan > > > Best regards / Mit freundlichen Gruessen > > Stefan Breitholz > > --- > Staeubli GmbH - Theodor-Schmidt-Str. 19 > DE - 95448 Bayreuth > Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126 > mailto:s.breith...@staubli.com > http://www.staubli.com > > Registered under HRB 175 - Local Court Bayreuth - Managing Director: > Karl Kirschner > --- > > > This e-mail and any attachment (the 'message') are confidential and > privileged and intended solely for the person or the entity to which > it is adressed. If you have received it in error, please advise the > sender by return e-mail and delete it immediately. Any use not in > accordance with its purpose, any dissemination or reproduction, > either whole or partial, by entities other than the intended > recipient is strictly prohibited. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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] List of active savepoints names
Hello all! I am searching some API function for get list of active savepoint names. Is this possible? Thank you! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indexes problem in unicode extension support
Hello!
I'm using extension for base unicode support
(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two releases find
the problem with indexes by columns with redefined NOCASE collation
(groups.name autoindex and composite index with
const_telephony_direction.name):
=
$ sqlite3 :memory:
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE groups
...> (
...> name TEXT collate NOCASE UNIQUE NOT NULL DEFAULT ''
...> );
sqlite> INSERT INTO "groups" VALUES('Администраторы');
sqlite> CREATE TABLE const_telephony_direction (
...> name text not null,
...> destcode text not null
...> );
sqlite> INSERT INTO "const_telephony_direction"
VALUES('Совинтел_МГМН_старые_кмст','7485');
sqlite> CREATE INDEX const_telephony_direction_complex_idx on
const_telephony_direction(name,destcode);
sqlite> COMMIT;
sqlite>
sqlite> pragma integrity_check;
rowid 1 missing from index sqlite_autoindex_groups_1
=
Without indexes on redefined NOCASE collated fields this database is correct:
=
$ sqlite3 :memory:
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE groups
...> (
...> name TEXT collate NOCASE NOT NULL DEFAULT ''
...> );
sqlite> INSERT INTO "groups" VALUES('Администраторы');
sqlite> CREATE TABLE const_telephony_direction (
...> name text not null,
...> destcode text not null
...> );
sqlite> INSERT INTO "const_telephony_direction"
VALUES('Совинтел_МГМН_старые_кмст','7485');
sqlite> CREATE INDEX const_telephony_direction_complex_idx on
const_telephony_direction(destcode);
sqlite> COMMIT;
sqlite>
sqlite> pragma integrity_check;
ok
=
Can anybody help me to to fix it?
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
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?
If neither index individually offers a performance boost, it's possible a single multi-column index might be better. Sam On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert wrote: > 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 > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running test after misc7-6.1.2
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > It is suppose to use usleep() (or the equivalent, depending > on your OS) to sleep for short intervals Note that on non-Windows platforms the default is to use sleep() which has a one second granularity. For people who have directly included the amalgamation and not defined any of the HAVE's like HAVE_USLEEP, sleep is used. (If configure is used then it will define HAVE_USLEEP.) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqzkIwACgkQmOOfHg372QQT0QCfdvhqWAy7Q66Nbk+2T5X/rEUP OCMAoORIbTvSPfNmtzCfP78TY2DLbgGx =fzbN -END PGP SIGNATURE- ___ 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?
I was trying to force the use of a multi-column index. Will have a better look and see what is going on here. For now I get best performance with a 2-stage approach with the use of a intermediate temp table. Will post the exact details of this later. RBS > If neither index individually offers a performance boost, it's possible a > single multi-column index might be better. > Sam > > > On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert > wrote: > >> 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 >> >> >> > ___ > 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] cygwin and sqlite
> MacBook Mac OS X 10.5.8
> 2 GHz Intel Core Duo
> 1 GB memory:
> 17 minutes 46 seconds.
>
> IBM ThinkPad
> Windows XP (latest patches)
> 1.70 GHz, 512 MB memory:
> 6 hours 25 minutes 57 seconds
Windows is very slow in starting new processes if compared to any Unix
system (especially if compared Windows + 512 MB and Unix + 1 GB). In
cygwin starting new processes even slower because for some reason
emulating fork() involves starting 2 processes one of which dies
immediately. And bash scripts use processes a lot especially with Unix
paradigm when for each small action you start new program (like sed,
awk, test, true and whole lot of others). Thus bash scripts on cygwin
will be slow unavoidably.
But I'm digressing. This is subject for some other mailing list. :)
Pavel
On Fri, Sep 18, 2009 at 3:26 AM, John wrote:
> Pavel Ivanov wrote:
>>> 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
>
> It worked! Fortunately I had already parameterized SQLITE3 as a
> preference variable so I could have the same scripts run easily on Mac
> OS and Windows. There are dozens of sqlite3 calls throughout the scripts.
>
> My whole set of scripts that process raw data and load the database by
> reading text files seem to work.
>
> cygwin is as slow as I recall, however. I was writing expense scripts a
> few years ago and abandoned it for MacOS Unix. I moved 100% to Mac OS.
> (except for this project which I want to work on Mac, linux, and
> Windows; my next goal is recoding it in Java with its Swing GUI, but I'm
> just learning Java and Swing, but I'm on my way...).
>
> Observed elapsed times on my two notebook computers for the same scripts
> to load the database (using sqlite3 calls and lots of sed and awk
> processing of thousands of lines of input data):
>
> MacBook Mac OS X 10.5.8
> 2 GHz Intel Core Duo
> 1 GB memory:
> 17 minutes 46 seconds.
>
> IBM ThinkPad
> Windows XP (latest patches)
> 1.70 GHz, 512 MB memory:
> 6 hours 25 minutes 57 seconds
>
> Fortunately, sqlite .dump and restoring from the resultant sql will be
> able to be used for most of the heavy lifting when I'm done. Changes to
> the data will come in small increments over time from then on. My dumpit
> and restoreit scripts each take only seconds on both platforms for the
> full set of current data.
>
> Thanks!
>
>> 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 c
Re: [sqlite] full outer join questions
> Now the strange thing is that this query returns the correct number of rows, > but all the columns from the employee-table are empty. There's no "employee-table" in your query. > If I change "UNION" to "UNION ALL" the join works as expected. > Is there an explanation for this behavior ? As we don't see your query we can't explain what's going on there. > Also when I add an order clause, the left columns are all made empty ? Justing adding ORDER BY changes all "left" (from what?) columns in all rows to NULL? No way! Show the actual output from sqlite3 command line utility please. > Is it uberhaupt possible to order the resulting table on the column PID > and preferable get just 1 PID column ? Sure, why not? But again no query - no advice on how to correct it to achieve what you want. Pavel On Thu, Sep 17, 2009 at 6:39 PM, Stef Mientki wrote: > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] List of active savepoints names
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lukas Gebauer wrote: > I am searching some API function for get list of active savepoint > names. Is this possible? Thank you! Since your code is creating and releasing savepoints, why not just record them in your code? Also IIRC you can also have duplicate names. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqzkggACgkQmOOfHg372QQfuACgpNEzkhdLLDnRw5HNGEpAH6Am tIUAnjnlBEaD6V72b91QALN4L4oRwjfv =O3HE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] List of active savepoints names
> I am searching some API function for get list of active savepoint > names. Is this possible? Thank you! No, this is not possible. Why do you need it in the first place? Pavel On Fri, Sep 18, 2009 at 5:55 AM, Lukas Gebauer wrote: > > Hello all! > > I am searching some API function for get list of active savepoint > names. Is this possible? Thank you! > > > > -- > Lukas Gebauer. > > E-mail: gebau...@mlp.cz > http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. > > ___ > 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] Running test after misc7-6.1.2
On Fri, Sep 18, 2009 at 2:35 AM, D. Richard Hipp wrote: > > On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote: > >> 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 >> 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"? >> > > The next test (misc7-7.0) is testing the sqlite3_busy_timeout() > function. It is suppose to use usleep() (or the equivalent, depending > on your OS) to sleep for short intervals and retry the connection > until 2.0 seconds have elapsed, then it should give up and report an > error. My first guess would be that usleep() is not working quite > right on your system. I see. > > What OS are you running? What compiler? This is on a (Dreamhost) virtual server: $ uname -a Linux ps12345 2.6.22.19-grsec2.1.11-vs2.2.0.7 #10 SMP Tue Aug 4 14:56:37 PDT 2009 x86_64 GNU/Linux $ cc --version gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) Thanks! Stephan > D. Richard Hipp > d...@hwaci.com > > ___ > 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] Indexes problem in unicode extension support
Alexey, >I'm using extension for base unicode support >(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two >releases find the problem with indexes by columns with redefined >NOCASE collation This code has many problems and the version on your site (the same version is available elsewhere) doesn't work as expected. NOCASE is not the only function with problems, there are more with UPPER, LOWER. Even some tries have wrong data. I had to recompile three of them. I was in need of such extension (and a bit more), started looking at open source code and then began work on it. Unfortunately I had to switch to other boring but urgent tasks, and for really much longer than I would have liked. I now have some time to finish it and it should be ready for beta anytime soon. The module offers some universal Unicode support: UPPER LOWER UNACCENT FOLD LIKE(unaccented version) GLOB(unaccented version) TYPOS (unaccented lowercased Damerau-Levenshtein distance on strings with support for '_' and trailing '%' as in LIKE) NOCASE LETTERS (a simple wrapper to a Windows function for locale-independant unaccented collation) specific handling for small German sharp s 'ß' I compile for Windows 32 with MinGW gcc but it shouldn't be very hard for someone to make it work on another OS as well. JcD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] blocking when locking
I'm writing this system wherein I want operations performed on the database to block when a lock cannot be achieved, and I'm looking at my options. This system that has multiple processes accessing a single sqlite file with a single database with a single table. I was disappointed to find out yesterday that when a function in the API tries to achieve a lock on the db, it doesn't block, and put the request in a queue, it just returns an error. Since then I've come to realize that sqlite doesn't have such a blocking feature. Is that correct? I was thinking that a good solution would be to have a lock file, with POSIX locks (I'm doing this in Linux) on it whenever one tries to access the db in such a way that might return an SQLITE_LOCKED error. Is this a good solution for the system I have setup? Is there a better one? To be clear, my idea of blocking is as follows: if one tries to achieve a lock, and it is not possible, the request is put into a queue, and the caller stops consuming cycles. Locks are then granted (when feasible) in the queue in the order that they were requested. Simulating blocking by looping back to the API call on every SQLITE_LOCKED error doesn't count, because lock requests are not put into a queue, and it can be very expensive on cycles. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
> Since then I've come to realize that > sqlite doesn't have such a blocking feature. Is that correct? Yes, that's correct. >I was thinking that a good solution would be to have a lock file, > with POSIX locks (I'm doing this in Linux) on it whenever one tries to > access the db in such a way that might return an SQLITE_LOCKED error. Is > this a good solution for the system I have setup? Is there a better one? What in this paragraph differs from how SQLite operates? >To be clear, my idea of blocking is as follows: if one tries to > achieve a lock, and it is not possible, the request is put into a queue, > and the caller stops consuming cycles. Locks are then granted (when > feasible) in the queue in the order that they were requested. The problem is who will grant these locks? You want to launch some separate process which will contain information about all processes requested locks and will communicate somehow with these processes to tell them that they can continue in acquiring the lock? Pavel On Fri, Sep 18, 2009 at 12:13 PM, Angus March wrote: > I'm writing this system wherein I want operations performed on the > database to block when a lock cannot be achieved, and I'm looking at my > options. This system that has multiple processes accessing a single > sqlite file with a single database with a single table. I was > disappointed to find out yesterday that when a function in the API tries > to achieve a lock on the db, it doesn't block, and put the request in a > queue, it just returns an error. Since then I've come to realize that > sqlite doesn't have such a blocking feature. Is that correct? > I was thinking that a good solution would be to have a lock file, > with POSIX locks (I'm doing this in Linux) on it whenever one tries to > access the db in such a way that might return an SQLITE_LOCKED error. Is > this a good solution for the system I have setup? Is there a better one? > > To be clear, my idea of blocking is as follows: if one tries to > achieve a lock, and it is not possible, the request is put into a queue, > and the caller stops consuming cycles. Locks are then granted (when > feasible) in the queue in the order that they were requested. Simulating > blocking by looping back to the API call on every SQLITE_LOCKED error > doesn't count, because lock requests are not put into a queue, and it > can be very expensive on cycles. > ___ > 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] Running test after misc7-6.1.2
On Fri, Sep 18, 2009 at 7:51 AM, Stephan Wehner wrote: > On Fri, Sep 18, 2009 at 2:35 AM, D. Richard Hipp wrote: >> >> On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote: >> >>> 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 >>> 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"? >>> >> >> The next test (misc7-7.0) is testing the sqlite3_busy_timeout() >> function. It is suppose to use usleep() (or the equivalent, depending >> on your OS) to sleep for short intervals and retry the connection >> until 2.0 seconds have elapsed, then it should give up and report an >> error. My first guess would be that usleep() is not working quite >> right on your system. > > I see. > >> >> What OS are you running? What compiler? > > This is on a (Dreamhost) virtual server: > > $ uname -a > Linux ps12345 2.6.22.19-grsec2.1.11-vs2.2.0.7 #10 SMP Tue Aug 4 > 14:56:37 PDT 2009 x86_64 GNU/Linux > $ cc --version > gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) > > Thanks! Would you have a tip how to continue here? Stephan > > > Stephan > > >> D. Richard Hipp >> d...@hwaci.com >> >> ___ >> 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 > -- 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] Force the use of a specified index?
> Did something in the documentation make > you think SQLite wouldn't use a > multi-column index unless you forced it ? No, but I just noticed it didn't use the index I thought would be best. As it turned out it looks I was wrong in that that index didn't give the quickest result. RBS > > On 18 Sep 2009, at 1:51pm, bartsmissa...@blueyonder.co.uk wrote: > >> I was trying to force the use of a multi-column index. > > Did something in the documentation make you think SQLite wouldn't use > a multi-column index unless you forced it ? There is rarely any point > in forcing any particular index. The part of SQLite which determines > which index to use is very good at working out which index is best for > the operation it's doing. > > Simon. > ___ > 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?
On 18 Sep 2009, at 1:51pm, bartsmissa...@blueyonder.co.uk wrote: > I was trying to force the use of a multi-column index. Did something in the documentation make you think SQLite wouldn't use a multi-column index unless you forced it ? There is rarely any point in forcing any particular index. The part of SQLite which determines which index to use is very good at working out which index is best for the operation it's doing. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Pavel Ivanov wrote: > >>To be clear, my idea of blocking is as follows: if one tries to >> achieve a lock, and it is not possible, the request is put into a queue, >> and the caller stops consuming cycles. Locks are then granted (when >> feasible) in the queue in the order that they were requested. >> > > The problem is who will grant these locks? You want to launch some > separate process which will contain information about all processes > requested locks and will communicate somehow with these processes to > tell them that they can continue in acquiring the lock? > > The kernel grants them: http://www.manpagez.com/man/2/flock . Or I might use fcntl(). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
>The kernel grants them: http://www.manpagez.com/man/2/flock . Or I > might use fcntl(). That's why I've asked what is different here from what SQLite already does because SQLite uses fcntl() on database file already. You can try to change it to flock() of course but be aware that SQLite needs more lock grades than flock() provides. Pavel On Fri, Sep 18, 2009 at 1:07 PM, Angus March wrote: > Pavel Ivanov wrote: >> >>> To be clear, my idea of blocking is as follows: if one tries to >>> achieve a lock, and it is not possible, the request is put into a queue, >>> and the caller stops consuming cycles. Locks are then granted (when >>> feasible) in the queue in the order that they were requested. >>> >> >> The problem is who will grant these locks? You want to launch some >> separate process which will contain information about all processes >> requested locks and will communicate somehow with these processes to >> tell them that they can continue in acquiring the lock? >> >> > The kernel grants them: http://www.manpagez.com/man/2/flock . Or I > might use fcntl(). > > ___ > 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] blocking when locking
Pavel Ivanov wrote: >>The kernel grants them: http://www.manpagez.com/man/2/flock . Or I >> might use fcntl(). >> > > That's why I've asked what is different here from what SQLite already > does because SQLite uses fcntl() on database file already. You can try > Then it must use fcntl() with F_SETLK which doesn't block. If it used F_SETLKW then it would block, and I guess I wouldn't be in this mess. Hell if I know why they use fcntl() for locks, and don't even give you the option to block. > to change it to flock() of course but be aware that SQLite needs more > lock grades than flock() provides. > I think we are a long way from me screwing around with sqlite's source. What I'm trying for is a solution with my own source code. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
> Hell if I know why they use fcntl() for locks, and don't even give > you the option to block. I think because they need to detect dead locks. BTW, I believe in case of dead lock even busy_handler will not be called, just SQLITE_BUSY is returned... >I think we are a long way from me screwing around with sqlite's > source. What I'm trying for is a solution with my own source code. Then neither flock() nor fcntl() will not help you. Your own code have control only over the busy handler which gains control only when database is locked. And it should understand somehow when other process not calling any busy handlers unlocks database... And I believe there's no solution here any better than simple sleep-and-retry. Pavel On Fri, Sep 18, 2009 at 1:47 PM, Angus March wrote: > Pavel Ivanov wrote: >>> The kernel grants them: http://www.manpagez.com/man/2/flock . Or I >>> might use fcntl(). >>> >> >> That's why I've asked what is different here from what SQLite already >> does because SQLite uses fcntl() on database file already. You can try >> > > Then it must use fcntl() with F_SETLK which doesn't block. If it > used F_SETLKW then it would block, and I guess I wouldn't be in this > mess. Hell if I know why they use fcntl() for locks, and don't even give > you the option to block. > >> to change it to flock() of course but be aware that SQLite needs more >> lock grades than flock() provides. >> > I think we are a long way from me screwing around with sqlite's > source. What I'm trying for is a solution with my own source code. > ___ > 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] blocking when locking
Pavel Ivanov wrote: >> Hell if I know why they use fcntl() for locks, and don't even give >> you the option to block. >> > > I think because they need to detect dead locks. BTW, I believe in case > of dead lock even busy_handler will not be called, just SQLITE_BUSY is > returned... > I guess that makes sense, in cases where multiple tables are involved. >>I think we are a long way from me screwing around with sqlite's >> source. What I'm trying for is a solution with my own source code. >> > > Then neither flock() nor fcntl() will not help you. Your own code have > control only over the busy handler which gains control only when > database is locked. And it should understand somehow when other > process not calling any busy handlers unlocks database... And I > believe there's no solution here any better than simple > sleep-and-retry. > How does this preclude me from coming up w/my own lock file with POSIX locks? If a bunch of process start making incompatible requests on a single lock file, then they'll be queued and processed in order. I don't see how you can have a deadlock when you have multiple processes putting locks on a single, entire file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Angus March wrote: > Pavel Ivanov wrote: >>> Hell if I know why they use fcntl() for locks, and don't even give >>> you the option to block. >>> >> >> I think because they need to detect dead locks. BTW, I believe in >> case of dead lock even busy_handler will not be called, just >> SQLITE_BUSY is returned... >> >I guess that makes sense, in cases where multiple tables are > involved. It doesn't matter whether a single or multiple tables are involved - SQLite locks at the database level. The deadlock occurs when 1) transaction A starts to read, taking a SHARED lock; 2) transaction B prepares to write, taking a RESERVED lock; 3) transaction A now wants to write, too. At this point, transaction B is waiting for all readers (one of which is transaction A) to clear, while transaction A waits for the previous writer (which is transaction B) to complete. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
>> I think because they need to detect dead locks. BTW, I believe in case >> of dead lock even busy_handler will not be called, just SQLITE_BUSY is >> returned... >> >I guess that makes sense, in cases where multiple tables are involved. No, that makes sense when you're starting deferred transaction, then do selects, then do update. If this is done by several processes they can deadlock. >How does this preclude me from coming up w/my own lock file with > POSIX locks? If a bunch of process start making incompatible requests on > a single lock file, then they'll be queued and processed in order. I > don't see how you can have a deadlock when you have multiple processes > putting locks on a single, entire file. You mean you will lock this extra-file before doing any update and unlock when update is done? Then ok, it will work. But again be aware of possible dead locks. Pavel On Fri, Sep 18, 2009 at 2:27 PM, Angus March wrote: > Pavel Ivanov wrote: >>> Hell if I know why they use fcntl() for locks, and don't even give >>> you the option to block. >>> >> >> I think because they need to detect dead locks. BTW, I believe in case >> of dead lock even busy_handler will not be called, just SQLITE_BUSY is >> returned... >> > I guess that makes sense, in cases where multiple tables are involved. > >>> I think we are a long way from me screwing around with sqlite's >>> source. What I'm trying for is a solution with my own source code. >>> >> >> Then neither flock() nor fcntl() will not help you. Your own code have >> control only over the busy handler which gains control only when >> database is locked. And it should understand somehow when other >> process not calling any busy handlers unlocks database... And I >> believe there's no solution here any better than simple >> sleep-and-retry. >> > > How does this preclude me from coming up w/my own lock file with > POSIX locks? If a bunch of process start making incompatible requests on > a single lock file, then they'll be queued and processed in order. I > don't see how you can have a deadlock when you have multiple processes > putting locks on a single, entire file. > ___ > 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] Indexes problem in unicode extension support
Hello! On Friday 18 September 2009 20:05:15 Jean-Christophe Deschamps wrote: > Alexey, > > >I'm using extension for base unicode support > >(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two > >releases find the problem with indexes by columns with redefined > >NOCASE collation > > This code has many problems and the version on your site (the same > version is available elsewhere) doesn't work as expected. NOCASE is > not the only function with problems, there are more with UPPER, > LOWER. Even some tries have wrong data. I had to recompile three of them. This module is based on code from http://ioannis.mpsounds.net/blog/2007/12/19/sqlite-native-unicode-like-support/ > I now have some time to finish it and it should be ready for beta > anytime soon. > > I compile for Windows 32 with MinGW gcc but it shouldn't be very hard > for someone to make it work on another OS as well. I can test it on debian lenny. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Pavel Ivanov wrote: > >>How does this preclude me from coming up w/my own lock file with >> POSIX locks? If a bunch of process start making incompatible requests on >> a single lock file, then they'll be queued and processed in order. I >> don't see how you can have a deadlock when you have multiple processes >> putting locks on a single, entire file. >> > > You mean you will lock this extra-file before doing any update and > unlock when update is done? Then ok, it will work. But again be aware > of possible dead locks. > You mean deadlocks are still possible in that scenario? How? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Igor Tandetnik wrote: > Angus March wrote: > >> Pavel Ivanov wrote: >> Hell if I know why they use fcntl() for locks, and don't even give you the option to block. >>> I think because they need to detect dead locks. BTW, I believe in >>> case of dead lock even busy_handler will not be called, just >>> SQLITE_BUSY is returned... >>> >>> >>I guess that makes sense, in cases where multiple tables are >> involved. >> > > It doesn't matter whether a single or multiple tables are involved - > SQLite locks at the database level. The deadlock occurs when 1) > transaction A starts to read, taking a SHARED lock; 2) transaction B > prepares to write, taking a RESERVED lock; 3) transaction A now wants to > write, too. At this point, transaction B is waiting for all readers (one > of which is transaction A) to clear, while transaction A waits for the > previous writer (which is transaction B) to complete. > Yes, I see. So what is key to the problem is that someone tries to change their read lock to a write lock. I guess I just thought that the kernel that manages fcntl() would have a way of dealing with this. Can this situation not be averted if at step 3, transaction A releases its read lock before requesting a write lock? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
>> You mean you will lock this extra-file before doing any update and >> unlock when update is done? Then ok, it will work. But again be aware >> of possible dead locks. >> > >You mean deadlocks are still possible in that scenario? How? I mean just that I don't know exactly what do you want to do but dead locks should be always kept in mind. Of course if you surround each and every update and insert by locking of this extra-file and will never start transaction earlier than locking the file then probably you're good (I could miss some conditions here). Pavel On Fri, Sep 18, 2009 at 2:52 PM, Angus March wrote: > Pavel Ivanov wrote: >> >>> How does this preclude me from coming up w/my own lock file with >>> POSIX locks? If a bunch of process start making incompatible requests on >>> a single lock file, then they'll be queued and processed in order. I >>> don't see how you can have a deadlock when you have multiple processes >>> putting locks on a single, entire file. >>> >> >> You mean you will lock this extra-file before doing any update and >> unlock when update is done? Then ok, it will work. But again be aware >> of possible dead locks. >> > > You mean deadlocks are still possible in that scenario? How? > ___ > 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] blocking when locking
Angus March wrote: >Yes, I see. So what is key to the problem is that someone tries to > change their read lock to a write lock. I guess I just thought that > the kernel that manages fcntl() would have a way of dealing with > this. Can this situation not be averted if at step 3, transaction A > releases its read lock before requesting a write lock? Then it wouldn't be much of a transaction, now would it? Imagine the classic example, where a transaction first verifies that the balance in a bank account is sufficient, then performs a withdrawal. If it relinquishes all locks between these two steps, then somebody else may record a withdrawal from that account, so that the write operation would then make the balance negative, thus violating an invariant. Of course, if that's what the application wants, it can simply perform the read and the write operations in two separate transactions. Igor Tandetnik ___ 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?
On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote: >> Did something in the documentation make >> you think SQLite wouldn't use a >> multi-column index unless you forced it ? > > No, but I just noticed it didn't use the index I thought would be > best. As > it turned out it looks I was wrong in that that index didn't give the > quickest result. Ah. Okay, that's fine. It can be quite surprising what's best. Glad you got a good result. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Igor Tandetnik wrote: > Angus March wrote: > >>Yes, I see. So what is key to the problem is that someone tries to >> change their read lock to a write lock. I guess I just thought that >> the kernel that manages fcntl() would have a way of dealing with >> this. Can this situation not be averted if at step 3, transaction A >> releases its read lock before requesting a write lock? >> > > Then it wouldn't be much of a transaction, now would it? Imagine the > classic example, where a transaction first verifies that the balance in > a bank account is sufficient, then performs a withdrawal. If it > relinquishes all locks between these two steps, then somebody else may > record a withdrawal from that account, so that the write operation would > then make the balance negative, thus violating an invariant. > Oh, when you said "transaction" you meant a transaction. I find that term (among a few others) gets used rather liberally in sqlite subculture. Yes, fortunately, fcntl() can fail with a EDEADLK error wherein "It was detected that the specified F_SETLKW command would cause a deadlock." Maybe that would be a better time for functions to fail with SQLITE_LOCKED, making the blocking less than perfect, but but at least there'd be *some* of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Most wanted features of SQLite ?
So if you had a team of programmers to write something like SQLite which didn't have the drawbacks SQLite has, which drawbacks would you identify ? I'm asking not about minor faults with specific SQLite library calls, but about the sort of things which require rewriting from the ground up. The ones that seem to come up most often here are * Some sort of synchronisation support * Support for multiple concurrent clients/processes * Unicode support from the ground up Please note: I am not suggesting that any of these problems are easy to solve. I'm just interested in what problems people want solved. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > * Unicode support from the ground up SQLite already has "unicode support from the ground up". Try using non-Unicode strings and you'll see! The issue some developers have is that they also want collations, case comparisons etc but are not prepared to use the ICU extension which provides about the most correct implementation of those. ie they want something less accurate and smaller/quicker. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqz6HYACgkQmOOfHg372QQLCgCgnl8qDTaX4GExUyH5hJdCypGL m2wAmwRHO9Qig+yLW8WeORNhogn8sO6R =lEfE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > Simon Slavin wrote: >> * Unicode support from the ground up > > SQLite already has "unicode support from the ground up". Try using > non-Unicode strings and you'll see! SQLite's indexing correctly understands how to order Unicode strings ? It can do upper- and lower-case ordering in languages like Hebrew ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Stored Procedures Noah Simon Slavin-2 wrote: > > So if you had a team of programmers to write something like SQLite > which didn't have the drawbacks SQLite has, which drawbacks would you > identify ? I'm asking not about minor faults with specific SQLite > library calls, but about the sort of things which require rewriting > from the ground up. The ones that seem to come up most often here are > > * Some sort of synchronisation support > * Support for multiple concurrent clients/processes > * Unicode support from the ground up > > Please note: I am not suggesting that any of these problems are easy > to solve. I'm just interested in what problems people want solved. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Most-wanted-features-of-SQLite---tp25514570p25515213.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] Force the use of a specified index?
This is what I am dealing with: 2 tables with exactly the same schema (but could be slightly different, so can't put in same table): CREATE TABLE TABLE1( [PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER PRIMARY KEY, [READ_CODE] TEXT, [ADDED_DATE] TEXT, [START_DATE] TEXT) The last 2 date fields are in the format -mm-dd Again in both tables the following indexes: CREATE INDEX IDX_TABLE1_PATIENT_ID ON TABLE1(PATIENT_ID) CREATE INDEX IDX_TABLE1_PATIENT_ID_START_DATE ON TABLE1(PATIENT_ID, START_DATE) CREATE INDEX IDX_TABLE1_PATIENT_ID_ENTRY_ID ON TABLE1(PATIENT_ID, ENTRY_ID) CREATE INDEX TABLE1_START_DATE ON TABLE1(START_DATE) Then some delete SQL's will be run so that in the end PATIENT_ID is unique in both tables. Then the SQL I was trying to improve: DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE JULIANDAY(TABLE2.START_DATE, '-14 month') > JULIANDAY(TABLE1.START_DATE) AND TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) The index it will use for this SQL is IDX_TABLE1_PATIENT_ID_ENTRY_ID and that surprises me as ENTRY_ID is not in the above SQL. If I drop that index then it will use IDX_TABLE1_PATIENT_ID and only if that is dropped as well will it use IDX_TABLE1_PATIENT_ID_START_DATE, which I thought would be the best, but turns out to be no better than the first. Whatever way I do this it is slow and I can do it a lot quicker by doing this: CREATE TEMP TABLE DATE_COMPARE_TEMP([E_ID] INTEGER) INSERT INTO DATE_COMPARE_TEMP (E_ID) SELECT T2.ENTRY_ID FROM TABLE2 T2 INNER JOIN TABLE1 T2 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE julianday(T2.START_DATE, '+15 month') > julianday(T1.START_DATE) CREATE INDEX IDX1_DATE_COMPARE_TEMP_E_ID ON DATE_COMPARE_TEMP(E_ID) analyze DATE_COMPARE_TEMP and then the delete SQL like this: DELETE FROM TABLE2 WHERE ENTRY_ID NOT IN (SELECT E_ID FROM DATE_COMPARE_TEMP) Although this involves a third temp table this method is about twice as fast as the first one. Maybe that is just the way it is and there just is no way to do this as fast without the intermediate temp table, but I just wondered. Again there is no problem here as the second method is simple and fast. Just trying to increase my understanding of SQLite and indexes. RBS On Fri, Sep 18, 2009 at 8:25 PM, Simon Slavin wrote: > > On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote: > >>> Did something in the documentation make >>> you think SQLite wouldn't use a >>> multi-column index unless you forced it ? >> >> No, but I just noticed it didn't use the index I thought would be >> best. As >> it turned out it looks I was wrong in that that index didn't give the >> quickest result. > > Ah. Okay, that's fine. It can be quite surprising what's best. Glad > you got a good result. > > Simon. > ___ > 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] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 00:43:18 Noah Hart wrote: > Stored Procedures There are Tiny C compiler extension and realization of stored procedures for SQLite 2 and Lua extension and other. So you can use one or all of these. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> Simon Slavin wrote: >>> * Unicode support from the ground up >> >> SQLite already has "unicode support from the ground up". Try using >> non-Unicode strings and you'll see! > > SQLite's indexing correctly understands how to order Unicode > strings ? With ICU extension enabled and correct collation specified, yes. Note that the correct ordering of Unicode strings is locale-dependent. > It can do upper- and lower-case ordering in languages like > Hebrew ? To the best of my knowledge, Hebrew doesn't have the notion of case. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > > > Simon Slavin wrote: > >> * Unicode support from the ground up > > > > SQLite already has "unicode support from the ground up". Try using > > non-Unicode strings and you'll see! > > SQLite's indexing correctly understands how to order Unicode > strings ? It can do upper- and lower-case ordering in languages like > Hebrew ? Like he said: > > The issue some developers have is that they also want collations, case > > comparisons etc but are not prepared to use the ICU extension which provides > > about the most correct implementation of those. ie they want something less > > accurate and smaller/quicker. ___ 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?
*I'm leaving aside the rant that your first delete is not identical to combination of the select and delete in the second approach and select in second approach contains typos...* But did you try to combine your insert and delete statements from the second approach? This approach quicker because of exactly that - select and delete statements are independent whereas in first approach your select is executed again and again for each row in TABLE2. So just make it like this: DELETE FROM TABLE2 WHERE ENTRY_ID NOT IN (SELECT T2.ENTRY_ID FROM TABLE2 T2 INNER JOIN TABLE1 T1 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE julianday(T2.START_DATE, '+15 month') > julianday(T1.START_DATE) ) And about indexes: for this query index on (PATIENT_ID, START_DATE) doesn't do any better than on (PATIENT_ID) because SQLite must to check all rows with given PATIENT_ID anyway. And that is because START_DATE is in the query inside function call to julianday(). Index on 2 fields could help only if your condition was e.g. T2.START_DATE > T1.START_DATE. And in most cases there's no benefit creating index on 1 field (PATIENT_ID) when there's already index on 2 fields (PATIENT_ID, START_DATE) where PATIENT_ID is the first field. Pavel On Fri, Sep 18, 2009 at 4:46 PM, Bart Smissaert wrote: > This is what I am dealing with: > 2 tables with exactly the same schema (but could be slightly > different, so can't put in same table): > > CREATE TABLE TABLE1( > [PATIENT_ID] INTEGER, > [ENTRY_ID] INTEGER PRIMARY KEY, > [READ_CODE] TEXT, > [ADDED_DATE] TEXT, > [START_DATE] TEXT) > > The last 2 date fields are in the format -mm-dd > > Again in both tables the following indexes: > CREATE INDEX IDX_TABLE1_PATIENT_ID ON TABLE1(PATIENT_ID) > CREATE INDEX IDX_TABLE1_PATIENT_ID_START_DATE ON TABLE1(PATIENT_ID, > START_DATE) > CREATE INDEX IDX_TABLE1_PATIENT_ID_ENTRY_ID ON TABLE1(PATIENT_ID, ENTRY_ID) > CREATE INDEX TABLE1_START_DATE ON TABLE1(START_DATE) > > Then some delete SQL's will be run so that in the end PATIENT_ID is > unique in both tables. > > Then the SQL I was trying to improve: > > DELETE > FROM > TABLE2 > WHERE > PATIENT_ID NOT IN ( > SELECT > TABLE1.PATIENT_ID > FROM > TABLE1 > WHERE > JULIANDAY(TABLE2.START_DATE, '-14 month') > > JULIANDAY(TABLE1.START_DATE) AND > TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) > > The index it will use for this SQL is IDX_TABLE1_PATIENT_ID_ENTRY_ID > and that surprises me as ENTRY_ID is not in the above SQL. If I drop > that index then it will > use IDX_TABLE1_PATIENT_ID and only if that is dropped as well will it > use IDX_TABLE1_PATIENT_ID_START_DATE, which I thought would be the > best, but turns > out to be no better than the first. Whatever way I do this it is slow > and I can do it a lot quicker by doing this: > > CREATE TEMP TABLE DATE_COMPARE_TEMP([E_ID] INTEGER) > > INSERT INTO DATE_COMPARE_TEMP (E_ID) > SELECT T2.ENTRY_ID FROM > TABLE2 T2 INNER JOIN TABLE1 T2 ON > (T1.PATIENT_ID = T2.PATIENT_ID) > WHERE julianday(T2.START_DATE, '+15 month') > > julianday(T1.START_DATE) > > CREATE INDEX IDX1_DATE_COMPARE_TEMP_E_ID ON DATE_COMPARE_TEMP(E_ID) > > analyze DATE_COMPARE_TEMP > > and then the delete SQL like this: > > DELETE > FROM > TABLE2 > WHERE > ENTRY_ID NOT IN > (SELECT E_ID FROM DATE_COMPARE_TEMP) > > Although this involves a third temp table this method is about twice > as fast as the first one. > Maybe that is just the way it is and there just is no way to do this > as fast without the intermediate temp table, but I just wondered. > Again there is no problem here as the second method is simple and > fast. Just trying to increase my understanding of SQLite and indexes. > > > RBS > > > > > On Fri, Sep 18, 2009 at 8:25 PM, Simon Slavin > wrote: >> >> On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote: >> Did something in the documentation make you think SQLite wouldn't use a multi-column index unless you forced it ? >>> >>> No, but I just noticed it didn't use the index I thought would be >>> best. As >>> it turned out it looks I was wrong in that that index didn't give the >>> quickest result. >> >> Ah. Okay, that's fine. It can be quite surprising what's best. Glad >> you got a good result. >> >> Simon. >> ___ >> 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?
Thanks, will have a look at your suggestion and yes, I had a feeling I was overlooking some elemental things here. I typed it out all bit quick (hence the typo's and difference in the deletes), but I thought it would make clear what was going on. Will test now and see if your suggestion is indeed quicker. RBS On Fri, Sep 18, 2009 at 10:04 PM, Pavel Ivanov wrote: > *I'm leaving aside the rant that your first delete is not identical to > combination of the select and delete in the second approach and select > in second approach contains typos...* > > But did you try to combine your insert and delete statements from the > second approach? This approach quicker because of exactly that - > select and delete statements are independent whereas in first approach > your select is executed again and again for each row in TABLE2. So > just make it like this: > > DELETE > FROM > TABLE2 > WHERE > ENTRY_ID NOT IN > (SELECT T2.ENTRY_ID FROM > TABLE2 T2 INNER JOIN TABLE1 T1 ON > (T1.PATIENT_ID = T2.PATIENT_ID) > WHERE julianday(T2.START_DATE, '+15 month') > > julianday(T1.START_DATE) > ) > > And about indexes: for this query index on (PATIENT_ID, START_DATE) > doesn't do any better than on (PATIENT_ID) because SQLite must to > check all rows with given PATIENT_ID anyway. And that is because > START_DATE is in the query inside function call to julianday(). Index > on 2 fields could help only if your condition was e.g. T2.START_DATE > > T1.START_DATE. > And in most cases there's no benefit creating index on 1 field > (PATIENT_ID) when there's already index on 2 fields (PATIENT_ID, > START_DATE) where PATIENT_ID is the first field. > > Pavel > > On Fri, Sep 18, 2009 at 4:46 PM, Bart Smissaert > wrote: >> This is what I am dealing with: >> 2 tables with exactly the same schema (but could be slightly >> different, so can't put in same table): >> >> CREATE TABLE TABLE1( >> [PATIENT_ID] INTEGER, >> [ENTRY_ID] INTEGER PRIMARY KEY, >> [READ_CODE] TEXT, >> [ADDED_DATE] TEXT, >> [START_DATE] TEXT) >> >> The last 2 date fields are in the format -mm-dd >> >> Again in both tables the following indexes: >> CREATE INDEX IDX_TABLE1_PATIENT_ID ON TABLE1(PATIENT_ID) >> CREATE INDEX IDX_TABLE1_PATIENT_ID_START_DATE ON TABLE1(PATIENT_ID, >> START_DATE) >> CREATE INDEX IDX_TABLE1_PATIENT_ID_ENTRY_ID ON TABLE1(PATIENT_ID, ENTRY_ID) >> CREATE INDEX TABLE1_START_DATE ON TABLE1(START_DATE) >> >> Then some delete SQL's will be run so that in the end PATIENT_ID is >> unique in both tables. >> >> Then the SQL I was trying to improve: >> >> DELETE >> FROM >> TABLE2 >> WHERE >> PATIENT_ID NOT IN ( >> SELECT >> TABLE1.PATIENT_ID >> FROM >> TABLE1 >> WHERE >> JULIANDAY(TABLE2.START_DATE, '-14 month') > >> JULIANDAY(TABLE1.START_DATE) AND >> TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) >> >> The index it will use for this SQL is IDX_TABLE1_PATIENT_ID_ENTRY_ID >> and that surprises me as ENTRY_ID is not in the above SQL. If I drop >> that index then it will >> use IDX_TABLE1_PATIENT_ID and only if that is dropped as well will it >> use IDX_TABLE1_PATIENT_ID_START_DATE, which I thought would be the >> best, but turns >> out to be no better than the first. Whatever way I do this it is slow >> and I can do it a lot quicker by doing this: >> >> CREATE TEMP TABLE DATE_COMPARE_TEMP([E_ID] INTEGER) >> >> INSERT INTO DATE_COMPARE_TEMP (E_ID) >> SELECT T2.ENTRY_ID FROM >> TABLE2 T2 INNER JOIN TABLE1 T2 ON >> (T1.PATIENT_ID = T2.PATIENT_ID) >> WHERE julianday(T2.START_DATE, '+15 month') > >> julianday(T1.START_DATE) >> >> CREATE INDEX IDX1_DATE_COMPARE_TEMP_E_ID ON DATE_COMPARE_TEMP(E_ID) >> >> analyze DATE_COMPARE_TEMP >> >> and then the delete SQL like this: >> >> DELETE >> FROM >> TABLE2 >> WHERE >> ENTRY_ID NOT IN >> (SELECT E_ID FROM DATE_COMPARE_TEMP) >> >> Although this involves a third temp table this method is about twice >> as fast as the first one. >> Maybe that is just the way it is and there just is no way to do this >> as fast without the intermediate temp table, but I just wondered. >> Again there is no problem here as the second method is simple and >> fast. Just trying to increase my understanding of SQLite and indexes. >> >> >> RBS >> >> >> >> >> On Fri, Sep 18, 2009 at 8:25 PM, Simon Slavin >> wrote: >>> >>> On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote: >>> > Did something in the documentation make > you think SQLite wouldn't use a > multi-column index unless you forced it ? No, but I just noticed it didn't use the index I thought would be best. As it turned out it looks I was wrong in that that index didn't give the quickest result. >>> >>> Ah. Okay, that's fine. It can be quite surprising what's best. Glad >>> you got a good result. >>> >>> 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?
From: "Bart Smissaert" Then the SQL I was trying to improve: DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE JULIANDAY(TABLE2.START_DATE, '-14 month') > JULIANDAY(TABLE1.START_DATE) AND TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) - To my understanding, SQLite will not use indices on function results. I wonder if you would have more luck (since START_DATE is -mm-dd) using DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE TABLE1.START_DATE < DATE(TABLE2.START_DATE,'-14 month') AND TABLE1.PATIENT_ID = TABLE2.PATIENT_ID ) I believe this will make use of a joint index on PATIENT_ID and START_DATE, with the preferred order depending on which is the more restrictive term (I'd guess best would be and index on (PATIENT_ID, START_DATE)). Jon Dixon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index usage
Hi, I'm trying to optimize a query for 2 different scenarios, and I'm having trouble getting something that works good in general. I want to be sure I'm not missing something. Here are the tables and indexes used in my database: sqlite> CREATE TABLE path_table (idx INTEGER PRIMARY KEY, path TEXT UNIQUE); sqlite> CREATE TABLE val_table (idx INTEGER, val REAL, time INTEGER); sqlite> CREATE INDEX time_idx ON val_table (time ASC); sqlite> CREATE INDEX path_idx ON val_table (idx ASC); 'path_table' contains unique string path names, while 'val_table' records any number of values associated with each path, and the time at which the value occurred. My query looks something like: sqlite> SELECT val FROM val_table WHERE idx IN (SELECT idx FROM path_table WHERE path GLOB '[a]') ORDER BY time ASC LIMIT [b]; where [a] and [b] are provided by my code's caller. My problem occurs when the idx value(s) selected from 'path_table' match a large number of records in the database - say, 100,000 out of 1,000,000 records. In that case, the query takes several minutes to complete even when [b] is small. Presumably it's first looking up all 100,000 rows where 'idx' matches, then applying the ORDER BY clause to those results without indexing. EXPLAIN QUERY PLAN confirms: 0|0|TABLE val_table WITH INDEX path_idx I tried adding "INDEXED BY time_idx", which greatly improved this particular case, because statistically 1/10 rows will match 'idx' and therefore we find [b] of them very quickly when [b] is small. But this hurts performance in other cases, since if there are only a few rows with a matching 'idx', the query ends up manually walking through most of the table. My question: how can I optimize this kind of query so that it utilizes both indexes, to grab the first [b] rows (ordered by time) which also match [a]? Or am I just going to have to guess at which way will be faster, and use "INDEXED BY" to force it? (The documentation says I shouldn't have to do this) Thanks for the help! -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
We don't really need that "SQLite." We already have it. It is commonly called MySQL. It take well over 150MB of disk space and major management efforts to maintain any level of performance. Just what the client/server guys love to play with. SQLite is way too small and Bring to catch their eye. If it ain't big and overly complex it must be a toy. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Simon Slavin Sent: Friday, September 18, 2009 2:56 PM To: General Discussion of SQLite Database Subject: [sqlite] Most wanted features of SQLite ? So if you had a team of programmers to write something like SQLite which didn't have the drawbacks SQLite has, which drawbacks would you identify ? I'm asking not about minor faults with specific SQLite library calls, but about the sort of things which require rewriting from the ground up. The ones that seem to come up most often here are * Some sort of synchronisation support * Support for multiple concurrent clients/processes * Unicode support from the ground up Please note: I am not suggesting that any of these problems are easy to solve. I'm just interested in what problems people want solved. Simon. ___ 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] Most wanted features of SQLite ?
StepSqlite brings powerful Stored Procedure support with full power of PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere 'wrapper' so it generates much more efficient code than any wrapper could ever achieve - for instance, it pre-compiles all SQL in your code right when the lib is loaded - no compile-overload at runtime - this is exactly what one expects from a true 'Stored' Procedure. -sk Sent from my iPhone On Sep 18, 2009, at 4:54 PM, Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 00:43:18 Noah Hart wrote: >> Stored Procedures > > There are Tiny C compiler extension and realization of > stored procedures for SQLite 2 and Lua extension and other. > So you can use one or all of these. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > 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?
DELETE FROM TABLE2 WHERE ENTRY_ID NOT IN (SELECT T2.ENTRY_ID FROM TABLE2 T2 INNER JOIN TABLE1 T1 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE julianday(T2.START_DATE, '+15 month') > julianday(T1.START_DATE) ) That is indeed a lot faster and then slightly faster than my approach with the intermediate table. Will now have a look at Jon's suggestion. RBS On Fri, Sep 18, 2009 at 10:04 PM, Pavel Ivanov wrote: > *I'm leaving aside the rant that your first delete is not identical to > combination of the select and delete in the second approach and select > in second approach contains typos...* > > But did you try to combine your insert and delete statements from the > second approach? This approach quicker because of exactly that - > select and delete statements are independent whereas in first approach > your select is executed again and again for each row in TABLE2. So > just make it like this: > > DELETE > FROM > TABLE2 > WHERE > ENTRY_ID NOT IN > (SELECT T2.ENTRY_ID FROM > TABLE2 T2 INNER JOIN TABLE1 T1 ON > (T1.PATIENT_ID = T2.PATIENT_ID) > WHERE julianday(T2.START_DATE, '+15 month') > > julianday(T1.START_DATE) > ) > > And about indexes: for this query index on (PATIENT_ID, START_DATE) > doesn't do any better than on (PATIENT_ID) because SQLite must to > check all rows with given PATIENT_ID anyway. And that is because > START_DATE is in the query inside function call to julianday(). Index > on 2 fields could help only if your condition was e.g. T2.START_DATE > > T1.START_DATE. > And in most cases there's no benefit creating index on 1 field > (PATIENT_ID) when there's already index on 2 fields (PATIENT_ID, > START_DATE) where PATIENT_ID is the first field. > > Pavel > > On Fri, Sep 18, 2009 at 4:46 PM, Bart Smissaert > wrote: >> This is what I am dealing with: >> 2 tables with exactly the same schema (but could be slightly >> different, so can't put in same table): >> >> CREATE TABLE TABLE1( >> [PATIENT_ID] INTEGER, >> [ENTRY_ID] INTEGER PRIMARY KEY, >> [READ_CODE] TEXT, >> [ADDED_DATE] TEXT, >> [START_DATE] TEXT) >> >> The last 2 date fields are in the format -mm-dd >> >> Again in both tables the following indexes: >> CREATE INDEX IDX_TABLE1_PATIENT_ID ON TABLE1(PATIENT_ID) >> CREATE INDEX IDX_TABLE1_PATIENT_ID_START_DATE ON TABLE1(PATIENT_ID, >> START_DATE) >> CREATE INDEX IDX_TABLE1_PATIENT_ID_ENTRY_ID ON TABLE1(PATIENT_ID, ENTRY_ID) >> CREATE INDEX TABLE1_START_DATE ON TABLE1(START_DATE) >> >> Then some delete SQL's will be run so that in the end PATIENT_ID is >> unique in both tables. >> >> Then the SQL I was trying to improve: >> >> DELETE >> FROM >> TABLE2 >> WHERE >> PATIENT_ID NOT IN ( >> SELECT >> TABLE1.PATIENT_ID >> FROM >> TABLE1 >> WHERE >> JULIANDAY(TABLE2.START_DATE, '-14 month') > >> JULIANDAY(TABLE1.START_DATE) AND >> TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) >> >> The index it will use for this SQL is IDX_TABLE1_PATIENT_ID_ENTRY_ID >> and that surprises me as ENTRY_ID is not in the above SQL. If I drop >> that index then it will >> use IDX_TABLE1_PATIENT_ID and only if that is dropped as well will it >> use IDX_TABLE1_PATIENT_ID_START_DATE, which I thought would be the >> best, but turns >> out to be no better than the first. Whatever way I do this it is slow >> and I can do it a lot quicker by doing this: >> >> CREATE TEMP TABLE DATE_COMPARE_TEMP([E_ID] INTEGER) >> >> INSERT INTO DATE_COMPARE_TEMP (E_ID) >> SELECT T2.ENTRY_ID FROM >> TABLE2 T2 INNER JOIN TABLE1 T2 ON >> (T1.PATIENT_ID = T2.PATIENT_ID) >> WHERE julianday(T2.START_DATE, '+15 month') > >> julianday(T1.START_DATE) >> >> CREATE INDEX IDX1_DATE_COMPARE_TEMP_E_ID ON DATE_COMPARE_TEMP(E_ID) >> >> analyze DATE_COMPARE_TEMP >> >> and then the delete SQL like this: >> >> DELETE >> FROM >> TABLE2 >> WHERE >> ENTRY_ID NOT IN >> (SELECT E_ID FROM DATE_COMPARE_TEMP) >> >> Although this involves a third temp table this method is about twice >> as fast as the first one. >> Maybe that is just the way it is and there just is no way to do this >> as fast without the intermediate temp table, but I just wondered. >> Again there is no problem here as the second method is simple and >> fast. Just trying to increase my understanding of SQLite and indexes. >> >> >> RBS >> >> >> >> >> On Fri, Sep 18, 2009 at 8:25 PM, Simon Slavin >> wrote: >>> >>> On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote: >>> > Did something in the documentation make > you think SQLite wouldn't use a > multi-column index unless you forced it ? No, but I just noticed it didn't use the index I thought would be best. As it turned out it looks I was wrong in that that index didn't give the quickest result. >>> >>> Ah. Okay, that's fine. It can be quite surprising what's best. Glad >>> you got a good result. >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noah Hart wrote: > Stored Procedures Stored procedures don't make sense as a core part of SQLite because there is no one solution that fits all. For example what language would you write them in, and how would you deal with security (blindly loading anything that is there wouldn't be a good idea is most situations)? To implement stored procedures, write them in the language of your choice and store them in the database with a schema and form of your own choosing (text/bytecode/binary). If you are using the C api then use sqlite3_auto_extension - http://www.sqlite.org/c3ref/auto_extension.html - to register a callback that is called whenever a new db is opened. In that callback you can then grab the stored procedures out of the database, apply whatever security rules you want and then register/execute them. If you are not using C then most of the language bindings provide a way of doing something substantially similar. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq0CmAACgkQmOOfHg372QSIzACgywwsjWKAaxFISkyxFioNSVom LFgAoN/ywQiICU4bwlUfws2+QY/DZx8z =wdx9 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Fri, Sep 18, 2009 at 8:56 PM, Simon Slavin wrote: > So if you had a team of programmers to write something like SQLite > which didn't have the drawbacks SQLite has, which drawbacks would you > identify ? I'm asking not about minor faults with specific SQLite > library calls, but about the sort of things which require rewriting > from the ground up. Not sure whether this would need a rewrite, but for debug purposes I'd love to be able to view the SQL for a prepared statement with its values bound. Hamish ___ 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?
Had a look at this suggestion now and it works and uses the PATIENT_ID, ADDED_DATE index, but it is as slow as my delete with Julianday. It looks Pavel's suggestion is the way to do this. Just will have a look now and see if doing the construction with DATE( instead of Julianday is any faster. RBS On Fri, Sep 18, 2009 at 10:48 PM, Jon Dixon wrote: > From: > "Bart Smissaert" > Then the SQL I was trying to improve: > > DELETE > FROM > TABLE2 > WHERE > PATIENT_ID NOT IN ( > SELECT > TABLE1.PATIENT_ID > FROM > TABLE1 > WHERE > JULIANDAY(TABLE2.START_DATE, '-14 month') > > JULIANDAY(TABLE1.START_DATE) AND > TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) > > - > > To my understanding, SQLite will not use indices on function results. I > wonder if you would have more luck (since START_DATE is -mm-dd) using > > DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( > SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE TABLE1.START_DATE < > DATE(TABLE2.START_DATE,'-14 month') AND TABLE1.PATIENT_ID = > TABLE2.PATIENT_ID > ) > > I believe this will make use of a joint index on PATIENT_ID and START_DATE, > with the preferred order depending on which is the more restrictive term > (I'd guess best would be and index on (PATIENT_ID, START_DATE)). > > Jon Dixon > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
thanks Pavel, and sorry for mixing the wikipedia example with the real situation. create table RT1 ( PID integer, V1 text ); insert into RT1 values ( '684', 'aap' ); insert into RT1 values ( '685', 'other empty' ); create table RT2 ( PID integer, V2 text ); insert into RT2 values ( '684', 'beer' ); insert into RT2 values ( '686', 'other empty' ); select RT1.*, RT2.* from RT1 left join RT2 on RT1.PID = RT2.PID union select RT1.*, RT2.* from RT2 left join RT1 on RT1.PID = RT2.PID where RT1.PID IS NULL; ok I tried in sqlite3 command line ( terrible job ;-) and as you said, both "union" and "union all" works correct (only a different order). And I also can add the "order by" clause without affecting the result (except ordening). So the problem is in the IDE I use. I tried another IDE which worked also correct. Now I still have one question: The result of the above query is: PID V1PID V2 686 from RT2 684 from RT1684 from RT2 685 from RT1 Now I want to combine the columns PID, so the result would look like PID V1 V2 686from RT2 684 from RT1 from RT2 685 from RT1 Is that possible with SQL ? cheers, Stef So let's try again: This following code in the sqlite command line utility: create table RT1 ( PID integer, V1 text ); insert into RT1 values ( '684', 'aap' ); insert into RT1 values ( '685', 'other empty' ); create table RT2 ( PID integer, V2 text ); insert into RT2 values ( '684', 'beer' ); insert into RT2 values ( '686', 'other empty' ); select RT1.*, RT2.* from RT1 left join RT2 on RT1.PID = RT2.PID union select RT1.*, RT2.* from RT2 left join RT1 on RT1.PID = RT2.PID where RT1.PID IS NULL; gives the following output ( I hope I don't make typo's because I don't know how to copy from the command line window) 684|from RT1|684|from RT2 685|from RT1|| ||684|from RT2 which is as (I) expected. If "union all" is replaced by Pavel Ivanov wrote: >> Now the strange thing is that this query returns the correct number of rows, >> but all the columns from the employee-table are empty. >> > > There's no "employee-table" in your query. > > >> If I change "UNION" to "UNION ALL" the join works as expected. >> Is there an explanation for this behavior ? >> > > As we don't see your query we can't explain what's going on there. > > >> Also when I add an order clause, the left columns are all made empty ? >> > > Justing adding ORDER BY changes all "left" (from what?) columns in all > rows to NULL? No way! Show the actual output from sqlite3 command line > utility please. > > >> Is it uberhaupt possible to order the resulting table on the column PID >> and preferable get just 1 PID column ? >> > > Sure, why not? But again no query - no advice on how to correct it to > achieve what you want. > > Pavel > > On Thu, Sep 17, 2009 at 6:39 PM, Stef Mientki wrote: > >> 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 >> >> > ___ > 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] Most wanted features of SQLite ?
On 18 Sep 2009, at 9:43pm, Noah Hart wrote: > Stored Procedures How do those differ from what can be done with triggers ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > Simon Slavin > wrote: >> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: >> >>> Simon Slavin wrote: * Unicode support from the ground up >>> >>> SQLite already has "unicode support from the ground up". Try using >>> non-Unicode strings and you'll see! >> >> SQLite's indexing correctly understands how to order Unicode >> strings ? > > With ICU extension enabled and correct collation specified, yes. Note > that the correct ordering of Unicode strings is locale-dependent. Okay. So I create an indexed database in one locale. I have a thousand records in there. The indexes are created using the locale I set. I then send a copy of this database to a client in another place, and the client has different locale settings. The client adds another thousand records with their locale settings. What happens when I use WHERE clauses with '<' or '>' ? Does the system vaguely work, or does it get a mess ? 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?
OK, it looks the construction with DATE instead of Julianday is a bit faster, so best option here seems to be: DELETE FROM TABLE1 WHERE ENTRY_ID NOT IN ( SELECT T1.ENTRY_ID FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE DATE(T1.ADDED_DATE, '+15 month') > T2.ADDED_DATE) This will use the index on the single field PATIENT_ID, which I need in any case for other queries. So, I now have a faster, simpler query and also less indexes needed. Thanks again for all the suggestions. RBS On Sat, Sep 19, 2009 at 12:03 AM, Bart Smissaert wrote: > Had a look at this suggestion now and it works and uses the PATIENT_ID, > ADDED_DATE index, but it is as slow as my delete with Julianday. It > looks Pavel's suggestion is the way to do this. Just will have a look > now and see if doing the construction with DATE( instead of Julianday > is any faster. > > RBS > > > On Fri, Sep 18, 2009 at 10:48 PM, Jon Dixon wrote: >> From: >> "Bart Smissaert" >> Then the SQL I was trying to improve: >> >> DELETE >> FROM >> TABLE2 >> WHERE >> PATIENT_ID NOT IN ( >> SELECT >> TABLE1.PATIENT_ID >> FROM >> TABLE1 >> WHERE >> JULIANDAY(TABLE2.START_DATE, '-14 month') > >> JULIANDAY(TABLE1.START_DATE) AND >> TABLE1.PATIENT_ID = TABLE2.PATIENT_ID) >> >> - >> >> To my understanding, SQLite will not use indices on function results. I >> wonder if you would have more luck (since START_DATE is -mm-dd) using >> >> DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( >> SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE TABLE1.START_DATE < >> DATE(TABLE2.START_DATE,'-14 month') AND TABLE1.PATIENT_ID = >> TABLE2.PATIENT_ID >> ) >> >> I believe this will make use of a joint index on PATIENT_ID and START_DATE, >> with the preferred order depending on which is the more restrictive term >> (I'd guess best would be and index on (PATIENT_ID, START_DATE)). >> >> Jon Dixon >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
Stef Mientki wrote: > create table RT1 ( PID integer, V1 text ); > insert into RT1 values ( '684', 'aap' ); > insert into RT1 values ( '685', 'other empty' ); > create table RT2 ( PID integer, V2 text ); > insert into RT2 values ( '684', 'beer' ); > insert into RT2 values ( '686', 'other empty' ); > select RT1.*, RT2.* > from RT1 >left join RT2 on RT1.PID = RT2.PID > union >select RT1.*, RT2.* > from RT2 >left join RT1 on RT1.PID = RT2.PID >where RT1.PID IS NULL; > > Now I want to combine the columns PID, so the result would look like > > PID V1 V2 > 686from RT2 > 684 from RT1 from RT2 > 685 from RT1 select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ... Modifying the second select clause is left as an exercise for the reader. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin
wrote:
> On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote:
>
>> Simon Slavin
>> wrote:
>>> On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
>>>
Simon Slavin wrote:
> * Unicode support from the ground up
SQLite already has "unicode support from the ground up". Try using
non-Unicode strings and you'll see!
>>>
>>> SQLite's indexing correctly understands how to order Unicode
>>> strings ?
>>
>> With ICU extension enabled and correct collation specified, yes. Note
>> that the correct ordering of Unicode strings is locale-dependent.
>
> Okay. So I create an indexed database in one locale. I have a
> thousand records in there. The indexes are created using the locale I
> set. I then send a copy of this database to a client in another
> place, and the client has different locale settings. The client adds
> another thousand records with their locale settings. What happens
> when I use WHERE clauses with '<' or '>' ? Does the system vaguely
> work, or does it get a mess ?
I'm not sure what you mean by "the client has locale settings". Has them
where, and how are these settings supposed to affect SQLite database?
When you create a table or an index, you may explicitly specify the
collation each field should use. Like this:
SELECT icu_load_collation('he_IL', 'hebrew');
create table myTable(myField text collate hebrew);
-- and/or
create index myIndex on myTable(myField collate hebrew);
Using ICU extension does require certain discipline. You must run
icu_load_collation soon after opening the database, and all users of the
database must agree to map the same identifiers to the same locales (the
best way to achieve that is probably to make collation name the same as
locale name: SELECT icu_load_collation('he_IL', 'he_IL'); ). Mapping
the same collation identifier to different locales may indeed result in
corrupted indexes.
For more details, see
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 12:14:37AM +0100, Simon Slavin scratched on the wall: > > On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > > > Simon Slavin > > wrote: > >> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> > >>> Simon Slavin wrote: > * Unicode support from the ground up > >>> > >>> SQLite already has "unicode support from the ground up". Try using > >>> non-Unicode strings and you'll see! > >> > >> SQLite's indexing correctly understands how to order Unicode > >> strings ? > > > > With ICU extension enabled and correct collation specified, yes. Note > > that the correct ordering of Unicode strings is locale-dependent. > > Okay. So I create an indexed database in one locale. I have a > thousand records in there. The indexes are created using the locale I > set. I then send a copy of this database to a client in another > place, and the client has different locale settings. The client adds > another thousand records with their locale settings. What happens > when I use WHERE clauses with '<' or '>' ? Does the system vaguely > work, or does it get a mess ? You get a mess. The locale is what defines the "rules" of the language and the meaning of the symbols that make up the string. This example is no different than if you added a bunch of records under NOCASE, then somehow changed the collation to be case sensitive and added a bunch more. Your indexes are likely bogus because you changed the underlying assumptions. And that's exactly correct. If you have one language where 'k' comes before 'm' and another language where it doesn't, what exactly do you expect to happen when you ask for a "sorted" column of strings? Does 'k' come before 'm' or not? You have to pick a set of rules-- i.e. a locale. I suppose you could record the locale for each specific string, essentially extending the "type" of the string to a string-locale. But all that buys you is the ability to group similar locales together, in the same way that SQLite sorts integers, floats, strings, and BLOB types together, sorting them internally but considering them overwise unmixable. You still need to pick some arbitrary ordering of those types, and you're likely to get some very odd results if you do that with locales. Of course, this has nothing to do with Unicode. Unicode is just an encoding system for strings that maps characters-codes to bytes. That's it. What those character-codes represent and the glyph that is associated with them is up to the locale. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 19 Sep 2009, at 12:55am, Igor Tandetnik wrote:
> Using ICU extension does require certain discipline. You must run
> icu_load_collation soon after opening the database, and all users of
> the
> database must agree to map the same identifiers to the same locales
> (the
> best way to achieve that is probably to make collation name the same
> as
> locale name: SELECT icu_load_collation('he_IL', 'he_IL'); ). Mapping
> the same collation identifier to different locales may indeed result
> in
> corrupted indexes.
>
> For more details, see
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt
Thanks to you and Jay for explanations. I hadn't encountered ICU at
all before. Your descriptions make perfect sense and are very
interesting since ICU is a good attempt to get around one of the
fundamental problems of Unicode.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > Thanks to you and Jay for explanations. I hadn't encountered ICU at > all before. Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Out of curiosity - what do you consider a fundamental problem of Unicode? The fact that different people may prefer their strings sorted differently? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Errr, this is not the fault of Unicode. It is the fault of people! Unicode lets you represent the majority of the world's past and present characters using the same character set. Note that there is a lot of debate over exactly what constitutes a character, ways they combine code points, the same code point being used for different native character sets, dealing with older text where the character depiction matters even if it the "same" as a modern character. Unicode is a reasonable compromise. See http://en.wikipedia.org/wiki/Unicode#Issues Sorting and comparing strings are hard. For example someone in the US or UK would consider cafe and café to be equivalent. German has a different ordering for looking in a phonebook versus a dictionary. What do you do about a German user having a Swedish name in their phonebook? Is it sorted using Swedish rules or German rules? Unicode is not required to sort and compare strings, but it is a lot nicer place to start. And then the folks at the Unicode consortium who have been thinking about this for a very long time have come up with an algorithm that works (with locale specific adjustments) called the Unicode Collation Algorithm. Their report gives you a good idea of the complexity and issues involved. Section 1.8 is enlightening. http://www.unicode.org/unicode/reports/tr10/ ICU is a programming library implementing UCA plus a few other things. It is large and slow because of people, needing all sorts of builtin tables such as how each locale sorts things like accents and combining characters as well as ordinary codepoints commonly used across multiple locales: http://en.wikipedia.org/wiki/International_Components_for_Unicode You likely didn't intend your comment to be taken as condescending towards Unicode/UCA/ICU but I did want to make it *very* clear that they make life considerably easier for us as programmers dealing with human text and provide solutions to collation/case etc that we frequently need. It is far more than a "good attempt", closer to a very good solution. There aren't any alternatives that come *remotely* close as using the examples in the UCA report will show you. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq0P0QACgkQmOOfHg372QSz9ACggmw5kaLKwL90nggbr0GaTxkZ SNMAn17gWLmy3SdbzZVMI6fSoUtTVmYS =jOGK -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hamish Allan wrote: > Not sure whether this would need a rewrite, but for debug purposes I'd > love to be able to view the SQL for a prepared statement with its > values bound. I am always confused by requests like this. Your code called prepare and your code called the various bind methods. Consequently your code can store that information with as much detail and context as is useful to you. For my Python wrapper (APSW) I even provide a tool where it will show all SQL executed (including bindings), results returned, timings, statistical information etc. Your own code doesn't even have to be touched or modified in any way: http://apsw.googlecode.com/svn/publish/execution.html#apsw-trace Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq0QMQACgkQmOOfHg372QQ50QCgyxF4NNwxrtFpVtSs6qnsXJIL 2Z8An2l/nw7I39LIMjHMgQ6rfzmAvPVY =t8XG -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 3:24 AM, Roger Binns wrote: > > Hamish Allan wrote: >> Not sure whether this would need a rewrite, but for debug purposes I'd >> love to be able to view the SQL for a prepared statement with its >> values bound. > > I am always confused by requests like this. Your code called prepare and > your code called the various bind methods. Consequently your code can store > that information with as much detail and context as is useful to you. Like I say, it's for debug purposes. Sometimes my code doesn't behave as I would expect it to; perhaps there's something wrong with my string escaping, or perhaps my query is awry, and I would like to see if it works from the command line. I should note that I am a relative beginner using the C interface, which is perhaps not the most forgiving combination. Hamish ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 19 Sep 2009, at 3:07am, Igor Tandetnik wrote: > Simon Slavin wrote: >> Thanks to you and Jay for explanations. I hadn't encountered ICU at >> all before. Your descriptions make perfect sense and are very >> interesting since ICU is a good attempt to get around one of the >> fundamental problems of Unicode. > > Out of curiosity - what do you consider a fundamental problem of > Unicode? The fact that different people may prefer their strings > sorted > differently? Only in that it's a fundamental problem with the way Unicode was defined. I completely recognise that the question of sorting cannot be answered at the level of characters for the reasons we discussed: different alphabets have different meanings for the same characters, and Unicode has just one entry for the character. It might have made more sense to define two levels of character definitions: one which says what 'c with a hat on' looks like, and another that defines alphabets, character alternatives, and where 'c with a hat on' comes in various alphabets. The problem I was referring to is that there's no consistent way of picking up which characters are variants of other characters. In the Roman alphabet, it would be very useful to be able to look at the codes for 'l' and capital 'L' and realise that they're somehow the same. In Hebrew it would be useful to be able match not only capital and lower-case characters, but also the variants used when a character occurs at the end of a word. ICU is a great way to approach these problems and similar ones. I have no problem with it. On 19 Sep 2009, at 3:17am, Roger Binns wrote: > Errr, this is not the fault of Unicode. Your reaction to my post is amusingly similar to my reaction when people assume that database synchronisation is simple. Sorry to have irritated you. I understand Unicode in more detail than we've discussed here. I do not consider these things to be 'the fault of Unicode' rather, in the words I used, 'problems with Unicode'. And I do consider Unicode to be far superior to the mess of code pages we used to have to implement before it became popular. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
This is not a good example i think. If a transaction is intent to update after the select, it should start a write lock before the select. And as described in previous 'dead lock' example, the update in this example could fail due to 'dead lock' I believe the 'read lock' is designed for a 'read only' transaction, and the 'write lock' is for a transaction that 'may write something'. 2009/9/19 Igor Tandetnik > Angus March wrote: > >Yes, I see. So what is key to the problem is that someone tries to > > change their read lock to a write lock. I guess I just thought that > > the kernel that manages fcntl() would have a way of dealing with > > this. Can this situation not be averted if at step 3, transaction A > > releases its read lock before requesting a write lock? > > Then it wouldn't be much of a transaction, now would it? Imagine the > classic example, where a transaction first verifies that the balance in > a bank account is sufficient, then performs a withdrawal. If it > relinquishes all locks between these two steps, then somebody else may > record a withdrawal from that account, so that the write operation would > then make the balance negative, thus violating an invariant. > > Of course, if that's what the application wants, it can simply perform > the read and the write operations in two separate transactions. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best Regards, ZHAO, Wenbo === ___ 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
Please sorry for my terrible Engilsh. :) Thanks for the answer. Yes, I know that it is bad design in the common case. But I have to use it because I have data which has following format: time | value_1 | value_2|value_ |---|-| [time_1] [value_1.1] [value_2.1] ... [value_.1] [time_2] [value_1.2] [value_2.2] ... [value_.2] [time_3] [value_1.3] [value_2.3] ... [value_.3] ... And I must filter it by comparing some values, for example, as this: SELECT FROM table_name WHERE time > 1000 AND time < 1500 AND value_1 > value_2 + 3 OR value_456 != value_654 I asked this question because SQLite may use such algorithms which are very slow with very big number of colums and may be in this case it will work rapidly if, for example, I will combine values in such manner: time | value_1_to_value_100 | value_101_to_value_200 | ... |-|| [time_1] [value_1_to_value_100.1] [value_101_to_value_200.1] ... [time_2] [value_1_to_value_100.2] [value_101_to_value_200.2] ... [time_3] [value_1_to_value_100.3] [value_101_to_value_200.3] ... And I will write some functions for extracting values from them and will filter data by following query: SELECT FROM table_name WHERE time > 1000 AND time < 1500 AND get_value(value_1_to_value_100, 1) > get_value(value_1_to_value_100, 2) + 3 OR get_value(value_400_to_value_500, 56) != get_value(value_600_to_value_700, 54) ___ 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
> 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. Thanks for this information, it is helpful for me. > 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 Unfortunately I can't use such design because in this case I will not able to find in the database such data as I need (I described problems with which I faced in http://www.mail-archive.com/sqlite-users@sqlite.org/msg46229.html). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
