Site Attribute
Table
| Database |
Table |
Entity |
On Key 5 Conversions |
On Key 5 Tables |
Other Entities stored in same Table |
Transaction |
gen.site_attribute |
Site Attribute |
ConvertSiteAttribute
|
stdAttributeValues
|
|
Columns
| Property |
Db Table |
Db Column |
Db DataType |
Nullable |
Primary Key |
Joins |
SiteId |
gen.site_attribute |
site_id |
bigint |
False |
|
|
SiteCode |
gen.site |
code |
character varying |
False |
|
gen.site_attribute.site_id = gen.site.site_id |
SiteDescription |
gen.site |
description |
character varying |
False |
|
gen.site_attribute.site_id = gen.site.site_id |
CreatedByUserId |
gen.site_attribute |
created_by_user_id |
bigint |
False |
|
|
CreatedByUserCode |
uam.user |
code |
character varying(50) |
False |
|
gen.site_attribute.created_by_user_id = uam.user.user_id |
CreatedByUserFullName |
uam.user |
full_name |
character varying(200) |
False |
|
gen.site_attribute.created_by_user_id = uam.user.user_id |
CreatedOn |
gen.site_attribute |
created_on |
timestamp without time zone |
False |
|
|
IsActive |
gen.site_attribute |
is_active |
boolean |
False |
|
|
ModifiedByUserId |
gen.site_attribute |
modified_by_user_id |
bigint |
False |
|
|
ModifiedByUserCode |
uam.user |
code |
character varying(50) |
False |
|
gen.site_attribute.modified_by_user_id = uam.user.user_id |
ModifiedByUserFullName |
uam.user |
full_name |
character varying(200) |
False |
|
gen.site_attribute.modified_by_user_id = uam.user.user_id |
ModifiedOn |
gen.site_attribute |
modified_on |
timestamp without time zone |
False |
|
|
PermissionTreeId |
gen.site_attribute |
permission_tree_id |
bigint |
False |
|
|
PermissionTreeCode |
uam.permission_tree |
code |
text |
False |
|
gen.site_attribute.permission_tree_id = uam.permission_tree.permission_tree_id |
PermissionTreeDescription |
uam.permission_tree |
description |
text |
True |
|
gen.site_attribute.permission_tree_id = uam.permission_tree.permission_tree_id |
Version |
gen.site_attribute |
version |
integer |
False |
|
|
Id |
gen.site_attribute |
site_attribute_id |
bigint |
False |
True |
|
AttributeId |
gen.site_attribute |
attribute_id |
bigint |
False |
|
|
AttributeCode |
gen.attribute |
code |
character varying(50) |
False |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id |
AttributeDescription |
gen.attribute |
description |
character varying(100) |
False |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id |
AttributeAllowAnyValue |
gen.attribute |
allow_any_value |
boolean |
False |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id |
AttributeDataType |
gen.attribute |
data_type |
system.dynamic_value_data_type |
False |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id |
AttributeDefaultValue |
gen.attribute |
default_value |
system.dynamic_value |
True |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id |
AttributeDefaultAllowedValueId |
gen.attribute_allowed_value |
attribute_allowed_value_id |
bigint |
False |
True |
gen.site_attribute.attribute_id = gen.attribute.attribute_id Then
gen.attribute.default_allowed_value_id = gen.attribute_allowed_value.attribute_allowed_value_id |
AttributeDefaultAllowedValueDescription |
gen.attribute_allowed_value |
description |
text |
True |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id Then
gen.attribute.default_allowed_value_id = gen.attribute_allowed_value.attribute_allowed_value_id |
AttributeDefaultAllowedValueValue |
gen.attribute_allowed_value |
value |
system.dynamic_value |
False |
|
gen.site_attribute.attribute_id = gen.attribute.attribute_id Then
gen.attribute.default_allowed_value_id = gen.attribute_allowed_value.attribute_allowed_value_id |
Notes |
gen.site_attribute |
notes |
text |
True |
|
|
PredefinedValueId |
gen.site_attribute |
attribute_value_id |
bigint |
True |
|
|
PredefinedValueDescription |
gen.attribute_allowed_value |
description |
text |
True |
|
gen.site_attribute.attribute_value_id = gen.attribute_allowed_value.attribute_allowed_value_id |
PredefinedValueValue |
gen.attribute_allowed_value |
value |
system.dynamic_value |
False |
|
gen.site_attribute.attribute_value_id = gen.attribute_allowed_value.attribute_allowed_value_id |
SequenceNumber |
gen.site_attribute |
sequence_number |
integer |
True |
|
|
Value |
gen.site_attribute |
attribute_value |
system.dynamic_value |
True |
|
|
Enumerations
Mapping 1
| Property |
Property Type |
Db Column |
Db Column Type |
Db Column Values |
| AttributeDataType |
DynamicValueType |
data_type |
system.dynamic_value_data_type |
String
Email
Integer
Long
Float
Currency
DateTime
Date
Time
Duration
Boolean
|
Composites
Mapping 1
| Property |
Property Type |
Db Column |
Db Column Type |
| AttributeDefaultValue |
DbDynamicValue |
default_value |
system.dynamic_value |
| Composite Property |
Db Composite Column |
Property Type |
Type |
type |
String
Email
Integer
Long
Float
Currency
DateTime
Date
Time
Duration
Boolean
|
Value |
value |
String |
Mapping 2
| Property |
Property Type |
Db Column |
Db Column Type |
| AttributeDefaultAllowedValueValue |
DbDynamicValue |
value |
system.dynamic_value |
| Composite Property |
Db Composite Column |
Property Type |
Type |
type |
String
Email
Integer
Long
Float
Currency
DateTime
Date
Time
Duration
Boolean
|
Value |
value |
String |
Mapping 3
| Property |
Property Type |
Db Column |
Db Column Type |
| PredefinedValueValue |
DbDynamicValue |
value |
system.dynamic_value |
| Composite Property |
Db Composite Column |
Property Type |
Type |
type |
String
Email
Integer
Long
Float
Currency
DateTime
Date
Time
Duration
Boolean
|
Value |
value |
String |
Mapping 4
| Property |
Property Type |
Db Column |
Db Column Type |
| Value |
DbDynamicValue |
attribute_value |
system.dynamic_value |
| Composite Property |
Db Composite Column |
Property Type |
Type |
type |
String
Email
Integer
Long
Float
Currency
DateTime
Date
Time
Duration
Boolean
|
Value |
value |
String |
Queries
The list of example Site Attribute queries can be interpreted using the following legend:
| Legend |
Description |
| Primary Table Alias |
The alias for the gen.site_attribute table in the query |
| Include References |
Include (true) or exclude (false) all lookup table joins in the query |
| Include Permissions |
Include (true) or exclude (false) the permission table join in the query to enforce or skip row-level security based on the permissions for the user |
Query 1
| Primary Table Alias |
Include References |
Include Permissions |
_sa |
False |
False |
SELECT
_sa.site_id AS "SiteId",
_sa.created_by_user_id AS "CreatedByUserId",
_sa.created_on AS "CreatedOn",
_sa.is_active AS "IsActive",
_sa.modified_by_user_id AS "ModifiedByUserId",
_sa.modified_on AS "ModifiedOn",
_sa.permission_tree_id AS "PermissionTreeId",
_sa.version AS "Version",
_sa.site_attribute_id AS "Id",
_sa.attribute_id AS "AttributeId",
_sa.notes AS "Notes",
_sa.attribute_value_id AS "PredefinedValueId",
_sa.sequence_number AS "SequenceNumber",
_sa.attribute_value AS "Value"
FROM
gen.site_attribute _sa
Query 2
| Primary Table Alias |
Include References |
Include Permissions |
_sa |
True |
False |
SELECT
_s.site_id AS "SiteId",
_s.code AS "SiteCode",
_s.description AS "SiteDescription",
_u.user_id AS "CreatedByUserId",
_u.code AS "CreatedByUserCode",
_u.full_name AS "CreatedByUserFullName",
_sa.created_on AS "CreatedOn",
_sa.is_active AS "IsActive",
_u1.user_id AS "ModifiedByUserId",
_u1.code AS "ModifiedByUserCode",
_u1.full_name AS "ModifiedByUserFullName",
_sa.modified_on AS "ModifiedOn",
_pt.permission_tree_id AS "PermissionTreeId",
_pt.code AS "PermissionTreeCode",
_pt.description AS "PermissionTreeDescription",
_sa.version AS "Version",
_sa.site_attribute_id AS "Id",
_a.attribute_id AS "AttributeId",
_a.code AS "AttributeCode",
_a.description AS "AttributeDescription",
_a.allow_any_value AS "AttributeAllowAnyValue",
_a.data_type AS "AttributeDataType",
_a.default_value AS "AttributeDefaultValue",
_a.default_allowed_value_id AS "AttributeDefaultAllowedValue",
_aav.attribute_allowed_value_id AS "AttributeDefaultAllowedValueId",
_aav.description AS "AttributeDefaultAllowedValueDescription",
_aav.value AS "AttributeDefaultAllowedValueValue",
_sa.notes AS "Notes",
_aav1.attribute_allowed_value_id AS "PredefinedValueId",
_aav1.description AS "PredefinedValueDescription",
_aav1.value AS "PredefinedValueValue",
_sa.sequence_number AS "SequenceNumber",
_sa.attribute_value AS "Value"
FROM
gen.site_attribute _sa
JOIN gen.site _s ON _sa.site_id = _s.site_id
JOIN uam.user _u ON _sa.created_by_user_id = _u.user_id
JOIN uam.user _u1 ON _sa.modified_by_user_id = _u1.user_id
JOIN uam.permission_tree _pt ON _sa.permission_tree_id = _pt.permission_tree_id
JOIN gen.attribute _a ON _sa.attribute_id = _a.attribute_id
LEFT JOIN gen.attribute_allowed_value _aav ON _a.default_allowed_value_id = _aav.attribute_allowed_value_id
LEFT JOIN gen.attribute_allowed_value _aav1 ON _sa.attribute_value_id = _aav1.attribute_allowed_value_id
Query 3
| Primary Table Alias |
Include References |
Include Permissions |
_sa |
False |
True |
| Parameter Name |
Data Type |
Value |
@permission_user_id |
Bigint |
500000000000 |
@permission_id |
Bigint |
100110300 |
WITH permission_tree_ids AS
(
SELECT DISTINCT unnest(psd.permission_tree_ids) AS permission_tree_id FROM uam.user_permission up JOIN uam.permission_set_data psd ON up.permission_set_id = psd.permission_set_id WHERE up.user_id = @permission_user_id AND up.permission_id = @permission_id
)
SELECT
_sa.site_id AS "SiteId",
_sa.created_by_user_id AS "CreatedByUserId",
_sa.created_on AS "CreatedOn",
_sa.is_active AS "IsActive",
_sa.modified_by_user_id AS "ModifiedByUserId",
_sa.modified_on AS "ModifiedOn",
_sa.permission_tree_id AS "PermissionTreeId",
_sa.version AS "Version",
_sa.site_attribute_id AS "Id",
_sa.attribute_id AS "AttributeId",
_sa.notes AS "Notes",
_sa.attribute_value_id AS "PredefinedValueId",
_sa.sequence_number AS "SequenceNumber",
_sa.attribute_value AS "Value",
_sa.permission_tree_id AS "_sa_permission_tree_id"
FROM
gen.site_attribute _sa
JOIN permission_tree_ids permission_join ON _sa.permission_tree_id = permission_join.permission_tree_id
Query 4
| Primary Table Alias |
Include References |
Include Permissions |
_sa |
True |
True |
| Parameter Name |
Data Type |
Value |
@permission_user_id |
Bigint |
500000000000 |
@permission_id |
Bigint |
100110300 |
WITH permission_tree_ids AS
(
SELECT DISTINCT unnest(psd.permission_tree_ids) AS permission_tree_id FROM uam.user_permission up JOIN uam.permission_set_data psd ON up.permission_set_id = psd.permission_set_id WHERE up.user_id = @permission_user_id AND up.permission_id = @permission_id
)
SELECT
_s.site_id AS "SiteId",
_s.code AS "SiteCode",
_s.description AS "SiteDescription",
_u.user_id AS "CreatedByUserId",
_u.code AS "CreatedByUserCode",
_u.full_name AS "CreatedByUserFullName",
_sa.created_on AS "CreatedOn",
_sa.is_active AS "IsActive",
_u1.user_id AS "ModifiedByUserId",
_u1.code AS "ModifiedByUserCode",
_u1.full_name AS "ModifiedByUserFullName",
_sa.modified_on AS "ModifiedOn",
_pt.permission_tree_id AS "PermissionTreeId",
_pt.code AS "PermissionTreeCode",
_pt.description AS "PermissionTreeDescription",
_sa.version AS "Version",
_sa.site_attribute_id AS "Id",
_a.attribute_id AS "AttributeId",
_a.code AS "AttributeCode",
_a.description AS "AttributeDescription",
_a.allow_any_value AS "AttributeAllowAnyValue",
_a.data_type AS "AttributeDataType",
_a.default_value AS "AttributeDefaultValue",
_a.default_allowed_value_id AS "AttributeDefaultAllowedValue",
_aav.attribute_allowed_value_id AS "AttributeDefaultAllowedValueId",
_aav.description AS "AttributeDefaultAllowedValueDescription",
_aav.value AS "AttributeDefaultAllowedValueValue",
_sa.notes AS "Notes",
_aav1.attribute_allowed_value_id AS "PredefinedValueId",
_aav1.description AS "PredefinedValueDescription",
_aav1.value AS "PredefinedValueValue",
_sa.sequence_number AS "SequenceNumber",
_sa.attribute_value AS "Value",
_sa.permission_tree_id AS "_sa_permission_tree_id",
_s.permission_tree_id AS "_s_permission_tree_id",
_u.permission_tree_id AS "_u_permission_tree_id",
_u1.permission_tree_id AS "_u1_permission_tree_id",
_a.permission_tree_id AS "_a_permission_tree_id",
_aav.permission_tree_id AS "_aav_permission_tree_id",
_aav1.permission_tree_id AS "_aav1_permission_tree_id"
FROM
gen.site_attribute _sa
JOIN permission_tree_ids permission_join ON _sa.permission_tree_id = permission_join.permission_tree_id
JOIN gen.site _s ON _sa.site_id = _s.site_id
JOIN uam.user _u ON _sa.created_by_user_id = _u.user_id
JOIN uam.user _u1 ON _sa.modified_by_user_id = _u1.user_id
JOIN uam.permission_tree _pt ON _sa.permission_tree_id = _pt.permission_tree_id
JOIN gen.attribute _a ON _sa.attribute_id = _a.attribute_id
LEFT JOIN gen.attribute_allowed_value _aav ON _a.default_allowed_value_id = _aav.attribute_allowed_value_id
LEFT JOIN gen.attribute_allowed_value _aav1 ON _sa.attribute_value_id = _aav1.attribute_allowed_value_id