def init_db(cls, ip): db_url = (f'postgresql+psycopg2://root:{cls.password}@' f'{ip}:5432/postgres') with utils.SQLClient(db_url) as db_client: cmd = "CREATE DATABASE testdb;" db_client.pgsql_execute(cmd) db_url = (f'postgresql+psycopg2://root:{cls.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmds = [ "CREATE TABLE persons (id INT PRIMARY KEY NOT NULL, " "string VARCHAR(255));", ] db_client.pgsql_execute(cmds)
def verify_data_inc(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = (f'postgresql+psycopg2://root:{self.root_password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmd = "select * from persons;" ret = db_client.pgsql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertEqual(2, len(rows)) actual = [] for index in range(2): actual.append(dict(zip(keys, rows[index]))) expected = [ { 'id': 1, 'string': 'Lingxian Kong' }, { 'id': 99, 'string': 'OpenStack' }, ] self.assertEqual(expected, actual)
def create_database(self, name, ip=""): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/postgres') with utils.SQLClient(db_url) as db_client: cmd = f"CREATE DATABASE {name};" db_client.pgsql_execute(cmd)
def verify_data_inc(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmd = "select * from Persons;" ret = db_client.mysql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertEqual(2, len(rows)) actual = [] for index in range(2): actual.append(dict(zip(keys, rows[index]))) expected = [ { 'ID': 1, 'String': 'Lingxian Kong' }, { 'ID': 99, 'String': 'OpenStack' }, ] self.assertEqual(expected, actual)
def insert_data_replication(self, ip): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/postgres') with utils.SQLClient(db_url) as db_client: cmd = "CREATE DATABASE testdb;" db_client.pgsql_execute(cmd) db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmds = [ "CREATE TABLE Persons (ID int, String varchar(255));", "insert into Persons VALUES (1, 'replication');" ] db_client.pgsql_execute(cmds)
def insert_data_inc(cls, ip): db_url = (f'postgresql+psycopg2://root:{cls.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmds = [ "INSERT INTO persons (id,string) VALUES (99, 'OpenStack');" ] db_client.pgsql_execute(cmds)
def get_db_version(self, ip, username=constants.DB_USER, password=constants.DB_PASS): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306' with utils.SQLClient(db_url) as db_client: cmd = "SELECT @@GLOBAL.innodb_version;" ret = db_client.mysql_execute(cmd) return ret.first()[0]
def insert_data_before_rebuild(self, ip): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmds = [ "CREATE TABLE Rebuild (ID int, String varchar(255));", "insert into Rebuild VALUES (1, 'rebuild-data');" ] db_client.pgsql_execute(cmds)
def _access_db(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmd = "SELECT 1;" db_client.mysql_execute(cmd)
def insert_data_upgrade(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmds = ["insert into Persons VALUES (99, 'Upgrade');"] db_client.mysql_execute(cmds)
def get_config_value(self, ip, option): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/postgres') with utils.SQLClient(db_url) as db_client: cmd = f"SELECT setting FROM pg_settings WHERE name='{option}';" ret = db_client.pgsql_execute(cmd) rows = ret.fetchall() self.assertEqual(1, len(rows)) return int(rows[0][0])
def insert_data_inc(cls, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME, **kwargs): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmds = ["insert into Persons VALUES (99, 'OpenStack');"] db_client.mysql_execute(cmds)
def init_db(cls, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmds = [ "CREATE TABLE Persons (ID int, String varchar(255));", ] db_client.mysql_execute(cmds)
def get_db_version(self, ip, username=constants.DB_USER, password=constants.DB_PASS): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/postgres') with utils.SQLClient(db_url) as db_client: cmd = "SHOW server_version;" ret = db_client.pgsql_execute(cmd) version = ret.first()[0] return version.split(' ')[0]
def insert_data_before_rebuild(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmds = [ "CREATE TABLE Rebuild (ID int, String varchar(255));", "insert into Rebuild VALUES (1, 'rebuild-data');" ] db_client.mysql_execute(cmds)
def get_config_value(self, ip, option, username=constants.DB_USER, password=constants.DB_PASS): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306' with utils.SQLClient(db_url) as db_client: cmd = f"show variables where Variable_name in ('{option}');" ret = db_client.mysql_execute(cmd) rows = ret.fetchall() self.assertEqual(1, len(rows)) return int(rows[0][1])
def insert_data(cls, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME, **kwargs): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmds = [ "CREATE TABLE Persons (ID int, String varchar(255));", "insert into Persons VALUES (1, 'Lingxian Kong');", ] db_client.mysql_execute(cmds)
def verify_data(self, ip): db_url = (f'postgresql+psycopg2://root:{self.root_password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmd = "select * from persons;" ret = db_client.pgsql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertEqual(1, len(rows)) result = dict(zip(keys, rows[0])) expected = {'id': 1, 'string': 'Lingxian Kong'} self.assertEqual(expected, result)
def verify_data_after_rebuild(self, ip): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmd = "select * from Rebuild;" ret = db_client.pgsql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertEqual(1, len(rows)) actual = dict(zip(keys, rows[0])) expected = {'id': 1, 'string': 'rebuild-data'} self.assertEqual(expected, actual)
def get_databases(self, instance_id, ip="", **kwargs): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/postgres') with utils.SQLClient(db_url) as db_client: cmd = "SELECT datname FROM pg_catalog.pg_database WHERE " \ "(datistemplate ISNULL OR datistemplate = false);" ret = db_client.pgsql_execute(cmd) rows = ret.fetchall() dbs = [] for row in rows: dbs.append({'name': row[0]}) return dbs
def verify_data_upgrade(self, ip): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmd = "select * from Persons;" ret = db_client.pgsql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertGreaterEqual(len(rows), 1) result = [] for index in range(len(rows)): result.append(dict(zip(keys, rows[index]))) expected = {'id': 99, 'string': 'Upgrade'} self.assert_single_item(result, **expected)
def verify_data_after_rebuild(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmd = "select * from Rebuild;" ret = db_client.mysql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertEqual(1, len(rows)) actual = dict(zip(keys, rows[0])) expected = {'ID': 1, 'String': 'rebuild-data'} self.assertEqual(expected, actual)
def verify_data_upgrade(self, ip, username=constants.DB_USER, password=constants.DB_PASS, database=constants.DB_NAME): db_url = f'mysql+pymysql://{username}:{password}@{ip}:3306/{database}' with utils.SQLClient(db_url) as db_client: cmd = "select * from Persons;" ret = db_client.mysql_execute(cmd) keys = ret.keys() rows = ret.fetchall() self.assertGreaterEqual(len(rows), 1) result = [] for index in range(len(rows)): result.append(dict(zip(keys, rows[index]))) expected = {'ID': 99, 'String': 'Upgrade'} self.assert_single_item(result, **expected)
def insert_data_upgrade(self, ip): db_url = (f'postgresql+psycopg2://root:{self.password}@' f'{ip}:5432/testdb') with utils.SQLClient(db_url) as db_client: cmds = ["insert into Persons VALUES (99, 'Upgrade');"] db_client.pgsql_execute(cmds)