Node:Database structure, Next:Internal structure of kort program, Previous:Internationalization, Up:Top

Database structure - schema objects

In mysql database the database name holding kort objects is always named kort and all the tables and possible other objects must be present in the said database or schema, whatever term is used for a single namespace within a SQL server. Please refer to MySQL documentation about creating databases, for example "man mysqladmin".

A number of database tables is required for kort to be able to run. For each record a table must be present. For each table there must be a column in table surrogates with a numeric value. The table kort_ui must be present, as described in chapter Modes of deployment. When creating said tables, at least the following thingies should be taken into account:

For each data table there must be columns recid and recid_master with data types INTEGER or something similar that can hold a big integer value.
When creating the schema objects, the database table surrogates should containg one row and non-null numeric values for each columns that are to be named after tables in said kort schema.
Length of each data column in data tables is the same length that is given as length in Application definition file syntax but as wchar_t -> UTF-8 conversion takes place before data is actually saved to db then depending on your language you may need up to 4 times longer database fields than you have on screen. When writing mostly western languages, most unicode characters tranlate into 2 bytes UTF-8 sequences, like scandinavian letters ä and ö. Note that in mysql the max len of CHAR typed field is 255 characters and if your language contantly produces UTF-8 sequences that are 4 bytes long then effectively max len of your text fields is 62 characters. I've not tried using blobs instead of CHAR fields but it might work as well. And there is an exception to field length:
Data type for DATETIME columns should always be INTEGER as the kort program does the ugly thing and converts dates from time_t to signed long and saves dates as signed longs. So when making the schema, select such a datatype for dates that can hold all possible values of signed long, before or after of year 2038.

There is an awk program that tries to do all this, that is distributed with the kort program, named schemabuilder.awk that is used in the following manner:

cat kort_appl_file*.txt | awk -f schemabuilder.awk > kortschema.sql

that tries to find all the tables/records and all the columns/recfields from given files and construct a suitable database structure that is in this example saved to file kortschema.sql. This file then may be fed to mysql with mysql shell command "\. kortschema.sql". As it may be, that a largish application actually contains number of different user interfaces e.g. number of application definition files that actually belong to same application, schemabuilder.awk can process the same record several times. Also, as there is only one schema and there is possibly unlimited number of possible applications, each applications tables just must be contained in the same schema, also therefore possibility to feed kortparser.awk several files. Right now schemabuilder.awk takes lengths of strings and multiplies them by 2 to get the database field size in hope that all UTF-8 sequences used will fit. If that is not good for you, edit the number at function "conditionally_add" as it is there commented quite clearly. It also seems to be that when same database table appears in application definition file set several times then schemabuild.awk just might have some problems bindigin right columns to right tables ; you may want to check the produces SQL by hand before trying to use it in the real world.