Database tables

Database configuration for SproutCMS is managed through an XML file called db_struct.xml which contains definitions of the tables which are required.

There is a dev tool called database sync which collates all of the db_struct.xml files and executes the various sql statements required to ensure that the database structure is up-to-date with the definitions.

This tool can be accessed from the dev tools area, which is accessed by clicking on the icon of the servers in the top orange bar. Note that the database sync can only be performed by super-operators.

The db_struct.xml file

The format is fairly simple and should be self-explanatory, therefore let's begin with an example:

modules/Blogs/db_struct.xml

<database>
	<table name="blog_posts">
		<column name="id" type="INT UNSIGNED" allownull="0" autoinc="1" />
		<column name="name" type="VARCHAR(200)" allownull="0" />
		<column name="text" type="TEXT" />
 
	        <primary>
			<col name="id" />
		</primary>
        	<index>
			<col name="name" />
		</index>
	</table>
 
	<table name="blog_posts_cat_list">
		<column name="id" type="INT UNSIGNED" allownull="0" autoinc="1" />
		<column name="name" type="VARCHAR(50)" allownull="0" />
 
		<primary>
			<col name="id" />
		</primary>
	</table>
</database>

For people who like the nitty gritty, take a look at the schema located at sprout/config/db_struct.xsd, for everyone else, read the below.

The <database> tag is a wrapper around all the content in the file. It doesn't have any attributes.

Within the database should be one or more <table> tags. These contain one or more <column> tags, a <primary> index, and also optionally some additional <index> tags.

A table can also optionally specify a <default_records> section, to populate the table with initial data.

The <table> tag

Parent: <database> Children: <column> <primary> <index> <default_records>

This defines a table in the database. The attribute name is requred.

Name	Description	Default
name	The table name	Required field
engine	The MySQL storage engine to use	InnoDB
charset	The character set to use for the database columns	utf8
collate	The collation to use for the table	utf8_unicode_ci
previous-names	A comma-separated list of previous names for the table, which will trigger a table rename if the table itself doesn't exist but one exists that matches a previous name

The <column> tag

Parent: <table> Children: none

Defines a database column in the table. The name and type attributes are required. This element does not have any children.

Name	Description	Default
name	The column name	Required field
type	The SQL type for the column. Should include 'UNSIGNED' for unsigned columns. Do not provide column size for INT columns.	Required field
allownull	Whether NULLs are allowed in the column	1
autoinc	Whether the column is an AUTO_INCREMENT column	0
default	The default value for the column
previous-names	A comma-separated list of previous names for the column, which will trigger a column rename if the column itself doesn't exist but one exists that matches a previous name

The <primary> tag

Parent: <table> Children: <col>

Defines the columns used as the primary key of the table. Has no attributes. Must contain one or more childen <col> elements.

The <index> tag

Parent: <table> Children: <col> <foreign-key>

Defines an index on one or more columns. The type attribute is optional. Must contain one or more childen <col> elements. May contain a <foreign-key> element.

Name	Description	Default
type	The type of index: "index", or "unique".	index

The <col> tag

Parent: <primary> <index> Children: none

Defines a reference to a column used in the primary key or another index. The name attribute is required.

The <foreign-key> key

Parent: <index> Children: none

Defines a foreign key constraint for an index which has a single column.

Name	Description	Default
table	The name of the target table	Required field
column	The name of the column in the target table; usually the primary key, 'id'. The column specified must have a unique index on it.	Required field
update	What happens when the matching value in the target column gets updated: `cascade` to update this record as well `restrict` to prevent the update from happening `set-null` to set the source column to NULL. Obviously it must allow NULL values.	Required field
delete	What happens when the row containing the matching value in the target column gets deleted: `cascade` to delete this record as well `restrict` to prevent the deletion from happening `set-null` to set the source column to NULL. Obviously it must allow NULL values.	Required field

The <default_records> tag

Parent: <table> Children: <record>

Defines a set of records which should be added when the table is first created. Must contain at least one <record> element.

The <record> tag

Parent: <default_records> Children: none

Defines a single record to be created with the table it belongs to. Its attributes are the column names and the values they should store, for example:

<record id="1" name="Examplina" email="example@example.com" dob="1970-01-01 00:00:00" />