Location: PHPKode > scripts > CodeKit > codekit/ck_schema.html
<html>
<head>
<title>CodeKit Code Table Setup</title>
<link rel="STYLESHEET" type="text/css" href="main.css">
</head>

<!--
  Copyright (C) 2003 John Gorman <hide@address.com>
  http://www.webbysoft.com/codekit
-->

<body bgcolor="#F0FFFF" text="#000000"
      link="#873852" vlink="#873852" alink="#B52E2E">

<center>
<table width="600">
<tr>
<td>


<div align="right">
[<a href="ck_overview.html">Overview</a>]
[<a href="index.html">Index</a>]
[<a href="ck_setup_php.html">PHP Setup</a>]
</div>


<center><h2>
<a href="http://www.webbysoft.com/codekit">CodeKit</a>
Code Table Setup</h2></center>

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.


<h4>Sample Schema</h4>

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.

<pre>
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, '');
</pre>

The default code table name is <b>ck_code</b>.  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.

<p>
The <b>code_set</b> 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.

<p>
The <b>code_code</b> 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.

<p>
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 [&amp;&lt;&quot&gt;?=]
or spaces in your codes.  CodeKit assumes that all
codes are html and url safe and does not encode them.

<p>
The <b>code_desc</b> field holds the code description for
the code.  None of the code_set, code_code or code_desc fields
may be blank.

<p>
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.

<p>
The <b>code_order</b> 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.

<p>
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.

<p>
The <b>code_flag</b> 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.


<h4>Core Data Records</h4>

The CodeKit Administration Page requires at least one core
data record to be loaded before it can function properly:

<pre>
INSERT INTO ck_code VALUES ('code_set', 'code_set', 'code set', -1, '');
</pre>

<p>
The code_set record defines the code set code set :-)


<h4>Loading the Schema Definition and Core Data Records</h4>

Load the schema definition.  With MySQL, do this:

<pre>
$ mysql yourdatabasename &lt; data/ck_schema.mysql
</pre>

Load the core data records.  With MySQL, do this:

<pre>
$ mysql yourdatabasename &lt; data/ck_setup.core
</pre>

<a name="demodata"></a>
you can optionally load the demo data records.  With MySQL, do this:

<pre>
$ mysql yourdatabasename &lt; data/ck_demo.sql
</pre>

Make sure that your records are loaded.  With MySQL, do this:

<pre>
$ mysqldump yourdatabasename ck_code | less
</pre>

You should see your schema definition and the data
records that you have loaded.


<p>
<div align="right">
[<a href="ck_overview.html">Overview</a>]
[<a href="index.html">Index</a>]
[<a href="ck_setup_php.html">PHP Setup</a>]
</div>


</td>
</tr>
</table>
</center>

</body>
</html>
Return current item: CodeKit