Re: [sqlite] Using SQLite for GIS Application

[Derrell Lipman]

On Sun, Aug 23, 2009 at 01:08, Itzchak Raiskin wrote:

> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this
> will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
>

The "rtree" extension may be of use to you.
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README
More information is available here: http://en.wikipedia.org/wiki/R-tree

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

Re: [sqlite] Reading pragma's results

[Derrell Lipman]

On Mon, May 25, 2009 at 1:10 PM, João Eiras  wrote:
> Btw, the same happens with the command line sqlite program
>
> $ sqlite
> SQLite version 2.8.17

These pragmas didn't exist six years ago when 2.8.17 was current. The
only pragmas in that version are:

default_cache_size
cache_size
default_synchronous
synchronous
temp_store
default_temp_store

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

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

[Derrell Lipman]

On Sun, May 3, 2009 at 12:32 PM, scientist scientist <
scientist92...@yahoo.com> wrote:

> Thank you for your fast answer, but my problem still exists after I
> followed your instructions.
>
> Firstly I removed the default sqlite3 using sudo apt-get remove sqlite3
> command.
> After that I moved to the sqlite-3.6.13 folder which was extracted from
> sqlite-amalgamation-3.6.13.tar.gz and executed the 3 commands:
> ../configure --prefix=/usr/local
> make
> sudo make install
> I did check the PATH variable and It did contain /usr/local/bin.
> However, when I entered
> sqlite3
> the output was still 3.4.2
> The weird thing is that, after executing all of these above commands, and
> then sudo apt-get remove sqlite3, I got the following message:
> Package sqlite3 is not installed, so not removed
> I can't understand what's going on.
>

Did you type "which sqlite3" to figure out where it's find it? Wherever that
one is, you need to remove it.

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

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

[Derrell Lipman]

On Sun, May 3, 2009 at 11:37 AM, scientist scientist <
scientist92...@yahoo.com> wrote:

> Hi all,
> My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now
> I want to upgrade sqlite3 to its newest version.
> I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the
> package and run the following commands:
> ../configure
> make
> sudo make install
> But when I enter
> sqlite3
> the output result is still version 3.4.2
>
> How can I upgrade sqlite3 to its latest version 3.6.13?
>

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

Now rerun configure like this, to specify the install prefix, and then
rebuild and reinstall:

  ../configure --prefix=/usr/local
  make
  sudo make install

If you're running csh type "rehash" to rescan your path.

Ensure that /usr/local/bin is in your path:

  echo $PATH

If not, add /usr/local/bin to your path (typically before /usr/bin in the
list) in your shell start-up file.

You can also type "which sqlite3" to see which version your shell is finding
first in the PATH.

Cheers,

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

Re: [sqlite] DateTime Objects

[Derrell Lipman]

On Sat, Feb 28, 2009 at 1:47 PM, jonwood  wrote:

>
> Greetings,
>
> I have a SQLite table that contains a DATETIME value. However, the database
> does not appear to provide enough control over how that value is formatted.
> For example, I'd like a two-digit year, to eliminate leading zeros, and to
> show time using AM/PM format.
>

http://sqlite.org/lang_datefunc.html

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

Re: [sqlite] Any concept of row number in SQLite?

[Derrell Lipman]

On Thu, Feb 19, 2009 at 10:45 PM, His Nerdship
wrote:

>
> Thank you Thomas, that was most helpful.
> I have just found the ROWID information
> (http://sqlite.org/lang_createtable.html#rowid) - this is what I was
> looking
> for!  You have to know something exists before you can look for it
> I take it this value will not change, even if the contents of its row
> change?
>

Be careful. Yes, that value *can* change if you depend on the implicit
ROWID. Specifically, if you delete rows and then VACUUM the rows will be
renumbered. You can avoid that with:

>
> I can create my own INTEGER PRIMARY KEY column, which becomes an alias for
> the ROWID, but is there a way I can get the 'original' (I don't want to add
> a column at this stage)?


If you use your own INTEGER PRIMARY KEY then it becomes the ROWID and it
will not change upon vacuum.

>
> If I call sqlite3_get_table(), it returns that ***result array (I'm using
> C++), but AFAIK that doesn't contain the ROWID.
>

Just request it explicitly:

  SELECT ROWID, * FROM table_name;

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

Re: [sqlite] Single quote, newlines and UPDATES

[Derrell Lipman]

>
>
> and I did an SQL command dump of the string that I am passing to the D
> SQLite wrapper and it is escaped.  I am reclining on the idea that it is
> the
> D wrapper.  I have to go to post this on the D wrapper newsgroup.
>

My understanding is that you can call C library functions from D. You
should, therefore, not need whatever the D wrapper is, and instead can call
the native sqlite3 library directly. Might be worth a try since you have
doubts about your current wrapper.

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

Re: [sqlite] Transaction?

[Derrell Lipman]

On Wed, Dec 31, 2008 at 11:36 AM, John Hawley
wrote:

> the following block of code executes 2 or 3 orders of magnitude slower
> than 1 think it should ( the db was opened in earlier code)
>

Yup, you're committing each insert to disk (a VERY slow process) rather than
waiting until you've inserted all 1 entries before committing to disk.
Insert code at the following marked places for dramatically increased
performance:

>
>RecOffset=sizeof(rec);
>fseek(infile,126,SEEK_SET);// data starts at byte 126
>

  /* Begin a transaction */
  rc = sqlite3_exec(db, "BEGIN;", NULL, 0, );
  if (rc != SQLITE_OK) { /* do error handling */ }

   while (fread(,sizeof(rec),1,infile))
>{
>iSeq++;
>GridY= (long) rec.lat*LATGRIDFACTOR + 0.5;
>GridX= (long) rec.longt*LongGridFactor(rec.lat) + 0.5;
>iGS =GridX*1000 + GridY;
>sprintf_s(SqlStr,200,"INSERT into soundings
> VALUES(%lf,%lf,%f,%s,%d,%ld);",rec.lat,
>rec.longt,rec.depth,DateStr,iSeq,iGS);
>rc= sqlite3_exec(db,SqlStr,NULL,0,);
>if(rc!=SQLITE_OK)
>{
>MessageBox(hwnd,zErrMsg,"SQL Error adding soundings
> data",MB_OK);
>

  ./* Rollback the transaction.  No need for error checking as journal
will recover next time anyway. */
  (void) sqlite3_exec(db, "ROLLBACK;", NULL, 0, NULL);

>exit(1);
>}
>
>}
>

  /* Commit the transaction.  This will be your single slow operation
rather than 1 of them */
  rc = sqlite3_exec(db, "COMMIT;", NULL, 0, );
  if (rc != SQLITE_OK) { /* do error handling */ }

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

Re: [sqlite] Open the database - Creating the empty database

[Derrell Lipman]

On Mon, Dec 15, 2008 at 1:43 PM, P Kishor  wrote:

> On 12/15/08, Joanne Pham  wrote:
> > Hi All,
> >  I have this problem about open the database. Here is the detail about
> the problem.
> >
> >  Our application have one process to create the database and another
> process to open the database and creating the report.
> >  The problem here is the database is not created but if the second
> process has tried to access the database then the empty database is created
> which has the size of 0. So the question is there any way the open database
> API should return an error message instead of creating the empty database
> when the second process opens the database.
> >
> >  Thanks,
>
>
> A SQLite database is just a file on the hard disk. Test for the
> existence of the file before trying to open it.
>

That is, unfortunately, a recipe for race conditions.  Application A tests
for whether the file exists and finds it doesn't.  The operating system task
switches to Application B which had previously determined that the file
doesn't exist, and now creates it and adds some data.  Task switch back to
Application A which assumes that the file doesn't exist (since it had
previously determined that) and scribbles over the data just written by
Application B.

An alternative is to open the database normally (sqlite3_open) and issue the
queries

  BEGIN EXCLUSIVE;
  SELECT 1 FROM sqlite_master LIMIT 1;

If you get back a value (1), then the database had already has tables (or
triggers or ...) in it.  If you get back NULL then you can go ahead and
create the database tables et al.  When done creating tables and doing
whatever else you need to do before allowing other applications access,
issue a COMMIT.  Since you had an exclusive transaction, any other
application trying to determine if the database is available or not will
block until your transaction completes.

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

Re: [sqlite] About select by "=" condition to a string

[Derrell Lipman]

On Fri, Oct 31, 2008 at 9:06 AM, yoky <[EMAIL PROTECTED]> wrote:

> Hi all,
>I create a table like this "create table tbl1 (ID integer primary
> key, name, addr)" ,
>then insert a record:
>   "insert into tbl1 values(1, 'aa', 'bb')",
>   select the record:
>   "select * from tbl1 where name ='aa' "   ,can get this  record :
> (1, 'aa', 'bb')
>   I change insert way like this:
>   char *pName = "aa";
>   sqlite3_prepare(db," insert into tbl1(1,?,'bb')",-1, , 0);
>   sqlite3_bind_blob( stat, 1, pName,  strlen(pName)+1, 0 );
>   sqlite3_step(stat);
>   Then select the record:
>   sqlite3_prepare(db,  "select * from tbl1 where name ='aa' ", -1,
> , 0);
>sqlite3_step(stat);
>   By this way, I can not get the record I want : (1, 'aa', 'bb').
>   Change the SQL statement by  "like":
>   sqlite3_prepare(db,  "select * from tbl1 where name  like  'aa'
> ", -1, , 0);sqlite3_step(stat);
>  I can get the record.
>  Why? and How can I select record by "=" condition to a string?


You are binding a blob and specifying a length of *three*.  strlen(pName)
returns 2 which is the proper length, but you're adding 1 to that.
Therefore the column contains three characters, 'a', 'a', '\0' which does
not equal "aa" but does begin with "aa".

So you want to either use the correct length for the blob (assuming you
really need blobs) or bind a type other than blob.

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

Re: [sqlite] Help Using RowID

[Derrell Lipman]

On Sat, Sep 6, 2008 at 10:44 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> > As a bonus, if the implementation of SQLite changes, SQL itself won't,
> > so id will _still_ be a valid integer primary key, even if the
> > implementation detail of rowid changes.
>
> I promise that INTEGER PRIMARY KEY will always be an alias for the
> rowid in SQLite.  This will not change.


I recall there was an issue a while back where the rowid of a record could
change if the table was vacuumed and it did not have an explicit INTEGER
PRIMARY KEY field.  If that's still (or ever was, assuming I'm remembering
correctly) the case, then having an explicit INTEGER PRIMARY KEY is
important if you want to reference the single-field rowid (by whatever name)
and assume it won't change over the life of the record.

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

Re: [sqlite] Default Column Value to Local Time

[Derrell Lipman]

On Mon, Sep 1, 2008 at 10:29 PM, Derrell Lipman <
[EMAIL PROTECTED]> wrote:

>
>
> On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:
>
>>
>> Okay, I give up. I've been searching for a while now. I've found a number
>> of
>> discussions about how CURRENT_DATE returns the date in UTC, and that it
>> can
>> be converted to local time. But I've yet to find one article that clearly
>> states how one might do this.
>>
>> I would love to have a table column default to the current date, but I
>> want
>> it to be the date where the computer is located, rather than some far off
>> place.
>>
>> Can anyone tell me if this is possible?
>
>
> I think this is the page you're looking for:
>   http://www.sqlite.org/lang_datefunc.html
>
> In particular, a query that returns the current time in the local (to
> sqlite) time zone is:
>SELECT datetime('now', 'localtime');
> or for just the date:
>SELECT date('now', 'localtime');
>
> Note, however, that if sqlite is being accessed by your web server,
> 'localtime' is in reference to the web server's time zone, not the time zone
> of the web browser which could be anyplace in the world.  If your
> application is running all locally, the above should work.
>

I guess I didn't quite complete the picture here.  I don't think you can use
functions as default values, but you can easily use triggers to accomplish
the same thing, like this:

sqlite> CREATE TABLE x (i INTEGER PRIMARY KEY, t TIMESTAMP);
sqlite> CREATE TRIGGER x_insert_tr
   ...>   AFTER INSERT ON X
   ...>   FOR EACH ROW
   ...>   BEGIN
   ...> UPDATE x
   ...>   SET t  = datetime('now', 'localtime')
   ...>WHERE i = new.i;
   ...>   END;
sqlite> INSERT INTO x (i) VALUES (1);
sqlite> INSERT INTO x (i) VALUES (2);
sqlite> INSERT INTO x (i) VALUES (3);
sqlite> SELECT * FROM x;
1|2008-09-01 23:32:49
2|2008-09-01 23:32:55
3|2008-09-01 23:33:01
sqlite>

Those times shown above are when I ran the INSERT queries, in my local time
zone.

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

Re: [sqlite] Default Column Value to Local Time

[Derrell Lipman]

On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote:

>
> Okay, I give up. I've been searching for a while now. I've found a number
> of
> discussions about how CURRENT_DATE returns the date in UTC, and that it can
> be converted to local time. But I've yet to find one article that clearly
> states how one might do this.
>
> I would love to have a table column default to the current date, but I want
> it to be the date where the computer is located, rather than some far off
> place.
>
> Can anyone tell me if this is possible?


I think this is the page you're looking for:
  http://www.sqlite.org/lang_datefunc.html

In particular, a query that returns the current time in the local (to
sqlite) time zone is:
   SELECT datetime('now', 'localtime');
or for just the date:
   SELECT date('now', 'localtime');

Note, however, that if sqlite is being accessed by your web server,
'localtime' is in reference to the web server's time zone, not the time zone
of the web browser which could be anyplace in the world.  If your
application is running all locally, the above should work.

Cheers,

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

Re: [sqlite] Clone SQLite databases

[Derrell Lipman]

On Tue, Aug 5, 2008 at 10:31 AM, csabi81 <[EMAIL PROTECTED]> wrote:

>
> I have made the test with following results:
> Useing "SELECT ALL * FROM  WHERE... order by id;" on the
> original
> database and
> "SELECT ALL * FROM  order by id;" on the cloned database give me
> the same results.
> outputing the results from SELECT in different files give me two 100%
> identical files.


You might also try doing ".dump" on each database from the sqlite shell and
compare the CREATE entries for tables, triggers, etc. to ensure that they
were created identically.

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

Re: [sqlite] Corrupted database repairing

[Derrell Lipman]

On Fri, Jul 25, 2008 at 5:23 AM, Alexey Pechnikov <[EMAIL PROTECTED]>
wrote:

> > > Database servers such as Oracle or PostgreSQL have transactions log and
> > > restore log.
> >
> > Those logs are effectively duplicates of the data or ways of
> > reconstructing the data.  SQLite has a transaction log for the last
> > transaction only and only while it is progress.
>
> Can I get full log of sql statements for to sent it other network or store
> to
> outher device?


Maybe sqlite3_trace() or sqlite3_profile() can help with what you're looking
for here.

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

Re: [sqlite] What is quicker?

[Derrell Lipman]

On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> Let me strongly reiterate that you look into using the new R-Tree
> virtual table available for SQLite.  R-Trees are specifically designed
> to do exactly the kind of query you are asking to do.  See
>
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.2
>

The README contains this text:

 CREATE VIRTUAL TABLE  USING rtree()

For example:

  CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, ymax);
  CREATE VIRTUAL TABLE boxes USING rtree(1, 1.0, 3.0, 2.0, 4.0);

Is that last line supposed to be
  INSERT INTO boxes VALUES (1, 1.0, 3.0, 2.0, 4.0);
?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[Derrell Lipman]

On Fri, May 2, 2008 at 11:56 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> However, as we have gained experience with the VFS, we have come to
> realize that the original VFS design has some warts.  In particular,
> not all of the methods in the VFS interface are able to return the
> errors that some systems want to return.  For example, the xAccess()
> method is used to determine whether or not a file exists.  On Unix and
> win32, the system calls to determine whether or not a file exists
> cannot fail as long as the call is well-formed (i.e. you give it a
> valid file descriptor.) The operating system will always be able to
> give you a straight yes/no answer about whether or not a file exists.
> And so we made no provisions in the VFS design for the xAccess()
> method to return an error code.  Since then, we have found that some
> embedded platforms do not work this way and that it is sometimes
> necessary for xAccess() to fail and say "I don't know".  This can
> happen, for example, when the  system is unable to allocate memory for
> an IPC buffer.


If you're going to be making changes in this area anyway...

The use of access() at all is really quite dangerous.  It causes a race
condition.  It is quite possible that at the time that access() is called, a
file can not exist, and when the file is then opened for creation, it can
already have been created by a different process (or vice versa).  It is
much safer to never use access() to determine whether you "can" do
something, but rather to actually try to "do" the operation, and test for
success or failure.  This avoid the possible race.

Cheers,

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

Re: [sqlite] selecting uncommited rows

[Derrell Lipman]

On Thu, Apr 17, 2008 at 3:15 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> I will give a simple example:
>
> create table t1(name);
> insert into t1('Alex');
> begin;
> insert into t1 values ('Dennis');
> select * from t1;
>
> The above will show two rows. How can I see only the 'Dennis' row in this
> simple example.
>

Here's one possibility:

sqlite> CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
sqlite> INSERT INTO t1(name) VALUES ('Alex');
sqlite> BEGIN;
sqlite> CREATE TEMPORARY TABLE start AS
   ...>   SELECT COALESCE(MAX(id), 0) AS v FROM t1;
sqlite> INSERT INTO t1(name) VALUES ('Dennis');
sqlite> SELECT name FROM t1 WHERE id > (SELECT v FROM start);
 name = Dennis
sqlite>

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

Re: [sqlite] sqlite with emacs (OT)

[Derrell Lipman]

On Wed, Apr 2, 2008 at 10:30 AM, Wensui Liu <[EMAIL PROTECTED]> wrote:

> good morning, all,
> when i tried to use sqlite in emacs with shell mode, it doesn't work.
> is there a interface for sqlite in emacs / xemacs?
>

I use sqlite in emacs shell mode all the time.  What problem are you
encountering?  (Note that sqlite's built-in readline history mechanism won't
be available, but shell mode's own history will be.)

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

Re: [sqlite] Clear screen in command line interface

[Derrell Lipman]

On Wed, Mar 26, 2008 at 4:14 PM, Fred J. Stephens <[EMAIL PROTECTED]> wrote:
> Mike McGonagle wrote:
>  > Hum, on the Mac (10.4.9), the terminal window can be cleared from within 
> the
>  > terminal program itself. I would imagine that other terminal programs also
>  > provide this. Does this functionality really need to be in SQLite?
>  > Mike
>  You're right I guess. In Konsole under KDE at least, there is a "Clear
>  Terminal" command.
>  With SQLite though it leaves you with a blank screen and no prompt.
>  Hitting enter brings back the prompt, but at the BOTTOM of the screen.
>  Better than nothing I guess. I just thought it would probably be trivial
>  to add this to SQLite's command line program.

In konsole (and xterm, I'm pretty sure), you can just hit CTRL-L to
clear the screen whenever you're at an sqlite> prompt.

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

Re: [sqlite] SELECTing calls < last two weeks?

[Derrell Lipman]

On Tue, Mar 25, 2008 at 11:02 AM, Gilles Ganault <[EMAIL PROTECTED]> wrote:
> Hello
>
>  In MySQL, I used the following to only get calls received in the last
>  two weeks:
>
> WHERE calls_date > DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY)
>
>  Is there an equivalent in SQLite, or should I perform this test in
>  PHP?

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] algorithm for adding columns to a table

[Derrell Lipman]

On Tue, Mar 18, 2008 at 1:03 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Yes. I did this in my custom version of SQLite. If statement is ALTER TABLE,
>  and SQLite returns error, I check if it´s ALTER TABLE (t) MODIFY COLUMN ou
>  DROP COLUMN, doing the exact flow you did.

Thanks!

Derrell


>
>
>
>  -Original Message-
>  From: [EMAIL PROTECTED]
>  [mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman
>  Sent: terça-feira, 18 de março de 2008 11:26
>  To: General Discussion of SQLite Database
>  Subject: [sqlite] algorithm for adding columns to a table
>
>  I could use another set of eyes or three to verify that this algorithm
>  makes sense.  I have legacy sqlite2 databases for which I need a
>  generic function to add columns to tables.  This is the pseudocode for
>  the function.  Am I missing anything?
>
>  In this pseudocode, the parameters are:
>
>   :tableName:
>  The table name being altered
>
>   :newColumnDefinition:
>  The complete text of the new column description, as if it were in a
>  CREATE TABLE command, e.g. "t TIMESTAMP DEFAULT '2008-03-18 10:08:47'"
>
>   :newColumnValue:
> The value to insert into the new column as we add the new column
>  to the table.
>
>  Of course, all queries need error checking which is not included in
>  the pseudocode.  Errors cause an immediate rollback.
>
>  Pseudocode follows...
>
>
>  // If anything fails, ensure we can get back to our original
>  query("begin;")
>
>  // Get the sql to generate the table
>  tableDef = query(
>"SELECT sql
>   FROM sqlite_master
>   WHERE tbl_name == :tableName:
> AND type = 'table';"
>  )
>
>  // Get the indexes associated with this table, excluding automatic indexes
>  indexes = query(
>"SELECT sql
>   FROM sqlite_master
>   WHERE tbl_name == :tableName:
> AND type = 'index'
> AND length(sql) > 0;"
>  )
>
>  // Get the triggers associated with this table
>  triggers = query(
>"SELECT sql
>   FROM sqlite_master
>   WHERE tbl_name == :tableName:
> AND type = 'trigger'
> AND length(sql) > 0;"
>  )
>
>  // Copy all of the data to a temporary table
>  query("CREATE TEMPORARY TABLE __t AS SELECT * FROM :tableName:;")
>
>  // Drop the table being altered
>  query("DROP TABLE :tableName:;")
>
>  // Copy the original table definition so we can modify it
>  sql = tableDef.sql;
>
>  // Find the trailing right parenthesis in the original table definition
>  p = strrchr(sql, ')');
>
>  // Where the right parenthesis was, append a comma and new column definition
>  *p++ = ',';
>  strcpy(p, :newColumnDefinition:);
>  strcat(p, ");");
>
>  // Recreate the table using the new definition
>  query(sql);
>
>  // Copy the data from our temporary table back into this table.
>  query("INSERT INTO :tableName: SELECT *, :newColumnValue: FROM __t;")
>
>  // We don't need the temporary table anymore
>  query("DROP TABLE __t;")
>
>  // Recreate the indexes
>  foreach index in indexes
>  {
> query(index.sql)
>  }
>
>  // Recreate the triggers (after having copied the data back to the table!)
>  foreach trigger in triggers
>  {
> query(trigger.sql)
>  }
>
>  query("commit;")
>
>
>  Thanks for any comments you can provide!
>
>  Derrell
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
"There is nothing more difficult to plan, more doubtful of success,
nor more dangerous to manage than the creation of a new system. For
the initiator has the enmity of all who would profit by the
preservation of the old system and merely lukewarm defenders in those
who would gain by the new one." --Machiavelli, 1513
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] algorithm for adding columns to a table

[Derrell Lipman]

I could use another set of eyes or three to verify that this algorithm
makes sense.  I have legacy sqlite2 databases for which I need a
generic function to add columns to tables.  This is the pseudocode for
the function.  Am I missing anything?

In this pseudocode, the parameters are:

  :tableName:
 The table name being altered

  :newColumnDefinition:
 The complete text of the new column description, as if it were in a
 CREATE TABLE command, e.g. "t TIMESTAMP DEFAULT '2008-03-18 10:08:47'"

  :newColumnValue:
The value to insert into the new column as we add the new column
to the table.

Of course, all queries need error checking which is not included in
the pseudocode.  Errors cause an immediate rollback.

Pseudocode follows...


// If anything fails, ensure we can get back to our original
query("begin;")

// Get the sql to generate the table
tableDef = query(
   "SELECT sql
  FROM sqlite_master
  WHERE tbl_name == :tableName:
AND type = 'table';"
)

// Get the indexes associated with this table, excluding automatic indexes
indexes = query(
   "SELECT sql
  FROM sqlite_master
  WHERE tbl_name == :tableName:
AND type = 'index'
AND length(sql) > 0;"
)

// Get the triggers associated with this table
triggers = query(
   "SELECT sql
  FROM sqlite_master
  WHERE tbl_name == :tableName:
AND type = 'trigger'
AND length(sql) > 0;"
)

// Copy all of the data to a temporary table
query("CREATE TEMPORARY TABLE __t AS SELECT * FROM :tableName:;")

// Drop the table being altered
query("DROP TABLE :tableName:;")

// Copy the original table definition so we can modify it
sql = tableDef.sql;

// Find the trailing right parenthesis in the original table definition
p = strrchr(sql, ')');

// Where the right parenthesis was, append a comma and new column definition
*p++ = ',';
strcpy(p, :newColumnDefinition:);
strcat(p, ");");

// Recreate the table using the new definition
query(sql);

// Copy the data from our temporary table back into this table.
query("INSERT INTO :tableName: SELECT *, :newColumnValue: FROM __t;")

// We don't need the temporary table anymore
query("DROP TABLE __t;")

// Recreate the indexes
foreach index in indexes
{
query(index.sql)
}

// Recreate the triggers (after having copied the data back to the table!)
foreach trigger in triggers
{
query(trigger.sql)
}

query("commit;")


Thanks for any comments you can provide!

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

Re: [sqlite] How long time to index this table

[Derrell Lipman]

On Thu, Feb 28, 2008 at 11:48 AM, Lars Aronsson <[EMAIL PROTECTED]> wrote:
> Gilles Ganault wrote:
>
>  > But then, how many people use SQLite to handle 68 milions rows
>  > in a table?

I've got 60 million in one table, and this is with an sqlite2
database.  Works just fine, BTW.

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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Derrell Lipman]

On Wed, Feb 27, 2008 at 6:23 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi all,
>  Thx for the response!
>  On different Linux server. So how to find out what is the SQLite version on 
> the server which has the problem.
>  How to find out the sqlite version using Linux commnad.

Doing it with a Linux command is not entirely reliable, since the
Linux command could be staticly linked with a different sqlite library
than the shared one your application is finding.  You can try it,
though:

% sqlite3 :memory:
sqlite> select sqlite_version();

or even:

% echo "select sqlite_version();" | sqlite3 :memory:

More reliable than using the command line tool (sqlite3) is to have
your application issue a "SELECT sqlite_version();" and see what
version is returned.

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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Derrell Lipman]

On Wed, Feb 27, 2008 at 12:34 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
>  I used the   sqlite3_prepare_v2 in my code and some of the servers are ok 
> but one of my server return an error message that "undefined symbol: 
> sqlite3_prepare_v2 " when the process is started and access the database 
> using sqlite3_prepare_v2 . I don't have any clue why this problem is only 
> happened on one single server but no others. Please help if you have an answer

It sounds like you're linking for shared library (or DLL) and the
version of sqlite3 on that one system is very old: it predates the
sqlite3_prepare_v2 function being added to sqlite.  I believe there's
an sqlite3_version() function you can call to find out what version of
the library your application is finding.

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

Re: [sqlite] Two commands at once

[Derrell Lipman]

On Feb 16, 2008 12:01 PM, Fred J. Stephens <[EMAIL PROTECTED]> wrote:
> How can I issue 2 commands to SQLite at the same time in a shell script?
> I want to temporarily change the separator for the returned data,
> something like:
> sqlite3 database.db ".separator " ", SELECT first,last FROM address;"

sqlite3 database.db <<'EOF'
.separator " "
SELECT first, last FROM address;
EOF


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

Re: [sqlite] default values at CREATE TABLE

[Derrell . Lipman]

"Frank Pool" <[EMAIL PROTECTED]> writes:

> I want to create a table with two colums:
>
> One ist the primary key (test_num)
> and the second column sholud contain the value of the primary key (maybe as
> a string) by default.
> How can I define this table in sql ?
>
> CREATE TABLE test_table ("test_num integer primary key AUTOINCREMENT NOT
> NULL, test_name varchar(256) DEFAULT ??? NOT NULL,")
>
> Any ideas ?

With the current version of sqlite3, you can do this.

CREATE TABLE test_table
  (
test_num INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
test_name VARCHAR(256)
  );

CREATE TRIGGER test_table_tr
  AFTER INSERT ON test_table
  BEGIN
UPDATE test_table
  SET test_name = test_num
  WHERE test_num = new.test_num;
  END;

INSERT INTO test_table (test_num) VALUES (23);
INSERT INTO test_table (test_num) VALUES (42);

.mode line
SELECT * FROM test_table;

This yields:

 test_num = 23
test_name = 23

 test_num = 42
test_name = 42

Derrell

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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Derrell . Lipman]

Brandon Eley <[EMAIL PROTECTED]> writes:

> No, I can't upgrade to PHP5, yet. We're on PHP4.

I was accessing sqlite 2.8.x databases with PHP4 a number of years ago.  It
worked fine.  The page I referred you to previously _initially_ discusses the
object interface for the sqlite functions.  That, AFAIR, didn't exist in php4.
The sqlite_* functions that are listed further down, however, did exist and
worked fine.

Try this as an alternate starting point:

  http://us.php.net/manual/en/function.sqlite-open.php

Just ignore the Object interface that is described for each function, and
stick with the sqlite_*() functions.

You might try running a script with phpinfo() to ensure that sqlite was
compiled into your build.  I recall it was compiled by default, but it's
conceivable it was left out of your build.

Cheers,

Derrell

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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Derrell . Lipman]

Brandon Eley <[EMAIL PROTECTED]> writes:

> Thank you for this information. How would I access the 2.8.x database  from
> within PHP? is it even possible?

Go to http://www.php.net.  Type "sqlite" into the search box at the top.  The
page it brings you to describes the sqlite 2.8.x interface.  The PECL
extension that you referenced is for accessing the newer 3.x databases.

Derrell

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

Re: [sqlite] Getting a list of items on a column

[Derrell . Lipman]

"jose isaias cabrera" <[EMAIL PROTECTED]> writes:

> "C.Peachment" wrote,
>
>
>> On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote:
>>
>>
>>>Greetings!
>>
>>>imagine this data content,
>>
>>>c1, c2, c3
>>>a, 1, 2
>>>b, 3, 4
>>>c, 5, 6
>>>a, 9, 8
>>>a, 1, 9
>>>c, 1, 3
>>
>>> I would like to get a list of all the items in c1.  So, the return would be,
>>
>>>a,
>>>b,
>>>c
>>
>>>I know how to do it programmatically, but is there a way to get it right
>>>from SQLite?
>>
>> select distinct c1 from tablename;
>
> Thanks.
>
> Ok, I am going to push my luck.  :-)  Imagine the same data, what if I wanted
> to get the sum of the other two columns also?  I know how to do it one at a
> time, so in this instance I would have to do 3 separate call using sum or
> total, but is there a way that I could do it on the same call and get the sum
> of columns c2 and c3, so that I would get something like this,
>
> a,11,19
> b,3,4
> c,6,9
>
> Yes, I know.  I am pushing my luck. :-)

How about something like

  SELECT c1, SUM(c2), SUM(c3)
FROM tablename
GROUP BY c1;

Cheers,

Derrell

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

Re: [sqlite] Index creation on huge table will never finish.

[Derrell . Lipman]

Chris Jones <[EMAIL PROTECTED]> writes:

> Derrell.Lipman wrote:
>> 
>> Chris Jones <[EMAIL PROTECTED]> writes:
>> 
>> I don't think that your original solution solves that problem either.  You
>> first posted this schema:
>> 
>>> My schema looks as follows:
>>> 
>>> CREATE TABLE rawfen ( fen VARCHAR(80) );
>>> CREATE INDEX rawfen_idx_fen ON rawfen(fen);
>> 
>> but you didn't indicate that rawfen_idx_fen is a UNIQUE INDEX so it won't
>> complain if there are duplicate strings.  To accomplish this (but not
>> solving
>> your timing issue), you need this:
>> 
>>   CREATE TABLE rawfen ( fen VARCHAR(80) );
>>   CREATE UNIQUE INDEX rawfen_idx_fen ON rawfen(fen);
>> 
>
> I probably should have made this more explicit, but in sqlite, every row has
> a unique identifier named rowid, which exists even if it isn't explicity
> declared in the schema, and I was depending on that.   If you declare a
> PRIMARY KEY, then this replaces rowid.
>
> Of course, it's probably better practice to explicitly declare the primary
> key, but anyway, that's where I was going with it.

Those are two separate issues.  Your declared problem was:

> I don't think that solves my problem.  Sure, it guarantees that the IDs are
> unique, but not the strings.  

So to guarantee that the *strings* are unique, you need a UNIQUE index on the
string field.  The ROWID is the INTEGER PRIMARY KEY whether you specify a
different name for it or not, but that will not guarantee that each of your
*strings* is unique.  Only a UNIQUE index on the string field will do that.

As long as you understand this, no need to reply.

Cheers,

Derrell

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

Re: [sqlite] Index creation on huge table will never finish.

[Derrell . Lipman]

Chris Jones <[EMAIL PROTECTED]> writes:

> I don't think that solves my problem.  Sure, it guarantees that the IDs are
> unique, but not the strings.  
>
> My whole goal is to be able to create a unique identifier for each string,
> in such a way that I dont have the same string listed twice, with different
> identifiers.

I don't think that your original solution solves that problem either.  You
first posted this schema:

> My schema looks as follows:
> 
> CREATE TABLE rawfen ( fen VARCHAR(80) );
> CREATE INDEX rawfen_idx_fen ON rawfen(fen);

but you didn't indicate that rawfen_idx_fen is a UNIQUE INDEX so it won't
complain if there are duplicate strings.  To accomplish this (but not solving
your timing issue), you need this:

  CREATE TABLE rawfen ( fen VARCHAR(80) );
  CREATE UNIQUE INDEX rawfen_idx_fen ON rawfen(fen);

or, more concisely,

  CREATE TABLE rawfen ( fen VARCHAR(80) UNIQUE);

As previously stated, you can guarantee a unique id for each string with

  CREATE TABLE rawfen (id INTEGER PRIMARY KEY, fen VARCHAR(80) UNIQUE);

Cheers,

Derrell

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

Re: [sqlite] SQLite 2 to SQLite 3 - Varchar sizes

[Derrell . Lipman]

"Mitchell Vincent" <[EMAIL PROTECTED]> writes:

> I have an old SQLite 2.8 database that shared it's schema with
> PostgreSQL. One of the nifty things about PostgreSQL (that admittedly
> has me spoiled) is the ability to just say "varchar" without any
> length specifier.
>
> Specifying "varchar" in SQLite works great - no problem at all. Until
> I tried to use it with ODBC. The SQLite ODBC driver works fine but
> assumes a 255 character limit. As soon as it returns a result longer
> than 255 it blows an error.
>
> I see in the SQLite ODBC driver's documentation that it does support >
> 255 varchar fields but I _assume_ have to specify that it's > 255 in
> the schema.
>
> Now comes the fun part. I'm converting these databases (and there are
> a LOT of them), I'm doing "sqlite OLD.DB .dump | sqlite3 NEW.DB" which
> works flawlessly. Is there any way to change the schema on the fly to
> say "Varchar(1024)" instead of just "varchar" (or just use an SQLite
> 'type' of "text") ?

If you don't have the word varchar anyplace else, e.g. in your data, you can
simply do:

  sqlite OLD.DB .dump | sed 's/varchar/text/' | sqlite3 NEW.DB

If the word varchar may exist elsewhere, or be in various cases (VARCHAR,
Varchar, etc.), you'll have to be a bit more creative.  "awk" may be your
friend.

Derrell

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

Re: [sqlite] Backing up a SQlite database

[Derrell . Lipman]

Rich Shepard <[EMAIL PROTECTED]> writes:

> On Fri, 9 Feb 2007, Mikey C wrote:
>
>> This might be a dumb question, but is taking a backup of a live database
>> simply a matter of copying the file to a backup device/drive?
>
>   Yes. It's a regular file to your OS. As a matter of fact, you can copy the
> file to another name and open that other name to see the same tables and
> data as with the original. That's how I keep a backup of the database I'm
> developing.
>
>> And restoring it a matter of copying it back?
>
>   Yes.

That is a dangerous way to back up a live database.  If another process
decides to write to the database while you're backing it up, you'll be backing
up partially modified data, and you're also ignoring any possible journal
file.  Whey you restore, you'll find corrupt data in this case.

You should either have your backup application open the database and do a
BEGIN EXCLUSIVE; statement to ensure that no other processes can write to it
while you're backing it up, or if you don't want to do that, you can use the
command line shell and do:

  sqlite3 .dump database.db > database.sql

and then back up database.sql.  In this latter case, the shell ensures that
the database is unchanged during the entire dump.  To restore, you do
something like this on Windows (I'm not a Windows expert so the command may
need some fixing):

  del database.db
  type database.sql | sqlite3 database.db

Derrell

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

Re: [sqlite] How to specify collating sequences in an expression.

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

>   (4)  What syntax do you prefer?

This seems the clearest to me, of the given choices:

 SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';

Derrell

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

Re: [sqlite] does select reliably return insert order?

[Derrell . Lipman]

"Kevin Dangoor" <[EMAIL PROTECTED]> writes:

> I'm using sqlite as a temporary store for flattened report database
> (generally just one table). This data is pulled out in chunks using
> OFFSET and LIMIT. This particular setup is quite simple... just insert
> all of the data, and then pull it out chunk by chunk afterwards.
>
> What I'm wondering about is whether I should put an integer primary
> key on the data table to ensure that the ordering is correct, or if
> "SELECT * FROM  LIMIT  OFFSET " will reliably return data
> in the same order in which is was inserted. It seems to, but I'd
> rather not take that for granted...

Although the order may be the same each time you retrieve the data, that could
change with a future version of sqlite.  I believe that the SQL spec doesn't
specify the order of returned rows if you don't specify an order.

You needn't add an integer primary key, however.  There's already one there
for you to use.  Just change your query to this, and you'll be guaranteed of
the order:

  SELECT * FROM  ORDER BY ROWID LIMIT  OFFSET 

Given that you're inserting the data into the table and then selecting from it
all in the same session, you'd likely have no problem, but it's not necessary
to take that chance.

Cheers,

Derrell

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

Re: [sqlite] pragma for headers ( pipe or redirect a series of commands)

[Derrell . Lipman]

Bill Hernandez <[EMAIL PROTECTED]> writes:

> I tried finding a pragma command for .headers on, but didn't have any  luck.
>
> sqlite> .headers on | .mode column customers | select * from customers ;
> I also tried :
>
> sqlite> select * from customers ; < .headers off
>
> and that didn't work either
>

You're mixing (incorrect use of) shell  pipe commands with commands to the
sqlite shell in these examples.

> The reason I am trying to do this in one call rather than using  multiple
> lines is that one "do shell script" call is totally  independent from the
> next, unlike scripting to a shell window which I  don't want to do.
>
> sqlite> .headers on
> sqlite> .mode column customers
> sqlite> select * from customers ;
>
> is that I am calling it from Applescript, and one "do shell script" to
> sqlite doesn't have a clue what the previous one did.

I don't know what capability Applescript has.  The easy way to do it with a
script in the various Linux shell languages is with a "HERE" document, where
the input to the command is redirected from the block of lines which follows
the command.  In your case, you'd do something like:

sqlite3 filename.db <<'EOF'
.headers on
.mode column customers
select * from customers ;
EOF

If Applcscript doesn't support HERE documents (it's unlikely it does), you can
accomplish something similar with redirecting from a separate file.  I would
hope it has that capability.  You'd then do something like this to create a
"commands" file:

echo '.headers on' > commands
echo '.mode column customers' >> commands
echo 'select * from customers;' >> commands

and then run sqlite using that command file for input:

sqlite3 filename.db < commands

Hope that helps.

Derrell

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

Re: [sqlite] attach in transaction

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> The prohibition against running ATTACH within transaction
> first appeared in version 3.0.1.  Presumably in version 3.0.0
> you could ATTACH within a transaction.

The prohibition may have appeared in 3.x but attempting an ATTACH within a
transaction doesn't work with 2.8.16 or later, either.  I don't remember the
exact results when trying it, but I know I moved my ATTACHes to outside of the
transaction because inside, they didn't work (properly?).

Derrell

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

Re: [sqlite] What's the collective term for table, view, trigger etc?

[Derrell . Lipman]

T <[EMAIL PROTECTED]> writes:

> Is there a standard (or even non-standard) term that refers to the  tables,
> triggers, views, indexes (any others?) in a database?

The collective term for all of the definitions taken together is the "schema"
of the database.

Derrell

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

Re: [sqlite] Is sqlite the right db for me?

[Derrell . Lipman]

"Steve Davis" <[EMAIL PROTECTED]> writes:

> It is not a web-based app, rather browser based.  Many brigades do not have
> net access at their stations, they might only have someone there once a
> fortnight, so it isn't viable.  Also this app might also be used on a
> notebook at a remote location...where there is often no mobile phone
> coverage.
>
> In fact, my mistake too...it is written in ASP/VBScript/MSaccess because
> that is the language I knew at the time.  That's why it is browser based. I
> have since moved on (ahead?) and use PHP/mySQL.

One of the up-and-coming technologies is having javascript applications that
communication via "remote procedure calls" to a server.  You've probably heard
of AJAX which is a method of communicating the information between the browser
application and the server.

There are toolkits that make this kind of thing very easy.  For example, go to
http://www.qooxdoo.org and click on the "demo" link.

The backend, the server side, can be whatever you like.  I wrote a PHP backend
and there's also a Java backend.  Both are included with qooxdoo.  You write
the stubs that allow your javascript application to make specific remote
procedure calls.  You can link to whatever database you like, in the backend.
PHP has access to sqlite (and nearly every other database in existence).  I
don't do Java, but I'm sure there are Java bindings for sqlite.  Either of
these would let your application run nicely in the browser, avoiding the
hassles of writing a stand-alone application.

Cheers,

Derrell

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

Re: [sqlite] Check for empty table

[Derrell . Lipman]

"Scott Hess" <[EMAIL PROTECTED]> writes:

> On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
>> Trying to find the fastest way to determine if a table has no rows.
>>
>> I think this will do:
>> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
>> If a table has rows then the result should be 1.
>
> What's wrong with "SELECT COUNT(*) FROM table;"?

COUNT(*) is generally slow.  It actually iterates over every row, so with
large tables, it's very slow.  By using a query with a "LIMIT 1", you ensure
that as soon as a single matching row is found, no additional rows are
searched for.

Derrell

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

Re: [sqlite] select from commandprompt with output to file

[Derrell . Lipman]

"RB Smissaert" <[EMAIL PROTECTED]> writes:

> How would I run these 4 commands via a .bat file or via whatever means:
>
> cd c:\test\ReadCodes
> c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db"
> .output testfile.txt
> select * from readcode where read_code glob 'G2*';

I haven't been tracking this thread so apologies if I'm misunderstanding what
you want.  Are you doing this from a DOS (Command) shell?  I'm far from a DOS
expert, but how about something like this:

cd c:\test\ReadCodes
echo "select * from readcode where read_code glob 'G2*'" | 
c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db > testfile.txt

Derrell

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

Re: [sqlite] Retrieving date

[Derrell . Lipman]

Lloyd <[EMAIL PROTECTED]> writes:

> Hi,
>   I stored a unix epoch (32 bit integer) date in the sqlite data base. I
> want to retrieve it in the readable date format. For that I use the
> following query
>
> select datetime(sdate,'unixepoch') from mytab;
>
> It shows a formatted date, but there is some changes in the hours.
>
> How can I retrieve the accurately converted date and time ?

You may want:

  select datetime(sdate, 'unixepoch', 'localtime') from mytab;

Derrell

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

Re: [sqlite] Q about new SQLite API

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> QUESTION 1: sqlite3_prepare_v2 is the merely the working name
> for the new function.  What should the official name be?
> Some possibilities include:
>
> sqlite3_prepare_ex1
> sqlite3_prepare_ng
> sqlite3_new_prepare
> sqlite3_compile

sqlite3_compile works for me.

> QUESTION 2: Are there any other API warts that need to be
> worked around that can be fixed by this same change?

I'll continue to think about it, but none that I can think of off hand.

> QUESTION 3: Suppose there is a schema change and the SQL
> statement is automatically reprepared. But the schema change
> is such that the SQL is no longer valid.  (Perhaps one of the
> tables mentioned in a SELECT statement was dropped.) What
> error code should sqlite3_step() return in that case?

This seems an appropriate use of SQLITE_SCHEMA.

Cheers,

Derrell

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

Re: [sqlite] command-line shell handling of errors.

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> So the question:  Who will be adversely effected by the new
> error behavior in the sqlite command-line shell?  Who is
> using the sqlite command-line shell in scripts in such a
> way that the script will no longer work with the new
> behaviors?  Do I need to change the behavior back to the
> way it was and provide a command-line option to provoke the
> new (more rational) behavior?

My use of the command line shell in scripts expects, in some cases, errors to
be ignored and for the remainder of the queries to be processed regardless of
the earlier errors.

I agree that your proposed change would have been preferred as the original
implementation.  I wouldn't mind if the new behavior became the default as
long as there was a new command-line option to enable backward-compatibility
mode of ignoring errors and continuing to process.

Cheers,

Derrell

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

Re: [sqlite] Retrieving column data by providing column names

[Derrell . Lipman]

"Ever Green" <[EMAIL PROTECTED]> writes:

> I dont think so!
>
> These functions would help to get the parameter names from a prepared
> statement. I want to retrieve data using column headings after a
> sqlite3_step.

The function I pointed you at takes the parameter name and returns its index.
I believe that index is what you'll want to pass to sqlite3_column_text16().

Derrell


> On 10/26/06, [EMAIL PROTECTED] <
> [EMAIL PROTECTED]> wrote:
>
>> "Ever Green" <[EMAIL PROTECTED]> writes:
>>
>> > I am using sqlite3_column_text16 to retrieve data.
>> >
>> > The problem is that sqlite3_column_text16 takes the column number as
>> > argument. This breaks my existing code if I insert new columns in the
>> > beginning of the column list that was specified in the SELECT statement.
>>
>> Does this solve your problem?
>>
>> http://sqlite.org/capi3ref.html#sqlite3_bind_parameter_index
>>
>> Derrell
>>
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>> -
>>
>>

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

Re: [sqlite] Retrieving column data by providing column names

[Derrell . Lipman]

"Ever Green" <[EMAIL PROTECTED]> writes:

> I am using sqlite3_column_text16 to retrieve data.
>
> The problem is that sqlite3_column_text16 takes the column number as
> argument. This breaks my existing code if I insert new columns in the
> beginning of the column list that was specified in the SELECT statement.

Does this solve your problem?

  http://sqlite.org/capi3ref.html#sqlite3_bind_parameter_index

Derrell


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

Re: [sqlite] Re: number problem with 3.2.8

[Derrell . Lipman]

"Lloyd Thomas" <[EMAIL PROTECTED]> writes:

> I did try number literal  >10 but mad no difference. I will rebuild the
> database row as an integer.

As an interim solution (prior to changing your database schema), you should be
able to use your existing schema with this query:

  select ring_time from calls where (ring_time + 0) > 10;

That should force the left side to be evaluated as an integer, and the right
side already is an integer.  You should get an integer comparison.

Note that if ring_time was indexed, your index will be ignored since you're
using a calculated value instead of the field.  Changing the schema is
certainly the proper solution if the value is supposed to be interpreted as an
integer.

Derrell

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

Re: [sqlite] Potential corruption bug in 2.8.17. Patch attached.

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> [EMAIL PROTECTED] wrote:
>> This was likely a typo.  In its current state, it's accessing uninitialized
>> memory.  It looks like it's conceivable that an incorrect nextRowid could be
>> later used if the uninitialized value happens to be a small integer (smaller
>> than pC->nextRowid) and the "valid" flag therefore doesn't get set to false.
>> 
>> --- vdbe.c~  2005-12-19 12:42:25.0 -0500
>> +++ vdbe.c   2006-10-22 16:32:45.0 -0400
>> @@ -2937,7 +2937,7 @@
>>if( pOp->p2 & OPFLAG_NCHANGE ) db->nChange++;
>>if( pOp->p2 & OPFLAG_LASTROWID ) db->lastRowid = pNos->i;
>>if( pOp->p2 & OPFLAG_CSCHANGE ) db->csChange++;
>> -  if( pC->nextRowidValid && pTos->i>=pC->nextRowid ){
>> +  if( pC->nextRowidValid && pNos->i>=pC->nextRowid ){
>>  pC->nextRowidValid = 0;
>>}
>>  }
>> 
>
> As it happens, pC->nextRowidValid is always false in this
> context, as far as I can tell, so the pTos->i variable is
> never accessed.

That explains why in many years of use, I've never seen an actual corruption
caused by this.  Thanks.

> The fix checked in was to remove the test altogether and unconditionally set
> pC->nextRowidValid to 0.

If, as you say, pC->nextRowidValid is always false anyway, wouldn't the
correct fix be to not even unconditionally set pC->nextRowidValid to 0; just
delete those two original lines entirely?  It sounds like you're now
unnecessarily setting a variable that's already false to false. (Or did I
misunderstand your statement?)

Derrell

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

[sqlite] Potential corruption bug in 2.8.17. Patch attached.

[Derrell . Lipman]

This was likely a typo.  In its current state, it's accessing uninitialized
memory.  It looks like it's conceivable that an incorrect nextRowid could be
later used if the uninitialized value happens to be a small integer (smaller
than pC->nextRowid) and the "valid" flag therefore doesn't get set to false.

--- vdbe.c~ 2005-12-19 12:42:25.0 -0500
+++ vdbe.c  2006-10-22 16:32:45.0 -0400
@@ -2937,7 +2937,7 @@
   if( pOp->p2 & OPFLAG_NCHANGE ) db->nChange++;
   if( pOp->p2 & OPFLAG_LASTROWID ) db->lastRowid = pNos->i;
   if( pOp->p2 & OPFLAG_CSCHANGE ) db->csChange++;
-  if( pC->nextRowidValid && pTos->i>=pC->nextRowid ){
+  if( pC->nextRowidValid && pNos->i>=pC->nextRowid ){
 pC->nextRowidValid = 0;
   }
 }

Cheers,

Derrell

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

Re: [sqlite] Performance Question

[Derrell . Lipman]

Kurt Welgehausen <[EMAIL PROTECTED]> writes:

> [EMAIL PROTECTED] wrote:
>
>> Saying NOT NULL on a PRIMARY KEY is redundant, by the way.
>> --
>> D. Richard Hipp   <[EMAIL PROTECTED]>
>
> **kaw<~/tdpsa>$ sqlite3
> Loading resources from /home/kaw/.sqliterc
> SQLite version 3.3.7
> Enter ".help" for instructions
> sqlite> .nullvalue '<>' 
> sqlite> create table t (k int primary key, d char);
> sqlite> insert into t (k, d) values (1, 'abc');
> sqlite> insert into t (k, d) values (1, 'def');
> SQL error: column k is not unique
> sqlite> insert into t (k, d) values (null, 'ghi');
> sqlite> insert into t (k, d) values (null, 'jkl');
> sqlite> select * from t;
> k   d 
> --  --
> 1   abc   
> <>  ghi   
> <>  jkl
>
>
> Am I missing something, or should I write a bug ticket
> about a primary key accepting nulls?

Yup.  "int primary key" is not the same as "integer primary key".  Although
I'm using an older version than you are, I got exactly the same results you
did with "int primary key".

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> .nullvalue '<>'
sqlite> create table t (k integer primary key, d char);
sqlite> insert into t (k, d) values (1, 'abc');
sqlite> insert into t (k, d) values (1, 'def');
SQL error: PRIMARY KEY must be unique
sqlite> insert into t (k, d) values (null, 'ghi');
sqlite> insert into t (k, d) values (null, 'jkl');
sqlite> select * from t;
1|abc
2|ghi
3|jkl
sqlite> 

Derrell

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

Re: [sqlite] From Windows file format to MacOSX (unsuccessfull)

[Derrell . Lipman]

Alexander Lamb <[EMAIL PROTECTED]> writes:

> 1.  (*) text/plain  
>
> Well, I am afraid it didn't work.
>
> Somehow, the legacy_file_format info is not "sticky".
>
> I did:
>
> sqlite3
>
> then in command mode:
>
> PRAGMA legacy_file_format=ON;
>
> then
>
> ATTACH "d:\mydb.db" AS mydb;

In order to have done an ATTACH, the database had to have already existed.
That's too late.  Instead, ensure that mydb.db *does not exist*, and then do:

  sqlite3 d:\mydb.db
  PRAGMA legacy_file_format=ON;
  CREATE TABLE TEST (id INTEGER);

The next time you then access the file, it will have the proper format.

Derrell

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

[Derrell . Lipman]

"John Newby" <[EMAIL PROTECTED]> writes:

> Does anyone know any reason why SQLite doesnt like tables called "Table" or
> is this a standard SQL thing?

It's a reserved word, so if you really, Really, REALLY want to create a table
of that name (you're making it confusing to read, so you really shouldn't),
you can do it using either quotes or square brackets around the table name, as
shown here:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table "TABLE" (i integer);
sqlite> .schema
CREATE TABLE "TABLE" (i integer);
sqlite> .mode line
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE "TABLE" (i integer)
sqlite> drop table "table";
sqlite> create table [TABLE] (i integer);
sqlite> .schema
CREATE TABLE [TABLE] (i integer);
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE [TABLE] (i integer)
sqlite>



Derrell

Re: [sqlite] Results even if no rows

[Derrell . Lipman]

"andrew gatt" <[EMAIL PROTECTED]> writes:

> I'm trying to come up with an SQL statement that works under sqlite that can
> return a list of rows depending if a list of ids exist.  For individual
> checking i'm using:
>
> select 1 from table where id == 1;
>
> which returns a 1 if there is a row or no rows if it doesn't exist. However
> i have a large list to check and was trying to think of a way to optimise
> this. I can use:
>
> select 1 from table where id == 1 OR id == 2;
>
> which will return:
>
> 1
> 1
>
> if they both exist, but if one doesn't exist i can't tell which one it is. Is 
> there some way to get the statement to return a row with a 0 if the id doe 
> not exist? 
>
> 1
> 0
>
> Or do i have to do the check for each one?

How about this:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table x (id integer);
sqlite> insert into x values (1);
sqlite> insert into x values (3);
sqlite> select (id in (1,2)) from x;
1
0
sqlite> select (id in (1,2,3)) from x;
1
1
sqlite>

Re: [sqlite] BEGIN TRANSACTION appears to not lock database

[Derrell . Lipman]

Bud Beacham <[EMAIL PROTECTED]> writes:

>   In the book, SQLite (version 2.x of SQLite) by Chris Newman, he writes on
> page 184 that a way to test a Tcl callback function for a locked database is
> to issue a BEGIN TRANSACTION in one session and run the program in another
> session.

Note the book title: version 2.x.  If you're using version 3, transactions
work a bit differently with the same BEGIN TRANSACTION command.  To get the
behavior of 2.x while using 3.x, issue BEGIN EXCLUSIVE instead of just BEGIN
or BEGIN TRANSACTION.  BEGIN EXCLUSIVE will lock the database immediately (as
was done by BEGIN TRANSACTION in 2.x), whereas BEGIN (without EXCLUSIVE or any
other qualifier) will wait until the transaction is actually required (e.g. an
INSERT is issued) before obtaining the lock.

Look at sqlite.org documentation for an explanation of the various ways to use
transactions.

Cheers,

Derrell

Re: [sqlite] Need sql query help

[Derrell . Lipman]

onemind <[EMAIL PROTECTED]> writes:

> What technology would be best suited for this task? I just assumed that a
> databse would be ideal, why do you say sql isn't suited for this and what
> is?

Take a look at the Controllable Regex Mutilator, CRM114,
.  It has mechanisms for detecting missing
letters, extra letters, changed letters, etc.  You'll likely find what you're
looking for there or at least get some good ideas from it.  (One of its
purposes is a spam filter, and it's the best at that of anything I've found!)

Derrell

Re: [sqlite] problem with creating a table

[Derrell . Lipman]

Bijan Farhoudi <[EMAIL PROTECTED]> writes:

> A. Pagaltzis wrote:
>> * Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 16:35]:
>>
>>> Thanks for your answer but still I am getting an error message:
>>> sqlite> create table foo(i integer, [order] integer);
>>> sqlite> .sch
>>> CREATE TABLE foo(i integer, [order] integer);
>>> sqlite> insert into foo values(1,2);
>>> sqlite> select order from foo
>>>   ...> ;
>>> SQL error: near "order": syntax error
>>>
>>> Any other idea?
>>>
>>
>> .headers on
>> SELECT [order] FROM foo
>>
> But how would you know the name of the col is "order"  not "[order]"?

Surround ALL table and column names in square brackets when you reference
them, and that should eliminate the confusion.  

sqlite> CREATE TABLE [foo] ([i] INTEGER, [order] INTEGER);
sqlite> INSERT INTO [foo] VALUES (1,2);
sqlite> .mode line
sqlite> SELECT [order] FROM [foo];
order = 2
sqlite> SELECT * FROM [foo];
i = 1
order = 2
sqlite> 

Note that the square brackets are not displayed because they are not part of
the column name.  They are delimiters to say, "Hey, I really, really know what
I'm doing; I know that my column names may be reserved words and I want to do
this anyway."

Since the square brackets let you put nearly any character in table and column
names, you can do hard to comprehend stuff, like this:

sqlite> CREATE TABLE [[x] (i integer);
sqlite> select * from sqlite_master where type = 'table';
type = table
name = foo
tbl_name = foo
rootpage = 2
 sql = CREATE TABLE [foo] ([i] INTEGER, [order] INTEGER)

type = table
name = [x
tbl_name = [x
rootpage = 3
 sql = CREATE TABLE [[x] (i integer)
sqlite> 

Note that the new table name is "[x" (a square bracket followed by 'x').

Like I said in my first reply to you, using reserved words for column names is
dangerous.  You should generally try to avoid it.

Derrell

Re: [sqlite] problem with creating a table

[Derrell . Lipman]

Bijan Farhoudi <[EMAIL PROTECTED]> writes:

> I am trying to create table and I would like to name one of the columns
> order, but pysqlite does not like it. I do not want to have order__ or any
> thing like that.  for example the following command does not work:
> cur.execute('create table foo(i integer, order integer)')

You're working on dangerous ground when you use reserved words as your column
names.  'order' is a reserved word, as it is used for the ORDER BY clause.

You can do it, though, like this:

  create table foo(i integer, [order] integer)

Placing column names in square brackets lets you use reserved words as column
names.

Derrell

Re: [sqlite] SQLite performance for 10 Million Records

[Derrell . Lipman]

"Manzoor Ilahi Tamimy" <[EMAIL PROTECTED]> writes:

> Here Is The Schema For these Tables. 
>
> CREATE TABLE HVH (
> Field1 VARCHAR(8),IDC  VARCHAR(4), 
> Field3 VARCHAR(2),Field4 VARCHAR(4), 
> Field5 VARCHAR(7),Field6 VARCHAR(8), 
> Field7 VARCHAR(1),Field8 FLOAT);
>
> CREATE TABLE ITM(
> IDC  VARCHAR(4),ITEMNAME VARCHAR(20),
> COLUMN3  VARCHAR(1),COLUMN4   VARCHAR(1),
> COLUMN5  VARCHAR(1),COLUMN6   VARCHAR(1),
> COLUMN7  VARCHAR(1),COLUMN8   VARCHAR(1),
> COLUMN9  VARCHAR(1),COLUMN10  VARCHAR(1),
> COLUMN11 VARCHAR(1),COLUMN12  VARCHAR(1),
> COLUMN13 VARCHAR(1),COLUMN14  VARCHAR(1),
> COLUMN15 VARCHAR(1),COLUMN16  VARCHAR(1));
>
> CREATE INDEX index1 ON ITM (IDC);

Ok, so at this point, you have one index, only on ITM(IDC).

> //
>
> TEST 2 ( Disk DB )
>
> Table Names itm , HVH   
> Number of Records :  itm   5 Million  and HVH   less than 
> 10,000
>
> QUERY:
> create index index1 on itm(IDC) 

Now you've created another index *on the same column of the same table*.  That
doesn't help you any.  Instead of that, do

  CREATE INDEX index1 ON HVH(IDC);

and you I expect you'll see dramatically lower select times.

Derrell

Re: [sqlite] SQLite performance for 10 Million Records

[Derrell . Lipman]

Bill King <[EMAIL PROTECTED]> writes:

> Manzoor Ilahi Tamimy wrote:
>
>> We are Using SQLite for one of our project.
>>
>>The Database Size is  more than 500 MB.
>>It contain one table and about 10 million Records.

> Err, for that size, I'd recommend going something heavier, like
> firebird. This is not sqlite's solution domain in the slightest.

I'd have to differ on opinion here.  I have an sqlite database that's at 6.9GB
with about 40 million records, and it's working just fine.  My query speed has
not changed substantially as the database has grown.

With sqlite, you *do* need to hand-optimize your queries.  Determine which
queries are slow, and consider splitting them into multiple queries which
generate temporary tables.  By doing this, you can create indexes, as
appropriate, on the temporary tables that make the overall time to accomplish
your goal much less than cramming it all into a single query that is not so
highly optimized.

Oh, and I'm using the old sqlite 2.8 series.  I expect I'd be getting even
better speed if I used the newer 3 series.

Cheers,

Derrell

Re: [sqlite] Problems with multiple threads?

[Derrell . Lipman]

"Jiri Hajek" <[EMAIL PROTECTED]> writes:

> 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
> to open the database file') error. I found out that it can be fixed by
> running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
> a big issue, but still I wonder why this error message is returned?
> Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?
>
> 2. More serious issue is that after I enable transaction usage (not used in
> 1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
> an error SQLITE_ERROR ('cannot start a transaction within a transaction').
> Problem is that I definitely am not already in a transaction. The only
> reason for this seems to be that there's >1 thread running, with only 1
> thread running there's no problem.

Do you have a separate database handle (provided by a unique call to
sqlite3_open()) for each and every thread?  If not, the above sort of error
can occur.  If you ensure that every thread opens the database itself and no
thread ever modifies the schema, you should not see either of those problems.

Cheers,

Derrell

Re: [sqlite] autonum primary key

[Derrell . Lipman]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:

> I need help in generating a unique integer for the table's primary key. I am
> more familiar with MS Access that has a data type called "Autonum" that
> generates the integer. Do I need to find the last record to know what the
> next number should be?

In SQLite, if you declare a column as INTEGER PRIMARY KEY and insert NULL into
that column, a unique value will be automagically inserted in that column for
you.  The value may be the "next" unused value, or could also be a
currently-unused value due to a "hole" in the sequence (perhaps because you
deleted a record).  If you need to guarantee that each new value is always the
next in a continuous sequence,you can use INTEGER PRIMARY KEY AUTOINCREMENT
instead of INTEGER PRIMARY KEY.

Derrell

Re: [sqlite] Re: Check for duplicate values in database

[Derrell . Lipman]

"Anish Enos Mathew" <[EMAIL PROTECTED]> writes:

> Igor, I know that it will show error when we try to insert a value which
> is already there in the database when that field is a primary key.  But
> I want to check it out and I don't want that number to be inserted into
> the database. I was asking about a method which finds this. I don't want
> to terminate the program and have other numbers in a loop to be inserted
> into the database.

Instead of using INSERT which will generate an error if the primary key is
already in use, you can use INSERT OR IGNORE to ignore the error and simply
not insert the new record.  If you wanted instead to replace the old record
with the new one, you could use INSERT OR REPLACE.

Derrell

Re: [sqlite] C API Question

[Derrell . Lipman]

Anders Persson <[EMAIL PROTECTED]> writes:

> I have written a wrapper around
>iRes = sqlite3_get_table(db,sql);
>
> But only SELECT works not INSERT or UPDATE
>
> any ide.. missing somting

The database file or the directory in which it is contained is read-only?
Obviously the database file must be writable.  Also, the directory must be
writable because the journal file is created therein.

Derrell

Re: [sqlite] backwards compatibility

[Derrell . Lipman]

"Michael P. Soulier" <[EMAIL PROTECTED]> writes:

> Hello,
>
> I just got a nasty surprise. I installed an sqlite 3.3.5 .dll on windows with
> pysqlite2, and I used an application to create a database file. I then scp'd
> the file home to my linux box where I have sqlite 3.2.2 installed, and I got
> this:
>
> sqlite> .tables
> Error: unsupported file format
>
> Ouch. I'm building 3.3.5 now but it puts a kink in some of my cross platform
> development plans. 
>
> How often is backwards compatibility to be broken in this way? 

Actually, that's not backwards compatibility you're looking for, it's forwards
compatibility.  You want a new database to be readable by the old version that
knows nothing of the new format.

If you take your database from 3.2.2 and use it with 3.3.5, it'll work fine.
The reverse is not true, however, and forcing it to be true would mean never
being able to add new features.

If you look at the docs, I believe there is a way to tell 3.3.5 to generate
the old-style database if that's really what you're looking to do.

Derrell

Re: [sqlite] How can I rename a column without ALTER COLUMN Command

[Derrell . Lipman]

Roman <[EMAIL PROTECTED]> writes:

> I know that ALTER TABLE -> ALTER COLUMN is not supported by sqlite3.
>
> I misspelled a column name, and I am curious if there is a command to change 
> the name from sqlite3 interface.

A bit simplistic, but:

echo ".dump" | \
  sqlite3 database.db | \
  sed 's/old_column_name/new_column_name'/g | \
  sqlite3 newdatabase.db

This assumes you're on Linux or equiv.  If you're using Windows, you should be
able to do something similar with:

  sqlite3 database.db

Re: [sqlite] segmentation fault error?

[Derrell . Lipman]

杰 张 <[EMAIL PROTECTED]> writes:

> 1.  (*) text/plain  
>
> Hi all,
>   I just want to get the values of a table.The result implemented is 
> "Open OK!
>   segmentation fault ". Why did I got this result ? The following is my code:

Just from a quick visual inspection, it appears that you need to make the
following changes.  You need to pass the ADDRESS of where to put the allocated
pointers and returned number of rows/columns.

>
>   #include 
> #include 
> #include "sqlite3.h"
> main()
> {

> char **errmsg;

char *errmsg;

> int ret;
> int rc;
>   sqlite3 *db;
> char *sql = "SELECT * FROM light;";

> char ***resultp;
> int *nrow;
> int *ncolumn;

char **resultp;
int nrow;
int ncolumn;

> ret = sqlite3_open("sensor.db",);
> if (ret)
> {
> fprintf(stderr, "Could not open database:%s\n", sqlite3_errmsg(db));
> exit (1);
> }
> else 
>  {  printf("Open OK!\n");

> rc = sqlite3_get_table(db,sql,resultp,nrow,ncolumn,errmsg);

rc = sqlite3_get_table(db,sql);

>   printf("rc=%d\n",rc);
> if (rc)
>  {
>  fprintf(stderr,"can't open the table:%s\n",sqlite3_errmsg(db));
>  exit(1);
>  }
> else printf("open the table ok");
>}
>  
> sqlite3_close(db);
> printf("Close OK!");
> }
>
>   Thank you so much!
>
>   zhangjie 
>
>
>   
> -
>  雅虎1G免费邮箱百分百防垃圾信
>  雅虎助手-搜索、杀毒、防骚扰  

Re: [sqlite] help with sqlite command

[Derrell . Lipman]

"Uma Venkataraman" <[EMAIL PROTECTED]> writes:

> Hi Jay,
>
> Thanks for your reply. I am trying the command
>
>select * from mytable where row_id = row_id % 5

Try this instead:

  SELECT * FROM mytable WHERE ROWID % 5 = 0;

Note that if you have an integer primary key in mytable, then ROWID and your
primary key are the same thing.  If those ROWID values are not incrementing
numbers (e.g. you inserted values into your primary key which were out of
sequence or if you have deleted any rows) then this method won't work.

Here's an example of one way to do it:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> .read /tmp/x.sql
CREATE TABLE x (i INTEGER PRIMARY KEY, t TEXT);
INSERT INTO x VALUES (1, 'one');
INSERT INTO x VALUES (2, 'two');
INSERT INTO x VALUES (3, 'three');
INSERT INTO x VALUES (4, 'four');
INSERT INTO x VALUES (5, 'five');
INSERT INTO x VALUES (6, 'six');
INSERT INTO x VALUES (7, 'seven');
INSERT INTO x VALUES (8, 'eight');
-- Retrieve every other row (we hope)
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
4|four
6|six
8|eight
-- Delete a row
DELETE FROM x WHERE i = 4;
-- The table now looks like this:
SELECT * FROM x;
1|one
2|two
3|three
5|five
6|six
7|seven
8|eight
-- Retrieve what should be every other row, but isn't
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
6|six
8|eight
-- Insert the values into a new table so pk is properly incrementing
CREATE TEMPORARY TABLE y
(pk INTEGER PRIMARY KEY,
 i INTEGER,
 t TEXT);
INSERT INTO y (i, t) SELECT i, t FROM x;
-- Now we can get every other row
SELECT * FROM y WHERE pk % 2 = 0;
2|2|two
4|5|five
6|7|seven
DROP TABLE y;
sqlite>

Cheers,

Derrell

Re: [sqlite] Different column names in callback function on Linux vs. Windows

[Derrell . Lipman]

"Iulian Popescu" <[EMAIL PROTECTED]> writes:

> How can I set the pragma values - is this something that can be done at
> build time or is it only possible at runtime by executing the command
> (before running any SELECT statements)?

I don't have the sqlite3 source in front of me, but in sqlite2, you could
modify the function sqlite_open() and issue something like

  db->flags |= SQLITE_ShortColNames;

In sqlite3, there's probably an sqlite3_open() function where you could do
something similar, but I'm only guessing without having the source available.

I'm not sure why you'd need to modify the sqlite3 source, though.  All you
have to do is issue the pragma from your application after opening a database.
It need only be issued once per database, so it can just be part of your
procedure for opening a database.

Derrell

Re: [sqlite] Different column names in callback function on Linux vs. Windows

[Derrell . Lipman]

"Iulian Popescu" <[EMAIL PROTECTED]> writes:

> I checked the versions and indeed the one I'm using on Windows is 3.0.8
> whether the one on Linux is 3.1.2. This being said as far as I understand
> and please correct me if I'm wrong the two PRAGMA(s) are just commands you
> run used to modify the operation of the SQLite library. I haven't invoked
> any of them before running the statements I mentioned so I assume the
> default behavior was used. How can I find what this is for the particular
> versions I'm running?

You can find out the current values like this:

   % sqlite3 :memory:
   SQLite version 3.2.1
   Enter ".help" for instructions
   sqlite> pragma full_column_names;
   0
   sqlite> pragma short_column_names;
   1
   sqlite>

but beware that the *meaning* of the pragmas changed over time, so just having
the same values doesn't necessarily mean that the same column names will be
returned by your queries.  (You can try to find out what changes were made
when, using the "timeline" on the sqlite.org website.  I don't recall when the
changes I'm referencing occurred.)

For full compatibility in this area, the easiest method is to just ensure that
you're using the same version of sqlite on both platforms, and then set the
pragma values the same.

Derrell

Re: [sqlite] Different column names in callback function on Linux vs. Windows

[Derrell . Lipman]

"Iulian Popescu" <[EMAIL PROTECTED]> writes:

> I'm doing an application port from Windows to Linux and one of the
> problems I'm facing is when executing the following statement through a call
> to sqlite3_exec():
>
> SELECT mytable.'mycolumn' FROM table
>
> The registered callback function 4th argument (a char**) denoting the column
> names contains the string mytable.'mycolumn' on Windows and the string
> mycolumn on Linux. Has anyone any idea why would that be?

I suspect you're using different versions of sqlite on Windows and Linux, or
you have pragma settings set differently on the two OSs.  You can verify the
version of sqlite with "sqlite3 -version".

There have been changes, through the development of sqlite, on what column
names to return.  IIRC, the meanings of

  PRAGMA short_column_names
and
  PRAGMA full_column_names

have changed a couple of times, and these affect exactly the information that
you're having trouble with.

You should first ensure that you are running the same version of sqlite on the
to OSs.  Then ensure that the settings of these two pragmas are the same.
With both the version and the pragma settings the same, I believe you should
get the same values passed to the callback function, given the same query.
Windows and Linux portations built from the same source, so should generate
similar results.

Derrell

Re: [sqlite] Problems compiling SQLite 3.3.4 on Mac OS X

[Derrell . Lipman]

Tito Ciuro <[EMAIL PROTECTED]> writes:

> Hello,
>
> SQLite 3.3.4
> Mac OS X Tiger 10.4.5
>
> After ./configure and make SQLite 3.3.4, I see that some files have
> disappeared:
>
> os_test.c
> os_test.h
> os_unix.h
> os_win.h
>
> This results in a few undefined symbols:
>
> sqlite3OsClose
> sqlite3FileSize
> sqlite3OsLock
> ...
>
> Any ideas?

First guess would be that 'configure' isn't detecting that OS X is Unix-like.
I suspect you might get a hint of what's going on if you carefully inspect the
output from 'configure' to see what supported OS it's detecting (if any).

Derrell

Re: [sqlite] DEFAULT values replace explicit NULLs?

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> Is the DEFAULT value for a column suppose to replace
> an explicit NULL value?  Or does the DEFAULT value only
> get used if no values for an insert is specified?  What
> is the correct SQL behavior?
>
> SQLite does the latter - the DEFAULT value is only used
> if no value is given for the column.  If you insert an
> explicit NULL value then a NULL value is inserted instead
> of the DEFAULT value.  Ticket #1705 says this is
> incorrect.
>
> Which is right?  The current SQLite implementation or
> ticket #1705?

I don't know which is "right" but I certainly have a strong preference.  If I
explicitly insert a value into a column, it's because I want *that* value
inserted -- even if the value I insert is NULL.  If I don't insert any value,
then I expect the DEFAULT value, if one is specified for the column to be
inserted in that column.

Derrell

Re: [sqlite] Help!!!

[Derrell . Lipman]

"Jay Sprenkle" <[EMAIL PROTECTED]> writes:

>> What I think you're really asking, though, is about the rows that are NOT
>> returned because there are null values.  To fix that, you're probably looking
>> for LEFT OUTER JOIN:
>
> Huh?  I thought left join and left outer join were equivalent.
>
> SQLite version 3.0.8
> Enter ".help" for instructions
> sqlite> create table x( id int, text char(8) );
> sqlite> insert into x values(1,'one');
> sqlite> insert into x values(2,'two');
> sqlite> insert into x values(3,'three');
> sqlite> create table y( id int, text char(8) );
> sqlite> insert into y values(3,'three');
> sqlite> select * from x inner join y on y.id = x.id;
> 3|three|3|three
> sqlite> select * from x left join y on y.id = x.id;
> 1|one||
> 2|two||
> 3|three|3|three
> sqlite> select * from x left outer join y on y.id = x.id;
> 1|one||
> 2|two||
> 3|three|3|three
> sqlite>

It looks like a bad recollection on my part.  I've always remembered that LEFT
JOIN was equivalent to LEFT INNER JOIN so I've always specified LEFT OUTER
JOIN when I wanted that.  (It's probably clearer to specify it anyway, I
guess.)

They say that memory is the second thing to go.  I can't remember what the
first is. :-)

Derrell

Re: [sqlite] Help!!!

[Derrell . Lipman]

Roger <[EMAIL PROTECTED]> writes:

> Select p.name||p1.name||p2.name
> from people as p left join user as u as
> u.uid = p.pid left joun people as p2 on
> u.uid=p2.pid left join people as p3 on
> u.uid=p3.pid;
>
> The problem is that if one of the rows does not have a value, it returns
> and empty string.Now in my report i end up with lots of null columns.
> Please help, how do i return something at least from this query if one
> of the columns has a null value in it.

I think you're asking two different questions here.

Firstly, to replace an empty string with something non-empty, you can use
COALESCE():

  SELECT COALESCE(p.name, 'empty') ||
 COALESCE(p1.name, 'empty') ||
 COALESCE(p2.name, 'empty')
  FROM people p

  LEFT JOIN user u
ON u.uid = p.pid

  LEFT JOIN people p2
ON u.uid=p2.pid

  LEFT JOIN people p3
ON u.uid=p3.pid;

What I think you're really asking, though, is about the rows that are NOT
returned because there are null values.  To fix that, you're probably looking
for LEFT OUTER JOIN:

  SELECT p.name || p1.name || p2.name
FROM people p

LEFT OUTER JOIN user u
  ON u.uid = p.pid

LEFT OUTER JOIN people p2
  ON u.uid=p2.pid

LEFT OUTER JOIN people p3
  ON u.uid=p3.pid;

Derrell

ps. if you copy/paste the query from your source instead of retyping it,
you'll end up with many fewer typos, and will make it easier for people to
help you.  Your query was full of typos.

Re: [sqlite] performance statistics

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> PostgreSQL has a much better query optimizer than SQLite.
> (You can do that when you have a multi-megabyte memory footprint
> budget versus 250KiB for SQLite.)  In your particular case,
> I would guess you could get SQLite to run as fast or faster
> than PostgreSQL by hand-optimizing your admittedly complex
> queries.

In this light, I had a single query that took about 24 *hours* to complete in
sqlite (2.8.x).  I hand optimized the query by breaking it into multiple (14
I think) separate sequential queries which generate temporary tables for the
next query to work with, and building some indexes on the temporary tables.
The 24 hour query was reduced to a few *seconds*.

Query optimization is critical for large queries in sqlite, and sqlite can be
made VERY fast if you take the time to optimize the queries that are taking a
long time to execute.

Derrell

Re: [sqlite] Triggers and TEMP tables: ticket #1689

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> Should I remove the tests from SQLite that prevent
> triggers in one database from referring to tables in
> a different database?  Or should I leave things as
> they are and close ticket #1689 with a remark of
> "works as designed".

I have had use for triggers that could operate on tables in a different
database, and been unable to use them.  I separate tables into two primary
databases because the tables in one of them can be locked for long periods of
time (long transactions) while the tables in the other database are accessed
frequently.  The mechanism for determining which databases will be operated on
is external to SQLite, so I would know that it is safe to issue a request that
would cause a trigger to operate on the other database.

I'd vote to allow a trigger to operate on a table in a different database, if
there are no reasons other than "they shouldn't need to do that" preventing
it.

Derrell

Re: [sqlite] :memory: and sessions with PHP

[Derrell . Lipman]

"CrazyChris" <[EMAIL PROTECTED]> writes:

> Hi there,
>
> I have a need to create a :memory: sqlite database, but save it into the
> user session (PHP) but can't see a way to access the data to save. Looking
> for a sqlite version of serialize() I guess.
>
> Has anyone managed to do this? Is it even possible?
>
> Wanting to be able to maintain a large chunk of data across a users session
> on a website, and the array's are getting tedious to manage and search
> through!

The PHP session information has to be persistent, so it's not going to be easy
to use a :memory: database.  There is lots of information about how to save
session information to a database, though, on the PHP web site.  I haven't
looked at it in a couple of years, but I'd guess that you'll get some good
pointers if you look at the documentation for session_set_save_handler().

Also, IIRC, PHP provides functions to do serialization.  You won't need them
if you go the session_set_save_handler() route, but if you want to serialize
data yourself, those functions should be available.

Derrell

Re: [sqlite] Question about Regular Expression

[Derrell . Lipman]

malcom <[EMAIL PROTECTED]> writes:

> Hello,
> I have a sqlite column with a string. This string is composed by n
> different lines separated by an \n character. Each line is composed by
> : . So my final string is something like this:
>
> : \n
> : \n
> : 
>
> Now I need to search *only* inside a particular key value. For example
> I would to see if the value of key_2 contains a string 'test' (and if
> possible return the entire value of key key_2).

This is a perfect use for having a different table contain your key/value
pairs.  Consider this:

CREATE TABLE item
(
  item_id   INTEGER PRIMARY KEY
);

CREATE TABLE attributes
(
  item_id   INTEGER REFERENCES item,
  data_key  TEXT,
  data_valueTEXT
);

INSERT INTO item VALUES (1);
INSERT INTO attributes VALUES (1, 'description', 'My first item');
INSERT INTO attributes VALUES (1, 'quantity', '3');

INSERT INTO item VALUES (2);
INSERT INTO attributes VALUES (2, 'description', 'My second item');
INSERT INTO attributes VALUES (2, 'quantity', '7');

-- now we can easily search for a particular value within a specific key
-- or, as you requested, the whole value if some text exists within the
-- value
SELECT item_id, data_value
  FROM attributes
  WHERE data_key = 'description'
AND data_value GLOB '*second*';


This didn't answer your question, but may provide a better method to
accomplish your goals.

Derrell

Re: [sqlite] print for debugging from triggers

[Derrell . Lipman]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:

> On Thu, Feb 09, 2006 at 09:51:44AM -0700, Dennis Cote wrote:
>> >program). But this didn't work. So I wrote a simple print method that takes
>> >one argument and printf it to the standard out. This works and actually
>> >solves my problem.
>> There isn't a way to get this output printed directly, but you can 
>> easily create a log of your applications activity in a table and then 
>> use a select to dump the table.
>
> The problem with logging to a table is that if the transaction aborts
> (ie: because there's a bug in the trigger), you won't get anything in
> the table. So when you need the info most you can't get it.

In the 2.8 series, if you ATTACH to a different database after beginning a
transaction and DETACH before ending the transaction, anything written to the
attached database will be retained regardless of whether the transaction is
committed or rolled back.  If this "feature" still exists in the 3.x series,
it may be of use to you for this purpose.  Just attach, insert, detach as part
of your trigger.  (I've never tried doing that in a trigger, so caveat
emptor.)

Derrell

Re: [sqlite] Auto Increment?

[Derrell . Lipman]

Dennis Cote <[EMAIL PROTECTED]> writes:

> Derrell,
>
> If you are using SQLite 3.3.0 or newer then you can do the same thing in a
> more direct manner using a CHECK constraint.
>
> CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32)));

Hehe.  I'm using 2.8.16 for most of my work, so I don't even have the
AUTOINCREMENT keyword available, let alone CHECK.

> Also, if you are concerned about signed vs unsigned interpretation of the 32
> bit value you may want to change the maximum to a 31 bit shift which will
> restrict i to values that are always positive signed values.

Hmmm... In the later versions of sqlite with 64-bit ROWID values, doesn't it
treat them as unsigned?  It sure seems that autoincremented rowid values
should always be positive...???

Cheers,

Derrell

Re: [sqlite] Sqlite powered Web Sites

[Derrell . Lipman]

Clint Bailey <[EMAIL PROTECTED]> writes:

> Can anyone point me to web sites that are powered by Sqlite? I'm curious as
> to how they function as compared to a MySQL, or brand M$ powered site.

How about http://sqlite.org ?

Derrell

Re: [sqlite] Auto Increment?

[Derrell . Lipman]

chetana bhargav <[EMAIL PROTECTED]> writes:

> Auto increment seems to return a unsigned long long is there any way for it
> to make it as 32 bit, as I am depending on this feilds to generate unique
> id, and i have a constraint fot the id to be 32 bit only.

You'll have to add enough rows to the table to use up all id values that fit
in 32 bits before you'll have a problem.  You can, however, protect from wrap-
around with something like this:

CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT);

CREATE TRIGGER x_insert_tr
  AFTER INSERT
  ON x
  FOR EACH ROW
  BEGIN
SELECT CASE
  WHEN new.i >= (1<<32) THEN RAISE(ROLLBACK, 'The table is full.')
  ELSE NULL
END;
  END;

Derrell

Re: [sqlite] ATTACH DATABASE how-to

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> The situation is:
> i have two DBs - one in memory and one in the filesystem. I
> need to fill some tables in the second DB from the fist DB.
> So, how can I ATTACH DATABASE from memory?
> (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...)

You need to ATTACH the disk-resident database to the memory one, rather than
vice versa as you're trying.  Since you already have the database handle for
the memory-resident database, issue an "ATTACH 'diskdatabase.db' AS disk_db;"
command on the memory-resident database handle.

Derrell

Re: [sqlite] sqlite in a shell

[Derrell . Lipman]

rahed <[EMAIL PROTECTED]> writes:

> Joe Wilson <[EMAIL PROTECTED]> writes:
>
>> I've seen this same behavior when running a MinGW-compiled sqlite3.exe in 
>> the cygwin shell. It has
>> something to do with the lack of flushing of information written to stdout 
>> (until close() anyway).
>> If you blindly type in the sqlite commands - it does work. Once you exit the 
>> shell via .q you can
>> see the entire output of the session.
>>
>> One workaround is to use a cygwin-compiled sqlite3.exe. Perhaps hacking the 
>> sqlite shell not to
>> buffer its console output would also do the trick.
>>
>
> I entered the commands (which I didn't do before) and I can see the
> output immediately. So the only hitch is a command line without a
> prompt sqlite>. 

The shell likely does not believe that it is running interactively for some
reason, and so is not presenting prompts.  Look at the documentation for the
shell to figure out how to tell it to go into interactive mode.

Derrell

Re: [sqlite] enum in SQLite

[Derrell . Lipman]

Michael Scharf <[EMAIL PROTECTED]> writes:

> Jim C. Nasby wrote:
>> On Thu, Jan 05, 2006 at 01:39:02PM -0600, Kurt Welgehausen wrote:
>>
>>> create trigger EnumTrg before insert on MainTbl for each row
>>> when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
>>>   select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
>>> end;
>> Wouldn't that be a lot more efficient with some kind of EXISTS test
>> rather than a count(*)?
>
> No. Because the count is at most 1 and therefore its cheaper than as exists,
> because no additional nested query is needed.
>
> However if the count(*) would be big EXISTS or LIMIT would make sense:
>   select count(*) where EXISTS (select * from TABLE where ...)
> or
>   select count(*) from (select * from TABLE where ... LIMIT 1)
>
> Michael

If you have many enum values, for slightly better efficiency (since all rows
need not be scanned), you should be able to do something like this:

 CREATE TRIGGER EnumTrg 
   BEFORE INSERT ON MainTbl
   FOR EACH ROW
 WHEN (SELECT 1
 FROM EnumVals
 WHERE val = new.EnumCol
 LIMIT 1) IS NULL
   BEGIN
 SELECT raise(rollback, 'forign-key violation: MainTbl.EnumCol');
   END;

Derrell

Re: [sqlite] LIMIT keyword does work in an UPDATE statement

[Derrell . Lipman]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:

> On Tue, Jan 03, 2006 at 10:15:17AM -0500, [EMAIL PROTECTED] wrote:
>> test mjom <[EMAIL PROTECTED]> writes:
>> 
>> >   create table tbl1 ( id integer primary key autoincrement, ref 
>> >   integer, sts varchar(16));
>> >   insert into tbl1 (ref,sts) values (10, 'ready' );
>> >   insert into tbl1 (ref,sts) values (20, 'ready' ); insert into tbl1 
>> >   (ref,sts) values (30, 'ready' );
>> >   update tbl1 set sts='busy' where sts='ready' ORDER BY ref DESC LIMIT 1;
>> >   
>> >   => i would like to have only the third record (30,'busy') updated.
>> 
>> How about something like
>> 
>>   UPDATE tbl1
>> SET sts = 'busy'
>> WHERE ref =
>>   (SELECT ref
>>  FROM tbl1
>>  WHERE sts = 'ready'
>>  ORDER BY ref DESC
>>  LIMIT 1);
>
> That won't work. Instead:
>
> UPDATE ...
> WHERE id =
> (SELECT id
> FROM tbl1
> WHERE ...
> );

Yeah, what he said. :-)

Duh!  Sorry about that.

Derrell

Re: [sqlite] LIMIT keyword does work in an UPDATE statement

[Derrell . Lipman]

test mjom <[EMAIL PROTECTED]> writes:

>   create table tbl1 ( id integer primary key autoincrement, ref 
>   integer, sts varchar(16));
>   insert into tbl1 (ref,sts) values (10, 'ready' );
>   insert into tbl1 (ref,sts) values (20, 'ready' ); insert into tbl1 
>   (ref,sts) values (30, 'ready' );
>   update tbl1 set sts='busy' where sts='ready' ORDER BY ref DESC LIMIT 1;
>   
>   => i would like to have only the third record (30,'busy') updated.

How about something like

  UPDATE tbl1
SET sts = 'busy'
WHERE ref =
  (SELECT ref
 FROM tbl1
 WHERE sts = 'ready'
 ORDER BY ref DESC
 LIMIT 1);

Derrell

Re: [sqlite] CURRENT_TIMESTAMP records / displays incorrect value?

[Derrell . Lipman]

"Murray @ PlanetThoughtful" <[EMAIL PROTECTED]> writes:

> I have a column defined with DEFAULT CURRENT_TIMESTAMP in an SQLite 3.2.7 db
> (on WinXP SP2, if that's important). I've noticed that the value being
> stored in that column is being recorded / displayed incorrectly. For
> example, it's currently 5:28am 22 Dec 2005 (in Australia, if that causes
> confusion), and yet a record inserted at this point contains "2005-12-21
> 19:28:54" when SELECTed back from the table.

Assuming that your column name is my_timestamp in table my_table, I expect
you'll get the results you're looking for by querying like this:

  SELECT datetime(my_timestamp, 'localtime') FROM my_table;

Derrell

Re: [sqlite] Version 3.2.8 and 2.8.17

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> New versions of SQLite 2 and 3 are now available on the website.
>
>   http://www.sqlite.org/
>
> These new versions contain a one-line fix to a problem that can
> lead to database corruption.  The problem has been in the code
> for almost 4 years and has so far gone unnoticed, suggesting that
> it is not a serious concern.  But because it can lead to database
> corruption, upgrading is recommended.

The source file 2.8.17.tar.gz available on the download page is only 45 bytes
long.  I think that's a bit incomplete. :-)

Also, any chance you could post (or email me, if it's not of general interest)
the small patch to 2.8.16 that generated this version (or give me the CVS
command or revision id's to determine the difference in the 2.8 branch)?

Thanks!

Derrell

Re: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?

[Derrell . Lipman]

"Bogdan Ureche" <[EMAIL PROTECTED]> writes:

> I am beginning to believe that maybe I was wrong in my assumption that 'if a
> table has an index, that index shows in sqlite_master'. Then my problem is
> now to find another way to get index information for a table. Any
> suggestions?

An index for an INTEGER PRIMARY KEY does not show in sqlite_master.  Instead,
you can find the primary key of a table using:

  PRAGMA table_info(table_name);

The field(s) marked with pk=1 are primary key field(s).

  % sqlite3 /tmp/xxx.db
  SQLite version 3.2.1
  Enter ".help" for instructions
  sqlite> CREATE TABLE test_table
 ...> (
 ...>   my_primary_keyINTEGER PRIMARY KEY,
 ...>   some_other_field  TEXT
 ...> );
  sqlite> .mode line
  sqlite> PRAGMA table_info(test_table);
 cid = 0
name = my_primary_key
type = INTEGER
 notnull = 0
  dflt_value = 
  pk = 1

 cid = 1
name = some_other_field
type = TEXT
 notnull = 0
  dflt_value = 
  pk = 0

With more than one field composing the primary key:

  sqlite> CREATE TABLE t2 (f1 INTEGER, f2 TEXT, f3 TEXT, PRIMARY KEY(f1, f2));
  sqlite> PRAGMA table_info(t2);
 cid = 0
name = f1
type = INTEGER
 notnull = 0
  dflt_value = 
  pk = 1

 cid = 1
name = f2
type = TEXT
 notnull = 0
  dflt_value = 
  pk = 1

 cid = 2
name = f3
type = TEXT
 notnull = 0
  dflt_value = 
  pk = 0
  sqlite>

With a non INTEGER PRIMARY KEY (which you *can* have, contrary to a comment
made previously by someone):

  sqlite> CREATE TABLE t3 (f1 TEXT PRIMARY KEY, f2 INTEGER);
  sqlite> PRAGMA table_info(t3);
 cid = 0
name = f1
type = TEXT
 notnull = 0
  dflt_value = 
  pk = 1

 cid = 1
name = f2
type = INTEGER
 notnull = 0
  dflt_value = 
  pk = 0

Note that any primary key fields *other than* a single INTEGER PRIMARY KEY are
also in sqlite_master:

  sqlite> select * from sqlite_master;
  type = table
  name = test_table
  tbl_name = test_table
  rootpage = 2
   sql = CREATE TABLE test_table
  (
my_primary_keyINTEGER PRIMARY KEY,
some_other_field  TEXT
  )

  type = table
  name = t2
  tbl_name = t2
  rootpage = 3
   sql = CREATE TABLE t2 (f1 INTEGER, f2 TEXT, f3 TEXT, PRIMARY KEY(f1, f2))

  type = index
  name = sqlite_autoindex_t2_1
  tbl_name = t2
  rootpage = 4
   sql = 

  type = table
  name = t3
  tbl_name = t3
  rootpage = 5
   sql = CREATE TABLE t3 (f1 TEXT PRIMARY KEY, f2 INTEGER)

  type = index
  name = sqlite_autoindex_t3_1
  tbl_name = t3
  rootpage = 6
   sql = 
  sqlite>

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

[Derrell . Lipman]

Dennis Cote <[EMAIL PROTECTED]> writes:

> Furthermore, I don't believe that most users are using an authorizer anyway
> (but I could definitely be wrong about that). In that case all the authorizer
> callbacks become no-ops don't they?

PHP's use of sqlite uses an authorizer callback in the original (sqlite2)
functionalilty and I expect does so in the current PDO implementation for
sqltie3.  That means that all apps using sqlite i PHP have an authorizer
callback.

Derrell

Re: [sqlite] SQL assistance with selecting the last n records inserted

[Derrell . Lipman]

Jay Sprenkle <[EMAIL PROTECTED]> writes:

> If you just want 10 records with the highest modified or created time I 
> think this will do it:
>
> select *
> from tbl 
> order by max(created_on,modified_on) desc
> limit 10

except that if modified_on is null, you won't get that record.  This variation
should fix that problem:


SELECT *
  FROM tbl 
  ORDER BY MAX(created_on, COALESCE(modified_on, 0)) DESC
  LIMIT 10;


Derrell

Re: [sqlite] modifying insert/updat data in triggers

[Derrell . Lipman]

Mark de Vries <[EMAIL PROTECTED]> writes:

> But I still don't know how to do what I want to do. Perhaps I need to
> explain with a litle more detail what I mean. Consider the following
> table.
>
> create table foo (
>   value TEXT,
>   date_create TEXT,
>   date_lch TEXT
> );
>
> Now, whenever I insert into this table I want to set date_create to
> CURRENT_TIMESTAMP.
>
> Whenever I update a row I want date_lch (lch=last change) to be set to
> CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
> 'ignored'.

I think this is what you're looking for.

CREATE TRIGGER foo_insert_tr
  AFTER INSERT
  ON foo
  BEGIN
UPDATE foo
  SET date_create = CURRENT_TIMESTAMP
  WHERE ROWID = new.rowid;
  END;

CREATE TRIGGER foo_update_tr
  AFTER UPDATE
  ON foo
  BEGIN
UPDATE foo
  SET date_lch = CURRENT_TIMESTAMP
  WHERE ROWID = new.rowid;
  END;

Derrell

Re: [sqlite] mixing GROUP and non-GROUP columns in a query

[Derrell . Lipman]

Will Leshner <[EMAIL PROTECTED]> writes:

> Say I have a table defined and populated as follows:
>
> CREATE TABLE test (a TEXT);
> INSERT INTO test (a) VALUES ('hello');
> INSERT INTO test (a) VALUES ('hello');
> INSERT INTO test (a) VALUES ('hello');
>
> And I perform the following query:
>
> SELECT rowid,count(a) FROM test
>
> In SQLite I get back:
>
> 3|hello

This must be typo.  You probably mean that you get back 3|3.

> But in MySQL I get back an error:
>
> #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no  
> GROUP columns is illegal if there is no GROUP BY clause
>
> I'm wondering if MySQL isn't right to treat this as an error?

Even sqlite can't quite decide what to do in this case.  sqlite3 returns, in
this case, the highest row number (it seems).  sqlite2 returns NULL for the
rowid given the same inserts and query.

In any case, there's no defined way to determine *which* row the non-group
data will be returned from, so at best, it's an unreliable query.

(It'd be nice if you got back three rows from the above query:
  1|3
  2|3
  3|3
but that's not what happens with either version of sqlite.)

Cheers,

Derrell

Re: [sqlite] Saving "Unsigned Long"s in database

[Derrell . Lipman]

Jay Sprenkle <[EMAIL PROTECTED]> writes:

> On 7/12/05, Gotzon Astondoa <[EMAIL PROTECTED]> wrote:
>> ...
>> struct hostent *hep;
>> ...
>> hep=gethostbyname(DomainTxt);
>> ...
>>  strcpy(txtIP, inet_ntoa(*((struct in_addr *)hep->h_addr_list[i])));
>>  ip = inet_addr(txtIP);
>>  ip = ntohl(ip);
>>  strcpy(sql,"INSERT into domains (domain) VALUES ('");
>>  sprintf(nIP,"%u",ip);
>>  strcat(sql,nIP);
>>  strcat(sql, "')");
>
> Try this:
>
> sprintf( sql, "INSERT into domains (domain) VALUES ('%u') ", ip  );

Being pedantic...

  char sql[MAX_QUERY_LEN];
  snprintf(sql, sizeof(sql),
   "INSERT into domains (domain) VALUES ('%lu') ",
   ip);

or

  char * sql;
  if ((sql = sqlite3_mprintf("INSERT into domains (domain) VALUES ('%lu') ",
 ip)) == NULL)
  {
  fatal("out of memory");
  }

  /* use sql pointer (issue query) */

  sqlite3_free(sql);

Note 1: snprintf() instead of sprintf() to ensure that you don't overrun the
sql buffer.  This function takes the size of the buffer as the second
parameter.  Alternatively, use sqlite3_mprintf() to allocate a buffer of an
appropriate length (containing the query), and then free it when done with the
query.

Note 2: Since you stated explicitely that you were treating the IP address as
a "long", then "%lu" instead of "%u" allows for the possibility that "int" and
"long" are different sizes, which of course, depends on the compiler you're
using.  Using "%lu" is more technically correct if "ip" is declared as "long".


The question remains, though as to why you are using LENGTH() on a numeric
field.  LENGTH() is intended for strings, and presumably type-casts the
numeric value to a string when you call it.


Cheers,

Derrell

Re: [sqlite] Possible enhancement to SQL logic

[Derrell . Lipman]

Ben Clewett <[EMAIL PROTECTED]> writes:

> D. Richard Hipp wrote:
>>> MySQL and PostgreSQL will use the indexes here, and therefore return the
>>> result considerably faster.
>>>
>> Really?  I would be very interested to know what
>> query plan MySQL and PostgreSQL use in this example.
>
> It looks like I didn't look before leaping.  MySQL does do a sequential 
> search through all rows in table with same example.
>
> But PostgreSQL does use indexes for the same example.

You can get the PostgreSQL query plan by issuing this query:

EXPLAIN SELECT * FROM a WHERE f1 = 1 OR f2 = 1;

The output from that command is what Dr. Hipp requested.

Derrell

Re: [sqlite] file db from memory db

[Derrell . Lipman]

Cornel Gazdaru <[EMAIL PROTECTED]> writes:

> I try copying a table from a :memory: data base to a file data base, but 
> could not make it work.
> Anybody has any suggestions?
> Thanks
>
> I tried something like:
>
> open a data base :memory:
> create table  MYTABLE (...)
> ...
>
> attach 'filename.db' as FileDB
> create table FileDB.MYTABLE as select * from  MYTABLE
> detach FileDB

IIRC, you can't create a table in an attached DB.  Instead, you can create an
empty table MYTABLE in filename.db first, then do similarly to what you were
previously:

open a data base :memory:
create table  MYTABLE (...)
...

attach 'filename.db' as FileDB
insert into FileDB.MYTABLE select * from MAIN.MYTABLE
detach FileDB

Re: [sqlite] Error 21, "library routine called out of sequence"

[Derrell . Lipman]

Ben Clewett <[EMAIL PROTECTED]> writes:

> Dear SQLite,
>
> I am running a sequence of inserts:
>
> BEGIN
> INSERT INTO table ...
> INSERT INTO table ...
> INSERT INTO table ...
> INSERT INTO table ...
> INSERT INTO table ...
>
> I am catching the 'kill -1' signal (aka CTRL-C) and executing a final query:
>
> COMMIT
>
> When I execute the 'COMMIT' I get:
>
> "library routine called out of sequence"
>
> Every other query command after this returns the same.
>
> My guess is the interrupt is kicking in during SQLite completion of the 
> previous query.  Therefore SQLite is half way through something when 
> this occurs.
>
> Can any person suggest a possible solution as I am out of options.  For 
> instance, some command to tidy up SQLite so that the next statement does 
> not fail.  Otherwise I loose all my inserted data :)

Instead of issuing the COMMIT from the signal handler, set a global flag in
the signal handler which you check in your main code.  If the flag has been
set, then COMMIT and exit.

You can do something akin to this (untested code):

--

static int  bKilled = 0;

static void
sigHandler(int signum)
{
if (signum == SIGTERM)
{
bKilled = 1;
}
}

static void
doInserts()
{
char ** ppQueries;
char *  queries[] =
{
"INSERT INTO table ...",
"INSERT INTO table ...",
"INSERT INTO table ...",
NULL
};

/* Start a transaction */
issueQuery("BEGIN;");

/* For each query... */
for (ppQueries = queries; ppQueries != NULL; ppQueries++)
{
/* Issue the query */
issueQuery(*ppQueries);

/* If we've been signaled, exit loop */
if (bKilled)
{
break;
}
}

/*
 * Commit the transaction.
 *
 * Note that signal could have occurred *before* the BEGIN.  You'll need
 * to handle that case as well (or ignore the error from COMMIT)
 */
issueQuery("COMMIT;");
}