Re: [sqlite] Feature request: extend the IN operator
2011/8/8 Simon Slavin slav...@bigfraud.org: You're quite right. If someone was going to write that functionality into a SQLite app in real life they'd probably use 'LIKE' or 'GLOB' and store all the possibilities in one row. FTS3/FTS4 is better as index for lists. See my test script for Igor. We can fast search any id by using match operator on FTS table. P.S. FTS table has nice scalability. Check insertion a lot of records and insertion speed is constant. I did try 400 millions of records (and did get database size 100 Gb). -- 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] Using Comand Lines inside a program
Have a look at the shell.c file. The relevant code is at line 1655 in the latest version (in the 3.7.7.1 amalgamation .zip file). It is only about 120 lines, and most of it you could probably cut out in your own program (all the errors and processing for the command line. You can just substitute your parameters directly) On Sun, Aug 7, 2011 at 7:07 PM, gabriel.b...@gmail.com gabriel.b...@gmail.com wrote: I need to import some data data to my sqlite I'm aware of the .import comand. I wish i could do the foliwing but inside a C# program i'm using this library (http://system.data.sqlite.org) .separator | .import mytextfile.txt mytable ___ 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] %Q vs. %q
What is the difference between mprintf(insert into a values(%Q);, val); and mprintf(insert into a values('%q');, val); If there is no difference, why have two almost identical things? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] %Q vs. %q
On 8 Aug 2011, at 10:35am, Baruch Burstein wrote: What is the difference between mprintf(insert into a values(%Q);, val); and mprintf(insert into a values('%q');, val); If there is no difference, why have two almost identical things? The '%q' signifies a 64-bit integer expressed as hex. My guess about capital Q vs. lower-case q is that one uses A-F and the other uses a-f for 10 to 15. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] %Q vs. %q
Sorry, I meant sqlite3_mprintf On Mon, Aug 8, 2011 at 12:40 PM, Simon Slavin slav...@bigfraud.org wrote: On 8 Aug 2011, at 10:35am, Baruch Burstein wrote: What is the difference between mprintf(insert into a values(%Q);, val); and mprintf(insert into a values('%q');, val); If there is no difference, why have two almost identical things? The '%q' signifies a 64-bit integer expressed as hex. My guess about capital Q vs. lower-case q is that one uses A-F and the other uses a-f for 10 to 15. 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] %Q vs. %q
On 8 August 2011 10:45, Baruch Burstein bmburst...@gmail.com wrote: Sorry, I meant sqlite3_mprintf On Mon, Aug 8, 2011 at 12:40 PM, Simon Slavin slav...@bigfraud.org wrote: On 8 Aug 2011, at 10:35am, Baruch Burstein wrote: What is the difference between mprintf(insert into a values(%Q);, val); and mprintf(insert into a values('%q');, val); If there is no difference, why have two almost identical things? The '%q' signifies a 64-bit integer expressed as hex. My guess about capital Q vs. lower-case q is that one uses A-F and the other uses a-f for 10 to 15. Simon. http://www.sqlite.org/c3ref/mprintf.html Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] %Q vs. %q
On 8 Aug 2011, at 10:58am, Simon Davies wrote: The '%q' signifies a 64-bit integer expressed as hex. My guess about capital Q vs. lower-case q is that one uses A-F and the other uses a-f for 10 to 15. Simon. [Slavin] http://www.sqlite.org/c3ref/mprintf.html Which standard we're using is anyone's guess. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Build tables on the fly
Hi!! I am using SQLite in an embedded device. The device writes a lot of values to a lot of tables every 3 seconds. Sometime, i want to add parameters to the DB, but i dont want to disturb the on-going operation of writing to the DB every 3 seconds. In order to prevent fragmentation, i build the whole table with its whole number of rows. So i have to build a table with 50 columns and 10 million rows. This is something like 4 GB!! But i can't lock the DB for so long and i have to do it as quick as possible. What is the best approach to build tables on the fly, do it quick, and not disturb the SQLite on-going process that writes data to the DB ?? I know that its better to write all the data in one transaction. Maybe duplicate an existing table is a faster? :confused: -- View this message in context: http://old.nabble.com/Build-tables-on-the-fly-tp32216906p32216906.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] exists clause in select
Hi, I am having trouble writing a select statement. What I want to do is this: SELECT * FROM customers WHERE NOT EXISTS (SELECT * FROM orders WHERE customers.id = orders.cust_id); Is this possible at all in Sqlite? regards W. Canfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] exists clause in select
Hi, this seems like correct SQL to me. SQLite should execute it. Doesn't it work? Martin Am 08.08.2011 07:50, schrieb William Canfield: Hi, I am having trouble writing a select statement. What I want to do is this: SELECT * FROM customers WHERE NOT EXISTS (SELECT * FROM orders WHERE customers.id = orders.cust_id); Is this possible at all in Sqlite? regards W. Canfield ___ 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] %Q vs. %q
Baruch Burstein bmburst...@gmail.com wrote: What is the difference between mprintf(insert into a values(%Q);, val); and mprintf(insert into a values('%q');, val); Read the fine manual: http://www.sqlite.org/c3ref/mprintf.html The %Q option works like %q except it also adds single quotes around the outside of the total string. Additionally, if the parameter in the argument list is a NULL pointer, %Q substitutes the text NULL (without single quotes). So, the difference is in what happens when val is NULL. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In memory v/s tmpfs
HI, I performed an experiment where I do db operations on an in-memory database and compared it with the results from same operations on a database in tmpfs.(Same db structure,records etc) From the point of view of performance, I expected similar performance , tmpfs being a little slower due to filesystem overhead. However, the operations on tmpfs was much slower than expected. What could be a possible explanation for this behaviour? One difference int configurations is that the sqlite lib is built for multithreading in the tmpfs scenario. -Sreekumar Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
What could be a possible explanation for this behaviour? One difference int configurations is that the sqlite lib is built for multithreading in the tmpfs scenario. Could it be an overhead of mutexes? Make tests with the same SQLite library. Run test with tmpfs using strace to see if filesystem overhead is more than you expect. And post brief results here - it will be interesting to see and/or try to explain them. Pavel On Mon, Aug 8, 2011 at 9:04 AM, sreekumar...@gmail.com wrote: HI, I performed an experiment where I do db operations on an in-memory database and compared it with the results from same operations on a database in tmpfs.(Same db structure,records etc) From the point of view of performance, I expected similar performance , tmpfs being a little slower due to filesystem overhead. However, the operations on tmpfs was much slower than expected. What could be a possible explanation for this behaviour? One difference int configurations is that the sqlite lib is built for multithreading in the tmpfs scenario. -Sreekumar Sent from BlackBerry® on Airtel ___ 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] exists clause in select
On Mon, 08 Aug 2011 07:50:21 +0200, William Canfield bill.canfi...@gmx.de wrote: Hi, I am having trouble writing a select statement. What I want to do is this: SELECT * FROM customers WHERE NOT EXISTS (SELECT * FROM orders WHERE customers.id = orders.cust_id); Is this possible at all in Sqlite? So you are looking for customers without orders. Try: SELECT customers.id,customers.someothercol FROM customers LEFT OUTER JOIN orders ON orders.cust_id = customers.id WHERE orders.cust_id IS NULL; regards W. Canfield -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 访问我的图片
嗨, 我创建了一个Netlog个人主页,其中包括我的图片、视频、博客和活动。非常希望邀请你成为我的朋友,一起共享我们的天地。这需要你先在Netlog上注册哦!在登录后,你也可以创建属于自己的个人主页了。 看一看: http://zh.netlog.com/go/mailurl/-bT0yODcxNjc1NTcmbD0xJmdtPTEyJnU9JTJGZ28lMkZyZWdpc3RlciUyRmlkJTNELWMzRnNhWFJsTFhWelpYSnpRSE54YkdsMFpTNXZjbWNfJTI2dWlkJTNEMTM2NTAyOTcz 祝好, 邓超 如果你不想再接接收到来自朋友的任何邀请,则请 http://zh.netlog.com/go/mailurl/-bT0yODcxNjc1NTcmbD0yJmdtPTEyJnU9JTJGZ28lMkZub21haWxzJTJGaW52aXRlJTJGZW1haWwlM0QtYzNGc2FYUmxMWFZ6WlhKelFITnhiR2wwWlM1dmNtY18lMjZjb2RlJTNEMDkyMDYzNDE_ Don't want to receive invitations from your friends anymore? http://zh.netlog.com/go/mailurl/-bT0yODcxNjc1NTcmbD0zJmdtPTEyJnU9aHR0cCUzQSUyRiUyRmVuLm5ldGxvZy5jb20lMkZnbyUyRm5vbWFpbHMlMkZpbnZpdGUlMkZlbWFpbCUzRC1jM0ZzYVhSbExYVnpaWEp6UUhOeGJHbDBaUzV2Y21jXyUyNmNvZGUlM0QwOTIwNjM0MQ__ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users