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