|
|
QPR Modeling Database Tables |
|
|
|
||
QPR Modeling Database Tables |
|
|
|
|
|
|
QPR Modeling Database Tables |
|
|
|
||
QPR Modeling Database Tables |
|
|
|
|
|
|
||
This chapter describes the detailed structure of QPR Modeling database. As database is implemented to several different relational database management systems (RDBMSs), the approach in database structure is to generalize model elements and store all data into three tables that include the following data.
| 1. | Element data, basic element attributes and instance data. |
| 2. | Graphical presentation of element. |
| 3. | Element type–specific attribute data including also relations and hierarchies (database allows hierarchies to be used with all element types). |
Database has separate tables that describe each element type and what data is saved with it. Also graphical presentation is described separately.
Tables
Tables of the database are described briefly in the table below. Some database tables store QPR Modeling-specific data (graphical presentation) in binary format. Also measure data is stored in binary format because of performance. If that data is needed, it can be retrieved by opening model in QPR Modeling Client and using measure data export.
Table |
Description |
|---|---|
PG_ATTRIBUTE |
Has one row for each attribute of a model element. |
PG_ATTRIBUTE_2 |
Used to store text and blob attributes. |
PG_ATTRIBUTE_TYPE |
Stores all the possible attribute types separately for each model. |
PG_ELEMENT |
Has one row for each model element and separate rows for each instance i.e. at least two rows for each instantiated element (but only one for elements without instances). |
PG_ELEM_GRAPH_PROP |
Stores graphical presentation of each model element instance. |
PG_ELEMENT_TYPE |
Has one row for each model element type (defined in Modeling Options dialog). |
PG_ELEMENT_USER |
Stores relation between user and resources/organization units for portal usage. |
PG_ELEMTY_GR_PROP |
Stores graphical presentation of each model element type. |
PG_FOLDER |
Has one row for each folder (used in Open/Save As dialogs like folders in storing files), root model, and branch. |
PG_MODEL |
Has one row for each model version. |
PG_PARAM_DATA |
This table can be used to store different QPR Modeling parameters. |
PG_VIEW |
Stores views (currently only named analysis views). |
PG_RESERVATION |
Stores reservations for checked out models (each time user checks out a model for editing a row is added). |
PG_USER_MODEL |
Stores one row for each model to which a QPR Modeling Client user has some rights. Stores also the state of the views separately for each user. |
PG_USER_FOLDER_RIGHT |
Stores user rights to different folders. |
PG_USER_RIGHT |
Stores user rights to different models. |
PG_TYPE_ATTRIBUTE |
Stores multilingual texts and other properties for element and attribute types. |
Database tables
Database Table Structure
Fields marked with (*) are key fields.
PG_ATTRIBUTE
Field Name |
Field Type |
Description |
|---|---|---|
ATT_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
ATT_ID |
NUMBER NOT NULL (*) |
Attribute's id |
ATT_ELM_ID |
NUMBER NOT NULL (*) |
Element's id |
ATT_ELM_IND |
NUMBER NOT NULL (*) |
Element's index |
ATT_ATY_ID |
NUMBER NOT NULL (*) |
Attribute type's id |
ATT_ATY_MOD_ID |
NUMBER NOT NULL |
Attribute type's model id |
ATT_PARENT_ATT_ID |
NUMBER NOT NULL (*) |
Parent attribute's id |
ATT_ORD |
NUMBER NOT NULL |
Order number |
ATT_VAL_INT |
NUMBER |
Integer attribute's value |
ATT_VAL_FLOAT |
FLOAT |
Floating point attribute's value |
ATT_VAL_DATETIME |
DATE |
Date/time attribute's value |
ATT_REL_MOD_ID |
NUMBER |
Relation attribute's target model id |
ATT_REL_ELM_ID |
NUMBER |
Relation attribute's target element id |
ATT_REL_ELM_IND |
NUMBER |
Relation attribute's target element index |
PG_ATTRIBUTE_2
Field Name |
Field Type |
Description |
|---|---|---|
AT2_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
AT2_ID |
NUMBER NOT NULL (*) |
Attribute's id |
AT2_ELM_ID |
NUMBER NOT NULL (*) |
Element's id |
AT2_ELM_IND |
NUMBER NOT NULL (*) |
Element's index |
AT2_ATY_ID |
NUMBER NOT NULL (*) |
Attribute type's id |
AT2_ATY_MOD_ID |
NUMBER NOT NULL |
Attribute type's model id |
AT2_PARENT_ATT_ID |
NUMBER NOT NULL (*) |
Parent attribute's id |
AT2_IND |
NUMBER NOT NULL (*) |
Attribute's index |
AT2_ORD |
NUMBER NOT NULL |
Order number |
AT2_VAL_INT |
NUMBER |
Number attribute's value |
AT2_VAL_TEXT |
CHAR(255) |
Text attribute's value |
AT2_VAL_MEMO |
Text |
Memo attribute's value |
AT2_VAL_BLOB |
LONGRAW |
Blob attribute's value |
PG_ATTRIBUTE_TYPE
Field Name |
Field Type |
Description |
|---|---|---|
ATY_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
ATY_ID |
NUMBER NOT NULL (*) |
Attribute type's id |
ATY_DATATYPE |
NUMBER NOT NULL |
Attribute’s data type |
ATY_FLAGS |
NUMBER NOT NULL |
Internal boolean setting |
ATY_CARDINALITY |
NUMBER NOT NULL |
Cardinality (1 or n) |
ATY_ORDER |
NUMBER NOT NULL |
Order number |
ATY_ATY_ID |
NUMBER |
Reference to another attribute |
ATY_ATY_MOD_ID |
NUMBER |
Reference to another attribute |
ATY_ETY_ID |
NUMBER |
Reference to element type |
ATY_ETY_MOD_ID |
NUMBER |
Reference to element type |
ATY_LKD_ATY_ID |
NUMBER |
Reference to linked attribute type |
ATY_LKD_ATY_MOD_ID |
NUMBER |
Reference to linked attribute type |
ATY_REL_ETY |
NUMBER |
Generic element type of the target element of a relation attribute |
ATY_REL_ETY_ID |
NUMBER |
Detailed element type of the target element of a relation attribute. 0 for generic element types. |
ATY_REL_ETY_MOD_ID |
NUMBER |
Model id of the generic element type of the target element of a relation attribute |
ATY_2WAY_ATY_ID |
NUMBER |
Two-way relation attribute type's id |
ATY_2WAY_ATY_MOD_ID |
NUMBER |
Two-way relation attribute type's model id |
PG_ELEMENT
Field Name |
Field Type |
Description |
|---|---|---|
ELM_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
ELM_ID |
NUMBER NOT NULL (*) |
Element's id |
ELM_IND |
NUMBER NOT NULL (*) |
Element's index |
ELM_ETY_ID |
NUMBER NOT NULL |
Element type's id |
ELM_ETY_MOD_ID |
NUMBER NOT NULL |
Element type's model id |
ELM_PAR_MOD_ID |
NUMBER |
Parent element's model id |
ELM_PAR_ID |
NUMBER |
Parent element's id |
ELM_PAR_IND |
NUMBER |
Parent element's index |
ELM_MOD_DATETIME |
DATE |
Element's last modification time |
ELM_USE_ID |
NUMBER |
User id of the user who last modified the element |
ELM_TEXT1 |
Text |
Text field for element data |
ELM_TEXT2 |
CHAR(255) |
Text field for element data |
ELM_TEXT3 |
CHAR(255) |
Text field for element data |
PG_ELEM_GRAPH_PROP
Field Name |
Field Type |
Description |
|---|---|---|
EGR_MOD_ID |
NUMBER NOT NULL (*) |
Model id |
EGR_ELM_ID |
NUMBER NOT NULL (*) |
Element id |
EGR_ELM_IND |
NUMBER NOT NULL (*) |
Element index |
EGR_ELM_MOD_ID |
NUMBER NOT NULL (*) |
Element's model id |
EGR_DATA_IND |
NUMBER NOT NULL (*) |
Data index |
EGR_DATA |
LONGRAW |
Graphical element's data |
PG_ELEMENT_USER
Field Name |
Field Type |
Description |
|---|---|---|
ELU_ELM_MOD_ID |
NUMBER NOT NULL (*) |
Element's model id |
ELU_ELM_ID |
NUMBER NOT NULL (*) |
Element's id |
ELU_ELM_IND |
NUMBER NOT NULL (*) |
Element's index |
ELU_USE_ID |
NUMBER NOT NULL (*) |
User's id |
PG_ELEMENT_TYPE
Field Name |
Field Type |
Description |
|---|---|---|
ETY_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
ETY_ID |
NUMBER NOT NULL (*) |
Element type's id |
ETY_ALLOW_INST |
NUMBER |
1 if element can have instances, 0 otherwise |
ETY_ELEM_TYPE |
NUMBER |
Element's subtype |
PG_ELEMTY_GR_PROP
Field Name |
Field Type |
Description |
|---|---|---|
TGR_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
TGR_ETY_ID |
NUMBER NOT NULL (*) |
Element type's id |
TGR_DATA_IND |
NUMBER NOT NULL (*) |
Data index |
TGR_DATA |
LONGRAW |
Element type's graphical data |
PG_FOLDER
Field Name |
Field Type |
Description |
|---|---|---|
FOL_ID |
NUMBER NOT NULL (*) |
Folder's id |
FOL_FOL_ID |
NUMBER |
Parent folder's id |
FOL_NAME |
CHAR(128) |
Folder's name |
FOL_TYPE |
NUMBER NOT NULL |
Folder's type |
FOL_FLAGS |
NUMBER NOT NULL |
Folder's flags |
PG_MODEL
Field Name |
Field Type |
Description |
|---|---|---|
MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
MOD_FOL_ID |
NUMBER |
Folder's id |
MOD_IS_TEMPLATE |
NUMBER NOT NULL |
0 if this is a model, 1 if this is a template, 2 if this is a public template |
MOD_FORCE_EXCL |
NUMBER |
Defines if the "Force exclusive" option is set in modeling options. |
MOD_IS_PUBLISHED |
NUMBER NOT NULL |
1 if the model is published, 0 otherwise |
MOD_USE_LOG |
NUMBER |
1 if log is used, 0 otherwise |
MOD_CREATION_DATE |
DATE |
Model's creation date/time |
MOD_MOD_DATE |
DATE |
Model's last save time |
MOD_DBNAME |
CHAR(128) NOT NULL |
Model's name |
MOD_LOG_DATA |
LONGRAW |
Log data |
MOD_BASE_MOD_ID |
NUMBER |
Base model's id |
MOD_IS_BASE_MODEL |
NUMBER NOT NULL |
1 if this is a base model, 0 otherwise |
MOD_BM_MOD_OPTIONS |
NUMBER NOT NULL |
1 if element types are used from base model, 2 if they are used as read-only |
MOD_BM_RESOURCES |
NUMBER NOT NULL |
0 if resources from base model are not used, 1 if they are used, 2 if they are used as read-only |
MOD_BM_INFO_ITEMS |
NUMBER NOT NULL |
0 if information items from base model are not used, 1 if they are used, 2 if they are used as read-only |
MOD_BM_ORG_ITEMS |
NUMBER NOT NULL |
0 if organization items from base model are not used, 1 if they are used, 2 if they are used as read-only |
MOD_BM_STORES |
NUMBER NOT NULL |
0 if stores from base model are not used, 1 if they are used, 2 if they are used as read-only |
MOD_BM_LANGUAGES |
NUMBER NOT NULL |
0 if base model's language settings are not used, 1 if base model's language settings are used, 2 if the use of base model's language settings is forced. |
MOD_BM_ELEMENTS |
BLOB |
Stores base model settings of custom element types. |
MOD_SAVE_COUNTER |
Model version id, incremented by one every time a model is saved |
|
MOD_EXCL_SAVE_COUNTER |
MOD_SAVE_COUNTER value when the model was last saved in exclusive mode |
|
MOD_LOCKING |
Defines if the model is locked (can be opened only in read-only mode) |
|
MOD_INHERIT_PLR_FROM_FR |
Inherit diagram rights from folder rights |
|
MOD_VERSION_ID |
NUMBER NOT NULL |
Model version unique id |
MOD_VERSION_ORDER_ID |
NUMBER NOT NULL |
Model revision number |
MOD_DATA |
BLOB NOT NULL |
|
MOD_SAVED_BY_ID |
N |
|
MOD_SAVED_BY_NAME |
VARCHAR(300) |
PG_PARAM_DATA
Field Name |
Field Type |
Description |
|---|---|---|
PAR_ENTRY |
CHAR(32) NOT NULL |
Parameter entry |
PAR_VALUE_INT |
NUMBER |
Number parameter's value |
PAR_VALUE_DT |
DATE |
Date/time parameter's value |
PAR_VALUE |
LONGRAW |
Other values |
PG_VIEW
Field Name |
Field Type |
Description |
|---|---|---|
PGV_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
PGV_VIEW_ID |
NUMBER NOT NULL (*) |
View's id |
PGV_ENT_TYPE |
NUMBER NOT NULL |
View's type (1 for analysis view) |
PGV_NAME |
CHAR(128) NOT NULL |
View's name |
PGV_DESCRIPTION |
VARCHAR(2000) |
View's description |
PGV_DATA_IND |
NUMBER NOT NULL (*) |
Data index |
PGV_DATA |
LONGRAW |
View's data |
PG_RESERVATION
Field Name |
Field Type |
Description |
|---|---|---|
RES_ID |
NUMBER NOT NULL (*) |
Reservation's id |
RES_MOD_ID |
NUMBER NOT NULL (*) |
Reserved model's id |
RES_USE_ID |
NUMBER NOT NULL (*) |
Reserving user's id |
RES_TYPE |
NUMBER NOT NULL |
Reservation type, 3 for exclusive check-out, 4 for non-exclusive check-out. |
RES_TIME |
DATE NOT NULL |
Reservation time |
RES_ID_SPACE |
NUMBER NOT NULL |
Id space for new model elements created during reservation |
PG_USER_FOLDER_RIGHT
Field Name |
Field Type |
Description |
|---|---|---|
USF_FOLD_ID |
NUMBER NOT NULL (*) |
Folder id |
USF_USE_ID |
NUMBER NOT NULL (*) |
User id |
USF_ACCESS_LEVEL |
NUMBER NOT NULL |
User's access level to folder |
PG_USER_MODEL
Field Name |
Field Type |
Description |
|---|---|---|
USM_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
USM_USE_ID |
NUMBER NOT NULL (*) |
User's id |
USM_ALLOW_RES |
NUMBER |
User's rights to resources, 0 for no rights, 2 for full rights |
USM_ALLOW_MEA |
NUMBER |
User's rights to measures, 0 for no rights, 2 for full rights |
USM_ALLOW_SIM |
NUMBER |
User's rights to simulation, 0 for no rights, 2 for full rights |
USM_OPEN_STATE |
LONGRAW |
Saved desktop state |
USM_MODEL_ADMIN |
NUMBER |
Model administrator flag |
PG_USER_RIGHT
Field Name |
Field Type |
Description |
|---|---|---|
USR_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
USR_ELM_ID |
NUMBER NOT NULL (*) |
Element's id |
USR_ELM_IND |
NUMBER NOT NULL (*) |
Element's index |
USR_USE_ID |
NUMBER NOT NULL (*) |
User's id |
USR_IS_FULL |
NUMBER |
User rights, 0 for no rights, 1 for view only rights, 2 for full rights |
PG_TYPE_ATTRIBUTE
Field Name |
Field Type |
Description |
|---|---|---|
TYA_MOD_ID |
NUMBER NOT NULL (*) |
Model's id |
TYA_ETY_ID |
NUMBER NOT NULL (*) |
Element type's id |
TYA_ATY_ID |
NUMBER NOT NULL (*) |
If this is 0, the row contains the name or a description of an element type. With other values the row contains the name or a description of an attribute type. |
TYA_VAL_IND |
NUMBER NOT NULL (*) |
Defines the type of data contained in the row. |
TYA_VAL_INT |
NUMBER NOT NULL (*) |
Id of the language. |
TYA_VAL_FLAGS |
NUMBER NOT NULL |
Not currently in use. |
TYA_VAL_MEMO |
Text |
Name, description or element type's symbol. |
TYA_VAL_BLOB |
LONGRAW |
Binary data |
Relationships
Relations between database tables (foreign key references) are described in the figure below.
Relations between tables (foreign key references are shown with arrows).
Note: User information is stored in a separate database used by QPR User Management System, referential integrity does not exist between it and PG database.
The following relations are not mandatory:
•Model -> Model
•Model -> Folder
•Folder -> Folder
•Attribute -> Element (When relation to another element is described.)
•Attribute Type -> Element Type (When possible relation to specific element type is described.)
With all relations the cascade deletion is not allowed.