def execute(self, readonly, precheck=False): if readonly: raise Exception("SQLScript does not support readonly operations") if not uPEM.is_sc_installed(self.owner): uLogging.info("%s is not installed, skipping", self.owner) return None con = uSysDB.connect() cur = con.cursor() rv = None for stmt in self.get_code(): uLogging.debug('executing %s', stmt) kind = stmtKind(stmt) if kind in ('BEGIN', 'COMMIT', 'ROLLBACK'): uLogging.warn( '%s statements are ignored, <SQL> action always implicitly begins and commits transaction', kind) else: cur.execute(stmt) con.commit() uSysDB.close(con) return rv
def checkRequirements2(required_updates, present_updates): con = uSysDB.connect() cur = con.cursor() cur.execute("SELECT build FROM version_history") installed = reduce(lambda s, x: s.update([x[0]]) or s, cur.fetchall(), set()) cur.execute("SELECT name FROM hotfixes") installed = reduce(lambda s, x: s.update([x[0]]) or s, cur.fetchall(), installed) # Crutch for upgrade from OA 6.5 TODO: Remove in OA 7.1 if "oa-6.5-258" in installed: installed.add("poa-6.0-3517") not_installed = required_updates - installed if not_installed == set([None]) or not_installed == set([]): not_installed = False for build_name in present_updates: for inst in installed: if PEMVersion.compareVersions(inst, build_name) > 0: raise uPrecheck.PrecheckFailed( "%s is installed, it is higher version than %s" % (inst, build_name), None) if not_installed: raise uPrecheck.PrecheckFailed("required Operation Automation versions are not installed: %s" % ', '.join( not_installed), "Install missing versions or select another update") cur.close() uSysDB.close(con)
def upgrade_aps_db(scriptsDir, con=None): if con is None: con = uSysDB.connect() # To be sure if not is_aps_db_installed(con): install_aps_db(scriptsDir, con) db_ver = get_db_version(con) uLogging.debug("APS DB v%d.%d found." % (db_ver[0], db_ver[1])) uLogging.info("Looking for application upgrades in '%s'" % scriptsDir) cursor = con.cursor() upgrade_found = None sc_pattern = get_update_scripts_pattern() sc_list = list_db_scripts(scriptsDir, sc_pattern) for path in sc_list: sc_matcher = sc_pattern.match(path) sc_ver = (int(sc_matcher.group(1)), int(sc_matcher.group(2))) if sc_ver[0] > db_ver[0] or (sc_ver[0] == db_ver[0] and sc_ver[1] > db_ver[1]): path = os.path.join(scriptsDir, path) execute_db_script(path, cursor) set_db_version(con, sc_ver[0], sc_ver[1]) con.commit() uLogging.info("'%s' applied." % path) upgrade_found = True db_ver = get_db_version(con) uSysDB.close(con) if upgrade_found: uLogging.info("APS DB upgraded to v%d.%d." % (db_ver[0], db_ver[1])) else: uLogging.info("No new upgrades for APS DB found.")
def _getPgDbInfo(): con = uSysDB.connect() try: cur = con.cursor() cur.execute("SELECT inet_server_addr(), inet_server_port(), current_database()") return cur.fetchone() finally: uSysDB.close(con)
def getNonMNHosts(): con = uSysDB.connect() cur = con.cursor() cur.execute("SELECT h.host_id, h.primary_name FROM hosts h WHERE h.host_id != 1 AND h.deleting !=1") rv = set([(x[0], x[1]) for x in cur.fetchall()]) cur.close() uSysDB.close(con) return rv
def _cleanup_verify_db(config_v): uLogging.info('Clear reference DB') uSysDB.init(config_v) assert config_v.database_name.endswith( '_verify' ), "Can't clear database. DB name is %s, which is not verify DB!" % config_v.database_name con_v = uSysDB.connect() uDBSchema.dropOwnedBy(con_v, config_v.dsn_login) con_v.commit() uSysDB.close(con_v)
def install_aps_db(scriptsDir, con=None): if con is None: con = uSysDB.connect() initial = os.path.join(scriptsDir, "dbschema-%s.sql" % get_db_type()) uLogging.info("Installing APS database from %s" % initial) execute_db_script(cursor=con.cursor(), path=initial) con.commit() db_ver = get_db_version(con) uSysDB.close(con) uLogging.info("New APS DB v%d.%d installed." % (db_ver[0], db_ver[1]))
def perform_aps2_sys_pkgs_upgrade(binfo): from poaupdater import uPEM, uAction if not uPEM.is_sc_installed("SaaS"): uLogging.info( "No 'SaaS' SC installed found. Skip bundled APS Type's packages importing." ) return con = uSysDB.connect() uAction.progress.do("importing APS Type's packages") performAPSTypesUpgrade(con, binfo) uAction.progress.done() uSysDB.close(con)
def getBrandingHosts(): con = uSysDB.connect() cur = con.cursor() cur.execute( "SELECT DISTINCT hosts.host_id, hosts.primary_name " "FROM brand_proxy_params brand " "JOIN subdomain_services website ON (website.subds_id = brand.vh_id) " "JOIN domain_services webspace ON (webspace.ds_id = website.ds_id) " "JOIN services on (services.service_id = webspace.service_id) " "JOIN hosts on (hosts.host_Id = services.host_Id and hosts.deleting != 1) " "WHERE hosts.host_id != 1 AND hosts.htype != 'e'") rv = set([(x[0], x[1]) for x in cur.fetchall()]) cur.close() uSysDB.close(con) return rv
def resetPIDs(): """ Set to null PIDs of POA soloaders in order to show that service controllers aren't started NOTE: PIDs of pleskd and vzpemagent WILL NOT be changed """ try: con = uSysDB.connect() cur = con.cursor() cur.execute( "UPDATE sc_instances SET pid = -1 WHERE sc_id IN (SELECT sc_id FROM service_classes WHERE name NOT IN ('pleskd', 'vzpemagent'))") con.commit() uSysDB.close(con) except Exception, e: uLogging.warn("Failed to reset SoLoader PIDs: %s", e)
def repairPGSequences(): if uSysDB.DBType != uSysDB.PgSQL: return con = uSysDB.connect() uLogging.debug("Repairing tables sequences") pattern = re.compile(r"^nextval\('(.*)'(:?::text|::regclass)?\)") try: cur = con.cursor() cur.execute(""" select n.nspname as schema_name, c.relname as table_name, a.attname as column_name, pg_get_expr(d.adbin, d.adrelid) as seq_name from pg_class c join pg_attribute a on (c.oid=a.attrelid) join pg_attrdef d on (a.attrelid=d.adrelid and a.attnum=d.adnum) join pg_namespace n on (c.relnamespace=n.oid) where n.nspname = 'public' and (not a.attisdropped) and d.adsrc like 'nextval%' """) seq_num = 0 for seq in cur.fetchall(): m = pattern.match(seq[3]) seq_name = m.group(1) cur.execute("SELECT %s" % seq[3]) cur.execute(""" SELECT SETVAL('%(seq_name)s', CASE WHEN lastval() > MAX(%(column_name)s) THEN lastval() ELSE MAX(%(column_name)s) END) FROM %(table_name)s """ % { "seq_name": seq_name, "column_name": seq[2], "table_name": seq[1] }) seq_num += 1 con.commit() uLogging.info("%d sequences were updated.", seq_num) finally: uSysDB.close(con)
def validateDatabase(binfo, config=None): uDBSchema.Table.reset_cache( ) # Need to get actual data in retriable actions config_v = initialize_verify_db(binfo, config) uSysDB.init(config_v) con_v = uSysDB.connect() uSysDB.init(config) con = uSysDB.connect() manifest_tables = readTablesFromDBManifests(con) manifest_tables.update(readTablesFromDB(con_v)) uSysDB.close(con_v) tables = readTablesFromDB(con) return validateDatabaseExt(manifest_tables, tables)
def perform_aps2_db_upgrade(binfo): from poaupdater import uAction scripts_dirs = binfo.upgrade_instructions.aps if not scripts_dirs: uLogging.info( "There is no APS Database upgrade scripts. Skip upgrading.") return uAction.progress.do("updating APS database") con = uSysDB.connect() for scripts_dir in scripts_dirs: upgrade_aps_db(scripts_dir, con) if Const.isWindows(): uAction.progress.do("dropping APS database page locking") performMSSQLUpgrade(con) uAction.progress.done() uAction.progress.done() uSysDB.close(con)
def repairDatabase(config, binfo): config_v = initialize_verify_db(binfo, config) uSysDB.init(config_v) con_v = uSysDB.connect() uSysDB.init(config) con = uSysDB.connect() manifest_tables = readTablesFromDB(con_v) uSysDB.close(con_v) manifest_tables.update(readTablesFromDBManifests(con)) tables = readTablesFromDB(con) validator = DBValidator() progress.do("repairing tables") errors = validator.repairTables(con, manifest_tables, tables) progress.done() return errors
def installPackageToHostAPI(host_id, name=None, ctype=None, pkg_id=None, properties=None, print_name=None): if pkg_id is None: pkg_id, version = findSuitablePackage(host_id, name, ctype) else: version = None if print_name is None: if name is None: print_name = 'package' elif ctype is None: print_name = name elif version is None: print_name = "%s-%s" % (ctype, name) else: print_name = "%s-%s-%s" % (ctype, name, version) uLogging.debug("installing %s (%s) to host %s", print_name, pkg_id, host_id) if properties is None: proplist = [] else: proplist = [{"name": x, "value": properties[x]} for x in properties] # As DB schema is going to be changed during 'installPackageSync' call # we have to make sure that only one transaction will be exist to avoid deadlocks in Postgres (#POA-75494) con = uSysDB.connect() uSysDB.close(con) api = openapi.OpenAPI() return api.pem.packaging.installPackageSync( host_id=host_id, package_id=pkg_id, properties=proplist)["component_id"]
def removePackage(name, ctype, host_id=None, removeDepends=None, platform=None): """ Removes package from host or from the whole system """ con = uSysDB.connect() cur = con.cursor() nicename = "%s-%s" % (ctype, name) if _mn_plesk_root is None: cur.execute("SELECT default_rootpath FROM hosts WHERE host_id = 1") init(None, cur.fetchone()[0]) if host_id is None: platform_id = None if platform: if hasattr(platform, "platform_id"): platform_id = platform.platform_id if platform_id is None: cur.execute( "SELECT platform_id FROM platforms WHERE opsys = %s AND osrel = %s AND arch = %s", (platform.os, platform.osver, platform.arch)) row = cur.fetchone() if row: platform_id = row[0] query = "SELECT h.host_id, h.primary_name, c.component_id, h.htype FROM packages p JOIN components c ON (c.pkg_id = p.pkg_id) JOIN hosts h ON (h.host_id = c.host_id) WHERE p.name = %s AND p.ctype = %s" params = (name, ctype) if platform_id: query += " AND h.platform_id = %s" params += (platform_id, ) cur.execute(query, params) to_deinstall = [(row[0], row[1], row[2], row[3]) for row in cur.fetchall()] query = "SELECT pkg_id FROM packages WHERE name = %s AND ctype = %s" if platform_id: query += " AND platform_id = %s" cur.execute(query, params) to_remove = [row[0] for row in cur.fetchall()] else: cur.execute( "SELECT h.host_id, h.primary_name, c.component_id, h.htype FROM packages p JOIN components c ON (c.pkg_id = p.pkg_id) JOIN hosts h ON (h.host_id = c.host_id) WHERE p.name = %s AND p.ctype = %s AND h.host_id = %s", (name, ctype, host_id)) to_deinstall = [(row[0], row[1], row[2], row[3]) for row in cur.fetchall()] to_remove = [] con.commit() uSysDB.close(con) if not to_deinstall and host_id: uLogging.info("%s is not installed on host %s, doing nothing", nicename, host_id) return elif not to_deinstall: uLogging.info("%s is not installed anywhere, doing nothing", nicename) else: for td in to_deinstall: host_id, hostname, component_id, host_type = td removeComponent(host_id, component_id, hostname, nicename, removeDepends) if not to_remove: if not host_id: uLogging.info( "There is no %s in package repository, nothing to remove", nicename) else: for pkg_id in to_remove: removePkgFromRepo(pkg_id, nicename)
def upgradeSC(component_id): con = uSysDB.connect() cur = con.cursor() cur.execute("""SELECT sc.sc_id, p.name, p.version, p.pkg_id, sc.sc_group_id, sc.name FROM service_classes sc JOIN sc_instances si ON (sc.sc_id = si.sc_id) JOIN components c ON (c.component_id = si.component_id) JOIN packages p ON (p.pkg_id = c.pkg_id) WHERE si.component_id = %s""", component_id) row = cur.fetchone() if not row: raise Exception("Component id %s does not exist, or not a service controller" % component_id) sc_id, pkg_name, old_pkg_version, old_pkg_id, sc_group_id, scname = row[0], row[1], row[2], row[3], row[4], row[5] uLogging.debug("Upgrading %s-%s (sc_id = %d, pkg_id = %d)", pkg_name, old_pkg_version, sc_id, old_pkg_id) new_pkg_id = find_newer_sc_pkg_id(con, old_pkg_id, pkg_name, old_pkg_version) if not new_pkg_id: uLogging.warn("No higher version package found") return # TODO: only one service_instance should be auto-updated, rest ones should refer to old components cur.execute("UPDATE components SET pkg_id = %s WHERE component_id = %s", (new_pkg_id, component_id)) cur.execute( "UPDATE service_classes SET version = p.version FROM packages p WHERE sc_id = %s AND p.pkg_id = %s", (sc_id, new_pkg_id)) # update component properties cur.execute(""" SELECT p.prop_id, p.name, p2.prop_id AS new_prop_id FROM component_properties cp JOIN properties p ON (cp.prop_id = p.prop_id) LEFT JOIN properties p2 ON (p.name = p2.name) WHERE p.pkg_id = %s AND p2.pkg_id = %s AND cp.component_id = %s """, (old_pkg_id, new_pkg_id, component_id)) rows = cur.fetchall() for row in rows: old_prop_id, prop_name, new_prop_id = row[0], row[1], row[2] if new_prop_id is None: uLogging.debug("Deleting property %s (old prop_id %s)", prop_name, old_prop_id) cur.execute("""DELETE FROM component_properties WHERE component_id = %s AND prop_id = %s """, (component_id, old_prop_id)) else: uLogging.debug('Copying property %s (old prop_id %s, new prop_id %s)', prop_name, old_prop_id, new_prop_id) cur.execute("""UPDATE component_properties SET prop_id = %s WHERE component_id = %s AND prop_id = %s """, (new_prop_id, component_id, old_prop_id)) # register new properties of updated SC except (they should be set manually in upgrade procedure): # 1. mandatory properties with not specified default # 2. properties which should be got by depend (TODO, probably updater should resolve such depends) cur.execute(""" SELECT p.default_value, p.name FROM properties p WHERE p.pkg_id = %s AND NOT EXISTS (SELECT 1 FROM component_properties cp WHERE cp.component_id = %s AND cp.prop_id = p.prop_id) AND (p.default_value IS NOT NULL or p.mandatory = 'n') AND NOT EXISTS (SELECT 1 FROM dep_get_properties dp JOIN package_dependencies pd ON pd.dep_id = dp.dep_id WHERE pd.pkg_id = p.pkg_id AND dp.name = p.name) """, (new_pkg_id, component_id)) rows = cur.fetchall() for row in rows: prop_val, prop_name = row[0], row[1] uLogging.debug('Register SC %s missing property %s (component_id %s, default_value %s)', scname, prop_name, component_id, prop_val) uPEM.setSCProperty(con, scname, prop_name, prop_val) # update OpenAPI methods and observer interfaces cur.close() cur = con.cursor() cur.execute("SELECT data FROM package_body WHERE pkg_id = %s", new_pkg_id) row = cur.fetchone() data = row[0] data = str(data) manifest = uManifestParser.unsign_package(cStringIO.StringIO(data)) xml = dom.parseString(manifest) observer_interfaces = [node.getAttribute("name") for node in xml.getElementsByTagName( "INTERFACE") if node.getAttribute("observer") == "yes"] uLogging.debug("Attempting to register observer interfaces: %s", ', '.join( ["%s" % service_type for service_type in observer_interfaces])) iparams = [sc_id] for service_type in observer_interfaces: iparams += [service_type] cur.execute(""" INSERT INTO observers(sc_id, interface_id) SELECT %s, interface_id FROM interfaces i WHERE service_type = %s AND NOT EXISTS (SELECT 1 FROM observers o WHERE sc_id = %s AND i.interface_id = o.interface_id)""", (sc_id, service_type, sc_id)) if not observer_interfaces: cur.execute("DELETE FROM observers WHERE sc_id = %s", sc_id) else: cur.execute(("DELETE FROM observers WHERE sc_id = %%s AND interface_id NOT IN (SELECT interface_id FROM interfaces WHERE (%s))" % ' OR '.join(['(service_type = %s)'] * len(observer_interfaces))), iparams) cur.execute( "DELETE FROM openapi_methods WHERE extension_id IN " "(SELECT extension_id FROM openapi_extensions WHERE sc_id = %s)", sc_id) cur.execute( "DELETE FROM openapi_extensions WHERE sc_id = %s", sc_id) new_sc_group_id = None if old_pkg_version >= "2.9": # upgrade SC group id sc_group_ids = [x.getAttribute("value") for x in xml.getElementsByTagName( "ATTRIBUTE") if x.getAttribute("name") == "SC_GROUP_ID"] if not sc_group_ids: uLogging.debug("Cannot find SC Group ID for %s-%s", pkg_name, old_pkg_version) else: try: new_sc_group_id = int(sc_group_ids[0]) uLogging.debug("New SC group id = %s, old = %s", new_sc_group_id, sc_group_id) except Exception: uLogging.err("%s: invalid sc_group_id", sc_group_ids[0]) if new_sc_group_id is not None and sc_group_id != new_sc_group_id: cur.execute("UPDATE service_classes SET sc_group_id = %s WHERE sc_id = %s", new_sc_group_id, sc_id) fixDependencies(con, component_id, new_pkg_id, pkg_name) uModularOpenAPI.update_extensions_from_xml(xml, con) con.commit() uSysDB.close(con)