[sqlite] sqlite3_trace args?
Hello, I am trying debugging my application ( Sqlite reports no errors after a CREATE query, but the table does not exist ). I'd like to use sqlite3_trace() function, but the documentation does not have the expected args, neither does the mail archives. Can someone explain to me briefly what the xTrace() args are at runtime, and the args for sqlite3_trace() as well? Thanks, Kervin
Re: [sqlite] what is difference with PK and Unique index
You can only have one primary key and the primary key cannot contain a NULL value. Also, INTEGER PRIMARY KEY has special properties but INTEGER UNIQUE does not. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] what is difference with PK and Unique index
as title!
[sqlite] Did sqlite3_free replace sqlite_freemem
Can someone tell me if the function sqlite3_free in sqllite version 3 replaced sqlite_freemem function in sqllite version 2? Thanks Steve
[sqlite] SQLite mime type(s)
What is preferred (or better: official) mime type for sqlite3 format? And for sqlite2? Is this ok: application/x-sqlite3 application/x-sqlite2 ? -- regards / pozdrawiam, Jaroslaw Staniek / OpenOffice Polska Kexi project: http://koffice.org/kexi/ http://www.kexi-project.org/ Qt-KDE Wrapper project: http://iidea.pl/~js/qkw/
Re: [sqlite] Get a table's primary key?
Tito Ciuro wrote: 3) Then I parse the CREATE TABLE... statement and separate columns from datatypes. You can look for INTEGER PRIMARY KEY. Would that work for you? In practice, how safe is it to parse the CREATE SQL? Unless, of course, you have a full SQL parser at your disposal.
Re: [sqlite] Get a table's primary key?
Hi Kirk, On Oct 5, 2004, at 21:10, Kirk Haines wrote: But is there a way to get the name of that field? 1) I obtain the SQL statement that generated the table in the first place with something like this: SELECT sql FROM sqlite_master WHERE tbl_name = 'test'; 2) The above statement returns something like: CREATE TABLE test(ROWID INTEGER PRIMARY KEY,testString QLString,testNumber QLNumber,testBoolean QLBoolean,testDateTime QLDateTime,testContainer QLContainer) 3) Then I parse the CREATE TABLE... statement and separate columns from datatypes. You can look for INTEGER PRIMARY KEY. Would that work for you? -- Tito
Re: [sqlite] Get a table's primary key?
On Tue, 05 Oct 2004 12:16:15 -0700, Will Leshner <[EMAIL PROTECTED]> wrote: > Kirk Haines wrote: > > You can use #PRAGMA table_info() to find out information about the columns in a > table, including which columns are primary keys. But you can have more than one > primary key column and they don't have to be INTEGER columns. You'll have to do a > bit more work to figure out which column maps to the 'rowid' column, I think. Thanks. More than one field as a primary key is not a problem. The ORM package handles that, and it doesn't matter whether they are integers for the ORM package, either. It just needs to be able to reasonable guess at the developer's intentions from the table design. Thanks again, Kirk Haines
Re: [sqlite] Get a table's primary key?
Kirk Haines, dando pulos de alegria, escreveu : On Tue, 05 Oct 2004 19:03:33 +0100, Eddy Macnaghten <[EMAIL PROTECTED]> wrote: rowid is alsways an alias for the primary key whether an "integer primary key" field was specified or not But is there a way to get the name of that field? Yes, you can use the "pk" field in "PRAGMA table_info(T)". Regards, ~Nuno Lucas
Re: [sqlite] Get a table's primary key?
Kirk Haines wrote: If, however, there is a way to get the name of the field from the API, then the driver could simply be patched to pass this info, like most of the drivers do, and then most of the time things should just work, without having to take extra steps. You can use #PRAGMA table_info() to find out information about the columns in a table, including which columns are primary keys. But you can have more than one primary key column and they don't have to be INTEGER columns. You'll have to do a bit more work to figure out which column maps to the 'rowid' column, I think.
Re: [sqlite] Degradation of performance in SQLite 3?
Dr. Hipp, > Ulrik Petersen wrote: >> >> has anyone experienced less performance with SQLite 3.0.7 over 2.8.13 on >> the same data? That is what I am experiencing. I'd appreciate help in >> figuring out why and perhaps what I can do about it. >> > > SQLite 3.0 requires less disk I/O at the expense of using more CPU cycles. > So if you have a fast disk and a slow CPU, SQLite 3.0 might well be > slower. On the other hand, a slow disk connected to a fast CPU will > make SQLite 3.0 faster. On my 3-year-old Athlon with an 7200RPM > IDE disk, SQLite 2.8 and 3.0 are about the same speed. But I figured > that CPUs tend to increase in speed more rapidly that disk drives, so > it was best to optimize for a faster CPU. > > Might this explain the result you are seeing? Do you (perhaps) have an > older CPU and/or an exceptionally fast disk drive? Thanks for the information. I have an AMD Athlon 3000+ with a 7200RPM IDE disk running at ATA100, so I am not sure whether that explains it. I may try it on one of my other computers and see what benchmark results I can get. Does SQLite 3 take longer to parse the schema at startup? The benchmark queries I run are run in strict, non-overlapping sequence, with a full sqlite3_open and sqlite3_close in separate processes. Yet even when I concatenate the queries and run them all in one go, SQLite 2.8 is faster than SQLite 3. Thanks in advance. Ulrik
Re: [sqlite] Get a table's primary key?
On Tue, 05 Oct 2004 19:03:33 +0100, Eddy Macnaghten <[EMAIL PROTECTED]> wrote: > > > rowid is alsways an alias for the primary key whether an "integer > primary key" field was specified or not But is there a way to get the name of that field? I have a object/relational modelling library that is database agnostic. It, however, currently has an issue with SQLite because the driver for sqlite doesn't return any info on what the primary key is. If this is a limitation of the API, and there is no way to determine that in a table with fields X, Y, and Z, the one named Z, for instance, is the primary key, then I can work around that with a little documentation and some enforcement of convention (i.e. always name the primary key 'id') or by make sure that SQLite users know that if the primary key on a table is something else, they need to let the ORM package know. If, however, there is a way to get the name of the field from the API, then the driver could simply be patched to pass this info, like most of the drivers do, and then most of the time things should just work, without having to take extra steps. Thanks, Kirk Haines
Re: [sqlite] Get a table's primary key?
rowid is alsways an alias for the primary key whether an "integer primary key" field was specified or not On Tue, 2004-10-05 at 18:57, Kirk Haines wrote: > Does the SQLite API (for either 2.x or 3.x versions of SQLite) offer > any way to determing which field, if any, in a table was created as an > INTEGER PRIMARY KEY? > > I've looked through the API docs, but either I am overlooking it, or > this capability doesn't exist. > > > Thanks, > > Kirk Haines -- Edward A. Macnaghten http://www.edlsystems.com
[sqlite] Get a table's primary key?
Does the SQLite API (for either 2.x or 3.x versions of SQLite) offer any way to determing which field, if any, in a table was created as an INTEGER PRIMARY KEY? I've looked through the API docs, but either I am overlooking it, or this capability doesn't exist. Thanks, Kirk Haines
Re: [sqlite] Degradation of performance in SQLite 3?
Ulrik Petersen wrote: has anyone experienced less performance with SQLite 3.0.7 over 2.8.13 on the same data? That is what I am experiencing. I'd appreciate help in figuring out why and perhaps what I can do about it. SQLite 3.0 requires less disk I/O at the expense of using more CPU cycles. So if you have a fast disk and a slow CPU, SQLite 3.0 might well be slower. On the other hand, a slow disk connected to a fast CPU will make SQLite 3.0 faster. On my 3-year-old Athlon with an 7200RPM IDE disk, SQLite 2.8 and 3.0 are about the same speed. But I figured that CPUs tend to increase in speed more rapidly that disk drives, so it was best to optimize for a faster CPU. Might this explain the result you are seeing? Do you (perhaps) have an older CPU and/or an exceptionally fast disk drive? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] version 3.0.7 giving unresolved external symbol with _sqlite_free_table, _sqlite_open, _sqlite_close, _sqlite_get_table, _sqlite_exec
The prefix for several like-named routines in sqlite 3.0.7 is "sqlite3". For example: sqlite3_open Could it be that somehow you've #included "sqlite.h" instead of "sqlite3.h" in your sources? --Steve On Oct 5, 2004, at 12:45 PM, Steve Frierdich wrote: I made a static library out of the source code for Sqlite for version 3.0.7 and when I used the library in an MFC program I get linker error on unresolved external symbol for the following symbols: _sqlite_close _sqlite_open _sqlite_free_table _sqlite_get_table _sqlite_exec _sqlite_error_string _sqlite_freemem I made the same type of library for the source for Sqlite for version 2.8.15 and used that library in the same MFC program and it worked find. Can anyone tell me why or how to solve the linker error problem for version 3.0.7? Thanks Steve Frierdich 727 455 4668 J
[sqlite] Degradation of performance in SQLite 3?
Hi all, has anyone experienced less performance with SQLite 3.0.7 over 2.8.13 on the same data? That is what I am experiencing. I'd appreciate help in figuring out why and perhaps what I can do about it. I have a linguistic database (it's from my project, Emdros http://emdros.org/) and the relevant parts of the schema look like this: CREATE TABLE clause_monad_ms ( object_id_d INT NOT NULL, mse_first INT NOT NULL, mse_last INT NOT NULL, is_first CHAR(1) NOT NULL DEFAULT 'N', PRIMARY KEY (object_id_d, mse_first) ); CREATE TABLE clause_objects( object_id_d INTEGER PRIMARY KEY, first_monad INT NOT NULL, last_monad INT NOT NULL, mdf_text_type TEXT NOT NULL , mdf_number_within_sentence INT NOT NULL , -- etc etc. plus other data for the clause ); -- similarly for phrase CREATE TABLE word_gut( object_id_d INTEGER PRIMARY KEY, first_monad INT NOT NULL, last_monad INT NOT NULL, mdf_word_number INT NOT NULL , mdf_verbal_tense INT NOT NULL , -- etc etc. plus many other columns containing word-data ); CREATE INDEX clause_mm_monads_i ON clause_monad_ms (mse_first, mse_last); CREATE INDEX clause_mm_o_i ON clause_monad_ms (object_id_d); CREATE INDEX clause_o_fm_i ON clause_objects (first_monad); CREATE INDEX clause_o_lm_i ON clause_objects (last_monad); CREATE INDEX word_g_flm_i ON word_gut (first_monad, last_monad); My application, Emdros, is a query engine for linguistic data. I have a suite of Emdros queries that I run to test the speed of Emdros. Emdros translates these queries into a series of SQL queries. I've ported Emdros over to SQLite 3.0.7, but experienced between 27% and 49% speed degradation over 2.8.13. This is on a 137MB database with around 1.7 million rows in various tables (up to 430,000 rows in one table, namely the word_gut table). I use the sqlite3_prepare/step interface, with sqlite3_column_XXX calls to get the data. Has anyone experienced anything similar? Can anyone suggest ways I could improve the above schema and/or indexes? Thanks in advance. Ulrik Petersen PS: Kudos to Dr. Hipp and all the contributors for making SQLite such a worthwhile and pleasant piece of software to interact with. -- Ulrik Petersen, Denmark Emdros - the text database engine for analyzed or annotated text http://emdros.org
Re: [sqlite] MT app using 2.8.14 - collision on sqlite_close() ?
Thanks for the response, Richard. I took a look through main.c, pager.c and os.c and do not see anything that may have caused such behavior. I have tried repeatedly to reproduce the phenomenon and have not been successful (which is good). Looks like I must have had some other issue around the time that I saw it. John
Re: [sqlite] Schema of an attached database
Wow - that was fast!
Any plans for a 3.0.8?
Eddy
On Tue, 2004-10-05 at 16:44, D. Richard Hipp wrote:
> Eddy Macnaghten wrote:
> > This does not seem to work for 3.0.7
> >
> >
> >>Marc Pitoniak wrote:
> >>
> >>>To get the schema of a table T from A I've tried "PRAGMA table_info('A.T') "
> >>>among other things without any success.
> >>>
> >>
> >>PRAGMA A.table_info(T);
> >
>
> Fixed with check-in [2001]
> http://www.sqlite.org/cvstrac/chngview?cn=2001
--
Edward A. Macnaghten
http://www.edlsystems.com
Re: [sqlite] Schema of an attached database
Eddy Macnaghten wrote:
This does not seem to work for 3.0.7
Marc Pitoniak wrote:
To get the schema of a table T from A I've tried "PRAGMA table_info('A.T') "
among other things without any success.
PRAGMA A.table_info(T);
Fixed with check-in [2001]
http://www.sqlite.org/cvstrac/chngview?cn=2001
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Schema of an attached database
This does not seem to work for 3.0.7
If you have a table named "t" in the main database, and you attach a
database that has a table named t in it as attachdb, then
pragma attachdb.table_info(t);
still seems to give the info for the table t in the main database.
Is this a bug or have I got it wrong?
On Tue, 2004-10-05 at 14:08, D. Richard Hipp wrote:
> Marc Pitoniak wrote:
> >
> > To get the schema of a table T from A I've tried "PRAGMA table_info('A.T') "
> > among other things without any success.
> >
>
> PRAGMA A.table_info(T);
--
Edward A. Macnaghten
http://www.edlsystems.com
Re: [sqlite] Re: FOREIGN:[sqlite] Receiving notification of table updates
On Tue, 5 Oct 2004, Kazuho Oku wrote: >From: "Christian Smith" <[EMAIL PROTECTED]> > >The apache module I am writing is an access controller. It queries the >SQLite database and send different contents to clients depending on their IP >addresses / cookies. So it's a simple table lookup based on a multi-part key (ip,cookie)? > >When the apache module was configured to issue SQLite queries, the >performance decreased for 45% (it took 72.9 seconds, compared to 50.3 secs >when the module was configured not to issue any queries). ># test details are shown below > >> There is going to be some performance penalty, but if it's only 1% of your >> request time, you'll have complicated your design for very little >> performance gain. >(snip) >> AFAIK, flock() is a kernel operation, and seek() and read() should not >> touch the disk if the data blocks required are already cached, and so all >> should be relatively cheap operations (against disk IO and network >> latencies.) > >I also tried using on-memory database of SQLite. When doing so, the >decrease was 28% (64.2 seconds). This means that the other 27% decrease is >caused by I/O operations of SQLite. > >My target is to minimize the performance penalty to less than 10%. So it >seems I have to cache data by myself (to avoid overhead like compilation of >SQL statement, etc.):-( You can precompile the SQL statement and execute it many times, that should give a speed boost. One of the upcoming enhancements (possibly) in SQLite 3 is a persistent page cache between transactions, which should increase performance somewhat when there's been no updates. > > > >Benchmark Information: > >Test Platform: > [snip] > >Total time for handing 5000 HTTP requests * 5 times: > >with SQLite queries: 72.943 seconds >wo. SQLite queries: 50.299 seconds >with SQLite queries to on-memory database: 64.200 seconds > >The test was performed from a different server connected by fast ethernet. >For the test apachebench 1.3d was used. The options supplied to apachebench >were: > > % ab -k -n 5000 -c 20 http://192.168.10.221:82/ > >The content sent was the default root document of apache2. Note, while the figures above indeed do indicate some significant slowdown using SQLite, is the above test really representitive? On the internet, you'll have much higher latencies. Delivering a non-static page, instead of the static root now, will also increase the benchmark time. Factor in those, and you'll come much closer to your target performance limit. Remember, your fastest time is 100 hits/second (or is that 500 hits/second?) For an internet, or even intranet site, that's a lot. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Locking enhancments
Ned Batchelder wrote: I would really like to try the changes, but I'm not set up to build from CVS. Can you make a source code zip file available with the preprocessing and code generation done already? http://www.sqlite.org/sqlite3071.zip -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Locking enhancments
I would really like to try the changes, but I'm not set up to build from CVS. Can you make a source code zip file available with the preprocessing and code generation done already? --Ned. http://nedbatchelder.com -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Monday, October 04, 2004 11:03 PM To: [EMAIL PROTECTED] Subject: [sqlite] Locking enhancments The code in CVS contains enhancements to transactions to help people work around the so-called "deadlock" issues they have been having. Those who are able are encouraged to try out the latest code in CVS. If no problems are reported within the next week, I will release version 3.0.8 that contains the new features. Note that technically, SQLite has never been able to deadlock. It is possible that two or more processes or threads could be contending for the same lock. But eventually, all but one of the processes would time out and allow one process to continue. SQLite has never used blocking locks so a true deadlock was never possible. Though the locking contention could result it signficant delays if not handled properly. SQLite has always allowed you to do transactions as follows: BEGIN TRANSACTION; -- queries and updates here COMMIT TRANSACTION; (Aside: the TRANSACTION keyword is optional and is usually omitted. I've included it here for completeness.) Beginning with code checked in earlier today, it is now possible to specify three different types of transactions: BEGIN DEFERRED TRANSACTION; BEGIN IMMEDIATE TRANSACTION; BEGIN EXCLUSIVE TRANSACTION; (Again: the TRANSACTION keyword is optional.) The default behavior is a deferred transaction. A deferred transaction works like version 3.0 has always worked. With a deferred transaction, no locks are acquired on any database until there is I/O against that database. The first time a database is read, a SHARED lock is acquired. The first time a database is written, a RESERVED lock is acquired. Deferred transactions give more concurrency, but they were also create the greatest opportunity for lock contention. An immediate transaction creates a RESERVED lock on all attached databases as soon as the BEGIN statement executes. Other processes and/or threads can continue to read the databases but no other process will be able to write to a database until the transaction ends. Immediate transactions provide less concurrency but are also less likely to cause lock contention. An exclusive transaction creates an EXCLUSIVE lock on all attached databases as soon as the BEGIN command executes. This is how version 2.8 works. This method gives you the least amount of concurrency but eliminates the problem of lock contention. Again, the default behavior continues to be DEFERRED and there is (currently) no way to change the default behavior. So if you want to use one of the new locking modes, you have to specify the mode you want in the BEGIN statement. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Schema of an attached database
Thank you very much.
-Marc
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 05, 2004 8:09 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Schema of an attached database
Marc Pitoniak wrote:
>
> To get the schema of a table T from A I've tried "PRAGMA table_info('A.T')
"
> among other things without any success.
>
PRAGMA A.table_info(T);
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Schema of an attached database
Marc Pitoniak wrote:
To get the schema of a table T from A I've tried "PRAGMA table_info('A.T') "
among other things without any success.
PRAGMA A.table_info(T);
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
