Wiki Sumber Informasi iSIKHNAS

New table creation

Technical guide

New tables are created manually by administrators or iSIKHNAS technical champions using either direct server shell access and psql, or PGAdmin on a remote computer.

In either case you will need access passwords and appropriate permissions for table creation. This example uses PGAdmin.

1. Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
2. Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
   1. Crashtest - this is the first one you should use. It is the development server and is reconfigured every night (so all changes are lost). Use this to make sure your table creation script is working
   2. Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
   3. Live - Once you are sure of the table structure, create it on the live system. it will then be automatically copied to Crashtest (every night) or Sidecar (every Monday).
3. Paste your table creation SQL script into an SQL window and run the script
4. Set permissions Important
   1. Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT.
5. Test the table

Standard fields

Every table in the database has a number of standard fields, which are used for consistency and for data auditing purposes. You may only omit or alter these standard fields if you have a very very good reason.

CREATE TABLE ages (
  id serial primary key,
  ...data fields here...
  createdby integer NOT NULL REFERENCES users,
  createdon timestamp NOT NULL DEFAULT now(),
  modifiedby integer REFERENCES users,
  modifiedon timestamp without time zone,
  del boolean NOT NULL DEFAULT false
);

The required standard fields are:

• id - the unique record id and primary key.
• createdby - the id of the user creating the record. This must be explicitly included in any INSERT statements
• createdon - time stamp for creation - automatically inserted
• modifiedby - user id for UPDATE - must be explicitly included
• modifiedon - timestamp for UPDATE - use now()
• del - the 'deleted' flag. iSIKHNAS never uses DELETE statements, but instead simply flags records as deleted, so they can be referenced for historical purposes or undeleted if required.