Schema Syntax

Quick Reference

Syntax:
schemaname
  tablename[|options]
    columnname|datatype[|options]
    columnname|datatype[|options]
  tablename[|options]
    columnname|datatype[|options]
    columnname|datatype[|options]
schemaname
  tablename[|options]
    columnname|datatype[|options]
    columnname|datatype[|options]
  tablename[|options]
    columnname|datatype[|options]
    columnname|datatype[|options]

  • schema names are not indented
  • tables are indented 1 tab
  • columns are indented 2 tabs
  • empty/whitespace only lines are ignored
  • lines starting with a dash - are ignored (use for comments)

Column Options
Token Description
* Primary key
@ Autogenerated column
@* Automatic IDENTITY column (Primary key + Autogenerated)
? Nullable
>schema.table.column Foreign key
=[value] Default value
# Specifies enum name or value
$TypeName Force typename for C# generation
~ Decorates the generated property with DataMemberAttribute


Table Options
Token Description
# Forces C# enum generation instead of class
~ Decorates the generated class with DataContractAttribute
& Flags enum - decorates a generated enum with FlagsAttribute



Datatypes With Size, Precision and/or Scale

  • Size or Precision are indicated by following the name of a datatype with a comma, and then the size (integer or MAX)
  • Scale is indicated by following the precision with a second comma, and then the scale (integer)

Example:
dbo
  product
    name|nvarchar,50
    description|nvarchar,max
    price|decimal,18,2


IDENTITY

Using the * and @ tokens together automatically creates an IDENTITY column seeded at 1, incrementing by one.

Example:
dbo
  category
    id|int|@*


Foreign Keys

Using the > token followed by the schema, table and column name of the desired foreign key creates a foreign key constraint.

Example:
dbo
  categories
    id|int|@*
    name|varchar,50
  products
    id|int|@*
    category_id|int|>dbo.categories.id


Nullable columns

Unless defined as nullable using the ? token, all columns are implicitly defined as non-nullable.

Example:
dbo
  categories
    id|int|@*
    name|varchar,50
    description|nvarchar,150|?


Default values

Default values may be specified using the same syntax that would be used when scripting in T-SQL.

Example:
dbo
  categories
    id|int|@*
    name|varchar,50
    is_active|bit|=(1)
    inserted_on_utc|datetime|=(getutcdate())


Enums

If desired, lookup tables may be generated as enums instead of classes in the C# generated code.
  • The table must be marked with the # token.
  • A single value column must be marked with the # token.
    • Only non-nullable tinyint, smallint, int or bigint columns may be used as the value column.
    • The value column may not be an auto-generated or IDENTITY column.
  • A single name column must be marked with the # token.
    • Only non-nullable varchar or char columns may be used as the name column.
    • The name column may not be an auto-generated column.
  • There must be a corresponding entry for the table in the Lookup data provided.
  • If there are columns other than the value and name columns, the generated C# type is suffixed with "ID", and the full class is generated in addition to the enum.
  • Add the table token & for Flags enums

Example:
dbo
  product_flags|#&
    id|int|#
    name|varchar,50|#
  categories|#
    id|int|*#
    name|varchar,50|#


Typename Substitution

The type of a generated property may be overridden by using the $ token followed by the type name. This is generally used in conjunction with a foreign key to a class that is defined as an enum.
*Any "ID" suffix will automatically be removed from the column name.

Example:
dbo
  categories|#
    id|int|*#
    name|varchar,50|#
  products
    id|int|@*
    category_id|int|>dbo.categories.id$Category

// example of generated code
[Table(Name = "dbo.products")]
public class Product
{
    [Column(Name = "id", AutoSync = AutoSync.OnInsert, IsDbGenerated = true, IsPrimaryKey = true)]
    public Int32 ID { get; set; }

    [Column(Name = "category_id")]
    public Category Category { get; set; }
}


Data Contracts

If the class will be serialized as JSON, it may be desirable to control the names of the properties that will be generated when the class is serialized.
  • Using the ~ token on a table will decorate the generated C# class with DataContractAttribute, naming the contract as the Camel-cased form of the table's name. Underscores are removed.
  • Using the ~ token on a column will decorate the generated C# property with DataMemberAttribute, naming the member as the Camel-cased form of the column's name. Underscores are removed.

Example:
dbo
  categories|~
    id|int|@*~
    name|varchar,50|~


Commenting

Comments may be added by starting a line with the dash (-) character. The dash may be indented. Additionally, lines that are empty or only whitespace are ignored.

Example:
dbo

  - note: requires lookup data
  categories|#
    id|int|#*
    name|varchar,50|#

Last edited Aug 13, 2010 at 3:53 PM by DanielSchaffer, version 16

Comments

No comments yet.