Myna. Table

Create modify and delete SQL tables

Summary

Myna. Table	Create modify and delete SQL tables
Functions
Myna. Table	Construct a Query object.
Properties
deferExec	if this is set to true, all functions that modify the database will instead append their SQL to Table.sql
sql	Stores SQL from database altering functions when Table.deferExec is true
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.
columnNames	An array of column names, in the order they appear in the table.
exists	true, if this table exists
primaryKeys	An array of column names that make up the primary key.
primaryKeyInfo	An array of primary key metadata.
foreignKeys	An array of foreign(imported) key information.
exportedKeys	An array of exported key information.
indexInfo	An array of index info.
hasIndex	returns true is the specified index exists
_cacheKey	(private) cache key base for internal metadata caching
Functions
init	(re)loads table metadata
addColumn	Adds a column to an existing table
modifyColumn	Modifies an existing column
addForeignKey	Adds a foreign key to this table
addPrimaryKey	Adds a primary key to this table
addIndex	Adds an index to an existing table
getTemplate	Retrieves the template associated with the supplied type.
getSqlColumnName	returns requested column name properly formatted for the target database including quot characters
create	Create this table.
drop	Drop (delete) this table.
dropColumn	Drop (delete) this table.
dropConstraint	Drop (delete) a table constraint such as a foreign or primary key.
dropIndex	Drop (delete) a table constraint such as a foreign or primary key.
_getCache	(private) internal function for caching metadata
clearMetadataCache	clears metadata cache for this table
applyChanges	Applies 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

sql

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

See

Table.applyChanges
Table.deferExec

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_cat	string table catalog/db name (may be null)
table_schem	string table schema/username (may be null)
table_name	string table name
column_name	string column name
data_type	int sql type from java.sql.types
type_name	string data source dependent type name, for a UDT the type name is fully qualified
column_size	int column size. for char or date types this is the maximum number of characters, for numeric or decimal types this is precision.
buffer_length	null
decimalDigits	int the number of fractional digits
num_prec_radix	int radix (typically either 10 or 2)
nullable	int 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

remarks	string comment describing column (may be null)
column_def	string default value (may be null)
sql_data_type	int unused
sql_datetime_sub	int unused
char_octet_length	int for char types the maximum number of bytes in the column
ordinal_position	int index of column in table (starting at 1)
is_nullable	string “no” means column definitely does not allow null values; “yes” means the column might allow null values. an empty string means nobody knows.
scope_catlog	string catalog of table that is the scope of a reference attribute (null if data_type isn’t ref)
scope_schema	string schema of table that is the scope of a reference attribute (null if the data_type isn’t ref)
scope_table	string table name that this the scope of a reference attribure (null if the data_type isn’t ref)
source_data_type	short 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_cat	table catalog (may be null)
table_schem	table schema (may be null)
table_name	table name
column_name	column name
key_seq	sequence number within primary key
pk_name	primary key name (may be null)

foreignKeys

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

pktable_cat	string Referenced table catalog/db name being imported (may be null)
pktable_schem	string Referenced table schema/username being imported (may be null)
pktable_name	string Referenced key table name
pkcolumn_name	string Referenced column name being imported
fktable_cat	string this table catalog/db name (may be null)
fktable_schem	string this table schema/username (may be null)
fktable_name	string this table name
fkcolumn_name	string this column name
key_seq	short sequence number within a foreign key
update_rule	short 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_rule

short 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_name	string foreign key name (may be null)
pk_name	string primary key name (may be null)
deferrability	short 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_cat	string local column table catalog (may be null)
pktable_schem	string local column table schema (may be null)
pktable_name	string local column table name
pkcolumn_name	string local column column name
fktable_cat	string foreign catalog (may be null)
fktable_schem	string foreign schema (may be null)
fktable_name	string foreign table name
fkcolumn_name	string foreign column name
key_seq	short sequence number within foreign key
update_rule	short 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_rule

short 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_name	string foreign key name (may be null)
pk_name	string local column name (may be null)
deferrability	short 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:

name	index name
unique	boolean, true if this is a unique index
columns	an array of the column names that make of the index.

hasIndex

returns true is the specified index exists

Parameters

colnames	array of column names in the index
isUnique	true, 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

options

Object representing the porperties of this column. See below

Options

name	name of the column,
type	one of BIGINT,BLOB,CLOB,DATE,INTEGER,NUMERIC,TEXT, TIMESTAMP,VARBINARY,VARCHAR
maxLength	Maximum size in characters or digits
decimalDigits	Maximum decimal places stored for NUMERIC types
allowNull	Optional, default true Should null values be allowed in this column?
defaultValue	Optional, default null What should be stored in this column when no value is supplied?
isUnique	Optional, default false must all values in this column be unique?
isPrimaryKey	Optional, default false should this column be the primary key for this table?. will generate an error if a primary key already exists
references	Optional, default null column definition of existing column in foreign table that this column references. See Reference Definition below

Reference Definition properties

table	name of foreign table
column	name of foreign column
onDelete	Optional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”
onUpdate	Optional, 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

name	Name of existing column to modify
options	Object representing the properties of this column. See below

Options

name	Optional new name of the column,
type	Optional 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
maxLength	Optional Maximum size in characters or digits. Setting this to smaller value than the existing type will probably fail.
decimalDigits	Optional Maximum decimal places stored for NUMERIC types. Setting this to smaller value than the existing type will probably fail.
allowNull	Optional Should null values be allowed in this column? Setting this to false on a column with null values will fail
defaultValue	Optional What should be stored in this column when no value is supplied?
isUnique	Optional must all values in this column be unique?
isPrimaryKey	Optional should this column be the primary key for this table?. This will generate an error if a primary key already exists
references	Optional, default null column definition of existing column in foreign table that this column references. See Reference Definition below

Reference Definition properties

table	name of foreign table
column	name of foreign column
onDelete	Optional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”
onDelete	Optional, 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

options

Object representing the properties of this key. See below

Options

localColumn	Name of local column that references foreign table
foreignTable	Name of foreign table
foreignColumn	Name of foreign column
id	Optional, default auto generated Name of key
onDelete	Optional, default null one of “CASCADE, SET NULL, SET DEFAULT, NO ACTION”
onUpdate	Optional, 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

options

Object representing the properties of this key. See below

Options

column	Name of local column that will be the primary key. Myna only supports a single column key
id	Optional, 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

options

Object representing the porperties of this column. See below

Options

columns	Array of column names to include in the index
id	Optional, default auto-generate name to give to the index
unique	Optional, 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

type	template key to retrieve

getSqlColumnName

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

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

Parameters

colname

case-insensitive column name

create

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

Create this table.

Parameters

options

Object representing the porperties of this column. See below

Options

recreate	Optional, default false Should this table be dropped first if it already exists?
columns	Optional, 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 table.

Paramaters

name	name 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

name	name 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) a table constraint such as a foreign or primary key.

Paramaters

name	name of constraint to remove

Example

var db = new Database("example")

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

_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

Table.deferExec
Table.sql