The sqlobject-admin Tool

author:Ian Bicking <ianb@colorstudy.com> revision:$Rev$ date:$LastChangedDate$

Contents

• The sqlobject-admin Tool
  • Introduction
    • Common Options
    • Simple Commands
  • The create Command
  • The sql Command
  • The drop Command
  • The execute Command
  • The list Command
  • The status Command
    • Versioning & Upgrading
  • Basic Usage
  • The record Command
  • The upgrade Command
    • Future

Warning

This document isn’t entirely accurate; some of what it describes are the intended features of the tool, not the actual features.

Particularly inaccurate is how modules and classes are found.

Introduction

The sqlobject-admin tool included with SQLObject allows you to manage your database as defined with SQLObject classes.

Some of the features include creating tables, checking the status of the database, recording a version of a schema, and updating the database to match the version of the schema in your code.

To see a list of commands run sqlobject-admin help. Each sub-command has -h option which explains the details of that command.

Common Options

Many of the commands share some common options, mostly for finding the database and classes.

-c CONNECTION or --connection=CONNECTION:

This takes an argument, the connection string for the database. This overrides any connection the classes have (if they are hardwired to a connection).

-f FILENAME or --config-file=FILENAME:

This is a configuration file from which to get the connection. This configuration file should be a Python-syntax file that defines a global variable database, which is the connection string for the database.

-m MODULE or --module=MODULE:

A module to look in for classes. MODULE is something like myapp.amodule. Remember to set your $PYTHONPATH if the module can’t be imported. You can provide this argument multiple times.

-p PACKAGE or --package=PACKAGE:

A package to look in. This looks in all the modules in this class and subclasses for SQLObject classes.

--class=CLASSMATCH:

This restricts the classes found to the matching classes. You may use wildcards. You can provide multiple --class arguments, and if any pattern matches the class will be included.

--egg=EGG_SPEC:

This is an Egg description that should be loaded. So if you give --egg=ProjectName it’ll load that egg, and look in ProjectName.egg-info/sqlobject.txt for some settings (like db_module and history_dir).

When finding SQLObject classes, we look in the modules for classes that belong to the module – so if you import a class from another module it won’t be “matched”. You have to indicate its original module.

If classes have to be handled in a specific order, create a soClasses global variable that holds a list of the classes. This overrides the module restrictions. This is important in databases with referential integrity, where dependent tables can’t be created before the tables they depend on.

Simple Commands

The create Command

This finds the tables and creates them. Any tables that exist are simply skipped.

It also collects data from sqlmeta.createSQL (added in svn trunk) and runs the queries after table creation. createSQL can be a string with a single SQL command, a list of SQL commands, or a dictionary with keys that are dbNames and values that are either single SQL command string or a list of SQL commands. An example follows:

class MyTable(SQLObject):
    class sqlmeta:
        createSQL = {'postgres': [
            "ALTER TABLE my_table ADD CHECK(my_field != '');",
            ]}
    myField = StringCol()

The sql Command

This shows the SQL to create all the tables.

The drop Command

Drops tables! Missing tables are skipped.

The execute Command

This executes an arbitrary SQL expression. This is mostly useful if you want to run a query against a database described by a SQLObject connection string. Use --stdin if you want to pipe commands in; otherwise you give the commands as arguments.

The list Command

Lists out all the classes found. This can help you figure out what classes you are dealing with, and if there’s any missing that you expected.

The status Command

This shows if tables are present in the database. If possible (it depends on the database) it will also show if the tables are missing any columns, or have any extra columns, when compared to the table the SQLObject class describes. It doesn’t check column types, indexes, or constraints. This feature may be added in the future.

Versioning & Upgrading

There’s two commands related to storing the schema and upgrading the database: record and upgrade.

The idea is that you record each iteration of your schema, and this gets a version number. Something like 2003-05-04a. If you are using source control you’ll check all versions into your repository; you don’t overwrite one with the next.

In addition to the on-disk record of the different schemas you have gone through, the database itself contains a record of what version it is at. By having all the versions available at once, we can upgrade from any version. But more on that later

Basic Usage

Here’s a quick summary of how you use these commands:

1. In project where you’ve never used sqlobject-admin before, you run sqlobject-admin record --output-dir=sqlobject-history. If your active database is up-to-date with the code, then the tool will add a sqlobject_db_version table to the database with the current version.
2. Now, make some updates to your code. Don’t update the database! (You could, but for now it’s more fun if you don’t.)
3. Run sqlobject-admin record --edit. A new version will be created, and an editor will be opened up.

The record Command

Record will take the SQL CREATE statements for your tables, and output them in new version. It creates the version by using the ISO-formatted date (YYYY-MM-DD) and a suffix to make it unique. It puts each table in its own file.

This normally doesn’t touch the database at all – it only records the schema as defined in your code, regardless of the database. In fact, I recommend calling record before you update your database.

The upgrade Command

Future

• Get record to do svn cp when creating a new version, then write over those files; this way the version control system will have nice diffs.
• An option to record the SQL for multiple database backends at once (now only the active backend is recorded).
• An option to upgrade databases with Python scripts instead of SQL commands. Or a little of both.
• Review all the verbosity, maybe add logging, review simulation.
• Generate simple ALTER statements for upgrade scripts, to give people something to work with. Maybe.
• A command to trim versions, by merging upgrade scripts.