An internal class that the user is not expected to instantiate directly. Instances are created by patio.Database#createTable. It is used to specify table creation parameters. It takes a Database object and a block of column/index/constraint specifications, and gives the Database a table description, which the database uses to create a table.
patio.SchemaGenerator has some methods but also includes method_missing, allowing users to specify column type as a method instead of using the column method, which makes for a cleaner code.
Example
comb.executeInOrder(DB, function(DB){
DB.createTable("airplane_type", function () {
this.primaryKey("id");
this.name(String, {allowNull:false});
this.max_seats(Number, {size:3, allowNull:false});
this.company(String, {allowNull:false});
});
DB.createTable("airplane", function () {
this.primaryKey("id");
this.total_no_of_seats(Number, {size:3, allowNull:false});
this.foreignKey("typeId", "airplane_type", {key:"id"});
});
DB.createTable("flight_leg", function () {
this.primaryKey("id");
this.scheduled_departure_time("time");
this.scheduled_arrival_time("time");
this.foreignKey("departure_code", "airport", {key:"airport_code", type : String, size : 4});
this.foreignKey("arrival_code", "airport", {key:"airport_code", type : String, size : 4});
this.foreignKey("flight_id", "flight", {key:"id"});
});
DB.createTable("leg_instance", function () {
this.primaryKey("id");
this.date("date");
this.arr_time("datetime");
this.dep_time("datetime");
this.foreignKey("airplane_id", "airplane", {key:"id"});
this.foreignKey("flight_leg_id", "flight_leg", {key:"id"});
});
});
function (db){
this.db = db;
this.columns = [];
this.indexes = [];
this.constraints = [];
this.__primaryKey = null;
}
function (columns,opts){
this.constraints.push(merge({type:"foreignKey", columns:columns}, opts));
}
function (columns){
var args = argsToArray(arguments, 1);
var opts = args.pop() || {};
this.constraints.push(merge({type:"primaryKey", columns:columns}, opts));
}
Add an unnamed constraint to the DDL, specified by the given block or args:
Example
db.createTable("test", function(){
this.check({num : {between : [1,5]}})
//=> CHECK num >= 1 AND num <= 5
this.check(function(){return this.num.gt(5);});
//=> CHECK num > 5
});
Source
function (){
return this.constraint.apply(this, [null].concat(argsToArray(arguments)));
}
Add a column with the given name, type, and opts to the DDL.
DB.createTable("test", function(){
this.column("num", "integer");
//=> num INTEGER
this.column("name", String, {allowNull : false, "default" : "a");
//=> name varchar(255) NOT NULL DEFAULT 'a'
this.column("ip", "inet");
//=> ip inet
});
You can also create columns via method missing, so the following are equivalent:
DB.createTable("test", function(){
this.column("number", "integer");
this.number("integer");
});
Arguments
the name of the column
the datatype of the column.
additional options
The default value for the column.
This ensure Referential Integrity will work even if reference table will use for its foreign key a value that does not exists(yet) on referenced table. Basically it adds DEFERRABLE INITIALLY DEFERRED on key creation.
Boolean : Create an index on this column.
String|patio.sql.Identifier : For foreign key columns, the column in the associated table that this column references. Unnecessary if this column references the primary key of the associated table.
Boolean : Mark the column as allowing NULL values (if true), or not allowing NULL values (if false). If unspecified, will default to whatever the database default is.
String : Specify the behavior of this column when being deleted ("restrict", "cascade", "setNull", "setDefault", "noAction").
String : Specify the behavior of this column when being updated Valid options ("restrict", "cascade", "setNull", "setDefault", "noAction").
Boolean : Make the column as a single primary key column. This should only be used if you have a single, non-autoincrementing primary key column.
Number : The size of the column, generally used with string columns to specify the maximum number of characters the column will hold. An array of two integers can be provided to set the size and the precision, respectively, of decimal columns.
String : Mark the column as unique, generally has the same effect as creating a unique index on the column.
String : Make the column type unsigned, only useful for integer columns.
Array : Available items used for set and enum columns.
function (name,type,opts){
opts = opts || {};
this.columns.push(merge({name:name, type:type}, opts));
if (opts.index) {
this.index(name);
}
}
Adds a named constraint (or unnamed if name is nil) to the DDL, with the given block or args.
Example
DB.createTable("test", function(){
this.constraint("blah", {num : {between : [1,5]}})
//=> CONSTRAINT blah CHECK num >= 1 AND num <= 5
this.check("foo", function(){
return this.num.gt(5);
}); # CONSTRAINT foo CHECK num > 5
});
Arguments
the name of the constraint
variable number of arguments to create the constraint filter. See patio.Dataset#filter for valid filter arguments.
function (name,args){
args = argsToArray(arguments).slice(1);
var block = isFunction(args[args.length - 1]) ? args.pop : null;
this.constraints.push({name:name, type:"check", check:block || args});
}
Add a foreign key in the table that references another table to the DDL. See {@link patio.SchemaGenerator#column{ for options.
Example
DB.createTable("flight_leg", function () {
this.primaryKey("id");
this.scheduled_departure_time("time");
this.scheduled_arrival_time("time");
this.foreignKey("departure_code", "airport", {key:"airport_code", type : String, size : 4});
this.foreignKey("arrival_code", "airport", {key:"airport_code", type : String, size : 4});
this.foreignKey("flight_id", "flight", {key:"id"});
});
Arguments
function (name,table,opts){
opts = opts || {};
opts = isHash(table) ? merge({}, table, opts) : isString(table) ? merge({table:table}, opts) : opts;
if (isArray(name)) {
return this.__compositeForeignKey(name, opts);
} else {
return this.column(name, "integer", opts);
}
}
Add a full text index on the given columns to the DDL.
Example
DB.createTable("posts", function () {
this.title("text");
this.body("text");
this.fullTextIndex("title");
this.fullTextIndex(["title", "body"]);
});
Arguments
function (columns,opts){
opts = opts || {};
return this.index(columns, merge({type:"fullText"}, opts));
}
Check if the DDL includes the creation of a column with the given name.
ArgumentsBoolean true if the DDL includes the creation of a column with the given name.
function (name){
return this.columns.some(function (c) {
return c.name === name;
});
}
Add an index on the given column(s) with the given options to the DDL. The available options are:
Example
DB.createTable("test", function(table) {
table.primaryKey("id", "integer", {null : false});
table.column("name", "text");
table.index("name", {unique : true});
});
Arguments
the column/n to create the index from.
Additional options
String : The type of index to use (only supported by some databases)
Boolean : :: Make the index unique, so duplicate values are not allowed.
:: Create a partial index (only supported by some databases)
function (columns,opts){
this.indexes.push(merge({columns:toArray(columns)}, opts || {}));
}
Adds an auto-incrementing primary key column or a primary key constraint to the DDL. To create a constraint, the first argument should be an array of columns specifying the primary key columns. To create an auto-incrementing primary key column, a single column can be used. In both cases, an options hash can be used as the second argument.
If you want to create a primary key column that is not auto-incrementing, you should not use this method. Instead, you should use the regular patio.SchemaGenerator#column method with a {primaryKey : true} option.
Example
db.createTable("airplane_type", function () {
this.primaryKey("id");
//=> id integer NOT NULL PRIMARY KEY AUTOINCREMENT
this.name(String, {allowNull:false});
this.max_seats(Number, {size:3, allowNull:false});
this.company(String, {allowNull:false});
});
Arguments
function (name){
if (isArray(name)) {
return this.__compositePrimaryKey.apply(this, arguments);
} else {
var args = argsToArray(arguments, 1), type;
var opts = args.pop();
this.__primaryKey = merge({}, this.db.serialPrimaryKeyOptions, {name:name}, opts);
if (isDefined((type = args.pop()))) {
merge(opts, {type:type});
}
merge(this.__primaryKey, opts);
return this.__primaryKey;
}
}
Add a spatial index on the given columns to the DDL.
Arguments
function (columns,opts){
opts = opts || {};
return this.index(columns, merge({type:"spatial"}, opts));
}
Add a unique constraint on the given columns to the DDL. See patio.SchemaGenerator#constraint for argument types.
Example
DB.createTable("test", function(){
this.unique("name");
//=> UNIQUE (name)
});
Arguments
function (columns,opts){
opts = opts || {};
this.constraints.push(merge({type:"unique", columns:toArray(columns)}, opts));
}