Database, tell me a little about yourself

Databases start off small.  Perhaps just a couple of tables with a few dozen columns.  What could be simpler or more obvious?  Then someone adds a feature or five, a few configuration options, ports the application to a new platform, a browser interface, half a dozen different languages.  Soon enough the database is a huge mess.  Yes, yes, someone should sit down and normalize the thing.  By now the application is selling on an app store for $2, so who can afford the effort to normalize?  Maybe then a summer student could at least document the database.  Sure, and we can add the student’s efforts to a million other badly formatted, half completed database design documents in obsolete MSWORD files.

You know what would be nice?  A way to self-document a database.  The documentation should be contained in the database itself, so it can move with the database, grow with the database, and keep pace with database engine technology and popular documentation presentation.

I would like something like Doxygen for source code, but for a database.  Doxygen uses comments with a specified format, so that when the source code is read by the Doxygen application, pretty and up to data documentation is generated automatically.  The comments can be maintained in the normal course of editing the code, using any code editor without having to worry about special applications, but whenever required Doxygen can be used to generate the documentation for the latest code.

So, could we add a special table to our database which contains documentation of the other tables?  A table that can by updated by any database management tool, even raw SQL, but when read by a special application, will generate the documentation.

I mostly use SQLite for my application databases.  Paweł Salawa offers a nice SQLite database management tool SQLite Studio.  The Studio is open source, written in TCL and has a sophisticated plugin system, so it was easy for me to add what I needed.

A simple example:

Suppose you have a database with the following table

CREATE TABLE config ( pro, KEY );

Now add a new table to document this, plus any other tables.

CREATE TABLE __sqlite_studio_doc ( desc, _table, _column );

Now add the documentation to the new table

desc,   _table,   _column
True if PRO designer is running,   config,   pro
The license key,   config,   KEY
Stores configuration settings,  config,  __sqlite_studio_table

The special _column value, __sqlite_studio_table, is used for the documentation for the table itself.

Pretty simple? Well, it is if you are familiar with maintaining a SQL database.  ( If you are not, why are you reading this? )

Now to generate the documentation.

Install SQLite Studio

Install the new export plugin.  ( Download link  to sqlitestudio-2.0.13\src\plugins\export )

Run the new export plugin HTML+DOC on your database.

The output will look like this, when viewed in a browser:

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s