New table creation - Revision history

Jillhinchliffe: /* Reference fields */

2018-06-22T10:28:23Z

‎Reference fields

2018-06-22T10:24:38Z

‎Table names

2018-06-22T10:21:32Z

‎Table names

2018-06-22T10:20:01Z

‎Schemas

2014-09-02T15:15:34Z

‎Standard fields

2014-09-02T15:14:30Z

‎Standard fields

2014-07-08T03:53:46Z

‎Technical guide

2014-07-08T03:53:00Z

‎Technical guide

2014-07-08T02:26:23Z

‎Data tables

2014-07-08T02:25:35Z

‎Reference table

@@ Baris 82: / Baris 82: @@
 ===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 user table, the reference field would be userid.
+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.

@@ Baris 76: / Baris 76: @@
 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 schema).
+Table names should be unique in the entire database (even though tables with the same name are permitted in different schemas).
 ===Fields===

@@ Baris 74: / Baris 74: @@
 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 are use the combined names of the two tables they are linking.
+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 schema.
+Table names should be unique in the entire database (even though tables with the same name are permitted in different schema).
 ===Fields===

@@ Baris 67: / Baris 67: @@
 * sms - SMS system management
 * reports - output report definitions
-* public - not use, except for functions from imported libraries
+* public - not used, except for functions from imported libraries
 ===Table names===

@@ Baris 51: / Baris 51: @@
 * 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.
 ==Table and field naming standards==

@@ Baris 31: / Baris 31: @@
   CREATE TABLE ages (
     id serial primary key,
     ...data fields here...
     createdby integer NOT NULL REFERENCES users,
     createdon timestamp NOT NULL DEFAULT now(),

@@ Baris 18: / Baris 18: @@
   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

@@ Baris 11: / Baris 11: @@
 ## 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 permissions '''Important'''
+# Set table permissions '''Important'''
-## Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT.
+## 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:
 # Test the table

@@ Baris 108: / Baris 108: @@
     '''sexid integer REFERENCES sex,'''
     '''age double precision,'''
     '''msgid bigint,'''
     createdby integer NOT NULL DEFAULT 1 REFERENCES users,
@@ Baris 113: / Baris 114: @@
     modifiedby integer REFERENCES users,
     modifiedon timestamp without time zone,
-    del boolean NOT NULL DEFAULT false,
+    del boolean NOT NULL DEFAULT false
   );

← Revisi sebelumnya		Revisi per 8 Juli 2014 02.25
Baris 95:		Baris 95:
	repeat(' ',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])		repeat(' ',(array_length(regexp_split_to_array(hiercode,'\.'),1)-1)*2),name[2])
	from specimentypes order by regexp_split_to_array(hiercode,'\.')\|Hiercode,Code, Spesimen}}		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,'''
		+	'''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,
		+	ageunitsid integer REFERENCES ageunits
		+	);