[sqlite] Announcing SQLite3/SML binding v0.1
I've finished writing a binding for SQLite3 to SML. It can be found at http://terpstra.ca/sqlite3-sml/sqlite3-sml-0.1.tar.gz svn://mlton.org/mltonlib/trunk/ca/terpstra/sqlite3-sml It covers the entire non-experimental SQLite3 interface and I find it convenient to use. I've put the glue code (about 90k) into the public domain to match SQLite3. The included README, sql.sig, and demo.sml roughly document the API. I hope this is enough to get people started. The only caveat is that there is a bug in the current SQLite3 making custom authorizers unreliable. Either don't use this feature or help me campaign to get the bug fixed. :-) Please let me know if you encounter any bugs specific to the binding. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] retrieval speedup help requested
On 2/17/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: Where does one get "analyze"? man, its pretty much a standard SQL command (at least all the dbs I've used thus far). Check out http://www.sqlite.org/lang.html. Most questions answered. -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Saturday, February 17, 2007 5:25 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested Try running: analyze city_loc after adding the index. RBS -Original Message- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Disk corruption?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Anderson, James H (IT) wrote: > Is this likely to just be a hardware error. > > database disk image is malformed(1) at dbdimp.c line 398 It could also not be a SQLite file. There is a list of how to corrupt your database files in section 6 at http://www.sqlite.org/lockingv3.html Or in slideware at http://www.sqlite.org/php2004/page-031.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF16zdmOOfHg372QQRAtYnAKCOvH50YiYcjMXJfAsugh/0w3VYdACdEyUn C+Qrz8Mpmn3/p72jw90Ue7U= =ykUN -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieval speedup help requested
http://www.sqlite.org/lang_analyze.html RBS -Original Message- From: Anderson, James H (IT) [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 01:15 To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested Where does one get "analyze"? -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Saturday, February 17, 2007 5:25 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested Try running: analyze city_loc after adding the index. RBS -Original Message- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieval speedup help requested
Where does one get "analyze"? -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Saturday, February 17, 2007 5:25 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] retrieval speedup help requested Try running: analyze city_loc after adding the index. RBS -Original Message- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Disk corruption?
Is this likely to just be a hardware error. database disk image is malformed(1) at dbdimp.c line 398 jim NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
[sqlite] Bug: sqlite3_finalize in authorizer breaks step of another query
Compile the attached C program and run it in a directory with the attached test.db. kiwiw:~/sqlite3/x terpstra$ ls -l bug.c test.db -rw-r--r--1 terpstra terpstra 976 Feb 18 00:09 bug.c -rw-r--r--1 terpstra terpstra 6144 Feb 18 00:09 test.db kiwiw:~/sqlite3/x terpstra$ gcc -Wall -O0 -g bug.c -o bug -lsqlite3 kiwiw:~/sqlite3/x terpstra$ ./bug Failing step: 17 - library routine called out of sequence Invoking the finalizer for query 1 inside the authorization function leads the step of query 2 failing. As SQLite3 is supposed to be re- entrant, recursively altering q1 should not affect q2. I found this bug when running an SML program using the SQLite3 binding I'm working on. In that binding, queries are garbage collected. During the authorizer callback into SML, the runtime decided to recover memory. This led to finalizing a no-longer-used query. In case this mailing list drops my attachments again, find them also here: http://terpstra.ca/sqlite3/bug.c http://terpstra.ca/sqlite3/test.db - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] retrieval speedup help requested
Try running: analyze city_loc after adding the index. RBS -Original Message- From: Tom Shaw [mailto:[EMAIL PROTECTED] Sent: 17 February 2007 22:16 To: sqlite-users@sqlite.org Subject: [sqlite] retrieval speedup help requested In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] retrieval speedup help requested
In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do you combine two SQLite databases?
On Sat, 17 Feb 2007 15:27:25 -0500, you wrote: >Hello, > >I have two SQLite database files, stuff1.db and stuff2.db. Each has three >tables within. I want to combine the two so I have one database file, >stuff.db, with 6 tables. How could I combine the databases? I am aware of >the "attach" command, but this just seemed to create a file with two >sub-databases, each of which has its own tables. (Maybe I used it wrong.) Is >there a specific solution? With the command line program: sqlite3 database1 .dump >fileall.sql sqlite3 database2 .dump >>fileall.sql sqlite3 database3 http://www.sqlite.org/sqlite.html >Thanks! Hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How do you combine two SQLite databases?
Hello, I have two SQLite database files, stuff1.db and stuff2.db. Each has three tables within. I want to combine the two so I have one database file, stuff.db, with 6 tables. How could I combine the databases? I am aware of the "attach" command, but this just seemed to create a file with two sub-databases, each of which has its own tables. (Maybe I used it wrong.) Is there a specific solution? Thanks!
Re: [sqlite] [patch] cleanup cross-compiling logic
On Friday 16 February 2007, Joe Wilson wrote: > The sqlite autoconf stuff has been neglected for a while... ive merged my changes to CVS and i'll take a look at the open issues -mike pgpUTe4GZT41c.pgp Description: PGP signature
Re: [sqlite] [patch] cleanup cross-compiling logic
On Friday 16 February 2007, Joe Wilson wrote: > The sqlite autoconf stuff has been neglected for a while... > > http://marc.10east.com/?l=sqlite-users=116760371614235=2 i dont have a problem tackling these issues if my fixes would get merged ... i know autotools is a huge headache for a lot of people/projects, so i dont mind helping out > Consider making a ticket for this issue and mentioning it in this autoconf > meta ticket: > > http://www.sqlite.org/cvstrac/tktview?tn=2133 sure ... looks like my readline patch addresses #1966 :) -mike pgpnMM2TgjQGn.pgp Description: PGP signature
Re: [sqlite] Question regarding REPLACE INTO and last row ID
On Sat, 2007-02-17 at 00:38 -0800, Tom Olson wrote: > Hello, > > If a REPLACE INTO statement is executed and the there is an existing row > that is replaced will sqlite3_last_inserted_rowid return the rowid of the > record that was changed? REPLACE INTO doesn't change records. It replaces them. With new records that have new rowids. So the answer is yes, but the returned rowid will be that of the new record, not the old. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Question regarding REPLACE INTO and last row ID
Hello, If a REPLACE INTO statement is executed and the there is an existing row that is replaced will sqlite3_last_inserted_rowid return the rowid of the record that was changed? -- View this message in context: http://www.nabble.com/Question-regarding-REPLACE-INTO-and-last-row-ID-tf3243965.html#a9017825 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
