def main():
acs = analyze_columns()
sys.stdout.write('-- Running column analysis.\n')
acs.execute()
if acs.cmd_results.stdout != '':
if acs.args_handler.args.output_format == 1:
rows = []
headers = True
for line in acs.cmd_results.stdout.split('\n'):
if line == '':
continue
row = line.split('|')
if headers:
row = [col.replace('_', '\n') for col in row]
headers = False
rows.append(row)
print(tabulate(rows, headers="firstrow"))
else:
sys.stdout.write(acs.cmd_results.stdout)
if acs.cmd_results.stderr != '':
Common.error(acs.cmd_results.stderr, no_exit=True)
else:
sys.stdout.write('-- Completed column analysis.\n')
exit(acs.cmd_results.exit_code)
def execute(self):
dbs = self.get_dbs()
self.db_filter_args.schema_set_all_if_none()
db_ct = 0
broken_view_ct = 0
sys.stdout.write('-- Running broken view check.\n')
for db in dbs:
db_ct += 1
cmd_results = self.db_conn.call_stored_proc_as_anonymous_block(
'yb_check_db_views_p',
args={'a_filter': self.db_filter_sql()},
pre_sql=('\c %s\n' % db))
if cmd_results.exit_code == 0:
sys.stdout.write(Common.quote_object_paths(cmd_results.stdout))
elif cmd_results.stderr.find('permission denied') == -1:
Common.error(cmd_results.stderr)
exit(cmd_results.exit_code)
db_broken_view_ct = len(cmd_results.stdout.split())
broken_view_ct += db_broken_view_ct
sys.stdout.write('-- %d broken view/s in "%s".\n' %
(db_broken_view_ct, db))
sys.stdout.write(
'-- Completed check, found %d broken view/s in %d db/s.\n' %
(broken_view_ct, db_ct))
def additional_args_process(self):
if self.args_handler.args.create_dst_table:
self.src_to_dst_table_ddl(self.args_handler.args.src_table,
self.args_handler.args.dst_table,
self.src_conn, self.dst_conn,
self.args_handler)
print('-- created destination table: %s' %
self.args_handler.args.dst_table)
#thread use may have severe impact on the YB cluster, so I'm limiting it to super users
if (self.args_handler.args.threads > 1
and not self.src_conn.ybdb['is_super_user']
and not self.dst_conn.ybdb['is_super_user']):
Common.error(
Text.color(
"The '--threads' option is only supported for YBDB super users.",
'yellow'))
if (self.args_handler.args.chunk_rows
and self.src_conn.ybdb['version_major'] < 4):
Common.error(
Text.color(
"The '--chunk_rows' option is only supported on YBDB version 4 or higher."
" The source db is running YBDB %s..." %
self.src_conn.ybdb['version'], 'yellow'))
self.log_file_name_template = (
'{}{}{}_{}_{{{{CofC}}}}{{{{TofT}}}}_{{log_type}}.log'.format(
('%s/' % self.args_handler.args.log_dir
if self.args_handler.args.log_dir else ''),
('%s_' % self.args_handler.args.log_prefix
if self.args_handler.args.log_prefix else ''),
datetime.now().strftime("%Y%m%d_%H%M%S"),
"%04d" % random.randint(0, 9999)))
def main():
ts = table_skew()
if not ts.db_conn.ybdb['is_super_user']:
Common.error('must be run by a database super user...')
print(ts.execute())
exit(0)
def additional_args_process(self):
if (not (bool(self.args_handler.args.query) or
(self.args_handler.args.drop))
or (bool(self.args_handler.args.query) and
(self.args_handler.args.drop))):
self.args_handler.args_parser.error(
'one of --query or the --drop options must be provided...')
self.stored_proc = self.args_handler.args.stored_proc
# refomrat roles, double quote names with upper case, change list to comma delimited string
if isinstance(self.args_handler.args.grant_execute_to, list):
self.args_handler.args.grant_execute_to = '\n'.join(
self.args_handler.args.grant_execute_to)
self.args_handler.args.grant_execute_to = (Common.quote_object_paths(
self.args_handler.args.grant_execute_to).replace('\n', ', '))
def execute(self):
sql_query = """
WITH
tbl AS (
SELECT
DECODE(
LOWER(t.distribution)
, 'hash', t.distribution_key
, UPPER(t.distribution)
) AS distribution
, c.relname AS tablename
, n.nspname AS schemaname
, pg_get_userbyid(c.relowner) AS ownername
FROM {database}.pg_catalog.pg_class AS c
LEFT JOIN {database}.pg_catalog.pg_namespace AS n
ON n.oid = c.relnamespace
LEFT JOIN {database}.sys.table AS t
ON c.oid = t.table_id
WHERE
c.relkind = 'r'::CHAR
)
SELECT
distribution
FROM
tbl
WHERE
distribution IS NOT NULL
AND {filter_clause}""".format(
filter_clause = self.db_filter_sql()
, database = self.db_conn.database)
self.cmd_results = self.db_conn.ybsql_query(sql_query)
self.cmd_results.on_error_exit()
if self.cmd_results.stdout != '':
if self.cmd_results.stdout.strip() in ('RANDOM', 'REPLICATED'):
sys.stdout.write(self.cmd_results.stdout)
else:
sys.stdout.write(Common.quote_object_paths(self.cmd_results.stdout))
def execute_create(self):
print('-- Creating the %s stored procedure for the query provided.' %
Text.color(self.stored_proc, style='bold'))
stored_proc_template_file = (
'%s/%s' % (Common.util_dir_path,
'sql/yb_query_to_stored_proc_template_1_p.sql'))
stored_proc_template = Common.read_file(stored_proc_template_file)
self.cmd_results = self.db_conn.call_stored_proc_as_anonymous_block(
'yb_query_to_stored_proc_p',
args={
'a_query': self.args_handler.args.query,
'a_stored_proc_name': self.stored_proc,
'a_stored_proc_template': stored_proc_template,
'a_max_varchar_size': self.args_handler.args.max_varchar_size,
'a_limit_default': self.args_handler.args.query_limit_default,
'a_grant_execute_to': self.args_handler.args.grant_execute_to
},
pre_sql=self.args_handler.args.pre_sql,
post_sql=self.args_handler.args.post_sql)
self.cmd_results.write(tail='-- Created.\n')
def chunk_table_unload_sql(self, table_unload_sql):
self.args_handler.args.dml = ("%s AND <chunk_where_clause>" %
table_unload_sql)
self.args_handler.args.execute_chunk_dml = False
self.args_handler.args.verbose_chunk_off = False
self.args_handler.args.null_chunk_off = False
self.args_handler.args.print_chunk_dml = True
self.args_handler.args.table = Common.quote_object_paths(
self.args_handler.args.src_table)
self.args_handler.args.column = 'rowunique'
self.args_handler.args.column_cardinality = 'high'
if self.args_handler.args.where_clause:
self.args_handler.args.table_where_clause = self.args_handler.args.where_clause
else:
self.args_handler.args.table_where_clause = 'TRUE'
cdml = chunk_dml_by_integer(db_conn=self.src_conn,
args_handler=self.args_handler)
cdml.execute()
if cdml.cmd_results.exit_code:
cdml.cmd_results.write()
exit(cdml.cmd_results.exit_code)
return cdml.cmd_results.stdout.strip().split('\n')
def init_args(self):
"""Initialize the args class.
This initialization performs argument parsing.
It also provides access to functions such as logging and command
execution.
:return: An instance of the `args` class
"""
cnfg = Util.config_default.copy()
cnfg['description'] = 'Run unit test cases on utility.'
cnfg['positional_args_usage'] = None
args_handler = ArgsHandler(cnfg, init_default=False)
args_handler.args_process_init()
args_handler.args_add_positional_args()
args_handler.args_add_optional()
args_test_required_grp = args_handler.args_parser.add_argument_group(
'test required arguments')
args_test_required_grp.add_argument(
"--test_name",
"--tn",
"-t",
dest="name",
help="the test case name to run, like 'yb_get_table_name'"
" for the test case file 'test_cases__yb_get_table_name.py'")
args_test_required_grp.add_argument(
"--all",
action="store_true",
help="run test cases for all the test case files")
args_test_optional_grp = args_handler.args_parser.add_argument_group(
'test optional arguments')
args_test_optional_grp.add_argument(
"--host",
"-h",
"-H",
dest="host",
help="database server hostname,"
" overrides YBHOST env variable, the host where the tests are run")
args_test_optional_grp.add_argument(
"--case",
type=int,
default=None,
help="unit test case number to execute")
args_test_optional_grp.add_argument(
"--print_test",
"--pt",
action="store_true",
help="instead of the test command display what the test ran")
args_test_optional_grp.add_argument("--print_output",
"--po",
action="store_true",
help="print the test output")
args_test_optional_grp.add_argument(
"--print_diff",
"--pd",
action="store_true",
help="if the test fails, print the diff of the expected"
" verse actual result")
args_test_optional_grp.add_argument(
"--python_exe",
default=None,
help="python executable to run tests with, this allows testing"
" with different python versions, defaults to 'python3'")
args = args_handler.args_process()
if args.python_exe:
if os.access(args.python_exe, os.X_OK):
cmd_results = Cmd('%s --version' % args.python_exe)
self.test_py_version = (int(
cmd_results.stderr.split(' ')[1].split('.')[0]))
else:
Common.error("'%s' is not found or not executable..." %
args.python_exe)
else:
self.test_py_version = 3
if not args.host and os.environ.get("YBHOST"):
args.host = os.environ.get("YBHOST")
elif not args.host and len(self.config.hosts) == 1:
args.host = self.config.hosts[0]
if (args.host and not (args.host in self.config.hosts)):
Common.error(
"the '%s' host is not configured for testing,"
" run 'test_create_host_objects.py' to create host db objects for testing"
% args.host,
color='white')
elif len(self.config.hosts) == 0:
Common.error(
"currently there are no hosts configures for testing,"
" run 'test_create_host_objects.py' to create host db objects for testing",
color='white')
elif len(self.config.hosts) > 1 and not args.host:
Common.error(
"currently there is more than 1 host(%s) configures for testing,"
" use the --host option or YBHOST environment variable to select a host"
% self.config.hosts,
color='white')
if bool(args.name) == args.all: # exclusive or
Common.error(
"either the option --test_name or --all must be specified not both"
)
self.test_case_files = []
if args.name:
test_case_file_path = '%s/test_cases__%s.py' % (path, args.name)
if os.access(test_case_file_path, os.R_OK):
self.test_case_files.append(test_case_file_path)
else:
Common.error("test case '%s' has no test case file '%s'..." %
(args.name, test_case_file_path))
else:
for test_case_file_path in glob.glob("%s/test_cases__*.py" % path):
if os.access(test_case_file_path, os.R_OK):
self.test_case_files.append(test_case_file_path)
self.test_case_files.sort()
self.args = args
return args
def execute(self):
table_unload_sql = "SELECT * FROM {src_table} WHERE TRUE{where_clause}".format(
src_table=Common.quote_object_paths(
self.args_handler.args.src_table).replace('"', '"\\""'),
where_clause=(' AND %s' % self.args_handler.args.where_clause
if self.args_handler.args.where_clause else ''))
if self.args_handler.args.chunk_rows:
chunks_sql = self.chunk_table_unload_sql(table_unload_sql)
else:
chunks_sql = [table_unload_sql]
os.environ['SRC_YBPASSWORD'] = self.src_conn.env['pwd']
os.environ['DST_YBPASSWORD'] = self.dst_conn.env['pwd']
total_chunks = len(chunks_sql)
format_CofC = 'chunk%.0{len}dof%.0{len}d'.format(
len=len(str(total_chunks)))
total_threads = self.args_handler.args.threads
format_TofT = '_thread%.0{len}dof%.0{len}d'.format(
len=len(str(total_threads)))
TofT = ''
thread_clause = ''
for chunk in range(1, total_chunks + 1):
unload_sql = chunks_sql[chunk - 1]
CofC = format_CofC % (chunk, total_chunks)
cmd_threads = []
for thread in range(1, total_threads + 1):
if total_threads > 1:
thread_clause = ' AND /* thread_clause(thread: %d) >>>*/ rowunique %% %d = %d /*<<< thread_clause */' % (
thread, total_threads, thread - 1)
TofT = format_TofT % (thread, total_threads)
copy_cmd = self.table_copy_cmd.format(
unload_sql=unload_sql.rstrip().rstrip(';') + thread_clause,
CofC=CofC,
TofT=TofT)
cmd = Cmd(copy_cmd, False, wait=False)
cmd.TofT = TofT
cmd_threads.append(cmd)
thread_exit_code = 0
thread_failed = False
for cmd in cmd_threads:
cmd.wait()
loaded = False
print('-- %s%s' % (CofC, cmd.TofT))
if cmd.exit_code == 0:
ybload_log_file_name = self.log_file_name_template.format(
log_type='ybload').format(CofC=CofC, TofT=cmd.TofT)
file = open(ybload_log_file_name, "r")
for line in file:
if re.search('SUCCESSFUL BULK LOAD', line):
loaded = True
sys.stdout.write(line)
break
if not loaded:
cmd.write()
log_file_name = self.log_file_name_template.format(
log_type='*').format(CofC=CofC, TofT=cmd.TofT)
print('Table Copy {}, please review the log files: {}'.
format(Text.color('Failed', 'red'), log_file_name))
thread_exit_code = cmd.exit_code
thread_failed = True
if thread_failed:
exit(thread_exit_code)
del os.environ['SRC_YBPASSWORD']
del os.environ['DST_YBPASSWORD']
exit(0)
def build_table_copy_cmd(self):
ybunload_env = "YBPASSWORD=$SRC_YBPASSWORD"
ybunload_cmd = (
"ybunload"
" -h {src_host}"
"{port_option}"
" -U {src_user}"
" -d {src_db}"
" --delimiter '{delimiter}'"
" --stdout true"
" --quiet true"
" --logfile {log_file_name}"
"{additionl_options}"
""" --select "{{unload_sql}}" """).format(
src_host=self.src_conn.env['host'],
port_option=(' --port %s' % self.args_handler.args.src_port
if self.args_handler.args.src_port else ''),
src_user=self.src_conn.env['dbuser'],
src_db=self.src_conn.database,
delimiter=self.args_handler.args.delimiter,
log_file_name=(self.log_file_name_template.format(
log_type='ybunload')),
additionl_options=(' %s' %
self.args_handler.args.ybunload_options
if self.args_handler.args.ybunload_options
else ''))
if (self.args_handler.args.ybload_options and re.search(
'logfile-log-level', self.args_handler.args.ybload_options,
re.IGNORECASE)):
#the user has set their own log level in ybload_options
logfile_log_level_option = ''
else:
#set default log level
logfile_log_level_option = ' --logfile-log-level INFO'
ybload_env = "YBPASSWORD=$DST_YBPASSWORD"
ybload_cmd = (
"ybload"
" -h {dst_host}"
"{port_option}"
" -U {dst_user}"
" -d {dst_db}"
" -t '{dst_table}'"
" --delimiter '{delimiter}'"
" --log-level OFF" #turns off console logging
"{logfile_log_level_option}"
" --logfile {log_file_name}"
" --bad-row-file {bad_log_file_name}"
"{additionl_options}"
" -- -").format(
dst_host=self.dst_conn.env['host'],
port_option=(' --port %s' % self.args_handler.args.dst_port
if self.args_handler.args.dst_port else ''),
dst_user=self.dst_conn.env['dbuser'],
dst_db=self.dst_conn.database,
dst_table=Common.quote_object_paths(
self.args_handler.args.dst_table),
delimiter=self.args_handler.args.delimiter,
log_file_name=(self.log_file_name_template.format(
log_type='ybload')),
logfile_log_level_option=logfile_log_level_option,
bad_log_file_name=(self.log_file_name_template.format(
log_type='ybload_bad')),
additionl_options=(' %s' %
self.args_handler.args.ybload_options
if self.args_handler.args.ybload_options
else ''))
self.table_copy_cmd = ("{ybunload_env} {ybunload_cmd}"
" | {ybload_env} {ybload_cmd}").format(
ybunload_env=ybunload_env,
ybunload_cmd=ybunload_cmd,
ybload_env=ybload_env,
ybload_cmd=ybload_cmd)
def ddl_modifications(self, ddl, args):
"""
Modify a given DDL statement by optionally adding db/schema name to a
CREATE statement and transforming all SQL reserved words to uppercase.
:param ddl: The DDL statement to modify
:param args: The command line args after being processed
:return: A string containing the modified DDL statement
"""
new_ddl = []
ddl_schema = ''
for line in ddl.split('\n'):
token = line.split(':')
if token[0] == '-- Schema':
ddl_schema = token[1].strip()
#add schema and database to object name and quote name where needed
matches = re.match(r"\s*CREATE\s*([^\s]*)\s*([^\s(]*)(.*)", line,
re.MULTILINE)
if matches:
tablepath = matches.group(2)
if args.with_schema or args.with_db:
tablepath = ((args.schema_name if args.schema_name else
ddl_schema) + '.' + tablepath)
if args.with_db:
tablepath = ((args.db_name if args.db_name else
self.db_conn.database) + '.' + tablepath)
tablepath = Common.quote_object_paths(tablepath)
line = 'CREATE %s %s%s' % (matches.group(1), tablepath,
matches.group(3))
#change all data type key words to upper case
d_types = [
'bigint', 'integer', 'smallint', 'numeric', 'real',
'double precision', 'uuid', 'character varying', 'character',
'date', 'time without time zone',
'timestamp without time zone', 'timestamp with time zone',
'ipv4', 'ipv6', 'macaddr', 'macaddr8', 'boolean'
]
for data_type in d_types:
line = re.sub(r"( )" + data_type + r"(,?$|\()",
r"\1%s\2" % data_type.upper(), line)
new_ddl.append(line)
new_ddl = '\n'.join(new_ddl).strip() + '\n'
if self.object_type in ('stored_proc',
'view') and self.args_handler.args.or_replace:
typ = {
'view': 'VIEW',
'stored_proc': 'PROCEDURE'
}[self.object_type]
new_ddl = new_ddl.replace('CREATE %s' % typ,
'CREATE OR REPLACE %s' % typ)
#remove DDL comments at the beginning of each object definition
new_ddl = re.sub(r"--( |-).*?\n", "", new_ddl)
#correct trailing ';' at end of each definition to be consistent
new_ddl = re.sub(r"(\s*);", ";", new_ddl)
return new_ddl
def __init__(self):
args_handler = self.init_args()
args_handler.args.conn_db = 'yellowbrick'
db_conn = DBConnect(args_handler)
if not (db_conn.ybdb['has_create_user']
and db_conn.ybdb['has_create_db']):
Common.error(
'You must login as a user with create database/'
'user permission to drop the test database/user objects...')
configFilePath = '%s/%s' % (os.path.expanduser('~'), '.YbEasyCli')
config = configparser.ConfigParser()
config.read(configFilePath)
section = '%s_%s' % ('test', db_conn.env['host'])
if config.has_section(section):
test_user = config.get(section, 'user')
print(
"\nDropping the '%s' test environment, including the '%s' and '%s' databases.\n"
% (Text.color(db_conn.env['host'], 'cyan'),
Text.color(config.get(section, 'db1'), 'cyan'),
Text.color(config.get(section, 'db2'), 'cyan')))
answered_yes = input(
" Enter(yes) to continue: ").lower() == 'yes'
if answered_yes:
cmd_results = db_conn.ybsql_query(
"DROP DATABASE IF EXISTS {db1}; DROP DATABASE IF EXISTS {db2};"
.format(db1=config.get(section, 'db1'),
db2=config.get(section, 'db2')))
cmd_results.write()
if cmd_results.stderr != '':
exit(cmd_results.exit_code)
print("\nDropped databases '%s' and '%s', if they existed..." %
(Text.color(config.get(section, 'db1'), 'cyan'),
Text.color(config.get(section, 'db2'), 'cyan')))
config.remove_section(section)
config_fp = open(configFilePath, 'w')
config.write(config_fp)
config_fp.close()
os.chmod(configFilePath, stat.S_IREAD | stat.S_IWRITE)
answered_yes = input(
"\n Enter(yes) to drop user '%s': " %
Text.color(test_user, 'cyan')).lower() == 'yes'
if answered_yes:
cmd_results = db_conn.ybsql_query(
"DROP USER IF EXISTS %s" % test_user)
cmd_results.write()
if cmd_results.stderr != '':
exit(cmd_results.exit_code)
print("\nDropped user '%s', if existed..." %
(Text.color(test_user, 'cyan')))
else:
print(Text.color('\nExiting without clean up...', 'yellow'))
else:
Common.error(
"There is no test environment setup for '%s' in your"
" '~/.YbEasyCli file', run 'test_create_host_objects.py'"
" to set up this host" % db_conn.env['host'],
color='yellow')
def __init__(self):
args_handler = self.init_args()
args_handler.args.conn_db = 'yellowbrick'
DBConn = DBConnect(args_handler)
if (not (DBConn.ybdb['is_super_user'])
and not (DBConn.ybdb['has_create_user']
and DBConn.ybdb['has_create_db'])):
Common.error(
'You must login as a user with create database/'
'user permission to create all the required test database objects...'
)
configFilePath = '%s/%s' % (os.path.expanduser('~'), '.YbEasyCli')
config = configparser.ConfigParser()
config.read(configFilePath)
section = '%s_%s' % ('test', DBConn.env['host'])
if config.has_section(section):
Common.error(
"A test environment has already been set up for '%s',"
" first run test_drop_host_objects.py to clean up the old host objects or cleanup '%s'..."
% (DBConn.env['host'], configFilePath),
color='yellow')
print(
'\nThe util testing framework requires a test user and 2 test databases.\n'
"The test user may be an existing user, if the user doesn't exist it will be created.\n"
"If you continue the test user password will be stored in the ~/.YbEasyCli file.\n"
"Additionally, 2 test databases will be created, these databases must not already exist.\n"
)
config.add_section(section)
while True:
test_user = input(" Supply the DB test user name: ")
if test_user != '':
config.set(section, 'user', test_user)
break
while True:
test_pwd = getpass.getpass(" Supply the password for '%s': " %
(Text.color(test_user, 'cyan')))
if test_pwd != '':
config.set(section, 'password', test_pwd)
break
cmd_results = DBConn.ybsql_query(
"""SELECT TRUE FROM sys.user WHERE name = '%s'""" % (test_user))
if cmd_results.stdout.strip() == 't':
# exits on failed connection
self.get_DBConn(test_user, test_pwd, DBConn.env['conn_db'],
DBConn.env['host'])
#test_user_DBConn = self.get_DBConn(test_user, test_pwd
# , DBConn.env['conn_db'], DBConn.env['host'], on_fail_exit=False)
else:
cmd_results = DBConn.ybsql_query(
"""CREATE USER %s PASSWORD '%s'""" % (test_user, test_pwd))
cmd_results.on_error_exit()
print("\nCreated database user '%s'..." %
Text.color(test_user, 'cyan'))
self.test_user_login(test_user, test_pwd, DBConn.env['conn_db'],
DBConn.env['host'])
while True:
test_db_prefix = input(
"\n Supply the database prefix for the 2 test dbs: ")
if test_db_prefix != '':
test_db1 = '%s_db1' % test_db_prefix
test_db2 = '%s_db2' % test_db_prefix
config.set(section, 'db1', test_db1)
config.set(section, 'db2', test_db2)
break
cmd_results = DBConn.ybsql_query(
"CREATE DATABASE {db1} ENCODING=LATIN9; CREATE DATABASE {db2} ENCODING=UTF8;"
" ALTER DATABASE {db1} OWNER TO {user};"
" ALTER DATABASE {db2} OWNER TO {user};"
" GRANT CONNECT ON DATABASE {db1} TO {user};"
" GRANT CONNECT ON DATABASE {db2} TO {user};".format(
db1=test_db1, db2=test_db2, user=test_user))
cmd_results.on_error_exit()
print("\nCreated '%s' as LATIN9 DB..." % Text.color(test_db1, 'cyan'))
print("Created '%s' as UTF8 DB..." % Text.color(test_db2, 'cyan'))
config_fp = open(configFilePath, 'w')
config.write(config_fp)
config_fp.close()
os.chmod(configFilePath, stat.S_IREAD | stat.S_IWRITE)
self.config = config
self.host = DBConn.env['host']
self.section = section
self.create_db_objects()