def execute_db_script(path, cursor=None):
assert os.path.exists(path)
isf = open(path, 'r')
script = isf.read()
isf.close()
runMode = "entirely" #statement-by-statement or statements-in-parallel
sqlmeta = find_sql_script_meta(script)
if sqlmeta:
runMode = sqlmeta.get("run-mode", runMode)
if runMode and runMode in ("statement-by-statement",
"statements-in-parallel"):
parts = split_sql_script(script)
if runMode == "statements-in-parallel":
try:
pool = ThreadPool()
pool.map(onDBScriptPart, parts)
pool.close()
pool.join()
finally:
uSysDB.PerThreadConnections.closeAll()
else:
if cursor is None:
con = uSysDB.connect()
cursor = con.cursor()
for q in parts:
cursor.execute(q)
con.commit()
else:
if cursor is None:
con = uSysDB.connect()
cursor = con.cursor()
cursor.execute(script)
def precheck_for_access_verify_db(config):
uLogging.info("Checking verify_db exists...")
import uDBValidator
import uConfig
config_v = copy.copy(config)
config_v.database_name = config_v.database_name + '_verify'
try:
uSysDB.init(config_v)
con_v = uSysDB.connect()
except Exception, e:
uLogging.debug(str(e))
uLogging.warn("%s DB does not exist" % config_v.database_name)
try:
if config_v.admin_db_password or uUtil.isLocalAddress(
config_v.database_host):
# try to create verify db:
uLogging.info("Trying to create '%s' database..." %
config_v.database_name)
uDBValidator._recreate_verify_db(config_v)
uLogging.info("'%s' is created successfully." %
config_v.database_name)
else:
raise Exception(
"Postgres admin user credentials are required to create %s"
% config_v.database_name)
except Exception, e:
uLogging.debug(str(e))
dsn_login_short = re.sub("@.*", "", config_v.dsn_login)
raise PrecheckFailed(
reason="'%s' database is not accessible or does not exist" %
config_v.database_name,
what_to_do=
"Connect to %s Postgres server as admin user and create database: 'CREATE DATABASE %s OWNER %s'. If database '%s' already exists, make sure its owner is '%s'."
% (config_v.database_host, config_v.database_name,
dsn_login_short, config_v.database_name, dsn_login_short))
def performFakeUpgrade(scs, rootpath):
if not scs:
return
con = uSysDB.connect()
cur = con.cursor()
for sc, sc_rootpath in scs.values():
if not sc_rootpath:
sc_rootpath = rootpath
new_location = '%s:%s' % (sc.package.name,
os.path.join(sc_rootpath, sc.content.bin))
cur.execute(
"UPDATE sc_instances SET location_id = %s WHERE sc_id IN (SELECT sc_id FROM service_classes WHERE name= %s)",
(new_location, sc.package.name))
con.commit()
uAction.progress.do("fake upgrading service controllers")
cur.execute(
"SELECT component_id, name FROM service_classes sc JOIN sc_instances si ON (si.sc_id = sc.sc_id) ORDER BY component_id"
)
changed_scs_component_ids = [(x[0], x[1]) for x in cur.fetchall()
if x[1] in scs]
cur.close()
con.commit()
for sc in changed_scs_component_ids:
uLogging.info('%s', sc)
cid, name = sc
cid = int(cid)
uLogging.info("Upgrading %s(%s)", name, cid)
uAction.retriable(uFakePackaging.upgradeSC)(cid)
uAction.progress.done()
def turnIndexToUniq(tname, idxname):
if uSysDB.DBType != uSysDB.PgSQL:
return
con = uSysDB.connect()
tab = uDBSchema.getTable(tname, con)
idx = tab.getIndex(idxname)
if idx is None:
uLogging.debug("%s: no such index, it's ok", idxname)
return
cur = con.cursor()
cur.execute("SELECT oid FROM pg_class WHERE relname = %s", tname)
row = cur.fetchone()
if not row:
uLogging.err("Table %s does not exist", tname)
return
toid = row[0]
cur.execute(
"SELECT relname, conname FROM pg_constraint c JOIN pg_class r ON (r.oid = c.conrelid) WHERE c.confrelid = %s", toid)
fks = [(uDBSchema.getTable(row[0], con), row[1]) for row in cur.fetchall()]
fks = [(x[0], x[0].getConstraint(x[1])) for x in fks]
for t, fk in fks:
uLogging.debug("Temporarily dropping %s", fk)
t.dropConstraint(fk.name, con)
tab.dropIndex(idxname, con)
tab.addConstraint(uDBSchema.UniqueKey(idxname, idx.columns), con)
for t, fk in fks:
uLogging.debug("Restoring %s", fk)
t.addConstraint(fk, con)
def clean_interfaces():
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
"DELETE FROM interfaces WHERE NOT EXISTS (SELECT 1 FROM package_interfaces WHERE package_interfaces.interface_id = interfaces.interface_id UNION ALL SELECT 1 FROM dep_interfaces di WHERE di.interface_id = interfaces.interface_id)"
)
con.commit()
cur.execute(
"SELECT p.pkg_id, p.name, p.ctype, i.service_type FROM interfaces i JOIN dep_interfaces di ON (i.interface_id = di.interface_id) JOIN package_dependencies dp ON (di.dep_id = dp.dep_id) JOIN packages p ON (dp.pkg_id = p.pkg_id) WHERE i.interface_id NOT IN (SELECT interface_id FROM package_interfaces)"
)
pkgs = {}
for row in cur.fetchall():
pkg_name = row[1], row[2]
if not pkgs.has_key(pkg_name):
pkgs[pkg_name] = [], []
pkg_id = str(row[0])
iface = row[3]
if pkg_id not in pkgs[pkg_name][0]:
pkgs[pkg_name][0].append(pkg_id)
if iface not in pkgs[pkg_name][1]:
pkgs[pkg_name][1].append(iface)
for pkg in pkgs:
uLogging.warn("%s-%s (%s) depends on non-existing interface(s): %s",
pkg[0], pkg[1], ', '.join(pkgs[pkg][0]),
', '.join(pkgs[pkg][1]))
def initFromEnv(config):
port = config.openapi_port or '8440'
proto = 'http'
user = password = None
con = uSysDB.connect()
if uSysDB.table_exist(con, "openapi_config"):
cur = con.cursor()
cur.execute("SELECT require_auth, use_ssl FROM openapi_config")
row = cur.fetchone()
if row:
if row[1] == 'y':
proto = 'https'
if row[0] == 'y':
pleskd_props = uPEM.getPleskdProps()
user = pleskd_props['login']
password = pleskd_props['passwd']
if not user and not password:
raise Exception(
"OpenAPI authentication is enabled, but valid credentials were not found."
)
init(host=config.communication_ip,
port=port,
user=user,
proto=proto,
password=password)
def getHostCertificateDigest(host):
con = uSysDB.connect()
cur = con.cursor()
cur.execute("select sn_certificate from hosts where host_id = %s",
host.host_id)
row = cur.fetchone()
if not (row and row[0]):
raise Exception(
"Failed to get SN certificate for host %s from database. To fix, refer to https://kb.cloudblue.com/131620\n"
% (str(host.host_id)))
(keyfd, keyfile) = tempfile.mkstemp()
try:
os.write(keyfd, row[0])
finally:
os.close(keyfd)
cmd = "openssl x509 -in %s -pubkey -noout | grep -v '--' - | tr -d '\\n' | base64 -d | openssl dgst -sha256 -hex" % keyfile
(digest, err, status) = uUtil.readCmdExt(["/bin/sh", "-c", cmd])
os.remove(keyfile)
digest = digest.strip()
spaceidx = digest.find(" ")
if spaceidx <> -1:
digest = digest[spaceidx + 1:]
return digest
def initFromEnv():
import uPEM
import uCrypt
con = uSysDB.connect()
bmBridgeExists = uSysDB.table_exist(con, "bmbridge_settings")
if not bmBridgeExists:
raise Exception(
"bmbridge_settings table doesn't exist in Operation Automation database, Operation Automation is not properly integrated with Billing."
)
cur = con.cursor()
cur.execute(
"SELECT name, value FROM bmbridge_settings WHERE name in ('bm.xmlrpc.host', 'bm.xmlrpc.port', 'bm.xmlrpc.uri', 'bm.xmlrpc.ssl', 'bm.xmlrpc.user', 'bm.common.password')"
)
rows = cur.fetchall()
options = {}
for row in rows:
options[row[0]] = row[1]
global _settings
_settings.host = options['bm.xmlrpc.host']
_settings.port = options['bm.xmlrpc.port']
_settings.uri = options['bm.xmlrpc.uri']
_settings.ssl = options.get('bm.xmlrpc.ssl') == "1"
_settings.username = options.get('bm.xmlrpc.user')
if _settings.username:
_settings.password = uCrypt.decryptData(
options.get('bm.common.password'))
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 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 getAllHosts():
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
"SELECT h.host_id, h.primary_name, h.htype, p.opsys, p.osrel, p.arch, default_rootpath, h.pleskd_id, h.note FROM hosts h JOIN platforms p ON (p.platform_id = h.platform_id) WHERE h.deleting != 1 ORDER BY h.host_id ASC")
return [uUtil.PEMHost(row[0], row[1], row[2], uBuild.Platform(row[3], row[4], row[5]), row[6], row[7], row[8]) for row in cur.fetchall()]
def findHostComponentId(host_id, name, ctype):
"""
Returns component_id and version of the requested package on specified host
:param host_id:
:param name:
:param ctype:
:return: (component_id, version)
:raise: ComponentNotFound if not such component
"""
component = version = None
try:
pkg_id, version = findSuitablePackage(host_id, name, ctype)
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
'SELECT c.component_id FROM packages p INNER JOIN components c ON p.pkg_id = c.pkg_id WHERE p.pkg_id = %s AND c.host_id = %s',
(pkg_id, host_id))
component = cur.fetchone()
except PackageNotFound:
pass
if component is None:
raise ComponentNotFound(name, ctype, host_id)
return component[0], version
def registerPlatform(platform, parent, con=None):
own_con = False
if con is None:
own_con = True
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
"SELECT 1 FROM platforms "
"WHERE arch = %s AND opsys = %s AND osrel = %s",
(platform.arch, platform.os, platform.osver))
if cur.fetchone():
return
cur.execute(
"SELECT r, platform_id FROM platforms "
"WHERE arch = %s AND opsys = %s AND osrel = %s",
(parent.arch, parent.os, parent.osver))
parent_platform = cur.fetchone()
if not parent:
raise Exception("Parent platform %s-%s-%s not found" %
(parent.arch, parent.os, parent.osver))
r = parent_platform[0]
parent_id = parent_platform[1]
cur.execute("UPDATE platforms SET l = l + 2 WHERE l > %s", r)
cur.execute("UPDATE platforms SET r = r + 2 WHERE r >= %s", r)
cur.execute(
"INSERT INTO platforms(arch, opsys, osrel, parent_id, l, r) "
"VALUES (%s, %s, %s, %s, %s, %s)", platform.arch, platform.os,
platform.osver, parent_id, r, r + 1)
if own_con:
con.commit()
def removeComponent(host_id,
component_id,
hostname="host",
pkg_name="package",
removeDepends=None):
uAction.progress.do("removing %s(id = %s) from %s(id = %s)", pkg_name,
component_id, hostname, host_id)
deps = getDependencies(component_id)
while deps:
uLogging.info("%s-%s-%s (%s) installed on %s(%d) depends on %s (%s)",
deps[0].ctype, deps[0].name, deps[0].version,
deps[0].component_id, deps[0].hostname, deps[0].host_id,
pkg_name, component_id)
if ((removeDepends is None) and uDialog.askYesNo(
"Shall I remove it also?", False)) or removeDepends:
removeComponent(deps[0].host_id, deps[0].component_id,
deps[0].hostname, deps[0].name, removeDepends)
else:
raise Exception("Cannot deinstall package, there are dependencies")
deps = getDependencies(component_id)
con = uSysDB.connect()
cur = con.cursor()
cur.execute('SELECT pkg_id FROM components WHERE component_id = %s',
(component_id))
pkg_id = cur.fetchone()[0]
api = openapi.OpenAPI()
api.pem.packaging.uninstallPackageSync(host_id=host_id, package_id=pkg_id)
uAction.progress.done()
def findSuitablePackage(host_id, name, ctype):
con = uSysDB.connect()
uSysDB.set_verbose(False)
platform, rootpath = uPEM.getHostInfo(con, host_id)
rv = findPackageByPlatform(con, platform, name, ctype)
uSysDB.set_verbose(True)
return rv
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 runHCLCmd(hostId, commandText):
uLogging.debug(commandText)
con = uSysDB.connect()
host = uPEM.getHost(hostId)
commandText = commandText.replace(
"${", '$${'
) #for more details see following issue https://jira.int.zone/browse/POA-109131
rq = Request(user='******', group='root')
rq.command(commandText,
stdout='stdout',
stderr='stderr',
valid_exit_codes=[0])
if Const.isOsaWinPlatform(host.platform.os):
rq.export("default_shell_name", "cmd.exe", True)
rq.export("shell_cmd_switch", "/C", True)
else:
rq.export("default_shell_name", "/bin/sh", True)
rq.export("shell_cmd_switch", "-c", True)
rqRv = None
try:
rqRv = rq.send(host)
except uUtil.ExecFailed:
rqRv = rq.performRaw(host)
o = rqRv["stdout"]
if o: uLogging.debug(o)
return o
def perform(self, host_id=None):
host_id = host_id or self.__host_id
if host_id is None:
raise Exception("host_id not defined")
con = uSysDB.connect()
host = uPEM.getHost(host_id)
return self.performRaw(host)
def getNumberOfTasks(where):
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
"SELECT count(1) as c FROM tm_tasks t JOIN tm_usual u ON (t.task_id = u.task_id) WHERE "
+ where)
row = cur.fetchone()
return row[0]
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 getMNInfo():
# cache used by multithread slave updater
global _MN_info
if _MN_info is None:
con = uSysDB.connect()
_MN_info = getHostInfo(con, 1)
return _MN_info
def _getPkgsByQuery(q, *params):
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
"SELECT c.component_id, c.host_id, p.name, p.ctype, p.pkg_id, p.version, h.primary_name "
+ q, *params)
return [PkgOnHost(row) for row in cur.fetchall()]
def get_sched_strategy(self, con, request_id):
if con is None:
con = uSysDB.connect()
if uPEM.is_started('TaskManager'):
return APITaskManagement(request_id)
else:
return DBTaskManagement(con)
def __packageImported(pkg):
"""Return True if package with specific name, ctype, and platform exists in POA database. Version is ignored."""
con = uSysDB.connect()
cur = con.cursor()
cur.execute(
"SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM packages pk JOIN platforms pl ON (pk.platform_id = pl.platform_id) WHERE pk.name = %s AND pk.ctype = %s AND pl.arch = %s AND pl.opsys = %s AND pl.osrel = %s)",
pkg.name, pkg.ctype, pkg.platform.arch, pkg.platform.os,
pkg.platform.osver)
return bool(cur.fetchall())
def is_sc_installed(sc):
global _installed_scs
if not _installed_scs:
con = uSysDB.connect()
cur = con.cursor()
cur.execute("SELECT name FROM service_classes")
_installed_scs = [row[0] for row in cur.fetchall()]
return (sc in _installed_scs)
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 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 listAvailablePackages(host_id):
con = uSysDB.connect()
platform, rootpath = uPEM.getHostInfo(con, host_id)
platforms = uPEM.getPlatformLine(con, platform)
cur = con.cursor()
cur.execute(
"SELECT p.pkg_id, p.name, p.ctype FROM packages p where platform_id IN (%s)"
% ','.join(['%s'] * len(platforms)),
[p.platform_id for p in platforms])
return cur.fetchall()
def performCompat(self, host_id=None):
host_id = host_id or self.__host_id
if host_id is None:
raise Exception("host_id not defined")
con = uSysDB.connect()
host = uPEM.getHost(host_id)
try:
return self.performRaw(host)
except uUtil.ExecFailed:
return self.send(host)