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')