Hack 92. Deploy Applications

If you are in the business of distributing database-driven applications, you need a few techniques to support initial database installation and subsequent schema updates.

In your development environment, all your attempts to install and update your own applications may work perfectly, but this does not mean that when a client picks up your application they will have the same success.

11.3.1. Namespace Management

One significant problem is that your client may not have administrator rights over their database system. They may be on a hosted system where their ISP allocated them a single username in, for instance, MySQL, and the user has to put all his database applications into a single database. Within this database, all tables and views have to be uniquely named. This is problematic if all the applications that user might install (web log software, forum software, etc.) insist on having a table called USERS.

Some applications fail to operate correctly in a shared namespace environment. There are other solutions, and they are not difficult to implement.

The open source project phpBB is a web-based bulletin board program. phpBB uses a simple but effective way to share the namespace. It has a file of configuration settings which was built when the package was first installed. One of the options is to give each table a prefix. The default prefix is phpbb_. This means that when the application is installed or updated, a table called users becomes phpbb_users. This has the added benefit that you can install phpbb more than once in the same account with, for example, the prefix for the first install being phpbb_v1_ and the prefix for the second install being phpbb_v2_.

With care, this approach need not cause significant problems in the packaging of your queries and applications. Once you know the desired prefix, make a sweep through your queries and insert the prefix at install time. If you distribute your table names as THE_TABLE_PREFIX_USERS, you can update it quickly with a simple search and replace to phpbb_v1_USERS. You simply have to ensure that your original string is unusual enough so as not to appear accidentally in another context. Another approach is to have the table names combined with a program variable, and then to set the variable to the new prefix.

11.3.2. Keep Your CREATE Script

When I am working on a new application one of the first things I do is to create a file called mktable.sql that has the CREATE script for every table being used. Of course, I never get the database schema right the first time, so I have to edit and run the mktable.sql script many times before I have it the way I want it:

DROP TABLE casting; DROP TABLE movie; DROP TABLE actor; CREATE TABLE actor (id INTEGER NOT NULL ,name VARCHAR(35) ,PRIMARY KEY (id) ); CREATE INDEX actor_name ON actor(name); CREATE TABLE movie (id INTEGER NOT NULL ,title VARCHAR(70) ,yr DECIMAL(4) ,score FLOAT ,votes INTEGER ,director INTEGER ,PRIMARY KEY (id) ,FOREIGN KEY (director) REFERENCES actor(id) ); CREATE INDEX movie_title ON movie(title); CREATE TABLE casting (movieid INTEGER NOT NULL ,actorid INTEGER NOT NULL ,ord INTEGER ,PRIMARY KEY (movieid, actorid) ,FOREIGN KEY (movieid) REFERENCES movie(id) ,FOREIGN KEY (actorid) REFERENCES actor(id) );

Notice that the DROP commands come before the CREATE commands. This means you will get error messages the first time you run the script, but the tables will still be created. If you change your mind about a column name, you edit the mktables.sql script and run the whole thing again; the old versions of the tables will be removed before the new ones are created. Also notice that the DROPs are in the reverse order to the CREATEs. This will ensure that foreign key dependencies do not hinder the dropping of tables.

You don't have to drop the tables. When you change your mind about a column name or type you could run an ALTER command; the advantage is that it will preserve your data, but the disadvantage is that you do not have a clean copy of your schema. If you want to start fresh to see what the customer's experience would be like, you have to export the ALTERed table definition and then reload the definitions. And other people may have run ALTER commands besides you, or you may have run a few ALTER commands to test something and then forgotten to revert the schema. It is much simpler to perform version control with a CREATE script.

During development you can include the test data in your mktable.sql script. Don't forget to take it out again before you release (unless it is helpful for your users to keep the test data in)!

In some cases, issuing the DROP TABLE before the table exists may cause the script to abort. If you want to test to see whether the table exists, and do the DROP only if the table exists, you can do one of the following.

11.3.2.1. SQL Server

In SQL Server, you can check the sysobjects table:

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[tablename]') AND OBJECTPROPERTY(id, N'IsTable') = 1) DROP TABLE [dbo].[tablename] ;

11.3.2.2. MySQL

MySQL lets you append IF EXISTS to the DROP TABLE statement:

DROP TABLE IF EXISTS tablename ;

11.3.2.3. Oracle

Oracle lets you use exception handling for this:

begin execute immediate 'DROP TABLE tablename'; Exception when others then null; end; /

11.3.2.4. PostgreSQL

PostgreSQL is moving toward using the DROP TABLE IF EXISTS approach shown in the MySQL example. In the meantime, you can still have the functionality of IF EXISTS using plpgsql. In order to do this, the postgres user needs to run the following script at the Linux prompt:

$ createlang plpgsql dbname

Once the plpgsql language has been activated for your particular database, you can create a function that mimics IF EXISTS:

CREATE or REPLACE function drop_table (varchar) returns varchar as ' DECLARE tname alias for $1; rows int4; BEGIN SELECT into rows count(*) from pg_class where relname = tname; if rows > 0 then execute 'DROP TABLE ' || tname; return tname || ' DROPPED'; end if; return tname || ' does not exist'; END;' language 'plpgsql' ;

To use this you can do:

scott=> select drop_table('tablename'); drop_table ------------ tablename DROPPED (1 row)

11.3.3. Portability

It would be nice to run applications using any database without change. In practice, it is not always possible, but there are some things you can do to make the porting easier:

NOT NULL

You should not need to specify that primary key fields are not null, as the restriction is enforced whether you do or not. However, SQL Server insists that you do. The other engines do not care. It is useful for the sake of portability to put in the constraint anyway.

FOREIGN KEY

If the foreign key references are placed alongside the column name definition, MySQL silently ignores the foreign key references. The following will not work properly in MySQL:

CREATE TABLE job ( jobtitle VARCHAR(20) PRIMARY KEY, salary DECIMAL(8,2) ) ENGINE=InnoDB; CREATE TABLE employ ( empname VARCHAR(20) PRIMARY KEY, jtitle VARCHAR(20) REFERENCES job(jobtitle) ) ENGINE=InnoDB;

Instead, you must put foreign key constraints after the column name definitions:

CREATE TABLE job ( jobtitle VARCHAR(20) PRIMARY KEY, salary DECIMAL(8,2) ) ENGINE=InnoDB; CREATE TABLE employ ( empname VARCHAR(20), jtitle VARCHAR(20), PRIMARY KEY (empname), FOREIGN KEY (jtitle) REFERENCES job(jobtitle) ) ENGINE=InnoDB;

11.3.4. DROP Avoiding Constraints

Sometimes you want to drop the entire schema and replace it with the new one. However, deleting a schema is not always easy.

In your database, you have a simple dependency:

CREATE TABLE T1 ( x VARCHAR(30), PRIMARY KEY (x) ); CREATE TABLE T2 ( y VARCHAR(30), PRIMARY KEY (y), FOREIGN KEY (y) REFERENCES t1(x) );

DROP TABLE should drop these tables and their contents:

SQL> DROP TABLE t1; ORA-02449: unique/primary keys in table referenced by foreign keys SQL> DROP TABLE t2; Table dropped.

So the drop worked for t2, but not for t1. In this case, you can retry the DROP for t1 and it will succeed the second time. This was caused by the foreign key constraint in t2. The easy solution to this problem is to always reverse the order of the drop statement in comparison to the create statements. In this case, drop t2 before t1:

DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 ( x VARCHAR(30), PRIMARY KEY (x) ); CREATE TABLE t2 ( y VARCHAR(30), PRIMARY KEY (y), FOREIGN KEY (y) REFERENCES t1(x) );

It is not always possible to choose a drop order that will drop the entire schema:

CREATE TABLE T1 ( x VARCHAR(30), PRIMARY KEY (x) ); CREATE TABLE T2 ( y VARCHAR(30), PRIMARY KEY (y), FOREIGN KEY (y) REFERENCES t1(x) ); ALTER TABLE t1 ADD CONSTRAINT t1_1 FOREIGN KEY (x) REFERENCES t2(y) ;

In the preceding example, t1 and t2 each reference the other. There are different ways of dealing with this problem. For one, a database system may have a way of forcing the DROP. In Oracle, for instance, you can instruct it to drop a table, and if necessary drop any constraints which stop it from being dropped:

DROP TABLE t1 CASCADE CONSTRAINTS; DROP TABLE t2;

PostgreSQL uses a slightly different syntax:

DROP TABLE t1 CASCADE; DROP TABLE t2;

In MySQL, you can instruct the system to stop checking constraints until the transaction is over, which allows you to delete the tables normally:

SET FOREIGN_KEY_CHECKS = 0; DROP TABLE t1; DROP TABLE t2; SET FOREIGN_KEY_CHECKS = 1;

In SQL Server, there is no CASCADE option. You can find out the dependencies by running:

sp_help t1

If all else fails, you can instead name each of your constraints, and delete them first before deleting the tables themselves:

ALTER TABLE t1 DROP CONSTRAINT t1_1; DROP TABLE t2; DROP TABLE t1;