Thank you both for the advice. Dern NULLs causing trouble again. GL
On Tue, Sep 15, 2009 at 4:34 PM, Conor <conor.edward.da...@gmail.com> wrote: > > On Sep 15, 4:08 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > > Gregg Lind wrote: > > > What I think I'm seeing is that an object can be created even without > it's > > > ForeignKeyConstraint being filled. > > > > > To run the test code below: > > > > > $ dropdb test18; createdb test18; python testcode.py > > > > "on" is not defined: > > > > ForeignKeyConstraint(['regstring_id', 'regstring','regstring_type'], > > [A1String.id, A1String.string, A1String.origin], on), > > > > when removing "on", the row inserts with "regstring_id" as NULL. PG > > appears to accept this so I would assume PG considers a three-column > > foreign key with one NULL to be NULL. If I try it with all three columns > > not null, then you get the constraint error. > > > > SQLalchemy itself relies upon the database to enforce constraints. In > > this case you should have the "NOT NULL" constraint on the Product > > columns. > > > > To expand on this: > Most (all?) databases default to a MATCH SIMPLE policy for foreign key > constraints: if any FK column is NULL then the FK constraint is > satisfied (regardless of the actual values of the non-null columns). > It looks like you want MATCH FULL behavior: if some but not all FK > columns are NULL then the FK constraint fails. > > Assuming you really do need the the FK columns to be nullable, you > have to either add MATCH FULL to your DDL (probably have to use DDL() > + ALTER TABLE; also requires that your database actually supports > MATCH FULL) or add a check constraint that mimics the MATCH FULL > behavior, e.g.: > (regstring_id IS NULL) = (regstring IS NULL) = (regstring_type IS > NULL) > > > > This builds on > > >http://groups.google.com/group/sqlalchemy/browse_thread/thread/eb240f. > .. > > > . > > > > > I understand that the foreign table can't create the referent. (and > > > finding > > > the best idiom for "use one if it exists or create one") is yet be > > > determined. What I truly don't understand is how any instances of > > > "Product" > > > can be created, since there is a FK constraint that is not fulfulled. > > > > > 1. Is the foreign key constraint fulfilled? > > > 2. Is there a good "create the referent if it doesn't exist, else use > it" > > > idiom? > > > 3. Is the polymorphic table business complicating it? It seems liek > the > > > compound primary key for A1String is. > > > > > ---------------- > > > from sqlalchemy.ext.declarative import > > > declarative_base > > > from sqlalchemy import CheckConstraint, ForeignKey, MetaData, > > > PrimaryKeyConstraint > > > from sqlalchemy import ForeignKeyConstraint > > > from sqlalchemy import Table, Column, Integer, Boolean,Unicode,String > > > from sqlalchemy.orm import relation, backref > > > from sqlalchemy import create_engine > > > from sqlalchemy.orm import sessionmaker > > > from sqlalchemy.schema import DDL > > > > > import sys > > > ECHO = bool((sys.argv + [False])[1]) > > > > > ## utilties for connecting the db, printing it, etc. > > > def print_schema(T="postgres", Base=None): > > > ''' print print_schema will print the schema in use ''' > > > from StringIO import StringIO > > > buf = StringIO() > > > engine = create_engine('%s://' % T, strategy='mock', > executor=lambda > > > s, > > > p='': buf.write(str(s) + p)) > > > Base.metadata.create_all(engine) > > > return buf.getvalue() > > > > > def db_setup(connstring='sqlite:///:memory:', echo=False, Base=None): > > > engine = create_engine(connstring, echo=echo) > > > Session = sessionmaker(bind=engine, autoflush=False, > autocommit=False) > > > session = Session() > > > Base.metadata.bind = engine > > > Base.metadata.create_all() > > > return session, engine > > > > > def _class_repr(self): > > > ''' print our SA class instances in a nicer way ''' > > > # ugly, use sparingly, may have performance hit > > > d = [(k,v) for k,v in self.__dict__.iteritems() if k[0] != "_"] > > > d = sorted(d, key=lambda x: x[0].lower()) > > > return "<%s, %s>" % (self.__class__, d) > > > > > Base = declarative_base() > > > > > class Polystring(Base): > > > __tablename__ = 'strings' > > > id = Column(Integer, nullable=False, primary_key=True) > > > string = Column(String, nullable=False, primary_key=True) > > > origin = Column(String, nullable=False, primary_key=True) > > > __mapper_args__ = {'polymorphic_on': origin} > > > > > # subtype of string > > > class A1String(Polystring): > > > __mapper_args__ = {'polymorphic_identity': 'a1'} > > > products = relation('Product', order_by="Product.id") > > > > > class Product(Base): > > > __tablename__ = 'product' > > > __table_args__ = ( > > > ForeignKeyConstraint(['regstring_id', 'regstring', > > > 'regstring_type'], [A1String.id, A1String.string > > > , A1String.origin], on), > > > {} > > > ) > > > id = Column(Integer,primary_key=True) > > > regstring_id = Column(Integer) > > > regstring = Column(String) > > > regstring_type = Column(String,default="asn") > > > > > ## test code > > > session,eng = db_setup("postgres:///test18", Base=Base, echo=ECHO) > > > add = session.add > > > q = session.query > > > c = session.commit > > > r = _class_repr > > > > > A = Product(id=192832, regstring="some part id") > > > print r(A) > > > add(A) > > > c() # commit > > > print map(r,q(Product).all()) > > > print "somehow this managed to get in, without making a polystring, > which > > > it > > > should be referencing." > > > assert len(q(Polystring).all()) > 0, "So, where is the polystring?" > > > ---------- > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
