def checkExtentDir(self):
taxonExtentDir = self.extentDir.get()
if os.path.isdir(taxonExtentDir):
dbConn = getDbConnection(
optparse.Values(self.dbPane.getDbOptions()))
try:
for subdir in [x[0] for x in os.walk(taxonExtentDir)]:
if subdir != taxonExtentDir:
for shpFile in [
f for f in os.listdir(subdir)
if f.endswith('.shp')
]:
match = self.shapeFilePattern.match(shpFile)
if match:
taxonKey = int(match.groups()[0])
taxons = dbConn.execute(
"SELECT scientific_name AS taxon_name " +
" FROM master.taxon WHERE taxon_key = %s"
% taxonKey)
for taxon in taxons:
foundInDirTaxonName = path.basename(subdir)
if taxon.taxon_name != foundInDirTaxonName.replace(
"_", " "):
print(
"Taxon %s's name %s does not match with subdir %s"
% (taxonKey, taxon.taxon_name,
foundInDirTaxonName))
finally:
if dbConn:
dbConn.close()
def processYearPartition(self, year):
opts = self.dbPane.getDbOptions()
try:
dbSession = getDbConnection(optparse.Values(opts)).getSession()
dbSession.execute(
"TRUNCATE TABLE allocation.allocation_data_partition_udi")
dbSession.execute(
"SELECT allocation.populate_allocation_data_partition_udi(%s)"
% year)
dbSession.execute(
"VACUUM ANALYZE allocation.allocation_data_partition_udi")
opts['sqlfile'] = None
opts[
'sqlcmd'] = "SELECT allocation.generate_insert_cell_catch_partition_statements(%s)" % year
opts['threads'] = 16
sp.process(optparse.Values(opts))
# Post insertions operation to finalize the target cell catch partition for immediate use
cellCatchPartition = "cell_catch_p%s" % year
dbSession.execute("VACUUM ANALYZE web_partition.%s" %
cellCatchPartition)
for indexSql in dbSession.execute(
"SELECT web_partition.maintain_cell_catch_indexes('%s') AS cmd"
% cellCatchPartition).fetchall():
dbSession.execute(indexSql.cmd)
finally:
dbSession.close()
def processExtentDir(self):
taxonExtentDir = self.extentDir.get()
if os.path.isdir(taxonExtentDir):
opts = self.dbPane.getDbOptions()
rawConn = getDbConnection(
optparse.Values(opts)).getSession().connection().connection
cursor = rawConn.cursor()
try:
for subdir in [x[0] for x in os.walk(taxonExtentDir)]:
if subdir != taxonExtentDir:
for shpFile in [
f for f in os.listdir(subdir)
if f.endswith('.shp')
]:
match = self.shapeFilePattern.match(shpFile)
if match:
self.processExtentShapeFile(
cursor, path.join(subdir, shpFile),
match.groups())
rawConn.commit()
print("Vacuuming distribution.taxon_extent afterward...")
cursor.execute("vacuum analyze distribution.taxon_extent")
rawConn.commit()
cursor.close()
finally:
if rawConn:
rawConn.close()
def postAggregationOperations(self, summaryTable):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
print("Updating allocation data unit price...")
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
opts['sqlfile'] = "sql/update_allocation_data_unit_price.sql"
sp.process(optparse.Values(opts))
dbConn.execute(
"UPDATE allocation.allocation_data SET unit_price = %s WHERE unit_price IS NULL"
% SummarizeCommandPane.GLOBAL_AVERAGE_UNIT_PRICE)
dbConn.execute("VACUUM ANALYZE allocation.allocation_data")
print("Vacuum and analyze target summary table(s)...")
if summaryTable:
dbConn.execute("VACUUM ANALYZE allocation.%s" % summaryTable)
else:
# if input summaryTable = None, it's really the signal to vacuum analyze all summary tables
for tab in SummarizeCommandPane.SUMMARY_TABLES:
if tab:
dbConn.execute("VACUUM ANALYZE allocation.%s" % tab)
print("Summarization process completed...")
dbConn.close()
def setupCommandPane(self):
if not self.mainDbPane.isConnectionTestedSuccessfully():
popup_message("Connection not yet tested",
"The Main DB Connection has not been tested successfully.\n" +
"Once the Main DB Connection has been tested successfully, you can click that button again.")
return
if self.sourceDbPane.db_type.get() != 'sqlserver':
popup_message("DB Connection not SQL Server",
"The Source DB Connection should be a SQL Server instance.\n" +
"Once the Source DB Connection has been re-configured, you can click that button again.")
return
for child in self.cmdFrame.winfo_children(): child.destroy()
dbConn = getDbConnection(optparse.Values(self.mainDbPane.getDbOptions()))
self.dbSession = dbConn.getSession()
self.dataTransfer = self.dbSession.query(DataTransfer).filter_by(target_schema_name='allocation').order_by(DataTransfer.id).all()
add_buttons(self.cmdFrame,
[[tab.target_table_name, partial(self.processTable, tab), "blue"] for tab in self.dataTransfer],
0, 0, "horizontal")
add_buttons(self.cmdFrame,
[["Pull all allocation db tables", self.pullAllAllocationData, "red"],
["Drop foreign keys", partial(drop_foreign_key, self.mainDbPane), "red"],
["Restore foreign keys", partial(restore_foreign_key, self.mainDbPane), "red"]],
1, 0, "horizontal")
grid_panel(self.cmdFrame)
def postAggregationOperations(self):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 2
print("Merging Unknown fishing entity in catch data...")
dbConn.execute(
"UPDATE web.v_fact_data SET fishing_entity_id = 213 WHERE fishing_entity_id = 223"
)
print("Vacuuming v_fact_data afterward...")
dbConn.execute("vacuum analyze web.v_fact_data")
# And now refresh all materialized views as most are dependent on data in the v_fact_data table
opts[
'sqlcmd'] = "SELECT 'refresh materialized view web.' || table_name FROM matview_v('web') WHERE table_name NOT LIKE 'TOTALS%'"
sp.process(optparse.Values(opts))
opts[
'sqlcmd'] = "SELECT 'vacuum analyze web.' || table_name FROM matview_v('web') WHERE table_name NOT LIKE 'TOTALS%'"
sp.process(optparse.Values(opts))
print("Aggregation process completed...")
dbConn.close()
def postAggregationOperations(self, summaryTable):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
# Not necessary to update price in the allocation data table because the price will be added in the aggregate step to account for end use type
# print("Updating allocation data unit price...")
# if 'threads' not in opts or opts['threads'] == 0:
# opts['threads'] = 8
# opts['sqlfile'] = "sql/update_allocation_data_unit_price.sql"
# #Uncomment on January 2019
# #opts['sqlfile'] = "sql/layer3gear_update.sql"
# #print("Changing layer3gear to sau_gear")
sp.process(optparse.Values(opts))
# # dbConn.execute("UPDATE allocation.allocation_data SET unit_price = %s WHERE unit_price IS NULL" % SummarizeCommandPane.GLOBAL_AVERAGE_UNIT_PRICE)
dbConn.execute("VACUUM ANALYZE allocation.allocation_data")
print("Vacuum and analyze target summary table(s)...")
if summaryTable:
dbConn.execute("VACUUM ANALYZE allocation.%s" % summaryTable)
else:
# if input summaryTable = None, it's really the signal to vacuum analyze all summary tables
for tab in SummarizeCommandPane.SUMMARY_TABLES:
if tab:
dbConn.execute("VACUUM ANALYZE allocation.%s" % tab)
print("Summarization process completed...")
dbConn.close()
def setupCommandPane(self):
if not self.mainDbPane.isConnectionTestedSuccessfully():
popup_message("Connection not yet tested",
"The Main DB Connection has not been tested successfully.\n" +
"Once the Main DB Connection has been tested successfully, you can click that button again.")
return
for child in self.cmdFrame.winfo_children(): child.destroy()
sourceDbOpts = optparse.Values(self.sourceDbPane.getDbOptions())
self.sourceDbSession = getDbConnection(sourceDbOpts).getSession()
self.mainDbSession = getDbConnection(optparse.Values(self.mainDbPane.getDbOptions())).getSession()
self.dataTransfer = self.mainDbSession.query(DataTransfer).filter(func.lower(DataTransfer.source_database_name)==func.lower(sourceDbOpts.dbname)) \
.order_by(DataTransfer.id).all()
button_data = []
column=0
row=0
for tab in self.dataTransfer:
button_data.append([tab.target_table_name, partial(self.processTable, tab), "blue"])
column += 1
if column >= self.buttonsPerRow:
add_buttons(self.cmdFrame, button_data, row, 0, "horizontal")
button_data = []
column = 0
row += 1
if button_data != []:
add_buttons(self.cmdFrame, button_data, row, 0, "horizontal")
row += 1
if not self.silentMode:
row = add_buttons(self.cmdFrame,
[["Pull all integration db tables", self.pullAllIntegrationDbData, "red"],
["Drop foreign keys", partial(drop_foreign_key, self.mainDbPane), "red"],
["Restore foreign keys", partial(restore_foreign_key, self.mainDbPane), "red"]],
row, 0, "horizontal")
grid_panel(self.cmdFrame)
def getAirconModeId(stateValue):
conn = db.getDbConnection()
with conn:
cur = conn.cursor()
# Get Stateid for specified stateValue
sql = "SELECT StateId FROM REFERENCE_AIRCON_MODE WHERE Value = :value"
cur.execute(sql, { "value": stateValue })
return cur.fetchone()[0]
def kickoffSqlProcessor(self, summaryTable, isPostOpsRequired=True):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
dbConn.execute("TRUNCATE allocation.%s" % summaryTable)
opts['sqlfile'] = "sql/summarize_%s.sql" % summaryTable
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
sp.process(optparse.Values(opts))
if isPostOpsRequired:
self.postAggregationOperations(summaryTable)
def kickoffSqlProcessor(self, summaryTable, isPostOpsRequired=True):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
dbConn.execute("TRUNCATE allocation.%s" % summaryTable)
opts['sqlfile'] = "sql/summarize_%s.sql" % summaryTable
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
sp.process(optparse.Values(opts))
if isPostOpsRequired:
self.postAggregationOperations(summaryTable)
def recreateIndexes(self):
if len(self.index_create_cmds) == 0:
popup_message("Prior index drop",
"A prior execution of index drop has not been detected. This invocation is aborted.")
return
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
for index_create_cmd in self.index_create_cmds:
dbConn.execute(index_create_cmd)
dbConn.close()
def getReferenceCodeAndValue(table, typeName, makerName):
conn = db.getDbConnection()
with conn:
cur = conn.cursor()
# Get code and human readable value for specified code type and maker
sql = "\
SELECT Code, CodeValue\
FROM " + table + "\
WHERE TypeName = :type\
AND MakerName = :maker"
cur.execute(sql, { "type": typeName, "maker": makerName })
return cur.fetchone()
def recreateIndexes(self):
if len(self.index_create_cmds) == 0:
popup_message(
"Prior index drop",
"A prior execution of index drop has not been detected. This invocation is aborted."
)
return
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
for index_create_cmd in self.index_create_cmds:
dbConn.execute(index_create_cmd)
dbConn.close()
def run(self):
dbconn = getDbConnection(self.options)
proc_name = self.name
while True:
next_cmd = self.cmd_queue.get()
if next_cmd is None:
# Poison pill means we should exit
print('%s: Exiting' % proc_name)
break
print('%s: %s' % (proc_name, next_cmd))
dbconn.execute(next_cmd)
return
dbconn.close()
def run(self):
dbconn = getDbConnection(self.options)
proc_name = self.name
while True:
next_cmd = self.cmd_queue.get()
if next_cmd is None:
# Poison pill means we should exit
print('%s: Exiting' % proc_name)
break
print('%s: %s' % (proc_name, next_cmd))
dbconn.execute(next_cmd)
return
dbconn.close()
def process(self, entity_layer_id):
if not self.dbPane.isConnectionTestedSuccessfully():
popup_message("Connection not yet tested",
"The DB Connection has not been tested successfully.\n" +
"Once the DB Connection has been tested successfully, you can click the Process button again.")
return
dbOpts = self.dbPane.getDbOptions()
dbSession = getDbConnection(optparse.Values(dbOpts)).getSession()
dbSession.execute("SELECT * FROM web_cache.maintain_catch_csv_partition(%s)" % entity_layer_id)
dbOpts['sqlfile'] = "sql/populate_catch_data_in_csv.sql"
dbOpts['sqlcmd'] = "select format('vacuum analyze web_cache.%s', table_name) from schema_v('web_cache') where table_name not like 'TOTAL%'"
dbOpts['threads'] = 4
sp.process(optparse.Values(dbOpts))
def dropIndexes(self):
if len(self.index_create_cmds) > 0:
popup_message("Index drop previously executed",
"A prior execution of index drop has been detected. This invocation is aborted.")
return
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
indexes = dbConn.execute("SELECT (schemaname || '.' || indexname) index_name, indexdef index_create_cmd \
FROM pg_indexes \
WHERE tablename = 'v_fact_data' AND indexname NOT LIKE 'v_fact_data_pkey'")
for index in indexes:
self.index_create_cmds.append(index.index_create_cmd)
dbConn.execute("DROP INDEX %s" % index.index_name)
dbConn.close()
def setupCommandPane(self):
if not self.mainDbPane.isConnectionTestedSuccessfully():
popup_message(
"Connection not yet tested",
"The Main DB Connection has not been tested successfully.\n" +
"Once the Main DB Connection has been tested successfully, you can click that button again."
)
return
if self.sourceDbPane.db_type.get() != 'sqlserver':
popup_message(
"DB Connection not SQL Server",
"The Source DB Connection should be a SQL Server instance.\n" +
"Once the Source DB Connection has been re-configured, you can click that button again."
)
return
for child in self.cmdFrame.winfo_children():
child.destroy()
dbConn = getDbConnection(
optparse.Values(self.mainDbPane.getDbOptions()))
self.dbSession = dbConn.getSession()
self.dataTransfer = self.dbSession.query(DataTransfer).filter_by(
target_schema_name='allocation').order_by(DataTransfer.id).all()
add_buttons(
self.cmdFrame,
[[tab.target_table_name,
partial(self.processTable, tab), "blue"]
for tab in self.dataTransfer], 0, 0, "horizontal")
add_buttons(self.cmdFrame,
[[
"Pull all allocation db tables",
self.pullAllAllocationData, "red"
],
[
"Drop foreign keys",
partial(drop_foreign_key, self.mainDbPane), "red"
],
[
"Restore foreign keys",
partial(restore_foreign_key, self.mainDbPane), "red"
]], 1, 0, "horizontal")
grid_panel(self.cmdFrame)
def drop_foreign_key(dbPane):
dbConn = getDbConnection(optparse.Values(dbPane.getDbOptions()))
dbConn.execute("TRUNCATE TABLE admin.database_foreign_key")
dbConn.execute(
"WITH db(db_owner) AS (" + "SELECT u.usename " +
" FROM pg_database d" +
" JOIN pg_user u ON (u.usesysid = d.datdba)" +
" WHERE d.datname = current_database()" + ")" +
"INSERT INTO admin.database_foreign_key(drop_fk_cmd, add_fk_cmd) " +
"SELECT f.* " +
" FROM get_foreign_key_cmd_by_db_owner((SELECT db_owner FROM db)) AS f"
+ " WHERE COALESCE(f.drop_fk_cmd, '') <> ''")
dbConn.execute("SELECT exec(drop_fk_cmd) FROM admin.database_foreign_key")
print("Foreign keys successfully dropped.")
def getReference(table):
conn = db.getDbConnection()
with conn:
cur = conn.cursor()
# Get NEC code for lights
sql = "SELECT * FROM " + table
cur.execute(sql)
rows = cur.fetchall()
map = []
for r in rows:
row = { r[0]: r[1] }
map.append(row)
return json.dumps(map)
def drop_foreign_key(dbPane):
dbConn = getDbConnection(optparse.Values(dbPane.getDbOptions()))
dbConn.execute("TRUNCATE TABLE admin.database_foreign_key")
dbConn.execute("WITH db(db_owner) AS (" +
"SELECT u.usename " +
" FROM pg_database d" +
" JOIN pg_user u ON (u.usesysid = d.datdba)" +
" WHERE d.datname = current_database()" +
")" +
"INSERT INTO admin.database_foreign_key(drop_fk_cmd, add_fk_cmd) " +
"SELECT f.* " +
" FROM get_foreign_key_cmd_by_db_owner((SELECT db_owner FROM db)) AS f" +
" WHERE COALESCE(f.drop_fk_cmd, '') <> ''")
dbConn.execute("SELECT exec(drop_fk_cmd) FROM admin.database_foreign_key")
print("Foreign keys successfully dropped.")
def getReferenceCode(table, state, makerName, hasInputMask=False):
conn = db.getDbConnection()
with conn:
cur = conn.cursor()
# Get NEC code for lights
sql = "\
SELECT Code\
FROM " + table + "\
WHERE StateId = :state\
AND MakerName = :maker"
cur.execute(sql, { "state": state, "maker": makerName })
code = cur.fetchone()[0]
if (hasInputMask):
return code
else:
return code.decode("hex")
def simplifyExtent(self):
taxonKey = int(self.taxonKeyToSimplify.get())
print("Simplifying taxon: %s" % taxonKey)
dbConn = getDbConnection(optparse.Values(self.dbPane.getDbOptions()))
dbConn.execute((
"WITH ext(taxon_key, geom) AS (" +
" SELECT e.taxon_key, (st_dump(geom)).geom" +
" FROM distribution.taxon_extent e" +
" WHERE e.taxon_key = %(tk)s" + ")" +
"UPDATE distribution.taxon_extent e" +
" SET geom = (SELECT ST_MULTI(ST_Union(ST_Buffer(ST_SimplifyPreserveTopology(geom, 0.01), 0.25)))"
+ " FROM ext" +
" GROUP BY ext.taxon_key)" +
" WHERE e.taxon_key = %(tk)s") % {"tk": taxonKey})
print("Taxon %s simplified. Please review resulting extent." %
taxonKey)
def dropIndexes(self):
if len(self.index_create_cmds) > 0:
popup_message(
"Index drop previously executed",
"A prior execution of index drop has been detected. This invocation is aborted."
)
return
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
indexes = dbConn.execute(
"SELECT (schemaname || '.' || indexname) index_name, indexdef index_create_cmd \
FROM pg_indexes \
WHERE tablename = 'v_fact_data' AND indexname NOT LIKE 'v_fact_data_test_pkey'"
)
for index in indexes:
self.index_create_cmds.append(index.index_create_cmd)
dbConn.execute("DROP INDEX %s" % index.index_name)
dbConn.close()
def postAggregationOperations(self):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
print("Merging Unknown fishing entity in catch data...")
dbConn.execute("UPDATE web.v_fact_data SET fishing_entity_id = 213 WHERE fishing_entity_id = 223")
print("Vacuuming v_fact_data afterward...")
dbConn.execute("vacuum analyze web.v_fact_data")
# And now refresh all materialized views as most are dependent on data in the v_fact_data table
opts['sqlcmd'] = "SELECT 'refresh materialized view web.' || table_name FROM matview_v('web') WHERE table_name NOT LIKE 'TOTALS%'"
sp.process(optparse.Values(opts))
opts['sqlcmd'] = "SELECT 'vacuum analyze web.' || table_name FROM matview_v('web') WHERE table_name NOT LIKE 'TOTALS%'"
sp.process(optparse.Values(opts))
print("Aggregation process completed...")
dbConn.close()
def process(self, entity_layer_id):
if not self.dbPane.isConnectionTestedSuccessfully():
popup_message(
"Connection not yet tested",
"The DB Connection has not been tested successfully.\n" +
"Once the DB Connection has been tested successfully, you can click the Process button again."
)
return
dbOpts = self.dbPane.getDbOptions()
dbSession = getDbConnection(optparse.Values(dbOpts)).getSession()
dbSession.execute(
"SELECT * FROM web_cache.maintain_catch_csv_partition(%s)" %
entity_layer_id)
dbOpts['sqlfile'] = "sql/populate_catch_data_in_csv.sql"
dbOpts[
'sqlcmd'] = "select format('vacuum analyze web_cache.%s', table_name) from schema_v('web_cache') where table_name not like 'TOTAL%'"
dbOpts['threads'] = 1
sp.process(optparse.Values(dbOpts))
def processYearPartition(self, year):
opts = self.dbPane.getDbOptions()
try:
dbSession = getDbConnection(optparse.Values(opts)).getSession()
dbSession.execute("TRUNCATE TABLE allocation.allocation_data_partition_udi")
dbSession.execute("SELECT allocation.populate_allocation_data_partition_udi(%s)" % year)
dbSession.execute("VACUUM ANALYZE allocation.allocation_data_partition_udi")
opts['sqlfile'] = None
opts['sqlcmd'] = "SELECT allocation.generate_insert_cell_catch_partition_statements(%s)" % year
opts['threads'] = 16
sp.process(optparse.Values(opts))
# Post insertions operation to finalize the target cell catch partition for immediate use
cellCatchPartition = "cell_catch_p%s" % year
dbSession.execute("VACUUM ANALYZE web_partition.%s" % cellCatchPartition)
for indexSql in dbSession.execute(
"SELECT web_partition.maintain_cell_catch_indexes('%s') AS cmd" % cellCatchPartition).fetchall():
dbSession.execute(indexSql.cmd)
finally:
dbSession.close()
def simplifyExtent(self):
taxonKey = int(self.taxonKeyToSimplify.get())
print("Simplifying taxon: %s" % taxonKey)
dbConn = getDbConnection(optparse.Values(self.dbPane.getDbOptions()))
dbConn.execute(
("WITH ext(taxon_key, geom) AS (" +
" SELECT e.taxon_key, (st_dump(geom)).geom" +
" FROM distribution.taxon_extent e" +
" WHERE e.taxon_key = %(tk)s" +
")" +
"UPDATE distribution.taxon_extent e" +
" SET geom = (SELECT ST_MULTI(ST_Union(ST_Buffer(ST_SimplifyPreserveTopology(geom, 0.01), 0.25)))" +
" FROM ext" +
" GROUP BY ext.taxon_key)" +
" WHERE e.taxon_key = %(tk)s")
% {"tk": taxonKey}
)
print("Taxon %s simplified. Please review resulting extent." % taxonKey)
def checkExtentDir(self):
taxonExtentDir = self.extentDir.get()
if os.path.isdir(taxonExtentDir):
dbConn = getDbConnection(optparse.Values(self.dbPane.getDbOptions()))
try:
for subdir in [x[0] for x in os.walk(taxonExtentDir)]:
if subdir != taxonExtentDir:
for shpFile in [f for f in os.listdir(subdir) if f.endswith('.shp')]:
match = self.shapeFilePattern.match(shpFile)
if match:
taxonKey = int(match.groups()[0])
taxons = dbConn.execute("SELECT scientific_name AS taxon_name " +
" FROM master.taxon WHERE taxon_key = %s"
% taxonKey)
for taxon in taxons:
foundInDirTaxonName = path.basename(subdir)
if taxon.taxon_name != foundInDirTaxonName.replace("_", " "):
print("Taxon %s's name %s does not match with subdir %s"
% (taxonKey, taxon.taxon_name, foundInDirTaxonName))
finally:
if dbConn:
dbConn.close()
def processExtentDir(self):
taxonExtentDir = self.extentDir.get()
if os.path.isdir(taxonExtentDir):
opts = self.dbPane.getDbOptions()
rawConn = getDbConnection(optparse.Values(opts)).getSession().connection().connection
cursor = rawConn.cursor()
try:
for subdir in [x[0] for x in os.walk(taxonExtentDir)]:
if subdir != taxonExtentDir:
for shpFile in [f for f in os.listdir(subdir) if f.endswith('.shp')]:
match = self.shapeFilePattern.match(shpFile)
if match:
self.processExtentShapeFile(cursor, path.join(subdir, shpFile), match.groups())
rawConn.commit()
print("Vacuuming distribution.taxon_extent afterward...")
cursor.execute("vacuum analyze distribution.taxon_extent")
rawConn.commit()
cursor.close()
finally:
if rawConn:
rawConn.close()
def postAggregationOperations(self, summaryTable):
opts = self.dbPane.getDbOptions()
dbConn = getDbConnection(optparse.Values(opts))
print("Updating allocation data unit price...")
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
opts['sqlfile'] = "sql/update_allocation_data_unit_price.sql"
sp.process(optparse.Values(opts))
dbConn.execute("UPDATE allocation.allocation_data SET unit_price = %s WHERE unit_price IS NULL" % SummarizeCommandPane.GLOBAL_AVERAGE_UNIT_PRICE)
dbConn.execute("VACUUM ANALYZE allocation.allocation_data")
print("Vacuum and analyze target summary table(s)...")
if summaryTable:
dbConn.execute("VACUUM ANALYZE allocation.%s" % summaryTable)
else:
# if input summaryTable = None, it's really the signal to vacuum analyze all summary tables
for tab in SummarizeCommandPane.SUMMARY_TABLES:
if tab:
dbConn.execute("VACUUM ANALYZE allocation.%s" % tab)
print("Summarization process completed...")
dbConn.close()
def setupCommandPane(self):
if not self.dbPane.isConnectionTestedSuccessfully():
popup_message("Connection not yet tested",
"The Main DB Connection has not been tested successfully.\n" + \
"Once the Main DB Connection has been tested successfully, you can click that button again.")
return
for child in self.cmdFrame.winfo_children():
child.destroy()
i = 0
row = 0
column = 0
try:
opts = self.dbPane.getDbOptions()
dbSession = getDbConnection(optparse.Values(opts)).getSession()
# Rebuild the allocation_data partitions to make sure we are using the freshest allocation data
partitions = dbSession.execute(
"SELECT ('allocation_data_partition.' || table_name) AS partition_name"
+ " FROM schema_v('allocation_data_partition') " +
" WHERE table_name NOT LIKE 'TOTALS%'" +
" ORDER BY 1").fetchall()
for partition in partitions:
dbSession.execute("DROP TABLE %s" % partition.partition_name)
dbSession.execute(
"SELECT allocation.maintain_allocation_data_partition()")
opts['sqlfile'] = "sql/insert_allocation_data_eez_hs.sql"
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
sp.process(optparse.Values(opts))
# Add buttons to command pane
self.yearList = dbSession.execute(
"SELECT replace(table_name, 'allocation_data_', '')::INT AS year "
+ " FROM schema_v('allocation_data_partition') " +
" WHERE table_name NOT LIKE 'TOTALS%'" +
" ORDER BY 1").fetchall()
row = 0
button_data = []
for par in self.yearList:
button_data.append([
par.year,
partial(self.processYearPartition, par.year), "blue"
])
column += 1
if column >= self.buttonsPerRow:
add_buttons(self.cmdFrame, button_data, row, 0,
"horizontal")
button_data = []
column = 0
row += 1
if button_data != []:
add_buttons(self.cmdFrame, button_data, row, 0, "horizontal")
row += 1
# Calling maintain cell catch just in case the cell catch partitions are not present for any reason
dbSession.execute(
"SELECT web_partition.maintain_cell_catch_partition()")
finally:
dbSession.close()
for child in self.cmdFrame.winfo_children():
child.grid_configure(padx=5, pady=5)
if self.aggregateAllBt == None:
self.aggregateAllBt = tk.Button(
self.parent,
text="Aggregate All Year Partitions",
fg="red",
command=self.aggregateAllPartition,
height=1)
self.parent.add(self.aggregateAllBt)
# Adding a filler pane for look only
self.fillerPane = ttk.Panedwindow(self.parent, orient=VERTICAL)
self.parent.add(self.fillerPane)
def rollupExtent(self):
if not self.taxonLevelToRollupFor or self.taxonLevelToRollupFor == 0:
print("Taxon level should be between 1 and 5. Please try again.")
return
print("Processing input taxon level: %s" %
(self.taxonLevelToRollupFor.get()))
dbConn = getDbConnection(optparse.Values(self.dbPane.getDbOptions()))
try:
# First off refresh the materialized views that we rely on to indicate which taxon has/has not an extent already
self.refreshMaterializedViews(dbConn)
# Get the list of target taxon keys to rollup for the input taxon level
rollups = dbConn.execute("SELECT * FROM distribution.get_rollup_taxon_list(%s::int) ORDER BY children_distributions_found" \
% int(self.taxonLevelToRollupFor.get()))
curTaxonKey = None
for rollup in rollups:
curTaxonKey = rollup.taxon_key
childrenTaxa = rollup.children_taxon_keys
print("Rollup for %s using lower-level taxons %s [%s]" %
(curTaxonKey, childrenTaxa,
datetime.now().strftime('%Y/%m/%d %H:%M:%S')))
try:
if len(childrenTaxa) == 1:
dbConn.execute((
"INSERT INTO distribution.taxon_extent(taxon_key, is_extended, is_rolled_up, geom) "
+
"SELECT %s, is_extended, TRUE, geom FROM distribution.taxon_extent WHERE taxon_key = %s"
) % (curTaxonKey, childrenTaxa[0]))
else:
last_seq = dbConn.execute(
"SELECT f.seq FROM distribution.extent_rollup_dumpout_polygons(%s, ARRAY%s::int[]) AS f(seq)"
% (curTaxonKey, childrenTaxa)).fetchone()[0]
while last_seq and last_seq > 1:
last_seq = dbConn.execute(
"SELECT f.seq FROM distribution.extent_rollup_purge_contained_polygons(%s, %s) AS f(seq)"
% (curTaxonKey, last_seq)).fetchone()[0]
dbConn.execute((
"INSERT INTO distribution.taxon_extent(taxon_key, is_rolled_up, geom) "
+
"SELECT taxon_key, TRUE, st_multi(st_collectionextract(st_union(st_buffer(st_simplifypreservetopology(geom, 0.01), 0.25)), 3)) "
+
" FROM distribution.taxon_extent_rollup_polygon WHERE taxon_key = %s "
+ " GROUP BY taxon_key") % curTaxonKey)
dbConn.execute(
"DELETE FROM distribution.taxon_extent_rollup_polygon WHERE taxon_key = %s"
% curTaxonKey)
dbConn.execute((
"INSERT INTO distribution.taxon_extent_rollup(taxon_key, children_distributions_found, children_taxon_keys) "
+ "VALUES (%s, %s, ARRAY%s::int[])") %
(curTaxonKey,
rollup.children_distributions_found,
childrenTaxa))
except Exception:
print(
"Exception encountered during the processing of taxon: %s"
% curTaxonKey)
print(sys.exc_info())
if curTaxonKey:
dbConn.execute(
"DELETE FROM distribution.taxon_extent WHERE taxon_key = %s"
% curTaxonKey)
finally:
self.refreshMaterializedViews(dbConn)
finally:
dbConn.close()
print(
"All taxon extent rollup operations for input taxon level completed."
)
def process(opts):
if not opts.dbname:
print("dbname is a required input parameter")
parser.print_help()
exit(1)
if not opts.username:
print("username is a required input parameter")
parser.print_help()
exit(1)
#Start timing
start = time.clock()
# Establish communication queues
global cmd_queue
cmd_queue = multiprocessing.Queue()
# Start SQL processors
print('Creating %d SQL processors' % opts.threads)
processors = [Processor(opts, cmd_queue) for i in range(opts.threads)]
for p in processors:
p.start()
# Create a db connection and fetch SQL commands from db server
db_connection = getDbConnection(opts)
# Enqueue SQL commands
if 'sqlfile' in vars(opts) and opts.sqlfile:
with open(opts.sqlfile) as fileObj:
sql_cmd_buffer = ''
for line in fileObj:
for ch in line:
if ch == ';':
sql_cmds = db_connection.execute(sql_cmd_buffer + ch)
wait_till_cmd_queue_empty()
for cmd in sql_cmds:
cmd_queue.put(cmd[0])
sql_cmd_buffer = ''
else:
sql_cmd_buffer += ch
wait_till_cmd_queue_empty()
if 'sqlcmd' in vars(opts) and opts.sqlcmd:
sql_cmds = db_connection.execute(opts.sqlcmd)
for cmd in sql_cmds:
cmd_queue.put(cmd[0])
db_connection.close()
else:
db_connection.close()
# Add a poison pill for each consumer
for i in range(opts.threads):
cmd_queue.put(None)
for p in processors:
p.join()
# Stop timing and report duration
end = time.clock()
duration = end - start
hours, remainder = divmod(duration, 3600)
minutes, seconds = divmod(remainder, 60)
print('Completed in %d:%d:%f' % (hours, minutes, seconds))
def restore_foreign_key(dbPane):
getDbConnection(optparse.Values(dbPane.getDbOptions())).execute("SELECT exec(add_fk_cmd) FROM admin.database_foreign_key")
print("Foreign keys successfully added.")
def process(opts):
if not opts.dbname:
print("dbname is a required input parameter")
parser.print_help()
exit(1)
if not opts.username:
print("username is a required input parameter")
parser.print_help()
exit(1)
#Start timing
start = time.clock()
# Establish communication queues
global cmd_queue
cmd_queue = multiprocessing.Queue()
# Start SQL processors
print('Creating %d SQL processors' % opts.threads)
processors = [Processor(opts, cmd_queue)
for i in range(opts.threads)]
for p in processors:
p.start()
# Create a db connection and fetch SQL commands from db server
db_connection = getDbConnection(opts)
# Enqueue SQL commands
if 'sqlfile' in vars(opts) and opts.sqlfile:
with open(opts.sqlfile) as fileObj:
sql_cmd_buffer = ''
for line in fileObj:
for ch in line:
if ch == ';':
sql_cmds = db_connection.execute(sql_cmd_buffer + ch)
wait_till_cmd_queue_empty()
for cmd in sql_cmds:
cmd_queue.put(cmd[0])
sql_cmd_buffer = ''
else:
sql_cmd_buffer += ch
wait_till_cmd_queue_empty()
if 'sqlcmd' in vars(opts) and opts.sqlcmd:
sql_cmds = db_connection.execute(opts.sqlcmd)
for cmd in sql_cmds:
cmd_queue.put(cmd[0])
db_connection.close()
else:
db_connection.close()
# Add a poison pill for each consumer
for i in range(opts.threads):
cmd_queue.put(None)
for p in processors:
p.join()
# Stop timing and report duration
end = time.clock()
duration = end - start
hours, remainder = divmod(duration, 3600)
minutes, seconds = divmod(remainder, 60)
print('Completed in %d:%d:%f' % (hours, minutes, seconds))
def restore_foreign_key(dbPane):
getDbConnection(optparse.Values(dbPane.getDbOptions())).execute(
"SELECT exec(add_fk_cmd) FROM admin.database_foreign_key")
print("Foreign keys successfully added.")
def setupCommandPane(self):
if not self.dbPane.isConnectionTestedSuccessfully():
popup_message("Connection not yet tested",
"The Main DB Connection has not been tested successfully.\n" + \
"Once the Main DB Connection has been tested successfully, you can click that button again.")
return
for child in self.cmdFrame.winfo_children(): child.destroy()
i = 0
row = 0
column = 0
try:
opts = self.dbPane.getDbOptions()
dbSession = getDbConnection(optparse.Values(opts)).getSession()
# Rebuild the allocation_data partitions to make sure we are using the freshest allocation data
partitions = dbSession.execute(
"SELECT ('allocation_data_partition.' || table_name) AS partition_name" +
" FROM schema_v('allocation_data_partition') " +
" WHERE table_name NOT LIKE 'TOTALS%'" +
" ORDER BY 1").fetchall()
for partition in partitions:
dbSession.execute("DROP TABLE %s" % partition.partition_name)
dbSession.execute("SELECT allocation.maintain_allocation_data_partition()")
opts['sqlfile'] = "sql/insert_allocation_data_eez_hs.sql"
if 'threads' not in opts or opts['threads'] == 0:
opts['threads'] = 8
sp.process(optparse.Values(opts))
# Add buttons to command pane
self.yearList = dbSession.execute(
"SELECT replace(table_name, 'allocation_data_', '')::INT AS year " +
" FROM schema_v('allocation_data_partition') " +
" WHERE table_name NOT LIKE 'TOTALS%'" +
" ORDER BY 1").fetchall()
row = 0
button_data = []
for par in self.yearList:
button_data.append([par.year, partial(self.processYearPartition, par.year), "blue"])
column += 1
if column >= self.buttonsPerRow:
add_buttons(self.cmdFrame, button_data, row, 0, "horizontal")
button_data = []
column = 0
row += 1
if button_data != []:
add_buttons(self.cmdFrame, button_data, row, 0, "horizontal")
row += 1
# Calling maintain cell catch just in case the cell catch partitions are not present for any reason
dbSession.execute("SELECT web_partition.maintain_cell_catch_partition()")
finally:
dbSession.close()
for child in self.cmdFrame.winfo_children():
child.grid_configure(padx=5, pady=5)
if self.aggregateAllBt == None:
self.aggregateAllBt = tk.Button(self.parent, text="Aggregate All Year Partitions", fg="red", command=self.aggregateAllPartition, height=1)
self.parent.add(self.aggregateAllBt)
# Adding a filler pane for look only
self.fillerPane = ttk.Panedwindow(self.parent, orient=VERTICAL)
self.parent.add(self.fillerPane)
def rollupExtent(self):
if not self.taxonLevelToRollupFor or self.taxonLevelToRollupFor == 0:
print("Taxon level should be between 1 and 5. Please try again.")
return
print("Processing input taxon level: %s" % (self.taxonLevelToRollupFor.get()))
dbConn = getDbConnection(optparse.Values(self.dbPane.getDbOptions()))
try:
# First off refresh the materialized views that we rely on to indicate which taxon has/has not an extent already
self.refreshMaterializedViews(dbConn)
# Get the list of target taxon keys to rollup for the input taxon level
rollups = dbConn.execute("SELECT * FROM distribution.get_rollup_taxon_list(%s::int) ORDER BY children_distributions_found" \
% int(self.taxonLevelToRollupFor.get()))
curTaxonKey = None
for rollup in rollups:
curTaxonKey = rollup.taxon_key
childrenTaxa = rollup.children_taxon_keys
print("Rollup for %s using lower-level taxons %s [%s]" % (curTaxonKey, childrenTaxa, datetime.now().strftime('%Y/%m/%d %H:%M:%S')))
try:
if len(childrenTaxa) == 1:
dbConn.execute(
("INSERT INTO distribution.taxon_extent(taxon_key, is_extended, is_rolled_up, geom) " +
"SELECT %s, is_extended, TRUE, geom FROM distribution.taxon_extent WHERE taxon_key = %s")
% (curTaxonKey, childrenTaxa[0]))
else:
last_seq = dbConn.execute("SELECT f.seq FROM distribution.extent_rollup_dumpout_polygons(%s, ARRAY%s::int[]) AS f(seq)"
% (curTaxonKey, childrenTaxa)).fetchone()[0]
while last_seq and last_seq > 1:
last_seq = dbConn.execute(
"SELECT f.seq FROM distribution.extent_rollup_purge_contained_polygons(%s, %s) AS f(seq)"
% (curTaxonKey, last_seq)).fetchone()[0]
dbConn.execute(
("INSERT INTO distribution.taxon_extent(taxon_key, is_rolled_up, geom) " +
"SELECT taxon_key, TRUE, st_multi(st_collectionextract(st_union(st_buffer(st_simplifypreservetopology(geom, 0.01), 0.25)), 3)) " +
" FROM distribution.taxon_extent_rollup_polygon WHERE taxon_key = %s " +
" GROUP BY taxon_key")
% curTaxonKey)
dbConn.execute("DELETE FROM distribution.taxon_extent_rollup_polygon WHERE taxon_key = %s" % curTaxonKey)
dbConn.execute(
("INSERT INTO distribution.taxon_extent_rollup(taxon_key, children_distributions_found, children_taxon_keys) " +
"VALUES (%s, %s, ARRAY%s::int[])")
% (curTaxonKey, rollup.children_distributions_found, childrenTaxa))
except Exception:
print("Exception encountered during the processing of taxon: %s" % curTaxonKey)
print(sys.exc_info())
if curTaxonKey:
dbConn.execute("DELETE FROM distribution.taxon_extent WHERE taxon_key = %s" % curTaxonKey)
finally:
self.refreshMaterializedViews(dbConn)
finally:
dbConn.close()
print("All taxon extent rollup operations for input taxon level completed.")
