Tables in Coda are much like tables in SQL-based databases. A table has a number of columns, each with a datatype and nullability flag. They can have data INSERTED into them, UPDATED, or DELETED, and SELECT statements can be used to run queries against them. They can have triggers, procedural code that is run either before or after a data-changing statement. Users need to have the correct INSERT, UPDATE, DELETE, or SELECT privileges to access them.
So what is different?
- Coda does not use the SQL datatypes. Instead it uses its own.
- Coda tables automatically have an autonumbering primary key column called ID. Developers don't need to think about this.
- All tables have audit columns called CREATE_USER, CREATE_DATE, MOD_USER, and MOD_DATE. These are automatically populated with the expected values.
- Coda tables have a "soft delete" flag. If specified, a boolean column called ACTIVE_FLAG is added and is used to indicate the deletedness of each row.
- A table can be designated as a REF table. This means that it and its data is automatically replicated across all environments of the application, automatically. Inserting, updating, and deleting its rows make the corresponding edit on each environments' datasource.
- A table can be designated as a GROUP table. A column called GROUP_ID is added and is automatically set on insert to be that of the current session's group.
- Tables can have parent-child relationships. The child table gets a column called PARENT_TABLE_ID which refers back to a row in the parent table.
Each of these additions codifies (and Codafies) some standard patterns seen in SQL databases and makes these patterns available through metadata rather than through convention.
create_table := CREATE [ REF | GROUP ]? [ TABLE <table_name> | SUBTABLE <table_name> OF <parent_table_name> ] [ DISPLAYED AS <display_name> ]? [ IN APPLICATION <application_name> ]? [ WITH SOFT DELETE ]? ( <column_definition> [ , ... ]* ) column_definition := <column_name> <field_type> [ DISPLAYED AS <display_name> ]? [ NULL | NOT NULL ]? [ DEFAULT [ <system_variable> | <string_literal> | <array_literal> ] ]? field_type := [ <type_name> | REFERENCE TO <table_name> ] [ ARRAY ]? system_variable := CURRENT_TIMESTAMP | CURRENT_GROUP_NAME | CURRENT_USERNAME
Creates a new database table.
All tables automatically have an autonumbering primary key column called ID and audit fields called CREATE_USER, CREATE_DATE, MOD_USER, and MOD_DATE which contain the appropriate information and are updated automatically. When creating a new table, these names as well as several others are reserved.
Tables can be defined as either REF, GROUP, or normal tables, with the latter being the default. REF tables are automatically replicated amongst all deployed environments, meaning any updates to them are immediately pushed to DEV, TEST, and PROD. This is useful for data elements such as states or provinces, postal codes, or simply pulldown menus that should be consistent between environments. GROUP tables have a string column added called GROUP_NAME which is used to bind the row to a particular group. This is only really useful in group applications where each user's session has a current group defined. This group is used as the default on INSERT statements into the table.
Tables can also be declared as SUBTABLEs of other tables. This is used to define one-to-many relationships between tables and subtables, and automatically adds an integer column called PARENT_TABLE_ID to the subtable that is used to refer back to the associated row in the parent table.
Table columns require a name and type, and can have an optional NOT NULL property, default value, and array flag.
alter_table := ALTER TABLE [ <application_name> . ]? <table_name> [ RENAME TO <table_name> | SET DISPLAY '<display_name>' | ADD COLUMN <column_definition> | ALTER COLUMN <column_name> SET <column_definition> | DROP COLUMN <column_name> | SET IDENTITY ( <column_name> [, ... ]* ) ] column_definition := <column_name> <field_type> [ DISPLAYED AS <display_name> ]? [ NULL | NOT NULL ]? [ DEFAULT [ <system_variable> | <string_literal> | <array_literal> ] ]? field_type := [ <type_name> | REFERENCE TO <table_name> ] [ ARRAY ]? system_variable := CURRENT_TIMESTAMP | CURRENT_GROUP_NAME | CURRENT_USERNAME
Modifies an existing table. The application name is only required if there is not a current application set in the session. When altering a column, the entire column definition must be entered, including the name of the column or a new name if that is desired.
Every table can have one or more columns set as its IDENTITY. These columns together form what would be in SQL parlance a unique key, and can be used to identify a row in addition to the table's ID column. It is also possible to add additional indexes to a table using the CREATE INDEX command.
drop_table := DROP TABLE [ <application_name> . ]? <table_name>
Drops a table from the database. The application name is only required if there is not a current application set in the session.
THIS IS A HIGHLY DESTRUCTIVE PROCESS. BE VERY CAREFUL.
First, it drops any reference columns in other tables that refer to subtables of this table. Then it drops any reference columns that point to this table. Then it drops any subtables, and finally it drops this table itself.
CodaServer's philosophy can best be described as "The developer knows what they are doing." Don't let it down.