Installation
Configuration
Administration
Usage
Development
F.A.Q.
Database Schema (2.0)
tbl_accounts
The table contains information about user accounts, both internal and LDAP ones. Everytime, some LDAP user is logging in, his data (fullname and email) are updated in this table. When eTraxis needs to get fullname or email of some LDAP user, it takes the data from this table instead of querying LDAP server.
account_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
username
Type: string (ASCII)
Possible values: up to 112 characters
NULL values: none
Description: User login. Can contain only latin characters, digits, and underscore. Each internal account is stored with suffix "@eTraxis" to distinguish internal accounts from LDAP ones (see also is_ldapuser).
fullname
Type: string (unicode)
Possible values: up to 100 characters
NULL values: none
Description: Full user name.
Type: string (ASCII)
Possible values: up to 50 characters
NULL values: none
Description: User email address.
passwd
Type: string (ASCII)
Possible values: exactly 32 characters
NULL values: none
Description: MD5 hash of user password. Ignored for LDAP accounts.
description
Type: string (unicode)
Possible values: up to 100 characters
NULL values: allowed
Description: Optional description of account.
auth_token
Type: string (ASCII)
Possible values: exactly 32 characters
NULL values: allowed
Description: Authorization token of last user log on. See also token_expire.
token_expire
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when authorization token will be expired.
passwd_expire
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when password was changed last time (to determine when it will be expired).
is_admin
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether account has administration privileges. Ignored for LDAP accounts.
is_disabled
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether account is disabled by administrator. Ignored for LDAP accounts.
is_ldapuser
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether account is internal (FALSE), or from LDAP server (TRUE).
locks_count
Type: integer
Possible values: from 0 to infinity
NULL values: none
Description: Number of unsuccessful attempts to log on in a row. Cleared after successful log on.
lock_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp of last unsuccessful attempt to log on.
locale
Type: integer
Possible values:
1000 - English (US)
1001 - English (UK)
1002 - English (CAN)
1003 - English (AUS)
1010 - French
1020 - German
1040 - Spanish
1080 - Portuguese (Brazil)
1090 - Dutch
2050 - Latvian
3000 - Russian
3130 - Bulgarian
6000 - Turkish
7160 - Indonesian
NULL values: none
Description: Language of user interface.
page_rows
Type: integer
Possible values: from 10 till 100
NULL values: none
Description: Number of rows per page in the list.
page_bkms
Type: integer
Possible values: from 10 till 100
NULL values: none
Description: Number of bookmarks per page in the list.
csv_delim
Type: integer
Possible values: see eTraxis Function Reference
NULL values: none
Description: ASCII code of character that should be used as CSV delimiter when user exports list of records to CSV file.
csv_encoding
Type: integer
Possible values:
1 - UTF-8
2 - UCS-2
3 - ISO-8859-1
4 - ISO-8859-2
5 - ISO-8859-3
6 - ISO-8859-4
7 - ISO-8859-5
8 - ISO-8859-6
9 - ISO-8859-7
10 - ISO-8859-8
11 - ISO-8859-9
12 - ISO-8859-10
13 - ISO-8859-13
14 - ISO-8859-14
15 - ISO-8859-15
16 - KOI8-R
17 - Windows-1251
18 - Windows-1252
NULL values: none
Description: Characters set that should be used when user exports list of records to CSV file.
csv_line_ends
Type: integer
Possible values:
1 - Windows (0D+0A)
2 - Unix (0A)
3 - Mac (0D)
NULL values: none
Description: Line endings that should be used when user exports list of records to CSV file.
fset_id
Type: integer
Possible values: foreign key to tbl_fsets table
NULL values: allowed
Description: Current filters set.
view_id
Type: integer
Possible values: foreign key to tbl_views table
NULL values: allowed
Description: Current view.
tbl_attachments
The table contains information about attachments, added to records.
attachment_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
attachment_name
Type: string (unicode)
Possible values: up to 100 characters
NULL values: none
Description: Name of attachment.
attachment_type
Type: string (ASCII)
Possible values: up to 100 characters
NULL values: none
Description: MIME type of attachment.
attachment_size
Type: integer
Possible values: from 0 to infinity
NULL values: none
Description: Size of attachment in bytes.
event_id
Type: integer
Possible values: foreign key to tbl_events table
NULL values: none
Description: ID of event, when attachment has been added.
is_removed
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: FALSE if attachment is alive, TRUE otherwise.
tbl_changes
The table contains history of values.
event_id
Type: integer
Possible values: foreign key to tbl_events table
NULL values: none
Description: ID of event, caused changing value of the field.
field_id
Type: integer
Possible values: foreign key to tbl_fields table
NULL values: allowed
Description: ID of field, which value has been changed.
old_value_id
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: Old (previous) value of the field — depends on field type as following:
1 - integer value (from –1000000000 till +1000000000)
2 - string value (foreign key to tbl_string_values table)
3 - string value (foreign key to tbl_text_values table)
4 - state of checkbox (0 - unchecked, 1 - checked)
5 - integer value of list item (see int_value)
6 - record ID (see record_id)
7 - date value (Unix Epoch timestamp from 1/1/1980 till 12/31/2037)
8 - duration value (amount of minutes from 0:00 till 999999:59)
new_value_id
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: New value of the field — depends on field type as following:
1 - integer value (from –1000000000 till +1000000000)
2 - string value (foreign key to tbl_string_values table)
3 - string value (foreign key to tbl_text_values table)
4 - state of checkbox (0 - unchecked, 1 - checked)
5 - integer value of list item (see int_value)
6 - record ID (see record_id)
7 - date value (Unix Epoch timestamp from 1/1/1980 till 12/31/2037)
8 - duration value (amount of minutes from 0:00 till 999999:59)
tbl_children
The table contains information about "parent-child" relations between records. Child records are also named 'subrecords'. Any record can have unlimited number of child records, but any child record can have only one parent. Also, each child can be dependency. Record cannot be closed, if it has at least one opened child as dependency.
parent_id
Type: integer
Possible values: foreign key to tbl_records table
NULL values: none
Description: ID of parent record.
child_id
Type: integer
Possible values: foreign key to tbl_records table
NULL values: none
Description: ID of child record ("subrecord").
is_dependency
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether the child is dependency.
tbl_comments
The table contains information about comments, added to records.
comment_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
comment_body
Type: string (unicode)
Possible values: up to 4000 characters
NULL values: none
Description: Text of comment.
event_id
Type: integer
Possible values: foreign key to tbl_events table
NULL values: none
Description: ID of event, when comment has been added.
is_confidential
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether the comment is confidential.
tbl_def_columns
The table contains information about set of columns of current view.
column_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Owner of this columns set.
state_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: allowed
Description: Name of state, which owns a field this column is linked to.
field_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: allowed
Description: Name of field, which this column is linked to.
column_type
Type: integer
Possible values:
1 - standard column "ID"
2 - standard column "State"
3 - standard column "Project"
4 - standard column "Subject"
5 - standard column "Author"
6 - standard column "Responsible"
7 - standard column "L/E"
8 - standard column "Age"
9 - standard column "Created"
100 - custom column of "number" type
101 - custom column of "string" type
102 - custom column of "multilined text" type
103 - custom column of "check box" type
104 - custom column of "list" type (show integer values)
105 - custom column of "list" type (show text values)
106 - custom column of "record" type
107 - custom column of "date" type
108 - custom column of "duration" type
NULL values: none
Description: Type of column.
column_order
Type: integer
Possible values: from 1 to amount of columns of the same account
NULL values: none
Description: Each column must have its own ordinal number. No duplicates of this number among columns of the same account are allowed.
tbl_events
The table contains information about records history.
event_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
record_id
Type: integer
Possible values: foreign key to tbl_records table
NULL values: none
Description: ID of record, which is owner of the event.
originator_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: ID of account, which raised the event.
event_type
Type: integer
Possible values:
1 - record has been created
2 - record has been assigned
3 - record has been modified
4 - state has been changed
5 - record has been postponed
6 - record has been resumed
7 - comment has been added
8 - attachment has been added
9 - attachment has been removed
10 - record has been cloned
11 - subrecord has been added
12 - subrecord has been removed
13 - confidential comment has been added
NULL values: none
Description: Type of event.
event_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when the event has been happened.
event_param
Type: integer
Possible values: depends on event_type
NULL values: allowed
Description: Parameter of event — depends on event type as following:
1 - ID of first (initial) state of created record
2 - ID of account, the record has been assigned on
3 - NULL (not used)
4 - ID of state, the record has been changed to
5 - Unix Epoch timestamp, when record should be resumed automaticaly
6 - NULL (not used)
7 - NULL (not used)
8 - NULL (not used)
9 - ID of removed attachment
10 - ID of original record
11 - ID of subrecord
12 - ID of subrecord
13 - NULL (not used)
tbl_fields
The table contains information about fields.
field_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
state_id
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state.
field_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Name of field.
field_order
Type: integer
Possible values: from 1 to amount of fields of the same state
NULL values: none
Description: Each field must have its own ordinal number. No duplicates of this number among fields of the same state are allowed.
field_type
Type: integer
Possible values:
1 - number
2 - string
3 - multilined text
4 - checkbox (boolean)
5 - list
6 - record ID
7 - date
8 - duration
NULL values: none
Description: Type of field.
is_required
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether the field is required.
guest_access
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether the field values are accessible for non-authenticated users.
registered_perm
Type: integer
Possible values:
0 - no access
1 - read only
2 - read/write
NULL values: none
Description: Permissions, which are granted to any authenticated (logged in) user (for this field only).
author_perm
Type: integer
Possible values:
0 - no access
1 - read only
2 - read/write
NULL values: none
Description: Permissions, which are granted to author of record (for this field only).
responsible_perm
Type: integer
Possible values:
0 - no access
1 - read only
2 - read/write
NULL values: none
Description: Permissions, which are granted to current responsible of record (for this field only).
add_separator
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: If TRUE, then eTraxis will add separator (<hr/>) after the field, when record is being displayed.
regex_check
Type: string (unicode)
Possible values: up to 1000 characters
NULL values: allowed
Description: Perl-compatible regular expression, which values of the field must conform to.
regex_search
Type: string (unicode)
Possible values: up to 1000 characters
NULL values: allowed
Description: Perl-compatible regular expression to modify values of the field (search for).
regex_replace
Type: string (unicode)
Possible values: up to 1000 characters
NULL values: allowed
Description: Perl-compatible regular expression to modify values of the field (replace with).
param1
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: First parameter of the field — depends on field type as following:
1 - minimum of range of allowed values (from –1000000000 till +1000000000)
2 - maximum allowed length of values (up to 250)
3 - maximum allowed length of values (up to 4000)
4 - NULL (not used)
5 - NULL (not used)
6 - NULL (not used)
7 - minimum of range of allowed values (amount of days since current date; negative value shifts to the past)
8 - minimum of range of allowed values (amount of minutes from 0:00 till 999999:59)
param2
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: Second parameter of the field — depends on field type as following:
1 - maximum of range of allowed values (from –1000000000 till +1000000000)
2 - NULL (not used)
3 - NULL (not used)
4 - NULL (not used)
5 - NULL (not used)
6 - NULL (not used)
7 - maximum of range of allowed values (amount of days since current date; negative value shifts to the past)
8 - maximum of range of allowed values (amount of minutes from 0:00 till 999999:59)
value_id
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: Default value of the field — depends on field type as following:
1 - default integer value (from –1000000000 till +1000000000)
2 - default string value (foreign key to tbl_string_values table)
3 - default string value (foreign key to tbl_text_values table)
4 - default state of checkbox (0 - unchecked, 1 - checked)
5 - integer value of default list item (see int_value)
6 - NULL (not used)
7 - default date value (amount of days since current date; negative value shifts to the past)
8 - default duration value (amount of minutes from 0:00 till 999999:59)
tbl_field_perms
The table contains information about field permissions for system groups.
field_id
Type: integer
Possible values: foreign key to tbl_fields table
NULL values: none
Description: ID of field.
group_id
Type: integer
Possible values: foreign key to tbl_groups table
NULL values: none
Description: ID of group.
perms
Type: integer
Possible values:
1 - read only
2 - read/write
NULL values: none
Description: Permissions, which are granted to the group (for this field only).
tbl_field_values
The table contains information about old and current values of fields.
event_id
Type: integer
Possible values: foreign key to tbl_events table
NULL values: none
Description: ID of event, caused appearance of this value (e.g. modification of record).
field_id
Type: integer
Possible values: foreign key to tbl_fields table
NULL values: none
Description: ID of field.
value_id
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: Value of the field — depends on field type as following:
1 - integer value (from –1000000000 till +1000000000)
2 - string value (foreign key to tbl_string_values table)
3 - string value (foreign key to tbl_text_values table)
4 - state of checkbox (0 - unchecked, 1 - checked)
5 - integer value of list item (see int_value)
6 - record ID (see record_id)
7 - date value (Unix Epoch timestamp from 1/1/1980 till 12/31/2037)
8 - duration value (amount of minutes from 0:00 till 999999:59)
is_latest
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: If TRUE, then this value is current one for this field of the record (record is determined from event ID). If FALSE, then the value was changed somewhy (e.g. per modification of record). Only one value of the same field and same record can have TRUE in this attribute.
tbl_filters
The table contains information about created filters.
filter_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Owner of filter.
filter_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Name of filter.
filter_type
Type: integer
Possible values:
1 - all projects
2 - all templates of some project
3 - all states of some template
4 - selected states of some template
NULL values: none
Description: Filter scope.
filter_flags
Type: integer
Possible values: set of binary flags
NULL values: none
Description: Flags of filter (hexadecimal):
00 00 00 01 - filter shows only records, created by specified persons (see tbl_filter_accounts)
00 00 00 02 - filter shows only records, assigned on specified persons (see tbl_filter_accounts)
00 00 00 04 - filter shows only opened records
00 00 00 08 - filter shows only postponed records
00 00 01 00 - filter shows only active (not postponed) records
filter_param
Type: integer
Possible values: filter parameter
NULL values: allowed
Description: Depends on type of filter (see filter_type) as following:
1 - NULL (not used)
2 - ID of project (see project_id)
3 - ID of template (see template_id)
4 - ID of template (see template_id, tbl_filter_states)
tbl_filter_accounts
The table contains additional information about created filters (see tbl_filters). Every account, chosen as creator or responsible of records to be filtered out, is stored in this table.
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of filter, which the information is related to.
filter_flag
Type: integer
Possible values: hexadecimal binary flag
NULL values: none
Description: One of following filter flags (hexadecimal):
00 00 00 01 - filter shows only records, created by specified persons (see tbl_filters)
00 00 00 02 - filter shows only records, assigned on specified persons (see tbl_filters)
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: ID of user, who created a record (filter_flag equals to 0x01), or is its current responsible (filter_flag equals to 0x02).
tbl_filter_activation
The table contains information about enabled filters.
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of enabled filter.
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: ID of account, which the filter is enabled for.
tbl_filter_fields
The table contains information about filtering by values custom fields.
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of filter, which the information is related to.
field_id
Type: integer
Possible values: foreign key to tbl_fields table
NULL values: none
Description: ID of field, which values should be examined.
param1
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: Allowed range of the field values — depends on field type as following:
1 - minimum integer value (from –1000000000 till +1000000000), when NULL then any
2 - substring of value (foreign key to tbl_string_values table), when NULL then only records with empty fields will be shown
3 - substring of value (foreign key to tbl_string_values table), when NULL then only records with empty fields will be shown
4 - state of checkbox (0 - unchecked, 1 - checked)
5 - integer value of list item (see int_value), when NULL then any
6 - record ID (see record_id), when NULL then any
7 - minimum date value (Unix Epoch timestamp from 1/1/1980 till 12/31/2037), when NULL then any
8 - minimum duration value (amount of minutes from 0:00 till 999999:59), when NULL then any
param2
Type: integer
Possible values: depends on field_type
NULL values: allowed
Description: Allowed range of the field values — depends on field type as following:
1 - maximum integer value (from –1000000000 till +1000000000), when NULL then any
2 - NULL (not used)
3 - NULL (not used)
4 - NULL (not used)
5 - NULL (not used)
6 - NULL (not used)
7 - maximum date value (Unix Epoch timestamp from 1/1/1980 till 12/31/2037), when NULL then any
8 - maximum duration value (amount of minutes from 0:00 till 999999:59), when NULL then any
tbl_filter_sharing
The table contains information about sharing of created filters (see tbl_filters).
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of shared filter.
group_id
Type: integer
Possible values: foreign key to tbl_groups table
NULL values: none
Description: ID of group, which the filter is shared with.
tbl_filter_states
The table contains additional information about created filters (see tbl_filters). Every state, chosen in created filter, is stored in this table.
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of filter, which the information is related to.
state_id
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state.
tbl_filter_trans
The table contains information about timestamps, when filtering records were moved to specified state.
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of filter, which the information is related to.
state_id
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state, which filtering record was moved to.
date1
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Minimum date value of the timestamps range (always means 0:00 time of specified day).
date2
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Maximum date value of the timestamps range (always means 0:00 time of specified day).
tbl_fsets
The table contains information about filters sets.
fset_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Owner of filters set.
fset_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Name of filters set.
tbl_fset_filters
The table contains information about filters, which filters sets consist of.
vfset_id
Type: integer
Possible values: foreign key to tbl_fsets table
NULL values: none
Description: ID of filters set.
filter_id
Type: integer
Possible values: foreign key to tbl_filters table
NULL values: none
Description: ID of filter.
tbl_groups
The table contains information about registered groups. Any group is either local or global. Local groups exist in some project only, while global groups are system-wide.
group_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
project_id
Type: integer
Possible values: foreign key to tbl_projects table
NULL values: allowed
Description: NULL when groups is global; otherwise ID of project, this local group belongs to.
group_name
Type: string (unicode)
Possible values: up to 25 characters
NULL values: none
Description: Name of group.
description
Type: string (unicode)
Possible values: up to 100 characters
NULL values: allowed
Description: Optional description of group.
tbl_group_perms
The table contains information about template permissions for system groups.
group_id
Type: integer
Possible values: foreign key to tbl_groups table
NULL values: none
Description: ID of group.
template_id
Type: integer
Possible values: foreign key to tbl_templates table
NULL values: none
Description: ID of template.
perms
Type: integer
Possible values: set of binary flags
NULL values: none
Description: Permissions, which are granted to the group (for this template, hexadecimal):
00 00 00 01 - permission to create new records
00 00 00 02 - permission to modify records
00 00 00 04 - permission to postpone records
00 00 00 08 - permission to resume records
00 00 00 10 - permission to reassign records, which are already assigned on another person
00 00 00 20 - permission to change state of records, which are assigned on another person
00 00 00 40 - permission to add comments
00 00 00 80 - permission to add attachments
00 00 01 00 - permission to remove attachments
00 00 02 00 - permission to add and read confidential comments
00 00 04 00 - permission to send reminders
00 00 08 00 - permission to delete records from database
00 00 10 00 - permission to add subrecords
00 00 20 00 - permission to remove subrecords
40 00 00 00 - permission to read records
tbl_group_trans
The table contains information about allowed transitions between states for system groups.
state_id_from
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state, which record can be moved from.
state_id_to
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state, which record can be moved to.
group_id
Type: integer
Possible values: foreign key to tbl_groups table
NULL values: none
Description: ID of group, which this transition is allowed to.
tbl_list_values
The table contains information about possible items of the fields of list type (see field_type).
field_id
Type: integer
Possible values: foreign key to tbl_fields table
NULL values: none
Description: ID of field.
int_value
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Integer value of the item.
str_value
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Text value of the item.
tbl_membership
The table contains information about members of groups.
group_id
Type: integer
Possible values: foreign key to tbl_groups table
NULL values: none
Description: ID of group.
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: ID of account, which is member of this group.
tbl_projects
The table contains information about projects.
project_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
project_name
Type: string (unicode)
Possible values: up to 25 characters
NULL values: none
Description: Name of project.
start_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when the project has been registered.
description
Type: string (unicode)
Possible values: up to 100 characters
NULL values: allowed
Description: Optional description of project.
is_suspended
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether the project is suspended. When project is suspended, its records can be read only, and new records cannot be created.
tbl_reads
The table contains information about last read access for each account and record.
record_id
Type: integer
Possible values: foreign key to tbl_records table
NULL values: none
Description: ID of record, which was read.
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: ID of account, who read the record.
read_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when record has been read.
tbl_records
The table contains information about records.
record_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
state_id
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: Current state of the record.
subject
Type: string (unicode)
Possible values: up to 250 characters
NULL values: none
Description: Text of subject.
responsible_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: allowed
Description: Person, assigned on the record.
creator_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Author of the record.
creation_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when record has been created.
change_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp of last record's event.
closure_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: allowed
Description: Timestamp, when record has been moved to final state.
postpone_time
Type: integer
Possible values: Unix Epoch timestamp
NULL values: none
Description: Timestamp, when postponed record will be automatically resumed. If this timestamp is in past, then the record is not postponed.
tbl_record_subscribes
The table contains information about subscribes to particular records.
record_id
Type: integer
Possible values: foreign key to tbl_records table
NULL values: none
Description: ID of record, which the account is subscribed to.
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: ID of account, subscribed to the record.
subscribed_by
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Person, who subscribed this account to this record (same ID as account_id when person had subscribed himself).
tbl_reminders
The table contains information about email reminders.
reminder_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Owner of reminder.
reminder_name
Type: string (unicode)
Possible values: up to 25 characters
NULL values: none
Description: Name of reminder.
subject_text
Type: string (unicode)
Possible values: up to 100 characters
NULL values: allowed
Description: Text of email subject.
state_id
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: State of records, which email should remind about.
group_id
Type: integer
Possible values: foreign key to tbl_groups table
NULL values: allowed
Description: Group, which should be reminded (NULL, if group_flag is negative).
group_flag
Type: integer
Possible values:
0 - group_id contains ID of group
–1 - only authors of records
–2 - only current responsibles of records
NULL values: none
Description: Flag of group, which should be reminded.
tbl_role_trans
The table contains information about allowed transitions between states for system roles.
state_id_from
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state, which record can be moved from.
state_id_to
Type: integer
Possible values: foreign key to tbl_states table
NULL values: none
Description: ID of state, which record can be moved to.
role
Type: integer
Possible values:
–1 - author of record
–2 - current responsible of record
NULL values: none
Description: System role, which this transition is allowed to.
tbl_states
The table contains information about states.
state_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
template_id
Type: integer
Possible values: foreign key to tbl_templates table
NULL values: none
Description: ID of template.
state_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Name of state.
state_abbr
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Abbreviation of state (used in list of records as short alternative).
state_type
Type: integer
Possible values:
1 - initial
2 - intermediate
3 - final
NULL values: none
Description: There are three types of states — initial, intermediate, and final. Record is opened while its state is initial or intermediate type of. When record's state is changed to final one, the record becomes closed. You can change state of record only while the record is opened. All closed records are displayed in grey in the list of records. Only and exactly one state of same template can be initial — this is a first state of newly created records.
next_state_id
Type: integer
Possible values: foreign key to tbl_states table
NULL values: allowed
Description: ID of state, that is next by default.
responsible
Type: integer
Possible values:
1 - remain unchanged
2 - assign
3 - remove
NULL values: none
Description: Some states mean that record must be assigned on some person, which is responsible for this record in its current state. Assign means that record must be assigned on somebody when it's being moved to this state. Remove means that record must become unassigned when it's being moved to this state. Remain unchanged means that current assignment of record must not be changed when record is being moved to this state (unassigned record will remain unassigned).
tbl_string_values
The table contains all string values, used in fields of string type (see field_type).
value_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
value_token
Type: string (ASCII)
Possible values: exactly 32 characters
NULL values: none
Description: MD5 hash of value (to search for, and to avoid duplicates).
string_value
Type: string (unicode)
Possible values: up to 250 characters
NULL values: none
Description: String value.
tbl_subscribes
The table contains information about subscriptions to notifications.
subscribe_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Owner of subscription.
subscribe_name
Type: string (unicode)
Possible values: up to 25 characters
NULL values: none
Description: Name of subscription.
carbon_copy
Type: string (ASCII)
Possible values: up to 50 characters
NULL values: allowed
Description: Optional email address, where notifications should be copied.
subscribe_type
Type: integer
Possible values:
1 - all projects
2 - all templates of specified project
3 - specified template
NULL values: none
Description: Type of subscription (subscription scope).
subscribe_flags
Type: integer
Possible values: set of binary flags
NULL values: none
Description: Types of events, which this subscription should notify about (hexadecimal):
00 00 00 01 - notify when record is created
00 00 00 02 - notify when record is assigned
00 00 00 04 - notify when record is modified
00 00 00 08 - notify when state is changed
00 00 00 10 - notify when record is postponed
00 00 00 20 - notify when record is resumed
00 00 00 40 - notify when comment is added
00 00 00 80 - notify when file is attached
00 00 01 00 - notify when file is removed
00 00 02 00 - notify when record is cloned
00 00 04 00 - notify when subrecord is added
00 00 08 00 - notify when subrecord is removed
subscribe_param
Type: integer
Possible values: depends on subscribe_type
NULL values: allowed
Description: Parameter of subscription — depends on subscription type as following:
1 - NULL (not used)
2 - ID of project
3 - ID of template
is_activated
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether subscription is enabled or disabled.
tbl_sys_vars
The table contains predefined system variables, which are for internal use only and are never changed by user.
var_name
Type: string (ASCII)
Possible values: up to 32 characters
NULL values: none
Description: Name of the variable (e.g. 'DATABASE_TYPE').
var_value
Type: string (ASCII)
Possible values: up to 100 characters
NULL values: allowed
Description: Value of the variable (e.g. 'MySQL 5.0').
tbl_templates
The table contains information about templates.
template_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
project_id
Type: integer
Possible values: foreign key to tbl_projects table
NULL values: none
Description: ID of project, this template belongs to.
template_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Name of template.
template_prefix
Type: string (unicode)
Possible values: up to 3 characters
NULL values: none
Description: Prefix of template (used as prefix in ID of records, created with this template).
critical_age
Type: integer
Possible values: from 1 to 100
NULL values: allowed
Description: Critical age is an optional integer value which means amount of days. When record remains opened more than this amount of days it is displayed in red in the list of records. If this value is not specified, record will never been "expired".
frozen_time
Type: integer
Possible values: from 1 till 100
NULL values: allowed
Description: Frozen time is an optional integer value which means amount of days. When record is closed you cannot change its state anymore, but you still can modify its fields, add comments and attach files. If frozen time is specified it will be allowed to modify record this amount of days after its closure. After that record will become read only. If this attribute is not specified, record will never become read only.
description
Type: string (unicode)
Possible values: up to 100 characters
NULL values: allowed
Description: Optional description of template.
is_locked
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether the template is locked for edition. When template is locked, it can be modified, but its records can be read only, and new records cannot be created with this template. If template is unlocked, it can be used to create records, but cannot be changed.
guest_access
Type: boolean
Possible values:
0 - FALSE
1 - TRUE
NULL values: none
Description: Whether a record, created with this template, is accessible for non-authenticated user.
registered_perm
Type: integer
Possible values: set of binary flags
NULL values: none
Description: Permissions, which are granted to any authenticated (logged in) user (hexadecimal):
00 00 00 01 - permission to create new records
00 00 00 02 - permission to modify records
00 00 00 04 - permission to postpone records
00 00 00 08 - permission to resume records
00 00 00 10 - permission to reassign records, which are already assigned on another person
00 00 00 20 - permission to change state of records, which are assigned on another person
00 00 00 40 - permission to add comments
00 00 00 80 - permission to add attachments
00 00 01 00 - permission to remove attachments
00 00 02 00 - permission to add and read confidential comments
00 00 04 00 - permission to send reminders
00 00 08 00 - permission to delete records from database
00 00 10 00 - permission to add subrecords
00 00 20 00 - permission to remove subrecords
40 00 00 00 - permission to read records
author_perm
Type: integer
Possible values: set of binary flags
NULL values: none
Description: Permissions, which are granted to author of record, created with this template (same as for registered_perm).
responsible_perm
Type: integer
Possible values: set of binary flags
NULL values: none
Description: Permissions, which are granted to current responsible of record, created with this template (same as for registered_perm).
tbl_text_values
The table contains all multilined text values, used in fields of text type (see field_type).
value_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
value_token
Type: string (ASCII)
Possible values: exactly 32 characters
NULL values: none
Description: MD5 hash of value (to search for, and to avoid duplicates).
text_value
Type: string (unicode)
Possible values: up to 4000 characters
NULL values: none
Description: Text value.
tbl_views
The table contains information about stored custom views.
view_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
account_id
Type: integer
Possible values: foreign key to tbl_accounts table
NULL values: none
Description: Owner of the view.
view_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: none
Description: Name of the view.
tbl_view_columns
The table contains information about set of columns of each view.
column_id
Type: integer
Possible values: from 1 to infinity
NULL values: none
Description: Unique ID (primary key).
view_id
Type: integer
Possible values: foreign key to tbl_views table
NULL values: none
Description: ID of view.
state_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: allowed
Description: Name of state, which owns a field this column is linked to.
field_name
Type: string (unicode)
Possible values: up to 50 characters
NULL values: allowed
Description: Name of field, which this column is linked to.
column_type
Type: integer
Possible values:
1 - standard column "ID"
2 - standard column "State"
3 - standard column "Project"
4 - standard column "Subject"
5 - standard column "Author"
6 - standard column "Responsible"
7 - standard column "L/E"
8 - standard column "Age"
9 - standard column "Created"
100 - custom column of "number" type
101 - custom column of "string" type
102 - custom column of "multilined text" type
103 - custom column of "check box" type
104 - custom column of "list" type (show integer values)
105 - custom column of "list" type (show text values)
106 - custom column of "record" type
107 - custom column of "date" type
108 - custom column of "duration" type
NULL values: none
Description: Type of column.
column_order
Type: integer
Possible values: from 1 till amount of columns of the same view
NULL values: none
Description: Each column must have its own ordinal number. No duplicates of this number among columns of the same view are allowed.
Last update: 2009-09-29