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

[Ned Batchelder]

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 PROTECTED] 
Sent: Tuesday, 27 December, 2005 4:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to retrieve names of all the tables in db

Hi

Is there any function in the sqlite API which would give me the names of
all the tables in the DB?

Or maybe there is some sql query which does this - please let me know
what is the syntax of this query.

The problem is that I have a DB which has say 3 tables in it -
1. Employee_Personal_Info
2. Employee_Offical_Info
3. Corporate_Info

Is there a way to retrieve the names of the tables assuming that I do
have a connection open with the DB.

Thanks,
Ritesh Kapoor
Senior Software Engineer
Atrenta Pvt. Ltd.

RE: [sqlite] Concurrency handling question

[Ned Batchelder]

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 the
way to do that.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Doug Nebeker [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 30 November, 2005 11:04 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency handling question

I've written a C++ wrapper for SQLite which has been working great.
Lately though I've started getting what might be deadlocks when running
two threads against the same database.  One thread is a reader and the
other is a writer (at the moment, I'm getting 'database locked' errors,
but that's after many hours of failures so not sure where it started).
It's possible to have two or three readers and/or writers, but not more
than that.

Each thread has it's own database handle, however, I keep the database
handle open for many calls (where each 'call' opens a transaction,
executes SQL statements, then closes the transaction).

I've read about concurrency, and it sounds like the best way to work is
just to retry the last call if SQLITE_BUSY is ever returned, but that
doesn't seem to be working for me in this case.  I've stripped my main
processing loop down to make it concise (it is not completely correct
nor will it compile as shown, but it shows the flow).

Can anyone see what I'm doing wrong?  Is the fact that I keep the
database handle open between calls and between retries the problem?  If
the retry loops finally fails, sqlite3_finalize gets called,
sqlite3_close and then sqlite3_open16 to re-initialize the state.

Thanks in advance for any insight.

do
{
long prepareLoopCount = 0;

PrepareLoop:
if(SQLITE_BUSY == (res = sqlite3_prepare16(m_hDB,
nextStatement, -1, , )))
{
if(prepareLoopCount++ < 200)
{
Sleep(300 ms);
goto PrepareLoop;
}
}

if((SQLITE_OK == res) && (NULL != pStmt)) 
{
long stepLoopCount = 0;
StepLoop:
res = sqlite3_step(pStmt);
if(SQLITE_BUSY == res)
{
if(stepLoopCount++ < 200)
{
Sleep(300 ms);
goto StepLoop;
}
}
else do other processing like fetching the
rows

}

if(NULL != pStmt)
{
sqlite3_finalize(pStmt);
pStmt = NULL;
}
}
while(NULL != nextStatement)


To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of Reuters
Ltd.

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

[Ned Batchelder]

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 generate the SQL and the C
structures.  The data description could be whatever you find convenient: I
use XML, but anything will do so long as it is expressive enough for you and
you can parse it.

I use a code generation tool called Cog (http://nedbatchelder.com/code/cog)
to generate the code itself.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Monday, 17 October, 2005 10:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question about automatic schema creation from custom
data-strucutre for persistence storage

I addressed this some time back in a C program which dynamically 
generates XML.  Generating SQL would be an option.

By building a table with a keyname for each data element and a pointer 
to a data item the output structure can be assembled.  As a wise person 
once told me "There is no problem in CS which cannot be solved by yet 
another level of indirection".

In my case I also added a function pointer so that callbacks could be 
used.  With XML the table is actually a transformation of the DTD so 
that the result is guaranteed to be "well formed".  If the table were 
compiled from the data structure then a similar integrity could be assured.

This method would require a few lines of code, but you would only need 
to do it once as opposed to writing code for each data structure.
JS

Dan Kennedy wrote:
> As far as I know C++ has no introspection capability so the answer 
> is probably no. Unless you want to parse your header file yourself,
> or something like that.
>  
> With Java or another high-level programming language that supports 
> introspection it would be possible I think.
> 
> 
> "Rajan, Vivek K" <[EMAIL PROTECTED]> wrote:
> Hello- 
> 
> 
> 
> Has anyone had experience with automatically creating schema from a
> custom data-structure for persistence storage using SQLite? So, in my
> C/C++ program I have the following type of data-structure:
> 
> 
> 
> struct _trace {
> 
> std::string name; 
> 
> std::string prop;
> 
> std:string object; 
> 
> int a; 
> 
> double b; 
> 
> }; 
> 
> 
> 
> std::vector _myPath; 
> 
> 
> Now, let's say I want to write and read _myPath data-structure
> (persistence) in SQLite database. 
> 
> 
> 
> My questions:
> 
> - Is there a mechanism to dynamically create a schema on-the-fly from
> the data-structure? 
> 
> - Essentially a capability to store any custom data-structure into
> SQLite data-base by automatic creation of schema for the data-structure
> 
> 
> 
> Rajan
> 
> 
> 
> 
> 
> 
>   
> -
>  Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

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

[Ned Batchelder]

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.

http://nedbatchelder.com

 

 

RE: [sqlite] CROSS keyword disables certain join optimizations

[Ned Batchelder]

I'm not sure if we agree or disagree.  You say:

> I don't think the number of optimizer controls should grow. 
> It should be kept as close to zero as possible.

But then you say:

> If [the optimizer] can make a mistake, I want a simple
> and _unobtrusive_ way to correct _just_that_mistake_ for
> _just_that_query_.

As the optimizer grows, the number of mistakes it could make will grow. You
seem to want a control for each mistake.  The number of controls will grow.

As a few of us have mentioned, other database engines have used inline
pragmas in the form of specially formatted comments:

select /* sqlite_stupid */ A.parent, A.child from /* sqlite_stupid */
revision_ancestry ...

New keywords will also work.  The comments have the advantage that they are
"portable": you can pass this exact query to any database.  Only SQLite will
interpret the comments.  If SQLite invents a new STUPID keyword (or
SQLITE_STUPID), the query will only be parsable by SQLite.

Granted, using pragma comments in queries is unlikely in a system where
queries are fed to multiple DBMS's, but at least it would be possible.

--Ned.
http://nedbatchelder.com
 

-Original Message-
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 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.

I don't think the number of optimizer controls should grow. It should be
kept as close to zero as possible.

The query optimizer isn't (and shouldn't be) designed to help out people
who don't know SQL, but should be for people who know SQL but don't know
SQLite.

When SQLite does this optimization of table reorganization, it's doing
so because it doesn't have a real optimal way to answer the
query-as-stated but if the query is transformed into something seemingly
(and sometimes algebraically) equivalent, then it does so it can.

Consider a mythical SQL engine that doesn't reorder where terms before
searching an index of the form "a,b".

It can answer queries like:
WHERE a='abc' AND b='def';

but give it:
WHERE b='def' AND a='abc';

and it could take forever. Anyone reasonably aware of the various SQL
engines out there would undoubtedly be aware of engines that DO treat
them as identical, but here is one that isn't.

Given that this is indeed a _query_optimization_ do you really want a
control for this?

While I'm sure many SQL engines don't put this nonsense in the block of
code that looks for query optimizations, I want the query optimizer to
do the best job that it can. If it can make a mistake, I want a simple
and _unobtrusive_ way to correct _just_that_mistake_ for
_just_that_query_.

I think anyone close to the various SQL working groups-that-be should
consider a "STUPID" operator that takes the next term (whatever that
might mean to the SQL engine) and stops any query optimization on that
term. The STUPID keyword would be allowed everywhere. It'll improve the
readability of SQL as it is:
SELECT STUPID A.parent, STUPID A.child STUPID FROM STUPID
revision_ancestry STUPID AS STUPID A ...


I don't like CROSS, but it is the closest thing to what I might find
acceptable.

:)

RE: [sqlite] CROSS keyword disables certain join optimizations

[Ned Batchelder]

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 the SQL
query to control the internals of the system.  It isn't portable, but
neither is this new meaning of the CROSS keyword. 

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Kervin L. Pierre [mailto:[EMAIL PROTECTED] 
Sent: Saturday, 10 September, 2005 9:08 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CROSS keyword disables certain join optimizations

Darren Duncan wrote:
> At 7:25 PM -0400 9/10/05, D. Richard Hipp wrote:
> 
> Well, that's fine as long as CROSS still continues to mean and do what 
> it has always meant, which is that you explicitly want the result set of 

If I understand the issue correctly, it does.

"FROM a, b" is usually equivalent to
"FROM a CROSS JOIN b" in most databases.  With
the new fix, the first form gives you the
optimized query, whilst the second form turns
it off.  But you should get the same results.

...I think :)

Regards,
Kervin

RE: [sqlite] SUM and NULL values

[Ned Batchelder]

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 return 0. A SUM() of a
mixture of NULLs and numbers returns the sum of the numbers:
http://www.sqlite.org/cvstrac/chngview?cn=2677

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Friday, 09 September, 2005 4:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values

Dr. Hipp,

If my opinion is worth anything I'd prefer to stay with the standard, even 
though it might be a pain, etc. I've had too much bad experience with people

writing code that depends on the quirks in other people's code.

The decision is ultimately yours, and thanks for putting so much effort into

it!

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

[Ned Batchelder]

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 September, 2005 5:46 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the
pre-compiled version downloaded from the site, AND a local compilation).
in both
cases, the statement fails with an ACCESS VIOLATION. however, the same
statement, when run from the pre-compiled sqlite3.exe, works. 
further, having succesfully ANALYZED a database with the command-line
program, I can no longer work with the database using the DLL api. 
I think there is something very wrong with the compilation defines of
the DLL. 

DRH: can you pls check and fix ?
ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS,
obviously)

RE: [sqlite] How to retrieve sqlite version

[Ned Batchelder]

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: sqlite-users@sqlite.org
Subject: [sqlite] How to retrieve sqlite version

I can't seem to come up with how to get the version from sqlite.

The frustrating thing is that I've done it before... As I recall, it's a
select, but for the life of me, I can't remember the right syntax.

Anyone have  that info handy?

Thanks,

Jeff Dinsmore
MIS - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592



Ridgeview Medical Center Confidentiality Notice: This email message,
including any attachments, is for the sole use of the intended recipient(s)
and may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply email and destroy all
copies of the original message.

RE: [sqlite] Unlucky number for the ROUND function

[Ned Batchelder]

One more thing: this seems to happen because realvalue is
9.9493, and rounder is 0.050003, and adding them
together produces exactly 10.000!  Other platforms or libraries
may be producing slightly different values which ended up being a hair less
than 10 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 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 make realvalue be less than 10.
Changing the line makes the round example given produce the right answer.

The colon in the wrong answer is because et_getdigit is being passed 10.0 as
the real value, and returing '0'+10 as the digit, which is ':'.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 August, 2005 2:35 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I
get the ":.0" result (less the quotes).  I have tried this on a couple
machines in the office here running similar environments.  

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't
9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9*Rounded Down*
> Round(9.85,1)  -> 9.8*Rounded Down*
> Round(9.5,0)   -> 10 *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Unlucky number for the ROUND function

[Ned Batchelder]

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 make realvalue be less than 10.
Changing the line makes the round example given produce the right answer.

The colon in the wrong answer is because et_getdigit is being passed 10.0 as
the real value, and returing '0'+10 as the digit, which is ':'.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 August, 2005 2:35 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I
get the ":.0" result (less the quotes).  I have tried this on a couple
machines in the office here running similar environments.  

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 30, 2005 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Unlucky number for the ROUND function

On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote:
> According to that, it rounds to the nearest even number.  Shouldn't
9.95
> go to 10 then, and 9.85 go to 9.8?
> 
> After additional testing with SQLite 3.2.2, I have the following
> results:
> 
> Round(9.95,1)  -> 9.9*Rounded Down*
> Round(9.85,1)  -> 9.8*Rounded Down*
> Round(9.5,0)   -> 10 *Rounded Up*
> Round(9.995,2) -> 9.99   *Rounded Down*
> 
> I really see no pattern or sense to the results.
> 

The reason you see no pattern is because you are thinking in
decimal whereas SQLite thinks in binary.

The number 9.95 does not a have finite representation in
binary.  The closest you can get with a 64-bit IEEE float
is:

   9.949289457264239899814128875732421875

So when you type "9.95" into an SQL statement, SQLite really
inserts the number shown above, not 9.95.  And the number 
shown above rounds down.

9.5 does have an exact representation in binary so it rounds
as you would expect.  But neither 9.85 nor 9.995 do - the
binary values chosen to represent them are both just a little
less than their decimal values.  Hence they both round
down.

So I'm not overly worried when I see round(9.95,1) come out
with 9.9.  But I am concerned about the people who are seeing
results like ":.0".  I wish I could reproduce that problem.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] problems with 3.2.5

[Ned Batchelder]

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 that
3.2.5 returns them differently than 3.2.2, considering the breadth of
changes in the query optimizer between the two releases.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: mr sql [mailto:[EMAIL PROTECTED] 
Sent: Monday, 29 August, 2005 8:19 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] problems with 3.2.5

I have encountered some problems when upgrading to 3.2.5 from 3.2.2.  I am
using the sqlite3.dll from a delphi application (windows xp), not the
sqlite3.exe
 
1. Order of returned records in a SELECT sometimes is not the same as 3.2.2,
when statement does not have an "ORDER BY".  As an inmediate solution I
added the "order by" clause in every query, but am wandering whether there
is an alternative.
 
2. Performance (time to retrieve the same set of records, using the same
SELECT statement on both) is slightly slower on 3.2.5 than 3.2.2.  Is there
any parameter that changed or that I need to set on this release?
 
3. When using the ":memory:" database, I attached a physical database and
ran a select statement which joined one table from the ":memory:" database
to two tables from the physical one.  I use WHERE instead of JOINs.  On
3.2.5 the query freezes, on 3.2.2 it works fine.  Is there anything special
I need to setup to work with attached databases on 3.2.5?
 
jp



-
 Start your day with Yahoo! - make it your home page 

RE: [sqlite] RFC Beginning digit in column name

[Ned Batchelder]

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: Wednesday, 10 August, 2005 4:23 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] RFC Beginning digit in column name

Hello there,

I think that adhering to standards is a Very Good Thing(tm). They are
standards for a reason. Deviations can introduce ambiguity, confusion,
complexity, vendor lock-ins and all sorts of other headaches.

That said, however, I believe that "no, because it's not the standard" isn't
exactly what a customer wants to hear. Then again "no" isn't what a customer
wants to ever hear in almost any context, but I digress..

Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
you?" And I think in a fair number of situations, if possible to implement
at a reasonable amount of cost the answer should be "We can, but be warned,
it is NOT the standard."

Some proprietary extensions are rather handy, too, and may want be be
considered. I don't really think "`" and "[]" are useful nor pretty, but I
also don't think that it would be unethical to support it. On the other
hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
query counts how many rows there would have been returned with the LIMIT
clause omitted, great for pagination if you want to display something like
"Rows 1 - 15, 400 total" all while running a complex query (with a whole
slew of JOINS and WHERE conditions) only once.

The short of it all: I don't know much about SQLite's internal architecture,
besides glossing over the web site documentation, but my suggestion is to
add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
should be _disabled_ by default, but when enabled would honor proprietary
extensions such as "[]" and "`" for quotation, digits in column names, and
who knows, maybe even some of the other extensions and vendor specific
functions, etc.

It would allow numerous projects to more easily migrate away from MySQL to
SQLite without needing to clean up all their broken SQL instantly, and it
would allow projects that want to stick with MySQL to also offer a SQLite
version with little effort.

How off the wall am I with this?

-m

-Original Message-
From: Austin Ziegler [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 10, 2005 2:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RFC Beginning digit in column name

On 8/10/05, ender <[EMAIL PROTECTED]> wrote:
> Austin Ziegler wrote:
>>On 8/9/05, ender <[EMAIL PROTECTED]> wrote:
>>> So my simple feature request would be: allow '`' as a quoting symbol -
>>> as mySQL does. Or - what would be as helpful as the other idea - allow
>>> unquoted column names with leading digits - as mySQL does.
>>> Also see ticket # 1337  http://www.sqlite.org/cvstrac/tktview?tn=1337
>> I think that supporting '`' (backquote) would be a bad idea. Then
>> again, MySQL ignores so much of the SQL standard that I think that
>> importing any idea from MySQL is a bad idea.
> What are your reasons for thinking supporting '`' is a bad idea?

1. It's not part of the standard.
2. It's not part of the standard.
3. MySQL does it. Therefore, no one else should do it, because:
4. It's not part of the standard (and most of what MySQL does is not
part of the standard).

If you've learned "SQL" on MySQL, you haven't learned SQL. You've
learned MySQL. The same applies to people who want to do [tablename]
-- it's a Microsoftism that (IMO) should be *de-supported* in SQLite
because it's ugly and wrong.

The only quotes that SQL92 supports are ' and "; ' indicates data, "
indicates literal interpretation of table and column names. Not `, not
[].

If MySQL does it and it's not part of the standard, I'm opposed to it.

-austin
-- 
Austin Ziegler * [EMAIL PROTECTED]
   * Alternate: [EMAIL PROTECTED]

[sqlite] What is SSE?

[Ned Batchelder]

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.

http://nedbatchelder.com

 

 

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

[Ned Batchelder]

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 scenario:

1. begin()
2. do_db_work()
3.   begin()
4.   do_db_work()
5.   rollback()
6. do_db_work()
7. end()

What does line 5 do?  What does line 6 do?  What does line 7 do?

I decided for my own work that magic nested transactions are a bad idea.  To
properly handle errors and rollback the database, you need to know where the
edges of the transaction really are.  Fully-supported nested transactions
may be a good thing (I've never used them, so I don't know).  But pretending
that you have nested transactions when you don't is just waving your hands
over some very important issues.

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Eli Burke [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 01, 2005 12:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] idea: sqlite3_begin() and sqlite3_end() ?


>That's a good question. It would save all the wrapper writers some
>time. I don't think the library is the place to put thread safe
>code for several reasons:
>
>Some of us don't use threads and don't need thread
>safe code.
>
>I prefer modular code with as little mixing of function
>as possible. It makes it easier for me to reuse code in
>other projects. (For example: Don't put GUI messagebox
>code to report errors into sqlite. I might want to use it in
>an mp3 player, which doesn't have a GUI.)
>
>Since it's very operating system dependent it would
>be difficult for Dr. Hipp to test. He doesn't have a copy of every
>operating system and every version at his office. He probably
>doesn't know how to write thread safe code on every OS either.
>
>  
>
I don't think the discussion of adding sqlite_begin/end/rollback should 
get bogged down
with whether or not it should support threads. It's an idea that stands 
on its own merit.

Actually, come to think of it, each thread *should*  have it's own DB 
handle, and each
DB handle can use transactions independently of the others 
(IMMEDIATE/DEFERRED/
EXCLUSIVE notwithstanding). So really it's a non-issue. I shouldn't have 
mentioned it in
the first place :-)

-Eli

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

[Ned Batchelder]

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
will be correct).  If they don't (Ar Br Aw Bw), then A's write will be lost.
By queuing the writes, you've lost the association with the reads they were
based on, and the database doesn't have a chance to enforce the isolation of
the transactions.  Don't forget: the whole reason the SQLite locks work the
way they do it to guarantee the proper transaction semantics.

To make a single writer thread work, you'd need to put the *entire*
transaction, including the reads, onto the writer thread.  If you're going
to do reads on one thread, and then writes on another, you might was well
avoid the threading mess, and structure your operations like this:

begin;
/* do all the reads */
commit;

do {
begin;
/* do all the writes */ 
commit;
} until the commit succeeds;

The best solution is to rollback entire transactions when the database is
busy.  This means structuring your code so that all transactions can
rollback and retry.  In my experience, this is necessary (though *far* less
frequently) even with the "big boy" databases.  See, for example,
http://staff.newtelligence.net/clemensv/PermaLink,guid,826bc7c9-8b0f-4df6-aa
be-e6c5377a9446.aspx

--Ned.

-Original Message-
From: Andrew Piskorski [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 14, 2005 8:23 PM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty
SQLITE_BUSY workaround

On Tue, Mar 15, 2005 at 12:21:15AM +0100, Thomas Lotterer wrote:
> On Sun, Mar 13, 2005, jed wrote:
> 
> > [...] web applications fit well into the model of "many readers, one
> > writer", sqlite does this very well.
> > 
> Well, there might be web applications which are read-only from the web's
> view. But to be honest, most of them also call for occasional writes.
> Think of a simple address book. Also I think of uses like tracking
> session cookies which also use occasional writes. In all those cases

I have not tried this with SQLite, but if I was using SQLite for such
an app, I assume I would need to serialize all such writes through a
single thread or process.  E.g., in my web server, the connection
thread servicing the user request would send a message to my one
single db writer thread, saying, "Heh, please insert this data for
me!".  Then the conn thread would simply block until the db writer
thread replies with, "Yup, your transaction is committed."

That is a uglier than what you'd do with a real server-based RDBMS
like Oracle or PostgreSQL, but it should scale fine until you have
either:  One, very large numbers of hits on your simple and efficient
web app.  Or two, a complicated web app with many potentially long
running transactions, etc.  In the real world, the second concern is
much more likely to bite you than the first, and cries out for a more
capable, more general purpose database than SQLite.

It would be nice if SQLite had MVCC, which would let it scale much
further up into the PostgreSQL-like realm normally dominated by
client/server databases, but given the "simple, small, embedded" niche
that Dr. Hipp intended for SQLite, it's easy to see why adding MVCC
isn't any sort of priority, even if it could be done without making
the code much more complicated.

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

RE: [sqlite] BLOB versus table storage

[Ned Batchelder]

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 entire
polygons, storing them in a blob will be a better solution.

--Ned.
http://nedbatchelder.com

-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 17, 2005 2:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BLOB versus table storage


[EMAIL PROTECTED] said:
> CREATE TABLE polygons (id INTEGER PRIMARY KEY, externalref INTEGER,
> pointcount INTEGER, pointlist BLOB)
>
> When I insert data to this table, I have to write a binary list of x,y
> coordinates in a sequential memory region before storing in the database.
> Getting information back from the database requires a conversion in the
> opposite direction.  Typically these lists contain 10-20 points, so they
> are not very large.
>
> This currently works, but I'm wondering if it would be better to create a
> new table for the points and reference the polygon primary key:

Create the second table.  There shouldn't be any question about this. 
That's just good database design.

Clay Dowling
Opinionated Programmer

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

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

[Ned Batchelder]

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.

--Ned.
http://nedbatchelder.com


-Original Message-
From: teoh [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 12:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite search by "DATE" range ?

does that mean. i just create  "timedate int" in sql.
and use  unixepox to store the date+time inside it.
and query it using unixepox for range ?

select * from table1 where timedate >=
unixepox_number1 and timedate <= unixepox_number2;

is this correct? thank you.



--- [EMAIL PROTECTED] wrote:

> teoh <[EMAIL PROTECTED]> writes:
> 
> > does anyone knows algorithm use to quote date
> range?  
> > let say, user wants to quote for result from
> > 20-12-2004 until 14-1-2005 ?(dd-mm-)
> 
> This page contains all of the information about
> supported date and time
> functions in SQLite:
> 
>
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> 
> You'll probably want to save your dates as a single
> number for ease of
> manipulation and comparison.  If all you care about
> are dates (i.e. without
> associated times during the day), then you can use
> the Julian day number, "%J"
> in the strftime() documentation.  If you need times
> as well, then a convenient
> format may be the Unix epoch value, the number of
> seconds since 1970-01-01 at
> midnight, "%s" in strftime().
> 
> Derrell
> 





__ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail

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

[Ned Batchelder]

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
columns if there are more than one).

--Ned.
http://nedbatchelder.com

-Original Message-
From: Scott Chapman [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 11:51 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Can you get a description of a table when the sql results
are empty?

I made a little python code below that fetches a sqlite3 table description 
(using apsw) but it won't work when the table is empty.  I made it tell me 
the table is empty in this case.  

Is there a way to get the columns in a table without having to parse the SQL

that created the table, when the table is empty?

import re
import sys
import apsw
db = apsw.Connection(sys.argv[1])
cursor = db.cursor()
print
sql="select tbl_name from sqlite_master where type='table'"
cursor.execute(sql)
tables=[]
while True:
    try:
        db_row=cursor.next()
    except StopIteration:
        break
    tables.append(db_row)
print "Tables:"
for table_name in tables:
  print table_name[0]
  print
  table_name=table_name[0]
  print ""
  print "table name: %s" % table_name
  print        
  # Get the sql that created table and parse it to determine which is the 
primary key.
  # I don't know of any other way to determine the primary key in sqlite3.
  sql="select sql from sqlite_master where tbl_name='%s';" % table_name
  cursor.execute(sql)
  create_table_sql=cursor.next()[0]
  print "Creation SQL: %s" % create_table_sql
  print
  match=re.search(r'.*[ (](.*?) integer primary 
key',create_table_sql,re.IGNORECASE)
  if match:
      pkey_column = match.group(1)
      print "Primary key: %s" % pkey_column
      print
  
  # Get a row from the table so I can determine the description
  sql="select oid,* from %s limit 1" % table_name
  cursor.execute (sql)
          
  # Get the column names and make the HTML  header row
  try:
      cdes=cursor.getdescription()
  except apsw.ExecutionCompleteError:
      # HERE's where I'd like to be able to go ahead and get the
description!
      print 'table: %s is empty!' % table_name  
  else:
      print "Columns in table %s:" % table_name
      for header,dummy in cdes[1:]:
          print '  %s: %s' % (header,dummy)

Scott

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

[Ned Batchelder]

The pk column in the table_info pragma is populated, but as I said, the
index_list pragma doesn't mention an index.

--Ned.
http://nedbatchelder.com

-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED] 
Sent: Sunday, January 23, 2005 3:34 PM
To: sqlite-users@sqlite.org
Subject: 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 pragma index_list about the primary key at all:

Are you sure? I'm pretty sure I use the 'pk' value from the table_info
results in my own SQLite manager application, and it appears to be
able to detect the primary key column from the table created by your
schema.

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

[Ned Batchelder]

One more twist I just discovered:

If the primary key is a single column that auto-increments, there is no
information in the pragma index_list about the primary key at all:

sqlite> create table foo(a int, b integer primary key, c int);
sqlite> pramga table_info(foo);
cid nametypenotnull dflt_value  pk
--  --  --  --  --
--
0   a   int 0   0
1   b   integer 0   1
2   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] 
Sent: Sunday, January 23, 2005 12:55 PM
To: sqlite-users@sqlite.org
Subject: [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 in the key.

 

pragma index_info tells me the order of columns in the index, but not which
index is the primary key.

 

pragma index_list tells me all the indexes on a table, but not which index
is the primary key.

 

It looks like the first index in index_list which is named
"sqlite_autoindex_*", and is unique, is the primary key, but can I be
guaranteed of that?

 

What would be ideal is if the pk column in pragma table_info was not just 0
or 1, but was 0 for columns not in the primary key, and 1 through n for the
columns in the primary key, with the value determining their ordering.  I
understand that represents a slight backward compatibility problem.

 

Is there something I've missed?  Does anyone have a better way (other than
parsing the table SQL) to determine the primary key?

 

--Ned.

http://nedbatchelder.com

 

 

 

[sqlite] Determining the primary key of a table?

[Ned Batchelder]

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 in the key.

 

pragma index_info tells me the order of columns in the index, but not which
index is the primary key.

 

pragma index_list tells me all the indexes on a table, but not which index
is the primary key.

 

It looks like the first index in index_list which is named
"sqlite_autoindex_*", and is unique, is the primary key, but can I be
guaranteed of that?

 

What would be ideal is if the pk column in pragma table_info was not just 0
or 1, but was 0 for columns not in the primary key, and 1 through n for the
columns in the primary key, with the value determining their ordering.  I
understand that represents a slight backward compatibility problem.

 

Is there something I've missed?  Does anyone have a better way (other than
parsing the table SQL) to determine the primary key?

 

--Ned.

http://nedbatchelder.com

 

 

 

RE: [sqlite] Good db XML datastructure?

[Ned Batchelder]

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 to use XMLSpy, as it will connect to a database and
generate XML Schema for your tables.  I haven't tried it though.

For my purposes, I ended up using a simple ad-hoc XML file.

--Ned.


-Original Message-
From: Keith Herold [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 13, 2005 3:25 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Good db XML datastructure?

Hi all; I have been trying to find a good XML schema for describing
databases, so that I can, among other things, produce documentation
for my database format.  Is there a good (free) one already out there,
that is genarally accepted/used?  I'm not looking to export data, just
the description of the database (i.e., schema, comments, etc.)

-- Keith
**
- Ever notice how 'big' isn't, compared to 'small'?

- Sounds like a Wookie; acts like mad cow.

- I'm not a professional; I just get paid to do this.

- Rules for programming:
   1.  Get it working, right?
   2.  Get it working right.

- Things I've learned about multithreaded programming:

123...   PPArrvooottieedcc ttm  ueelvvteeirrtyyhtt
rhheiianndgge  dwi hnpi rctohhg eri aslm omscitanalgt 
 iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd  'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
**

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

[Ned Batchelder]

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
determine if the file already exists, etc, to get their setup logic right.

--Ned.

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 08, 2004 1:23 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] new Ticket 949: add user-level test for file validity

At 12:22 PM +1000 10/8/04, [EMAIL PROTECTED] wrote:
>Darren,
>Are you asking for a pragma integrity_check (which already exists) or are
>you just wanting to verify the magic string at the beginning of the file?

The magic string.  I want an elegant way for a user to explicitly 
check the magic string, that is built into the SQLite library itself, 
and which means not issuing a superflous select.

>Personally, I think it would be nice to have some means to say "Open this
>file if it already exists and is an sqlite file: Don't create it if it
>doesn't exist". I'm not sure I've found any cases where it is entirely
>necessary, though.
>Benjamin.

You're absolutely right.  In fact, I very much want that too.

1. I want a command that is explicitly for creating a new file, that 
will fail with an error if the file already exists.

2. I want a command that is explicitly for opening an existing file, 
that will fail with an error if the file does not exist.

3. While we're at it, maybe an explicit built-in command to delete a 
database (done by the SQLite library since it knows best how to clean 
up after itself).

Perhaps an elegant solution for this and similar things be to add a third
argument to open() called "mode" which simply takes one of a list of 
defined numerical codes (or alternately, a single bitmask) 
representing the behaviour we want.  Example values could be: 1. "new 
or fail"; 2. "existing or fail"; 3. "new or existing" (the default). 
Possibly the thing about checking the magic number could be a fourth 
and fifth option (to complement 2 and 3), unless that is simply asked 
for separately.

Any thoughts on this idea?

-- Darren Duncan

RE: [sqlite] Locking enhancments

[Ned Batchelder]

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] How to compile without TCL on Vs.Net

[Ned Batchelder]

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 Message-
From: Peter Loveday [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 16, 2004 7:02 PM
To: [EMAIL PROTECTED]; Sören Krings
Subject: Re: [sqlite] How to compile without TCL on Vs.Net

> how can i compile the sqlite3 source without any TCL bindings with
> Visual Studio 7.1?

I simply left out 'tclsqlite.c' and 'shell.c', and the rest compiled fine. 
You could probably define NO_TCL if you really want the source file in 
there, but I just removed it.

> And is ist normal that i get so much conversion warnings?

I get conversion warnings also, most of them are harmless, but annoying.

Love, Light and Peace,
- Peter Loveday
Director of Development, eyeon Software

RE: [sqlite] Concerns about checkin 1879

[Ned Batchelder]

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
the overall throughput of the system, and I know it will eventually get its
chance, so I don't mind if it has to redo a bunch of work. 

--Ned.
-Original Message-
From: Brass Tilde [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 19, 2004 9:24 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Concerns about checkin 1879

> More sophisticated programs that want more control can still
> have it, even with check-in [1879].  If thread A is trying
> to COMMIT and thread B is trying to UPDATE, you will get
> a busy handler callback from thread A and an SQLITE_BUSY
> reply from thread B.  But the SQLITE_BUSY return from thread
> B did not clear any locks.  There is nothing that prevents
> the program from rolling back thread A then reissuing the
> UPDATE statement of thread B.

OK, maybe I'm just not getting something here, but why on earth would I want
to roll back a commit in order to allow an update?  Shouldn't it be the
other way around?  If thread A has completed it's update, and is now in the
process of committing that change, why does that *not* take precedence over
a thread that is just now starting it's update?

[sqlite] Concerns about checkin 1879

[Ned Batchelder]

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.  Thread B begins making changes, and wants to promote its lock
from shared to reserved.  At that point, no more progress can be made.
Thread A can't go from pending to exclusive because B still has a shared
lock.  Thread B can't go from shared to reserved because Thread A has a
pending lock.

 

My experience with the code up to 3.0.3 is that both thread A and B will
begin calling the busy handler.  I've written my application so that my
busy handler can choose which thread to force to rollback, using
application-specific knowledge about the precedence of the threads (UI
threads are given preference, and background threads roll back).  It may
happen that thread A is actually a background thread, so even if it has
the more advanced lock, I will choose to roll it back, letting thread B
continue on.

 

With the change in checkin 1879, it looks like my busy handler doesn't
get a chance to make a choice any more.  Now thread B immediately gets a
BUSY error, without invoking the busy handler.

 

Personally, I was very pleased that the busy handler allowed me so much
control.  I can see the other side of the argument: for users who don't
want to be bothered implementing a fancy busy handler, the fewer
deadlocks the better.  My application will continue to work (because all
threads are prepared for deadlock errors), but I liked being able to
make an intelligent choice.

 

Wouldn't it be better to invoke the busy handler in all cases?  Have I
gotten something wrong?

 

--Ned.

http://nedbatchelder.com

RE: [sqlite] 3.0 ship date?

[Ned Batchelder]

I don't mean to be pushy, but do you have a timeframe, even a vague one?
The time between 2.0 alpha and ship was eight days.  Are you thinking in
terms of days, weeks, or months?

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: 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 leaving beta and becoming official, as in "OK for production use"?
>  

I would say that 3.0 is OK to begin integrating with product.
But I want to get out a few more releases and test it a bit more before
you start shipping those products.


--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

[sqlite] 3.0 ship date?

[Ned Batchelder]

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