create_tables:

The value is a multi-level nested map which specifies the definition of database tables to be created. Each top level key of the map is a database Table Name. The value of this Table Name key is a map (or an ordered map) containing keys which are either Column Names or Reserved Words. Any of these not recognized as Reserved Words (see below) are assumed to be Column Names. Use of an ordered map rather than a map allows the order of the table columns to be fixed.

When the column level key is a Column Name (not a Reserved Word), its value can be either a string or a map. A map value here is used to define any column options as described below. A string value indicates the column type and will be either a Basic Type or an Enumerated Type name as defined under the ‘enum_types’ configuration key. Note that if it is an Enumerated Type, the user does not need to also create an entry in a ‘class_enums’ map since there is enough information here to create that mapping.

Basic Types are one of the following: (binary, boolean, date, datetime, decimal, float, integer, string, text, time, timestamp) Note: binary, date and float types may be defined in tables which are created, but they are not yet fully supported across all tiers of the LZadmin application and so should probably be avoided if possible. In practice this should not be a problem for most applications, since datetime and decimal are supported and binary data is not usually appropriate for use in forms.

Tables do not need to explicitly define a primary key as this is done for them automatically by the Rails environment which gives all tables an ‘id’ integer key unless otherwise specified (see ‘primary_key’ below). Example (with no Reserved Words and 2 Enumerated Types used):

create_tables:
  products:
    name: string
    product_type: ProductType
    vendor: string
    notes: text
  users:
    name: string
    email: string
    street: string
    city: string
    state: State
    zipcode: string

If column options need to be defined then the value of the Column Name will be a map in which all of the options are defined. In this case, the Basic Type of the column must also be defined in this lower level map. Options allowed are the following:

  • limit: The maximum column length in bytes. Can be used with (‘string’, ‘text’, ‘integer’ or ‘binary’) types.
  • precision: The precision of a ‘decimal’ column.
  • scale: The scale of a ‘decimal’ column.
  • default: The columns default value. Use ‘null’ or ‘NULL’ for ‘NULL’. (Do not use ‘nil’ as this will result in the string “nil”)
  • null: If ‘false’ a not null constraint is added to the column.

Example (using column options):

create_tables:
  items:
    name:
      type: string
      null: false
      limit: 32
    price:
      type: decimal
      precision: 8
      scale: 2

Reserved Words are one of (‘primary_key’, ‘index’, ‘indexes’, ‘unique_key’, ‘unique_keys’, ‘foreign_key’, ‘foreign_keys’ or ‘table_options’)

When the column level key is a plural form of a Reserved Word, i.e. (‘indexes’, ‘unique_keys’ or ‘foreign_keys’) the value is a list of the indicated items. When it is a singular form, the value is a single instance of the item. The following sections describe the structure of these Reserved Word items.

primary_key:

The value is a boolean or a string type. A value of ‘false’ means there is no primary key. A value of ‘true’ is redundant and results in the default primary key ‘id’ being used. A string value is taken as the name of the column to be used as a primary key instead of the default ‘id’. Note that Rails and LZadmin do not support multi-column primary keys. In general it is recommended to use the default primary key if possible.

index:

The value is either a string, a list or a map as described below:

  • String: The column name which is to be an index.
  • List: A list of column names which are to be included in this index.
  • Map: Contains a single map entry where the key is the name of the index and the value is a string or list as described in the first two bullets.

Example (plural form and named index):

create_tables:
  companies:
    name: string
    symbol: string
    indexes:
      - name
      - symbol
  sites:
    name: string
    index:
      idx_name: name

unique_index:

The structure is the same as ‘index’. A unique index is to be created.

unique_key:

The structure is the same as ‘index’. A unique key is to be created.

foreign_key:

The value is a nested map structure with a single entry at each level. The first level key may be either a string or a list as described below:

  • String: A valid column name which is the foreign key and its value is a Table Reference as described below. If it is not a valid column name, then it is interpreted as the name of the foreign key and its value must be a foreign key specification as described in this section (but with no foreign key name). So use of a foreign key name effectively pushes the specification down one level in the data structure.
  • List: A list of column names which are to be included in this foreign key and its value is a Table Reference as described below.

A Table Reference is a single entry map where the key is the Table Name and the value is a column name in that table or a list of column names in that table.

Example (single column, no name):

foreign_key:
  product_id:
    products: id

Example (single column, with name):

foreign_key:
  fk_prod:
    product_id:
      products: id

Example (multiple column, no foreign key name):

foreign_key:
  ? [prod_category, prod_id]
  :
    products:
      [category, id]

Example (multiple column, with foreign key name):

foreign_key:
  fk_prod:
     ? [prod_category, prod_id]
     :
       products:
         [category, id]

In the last two examples, notice the use of the YAML ’? ’ (question mark and space) which is the complex mapping key indicator. This is required here since the Table Reference key is a list rather than a string.

In addition to the above forms, there is also a short form of Table Reference which can be used when the foreign reference is to a table column named “id”, which is the default primary key name. In this case the Table Reference is simply the Table Name, which provides a compact notation for the simplest case. Example (equivalent to the first example above):

foreign_key:
  product_id: products

table_options:

The value is a database specific string which is passed in at the end of the generated SQL CREATE TABLE statement. Example (for mysql):

table_options: "ENGINE=InnoDB COMMENT='A table comment'"

create_table_options_all:

This is a top level config file key which relates to use of ‘table_options’. It is used to specify one or more attributes which will be inserted into all CREATE TABLE statements generated. Attributes defined here will be added to any attributes specified in any ‘table_options’ map and overwrite any identically named attrbibutes. This allows specification of a common attriute like ENGINE once in ‘create_table_options_all’ and table specific attributes like COMMENT with each table in ‘table_options’.

previous | up | next