Exago Logo
Search
Generic filters
Exact matches only

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_Graphic_Blue.png

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.

Table A — Storage Management Content Table Schema
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:

  • Chart
  • CrossTab
  • Express
  • ExpressView
  • ExpressView_v2
  • Map — theme for GeoChart

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:

  • 00001 = 1 = HTML only. No file exports.
  • 00011 = 3 = HTML and PDF only
  • 11001 = 25 = HTML (report viewer), Excel Workbook and CSV exports
  • 11111 = 31 = All file types allowed
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.

  • Chained Reports: Each component report in a chain is included.
  • Dashboards: Each Existing Report tile is included.
  • Advanced Report with linked report: a report is included for each cell that is linked. For example, if there are 4 cells that link to the same report, the same content_id will appear in this list 4 times.

Party Type Table

The Party Type defines groups of users. A party could be an individual user or all users, or anywhere in between.

Table B — Storage Management Party Type Table Schema
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:

  • classId
  • companyId
  • userId

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.

Table C — Party Type Table priority Values
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:

Table D — Default Party Type Values
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.

Table E — Storage Management Content Access Table Schema
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:

Table F — Examples
If party_type_id is… And party_id is… These users are affected by this content access record…
1 n/a All users
2 report-builder Any user whose classId key value is also report-builder.
3 Exago, Inc. Any user whose companyId key value is also Exago, Inc., regardless of their classId key.
4 Astro Boy Only the user whose userId key value is Astro Boy, regardless of their companyId or classId keys.
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:

Name sort_order
Nick 99
emma 99
Tim 0
alex 0
Bailey 0

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:

  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • CanView — the content item is visible in the Report Tree
  • CanDownload — if Show Report Upload/Download Options in the Admin Console is True, this value determines if the content can be downloaded. Has no effect if Show Report Upload/Download Options is False, or if the content is set as read-only by a Role.
  • CanCopy — the content can be duplicated
  • CanExecute — the report can be run
  • CanDelete — the content can be deleted
  • CanShare — for future functionality and is not implemented yet
  • CanRename — the content can be renamed
  • CanSave — the report can be saved
  • CanEdit — the report can be opened in the Report Designer

Examples:

  • 1111111111111111 = 65535 = All permissions available
  • 0000000100100000 = 288 = Report appears in the report tree and can be run, but no other permissions.
  • 0000000100100011 = 291 = Report may only be run, edited and saved and will appear in the report tree.
  • 0000000111100000 = 480 = Report appears in the report tree, and can be downloaded, duplicated and run. Otherwise no other permissions.

    Tip

    This is the same behavior as the Read Only option in Roles.

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:

  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • Reserved — this bit is reserved for future use
  • CanMove — the item can be moved to a different location
  • CanSchedule — the item can be scheduled or e-mailed
  • CanView — the content item is visible in the Report Tree
  • Reserved — this bit is reserved for future use
  • CanCopy — the item may be duplicated or downloaded
  • Reserved — this bit is reserved for future use
  • CanDelete — the item can be deleted
  • CanShare — for future functionality and is not implemented yet
  • CanRename — the content can be renamed
  • Reserved — this bit is reserved for future use
  • CanEdit — the item can be opened in a designer

Examples:

  • 1111111111111111 = 65535 = All permissions available
  • 0000010100000001 = 1281 = item may be edited in the designer, moved to a different folder and seen in the report tree only
  • 0000000100000000 = 256 = item is visible in the Report Tree, but no new content may be saved and it may not open in the Designer.

    Note

    This is the same behavior as marking items as Read Only with a Role. The Read Only TreeReportLock.png icon will appear for items that do not have the Can Rename, Can Move, Can Delete and Can Edit permissions.

🏷️ 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.

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.

Table G — Storage Management StorageMeta Table Schema
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:

Table H — StorageMeta Key-Value Pairs
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.

Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents