def insert_admin_feature():
from general.domains.admin.models.feature_sets import FeatureSets
from general.domains.admin.models.feature_sets_users import FeatureSetsUsers
from general.domains.auth.models.users import Users
with session_scope(raise_integrity_error=False) as session:
new_feature_set = FeatureSets()
new_feature_set.name = 'admin'
session.add(new_feature_set)
with session_scope(raise_integrity_error=False) as session:
admin_feature_set = (
session.query(FeatureSets)
.filter(FeatureSets.name == 'admin')
.one()
)
user_role = os.environ['PGUSER']
try:
user = (
session.query(Users)
.filter(Users.role == user_role)
.one()
)
except NoResultFound:
user = Users()
user.role = user_role
user.active = True
session.add(user)
session.commit()
new_feature_sets_users = FeatureSetsUsers()
new_feature_sets_users.user_id = user.id
new_feature_sets_users.feature_set_id = admin_feature_set.id
session.add(new_feature_sets_users)
def insert_transactions():
for TransactionType in [
BankingTransactions,
BitfinexTransactions,
BitstampTransactions,
BittrexTransactions,
BtceTransactions,
CoinapultTransactions,
CryptsyTransactions,
GeminiTransactions,
GenesisTransactions,
HavelockTransactions,
KrakenTransactions,
MtgoxTransactions,
PoloniexTransactions
]:
file_names = list(
glob.iglob(data_directory + TransactionType.file_pattern,
recursive=True))
for file_name in file_names:
logging.info(file_name)
user_name = file_name.split('/')[-2]
with session_scope() as session:
user = (
session.query(Users)
.filter(Users.role == user_name)
.one()
)
user_id = user.id
records = TransactionType.process_file(file_name=file_name,
user_id=user_id)
logging.info(f'Found {len(records)} records')
tmp_file_path = data_directory + '/tmp.csv'
with open(tmp_file_path, 'w', newline='') as csvfile:
field_names = set().union(*(d.keys() for d in records))
writer = csv.DictWriter(csvfile, fieldnames=field_names)
writer.writeheader()
for record in records:
writer.writerow(record)
flags = {'format': 'csv', 'header': True}
with session_scope(database='bitcoinadvisory') as session:
with open(tmp_file_path, 'rb') as fp:
try:
postgres_copy.copy_from(fp,
TransactionType,
session.connection(),
field_names,
**flags)
except psycopg2.IntegrityError as exc:
logging.warn(exc)
os.remove(tmp_file_path)
def create_submenu_items_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS admin.submenu_items CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW admin.submenu_items AS
SELECT
dts.user,
dts.id,
dts.custom_name AS "label",
dts.icon,
ARRAY['/', dts.schema_name, dts.table_name] AS "routerLink",
dts.order_index,
NULL as "items",
dts.submenu_id
FROM admin.default_datatable_settings dts
WHERE dts.submenu_id IS NOT NULL
UNION
SELECT
dfs.user,
dfs.id,
dfs.custom_name AS "label",
dfs.icon,
ARRAY['/', dfs.schema_name, 'rpc', dfs.form_name] AS "routerLink",
dfs.order_index,
NULL as "items",
dfs.submenu_id
FROM admin.default_form_settings dfs
WHERE dfs.submenu_id IS NOT NULL
ORDER BY order_index ASC NULLS LAST, "label" ASC NULLS LAST;
""")
def create_default_form_settings_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS admin.default_form_settings CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW admin.default_form_settings AS
SELECT coalesce(fs.id, auth.gen_random_uuid()) as id,
u.role as "user",
u.id as user_id,
f.form_name,
f.schema_name,
coalesce(fs.custom_button_copy, 'Submit') AS custom_button_copy,
coalesce(fs.custom_name, initcap(replace(f.form_name, '_', ' '))) as custom_name,
fs.submenu_id,
coalesce(fs.icon, 'fa-pencil-square-o') AS icon,
coalesce(fs.is_visible, TRUE) AS is_visible,
coalesce(fs.order_index, 99) AS order_index,
row_to_json(ds)::JSONB AS dialog_settings
FROM auth.users u
LEFT OUTER JOIN admin.forms f
ON TRUE
LEFT OUTER JOIN admin.form_settings fs
ON f.form_name = fs.form_name
AND f.schema_name = fs.schema_name
AND u.id = fs.user_id
LEFT OUTER JOIN admin.dialog_settings ds
ON fs.dialog_settings_id = ds.id
AND fs.user_id = ds.user_id
ORDER BY u.role, f.schema_name, f.form_name;
""")
def create_jwt_algorithm_sign_function():
with session_scope() as session:
session.execute("""
DROP FUNCTION IF EXISTS auth.jwt_algorithm_sign(signables TEXT,
secret TEXT,
algorithm TEXT);
""")
session.execute("""
CREATE OR REPLACE FUNCTION auth.jwt_algorithm_sign(signables TEXT, secret TEXT,
algorithm TEXT)
RETURNS TEXT
LANGUAGE SQL
AS $$
WITH
alg AS (
SELECT CASE
WHEN algorithm = 'HS256'
THEN 'sha256'
WHEN algorithm = 'HS384'
THEN 'sha384'
WHEN algorithm = 'HS512'
THEN 'sha512'
ELSE '' END AS id) -- hmac throws error
SELECT auth.jwt_url_encode(auth.hmac(signables, secret, alg.id))
FROM alg;
$$;
""")
def create_token_api_trigger():
with session_scope() as session:
session.execute("""
DROP FUNCTION IF EXISTS auth_api.token() CASCADE;
""")
session.execute("""
CREATE OR REPLACE FUNCTION
auth_api.token()
RETURNS auth.JWT_TOKEN
LANGUAGE plpgsql
AS $$
DECLARE
result auth.JWT_TOKEN;
BEGIN
SELECT auth.jwt_sign(row_to_json(r), current_setting('app.jwt_ws_secret')) AS token
FROM (
SELECT
'anon' AS role,
'rw' AS mode,
extract(EPOCH FROM now()) :: INTEGER + current_setting('app.jwt_hours')::INTEGER * 60 * 60 AS exp
) r
INTO result;
RETURN result;
END;
$$;
""")
def create_output_entries_api_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS wallet_api.output_entries CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW wallet_api.output_entries AS
SELECT
oe.id,
t.time_received,
t.txid,
oe.vector AS vout,
oe.category,
oe.amount*1000000 AS bits,
oe.destination,
a.name,
a.purpose
FROM wallet.output_entries oe
LEFT JOIN wallet.transactions t
ON oe.etransaction_id = t.id
LEFT JOIN wallet.addresses a
ON (a.p2pkh_address = oe.destination
OR a.sw_bech32_address = oe.destination
OR a.sw_p2sh_address = oe.destination)
WHERE oe.role = current_user
ORDER BY t.time_received DESC;
""")
def create_wallet_gemini_reconciliation_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS exchanges.wallet_gemini_reconciliation CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW exchanges.wallet_gemini_reconciliation AS
SELECT
t.date,
t.transaction_type,
t.symbol,
t.specification,
t.tx_hash,
t.deposit_tx_output,
t.withdrawal_tx_output,
t.withdrawal_destination,
oe.id as output_entry_id,
oe.time_received as wallet_time_received,
oe.name as wallet_name,
abs(t.btc_amount*1000000) as gemini_bits,
oe.bits as wallet_bits,
abs(t.btc_amount*1000000) - coalesce(oe.bits, 0) AS amount_rec
FROM exchanges.gemini_transactions t
LEFT OUTER JOIN wallet_api.output_entries oe
ON oe.txid = t.tx_hash
AND (
oe.vout = t.withdrawal_tx_output OR
oe.vout = t.deposit_tx_output
)
JOIN auth.users u
ON u.id = t.user_id
WHERE t.tx_hash IS NOT NULL
ORDER BY t.date DESC
""")
def create_jwt_sign_function():
with session_scope() as session:
session.execute("""
DROP FUNCTION IF EXISTS auth.jwt_sign(payload JSON, secret TEXT) CASCADE;
""")
session.execute("""
CREATE OR REPLACE FUNCTION auth.jwt_sign(payload JSON, secret TEXT)
RETURNS TEXT
LANGUAGE SQL
AS $$
WITH
header AS (
SELECT auth.jwt_url_encode(
convert_to('{"alg":"HS256","typ":"JWT"}',
'utf8')) AS data
),
payload AS (
SELECT auth.jwt_url_encode(convert_to(payload :: TEXT, 'utf8')) AS data
),
signables AS (
SELECT header.data || '.' || payload.data AS data
FROM header, payload
)
SELECT signables.data || '.' ||
auth.jwt_algorithm_sign(signables.data, secret, 'HS256')
FROM signables;
$$;
""")
def create_addresses_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS wallet.addresses CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW wallet.addresses AS
SELECT
ea.id,
ea.p2pkh_address,
ea.sw_p2sh_address,
ea.sw_bech32_address,
ea.purpose,
ea.name,
ea.is_used,
ea.time,
u.role
FROM wallet."eAddresses" ea
JOIN wallet."eWallets" ew
ON ew.wallet_id = ea.wallet_id
JOIN wallet.wallets_users wu
ON wu.wallet_id = ew.wallet_id
JOIN auth.users u
ON u.id = wu.user_id
""")
def create_output_entries_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS wallet.output_entries CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW wallet.output_entries AS
SELECT
eoe.id,
to_timestamp(output_eb.time) AS output_time,
eoe.is_output_mine,
eoe.n_value::DECIMAL/100000000 AS n_value,
to_timestamp(input_eb.time) AS input_time,
output_et.hash AS output_hash,
eoe.output_vector,
input_et.hash AS input_hash,
eoe.input_vector,
eoe.destination
FROM wallet."eOutputEntries" eoe
JOIN wallet."eTransactions" output_et
ON output_et.id = eoe.output_etransaction_id
LEFT OUTER JOIN wallet."eTransactions" input_et
ON input_et.id = eoe.input_etransaction_id
JOIN wallet."eBlocks" output_eb
ON output_et.block_id = output_eb.id
LEFT OUTER JOIN wallet."eBlocks" input_eb
ON input_et.block_id = input_eb.id
ORDER BY output_time ASC;
""")
def create_table_notifications(source_schema_name, source_table_name,
target_schema_name, target_table_name):
with session_scope() as session:
session.execute('''
CREATE OR REPLACE FUNCTION {source_schema_name}_{source_table_name}_notify() RETURNS TRIGGER AS $$
DECLARE
id INT;
payload TEXT;
json_record JSON;
notification_message_settings RECORD;
BEGIN
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
id = NEW.id;
json_record = row_to_json(NEW);
ELSE
id = OLD.id;
json_record = row_to_json(OLD);
END IF;
SELECT * INTO notification_message_settings
FROM admin.notification_message_settings nms
WHERE nms.namespace = '{target_schema_name}.{target_table_name}'
AND nms.message_type = TG_OP;
payload = json_build_object('id', id,
'schema_name', '{target_schema_name}',
'table_name', '{target_table_name}',
'type', TG_OP,
'is_visible', notification_message_settings.is_visible,
'severity', notification_message_settings.severity,
'summary', notification_message_settings.summary,
'detail', notification_message_settings.detail)::TEXT;
PERFORM pg_notify('updates', payload);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
'''.format(source_schema_name=source_schema_name,
source_table_name=source_table_name,
target_schema_name=target_schema_name,
target_table_name=target_table_name))
session.execute('''
DROP TRIGGER IF EXISTS {source_schema_name}_{source_table_name}_notify_update ON {source_schema_name}."{source_table_name}";
CREATE TRIGGER {source_schema_name}_{source_table_name}_notify_update
AFTER UPDATE ON {source_schema_name}."{source_table_name}"
FOR EACH ROW EXECUTE PROCEDURE {source_schema_name}_{source_table_name}_notify();
DROP TRIGGER IF EXISTS {source_schema_name}_{source_table_name}_notify_insert ON {source_schema_name}."{source_table_name}";
CREATE TRIGGER {source_schema_name}_{source_table_name}_notify_insert
AFTER INSERT ON {source_schema_name}."{source_table_name}"
FOR EACH ROW EXECUTE PROCEDURE {source_schema_name}_{source_table_name}_notify();
DROP TRIGGER IF EXISTS {source_schema_name}_{source_table_name}_notify_delete ON {source_schema_name}."{source_table_name}";
CREATE TRIGGER {source_schema_name}_{source_table_name}_notify_delete
AFTER DELETE ON {source_schema_name}."{source_table_name}"
FOR EACH ROW EXECUTE PROCEDURE {source_schema_name}_{source_table_name}_notify();
'''.format(source_schema_name=source_schema_name,
source_table_name=source_table_name))
def insert_form_settings():
form_settings_path = os.path.join(script_path, 'form_settings')
form_settings_file_names = os.listdir(form_settings_path)
for file_name in form_settings_file_names:
file_path = os.path.join(form_settings_path, file_name)
with open(file_path) as data_file:
form_settings_list = json.load(data_file)
for form_settings in form_settings_list:
with session_scope() as session:
for user in session.query(Users).all():
try:
form_settings_record = (
session.query(FormSettings).filter(
and_(
FormSettings.user_id == user.id,
FormSettings.form_name ==
form_settings['form_name'],
FormSettings.schema_name ==
form_settings['schema_name'])).one())
except NoResultFound:
form_settings_record = FormSettings()
form_settings_record.user_id = user.id
form_settings_record.form_name = form_settings[
'form_name']
form_settings_record.schema_name = form_settings[
'schema_name']
session.add(form_settings_record)
session.commit()
for key, value in form_settings.items():
setattr(form_settings_record, key, value)
def insert_table_column_settings():
schema_name = 'wallet'
tcs_path = os.path.join(script_path, 'table_column_settings')
file_names = list(glob.iglob(tcs_path + '/*.json', recursive=True))
print(pformat(file_names))
for file_name in file_names:
with open(file_name) as data_file:
table_column_settings = json.load(data_file)
with session_scope() as session:
for user in session.query(Users).all():
for table_column_setting in table_column_settings:
column_name = table_column_setting['column_name']
table_name = table_column_setting['table_name']
try:
record = (session.query(TableColumnSettings).filter(
and_(
TableColumnSettings.schema_name == schema_name,
TableColumnSettings.table_name == table_name,
TableColumnSettings.column_name == column_name,
TableColumnSettings.user_id == user.id)).one())
except NoResultFound:
record = TableColumnSettings()
record.user_id = user.id
record.schema_name = schema_name
record.table_name = table_name
record.column_name = column_name
session.add(record)
session.commit()
for key, value in table_column_setting.items():
if not hasattr(record, key):
print(
f'Warning: {key} from {file_name} does not exist in TCS'
)
setattr(record, key, value)
def create_check_if_role_exists_function():
with session_scope() as session:
session.execute("""
DROP FUNCTION IF EXISTS auth.check_if_role_exists() CASCADE;
""")
session.execute("""
CREATE OR REPLACE FUNCTION
auth.check_if_role_exists()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT exists(SELECT 1
FROM pg_roles
WHERE pg_roles.rolname = NEW.role)
THEN
RAISE foreign_key_violation
USING MESSAGE = 'Unknown database role: ' || NEW.role;
RETURN NULL;
END IF;
RETURN NEW;
END
$$;
""")
def insert_dialog_settings():
dialog_settings_path = os.path.join(script_path, 'dialog_settings')
dialog_settings_file_names = os.listdir(dialog_settings_path)
for file_name in dialog_settings_file_names:
file_path = os.path.join(dialog_settings_path, file_name)
with open(file_path) as data_file:
dialog_settings_list = json.load(data_file)
for dialog_settings in dialog_settings_list:
with session_scope() as session:
for user in session.query(Users).all():
form_settings_record = (session.query(FormSettings).filter(
and_(
FormSettings.user_id == user.id,
FormSettings.form_name ==
dialog_settings['form_name'],
FormSettings.schema_name ==
dialog_settings['schema_name'])).one())
if not form_settings_record.dialog_settings_id:
dialog_settings_record = DialogSettings()
dialog_settings_record.user_id = user.id
dialog_settings_record.name = dialog_settings['name']
session.add(dialog_settings_record)
session.commit()
form_settings_record.dialog_settings_id = dialog_settings_record.id
else:
dialog_settings_record = (
session.query(DialogSettings).filter(
DialogSettings.id ==
form_settings_record.dialog_settings_id).one())
for key, value in dialog_settings.items():
setattr(dialog_settings_record, key, value)
def create_transactions_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS wallet.transactions CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW wallet.transactions AS
SELECT
et.id,
et.block_index,
et.is_trusted,
et.size,
to_timestamp(et.time) AS time,
to_timestamp(et.time_received) AS time_received,
et.txid,
u.role,
ew.wallet_id
FROM wallet."eTransactions" et
JOIN wallet."eWallets" ew
ON ew.wallet_id = et.wallet_id
JOIN wallet.wallets_users wu
ON wu.wallet_id = ew.wallet_id
JOIN auth.users u
ON u.id = wu.user_id;
""")
def insert_user_feature():
"""
The user feature set is the opposite of the admin feature set.
Hide the admin tables.
"""
schema_name = 'admin'
api_view_names = [
'datatable_columns', 'datatables', 'form_fields', 'forms', 'home'
]
with session_scope() as session:
users = (session.query(Users).outerjoin(
FeatureSetsUsers, FeatureSetsUsers.user_id == Users.id).outerjoin(
FeatureSets,
FeatureSetsUsers.feature_set_id == FeatureSets.id).filter(
FeatureSets.name.is_(None)).all())
user_ids = [user.id for user in users]
for user in users:
for api_view_name in api_view_names:
try:
menubar_view_setting = (
session.query(TableSettings).filter(
TableSettings.user_id == user.id).filter(
TableSettings.table_name == api_view_name).
filter(TableSettings.schema_name == schema_name).one())
except NoResultFound:
menubar_view_setting_data = {
'schema_name': schema_name,
'table_name': api_view_name,
'user_id': user.id
}
menubar_view_setting = TableSettings(
**menubar_view_setting_data)
session.add(menubar_view_setting)
session.commit()
menubar_view_setting.is_visible = False
def create_types():
with session_scope(raise_programming_error=False) as session:
session.execute("""
CREATE TYPE auth.jwt_token AS (
token TEXT
);
""")
def insert_context_menu_items():
path = os.path.join(script_path, 'context_menu_items')
file_names = os.listdir(path)
for file_name in file_names:
file_path = os.path.join(path, file_name)
with open(file_path) as data_file:
record_data_list = json.load(data_file)
for record_data in record_data_list:
with session_scope() as session:
for user in session.query(Users).all():
try:
record = (session.query(ContextMenuItems).filter(
and_(
ContextMenuItems.schema_name ==
record_data['schema_name'],
ContextMenuItems.table_name ==
record_data['table_name'],
ContextMenuItems.label == record_data['label'],
ContextMenuItems.user_id == user.id)).one())
for key, value in record_data.items():
setattr(record, key, value)
except NoResultFound:
record = ContextMenuItems(**record_data)
record.user_id = user.id
session.add(record)
session.commit()
def has_been_tweeted(subject_type, subject_id) -> bool:
with session_scope() as session:
try:
(session.query(Tweets).filter(
and_(Tweets.subject_type == subject_type,
Tweets.subject_id == subject_id)).one())
return True
except NoResultFound:
return False
def create_triggers_on_users():
with session_scope() as session:
session.execute("""
DROP TRIGGER IF EXISTS encrypt_password
ON auth.users;
CREATE TRIGGER encrypt_password
BEFORE INSERT OR UPDATE ON auth.users
FOR EACH ROW
EXECUTE PROCEDURE auth.encrypt_password();
""")
def create_constraint_triggers_on_users():
with session_scope() as session:
session.execute("""
DROP TRIGGER IF EXISTS ensure_user_role_exists
ON auth.users;
CREATE CONSTRAINT TRIGGER ensure_user_role_exists
AFTER INSERT OR UPDATE ON auth.users
FOR EACH ROW
EXECUTE PROCEDURE auth.check_if_role_exists();
""")
def get_blocks(start_height: int, last_height: int) -> pd.DataFrame:
with session_scope() as session:
query = (session.query(EBlocks).filter(
and_(EBlocks.height >= start_height,
EBlocks.height <= last_height)))
df = pd.read_sql(query.statement, session.bind)
df = df.sort_values('height')
for column in ['time', 'median_time']:
df[column] = df[column].apply(
lambda x: pd.to_datetime(x, unit='s'))
return df
def get_latest_block(limit: int = 1) -> pd.DataFrame:
log.debug('Getting latest block from db')
with session_scope() as session:
query = (session.query(EBlocks).order_by(
EBlocks.height.desc()).limit(limit))
df = pd.read_sql(query.statement, session.bind)
df = df.sort_values('height')
for column in ['time', 'median_time']:
df[column] = df[column].apply(
lambda x: pd.to_datetime(x, unit='s'))
return df
def create_logout_api_trigger():
with session_scope() as session:
session.execute("""
CREATE OR REPLACE FUNCTION
auth_api.logout()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
END;
$$;
""")
def create_schemas_materialized_view():
with session_scope() as session:
session.execute("""
DROP MATERIALIZED VIEW IF EXISTS admin.schemas CASCADE;
""")
session.execute("""
CREATE MATERIALIZED VIEW admin.schemas AS
SELECT
replace(schema_name, '_api', '') AS schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%_api';
""")
def create_forms_materialized_view():
with session_scope() as session:
session.execute("""
DROP MATERIALIZED VIEW IF EXISTS admin.forms CASCADE;
""")
session.execute("""
CREATE MATERIALIZED VIEW admin.forms AS
SELECT replace(specific_schema, '_api', '') AS schema_name,
routine_name as form_name
FROM information_schema.routines
WHERE specific_schema LIKE '%_api';
""")
def insert_anon():
from general.domains.auth.models import Users
with session_scope() as session:
try:
session.execute("""
CREATE ROLE anon noinherit;
""")
except ProgrammingError:
pass
with session_scope() as session:
try:
user = (
session.query(Users)
.filter(Users.role == 'anon')
.one()
)
except NoResultFound:
user = Users()
user.role = 'anon'
user.active = True
session.add(user)
def create_datatables_view():
with session_scope() as session:
session.execute("""
DROP VIEW IF EXISTS admin_api.datatables CASCADE;
""")
session.execute("""
CREATE OR REPLACE VIEW admin_api.datatables AS
SELECT (row_number() OVER())::INT id, *
FROM (
SELECT
dts.can_archive,
dts.can_delete,
dts.custom_name,
dts.order_index,
dts.row_limit,
dts.row_offset,
dts.schema_name,
dts.sort_column,
dts.sort_order,
dts.table_name,
dts.user_id,
dts.context_menu_items,
map.mapper_settings
FROM admin.default_datatable_settings dts
LEFT OUTER JOIN (
SELECT
mq.table_settings_id,
row_to_json(mq)::JSONB AS "mapper_settings"
FROM
(
SELECT
ms.table_settings_id,
row_to_json(fcdc) AS filter_column,
row_to_json(mcdc) AS mapping_column,
row_to_json(smcdc) AS saved_keyword_column,
row_to_json(skdc) AS saved_mapping_column
FROM ADMIN.mapper_settings MS
LEFT JOIN ADMIN.default_datatable_column_settings fcdc
ON fcdc.id = MS.filter_column_settings_id
LEFT JOIN ADMIN.default_datatable_column_settings mcdc
ON mcdc.id = MS.mapping_column_settings_id
LEFT JOIN ADMIN.default_datatable_column_settings smcdc
ON smcdc.id = MS.saved_mapping_column_settings_id
LEFT JOIN ADMIN.default_datatable_column_settings skdc
ON skdc.id = MS.saved_keyword_column_settings_id
) mq
) map
ON dts.id = map.table_settings_id
WHERE dts.user = current_user
) sub;
""")
