Re: [sqlite] FTS3 Unicode support
The problem with ICU is that it's a rather large library, and mozilla already has it's own unicode system. That's we we opted on doing unicode support ourselves (less code duplication, and a smaller binary). Cheers, Shawn Wilsher On Jan 24, 2008 11:35 PM, Dan <[EMAIL PROTECTED]> wrote: > > On Jan 25, 2008, at 7:26 AM, Myk Melez wrote: > > > Hi all, > > > > I'm working to enable FTS3 in the next version of Firefox [1] so > > that extenders can take advantage of it, although Firefox itself > > isn't using it for the next release. > > > > Given Firefox's international audience, it would be useful for FTS3 > > to support Unicode. We currently do this for upper(), lower(), and > > LIKE by redefining them with sqlite3_create_function [2]. > > > > For FTS3 it seems like we'd have to redefine the tokenizer and > > MATCH. Can that be done using sqlite3_create_function, and what's > > the status of the international support mentioned in a previous > > message on this list [3]? > > Hi Myk, > > The 'icu' and 'fts3' SQLite extensions can take advantage of the > ICU library to provide internationalization if it is available. > The ICU extension provides internationalized versions of upper(), > lower(), collation sequences and a REGEXP operator. Details > are available here: > >http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt > > Fts3 has an API for creating new tokenizers. See here: > >http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/ > README.tokenizers > > One of the example tokenizers uses the ICU library for localization. > See the same document for details. It is built if the > SQLITE_ENABLE_ICU macro is defined when fts3 is compiled. > > Regards, > Dan. > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
There is two way of compiling DBD::SQLite: 1. to use his own internal version of SQLite USE_LOCAL_SQLITE=1 perl Maker.pl 2. to use shared library of SQLite SQLITE_LOCATION=/path/to/libsqlite perl Makefile.pl So if you install 3.5.4 in /usr/local/lib, you should set SQLITE_LOCATION=/usr/local/lib/ On Jan 25, 2008 5:13 AM, Jim Dodgen <[EMAIL PROTECTED]> wrote: > I have tend to build the DBD::SQLite from source, when ever I have built > with it looking for sqlite libs it reports a old version older than > 3.3.9 or something > and then uses the current 3.4.2 stuff supplied in the module. > > I do have 3.5.4 installed, it migh be that there could be a older > version hiding someplace. not sure how to find it or delete it. > > I'm on fedora also a RHES > > Alexander Batyrshin wrote: > > I have no problem with 3.5.4. > > Maybe your is linked with libsqlite in other dirrectory? > > > For example your DBD::SQLite is linked against > > /usr/lib/libsqlite3.so.0, and you installed new 3.5.2 into > > /usr/local/lib ? > > > > > > Here is my linking information: > > # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so > > libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7eb7000) > > libc.so.6 => /lib/libc.so.6 (0xb7d87000) > > libpthread.so.0 => /lib/libpthread.so.0 (0xb7d7) > > /lib/ld-linux.so.2 (0x4100) > > > > > > On Jan 25, 2008 4:41 AM, Jim Dodgen <[EMAIL PROTECTED]> wrote: > > > >> sorry I attached another email by accident, it's content is not related > >> to my question > >> > >> Jim > >> > >> Jim Dodgen wrote: > >> > >>> the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have > >>> attempted to get a version up to 3.5.2 with no success so far. > >>> > >>> anyone have any success yet? If so what is the magic. > >>> > >>> Jim > >>> > >>> > >>> > >> - > >> > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> - > >> > >> > >> > > > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS3 Unicode support
On Jan 25, 2008, at 7:26 AM, Myk Melez wrote: Hi all, I'm working to enable FTS3 in the next version of Firefox [1] so that extenders can take advantage of it, although Firefox itself isn't using it for the next release. Given Firefox's international audience, it would be useful for FTS3 to support Unicode. We currently do this for upper(), lower(), and LIKE by redefining them with sqlite3_create_function [2]. For FTS3 it seems like we'd have to redefine the tokenizer and MATCH. Can that be done using sqlite3_create_function, and what's the status of the international support mentioned in a previous message on this list [3]? Hi Myk, The 'icu' and 'fts3' SQLite extensions can take advantage of the ICU library to provide internationalization if it is available. The ICU extension provides internationalized versions of upper(), lower(), collation sequences and a REGEXP operator. Details are available here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt Fts3 has an API for creating new tokenizers. See here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/ README.tokenizers One of the example tokenizers uses the ICU library for localization. See the same document for details. It is built if the SQLITE_ENABLE_ICU macro is defined when fts3 is compiled. Regards, Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
I have tend to build the DBD::SQLite from source, when ever I have built with it looking for sqlite libs it reports a old version older than 3.3.9 or something and then uses the current 3.4.2 stuff supplied in the module. I do have 3.5.4 installed, it migh be that there could be a older version hiding someplace. not sure how to find it or delete it. I'm on fedora also a RHES Alexander Batyrshin wrote: I have no problem with 3.5.4. Maybe your is linked with libsqlite in other dirrectory? For example your DBD::SQLite is linked against /usr/lib/libsqlite3.so.0, and you installed new 3.5.2 into /usr/local/lib ? Here is my linking information: # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7eb7000) libc.so.6 => /lib/libc.so.6 (0xb7d87000) libpthread.so.0 => /lib/libpthread.so.0 (0xb7d7) /lib/ld-linux.so.2 (0x4100) On Jan 25, 2008 4:41 AM, Jim Dodgen <[EMAIL PROTECTED]> wrote: sorry I attached another email by accident, it's content is not related to my question Jim Jim Dodgen wrote: the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have attempted to get a version up to 3.5.2 with no success so far. anyone have any success yet? If so what is the magic. Jim - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] "Parameter suggestion" for Makefile...
I tried to install newest sqlite3 on OpenBSD 4.2 - unfortunately, when using sqlite3 module for TCL, immediately after exiting tclsh, there's always "core dump" occurence. It seems, that sqlite needs some patching by OpenBSD port maintainers. But it wasn't a big problem, there is binary package version for OpenBSD provided, not that old (3.4.1) anyway. But that's not the point: my suggestion would be to add "deinstall" target into Makefile - especially for such occasion, to give the possibility to make clean-up automatically. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB disappears at times...
Did you try to use it on real drive disk? On Jan 25, 2008 3:17 AM, Rasanth Akali Kandoth <[EMAIL PROTECTED]> wrote: > Hi All, > i have an application that uses sqlite3 version 3.3.17 to create a DB on a > ramdisk. I see a strange issue that the DB disappears at times( the DB size > is becoming zero. it was arround 16k after all my tables are created). i > dont have any code in my application which deletes the db file or deletes > all the tables in the db. > does anyone know why this is happening? > > Thanks&Regards, > Rasanth > -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
I have no problem with 3.5.4. Maybe your DBD::SQLite is linked with libsqlite in other dirrectory? For example your DBD::SQLite is linked against /usr/lib/libsqlite3.so.0, and you installed new 3.5.2 into /usr/local/lib ? Here is my linking information: # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7eb7000) libc.so.6 => /lib/libc.so.6 (0xb7d87000) libpthread.so.0 => /lib/libpthread.so.0 (0xb7d7) /lib/ld-linux.so.2 (0x4100) On Jan 25, 2008 4:41 AM, Jim Dodgen <[EMAIL PROTECTED]> wrote: > sorry I attached another email by accident, it's content is not related > to my question > > Jim > > Jim Dodgen wrote: > > the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have > > attempted to get a version up to 3.5.2 with no success so far. > > > > anyone have any success yet? If so what is the magic. > > > > Jim > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
sorry I attached another email by accident, it's content is not related to my question Jim Jim Dodgen wrote: the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have attempted to get a version up to 3.5.2 with no success so far. anyone have any success yet? If so what is the magic. Jim - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DBD::SQLite for 3.5.4?
the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have attempted to get a version up to 3.5.2 with no success so far. anyone have any success yet? If so what is the magic. Jim John Stanton wrote: Using Apache is the problem. The connections are not persistent so caching is destroyed. It sounds like you are using CGI, and that makes it more so. Somevariant like fastcgi (?) might give you what you look for. Clark Christensen wrote: I don't think you're going to get the kind of caching you want using Perl and a web server (Apache, right?). There's just no persistence across processes, no shared memory, no database connections. Now, Apache's mod_perl and some associated modules could get you all that and more. For me, anyway, it requires a big adjustment in the way you build your apps if you want to take advantage of the shared $dbh, shared variables, and caching. For me, the investment isn't quite worth the benefit. -Clark - Original Message From: Alexander Batyrshin <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 24, 2008 7:19:47 AM Subject: Re: [sqlite] Cache for SQLite On Jan 24, 2008 4:03 PM, Doug <[EMAIL PROTECTED]> wrote: I don't know of a daemon, but based on someone else's post where they described keeping a pool of sqlite3* handles to the database, and always reusing the most recently used handle first (so that the SQLite page cache is most likely still valid) I saw a very big jump in performance. Perhaps that would help in your case too? Sounds interesting, maybe it help me a little. I am using Perl DBD::SQLite, so i need some investigation how this library work. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
Alexander Batyrshin wrote: Hello John, Right now i am using apache + fcgid (fast-cgi). I will try to keep database handler open. But i need to implement it, because i am using now more than 200 databases. That means keeping open 200 connections if you want to maximize performance by exploiting cacheing. On Jan 24, 2008 9:38 PM, John Stanton <[EMAIL PROTECTED]> wrote: Using Apache is the problem. The connections are not persistent so caching is destroyed. It sounds like you are using CGI, and that makes it more so. Somevariant like fastcgi (?) might give you what you look for. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DB disappears at times...
Hi All, i have an application that uses sqlite3 version 3.3.17 to create a DB on a ramdisk. I see a strange issue that the DB disappears at times( the DB size is becoming zero. it was arround 16k after all my tables are created). i dont have any code in my application which deletes the db file or deletes all the tables in the db. does anyone know why this is happening? Thanks&Regards, Rasanth
[sqlite] FTS3 Unicode support
Hi all, I'm working to enable FTS3 in the next version of Firefox [1] so that extenders can take advantage of it, although Firefox itself isn't using it for the next release. Given Firefox's international audience, it would be useful for FTS3 to support Unicode. We currently do this for upper(), lower(), and LIKE by redefining them with sqlite3_create_function [2]. For FTS3 it seems like we'd have to redefine the tokenizer and MATCH. Can that be done using sqlite3_create_function, and what's the status of the international support mentioned in a previous message on this list [3]? -myk [1] https://bugzilla.mozilla.org/show_bug.cgi?id=413589 [2] http://lxr.mozilla.org/mozilla/source/storage/src/mozStorageUnicodeFunctions.cpp [3] http://www.mail-archive.com/sqlite-users@sqlite.org/msg27238.html - To unsubscribe, send email to [EMAIL PROTECTED] -
sqlite-users@mailinglists.sqlite.org
Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > > I think the issue however was that sqlite uses Signed integers And my Number > although a valid 64bit hex number was to large to fit into a 64 bit signed > value. Yes, that's what I said. :-) > Sqlite simply coerced it into a "text" field for storage. No, SQLite converted in to a floating point numeric value. The trace below shows what happens when inserting these values. sqlite> create table t(a); sqlite> insert into t values (18446744073709486080); sqlite> select typeof(a) from t; real sqlite> select a from t; 1.84467440737095e+19 sqlite> insert into t values (-1 << 16); sqlite> select typeof(a) from t; real integer sqlite> select a from t; 1.84467440737095e+19 -65536 > > In the mean time I wonder if it would be possible as an enhancement > to add 64bit unsigned integer storage into sqlite? And also the > capability to convert strings such as '0x' as this would make > writing this typeof bit based logic so much easier. > I doubt if you will ever see unsigned 64 bit integer support in SQLite. The range of valid integer values is -9223372036854775808 to +9223372036854775807. I believe Richard Hipp thinks one integer type is already one too many. :-) I can see the usefulness of the hex literals for integers in some applications, but I doubt it will be seen as common enough to warrant adding it to the language. You can always convert the hex to decimal outside SQLite and insert the decimal literal along with the corresponding hex value in a comment, or you can generate the required values when needed as I have suggested. As always you should check if you are using the right tool for the job. SQL isn't really intended for bit field manipulations. It can be done, but perhaps it should be done elsewhere. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - Thanks for your help and thoughtfulness on this Dennis. I'll use the shifted version to do the work as discussed.. Actually I already implemented this and it works great. To be honest, I was quite surprised to see the bitfield manipulation as part of the sqlite language. Good thing its there because I need to do several selects based upon a single bit being set. And sqlite came through for me yet again. Regards, Ken
[sqlite] Could this cause slow queries?
Latest SQLite version with the VB wrapper from Olaf Schmidt, dhRichClient.dll. Running this in VBA Excel on Windows XP. Have a suspicion that maybe you could get slow queries if a table repeatedly gets a DELETE FROM TABLE followed by re-populating the table with inserts, so cyling this repeatedly. It is the delete query that gets slow. No logical explanation and no idea yet what exactly happens, but definitely something strange going on. Maybe it somehow has to do with my application or maybe the wrapper, but these are simple queries and there and the data is all simple and nothing unusual there. I have dealt with this now by dropping the table once in every Excel session and this seems to do the trick. Is there anything in the SQLite code that could make this happen or should I look at my app or the wrapper? Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
sqlite-users@mailinglists.sqlite.org
Ken wrote: I think the issue however was that sqlite uses Signed integers And my Number although a valid 64bit hex number was to large to fit into a 64 bit signed value. Yes, that's what I said. :-) Sqlite simply coerced it into a "text" field for storage. No, SQLite converted in to a floating point numeric value. The trace below shows what happens when inserting these values. sqlite> create table t(a); sqlite> insert into t values (18446744073709486080); sqlite> select typeof(a) from t; real sqlite> select a from t; 1.84467440737095e+19 sqlite> insert into t values (-1 << 16); sqlite> select typeof(a) from t; real integer sqlite> select a from t; 1.84467440737095e+19 -65536 In the mean time I wonder if it would be possible as an enhancement to add 64bit unsigned integer storage into sqlite? And also the capability to convert strings such as '0x' as this would make writing this typeof bit based logic so much easier. I doubt if you will ever see unsigned 64 bit integer support in SQLite. The range of valid integer values is -9223372036854775808 to +9223372036854775807. I believe Richard Hipp thinks one integer type is already one too many. :-) I can see the usefulness of the hex literals for integers in some applications, but I doubt it will be seen as common enough to warrant adding it to the language. You can always convert the hex to decimal outside SQLite and insert the decimal literal along with the corresponding hex value in a comment, or you can generate the required values when needed as I have suggested. As always you should check if you are using the right tool for the job. SQL isn't really intended for bit field manipulations. It can be done, but perhaps it should be done elsewhere. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
Hello John, Right now i am using apache + fcgid (fast-cgi). I will try to keep database handler open. But i need to implement it, because i am using now more than 200 databases. On Jan 24, 2008 9:38 PM, John Stanton <[EMAIL PROTECTED]> wrote: > Using Apache is the problem. The connections are not persistent so > caching is destroyed. It sounds like you are using CGI, and that makes > it more so. Somevariant like fastcgi (?) might give you what you look for. - To unsubscribe, send email to [EMAIL PROTECTED] -
sqlite-users@mailinglists.sqlite.org
Deinnis Thanks for the help. That worked quited well. I think the issue however was that sqlite uses Signed integers And my Number although a valid 64bit hex number was to large to fit into a 64 bit signed value. Sqlite simply coerced it into a "text" field for storage. I'll proceed with your workaround. In the mean time I wonder if it would be possible as an enhancement to add 64bit unsigned integer storage into sqlite? And also the capability to convert strings such as '0x' as this would make writing this type of bit based logic so much easier. Regards, Ken Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > I've created a table and need to perform some bitwise operations against one > of the fields. I find that 0 is returned back from the select. > > Also I'd like to add a feature request such that sqlite would understand > '0x' syntax and convert a hex string into a number. Or at least a > function so that the sql code is more readable. > > Example follows: > Thanks, > Ken > > > Sqlite version is 3.5.4 > > create table x (val integer ); > insert into x values (10083463462913); > > select to_hex(val) from x;(note to_hex is my own function). > to_hex(val) > 0x92bbd420001 > > I want to strip off the 0001 portion. So and it with 18446744073709486080 > which is 0x > > select val&18446744073709486080 from x; > val&18446744073709486080 > 0 > > select (val&18446744073709486080) from x; > (val&18446744073709486080) > 0 > Ken, Sqlite uses signed 64 bit integer values, so your literal 18446744073709486080 is too large and it is approximated as a floating point value 1.84467440737095e+19. You could use the corresponding signed value -65536, but a better way to generate such a mask is to shift a -1 (all F's) value to insert the required zero bits. select 10083463462913 & (-1 << 16); 10083463462912 This is the correctly masked value. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
Using Apache is the problem. The connections are not persistent so caching is destroyed. It sounds like you are using CGI, and that makes it more so. Somevariant like fastcgi (?) might give you what you look for. Clark Christensen wrote: I don't think you're going to get the kind of caching you want using Perl and a web server (Apache, right?). There's just no persistence across processes, no shared memory, no database connections. Now, Apache's mod_perl and some associated modules could get you all that and more. For me, anyway, it requires a big adjustment in the way you build your apps if you want to take advantage of the shared $dbh, shared variables, and caching. For me, the investment isn't quite worth the benefit. -Clark - Original Message From: Alexander Batyrshin <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 24, 2008 7:19:47 AM Subject: Re: [sqlite] Cache for SQLite On Jan 24, 2008 4:03 PM, Doug <[EMAIL PROTECTED]> wrote: I don't know of a daemon, but based on someone else's post where they described keeping a pool of sqlite3* handles to the database, and always reusing the most recently used handle first (so that the SQLite page cache is most likely still valid) I saw a very big jump in performance. Perhaps that would help in your case too? Sounds interesting, maybe it help me a little. I am using Perl DBD::SQLite, so i need some investigation how this library work. - To unsubscribe, send email to [EMAIL PROTECTED] -
sqlite-users@mailinglists.sqlite.org
Ken wrote: I've created a table and need to perform some bitwise operations against one of the fields. I find that 0 is returned back from the select. Also I'd like to add a feature request such that sqlite would understand '0x' syntax and convert a hex string into a number. Or at least a function so that the sql code is more readable. Example follows: Thanks, Ken Sqlite version is 3.5.4 create table x (val integer ); insert into x values (10083463462913); select to_hex(val) from x;(note to_hex is my own function). to_hex(val) 0x92bbd420001 I want to strip off the 0001 portion. So and it with 18446744073709486080 which is 0x select val&18446744073709486080 from x; val&18446744073709486080 0 select (val&18446744073709486080) from x; (val&18446744073709486080) 0 Ken, Sqlite uses signed 64 bit integer values, so your literal 18446744073709486080 is too large and it is approximated as a floating point value 1.84467440737095e+19. You could use the corresponding signed value -65536, but a better way to generate such a mask is to shift a -1 (all F's) value to insert the required zero bits. select 10083463462913 & (-1 << 16); 10083463462912 This is the correctly masked value. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
sqlite-users@mailinglists.sqlite.org
As an add on: it looks like this is interpreted correctly. fff 1152921504606781440 select to_hex((val&1152921504606781440)) from x; to_hex((val&1152921504606781440)) 0x92bbd42 0x99 Could this be related to the sign bit, causing 0x not to be a valid number? Thanks, Ken
sqlite-users@mailinglists.sqlite.org
I've created a table and need to perform some bitwise operations against one of the fields. I find that 0 is returned back from the select. Also I'd like to add a feature request such that sqlite would understand '0x' syntax and convert a hex string into a number. Or at least a function so that the sql code is more readable. Example follows: Thanks, Ken Sqlite version is 3.5.4 create table x (val integer ); insert into x values (10083463462913); select to_hex(val) from x;(note to_hex is my own function). to_hex(val) 0x92bbd420001 I want to strip off the 0001 portion. So and it with 18446744073709486080 which is 0x select val&18446744073709486080 from x; val&18446744073709486080 0 select (val&18446744073709486080) from x; (val&18446744073709486080) 0
Re: [sqlite] sqlite 3.5.2 for 32 bits and 64 bits
Joanne Pham wrote: I am new to sqlite and I want to build the sqlite library. What is the step to build the library. Joanne, See the following pages for more info: http://www.sqlite.org/cvstrac/wiki?p=SqliteBuildProcess http://www.sqlite.org/cvstrac/wiki?p=HowToCompile Also, you were well advised by Roger to read the page at http://catb.org/~esr/faqs/smart-questions.html before you post any more vague and open ended questions. You are likely to get a much better response by posting more specific questions that show you have put some effort into finding the solution yourself. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
Hello Alexander, I can't really comment about those modules. I have no experience with them. -Clark - Original Message From: Alexander Batyrshin <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 24, 2008 9:24:39 AM Subject: Re: [sqlite] Cache for SQLite Hello Clark, I am using Apache + Fast-CGI :) But my next move will be to mod_perl. Currently I have only idea to use something like Cache::SharedMemoryCache or Cache::Memcached for implementing caching inside my application. What are you thinking about this? If you have any interesting ideas or knowledge - it'll be great if you share it with me. On Jan 24, 2008 6:06 PM, Clark Christensen <[EMAIL PROTECTED]> wrote: > I don't think you're going to get the kind of caching you want using Perl and a web server (Apache, right?). There's just no persistence across processes, no shared memory, no database connections. > > Now, Apache's mod_perl and some associated modules could get you all that and more. For me, anyway, it requires a big adjustment in the way you build your apps if you want to take advantage of the shared $dbh, shared variables, and caching. For me, the investment isn't quite worth the benefit. > > -Clark -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite 3.5.2 for 32 bits and 64 bits
Sorry!! I didn't make it clear. I am new to sqlite and I want to build the sqlite library. What is the step to build the library. -JP - Original Message From: Roger Binns <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 24, 2008 10:40:24 AM Subject: Re: [sqlite] sqlite 3.5.2 for 32 bits and 64 bits Joanne Pham wrote: > If you know how to build sqlite library for 64bits please help me with the > detail information. What problems did you encounter when you tried to build it on a 64 bit machine? http://catb.org/~esr/faqs/smart-questions.html Roger - To unsubscribe, send email to [EMAIL PROTECTED] - Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [sqlite] sqlite 3.5.2 for 32 bits and 64 bits
Joanne Pham wrote: > If you know how to build sqlite library for 64bits please help me with the > detail information. What problems did you encounter when you tried to build it on a 64 bit machine? http://catb.org/~esr/faqs/smart-questions.html Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
On Wed, Jan 23, 2008 at 10:24:53PM -0800, Scott Hess wrote: > Seems to me that GLOB is a poor substitute for REGEXP. At the shell If, as I suspect, many more users can enter simple globs than can enter simple regexps, then providing a GLOB operator and function in SQLite is very useful indeed. Of course, regexps are very useful as well, particularly for "power" users, so it's good that SQLite offers both: it makes development of applications that offer either, or both glob and regexp search options, much easier. IMO the regexp facility should get more attention than the glob facility, but both should be present. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite 3.5.2 for 32 bits and 64 bits
Hi All, If you know how to build sqlite library for 64bits please help me with the detail information. Thanks in advance, JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 4:09:42 PM Subject: [sqlite] sqlite 3.5.2 for 32 bits and 64 bits Hi All, I already had the sqlite library for 32 bits as libsqlite3.so.0.86 and now I want to build the sqlite library for 64bits for 64bits machine. Can someone help me with the information how to build the sqlite library which is used for 64bits machine. Thanks in advance for your help. JP Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [sqlite] Cache for SQLite
Hello Clark, I am using Apache + Fast-CGI :) But my next move will be to mod_perl. Currently I have only idea to use something like Cache::SharedMemoryCache or Cache::Memcached for implementing caching inside my application. What are you thinking about this? If you have any interesting ideas or knowledge - it'll be great if you share it with me. On Jan 24, 2008 6:06 PM, Clark Christensen <[EMAIL PROTECTED]> wrote: > I don't think you're going to get the kind of caching you want using Perl and > a web server (Apache, right?). There's just no persistence across processes, > no shared memory, no database connections. > > Now, Apache's mod_perl and some associated modules could get you all that and > more. For me, anyway, it requires a big adjustment in the way you build your > apps if you want to take advantage of the shared $dbh, shared variables, and > caching. For me, the investment isn't quite worth the benefit. > > -Clark -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
I feel compelled to throw in my $0.02 here. To everyone who thinks that SQLite should allow 'foo ' == 'foo': SQL was originally conceived as a query *language* -- a way for a human being to request a set of data from a database. It was specifically designed for ad-hoc queries. This little 'magic space trimming' feature exists to match the 'char(N)' data type. A char(10) field is always exactly 10 characters long; longer strings are truncated and shorter strings are space-padded. Most database engines are more efficient at these, because when all rows are the same width, the task of finding a particular row reduces to a simple array lookup; therefore, if performance is a critical issue (and when SQL was first formed, CPUs weren't quite as powerful as they are now.) But this presents a problem: the 'usual' definition of equality would mean that any comparisons to a char(N) field would need to be N characters long, or they would always fail. Since it's stupid to make people count spaces, somebody came up with the solution 'if they enter something shorter, pad it with spaces and then compare.' (If anyone wishes to quote the spec regarding space-extension and varchar(N) fields, first recall that the SQL specifications have been created by committees.) Since SQLite does not have any concept of a fixed-width field of character data, the whole concept of ignoring/appending trailing spaces doesn't even apply. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
I don't think you're going to get the kind of caching you want using Perl and a web server (Apache, right?). There's just no persistence across processes, no shared memory, no database connections. Now, Apache's mod_perl and some associated modules could get you all that and more. For me, anyway, it requires a big adjustment in the way you build your apps if you want to take advantage of the shared $dbh, shared variables, and caching. For me, the investment isn't quite worth the benefit. -Clark - Original Message From: Alexander Batyrshin <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 24, 2008 7:19:47 AM Subject: Re: [sqlite] Cache for SQLite On Jan 24, 2008 4:03 PM, Doug <[EMAIL PROTECTED]> wrote: > I don't know of a daemon, but based on someone else's post where they > described keeping a pool of sqlite3* handles to the database, and always > reusing the most recently used handle first (so that the SQLite page cache > is most likely still valid) I saw a very big jump in performance. > > Perhaps that would help in your case too? Sounds interesting, maybe it help me a little. I am using Perl DBD::SQLite, so i need some investigation how this library work. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Foreign Constraint Triggers Across Attached Databases
From Nico: You could always copy the users_history table records to an attached DB and "delete from users_history;" after every, or every N, transactions on your main DB. This ways your users_history table size is bounded in the main DB and you still get to keep all your history in a separate DB. ... = You know your app better than I, but I would think that if you used Nico's suggestion, but kept the most-recent X history records in the main database, that might be very useful to you in debugging a user's problem. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Foreign Constraint Triggers Across Attached Databases
Hi Sam, Brainstorming your suggestion a bit, rather than allowing triggers to function across databases (which is understandably not practical given the architecture), perhaps there could be a way to define a "partition" within a database (similar to creating a folder on a drive). Borrowing from Oracle, maybe this could be called a "Schema", and a "create schema" command could be used to create it. Queries referencing a table in a different schema must preface the table name with the schema name - e.g., "SELECT * FROM Sam.users". Just some rainy day ideas.. - Jeff -Original Message- From: Samuel R. Neff [mailto:[EMAIL PROTECTED] Sent: Thursday, January 24, 2008 10:59 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Foreign Constraint Triggers Across Attached Databases I've run into two situations recently where I would have preferred to write triggers across databases. Both related to audit tracking of data. The first situation is that for every table, I have a corresponding history table that records the history of every record. So let's say I have CREATE TABLE Users (UserID, UserName); then I also have CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType, UserID, UserName) and to track transactions I have CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID) and to track the single active transaction I have CREATE TABLE ActiveTransaction(TransactionID) which is always blank except when in the middle of a transaction (which always starts with inserting a record to that table, and then deleting it right before commit). So I have triggers on the Users table that whenever a record is inserted, updated, or deleted, the corresponding new values for insert/update and old values for delete are inserted into the history table. The triggers look like this: CREATE TRIGGER HI_Users AFTER INSERT ON Users FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'Can not update database when no transaction is active. Create a new transaction in the Transactions table and create an associated record in the ActiveTransaction table.') WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0; INSERT INTO Users_History ( TransactionID, ChangeType, UserID, UserName ) SELECT (SELECT MIN(TransactionID) FROM ActiveTransaction), 'I', NEW.UserID, NEW.UserName ; END; Due to the restriction that triggers cannot span databases, I have my main data tables, history tables, and the ActiveTransaction table all in the same database. I'd really rather the history tables be in a separate database because they can grow quite large and when I ask a customer to e-mail me their database, I'd like them to be able to easily e-mail the main data only without the extra history info. Also, it would be much cleaner if the ActiveTransaction table was in TEMP instead of in MAIN so each connection clearly has it's own table (except where now they share the same table definition, just the data is never shared due to convention of being populated only within a transaction). I hope these examples are helpful. I would like to see the ability to create a trigger that spans database some day and would expect that the trigger could be defined and simply would error out if at runtime the required database was not present. Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, January 24, 2008 6:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached Databases Triggers between two separate databases are not allowed since if you DETACH one of the databases, the triggers obviously will no longer work. If two separate databases are so inseparably bound that they need triggers between them, why not just make them a single database? The same goes for foreign key constraints. There are *severe* implementation difficulties trying to get this to work across separate database. If you have a foreign key in a separate database, that really argues that the two databases ought to be one. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign Constraint Triggers Across Attached Databases
On Thu, Jan 24, 2008 at 10:59:17AM -0500, Samuel R. Neff wrote: > Due to the restriction that triggers cannot span databases, I have my main > data tables, history tables, and the ActiveTransaction table all in the same > database. I'd really rather the history tables be in a separate database > because they can grow quite large and when I ask a customer to e-mail me > their database, I'd like them to be able to easily e-mail the main data only > without the extra history info. You could always copy the users_history table records to an attached DB and "delete from users_history;" after every, or every N, transactions on your main DB. This ways your users_history table size is bounded in the main DB and you still get to keep all your history in a separate DB. Triggers across DBs could only work if there was a way to create a persistent DB ATTACHment -- a way to tell SQLite to ATTACH dependencies of such triggers whenever you open the DB. I imagine that wouldn't be too hard, but I'm not writing nor maintaining the code. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Foreign Constraint Triggers Across Attached Databases
I've run into two situations recently where I would have preferred to write triggers across databases. Both related to audit tracking of data. The first situation is that for every table, I have a corresponding history table that records the history of every record. So let's say I have CREATE TABLE Users (UserID, UserName); then I also have CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType, UserID, UserName) and to track transactions I have CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID) and to track the single active transaction I have CREATE TABLE ActiveTransaction(TransactionID) which is always blank except when in the middle of a transaction (which always starts with inserting a record to that table, and then deleting it right before commit). So I have triggers on the Users table that whenever a record is inserted, updated, or deleted, the corresponding new values for insert/update and old values for delete are inserted into the history table. The triggers look like this: CREATE TRIGGER HI_Users AFTER INSERT ON Users FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'Can not update database when no transaction is active. Create a new transaction in the Transactions table and create an associated record in the ActiveTransaction table.') WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0; INSERT INTO Users_History ( TransactionID, ChangeType, UserID, UserName ) SELECT (SELECT MIN(TransactionID) FROM ActiveTransaction), 'I', NEW.UserID, NEW.UserName ; END; Due to the restriction that triggers cannot span databases, I have my main data tables, history tables, and the ActiveTransaction table all in the same database. I'd really rather the history tables be in a separate database because they can grow quite large and when I ask a customer to e-mail me their database, I'd like them to be able to easily e-mail the main data only without the extra history info. Also, it would be much cleaner if the ActiveTransaction table was in TEMP instead of in MAIN so each connection clearly has it's own table (except where now they share the same table definition, just the data is never shared due to convention of being populated only within a transaction). I hope these examples are helpful. I would like to see the ability to create a trigger that spans database some day and would expect that the trigger could be defined and simply would error out if at runtime the required database was not present. Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, January 24, 2008 6:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached Databases Triggers between two separate databases are not allowed since if you DETACH one of the databases, the triggers obviously will no longer work. If two separate databases are so inseparably bound that they need triggers between them, why not just make them a single database? The same goes for foreign key constraints. There are *severe* implementation difficulties trying to get this to work across separate database. If you have a foreign key in a separate database, that really argues that the two databases ought to be one. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Shared Cache for Processes
Hi all, Could the 'Shared Cache' option in SQLite theoretically improve the performance of the db if used by multiple processes? The application in particular is Apache using pre-fork processes accessing the same db. The info at http://www.sqlite.org/sharedcache.html seems to indicate it could benefit threads only. I believe it would not but would like confirmation from someone else. Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Advice on adding page cache statistics to Sqlite
Hi, I'm looking into adding cache statistics (at the pager level) to SQLite to try to better understand how often SQLite is 'hitting' the disk for a particular application. Two ways I've considered doing this are: 1. Add a static array to SQLite and populate from function 'pagerAcquire'. - Pros: Simple - Cons: Not particularly accessible, resides in memory, harder to dynamically adjust 2. Using some sort of special table (similar to sqlite_master) and populate using SQL. - Pros: Data accessible from application. Cons: Not sure where to start At this stage I'm only looking to record primitive information. If, for example, we went down route 2, below could be a suitable table definition. "CREATE TABLE sqlite_cache_stats(\n" " total_page_reads_from_cache integer,\n" " total_page_reads_from_disk integer,\n" " session_page_reads_from_cache integer,\n" " session_page_reads_from_disk integer\n" ")" Where 'total_*' fields are kept for the life of the database file and "session_*" are kept for the life of the connection, resetting on opening the database. I would appreciate some suggestions on how to progress this. Perhaps something similar may already exist, so any pointers would be beneficial Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
On Jan 24, 2008 4:03 PM, Doug <[EMAIL PROTECTED]> wrote: > I don't know of a daemon, but based on someone else's post where they > described keeping a pool of sqlite3* handles to the database, and always > reusing the most recently used handle first (so that the SQLite page cache > is most likely still valid) I saw a very big jump in performance. > > Perhaps that would help in your case too? Sounds interesting, maybe it help me a little. I am using Perl DBD::SQLite, so i need some investigation how this library work. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_MAX_EXPR_DEPTH
Hi, Is there any limitation on the number of elements in IN clause ? Thanks. Felix Radensky Embedded Solutions Ltd. drh-2 wrote: > > Jerry Krinock <[EMAIL PROTECTED]> wrote: >> My query: >> >> DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N) >> >> using the C API. When N exceeds 999, I get an error stating that the >> maximum depth of 1000 has been exceeded, and this is documented in >> http://www.sqlite.org/limits.html >> , item 5. >> >> Of course, I could fix this by doing multiple queries when N>999, but >> my code is very nicely encapsulated as is, and that change would make >> it all yucky. So I'd like other alternatives. > > The WHERE expression is parsed as follows: > >(...(((id=1 OR id=2) OR id=3) OR id=4) OR ...) OR id=N) > > If you draw this as a tree, you find that, indeed, it is N > levels deep. But by explicit use of parentheses, you can > force a balanced tree with a depth of only logN. > >(...((id=1 OR id=2) OR (id=3 OR id=4)) OR (...)...) > > But instead of all that trouble, why not just say: > >id IN (1,2,3,4,5,...,N) > > The latter is not only more efficient, but easier for human > readers to understand as well. > >> >> 1. I would describe my query as "1000 clauses wide". I'm not nesting >> anything "1000 levels deep". Is there a way to rewrite my query and >> make it work? >> >> 2. Documentation implies that I can change the parameter >> SQLITE_MAX_EXPR_DEPTH from the default of 1000. But I can't find >> SQLITE_MAX_EXPR_DEPTH in sqlite3.h. Seems to be neither a compiler >> macro nor a global. (Mac OS X 10.5, sqlite 3.4.0). Where is it? >> > > Look in sqliteLimit.h > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- View this message in context: http://www.nabble.com/SQLITE_MAX_EXPR_DEPTH-tp14966776p15065740.html Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Cache for SQLite
I don't know of a daemon, but based on someone else's post where they described keeping a pool of sqlite3* handles to the database, and always reusing the most recently used handle first (so that the SQLite page cache is most likely still valid) I saw a very big jump in performance. Perhaps that would help in your case too? > -Original Message- > From: Alexander Batyrshin [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 24, 2008 7:38 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Cache for SQLite > > Hello All, > I've used SQLite for half of year and find it perfect. > But for my case (web-site) there is a gap in feature like cache. > I know that file-system cache do a lot of work for SQLite, but it is > still not perfect. > For example IMHO it's possible to crate something like "daemon" which will be > between application and SQLite engine and which will do caching. > > Do you know any extensions/modification/patch that allow to add cache feature? > > -- > Alexander Batyrshin aka bash > bash = Biomechanica Artificial Sabotage Humanoid > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] startswith and contains
You're right, % is standard. MS Access used * and more recently supports both * and %. I'm not aware of any other DB that supports using * as wildcard for LIKE. It's in the docs, but is kinda buried in the middle of this page: http://sqlite.org/lang_expr.html Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Jonas Sandman [mailto:[EMAIL PROTECTED] Sent: Thursday, January 24, 2008 7:28 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] startswith and contains Oh I have no idea. I thought LIKE with '%' was a standard, % being the wildcard. /Jonas On Jan 24, 2008 1:25 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: > I was still trying "*jup*" > Could you show me documentation page, where this is mentioned? > > Pavel Kosina > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Cache for SQLite
Hello All, I've used SQLite for half of year and find it perfect. But for my case (web-site) there is a gap in feature like cache. I know that file-system cache do a lot of work for SQLite, but it is still not perfect. For example IMHO it's possible to crate something like "daemon" which will be between application and SQLite engine and which will do caching. Do you know any extensions/modification/patch that allow to add cache feature? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite omit using index
As usual your answer is perfect in explanation!
Thank you very much.
On Jan 24, 2008 2:49 AM, <[EMAIL PROTECTED]> wrote:
> "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> > Hello All,
> > I've found that SQLite-3.5.4 doesnt use index in this situation:
> >
> > sqlite> create table t1 (id int primary key, val int);
> > sqlite> create table t2 (id unique, val int primary key);
> > sqlite> explain query plan update t1 set val = (select t2.val from t2
> > where t1.id = t2.id);
> > 0|0|TABLE t1
> > 0|0|TABLE t2
> >
> > In this case, SQLite should takes value from t2 via unique id INDEX,
> > but it doesn't
> >
>
> The t2.id field has no datatype specified. That means it has
> an affinity of NONE. (See http://www.sqlite.org/datatypes3.html
> paragraph 2.1 bullet 3.) That means that if you insert a string
> into t2.id it goes in as a string:
>
>INSERT INTO t2(id) VALUES('123');
>SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
> --> answer "text"
>
> Or if you insert an integer, it goes in as an integer:
>
>INSERT INTO t2(id) VALUES(123);
>SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
> --> answer "integer"
>
> But the t1.id column to which you are comparing t2.id has
> an affinity of INTEGER. (paragraph 2.1 bullet 1.) That means
> if you insert a string it is converted into an integer if it
> looks like an integer.
>
>INSERT INTO t1(id) VALUES('123');
>SELECT typeof(id) FROM t1 WHERE rowid=last_insert_rowid();
> --> answer "integer"
>
> Now, the index on t2(id) also uses NO-affinity because the
> affinity of the column is NONE. So the index stores separate
> entries in separate places for '123' and 123. But the value
> you are comparing against is always an integer, because it is
> coming out of t1.id which has integer affinity. So if you
> look up the entry using just the integer value 123, you will
> miss the '123' entry. That is unacceptable. Hence, you cannot
> use a value with INTEGER-affinity as the key to an index
> with NO-affinity.
>
> Hence the index on t2.id cannot be used to speed the search.
>
> You can get the index to work by saying:
>
>create table t1(id int primary key, val int);
>create table t2(id INT unique, val int primary key);
>
> Note the added INT in the definition of t2.id, thus
> giving it integer affinity. You'll still be able to store
> text in t2.id if you want to, but if that text looks like
> an integer, it is converted into an integer.
>
> Please also not that INT PRIMARY KEY is not the same
> thing as INTEGER PRIMARY KEY. You probably want
> to use INTEGER PRIMARY KEY in this context, not what
> you have - but that is a whole other issue.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: startswith and contains
Pavel Kosina <[EMAIL PROTECTED]> wrote: I was still trying "*jup*" You are thinking about GLOB. This should work too: SELECT * FROM some WHERE xyz GLOB '*jup*' Could you show me documentation page, where this is mentioned? http://sqlite.org/lang_expr.html Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] startswith and contains
Oh I have no idea. I thought LIKE with '%' was a standard, % being the wildcard. /Jonas On Jan 24, 2008 1:25 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: > I was still trying "*jup*" > Could you show me documentation page, where this is mentioned? > > Pavel Kosina > > > > Jonas Sandman napsal(a): > > > 1. SELECT * FROM some WHERE xyz LIKE '%jup%' > > > > 2. SELECT * FROM SOME WHERE zyx LIKE 'jul%' > > > > should work. > > > > On Jan 24, 2008 12:44 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: > > > >> Hello, > >> > >> How to do following queries?: > >> > >> 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be > >> text "jup") > >> 2/ select * from some where xyz STARTSWITH "jup" (the xyz starts with > >> "jup") > >> > >> Thank you. > >> > >> > >> -- > >> Pavel Kosina > >> > >> > >> - > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> - > >> > >> > >> > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] startswith and contains
I was still trying "*jup*" Could you show me documentation page, where this is mentioned? Pavel Kosina Jonas Sandman napsal(a): 1. SELECT * FROM some WHERE xyz LIKE '%jup%' 2. SELECT * FROM SOME WHERE zyx LIKE 'jul%' should work. On Jan 24, 2008 12:44 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: Hello, How to do following queries?: 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be text "jup") 2/ select * from some where xyz STARTSWITH "jup" (the xyz starts with "jup") Thank you. -- Pavel Kosina - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign Constraint Triggers Across Attached Databases
On Jan 23, 2008, at 10:07 PM, [EMAIL PROTECTED] wrote: I have been trying to implement the paradigm of using Triggers to emulate referential integrity, for example cascading updates and deletes between two database tables. This works when the two database tables are in “main” but when I try to create the triggers between database tables in attached database tables, the create doesn’t work. Tried several iterations and couldn’t come up with the proper SQL syntax to do this. Is there a way to do this, add referential integrity triggers with database tables in attached databases? Even better, if/when is SQLite going to support built-in referential integrity using foreign key constraints in the SQL when creating the tables? Can it support referential integrity with attached database tables? Triggers between two separate databases are not allowed since if you DETACH one of the databases, the triggers obviously will no longer work. If two separate databases are so inseparably bound that they need triggers between them, why not just make them a single database? The same goes for foreign key constraints. There are *severe* implementation difficulties trying to get this to work across separate database. If you have a foreign key in a separate database, that really argues that the two databases ought to be one. Example use case: “main” containing an “AccountTable”, and a daily transactional table “-MM-DD.db3” that will be attached to “main” that contains a TransactionTable that has a “foreign key relation” to the “AccountTable” by having an account primary key as a foreign key in the transaction table. -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] startswith and contains
1. SELECT * FROM some WHERE xyz LIKE '%jup%' 2. SELECT * FROM SOME WHERE zyx LIKE 'jul%' should work. On Jan 24, 2008 12:44 PM, Pavel Kosina <[EMAIL PROTECTED]> wrote: > Hello, > > How to do following queries?: > > 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be > text "jup") > 2/ select * from some where xyz STARTSWITH "jup" (the xyz starts with "jup") > > Thank you. > > > -- > Pavel Kosina > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] startswith and contains
Hello, How to do following queries?: 1/ select * from some where xyz CONTAINS "jup" (anywhere in xyz could be text "jup") 2/ select * from some where xyz STARTSWITH "jup" (the xyz starts with "jup") Thank you. -- Pavel Kosina - To unsubscribe, send email to [EMAIL PROTECTED] -
