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
- Valid datatypes are INT, TINYINT, CHAR, VARCHAR, TEXT, LONGTEXT, DATETIME, FLOAT, DOUBLE
- You use the LENGTH argument with CHAR and VARCHAR datatypes.
- If you add a column to a table that does not exists, it will create the table.
- 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>