def check_create_fk(self, from_table_id, to_table_id, ignoreexisting=False):
from_type = setobject_type_registry.lookup_by_table(from_table_id)
to_type = setobject_type_registry.lookup_by_table(to_table_id)
pk = to_type.get_primary_key_attr_name()
# Now add foreign key if not existant yet
if not field_exists(from_table_id, self.foreignkeycol):
col = Column(
self.foreignkeycol,
getattr(to_type.get_table_class().c, pk).type,
ForeignKey(to_table_id + "." + pk),
)
col.create(from_type.get_table_class())
# The foreign key column has been newly created
Session().flush()
# deferred import
from p2.datashackle.core.models.mapping import map_tables
map_tables(exclude_sys_tables=True)
else:
# it exists, check whether it is what we want or something else
fkset = getattr(from_type.get_table_class().c, self.foreignkeycol).foreign_keys
if len(fkset) > 0:
for fk in fkset:
if str(fk.column) == to_table_id + "." + pk \
and ignoreexisting == True:
return # this is what we want! fine.
raise UserException("A relation with a similar Data Field Name but targetting the table '" + \
str(fk.column).split('.',1)[0] + "' already exists. Please use another Data Field Name.")
raise UserException("The column '" + self.foreignkeycol + "' in the table '" + to_table_id + \
"' does already exist. Please choose a unique Data Field Name that doesn't collide with existing data columns.")
def init_xref_relation(self):
# Get some vars beforehand:
type1 = setobject_type_registry.lookup(self.source_module, self.source_classname)
type2 = setobject_type_registry.lookup(self.target_module, self.target_classname)
table1class = type1.get_table_class()
table2class = type2.get_table_class()
table1primarykey = type1.get_primary_key_attr_name()
table2primarykey = type2.get_primary_key_attr_name()
table1name = type1.get_table_name()
table2name = type2.get_table_name()
if self.relation.foreignkeycol.lower() == self.relation.foreignkeycol2.lower():
raise UserException("Please choose different column names for both of the two foreign key columns")
# Create/obtain xref table
fkcol1 = Column(
self.relation.foreignkeycol,
getattr(table1class.c, table1primarykey).type,
ForeignKey(table1name + "." + table1primarykey)
)
fkcol2 = Column(
self.relation.foreignkeycol2,
getattr(table2class.c, table2primarykey).type,
ForeignKey(table2name + "." + table2primarykey)
)
mytable = Table(self.relation.xref_table, metadata, useexisting=True)
if not mytable.exists():
# Create table with new columns
mytable = Table(self.relation.xref_table, metadata,
fkcol1,
fkcol2,
Column('id', Integer(8), primary_key=True, autoincrement=True, default=0, nullable=False),
useexisting=True, mysql_engine='InnoDB')
# Register table type
setobject_table_registry.register_type('p2.datashackle.core.models.model', self.relation.xref_table, self.relation.xref_table, mytable)
# Create table
mytable.create()
# Create setobject type
create_basemodel_type(self.relation.xref_table, self.relation.xref_table)
else:
# Alter table to have the new columns if they aren't there yet
mytable = Table(self.relation.xref_table, metadata, autoload=True, useexisting=True)
if hasattr(mytable.c, self.relation.foreignkeycol) != True:
# add fkcol1
fkcol1.create(mytable)
if hasattr(mytable.c, self.relation.foreignkeycol2) != True:
# add fkcol2
fkcol2.create(mytable)
# Remap xref table setobject
setobject_type_registry.lookup_by_table(self.relation.xref_table).sa_map()
def create_relation(self):
# Check whether the target table actually exists
try:
targetobj = setobject_type_registry.lookup_by_table(self.target_table)
except KeyError:
raise UserException("The table '" + self.target_classname + "' specified as target table does not exist")
self._create_relation()
def _create_adjacency_list_relation(self):
if not field_exists(self.target_table, self.foreignkeycol):
target = setobject_type_registry.lookup_by_table(self.target_table)
pk = target.get_primary_key_attr_name()
col = Column(
self.foreignkeycol,
getattr(target.get_table_class().c, pk).type,
ForeignKey(self.target_table + "." + pk),
)
col.create(target.get_table_class())
# update session and remap table
#Session().flush()
# re-map user tables with newly created linkage
from p2.datashackle.core.models.mapping import map_tables
map_tables(exclude_sys_tables=True)
def query_joined(self, query_mode):
if query_mode == None:
if (
self.relation.linkage.cardinality.id == "ONE(FK)_TO_ONE"
or self.relation.linkage.cardinality.id == "ONE_TO_ONE(FK)"
):
query_mode = QueryMode.WITH_UNLINKED
else:
query_mode = QueryMode.SHARED
source_type = setobject_type_registry.lookup(self.relation.linkage.source_model.klass)
target_type = setobject_type_registry.lookup(self.relation.linkage.target_model.klass)
session = getUtility(IDbUtility).Session()
if target_type == source_type:
target_type = aliased(source_type)
# Check if it is an xref relation
xref_object = None
if self.relation.linkage.cardinality.id == "MANY_TO_MANY":
xref_object = setobject_type_registry.lookup_by_table(self.relation.linkage.xref_table)
# Compose query for various modes:
if query_mode == QueryMode.SHARED:
query = session.query(target_type)
# For n:m, we need to outerjoin the xref table first:
if not xref_object == None:
query = query.outerjoin(
(
xref_object,
target_type.get_primary_key_attr()
== getattr(xref_object, self.relation.linkage.relation.foreignkeycol2),
)
)
# Simply join with our source table from the target table. The join condition ensures we either get linked elements, or the source table's primary key field will be null/None.
if xref_object == None:
if (
self.relation.linkage.cardinality.id == "ONE_TO_MANY"
or self.relation.linkage.cardinality.id == "ONE_TO_ONE(FK)"
):
joincondition = getattr(source_type, source_type.get_primary_key_attr_name()) == getattr(
target_type, self.relation.linkage.relation.foreignkeycol
)
else:
joincondition = (
getattr(source_type, self.relation.linkage.relation.foreignkeycol)
== target_type.get_primary_key_attr()
)
else:
joincondition = getattr(source_type, source_type.get_primary_key_attr_name()) == getattr(
xref_object, self.relation.linkage.relation.foreignkeycol
)
query = query.outerjoin((source_type, joincondition))
# Since the the source table's primary key is either filled for linked elements or None for unlinked, this simple condition to find out what is linked to ourselves is sufficient.
# Unlinked entries won't match since their source primary key field will be None.
query = query.add_column(
func.max(
case(
[
(
getattr(source_type, source_type.get_primary_key_attr_name())
== self.relation_source.id,
"true",
)
],
else_="false",
)
).label("linked")
)
# For 1:n/1:1(fk), don't allow entries linked to someone else to show up (since they cannot have their foreign key point at two things).
if xref_object == None and (
self.relation.linkage.cardinality.id == "ONE_TO_MANY"
or self.relation.linkage.cardinality.id == "ONE_TO_ONE(FK)"
):
query = query.filter(
or_(
getattr(source_type, source_type.get_primary_key_attr_name()) == self.relation_source.id,
getattr(source_type, source_type.get_primary_key_attr_name()) == None,
)
)
# Group query
query = query.group_by(target_type.get_primary_key_attr())
else:
if query_mode == QueryMode.EXCLUSIVE:
# Inner join should be sufficient
if xref_object is None:
# The join condition is also explicitely required here for cases with many relations to the same table
if self.relation.linkage.cardinality.id == "ONE_TO_MANY":
query = session.query(target_type).join(
(
source_type,
source_type.get_primary_key_attr()
== getattr(target_type, self.relation.linkage.relation.foreignkeycol),
)
)
else:
query = session.query(target_type).join(
(
source_type,
target_type.get_primary_key_attr()
== getattr(source_type, self.relation.linkage.relation.foreignkeycol),
)
)
else:
# n:m, we need to specify the join condition:
query = session.query(target_type).join(
(
xref_object,
target_type.id == getattr(xref_object, self.relation.linkage.relation.foreignkeycol2),
)
)
query = query.join(
(
source_type,
and_(
source_type.id == getattr(xref_object, self.relation.linkage.relation.foreignkeycol),
source_type.id == self.relation_source.get_primary_key_attr(),
),
)
)
elif query_mode == QueryMode.WITH_UNLINKED:
# we require a full outer join
if xref_object is None:
query = session.query(target_type).outerjoin(source_type)
else:
# n:m, we need to specify the join condition:
query = session.query(target_type).outerjoin(
(
xref_object,
target_type.id == getattr(xref_object, self.relation.linkage.relation.foreignkeycol2),
)
)
query = query.join(
(
source_type,
or_(
and_(
source_type.id
== getattr(xref_object, self.relation.linkage.relation.foreignkeycol),
source_type.id == self.relation_source.get_primary_key_attr(),
),
source_type.id == None,
),
)
)
if xref_object is None:
if self.relation.linkage.cardinality.id == "ONE_TO_MANY":
# Foreignkey on target_type side
foreignkey_attr = getattr(target_type, self.relation.linkage.relation.foreignkeycol)
else:
# Foreignkey on source_type side
foreignkey_attr = getattr(source_type, self.relation.linkage.relation.foreignkeycol)
query = query.add_column(case([(foreignkey_attr == None, "false")], else_="true").label("linked"))
if query_mode == QueryMode.WITH_UNLINKED:
query = query.filter(
or_(source_type.get_primary_key_attr() == self.relation_source.id, foreignkey_attr == None)
)
else:
# n:m relation
foreignkeycolumn1 = getattr(xref_object, self.relation.linkage.relation.foreignkeycol)
foreignkeycolumn2 = getattr(xref_object, self.relation.linkage.relation.foreignkeycol2)
query = query.add_column(
case([((or_(foreignkeycolumn1 == None, foreignkeycolumn2 == None)), "false")], else_="true").label(
"linked"
)
)
if query_mode == QueryMode.WITH_UNLINKED:
query = query.filter(
or_(
source_type.get_primary_key_attr() == self.relation_source.id,
foreignkeycolumn1 == None,
foreignkeycolumn2 == None,
)
)
if query_mode == QueryMode.EXCLUSIVE:
query = query.filter(
getattr(source_type, source_type.get_primary_key_attr_name()) == self.relation_source.id
)
# Check for additional constraints on relation widget
if (
hasattr(self.relation, "filter_clause")
and self.relation.filter_clause != None
and len(self.relation.filter_clause) > 0
):
query = query.filter(self.relation.filter_clause)
return query
