Database Field Descriptions

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.

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

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

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.

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.

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.

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

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.

Database Field Descriptions

Tables

Table: check_field

Table: check_project

Table: constraint_type

Table: field_constraint

Table: field_dt_format

Table: field_list

Table: field_type

Table: project_constraint