Data. It’s everywhere. We leave a trail of it everywhere we go, both on the web, and in real life, and these days there aren’t many applications that don’t use a database, even if it’s just a simple table to track high scores in a game. And while the languages Komodo supports are great for interacting with databases, if you wanted to view your database tables you needed to use a tool outside Komodo.
We wanted to add a tool to Komodo to make it easy to see what your data is doing, so we built the Database Explorer. This article will give a quick overview on how to use it. For those who prefer a demonstration, there’s also a short screencast (click to the right).
To add a new database connection, bring up the Databases tab in the sidebar, and click on the “add database” icon in the upper left. This brings up a simple properties dialog that distinguishes between file-based databases (like SQLite) and connection-based ones (like MySQL). For the latter, Komodo lets you specify whether the connection needs a password or not; this lets you distinguish a non-password account from one where the password is the empty string (true, neither is a good idea for a production database, but both are common with development databases located inside a firewall).
Once you’ve specified some databases, you can browse them in the sidebar.
In the example here, Komodo is showing the list of databases in the MySQL connection, but the list of tables in the SQLite database. Each database has a different kind of hierarchy, and the database explorer adjusts accordingly.
Inspecting and Querying
Double-clicking on a table brings up a separate window. You can browse the full table’s contents, view its schema, or create and run custom queries. One of the problems viewing this kind of data with the standard command-line tools is that there are often too many columns to fit on a single row.
This figure shows how you can choose which columns to view and which to exclude. Komodo retains this info across sessions, so you don’t have to constantly uncheck uninteresting columns.
The database explorer is a general tool, so it doesn’t know the semantics of each field. To help make sense of data, the explorer supports adding filters to each database’s adapter. For example, people often use floating point or long integers to denote timestamps in SQLite. This figure shows how you can have the database explorer reinterpret a numeric field as an SQLite date, time, or datetime field.
Komodo currently ships with support for SQLite3, and extensions for MySQL and Oracle are available at the community site, with support for PostgreSQL expected soon. All the databases have the same front-end UI in Komodo.
One cool aspect here is that each database connection is a Komodo/Mozilla extension that implements a few of Komodo’s own interfaces, including “koIDBXConnection” and “koIDBXTableConnector”. When it’s loaded, each extension is responsible for making sure it can load all of its dependencies (usually a database client library). If it’s successful, it automatically advertises its availability to Komodo.
If you’re interested in adding support for a database, please see the code for the MySQL adapter as a reference.