Re: [sqlite] counting tables
At 18:02 03/07/2008, Simon Davies wrote: >Hi Tim, > >select count(*) from sqlite_master; > >will return 0 on a brand new db > >Rgds, >Simon Thanks - that's a handy pointer. At 18:04 03/07/2008, Igor Tandetnik wrote: >Tim Streater <[EMAIL PROTECTED]> >wrote: > > As a work around I thought I might check that the newly opened db has > > no tables, and clean up if so. My question is, what query can I make > > of the db to determine that it has no tables? > >select exists (select * from sqlite_master where type='table'); > >Igor Tandetnik Aha - another useful clue. At 19:04 03/07/2008, Stephen Woodbridge wrote: >Why don't you check for the existence of the file using PHP BEFORE you >open it in? Trivial file exist test in PHP. OK, OK, red faces on this side of the keyboard. Still, I'm glad I asked as the info I got back has allowed me to add extra sanity checks. Thanks all. -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] counting tables
Tim Streater wrote: > > I have a PHP script that I run in Terminal under OS X, which opens an > sqlite db and works on it. Trouble is, if the db doesn't exist then > the PHP library silently creates an empty db with the given name. > Given this, I need to detect that the empty db has been created, > delete it, and exit. > > As a work around I thought I might check that the newly opened db has > no tables, and clean up if so. My question is, what query can I make > of the db to determine that it has no tables? I couldn't find one (or > perhaps I don't know SQL well enough). If I run sqlite3 I can give it > the .tables command - I suppose I could have a call to this module > from my script, but I'd prefer to avoid that. Alternatively is then > another approach? > Another way to handle this situation is to use the user version (see http://www.sqlite.org/pragma.html#version) in SQLite to check if the database has been initialized. On an empty database the user version is zero. pragam user_version; 0 After you have initialized the database you can change the user version to some other value, say 1. To ensure the database is completely initialized this should be done in a transaction. begin; -- initialize tables etc pragma user_version = 1; commit; Now you can check if the database has been initialized or not, by checking the user_version. If you really want to delete empty database files, you can simply delete those with user version of zero, but it may be just as effective to simply initialize those files. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] counting tables
Tim Streater <[EMAIL PROTECTED]> wrote: > As a work around I thought I might check that the newly opened db has > no tables, and clean up if so. My question is, what query can I make > of the db to determine that it has no tables? select exists (select * from sqlite_master where type='table'); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] counting tables
Tim Streater wrote: > Hi, > > I have a PHP script that I run in Terminal under OS X, which opens an sqlite > db and works on it. Trouble is, if the db doesn't exist then the PHP library > silently creates an empty db with the given name. Given this, I need to > detect that the empty db has > been created, delete it, and exit. > > As a work around I thought I might check that the newly opened db has no > tables, and clean up if so. My question is, what query can I make of the db > to determine that it has no tables? I couldn't find one (or perhaps I don't > know SQL well enough). If I > run sqlite3 I can give it the .tables command - I suppose I could have a call > to this module from my script, but I'd prefer to avoid that. Alternatively is > then another approach? Why don't you check for the existence of the file using PHP BEFORE you open it in? Trivial file exist test in PHP. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] counting tables
Hi Tim, select count(*) from sqlite_master; will return 0 on a brand new db Rgds, Simon 2008/7/3 Tim Streater <[EMAIL PROTECTED]>: > Hi, > > I have a PHP script that I run in Terminal under OS X, which opens an sqlite > db and works on it. Trouble is, if the db doesn't exist then the PHP library > silently creates an empty db with the given name. Given this, I need to > detect that the empty db has > been created, delete it, and exit. > > As a work around I thought I might check that the newly opened db has no > tables, and clean up if so. My question is, what query can I make of the db > to determine that it has no tables? I couldn't find one (or perhaps I don't > know SQL well enough). If I > run sqlite3 I can give it the .tables command - I suppose I could have a call > to this module from my script, but I'd prefer to avoid that. Alternatively is > then another approach? > > Thanks, > > -- Tim > > ___ > 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
[sqlite] counting tables
Hi, I have a PHP script that I run in Terminal under OS X, which opens an sqlite db and works on it. Trouble is, if the db doesn't exist then the PHP library silently creates an empty db with the given name. Given this, I need to detect that the empty db has been created, delete it, and exit. As a work around I thought I might check that the newly opened db has no tables, and clean up if so. My question is, what query can I make of the db to determine that it has no tables? I couldn't find one (or perhaps I don't know SQL well enough). If I run sqlite3 I can give it the .tables command - I suppose I could have a call to this module from my script, but I'd prefer to avoid that. Alternatively is then another approach? Thanks, -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
