DataFaucet ORM

Data Definition Language (DDL)

The SQL standard is composed of two sets of syntax, alternatively known as Data Manipulation Language (DML) and Data Definition Language (DDL). DML consists of the insert, update, select and delete statements which are collectively the most common tasks performed with SQL. DDL consists of create, drop and alter statements which allow the structure of a database to be altered.

Most web application developers don't use DDL directly, instead creating tables, views and other database entities though the use of client tools provided by the database vendor or a third party. The reason DDL syntax has been implemented in DataFaucet is that like DML most database vendors don't adhere to the standard for DDL syntax.

The problem this creates becomes apparent when a web application development company attempts to sell their application to a client who uses a different database platform than the developer. With even a small application the task of installing the database elements can become a time-consuming chore including hours of research. Most importantly hours spent during installation are usually not billable hours, thus using DataFaucet's DDL support can rein in some of the costs involved in selling to clients with different platforms.

DDL can be executed using XML tags that are parsed either via the parseDDL method of the Datasource component or using the CF_DDL custom tag (which uses the Datasource object).

All DDL actions are performed through the use of <create> and <drop> tags. Most of the XML elements have a "name" attribute to identify the created or dropped database entity. The exception to this rule is that when creating or dropping entities which are part of a table (columns, constraints and triggers) the name of the table is specified with a "table" attribute. Columns and parameters are specified with <col> and <param> tags respectively (these are shortcuts and it is acceptable to specify a <column> or <parameter> tag instead).

Examples:

<cfmodule template="/datafaucet/ddl.cfm" return="MySqlScript">
  <ddl>
    <!-- create a table with a single-column primary key -->
    <create name="mytable">
      <!-- use an auto-number column if the database supports them -->
      <col name="myid" type="int" key="true" autonumber="true" />
    </create>
    
    <!-- create a more complicated table -->
    <create name="mytable" replace="true">
      <col name="pk" type="nvarchar(35)" required="true" />
      <col name="uid" type="nvarchar(35)" required="true" />
      <col name="name" type="nvarchar(100)" unique="true" />
      <col name="num" type="integer" required="true" default="1" />
      
      <!-- 
      create a long-varchar column 
        oracle = nclob 
        mssql = ntext 
        access = memo 
        mysql = nvarchar(2000) -->
      <col name="contractText" type="nlongvarchar" required="false" />
      
      <!-- create a multi-column primary key -->
      <constraint name="mytable_pk" primarykey="pk,uid" />
      
      <!-- create a trigger in the current table -->
      <trigger name="mytable_tr_ins" action="insert">
        select * from inserted
      </trigger>
    </create>
    
    <!-- constraints may be created outside of a create-table tag -->
    <create type="constraint" table="mytable" 
      name="mytable_pk" primarykey="pk,uid" />
    <create type="constraint" table="mytable" 
      name="mytable_ref" foreignkey="pk,uid" reftable="othertable" />
    
    <!-- triggers may be created outside of a create-table tag -->
    <create type="trigger" table="mytable" 
    name="mytable_tr_ins" action="insert">
      select * from inserted
    </create>
    
    <!-- columns may be added to existing tables -->
    <create type="column" table="mytable">
      <col name="num" type="integer" />
    </create>
    
    <!-- columns, constraints and triggers 
      may be removed from existing tables -->
    <drop type="column" table="mytable" column="num" />
    <drop type="constraint" table="mytable" constraint="mytable_pk" />
    <drop type="trigger" table="mytable" trigger="mytable_tr_ins" />
    
    <!-- create and drop a view - the replace attribute forces an overwrite of an existing view -->
    <create type="view" name="myView" replace="true">
      select * from myTable
    </create>
    <drop type="view" name="vTest" />
    
    <!-- create and drop a sequence - only the name is required 
    - sequences are ignored by databases that don't support them -->
    <create type="sequence" name="seqMyTable" startwith="1" increment="1" />
    <drop type="sequence" name="seqMyTable" />
    
    <!-- create a cyclical sequence (i.e. ad rotator) -->
    <create type="sequence" name="seqCycle" minvalue="1" maxvalue="100" cycle="true" />
    
    <!-- create a self-destruct sequence --->
    <create type="sequence" name="SELF_DESTRUCT" startwith="10" increment="-1" minvalue="0" />
    
    <!-- create and drop a stored procedure -->
    <create name="myProc" type="procedure" replace="true">
      <param name="pk" type="nvarchar(35)" required="true" />
      <param name="uid" type="nvarchar(35)" required="true" />
      <param name="name" type="nvarchar(100)" />
      <param name="num" type="integer" default="1" />
      
      insert into myTable (pk,name,num,uid) 
      values (@pk,@name,@num,@uid) 
    </create>
    <drop type="procedure" name="myProc" />
  </ddl>
</cfmodule>

Schema Export

The SchemaExport CFC provides a method of exporting the definition of some database entities as a DDL packet which can then be imported into another database. You may find this useful for upgrading new features from a development or staging server to production, however the biggest advantage is in upgrading multiple databases which may not be of the same type. If you've sold an application to a variety of clients who installed it on their own servers, this can help by eliminating the need to manage many complicated upgrade scripts between different versions of your application and different database platforms.

The export object is instantiated with a comma delimited list of tables and can then be used to export those tables via the getDDL() method. Further the public methods of the schema export include utilities for comparing arbitrary install scripts and producing a diff of them (an XML packet containing an "upgrade" script), as well as for exporting a schema from the properties of an active record object.

Example:

<cfscript>   tables = "tblCategory,tblProduct,tblSale,tblSaleItem"; 
  
  // get datasources 
  sources = request.DataFaucet.getSources(); 
  
  // get the primary datasource 
  ds = sources.get("primary"); 
  
  // get a schema export for some tables 
  export = ds.getSchemaExport().init(tables); 
  
  // get an XML packet for installing the above listed tables 
  xml = export.getDDL(tables); 
  
  // get an alternate database 
  newDB = sources.get("altDB"); 
  
  // get an XML packet for the above tables from the new DB  
  export.init(tables=tables, datasource=newDB); 
  
  // get an upgrade XML packet to update newDB to match the primary 
  upgrade = export.getDiff(new=xml, old=export.getDDL()); 
  
  // run the upgrade script 
  newDB.parseDDL(upgrade); 
</cfscript>