Creating and Working with db.xml Files

A working example...

Now that you know how to properly code your db.xml file, you can use the example below to begin building your very own. Good luck!

<?xml version="1.0"?>
<schema version="0.3">
    <table name="btBasicTest">
        <field name="bID" type="I">
            <key ></key>
            <unsigned ></unsigned>
            <default value="0"/>
        </field>
        <field name="content" type="X2">
        </field>
    </table>
</schema>

Originally posted by lucasanderson at lucasanderson.com.

db.xml is a file contained in either a block's or package's directory, that defines one or more database tables. These are then created when a block or package is installed. db.xml is required for any block that needs to save its data in the database (which is 99% of them). This file is specified in the AXMLS format.

Many new concrete5 developers choose to learn how to create a db.xml file for their block by downloading a free block from the concrete5 marketplace and reviewing how it was setup. This guide was created to help you understand how to properly code your file based on the AdoDB XML Schema and to provide a cheat sheet of available Types and Keywords.

Hint: You should download the basic block example which is referenced in this guide.

In the beginning...

Start by creating the shell of your file with the following:

<?xml version="1.0"?>
<schema version="0.3">
    <table name="btBasicTest">
        <field name="bID" type="I">
            <key ></key>
            <unsigned ></unsigned>
        </field>
        ......
        ......
    </table>
</schema>

Hint: In the basic test block, they've defined the database table as "btBasicTest." You will need to rename this to the handle of your block specified in your controller.

Hint: Every table specified by the controller's btTable property must have bID as its primary key. This is an integer (and not set to auto_increment.) This key maps to an entry in Concrete5's core Blocks table, and is used to join your block-specific data to Concrete5 data about the block (metadata, its positioning, versions, etc...)

And then there was light...

Now that you've got your shell, you can begin adding fields that you will need for your block. For example, if you need to collect, save and display a "Name", you could add this to your xml structure:

<field name="name" type="C" size="255">
</field>

Hint: It is considered proper technique to indent each new level of the XML file, as shown in the examples.

The tables below explain what field types and keywords can be used to define your database table structure.

Field Types

Type Prefix	Description
C	Varchar, capped to 255 characters.
X	Larger varchar, capped to 4000 characters (to be compatible with Oracle).
XL	For Oracle, returns CLOB, otherwise the largest varchar size.
C2	Multibyte varchar
X2	Multibyte varchar (largest size)
B	BLOB (binary large object)
D	Date (some databases do not support this, and we return a datetime type)
T	Datetime or Timestamp
L	Integer field suitable for storing booleans (0 or 1)
I	Integer (mapped to I4)
I1	1-byte integer
I2	2-byte integer
I4	4-byte integer
I8	8-byte integer
F	Floating point number
N	Numeric or decimal number

db.xml Field Type to MySQL

db.xml	MySQL
C	VARCHAR
XL	LONGTEXT
X	TEXT
C2	VARCHAR
X2	LONGTEXT
B	LONGBLOB
D	DATE
TS	DATETIME
T	DATETIME
I4	INTEGER
I	INTEGER
I1	TINYINT
I2	SMALLINT
I8	BIGINT
F	DOUBLE
N	NUMERIC

Field Type Modifiers

Keywords	Description
AUTOINCREMENT	For autoincrement number. Emulated with triggers if not available. Sets NOTNULL also.
KEY	Primary key field. Sets NOTNULL also. Compound keys are supported.
PRIMARY	Same as KEY.
DEF	Synonym for DEFAULT for lazy typists.
DEFAULT	The default value. Character strings are auto-quoted unless the string begins and ends with spaces, eg ' SYSDATE '.
NOTNULL	If field is not null.
DEFDATE	Set default value to call function to get today's date.
DEFTIMESTAMP	Set default to call function to get today's datetime.
NOQUOTE	Prevents autoquoting of default string values.
CONSTRAINTS	Additional constraints defined at the end of the field definition.

Loading Conversation