Storage Management: Database Schema
Exago’s Storage Management implementation defines several tables: Content, Party Type, Content Access and StorageMeta. At a very high level, the basic relationship between these tables is depicted by the following diagram:
Storage Management table relationship
Database Requirements
Permissions
Exago must be able to INSERT, UPDATE, SELECT and DELETE on the Storage Management database. If Exago is used to initialize the database (either through the Admin Console or one of the transitioning utilities), Exago must also be able to CREATE TABLE.
MySQL
If using MySQL for the Storage Management database, increase MySQL’s max_allowed_packet parameter to greater than the largest report or template file before using the transitioning utilities.
Symbols Used in this Article
- 🔑 indicates a primary key
- 🏷️ indicates a foreign key
Note
The current Storage Management data schema version is 1.1.
Content Table
Each report, folder, theme or template is represented by a row in the Content table. A GUID is used as a unique identifier throughout the database and application to reference the content.
| Column | Description |
|---|---|
| 🔑 content_id | ID which uniquely identifies this content item |
| content_type | The type of content this item represents, using the wrContentType enumeration (e.g. folder, report, theme, template) |
| report_type |
The type of report this row represents, using the wrReportType enumeration. If the content_type is not 0 (i.e. this row does not represent a report), this column is ignored. |
| content_attribute |
The component of the application that this item is associated with. Used when content_type is 2 to associate chart types with their respective themes. Will be one of the following values:
If the content_type is not 2 (i.e. this row does not represent a theme), this column is null and is ignored. |
| name | The name of the content item. |
| description | The description of the content item. For reports, this is the Description field provided when the report is saved. |
| text_content |
Plain text contents of the content item. Report definitions and themes are stored in this column. |
| bit_content |
Binary contents of the content item. Template files are stored in this column. |
| deleted_flag | Indicates if the content owner has deleted this item. When an item is deleted by the owner, it is no longer visible to any user. 1 represents True (item has been deleted), 0 represents False (item has not been deleted). |
| created_date | UTC timestamp when this content item was created. |
| created_by | The value of the User Id identity key that created this content item. |
| modified_date | UTC timestamp when this content was last saved. |
| modified_by | The value of the User Id identity key that last saved this content item. |
| owner_id | The value of the Owner Id identity key that owns this content. If the session’s Owner Id identity key matches this column’s value, that party will be given full permissions to the content item. |
| exports_allowed |
A bitmap indicating which export types the content item allows, where 1 indicates an export type is permitted and 0 indicates an export type is not permitted. This column is used for informational, sorting and filtering purposes only. These flags have no affect on the export types allowed for a report, which is still controlled by the report itself. When content is saved, this column will be updated to match the export type saved in the report definition. From MSB to LSB: Excel, CSV, RTF, PDF, HTML. Examples:
|
| inherit_flag | True if all of this content item’s content access records should be copied to new child content when it’s created. If False, a new content access record will be written for new child content with the default_party_type_id and default_access_flags. If null, the value in the system configuration will be used. |
| 🏷️ default_party_type_id |
If inherit_flag is False, a content access record with this party type and default_access_flags will be written for new content that is a child of this item. If the new content is a folder, this value will always be copied from the parent folder whether or not inherit_flag is true. |
| default_access_flags |
If inherit_flag is False, a content access record with the default_party_type_id and these access flags will be written for new content that is a child of this item. For more information, see the access_flags column in Table E below. If the new content is a folder, this value will always be copied from the parent folder whether or not inherit_flag is true. These flags will be implemented in v2020.2 of the application. |
| extended_attributes | This field is reserved for use by Exago clients, for storing metadata about content as they see fit in their custom implementation. |
| default_export_type |
This column is used for informational, sorting and filtering purposes only. This flag have no affect on the default export type allowed for a report, which is still controlled by the report itself. When content is saved, this column will be updated to match the default export type saved in the report definition. The report’s Default Export Type setting, represented by the ExportFlag enumeration. |
| report_tree_shortcut_action |
This column is used for informational, sorting and filtering purposes only. This flag have no affect on the report tree shortcut for a report, which is still controlled by the report itself. When content is saved, this column will be updated to match the report tree shortcut saved in the report definition. The report’s Report Tree Shortcut setting, using the TreeShortcut enumeration. |
| use_cache_execution |
This column is used for informational, sorting and filtering purposes only. This flag have no affect on enabling or disabling Execution Caching for a report, which is still controlled by the report itself. When content is saved, this column will be updated to match the execution cache settings saved in the report definition. If this content item is a report (content_type = 0), this column is True if Execution Caching is enabled for this report, or False if Execution Caching is disabled. |
| is_cache_valid | This column is for future functionality and is not implemented yet. |
| associated_reports |
A comma separated list of content_ids for each report that is associated with this one. Reports become associated with others when they are components in a Composite Report such as Chained Report or Dashboard, or if an Advanced Report contains linked reports.
|
Party Type Table
The Party Type defines groups of users. A party could be an individual user or all users, or anywhere in between.
| Column | Description |
|---|---|
| 🔑 party_type_id | Unique key identifying this party type. |
| priority | Defines the priority level of the access. A larger number increases priority of this party type. See Priority Levels below for additional information. |
| name | Name of this party_type. |
| parameter |
The name of the Storage Management identity key that will be compared to see if the user has access to this content. Out-of-the-box these values are:
but any identity key name defined in the configuration may be used. |
| description | A long format user friendly description of this party type. |
Priority Levels
The party type priorities determine in which scope the associated content access record applies. They are called priorities because higher values override lower ones.
For example if there are two content access records that apply to the same user for a content item, the record with the numerically greater value will apply over the other.
For more information, review the Permissions section of the Storage Management: Introduction article.
| Value | Name | Description |
|---|---|---|
| 1 | Everyone | Everyone has access to this item. |
| 2 | Class |
Users based on the their role in the organization. Exago will compare the value of the classId key against content_access.party_id for all records associated with the content_id to determine if the record applies. |
| 3 | Company |
Users based on their role in the company. Exago will compare the value of the companyId key against content_access.party_id for all records associated with the content_id to determine if the record applies. |
| 4 | User |
Individual user access. Exago will compare the value of the userId key against content_access.party_id for all records associated with the content_id to determine if the record applies. |
Default Party Type Values
When one of the Transition Utilities is used, or the Initialize Database button is clicked in the Admin Console, Exago will create the Storage Management database tables and load the following default party types into it:
| party_type_id | priority | name | parameter | description |
|---|---|---|---|---|
| 1 | 0 | Everyone | All users belong to this party | |
| 2 | 1 | Class | classId | Users based on their role in the organization, e.g. an admin or user |
| 3 | 2 | Company | companyId | Users that belong to a specific company |
| 4 | 3 | User | userId | Individual users |
Content Access Table
The relationship between content and party_type is defined in the content_access table. Each content access record defines permissions available for each content item. The content access table also establishes a parent-child relationship between content items.
| Column | Description | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 🏷️ content_id | Foreign key referencing the content_id in the content table that this record relates to. | |||||||||||||||
| 🏷️ party_type_id | Foreign key referencing the party_type_id in the party_type table, establishing the party for which this access record applies. | |||||||||||||||
| party_id |
The value of the Storage Management identity key specified by party_type_id that this content access record applies to. Sets the identity of the user group for which this access record applies. Example with default party types:
|
|||||||||||||||
| sort_order |
Content is sorted first by the value in this column, then by name (case in-sensitive). Larger values appear at the top of the sorted list). For example:
the sorted list would be: emma, Nick, alex, Bailey, Tim |
|||||||||||||||
| access_flags v2020.1–v2021.1 |
A bitmap indicating what access is permitted by this record, where 1 indicates a user in the party can complete the action and 0 indicates that they cannot. CanView, CanSave and CanEdit are currently implemented. The other flags are not yet active and will be implemented in a future release. All flags should be set as content is created to insure expected behavior when they are made active. From MSB to LSB:
Examples:
|
|||||||||||||||
| access_flags v2021.1+ |
A bitmap indicating what access is permitted by this record, where 1 indicates a user in the party can complete the action and 0 indicates that they cannot. From MSB to LSB:
Examples:
|
|||||||||||||||
| 🏷️ parent_id |
Foreign key referencing the content_id in the content table for the parent of this item. For a report, this would be the folder the report is stored in. For a folder, this would be the parent folder. If 00000000-0000-0000-0000-000000000000, this item is in the root of the hierarchy (it has no parent). |
|||||||||||||||
| child_inherits | This column is for future functionality and is not implemented yet. |
icon will appear for items that do not have the Can Rename, Can Move, Can Delete and Can Edit permissions.StorageMeta
Metadata pertaining to the Storage Management system is stored in this table as name-value pairs. It is used internally by Exago’s Storage Management implementation.
| Column | Data Type | Description |
|---|---|---|
| 🔑 name | string | The name of the name-value pair. |
| value | string | The value of the name-value pair. |
The StorageMeta table will be loaded with some initial name-value pairs when the Storage Management database is initialized. They are:
| name | value Data Type | value Description |
|---|---|---|
| CREATED | string | A timestamp representing the instant the database was initialized (created). For example: 2020-01-23T10:35:22 |
| SCHEMA_VERSION | string | The Storage Management data schema version currently employed by this implementation. For example: 1.1 |
Schema JSON
The schema defined above is also detailed in the following JSON file. This file is located in <WebApp>\Config\Other\StorageMgmtSchema.json. It is read by the transitioning utilities and the Web Application when initializing a new database.
In a custom Storage Management implementation, it is possible to add tables, columns and have Exago’s tools create them when executed.
