def __view(self) -> str:
"""
:return:
"""
sorted_joins = sorted(self.joins.items())
sql = """
CREATE OR REPLACE VIEW {vs}.{vn} AS
SELECT
CASE
{types}
ELSE {no_subtype}
END AS {type_name}
{master_columns}{merge_columns}
{joined_columns}{additional_columns}
FROM {mt}.{ms} {sa}
{joined_tables}{additional_joins};
""".format(vs=self.view_schema,
vn=self.view_name,
types='\n '.join([
"WHEN {shal}.{mrf} IS NOT NULL THEN '{al}'::{vs}.{tn}".format(
shal=table_def['short_alias'],
mrf=table_def['ref_master_key'],
al=alias,
vs=self.view_schema,
tn=self.type_name) for alias, table_def in sorted_joins
]),
no_subtype="'{type}'::{vs}.{tn}".format(
type=self.view_alias if self.allow_parent_only else 'unknown',
vs=self.view_schema,
tn=self.type_name),
type_name=self.type_name,
master_columns=select_columns(
self.cursor,
self.master_schema,
self.master_table,
table_alias=self.view_alias,
skip_columns=self.master_skip_colums,
prefix=self.master_prefix,
remap_columns=self.master_remap_columns,
indent=4,
separate_first=True),
merge_columns='\n , '.join([
'\n , CASE'
'\n {conditions}'
'\n ELSE NULL{cast}'
'\n END AS {col}'.format(
col=col,
conditions='\n '.join([
'WHEN {ta}.{rmk} IS NOT NULL THEN {ta}.{col}'.format(
ta=table_def['short_alias'],
rmk=table_def['ref_master_key'],
col=col) for alias, table_def in sorted_joins
if col in columns(self.cursor,
table_def['table_schema'],
table_def['table_name'],
skip_columns=table_def.get(
'skip_columns', []))
]),
cast=self.merge_column_cast.get(col, ''))
for col in self.merge_columns
]),
joined_columns='\n '.join([
select_columns(self.cursor,
table_def['table_schema'],
table_def['table_name'],
table_alias=table_def['short_alias'],
skip_columns=table_def.get('skip_columns', []) +
[table_def['ref_master_key']],
safe_skip_columns=self.merge_columns,
prefix=table_def.get('prefix', None),
remove_pkey=False,
remap_columns=table_def.get('remap_columns', {}),
indent=4,
separate_first=True)
for alias, table_def in sorted_joins
]),
additional_columns=''.join([
',\n {cdef} AS {alias}'.format(cdef=cdef, alias=alias)
for alias, cdef in self.additional_columns.items()
]),
mt=self.master_schema,
ms=self.master_table,
sa=self.short_alias,
joined_tables='\n '.join([
"LEFT JOIN {tbl} {tal} ON {tal}.{rmk} = {msa}.{mpk}".format(
tbl=table_def[1]['table'],
tal=table_def[1]['short_alias'],
rmk=table_def[1]['ref_master_key'],
msa=self.short_alias,
mpk=self.master_pkey) for table_def in sorted_joins
]),
additional_joins='\n {ad}'.format(
ad=self.additional_joins) if self.additional_joins else '')
return sql
def update_command(pg_cur: cursor,
table_schema: str,
table_name: str,
table_alias: str = None,
table_type: str = 'table',
remove_pkey: bool = True,
pkey: str = None,
skip_columns: list = [],
comment_skipped: bool = True,
remap_columns: dict = {},
update_values: dict = {},
columns_on_top: list = [],
columns_at_end: list = [],
prefix: str = None,
where_clause: str = None,
indent: int = 2,
inner_defaults: dict = {}) -> str:
"""
Creates an UPDATE command
Parameters
----------
pg_cur
the psycopg cursor
table_schema
the schema
table_name
the name of the table
table_type
the type of table, i.e. view or table
remove_pkey
if True, the primary key will also be updated
pkey
can be manually specified.
table_alias
if not specified, table is used
skip_columns
list of columns to be skipped
comment_skipped
if True, skipped columns are written but commented, otherwise they are not written
remap_columns
dictionary to remap columns
update_values
dictionary of expression to be used at insert
columns_on_top
bring the columns to the front of the list
columns_at_end
bring the columns to the end of the list
prefix
add a prefix to the columns (do not applied to remapped columns)
where_clause
can be manually specified
indent
add an indent in front
inner_defaults
dictionary of other columns to default to in case the provided value is null (can be used instead of insert_values to make it easier to reuse other columns definitions)
Returns
-------
the SQL command
"""
remove_pkey = remove_pkey and pkey is None and where_clause is None
# get columns
try:
pk_for_sort = primary_key(pg_cur, table_schema, table_name)
except TableHasNoPrimaryKey:
pk_for_sort = None
cols = sorted(columns(pg_cur,
table_schema=table_schema,
table_name=table_name,
table_type=table_type,
remove_pkey=remove_pkey),
key=lambda _col, pk_for_sort=pk_for_sort: __column_priority(
_col, primary_key=pk_for_sort))
if pkey and remove_pkey:
cols.remove(pkey)
# if no columns, return NULL
if len([col for col in cols if col not in skip_columns]) == 0:
return "-- Do not update for {} since all columns are skipped".format(
table_name)
if not pkey and not where_clause:
pkey = primary_key(pg_cur, table_schema, table_name)
# check arguments
for param, dict_or_list in {
'skip_columns': skip_columns,
'remap_columns': remap_columns,
'update_values': update_values,
'columns_on_top': columns_on_top,
'columns_at_end': columns_at_end
}.items():
for col in dict_or_list:
if col not in cols and col != pkey:
raise InvalidColumn(
'Invalid column in {param} paramater: "{tab}" has no column "{col}"'
.format(param=param, tab=table_name, col=col))
next_comma_printed = [False]
def value(col):
if col in update_values:
return update_values[col]
cal = __column_alias(col,
remap_columns=remap_columns,
prefix=prefix,
field_if_no_alias=True)
if col in inner_defaults:
def_col = inner_defaults[col]
# we don't use COALESCE to deal with empt strings too
# we use recursion in case we need to call default to obj_id which may be calculated as just above
return 'CASE WHEN NEW.{cal} IS NOT NULL AND NEW.{cal}::text <> \'\' THEN NEW.{cal} ELSE {default} END'.format(
cal=cal, default=value(def_col))
else:
return 'NEW.{cal}'.format(cal=cal)
return """UPDATE {s}.{t}{a} SET
{indent} {cols}
{indent} WHERE {where_clause};"""\
.format(indent=indent*' ',
s=table_schema,
t=table_name,
a=' {alias}'.format(alias=table_alias) if table_alias else '',
cols='\n{indent} '
.format(indent=indent*' ')
.join(['{skip}{comma}{col} = {new_col}'
.format(indent=indent*' ',
skip='-- ' if col in skip_columns else '',
comma=', ' if __print_comma(next_comma_printed, col in skip_columns) else '',
col=col,
new_col=value(col))
for col in cols if (comment_skipped or col not in skip_columns)]),
where_clause=where_clause or '{pkey} = {pkal}'.format(pkey=pkey,
pkal=update_values.get(pkey,
'OLD.{cal}'.format(cal=__column_alias(pkey,
remap_columns=remap_columns,
prefix=prefix,
field_if_no_alias=True)))))
def select_columns(pg_cur: cursor,
table_schema: str,
table_name: str,
table_type: str = 'table',
table_alias: str = None,
remove_pkey: bool = False,
skip_columns: list = [],
safe_skip_columns: list = [],
columns_list: list = None,
comment_skipped: bool = True,
remap_columns: dict = {},
columns_on_top: list = [],
columns_at_end: list = [],
prefix: str = None,
indent: int = 2,
separate_first: bool = False) -> str:
"""
Returns the list of columns to be used in a SELECT command
Parameters
----------
pg_cur
the psycopg cursor
table_schema
the schema
table_name
the name of the table
table_type
the type of table, i.e. view or table
table_alias
if not specified, table is used
remove_pkey
if True, the primary is removed from the list
skip_columns
list of columns to be skipped, raise an exception if the column does not exist
safe_skip_columns
list of columns to be skipped, do not raise exception if column does not exist
columns_list
if given use as list of columns
comment_skipped
if True, skipped columns are written but commented, otherwise they are not written
If remove_pkey is True, the primary key will not be printed
remap_columns
dictionary to remap columns
columns_on_top
bring the columns to the front of the list
columns_at_end
bring the columns to the end of the list
prefix
add a prefix to the columns (do not applied to remapped columns)
indent
add an indent in front
separate_first
separate the first column with a comma
"""
try:
pk_for_sort = primary_key(pg_cur, table_schema, table_name)
except TableHasNoPrimaryKey:
pk_for_sort = None
cols = sorted(columns_list or columns(pg_cur,
table_schema=table_schema,
table_name=table_name,
table_type=table_type,
remove_pkey=remove_pkey),
key=lambda col, pk_for_sort=pk_for_sort: __column_priority(
col, primary_key=pk_for_sort))
cols = [col for col in cols if col not in safe_skip_columns]
# check arguments
for param, dict_or_list in {
'skip_columns': skip_columns,
'remap_columns': remap_columns,
'columns_on_top': columns_on_top,
'columns_at_end': columns_at_end
}.items():
for col in dict_or_list:
if col not in cols:
raise InvalidColumn(
'Invalid column in {param} paramater: "{tab}" has no column "{col}"'
.format(param=param, tab=table_name, col=col))
first_column_printed = [separate_first]
def print_comma(first_column_printed, print: bool) -> str:
if first_column_printed[0]:
# we can print in any case
return ', '
elif print:
# we start printing commas (i.e. not commenting anymore)
if not first_column_printed[0]:
# this is the first column to be printed => no comma
first_column_printed[0] = True
return ''
else:
return ', '
else:
return ''
return '\n{indent}'\
.format(indent=indent*' ')\
.join(['{skip}{comma}{table_alias}.{column}{col_alias}'
.format(comma=print_comma(first_column_printed, col not in skip_columns),
skip='-- ' if col in skip_columns else '',
table_alias=table_alias or table_name,
column=col,
col_alias=__column_alias(col, remap_columns=remap_columns, prefix=prefix, prepend_as=True))
for col in cols if (comment_skipped or col not in skip_columns)])
def insert_command(pg_cur: cursor,
table_schema: str,
table_name: str,
table_type: str = "table",
table_alias: str = None,
remove_pkey: bool = True,
pkey: str = None,
coalesce_pkey_default: bool = False,
skip_columns: list = [],
comment_skipped: bool = True,
remap_columns: dict = {},
insert_values: dict = {},
columns_on_top: list = [],
columns_at_end: list = [],
prefix: str = None,
returning: str = None,
indent: int = 2,
inner_defaults: dict = {}) -> str:
"""
Creates an INSERT command
Parameters
----------
pg_cur
the psycopg cursor
table_schema
the schema
table_name
the name of the table
table_type
the type of table, i.e. view or table
table_alias
the alias of the table
remove_pkey
if True, the primary is removed from the list
pkey
can be manually specified.
coalesce_pkey_default
if True, the following expression is used to insert the primary key: COALESCE( NEW.{pkey}, {default_value} )
skip_columns
list of columns to be skipped
comment_skipped
if True, skipped columns are written but commented, otherwise they are not written
remap_columns
dictionary to remap columns
insert_values
dictionary of expression to be used at insert
columns_on_top
bring the columns to the front of the list
columns_at_end
bring the columns to the end of the list
prefix
add a prefix to the columns (do not applied to remapped columns)
returning
returning command
indent add
an indent in front
inner_defaults
dictionary of other columns to default to in case the provided value is null (can be used instead of insert_values to make it easier to reuse other columns definitions)
"""
remove_pkey = remove_pkey and pkey is None
# get columns
try:
pk_for_sort = primary_key(pg_cur, table_schema, table_name)
except TableHasNoPrimaryKey:
pk_for_sort = None
cols = sorted(columns(pg_cur,
table_schema=table_schema,
table_name=table_name,
table_type=table_type,
remove_pkey=remove_pkey),
key=lambda col, pk_for_sort=pk_for_sort: __column_priority(
col, primary_key=pk_for_sort))
if pkey and remove_pkey:
cols.remove(pkey)
# if no columns, return NULL
if len([col for col in cols if col not in skip_columns]) == 0:
return "-- Do not insert for {} since all columns are skipped".format(
table_name)
if not pkey and coalesce_pkey_default:
pkey = primary_key(pg_cur, table_schema, table_name)
# check arguments
for param, dict_or_list in {
'skip_columns': skip_columns,
'remap_columns': remap_columns,
'insert_values': insert_values,
'columns_on_top': columns_on_top,
'columns_at_end': columns_at_end
}.items():
for col in dict_or_list:
if col not in cols:
raise InvalidColumn(
'Invalid column in {param} paramater: "{tab}" has no column "{col}"'
.format(param=param, tab=table_name, col=col))
def value(col):
if col in insert_values:
return '{val} -- {ori_col}'.format(val=insert_values[col],
ori_col=col)
cal = __column_alias(col,
remap_columns=remap_columns,
prefix=prefix,
field_if_no_alias=True)
if coalesce_pkey_default and col == pkey:
return 'COALESCE( NEW.{cal}, {pk_def} )'.format(
cal=cal,
pk_def=default_value(pg_cur, table_schema, table_name, pkey))
elif col in inner_defaults:
def_col = inner_defaults[col]
# we don't use COALESCE to deal with empt strings too
# we use recursion in case we need to call default to obj_id which may be calculated as just above
return 'CASE WHEN NEW.{cal} IS NOT NULL AND NEW.{cal}::text <> \'\' THEN NEW.{cal} ELSE {default} END'.format(
cal=cal, default=value(def_col))
else:
return 'NEW.{cal}'.format(cal=cal)
next_comma_printed_1 = [False]
next_comma_printed_2 = [False]
return """INSERT INTO {s}.{t} (
{indent} {cols}
{indent} ) VALUES (
{indent} {new_cols}
{indent} ){returning};
""".format(indent=indent * ' ',
s=table_schema,
t=table_name,
cols='\n{indent} '.format(indent=indent * ' ').join([
'{skip}{comma}{col}'.format(
indent=indent * ' ',
skip='-- ' if col in skip_columns else '',
comma=', ' if __print_comma(next_comma_printed_1, col
in skip_columns) else '',
col=col) for col in cols
if (comment_skipped or col not in skip_columns)
]),
new_cols='\n{indent} '.format(indent=indent * ' ').join([
'{skip}{comma}{value}'.format(
skip='-- ' if col in skip_columns else '',
comma=', ' if __print_comma(next_comma_printed_2, col
in skip_columns) else '',
value=value(col)) for col in cols
if (comment_skipped or col not in skip_columns)
]),
returning=' RETURNING {returning}'.format(
indent=4 * ' ', returning=returning) if returning else '')
def update_command(pg_cur: cursor,
table_schema: str,
table_name: str,
table_alias: str = None,
table_type: str = 'table',
remove_pkey: bool = True,
pkey: str = None,
skip_columns: list = [],
comment_skipped: bool = True,
remap_columns: dict = {},
update_values: dict = {},
columns_on_top: list = [],
columns_at_end: list = [],
prefix: str = None,
where_clause: str = None,
indent: int = 2) -> str:
"""
Creates an UPDATE command
:param pg_cur: the psycopg cursor
:param table_schema: the schema
:param table_name: the name of the table
:param table_type: the type of table, i.e. view or table
:param remove_pkey: if True, the primary key will also be updated
:param pkey: can be manually specified.
:param table_alias: if not specified, table is used
:param skip_columns: list of columns to be skipped
:param comment_skipped: if True, skipped columns are written but commented, otherwise they are not written
:param remap_columns: dictionary to remap columns
:param update_values: dictionary of expression to be used at insert
:param columns_on_top: bring the columns to the front of the list
:param columns_at_end: bring the columns to the end of the list
:param prefix: add a prefix to the columns (do not applied to remapped columns)
:param where_clause: can be manually specified
:param indent: add an indent in front
:return: the SQL command
"""
remove_pkey = remove_pkey and pkey is None and where_clause is None
# get columns
cols = sorted(columns(pg_cur,
table_schema=table_schema,
table_name=table_name,
table_type=table_type,
remove_pkey=remove_pkey),
key=lambda _col: __column_priority(_col))
if pkey and remove_pkey:
cols.remove(pkey)
if not pkey and not where_clause:
pkey = primary_key(pg_cur, table_schema, table_name)
# check arguments
for param, dict_or_list in {
'skip_columns': skip_columns,
'remap_columns': remap_columns,
'update_values': update_values,
'columns_on_top': columns_on_top,
'columns_at_end': columns_at_end
}.items():
for col in dict_or_list:
if col not in cols and col != pkey:
raise InvalidColumn(
'Invalid column in {param} paramater: "{tab}" has no column "{col}"'
.format(param=param, tab=table_name, col=col))
next_comma_printed = [False]
return """UPDATE {s}.{t}{a} SET
{indent} {cols}
{indent} WHERE {where_clause};"""\
.format(indent=indent*' ',
s=table_schema,
t=table_name,
a=' {alias}'.format(alias=table_alias) if table_alias else '',
cols='\n{indent} '
.format(indent=indent*' ')
.join(['{skip}{comma}{col} = {new_col}'
.format(indent=indent*' ',
skip='-- ' if col in skip_columns else '',
comma=', ' if __print_comma(next_comma_printed, col in skip_columns) else '',
col=col,
new_col=update_values.get(col,
'NEW.{cal}'.format(cal=__column_alias(col,
remap_columns=remap_columns,
prefix=prefix,
field_if_no_alias=True))))
for col in cols if (comment_skipped or col not in skip_columns)]),
where_clause=where_clause or '{pkey} = {pkal}'.format(pkey=pkey,
pkal=update_values.get(pkey,
'OLD.{cal}'.format(cal=__column_alias(pkey,
remap_columns=remap_columns,
prefix=prefix,
field_if_no_alias=True)))))
def insert_command(pg_cur: cursor,
table_schema: str,
table_name: str,
table_type: str = 'table',
table_alias: str = None,
remove_pkey: bool = True,
pkey: str = None,
coalesce_pkey_default: bool = False,
skip_columns: list = [],
comment_skipped: bool = True,
remap_columns: dict = {},
insert_values: dict = {},
columns_on_top: list = [],
columns_at_end: list = [],
prefix: str = None,
returning: str = None,
indent: int = 2) -> str:
"""
:param pg_cur: the psycopg cursor
:param table_schema: the schema
:param table_name: the name of the table
:param table_type: the type of table, i.e. view or table
:param table_alias: the alias of the table
:param remove_pkey: if True, the primary is removed from the list
:param pkey: can be manually specified.
:param coalesce_pkey_default: if True, the following expression is used to insert the primary key: COALESCE( NEW.{pkey}, {default_value} )
:param skip_columns: list of columns to be skipped
:param comment_skipped: if True, skipped columns are written but commented, otherwise they are not written
:param remap_columns: dictionary to remap columns
:param insert_values: dictionary of expression to be used at insert
:param columns_on_top: bring the columns to the front of the list
:param columns_at_end: bring the columns to the end of the list
:param prefix: add a prefix to the columns (do not applied to remapped columns)
:param returning: returning command
:param indent: add an indent in front
:return:
"""
remove_pkey = remove_pkey and pkey is None
# get columns
cols = sorted(columns(pg_cur,
table_schema=table_schema,
table_name=table_name,
table_type=table_type,
remove_pkey=remove_pkey),
key=lambda col: __column_priority(col))
if pkey and remove_pkey:
cols.remove(pkey)
if not pkey and coalesce_pkey_default:
pkey = primary_key(pg_cur, table_schema, table_name)
# check arguments
for param, dict_or_list in {
'skip_columns': skip_columns,
'remap_columns': remap_columns,
'insert_values': insert_values,
'columns_on_top': columns_on_top,
'columns_at_end': columns_at_end
}.items():
for col in dict_or_list:
if col not in cols:
raise InvalidColumn(
'Invalid column in {param} paramater: "{tab}" has no column "{col}"'
.format(param=param, tab=table_name, col=col))
def value(col):
if col in insert_values:
return '{val} -- {ori_col}'.format(val=insert_values[col],
ori_col=col)
cal = __column_alias(col,
remap_columns=remap_columns,
prefix=prefix,
field_if_no_alias=True)
if coalesce_pkey_default and col == pkey:
return 'COALESCE( NEW.{cal}, {pk_def} )'.format(
cal=cal,
pk_def=default_value(pg_cur, table_schema, table_name, pkey))
else:
return 'NEW.{cal}'.format(cal=cal)
next_comma_printed_1 = [False]
next_comma_printed_2 = [False]
return """INSERT INTO {s}.{t} (
{indent} {cols}
{indent} ) VALUES (
{indent} {new_cols}
{indent} ){returning};
""".format(indent=indent * ' ',
s=table_schema,
t=table_name,
cols='\n{indent} '.format(indent=indent * ' ').join([
'{skip}{comma}{col}'.format(
indent=indent * ' ',
skip='-- ' if col in skip_columns else '',
comma=', ' if __print_comma(next_comma_printed_1, col
in skip_columns) else '',
col=col) for col in cols
if (comment_skipped or col not in skip_columns)
]),
new_cols='\n{indent} '.format(indent=indent * ' ').join([
'{skip}{comma}{value}'.format(
skip='-- ' if col in skip_columns else '',
comma=', ' if __print_comma(next_comma_printed_2, col
in skip_columns) else '',
value=value(col)) for col in cols
if (comment_skipped or col not in skip_columns)
]),
returning=' RETURNING {returning}'.format(
indent=4 * ' ', returning=returning) if returning else '')