All of the SQL-based versions of FootPrints use the Perl DBI/DBD modules to access the database. Windows versions no longer use fpService.exe for database access. This means that all database connectivity is done directly within each Perl script without having to call any external programs as was done in earlier (pre 6.5) Windows versions of FootPrints. The database design was always the same on Windows and Unix/Linux versions, but now the method of accessing the data is the same on both as well. A single user is created in the DBMS which FootPrints uses for all connections.
|
Column Name |
Datatype |
Comments |
|---|---|---|
|
mrID |
int |
Unique incremental ticket # |
|
mrREF_TO_AB |
varchar |
Either an ABMASTERy.abID that created this ticket, OR an LDAP dn |
|
mrREF_TO_MR |
varchar |
A MASTERx.mrID that this ticket is possibly "linked" to - used for subtasks, global issues, etc. |
|
mrTITLE |
varchar |
Ticket summary / title |
|
mrPRIORITY |
int |
Ticket Priority, 1..100 |
|
mrSTATUS |
char |
Status of Ticket |
|
mrDESCRIPTION |
longvarchar |
The text of the most current description, starts with a date/time/user stamp |
|
mrASSIGNEES |
varchar |
Assignees (FootPrints usernames) of ticket, separated by spaces |
|
mrATTACHMENTS |
varchar |
File attachment filenames separated by new lines |
|
mrUPDATEDATE |
timestamp |
Last edit date |
|
mrSUBMITTER |
char |
Original creator of ticket |
|
mrSUBMITDATE |
timestamp |
Original creation date of ticket |
|
mrPOPULARITY |
int |
For solutions only - number of votes that it was helpful |
|
mrURGENT |
int |
0 or 1 denoting if this ticket is urgent |
|
mrESCALATEDBY |
varchar |
This column is used for certain escalations that are only allowed to act once, ever, on an issue. If one of these escalations run, the unique escalation ID is added to this field. It is possible for this column to not exist, it is added only when it is needed. |
|
mrALLDESCRIPTIONS |
longvarchar |
This column is added after upgrading to FootPrints 6.5+ and running MR60to65.pl. It is a single field which contains the current description and all of the appended descriptions. It is never displayed, and exists only to be searched on. Searching on this field allows FootPrints to avoid joining the MASTERx and MASTERx_DESCRIPTIONS tables when a search on all descriptions is needed. This can improve performance on large databases where table joins are very expensive. |
|
mrREF_TO_MRX |
varchar |
This contains overflow from mrREF_TO_MR if the data cannot all fit in the one column. It is possible for this column to not exist, it is added only when it is needed. It is a varchar(4000) on Oracle and Postgres, a clob(4000) on DB2, mediumtext on MySQL, and varchar(254) on everything else. |
|
mrTIMESTAMP |
timestamp |
Microsoft SQL Server only, this column improves the performance of full text indexing. |
|
mrUNASSIGNED |
int |
If this column is used, searches on unassigned can be done faster. This is set to 1 if the issue is not assigned to anyone. |
|
custom project fields |
n/a |
Named after actual field name, with correct data type. |
|
Column Name |
Datatype |
Comments |
|---|---|---|
|
mrID |
int |
The MASTERx.mrID that this pertains to |
|
mrGENERATION |
int |
Generation of this mrHISTORY |
|
mrHISTORY |
varchar |
timestamp user comment |
|
mrCOMMENT |
varchar |
User comment |
|
Column Name |
Datatype |
Comments |
|---|---|---|
|
mrID |
int |
The MASTERx.mrID that this pertains to |
|
mrGENERATION |
int |
Generation of this description: Range (1..max) where max is the most current of this set. MASTERx.mrDESCRIPTION is always the most current description. |
|
mrDESCRIPTION |
longvarchar |
The text of the description, starts with a date/time/user stamp |
|
Column Name |
Datatype |
Comments |
|---|---|---|
|
mrID |
int |
The MASTERx.mrID that this pertains to |
|
Last__bName |
varchar |
Last name of a contact |
|
First__bName |
varchar |
First name of a contact |
|
Email__bAddress |
varchar |
Email address of a contact |
|
custom address book fields |
n/a |
The above first three custom fields are auto-created on table creation. The rest of the address book fields will follow, named after the actual field name, with the correct data type. |
|
Column Name |
Datatype |
Comments |
|---|---|---|
|
mrID |
int |
The MASTERx.mrID that this pertains to |
|
mrGENERATION |
int |
The time-tracking record number, ie; mrID 5 might have 23 time tracking records each with a unique RECNO for mrID 5.. |
|
mrTIMESPENT |
varchar |
Time spent in minutes, stored as 120m for 2 hrs. |
|
mrRATE |
decimal |
The money per hour for this time tracking record |
|
mrTIMEDATE |
datetime |
Time and Date stamp |
|
mrTIMEUSER |
varchar |
User stamp |
|
mrRATEDESC |
varchar |
Time-tracking rate description |
The following set of MASTERx_APPROVAL tables only exist if Change Management is being used for the given project x.
Column Name | Datatype | Comments |
|---|---|---|
mrID | int | The MASTERx.mrID that this pertains to |
mrPROCID | int | Approval Process ID |
mrPHASEID | int | Approval Phase ID |
mrPHASESTATE | varchar | Approved|Disapproved|Processing |
mrSTATETIMEDATE | datetime | Date and time this row was set or updated |
mrALERTTIMEDATE | datetime | Date and time of last recurring email alert |
Column Name | Datatype | Comments |
|---|---|---|
mrID | int | The MASTERx.mrID that this pertains to |
mrPROCID | int | Approval Process ID |
mrPHASEID | int | Approval Phase ID |
mrVOTEVALUE | varchar | Approve|Disapprove |
mrVOTEUSER | varchar | UserID of user who cast vote |
mrVOTECOMMENT | longvarchar | Comment about this vote |
mrVOTETIMEDATE | datetime | Date and time of vote |
Column Name | Datatype | Comments |
|---|---|---|
mrPROCID | int | Approval Process ID |
mrORDER | int | Order of Process in list |
mrPROCNAME | varchar | Process name |
mrPROCDESC | longvarchar | Process description |
mrPROCTRIGGER | longvarchar | CGI input for search criteria to trigger start of process |
mrPROCOPTIONS | text | Delimited process options |
mrDELETED | int | If process is deleted, this has a value of 1 |
Column Name | Datatype | Comments |
|---|---|---|
mrPROCID | int | Approval Process ID that this phase belongs to |
mrPHASEID | int | Approval Phase ID |
mrORDER | int | Order of Phase in list |
mrPHASENAME | varchar | Phase name |
mrPHASEDESC | longvarchar | Phase description |
mrPHASEOPTIONS | text | Delimited phase options |
mrDELETED | int | If process is deleted, this has a value of 1 |
mrVOTEUSERS | varchar | Space-separated list of eligible voters |
The following are the tables for the address book:
Column Name | Datatype | Comments |
|---|---|---|
abID | int | Unique ID for this contact |
abSUBMITTER | char | Person who created contact |
abASSIGNEE | char | Users assigned to this contact |
abSUBMITDATE | timestamp | When contact was created |
abUPDATEDATE | timestamp | When contact was last modified |
abSTATUS | char | Deleted or not (status) of contact |
Last__bName | varchar | Last name of a contact |
First__bName | varchar | First name of a contact |
Email__bAddress | varchar | Email address of a contact |
custom address book fields | n/a | The above first three custom fields are auto-created on table creation. The rest of the address book fields will follow, named after the actual field name, with the correct data type. Note: Any projects which use this address book must keep the column names of custom address book fields in their MASTERx_ABDATA in sync with this. |
Column Name | Datatype | Comments |
|---|---|---|
Last__bName | varchar | Last name of a contact |
First__bName | varchar | First name of a contact |
Email__bAddress | varchar | Email address of a contact |
custom address book fields | n/a | The above first three custom fields are auto-created on table creation. The rest of the address book fields will follow, named after the actual field name, with the correct data type. Note: Any projects which use this address book must keep the column names of custom address book fields in their MASTERx_ABDATA in sync with this. |
NOTE
On SQL Server only, you may see a column for mrTIMESTAMP. This is for non-displayed binary data to improve performance for full-text indexing and should be of no concern to administrators.
Index | Type | Comments |
|---|---|---|
Pk__random | Primary Key | mrID is the primary key of the MASTERx table and automatically an index. |
Pk__random | Primary Key | abID is the primary key of the ABMASTERx table and automatically an index. |
Pk__random | Primary Key | mrID is the primary key of the MASTERx_ABDATA table and automatically an index. |
MASTERx_STATUS_IDX | Index | Index on mrSTATUS in MASTERx table. |
MASTERx_PRI_IDX | Index | Index on mrPRIORITY in MASTERx table. |
MASTERx_URGENT_IDX | Index | Index on mrURGENT in MASTERx table. |
MASTERx_HISTORY_IDX | Composite Index | Index on (mrID, mrGENERATION) in MASTERx_HISTORY table. |
MASTERx_DESCRIPTIONS_IDX | Composite Index | Index on (mrID, mrGENERATION) in MASTERx_DESCRIPTION table. |
MASTERx_TIME_IDX | Composite Index | Index on (mrID, mrGENERATION) in MASTERx_DESCRIPTION table. |
Full Text Indexing (optional) | FULL TEXT | Full text indexing is implemented on MySQL, Microsoft SQL Server, and Oracle if Intermedia is enabled. In these situations, we can add a full text index on the mrALLDESCRIPTIONS column and use MATCH or CONTAINS operators for faster searching. |
MASTERx_APPROVALSTATES_IDX1 | Composite Index | Index on (MRID, MRPROCID, MRPHASEID) in MASTERx_APPROVALSTATES table. |
MASTERx_APPROVALSTATES_IDX2 | Index | Index on mrPHASESTATE in MASTERx_APPROVALSTATES table. |
MASTERx_APPROVALVOTES_IDX1 | Composite Index | Index on (MRID, MRPROCID, MRPHASEID) in MASTERx_APPROVALVOTES table. |
MASTERx_APPROVALVOTES_IDX2 | Index | Index on mrVOTEUSER in MASTERx_APPROVALVOTES table. |
MASTERx_APPROVALVOTESH_IDX1 | Composite Index | Index on (MRID, MRPROCID, MRPHASEID) in MASTERx_APPROVALVOTESHISTORY table. |
MASTERx_APPROVALVOTESH_IDX2 | Index | Index on mrVOTEUSER in MASTERx_APPROVALVOTESHISTORY table. |
MASTERx_APPROVALPROCS_IDX1 | Index | Index on mrPROCID in MASTERx_APPROVALPROCS table. |
MASTERx_APPROVALPHASES_IDX1 | Composite Index | Index on (MRPROCID, MRPHASEID) in MASTERx_APPROVALPHASES table. |
Configuration Management and FootPrints Calendar and Field History database tables are described at the following URL: