dbUtility

The dbUtility allows you to seamlessly add and alter custom schema within Mura's database. Here's a basic example creating a table with an auto-incrementing primary key.

<cfscript>
$ = application.serviceFactory
.getBean('$')
.init(session.siteid);
myTable = $.getBean('dbUtility')
.setTable('widget')
// If you set autoincrement=true it will
// auto-create an int primary key field
.addColumn(column='widgetID',autoincrement=true)
.addColumn(column='name',datatype='varchar',length='255')
.addColumn(column='description',datatype='longtext');
WriteDump( myTable.columns() );
</cfscript>

Here's example of creating a table and explicitly setting the primary key as well as an index.

<cfscript>
$ = application.serviceFactory
.getBean('$')
.init(session.siteid);
myTable = $.getBean("dbUtility")
.setTable('widget')
.addColumn(column="widgetID",datatype="char",length='35')
.addColumn(column='name',datatype='varchar',length='255')
.addColumn(column='description',datatype='longtext');
.addPrimaryKey(column='widgetID')
.addIndex(column='name');
WriteDump( myTable.columns() );
</cfscript>

Here's an example of adding a foreignKey.

<cfscript>
$ = application.serviceFactory
.getBean('$')
.init(session.siteid);
myTable = $.getBean('dbUtility')
.setTable('widget')
.addColumn(column='widgetID',datatype='char',length='35')
.addColumn(column='name',datatype='varchar',length='255')
.addColumn(column='description',datatype='longtext')
.addPrimaryKey(column='widgetID')
.addIndex(column='name')
.addColumn(column='widgetTypeID',datatype='char',length='35')
.addForeignKey(
column='widgetTypeID'
, fkTable='widgetTypes'
, fkColumn='widgetTypeID'
);
WriteDump( myTable.columns() );
</cfscript>

Key Points

1. Valid datatypes are INT, TINYINT, CHAR, VARCHAR, TEXT, LONGTEXT, DATETIME, FLOAT, DOUBLE
2. You use the LENGTH argument with CHAR and VARCHAR datatypes.
3. If you add a column to a table that does not exists, it will create the table.
4. If you add a column that already exists, it will be updated.

Key Methods

<cfscript>
dbUtility.addColumn(
column
, datatype
, length='50'
, nullable=true
, default=null
, autoincrement=false
);
dbUtility.alterColumn(
column
, datatype
, length='50'
, nullable=true
, default=null
, autoincrement=false
);
dbUtility.dropColumn(column);
dbUtility.columnExists(column);
dbUtility.columnMetaData(column);
dbUtility.indexExists(column);
dbUtility.dropIndex(column);
dbUtility.tableExists(table);
dbUtility.dropTable(table);
dbUtility.primaryKeyExists();
dbUtility.dropPrimaryKey();
dbUtility.addPrimaryKey(column);
dbUtility.foreignKeyExists(column,refTable,refColumn);
dbUtility.addForeignKey(column,refTable,refColumn);
dbUtility.dropForeignKey(column,refTable,refColumn);
//Returns the columns of the current table per setTable()
dbUtility.columns();
//Returns db/driver version info
dbUtility.version();
//Returns the indexes of the current table per setTable()
dbUtility.indexes();
//Returns the foreignKeys of the current table per setTable()
dbUtility.foreignKeys();
</cfscript>