Site Search
Content Tools
Main Content
Database Storage
| COLUMN NAME | TYPE | NULL? | DESCRIPTION |
|---|---|---|---|
| #ASSETID | CHARACTER VARYING(255) |  | |
| #USERID | CHARACTER VARYING(255) | | |
| #PERMISSION | SMALLINT | | |
| GRANTED | CHARACTER(1) | |
| Indexes: |
| "sq_ast_perm_pkey" primary key btree(assetid, userid, permission) |
| "sq_ast_perm_assetid" btree(assetid) |
| "sq_ast_perm_userid" btree(userid) |
The primary key ensures that a permission for a user cannot be entered twice for the same asset. This futher ensures that a permission for a user cannot be granted and denied at the same time for a single asset. IE. the following condition will not occur:
assetid | userid | permission | granted ---------+--------+------------+--------- 123 | 5 | 2 | 1 123 | 5 | 2 | 0
Here, write access has been both granted and denied for user #5 to asset #123. The primary key ensures this situation cannot occur. This condition is also enforced in the code when setting permissions to allow for a more informative error message to be shown.
The index present on the assetid column is used for both joining and selecting. This column represents the ID of the asset the permission is applied to and so can be used, for example, to joing to the sq_ast table to gather basic asset information, or to the sq_ast_lnk table to get permissions for child assets of a defined parent. The assetid column will not contain shadow assetids as permissions cannot be applied to shadow assets. The column type is set to VARCHAR(255) instead of the standard VARCHAR(15) to aid in joining to other tables that do allow for shadow assetids to be stored.
The index present on the userid column is used for selecting only. While it can be used for joins with other tables, like the assetid index, this column contains shadow assetids and should not be joined to other tables as a match can not be guarenteed. An exception is if using LEFT or RIGHT JOINS where the goal is to return NULL values for non-matching rows.