def do_add(self, params):
"""
add a given table to the database (connection via URL)
"""
if len(params) < 1:
logger.error("Please specify a module to add (e.g. dialog)")
return -1
module = params[0]
if len(params) < 2:
db_name = cfg.read_param("database_name",
"Please provide the database to add the module to")
else:
db_name = params[1]
db_url = self.get_db_url(db_name)
if not db_url:
logger.error("no DB URL specified: aborting!")
return -1
admin_url = self.get_admin_db_url(db_name)
if not admin_url:
return -1
admin_db = self.get_db(admin_url, db_name)
if not admin_db:
return -1
ret = self.create_tables(db_name, db_url, admin_db, tables=[module],
create_std=False)
admin_db.destroy()
return ret
def user_db_connect(self):
engine = osdb.get_db_engine()
db_url = cfg.read_param(["database_user_url", "database_url"],
"Please provide us the URL of the database")
if db_url is None:
print()
logger.error("no URL specified: aborting!")
return None
db_url = osdb.set_url_driver(db_url, engine)
db_name = cfg.read_param(["database_user_name", "database_name"],
"Please provide the database to add user to",
DEFAULT_DB_NAME)
try:
db = osdb(db_url, db_name)
except osdbError:
logger.error("failed to connect to database %s", db_name)
return None
if not db.connect():
return None
return db
def insert(self, table, keys):
"""
insert values into table
"""
# TODO: do this only for SQLAlchemy
if not self.__conn:
raise osdbError("connection not available")
values = ""
for v in keys.values():
values += ", "
if type(v) == int:
values += v
else:
values += "'{}'".format(
v.translate(str.maketrans({'\'': '\\\''})))
statement = "INSERT INTO {} ({}) VALUES ({})".format(
table, ", ".join(keys.keys()), values[2:])
try:
result = self.__conn.execute(statement)
except sqlalchemy.exc.SQLAlchemyError as ex:
logger.error("cannot execute query: {}".format(statement))
logger.error(ex)
return False
return result
def connect(self, db_name=None):
"""
connect to database
"""
if db_name is not None:
self.db_name = db_name
# TODO: do this only for SQLAlchemy
try:
if self.dialect == "postgres":
self.db_url = self.set_url_db(self.db_url, self.db_name)
if sqlalchemy_utils.database_exists(self.db_url) is True:
engine = sqlalchemy.create_engine(
self.db_url, isolation_level='AUTOCOMMIT')
if self.__conn:
self.__conn.close()
self.__conn = engine.connect()
# connect the Session object to our engine
self.Session.configure(bind=self.__engine)
# instanciate the Session object
self.session = self.Session()
logger.debug("connected to database URL '%s'", self.db_url)
else:
self.__conn.execute("USE {}".format(self.db_name))
except Exception as e:
logger.error("failed to connect to %s", self.db_url)
logger.error(e)
return False
return True
def ensure_user(self, db_url, db_name, admin_db):
"""
Ensures that the user/password in @db_url can connect to @db_name.
It assumes @db_name has been created beforehand. If the user doesn't
exist or has insufficient permissions, this will be fixed using the
@admin_db connection.
"""
db_url = osdb.set_url_db(db_url, db_name)
try:
db = self.get_db(db_url, db_name, check_access=True)
logger.info("connected to DB, '%s' user is already created",
osdb.get_url_user(db_url))
except osdbAccessDeniedError:
logger.info("creating access user for {} ...".format(db_name))
if not admin_db.ensure_user(db_url):
logger.error("failed to create user on {} DB".format(db_name))
return -1
db = self.get_db(db_url, db_name, cfg_url_param='database_url')
if db is None:
return -1
db.destroy()
return 0
def do_drop(self, params=None):
db_url = cfg.read_param("database_url",
"Please provide us the URL of the database")
if db_url is None:
print()
logger.error("no URL specified: aborting!")
return -1
if params and len(params) > 0:
db_name = params[0]
else:
db_name = cfg.read_param("database_name",
"Please provide the database to drop",
DEFAULT_DB_NAME)
db = self.getdb(db_url, db_name)
if db is None:
return -1
# check to see if the database has already been created
if db.exists():
if cfg.read_param("database_force_drop",
"Do you really want to drop the '{}' database".
format(db_name),
False, True):
db.drop()
else:
logger.info("database '{}' not dropped!".format(db_name))
else:
logger.warning("database '{}' does not exist!".format(db_name))
def do_delete(self, params=None):
if len(params) < 1:
name = cfg.read_param(
None, "Please provide the username you want to delete")
if not name:
logger.warning("no username to delete!")
return -1
else:
name = params[0]
username, domain = self.user_get_domain(name)
db = self.user_db_connect()
if not db:
return -1
delete_dict = {USER_NAME_COL: username, USER_DOMAIN_COL: domain}
# check if the user already exists
if not db.entry_exists(USER_TABLE, delete_dict):
logger.error("User {}@{} does not exist".format(username, domain))
return -1
db.delete(USER_TABLE, delete_dict)
logger.info("Successfully deleted {}@{}".format(username, domain))
db.destroy()
return True
def complete(self, text, state):
"""
auto-complete selection based on given text and state parameters
"""
if state == 0:
origline = readline.get_line_buffer()
line = origline.lstrip()
stripped = len(origline) - len(line)
begidx = readline.get_begidx() - stripped
endidx = readline.get_endidx() - stripped
if begidx > 0:
mod, args, foo = self.parseline(line)
if mod == '':
return self.complete_modules(text)[state]
elif not mod in self.modules:
logger.error("BUG: mod '{}' not found!".format(mod))
else:
module = self.modules[mod]
self.completion_matches = \
self.complete_functions(module, text, line, begidx, endidx)
else:
self.completion_matches = self.complete_modules(text)
try:
return self.completion_matches[state]
except IndexError:
return ['']
def ensure_user(self, db_url, db_name, admin_db):
"""
Ensures that the user/password in @db_url can connect to @db_name.
It assumes @db_name has been created beforehand. If the user doesn't
exist or has insufficient permissions, this will be fixed using the
@admin_db connection.
"""
db_url = osdb.set_url_db(db_url, db_name)
try:
db = self.get_db(db_url, db_name, check_access=True)
logger.info("access works, opensips user already exists")
except osdbAccessDeniedError:
logger.info("creating access user for {} ...".format(db_name))
if not admin_db.ensure_user(db_url):
logger.error("failed to create user on {} DB".format(db_name))
return -1
try:
db = self.get_db(db_url, db_name, check_access=True)
except Exception as e:
logger.exception(e)
logger.error("failed to connect to {} " +
"with non-admin user".format(db_name))
return -1
db.destroy()
return 0
def create(self, db_name=None):
"""
create a database object
"""
if db_name is None:
db_name = self.db_name
# TODO: do this only for SQLAlchemy
if not self.__conn:
raise osdbError("connection not available")
logger.debug("Create Database '%s' for dialect '%s' ...",
self.db_name, self.dialect)
# all good - it's time to create the database
if self.dialect == "postgres":
self.__conn.connection.connection.set_isolation_level(0)
try:
self.__conn.execute("CREATE DATABASE {}".format(self.db_name))
self.__conn.connection.connection.set_isolation_level(1)
except sqlalchemy.exc.OperationalError as se:
logger.error("cannot create database: {}!".format(se))
else:
self.__conn.execute("CREATE DATABASE {}".format(self.db_name))
logger.debug("success")
return True
def alter_role(self, role_name, role_options=None, role_password=None):
"""
alter attributes of a role object
"""
# TODO: is any other dialect using the "role" concept?
if self.dialect != "postgres":
return False
# TODO: do this only for SQLAlchemy
if not self.__conn:
raise osdbError("connection not available")
return False
if not role_options is None:
sqlcmd = "ALTER ROLE {} WITH {}".format(role_name, role_options)
msg = "Alter role '{}' with options '{}'". \
format(role_name, role_options, self.db_name)
if not role_password is None:
sqlcmd += " PASSWORD '{}'".format(role_password)
msg += " and password '********'"
msg += " on database '{}'".format(self.db_name)
try:
result = self.__conn.execute(sqlcmd)
if result:
logger.info( "{} was successfull".format(msg))
except:
logger.error("%s failed", msg)
return False
return
def __init__(self, db_url, db_name):
"""
constructor
"""
self.db_url = db_url
self.db_name = db_name
self.dialect = osdb.get_dialect(db_url)
self.Session = sessionmaker()
self.__engine = None
self.__conn = None
# TODO: do this only for SQLAlchemy
try:
if self.dialect == "postgresql":
self.__engine = sqlalchemy.create_engine(db_url, isolation_level='AUTOCOMMIT')
else:
self.__engine = sqlalchemy.create_engine(db_url)
self.__conn = self.__engine.connect()
# connect the Session object to our engine
self.Session.configure(bind=self.__engine)
# instanciate the Session object
self.__session = self.Session()
except sqlalchemy.exc.OperationalError as se:
logger.error("cannot connect to DB server: {}!".format(se))
raise osdbError("unable to connect to the database")
except sqlalchemy.exc.NoSuchModuleError:
raise osdbError("cannot handle {} dialect".
format(self.dialect))
except sqlalchemy.exc.ArgumentError:
raise osdbArgumentError("bad DB URL: {}".format(
self.db_url))
def cmdloop(self, intro=None):
"""
command loop, catching SIGINT
"""
if self.execute:
if len(self.command) < 1:
logger.error("no modules to run specified!")
return -1
if len(self.command) < 2:
logger.debug("no method to in '{}' run specified!".
format(self.command[0]))
command = None
params = None
else:
command = self.command[1]
params = self.command[2:]
logger.debug("running in non-interactive mode '{}'".format(self.command))
ret = self.run_command(self.command[0], command, params)
# assume that by default it exists with success
if ret is None:
ret = 0
return ret
while True:
try:
super(OpenSIPSCLIShell, self).cmdloop(intro='')
break
except KeyboardInterrupt:
print('^C')
# any other commands exits with negative value
return -1
def update(self, table, update_keys, filter_keys=None):
"""
update table
"""
# TODO: do this only for SQLAlchemy
if not self.__conn:
raise osdbError("connection not available")
update_str = ""
for k, v in update_keys.items():
update_str += ", {} = ".format(k)
if type(v) == int:
update_str += v
else:
update_str += "'{}'".format(
v.translate(str.maketrans({'\'': '\\\''})))
where_str = self.get_where(filter_keys)
statement = "UPDATE {} SET {}{}".format(table,
update_str[2:], where_str)
try:
result = self.__conn.execute(statement)
except sqlalchemy.exc.SQLAlchemyError as ex:
logger.error("cannot execute query: {}".format(statement))
logger.error(ex)
return False
return result
def grant_db_options(self, role_name="opensips", role_options="ALL PRIVILEGES"):
"""
assign attibutes to a role object (PostgreSQL specific)
"""
# TODO: is any other dialect using the "role" concept?
if self.dialect != "postgres":
return False
# TODO: do this only for SQLAlchemy
if not self.__conn:
raise osdbError("connection not available")
return False
logger.debug("Role '%s' will be granted with options '%s' on database '%s'",
role_name, role_options, self.db_name)
sqlcmd = "GRANT {} ON DATABASE {} TO {}".format(role_options, self.db_name, role_name)
try:
result = self.__conn.execute(sqlcmd)
if result:
logger.info("granted options '%s' to role '%s' on database '%s'",
role_options, role_name, self.db_name)
except:
logger.error("granting options '%s' to role '%s' on database '%s' failed",
role_options, role_name, self.db_name)
return False
return
def get_admin_db_url(self, db_name):
engine = osdb.get_db_engine()
if not engine:
return None
if cfg.exists('database_admin_url'):
admin_url = cfg.get("database_admin_url")
if engine == "postgres":
admin_url = osdb.set_url_db(admin_url, 'postgres')
else:
if engine == 'postgres':
if getuser() != "postgres":
logger.error("Command must be run as 'postgres' user: "******"sudo -u postgres opensips-cli ...")
return None
"""
For PG, do the initial setup using 'postgres' as role + DB
"""
admin_url = "postgres://postgres@localhost/postgres"
else:
admin_url = "{}://root@localhost".format(engine)
if osdb.get_url_pswd(admin_url) is None:
pswd = getpass("Password for admin {} user ({}): ".format(
osdb.get_url_driver(admin_url, capitalize=True),
osdb.get_url_user(admin_url)))
logger.debug("read password: '******'", pswd)
admin_url = osdb.set_url_password(admin_url, pswd)
logger.debug("admin DB URL: '{}'".format(admin_url))
return admin_url
def create_role(self, role_name, role_password, update=False,
role_options="NOCREATEDB NOCREATEROLE LOGIN"):
"""
create a role object (PostgreSQL secific)
"""
# TODO: is any other dialect using the "role" concept?
if self.dialect != "postgres":
return False
# TODO: do this only for SQLAlchemy
if not self.__conn:
raise osdbError("connection not available")
if update:
sqlcmd = "ALTER USER {} WITH PASSWORD '{}' {}".format(
role_name, role_password, role_options)
else:
sqlcmd = "CREATE ROLE {} WITH {} PASSWORD '{}'".format(
role_name, role_options, role_password)
logger.info(sqlcmd)
try:
result = self.__conn.execute(sqlcmd)
if result:
logger.info("role '{}' with options '{}' created".
format(role_name, role_options))
except Exception as e:
logger.exception(e)
logger.error("creation of new role '%s' with options '%s' failed",
role_name, role_options)
return False
return result
def parse(self, in_file):
if not in_file:
logger.info("no config file used!")
elif os.path.isfile(in_file) and os.access(in_file, os.R_OK):
self.config.read(in_file)
else:
logger.error("Either file is missing or is not readable.")
def do_trap(self, params):
self.pids = []
self.gdb_outputs = {}
self.process_info = ""
trap_file = cfg.get("trap_file")
logger.info("Trapping {} in {}".format(PROCESS_NAME, trap_file))
if params and len(params) > 0:
self.pids = params
else:
thread = Thread(target=self.get_pids)
thread.start()
thread.join(timeout=1)
if len(self.pids) == 0:
logger.warning("could not get OpenSIPS pids through MI!")
try:
ps_pids = subprocess.check_output(["pidof",PROCESS_NAME])
self.pids = ps_pids.decode().split()
except:
logger.warning("could not find any OpenSIPS running!")
self.pids = []
if len(self.pids) < 1:
logger.error("could not find OpenSIPS' pids")
return -1
logger.debug("Dumping PIDs: {}".format(", ".join(self.pids)))
threads = []
for pid in self.pids:
thread = Thread(target=self.get_gdb_output, args=(pid,))
thread.start()
threads.append(thread)
for thread in threads:
thread.join()
if len(self.gdb_outputs) == 0:
logger.error("could not get output of gdb")
return -1
with open(trap_file, "w") as tf:
tf.write(self.process_info)
for pid in self.pids:
if pid not in self.gdb_outputs:
logger.warning("No output from pid {}".format(pid))
continue
try:
procinfo = subprocess.check_output(
["ps", "--no-headers", "-ww", "-fp", pid]).decode()[:-1]
except:
procinfo = "UNKNOWN"
tf.write("\n\n---start {} ({})\n{}".
format(pid, procinfo, self.gdb_outputs[pid]))
print("Trap file: {}".format(trap_file))
def do_set(self, line):
parsed = line.split('=', 1)
if len(parsed) < 2:
logger.error("setting value format is 'key=value'!")
return
key = parsed[0]
value = parsed[1]
cfg.set(key, value)
def initialize():
global comm_handler
comm_type = cfg.get('communication_type')
comm_func = 'opensipscli.communication.{}'.format(comm_type)
try:
comm_handler = __import__(comm_func, fromlist=[comm_type])
except ImportError as ie:
comm_handler = None
logger.error("cannot import '{}' handler: {}".format(comm_type, ie))
def ask_db_url(self):
db_url = cfg.read_param("template_url",
"Please provide the URL of the SQL database")
if db_url is None:
print()
logger.error("no URL specified: aborting!")
return -1
return db_url
def do_set(self, line):
"""
handle dynamic settings (key-value pairs)
"""
parsed = line.split('=', 1)
if len(parsed) < 2:
logger.error("setting value format is 'key=value'!")
return
key = parsed[0]
value = parsed[1]
cfg.set(key, value)
def do_switch(self, params):
if len(params) == 0:
return
new_instance = params[0]
if cfg.has_instance(new_instance):
cfg.set_instance(new_instance)
else:
logger.error(
"cannot switch to instance '{}': instance not found!".format(
new_instance))
return -1
def execute(cmd, params=[]):
global comm_handler
try:
ret = comm_handler.execute(cmd, params)
except communication.jsonrpc_helper.JSONRPCError as ex:
logger.error("command '{}' returned {}".format(cmd, ex))
return None
except communication.jsonrpc_helper.JSONRPCException as ex:
logger.error("communication exception for '{}' returned {}".format(
cmd, ex))
return None
return ret
def do_migrate(self, params):
if len(params) < 2:
print("Usage: database migrate <old-database> <new-database>")
return 0
old_db = params[0]
new_db = params[1]
db_url = self.ask_db_url()
if db_url is None:
return -1
# create an object store database instance
db = self.get_db(db_url, old_db)
if db is None:
return -1
if not db.exists(old_db):
logger.error(
"the source database ({}) does not exist!".format(old_db))
return -2
print("Creating database {}...".format(new_db))
if self._do_create_db([new_db], db_url=db_url) < 0:
return -1
if self.create_tables(new_db, db_url=db_url) < 0:
return -1
# get schema path for active database dialect
db_schema = db.db_url.split(":")[0]
schema_path = self.get_schema_path(db_schema)
if schema_path is None:
return -1
# get schema scripts for active database dialect
db_schema = db.db_url.split(":")[0]
migrate_scripts = self.get_migrate_scripts_path(db_schema)
if migrate_scripts is None:
logger.debug("migration scripts for db_schema '%s' not found",
db_schema)
return -1
else:
logger.debug("migration scripts for db_schema: '%s'",
migrate_scripts)
print("Migrating all matching OpenSIPS tables...")
db.migrate(migrate_scripts, old_db, new_db, MIGRATE_TABLES_24_TO_30)
print("Finished copying OpenSIPS table data " +
"into database '{}'!".format(new_db))
db.destroy()
return True
def do_migrate(self, params):
if len(params) < 2:
print("Usage: database migrate <old-database> <new-database>")
return 0
old_db = params[0]
new_db = params[1]
admin_url = self.get_admin_db_url(new_db)
if not admin_url:
return -1
db = self.get_db(admin_url, new_db)
if not db:
return -1
if db.dialect != "mysql":
logger.error("'migrate' is only available for MySQL right now! :(")
return -1
if not db.exists(old_db):
logger.error(
"the source database ({}) does not exist!".format(old_db))
return -2
print("Creating database {}...".format(new_db))
if self.create_db(new_db, admin_url, db) < 0:
return -1
if self.create_tables(new_db, db, admin_url) < 0:
return -1
backend = osdb.get_url_driver(admin_url)
# obtain the DB schema files for the in-use backend
schema_path = self.get_schema_path(backend)
if schema_path is None:
return -1
migrate_scripts = self.get_migrate_scripts_path(backend)
if migrate_scripts is None:
logger.debug("migration scripts for %s not found", backend)
return -1
else:
logger.debug("migration scripts for %s", migrate_scripts)
print("Migrating all matching OpenSIPS tables...")
db.migrate(migrate_scripts, old_db, new_db, MIGRATE_TABLES_24_TO_30)
print("Finished copying OpenSIPS table data " +
"into database '{}'!".format(new_db))
db.destroy()
return True
def get_db_engine():
if cfg.exists('database_admin_url'):
engine = osdb.get_url_driver(cfg.get('database_admin_url'))
elif cfg.exists('database_url'):
engine = osdb.get_url_driver(cfg.get('database_url'))
else:
engine = "mysql"
if engine not in SUPPORTED_BACKENDS:
logger.error("bad database engine ({}), supported: {}".format(
engine, " ".join(SUPPORTED_BACKENDS)))
return None
return engine
def drop(self):
# TODO: do this only for SQLAlchemy
if not self.conn:
raise osdbError("connection not available")
database_url = "{}/{}".format(self.db_url, self.db_name)
try:
if sqlalchemy_utils.drop_database(database_url):
return True
except sqlalchemy.exc.NoSuchModuleError as me:
logger.error("cannot check if database {} exists: {}".
format(self.db_name, me))
raise osdbError("cannot handle {} dialect".
format(self.dialect)) from None
def get_migrate_scripts_path(self, db_schema):
if self.db_path is not None:
scripts = [
os.path.join(self.db_path, db_schema, 'table-migrate.sql'),
os.path.join(self.db_path, db_schema, 'db-migrate.sql'),
]
if any(not os.path.isfile(i) for i in scripts):
logger.error("The SQL migration scripts are missing! " \
"Please pull the latest OpenSIPS packages!")
return None
return scripts
