BabelKit Code Table Setup

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

Sample Schema

Here is a sample MySQL code table schema definition plus a few example English and French month code records. Feel free to adapt these field definitions to your needs and your database management system as outlined below.
CREATE TABLE bk_code (
  code_set varchar(16) binary NOT NULL default '',
  code_lang varchar(5) 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_lang, code_code)
);

INSERT INTO bk_code VALUES ('month', 'en', '5',  'May',      5,'');
INSERT INTO bk_code VALUES ('month', 'en', '10', 'October', 10,'')
INSERT INTO bk_code VALUES ('month', 'fr', '5',  'mai',      0,'');
INSERT INTO bk_code VALUES ('month', 'fr', '10', 'octobre',  0,'');
The default code table name is bk_code. BabelKit can support multiple code tables per database, although this is rarely necessary. If your code table name is other than 'bk_code' pass the code table name into the new BabelKit() 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 month code set will have a code_set value of 'month'. The code_set field is limited to 16 characters by the Translation Utility.

The code_lang field holds the code for the language that each code record belongs to. For example, each French code record will have a code_lang value of 'fr'. The code_lang field is limited to 5 characters by the Translation Utility. This leaves room for the two character language code plus a dialect such as 'en-us' for U.S. English. All code records must exist first in the native language.

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, code_lang and code_code values all 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. BabelKit 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, one record for each language translation. For example 'October' for the English description and 'ottobre' for the French description. None of the code_set, code_lang, 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 BabelKit to support translation of 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. It is only set in the native language code records, the 'en' records in the example above. 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.

When a code is first added in the native language, if the code_order field is not specified and the code_code field value is numeric, the code_order field is set to the value of the code_code field. This provides numeric sorting by default which can be overridden as necessary by specifying the code_order field values.

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. The 'd' flags are only stored in the native language records. 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 BabelKit Translation Utility requires at least five core data records to be loaded before it can function properly:
INSERT INTO bk_code VALUES ('code_set',  'en', 'code_set',  'code set',   -3,'');
INSERT INTO bk_code VALUES ('code_set',  'en', 'code_lang', 'language',   -2,'');
INSERT INTO bk_code VALUES ('code_set',  'en', 'code_admin','code admin', -1,'');

INSERT INTO bk_code VALUES ('code_admin','en', 'code_admin', 'param=1 slave=1',
                            0,'');

INSERT INTO bk_code VALUES ('code_lang', 'en', 'en',        'English',    -1,'');

Three code_set records define the three core code sets. One code_lang record defines the native language.

They should all be in the native language. BabelKit discovers which language is the native language for this code table by looking at the language of the 'code_admin' 'code_admin' record. In this case 'en' for English.

To initialize a code table with another native language replace the 'en' values above with another language code. At some point it might be fun to make the BabelKit Translation Utility user interface multilingual.

Loading the Schema Definition and Core Data Records

Load the schema definition. With MySQL, do this:
$ mysql yourdatabasename < data/bk_schema.mysql
Load the core data records. With MySQL, do this:
$ mysql yourdatabasename < data/bk_setup.core
You can optionally load the demo data records. With MySQL, do this:
$ mysql yourdatabasename < data/bk_demo.latin1
Make sure that your records are loaded. With MySQL, do this:
$ mysqldump yourdatabasename bk_code | less
You should see your schema definition and the data records that you have loaded.