mirror of
https://gitlab.com/wgp/dougal/software.git
synced 2025-12-06 11:17:08 +00:00
ca41bd8132
The database upgrade script is updated to export also user-entered data stored in columns of tables containing also derived data, and to re-import everything after the upgrade.
119 lines
3.1 KiB
Python
Executable File
119 lines
3.1 KiB
Python
Executable File
#!/usr/bin/python3
|
||
|
||
import sys
|
||
import os
|
||
# Because Python 🙄
|
||
bin_path = os.path.abspath(os.path.join(os.path.dirname(__file__), "../bin"))
|
||
sys.path.insert(1, bin_path)
|
||
import configuration
|
||
|
||
print(
|
||
"""
|
||
\u001b[31;4mYou are about to upgrade the database to the latest schema in /etc/db/schema-template.sql.\u001b[0m
|
||
|
||
\u001b[33mThis will cause all existing surveys to be wiped out and re-created.
|
||
Data which is captured by the database itself, e.g., event data will be exported
|
||
and re-imported. The rest should be brought back in at the next bin/runner.sh
|
||
run.
|
||
|
||
\u001b[0m
|
||
|
||
If this is what you want to do, press ENTER, else press Ctrl+C to cancel.
|
||
""")
|
||
|
||
input()
|
||
|
||
#
|
||
# If we got here it's because the user has asked us to proceed
|
||
#
|
||
|
||
locals().update(configuration.vars())
|
||
|
||
if os.path.exists(LOCKFILE):
|
||
print(f"Dougal's runner is currently active. Retry in a few moments or remove {LOCKFILE} if you think it's stale")
|
||
exit(1)
|
||
|
||
# Let's stop runner.sh from messing with our upgrade
|
||
with open(LOCKFILE, "w") as fd:
|
||
fd.write(str(os.getpid()))
|
||
|
||
#
|
||
# Define some variables that we will need later on
|
||
#
|
||
|
||
dbname=os.environ["PGDATABASE"] if "PGDATABASE" in os.environ else "dougal"
|
||
dbuser=os.environ["PGUSER"] if "PGUSER" in os.environ else "postgres"
|
||
dbtemplate=os.path.abspath(os.path.join(bin_path, "..", "etc", "db", "database-template.sql"))
|
||
|
||
system_exports = os.path.join(bin_path, "system_exports.py")
|
||
system_dump = os.path.join(bin_path, "system_dump.py")
|
||
system_load = os.path.join(bin_path, "system_load.py")
|
||
create_survey = os.path.join(bin_path, "create_survey.sh")
|
||
runner = "RUNNER_NOEXPORT=true RUNNER_IMPORT=true " + os.path.join(bin_path, "runner.sh")
|
||
|
||
#
|
||
# Export data
|
||
#
|
||
|
||
# The usual exports
|
||
print(system_exports)
|
||
os.system(system_exports)
|
||
|
||
# And the exports from “mixed” data
|
||
print(system_dump)
|
||
os.system(system_dump)
|
||
|
||
#
|
||
# Do database stuff – this is where we start breaking things
|
||
#
|
||
|
||
# Everyone gets kicked out
|
||
print(f"Terminate all sessions in {dbname}")
|
||
cmd = f"psql -U {dbuser} -d {dbname} -c 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();'"
|
||
print(cmd)
|
||
res = os.system(cmd)
|
||
|
||
# The database gets dropped
|
||
cmd = f"psql -U {dbuser} -d template1 -c 'DROP DATABASE {dbname};'"
|
||
print(cmd)
|
||
res = os.system(cmd)
|
||
if res != 0:
|
||
print("Cannot proceed. Upgrade aborted.")
|
||
exit(res)
|
||
|
||
# The new database gets created
|
||
cmd = f"psql -U {dbuser} -d template1 <{dbtemplate}"
|
||
print(cmd)
|
||
os.system(cmd)
|
||
|
||
#for survey in configuration.surveys():
|
||
#schema = survey["schema"]
|
||
#cmd = f"psql dougal -c 'DROP schema {schema} CASCADE;'"
|
||
#print(cmd)
|
||
|
||
# The surveys get re-created
|
||
for survey in configuration.surveys(include_archived=True):
|
||
id = survey["id"].lower()
|
||
name = survey["name"]
|
||
epsg = survey["epsg"]
|
||
schema = survey["schema"]
|
||
|
||
cmd = f"SCHEMA_NAME={schema} {create_survey} \"{id}\" \"{name}\" {epsg}"
|
||
print(cmd)
|
||
os.system(cmd)
|
||
|
||
#
|
||
# Here is where we re-import the data
|
||
#
|
||
|
||
os.remove(LOCKFILE) # Let runner.sh run again
|
||
# Import the bulk of the data
|
||
print(runner)
|
||
os.system(runner)
|
||
# Import the data that lives only in the database
|
||
print(system_load)
|
||
os.system(system_load)
|
||
|
||
|
||
print("Done")
|