Skip to Content search facebook instagram pinterest twitter youtube

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" />