Generator

MetaSQL

picture by Scott Speck via Flickr

MetaSQL is SQL code that generates SQL code. This is one of my favorite techniques for tapping SQL’s power.

An Example

Suppose that you are preparing an ERwin data model. You have carefully defined the entities and attributes. But when you go to generate schema, ERwin does not carry the definitions forward. That’s frustrating since you put so much effort into them. But there is a ready solution. You can access the definitions from the ERwin metamodel and use them to generate SQL comment statements.

We did this in practice for a large production database using Netezza. In this blog we’ll look at a simple example where we use metaSQL to load table comments for MySQL.

A Sample Data Model

Here’s a simple ERwin data model. This model is an excerpt from our recent Agile Data Warehouse Design video course with O’Reilly. We defined all entities and attributes.

A Sample Data Model

MetaSQL Code

Here’s metaSQL code to generate table comment commands from ERwin. MetaSQL is not especially readable but the meaning is clear. You access the ERwin metamodel by selecting the query tool, connect to ERwin ODBC, and run the metaSQL code.

SELECT ‘ALTER TABLE ‘ || USER_FORMATTED_PHYSICAL_NAME || ‘ COMMENT ”’ || DEFINITION || ”’;’
FROM M0.ENTITY E
ORDER BY USER_FORMATTED_PHYSICAL_NAME;

This example is simple, but we have used metaSQL in other situations. For example, we wrote a generator for a data warehouse project that creates a wrapper view for each table. On another occasion we used metaSQL to generate repeated error checks for data in tables.

Generated SQL Code

Here’s the generated ERwin SQL code to add table comments. We saved the generated code in csv format, saved it in txt format with Excel, and then used a text editor to remove embedded quotes. Alternatively, the metaSQL logic could be placed within a stored procedure to eliminate the need for post-editing.

ALTER TABLE Address_Dim COMMENT ‘A physical address that may be used for delivery of an order.’;
ALTER TABLE Credit_Card_Dim COMMENT ‘Has data for customer credit cards.’;
ALTER TABLE Customer_Dim COMMENT ‘One record for each Customer.’;
ALTER TABLE Date_Dim COMMENT ‘One record for each calendar date.’;
ALTER TABLE Discount_Dim COMMENT ‘One record for each kind of Discount. Examples: none, senior.’;
ALTER TABLE Location_Dim COMMENT ‘One of the locations for Tims Sandwich Shop.’;
ALTER TABLE Order_Fact COMMENT ‘Data about an entire customer order.’;
ALTER TABLE Order_Junk_Dim COMMENT ‘Data for each payment type. Examples: cash, check, credit card. The current software  only supports credit cards.
+
Data for each Delivery Type. Examples: to an address, to a table.’;
ALTER TABLE Time_Dim COMMENT ‘One record for each moment within a day.’;
You can see the stored comments by running the command:
SELECT table_name, table_comment FROM information_schema.tables;

In Summary

SQL is a powerful language and is grossly underutilized in practice. Developers spend much effort and time on writing code that could be better handled by SQL. SQL often delivers better performance and is less prone to bugs. MetaSQL is one technique for using SQL in an advanced manner.

2 thoughts on “MetaSQL

  1. Michael,

    Just to mention that there are professional data modeling tools (e.g. SILVERRUN) available that include a built-in feature allowing the user to choose carrying the definition forward to the DDL script (and thus to major databases such as Oracle and SQL Server) as well as back from the database to the model.

    Axel

    1. I agree Axel. I need to take a detailed look at Silverrun. I’m working on a new video course for next year in which I plan to use several different data modeling tools. Silverrun is high on my list to look at for this course.

Leave a Reply

Your email address will not be published. Required fields are marked *