def copy_tables_schema():
"""
Copy tables and data from active to new provisioning
:return: None
"""
# Check if provisioning sequences exist or else create those sequences
db = postgresdb.DB()
schema_tables = db.get_all_table_names("active")
schema_sql = ""
for table_name in schema_tables:
schema_sql += """CREATE TABLE {0}.{1} (LIKE active.{2} INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS); INSERT INTO {0}.{3} SELECT * FROM active.{4};""".format(
settings.target_schema, table_name, table_name, table_name,
table_name)
db = postgresdb.DB()
databases = postgresdb.get_databases()
logger.info("Copy tables and data from active to new {0}".format(
settings.target_schema))
for database in databases:
credentials = eval(config["Database"]["dbs"])[database]
engine = db.connect(credentials)
try:
with engine.connect() as con:
con.execute(schema_sql)
except Exception as error:
logger.error(error)
exit()
def mapserviceboundary_si_to_udm_delete(transaction_data,
transaction_type='Delete'):
"""
Map ServiceBoundary to UDM Delete
:param transaction_data: Dictionary containing the column values for the table
:param transaction_type: Delete transaction
:return: None
"""
postgres = postgresdb.DB()
service_table_name = get_service_urn(transaction_data)
table_values = list(transaction_data.get(transaction_type).values())[0]
paramkeylist = ['UniqueId']
paravalues = get_paravalues(paramkeylist, table_values)
sql = "Delete from {}.{} where srcunqid = '{}';".format(settings.target_schema,
service_table_name, paravalues[0])
if TRANSACTION_RESULTS.get(service_table_name):
TRANSACTION_RESULTS[service_table_name] += 1
else:
TRANSACTION_RESULTS[service_table_name] = 1
if TRANSACTION_RESULTS.get('sql'):
TRANSACTION_RESULTS['sql'].append(sql)
else:
TRANSACTION_RESULTS['sql'] = [sql]
return True
def update_service_urn():
""" Update the service_urn_mapping data which is stored in a file"""
try:
db = postgresdb.DB()
credentials = eval(config["Database"]["dbs"])["srgis"]
engine = db.connect(credentials)
SERVICELAYERURNMAPPING = get_urn_table_mappings(engine)
with open(settings.service_urn_file, 'w') as fp:
json.dump(SERVICELAYERURNMAPPING, fp)
except Exception as error:
logger.error(error)
try:
os.remove(settings.application_flag)
exit()
except OSError:
pass
def mapserviceboundary_si_to_udm_update(transaction_data,
transaction_type='Update'):
"""
Map ServiceBoundary to UDM Update
:param transaction_data: Dictionary containing the column values for the table
:param transaction_type: Update Transaction
:return: None
"""
postgres = postgresdb.DB()
table_name = 'serviceboundary'
# service_table_name = postgres.get_service_urn_layer_mapping(
# transaction_data)
service_table_name = get_service_urn(transaction_data)
table_values = list(transaction_data.get(transaction_type).values())[0]
table_map = schema_mapper.get_table_fields(table_name)
paramkeylist = table_map.get('paramkeylist')
srcunqid = table_values.get('UniqueId')
mandatory_check = check_mandatory_fields(table_values, table_name)
if mandatory_check:
paravalues = get_paravalues(paramkeylist, table_values)
sql = """ UPDATE {}.{} SET wkb_geometry = ST_Multi(ST_SetSRID(
ST_GeomFromGML('{}'),4326)), srcunqid = '{}', srcofdata = '{}',
updatedate = '{}', effective = '{}', expire = '{}', country = '{}',
state = '{}', county = '{}', agencyid = '{}', routeuri = '{}',
serviceurn = '{}', servicenum = '{}', vcarduri = '{}',
displayname = '{}' """.format(settings.target_schema,
service_table_name, paravalues[0], paravalues[1], paravalues[2],
paravalues[3], paravalues[4], paravalues[5], paravalues[6],
paravalues[7], paravalues[8], paravalues[9], paravalues[10],
paravalues[11], paravalues[12], paravalues[13],
paravalues[14]) + " WHERE srcunqid = '" + srcunqid + "';"
sql = sql.replace("'None'", 'NULL')
if TRANSACTION_RESULTS.get(table_name):
TRANSACTION_RESULTS[service_table_name] += 1
else:
TRANSACTION_RESULTS[service_table_name] = 1
if TRANSACTION_RESULTS.get('sql'):
TRANSACTION_RESULTS['sql'].append(sql)
else:
TRANSACTION_RESULTS['sql'] = [sql]
else:
logger.error('{} Error!!: Mandatory Field not found!!! Transaction declined'.format(table_name))
return mandatory_check
def drop_schema(flip_flag_exception=None):
"""
Drop provisioning cascade and recreate
:return: None
"""
db = postgresdb.DB()
drop_sql = """DROP SCHEMA {0} CASCADE;
CREATE SCHEMA {0};""".format(settings.target_schema)
databases = postgresdb.get_databases()
logger.info("Drop and recreate {0} schema".format(settings.target_schema))
for database in databases:
credentials = eval(config["Database"]["dbs"])[database]
engine = db.connect(credentials)
try:
postgresdb.execute_sql(drop_sql)
except sqlalchemy.exc.DatabaseError as sqlerror:
logger.error(sqlerror)
except Exception as error:
logger.error(error)
exit()
def create_sequences_provisioning():
"""
If there are no sequences in provisioning, This will create sequences
:return: None
"""
db = postgresdb.DB()
schema_tables = db.get_all_table_names("active")
create_sequence_sql = ""
last_val_sql = ""
schema_sql = ""
for table_name in schema_tables:
last_val_sql = ""
last_val = ""
last_val_sql = "select nextval('active.{}_ogc_fid_seq');".format(
table_name)
last_val = db.get_lastval(last_val_sql)
create_sequence_sql += """CREATE SEQUENCE IF NOT EXISTS {0}.{1}_ogc_fid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE {0}.{2}_ogc_fid_seq
OWNER TO postgres;
Alter sequence {0}.{3}_ogc_fid_seq RESTART WITH {4};
Alter table {0}.{5} alter ogc_fid set DEFAULT nextval('{0}."{6}_ogc_fid_seq"');""".format(
settings.target_schema, table_name, table_name, table_name,
last_val, table_name, table_name)
try:
postgresdb.execute_sql(create_sequence_sql)
logger.info("Creating provisioning sequences for all tables")
except Exception as error:
try:
os.remove(settings.application_flag)
except OSError as ose:
pass
logger.error(error)
exit()
def download_atom_feed():
"""Download the xml file and parse through and check the entry tags.
:return: None
"""
logger.info("******* START download_atom_feed() *******")
starttime = datetime.now(tz=pytz.utc)
# Create a file for application status flag
open(settings.application_flag, 'w').close()
global ns1
global url
global fp
global mandatory_field_check
db = postgresdb.DB()
# Namespaces
ns1 = {
"atom": "http://www.w3.org/2005/Atom",
"gml": "http://www.opengis.net/gml",
"georss": "http://www.opengis.net/georss",
"wfs": "http://www.opengis.org/wfs"
}
# Update serviceurn tables
update_service_urn()
start_position = 0
entrycount = 0
entrycount_res = 0
while entrycount % 25 in (0, 25):
xfp = get_xml(start_position)
entrycount, start_position = parse_create_entry(xfp, start_position)
entrycount_res += entrycount
start_position = entrycount_res + 1
if entrycount < 25:
start_position = 0
break
# drop provisioning schema and create again if there is something to update in database
drop_schema()
# copy tables from active schema to provisioning schema
copy_tables_schema()
# create sequences for new tables in provisioning schema
create_sequences_provisioning()
if isinstance(mandatory_field_check, tuple) and (False
in mandatory_field_check):
mandatory_field_check = False
elif isinstance(mandatory_field_check,
tuple) and not (False in mandatory_field_check):
mandatory_field_check = True
else:
mandatory_field_check = mandatory_field_check
# mandatory_field will be( False,True, False) if false exists then its F
if mandatory_field_check and transaction_flag:
commit_flag = True
# Commit to database only if the commit_flag is true
# Perform transactions
db.transaction_sql()
try:
flip_flag = flip_schema()
if flip_flag:
logger.error("Flip Schema exception occurred!!! Exiting!")
open(settings.application_flag, 'w').close()
FIX_FLAG = True
exit()
# for now it's commented, it will delete all the entries in the feed
# Loop through entry id and clear the entry id tag
update_feed_flag = config["Default"]["update_feed_flag"]
if update_feed_flag:
for e_id in entryid:
put_xml(entry_id=e_id)
except Exception as error:
try:
os.remove(settings.application_flag)
except OSError as ose:
pass
logger.error(error)
elif transaction_flag and not mandatory_field_check:
try:
os.remove(settings.application_flag)
except OSError as ose:
pass
logger.error("Transaction Aborted, nothing to update!!!")
else:
logger.info("No changes in XML, nothing to update!!!")
# Remove application status flag
try:
os.remove(settings.application_flag)
except OSError as ose:
pass
endtime = datetime.now(tz=pytz.utc)
db.log_modification_history(starttime, endtime)
logger.info("******** END download_atom_feed() *******")