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