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 ...> ; ...> ...> ...> ...>
[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.
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.
Posts
