def test_load_sql_db_config(self):
task = isql.ISQLSink(self.args)
dbconf = task.load_sql_db_config(self.dbconfig_tstfyl)
print(dbconf)
assert dbconf is not None
assert len(dbconf) >= 7 # specific to this test file
def test_sql_file_info_comment_str(self):
task = isql.ISQLSink(self.args)
finfo = {
'file_name': 'file-name',
'file_size': 999,
'file_path': '/path/file-name'
}
fics = task.sql_file_info_comment_str(finfo)
assert fics is not None
assert fics == task.SQL_COMMENT + ' file-name 999 /path/file-name'
def test_sql_file_info_comment_str_empty_fi(self):
task = isql.ISQLSink(self.args)
finfo = dict()
fics = task.sql_file_info_comment_str(finfo)
assert fics is not None
assert fics == task.SQL_COMMENT + ' NO_FILENAME 0'
def test_load_sql_db_config_no_uri(self):
task = isql.ISQLSink(self.args)
with pytest.raises(errors.ProcessingError,
match='no database URI .* found'):
task.load_sql_db_config(self.nouri_dbconfig_tstfyl)
def test_load_sql_db_config_empty(self):
task = isql.ISQLSink(self.args)
with pytest.raises(errors.ProcessingError,
match='no database parameters .* found'):
task.load_sql_db_config(self.empty_dbconfig_tstfyl)
def test_load_sql_db_config_bad(self):
task = isql.ISQLSink(self.args)
with pytest.raises(errors.ProcessingError,
match='not found or not readable'):
task.load_sql_db_config(self.nosuch_tstfyl)
def test_file_info_to_comment_string(self):
task = isql.ISQLSink(self.args)
fics = task.file_info_to_comment_string('file-name', 999,
'/path/file-name')
assert fics is not None
assert fics == task.SQL_COMMENT + ' file-name 999 /path/file-name'
def test_file_info_to_comment_string_fpath(self):
task = isql.ISQLSink(self.args)
fics = task.file_info_to_comment_string(None, None, '/tmp/file-name')
assert fics is not None
assert fics == task.SQL_COMMENT + ' /tmp/file-name'
def test_file_info_to_comment_string_fsize(self):
task = isql.ISQLSink(self.args)
fics = task.file_info_to_comment_string(None, 888, None)
assert fics is not None
assert fics == task.SQL_COMMENT + ' 888'
def test_file_info_to_comment_string_fname(self):
task = isql.ISQLSink(self.args)
fics = task.file_info_to_comment_string('file-name', None, None)
assert fics is not None
assert fics == task.SQL_COMMENT + ' file-name'
def test_sql_file_info_comment_str_fpath(self):
task = isql.ISQLSink(self.args)
finfo = {'file_path': '/tmp/file-name'}
fics = task.sql_file_info_comment_str(finfo)
assert fics is not None
assert fics == task.SQL_COMMENT + ' /tmp/file-name'
def test_sql_file_info_comment_str_fsize(self):
task = isql.ISQLSink(self.args)
finfo = {'file_size': 888}
fics = task.sql_file_info_comment_str(finfo)
assert fics is not None
assert fics == task.SQL_COMMENT + ' 888'
class TestFitsPgSql(object):
args = {
'debug': True,
'verbose': True,
'TOOL_NAME': 'TestFitsPgSql',
'catalog_table': 'myCatalog'
}
# load DB test configuration parameters
task = isql.ISQLSink(args)
dbconfig = task.load_sql_db_config(TEST_DBCONFIG_FILEPATH)
print("TestFitsPgSql:dbconfig={}".format(dbconfig))
cat_names = [
"ID", "RA", "DEC", "redshift", "x", "y", "a", "bb", "ccc", "kron_flag"
]
cat_formats = ["A", "D", "E", "F", "I", "J", "K", "L", "X", "Z"]
format_codes = {
'A': 'text',
'D': 'double precision',
'E': 'real',
'F': 'real',
'I': 'smallint',
'J': 'integer',
'K': 'bigint',
'L': 'boolean',
'X': 'bit',
'Z': 'bytea'
}
def test_check_missing_parameters_noreq(self):
miss = pg_gen.check_missing_parameters(self.dbconfig, [])
assert miss is None
def test_check_missing_parameters_nomiss(self):
has_params = ['db_uri', 'db_schema_name', 'db_user']
miss = pg_gen.check_missing_parameters(self.dbconfig, has_params)
assert miss is None
def test_check_missing_parameters_miss(self):
bad_params = ['db_db', 'dba', 'bb', 'CCC']
emsg = f'Missing required .* {bad_params}'
with pytest.raises(errors.ProcessingError, match=emsg):
pg_gen.check_missing_parameters(self.dbconfig, bad_params)
def test_check_missing_parameters_mixed(self):
mix_params = ['db_uri', 'dba', 'bb', 'db_user', 'CCC', 'db_name']
bad_params = ['dba', 'bb', 'CCC']
emsg = f'Missing required .* {bad_params}'
with pytest.raises(errors.ProcessingError, match=emsg):
pg_gen.check_missing_parameters(self.dbconfig, mix_params)
def test_clean_id_empty(self):
with pytest.raises(errors.ProcessingError,
match='cannot be empty or None'):
pg_gen.clean_id(None)
with pytest.raises(errors.ProcessingError,
match='cannot be empty or None'):
pg_gen.clean_id('')
with pytest.raises(errors.ProcessingError,
match='cannot be empty or None'):
pg_gen.clean_id('', '')
def test_clean_id(self):
assert pg_gen.clean_id('_') == '_'
assert pg_gen.clean_id('a') == 'a'
assert pg_gen.clean_id('_a_') == '_a_'
assert pg_gen.clean_id('_ABC_') == '_ABC_'
assert pg_gen.clean_id('abc_') == 'abc_'
assert pg_gen.clean_id('ABCxyz') == 'ABCxyz'
def test_clean_id_remove(self):
assert pg_gen.clean_id('ABC xyz') == 'ABCxyz'
assert pg_gen.clean_id('*ABC;xyz') == 'ABCxyz'
assert pg_gen.clean_id('*ABC;xyz') == 'ABCxyz'
assert pg_gen.clean_id(
'Robert;drop all tables;') == 'Robertdropalltables'
def test_clean_id_allow(self):
letvec = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
assert pg_gen.clean_id('ABC xyz', letvec) == ''
assert pg_gen.clean_id('xyz; ABC', letvec) == ''
assert pg_gen.clean_id('abc xyz', letvec) == 'abc'
assert pg_gen.clean_id('XYZ; abc', letvec) == 'abc'
assert pg_gen.clean_id('Robert;drop all tables;', letvec) == 'bedaabe'
def test_fits_format_to_sql_unsup(self):
for fcode in ['B', 'C', 'M', 'P', 'Q', 'BAD', 'CRAZY']:
with pytest.raises(errors.ProcessingError,
match='is not supported'):
pg_gen.fits_format_to_sql(fcode)
def test_fits_format_to_sql(self):
for fcode in self.format_codes.keys():
dtype = pg_gen.fits_format_to_sql(fcode)
print(dtype)
assert dtype == self.format_codes.get(fcode)
def test_fits_format_to_sql_long(self):
fcodes = [
'A40', 'D3.2', 'E1.0', 'F2.1', 'I4.1', 'J12', 'K128', 'L2', 'X8',
'Z2'
]
dtypes = [pg_gen.fits_format_to_sql(fcode) for fcode in fcodes]
print(dtypes)
assert dtypes == list(self.format_codes.values())
def test_gen_column_decls_sql_empty(self):
sql = pg_gen.gen_column_decls_sql([], [])
print(sql)
assert sql is not None
assert len(sql) == 0
def test_gen_column_decls_sql_nofmt(self):
with pytest.raises(errors.ProcessingError,
match='lists must be the same length'):
pg_gen.gen_column_decls_sql(['NAME'], [])
def test_gen_column_decls_sql_noname(self):
with pytest.raises(errors.ProcessingError,
match='lists must be the same length'):
pg_gen.gen_column_decls_sql([], ['K'])
def test_gen_column_decls_sql_unequal(self):
with pytest.raises(errors.ProcessingError,
match='lists must be the same length'):
pg_gen.gen_column_decls_sql(['f1', 'f2', 'f3'], ['K', 'J'])
def test_gen_column_decls_sql(self):
sql = pg_gen.gen_column_decls_sql(self.cat_names, self.cat_formats)
print(sql)
assert sql is not None
assert len(sql) == len(self.cat_names)
assert sql[0] == 'ID text'
assert sql[1] == 'RA double precision'
assert sql[2] == 'DEC real'
assert sql[5] == 'y integer'
assert sql[8] == 'ccc bit'
assert sql[9] == 'kron_flag bytea'
def test_gen_search_path_sql_bad(self):
with pytest.raises(errors.ProcessingError):
pg_gen.gen_search_path_sql(dict())
def test_gen_search_path_sql(self):
schema = self.dbconfig.get('DB_SCHEMA_NAME') or 'sia'
sql = pg_gen.gen_search_path_sql(self.dbconfig)
print(sql)
assert sql is not None
assert len(sql) > 0
assert "SET search_path TO {}".format(schema) in sql[0]
class TestPgSql(object):
args = { 'debug': True, 'verbose': True, 'TOOL_NAME': 'TestPgSql', 'catalog_table': 'myCatalog' }
# load DB test configuration parameters
task = isql.ISQLSink(args)
dbconfig = task.load_sql_db_config(TEST_DBCONFIG_FILEPATH)
print("TestPgSql:dbconfig={}".format(dbconfig))
cat_names = [
"ID",
"RA",
"DEC",
"Redshift",
"X",
"Y",
"A",
"BB",
"CCC",
"kron_flag"
]
cat_formats = [
"A",
"D",
"E",
"F",
"I",
"J",
"K",
"L",
"X",
"Z"
]
datad = {
"SIMPLE": True,
"BITPIX": -64,
"NAXIS": 2,
"NAXIS1": 9791,
"NAXIS2": 4305,
"INSTRUME": "JWST",
"TIMESYS": "UTC",
"WCSAXES": 2,
"CRVAL1": 53.157662568,
"CRVAL2": -27.8075199236
}
datad_hyb = {
"obs_collection": "JWST",
"s_dec": 53.157662568,
"s_ra": -27.8075199236,
"is_public": 0,
"metadata": {
"file_name": "some.fits",
"file_size": 4305,
"timesys": "UTC",
}
}
# def test_create_table_empty (self):
# self.args['catalog_table'] = 'test_tbl'
# pgsql.create_table(self.args, self.dbconfig, [], [])
# def test_create_table (self):
# self.args['catalog_table'] = 'test_tbl2'
# ret = pgsql.create_table(self.args, self.dbconfig, self.cat_names, self.cat_formats)
def test_create_table_str_empty (self):
self.args['catalog_table'] = 'NEWTBL'
tbl = pgsql.create_table_str(self.args, self.dbconfig, [], [])
print(tbl)
assert tbl is not None
assert 'CREATE TABLE' in tbl
assert 'NEWTBL' in tbl
assert 'SET search_path TO sia' in tbl
assert 'ID text' not in tbl
assert 'RA double precision' not in tbl
assert 'DEC real' not in tbl
def test_create_table_str (self):
self.args['catalog_table'] = 'NEWTBL'
tbl = pgsql.create_table_str(self.args, self.dbconfig, self.cat_names, self.cat_formats)
print(tbl)
assert tbl is not None
assert 'CREATE TABLE' in tbl
assert 'NEWTBL' in tbl
assert 'SET search_path TO sia' in tbl
assert 'ID text' in tbl
assert 'RA double precision' in tbl
assert 'DEC real' in tbl
assert 'Redshift real' in tbl
assert 'X smallint' in tbl
assert 'Y integer' in tbl
assert 'A bigint' in tbl
def test_list_table_names_schema (self):
tbls = pgsql.list_table_names(self.args, self.dbconfig, db_schema='sia')
print(tbls)
assert tbls is not None
assert len(tbls) > 0
assert 'eazy' in tbls
assert 'jaguar' in tbls
assert 'jwst' in tbls
assert 'hybrid' in tbls
assert 'columns' not in tbls
assert 'keys' not in tbls
assert 'schemas' not in tbls
def test_list_table_names (self):
tbls = pgsql.list_table_names(self.args, self.dbconfig)
print(tbls)
assert tbls is not None
assert len(tbls) > 0
assert 'eazy' in tbls
assert 'jaguar' in tbls
assert 'jwst' in tbls
assert 'hybrid' in tbls
assert 'columns' not in tbls
assert 'keys' not in tbls
assert 'schemas' not in tbls
def test_list_table_names_bad_schema (self):
tbls = pgsql.list_table_names(self.args, self.dbconfig, db_schema='nosuch')
print(tbls)
assert tbls is not None
assert len(tbls) == 0
def test_list_catalog_tables_schema (self):
cats = pgsql.list_catalog_tables(self.args, self.dbconfig, db_schema='sia')
print(cats)
assert cats is not None
assert len(cats) > 0
assert 'sia.eazy' in cats
assert 'sia.jaguar' in cats
assert 'sia.jwst' in cats
assert 'sia.hybrid' in cats
assert 'sia.columns' not in cats
assert 'sia.keys' not in cats
assert 'sia.schemas' not in cats
def test_list_catalog_tables (self):
cats = pgsql.list_catalog_tables(self.args, self.dbconfig)
print(cats)
assert cats is not None
assert len(cats) > 0
assert 'sia.eazy' in cats
assert 'sia.jaguar' in cats
assert 'sia.jwst' in cats
assert 'sia.hybrid' in cats
assert 'sia.columns' not in cats
assert 'sia.keys' not in cats
assert 'sia.schemas' not in cats
def test_list_catalog_tables_bad_schema (self):
cats = pgsql.list_catalog_tables(self.args, self.dbconfig, db_schema='nosuch')
print(cats)
assert cats is not None
assert len(cats) == 0
def test_insert_row_str_empty (self):
with pytest.raises(errors.ProcessingError, match='cannot be inserted'):
pgsql.insert_row_str(self.dbconfig, dict(), 'test_table')
# print(sql)
# assert sql is not None
# assert len(sql) > 0
# assert 'insert' in sql
# assert 'test_table' in sql
# assert 'values' in sql
# assert '();' in sql
def test_insert_row_str (self):
sql = pgsql.insert_row_str(self.dbconfig, self.datad, 'test_table')
print(sql)
assert sql is not None
assert len(sql) > 175 # specific to datad table
assert 'insert' in sql
assert 'test_table' in sql
assert 'values' in sql
assert 'true' in sql
assert 'SIMPLE' in sql
assert 'BITPIX' in sql
assert '-64' in sql
assert '9791' in sql
assert "'JWST'" in sql
assert '53.157662568'
def test_insert_hybrid_row_str_empty (self):
with pytest.raises(errors.ProcessingError, match='cannot be inserted'):
pgsql.insert_hybrid_row_str(self.dbconfig, dict(), 'TESTTBL')
def test_insert_hybrid_row_str_min (self):
datad_hyb_min = {
"obs_collection": "JWST",
"s_dec": 53.157662568,
"s_ra": -27.8075199236,
"is_public": 0
}
sql = pgsql.insert_hybrid_row_str(self.dbconfig, datad_hyb_min, 'test_tbl')
print(sql)
assert 'insert' in sql
assert 'test_tbl' in sql
assert 's_dec' in sql
assert 'is_public' in sql
assert 'values' in sql
assert '53.157662568' in sql
assert '-27.8075199236' in sql
assert 'JWST' in sql
assert 'true' not in sql
assert 'SIMPLE' not in sql
assert 'BITPIX' not in sql
def test_insert_hybrid_row_str (self):
sql = pgsql.insert_hybrid_row_str(self.dbconfig, self.datad_hyb, 'TESTTBL')
print(sql)
assert sql is not None
print("LEN(sql)={}".format(len(sql)))
assert len(sql) > 175 # specific to datad_hyb table
assert 'insert' in sql
assert 'TESTTBL' in sql
assert 'values' in sql
for fld in SQL_FIELDS_HYBRID:
assert fld in sql
assert '53.157662568' in sql
assert '-27.8075199236' in sql
assert 'true' not in sql
assert 'SIMPLE' not in sql
assert 'BITPIX' not in sql
def test_insert_hybrid_row_str_noval (self):
datad_hyb_min = {
"obs_collection": "JWST",
"s_dec": 53.157662568,
"s_ra": -27.8075199236,
"is_public": None
}
with pytest.raises(errors.ProcessingError, match='Unable to find .* required fields'):
pgsql.insert_hybrid_row_str(self.dbconfig, datad_hyb_min, 'test_tbl')