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