CodeKit Code Table Setup

How to set up a CodeKit code table. This consists of the database table definition and at least five core code records that need to be loaded for the CodeKit Administration Page to function.

Sample Schema

Here is a sample MySQL code table schema definition plus a few example day of week records. Feel free to adapt these field definitions to your needs and your database management system as outlined below.
CREATE TABLE ck_code (
  code_set varchar(16) binary NOT NULL default '',
  code_code varchar(32) binary NOT NULL default '',
  code_desc blob NOT NULL,
  code_order smallint(6) NOT NULL default '0',
  code_flag char(1) binary NOT NULL default '',
  PRIMARY KEY  (code_set, code_code)
);

INSERT INTO ck_code VALUES ('day', '1', 'Monday',    1, '');
INSERT INTO ck_code VALUES ('day', '2', 'Tuesday',   2, '');
INSERT INTO ck_code VALUES ('day', '3', 'Wednesday', 3, '');
INSERT INTO ck_code VALUES ('day', '4', 'Thursday',  4, '');
INSERT INTO ck_code VALUES ('day', '5', 'Friday',    5, '');
The default code table name is ck_code. CodeKit can support multiple code tables per database, although this is rarely necessary. If your code table name is other than 'ck_code' pass the code table name into the new CodeKit() method.

The code_set field holds the code for the code set that each code record belongs to. For example, each code record for the day code set will have a code_set value of 'day'. The code_set field is limited to 16 characters by the Translation Utility.

The code_code field holds the actual code value. For example '10' for the month of October, or 'cad' for Canadian Dollars. The code_code field is limited to 32 characters by the Translation Utility. Codes rarely need to be that long and since long code values can take up considerable space in the database proper, they should be kept to a reasonable length.

Code_set and code_code values consist of alphanumeric plus '-' and '_' like this: /^[a-zA-Z_0-9-]+$/. Avoid html or url unsafe characters such as [&<">?=] or spaces in your codes. CodeKit assumes that all codes are html and url safe and does not encode them.

The code_desc field holds the code description for the code. None of the code_set, code_code or code_desc fields may be blank.

In this example the code_desc field is defined as a 'blob' which has a limit of 65535 characters under MySQL. This would be appropriate if you are using CodeKit to store paragraphs or pages of text. If you expect to be keeping only regular code descriptions then varchar(255) will be plenty.

The code_order field is used to specify the sort order for a code set. If the month sort order were not set, '10' would sort before '5', displaying October before May. Negative code_order numbers can be used to make a code sort early. See the 'code_set' records below for an example of this.

The example schema above defines code_order as a two byte signed integer. If you expect code_order values above 32767 then define code_order as a four byte signed integer field.

The code_flag field is a one character field used to tag obsolete records as deprecated with a 'd' value. Deprecated code descriptions are still available for display of old database values, but are not available for new data entry selections.

Core Data Records

The CodeKit Administration Page requires at least one core data record to be loaded before it can function properly:
INSERT INTO ck_code VALUES ('code_set', 'code_set', 'code set', -1, '');

The code_set record defines the code set code set :-)

Loading the Schema Definition and Core Data Records

Load the schema definition. With MySQL, do this:
$ mysql yourdatabasename < data/ck_schema.mysql
Load the core data records. With MySQL, do this:
$ mysql yourdatabasename < data/ck_setup.core
you can optionally load the demo data records. With MySQL, do this:
$ mysql yourdatabasename < data/ck_demo.sql
Make sure that your records are loaded. With MySQL, do this:
$ mysqldump yourdatabasename ck_code | less
You should see your schema definition and the data records that you have loaded.