DBI::SQLite and dbish

Today I needed to have a SQLite db with foreign keys and realise that my server's SQLite3 is very old (3.3.6). My perl DBD::SQLite is using version 3.7.6. I read in the DBD::SQLite that you can access to the SQLite db using dbish (a shell wrapper to DBI::Shell).

I wanted to use the same SQLite version in shell and scripts. Before installing a new version of sqlite3 I tried dbish.

dbish is part of DBI::Shell. It will be installed in your local-lib/bin when you install DBI::Shell.

First encounter was disappointing: following the POD it was not working at all

$   dbish dbi:SQLite:test.db

     DBI::Shell 11.95 using DBI 1.611

     WARNING: The DBI::Shell interface and functionality are
     =======  very likely to change in subsequent versions!


     Connecting to 'dbi:SQLite:test.db' as ''...
     @dbi:SQLite:test.db> table_info
     @dbi:SQLite:test.db> quit
     @dbi:SQLite:test.db> exit
     @dbi:SQLite:test.db> help
     @dbi:SQLite:test.db> type_info
     @dbi:SQLite:test.db> help
     @dbi:SQLite:test.db>

None of the comands worked :-(.


Googling around I found that the book 'Programming the Perl DBI' By Alligator Descartes, Tim Bunce has a chapter about it and discovered that the commnads must be preceded with a '/'.

@dbi:SQLite:srf2cram.db> /help
Defined commands, in alphabetical order: 
  [/;]chistory   display command history 
  [/;]clear      erase the current statement 
  [/;]col_info   display columns that exist in current database 
  [/;]commit     commit changes to the database 
  [/;]connect    connect to another data source/DSN 
  [/;]count      execute 'select count(*) from table' (on each table listed). 
  [/;]current    display current statement 
  [/;]describe   display information about a table (columns, data types). 
  [/;]do         execute the current (non-select) statement 
  [/;]drivers    display available DBI drivers 
  [/;]edit       edit current statement in an external editor 
  [/;]exit       exit 
  [/;]format     set display format for selected data (Neat|Box) 
  [/;]get        make a previous statement current again 
  [/;]go         execute the current statement 
  [/;]help       display this list of commands 
  [/;]history    display combined command and result history 
  [/;]load       load a file from disk to the current buffer. 
  [/;]option     display or set an option value 
  [/;]perl       evaluate the current statement as perl code 
  [/;]ping       ping the current connection 
  [/;]primary_key_info display primary keys that exist in current database 
  [/;]prompt     change the displayed prompt 
  [/;]quit       exit 
  [/;]redo       re-execute the previously executed statement 
  [/;]rhistory   display result history 
  [/;]rollback   rollback changes to the database 
  [/;]run        load a file from disk to current buffer, then executes. 
  [/;]save       save the current buffer to a disk file. 
  [/;]spool      send all output to a disk file. usage: spool file name or spool off. 
  [/;]table_info display tables that exist in current database 
  [/;]trace      set DBI trace level for current database 
  [/;]type_info  display data types supported by current server 
Commands can be abbreviated. 

dbish is interesting because I can interact whith the databases and test the DBI commands interactively, but I think that with SQLite3 I have more control and is better documented. So I ended installing the latest version of SQLite3.

[update]

I downloaded the latest precompiled sqlite3 and it was not working properly in my CentOS release 5.4: the cursor got detached from the line and was able to move through all the screen (not allowing up for previous history). Also was not quiting.

$  sqlite3 test.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .q
   ...> ;
   ...>
   ...>
   ...>
   ...>

I killed manually and compiled from the 'amalgamation' download and now works fine.


[edit2]

After reading this question in StackOverflow about how to set by default the foreign keys for a database, I am having second thoughts about using foreign keys with SQLite:

No, not even with compile-time options.

The only way, so far, is to use pragma foreign_keys=on at run time. The particular danger is that every application that touches the database has to do that.

If a specific application doesn't run that pragma statement, it can insert data that will violate foreign key constraints, and other applications won't know it. That is, turning on foreign keys doesn't warn you of existing data that violates the constraint.