dbUtility - Mura Docs v6

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>