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)
