def _get_schema_column_info(self, connection, schema=None, **kw):
schema_clause = "AND schema = '{schema}'".format(
schema=schema) if schema else ""
all_columns = defaultdict(list)
with connection.connect() as cc:
result = cc.execute("""
SELECT s.table_schema AS "schema",
s.table_name AS "table_name",
s.column_name AS "name",
NULL AS "encode",
s.data_type AS "type",
NULL AS "distkey",
NULL AS "sortkey",
NULL AS "notnull",
NULL AS "comment",
NULL AS "adsrc",
NULL AS "attnum",
s.data_type AS "format_type",
NULL AS "default",
NULL AS "schema_oid",
NULL AS "table_oid"
FROM SVV_COLUMNS s
WHERE s.table_schema !~ '^pg_'
{schema_clause}
""".format(schema_clause=schema_clause))
for col in result:
key = RelationKey(col.table_name, col.schema, connection)
all_columns[key].append(col)
return dict(all_columns)
def _get_all_relation_info(self, connection, **kw):
result = connection.execute(
"""
SELECT c.relkind,
n.oid AS "schema_oid",
n.nspname AS "schema",
c.oid AS "rel_oid",
c.relname,
CASE c.reldiststyle
WHEN 0 THEN 'EVEN'
WHEN 1 THEN 'KEY'
WHEN 8 THEN 'ALL'
END AS "diststyle",
c.relowner AS "owner_id",
u.usename AS "owner_name",
TRIM(TRAILING ';' FROM pg_catalog.pg_get_viewdef (c.oid,TRUE)) AS "view_definition",
pg_catalog.array_to_string(c.relacl,'\n') AS "privileges"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
WHERE c.relkind IN ('r','v','m','S','f')
AND n.nspname !~ '^pg_'
AND n.nspname != 'information_schema'
UNION
SELECT 'r' AS "relkind",
NULL AS "schema_oid",
schemaname AS "schema",
NULL AS "rel_oid",
tablename AS "relname",
NULL AS "diststyle",
NULL AS "owner_id",
NULL AS "owner_name",
NULL AS "view_definition",
NULL AS "privileges"
FROM pg_catalog.svv_external_tables
ORDER BY "schema",
"relname";"""
)
relations = {}
for rel in result:
key = RelationKey(rel.relname, rel.schema, connection)
relations[key] = rel
return relations
def _get_all_table_comments(self, connection, **kw):
COMMENT_SQL = """
SELECT n.nspname as schema,
c.relname as table_name,
pgd.description as table_comment
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_description pgd ON pgd.objsubid = 0 AND pgd.objoid = c.oid
WHERE c.relkind in ('r', 'v', 'm', 'f', 'p')
AND pgd.description IS NOT NULL
ORDER BY "schema", "table_name";
"""
all_table_comments: Dict[RelationKey, str] = {}
result = connection.execute(COMMENT_SQL)
for table in result:
key = RelationKey(table.table_name, table.schema, connection)
all_table_comments[key] = table.table_comment
return all_table_comments
def get_table_comment(self, connection, table_name, schema=None, **kw):
all_table_comments = self._get_all_table_comments(connection, **kw)
key = RelationKey(table_name, schema, connection)
if key not in all_table_comments.keys():
key = key.unquoted()
return {"text": all_table_comments.get(key)}
def _get_schema_column_info(self, connection, schema=None, **kw):
schema_clause = "AND schema = '{schema}'".format(
schema=schema) if schema else ""
all_columns = defaultdict(list)
with connection.connect() as cc:
result = cc.execute("""
SELECT
n.nspname as "schema",
c.relname as "table_name",
att.attname as "name",
format_encoding(att.attencodingtype::integer) as "encode",
format_type(att.atttypid, att.atttypmod) as "type",
att.attisdistkey as "distkey",
att.attsortkeyord as "sortkey",
att.attnotnull as "notnull",
pg_catalog.col_description(att.attrelid, att.attnum)
as "comment",
adsrc,
attnum,
pg_catalog.format_type(att.atttypid, att.atttypmod),
pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) AS DEFAULT,
n.oid as "schema_oid",
c.oid as "table_oid"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
JOIN pg_catalog.pg_attribute att
ON att.attrelid = c.oid
LEFT JOIN pg_catalog.pg_attrdef ad
ON (att.attrelid, att.attnum) = (ad.adrelid, ad.adnum)
WHERE n.nspname !~ '^pg_'
AND att.attnum > 0
AND NOT att.attisdropped
{schema_clause}
UNION
SELECT
view_schema as "schema",
view_name as "table_name",
col_name as "name",
null as "encode",
col_type as "type",
null as "distkey",
0 as "sortkey",
null as "notnull",
null as "comment",
null as "adsrc",
null as "attnum",
col_type as "format_type",
null as "default",
null as "schema_oid",
null as "table_oid"
FROM pg_get_late_binding_view_cols() cols(
view_schema name,
view_name name,
col_name name,
col_type varchar,
col_num int)
WHERE 1 {schema_clause}
UNION
SELECT
schemaname as "schema",
tablename as "table_name",
columnname as "name",
null as "encode",
-- Spectrum represents data types differently.
-- Standardize, so we can infer types.
CASE
WHEN external_type = 'int' THEN 'integer'
ELSE
regexp_replace(
replace(
replace(
replace(
replace(
replace(
replace(external_type, 'decimal', 'numeric'),
'varchar', 'character varying'),
'string', 'character varying'),
'char(', 'character('),
'float', 'real'),
'double', 'float'),
'^array<(.*)>$', '$1[]', 1, 'p')
END AS "type",
null as "distkey",
0 as "sortkey",
null as "notnull",
null as "comment",
null as "adsrc",
null as "attnum",
CASE
WHEN external_type = 'int' THEN 'integer'
ELSE
regexp_replace(
replace(
replace(
replace(
replace(
replace(
replace(external_type, 'decimal', 'numeric'),
'varchar', 'character varying'),
'string', 'character varying'),
'char(', 'character('),
'float', 'real'),
'double', 'float'),
'^array<(.*)>$', '$1[]', 1, 'p')
END AS "format_type",
null as "default",
null as "schema_oid",
null as "table_oid"
FROM SVV_EXTERNAL_COLUMNS
WHERE 1 {schema_clause}
ORDER BY "schema", "table_name", "attnum"
""".format(schema_clause=schema_clause))
for col in result:
key = RelationKey(col.table_name, col.schema, connection)
all_columns[key].append(col)
return dict(all_columns)
def test_unquoted(raw_table, raw_schema):
key = RelationKey(raw_table, raw_schema)
assert key.unquoted().__str__() == "schema.table"