IDRS Data Model
There are two types of IDRS databases: secure and insecure. The secure database requires three tables: tblDoc, tblGroups, tblUser. The insecure version only requires the tblDoc table. It is recommended that insecure document be stored in a completely separate database from secure databases. If they are stored in the same database that anyone can use the insecure IDRS to access sensitive information. The tblDoc table used in the secure data model is the exact same in the insecure model minus the DocGroupsAllowed field.Below are SQL3 statements that will build the tables needed for most SQL3 databases. NOTE: string lengths are arbitrary. The DBA may chose any lengths for the string based on system capacity and need.

 
 

CREATE TABLE tblDoc (
DocID int,
DocName varchar(20),
DocSrc text,
DocGroups varchar(50),
DocVarSrc text,
isFile boolean,
DocParams text,
DocConns varchar(30)
);

This statement creates a usable tblDoc table. The individual fields are explained below:

DocID: Unique. Identifies the RML document.

DocName: Unique. Identifies the RML document

DocSrc: either the source of the RML document or a path to the source.

DocGroupsAllowed: GroupID's separated and terminated by commas.

Example: 2,3,6,9,11,

DocVarSrc: HTML source for a form that will take in all of the required parameters needed by the RML document. All form fields should fallow the pattern of DBID_VarName.

isFile: Identifies whether or not DocSrc is a file or actuall source

DocParams: The names of the form fields as defined in DocVarSrc in the order they appear in the RML source. NOTE: When using a UserID type in an RML document, there must be an entry in this field to fill the spot of that variable in the form of DBID_UserID. All entries are separated by collons and terminated by collons.

Example:

dbHeader_UserID:dbHeader_Name:dbHeader_Addr:dbBody_UserID:dbBody_Item:dbFooter_UserID:

DocConns : Connection Strings used by the report for connection pooling.  Should be seperated and terminated by commas

Example:

jdbc:odbc:Reports,jdbc:postgresql:inventory,
CREATE TABLE tblGroups (

GroupID int,
GroupName varchar(20)
);

GroupID: Unique. Used to identify the group

GroupName: Unique. Used to identify the group

CREATE TABLE tblUser(
UserID int,
Username varchar(20),
Password varchar(10),
GroupID varchar(50)
);

UserID: Unique. Used to identify the user.

Username: Unique. Used to identify the user.

Password: Used to verify the user.

UserGroup: GroupID's separated by and terminated by commas.