Determining if SQLite db exists

TonyDonker has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Determining if SQLite db exists by friedo (Prior) on Feb 28, 2005 at 15:05 UTC
You can use the -f operator to check if a file exists. (See `perldoc -f -X` for information on all the filetest operators. `sub connectdb { die "Database file does not exist" unless -f "as2con.db"; my $dbase = DBI->connect("dbi:SQLite:dbname=as2con.db", "", "", {PrintWarn => 0, PrintError => 0}); return $dbase; }` [download] By the way, module names should start with an uppercase letter. Lowercase module names are reserved for Perl pragmas.	[reply] [d/l]
Re: Determining if SQLite db exists by jZed (Prior) on Feb 28, 2005 at 15:07 UTC
First, I don't see a use DBI anywhere. Second, since SQLite databases are stored in a single file, you can check for its existence with -e $database_name. So check for the existence of 'as2cond.db' and don't even attempt the connect() if it doesn't exist.	[reply]
Re: Determining if SQLite db exists by minter (Novice) on Feb 28, 2005 at 15:08 UTC
Your best bet may be do a file test before you try to connect. Something like: `sub connectdb { unless (-r "as2con.db") { print "Database file as2con.db does not exist.\n"; return; } my $dbase = DBI->connect("dbi:SQLite:dbname=as2con.db", "", "", {P +rintWarn => 0, PrintError => 0}); return $dbase; }` [download] Then your code would check the return status of the connectdb method to see if it was undefined, and handle it appropriately.	[reply] [d/l]
Re: Determining if SQLite db exists by jfroebe (Parson) on Feb 28, 2005 at 16:17 UTC
Hi Tony If you're looking for a solution where the connect() returns a "Database file does not exist" instead of checking for the existance of the file yourself, then your best bet is to contact Mat Sergeant who wrote DBD::SQLite. Ask politely for the new feature using rt.cpan.org. This is because, as others have already mentioned, that this particular feature is DBD specific. You could always subclass DBD::SQLite and write the check yourself, but I would recommend against it unless you are familiar with Perl's ways of OOP. Do NOT modify DBD-SQLite directly yourself as the next time you update your cpan modules, it will be overwritten. Order of preference: test the file existance yourself have DBD::SQLite maintainers make the modification subclass DBD::SQLite Hope this helps Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re^2: Determining if SQLite db exists by jZed (Prior) on Feb 28, 2005 at 17:06 UTC
Mostly good advice, though I'd add this caveat: Matt probably wouldn't and shouldn't change the default behaviour. There are lots of scripts out there that depend on the automatic database creation. I suppose the best way (if it belongs in the module at all) would be to allow users to pass a "sl_no_db_create" attribute in connect() that would trigger the die on non-existant database behaviour	[reply]
SQLite (or other module) feature additions by ChemBoy (Priest) on Feb 28, 2005 at 17:29 UTC
An excellent point, to which I would only add that producing a patch to implement such a flag would almost certainly greatly enhance its likelihood of being included in a future release. (Why yes, I'm having a jargon-heavy day at work, why do you ask?) If God had meant us to fly, he would never have given us the railroads. --Michael Flanders	[reply]
Re^3: Determining if SQLite db exists by jfroebe (Parson) on Feb 28, 2005 at 17:09 UTC
I totally agree :) Setting an attribute for this behavior would be best - if such a change to DBD::SQLite were to be made Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re: Determining if SQLite db exists by osunderdog (Deacon) on Feb 28, 2005 at 15:51 UTC
I almost responded with the ubiquitous "use `-f`", however I thought that perhaps the OP was looking for a more dbd independent solution. The only reason you know that it is file based access is that he's using "dbi:SQLite". In other words, if the OP was using "dbi:Oracle" or "dbi:mysql" is there an answer? *"Look, Shiny Things!"* is not a better business strategy than compatibility and reuse.	[reply] [d/l]
Re^2: Determining if SQLite db exists by gellyfish (Monsignor) on Feb 28, 2005 at 16:09 UTC
Part of the problem here is that there isn't any particular DBMS independent way of checking whether a database exists - you may be able to use the DBI `data_sources` method, however some database systems (such as SQLite) don't necessarily know this information, others (such as Informix and MS SQL Server) might allow you to query a table such as `sysdatabases`. So `data_sources()` might be your man but it definitely comes with some caveats. /J\	[reply] [d/l] [select]
Re^2: Determining if SQLite db exists by Corion (Patriarch) on Feb 28, 2005 at 15:57 UTC
None of the other DBDs implicitly create files. You get an error with them, like Database does not exist or something to that intent.	[reply]
Re^2: Determining if SQLite db exists by jZed (Prior) on Feb 28, 2005 at 16:06 UTC
For most DBDs, wrap the connect() in an eval and use RaiseError or check DBI->errstr since most of the others die if an attempt is made to connect to a non-existant database. (though, aside, "database" is a very vague term here, it's actually what the standard calls a schema)	[reply]