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