Schema Generation Scripts

While some project teams include a full-time database designer who oversees the design and implementation of the database, many projects take the “design-by-committee” approach, allowing multiple people to create database objects. After several weeks or months of development, you may need to generate a script that will create the various tables, indexes, views, and so on that the team has deployed. Although a variety of tools and utilities will generate these types of scripts for you, you can also query the information_schema views and generate the script yourself.

As an example, let’s build a script that will create the sakila.category table. Here’s the command used to build the table, which I extracted from the script used to build the example database:

CREATE TABLE category (
  category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(25) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (category_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Although it would certainly be easier to generate the script with the use of a procedural language (e.g., Transact-SQL or Java), since this is a book about SQL I’m going to write a single query that will generate the create table statement. The first step is to query the information_schema.columns table to retrieve information about the columns in the table:

mysql> SELECT 'CREATE TABLE category (' create_table_statement
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->  (SELECT concat('  ',column_name, ' ', column_type,
    ->    CASE
    ->      WHEN is_nullable = 'NO' THEN ' not null'
    ->      ELSE ''
    ->    END,
    ->    CASE
    ->      WHEN extra IS NOT NULL AND extra LIKE 'DEFAULT_GENERATED%'
    ->       THEN concat(' DEFAULT ',column_default,substr(extra,18))
    ->      WHEN extra IS NOT NULL THEN concat(' ', extra)
    ->      ELSE ''
    ->    END,
    ->    ',') txt
    ->   FROM information_schema.columns
    ->   WHERE table_schema = 'sakila' AND table_name = 'category'
    ->   ORDER BY ordinal_position
    ->  ) cols
    -> UNION ALL
    -> SELECT ')';
+-----------------------------------------------------------------------+
| create_table_statement                                                |
+-----------------------------------------------------------------------+
| CREATE TABLE category (                                               |
|   category_id tinyint(3) unsigned not null auto_increment,            |
|   name varchar(25) not null ,                                         |
|   last_update timestamp not null DEFAULT CURRENT_TIMESTAMP                    |
|     on update CURRENT_TIMESTAMP,                                      |
| )                                                                     |
+-----------------------------------------------------------------------+
5 rows in set (0.00 sec)

Well, that got us pretty close; we just need to add queries against the table_constraints and key_column_usage views to retrieve information about the primary key constraint:

mysql> SELECT 'CREATE TABLE category (' create_table_statement
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->  (SELECT concat('  ',column_name, ' ', column_type,
    ->    CASE
    ->      WHEN is_nullable = 'NO' THEN ' not null'
    ->      ELSE ''
    ->    END,
    ->    CASE
    ->      WHEN extra IS NOT NULL AND extra LIKE 'DEFAULT_GENERATED%'
    ->        THEN concat(' DEFAULT ',column_default,substr(extra,18))
    ->      WHEN extra IS NOT NULL THEN concat(' ', extra)
    ->      ELSE ''
    ->    END,
    ->    ',') txt
    ->   FROM information_schema.columns
    ->   WHERE table_schema = 'sakila' AND table_name = 'category'
    ->   ORDER BY ordinal_position
    ->  ) cols
    -> UNION ALL
    -> SELECT concat('  constraint primary key (')
    -> FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila' AND table_name = 'category'
    ->   AND constraint_type = 'PRIMARY KEY'
    -> UNION ALL
    -> SELECT cols.txt
    -> FROM
    ->  (SELECT concat(CASE WHEN ordinal_position > 1 THEN '   ,'
    ->     ELSE '    ' END, column_name) txt
    ->   FROM information_schema.key_column_usage
    ->   WHERE table_schema = 'sakila' AND table_name = 'category'
    ->     AND constraint_name = 'PRIMARY'
    ->   ORDER BY ordinal_position
    ->  ) cols
    -> UNION ALL
    -> SELECT '  )'
    -> UNION ALL
    -> SELECT ')';
+-----------------------------------------------------------------------+
| create_table_statement                                                |
+-----------------------------------------------------------------------+
| CREATE TABLE category (                                               |
|   category_id tinyint(3) unsigned not null auto_increment,            |
|   name varchar(25) not null ,                                         |
|   last_update timestamp not null DEFAULT CURRENT_TIMESTAMP            |
|     on update CURRENT_TIMESTAMP,                                      |
|   constraint primary key (                                            |
|     category_id                                                       |
|   )                                                                   |
| )                                                                     |
+-----------------------------------------------------------------------+
8 rows in set (0.02 sec)

To see whether the statement is properly formed, I’ll paste the query output into the mysql tool (I’ve changed the table name to category2 so that it won’t step on our existing table):

mysql> CREATE TABLE category2 (
    ->   category_id tinyint(3) unsigned not null auto_increment,
    ->   name varchar(25) not null ,
    ->   last_update timestamp not null DEFAULT CURRENT_TIMESTAMP 
    ->     on update CURRENT_TIMESTAMP,
    ->   constraint primary key (
    ->     category_id
    ->   )
    -> );
Query OK, 0 rows affected (0.61 sec)

The statement executed without errors, and there is now a category2 table in the sakila database. In order for the query to generate a well-formed create table statement for any table, more work is required (such as handling indexes and foreign key constraints), but I’ll leave that as an exercise.

Deployment Verification

Many organizations allow for database maintenance windows, wherein existing database objects may be administered (such as adding/dropping partitions) and new schema objects and code can be deployed. After the deployment scripts have been run, it’s a good idea to run a verification script to ensure that the new schema objects are in place with the appropriate columns, indexes, primary keys, and so forth. Here’s a query that returns the number of columns, number of indexes, and number of primary key constraints (0 or 1) for each table in the sakila schema:

mysql> SELECT tbl.table_name,
    ->  (SELECT count(*) FROM information_schema.columns clm
    ->   WHERE clm.table_schema = tbl.table_schema
    ->     AND clm.table_name = tbl.table_name) num_columns,
    ->  (SELECT count(*) FROM information_schema.statistics sta
    ->   WHERE sta.table_schema = tbl.table_schema
    ->     AND sta.table_name = tbl.table_name) num_indexes,
    ->  (SELECT count(*) FROM information_schema.table_constraints tc
    ->   WHERE tc.table_schema = tbl.table_schema
    ->     AND tc.table_name = tbl.table_name
    ->     AND tc.constraint_type = 'PRIMARY KEY') num_primary_keys
    -> FROM information_schema.tables tbl
    -> WHERE tbl.table_schema = 'sakila' AND tbl.table_type = 'BASE TABLE'
    -> ORDER BY 1;
+---------------+-------------+-------------+------------------+
| TABLE_NAME    | num_columns | num_indexes | num_primary_keys |
+---------------+-------------+-------------+------------------+
| actor         |           4 |           2 |                1 |
| address       |           9 |           3 |                1 |
| category      |           3 |           1 |                1 |
| city          |           4 |           2 |                1 |
| country       |           3 |           1 |                1 |
| customer      |           9 |           7 |                1 |
| film          |          13 |           4 |                1 |
| film_actor    |           3 |           3 |                1 |
| film_category |           3 |           3 |                1 |
| film_text     |           3 |           3 |                1 |
| inventory     |           4 |           4 |                1 |
| language      |           3 |           1 |                1 |
| payment       |           7 |           4 |                1 |
| rental        |           7 |           7 |                1 |
| staff         |          11 |           3 |                1 |
| store         |           4 |           3 |                1 |
+---------------+-------------+-------------+------------------+
16 rows in set (0.01 sec)

You could execute this statement before and after the deployment and then verify any differences between the two sets of results before declaring the deployment a success.

Dynamic SQL Generation

Some languages, such as Oracle’s PL/SQL and Microsoft’s Transact-SQL, are supersets of the SQL language, meaning that they include SQL statements in their grammar along with the usual procedural constructs, such as “if-then-else” and “while.” Other languages, such as Java, include the ability to interface with a relational database, but do not include SQL statements in the grammar, meaning that all SQL statements must be contained within strings.

Therefore, most relational database servers, including SQL Server, Oracle Database, and MySQL, allow SQL statements to be submitted to the server as strings. Submitting strings to a database engine rather than utilizing its SQL interface is generally known as dynamic SQL execution. Oracle’s PL/SQL language, for example, includes an execute immediate command, which you can use to submit a string for execution, while SQL Server includes a system stored procedure called sp_executesql for executing SQL statements dynamically.

MySQL provides the statements prepare, execute, and deallocate to allow for dynamic SQL execution. Here’s a simple example:

mysql> SET @qry = 'SELECT customer_id, first_name, last_name FROM customer';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE dynsql1 FROM @qry;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE dynsql1;
+-------------+-------------+--------------+
| customer_id | first_name  | last_name    |
+-------------+-------------+--------------+
|         505 | RAFAEL      | ABNEY        |
|         504 | NATHANIEL   | ADAM         |
|          36 | KATHLEEN    | ADAMS        |
|          96 | DIANA       | ALEXANDER    |
...
|          31 | BRENDA      | WRIGHT       |
|         318 | BRIAN       | WYMAN        |
|         402 | LUIS        | YANEZ        |
|         413 | MARVIN      | YEE          |
|          28 | CYNTHIA     | YOUNG        |
+-------------+-------------+--------------+
599 rows in set (0.02 sec)

mysql> DEALLOCATE PREPARE dynsql1;
Query OK, 0 rows affected (0.00 sec)

The set statement simply assigns a string to the qry variable, which is then submitted to the database engine (for parsing, security checking, and optimization) using the prepare statement. After executing the statement by calling execute, the statement must be closed using deallocate prepare, which frees any database resources (e.g., cursors) that have been utilized during execution.

The next example shows how you could execute a query that includes placeholders so that conditions can be specified at runtime:

mysql> SET @qry = 'SELECT customer_id, first_name, last_name 
  FROM customer WHERE customer_id = ?';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE dynsql2 FROM @qry;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @custid = 9;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql2 USING @custid;
+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           9 | MARGARET   | MOORE     |
+-------------+------------+-----------+
1 row in set (0.00 sec)

mysql> SET @custid = 145;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql2 USING @custid;
+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|         145 | LUCILLE    | HOLMES    |
+-------------+------------+-----------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE dynsql2;
Query OK, 0 rows affected (0.00 sec)

In this sequence, the query contains a placeholder (the ? at the end of the statement) so that the customer ID value can be submitted at runtime. The statement is prepared once and then executed twice, once for customer ID 9, and again for customer ID 145, after which the statement is closed.

What, you may wonder, does this have to do with metadata? Well, if you are going to use dynamic SQL to query a table, why not build the query string using metadata rather than hardcoding the table definition? The following example generates the same dynamic SQL string as the previous example, but it retrieves the column names from the information_schema.columns view:

mysql> SELECT concat('SELECT ',
    ->   concat_ws(',', cols.col1, cols.col2, cols.col3, cols.col4,
    ->     cols.col5, cols.col6, cols.col7, cols.col8, cols.col9),
    ->   ' FROM customer WHERE customer_id = ?')
    -> INTO @qry
    -> FROM
    ->  (SELECT
    ->     max(CASE WHEN ordinal_position = 1 THEN column_name
    ->       ELSE NULL END) col1,
    ->     max(CASE WHEN ordinal_position = 2 THEN column_name
    ->       ELSE NULL END) col2,
    ->     max(CASE WHEN ordinal_position = 3 THEN column_name
    ->       ELSE NULL END) col3,
    ->     max(CASE WHEN ordinal_position = 4 THEN column_name
    ->       ELSE NULL END) col4,
    ->     max(CASE WHEN ordinal_position = 5 THEN column_name
    ->       ELSE NULL END) col5,
    ->     max(CASE WHEN ordinal_position = 6 THEN column_name
    ->       ELSE NULL END) col6,
    ->     max(CASE WHEN ordinal_position = 7 THEN column_name
    ->       ELSE NULL END) col7,
    ->     max(CASE WHEN ordinal_position = 8 THEN column_name
    ->       ELSE NULL END) col8,
    ->     max(CASE WHEN ordinal_position = 9 THEN column_name
    ->       ELSE NULL END) col9
    ->   FROM information_schema.columns
    ->   WHERE table_schema = 'sakila' AND table_name = 'customer'
    ->   GROUP BY table_name
    ->  ) cols;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @qry;
+--------------------------------------------------------------------+
| @qry                                                               |
+--------------------------------------------------------------------+
| SELECT customer_id,store_id,first_name,last_name,email,
    address_id,active,create_date,last_update 
  FROM customer WHERE customer_id = ?                                |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE dynsql3 FROM @qry;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @custid = 45;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE dynsql3 USING @custid;
+-------------+----------+------------+-----------+-----------------------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email                             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-----------------------------------+------------+--------+---------------------+---------------------+
|          45 |        1 | JANET      | PHILLIPS  | [email protected] |         49 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
+-------------+----------+------------+-----------+-----------------------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE dynsql3;
Query OK, 0 rows affected (0.00 sec)

The query pivots the first nine columns in the customer table, builds a query string using the concat and concat_ws functions, and assigns the string to the qry variable. The query string is then executed as before.

Exercise 15-1

Write a query that lists all of the indexes in the sakila schema. Include the table names.

Exercise 15-2

Write a query that generates output that can be used to create all of the indexes on the sakila.customer table. Output should be of the form:

"ALTER TABLE <table_name> ADD INDEX <index_name> (<column_list>)"