def generate_query(query_type, query_data=None):
bindparams = sa.transform_args_to_bindparams(query_data)
if query_type == 'create_user':
# create role statement
q0 = "CREATE ROLE {role_name}".format(**query_data)
if query_data['can_login']:
q0 += " LOGIN"
if query_data['password']:
q0 += " ENCRYPTED PASSWORD '{password}'".format(**query_data)
if query_data['role_privileges']:
for option in query_data['role_privileges']:
q0 += " " + option
if query_data['connection_limit']:
q0 += " CONNECTION LIMIT {connection_limit}".format(**query_data)
if query_data['valid_until']:
q0 += " VALID UNTIL '{valid_until}'".format(**query_data)
if query_data['group_membership']:
q0 += " IN ROLE"
for grp_index in range( len(query_data['group_membership']) ):
if grp_index == len(query_data['group_membership']) - 1:
q0 += " " + query_data['group_membership'][grp_index]
else:
q0 += " " + query_data['group_membership'][grp_index] + ","
# if query_data['comment']:
# q1 = "COMMENT ON ROLE {role_name} IS \'{comment}\'".format(**query_data)
# queries.append(q1)
queries = (q0, )
return queries
elif query_type == 'drop_user':
queries = []
for cond in query_data:
q = "DROP ROLE {rolname}".format(**cond)
queries.append(q)
return tuple(queries)
elif query_type == 'create_db':
_l = []
_l.append("CREATE DATABASE {name}")
if query_data['encoding']: _l.append(" WITH ENCODING='{encoding}'")
if query_data['owner']: _l.append(" OWNER={owner}")
if query_data['template']: _l.append(" TEMPLATE={template}")
return ("".join(_l).format(**query_data), )
elif query_type == 'table_rpr':
stmt = """
SELECT
t2.tablename AS table,
t2.tableowner AS owner,
t2.tablespace,
t1.reltuples::integer AS "estimated row count"
FROM
pg_catalog.pg_class as t1
INNER JOIN pg_catalog.pg_tables AS t2
ON t1.relname = t2.tablename
WHERE
t2.schemaname= :schm
ORDER BY t2.tablename ASC
"""
q0 = text(stmt, bindparams=bindparams)
return (q0, )
elif query_type == 'indexes':
stmt = """
SELECT
kcu.column_name, kcu.constraint_name, tc.constraint_type
FROM
information_schema.key_column_usage AS kcu
LEFT OUTER JOIN information_schema.table_constraints AS tc
ON (kcu.constraint_name = tc.constraint_name)
WHERE
kcu.table_name= :tbl AND
kcu.table_schema= :schm AND
kcu.table_catalog= :db
"""
q0 = text(stmt, bindparams=bindparams)
return (q0,)
elif query_type == 'primary_keys':
stmt = """
SELECT
kcu.column_name, kcu.constraint_name, tc.constraint_type
FROM
information_schema.key_column_usage AS kcu
LEFT OUTER JOIN information_schema.table_constraints AS tc
ON (kcu.constraint_name = tc.constraint_name)
WHERE
kcu.table_name = :tbl AND
kcu.table_schema = :schm AND
kcu.table_catalog = :db AND
(tc.constraint_type='PRIMARY KEY')
"""
q0 = text(stmt, bindparams=bindparams)
return (q0, )
elif query_type == 'table_structure':
stmt = """
SELECT
column_name as column,
data_type as type,
is_nullable as nullable,
column_default as default,
character_maximum_length,
numeric_precision, numeric_scale, datetime_precision,
interval_type, interval_precision
FROM
information_schema.columns
WHERE
table_catalog = :db AND
table_schema = :schm AND
table_name = :tbl
ORDER BY ordinal_position ASC
"""
q0 = text(stmt, bindparams=bindparams)
return (q0, )
elif query_type == 'foreign_key_relation':
stmt = """
SELECT
conname,
confrelid::regclass AS "referenced_table",
conkey AS array_local_columns,
confkey AS array_foreign_columns
FROM
pg_constraint
WHERE
contype = 'f'
AND conrelid::regclass = :tbl::regclass
AND connamespace = (
SELECT oid
FROM pg_namespace
WHERE nspname= :schm)
"""
q0 = text(stmt, bindparams=bindparams)
return (q0, )
elif query_type == 'seqs_rpr':
# very ugly query and sometimes take long to run.
# it increments every sequence and decrements to get the current value
# reasons why its this long:
# netval gives the minimum_value for a new sequence(have never been consumed).
# on reseting the value of the sequence to its previous the case above results in an error
# so this long query circumvents that error by adding a CASE construct
stmt = """
WITH temp_seqs_tbl AS (
SELECT
sequence_name,
start_value, minimum_value, increment, maximum_value,
nextval(sequence_name::text)
FROM
information_schema.sequences
WHERE
sequence_schema = :schm
)
SELECT
sequence_name AS name,
start_value, minimum_value, increment, maximum_value,
CASE
WHEN nextval::bigint=start_value::bigint THEN
setval(sequence_name::text, start_value::bigint, false)
WHEN nextval::bigint<>start_value::bigint THEN
setval(sequence_name::text, lastval() - 1, true)
END
FROM
temp_seqs_tbl
"""
q0 = text(stmt, bindparams=bindparams)
return (q0, )
elif query_type == 'drop_sequence':
queries = []
for where in query_data['conditions']:
where['name'] = where['name'].replace("'", "")
queries.append( "DROP SEQUENCE {schm}.{name}".format(schm=query_data['schm'], **where))
return tuple(queries)
elif query_type == 'reset_sequence':
queries = []
for where in query_data['conditions']:
where['name'] = where['name'].replace("'", "")
queries.append( "ALTER SEQUENCE {schm}.{name} RESTART".format(
schm=query_data['schm'], **where )
)
return tuple(queries)
elif query_type == 'pgadmin_deps':
# lifted from pgadmin3
stmt = '''
SELECT DISTINCT
dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc,
CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '')
WHEN tg.oid IS NOT NULL THEN 'T'::text
WHEN ty.oid IS NOT NULL THEN 'y'::text
WHEN ns.oid IS NOT NULL THEN 'n'::text
WHEN pr.oid IS NOT NULL THEN 'p'::text
WHEN la.oid IS NOT NULL THEN 'l'::text
WHEN rw.oid IS NOT NULL THEN 'R'::text
WHEN co.oid IS NOT NULL THEN 'C'::text || contype
WHEN ad.oid IS NOT NULL THEN 'A'::text
ELSE '' END AS type,
COALESCE(coc.relname, clrw.relname) AS ownertable,
CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || '.' || att.attname
ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname)
END AS refname,
COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
FROM
pg_depend dep
LEFT JOIN pg_class cl ON dep.objid=cl.oid
LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum
LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
LEFT JOIN pg_proc pr ON dep.objid=pr.oid
LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid
LEFT JOIN pg_trigger tg ON dep.objid=tg.oid
LEFT JOIN pg_type ty ON dep.objid=ty.oid
LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid
LEFT JOIN pg_constraint co ON dep.objid=co.oid
LEFT JOIN pg_class coc ON co.conrelid=coc.oid
LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid
LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid
LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
LEFT JOIN pg_language la ON dep.objid=la.oid
LEFT JOIN pg_namespace ns ON dep.objid=ns.oid
LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid
WHERE
dep.refobjid::regclass = '{schm}.{tbl}'::regclass AND
classid IN (
SELECT oid
FROM pg_class
WHERE relname IN ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc',
'pg_rewrite', 'pg_namespace', 'pg_trigger', 'pg_type', 'pg_attrdef')
)
ORDER BY classid, cl.relkind;
'''
q0 = stmt.format(**query_data)
return (q0, )
def generate_query(query_type, query_data=None):
bindparams = sa.transform_args_to_bindparams(query_data)
# would be a function when users view is reenabled
if query_type == "create_user":
# create user statement
queries = []
q1 = "CREATE USER '{username}'@'{host}'".format(**query_data)
if query_data["password"]:
q1 += " IDENTIFIED BY '{password}'".format(**query_data)
queries.append(q1)
# grant privileges
q2 = "GRANT"
if query_data["privileges"] == "all":
q2 += " ALL"
elif query_data["privileges"] == "select":
priv_groups = ["user_privileges", "administrator_privileges"]
for priv_group in priv_groups:
for priv_in in range(len(query_data[priv_group])):
if priv_in == len(query_data[priv_group]) - 1:
q2 += " " + query_data[priv_group][priv_in]
else:
q2 += " " + query_data[priv_group][priv_in] + ","
if query_data["select_databases"] and len(query_data["select_databases"]) > 1:
for db in query_data["select_databases"]: # mutliple grant objects
q3 = q2 + " ON {db}.*".format(database=db)
# user specification
q3 += " TO '{username}'@'{host}'".format(**query_data)
# grant option
if query_data["options"]:
q3 += " WITH {options[0]}".format(**query_data)
# append generated query to queries
queries.append(q3)
else:
# database access
if query_data["access"] == "all":
q4 = q2 + " ON *.*"
elif query_data["access"] == "select":
q4 = q2 + " ON {select_databases[0]}.*".format(**query_data)
# user specification
q4 += " TO '{username}'@'{host}'".format(**query_data)
# grant option
if query_data["options"]:
q4 += " WITH {options[0]}".format(**query_data)
queries.append(q4)
return tuple(queries)
elif query_type == "create_db":
q = "CREATE DATABASE {name}".format(**query_data)
if query_data["charset"]:
q += " CHARACTER SET {charset}".format(**query_data)
return (q,)
elif query_type == "column_list":
return "SELECT column_name FROM information_schema.columns WHERE table_schema= :db AND table_name= :tbl"
elif query_type == "drop_user":
queries = []
for where in query_data:
q = "DROP USER '{user}'@'{host}'".format(**where)
queries.append(q)
return tuple(queries)
elif query_type == "table_rpr":
stmt = """
SELECT
TABLE_NAME AS 'table',
TABLE_ROWS AS 'rows',
TABLE_TYPE AS 'type',
ENGINE as 'engine'
FROM
information_schema.tables
WHERE
TABLE_SCHEMA = :db
"""
q0 = text(stmt, bindparams=bindparams)
return (q0,)
elif query_type == "indexes":
stmt = """
SELECT DISTINCT
kcu.column_name,
kcu.constraint_name,
tc.constraint_type
FROM
information_schema.key_column_usage as kcu,
information_schema.table_constraints as tc
WHERE
kcu.constraint_name = tc.constraint_name
AND kcu.table_schema= :db
AND tc.table_schema= :db
AND kcu.table_name= :tbl
"""
q0 = text(stmt, bindparams=bindparams)
return (q0,)
elif query_type == "primary_keys":
stmt = """
SELECT DISTINCT
kcu.column_name,
kcu.constraint_name,
tc.constraint_type
FROM
information_schema.key_column_usage as kcu,
information_schema.table_constraints as tc
WHERE
kcu.constraint_name = tc.constraint_name AND
kcu.table_schema= :db AND
tc.table_schema= :db AND
kcu.table_name= :tbl AND
tc.table_name= :tbl AND
tc.constraint_type='PRIMARY KEY'
"""
q0 = text(stmt, bindparams=bindparams)
return (q0,)
elif query_type == "table_structure":
stmt = """
SELECT
column_name AS "column",
column_type AS "type",
is_nullable AS "nullable",
column_default AS "default",
extra
FROM
information_schema.columns
WHERE
table_schema= :db AND
table_name= :tbl
ORDER BY ordinal_position ASC
"""
q0 = text(stmt, bindparams=bindparams)
return (q0,)
elif query_type == "raw_table_structure":
stmt = """
SELECT
column_name AS "column",
data_type AS "type",
is_nullable AS "nullable",
column_default AS "default",
character_maximum_length,
numeric_precision, numeric_scale, extra, column_type
FROM
information_schema.columns
WHERE
table_schema= :db
AND table_name= :tbl
ORDER BY ordinal_position ASC
"""
q0 = text(stmt, bindparams=bindparams)
return (q0,)
