http://permalink.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://permalink.gmane.org/gmane.comp.db.sqlite.general/43081 On Dec 3, 2008, at 7:09 PM, Karl Thiessen wrote: I haven't used it in years. You should feel free to take over maintenance. I can provide you with a domain name and website preloaded with configuration management system (http://www.fossil-scm.org/ ) repository in which to keep the sources, if you like. tws.tcl.tk, perhaps? D. Richard Hipp drh-X1OJI8nnyKUAvxtiuMwx3w< at >public.gmane.org _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp 2008-12-04T00:25:56 http://permalink.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://permalink.gmane.org/gmane.comp.db.sqlite.general/43079 Hi again, Daniel, So I guess you're still having certain queries that take about 200x longer than with your custom code, right? There's nothing magical about sqlite, so it's not surprizing that code customized for an application can outperform a generalized sql engine, but a factor of 200 does seems pretty large. If it's neither especially proprietary nor private, I'd again urge you to post here your schema (and repost the slow queries). You can dump this easily using the command-line utility program, e.g.: sqlite3 myDatabase.db .output mySchema .schema .quit The schema should then be in the file "mySchema" If the database itself is not too private, you may want to compress it (with something like 7zip) and upload it somewhere on the web and provide a link to it. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Griggs, Donald 2008-12-03T23:50:46 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43078 Igor Tandetnik wrote: Tks. It would help if the doco made that known. Howard Lowndes 2008-12-03T23:34:59 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43077 Be aware that SQLite silently ignores REFERENCES clause (together with on delete, on update parts). You will have to create triggers to enforce these constraints. Still applicable. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Igor Tandetnik 2008-12-03T23:31:25 http://permalink.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 Howard Lowndes 2008-12-03T23:19:59 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43075 try looking at the pragmas page and determine what you can get away with. For me, I relaxed the synchronization requirements and also the locking strictness, and I was able to boost my speeds to 80,000 records per second :) FYI, my records only consist of 6 numbers and a binary. On Wed, Dec 3, 2008 at 12:23 PM, Stephen Woodbridge <woodbri-BcHlzckR+4gE8Gx1Vj3tGg< at >public.gmane.org _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Julian Bui 2008-12-03T20:29:21 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43074 You should be doing all your inserts inside a transaction. begin; <loop on inserts> commit; -Steve _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Stephen Woodbridge 2008-12-03T20:23:38 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43073 Is there any important reason for counter(X) function to not be included in main sqlite? There is already an implementation of counter function in src\test_func.c and given the usefulness of counter function in analytics, it is a petty to have to write obnoxious queries to workaround the lack of a function like that. Is it possible for a counter function that resets on group boundaries to be implemented in sqlite? It also would help immensely with analytics. lefteris _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Elefterios Stamatogiannakis 2008-12-03T18:06:44 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43072 Unique_User <robert.muench-cVf1oMGW/87cRzAXig1ULA< at >public.gmane.org> wrote: What do you mean, for database A? The connection handle determines which database you are talking to. "Open database file" and "open connection handle" are, basically, one and the same. By closing handleA, you close file A. Since handleB is still open, you can continue to work with database file B using that handle. It's simple, really. When you want to read or write a text file, you call fopen() and get back FILE* handle. Then you call fread() and fwrite() passing this file handle, so they know which file to read or write to. Finally, you close the file with fclose(). You can have multiple files open at the same time, each represented by its own FILE* handle (and, in principle, you can open the same file twice and get two handles to it). Similarly, you open a SQLite database file with sqlite3_open, and get back a handle. You pass this handle to various SQLite functions, so they know which database to work on. When y Igor Tandetnik 2008-12-03T17:10:45 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43071 Are you doing the inserts inside a transaction? _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Gerry Snyder 2008-12-03T15:59:56 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43070 Whereby "objects" you mean "rows," no? You are getting upward of 5500 sustained inserts per second. That sounds pretty good. That said, are you using transactions? See what difference that makes. P Kishor 2008-12-03T15:55:45 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43069 Hi Ken, thanks for the code, but I guess it will still crash: Ken wrote: ok, but this usually works without a BUSY state. so no problem here. This may now generate a BUSY or LOCKED state and fails. Same as in my code except that the loop is outside. if I come here from the step() error, I still have the same problem, and I'm sure it will still crash in sqlite_finalize. Or do you think it is a problem that I call Step multible times in case it returns a busy state ? Maybe the Step() call allready frees the pStmt because it was trying to do an exclusive transaction, maybe a special handling for this SQL statement ? If nobody else has an explanation it seems that I'll have to run it in a debugger to see where it actually crashes inside finalize... ;) Thanks for the reply Marcus Marcus Grimm 2008-12-03T15:53:20 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43068 Hello! В сообщении от Wednesday 03 December 2008 04:48:48 Jerry Krinock написал(а): sqlite> PRAGMA page_size; 1024 sqlite> PRAGMA page_size=4096 ;vacuum; sqlite> PRAGMA page_size; 4096 Best regards, Alexey. _______________________________________________ sqlite-users mailing list sqlite-users< at >sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Alexey Pechnikov 2008-12-03T15:47:58 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43067 Marcus try something like this pseudo code: local_Exec( exec_Str) { *pStmt = NULL; rc = prepare_v2 (exec_Str) if (rc != SQLITE_OK) goto exec_err rc = step ( ); if (rc != SQLITE_OK or SQLITE_DONE ) goto exec_err rc = finalize( ) if rc != SQLITE_OK ) goto exec_err return SQLITE_OK; :exec_err if (pStmt) sqlite_finalize; return rc ; } ============================== code to lock: do { rc = local_exec(db, "BEGIN EXCLUSIVE TRANSACTION;", -1, 0); if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) ) { n++; Sleep(SQLTM_TIME); } }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED))); ============================================================== --- On Wed, 12/3/08, Marcus Grimm <mgrimm-P6H0LW+CJDcuJJzQmaKVtg< at >public.gmane.org> wrote: _______________________________________________ sqlite-users mailing list sqlite-us Ken 2008-12-03T15:31:03 http://permalink.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://permalink.gmane.org/gmane.comp.db.sqlite.general/43065 Hi, Yes, I am aware of that limitation and I am not ever deleting apart from a full truncate. I have yet to test that case, if shut turn out to not work I can live with a DROP TABLE or even delete the database file. It is that kind of project :-). 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-03T15:30:02 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43064 For the second table, the index will contain the BIGINT value and the table rowid, which is almost as big as the actual row, so use of a separate index will literally double storage used for the second table including index. If your BIGINT fields are just record ids, then you are better off using an "integer primary key" for these fields, as this is used as the table rowid, and no separate index will be required. The "integer primary key" type is good for 64 bit ids, so should be adequate. Regards, Christian _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Christian Smith 2008-12-03T15:13:56 http://permalink.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 gen Jos van den Oever 2008-12-03T14:57:32 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43062 And will only work if you never delete any rows from the table. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Brad Stiles 2008-12-03T14:17:05 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43061 Hi, > select max(rowid) from sometable; Looks good and is instantaneous. Thank you very much. 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-03T14:11:40 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general