Re: [sqlite] Unique ids for each record

[Eric Bohlman]

Sreedhar.a wrote:

Hi Lokesh,

Thanks for your suggestions.

My proble is assigning a unique record for each record in a table.

The database table name MUSIC.

I have 3 columns Artist Album and Tracks.

Artist1 Album1  Track1
Artist2 Album2  Track2
Artist3 Album2  Track3

I need to assign a unique id value for each item in the row.


Your data model is extremely awkward because you're trying to model 
entities as columns rather than table rows. "Artist," "album" and 
"track" are all distinct entities and should each have their own tables.
You really need four tables: an "artist" table that contains a unique 
identifier for each artist, as well as any attributes that relate to the 
artist (e.g. year formed), an "album" table that contains a unique 
identifier for the album, along with any attributes that describe the 
album (date released, producer, etc.), a "tracks" table that describes 
each track, with a unique identifier and any track-specific information 
(was it done as a video? released as a single?) and, finally, a 
"tracklist" table that pairs up albums with tracks.


You need artist identifiers to be unique (you could in fact use artist 
names, since copyright rules require that such names be unique). You 
also need album names to be unique, but only within artist groups 
(you're not going to have the *same* artist release two albums with the 
same name). You need track names to be unique within artists, but not 
otherwise (quick, name two boybands whose first singles had the same 
names*).


You'll want to use joins to relate the four tables.

* Both the Jackson 5 and N'Sync had first singles titled "I Want You 
Back" Two completely different songs.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] converting a sqlite table to dbf

[Eric Bohlman]

P Kishor wrote:

Thanks, so the answer is there is no straight-forward way of doing
this. I will export to CSV, and then rebuild a dbf using Perl.


Since you've got Perl, you can skip the CSV step; just make sure you 
have DBI and DBD::SQLite and DBD::XBase installed and create connections 
to both databases.  Then it's a simple matter of doing SELECTs on the 
SQLite connection and INSERTs on the XBase connection.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] More problems with fts1

[Eric Bohlman]

The following query

  SELECT title,snippet(stories,"~","~","...")
  FROM titles JOIN stories ON titles.story_id=stories.rowid
  WHERE stories.body MATCH ?

results in a complaint that MATCH is being called in the wrong context. 
Prefixing any/all column names with "+" doesn't change this.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Eric Bohlman]

COS wrote:

A small opinion on that matter: what I would really like to see is something
like system tables. Today sqlite uses only sqlite_master to keep information
about its objects and parsing is required to getter better info of each
object (if one needs to). Using other system tables to keep information
about each object seems very appropriated since most RDBMS already implement
that and it is compatible with SQL ANSI. I think it shouldn't be much of a
problem since sqlite engine already parses each object when it opens a
database. This would remove the need for PRAGMA commands and would make life
much simpler.


It might be possible to implement something like this using virtual 
tables.  I seem to remember that you wouldn't be able to support the 
exact syntax of ANSI INFORMATION_SCHEMA, but would be able to support 
most of the functionality.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Regexp

[Eric Bohlman]

T&B wrote:
I know there are hooks for adding a Regexp function/operator ourselves, 
but I need to know it's available on other machines with standard 
install. It's the same reasoning, I guess, as why Trim() was added, but 
Regexp seems to serve a wider need.


I'm pretty sure the problem is that while there are many open-source 
regexp libraries, none of them are public domain and consequently 
couldn't be added to the standard SQLite distribution without 
compromising its public-domain status.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

[Eric Bohlman]

Derrell Lipman wrote:
> The amalgamation probably installed into some directory not in your path.
> You should look at where it installed (re-run ../configure and look at its
> output, which should tell you where it will install to. For Ubuntu, you
> almost certainly want it to install into /usr/local with the executable
> going into /usr/local/bin. If it chose some path other than /usr/local, you
> probably want to remove it from wherever it installed to.
> 
> Next, remove the Ubuntu-provided version of sqlite3 since you won't need it
> any longer:
> 
>   sudo apt-get remove sqlite3

The amalgamation builds by default into /usr/local, but the apt package 
goes into /usr. Since some other packages have sqlite3 as a dependency, 
it's best not to remove the original install, but rather to overwrite 
it; otherwise installing new packages may end up "restoring" the old 
version. Simply configure with --prefix=/usr.

Note that the main problem isn't the search path for executables; it's 
the search path for dynamic libraries.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DBD::SQLite reporting corruption sqlite3 CL program does not

[Eric Bohlman]

Craig Talbert wrote:
>>From Perl, when I attempt to make a database connection using SQLite,
> I get the following error:
> 
> [Tue Jun 23 17:10:22 2009] projectory.cgi:
> DBI->connect(dbname=projectory.sqlite3) failed: database disk image is
> malformed at ./projectory.cgi line 1577
> 
> At line 1577 it is executing this code
> 
> $dbh = DBI->connect("dbi:SQLite:dbname=projectory.sqlite3","","") or
> die "$DBI::errstr\n";
> 
> When I use the sqlite3 tool to do an integrity check, I get the following:
> 
> rintintin> sqlite3 projectory.sqlite3
> SQLite version 3.6.15
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> PRAGMA integrity_check;
> ok
> sqlite> .exit

Pretty obvious, but: since your dbname is unqualified, are you sure your 
working directory in your code is the same as your current directory 
when using the command line? Also, what version of SQLite is linked into 
your DBD::SQLite3? (if you're using DBD::SQLite, make sure it's not so 
old as to be using SQLite v2; I forget when the naming change was made).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Near misses

[Eric Bohlman]

Simon Slavin wrote:
> On 26 Jun 2009, at 12:25pm, Alberto Simões wrote:
> 
>> one adition, one remotion or one substitution
> 
> I am always amazed at how well people use English.  For your word  
> 'remotion' you probably mean 'removal' or 'omission'.  You have joined  
> the two possibilities together !

Although Alberto has explained the etymology of the term, in general the 
condensation of two or more words into one is called a "portmanteau." My 
favorite portmanteau arose when about 30 years ago a co-worker reported 
that software problems on an embedded device were caused by two routines 
"interfecting with each other." Interacting, interfering, affecting, 
infecting and probably more, all packed with a remarkable economy of 
expression.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding data with periods

[Eric Bohlman]

Mohd Radzi Ibrahim wrote:
> It seems to works either way.
> 
> I'm just wondering is there any hidden reason that single quote is 
> preferred? Portability?
> Or is double-qoute has some kind of special meaning that we should use it 
> for that special purpose?

If what's enclosed in the double quotes winds up being a column name or 
other identifier, SQLite will treat it as an identifier rather than a 
literal. This can cause all sorts of unpleasant surprises, particularly 
of the "this was working fine until I made this cosmetic change..." sort.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing Value from one query to another

[Eric Bohlman]

BareFeet wrote:
> In general, I think it's much better (performance and logic) to do
> all you can in SQL, without passing values out of SQL results, into
> your non-SQL code, then re-injecting back into another SQL query etc.

With SQLite, that's not really going to make a difference. Since it's an 
embedded library rather than an out-of-process server, 
"passing/injecting" doesn't require marshalling or network overhead, so 
there's no performance hit. In terms of logic, since SQLite doesn't have 
stored procedures the idea of centralizing database interactions into 
the DB itself rather than distributing the logic between applications 
isn't applicable here.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ALL and ANY

[Eric Bohlman]

Are there any (hehe) plans to implement the ALL and ANY keywords for 
testing against subqueries?

Re: [sqlite] Unlucky number for the ROUND function

[Eric Bohlman]

Edzard Pasma wrote:

I found a number where the ROUND () function goes wrong:

SQLite version 3.2.5
Enter ".help" for instructions
sqlite> select round (9.95, 1);
:.0 


I get 9.9 (running on Win98, compiled with MingW).

Re: [sqlite] group by error in 3.2.6?

[Eric Bohlman]

Edzard Pasma wrote:

There is a difference in the behaviour of GROUP BY in version 3.2.6. If you group by a column that 
contains NULL values, each NULL value comes on a seperate output line. Conceptually not so bad, if 
we read NULL as "unknown" and not as "empty". But I guess it is an error.


This does indeed appear to be an error: see 
http://archives.postgresql.org/pgsql-sql/1999-05/msg00164.php
for a discussion which cites the SQL-92 specification as saying that two 
NULLs are considered "not distinct" and therefore should be grouped 
together.


MSSQL (and its parent Sybase) document that nulls will be grouped together.

Re: [sqlite] SQL Queries

[Eric Bohlman]

Chris Gurtler wrote:
I'm pretty new to SQLite, and am just looking for a few pointers on SQL 
queries, this is an example of a query from a crapy MS Access database 
that I want to convert to SQLite, but it fails. it says c.group_id does 
not exist.


I'm wondering if anyone has got some tips on multiple joins, I suspect 
SQLite doesn't like these joins very much and I will need to rewrite 
some of the queries.


It's not a problem with joins _per se_, it involves the propagation of 
column names from subqueries.  It looks like this is the same problem 
reported in ticket  
(http://www.sqlite.org/cvstrac/tktview?tn=,33) and the workaround 
suggested there might help.

Re: [sqlite] Tool to load a database and see all tables and fields.

[Eric Bohlman]

spudse bud wrote:

I always was a big fan of phpmyadmin because when php/mysql errored I could
check table/field names etc to see where my script goes wrong.

I am wondering if a easy tool exsists for SQLite, that just lists the tables
and fields in a database. I have tried sqlitemanager, but I didn't really
liked it.


Have you checked out the PHP section of 
http://www.sqlite.org/cvstrac/wiki?p=SqliteTools ?  There's a listing 
for phpSQLiteAdmin, which is supposed to be patterned after phpmyadmin, 
though it appears not to be under active development.

Re: [sqlite] Static library for sqlite for Windows

[Eric Bohlman]

Murugan, Muthulakshmi wrote:

I have downloaded the sqlite 3.2.7. binary version for Windows. But I
found no libsqlite3.lib.
Can anyone help in this regard?


See http://www.sqlite.org/cvstrac/wiki?p=HowToCompile

This question comes up so often that it should really be included in the 
FAQ.

Re: AW: [sqlite] and , or

[Eric Bohlman]

Martin Engelschalk wrote:
The problem seems to be that sqlite makes a difference between an empty 
string and a null value.


s/sqlite/SQL/

The SQL standards all say that nulls never compare equal to anything, 
not even other nulls.  SQLite's behavior here is the correct one.

Re: [sqlite] SQLite as a Windows kernel tool

[Eric Bohlman]

Ken & Deb Allen wrote:
I had a quick look at some of the code, but I am not certain whether  
all, or even most, of these warnings can be safely ignored or not. I  
tried modifying the code to add explicit casts to eliminate all of  the 
warnings, which worked, but I do not know whether or not the  resulting 
code contains runtime errors or not (specifically as a  result of data 
loss at runtime or improper comparison logic).


You might want to look at ticket 1255 
(http://www.sqlite.org/cvstrac/tktview?tn=1255,2).

Re: [sqlite] Re: Number of rows in a query result

[Eric Bohlman]

Edward Wilson wrote:

What I was trying to say was: with other db products the drivers (or something 
somewhere)
calculated the number of rows returned in a query automagicly.  I have never 
had to do anything
'extra' to get the number of rows in a query other than 
resultset-object.rowcout - 'rowcout' being
whatever the syntax was for that particular environment.  So what I meant was, 
I have always taken
for granted that the rowcount was 'apart of' the query returned from the 
database and not
something that I had to do 'extra' in addition to fetching the data to begin 
with.  I hope this
was clear.


I think you'll find that any interface involving a "resultset object" is 
really a wrapper that talks to the database at a fairly low level and 
actually retrieves all the selected rows into its internal memory, later 
parcelling them out in response to method calls.  Of course it can count 
the rows as it retrieves them and make the count available through a 
method or variable.  SQLite's native API actually corresponds to the 
low-level communication between the wrapper and database; the row 
counting would be done by the code that calls the API.  I believe that a 
while back drh strongly implied that most substantial code should be 
accessing SQLite via a wrapper rather than the "raw" API.

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Eric Bohlman]

Henry Miller wrote:

As for 5/2, my grade school teachers taught me that if I round it at
all, the answer is 3, NEVER 2.   It is only latter in life that I
learned about bankers rounding which sometimes is 2, sometimes 3.I
have never seen a justification for rounding to 2, except for the bogus
answer that it is easy for the computer to do.   Thus I conclude
whatever the final answer is, sqlite would be wrong if 5/2 is always 2.


The problem with the grade-school rule is that, assuming the last digit 
is uniformly distributed, you'll be rounding up 5 times out of 9 and 
rounding down 4 times out of 9.  That means that if you add up a large 
number of rounded numbers, the result will always be larger than the 
what you'd get if you added up the unrounded numbers and then rounded 
the sum.  That introduces a systematic bias in financial and statistical 
calculations.

Re: [sqlite] Request for comment: Proposed SQLite API changes

[Eric Bohlman]

Dennis Cote wrote:

I think both of these proposed changes are useful enhancements to SQLite.

I also think it would be better to add a new sqlite3_step_v2() API 
function that does this. This will eliminate the need to change the base 
version number, since existing code can continue to use the existing 
step function. New code, or those who want to modify their existing 
code, could use the new function.


The new step function should be a wrapper around the existing step 
function in much the same way as sqlite3_exec() wraps the entire 
prepare, step, finalize process. It would do the enhanced error checks 
and return the real error, or automatically re-prepare the SQL if the 
error was SQLITE_SCHEMA.


This would give you the best of both worlds, backwards compatibility and 
a cleaner API for new code.


I have to agree here; simply changing sqlite3_step() could cause 
problems for code that links to sqlite as a shared library/dll and can't 
be sure which minor version is installed.  Currently it's usually 
possible to simply drop in the latest version of the library to get the 
latest features/optimizations/bug fixes; I think that behavior is 
important to preserve.


The auto-compilation change is potentially a can of worms, because while 
it's not likely in a sane environment, it's possible that a schema 
change could render the original query uncompilable, particularly if 
SQLite ever added support for dropping or renaming columns.  Or the 
authorizer callback could fail upon recompilation.  The upshot is that 
there are still going to be cases where a schema change could cause an 
sqlite3_step() to fail so it would still be necessary to write defensive 
code; the change would simply cause that code to be executed less 
frequently.

Re: [sqlite] optimizing out function calls

[Eric Bohlman]

[EMAIL PROTECTED] wrote:

Nathan Kurz <[EMAIL PROTECTED]> wrote:

SELECT uid, match("complex", "function", vector) AS match FROM vectors 
 ORDER BY match DESC LIMIT 20;



SELECT uid, mx FROM
  (SELECT uid, match(...) AS mx FROM vectors LIMIT -1)
ORDER BY mx DESC LIMIT 20;

The LIMIT -1 on the subquery is to fake out the optimizer and prevent
it from folding the subquery back into the main query, resulting in the
same statement you started with.  A "LIMIT -1" is effectively a no-op.
It does no limiting.  But subqueries that contain a limit will not be
folded into outer queries that also contain a limit.


That strikes me as a hack, and also a matter of allowing implementation 
to leak into interface.  The current implementation seems to assume that 
all functions are idempotent; once you allow user-defined functions, 
that's no longer a safe assumption and it can lead to incorrect, as well 
as inefficient, behavior if a user-defined function has side effects.

Re: [sqlite] SQL syntax possibilities

[Eric Bohlman]

[EMAIL PROTECTED] wrote:

uSQLite does not (generally) enter into the details of the query it is
passed. There is however an exception for security. uSQLite requires a
login and (depending on the user and network) assigns the user a level:

0: No access
1: Select only
2: Update/Insert only
3: Select/Update/Insert only
4: Power user

These rules are enforced by looking at the first word in a query, the
code is at the bottom of this post. My problem is that I allow multiple
queries to be concatented, and this is a useful feature for bulk inserts
and blocks of queries that must be executed atomicaly (Major hangup is
level 2 for remote updates inserts). However, with the present routine
I could do eg:


I think you should look into using SQLite's authorizer callback 
mechanism; that way you don't have to parse syntax.

Re: [sqlite] Are my assumptions about the implyed OID valid?

[Eric Bohlman]

Michael Scharf wrote:

-- The user sorts on NUMBER and selects NAME that
-- start with 'foo'. I create a temp table view1.
-- I make the assumption that view1.oid is in order
-- (1...size_of_the_table)
-- This is currently true with sqlite 3.2.7

CREATE TEMP TABLE view1 AS
  SELECT oid AS dataid
  FROM data
  WHERE name like 'foo%' ORDER number;

-- when some columns are displayed in the UI I fetch
-- the items. Say row 100-150 is to be displayed:

  SELECT *
  FROM data
  WHERE oid IN
 (SELECT dataid
  FROM view1
  WHERE rowid between 100 AND 150)
  ORDER BY number; -- this sorting is needed, to
   -- get the result in order!

This work fine currently. The question is, is this an artifact
of the current implementation or can I rely on this in the
future? (I know that's not compatible with the SQL specs)


Wouldn't LIMIT and OFFSET do the trick for you?

Re: [sqlite] Improve INSERT INTO tablename performance for bulk loads....

[Eric Bohlman]

Michael Scharf wrote:

SQLite does not support the "row value constructors"
  http://troels.arvin.dk/db/rdbms/#insert

INSERT INTO tablename
VALUES (0,'foo') , (1,'bar') , (2,'baz');

— which can be seen as a shorthand for

INSERT INTO tablename VALUES (0,'foo');
INSERT INTO tablename VALUES (1,'bar');
INSERT INTO tablename VALUES (2,'baz');

This could reduce the amount of parsing when
bulk-loading data from a file


Using prepared statements with placeholders would reduce the amount of 
parsing even further.

Re: [sqlite] Improve INSERT INTO tablename performance for bulk loads....

[Eric Bohlman]

Michael Scharf wrote:

Eric Bohlman wrote:

Using prepared statements with placeholders would reduce the amount of 
parsing even further.



Well, I think
  INSERT INTO tablename VALUES (0,'foo') , (1,'bar') , (2,'baz');

is a kind of prepared statement, or it could be implemented as
a prepared statement. How would a prepared statemnt reduce the
amout of parsing significantly?

(1,2),  -- the overhead is 2 charactes per statement the '(' and
-- the ')'

The data part of a prepared statement could drop the () but it
would need a separation character for columns ane one for rows.
You coul you use ',' and ';'.

The nice thing about the "row value constructors", is that it is
an (optional) part of SQL


The point is that a prepared statement (INSERT INTO tablename VALUES 
(?,?);) is parsed and translated into a VDBE program only once.  Each 
time you bind values to it and call sqlite3_step(), you're simply 
invoking the already-created VDBE program.  With your proposal, each set 
of values to be inserted has to be parsed and translated into VDBE code.

Re: [sqlite] Any plans for 3.2.8?

[Eric Bohlman]

[EMAIL PROTECTED] wrote:

Next release will probably be 3.3.0.  Not clear yet if it will
occur in 2005 or early in 2006.


Any specific plans for further changes to be included in 3.3.0? (May I 
suggest ALTER TABLE ADD CONSTRAINT now that there are going to be at 
least CHECK constraints?  I'll also put in a vote for quantified 
subquery comparisons (ALL and ANY).)


The average interval between production releases of 3.x has been 26 
days, though the distribution is pretty skewed, with real flurries 
between 3.1.4 and 3.2.1, and 3.2.3 to 3.2.5.  There have been only three 
gaps (not counting the one leading up to 3.3.0) longer than a month; 4 
months between 3.0.8 and 3.1.2, 2.5 months between 3.2.1 and 3.2.2, and 
2.3 months between 3.2.2 and 3.2.3.  IMHO, an interval of at least two 
months is probably beneficial in terms of getting projects to adopt 
SQLite (or 3.x over 2.x) since it increases the perception of stability 
(another thing that might help is trying to clear up a bunch of pending 
tickets in the "known issues, version 3 only" report, many of which may 
be fixed or irrelevant; some of them are definitely duplicates).

Re: [sqlite] how can I import CSV file into SQLite quickly

[Eric Bohlman]

Arjen Markus wrote:

Hm, there is a CSV reading module in Tcllib, so one could contemplate
using Tcl instead of Perl for this. That ought to take care of the
quotes
and other nastiness...


Perl's Text::CSV module available from CPAN also handles these issues.

Re: [sqlite] Final Year Project/Dissertation help required!!!!

[Eric Bohlman]

m christensen wrote:
What you are doing is needs analysis and by definition requires 'help' 
or input from others. This is not doing YOUR work for you.


On the other hand needs analysis is much more complex than just asking 
users what they want.


Most of the time they simply don't know.

Sometimes, and much lest often than some arrogant developers think, they 
are wrong about what they really need.


Sometimes you need to stir the pot some to get people thinking.

Sometimes need to show them potential options to get them thinking.


Definitely true.  Developers often fall into the false dichotomy of 
assuming that software design means implementing either a user/marketing 
wishlist or a lonergeek's personal idea of what's best.  The former 
almost never works and the latter works well in some very limited 
domains but poorly everywhere else.


Proper needs analysis requires:

-- Identifying the users ("customers").

-- *Understanding* the *tasks* the users need to accomplish, and 
understanding them first in task-oriented terms ("business processes") 
rather than implementation-oriented terms.  Otherwise you wind up with 
"XY problems" where what's really needed is a way to accomplish task X, 
but the developer/users/both prematurely decide that the way to do it is 
with tool/implementation Y, and the focus shifts away from the actual tasks.


-- Learning how the users currently accomplish their tasks.

-- Learning in what ways the users' currently method of accomplishing 
their tasks fails to meet their needs.  This is more than just asking 
for wishlists.  It requires working with the users, who may not be able 
to immediately articulate their needs.  One of the most important 
aspects of this phase is recognizing what *doesn't* need to be changed. 
 It is also an almost certainty that simply trying to automate an 
existing manual process will be unproductive and merely increase complexity.


Note that all this does require some "social skills" such as listening 
and perspective taking, which puts off some geeks.  But it does *not* 
require a bubbly, glib, extroverted personality, and the assumption that 
it does is really just an excuse for not doing the work.  It's really 
just a matter of disciplining one's mind, comparable to disciplining 
oneself to finish designing an interface before diving into the 
implementation.  The lack of such discipline leads to interfaces that 
are organized around the implementation rather than vice-versa.


In Mr. Newby's case, the first step really should be to see what's 
currently being done with GUIs for SQLite; what's out there, and how do 
they differ?  Some of the acrimony in this thread came about because he 
skipped that step.  Then the next step should be asking who's using what 
tools, what tasks do they use them for, why did they choose them, what 
do they do well, what do they do poorly, etc.

Re: [sqlite] Is it possible to determine if data is string or numeric inside a callback in C?

[Eric Bohlman]

Downey, Shawn wrote:

Does anyone know if it is possible to determine if data is string or
numeric inside a callback in C++?  I am using sqlite 3.2.7.


Unless you have some requirements that your code be compatible with 
SQLite 2.*, I'd suggest you rewrite it to use the prepare/step 
interface; then you'll be able to access the column types easily.

Re: [sqlite] Fw: Slow sqlite_step

[Eric Bohlman]

Carl Jacobs wrote:

Hello, I'm a new user to this group as I haven't had any issues with sqlite
till now. I tried searching to see if this question has been asked before,
but couldn't find anything.

I'm using Windows.

I have a table with ~45 colums and ~17000 records. I do a search for some
stuff using 'SELECT * FROM MyTable WHERE Field1 LIKE "%val%"', and I know


That particular comparison demands a full scan of the table.


that it returns 5 records.  When I step through the code sqlite_prepare and
sqlite_step return immediately, the column names/types process immediately
using sqlite_column_* functions, as does the first row of data. I then call
sqlite_step which returns immediately (as far as I can tell when single
stepping), and does so till all the data has been collected (all 5 rows). So
far, no problems . . .

. . . BUT . . .

after collecting the results for the last row I then call sqlite_step to go
on with the next step of the operation. Eventually this returns SQLITE_DONE
(as expected) but takes almost 2 seconds to execute!!!

What's happening?


Sounds to me like the matching rows are located near the beginning of 
the table's physical storage, so you get them quickly, and then the next 
sqlite3_step() has to scan the rest of the table.



If I have a search that returns only a single record it still takes 2
seconds.

If field1 is part of an index, then it still takes 2 seconds.


An index won't help with a %% comparison; there's no way to do it 
short of examining every non-NULL value in the column.

Re: [sqlite] Conversion of SQLite 3.2.5 database to 3.3.1

[Eric Bohlman]

Robert L Cochran wrote:
I have a database I created in SQLite 3.2.5, then updated with 
subsequent versions up to 3.2.7. There are several tables.


Version 3.3.1 has a new file format and I'd like to convert the database 
file to this new format. How do I do that?


sqlite3 olddatabase .dump | sqlite3 newdatabase

Re: [sqlite] Slow query after reboot

[Eric Bohlman]

Geoff Simonds wrote:

The app is running on Windows XP machines


Is it possible that indexing services are enabled and XP is trying to 
index the database file?

Re: [sqlite] aynchronous loading

[Eric Bohlman]

chetana bhargav wrote:

Does sqlite provides asynchronous loading of data. Basically if I
have something around 3000 records and want to do some query, instead
of returning the result in one single query is it possible for me to
relinquish the control to other apps so that I wont get a time out
error. and get the data in sets.


Certainly.  Just use the prepare/bind/step interface rather than the 
(legacy) sqlite3_exec() interface.  Prepare your query, bind any 
parameters to it, and then retrieve as many rows at a time as you want 
using sqlite3_step().

Re: [sqlite] Compatability issues with DBD::SQLite and SQLite v3.3.3 and 3.3.4

[Eric Bohlman]

Nathan Kurz wrote:

On Mon, Mar 06, 2006 at 06:24:13PM -0800, Steve Green wrote:

Hi,

I'm hoping that someone can shed some light on the following issue that
I'm seeing.  When I attempt to create a temp table using DBD::SQLite (v1.11)
and either SQLite v3.3.3 or v3.3.4, I get the following error:

DBD::SQLite::db do failed: not an error(21) at dbdimp.c line 398 at 


Same failures here on Linux 2.4.29.  I didn't have time to debug it,
so I decided just to downgrade to a copy of 3.2.7 that I had around,
and things were fine thereafter.  I presume that some recent change to
sqlite is not yet reflected in DBD::SQLite.  'make test' for
DBD::SQLite is another fine test program that fails.


You'll need to go into dbdimp.c and change the two calls to 
sqlite3_prepare() so that the third argument is -1 rather than zero. 
This is due to the change in check-in 3047.

Re: [sqlite] Sorting a text field as if it were integer/float

[Eric Bohlman]

Randy J. Ray wrote:

This may be a basic SQL question, but I can't find the answer in the
SQL-related documents on the site, so...

I have a field in a table that is typed as text, though it is 99% of the time
numerical. (It's used to track issue numbers of magazines, which for some
esoteric publications may be alphanumeric.)

I'd like to sort a query by this field, but when I do so "2" sorts after "10",
as is the age-old comp-sci problem with treating numbers as strings. Is there a
way, maybe with some variant of "SELECT field AS", to get SQLite to treat this
data as numerical for the sake of sorting?


Cast it as numeric in your ORDER BY clause:

create table dummy (a text);
insert into dummy values (2);
insert into dummy values (10);
select a from dummy order by a;
  10
  2
select a from dummy order by cast(a as numeric);
  2
  10

Re: [sqlite] LIKE operator with prepared statements

[Eric Bohlman]

Dennis Cote wrote:

You could also do this:
SELECT x from y WHERE y.x LIKE '%' || ? || '%' ;

The || operator concatenates the % characters with your string.  Now you 
don't need to massage the string in the calling code.  Six of one, half 
dozen of the other.


Note, though, that as currently implemented (DRH has said it might 
change in the future) the concatenation will be performed for each row 
evaluated (and that particular query will guarantee a full-table scan) 
so doing it in the calling code would be a better idea if large tables 
are involved.

Re: [sqlite] Complile and connecting to SQLite

[Eric Bohlman]

Aaron Jones wrote:

Windows XP, don't know what compiler to use.


MinGW and all the common versions of Visual Studio work.



I am creating a GUI to SQLite, so need my interface to connect to SQLite,
and wanted to know what it connected to, the source code or the exe file.


SQLite is an embeddable engine (library), not a server, so you don't 
"connect to" it, you link it into your program.  The "exe file" is 
simply a command-line shell program that's been linked with the SQLite 
library.  You can link SQLite either statically or dynamically (.dll) 
the same way you can link any other library.

Re: [sqlite] Execute SQLite statements within callback

[Eric Bohlman]

mtenuta wrote:

Is this possible?  I need to either delete the current row or update a field
in the current row during the callback execution for that row.  I am using
the sqlite3 C API.


You can't insert into, delete, or update rows in a table that you're 
currently reading from.  One workaround is to create a temporary table 
from the original, read from it and do the changes on the original table.

Re: [sqlite] Extra functions - New Project?

[Eric Bohlman]

Mikey C wrote:

StdDev
Variance


When implementing these, make sure *not* to use the "textbook" one-pass 
formula (mean of the squares - square of the mean); it simply doesn't 
work properly in floating point (you can find yourself subtracting one 
large number from another and losing most of your precision).  I believe 
many versions of Excel made that mistake.  There are some numerically 
stable one-pass algorithms for computing variance; search for them if 
you don't want to make two passes.

Re: [sqlite] SQLite as R data store

[Eric Bohlman]

Andrew Piskorski wrote:

As an R user, I'm happy to see the project below, it could become a
very handy use of SQLite.  


I suspect it's well out of scope for that project, but it would be
particularly cool to eventually see some of the integration go the
other way, and allow use of SQLite's SQL engine to manipulate R data
frames (which are tables, basically) from within R, as that would be
much more powerful than R's rather limited set of data frame
manipulation functions.


You might want to take a look at DRH's proposal, 
http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
which looks like it would make it easier for the R community to 
implement the sort of interface you're talking about.

Re: [sqlite] Functions embedded in SQL statements

[Eric Bohlman]

[EMAIL PROTECTED] wrote:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
http://www.sqlite.org/lang_expr.html#corefunctions

I will admit that the SQLite website is not particularly
well indexed for human browsing.  But you can always find
things easily enough using google.  Search examples:

 site:sqlite.org ifnull
 site:sqlite.org strftime


Isn't it time (hehe) to include the date/time function documentation in 
the distribution rather than just the wiki?

Re: [sqlite] Functions embedded in SQL statements

[Eric Bohlman]

[EMAIL PROTECTED] wrote:

I'm thinking that all documentation is better placed in
a wiki.


Hmmm.  The problem I see is that it makes access to the full 
documentation contingent on connectivity to a possibility ephemeral 
external site.  Maybe the solution is to incorporate wiki snapshots into 
the distribution somehow (maybe by including an embedded server in the 
distribution, something like Sean Burke's Podwebserver that enables me 
to have the complete Perl documentation, in HTML format, sitting in a 
tab on my browser even if my Internet connection goes down).



I guess I just have a problem with the "everything you need to know is 
on someone else's machine" attitude that seems to be spreading.

Re: [sqlite] Support for ODBC?

[Eric Bohlman]

R S wrote:
  Can an application access data from SQLite via ODBC? Didn't see
documentation on the same.
If you have an ODBC driver for SQLite, yes.  The "SQLite Wrappers" page 
of the wiki mentions "ODBC driver for SQLite. 
http://www.ch-werner.de/sqliteodbc/";.  I don't know anything about it, 
but check out the URL.

Re: [sqlite] sqlite3ErrStr giving linker error

[Eric Bohlman]

Steve Frierdich wrote:
The sqlite3ErrStr function is giving me a linker error when compiled. 
All the other sqlite version 3_0_7 functions used compile correctly.  
Sqlite is compiled in to a static library, and that library is used in a 
Win32 application compiled in visual studio. The exact linker error that 
is shown when compiling the Win32 application using the library is:

unresolved external symbol "char const * __cdecl sqlite3ErrStr(int)
Can anyone give some information on maybe how to solve this? The 
sqliteint.h file is in included in the cpp file that calls the 
sqlite3ErrStr function
This is just a guess, but I think it's a pretty informed one.  Unlike 
sqlite3.h, sqliteint.h (which, as I understand it, is really for 
internal use only and shouldn't be included in anything other than the 
SQLite files themselves) doesn't test for a C++ build and wrap its 
declarations up in an extern "C" {...}.  Thus it's possible that MSVC is 
treating that function as a C++ function and "mangling" its name in the 
object file (since C++ functions can be overloaded, the compiler has to 
generate a different "mangled" name for each combination of parameters). 
 But SQLite's API is strictly C, with unmangled names, so the linker 
wouldn't be able to resolve the mangled name.

Re: [sqlite] Single-file databases: how do they do it?

[Eric Bohlman]

Cena, Resty wrote:
How do single-file databases handle multi-table databases? Do they simply pack separate physical table files into a single file, or have they found a way to store in one table all the definition and data values of a database? Can someone un-curious-ify me? Thanks.
To see how SQLite does it, check out .

Re: [sqlite] Join function in select statement

[Eric Bohlman]

Marcel Strittmatter wrote:
Hi all
I like to have a join function that I can use in a select statement like 
this:

select join(name, ',') from people;
this should produce something like this:
scott,martin,jones,adams
It is not possible to use perl or another script language. The only 
language I can use is C/C++. Is there a way to create such a function 
outside of sqlite, or do I have to extend the sqlite library?
You ought to be able to do it with a user-defined function (I'd call it 
something other than "join," though, for sanity reasons).

Re: [sqlite] Single row insert failure

[Eric Bohlman]

Roderick A. Anderson wrote:
So here is the issue.  Inserting a single row into a SQLite2 database 
doesn't work using perl on a Windows system.

I have a data in a MS SQL Server database I need to process on a Linux 
box (and getting ODBC to work isn't an option at this time) so my cheat 
is to pull the data and put it in the SQLite db mount the share and 
access the data.
  This was part of a new process so I wanted to test it using a single 
row.  I'm sure I have something missing here but it works when I run 
this and inserting multiple rows.

Here is the code.
Which can't be the actual code you're using.  Please copy and paste your 
actual code rather than retyping it.

   use DBI;
   my $dbh1 = DBI->connect( "dbi:ODBC:my.dsn", "user", "password" );
   my $dbh2 = DBI->connect( "dbi:SQLite2:dbname=reports.db", "", "" );
   my $sth1;
   my $sth2;
   $reportsdb->do( "DELETE FROM table1" );
Nowhere have you defined $reportsdb; this shouldn't run.
   $sth1 = $dbh1->prepare(
   "INSERT INTO table1 " .
   "( field1, field2, field3, field4 ) " .
   " VALUES ( ?, ?, ?, ? )" );
You're preparing an insert statement for the ODBC database, not the 
SQLite database.

   $sth2 = $dbh2->prepare( "SELECT  field1, field2, field3, field4 FROM
   table1 WHERE field1 = 'XXX'" );
   $sth2->execute();
And you're attempting to fetch records from the SQLite database, which 
you (unsuccessfully) tried to empty earlier.

   while ( my @report = $sth2->fetchrow_array() ) {
   # Just to see what is being selected
   print join(' : ', @report) . "\n";
   $sth1->execute( $report[0], $report[1], $report[2], $report[3] );
   # Changed this as part of the testing.  Works for mutiple rows
   # $sth1->execute( @report );
What do you mean by "multiple rows" here?
   }
   $dbh1->disconnect;
   $dbh2->disconnect;
After running this I looked at the reports.db with notepad, less, and 
strings.  No data, just the table definition.
Of course, since you never executed an insert statement on it.
Is there something I'm missing?  The concern is there could be just a 
single row in the MS SQL Server database.
I think you've misfocused and come up with a red herring; the problem 
doesn't appear to have anything to do with the number of rows fetched.

Re: [sqlite] field=null vs. field isnull

[Eric Bohlman]

Ara.T.Howard wrote:
i've read the 'null handling' section and still not found the answer to 
this
question:

  why should
'select * from tbl where field=null'
  be any different from
'select * from tbl where field isnull'
Because the SQL standard says so.  By definition, a null never compares 
as equal to anything, *even another null* ("null=null" is defined to 
evaluate as false).  Some people refer to this as "SQL's three-valued 
logic."

Re: [sqlite] php4/sqlite - sqlite_escape_string doesn't function

[Eric Bohlman]

Peter Jay Salzman wrote:
Hi all,
This piece of code kept giving error messages that looked like some of my
VALUES were getting parsed by php:
sqlite_query( $handle, "
INSERT INTO course VALUES (
sqlite_escape_string($semester),
sqlite_escape_string($course),
sqlite_escape_string($course_desc),
sqlite_escape_string($college),
sqlite_escape_string($reference)
)
");
You're expecting PHP to interpolate function calls within quoted 
strings.  It doesn't.  See the "Strings" section of Chapter 6 ("Types") 
of the PHP manual.

Re: [sqlite] String Concatenation

[Eric Bohlman]

Keith Herold wrote:
OwnerID, AString, Sequence
1,  'concatenate', 0
1, 'some', 1
1, 'strings', 2
1, 'together', 3
What I need to do is create a single string out of the AString,
Sequence pairs, for a given owner. Obviously I could do this through
some C++ code, but I would prefer to do it within SQL code, but can't
think of a way.  The sequences are not always the same lenght, i.e., a
particular owner may have 1, 10, or 1972 word long sequences.  With
cursors, I don't think this would be difficult, but I can't figure out
how to do this within SQLite.
Mike Chirico's tutorial 
() 
might be helpful; it looks like what you want to do is conceptually 
similar to creating the "pivot tables" he describes.

Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?

[Eric Bohlman]

Eric Scouten wrote:
James, thanks for that update. I've filed a new ticket for this issue, 
#1096 (http://www.sqlite.org/cvstrac/tktview?tn=1096). I hope you don't 
mind that I quoted your response and implementation suggestion in the 
body of that ticket.
There you express some concerns about performance.  However, it seems to 
me that the chief use case for LIMIT ... OFFSET is paging results for 
interactive display, in which case the repeated queries are going to 
occur at human-speed intervals, no more than once every few seconds. 
It's not as if the query is going to be recompiled inside a tight loop, 
so I think any speed gain would be meaningless.

That said, allowing placeholders there would make for slightly easier 
coding.  I'm not sure, though, that that's a big enough concern to 
warrant complicating SQLite's own code.

Re: [sqlite] TYPEOF in triggers

[Eric Bohlman]

Witold Czarnecki wrote:
I just tested it on 3.0.8 and - you are right - it works.
Is there any reason to use 2.8 instead of 3.0? I use SQLite via python 
(pysqlite).
3.x uses a different database file format than 2.8, and the APIs are 
sufficiently different that they'd need different Python bindings.  If 
you don't need to use database files produced by tools that are still 
using 2.x and there are Python bindings available for 3.x, then it makes 
sense to use 3.x.

Re: [sqlite] strcasecmp build error in VC++

[Eric Bohlman]

Kevin Tew wrote:
Same here, except I used the sqlite_stricmp function or something like 
that.
Could a maintainer do a quick grep for strcasecmp and fix this.
Thanks
Kevin

Tim Anderson wrote:
I got a linker error when building sqlite3.exe with Visual Studio .NET
2003, sqlite version 3.1.2. The linker could not find strcasecmp
(shell.c line 659). I replaced strcasecmp with _stricmp to get it to
build.
Please take a look at ticket 1122 
().

Re: [sqlite] Functions

[Eric Bohlman]

marco wrote:
Hi *,
Where I can find the list of the internal functions? for example: 
datetime()
strftime()
All but the date/time functions are listed in func.c in a table that's 
part of sqlite3RegisterBuiltinFunctions(); the date/time functions are 
listed in a similar table in sqlite3RegisterDateTimeFunctions in date.c.

Re: [sqlite] Does SQLite.NET not support AUTOINCREMENT

[Eric Bohlman]

[EMAIL PROTECTED] wrote:
I used this SQL:
CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
LastName TEXT, FirstName TEXT);
and using SQLite3.exe did this
SQLite3 employees.db
.read create.sql
.exit
which created my database but no tools nor the SQLite.NET provider can
read it, I get the error "malformed database schema near AUTOINCREMENT?
That really sounds as if the tools and the provider are using a pre-3.1 
version of SQLite, since AUTOINCREMENT wasn't introduced until 3.1.0.

Re: [sqlite] Bug from ticket 1141 breaks existing applications

[Eric Bohlman]

George Ionescu wrote:
while trying to upgrade to sqlite 3.1.3, I've encountered the bug 
described in ticket #1141 (sqlite returns the primary key's column name, 
if any, instead of the ROWID column, in a query like SELECT rowid, * 
FROM table).

Any idea how to fix this (if it's an easy one), since it breaks existing 
applications.
SELECT rowid AS 'rowid',* FROM table

Re: [sqlite] Prepared Statement Interface

[Eric Bohlman]

Eugene Wee wrote:
As a test, I created a database containing a single table [children]. I 
filled it with the names of 3 girls and 2 boys, and then tried to write 
a program that selects the boys and girls separately and printed their 
names.
However, I have difficulty in binding text with sqlite3_bind_text()
It appears that sqlite3_step() returns SQLITE_DONE at once, so my loop 
to print the names never runs.
[snip]
 			#define SEX "'M'"
			sqlite3_bind_text(stmt, index, SEX, sizeof(SEX), SQLITE_TRANSIENT);
Change sizeof to strlen here (and in the corresponding statement below); 
sqlite3_bind_text wants the length of the string, not the size of the 
pointer to it.

Re: [sqlite] Quoestion on Order By ... ?

[Eric Bohlman]

Shum [Ming Yik] wrote:
Actually Sqlite same as other SQL in return order such as sample (1)
( it seems  follow the insert order when no specific order by ... )
But Sqlite break the rule (may be it is not a rule), when part of the whole
Order by  string passing into the select statement ... as  sample (2)
Why not both in a same way ?
I meams:
1.) both in (descending order )
or
2.) both in ( ascending order)
I suggest you get hold of an algorithms textbook and read the section on 
sorting, paying particular attention to the distinction between "stable" 
and "unstable" sorting algorithms.  You seem to be complaining that 
SQLite offers an unstable sort, but there's nothing in the SQL standards 
to require a stable sort.

As others have said, you're skating on thin ice if you make *any* 
assumptions about the ordering of rows coming out of a SELECT other than 
those explicitly stated in an ORDER BY clause.  There's no reason any 
implementation should even be required to return rows in the same order 
on two successive identical SELECTs, and I can easily think of 
implementations that wouldn't do so (e.g. due to caching).

Re: [sqlite] beat 120,000 inserts/sec

[Eric Bohlman]

Al Danial wrote:
A scientific application I work with has clumsy data retrieval
options.  I dumped the application's output--integer and floating
point numbers--into an SQLite database and soon after began to
enjoy the power of SQL to pull out interesting results.
The main complaint for making the transfer to SQLite a permanent
part of our solution is the time it takes to insert the numbers
into a database.  It takes about a minute to insert 24 million
numbers into three tables.  Most database people (including me)
would be thrilled to see this kind of insert performance but
my colleagues are asking if it can be sped up.
Try setting PRAGMA synchronous=OFF and seeing how much improvement you 
get.  If it's significant, you'll have to decide for yourself if you can 
live with the increased risk of corruption if there's a power failure 
during a batch load.

Re: [sqlite] Multiple Tables on one Flat File

[Eric Bohlman]

[EMAIL PROTECTED] wrote:
I am running into a situation that does not make sense.
I have allocated a flat file under the Operating System as follows (notice
that autocommit is off):
  $dbh = DBI->connect('dbi:SQLite:' . $dbms_file , "", "",
  { RaiseError => 1,AutoCommit => 0 });
Then, I create multiple tables in a loop with with the sequence:
1) CREATE TABLE 
2) $sql = SQL::Abstract->new;
3) ($sql,@bind) = $sql->insert($table,\%rec);
4) $sth{$table} = $dbh->prepare($sql);
Your problem is that each new CREATE invalidates all the statement 
handles you previously prepared.

Finally, right after the loop, i do the following for one table:
1) $#bind = -1;
2) foreach $item (sort keys %rec)
{
  push(@bind, $rec{$item});
}
3) $sth{$table} -> execute(@bind);  # this is line 697 in the error message
It's not your problem, but that's rather bizarre Perl; use the slice, Luke:
@bind = @rec{sort keys %rec);
4) Sometime later, I do the $dbh->commit().
When I run my loop with only one table, it works.  When I run the loop with
multiple tables, it fails on the following message:
DBD::SQLite::st execute failed: database schema has changed(1) at dbdimp.c
line 389 at ./sarparsed.pl line 697.
DBD::SQLite::st execute failed: database schema has changed(1) at dbdimp.c
line 389 at ./sarparsed.pl line 697.
It works fine with only one table because your only active statement 
handle was prepared *after* you stopped changing the schema.

I suspect that I will need to have multiple $dbh (as in $dbh{$table}) but I
don't want to connect to multiple Operating System files, just one.
am I on the right track?
Nope.  I confirmed the problem by creating the following test case:
#!perl
use strict;
use warnings;
use DBI;
unlink 'dummy.db';
my $dbh=DBI->connect(
  'dbi:SQLite:dummy.db',"","",{RaiseError=>1,AutoCommit=>0});
$dbh->do('create table t1 (a integer,b integer)');
my $sth1=$dbh->prepare('insert into t1 values (?,?)');
$dbh->do('create table t2 (c integer,d integer)');
my $sth2=$dbh->prepare('insert into t2 values (?,?)');
$sth1->execute(1,2); # this fails because $sth1 is now stale
$sth2->execute(3,4); # this succeeds because $sth2 is still fresh
$dbh->commit();
$dbh->disconnect();
This will fail, but moving the preparation of $sth1 to after the second 
CREATE will make it succeed.

Re: [sqlite] Multiple Tables on one Flat File

[Eric Bohlman]

D. Richard Hipp wrote:
Not only CREATE statements, but also DROP and VACUUM statements
will also invalidate all previously prepared statements.  Once a
prepared statement is invalidated, it must be prepared again.
And also ALTER TABLE.

Re: [sqlite] Multiple Tables on one Flat File

[Eric Bohlman]

[EMAIL PROTECTED] wrote:
I ended up creating a %dbh hash with $table as the index along with one
Operating System file for each table.
I was hoping there was a way not to create so many Operating System files
because of the extra Administration they require.
can you think of any way around this?
I read all my data from a log and I am creating multiple Tables based on
the content of the data in the log.
Each time you create a table, squirrel away the text of the INSERT 
statement (and any other table-related queries) by storing it in a hash 
keyed by the table name or the like.  Then, when you're done creating 
the tables, loop over the stored queries creating prepared sth's.

Re: [sqlite] request for additions to sqlite 3.xx

[Eric Bohlman]

basil thomas wrote:
As for storing queries, I'm not sure how useful this feature is given
that the database engine itself is part of your program.  For simple
queries your best bet is a static sqlite3_stmt object, which you prepare
once at program initiation and refer back to each time it is needed.
Just remember to reset the statement after each query execution.  For
more complex logic you can couple this trick with functions that choose
which query to execute.
Clay Dowling
Yes I am already creating static statement objects at program init time.
That is not the problem. I just do not want  the SQL source in my source
code
as i would like to control the SQL source seperately inside the database
I can use all of the c++ functions and do whatever I want. That is not my
point either.
I am just asking for a standard way to have stored procedures that do
exactly the
the same simple queries as I am creating now but just implemented as another
database
object like triggers. The triggers in SQLite are simpled stored procedures
fired by SQLite.
I just want my own stored procedures fired by my user code. This is a just a
simple request
that I think others would find usefull. I am already implementing all the
other features in my code
that simulates a single process/multi-threaded server database. We are using
SQLite just as a storage engine as we are fully implementing an xml database
on top of SQLite.
What's the objection to reading your SQL source out of the database and 
preparing it at program startup?

Re: [sqlite] Sqlite 3.2.2 ?

[Eric Bohlman]

Massimo Gaspari wrote:

Dear DRH,

I am using Sqlite in a Microsoft Windows environment with DevCpp + MinGW (a
GCC porting). I lack the UNIX build
facilities so I find very helpful the availability of a zip source archive
in which all the preprocessing is alredy done.

Now I can observe a lot of check-ins since the 3.2.1 was released.

Do you know when a 3.2.2 version with all  bug fixing will be available. Is
there a forecast for 3.2.2?


If you install MSys, you'll have all the necessary tools to build SQLite 
under MinGW.

Re: [sqlite] Re: philosophy behind public domain?

[Eric Bohlman]

Andreas Rottmann wrote:

Florian Weimer <[EMAIL PROTECTED]> writes:



* D. Richard Hipp:



Public domain just seemed the easiest way to go.


It is, until you want to incorporate a contribution from someone who
can't give up his copyrught in a binding way.  How do you handle
contributions from Europe, especially Germany?  Or hasn't that
happened yet?



This is a point that always stroke me about "public domain": There is
no such thing as "disclaiming copyright" in Europe (or at least
Germany and Austria).


This is a rather sticky point.  It's unlikely that someone who 
unofficially "disclaimed copyright" would willingly change his mind 
afterwards, but that assumes ideal circumstances.  In the Real World, 
people sometimes die, get divorced, or get sued by people they owe money 
to.  It seems to me that if someone from a country that doesn't 
recognize voluntary relinquishment of copyright (and, AFAIK, that's most 
countries) contributes code to SQLite or something similar, his heirs, 
ex-spouse, or creditors could end up with a proprietary interest in part 
of the code.  Not good.

Re: [sqlite] Merging to tables

[Eric Bohlman]

Rasmus Christian Kaae wrote:

I was wondering if there was some way of merging to tables *fast* for querying?
I have the following schemas:

CREATE TABLE a (term text, location blob);
CREATE TABLE b (term text, location blob);

What I would like to do is to iterate through *all* records in a and b sorted by
term (records from a and b should be interleaved if necessary).


SELECT term,location FROM a UNION ALL SELECT term,location FROM b ORDER 
BY term;