pdoconsole

pdoconsole is a very basic database administration console using PHP Data Objects (PDO). I wrote it because I got annoyed by having to deal with incompatibilities between versions of SQLite.

PDO uses SQLite version 3.2.8, which supports ALTER TABLE ADD COLUMN. The version of the SQLite console that comes with Fedora, which I use at work, is 3.1.4, which doesn't support ALTER TABLE ADD COLUMN. The version in Debian unstable, 3.3.5, supports ALTER TABLE ADD COLUMN but its file format is completely incompatible with 3.2.8.

Now, I could write a PHP script to add columns to tables. But I wanted something more flexible, hence pdoconsole. Pass it a connection string and you can use it like the console that presumably comes with your database backend.

Since pdoconsole uses only PDO, it doesn't have very many features. It can't list the fields in a table or the tables in a database. (Unless you're using MySQL, of course; then you can use the SHOW TABLES and DESCRIBE statements to do those things.) The version of PHP that I have doesn't have readline compiled in, so pdoconsole doesn't use readline, which makes correcting mistakes a pain, since there's no input history. For this reason, pdoconsole looks for a program called 'rlwrap', either in the directories specified in the PATH environment variable or in the current directory. If it finds one, it calls it with itself as an argument. (For example, if invoked as ./pdoconsole sqlite:foo, it will execute rlwrap /usr/bin/php ./pdoconsole sqlite:foo. (It uses the constant PHP_BINDIR to guess where the PHP interpreter is; this is not foolproof.) To disable this behavior set the environment variable PDOCONSOLE_NO_RLWRAP.

Also note that if you're using the CGI version of PHP (as opposed to the CLI version), you might have to make some changes to get this to work.

I also make no guarantee that pdoconsole will work for you, or that it won't do terrible things to your database. I cannot be responsible for terrible things that pdoconsole does to your database.

That said, you can get the code here. Save as pdoconsole.php, make executable, and type pdoconsole <connection-string> (where of course <connection-string> is replaced by the connection string that you use to access your database. (Note that this means that you have to type your database password on the command line. Be careful with this; in particular, clear your shell command history afterward.)

Now you can type SQL statements and execute them by hitting the enter key. If you execute a SELECT (or some other statement that returns rows), pdoconsole will print a table containing the result. Note that, like the MySQL console, pdoconsole has to pass through all results to figure out the widths of the columns before it can start printing the table.