[sqlite] how can I find source of sqlite V 2.x
Hi all, My project had to use Qt 3.3.8, which supports sqlite V2 only. How can I get the source of V2? BR Russell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
Hi, > I'm thinking whether this is a memory leak or not sort of depends on > your definition. If a process is designed to remain open for long > periods of time with little activity, and it ends up taking up 1 > gigabyte of memory, that looks an awful lot like a leak to me. There > are likely to be at least three instances of this application running, > and after they all run for a month, they're likely to be consuming 5 > gigabytes of memory. This is not acceptable. If SQLite's sorted > query is taking up 2.5 megabytes of memory every time this piece of > the application is invoked, I need to know how to ensure that that > memory is released. Most probably SQLite does release malloc'ed memory using free(). Note however that free() merely notifies the C runtime the free'd piece of memory is not used anymore. The C runtime does not necessarily release this piece of memory to the system. That would be inefficient. As a result, the process appears to be still using the memory. That's why tools such as 'top' on Unix are not necessarily appropriate to detect memory leaks, they show memory still being used by a process, although the program has called free(). The C runtime might give memory back to the system when the system is short on memory, or unused memory may be swapped to disk. This is a C runtime issue, not an SQLite issue. As already explained, it could indeed be that the memory footprint is a problem for you, but a memory footprint problem is not a memory leak: http://en.wikipedia.org/wiki/Memory_leak -- Dimitri ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
If the maximum memory footprint is too large, then you should arrange to have a smaller memory footprint. For instance, you can use PRAGMA cache_size to reduce the footprint there, use PRAGMA temp_store to make sure you aren't storing temporary tables in memory, call sqlite3_release_memory() to release memory if it's using too much, use sqlite3_soft_heap_limit() to provide a soft limit on the footprint, use sqlite3_enable_shared_cache() to let your different threads share memory. "Memory leak" is a pretty specific thing, it means that the program no longer references memory in a way that will allow it to release the memory. You mention that the memory footprint is too large - it would be really helpful if you put up a database and query which demonstrated what you're describing, and the constraints you _wish_ things to operate under. Then people can make specific recommendations. -scott On Mon, Mar 24, 2008 at 2:34 PM, Rob Richardson <[EMAIL PROTECTED]> wrote: > I'm thinking whether this is a memory leak or not sort of depends on > your definition. If a process is designed to remain open for long > periods of time with little activity, and it ends up taking up 1 > gigabyte of memory, that looks an awful lot like a leak to me. There > are likely to be at least three instances of this application running, > and after they all run for a month, they're likely to be consuming 5 > gigabytes of memory. This is not acceptable. If SQLite's sorted > query is taking up 2.5 megabytes of memory every time this piece of > the application is invoked, I need to know how to ensure that that > memory is released. > > Here's a brief description of the application. My company, Rad-Con, > Inc., is a major supplier of annealing furnaces and related equipment > and software to metal processors worldwide. The application monitors > the annealing process on a customer's site. There could be well over > a hundred annealing bases. The applicaton's first screen displays an > overview of all of the bases, whether they have furnaces, if the > furnaces are turned on, and so on. A user can double-click on base to > see details. A button on the detail screen calls up a trend display. > Trend data is stored in SQLite database files, one per base. The > application executes the query I described to find when the last row > was written to the table, and uses that to calculate the times that > will be displayed on the graph. Then, the application reads the > entire table and plots the data. When the user is finished, he closes > the trend screen. My requirement is to ensure that the amount of > memory allocated to my application before the trend screen is > displayed and after the trend screen is closed is the same. If more > memory is allocated after it is closed, that is a leak, by my > definition. > > > RobR > > > > > On 3/23/08, Christian Smith <[EMAIL PROTECTED]> wrote: > > On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote: > > > My SQLite library is built from the single translation unit > > > sqlite.c/sqlite.h. That file contains the version number 3.3.17. > > > > > > I do not have valgrind, but circumstantial evidence that this is a > > > SQLite problem is strong. When stepping through my code, I see that > > > my application's memory jumps by over 2.5 megabytes when the > > > sqlite3_step() method is called when using either the sorted query or > > > the query using max(). The unsorted query doesn't show any memory > > > jump. Also, the difference in memory consumption before this part of > > > the code is executed and after it is left is the same size as the jump > > > in memory when sqlite3_step() is called. > > > > > > When doing a sorted query, the result set is formed in a temporary database > > somewhere defined by the environment. In your case, it sounds like the > > temporary database is memory based. Once the result set is done with, > SQLite > > may return the memory to the OS using free, but that will show under the > > process's virtual memory footprint. > > > > You can tell SQLite to use a disk based temporary database using: > > http://sqlite.org/pragma.html#pragma_temp_store > > > > Using this, your memory usage will probably be more stable. > > > > However, this certainly isn't a memory leak. > > > > > > > > > > RobR > > > > > > > Christian > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > Please do not copy or forward this message or any attachments without > my permission. Remember, asking permission is a great way to get me > to visit your site! > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___
[sqlite] Lit
"Rob Richardson" <[EMAIL PROTECTED]> wrote: > I'm thinking whether this is a memory leak or not sort of depends on > your definition. If a process is designed to remain open for long > periods of time with little activity, and it ends up taking up 1 > gigabyte of memory, that looks an awful lot like a leak to me. There > are likely to be at least three instances of this application running, > and after they all run for a month, they're likely to be consuming 5 > gigabytes of memory. This is not acceptable. If SQLite's sorted > query is taking up 2.5 megabytes of memory every time this piece of > the application is invoked, I need to know how to ensure that that > memory is released. The test harness for SQLite counts the number of mallocs and frees and prints an error if the two do not match. This happens every time we run a test. We also run the test suite through valgrind prior to each release. The test scripts provide close to 100% test coverage. About 70% of the SQLite source code is devoted to testing. Because of the extensive testing outlined above, memory leaks in SQLite have never been a problem. Historically, whenever somebody comes forward with claims of memory leaks in SQLite, it generally works out to be memory leaks in their application. Based on prior experience and our extensive tests, claims that SQLite leaks 2.5MB of RAM every time it sorts are generally met with great skepticism. I won't say that it is impossible that SQLite is leaking memory, though it does seem unlikely. Certainly the claim that SQLite leaks 2.5MB on every sort is an extraordinary one, and extradinary claims require extraordinary proof. If you would like to submit a script or a short program that appears to cause SQLite to leak memory, we will be happy to look into the problem. But until I see an actual demonstration of the problem, I'm going to assume that the problem is really outside of SQLite and pay the matter no further attention. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
I'm thinking whether this is a memory leak or not sort of depends on your definition. If a process is designed to remain open for long periods of time with little activity, and it ends up taking up 1 gigabyte of memory, that looks an awful lot like a leak to me. There are likely to be at least three instances of this application running, and after they all run for a month, they're likely to be consuming 5 gigabytes of memory. This is not acceptable. If SQLite's sorted query is taking up 2.5 megabytes of memory every time this piece of the application is invoked, I need to know how to ensure that that memory is released. Here's a brief description of the application. My company, Rad-Con, Inc., is a major supplier of annealing furnaces and related equipment and software to metal processors worldwide. The application monitors the annealing process on a customer's site. There could be well over a hundred annealing bases. The applicaton's first screen displays an overview of all of the bases, whether they have furnaces, if the furnaces are turned on, and so on. A user can double-click on base to see details. A button on the detail screen calls up a trend display. Trend data is stored in SQLite database files, one per base. The application executes the query I described to find when the last row was written to the table, and uses that to calculate the times that will be displayed on the graph. Then, the application reads the entire table and plots the data. When the user is finished, he closes the trend screen. My requirement is to ensure that the amount of memory allocated to my application before the trend screen is displayed and after the trend screen is closed is the same. If more memory is allocated after it is closed, that is a leak, by my definition. RobR On 3/23/08, Christian Smith <[EMAIL PROTECTED]> wrote: > On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote: > > My SQLite library is built from the single translation unit > > sqlite.c/sqlite.h. That file contains the version number 3.3.17. > > > > I do not have valgrind, but circumstantial evidence that this is a > > SQLite problem is strong. When stepping through my code, I see that > > my application's memory jumps by over 2.5 megabytes when the > > sqlite3_step() method is called when using either the sorted query or > > the query using max(). The unsorted query doesn't show any memory > > jump. Also, the difference in memory consumption before this part of > > the code is executed and after it is left is the same size as the jump > > in memory when sqlite3_step() is called. > > > When doing a sorted query, the result set is formed in a temporary database > somewhere defined by the environment. In your case, it sounds like the > temporary database is memory based. Once the result set is done with, SQLite > may return the memory to the OS using free, but that will show under the > process's virtual memory footprint. > > You can tell SQLite to use a disk based temporary database using: > http://sqlite.org/pragma.html#pragma_temp_store > > Using this, your memory usage will probably be more stable. > > However, this certainly isn't a memory leak. > > > > > > RobR > > > > Christian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Please do not copy or forward this message or any attachments without my permission. Remember, asking permission is a great way to get me to visit your site! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance under load
Sam Carleton wrote: > On Mon, Mar 24, 2008 at 4:11 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > >> Try the math over: >> >> 24 hours * 60 minutes * 60 seconds = 86,400 seconds/day. >> >> 100,000 hits/day / 86,400 seconds/day = 1.1574 hits/sec (on average). >> >> Of course, this is on average. A real website getting "100K hits per >> day" would generally expect 10x to 15x this rate during heavy times. >> >> That's still not ~69 hits/sec, however. > > > yep, I was off by a factor of 60. > > >> > As I am developing the software, is there anything I need to keep in >> > mind to help optimize the database usage to achieve the million hits a >> > day the "Appropriate Uses for SQLite" >> >> The big thing is that locks are exclusive across the whole database, >> so an application needs to get it, do what it needs, and get out. >> A clean database design and proper use of indexes on critical columns >> (for queries) as well as transactions (for updates) are most likely the >> first places to look. > > > So can SQLite can keep up with the load of 40 to 50 viewing stations > going at one time? Or am I better off looking at other options? If I > should look at other options, what would you recommend? The > application is used at events, each event having it's own set of > images and data. What I really like about SQLite is that it is file > based and I can keep the data for each event with the images for that > event, allowing my customer an easy way to back things up at the end > of the event. I also like the cost, too;) > > Sam This is a bit like asking "how long is a piece of string". If you are doing short transactions and have a well conceived database and efficient server side software (using things like PHP and CGI are detrimental) then it should work. If you do a lot of inserts and updates or have SQL which results in row searches then it will not work. > ___ > 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] performance under load
My performance measurements using a modest server and a more efficient www server than Apache gives an Sqlite capability of 30-50 hits per second using simple SQL selects. Think of that as a reasonable upper limit. Jay A. Kreibich wrote: > On Mon, Mar 24, 2008 at 03:55:12PM -0400, Sam Carleton scratched on the wall: > > >>>From the web site's "Appropriate Uses for SQLite" it says that "any >>site that gets fewer than 100K hits/day should work fine with SQLite". >> I did the math and that looks to be around 69 hits a second. > > > Try the math over: > > 24 hours * 60 minutes * 60 seconds = 86,400 seconds/day. > > 100,000 hits/day / 86,400 seconds/day = 1.1574 hits/sec (on average). > > Of course, this is on average. A real website getting "100K hits per > day" would generally expect 10x to 15x this rate during heavy times. > > That's still not ~69 hits/sec, however. > > >>As I am developing the software, is there anything I need to keep in >>mind to help optimize the database usage to achieve the million hits a >>day the "Appropriate Uses for SQLite" > > > The big thing is that locks are exclusive across the whole database, > so an application needs to get it, do what it needs, and get out. > A clean database design and proper use of indexes on critical columns > (for queries) as well as transactions (for updates) are most likely the > first places to look. > >-j > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [FreeBSD] Can read but can't write?
On Mon, 24 Mar 2008 21:57:52 +0100, Gilles Ganault <[EMAIL PROTECTED]> wrote: > I'm having the following issue trying to access an SQLite 3.3.17 >database from PHP+PDO under FreeBSD 6.3: I can read, but can't write, >with no error reported by PHP or /var/log/messages. Found what it was: Even if a file is set to 664 and owned by the right user, the _directory_ in which the file lives has precedence. In this case, I just chowned it to root:www, and chmoded it to 664: [/usr/local/share/asterisk/agi-bin]# ll drwxrwxr-x 3 root www 512 Mar 24 22:05 . drwxr-xr-x 9 root wheel512 Mar 14 08:05 .. -rw-rw-r-- 1 www www 3072 Mar 24 22:05 test.sqlite Learned something new today. Hope this helps. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Command to create a database in sqlite
sqlite3_open. Mozaharul Haque wrote: > Hi, > > A silly asking. I found the command to create a table but what about to > create a database? > > The table would be stored in the sqlite_master table. > > And how do I refer (full path) to the database using application like > Basic4ppc 6.05. > > > Please help. > > > regards, > > Mozaharul Haque > > ___ > 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] [FreeBSD] Can read but can't write?
Hello I'm having the following issue trying to access an SQLite 3.3.17 database from PHP+PDO under FreeBSD 6.3: I can read, but can't write, with no error reported by PHP or /var/log/messages. I've tried both Apache and Lighttpd as web server, to no avail. Here's some information: 1. echo exec('id'); => uid=80(www) gid=80(www) groups=80(www) 2. The PHP script and the SQLite database are owned by www:www: [/usr/local/www/apache22/data]# ll drwxr-xr-x 2 root wheel 512 Mar 24 19:52 . drwxr-xr-x 6 root wheel 512 Mar 24 18:56 .. -rw-r--r-- 1 www www2463 Mar 24 20:00 test.php [/usr/local/share/asterisk/agi-bin]# ll drwxr-xr-x 3 root wheel512 Mar 24 18:38 . drwxr-xr-x 9 root wheel512 Mar 14 08:05 .. -rw-rw-r-- 1 www www 3072 Mar 24 18:37 test.sqlite 3. And here's the code: //GOOD $dbh = new PDO("sqlite:test.sqlite"); //GOOD $dbh = new PDO("sqlite:/tmp/test.sqlite"); $dbh = new PDO("sqlite:/usr/local/share/asterisk/agi-bin/test.sqlite"); $time = time(); $current = date("Y-m-d H:i:s",$time); $sql = "INSERT INTO mytable VALUES (NULL,'$current')"; print "$sql"; $dbh->exec($sql); $sql = "SELECT * FROM mytable"; foreach($dbh->query($sql) as $row) { print $row['name'] . "\n"; } $dbh = null; I don't understand why test.php can read, but cannot write. Could it be that SQLite does something that requires 777 access to the directory where the database file lives? Something else? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance under load
On Mon, Mar 24, 2008 at 4:11 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > Try the math over: > > 24 hours * 60 minutes * 60 seconds = 86,400 seconds/day. > > 100,000 hits/day / 86,400 seconds/day = 1.1574 hits/sec (on average). > > Of course, this is on average. A real website getting "100K hits per > day" would generally expect 10x to 15x this rate during heavy times. > > That's still not ~69 hits/sec, however. yep, I was off by a factor of 60. > > As I am developing the software, is there anything I need to keep in > > mind to help optimize the database usage to achieve the million hits a > > day the "Appropriate Uses for SQLite" > > The big thing is that locks are exclusive across the whole database, > so an application needs to get it, do what it needs, and get out. > A clean database design and proper use of indexes on critical columns > (for queries) as well as transactions (for updates) are most likely the > first places to look. So can SQLite can keep up with the load of 40 to 50 viewing stations going at one time? Or am I better off looking at other options? If I should look at other options, what would you recommend? The application is used at events, each event having it's own set of images and data. What I really like about SQLite is that it is file based and I can keep the data for each event with the images for that event, allowing my customer an easy way to back things up at the end of the event. I also like the cost, too;) Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance under load
On Mon, Mar 24, 2008 at 03:55:12PM -0400, Sam Carleton scratched on the wall: > >From the web site's "Appropriate Uses for SQLite" it says that "any > site that gets fewer than 100K hits/day should work fine with SQLite". > I did the math and that looks to be around 69 hits a second. Try the math over: 24 hours * 60 minutes * 60 seconds = 86,400 seconds/day. 100,000 hits/day / 86,400 seconds/day = 1.1574 hits/sec (on average). Of course, this is on average. A real website getting "100K hits per day" would generally expect 10x to 15x this rate during heavy times. That's still not ~69 hits/sec, however. > As I am developing the software, is there anything I need to keep in > mind to help optimize the database usage to achieve the million hits a > day the "Appropriate Uses for SQLite" The big thing is that locks are exclusive across the whole database, so an application needs to get it, do what it needs, and get out. A clean database design and proper use of indexes on critical columns (for queries) as well as transactions (for updates) are most likely the first places to look. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance under load
"Sam Carleton" <[EMAIL PROTECTED]> wrote: > My application is an apache based kiosk system that displays images. > The SQLite database is used by PHP to track user info, who is logged > in and what they have selected. SQLite is NOT managing anything about > the files. I have a few customers that are running with 40 to 50 > kiosks. > > >From the web site's "Appropriate Uses for SQLite" it says that "any > site that gets fewer than 100K hits/day should work fine with SQLite". > I did the math and that looks to be around 69 hits a second. Actually, it works out to about 69 hits per *minute* or about 1.16 hits per second. > Considering the SQLite database is used on page loads and when users > tag images, but NOT when requesting images, I believe that 69 hits a > second is more then fast enough. Am I correct or is there something > else I need to be taking into consideration? > > As I am developing the software, is there anything I need to keep in > mind to help optimize the database usage to achieve the million hits a > day the "Appropriate Uses for SQLite" > > Sam > ___ > 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] performance under load
My application is an apache based kiosk system that displays images. The SQLite database is used by PHP to track user info, who is logged in and what they have selected. SQLite is NOT managing anything about the files. I have a few customers that are running with 40 to 50 kiosks. >From the web site's "Appropriate Uses for SQLite" it says that "any site that gets fewer than 100K hits/day should work fine with SQLite". I did the math and that looks to be around 69 hits a second. Considering the SQLite database is used on page loads and when users tag images, but NOT when requesting images, I believe that 69 hits a second is more then fast enough. Am I correct or is there something else I need to be taking into consideration? As I am developing the software, is there anything I need to keep in mind to help optimize the database usage to achieve the million hits a day the "Appropriate Uses for SQLite" Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Issue with LEFT JOIN, index, and literal in ON clause
The following behavior when using LEFT OUTER JOIN with an indexed column and literals in the ON clause is, I'm pretty sure, wrong. SQLite seems to convert the join to an INNER JOIN in some cases. The capture is from SQLite 3.4.2, but I get the same results with 3.5.7. I also copied the raw SQL at the end for your copy-and-pasting pleasure. SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> CREATE TABLE "aaa" ( ...> 'id' integer NOT NULL PRIMARY KEY, ...> 'o_id' integer UNSIGNED NOT NULL, ...> 'o_type' integer NOT NULL ...> ); sqlite> sqlite> CREATE TABLE 'bbb' ( ...> 'id' integer NOT NULL PRIMARY KEY ...> ); sqlite> sqlite> CREATE TABLE 'ccc' ( ...> 'id' integer NOT NULL PRIMARY KEY ...> ); sqlite> sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1); sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2); sqlite> sqlite> INSERT INTO bbb ('id') VALUES (3); sqlite> INSERT INTO ccc ('id') VALUES (4); sqlite> sqlite> SELECT aaa.id, bbb.id, ccc.id ...> FROM aaa ...> LEFT OUTER JOIN bbb ...> ON (aaa.o_id=bbb.id AND aaa.o_type=1) ...> LEFT OUTER JOIN ccc ...> ON (aaa.o_id=ccc.id AND aaa.o_type=2); 1|3| 2||4 sqlite> This is correct, but add an index and... sqlite> CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type'); sqlite> sqlite> SELECT aaa.id, bbb.id, ccc.id ...> FROM aaa ...> LEFT OUTER JOIN bbb ...> ON (aaa.o_id=bbb.id AND aaa.o_type=1) ...> LEFT OUTER JOIN ccc ...> ON (aaa.o_id=ccc.id AND aaa.o_type=2); 1|3| sqlite> I expect the second SELECT to produce the same results as the first (presumably faster). So how can I rewrite the query to compensate? sqlite> INSERT INTO ccc ('id') VALUES (3); sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (3, 3, 2); sqlite> sqlite> SELECT aaa.id, bbb.id, ccc.id ...> FROM aaa ...> LEFT OUTER JOIN bbb ...> ON (aaa.o_id=bbb.id) ...> LEFT OUTER JOIN ccc ...> ON (aaa.o_id=ccc.id) ...> WHERE ...> (aaa.o_type=1 AND bbb.id IS NOT NULL) OR ...> (aaa.o_type=2 AND ccc.id IS NOT NULL); 1|3|3 2||4 3|3|3 sqlite> This is the solution I saw elsewhere on this list, and it works, but I want exactly one of the joined tables to be non-null. sqlite> SELECT aaa.id, bbb.id, ccc.id ...> FROM aaa ...> LEFT OUTER JOIN bbb ...> ON (aaa.o_id=bbb.id AND (aaa.o_type=1 OR 0)) ...> LEFT OUTER JOIN ccc ...> ON (aaa.o_id=ccc.id AND (aaa.o_type=2 OR 0)); 1|3| 2||4 3||3 sqlite> This is what I want, but the extra 0 makes me sad. So what am I doing wrong? Daniel Ring -- CREATE TABLE "aaa" ( 'id' integer NOT NULL PRIMARY KEY, 'o_id' integer UNSIGNED NOT NULL, 'o_type' integer NOT NULL ); CREATE TABLE 'bbb' ( 'id' integer NOT NULL PRIMARY KEY ); CREATE TABLE 'ccc' ( 'id' integer NOT NULL PRIMARY KEY ); INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1); INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2); INSERT INTO bbb ('id') VALUES (3); INSERT INTO ccc ('id') VALUES (4); SELECT aaa.id, bbb.id, ccc.id FROM aaa LEFT OUTER JOIN bbb ON (aaa.o_id=bbb.id AND aaa.o_type=1) LEFT OUTER JOIN ccc ON (aaa.o_id=ccc.id AND aaa.o_type=2); CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type'); SELECT aaa.id, bbb.id, ccc.id FROM aaa LEFT OUTER JOIN bbb ON (aaa.o_id=bbb.id AND aaa.o_type=1) LEFT OUTER JOIN ccc ON (aaa.o_id=ccc.id AND aaa.o_type=2); INSERT INTO ccc ('id') VALUES (3); INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (3, 3, 2); SELECT aaa.id, bbb.id, ccc.id FROM aaa LEFT OUTER JOIN bbb ON (aaa.o_id=bbb.id AND aaa.o_type=1*(aaa.id/aaa.id)) LEFT OUTER JOIN ccc ON (aaa.o_id=ccc.id AND aaa.o_type=2*(aaa.id/aaa.id)); SELECT aaa.id, bbb.id, ccc.id FROM aaa LEFT OUTER JOIN bbb ON (aaa.o_id=bbb.id AND (aaa.o_type=1 OR 0)) LEFT OUTER JOIN ccc ON (aaa.o_id=ccc.id AND (aaa.o_type=2 OR 0)); -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance of bulk insertion
Hi, Mahalakshmi, Regarding: "..Am I doing right or it's a lengthy process.If so suggest some other way for inserting the records?..." I must confess I have not spent much time looking at the details of your email, but you will want to be sure to use a single transaction to insert many rows in your table if performance is an issue: http://www.sqlite.org/lang_transaction.html You'll likely want to use a transaction for at least 100 rows at a time -- or perhaps even for all 4. After completing the insert, you may find you wish to use the CREATE INDEX command to speed up queries. 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fprintf(stderr,...) wrote over my database.........
Sounds like the file was closed then re-opened, But the db was opened in between assigning the db file number to STDERR. Your app then happily used fprintf(STDERR, " blah blah blah" ); writing into the DB file. Solutions: 1. Don't use hard coded stderr. 2. Dont use freopen. (why are you re-opening a STDERR in the first place?) 3. Rebuild your logging routines. to open a specific log file. 4. Use syslog ? (if your on a *nix). HTH, Ken Mark Gilbert <[EMAIL PROTECTED]> wrote: Anyone seen this ? - My app is running happily like it has for years. Something happens.. - Result is that my logging which normally goes to a completely different file (connected to stderr with freopen) ends up connected to the SQLite database file, and writes all over the start of the file.. - Database corrupt Anyone have any wisdom on this ? thanks ___ 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] fprintf(stderr,...) wrote over my database.........
Anyone seen this ? - My app is running happily like it has for years. Something happens.. - Result is that my logging which normally goes to a completely different file (connected to stderr with freopen) ends up connected to the SQLite database file, and writes all over the start of the file.. - Database corrupt Anyone have any wisdom on this ? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE3_OPEN returns SQLITE_NOMEM
Dear All, I have just started using the SQLite Db in my applications. My application runs under MIPS processor. I have generated the Sqlite3.dll and Sqlite3.lib file for the MIPS processor and it is geting compiled. When i use Sqlit3_Open function to open a DB, it fails with the error message SQLITE_NOMEM. But the same code is running fine in the Windows mode. How should i rectify this? Can anyone help me in this regard. Thanks Kartthi -- View this message in context: http://www.nabble.com/SQLITE3_OPEN-returns-SQLITE_NOMEM-tp16254109p16254109.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] Rowid After Sorting
1) SELECT rowid,Id,Name FROM MyTable ORDER BY Name; Rowid Id Name 4 4 aaa 3 3 bbb 2 2 xxx 1 1 zzz 2) "create table Temp as select Name from Mytable order by Name;" RowidId Name 1 4 aaa 2 3 bbb 3 2 xxx 4 1 zzz I wish to perform all operations in my code with sorted order of the Name field. So, to find the index of a name, which is in sorted order, I need the Rowid to be changed as shown in case 2 rather than in case 1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance of bulk insertion
Hi, I am working in 3.3.6 The table schema of my code is as follows. "CREATE TABLE ALBUMARTIST( AlbumArtistId INTEGER PRIMARY KEY NOT NULL, AlbumArtistName TEXT NOT NULL COLLATE NOCASE,UNIQUE(AlbumArtistName));" "CREATE TABLE ARTIST( ArtistId INTEGER PRIMARY KEY NOT NULL, ArtistName TEXT NOT NULL COLLATE NOCASE , UNIQUE(ArtistName));" "CREATE TABLE ALBUM( AlbumId INTEGER PRIMARY KEY NOT NULL, AlbumName TEXT NOT NULL COLLATE NOCASE , UNIQUE(AlbumName));" "CREATE TABLE MUSIC( Id INTEGER PRIMARY KEY NOT NULL, Track TEXT NOT NULL, URL TEXT NOT NULL, Album_Id INTEGER, Artist_Id INTEGER, AlbumArtist_Id INTEGER);" "CREATE TABLE PLAYLIST ( PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListTrack TEXT, PlayListUrl TEXT);" "CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN DELETE FROM ALBUM WHERE AlbumId = old.Album_Id and not exists (SELECT Id FROM MUSIC WHERE Album_Id = old.Album_Id); DELETE FROM ARTIST WHERE ArtistId = old.Artist_Id and not exists (SELECT Id FROM MUSIC WHERE Artist_Id = old.Artist_Id); DELETE FROM ALBUMARTIST WHERE AlbumArtistId = old.AlbumArtist_Id and not exists (SELECT Id FROM MUSIC WHERE AlbumArtist_Id = old.AlbumArtist_Id); DELETE FROM TRACKLIST WHERE PlayListUrl = old.URL and not exists (SELECT Id FROM MUSIC WHERE URL = old.URL); END;" My database has 4 records. My need is to add all the Track in the Table MUSIC to the Table TRACKLIST.For that i have used the following code. while(u32_PlayListRecordCount < TotalRecordCount) { if ( sqlite3_prepare(pst_SqliteCallback->db," SELECT Track, URL FROM MUSIC LIMIT 100 OFFSET ? ; ",-1,_PlayListPrepareStmt,0)!= SQLITE_OK) { return SQLITE_DB_ERROR; } else { sqlite3_bind_int(pst_PlayListPrepareStmt,1, u32_OffsetValue); Now I will step and store all the 100 results in one Buffer. } if(e_ReturnStatus == SQLITE_DB_SUCCESS) { if ( sqlite3_prepare(pst_SqliteCallback->db,"INSERT INTO TRACKLIST (PlayListTypeId,PlayListTrack,PlayListUrl) VALUES (?,?,?);",-1,_PlayListPrepareStmt,0)!= SQLITE_OK) { return SQLITE_DB_ERROR; } else { for(u32_Offset = 0;u32_Offset < gu32_PlayListindex;u32_Offset++) Here gu32_PlayListindex is the count of "SELECT count(*) FROM MUSIC LIMIT 100 OFFSET ? ;" { sqlite3_bind_int(pst_PlayListPrepareStmt,1,PlayListIndex); sqlite3_bind_text(pst_PlayListPrepareStmt,2,st_PlayList[u32_Offset].PlayList TrackBuffer,-1,SQLITE_STATIC); sqlite3_bind_text(pst_PlayListPrepareStmt,3,st_PlayList[u32_Offset].PlayList UrlBuffer,-1,SQLITE_STATIC); u32_Return = sqlite3_step(pst_PlayListPrepareStmt); sqlite3_reset(pst_PlayListPrepareStmt); } u32_Return = sqlite3_finalize(pst_PlayListPrepareStmt); u32_ PlayListRecordCount = u32_ PlayListRecordCount + gu32_PlayListindex; } } else { return SQLITE_DB_ERROR; } } Am I doing right or it's a lengthy process.If so suggest some other way for inserting the records. But this takes very long time to insert all the Tracks into playlist.Totally I need to insert 4 records. Is there anyother way to insert huge number of data in to another table.. I want to insert each record after getting the Track from MUSIC select statement.Can we use Triggers for solving this. Can anyone help to solve this . Thanks & Regards, Mahalakshmi.M ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with sqlite3_blob_open()
That work! Thanks a lot. Adolfo. - Original Message - From: <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database"Sent: Monday, March 24, 2008 1:48 PM Subject: Re: [sqlite] Help with sqlite3_blob_open() > "A.J.Millan" <[EMAIL PROTECTED]> wrote: >> Hello all: >> >> Using Windows XP: >> >> D:\Z\Zator5>sqlite3 zdb1 >> SQLite version 3.5.4 >> Enter ".help" for instructions >> sqlite> .tables >> AgEfHolder AgEfemerAgVtHolder AgVtos Usr lnk >> AgEfIDt AgPdHolder AgVtIDt Block atm prm >> AgEfKlv AgPdIDt AgVtPre FreqUse blb >> >> sqlite> .schema blb >> CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER); >> sqlite> >> >> I'm having troubles with this pseudo code: >> >> > begin transaction >> >> > some successful operations on tables atm and blb >> >> > sqlite3_blob* pBlob; >> >> > // open Blob for read/write access >> > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, >> > ); >> >> Here res = 1, and I get the following error: >> >> SQL error: no such table: D:\Z\Zator5\zDB1.blb >> > > The "database name" is not the same thing as the name of the > file tht contains the database. The database name is the name > assigned to the database when you ATTACH it. Or, for the original > database, then name is "main". Or for TEMP tables, the name is > "temp". > > -- > D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with sqlite3_blob_open()
"A.J.Millan" <[EMAIL PROTECTED]> wrote: > Hello all: > > Using Windows XP: > > D:\Z\Zator5>sqlite3 zdb1 > SQLite version 3.5.4 > Enter ".help" for instructions > sqlite> .tables > AgEfHolder AgEfemerAgVtHolder AgVtos Usr lnk > AgEfIDt AgPdHolder AgVtIDt Block atm prm > AgEfKlv AgPdIDt AgVtPre FreqUse blb > > sqlite> .schema blb > CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER); > sqlite> > > I'm having troubles with this pseudo code: > > > begin transaction > > > some successful operations on tables atm and blb > > > sqlite3_blob* pBlob; > > > // open Blob for read/write access > > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, > > ); > > Here res = 1, and I get the following error: > > SQL error: no such table: D:\Z\Zator5\zDB1.blb > The "database name" is not the same thing as the name of the file tht contains the database. The database name is the name assigned to the database when you ATTACH it. Or, for the original database, then name is "main". Or for TEMP tables, the name is "temp". -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with sqlite3_blob_open()
Hello all: Using Windows XP: D:\Z\Zator5>sqlite3 zdb1 SQLite version 3.5.4 Enter ".help" for instructions sqlite> .tables AgEfHolder AgEfemerAgVtHolder AgVtos Usr lnk AgEfIDt AgPdHolder AgVtIDt Block atm prm AgEfKlv AgPdIDt AgVtPre FreqUse blb sqlite> .schema blb CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER); sqlite> I'm having troubles with this pseudo code: > begin transaction > some successful operations on tables atm and blb > sqlite3_blob* pBlob; > // open Blob for read/write access > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, > ); Here res = 1, and I get the following error: SQL error: no such table: D:\Z\Zator5\zDB1.blb Neither I have luck with: > int res = sqlite3_blob_open (dbPtr, "zDB1", "blb", "nm", Id, 1, ); D:\Z\ is my actual (working) directory Do is there some that I'm missing or misunderstood? Thanks for any idea. Adolfo. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it safe to ...
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > Hello, > Is it safe to use this algorithm: > > open_db > fork() > sql_do() // both parent and child executes sql statements > close_db > > I am not familiar with locking mechanism and I am afraid that if > parent and child will use the same DB handlers it can cause of DB > corruptions > It is not safe to carry an open SQLite database connection across a fork. The documentation says this somewhere, IIRC, but I don't remember exactly where. I should probably state this fact in the documentation for sqlite3_open()... -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Command to create a database in sqlite
"Mozaharul Haque" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > A silly asking. I found the command to create a table but what about > to create a database? Just give a file name to sqlite3_open, then create a table. If the file didn't exist, it will be created at this point. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help needed: using sqlite with j2me (MIDP)
Hi, I am trying to compile sqlite with a java program (basically for J2ME - MIDP application). for this purpose I have downloaded the amalgamation version of the code (3.5.x). But There are some problems. 1. how to mix a C/C++ code with j2me code. 2. how do i provide the java.sql.* classes in a j2me program because the j2me libraries does not support java.sql. If any body has done this then please help me. With Regards Amit Akela ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Command to create a database in sqlite
Hi, A silly asking. I found the command to create a table but what about to create a database? The table would be stored in the sqlite_master table. And how do I refer (full path) to the database using application like Basic4ppc 6.05. Please help. regards, Mozaharul Haque ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users