Myna. Table

Create modify and delete SQL tables

Summary
Myna. TableCreate modify and delete SQL tables
Functions
Myna. TableConstruct a Query object.
Properties
deferExecif this is set to true, all functions that modify the database will instead append their SQL to Table.sql
sqlStores SQL from database altering functions when Table.deferExec is true
sqlTableNamename of this table including schema name that should be used in sql queries
tableNamename of this table
dbreference to the Myna.Database object that create this Table object
columnsStructure representing the defined columns in this table, keyed by the column name.
columnNamesAn array of column names, in the order they appear in the table.
existstrue, if this table exists
primaryKeysAn array of column names that make up the primary key.
primaryKeyInfoAn array of primary key metadata.
foreignKeysAn array of foreign(imported) key information.
exportedKeysAn array of exported key information.
indexInfoAn array of index info.
hasIndexreturns true is the specified index exists
_cacheKey(private) cache key base for internal metadata caching
Functions
init(re)loads table metadata
addColumnAdds a column to an existing table
modifyColumnModifies an existing column
addForeignKeyAdds a foreign key to this table
addPrimaryKeyAdds a primary key to this table
addIndexAdds an index to an existing table
getTemplateRetrieves the template associated with the supplied type.
getSqlColumnNamereturns requested column name properly formatted for the target database including quot characters
createCreate this table.
dropDrop (delete) this table.
dropColumnDrop (delete) this column.
dropConstraintDrop (delete) a table constraint such as a foreign or primary key.
dropIndexDrop (delete) an index
_getCache(private) internal function for caching metadata
clearMetadataCacheclears metadata cache for this table
applyChangesApplies the contents of Table.sql to this Table’s datasource.

Functions

Myna. Table

Myna.Table = function (db,
tableName)

Construct a Query object.  This should normally be called indirectly via Myna.Database.getTable

Properties

deferExec

if this is set to true, all functions that modify the database will instead append their SQL to Table.sql

See

sql

Stores SQL from database altering functions when Table.deferExec is true

See

sqlTableName

name of this table including schema name that should be used in sql queries

tableName

name of this table

db

reference to the Myna.Database object that create this Table object

columns

Structure representing the defined columns in this table, keyed by the column name.  If the “Case Sensitive” property of the Datasource is false, these keys will be lowercase.  Each entry contains:

table_catstring table catalog/db name (may be null)
table_schemstring table schema/username (may be null)
table_namestring table name
column_namestring column name
data_typeint sql type from java.sql.types
type_namestring data source dependent type name, for a UDT the type name is fully qualified
column_sizeint column size. for char or date types this is the maximum number of characters, for numeric or decimal types this is precision.
buffer_lengthnull
decimalDigitsint the number of fractional digits
num_prec_radixint radix (typically either 10 or 2)
nullableint is null allowed.
  • java.sql.DatabaseMetaData.columnNoNulls = might not allow null values
  • java.sql.DatabaseMetaData.columnNullable = definitely allows null values
  • java.sql.DatabaseMetaData.columnNullableUnknown = nullability unknown
remarksstring comment describing column (may be null)
column_defstring default value (may be null)
sql_data_typeint unused
sql_datetime_subint unused
char_octet_lengthint for char types the maximum number of bytes in the column
ordinal_positionint index of column in table (starting at 1)
is_nullablestring “no” means column definitely does not allow null values; “yes” means the column might allow null values. an empty string means nobody knows.
scope_catlogstring catalog of table that is the scope of a reference attribute (null if data_type isn’t ref)
scope_schemastring schema of table that is the scope of a reference attribute (null if the data_type isn’t ref)
scope_tablestring table name that this the scope of a reference attribure (null if the data_type isn’t ref)
source_data_typeshort source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn’t DISTINCT or user-generated REF)

columnNames

An array of column names, in the order they appear in the table.  If the “Case Sensitive” property of the Datasource is false, these names will be lowercase.

exists

true, if this table exists

primaryKeys

An array of column names that make up the primary key.  If this datasource is not case-sensitive, the nthe keys will be in lowercase.

primaryKeyInfo

An array of primary key metadata.  Each entry contains:

table_cattable catalog (may be null)
table_schemtable schema (may be null)
table_nametable name
column_namecolumn name
key_seqsequence number within primary key
pk_nameprimary key name (may be null)

foreignKeys

An array of foreign(imported) key information.  Each entry contains:

pktable_catstring Referenced table catalog/db name being imported (may be null)
pktable_schemstring Referenced table schema/username being imported (may be null)
pktable_namestring Referenced key table name
pkcolumn_namestring Referenced column name being imported
fktable_catstring this table catalog/db name (may be null)
fktable_schemstring this table schema/username (may be null)
fktable_namestring this table name
fkcolumn_namestring this column name
key_seqshort sequence number within a foreign key
update_ruleshort what happens to a local column when the referenced column is updated:
  • java.sql.DatabaseMetaData.importedKeyNoAction = do not allow update of referenced column if it has been imported
  • java.sql.DatabaseMetaData.importedKeyCascade = change local column to agree with referenced column update
  • java.sql.DatabaseMetaData.importedKeySetNull = change local column to null if its referenced column has been updated
  • java.sql.DatabaseMetaData.importedKeySetDefault = change local column to default values if its referenced column has been updated
  • java.sql.DatabaseMetaData.importedKeyRestrict = same as importedkeynoaction (for odbc x compatibility)
delete_ruleshort what happens to the local column when the referenced column is deleted.
  • java.sql.DatabaseMetaData.importedKeyNoAction = do not allow delete of referenced column if it has been imported
  • java.sql.DatabaseMetaData.importedKeyCascade = delete rows that import a deleted key
  • java.sql.DatabaseMetaData.importedKeySetNull = change local column to null if its referenced column has been deleted
  • java.sql.DatabaseMetaData.importedKeyRestrict = same as importedkeynoaction (for odbc x compatibility)
  • java.sql.DatabaseMetaData.importedKeySetDefault = change local column to default if its referenced column has been deleted
fk_namestring foreign key name (may be null)
pk_namestring primary key name (may be null)
deferrabilityshort can the evaluation of foreign key constraints be deferred until commit
  • java.sql.DatabaseMetaData.importedKeyInitiallyDeferred = see sql92 for definition
  • java.sql.DatabaseMetaData.importedKeyInitiallyImmediate = see sql92 for definition
  • java.sql.DatabaseMetaData.importedKeyNotDeferrable = see sql92 for definition

exportedKeys

An array of exported key information.  Each entry contains:

pktable_catstring local column table catalog (may be null)
pktable_schemstring local column table schema (may be null)
pktable_namestring local column table name
pkcolumn_namestring local column column name
fktable_catstring foreign catalog (may be null)
fktable_schemstring foreign schema (may be null)
fktable_namestring foreign table name
fkcolumn_namestring foreign column name
key_seqshort sequence number within foreign key
update_ruleshort what happens to foreign key when primary is updated:
  • java.sql.DatabaseMetaData.importedKeyNoAction = do not allow update of local column if it has been imported
  • java.sql.DatabaseMetaData.importedKeyCascade = change imported key to agree with local column update
  • java.sql.DatabaseMetaData.importedKeySetNull = change imported key to null if its local column has been updated
  • java.sql.DatabaseMetaData.importedKeySetDefault = change imported key to default values if its local column has been updated
  • java.sql.DatabaseMetaData.importedKeyRestrict = same as importedkeynoaction (for odbc x compatibility)
delete_ruleshort what happens to the foreign key when primary is deleted.
  • java.sql.DatabaseMetaData.importedKeyNoAction = do not allow delete of local column if it has been imported
  • java.sql.DatabaseMetaData.importedKeyCascade = delete rows that import a deleted key
  • java.sql.DatabaseMetaData.importedKeySetNull = change imported key to null if its local column has been deleted
  • java.sql.DatabaseMetaData.importedKeyRestrict = same as importedkeynoaction (for odbc x compatibility)
  • java.sql.DatabaseMetaData.importedKeySetDefault = change imported key to default if its local column has been deleted
fk_namestring foreign key name (may be null)
pk_namestring local column name (may be null)
deferrabilityshort can the evaluation of foreign key constraints be deferred until commit
  • java.sql.DatabaseMetaData.importedKeyInitiallyDeferred = see sql92 for definition
  • java.sql.DatabaseMetaData.importedKeyInitiallyImmediate = see sql92 for definition
  • java.sql.DatabaseMetaData.importedKeyNotDeferrable = see sql92 for definition

indexInfo

An array of index info.  Each entry contains:

nameindex name
uniqueboolean, true if this is a unique index
columnsan array of the column names that make of the index.

hasIndex

returns true is the specified index exists

Parameters

colnamesarray of column names in the index
isUniquetrue, if the index is unique

_cacheKey

(private) cache key base for internal metadata caching

Functions

init

Myna.Table.prototype.init = function()

(re)loads table metadata

addColumn

Myna.Table.prototype.addColumn = function(options)

Adds a column to an existing table

Parameters

optionsObject representing the porperties of this column.  See below

Options

namename of the column,
typeone of BIGINT,BLOB,CLOB,DATE,INTEGER,NUMERIC,TEXT, TIMESTAMP,VARBINARY,VARCHAR
maxLengthMaximum size in characters or digits
decimalDigitsMaximum decimal places stored for NUMERIC types
allowNullOptional, default true Should null values be allowed in this column?
defaultValueOptional, default null What should be stored in this column when no value is supplied?
isUniqueOptional, default false must all values in this column be unique?
isPrimaryKeyOptional, default false should this column be the primary key for this table?. will generate an error if a primary key already exists
referencesOptional, default null column definition of existing column in foreign table that this column references.  See Reference Definition below

Reference Definition properties

tablename of foreign table
columnname of foreign column
onDeleteOptional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”
onUpdateOptional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”

Example

var db = new Database("example")

var t = db.getTable("orders");
t.addColumn({
name:"customer_id",
type:"BIGINT",
allowNull:false,
references:{
table:"customers",
column:"customer_id",
onDelete:"cascade",
onUpdate:"cascade"
}
})

modifyColumn

Myna.Table.prototype.modifyColumn = function(name,
options)

Modifies an existing column

Parameters

nameName of existing column to modify
optionsObject representing the properties of this column.  See below

Options

nameOptional new name of the column,
typeOptional one of BIGINT,BLOB,CLOB,DATE,INTEGER, NUMERIC,TEXT,TIMESTAMP,VARBINARY,VARCHAR.  Be aware that if the new type is incompatible with the existing, the modification will fail.  As an example, changing from INTEGER to BIGINT will probably work, but VARCHAR to DATE probably won’t
maxLengthOptional Maximum size in characters or digits.  Setting this to smaller value than the existing type will probably fail.
decimalDigitsOptional Maximum decimal places stored for NUMERIC types.  Setting this to smaller value than the existing type will probably fail.
allowNullOptional Should null values be allowed in this column?  Setting this to false on a column with null values will fail
defaultValueOptional What should be stored in this column when no value is supplied?
isUniqueOptional must all values in this column be unique?
isPrimaryKeyOptional should this column be the primary key for this table?.  This will generate an error if a primary key already exists
referencesOptional, default null column definition of existing column in foreign table that this column references.  See Reference Definition below

Reference Definition properties

tablename of foreign table
columnname of foreign column
onDeleteOptional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”
onDeleteOptional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”

Example

var db = new Database("example")

var t = db.getTable("orders");
t.modifyColumn("customerid",{
name:"customer_id",
type:"BIGINT",
allowNull:false,
references:{
table:"customers",
column:"customer_id",
onDelete:"cascade",
onUpdate:"cascade"
}
})

addForeignKey

Myna.Table.prototype.addForeignKey = function(options)

Adds a foreign key to this table

Parameters

optionsObject representing the properties of this key.  See below

Options

localColumnName of local column that references foreign table
foreignTableName of foreign table
foreignColumnName of foreign column
idOptional, default auto generated Name of key
onDeleteOptional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”
onUpdateOptional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”

Example

var db = new Database("example")

var t = db.getTable("orders");
t.addForeignKey({
id:"fk_orders_customer_id",
localColumn:"customer_id",
foreignTable:"customers",
foreignColumn:"customer_id",
onDelete:"cascade",
onUpdate:"cascade"
})

addPrimaryKey

Myna.Table.prototype.addPrimaryKey = function(options)

Adds a primary key to this table

Parameters

optionsObject representing the properties of this key.  See below

Options

columnName of local column that will be the primary key.  Myna only supports a single column key
idOptional, default auto generated Name of key Example:
var db = new Database("example")

var t = db.getTable("orders");
t.addPrimaryKey({
id:"pk_orders",
column:"order_id",
})

addIndex

Myna.Table.prototype.addIndex = function(options)

Adds an index to an existing table

Parameters

optionsObject representing the porperties of this column.  See below

Options

columnsArray of column names to include in the index
idOptional, default auto-generate name to give to the index
uniqueOptional, default null is this a unique index?

Example

var db = new Database("example")

var t = db.getTable("orders");
t.addIndex({
id:"idx_order_ts",
columns:["order_ts"]
})

getTemplate

Myna.Table.prototype.getTemplate = function(type)

Retrieves the template associated with the supplied type.

Parameters

typetemplate key to retrieve

getSqlColumnName

Myna.Table.prototype.getSqlColumnName = function(colname)

returns requested column name properly formatted for the target database including quot characters

Parameters

colnamecase-insensitive column name

create

Myna.Table.prototype.create = function(options)

Create this table.

Parameters

optionsObject representing the porperties of this column.  See below

Options

recreateOptional, default false Should this table be dropped first if it already exists?
columnsOptional, default [] Array of column definitions; see Table.addColumn.  Although this is not required, some databases will throw errors if you attempt to create a table with no columns.  Also, attempting to add a primary key to an existing table is more difficult than declaring a primary key at table creation

Example

var db = new Database("example")

var t = db.getTable("customers");
t.create({
recreate:true,
columns:[{
name:"customer_id",
type:"BIGINT",
isPrimaryKey:true
},{
name:"fname",
type:"VARCHAR",
maxLength:100,
allowNull:true
},{
name:"lname",
type:"VARCHAR",
maxLength:100
}]
})

drop

Myna.Table.prototype.drop = function()

Drop (delete) this table.

Example

var db = new Database("example")

var t = db.getTable("customers");
t.drop();

dropColumn

Myna.Table.prototype.dropColumn = function(name)

Drop (delete) this column.

Parameters

namename of column ot drop

Example

var db = new Database("example")

var t = db.getTable("customers");
t.dropColumn("fname");

dropConstraint

Myna.Table.prototype.dropConstraint = function(name)

Drop (delete) a table constraint such as a foreign or primary key.

Paramaters

namename of constraint to remove

Example

var db = new Database("example")

var t = db.getTable("customers");
t.dropConstraint("pkey_customers");

dropIndex

Myna.Table.prototype.dropIndex = function(name)

Drop (delete) an index

Paramaters

namename of constraint to remove

Example

var db = new Database("example")

var t = db.getTable("customers");
t.dropIndex("idx_customers_first_name");

_getCache

Myna.Table.prototype._getCache = function(type,
f)

(private) internal function for caching metadata

clearMetadataCache

Myna.Table.prototype.clearMetadataCache = function()

clears metadata cache for this table

Detail

to improve performance, table metadata is cached between new Myna.Table calls.  After making changes to a table, a call to this function is necessary to refresh metadata.  This function is called automatically by all the Myna.Table functions that modify the table, so calling this should only be necessary if the table is modified outside of Myna.table or another Myna instance

applyChanges

Myna.Table.prototype.applyChanges = function()

Applies the contents of Table.sql to this Table’s datasource.

See

Myna.Table = function (db,
tableName)
Construct a Query object.
Stores SQL from database altering functions when Table.deferExec is true
if this is set to true, all functions that modify the database will instead append their SQL to Table.sql
Provides database metadata and manages Myna.Table objects
Myna.Table.prototype.init = function()
(re)loads table metadata
Myna.Table.prototype.addColumn = function(options)
Adds a column to an existing table
Myna.Table.prototype.modifyColumn = function(name,
options)
Modifies an existing column
Myna.Table.prototype.addForeignKey = function(options)
Adds a foreign key to this table
Myna.Table.prototype.addPrimaryKey = function(options)
Adds a primary key to this table
Myna.Table.prototype.addIndex = function(options)
Adds an index to an existing table
Myna.Table.prototype.getTemplate = function(type)
Retrieves the template associated with the supplied type.
Myna.Table.prototype.getSqlColumnName = function(colname)
returns requested column name properly formatted for the target database including quot characters
Myna.Table.prototype.create = function(options)
Create this table.
Myna.Table.prototype.drop = function()
Drop (delete) this table.
Myna.Table.prototype.dropColumn = function(name)
Drop (delete) this column.
Myna.Table.prototype.dropConstraint = function(name)
Drop (delete) a table constraint such as a foreign or primary key.
Myna.Table.prototype.dropIndex = function(name)
Drop (delete) an index
Myna.Table.prototype._getCache = function(type,
f)
(private) internal function for caching metadata
Myna.Table.prototype.clearMetadataCache = function()
clears metadata cache for this table
Myna.Table.prototype.applyChanges = function()
Applies the contents of Table.sql to this Table’s datasource.
Myna.Database.prototype.getTable = function(tableName)
returns a Myna.Table object representing the named table.