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 _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 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