table.column

In Andromeda we say that we place a column into a table. The column must be previously defined with a column definition. When the column is placed into a table any of its properties can be overridden except for type_id, colprec, colscale and alltables.

table table_id:
   # ...table property assignments
   column column_id:
      # When placing a column in a table, there are no required properties
      uisearch: Y/N
      primary_key: Y/N
      pk_change: Y/N
      range_from: column_id
      range_to: column_id
      suffix: string
      prefix: string
      # These are properties of a column that can be overridden
      # in each table they are placed in
      description: string
      automation_id: ( FETCH, DISTRIBUTE, SUM ... )
      auto_formula: table.column
      value_min:
      value_max:
      uiro: Y/N
      uino: Y/N
      uicols: numeric
      uirows: numeric
      required: Y/N
      dispsize: numeric
      # These are row-level security
      permrow: Y/N
      table_id_row: string

Properties

primary_key. A Y/N flag that adds the column to the table's primary key definition.

pk_change. A Y/N flag that allows a primary key to be changed. Defaults to no. This flag must be set for each column in the primary key. When a primary key value is updated in the database it will cascade to all child tables.

range_from and range_to modify the primary key. These are discussed below under 'Range Primary Keys'.

uisearch. A Y/N flag that adds the column to the list of columns displayed on regular search results.

suffix and prefix are used to modify the column's name. These are discussed below under 'Reusing and Renaming columns'.

automation_id. See the description of column#automation_id.

auto_formula. See the description of column#auto_formula.

value_min. The minimum allowed value for the column.

value_max. The maximum allowed value for the column.

uiro. See the description of column#uiro.

uino. See the description of column#uino.

uicols. See the description of column#uicols.

uirows. See the description of column#uirows.

required. See the description of column#required.

dispsize. See the description of column#dispsize.

uiwithnext. See the description of column#uiwithnext.

permrow. When this is set to Y, andromeda will only allow a user to see rows in the table where their user_id matches the value of this column. This is the first of two methods for row-level security. Using this method, no two users can ever see the same rows.

table_id_row. This property implement's Andromeda's second method of row-level security. This method has the advantage of allowing multiple people to gain access to the same rows. When "table_id_row" is set to the name of a table, then it is assumed that:

• This column must be a foreign key to said table
• The parent table contains a column called user_id
• A user will be able to see the row if their user_id matches in the parent table.

A Range Primary Key

Andromeda allows you to specify that a primary key can cover a range of values. Consider the case where you need to define billing rates for your customers. The rates change from time to time, but do not follow predictable patterns like always falling exactly into a year or a month. So you must specify that customer X is billed at rate Y from 4/15/07 to 6/1/08, and at rate Z from 6/2/08 to 12/31/08. The ranges may not overlap. This is done with the 'range_from' and 'range_to' properties. These must be specified with the 'primary_key' flag to establish a range:

table rates:
   foreign_key customers:  # make customers part of primary key
      primary_key: "Y"
   column date_begin:
      suffix: _begin   # 'suffix' is explained in the next section 
      primary_key: "Y"
      range_to: date_end
   column date_end:
      suffix: _end   
      primary_key: "Y"
      range_from: date_begin

It should be noted hat this feature has been tried by others, but is not in wide-spread use outside of Andromeda (at least not that we know of). For this reason it should be expected that the feature itself will become better defined as it is used by more people, and it should be understood that best practices have yet to emerge.

Re-using and Re-naming Columns

Andromeda supports the re-use of columns by placing them into tables with different suffixes or prefixes. The basic idea is that you may have a column called 'phone' but you want to put 'fax' and 'cell' into a table and keep their column names similar and not have to define all three columns.

If you make use of Andromeda pre-defined columns and the #prefix and #suffix properties then you will actually not need to define that many columns at all. Andromeda predefines columns like 'amt' (for amount), and 'date' and 'first_name' and 'description' and so forth. The following example from an POS application shows the predefine column 'amt' being used twice:

Andromeda does not support the complete renaming of columns, such as putting a 'phone' column into a table with the name 'cell'.

table example:  
   column phone:      # An andromeda pre-defined columns
   column phone_fax:  # specify the complete new column name
      suffix: _fax    # tell us what part of the name is the suffix
   column phone_cell:
      suffix: _cell   
   column pre_phone_suf:  # this is silly, but allowed
      prefix: pre_
      suffix: _suf