<database>.DBColumns

This file defines table columns in the textual database. Each line in the file contains a comma separated list of values that define a single column:

TEXT

<table_name>, <column_name>, <column_label>, <format>, <size>, <position>, <length>

The values are as follows:

Entry	Description
<table_name>	The name of the table this column appears in. Must be defined in <database>.DBTables.
<column_name>	The column name. This must be unique within this table.
<column_label>	A label (display name) for the column.
<format>	The column data type. One of the following values: `integer` (a 32 bit integer) `string` `real` (a 64-bit floating point number) `long` (a 64-bit integer) `date` `time` `date-time` The format string can also contain additional information about the column data type: If there is a chance the column may contain null values, then you must specify this as described below. If a column contains one of the date/time types, then you must specify the date format. See below for more information.
<size>	The maximum number of characters for string or date/time types.
<position>	The position of the column in the row. For fixed length columns (i.e. the value of the length parameter is greater than 0) this value indicates the byte offset from the start of the row. For variable length columns (i.e. the value of the length parameter is set to 0) this value indicates the number of delimiters to skip.
<length>	The length of the column. Setting the length to 0 indicates that the column has variable length.

For example, the following excerpt defines some of the columns in the People TDD:

People.DBColumns

TEXT

Person,Name,Name,string,9,0,0
Person,Income,Income,real,9,3,0
Person,Gender,Gender,string,9,2,0
Person,City,City,string,9,4,0
Person,Age,Age,integer,9,1,0
GENDER_CLASS,code,category key,string,60,0,0
GENDER_CLASS,name,category name,string,512,1,0

Null Values

By default, TDD source files do not support null values in data. If one of your columns contains null values then you must add :nullable: to the end of the format string.

For example, the following line indicates that the CITY_CLASS1 column contains a string and may be null:

TEXT

CITY_CLASS,CITY_CLASS1,CITY_CLASS1 label,string:nullable:,128,2,0

You can also specify a column value that should be treated as null. For example, the following lines indicate that:

For the personname column, a column that contains the value null should be treated as a null value.
For the gender column, a column value of X should be treated as null.

CODE

person, personname, Person Name, string:nullable: null, 20, 1, 0
person, gender, Person Gender, integer:nullable: X, 1, 2, 0

If your database contains null values and you do not include :nullable: in your column definition, then SuperCHANNEL will fail to build your database with an error message similar to the following: java.sql.SQLException: java.lang.Exception: java.lang.NumberFormatException: For input string: ""

Date and Time Formats

The TDD standard supports three time formats: date, time and date-time. If your data contains one of these formats then you must use format descriptors to define the date or time format.

Use the following letters to specify the date and time formats:

Y	Year field in `date` and `date-time`.
M	Month field in `date` and `date-time`.
D	Day field in `date` and `date-time`.
H	Hour field in `time` and `date-time`.
N	Minutes field in `time` and `date-time`.
S	Seconds field in `time` and `date-time`.

You can either specify the format with or without a separator. If there is no separator then a fixed width for each field is assumed. With a separator the fields can be variable length. For example:

The format DDMMYYYY specifies a date with a 2 digit day, 2 digit month and 4 digit year, such as 28011971 (28th January 1971).
The format M-D-Y can accommodate dates specified as 03-04-1977, 1-12-2001 and 1-1-1989.

The descriptors are not case sensitive. Upper case and lower case can be used interchangeably. YYYY and yyyy are interpreted in exactly the same way.

Two Digit Years in Source Data

You are recommended to use four digit years wherever possible. However, if your source data contains two digit years then you can specify a base year in brackets in the date format to address the problem of which century is implied by the two digits:

For a value equal to or higher than the base, SuperCHANNEL adds 1900 to the year.
For a value lower than the base, SuperCHANNEL adds 2000 to the year.

So for example if you specify the date format M-D-YY(74) then a value of 74 is interpreted as 1974, while a value of 73 is interpreted as 2073.

Date and Time Limitations

The earliest supported date value is 15/10/1582.

Examples

For example, the following line indicates that the erected column contains a date with a two digit year in the source data. Year values from 00 to 19 will be interpreted as 2000 to 2019, while 20 to 99 will be interpreted as 1920 to 1999:

TEXT

Building, erected, Date of Building, Date:DD.MM.YY(20): nullable:,8,1,0