http://blog.gmane.org/gmane.comp.db.sqlite.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.sqlite.general/43080 I've been lightly using TWS at http://karlht.gigdrag.net/ for some years now, and it's been a pleasure to use. But I've wondered if there are any plans to update it to SQLite 3 and Tcl 8.5? I'd hate to duplicate someone else's effort, if so. If not, I suppose I can start on the effort and report back to the list if I run into roadblocks. Anyone have any thoughts? Thanks, --K. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Karl Thiessen 2008-12-04T00:09:59 http://comments.gmane.org/gmane.comp.db.sqlite.general/43076 I'm playing about with the following syntax: CREATE TABLE atable ( a_ref integer not null primary key autoincrement, a_text text ); CREATE TABLE btable ( b_ref integer not null primary key autoincrement, b_text text, a_ref integer not null default 0 references atable (a_ref) on delete set default on update cascade ); My understanding of SQL (and I could be wrong) is that if a row in btable contains a reference to a value in atable a_ref, and the relevant row in atable is deleted, or the value of a_ref in atable is updated, then there should be a consequential change in the value of a_ref in table btable. This appears not to be working. Have I misunderstood the syntax for the SQL REFERENCES statement, or is the following from 2003 (??) still applicable: "That's not a legal FOREIGN KEY clause; you have to specify what the foreign key references. SQLite parses, but does not enforce, syntactically-legal FOREIGN KEY specifications; there's a PRAGMA that will retrieve foreign-key information from table definitions, allowing you to enforce such constraints with application code." which is in http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql Howard Lowndes 2008-12-03T23:19:59 http://comments.gmane.org/gmane.comp.db.sqlite.general/43066 Hi, I'm using SQLite from a scripting language (Rebol). There I have a CONNECT/CREATE function that calls sqlite3_open to open and if necessary create a database file. My situation is now that I have an application that needs to create several database files. Hence I need to call sqlite3_open serveral times. Each time I get back a new database handle. I didn't find any good information about dealing with multiple database handles beside that for each one I need to call sqlite3_close. Some questions: 1. If I open database A and B and get back handleA and handleB, can I call sqlite3* function for database A using handleB? 2. Could I close handleA after I got handleB and still use all open database files and tables or do I have to keep all database handles around? 3. What's exactly the role of a database handle when working with several database files and ATTACHED databases? To me it looks a bit fuzzy. Unique_User 2008-12-03T15:30:40 http://comments.gmane.org/gmane.comp.db.sqlite.general/43063 Hi all, Doing a join on a fts3 table can be very slow. I'm using these tables: CREATE TABLE general ( ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ... ); CREATE VIRTUAL TABLE general_text using fts3 ( ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, a TEXT, b TEXT, c TEXT, d TEXT, e TEXT DEFAULT '', f TEXT ); which creates these tables CREATE TABLE general_text_content( docid INTEGER PRIMARY KEY,c0ID, c1a, c2b, c3c, c4d, c5e, c6f); CREATE TABLE general_text_segdir( level integer, idx integer, start_block integer, leaves_end_block integer, end_block integer, root blob, primary key(level, idx)); CREATE TABLE general_text_segments( blockid INTEGER PRIMARY KEY, block blob); and fill them with about half a million rows. On these tables this query is very slow (about 1 row per second) select g.id from general g, general_text gt where g.id = gt.id; and these ones have a normal speed: select g.id from general g, general_text_content gt where g.id = gt.docid; select g.id from general g, general_text_content gt where g.id = gt.c0ID; What is causing the fts3 code to make joining so slow? Cheers, Jos _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Jos van den Oever 2008-12-03T14:57:32 http://comments.gmane.org/gmane.comp.db.sqlite.general/43060 Hi, I am fiddling around with the r-tree module a bit, which works great and gives the effect I am looking for. The only thing is that I wish I could speed up inserts. Populating the rtree-index with 1 million objects takes about 180 seconds (using prepared statements). Is there any trick to speed up the inserts here ? Oyvind _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Oyvind Idland 2008-12-03T13:01:55 http://comments.gmane.org/gmane.comp.db.sqlite.general/43055 Hello, I've looked for an explaination in the following pages without success: http://www.sqlite.org/optoverview.html http://www.sqlite.org/cvstrac/wiki?p=QueryPlans So I hope someone can help me... CREATE TABLE node( id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE edge( orig INTEGER REFERENCES node, dest INTEGER REFERENCES node, PRIMARY KEY(orig, dest) -- A unique index on (orig, dest) is created here automatically ); INSERT INTO node VALUES (1, 'node1'); INSERT INTO node VALUES (2, 'node2'); INSERT INTO node VALUES (3, 'node3'); INSERT INTO node VALUES (4, 'node4'); INSERT INTO edge VALUES (1, 2); INSERT INTO edge VALUES (1, 3); INSERT INTO edge VALUES (1, 4); INSERT INTO edge VALUES (2, 3); INSERT INTO edge VALUES (2, 4); ANALYZE; elbart0-GANU6spQydw< at >public.gmane.org 2008-12-02T20:22:35 http://comments.gmane.org/gmane.comp.db.sqlite.general/43054 Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word. Is there anyway to get partial matches for beginning or middle parts of a word? e.g. *sion - to match version or *si* to match version Thanks Rael _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Rael Bauer 2008-11-30T03:56:14 http://comments.gmane.org/gmane.comp.db.sqlite.general/43053 Hi all, while doing a stress test on my embedded server application I'm noting a crash in sqlite3_finalize that I don't understand, and I'm wondering if I'm doing the right error handling. Background: In order to encapsulate writings to the tables I'm using BEGIN EXCLUSIVE TRANSACTION to block all other threads from reading and writing. Of course this requires a timeout and busy handling when trying to acquire the exclusive lock. So the code looks like: --------------------------------------- /** Obtain exclusive lock **/ n = 0; do { rc = sqlite3_prepare_v2(db, "BEGIN EXCLUSIVE TRANSACTION;", -1, &stmt, 0); if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) ) { n++; Sleep(SQLTM_TIME); } }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED))); if( n == SQLTM_COUNT ) return(TIMEOUT_ERROR); /** --> Timeout during prepare, never happends so far **/ n = 0; do { rc = sqlite3_step(stmt); if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) ) { Sleep(SQLTM_TIME); n++; } }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED))); /** we are done **/ sqlite3_finalize(stmt); /** <<- crash if timeout occurs **/ if( n == SQLTM_COUNT ) return(TIMEOUT_ERROR); ------------------------ The problem is that it crashes in the sqlite3_finalize function in case the sqlite3_step doesn't actually did something and the timeout condition becames true. My feeling is that it occurs only when trying to get the exclusive transaction; A simple "SELECT *" that runs into a timeout doesn't crash when using sqlite3_finalize. Before starting to deeper debugging this, I would like to ask if there is an advice or known issue concerning the sqlite3_finalize when a step was not successful due to busy or locking state. It is not allowed to call this function in this case ? But then: How to free the statement, succesfully created by sqlite3_prepare_v2 ? I'm using sqlite 3.6.6.2, compiled using MSVS 2008, and of course threadsafe. I'm not using any timeout callback or timeout value settings since I'm handling this by my selve like in the code above. Thank you in advance. Marcus _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Marcus Grimm 2008-12-03T10:15:41 http://comments.gmane.org/gmane.comp.db.sqlite.general/43051 Hi, SELECT COUNT(*) FROM sometable; Takes 10 seconds on my testcase (340.000 rows, Database on CF, dead slow CPU). Is there a quicker way? Does SQLLite maybe store the total number of records somewhere else? The table only ever grows, there are now DELETEs on it ever, apart from complete truncations now and then. Can the number of rows be estimated from a total table size? Ciao, MM _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Marian Aldenhoevel 2008-12-03T08:41:03 http://comments.gmane.org/gmane.comp.db.sqlite.general/43044 I need a command-line script running on Mac OS 10.5 to rebuild sqlite 3 database files with a page_size of 4096 bytes. The first line of my script dumps the database to a text file, then next line should read it create a new one. Since the default page size is 1024 bytes, documentation says that I need to change it with a PRAGMA before creating the database. So I do this: sqlite3 newDatabase.sqlite 'PRAGMA page_size=4096; .read dump.txt' Result: SQL error: near ".": syntax error If I eliminate either the PRAGMA or the .read statement, there is no error. But I need them both. What can I do? Thank you, Jerry Krinock _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Jerry Krinock 2008-12-03T01:48:48 http://comments.gmane.org/gmane.comp.db.sqlite.general/43039 I would like to do a date search to find records that are between two dates. The dates are stored in the table in the format DD-MM-YYYY but I can change that to another format but I prefer something readable rather than an int or double. Could someone point me to some docs that could help me write the SQL and figure out the best format I should store my dates in? Thanks Greg _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Greg Robertson 2008-12-02T23:26:21 http://comments.gmane.org/gmane.comp.db.sqlite.general/43030 I note recently that the SQLite version has gone from a 3 point number (i.e. 3.6.2) to a 4 point number (i.e. 3.6.6.2). Should I read any significance into this change? Is there going to be two strands to development/release of SQLite or will the current practice of the 'latest is the best' still remain true? Regards 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. ******************************************************************** _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Brandon, Nicholas (UK 2008-12-02T16:55:02 http://comments.gmane.org/gmane.comp.db.sqlite.general/43026 Hi I have continious issues with subquery performance when subqueries are used for joins. It crops up all the time my daily work. If you create a derived table using a subquery and use it in a join SQLite performance is abysmal. However if you make a temp table from said subquery and index this temp table on the join keys, it goes at incredible speed. Examples include a query which takes over 2 hours and doesnt complete as I killed it, to running in under 10 seconds if use the temp table pattern. This pattern of the temp table has to be repeated for almost any data analysis I do as SQLite subquery performance with joins is so bad. To recreate the problem simple create two subqueries which produce say 100 000 records each with composite integer keys and join them. e.g Table1 (Key1, Key2, Key3, Value) Table2 (Key1, Key2, Key3, Value) select * from (select Key1, Key2, sum(Value) as Value) from Table1 group by Key1, Key2) t1 join (select Key1, Key2, sum(Value) as Value) from Table2 group by Key1, Key2) t2 on (t1.Key1 = t2.Key1 and t2.Key2 = t2.Key2) Make sure T1 and Most esp T2 have large volumes of records to highlight the problem, eg. 100 000 each does the job. >2 hours versus 10 seconds on my hardware. Can SQLite be altered to automatically create an index on subqueries used as joins or lookups for the key fields used in the join or lookup. This would, in my experience and opinion make SQLite so much more effective. The cost in time of creating said indices is usually less 1 second on my hardware and examples and saves hours! Thanks, _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Da Martian 2008-12-02T15:43:09 http://comments.gmane.org/gmane.comp.db.sqlite.general/43023 I am trying to call a C function in a sqlite 2 trigger. Here is the code ''''''''''''''''''''''''''''''''''''''' #include <stdio.h> #include <sqlite.h> #include <assert.h> void capitalize_alternate(sqlite_func *context, int argc, const char **argv) { int i; static char str[80]; for (i=0; i<strlen(argv[0]); i++) { if (i%2 == 0) str[i] = toupper(argv[0][i]); else str[i] = tolower(argv[0][i]); } str[i] = '\0'; sqlite_set_result_string(context, str, -1); } int main() { char *errmsg; char **result; char str[80]; int ret, rows, cols, i, j; sqlite *db = sqlite_open("/home/test", 0777, &errmsg); if (db == 0) { fprintf(stderr, "Could not open database: %s\n", errmsg); sqlite_freemem(errmsg); exit(1); } sqlite_create_function(db, "altcaps", 1, capitalize_alternate, NULL); ret = sqlite_exec(db, " CREATE TRIGGER example \n" " AFTER INSERT ON contacts \n" " BEGIN \n" " SELECT altcaps('this is a test'); \n" " END", NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "SQL Error: %s\n", errmsg); } sqlite_close(db); return 0; } ''''''''''''''''''''''''''''''''''''''''''''''''''''''''' when I complile the progrm with gcc gcc -Wall firsttprog.c -o firstprog -lsqlite ./firstprog It compiles without error and it creates the trigger "example" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sqlite> select * from sqlite_master; table|contacts|contacts|3|create table contacts (int a) table|requests|requests|4|create table requests(a int) trigger|example|contacts|0|CREATE TRIGGER example AFTER INSERT ON contacts BEGIN SELECT altcaps('this is a test'); END sqlite>insert into contacts values(2); SQL error: no such function: altcaps sqlite> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Here it says no such function ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Could anyone help me with that please. Regards, Nick _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Nadeem Iftikhar 2008-12-02T11:25:12 http://comments.gmane.org/gmane.comp.db.sqlite.general/43021 Anyone know if there's a way to get the actual query that results from calling sqlite3_bind*? I just want to be able to log it after the parameters have been evaluated. Thanks! schleg 2008-12-02T09:01:10 http://comments.gmane.org/gmane.comp.db.sqlite.general/43018 Good evening list, I have been profiling the performance of SQLite version 3.6.1 against my current custom (hacktastic) runtime database solution (which I am hoping to replace with SQLite) and I just got a nasty and unexpected result: SQLite is a lot slower! I am running SQLite completely in memory during profiling using an in memory database (:memory:) and I am setting temp_store=MEMORY and journal_mode=MEMORY. I have ten tests statements that select data from an database that I load completely into memory from file before each test begins. I iterate over the results summing the values of a column (usually max) and record the duration over ten iterations to get a rough mean, high and low time count: 1. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" 2. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.item_code > '100')" 3. "SELECT * FROM test_item WHERE (test_item.item_code > '100')" 4. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.item_code > '100') AND (test_item.max > '50') AND (test_item.initial > '30')" 5. "SELECT max FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code"; 6. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code ORDER BY max" 7. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.max > '50') ORDER BY max" 8. "SELECT max FROM test_item" 9. "SELECT * FROM test_item" SQLite: in memory. Test 1 Mean 288.470825, Low 286.080383, High 308.041931. (milliseconds) Test 2 Mean 270.140808, Low 267.758209, High 289.795166. (milliseconds) Test 3 Mean 68.888512, Low 66.573952, High 88.525116. (milliseconds) Test 4 Mean 255.051758, Low 251.905319, High 273.752533. (milliseconds) Test 5 Mean 103.347633, Low 101.023598, High 123.561203. (milliseconds) Test 6 Mean 2050.301025, Low 2047.153442, High 2069.840088. (milliseconds) Test 7 Mean 1927.293213, Low 1923.600952, High 1944.863159. (milliseconds) Test 8 Mean 16.426598, Low 13.990897, High 37.434727. (milliseconds) Test 9 Mean 68.630898, Low 66.280098, High 88.732208. (milliseconds) Custom DB: in memory. Test 1 (milliseconds) mean,low,high, 10.120694,9.463699,10.540760 Test 2 (milliseconds) mean,low,high, 10.226122,9.593320,10.600039 Test 3 (milliseconds) mean,low,high, 9.077908,8.492440,9.602920 Test 4 (milliseconds) mean,low,high, 10.259830,9.591120,10.668540 Test 5 (milliseconds) mean,low,high, 3.890896,3.626060,4.679040 Test 6 (milliseconds) mean,low,high, 10.156658,9.529779,10.696919 Test 7 (milliseconds) mean,low,high, 10.371894,9.598040,11.083039 Test 8 (milliseconds) mean,low,high, 1.155094,1.010600,2.416280 Test 9 (milliseconds) mean,low,high, 1.120254,1.002940,2.032100 As you can see the performance difference is significant, profiling indicates that 75%-80% of the test execution for SQLite is being spent inside sqlite3VdbeExec. What I'd like to know is if that is normal and if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? So far I have found force inlining sqlite3VdbeSerialTypeLen helped the performance a bit. I am not using the amalgamation version of the source as I have our my VFS implementations for two of the platforms I work with based on the original win_os.c VFS and the amalgamation does not provide the necessary header files (os_common.h and sqliteInt.h) to make VFS integration possible. Other than by inserting the source for my VFS implementations directly into the amalgamation source, which I'd rather not do as it would make upgrading to new SQLite versions much more complex. I love SQLite's feature set, flexibility, tools and syntax compared to my current solution but I need SQLite to be at least as fast as my current solution to make it worth the switch. Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Brown, Daniel 2008-12-02T05:19:19 http://comments.gmane.org/gmane.comp.db.sqlite.general/43014 what's the general rule for deciding when to put multiple tables within a single sqlite db file? I think the answer is something like you put tables together in one database file if they refer to different aspects of the same data element and you put them in separate database files if there's no connection except the databases are used in the same application. For example, in my case, I have one table which contains the raw original data, a thoroughly cooked form of the original data, and a series of data elements which are used for searching and display. The related table contains information derived from postprocessing and will also be used for searching and graphing. The second table's information could be regenerated anytime at a cost of running through every record in the database and recalculating it. As a result of this association, I figure it's appropriate to place both records in the same database file. The third table tracks data from another part of the process and has no connection to the first two tables except that it is used as part of the postprocessing calculations that feed the second table described above. I figure the third table should go in its own database. For what it's worth, record counts could easily cross 100,000 for each one of the tables. Hope it's not time for mysql :-) Thanks for a feedback. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Eric S. Johansson 2008-12-02T03:43:41 http://comments.gmane.org/gmane.comp.db.sqlite.general/42999 Hello all, I am looking for a way to completely turn off the creation of journal files. Any help is much appreciated. Thanks. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Stephen Abbamonte 2008-12-01T15:46:21 http://comments.gmane.org/gmane.comp.db.sqlite.general/42997 Hello! Is it possible? Best regards, Alexey. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Alexey Pechnikov 2008-12-01T14:56:26 http://comments.gmane.org/gmane.comp.db.sqlite.general/42992 To the best of my findings, it seems to me that one needs to write a mini SQL parser in order to read the 'sql' field from sqlite_master, in order to discover the fields in an Sqlite table. Is this really a necessary design? Would it not be better if sqlite3_table_column_metadata had a mode that could enumerate the columns in a table? Or am I missing something obvious? _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Ben Harper 2008-12-01T13:02:31 http://comments.gmane.org/gmane.comp.db.sqlite.general/42991 Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word.   Is there anyway to get partial matches for beginning or middle parts of a word?   e.g. *sion - to match version or *si* to match version   Thanks Rael _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Rael Bauer 2008-12-01T11:48:01 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general