def checkParameterValues(self, parameters, context):
# Check if run migrations is checked
run_migrations = self.parameterAsBool(parameters, self.RUN_MIGRATIONS,
context)
if not run_migrations:
msg = tr("You must use the checkbox to do the upgrade !")
return False, msg
if Qgis.QGIS_VERSION_INT >= 31400:
connection_name = self.parameterAsConnectionName(
parameters, self.CONNECTION_NAME, context)
else:
connection_name = self.parameterAsString(parameters,
self.CONNECTION_NAME,
context)
metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
connection = metadata.findConnection(connection_name)
if not connection:
raise QgsProcessingException(
tr("The connection {} does not exist.").format(
connection_name))
if SCHEMA in connection.schemas():
override = self.parameterAsBool(parameters, self.RUN_MIGRATIONS,
context)
if not override:
msg = tr(
"The schema {} already exists in the database {} ! "
"If you really want to remove and recreate the schema (and remove its data),"
" use the checkbox.").format(SCHEMA, connection_name)
return False, msg
return super().checkParameterValues(parameters, context)
def shortHelpString(self):
msg = tr(
"When you are running the plugin for the first time on a new database, you need to install the "
"database schema.")
msg += '\n\n'
msg += tr("It will erase and/or create the schema '{}'.").format(
SCHEMA)
msg += '\n\n'
msg += self.parameters_help_string()
return msg
def initAlgorithm(self, config):
# LizSync config file from ini
ls = lizsyncConfig()
connection_name = ls.variable('postgresql:central/name')
label = tr("Connection to the PostgreSQL database")
tooltip = tr(
"The database where the schema '{}' is installed.").format(SCHEMA)
if Qgis.QGIS_VERSION_INT >= 31400:
param = QgsProcessingParameterProviderConnection(
self.CONNECTION_NAME,
label,
"postgres",
defaultValue=connection_name,
optional=False,
)
else:
param = QgsProcessingParameterString(
self.CONNECTION_NAME,
label,
defaultValue=connection_name,
optional=False,
)
param.setMetadata({
"widget_wrapper": {
"class":
"processing.gui.wrappers_postgis.ConnectionWidgetWrapper"
}
})
if Qgis.QGIS_VERSION_INT >= 31600:
param.setHelp(tooltip)
else:
param.tooltip_3liz = tooltip
self.addParameter(param)
param = QgsProcessingParameterBoolean(
self.RUN_MIGRATIONS,
tr("Use this checkbox to upgrade."),
defaultValue=False,
)
tooltip = tr(
"For security reason, we ask that you explicitly use this checkbox."
)
if Qgis.QGIS_VERSION_INT >= 31600:
param.setHelp(tooltip)
else:
param.tooltip_3liz = tooltip
self.addParameter(param)
self.addOutput(
QgsProcessingOutputString(self.DATABASE_VERSION,
tr("Database version")))
def runAlgorithm(self, name):
if name not in self.algorithms:
self.iface.messageBar().pushMessage(
tr("Error"),
tr("This algorithm cannot be found") + ' {}'.format(name),
level=Qgis.Critical)
return
# Run alg
param = {}
alg_name = 'lizsync:{0}'.format(name)
execAlgorithmDialog(alg_name, param)
def shortHelpString(self):
msg = tr(
"When the plugin is upgraded, a database upgrade may be available as well. The database "
"migration must be applied as well on the existing database.")
msg += '\n\n'
msg += self.parameters_help_string()
return msg
def add_central_server_id(connection, context, feedback):
feedback.pushInfo(tr('ADD SERVER ID IN THE METADATA TABLE'))
server_name = 'central'
sql = '''
INSERT INTO lizsync.server_metadata (server_name)
VALUES ( '{server_name}' )
ON CONFLICT ON CONSTRAINT server_metadata_server_name_key
DO NOTHING
RETURNING server_id, server_name
'''.format(server_name=server_name)
try:
connection.executeSql(sql)
feedback.pushInfo(tr('Server id successfully added'))
except QgsProviderConnectionException as e:
msg = tr('Error adding server name in server_metadata table.')
msg += ' '
msg += str(e)
raise QgsProcessingException(msg)
def initAlgorithm(self, config):
# LizSync config file from ini
ls = lizsyncConfig()
connection_name = ls.variable('postgresql:central/name')
label = tr('PostgreSQL connection to the central database')
tooltip = tr('The PostgreSQL connection to the central database.')
tooltip += tr(
' You need to have the right to create a new schema in this database,'
' as a schema lizsync will be created and filled with the needed tables and functions'
)
if Qgis.QGIS_VERSION_INT >= 31400:
param = QgsProcessingParameterProviderConnection(
self.CONNECTION_NAME,
label,
"postgres",
defaultValue=connection_name,
optional=False,
)
else:
param = QgsProcessingParameterString(
self.CONNECTION_NAME,
label,
defaultValue=connection_name,
optional=False,
)
param.setMetadata({
"widget_wrapper": {
"class":
"processing.gui.wrappers_postgis.ConnectionWidgetWrapper"
}
})
if Qgis.QGIS_VERSION_INT >= 31600:
param.setHelp(tooltip)
else:
param.tooltip_3liz = tooltip
self.addParameter(param)
param = QgsProcessingParameterBoolean(
self.OVERRIDE,
tr("Erase the schema {} ?").format(SCHEMA),
defaultValue=False,
)
tooltip = tr("** Be careful ** This will remove data in the schema !")
if Qgis.QGIS_VERSION_INT >= 31600:
param.setHelp(tooltip)
else:
param.tooltip_3liz = tooltip
self.addParameter(param)
self.addOutput(
QgsProcessingOutputString(self.DATABASE_VERSION,
tr("Database version")))
def database_version(
connection: QgsAbstractDatabaseProviderConnection) -> str:
""" Get database version. """
sql = (
"SELECT version "
"FROM {}.sys_structure_metadonnee "
# "WHERE status = 1 "
"ORDER BY date_ajout DESC "
"LIMIT 1;").format(SCHEMA)
try:
data = connection.executeSql(sql)
except QgsProviderConnectionException as e:
raise QgsProcessingException(str(e))
db_version = None
for row in data:
db_version = row[0]
if not db_version:
error_message = tr("No version has been found in the database !")
raise QgsProcessingException(error_message)
return db_version
def displayName(self):
return tr('Upgrade LizSync tools in the central database')
def processAlgorithm(self, parameters, context, feedback):
if Qgis.QGIS_VERSION_INT >= 31400:
connection_name = self.parameterAsConnectionName(
parameters, self.CONNECTION_NAME, context)
else:
connection_name = self.parameterAsString(parameters,
self.CONNECTION_NAME,
context)
metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
connection = metadata.findConnection(connection_name)
connection: QgsAbstractDatabaseProviderConnection
if not connection:
raise QgsProcessingException(
tr("The connection {} does not exist.").format(
connection_name))
if not connection.tableExists(SCHEMA, 'sys_structure_metadonnee'):
raise QgsProcessingException(
tr("The table {}.{} does not exist. You must first create the database structure."
).format(SCHEMA, 'sys_structure_metadonnee'))
db_version = self.database_version(connection)
feedback.pushInfo("Current database version '{}'.".format(db_version))
# Get plugin version
plugin_version = version()
if plugin_version in ["master", "dev"]:
migrations = available_migrations(000000)
last_migration = migrations[-1]
plugin_version = (last_migration.replace("upgrade_to_",
"").replace(".sql",
"").strip())
feedback.reportError(
tr("Be careful, running the migrations on a development branch!"
))
feedback.reportError(
tr("Latest available migration is {}").format(plugin_version))
else:
feedback.pushInfo(
tr("Plugin's version is {}").format(plugin_version))
results = {self.DATABASE_VERSION: plugin_version}
# Return if nothing to do
if db_version == plugin_version:
feedback.pushInfo(
tr("The database version and the plugin version are the same, version {}. There isn't any "
"upgrade to do.").format(plugin_version))
return results
db_version_integer = format_version_integer(db_version)
sql_files = available_migrations(db_version_integer)
# Loop sql files and run SQL code
for sf in sql_files:
sql_file = os.path.join(plugin_path(),
"install/sql/upgrade/{}".format(sf))
with open(sql_file, "r") as f:
sql = f.read()
if len(sql.strip()) == 0:
feedback.pushInfo("* " + sf + " -- " +
tr("SKIPPING, EMPTY FILE"))
continue
try:
connection.executeSql(sql)
except QgsProviderConnectionException as e:
raise QgsProcessingException(str(e))
new_db_version = (sf.replace("upgrade_to_",
"").replace(".sql", "").strip())
self.update_database_version(connection, new_db_version)
feedback.pushInfo(
"Database version {} -- OK !".format(new_db_version))
self.vacuum_all_tables(connection, feedback)
self.update_database_version(connection, plugin_version)
feedback.pushInfo(
"Database upgraded to the current plugin version {}!".format(
plugin_version))
return results
def displayName(self):
return tr('Install Lizsync tools on the central database')
def processAlgorithm(self, parameters, context, feedback):
metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
if Qgis.QGIS_VERSION_INT >= 31400:
connection_name = self.parameterAsConnectionName(
parameters, self.CONNECTION_NAME, context)
else:
connection_name = self.parameterAsString(parameters,
self.CONNECTION_NAME,
context)
# store parameters
ls = lizsyncConfig()
ls.setVariable('postgresql:central/name', connection_name)
ls.save()
connection = metadata.findConnection(connection_name)
if not connection:
raise QgsProcessingException(
tr("The connection {} does not exist.").format(
connection_name))
# Drop schema if needed
override = self.parameterAsBool(parameters, self.OVERRIDE, context)
if override and SCHEMA in connection.schemas():
feedback.pushInfo(tr("Removing the schema {}…").format(SCHEMA))
try:
connection.dropSchema(SCHEMA, True)
except QgsProviderConnectionException as e:
raise QgsProcessingException(str(e))
# Create full structure
sql_files = [
"00_initialize_database.sql",
"{}/10_FUNCTION.sql".format(SCHEMA),
"{}/20_TABLE_SEQUENCE_DEFAULT.sql".format(SCHEMA),
"{}/30_VIEW.sql".format(SCHEMA),
"{}/40_INDEX.sql".format(SCHEMA),
"{}/50_TRIGGER.sql".format(SCHEMA),
"{}/60_CONSTRAINT.sql".format(SCHEMA),
"{}/70_COMMENT.sql".format(SCHEMA),
"99_finalize_database.sql",
]
plugin_dir = plugin_path()
plugin_version = version()
dev_version = False
run_migration = os.environ.get("TEST_DATABASE_INSTALL_{}".format(
SCHEMA.upper()))
if plugin_version in ["master", "dev"] and not run_migration:
feedback.reportError(
"Be careful, running the install on a development branch!")
dev_version = True
if run_migration:
plugin_dir = plugin_test_data_path()
feedback.reportError(
"Be careful, running migrations on an empty database using {} "
"instead of {}".format(run_migration, plugin_version))
plugin_version = run_migration
# Loop sql files and run SQL code
for sql_file in sql_files:
feedback.pushInfo(sql_file)
sql_file = os.path.join(plugin_dir,
"install/sql/{}".format(sql_file))
with open(sql_file, "r") as f:
sql = f.read()
if len(sql.strip()) == 0:
feedback.pushInfo(" Skipped (empty file)")
continue
try:
connection.executeSql(sql)
except QgsProviderConnectionException as e:
raise QgsProcessingException(str(e))
feedback.pushInfo(" Success !")
# Add version
if run_migration or not dev_version:
metadata_version = plugin_version
else:
migrations = available_migrations(000000)
last_migration = migrations[-1]
metadata_version = (last_migration.replace("upgrade_to_",
"").replace(".sql",
"").strip())
feedback.reportError(
"Latest migration is {}".format(metadata_version))
self.vacuum_all_tables(connection, feedback)
sql = """
INSERT INTO {}.sys_structure_metadonnee
(version, date_ajout)
VALUES ('{}', now()::timestamp(0))""".format(
SCHEMA, metadata_version)
try:
connection.executeSql(sql)
except QgsProviderConnectionException as e:
raise QgsProcessingException(str(e))
feedback.pushInfo("Database version '{}'.".format(metadata_version))
# Add central server id
self.add_central_server_id(connection, context, feedback)
results = {
self.DATABASE_VERSION: metadata_version,
}
return results
def group(self):
return tr('01 Installation')
