RE: [sqlite] How to retrieve names of all the tables in db

select name from sqlite_master where type='table'; There are two sources of info about the database: the sqlite_master table, and the schema pragmas: http://www.sqlite.org/pragma.html#schema --Ned. http://nedbatchelder.com -Original Message- From: Ritesh Kapoor [mailto:[EMAIL

RE: [sqlite] Concurrency handling question

You can't simply loop on retrying the last sqlite3_* call. You need to rollback one of the transactions and retry the entire transaction. Your two threads are deadlocked because (roughly) each has acquired a lock that the other needs. One needs to release the locks it holds. Rolling back is

RE: [sqlite] Question about automatic schema creation from custom data-strucutre for persistence storage

I my experience, the best approach is to create a description of your data in a form that is good for being parsed and feeding into a code generator. C structures are not good for this, they are good for being compiled into executable code. I would create a data description, and use it to

[sqlite] Checkins 2694 and 2697 (order of columns in primary key)?

I saw checkin 2694 ("The table_info pragma now gives the order of columns in the primary key"), and rejoiced. I currently have to parse the sql from sqlite_master to dig up this information myself.Then came checkin 2697 ("Undo check-in [2694]"). What happened? --Ned.

RE: [sqlite] CROSS keyword disables certain join optimizations

ge- From: Mrs. Brisby [mailto:[EMAIL PROTECTED] Sent: Sunday, 11 September, 2005 10:07 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] CROSS keyword disables certain join optimizations On Sat, 2005-09-10 at 21:38 -0400, Ned Batchelder wrote: > Rather than overload an existing SQL k

RE: [sqlite] CROSS keyword disables certain join optimizations

Rather than overload an existing SQL keyword, would it be possible to provide pragmas to control the optimizer? Assigning meanings to particular combinations of SQL queries won't scale as the number of optimizer controls grows. For example, some databases use specially-formatted comments within

RE: [sqlite] SUM and NULL values

I can't follow this thread (NULLs make my head hurt), but it looks like Dr. Hipp has already taken action. Yesterday he made two changes to the source: SUM returns NULL when it has no inputs: http://www.sqlite.org/cvstrac/chngview?cn=2678 A SUM() of all NULLs returns NULL. A SUM() of nothing

RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

Perhaps you could provide the exact SQL that crashed. The code seems to be very well tested automatically, so it is very unlikely that all ANALYZE executions fail. --Ned. http://nedbatchelder.com -Original Message- From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] Sent: Monday, 05

RE: [sqlite] How to retrieve sqlite version

SQLite version 3.2.5 Enter ".help" for instructions sqlite> select sqlite_version(*); sqlite_version(*) - 3.2.5 --Ned. http://nedbatchelder.com -Original Message- From: Dinsmore, Jeff [mailto:[EMAIL PROTECTED] Sent: Thursday, 01 September, 2005 1:56 PM To:

RE: [sqlite] Unlucky number for the ROUND function

or a hair more. An unlucky value indeed! You flipped a quarter, and it landed on the edge. --Ned. http://nedbatchelder.com -Original Message- From: Ned Batchelder [mailto:[EMAIL PROTECTED] Sent: Tuesday, 30 August, 2005 3:04 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Unlucky number

RE: [sqlite] Unlucky number for the ROUND function

I reproduced it here as well. As near as I can tell, this line in printf.c (line 445): while( realvalue>10.0 && exp<=350 ){ realvalue *= 0.1; exp++; } should be: while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; } The comment at line 440 says it's trying to

RE: [sqlite] problems with 3.2.5

For problem 1: the order of records from a SELECT is never promised to be any particular order unless you specify one with ORDER BY. All relational databases behave this way. Leaving the order up to the database allows them to be returned in whatever order is the fastest. It isn't surprising

RE: [sqlite] RFC Beginning digit in column name

Dr. Hipp isn't showing his hand here, but the timeline indicates that he added support for MySQL-style backquote quoting on Saturday: http://www.sqlite.org/cvstrac/chngview?cn=2591 --Ned. http://nedbatchelder.com -Original Message- From: Marcus Welz [mailto:[EMAIL PROTECTED] Sent:

[sqlite] What is SSE?

Every so often I check the CVS timeline (http://www.sqlite.org/cvstrac/timeline) to get a preview of the changes being checked in. It helps me know what to expect in upcoming releases. Lately Dan has been checking in stuff for "SSE". Do you mind if I ask: what is SSE? --Ned.

RE: [sqlite] idea: sqlite3_begin() and sqlite3_end() ?

I went through this same issue in my application, and realized that rollbacks throw off the whole scheme. This proposal doesn't account for rollbacks: how would they work? If only the outermost "transaction" would truly perform a rollback, then what would an inner one do? Consider this

RE: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround

The problem with queuing all writes is that you're playing fast and loose with the isolation of the transactions. Imagine two threads (A and B). Each reads the same value, increments it, and then writes it (Ar Aw, and Br Bw). If the operations interleave properly (Ar Aw Br Bw), the final values

RE: [sqlite] BLOB versus table storage

That's not "good database design", it's relational dogma. Good database design involves understand what data needs to be stored and *how it's going to be accessed*, which John hasn't told us in detail. If you don't need to access individual point relationally, and only are going to process

RE: [sqlite] sqlite search by "DATE" range ?

Another possibility is to store dates as strings in ISO8601 format: '20041220' '20050114' The conversion is simple (doesn't need epoch functions), and the strings compare the same as dates. It doesn't give you a way to compute the difference between two dates, but does let you select a range.

RE: [sqlite] Can you get a description of a table when the sql results are empty?

Look into the pragmas for querying the database schema: http://www.sqlite.org/pragma.html In particular, you want "pragma table_info(tablename)" Your code is trying to determine which column is the primary key. That information is in the results of table_info (though not the ordering of the

RE: [sqlite] Determining the primary key of a table?

: Re: [sqlite] Determining the primary key of a table? On Sun, 23 Jan 2005 13:18:27 -0500, Ned Batchelder <[EMAIL PROTECTED]> wrote: > One more twist I just discovered: > > If the primary key is a single column that auto-increments, there is no > information in the pragm

RE: [sqlite] Determining the primary key of a table?

c int 0 0 sqlite> pragma index_list(foo); sqlite> Maybe parsing the SQL from sqlite_master is the way to go after all.. :-( --Ned. http://nedbatchelder.com -Original Message----- From: Ned Batchelder [mailto:[EMAIL PROTECTED]

[sqlite] Determining the primary key of a table?

I need to examine the schema of a SQLite database programmatically. I've managed to find everything I need in the various pragmas for querying the schema, except: the order of the columns in the primary key. pragma table_info tells me which columns are in the primary key, but not their order

RE: [sqlite] Good db XML datastructure?

I touched on a similar topic in my blog: http://www.nedbatchelder.com/blog/200411.html#e20041117T084310 The comments there got distracted onto the question of how to use XML data in a relational database, and pointed off to http://www.sqlxml.org/ and http://www.sqlx.org/ There was a suggestion

RE: [sqlite] new Ticket 949: add user-level test for file validity

I have to say, using the sqlite3 api, it all made perfect sense to me, except that these very functions were missing. The silent create-if-needed behavior of Open() seems counter-intuitive to me. I would think that many consumers would have to do what I did: examine the file system themselves to

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:

RE: [sqlite] How to compile without TCL on Vs.Net

You can quiet those warnings in the project properties. In the project's property pages, under C/C++ - Advanced, there's a Disable Specific Warnings field. I put in 4018;4090;4101;4133;4244;4267;4311;4312 and all those warnings are silenced. --Ned. http://nedbatchelder.com -Original

RE: [sqlite] Concerns about checkin 1879

Because thread A is a low-priority background task, and I don't mind if it has to start all over again, while thread B is a high-priority UI thread, and I don't want it to wait. The responsiveness of the system depends directly on how quickly B can get its work done. Thread A will only affect

[sqlite] Concerns about checkin 1879

I'm confused about checkin 1879: "Do not invoke the busy callback when trying to promote a lock from SHARED to RESERVED. This avoids a deadlock." Consider two threads that are deadlocking. Thread A has made changes, and is preparing to commit, so promotes its lock from reserved to pending.

RE: [sqlite] 3.0 ship date?

: Wednesday, July 21, 2004 4:29 PM To: Ned Batchelder Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] 3.0 ship date? Ned Batchelder wrote: > I'm very pleased to see the 3.0 code stream, it looks like a lot of > good changes. I'd really like to use it. Is there a planned date for > it lea

[sqlite] 3.0 ship date?

I'm very pleased to see the 3.0 code stream, it looks like a lot of good changes. I'd really like to use it. Is there a planned date for it leaving beta and becoming official, as in "OK for production use"? --Ned. http://nedbatchelder.com