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.

# Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
# Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
## 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
## Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
## 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).
# Paste your table creation SQL script into an SQL window and run the script
# Set table permissions '''Important'''
## Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT, for any user that needs to access the table:

 GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO isikhnascore;
 GRANT SELECT, UPDATE, INSERT ON TABLE ''tablename'' TO smsd;
 GRANT SELECT ON TABLE ''tablename'' TO champion;
 GRANT SELECT ON TABLE ''tablename'' TO mailer;

# Set sequence permissions '''Very important'''
## Ensure that the sequence has all privileges set for any user that may INSERT records into the data table (mainly isikhnascore and smsd). This is really easy to forget and causes permission errors.

 GRANT ALL ON TABLE ''tablename''_id_seq TO isikhnascore;
 GRANT ALL ON TABLE ''tablename''_id_seq TO smsd;

# 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 (
   -- record identifier (primary key)
   id serial primary key,
   ...data fields here...
   -- if the table is used to store data submitted by SMS
   msgid bigint not null,
   -- standard system audit fields
   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.

If the table is used to store data submitted by SMS or IM, it should also include the '''msgid''' field, which stores the message ID used to link incoming and outgoing SMS data.

==Table and field naming standards==

===Schemas===
There are a number of schemas in the database for different purposes. Most tables should be created in the following schemas:
* data - all routine data tables (i.e. tables containing data submitted by users)
* reference - look up tables
* lab - data tables for the lab system 

The other schemas are for specialised purposes
* backoffice - web and database administrative tables
* metadata - data defining table, field, spreadsheet, structures etc
* sms - SMS system management 
* reports - output report definitions
* public - not used, except for functions from imported libraries

===Table names===
Tables may be named in English or Indonesian. Core tables developed by the original team have been named in English, but later tables developed by champions can be named in Indonesian.

Tables are named in all lowercase. Multiple words are combined without spaces or underscores. (This rule has been applied inconsistently by some developers less familiar with the standards but should be adhered to for future table creation)

The name of the table represents the elements contained in each record, expressed as a plural (in English). Link tables use the combined names of the two tables they are linking.

Table names should be unique in the entire database (even though tables with the same name are permitted in different schemas).

===Fields===
Fields are named all lowercase with no spaces, with the name reflecting concisely the content. 

===Reference fields===
Reference fields are named based on the target table followed by 'id' with no space or underscore. So to reference the id field of the users table, the reference field would be userid.

All reference fields are of type integer except in unusual circumstances where a very large number of records are expected, when a bigint is used.

==Examples==
See the automated list of [[database tables]] for examples. Note the displayed format is not exactly the same as the recommended table definition format in all cases (e.g., the use of 'serial' and references).

====Reference table====
The ageunits table is an example of a simple reference table:

 CREATE TABLE ageunits (
   id serial PRIMARY KEY,
   '''code character varying NOT NULL,'''
   '''name character varying[] NOT NULL,'''
   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
 );

Standard names for reference table fields include:
* name - an array of type character varying. This is used to display the Indonesian name [1] and English name [2]
* code - character varying. A short abbreviation such as may be used in SMS messages.
* hiercode - character varying. Numbers in the form 3.4.2.3 representing a hierarchical coding system. As an example

{{#apGetSQL:
select hiercode, code, 
format('%s%s',
  repeat('&nbsp;',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])
from specimentypes order by regexp_split_to_array(hiercode,'\.')|Hiercode,Code, Spesimen}}

====Data tables====
An example of a data table (animals in laboratory submissions) is shown below:

 CREATE TABLE animals (
   id serial PRIMARY KEY,
   '''labsubmissionid integer REFERENCES labsubmissions,'''
   '''animalident character varying NOT NULL,'''
   '''ownerid integer REFERENCES owners,'''
   '''animalid bigint REFERENCES hewan,'''
   '''speciesid integer NOT NULL REFERENCES species,'''
   '''sexid integer REFERENCES sex,'''
   '''age double precision,'''
   '''ageunitsid integer REFERENCES ageunits,'''
   '''msgid bigint,'''
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
 );