Database Field Descriptions

Tables

check_field
check_project
constraint_type
field_constraint
field_dt_format
field_list
field_type
project_constraint

Table: check_field

^top
This table holds information about each field in a project (data type, field number, min and max allowable value, if it is required, etc.).

project_id check_project.project_id corresponding to the project in which this field belongs. This can be set up as a foreign key to the check_project table if desired.
field_number Field number/position (1,2,3,...n). JFileChecker assumes the integer values start with 1 and end with the value of the project's num_fields column in the check_project table.
field_name Field name. Used to identify this field by name in the project ("Last name", "GPA", ...).
field_type_id Integer representing the type of field, being text (1), numeric (2), date (3), and list (4). This can be set up as a foreign key to the field_type table if desired. List fields must have associated list values in the field_list table. Date fields must have associated acceptable date formats in the field_dt_format table.
is_required Boolean/bit field representing if the field is required or not. If your database does not support boolean or bit data types, you can use an integer type where 1 implies the field is required and 0 implies it is not required.
start_column If the is_delemited field in the check_project table for this project is 0 (i.e., this is a fixed width data file), then the start_column field specifies the starting/first column position of this field in a project data file.
end_column If the is_delemited field in the check_project table for this project is 0 (i.e., this is a fixed width data file), then the end_column field specifies the ending/last column position of this field in a project data file.
txt_min_length If the field_type_id = 1 for this field, and the file is delimited, then txt_min_length can be used to specify the minimum number of characters the required for this field if applicable.
txt_max_length If the field_type_id = 1 for this field, and the file is delimited, then txt_max_length can be used to specify the maximum number of characters the field can allow if applicable. This is usually set to the field size of the corresponding database column the data will be loaded into.
txt_reg_exp If the field_type_id = 1 for this field and the text must adhere to a particular pattern (like for a social security number, phone number, zip code, employee number, etc.) then a regular expression can be specified here that will be used to match the field against. See http://java.sun.com/j2se/1.4.1/docs/api/java/util/regex/Pattern.html for more information on Java 1.4.1 regular expression patterns.
dt_min If the field_type_id = 3 for this field, then dt_min can be used to specify the minimum date allowable for this field (if applicable).
dt_max If the field_type_id = 3 for this field, then dt_max can be used to specify the maximum date allowable for this field (if applicable).
num_min If the field_type_id = 2 for this field, then num_min can be used to specify the minimum number allowable for this field (if applicable).
num_max If the field_type_id = 2 for this field, then num_max can be used to specify the maximum number allowable for this field (if applicable).
num_max_decimals If the field_type_id = 2 for this field, then num_max_decimals can be used to specify the maximum number of decimals for this field (if applicable). The minimum value for this field is 0.
is_list_case_sensitive If the field_type_id = 4 for this field, then is_list_case_sensitive can be used to specify whether or not list comparisons with values given in the field_list table should be case sensitive. 1 implies comparisons are case sensitive, 0 implies comparisons are not case sensitive.

Table: check_project

^top
This table holds project information (data file "profiles").

Field Description
project_id Integer primary key for the table. If you have more than one record in the check_project table, this field should contain consecutive incrementing integer values starting with 1. The code assumes no value for this field will ever be negative.
project_title Title for the project.
num_fields Number of fields contained in data files for this project. Note: JFileChecker expects the number of fields to be the same for each row of data.
is_delimited Should be 1 if data files for this project are delimited, 0 if they are fixed width.
delimiter If is_delimited = 1, then this should contain the delimiting character (",", "|", ":", ...)
escape If is_delimited = 1, then this should contain the delimiter "escape" character if one is applicable. Note: If an even number of (like 2) escape characters immediately preface a delimiter in a line of data, then the delimiter character is assumed to be a "real" field delimiter. If an odd number of escape characters immediately preface a delimiter in a line of data, then the delimiter character is assumed to be part of the field and not a field delimiter. For example, if a comma (",") is the delimiter, and a backslash ("\") is the escape character, then this line would be parsed as having three fields
Bill Smith, C:\\home\\Bill Smith\\, 2Mb
       
while this line would parsed as having only two fields
Hamlet, To be\, or not to be
       
max_file_size Maximum size for files that are to be checked for this project in Megabytes. Fractional sizes are allowed. Leave null if there is no maximum.
num_header_rows Number of header rows in the data files (rows at the top of the files that should not be checked). Set to zero if the data files contain no header rows.
max_errors Maximum number of errors to be found for a data file before JFileChecker stops checking the data file.
data_path Absolute path where the data files for this project are located. If a backslash is part of the path, it must be escaped with a second backslash. ("C:\\JFileChecker\\data\\", "/misc/JCheckfiles/data/", ...)
log_path Absolute path where the log file for this project is placed. As of version 0.1, if more than one project contains the same log_path, then the same log file in that directory is used (log entries are appended).
result_path Absolute path where the result files for this project are placed. As of version 0.1, the result file name is created by appending the data file name with the current date and time formatted as "_yyyyMMddHHmmss.results".

Table: constraint_type

^top
This table is houses constraint types. As of version 0.2, there are only two constraint types, "unique" and "all or none".

constraint_type_id Primary key/identity for the table. The table should contain only two records, the values for this field being 1 and 2.
constraint_type_name Constraint type name. The table should contain only two records, the values for this field being "unique" and "all or none".

Table: field_constraint

^top
This table is a cross reference table mapping fields to constraints. I.e., it defines which fields belong to which constraints.

constraint_id project_constraint.constraint_id for which the constraint applies. This can be set up as a foreign key to the project_constraint table if desired.
field_number check_field.field_number for which the constraint applies. This can be set up as a foreign key to the check_field table if desired.

Table: field_dt_format

^top
This table defines valid date formats for date fields (date fields are those with check_field.field_type_id=3).

field_dt_format_id Primary key/identity for the table.
project_id check_project.project_id for which the date format applies. This can be set up as a foreign key to the check_project table if desired.
field_number check_field.field_number for which the date format applies. This can be set up as a foreign key to the check_field table if desired.
dt_format Java date format. See http://java.sun.com/j2se/1.4.1/docs/api/java/text/SimpleDateFormat.html for more information on acceptable values for this field.
check_order If multiple acceptable date formats are specified for the same project/field number, then this field can be used to specify the order in which the formats should be compared against the raw data. The comparisons are performed by check_order in ascending order.

Table: field_list

^top
This table defines valid field values for list fields (list fields are those with check_field.field_type_id=4).

field_list_id Primary key/identity for the table.
project_id check_project.project_id for which the list item applies. This can be set up as a foreign key to the check_project table if desired.
field_number check_field.field_number for which the list item applies.
list_item Valid list item the for the project/field number. Examples may include country codes, the strings "true" and "false", etc.

Table: field_type

^top
This table is houses field types. As of version 0.1, there are four, "text", "number", "date", and "list"

field_type_id Primary key/identity for the table. The table should contain only four records, the values for this field being 1, 2, 3, and 4.
field_type_name Field type name. The table should contain only four records, the values for this field being "text", "number", "date", and "list".

Table: project_constraint

^top
This table defines project constraints by name, project, and constraint type ("unique" or "all or none"). Fields that belong to a project constraint are listed in the field_constraint table.

constraint_id Primary key/identity for the table.
project_id check_project.project_id for which the constraint applies. This can be set up as a foreign key to the check_project table if desired.
constraint_name Common name of this project constraint by which it can be referred.
constraint_type_id constraint_type.constraint_type_id designating the type of constraint for this project constraint ("unique" or "all or none"). This can be set up as a foreign key to the constraint_type table if desired.