17.6. Designing Screens

17.6.1.1. Special Columns

• PostERP uses column f0 to distinguish organizations (specific companies in business group, for example).

  If a table has column f0, all rows with value 1 in column f0 belong to company code 1. When designing SQL or functions, also take the values in this column into account in order to properly filter the desired rows. Also remember to add its constraint referencing to t45.f1 as foreign key.

• Refer to Section 6.3 for information about record privileges. Column f99, if exists, keeps track the record owner of every table row. PostERP automatically fills this column with the account with which an user enters a new record. Only row owner is allowed to update/delete that row.

  If a table does not consists of column f99, then all account have write privilege on all rows in that table.

• If a table consists of column f100 of data type BIGINT, that table automatically allows end users to attach any file to that table. Every row in that table accepts at most 32767 attachments.

  The default value of column f100 must be DEFAULT NEXTVAL('unique_int_seq')

  With column f100 added to a table, remember to also create a rule or trigger to automatically delete the associated attachment description row from table t61 when end users delete the row in question. Example:

  Example:

  --inventory items-- CREATE TABLE t_133 ( CONSTRAINT f2 FOREIGN KEY (f2) REFERENCES t_130 (f1) ON UPDATE CASCADE, f1 VARCHAR(20) PRIMARY KEY, --item# f2 VARCHAR(3) NOT NULL, --Finest Unit Of Measure (FUOM) f3 SMALLINT NOT NULL, --valid number of decimals for quantity f99 VARCHAR(12), f100 BIGINT DEFAULT NEXTVAL('unique_int_seq') ); CREATE OR REPLACE RULE r_133d AS ON DELETE TO t_133 DO ALSO DELETE FROM t61 WHERE f1=OLD.f100;

  Also remember to implement required mechanism for the system to also remove the attachments from file system after end user deletes rows from table. The backup procedure must also take into account the attached files.

17.6.1.2. Note

Please follow these guidelines to avoid clash with Tera Rows's design:

• When adding new columns to metadata tables, make sure to include _ as the new column names. For example, use these names for new columns: f_1, f_97, f_103.

• Include _ in new table names. Examples:t_120, t_143, d_143.

• Include _ in new view names. Examples: v_120, v1_43.

17.6.1.3. Note

• Due to the limitation of desktop client program, use VARCHAR instead of TEXT. If client program yields "access violation" error or silently crashes, shorten the width of the displayed VARCHAR column to see if the error get "fixed". 2048 or fewer characters seems to be fine.

• If a table's INSERT, UPDATE, and DELETE triggers want to post accounting journal but that table's primary key is not SERIAL, add a SERIAL column and use it as a surrogate key. See table t_33 in application# 2 and its triggers as an example.

• Use column data type TIMESTAMPTZ instead of TIMESTAMP. TIMESTAMP is not standard (browser?) conformant and thus mulfunctions.

• Do not allow NULL value in BOOLEAN columns in order to avoid ambiguous meanings and complicate business logic processing. Setting DEFAULT value for BOOLEAN data type is a good idea.

17.6.1.4. Note

If application server is running and the definition (colum data type, column name, new column, or dropped column, etc.) of any metadata table also known as dictionary is altered by DDL, then someone must connect to the same database and execute this command as soon as possible:

NOTIFY "DDLExecuted"
				

The purpose of this command is to notify application server to refresh its cache. Failing to do this in time, the thin clients using these tables may start to mulfunction and cause application server to log errors.