Re: [sqlite] How to specify regular expression in a query?
your example only needs a "like" not a regex. not having your schema i'll show a loose example: select id, flilepath from movies where filepath like "%MyMovie.%"; Bharath Booshan L wrote: Hello list, I have to perform a search something similar to this ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 2 /Volumes/Backup/MyMovies/Hello.mp4 3 /Volumes/Tiger/MyMovie.mov Search for file name MyMovie should retrieve ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 3 /Volumes/Tiger/MyMovie.mov To simplify, I am searching for a file name from a collection of absolute file paths. How can I achieve this in SQLite? Is there anyways I can use regular expression in a query to perform string matching. Thanks in advance, Bharath --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
thanks for all the help, I think I am getting closer ... but still broken ... look for the "---" for my comments --- tail of the install of sqlite 3.5.4 -- /usr/bin/install -c -d /usr/local/bin ./libtool --mode=install /usr/bin/install -c sqlite3 /usr/local/bin /usr/bin/install -c sqlite3 /usr/local/bin/sqlite3 /usr/bin/install -c -d /usr/local/include /usr/bin/install -c -m 0644 sqlite3.h /usr/local/include /usr/bin/install -c -m 0644 ../sqlite-3.5.4/src/sqlite3ext.h /usr/local/include /usr/bin/install -c -d /usr/local/lib/pkgconfig; /usr/bin/install -c -m 0644 sqlite3.pc /usr/local/lib/pkgconfig; # ls -l /usr/local/lib/libsql* -rw-r--r-- 1 root root 3221590 Jan 25 20:55 /usr/local/lib/libsqlite3.a -rwxr-xr-x 1 root root 823 Jan 25 20:55 /usr/local/lib/libsqlite3.la lrwxrwxrwx 1 root root 19 Jan 25 20:55 /usr/local/lib/libsqlite3.so -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 Jan 25 20:55 /usr/local/lib/libsqlite3.so.0 -> libsqlite3.so.0.8.6 -rwxr-xr-x 1 root root 1840520 Jan 25 20:55 /usr/local/lib/libsqlite3.so.0.8.6 --- then when I run the perl makefile: # SQLITE_LOCATION=/usr/local/lib perl Makefile.PL Checking installed SQLite version... Looks good Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 140 Using DBI 1.52 (for perl 5.008006 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::SQLite --- now things get funky when I make #make cp lib/DBD/SQLite.pm blib/lib/DBD/SQLite.pm /usr/bin/perl -p -e "s/~DRIVER~/SQLite/g" /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/Driver.xst > SQLite.xsi /usr/bin/perl /usr/lib/perl5/5.8.6/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.6/ExtUtils/typemap SQLite.xs > SQLite.xsc && mv SQLite.xsc SQLite.c gcc -c -I. -I/usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\"1.14\" -DXS_VERSION=\"1.14\" -fPIC "-I/usr/lib64/perl5/5.8.6/x86_64-linux-thread-multi/CORE" -DSQLITE_CORE -DSQLITE_ENABLE_FTS2 -DNDEBUG=1 -DSQLITE_PTR_SZ=8 -DHAVE_USLEEP=1 SQLite.c gcc -c -I. -I/usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\"1.14\" -DXS_VERSION=\"1.14\" -fPIC "-I/usr/lib64/perl5/5.8.6/x86_64-linux-thread-multi/CORE" -DSQLITE_CORE -DSQLITE_ENABLE_FTS2 -DNDEBUG=1 -DSQLITE_PTR_SZ=8 -DHAVE_USLEEP=1 dbdimp.c Running Mkbootstrap for DBD::SQLite () chmod 644 SQLite.bs rm -f blib/arch/auto/DBD/SQLite/SQLite.so gcc -shared SQLite.o dbdimp.o -o blib/arch/auto/DBD/SQLite/SQLite.so -lsqlite3 /usr/bin/ld: /usr/lib/gcc/x86_64-redhat-linux/4.0.0/../../../../lib64/libsqlite3.a(main.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/lib/gcc/x86_64-redhat-linux/4.0.0/../../../../lib64/libsqlite3.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [blib/arch/auto/DBD/SQLite/SQLite.so] Error 1 # --- I suspect that it has something to do with this being a 64 bit processor, I suspect that sqlite 3.5.4 is being bult as a 32 bit image --- and the perl module is a 64 bit image. and is looking for the 64 bit libs? Alexander Batyrshin wrote: if you DBD::SQlite built statically, then it uses it's internal SQLite If it's linked again libsqlite, you can check it by command ldd on: # find /usr/lib/perl5/ -name 'SQLite.so' /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7ed7000) libc.so.6 => /lib/libc.so.6 (0xb7da7000) libpthread.so.0 => /lib/libpthread.so.0 (0xb7d9) /lib/ld-linux.so.2 (0x4100) On Jan 26, 2008 12:00 AM, Jim Dodgen <[EMAIL PROTECTED]> wrote: thanks for the education, looks like i have multiple versions floating around, as seen below I'll blow a way the lib64 version just to eliminate the confusion. How do I tell which one is being used? computer A (this has 3.5.4 installed) lrwxrwxrwx 1 rootroot 19 Feb 8 2006 /usr/lib64/libsqlite3.so.0 -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 rootroot 19 Jan 24 15:59 /usr/local/lib/libsqlite3.so.0 -> libsqlite3.so.0.8.6 computer B (I have not done the upgrade here yet) lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib64/libsqlite3.so.0 -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 May 29 2007
[sqlite] OT Re: [sqlite] DBD::SQLite for 3.5.4?
On Fri, 2008-01-25 at 15:04 -0800, Jim Dodgen wrote:
> you are correct. sorry to offend
No offense taken, dude. :) I'm usually lurking in here only, anyway.
Also, I wasn't too serious...
What I *did* mean though, is, to tell you (and a lot of other readers,
for that matter) about the difference of a new mail and a reply. Good
mail clients offer a threaded view, highly useful with mailing lists.
Anyway, this list is one of the worst offenders with respect to proper
threading. ;)
guenther
--
char *t="[EMAIL PROTECTED]";
main(){ char h,m=h=*t++,*x=t+2*h,c,i,l=*x,s=0; for (i=0;i>=1)||!t[s+h]){ putchar(t[s]);h=m;s=0; }}}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How to specify regular expression in a query?
Hi! Why don't try with LIKE comparison: select * from table where column LIKE '%MyMovie%'; Bharath Booshan L escribió: Hello list, I have to perform a search something similar to this ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 2 /Volumes/Backup/MyMovies/Hello.mp4 3 /Volumes/Tiger/MyMovie.mov Search for file name MyMovie should retrieve ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 3 /Volumes/Tiger/MyMovie.mov To simplify, I am searching for a file name from a collection of absolute file paths. How can I achieve this in SQLite? Is there anyways I can use regular expression in a query to perform string matching. Thanks in advance, Bharath --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
if you DBD::SQlite built statically, then it uses it's internal SQLite If it's linked again libsqlite, you can check it by command ldd on: # find /usr/lib/perl5/ -name 'SQLite.so' /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7ed7000) libc.so.6 => /lib/libc.so.6 (0xb7da7000) libpthread.so.0 => /lib/libpthread.so.0 (0xb7d9) /lib/ld-linux.so.2 (0x4100) On Jan 26, 2008 12:00 AM, Jim Dodgen <[EMAIL PROTECTED]> wrote: > thanks for the education, looks like i have multiple versions floating > around, as seen below I'll blow a way the lib64 version just to > eliminate the confusion. > How do I tell which one is being used? > > computer A (this has 3.5.4 installed) > lrwxrwxrwx 1 rootroot 19 Feb 8 2006 /usr/lib64/libsqlite3.so.0 > -> libsqlite3.so.0.8.6 > lrwxrwxrwx 1 rootroot 19 Jan 24 15:59 > /usr/local/lib/libsqlite3.so.0 -> libsqlite3.so.0.8.6 > > computer B (I have not done the upgrade here yet) > lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib64/libsqlite3.so.0 -> > libsqlite3.so.0.8.6 > lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib/libsqlite3.so.0 -> > libsqlite3.so.0.8.6 > lrwxrwxrwx 1 root root 19 Jul 26 2007 /usr/local/lib/libsqlite3.so.0 -> > libsqlite3.so.0.8.6 > > I think I should clean out all the sqlite libs and bins, is all i have > to delete are sqlite3 and libsqlite3.so.0* on the system? > > thanks for the help > > Jim > > > > > > Alexander Batyrshin wrote: > > 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] > >> - > >> > >> > >> > > > > > > > > > > > - > 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?
you are correct. sorry to offend Karsten Bräckelmann wrote: On Thu, 2008-01-24 at 19:41 -0800, Jim Dodgen wrote: sorry I attached another email by accident, it's content is not related to my question No, you pretty much did so on purpose. You *replied* to a previous post, which inherits special headers for threading. Instead, you should have wrote a *new* mail. Please note that neither the messages subject nor its body are involved in proper threading, and thus it's not sufficient to remove quoted text. Googling for "thread hijacking", Message-Id, In-Reply-To and References should be enlightening... guenther - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
thanks for the education, looks like i have multiple versions floating around, as seen below I'll blow a way the lib64 version just to eliminate the confusion. How do I tell which one is being used? computer A (this has 3.5.4 installed) lrwxrwxrwx 1 rootroot 19 Feb 8 2006 /usr/lib64/libsqlite3.so.0 -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 rootroot 19 Jan 24 15:59 /usr/local/lib/libsqlite3.so.0 -> libsqlite3.so.0.8.6 computer B (I have not done the upgrade here yet) lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib64/libsqlite3.so.0 -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib/libsqlite3.so.0 -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 Jul 26 2007 /usr/local/lib/libsqlite3.so.0 -> libsqlite3.so.0.8.6 I think I should clean out all the sqlite libs and bins, is all i have to delete are sqlite3 and libsqlite3.so.0* on the system? thanks for the help Jim Alexander Batyrshin wrote: 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
On Thu, 2008-01-24 at 19:41 -0800, Jim Dodgen wrote:
> sorry I attached another email by accident, it's content is not related
> to my question
No, you pretty much did so on purpose.
You *replied* to a previous post, which inherits special headers for
threading. Instead, you should have wrote a *new* mail. Please note that
neither the messages subject nor its body are involved in proper
threading, and thus it's not sufficient to remove quoted text.
Googling for "thread hijacking", Message-Id, In-Reply-To and References
should be enlightening...
guenther
--
char *t="[EMAIL PROTECTED]";
main(){ char h,m=h=*t++,*x=t+2*h,c,i,l=*x,s=0; for (i=0;i>=1)||!t[s+h]){ putchar(t[s]);h=m;s=0; }}}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Differences SQLite 32bit/64bit
Hello All, I am interesting is there any benefits in SQLite built for 64 bit linux platform (amd64)? I am not good at subject of 32/64, but IMHO SQLite 64 should work faster or I am wrong? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
I am really thankful to all of you who replied my query and helped solve my problem. Again thank you very much. Best Regards, Yasir Nisar - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
[sqlite] Re: can not make indexing work fro me
Alex Katebi <[EMAIL PROTECTED]> wrote: create table rtm(dest blob, mask blob, nh blob); create index rtmi on rtm(dest); select nh from rtm where dest = bitwise_and(addr, mask) order by mask desc limit 1; hex(dest) hex(mask) rowid -- --- 01010100 FF00 1 0 2 0101 3 0100 FF00 4 04048000 C0005 7780 FFF0 6 So for a packet dest address 01010165, there will be four hits row 1,2,3,4 but 1 has the longest bit mask. Every thing works except for the indexing. My custom function bitwise_and is always called for all the rows in my table. Which means that indexing is not being used. How do you expect the index to be used for such a query? An index can help quickly select rows where a particular column has a particular value. Turning this around, an index can quickly dismiss as non-matching any row where the column that's part of the index doesn't match the given value, without looking at any other columns in this row. But in your query, the value you are looking for depends on those other columns. So you can't dismiss any row based only on fields that are part of the index - you have to look at each row in the table. What should I do to get around this problem. Relational database doesn't appear to be a suitable data structure. Essentially, you have a set of prefixes, and for a given word you want to find its longest prefix that's in the set. For that, I'd build a prefix tree aka trie: http://en.wikipedia.org/wiki/Trie Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re[sqlite] trieving only one record in the database
This is the syntax of a select statement as shown in the manual: SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr] [GROUP BY expr-list] [HAVING expr] [compound-op select]* [ORDER BY sort-expr-list] [LIMIT integer [( OFFSET | , ) integer]] The bit you want is LIMIT[integer]. Say LIMIT 1 and you'll get one record of the found set. Mike On 25/01/2008 13:45:32, arbalest06 ([EMAIL PROTECTED]) wrote: Good day! I want to retrieve only one record in the database with a condition that may cause a retrieval of many records. The purpose of this is for me to check only if there is at least 1 record in the database. So if given my condition, the query would now search for one record in the database that satisfies the condition the returns that record. After one record is found, it should now stop finding and retrieving other records that satisfies the condition. How can i do this? What query can i issue? Thank you and God bless! -- View this message in context: http://www.nabble.com/Retrieving-only-one- record-in-the-database-tp15087994p15087994.html Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re[sqlite] trieving only one record in the database
You could do SELECT whatever FROM table WHERE criteria LIMIT 1 or SELECT COUNT(*) AS RowCount FROM table WHERE criteria Then just check that the value of RowCount > 0. The nice thing about the latter is that now you are set up if you ever want to know how many rows meet the criteria that you've chosen. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: arbalest06 <[EMAIL PROTECTED]> > > Good day! > > I want to retrieve only one record in the database with a condition that may > cause a retrieval of many records. The purpose of this is for me to check > only if there is at least 1 record in the database. So if given my > condition, the query would now search for one record in the database that > satisfies the condition the returns that record. After one record is found, > it should now stop finding and retrieving other records that satisfies the > condition. How can i do this? What query can i issue? > > Thank you and God bless! > -- > View this message in context: > http://www.nabble.com/Retrieving-only-one-record-in-the-database-tp15087994p1508 > 7994.html > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] can not make indexing work fro me
Hi All, I am using sqlite to make an IP routing table. A routing table has a Destination and a Mask column. I chose blob for my types since IPv6 will be 16 bytes long, IPv4 is only 4 bytes. The query will be some IP packet destination address that needs to be forwarded in the internet. This address is anded bitwise with the mask in the table then tested for equality with the desination. All the rows that pass this test will be candidates, then the row with the longest mask length is chosen. Packet is then forwarded towards the nexthop address that was stored as another column in the same table row. An IPv4 Example: create table rtm(dest blob, mask blob, nh blob); create index rtmi on rtm(dest); select nh from rtm where dest = bitwise_and(addr, mask) order by mask desc limit 1; hex(dest) hex(mask) rowid -- --- 01010100 FF00 1 0 2 0101 3 0100 FF00 4 04048000 C0005 7780 FFF0 6 So for a packet dest address 01010165, there will be four hits row 1,2,3,4 but 1 has the longest bit mask. Every thing works except for the indexing. My custom function bitwise_and is always called for all the rows in my table. Which means that indexing is not being used. What should I do to get around this problem. Typically I will have about 200,000 enteries in my routing table for the internet and I want to be more efficient and not query every row. I am open to all suggestions. Thanks, Alex
[sqlite] Re: Re[sqlite] trieving only one record in the database
arbalest06 <[EMAIL PROTECTED]> wrote: I want to retrieve only one record in the database with a condition that may cause a retrieval of many records. The purpose of this is for me to check only if there is at least 1 record in the database. You can append a "LIMIT 1" clause to your query. Or do something like select exists(select ...); putting your original query inside parentheses (this will produce a single record with a single column holding either 0 or 1). Or, you can simply prepare your query as is, but only call sqlite3_step once and see if it returns SQLITE_ROW or SQLITE_DONE. Then reset or finalize the statement. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re[sqlite] trieving only one record in the database
Good day! I want to retrieve only one record in the database with a condition that may cause a retrieval of many records. The purpose of this is for me to check only if there is at least 1 record in the database. So if given my condition, the query would now search for one record in the database that satisfies the condition the returns that record. After one record is found, it should now stop finding and retrieving other records that satisfies the condition. How can i do this? What query can i issue? Thank you and God bless! -- View this message in context: http://www.nabble.com/Retrieving-only-one-record-in-the-database-tp15087994p15087994.html Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared Cache for Processes
Hi, Speaking only as a non-professional, I still try to answer. I don't want to comment on the benefits of shared cache mode now, but only on the question if it can be enabled in Apache. And I believe it can. As you say Apache pre-forks different processes, but within each process it pre-establishes different threads and I think the default is in the order of 100. So practically you will only use a single process and hence can easily enable shared-cache mode. This assumes that your application is loaded into Apache, and does not run via cgi. Regards, Edzard Pasma Op 24-jan-2008, om 16:48 heeft Brandon, Nicholas (UK) het volgende geschreven: 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] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
On 01/26/2008 02:40 AM, Bharath Booshan L wrote: > Hello list, I have to perform a search something similar to this ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 2 /Volumes/Backup/MyMovies/Hello.mp4 3 /Volumes/Tiger/MyMovie.mov Search for file name MyMovie should retrieve ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 3 /Volumes/Tiger/MyMovie.mov To simplify, I am searching for a file name from a collection of absolute file paths. How can I achieve this in SQLite? Is there anyways I can use regular expression in a query to perform string matching. Bharath -- I suppose if say, your Movie Table is called 'MyMovies' then, I you could: -- the following assumes all files have a 3-char extent select ID, FilePath from MyMovies where FilePath glob '*/MyMovie.???' Or (more portably but less precisely) ... select ID, FilePath from MyMovies where FilePath like '%/MyMovie.%' This could be slow on a large Table. Have you thought about splitting the Path (man dirname) from the FileName (man basename) ? Something like: create table MovieFiles ( ID integer, FilePath varchar(255), -- contains `dirname MovieFile` FileName varchar(255) -- contains `basename MovieFile` ) ; Might make for simpler queries and faster too if you add an index on the FileName Column. create index MovieFilesFileName on MovieFiles( FileName ) ; HTH -- kjh - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify regular expression in a query?
Hello Bharath Booshan L, yes, with SQLiteSpy you can do this: drop table if exists t; create table t (id integer primary key, filepath text); insert into t values (1, '/Volumes/Backup/MyMovies/MyMovie.mp4'); insert into t values (2, '/Volumes/Backup/MyMovies/Hello.mp4'); insert into t values (3, '/Volumes/Tiger/MyMovie.mov'); select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$'; Ralf > I have to perform a search something similar to this > > ID FilePath > 1 /Volumes/Backup/MyMovies/MyMovie.mp4 > 2 /Volumes/Backup/MyMovies/Hello.mp4 > 3 /Volumes/Tiger/MyMovie.mov > > >Search for file name MyMovie should retrieve > > ID FilePath > 1 /Volumes/Backup/MyMovies/MyMovie.mp4 > 3 /Volumes/Tiger/MyMovie.mov > > >To simplify, I am searching for a file name from a collection of absolute >file paths. > >How can I achieve this in SQLite? > >Is there anyways I can use regular expression in a query to perform string >matching. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
On 1/25/08, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > 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 ? I've just installed sqlite 3.5.4 on Solaris10 but DBD::SQLite 1.14 fails with tests 07 and 08. They end up with a segmentation fault at this location: #0 0xfefa8d48 in sqlite3_finalize (pStmt=0x2a32f8) at ../sqlite-3.5.4/ src/vdbeapi.c:47 47 sqlite3_mutex *mutex = v->db->mutex; If I can ask here, is there any solution to this problem? -- Radek - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to specify regular expression in a query?
Hello list, I have to perform a search something similar to this ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 2 /Volumes/Backup/MyMovies/Hello.mp4 3 /Volumes/Tiger/MyMovie.mov Search for file name MyMovie should retrieve ID FilePath 1 /Volumes/Backup/MyMovies/MyMovie.mp4 3 /Volumes/Tiger/MyMovie.mov To simplify, I am searching for a file name from a collection of absolute file paths. How can I achieve this in SQLite? Is there anyways I can use regular expression in a query to perform string matching. Thanks in advance, Bharath --- Robosoft Technologies - Come home to Technology Disclaimer: This email may contain confidential material. If you were not an intended recipient, please notify the sender and delete all copies. Emails to and from our network may be logged and monitored. This email and its attachments are scanned for virus by our scanners and are believed to be safe. However, no warranty is given that this email is free of malicious content or virus. - To unsubscribe, send email to [EMAIL PROTECTED] -
