[sqlite] Are tclsqlite.dll and tclsqliet3.dll supposed to be usable in the same script?
I remember reading that the C libraries of both versions can be used in
the same executable, and I may have drawn the incorrect conclusion that
the TCL bindings can be used together, too.
Below is a listing that shows the problem, but in a nutshell, it seems
that loading version 3 after version 2 corrupts the former:
$ tclsh
% load tclsqlite.dll
% sqlite sq mydb.sq2
0x01093E70
% sq eval "select sql from sqlite_master"
{create table a(b,c)}
% load tclsqlite3.dll
conflicting versions provided for package "sqlite": 2.0, then 3.0
% sq eval "select sql from sqlite_master"
{create table a(b,c)}
% sqlite3 sq3 mydb.sq3
0x01096980
% sq3 eval "select sql from sqlite_master"
{CREATE TABLE d(e,f)}
% sqlite sq mydb.sq2
0x0109E178
% sq eval "select sql from sqlite_master"
file is encrypted or is not a database
From the above it appears that the[sq] command created by using
[sqlite] still works after loading version 3, but [sqlite] gets messed
up and can no longer create a new sq.
If the .dll files are loaded in the other order, both versions still
seem to work:
$ tclsh
% load tclsqlite3.dll
% load tclsqlite.dll
conflicting versions provided for package "sqlite": 3.0, then 2.0
% sqlite sq mydb.sq2
0x01093E70
% sq eval "select sql from sqlite_master"
{create table a(b,c)}
% sqlite3 sq3 mydb.sq3
0x01096980
% sq3 eval "select sql from sqlite_master"
{CREATE TABLE d(e,f)}
I can see at least a couple possibilities: 1) There is a fundamental
incompatibility, and further testing would show that the seeming success
of the second example is just a fluke. 2) The behavior is as expected,
and there are good reasons why loading 3 then 2 works but loading 2 then
3 doesn't, and such behavior can be counted on in future releases of
version 3. 3) The two should behave properly no matter which order they
are loaded in, and I have found an error.
I am hoping the answer is not 1), because I am planning to try to add a
conversion routine (2->3 and 3->2) to my TCL/TK/SQLite utility.
TIA for any help,
Gerry
--
--
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
Re: [sqlite] CROSS keyword disables certain join optimizations
On Sat, Sep 10, 2005 at 07:25:48PM -0400, D. Richard Hipp wrote: > difficulties, I have now modified the query optimizer so > that it will no longer reorder tables in a join if the join > is constructed using the CROSS keyword. For additional This is a one-off to control one particular feature (table reordering) of the optimizer. Is it likely that in the future, perhaps as the optimizer grows more complex and featurful, that programmers will want to be able to give further such directives or hints to the optimizer? If the answer is yes, then maybe it would make more sense to provide an actual syntax or language for giving SQLite such hints, probably by embedding them into specially formatted SQL comments (which is Oracle's approach). -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
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 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] 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] CROSS keyword disables certain join optimizations
At 7:25 PM -0400 9/10/05, D. Richard Hipp wrote: The rational behind using the CROSS keyword to disable an optimization is that the CROSS keyword is perfectly valid SQL syntax but nobody ever uses it so I figured we can put it to use to help control the optimizer without creating any incompatibilities. 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 "SELECT * FROM foo CROSS JOIN bar" to be every foo row crossed with every bar row. This actually is used in real situations, even though it is less common than an INNER or OUTER join. See SQL:2003, 7.7 "". -- Darren Duncan
[sqlite] CROSS keyword disables certain join optimizations
Beginning with SQLite 3.2.3, the query optimizer has had the ability to reorder tables in the FROM clause if it thinks that doing so will make the query run faster. This has caused a few problems for some folks. To ameliorate those difficulties, I have now modified the query optimizer so that it will no longer reorder tables in a join if the join is constructed using the CROSS keyword. For additional detail see http://www.sqlite.org/cvstrac/tktview?tn=1414 http://www.sqlite.org/cvstrac/wiki?p=QueryPlans Thoughtful feedback on this design choice will be appreciated. I'd like to do a release of version 3.2.6 containing this and other changes on or before September 16. The rational behind using the CROSS keyword to disable an optimization is that the CROSS keyword is perfectly valid SQL syntax but nobody ever uses it so I figured we can put it to use to help control the optimizer without creating any incompatibilities. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] questions from a new user
Hi Dan, dan greene wrote: 1. Does anyone have a zipped up version of the SQLITE documentation? The online documentation is great but on at least one of my development systems I don't have web access. A local copy on my windows machine would make things easier. That's part of the sourcecode. If you are on a Unix/Linux box, you can do make doc and it will be built for you in doc/. Cheers, Ulrik -- Ulrik Petersen, Denmark
Re: [sqlite] questions from a new user
> > 2. Could someone give me a brief overview on how to insert and retrieve > BLOB data in SQLITE? It seems to me that sqlite3_exec is fine for > selecting and inserting rows when the column data being used is a > 0-terminated string. But what is the general approach if one or more > columns contain variable-length binary data? There is some example code for that here: http://www.sqlite.org/cvstrac/wiki?p=BlobExample __ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/
[sqlite] questions from a new user
Hi, I have just recently come across the SQLITE database library and it appears to be just the library I've been looking for. I do have a few questions that perhaps another, more experienced SQLITE user, could answer. 1. Does anyone have a zipped up version of the SQLITE documentation? The online documentation is great but on at least one of my development systems I don't have web access. A local copy on my windows machine would make things easier. 2. Could someone give me a brief overview on how to insert and retrieve BLOB data in SQLITE? It seems to me that sqlite3_exec is fine for selecting and inserting rows when the column data being used is a 0-terminated string. But what is the general approach if one or more columns contain variable-length binary data? Thanks for any help! Cheers Dan Greene
