column-definitions.md

Column Definitions

Column definitions are used to describe columns in create-table and alter-table query types.

Helper: 'type'

Format: type

Expects: 'string'

Simply returns the value passed into the helper. Used for defining column types.

Helper: 'primaryKey'

Format: primary key

Expects: 'boolean|string|array'

Returns primary key ... depending on the input:

{
  // primary key
  primaryKey: true

  // primary key ("name")
, primaryKey: 'name'

  // primary key ("fname", "lname")
, priamryKey: ['fname', 'lname']
}

Helper: 'references'

Format: references "table_name"("column_name") on update set null on delete cascade match full

Expects: 'object'

Column references another tables column.

Example:

{
  type: 'alter-table'
, table: 'users'
, action: {
    addColumn: {
      name: 'groupId'
    , type: 'int'
    , references: 'groups'
    , onDelete: 'cascade'
    , onUpdate: 'set null'
    , match: 'full'
    }
  }
}

alter table "users" add column "groupId" int references "groups"
  on delete cascade on update set null match full

Helper: 'null'

Format: null | not null

Expects: 'boolean'

If true, returns null. Otherwisee, return not null.

Helper: 'notNull'

Format: not null | null

Expects: 'boolean'

Inverse of null. If true, returns not null. Otherwise, returns null.

Helper: 'unique'

Format: unique ( col1, col2, ... )

Expects: 'boolean|string[]'

Specifies which columns to be unique. If the value passed in is a boolean, it will assume you mean to use the column in the current context to be unique and simply return the text 'unique' if the value is true. Otherwise, if it's an array, then it will do unique on all the strings in the array.

Example:

{
  type: 'alter-table'
, table: 'distributors'
, action: {
    addConstraint: {
      name: 'dist_id_zipcode_key'
    , unique: ['dist_id', 'zipcode']
    }
  }
}

alter table "distributors" add constraint "dist_id_zipcode_key" unique (
  "dist_id", "zipcode"
)

{
  type: 'create-table'
, table: 'jobs'
, definition: {
    id: {
      type: 'serial'
    }

  , name: {
      type: 'text'
    , unique: true
    }

  , createdAt: {
      type: 'timestamp'
    }
  }
}

create table "jobs" (
  "id" serial,
  "name" text unique,
  "createdAt" timestamp
)

Helper: 'default'

Format: default val

Expects: 'string'

Set a default value. Purposefully does not parameterize values because it is too often the case that a value is used that does not need to be parameterized. Something like now().

Example:

{
  type: 'create-table'
, table: 'jobs'
, definition: {
    id: {
      type: 'serial'
    }

  , name: {
      type: 'text'
    }

  , createdAt: {
      type: 'timestamp'
    , default: 'now()'
    }
  }
}

create table "jobs" (
  "id" serial, 
  "name" text, 
  "createdAt" timestamp default now()
)

Helper: 'check'

Format: check( {where} )

Expects: object

Checks whether some condition is true. Expects a valid MoSQL conditional object. See conditional helpers for full capabilities.

Example:

{
  type: 'alter-table'
, table: 'users'
, action: {
    addColumn: {
      name: 'groupId'
    , type: 'int'
    , check: { groupId: { $gt: 7 } }
    }
  }
}

alter table "users" add column "groupId" int check ("users"."groupId" > $1)

Helper: 'noInherit'

If true, returns the string 'no inherit'.

Registering your own Column Definitions

Column definitions uses the standard MoSQL Helper Interface, so it's very similar to the other helpers.

mosql.columnDefinitions.add( name, callback )

Add a new column definition. Name is the name of the column definition helper, callback is the function to be called when a match is made to the helper name. Callback's arguments are:

• Value - The value to be used for update.
• Values - The values array. All values not escaped by surrounding '$' signs are pushed to the values array for parameterized queries.
• Table - The table associated to the column
• Query - This is the whole MoSQL query object passed in by the user.

mosql.updateHelpers.has( name )

Returns a boolean denoting whether or not a update helper exists.

mosql.updateHelpers.get( name )

Returns the update helper interface: { fn, options }.