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)
