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)
