def profile_foreign_keys(profile):
"""
Gets all foreign keys for tables in the given profile.
:param profile: Profile object.
:type profile: Profile
:return: A list containing foreign key names for all tables in the given
profile.
:rtype: list(str)
"""
from stdm.data.pg_utils import pg_table_exists
_bind_metadata(metadata)
insp = reflection.Inspector.from_engine(metadata.bind)
fks = []
for t in profile.table_names():
#Assert if the table exists
if not pg_table_exists(t):
continue
t_fks = insp.get_foreign_keys(t)
for fk in t_fks:
if 'name' in fk:
fk_name = fk['name']
fks.append(fk_name)
return fks
def grant_revoke_privilege(self, operation):
try:
privilege = PrivilegeProvider.Privileges[
self.content_name[:self.content_name.index(' ')]]
except DummyException:
privilege = 'INSERT'
if operation == 'GRANT':
self.grant_privilege_base_table(self.role)
if pg_table_exists(self.content_table_name):
self.grant_or_revoke(operation, privilege, self.content_table_name,
self.role)
if privilege == 'INSERT':
# INSERT privilege will also trigger an issue of SELECT privilege
self.grant_or_revoke(operation, 'SELECT',
self.content_table_name, self.role)
for related_content in self.related_contents.values():
self._grant_revoke(operation, privilege, related_content,
self.role)
if self.support_doc_table_name != '':
self._grant_revoke(operation, privilege,
self.support_doc_table_name, self.role)
# Supporting document type
self._grant_revoke(operation, privilege,
self.support_doc_type_name, self.role)
def profile_foreign_keys(profile):
"""
Gets all foreign keys for tables in the given profile.
:param profile: Profile object.
:type profile: Profile
:return: A list containing foreign key names for all tables in the given
profile.
:rtype: list(str)
"""
from stdm.data.pg_utils import pg_table_exists
_bind_metadata(metadata)
insp = reflection.Inspector.from_engine(metadata.bind)
fks = []
for t in profile.table_names():
#Assert if the table exists
if not pg_table_exists(t):
continue
t_fks = insp.get_foreign_keys(t)
for fk in t_fks:
if 'name' in fk:
fk_name = fk['name']
fks.append(fk_name)
return fks
def _validate_data_source(self):
# Check if the specified data source exists.
status = pg_table_exists(self._config.data_source)
is_valid = True
if not status:
is_valid = False
return is_valid
def draw_spatial_unit(self, spatial_unit, model):
"""
Draw geometry of the given model in the respective local and web views.
:param model: Source model whose geometry will be drawn.
:type model: object
:param clear_existing: Clears any existing features prior to adding the
new features.
:type clear_existing: bool
"""
if model is None:
msg = QApplication.translate(
"SpatialPreview", "Data model is empty, the spatial "
"unit cannot be rendered.")
QMessageBox.critical(
self,
QApplication.translate("SpatialPreview",
"Spatial Unit Preview"), msg)
return
table_name = spatial_unit.name
if not pg_table_exists(table_name):
msg = QApplication.translate(
"SpatialPreview", "The spatial unit data source could "
"not be retrieved, the feature cannot "
"be rendered.")
QMessageBox.critical(
self,
QApplication.translate("SpatialPreview",
"Spatial Unit Preview"), msg)
return
sp_unit_manager = SpatialUnitManagerDockWidget(self.iface())
spatial_cols = sp_unit_manager.geom_columns(spatial_unit)
geom, geom_col = None, ""
sc_obj = None
for sc in spatial_cols:
db_geom = getattr(model, sc.name)
#Use the first non-empty geometry
# value in the collection
if not db_geom is None:
sc_obj = sc
geom_col = sc.name
geom = db_geom
QApplication.processEvents()
lyr = sp_unit_manager.geom_col_layer_name(table_name, sc_obj)
sp_unit_manager.add_layer_by_name(lyr)
if geom is not None:
self.highlight_spatial_unit(spatial_unit, geom,
self.local_map.canvas)
self._web_spatial_loader.add_overlay(model, geom_col)
def data_source_exists(self, data_source):
"""
:param data_source: Data source object containing table/view name and
corresponding columns.
:type data_source: ComposerDataSource
:return: Checks if the table or view specified in the data source exists.
:rtype: str
"""
return pg_table_exists(data_source.name())
def data_source_exists(self, data_source):
"""
:param data_source: Data source object containing table/view name and
corresponding columns.
:type data_source: ComposerDataSource
:return: Checks if the table or view specified in the data source exists.
:rtype: str
"""
return pg_table_exists(data_source.name())
def grant_revoke_privilege(self, operation):
for role, privileges in self.roles.iteritems():
for p in privileges:
privilege = PrivilegeProvider.Privileges[p]
temp_content = ''
for related_content in self.related_contents.values():
if temp_content == related_content:continue
if pg_table_exists(related_content):
temp_content = related_content
self.grant_or_revoke(operation, privilege, related_content, role)
self.grant_or_revoke(operation, 'SELECT', related_content, role)
def _check_table_exists(table):
table_exists = pg_table_exists(table, False)
if not table_exists:
LOGGER.debug(
'%s table does not exist. Foreign key will not be '
'created.', table)
return False
return True
def grant_revoke_privilege(self, operation):
for role, privileges in self.roles.items():
for p in privileges:
privilege = PrivilegeProvider.Privileges[p]
temp_content = ''
for related_content in self.related_contents.values():
if temp_content == related_content: continue
if pg_table_exists(related_content):
temp_content = related_content
self.grant_or_revoke(operation, privilege, related_content, role)
self.grant_or_revoke(operation, 'SELECT', related_content, role)
def view_updater(social_tenure, engine):
"""
Creates a generic database view linking all STR entities.
:param social_tenure: Social tenure object.
:type social_tenure: SocialTenure
:param engine: SQLAlchemy connectable object.
:type engine: Engine
"""
view_name = social_tenure.view_name
#Check if there is an existing one and delete if it exists
LOGGER.debug('Checking if %s view exists...', view_name)
#Do not create if it already exists
if pg_table_exists(view_name):
return
#Collection for foreign key parents so that appropriate pseudo names
# can be constructed if more than one parent is used for the same entity.
fk_parent_names = {}
#Create the SQL statement for creating the view where party is the
# primary entity
str_columns, str_join = _entity_select_column(
social_tenure, True, True, foreign_key_parents=fk_parent_names)
party_columns, party_join = _entity_select_column(
social_tenure.party,
True,
True,
True,
foreign_key_parents=fk_parent_names)
spatial_unit_columns, spatial_unit_join = _entity_select_column(
social_tenure.spatial_unit,
True,
join_parents=True,
foreign_key_parents=fk_parent_names)
view_columns = str_columns + party_columns + spatial_unit_columns
join_statement = str_join + party_join + spatial_unit_join
if len(view_columns) == 0:
LOGGER.debug('There are no columns for creating the social tenure '
'relationship view.')
return
#Create SQL statement
create_view_sql = u'CREATE VIEW {0} AS SELECT {1} FROM {2} {3}'.format(
view_name, ','.join(view_columns), social_tenure.name,
' '.join(join_statement))
normalized_create_view_sql = text(create_view_sql)
result = _execute(normalized_create_view_sql)
def _validate_custom_attr_dummy_column(self, custom_entity):
# Check if the dummy column has been added to the custom tenure entity
# Insert dummy column so that the table is not flagged as a m2m
dummy_col = custom_entity.column(self.CUSTOM_TENURE_DUMMY_COLUMN)
if dummy_col is None:
dummy_col = VarCharColumn(
self.CUSTOM_TENURE_DUMMY_COLUMN,
custom_entity,
maximum=1
)
custom_entity.add_column(dummy_col)
if pg_table_exists(custom_entity.name):
custom_ent_cols = table_column_names(custom_entity.name)
if dummy_col.name not in custom_ent_cols:
custom_table = alchemy_table(custom_entity.name)
varchar_updater(dummy_col, custom_table, custom_ent_cols)
def grant_revoke_privilege(self, operation):
try:
privilege = PrivilegeProvider.Privileges[self.content_name[:self.content_name.index(' ')]]
except:
privilege = 'INSERT'
if operation == 'GRANT':
self.grant_privilege_base_table(self.role)
if pg_table_exists(self.content_table_name):
self.grant_or_revoke(operation, privilege, self.content_table_name, self.role)
if privilege == 'INSERT':
# INSERT privilege will also trigger an issue of SELECT privilege
self.grant_or_revoke(operation, 'SELECT', self.content_table_name, self.role)
for related_content in self.related_contents.values():
self.grant_or_revoke(operation, 'SELECT', related_content, self.role)
if privilege <> 'SELECT':
self.grant_or_revoke(operation, privilege, related_content, self.role)
def view_updater(social_tenure, engine):
"""
Creates a generic database view linking all STR entities.
:param social_tenure: Social tenure object.
:type social_tenure: SocialTenure
:param engine: SQLAlchemy connectable object.
:type engine: Engine
"""
view_name = social_tenure.view_name
views = social_tenure.views
# Loop thru view name, primary entity items
for v, pe in views.iteritems():
# Check if there is an existing one and omit delete if it exists
LOGGER.debug('Checking if %s view exists...', v)
# Do not create if it already exists
if pg_table_exists(v):
continue
# Create view based on the primary entity
_create_primary_entity_view(social_tenure, pe, v)
def view_updater(social_tenure, engine):
"""
Creates a generic database view linking all STR entities.
:param social_tenure: Social tenure object.
:type social_tenure: SocialTenure
:param engine: SQLAlchemy connectable object.
:type engine: Engine
"""
views = social_tenure.views
# Loop through view name, primary entity items
for v, pe in views.iteritems():
# Check if there is an existing one and omit delete if it exists
LOGGER.debug('Checking if %s view exists...', v)
# Do not create if it already exists
if pg_table_exists(v):
continue
# Create view based on the primary entity
_create_primary_entity_view(social_tenure, pe, v)
def update_str_table(self):
"""
Updates the database to the next version.
"""
for profile in self.config.profiles.values():
social_tenure = profile.social_tenure
if not pg_table_exists(social_tenure.name, False):
return
parties = social_tenure.parties
if len(parties) < 1:
return
party = parties[0].short_name.lower()
party_table = parties[0].name
old_column = 'party_id'
if not old_column in table_column_names(social_tenure.name):
return
new_column = '{}_id'.format(party)
if old_column != new_column:
copy_from_column_to_another(str(social_tenure.name),
old_column, new_column)
add_constraint(str(social_tenure.name), new_column,
party_table)
def update_str_table(self):
"""
Updates the database to the next version.
"""
for profile in self.config.profiles.values():
social_tenure = profile.social_tenure
if not pg_table_exists(social_tenure.name, False):
return
parties = social_tenure.parties
if len(parties) < 1:
return
party = parties[0].short_name.lower()
party_table = parties[0].name
old_column = 'party_id'
if not old_column in table_column_names(social_tenure.name):
return
new_column = '{}_id'.format(party)
if old_column != new_column:
copy_from_column_to_another(
str(social_tenure.name), old_column, new_column
)
add_constraint(str(social_tenure.name), new_column, party_table)
def _entity_select_column(entity,
use_inner_join=False,
join_parents=False,
is_primary=False,
foreign_key_parents=None,
omit_view_columns=None,
omit_join_statement_columns=None,
view_name=None):
# Check if the entity exists in the database
if not pg_table_exists(entity.name):
msg = '{0} table does not exist, social tenure view will not be ' \
'created.'.format(entity.name)
LOGGER.debug(msg)
raise ConfigurationException(msg)
if omit_view_columns is None:
omit_view_columns = []
if omit_join_statement_columns is None:
omit_join_statement_columns = []
column_names = []
join_statements = []
columns = list(entity.columns.values())
# Create foreign key parent collection if none is specified
if foreign_key_parents is None:
foreign_key_parents = {}
str_entity = entity.profile.social_tenure
if entity in str_entity.custom_attribute_entities.values():
custom_tenure = True
else:
custom_tenure = False
i = 0
parents = []
for c in columns:
if c.TYPE_INFO not in _exclude_view_column_types:
normalized_entity_sname = entity.short_name.replace(' ',
'_').lower()
pseudo_column_name = '{0}_{1}'.format(normalized_entity_sname,
c.name)
# use sudo name for custom tenure entity
if custom_tenure:
col_select_name = '{0}_1.{1}'.format(entity.name, c.name)
else:
col_select_name = '{0}.{1}'.format(entity.name, c.name)
# Get pseudoname to use
select_column_name = '{0} AS {1}'.format(col_select_name,
pseudo_column_name)
if is_primary and c.name == 'id':
# add row number id instead of party.id
# if multi_party is allowed.
if str_entity.multi_party:
# for party entity add use row number
if not entity.has_geometry_column():
row_id = 'row_number() OVER () AS id'
select_column_name = row_id
else:
# add spatial unit id as the id.
col_spatial_unit_id = '{0}.{1} AS {1}'.format(
entity.name, c.name)
select_column_name = col_spatial_unit_id
else:
# add party id or spatial unit as id
entity_id = '{0}.{1} AS {1}'.format(entity.name, c.name)
select_column_name = entity_id
# Use custom join flag
use_custom_join = False
if isinstance(c, ForeignKeyColumn) and join_parents:
LOGGER.debug('Creating STR: Getting parent for %s column',
c.name)
fk_parent_entity = c.entity_relation.parent
parent_table = c.entity_relation.parent.name
LOGGER.debug('Parent found')
# Handle renaming of parent table names to appropriate
# pseudonames.
if parent_table not in foreign_key_parents:
foreign_key_parents[parent_table] = []
pseudo_names = foreign_key_parents.get(parent_table)
# Get pseudoname to use
table_pseudo_name = '{0}_{1}'.format(parent_table,
(len(pseudo_names) + 1))
pseudo_names.append(table_pseudo_name)
# Map lookup and admin unit values by default
if c.TYPE_INFO == 'LOOKUP':
lookup_model = entity_model(c.entity_relation.parent)
lookup_model_obj = lookup_model()
result = lookup_model_obj.queryObject().filter(
lookup_model.code != '').filter(
lookup_model.code is not None).all()
if len(result) == 0:
select_column_name = '{0}.value AS {1}'.format(
table_pseudo_name, pseudo_column_name)
else:
value = '{0}.value'.format(table_pseudo_name)
code = '{0}.code'.format(table_pseudo_name)
select_column_name = "concat({0}, ' (', {1}, ')') AS {2}". \
format(value, code, pseudo_column_name)
use_custom_join = True
# Check if the column is for tenure type
if c.name != 'tenure_type':
use_inner_join = False
elif c.TYPE_INFO == 'ADMIN_SPATIAL_UNIT':
select_column_name = '{0}.name AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
use_inner_join = False
elif c.TYPE_INFO == 'FOREIGN_KEY':
if c.entity_relation.parent not in str_entity.parties and \
c.entity_relation.parent not in str_entity.spatial_units:
if len(c.entity_relation.display_cols) > 0:
display_col_names = []
for display_col in c.entity_relation.display_cols:
name = '{0}.{1}'.format(
table_pseudo_name, display_col)
display_col_names.append(name)
select_column_name = "concat_ws(' '::text, {0}) AS {1}".format(
', '.join(display_col_names),
pseudo_column_name)
else:
if not custom_tenure:
select_column_name = '{0}.id AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
use_inner_join = False
else:
QApplication.processEvents()
# These are outer joins
join_type = 'LEFT JOIN'
# Use inner join only if parent entity is an STR entity and it is the current entity.
# Other str entities should use left join.
if str_entity.is_str_entity(fk_parent_entity) and \
fk_parent_entity.name in view_name:
join_type = 'INNER JOIN'
if use_custom_join:
# exclude replace str name with custom tenure name in join.
if custom_tenure:
if c.name == 'social_tenure_relationship_id':
i = i + 1
# pseudo_names = foreign_key_parents.get(parent_table)
col_select_name = '{0}_{1}.{2}'.format(
entity.name, str(i), c.name)
# Get pseudoname to use
table_pseudo_name = '{0}_{1}'.format(
entity.name, str(i))
join_statement = '{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, entity.name, table_pseudo_name,
col_select_name,
c.entity_relation.parent_column)
join_statements = [join_statement
] + join_statements
else:
join_statement = '{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, table_pseudo_name,
col_select_name,
c.entity_relation.parent_column)
join_statements.append(join_statement)
else:
join_statement = '{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, table_pseudo_name,
col_select_name, c.entity_relation.parent_column)
join_statements.append(join_statement)
else:
# Assert if the column is in the list of omitted join columns
#
# if c.name in omit_join_statement_columns:
# if 'INNER JOIN' in join_statement:
# join_statements.append(join_statement)
# else:
# This fix needs to be tested more ...
if parent_table in foreign_key_parents and 'relationship' not in entity.name:
parent_table_alias = foreign_key_parents[parent_table][
0]
join_statement = '{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, parent_table_alias,
col_select_name, c.entity_relation.parent_column)
else:
join_statement = '{0} {1} ON {2} = {1}.{3}'.format(
join_type, parent_table, col_select_name,
c.entity_relation.parent_column)
parents.append(parent_table)
join_statements.append(join_statement)
# Assert if the column is in the list of omitted view columns
if c.name not in omit_view_columns:
if select_column_name:
column_names.append(select_column_name)
QApplication.processEvents()
return column_names, join_statements
def _entity_select_column(
entity,
use_inner_join=False,
join_parents=False,
is_primary=False,
foreign_key_parents=None,
omit_view_columns=None,
omit_join_statement_columns=None,
view_name=None
):
# Check if the entity exists in the database
if not pg_table_exists(entity.name):
msg = u'{0} table does not exist, social tenure view will not be ' \
u'created.'.format(entity.name)
LOGGER.debug(msg)
raise ConfigurationException(msg)
if omit_view_columns is None:
omit_view_columns = []
if omit_join_statement_columns is None:
omit_join_statement_columns = []
column_names = []
join_statements = []
columns = entity.columns.values()
# Create foreign key parent collection if none is specified
if foreign_key_parents is None:
foreign_key_parents = {}
str_entity = entity.profile.social_tenure
if entity in str_entity.custom_attribute_entities.values():
custom_tenure = True
else:
custom_tenure = False
i = 0
parents = []
for c in columns:
if c.TYPE_INFO not in _exclude_view_column_types:
normalized_entity_sname = entity.short_name.replace(' ', '_').lower()
pseudo_column_name = u'{0}_{1}'.format(normalized_entity_sname,
c.name)
# use sudo name for custom tenure entity
if custom_tenure:
col_select_name = u'{0}_1.{1}'.format(
entity.name, c.name
)
else:
col_select_name = u'{0}.{1}'.format(entity.name, c.name)
# Get pseudoname to use
select_column_name = u'{0} AS {1}'.format(col_select_name,
pseudo_column_name)
if is_primary and c.name == 'id':
# add row number id instead of party.id
# if multi_party is allowed.
if str_entity.multi_party:
# for party entity add use row number
if not entity.has_geometry_column():
row_id = 'row_number() OVER () AS id'
select_column_name = row_id
else:
# add spatial unit id as the id.
col_spatial_unit_id = u'{0}.{1} AS {1}'.format(
entity.name, c.name
)
select_column_name = col_spatial_unit_id
else:
# add party id or spatial unit as id
entity_id = u'{0}.{1} AS {1}'.format(
entity.name, c.name
)
select_column_name = entity_id
# Use custom join flag
use_custom_join = False
if isinstance(c, ForeignKeyColumn) and join_parents:
LOGGER.debug('Creating STR: Getting parent for %s column', c.name)
fk_parent_entity = c.entity_relation.parent
parent_table = c.entity_relation.parent.name
LOGGER.debug('Parent found')
# Handle renaming of parent table names to appropriate
# pseudonames.
if not parent_table in foreign_key_parents:
foreign_key_parents[parent_table] = []
pseudo_names = foreign_key_parents.get(parent_table)
# Get pseudoname to use
table_pseudo_name = u'{0}_{1}'.format(
parent_table, (len(pseudo_names) + 1)
)
pseudo_names.append(table_pseudo_name)
# Map lookup and admin unit values by default
if c.TYPE_INFO == 'LOOKUP':
lookup_model = entity_model(c.entity_relation.parent)
lookup_model_obj = lookup_model()
result = lookup_model_obj.queryObject().filter(
lookup_model.code != '').filter(
lookup_model.code != None).all()
if len(result) == 0:
select_column_name = u'{0}.value AS {1}'.format(
table_pseudo_name,
pseudo_column_name
)
else:
value = u'{0}.value'.format(table_pseudo_name)
code = u'{0}.code'.format(table_pseudo_name)
select_column_name = u"concat({0}, ' (', {1}, ')') AS {2}".\
format(value, code, pseudo_column_name)
use_custom_join = True
# Check if the column is for tenure type
if c.name != 'tenure_type':
use_inner_join = False
elif c.TYPE_INFO == 'ADMIN_SPATIAL_UNIT':
select_column_name = u'{0}.name AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
use_inner_join = False
elif c.TYPE_INFO == 'FOREIGN_KEY':
if c.entity_relation.parent not in str_entity.parties and \
c.entity_relation.parent not in str_entity.spatial_units:
if len(c.entity_relation.display_cols) > 0:
display_col_names = []
for display_col in c.entity_relation.display_cols:
name = u'{0}.{1}'.format(table_pseudo_name, display_col)
display_col_names.append(name)
select_column_name = u"concat_ws(' '::text, {0}) AS {1}".format(
', '.join(display_col_names),
pseudo_column_name
)
else:
if not custom_tenure:
select_column_name = u'{0}.id AS {1}'.format(
table_pseudo_name,
pseudo_column_name
)
use_custom_join = True
use_inner_join = False
else:
QApplication.processEvents()
# These are outer joins
join_type = 'LEFT JOIN'
# Use inner join only if parent entity is an STR entity and it is the current entity.
# Other str entities should use left join.
if str_entity.is_str_entity(fk_parent_entity) and \
fk_parent_entity.name in view_name:
join_type = 'INNER JOIN'
if use_custom_join:
# exclude replace str name with custom tenure name in join.
if custom_tenure:
if c.name == 'social_tenure_relationship_id':
i = i + 1
# pseudo_names = foreign_key_parents.get(parent_table)
col_select_name = u'{0}_{1}.{2}'.format(
entity.name, str(i), c.name
)
# Get pseudoname to use
table_pseudo_name = u'{0}_{1}'.format(
entity.name, str(i)
)
join_statement = u'{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, entity.name, table_pseudo_name,
col_select_name,
c.entity_relation.parent_column
)
join_statements = [join_statement] + join_statements
else:
join_statement = u'{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, table_pseudo_name,
col_select_name,
c.entity_relation.parent_column
)
join_statements.append(join_statement)
else:
join_statement = u'{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, table_pseudo_name,
col_select_name, c.entity_relation.parent_column
)
join_statements.append(join_statement)
else:
# Assert if the column is in the list of omitted join columns
#
# if c.name in omit_join_statement_columns:
# if 'INNER JOIN' in join_statement:
# join_statements.append(join_statement)
# else:
# This fix needs to be tested more ...
if parent_table in foreign_key_parents and 'relationship' not in entity.name:
parent_table_alias = foreign_key_parents[parent_table][0]
join_statement = u'{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, parent_table_alias, col_select_name,
c.entity_relation.parent_column
)
else:
join_statement = u'{0} {1} ON {2} = {1}.{3}'.format(
join_type, parent_table, col_select_name,
c.entity_relation.parent_column
)
parents.append(parent_table)
join_statements.append(join_statement)
# Assert if the column is in the list of omitted view columns
if c.name not in omit_view_columns:
if select_column_name:
column_names.append(select_column_name)
QApplication.processEvents()
return column_names, join_statements
def _entity_select_column(entity,
use_inner_join=False,
join_parents=False,
is_primary=False,
foreign_key_parents=None,
omit_view_columns=None,
omit_join_statement_columns=None):
# Check if the entity exists in the database
if not pg_table_exists(entity.name):
msg = u'{0} table does not exist, social tenure view will not be ' \
u'created.'.format(entity.name)
LOGGER.debug(msg)
raise ConfigurationException(msg)
if omit_view_columns is None:
omit_view_columns = []
if omit_join_statement_columns is None:
omit_join_statement_columns = []
column_names = []
join_statements = []
columns = entity.columns.values()
# Create foreign key parent collection if none is specified
if foreign_key_parents is None:
foreign_key_parents = {}
str_entity = entity.profile.social_tenure
for c in columns:
if c.TYPE_INFO not in _exclude_view_column_types:
normalized_entity_sname = entity.short_name.replace(' ',
'_').lower()
pseudo_column_name = u'{0}_{1}'.format(normalized_entity_sname,
c.name)
col_select_name = u'{0}.{1}'.format(entity.name, c.name)
select_column_name = u'{0} AS {1}'.format(col_select_name,
pseudo_column_name)
if is_primary and c.name == 'id':
# add row number id instead of party.id
# if multi_party is allowed.
if str_entity.multi_party:
if not entity.has_geometry_column():
row_id = 'row_number() OVER () AS id'
column_names.append(row_id)
select_column_name = select_column_name
else:
# add spatial unit id as the id.
select_column_name = col_select_name
# add the social_tenure_relationship_id
str_id = u'{0}.id AS {1}_id'.format(
str_entity.name, str_entity.short_name.lower())
column_names.append(str_id)
else:
# add party_id on spatial unit view to use
# [party]_supporting_document for
# profiles with one party entity and no multi_party.
if len(str_entity.parties) == 1 and not str_entity.multi_party and \
entity.has_geometry_column():
party_id = '{}_id'.format(
str_entity.parties[0].short_name.lower().replace(
' ', '_'))
str_party_id = u'{0}.{1} AS {1}'.format(
str_entity.name, party_id)
column_names.append(str_party_id)
select_column_name = col_select_name
# if entity has a geometry column, even if not multi_party
# add social_tenure_relationship_id
if entity.has_geometry_column():
# add the social_tenure_relationship_id
str_id = u'{0}.id AS {1}_id'.format(
str_entity.name, str_entity.short_name.lower())
column_names.append(str_id)
# Use custom join flag
use_custom_join = False
if isinstance(c, ForeignKeyColumn) and join_parents:
LOGGER.debug('Creating STR: Getting parent for %s column',
c.name)
fk_parent_entity = c.entity_relation.parent
parent_table = c.entity_relation.parent.name
LOGGER.debug('Parent found')
select_column_name = ''
# Handle renaming of parent table names to appropriate
# pseudonames.
if not parent_table in foreign_key_parents:
foreign_key_parents[parent_table] = []
pseudo_names = foreign_key_parents.get(parent_table)
# Get pseudoname to use
table_pseudo_name = u'{0}_{1}'.format(parent_table,
(len(pseudo_names) + 1))
pseudo_names.append(table_pseudo_name)
# Map lookup and admin unit values by default
if c.TYPE_INFO == 'LOOKUP':
select_column_name = u'{0}.value AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
# Check if the column is for tenure type
if c.name != 'tenure_type':
use_inner_join = False
elif c.TYPE_INFO == 'ADMIN_SPATIAL_UNIT':
select_column_name = u'{0}.name AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
use_inner_join = False
# These are outer joins
join_type = 'LEFT JOIN'
# Use inner join only if parent entity is an STR entity
if use_inner_join and \
str_entity.is_str_entity(fk_parent_entity):
join_type = 'INNER JOIN'
if use_custom_join:
join_statement = u'{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, table_pseudo_name,
col_select_name, c.entity_relation.parent_column)
else:
join_statement = u'{0} {1} ON {2} = {1}.{3}'.format(
join_type, parent_table, col_select_name,
c.entity_relation.parent_column)
# Assert if the column is in the list of omitted join columns
if c.name not in omit_join_statement_columns:
join_statements.append(join_statement)
# Assert if the column is in the list of omitted view columns
if c.name not in omit_view_columns:
if select_column_name:
column_names.append(select_column_name)
return column_names, join_statements
def draw_spatial_unit(self, spatial_unit, model):
"""
Draw geometry of the given model in the respective local and web views.
:param model: Source model whose geometry will be drawn.
:type model: object
:param clear_existing: Clears any existing features prior to adding the
new features.
:type clear_existing: bool
"""
if model is None:
msg = QApplication.translate("SpatialPreview",
"Data model is empty, the spatial "
"unit cannot be rendered.")
QMessageBox.critical(self,
QApplication.translate(
"SpatialPreview",
"Spatial Unit Preview"),
msg)
return
table_name = spatial_unit.name
if not pg_table_exists(table_name):
msg = QApplication.translate("SpatialPreview",
"The spatial unit data source could "
"not be retrieved, the feature cannot "
"be rendered.")
QMessageBox.critical(
self,
QApplication.translate(
"SpatialPreview",
"Spatial Unit Preview"),
msg
)
return
sp_unit_manager = SpatialUnitManagerDockWidget(self.iface())
spatial_cols = sp_unit_manager.geom_columns(spatial_unit)
geom, geom_col = None, ""
sc_obj = None
for sc in spatial_cols:
db_geom = getattr(model, sc.name)
#Use the first non-empty geometry
# value in the collection
if not db_geom is None:
sc_obj = sc
geom_col = sc.name
geom = db_geom
QApplication.processEvents()
lyr = sp_unit_manager.geom_col_layer_name(
table_name, sc_obj
)
sp_unit_manager.add_layer_by_name(lyr)
if geom is not None:
self.highlight_spatial_unit(
spatial_unit, geom, self.local_map.canvas
)
self._web_spatial_loader.add_overlay(
model, geom_col
)
def supporting_document_exists(self):
sd_name = u'{0}_{1}_{2}'.format(self.profile.prefix,
self.entity.short_name.lower(),
'supporting_document')
return pg_table_exists(sd_name)
def _entity_select_column(entity,
use_inner_join=False,
join_parents=False,
is_primary=False,
foreign_key_parents=None):
#Check if the entity exists in the database
if not pg_table_exists(entity.name):
msg = u'{0} table does not exist, social tenure view will not be ' \
u'created.'.format(entity.name)
LOGGER.debug(msg)
raise ConfigurationException(msg)
column_names = []
join_statements = []
columns = entity.columns.values()
#Create foreign key parent collection if none is specified
if foreign_key_parents is None:
foreign_key_parents = {}
for c in columns:
if not c.TYPE_INFO in _exclude_view_column_types:
normalized_entity_sname = entity.short_name.replace(' ',
'_').lower()
pseudo_column_name = u'{0}_{1}'.format(normalized_entity_sname,
c.name)
col_select_name = u'{0}.{1}'.format(entity.name, c.name)
select_column_name = u'{0} AS {1}'.format(col_select_name,
pseudo_column_name)
if is_primary and c.name == 'id':
select_column_name = col_select_name
#Use custom join flag
use_custom_join = False
if isinstance(c, ForeignKeyColumn) and join_parents:
LOGGER.debug('Creating STR: Getting parent for %s column',
c.name)
parent_table = c.entity_relation.parent.name
LOGGER.debug('Parent found')
select_column_name = ''
#Handle renaming of parent table names to appropriate
# pseudonames.
if not parent_table in foreign_key_parents:
foreign_key_parents[parent_table] = []
pseudo_names = foreign_key_parents.get(parent_table)
#Get pseudoname to use
table_pseudo_name = u'{0}_{1}'.format(parent_table,
(len(pseudo_names) + 1))
pseudo_names.append(table_pseudo_name)
#Map lookup and admin unit values by default
if c.TYPE_INFO == 'LOOKUP':
select_column_name = u'{0}.value AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
#Check if the column is for tenure type
if c.name != 'tenure_type':
use_inner_join = False
elif c.TYPE_INFO == 'ADMIN_SPATIAL_UNIT':
select_column_name = u'{0}.name AS {1}'.format(
table_pseudo_name, pseudo_column_name)
use_custom_join = True
use_inner_join = False
#These are outer joins
join_type = 'LEFT JOIN'
if use_inner_join:
join_type = 'INNER JOIN'
if use_custom_join:
join_statement = u'{0} {1} {2} ON {3} = {2}.{4}'.format(
join_type, parent_table, table_pseudo_name,
col_select_name, c.entity_relation.parent_column)
else:
join_statement = u'{0} {1} ON {2} = {1}.{3}'.format(
join_type, parent_table, col_select_name,
c.entity_relation.parent_column)
join_statements.append(join_statement)
if select_column_name:
column_names.append(select_column_name)
return column_names, join_statements
