def __init__(self, parent_table: str, child_table: str,
pg_service: str = None,
view_schema: str = None,
view_name: str = None,
pkey_default_value: bool = False,
inner_defaults: dict = {}):
"""
Produces the SQL code of the join table and triggers
Parameters
----------
pg_service
if not given, it is determined using environment variable PGSERVICE
parent_table
the parent table, can be schema specified
child_table
the child table, can be schema specified
view_schema
the schema where the view will written to
view_name
the name of the created view, defaults to vw_{parent_table}_{join_table}
pkey_default_value
the primary key column of the view will have a default value according to the child primary key table
inner_defaults
dictionary of other columns to default to in case the provided value is null or empty
"""
if pg_service is None:
pg_service = os.getenv('PGSERVICE')
self.conn = psycopg2.connect("service={0}".format(pg_service))
self.cursor = self.conn.cursor()
self.pkey_default_value = pkey_default_value
self.inner_defaults = inner_defaults
(self.parent_schema, self.parent_table) = table_parts(parent_table)
(self.child_schema, self.child_table) = table_parts(child_table)
if view_schema is None:
if self.parent_schema != self.child_schema:
raise ValueError('Destination schema cannot be guessed if different on sources tables.')
else:
self.view_schema = self.parent_schema
else:
self.view_schema = view_schema
self.view_name = view_name or "vw_{pt}_{ct}".format(pt=self.parent_table, ct=self.child_table)
(self.ref_parent_key, parent_referenced_key) = reference_columns(self.cursor,
self.child_schema, self.child_table,
self.parent_schema, self.parent_table)
try:
self.child_pkey = primary_key(self.cursor, self.child_schema, self.child_table)
except TableHasNoPrimaryKey:
self.child_pkey = self.ref_parent_key
self.parent_pkey = primary_key(self.cursor, self.parent_schema, self.parent_table)
assert self.parent_pkey == parent_referenced_key
def __init__(self,
definition: dict,
pg_service: str = None,
variables: dict = {},
create_joins: bool = False,
drop: bool = False):
"""
Produces the SQL code of the join table and triggers
Parameters
----------
definition
the YAML definition of the multiple inheritance
pg_service
if not given, it is determined using environment variable PGSERVICE
variables
dictionary for variables to be used in SQL deltas ( name => value )
create_joins
if True, simple joins will be created for all joined tables
drop
if True, will drop any existing view, type or trigger that will be created later
"""
self.variables = variables
self.create_joins = create_joins
self.drop = drop
self.pg_service = pg_service
if self.pg_service is None:
self.pg_service = os.getenv('PGSERVICE')
self.conn = psycopg2.connect("service={0}".format(self.pg_service))
self.cursor = self.conn.cursor()
# check definition validity
for key in definition.keys():
if key not in ('table', 'view_schema', 'view_name', 'alias',
'short_alias', 'type_name', 'joins',
'insert_trigger', 'update_trigger',
'allow_type_change', 'allow_parent_only',
'additional_columns', 'additional_joins',
'merge_columns', 'merge_geometry_columns',
'pkey_default_value'):
raise InvalidDefinition('key {k} is not a valid'.format(k=key))
# check joins validity
for alias, table_def in definition['joins'].items():
for key in table_def.keys():
if key not in ('table', 'short_alias', 'skip_columns', 'fkey',
'remap_columns', 'prefix', 'insert_values',
'update_values'):
raise InvalidDefinition(
'in join {a} key "{k}" is not valid'.format(a=alias,
k=key))
for mandatory_key in ['joins']:
if mandatory_key not in definition:
raise InvalidDefinition(
'Missing key: "{k}" should be provided.'.format(
k=mandatory_key))
# check trigger modifiers validity
for trigger in ('insert_trigger', 'update_trigger'):
for key in definition.get(trigger, {}):
if key not in ('declare', 'pre', 'post'):
raise InvalidDefinition(
'key {k} is not valid in trigger definitions'.format(
k=key))
(self.master_schema,
self.master_table) = table_parts(definition.get('table', None))
self.master_skip_colums = definition.get('skip_columns', [])
self.master_prefix = definition.get('prefix', None)
self.master_remap_columns = definition.get('remap_columns', {})
self.pkey_default_value = definition.get('pkey_default_value', False)
# global options:
self.view_schema = definition.get('view_schema', self.master_schema)
self.view_name = definition.get(
'view_name', "vw_merge_{t}".format(t=self.master_table))
self.view_alias = definition.get('alias', self.master_table)
self.short_alias = definition.get('short_alias', self.view_alias)
self.type_name = definition.get('type_name',
'{al}_type'.format(al=self.view_alias))
self.insert_trigger = definition.get('insert_trigger', {})
self.update_trigger = definition.get('update_trigger', {})
self.allow_parent_only = definition.get('allow_parent_only', True)
self.allow_type_change = definition.get('allow_type_change', True)
self.additional_joins = definition.get('additional_joins', None)
self.additional_columns = definition.get('additional_columns', {})
try:
self.master_pkey = primary_key(self.cursor, self.master_schema,
self.master_table)
except TableHasNoPrimaryKey:
raise TableHasNoPrimaryKey(
'{vn} has no primary key, specify it with "key"'.format(
vn=self.view_alias))
# parse the joins definition
self.joins = definition['joins']
self.joined_ref_master_key = []
for alias, table_def in self.joins.items():
(table_def['table_schema'],
table_def['table_name']) = table_parts(table_def['table'])
table_def['short_alias'] = table_def.get('short_alias', alias)
if 'fkey' in table_def:
table_def['ref_master_key'] = table_def['fkey']
else:
table_def['ref_master_key'] = reference_columns(
self.cursor, table_def['table_schema'],
table_def['table_name'], self.master_schema,
self.master_table)[0]
try:
table_def['pkey'] = primary_key(self.cursor,
table_def['table_schema'],
table_def['table_name'])
except TableHasNoPrimaryKey:
table_def['pkey'] = table_def['ref_master_key']
# pre-process merged columns
self.merge_column_cast = {}
# for geometry columns, we need to get the type to cast the NULL value
merge_geometry_columns = definition.get('merge_geometry_columns', [])
for col in merge_geometry_columns:
for table_def in self.joins.values():
gt = geometry_type(self.cursor, table_def['table_schema'],
table_def['table_name'], col)
if gt:
self.merge_column_cast[
col] = '::geometry({type},{srid})'.format(type=gt[0],
srid=gt[1])
break
if col not in self.merge_column_cast:
raise InvalidDefinition(
'There is no geometry column "{col}" in joined tables'.
format(col=col))
self.merge_columns = definition.get('merge_columns',
[]) + merge_geometry_columns
def __init__(self, definition: dict, pg_service: str = None):
"""
Produces the SQL code of the join table and triggers
Parameters
----------
definition
the YAML definition of the multiple inheritance
pg_service
if not given, it is determined using environment variable PGSERVICE
"""
# check definition validity
for key in definition.keys():
if key not in ('table', 'view_schema', 'joins', 'pkey',
'view_name'):
raise InvalidDefinition('key {k} is not a valid'.format(k=key))
# check joins validity
for alias, table_def in definition['joins'].items():
for key in table_def.keys():
if key not in ('table', 'short_alias', 'fkey', 'prefix',
'skip_columns', 'remap_columns'):
raise InvalidDefinition(
'in join {a} key "{k}" is not valid'.format(a=alias,
k=key))
if pg_service is None:
pg_service = os.getenv('PGSERVICE')
self.conn = psycopg2.connect("service={0}".format(pg_service))
self.cursor = self.conn.cursor()
(self.parent_schema,
self.parent_table) = table_parts(definition['table'])
self.view_schema = definition.get('view_schema', self.parent_schema)
self.view_name = definition.get('view_name',
"vw_{pt}".format(pt=self.parent_table))
try:
self.parent_pkey = primary_key(self.cursor, self.parent_schema,
self.parent_table)
except TableHasNoPrimaryKey:
self.parent_pkey = definition['pkey']
class Table:
def __init__(self):
self.schema_name = None
self.table_name = None
self.pkey = None
self.ref_parent_key = None
self.parent_referenced_key = None
self.skip_columns = None
self.remap_columns = None
self.prefix = None
self.child_tables = {}
for alias, table_def in definition['joins'].items():
child = Table()
(child.schema_name,
child.table_name) = table_parts(table_def['table'])
child.pkey = primary_key(self.cursor, child.schema_name,
child.table_name)
try:
(child.parent_referenced_key,
child.ref_parent_key) = reference_columns(
self.cursor, self.parent_schema, self.parent_table,
child.schema_name, child.table_name)
assert child.pkey == child.ref_parent_key
except NoReferenceFound:
child.parent_referenced_key = table_def['fkey']
child.skip_columns = table_def.get('skip_columns', {})
child.remap_columns = table_def.get('remap_columns', {})
child.prefix = table_def.get('prefix', None)
self.child_tables[alias] = child
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 '')