def csv_to_db(): file_path = '{}/.stdm/db_backup/'.format(home) for table in pg_tables(): csv_path = '{}/{}.csv'.format(file_path, table) sql = "COPY {0} FROM '{1}' DELIMITER ',' CSV HEADER".format(table, csv_path) _execute(sql)
def csv_to_db(): file_path = '{}/.stdm/db_backup/'.format(home) for table in pg_tables(): csv_path = '{}/{}.csv'.format(file_path, table) sql = "COPY {0} FROM '{1}' DELIMITER ',' CSV HEADER".format( table, csv_path) _execute(sql)
def db_to_csv(): file_path = '{}/.stdm/db_backup/'.format(home) if not os.path.isdir(file_path): os.makedirs(os.path.dirname(file_path)) statistics = os.stat(file_path) os.chmod(file_path, statistics.st_mode | stat.S_IEXEC) os.chmod(file_path, 0777) for table in pg_tables(): backup_path = '{}/{}.csv'.format(file_path, table) out_file = open(backup_path, 'wb') out_csv = csv.writer(out_file) f = open(backup_path, 'wb') sql = 'SELECT * FROM {}'.format(table) result = _execute(sql) cursor = result.cursor out_csv.writerow([x[0] for x in cursor.description]) out_csv.writerows(cursor.fetchall()) f.close()
def db_to_csv(): file_path = '{}/.stdm/db_backup/'.format(home) if not os.path.isdir(file_path): os.makedirs(os.path.dirname(file_path)) statistics = os.stat(file_path) os.chmod(file_path, statistics.st_mode | stat.S_IEXEC) os.chmod(file_path, 0777) for table in pg_tables(): backup_path = '{}/{}.csv'.format(file_path, table) out_file = open(backup_path, 'wb') out_csv = csv.writer(out_file) f = open(backup_path, 'wb') sql = 'SELECT * FROM {}'.format(table) result = _execute(sql) cursor = result.cursor out_csv.writerow([x[0] for x in cursor.description]) out_csv.writerows(cursor.fetchall()) f.close()
def upgrade_view(self, view): """ Upgrades the view with the new tables and adds new_ prefix to the view. :param view: Tee name of the view :type view: String :return: The newly created view name :rtype: String """ view_def = self.view_details(view) # exclude str view of the new configuration if view in self.old_new_tables.values(): return if not view_def is None: query_lines = view_def.splitlines() query_list = [] if 'new' in view: for q in query_lines: query_list.append(q.strip()) query = ' '.join(query_list) query = 'CREATE OR REPLACE VIEW {} AS {}'.format( view, query ) else: for q in query_lines: query_list.append( self.replace_all(q).strip() ) query = ' '.join(query_list) query = 'CREATE OR REPLACE VIEW new_{} AS {}'.format( view, query ) # convert lookup id to value query = self.covert_to_lookup_value(query) try: _execute(query, view_name=view) return 'new_{}'.format(view) except Exception as ex: self.updater.append_log(str(ex)) return None
def upgrade_view(self, view): """ Upgrades the view with the new tables and adds new_ prefix to the view. :param view: Tee name of the view :type view: String :return: The newly created view name :rtype: String """ view_def = self.view_details(view) # exclude str view of the new configuration if view in self.old_new_tables.values(): return if not view_def is None: query_lines = view_def.splitlines() query_list = [] if 'new' in view: for q in query_lines: query_list.append(q.strip()) query = ' '.join(query_list) query = 'CREATE OR REPLACE VIEW {} AS {}'.format( view, query ) else: for q in query_lines: query_list.append( self.replace_all(q).strip() ) query = ' '.join(query_list) query = 'CREATE OR REPLACE VIEW new_{} AS {}'.format( view, query ) # convert lookup id to value query = self.covert_to_lookup_value(query) try: _execute(query, view_name=view) return 'new_{}'.format(view) except Exception as ex: self.updater.append_log(str(ex)) return None
def base_roles(self, table_name): results = set() sql = "Select grantee from information_schema.role_table_grants "\ "where table_name = '{}' ".format(table_name) records = _execute(sql) for record in records: results.add(record['grantee']) return results
def pg_certificate_number(): """ Get flts certificate view from the database """ t = text('SELECT flts_gen_cert_number();') result = _execute(t) cert_number = '' for row in result: cert_number = row[0] return cert_number
def base_roles(self, table_name): results = set() sql = "Select grantee from information_schema.role_table_grants " \ "where table_name = '{}' ".format(table_name) records = _execute(sql) for record in records: results.add(record['grantee']) return results
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 view_details(self, view): """ Gets the view definition/query used to create it. :param view: The name of the view. :type view: String :return: The definition/query. :rtype: String """ if view in pg_views(): t = text('SELECT definition ' 'FROM pg_views ' 'WHERE viewname=:view_name;') result = _execute(t, view_name=view) definition = [] for row in result: definition.append(row[0]) return definition[0]
def view_details(self, view): """ Gets the view definition/query used to create it. :param view: The name of the view. :type view: String :return: The definition/query. :rtype: String """ if view in pg_views(): t = text('SELECT definition ' 'FROM pg_views ' 'WHERE viewname=:view_name;' ) result = _execute(t, view_name=view) definition = [] for row in result: definition.append(row[0]) return definition[0]
def populate_roles(self): """ Inherit existing privileges for a given role. These are to be granted to the new content Structure of the roles dictionary: roles['manager'] = ['Select', 'Create'] """ stmt = "SELECT content_base.id, content_base.name AS content, " \ "content_roles.role_id, role.name AS role " \ " FROM content_base, content_roles, role " \ " WHERE content_roles.role_id = role.id " \ " AND content_base.id = content_roles.content_base_id " \ " AND content_base.name like '%%{}' " \ " ORDER BY role.name ".format(self.content_name) records = _execute(stmt) for row in records: content = row['content'] role = row['role'] if role == 'postgres': continue if role not in self.roles: self.roles[role] = [] self.roles[role].append(content[:content.index(' ')])
def populate_roles(self): """ Inherit existing privileges for a given role. These are to be granted to the new content Structure of the roles dictionary: roles['manager'] = ['Select', 'Create'] """ stmt = "SELECT content_base.id, content_base.name AS content, " \ "content_roles.role_id, role.name AS role " \ " FROM content_base, content_roles, role " \ " WHERE content_roles.role_id = role.id " \ " AND content_base.id = content_roles.content_base_id " \ " AND content_base.name like '%%{}' " \ " ORDER BY role.name ".format(self.content_name) records = _execute(stmt) for row in records: content = row['content'] role = row['role'] if role == 'postgres':continue if role not in self.roles: self.roles[role] = [] self.roles[role].append(content[:content.index(' ')])
def grant_or_revoke(self, action, privilege, table, role): gr_str = 'TO' if action == 'GRANT' else 'FROM' stmt = '{} {} ON TABLE {} {} {}'.format(action, privilege, table, gr_str, role) _execute(stmt)
def grant_or_revoke(self, action, privilege, table, role): gr_str = 'TO' if action == 'GRANT' else 'FROM' stmt = '{} {} ON TABLE {} {} {}'.format(action, privilege, table, gr_str, role) _execute(stmt)
def _create_primary_entity_view(social_tenure, primary_entity, view_name, distinct_column=None): """ Creates a basic view for the given primary entity. :param social_tenure: :param primary_entity: :param view_name: :param distinct_column: """ # 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 = {} omit_view_columns = [] omit_join_statement_columns = [] party_col_names = deepcopy(social_tenure.party_columns.keys()) # Flag to check if primary entity is a spatial unit entity pe_is_spatial = False # Check if the primary entity is a party in the STR collection if not social_tenure.is_str_party_entity(primary_entity): pe_is_spatial = True else: p_fk_col_name = u'{0}_id'.format(primary_entity.short_name.lower()) # Exclude other parties from the join statement if p_fk_col_name in party_col_names: party_col_names.remove(p_fk_col_name) # Create the SQL statement WRT the primary entity str_columns, str_join = _entity_select_column( social_tenure, True, True, foreign_key_parents=fk_parent_names, omit_join_statement_columns=party_col_names) party_columns, party_join = [], [] # Omit party entities in the spatial unit join if not pe_is_spatial: party_columns, party_join = _entity_select_column( primary_entity, True, True, True, foreign_key_parents=fk_parent_names, omit_view_columns=omit_view_columns, omit_join_statement_columns=omit_join_statement_columns) # Set removal of all spatial unit columns apart from the id column omit_view_columns = deepcopy(social_tenure.spatial_unit.columns.keys()) if 'id' in omit_view_columns: omit_view_columns.remove('id') else: # Set id column to be distinct distinct_column = '{0}.id'.format(primary_entity.name) # Omit STR columns if primary entity is spatial unit str_columns = [] spatial_unit_columns, spatial_unit_join = _entity_select_column( social_tenure.spatial_unit, True, join_parents=True, is_primary=pe_is_spatial, foreign_key_parents=fk_parent_names, omit_view_columns=omit_view_columns, omit_join_statement_columns=omit_join_statement_columns) view_columns = party_columns + str_columns + spatial_unit_columns # Set distinct column if specified if not distinct_column is None: view_columns = _set_distinct_column(distinct_column, view_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 _create_primary_entity_view( social_tenure, primary_entity, view_name, distinct_column=None ): """ Creates a basic view for the given primary entity. :param social_tenure: :param primary_entity: :param view_name: :param distinct_column: """ # 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 = {} omit_view_columns = [] omit_join_statement_columns = [] party_col_names = deepcopy(social_tenure.party_columns.keys()) # Flag to check if primary entity is a spatial unit entity pe_is_spatial = False # Check if the primary entity is a party in the STR collection if not social_tenure.is_str_party_entity(primary_entity): pe_is_spatial = True else: p_fk_col_name = u'{0}_id'.format(primary_entity.short_name.lower()) # Exclude other parties from the join statement if p_fk_col_name in party_col_names: party_col_names.remove(p_fk_col_name) # Create the SQL statement WRT the primary entity str_columns, str_join = _entity_select_column( social_tenure, True, True, foreign_key_parents=fk_parent_names, omit_join_statement_columns=party_col_names, view_name = view_name ) party_columns, party_join = [], [] # Omit party entities in the spatial unit join #### Create party views if not pe_is_spatial: party_columns, party_join = _entity_select_column( primary_entity, True, True, True, foreign_key_parents=fk_parent_names, omit_view_columns=omit_view_columns, omit_join_statement_columns=omit_join_statement_columns, view_name = view_name ) # Set removal of all spatial unit columns apart from the id column for spatial_unit in social_tenure.spatial_units: omit_view_columns = deepcopy(spatial_unit.columns.keys()) if 'id' in omit_view_columns: omit_view_columns.remove('id') view_columns = party_columns + str_columns # Set distinct column if specified if not distinct_column is None: view_columns = _set_distinct_column(distinct_column, view_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) _execute(normalized_create_view_sql) else: # Set id column to be distinct distinct_column = '{0}.id'.format(primary_entity.name) # Omit STR columns if primary entity is spatial unit # str_columns = [] #for spatial_unit in social_tenure.spatial_units: ### Create spatial_unit views if pe_is_spatial: spatial_unit_columns, spatial_unit_join = _entity_select_column( primary_entity, True, join_parents=True, is_primary=pe_is_spatial, foreign_key_parents=fk_parent_names, omit_view_columns=omit_view_columns, omit_join_statement_columns=omit_join_statement_columns, view_name = view_name ) custom_tenure_columns = [] custom_tenure_join = [] custom_tenure_entity = social_tenure.spu_custom_attribute_entity( primary_entity ) if custom_tenure_entity is not None: custom_tenure_columns, custom_tenure_join = _entity_select_column( custom_tenure_entity, True, join_parents=True, is_primary=False, foreign_key_parents=fk_parent_names, omit_view_columns=omit_view_columns, omit_join_statement_columns=omit_join_statement_columns, view_name=view_name ) view_columns = spatial_unit_columns + str_columns + custom_tenure_columns # Set distinct column if specified if not distinct_column is None: view_columns = _set_distinct_column(distinct_column, view_columns) join_statement = str_join + spatial_unit_join + custom_tenure_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) _execute(normalized_create_view_sql)