Hi again, On Apr 10, 11:17 pm, Michael Bayer <[email protected]> wrote: > On Apr 10, 2011, at 1:27 PM, Lars wrote: > > > Hi Michael, > > > Thank you for the suggestions. > > > I noticed that all the foreign keys to superclasses are identical > > (same id number). What would be the disadvantage of using only primary > > keys and using those in setting up relationships (compared to using > > foreign keys)? > > When two columns in a relational database, one being a primary key, the other > being possibly a primary key, are logically constrained to have the same > value, that's known as a foreign key. It's then best practice to actually > place a real foreign key constraint in the database to enforce this at the > data level. SQLite and MySQL by default don't enforce it, though. > SQLAlchemy doesn't particularly care if an actual constraint is defined in > the database, it can be told to join on any combination of columns. The > presence of a foreign key within table metadata just makes this automatic. > Does that mean that if you don't set the foreign key constraint and you want to be able to set an attribute, you also have to write code to copy the primary key of the parent to the child? > > > If I use a root base class (similar to "object" in python) and add a > > type column/attribute to refer to the actual class of the object/ > > record, is there an elegant way to get/set/del the polymorphic > > attribute object of the correct class using this type? > > Which table is this "type" column present in, base1 or base2 ? > Neither, I was thinking of using a class that is the root base class of all classes (not present in code above) to store the type and the "primary key" of all objects. This "type" should be accessible in all classes, since they would all be direct or indirect subclasses of this root class. Say that I store all classes in a dictionary called registry, then registry[type] would give the actual class of the object identified with the primary key in the root class table. This key would also identify the correct record in all superclasses of this class, e.g. to be used in a join over these classes.
(hope this is somewhat clear ...) > > > Would it be possible to redefine query() using this type to first > > resolve the correct table and use the query method SA provides on that > > (without this resulting in otherwise changing the API) ? > > but what's the "correct" table, base1, base2, claz ? with multiple > inheritance the path is not a straight line. If you have a mostly straight > inheritance model with an occasional offshoot, say its base1 -> subbase1 -> > (subbase1 , base2) -> claz, I'd use traditional inheritance and have just > "base2" via relationship(). > The correct table would be the table corresponding to the value in the type column. I am working on a framework where others will design the data structure and would like to use a uniform metadata model to avoid extra choices for the designer. > Cheers again, Lars > > > On Apr 7, 8:42 pm, Michael Bayer <[email protected]> wrote: > >> On Apr 7, 2011, at 2:30 PM, Lars wrote: > > >>> Hello, > > >>> I am pretty determined to find a way to get (a simplified version of) > >>> multiple inheritance working with SA. The simplification lies in that > >>> no overriding of attributes will be possible (but I don't know whether > >>> that is significant). I was thinking of a schema as follows: > > >>> --------------------------------------------------------------------------- > >>> ----------- > > >>> metadata = MetaData() > > >>> base1_table = Table("base1_table", metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('str', String) > >>> ) > > >>> base2_table = Table("base2_table", metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('int', Integer) > >>> ) > > >>> claz1_table = Table("claz1_table", metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('base1_id', None, ForeignKey('base1_table.id')), > >>> Column('base2_id', None, ForeignKey('base2_table.id')), > >>> Column('assoc_id', None, ForeignKey('assoc_table.id')) > >>> ) > > >>> assoc_table = Table("assoc_table", metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('name', String(50), nullable=False), > >>> Column('type', String(50), nullable=False) > >>> ) > > >>> base3_table = Table("base3_table", metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('assoc_id', None, ForeignKey('assoc_table.id')), > >>> Column('bool', Boolean) > >>> ) > > >>> claz2_table = Table("claz2_table", metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('base3_id', None, ForeignKey('base3_table.id')), > >>> Column('date', Date) > >>> ) > > >>> class base1(object): > >>> pass > >>> class base2(object): > >>> pass > >>> class base3(object): > >>> pass > >>> class claz1(base1, base2): > >>> pass > >>> class claz2(base3): > >>> pass > > >>> # do mappings, relationships and e.g. be able to > > >>> c1 = claz1(str = "hello", int = 17) > >>> setattr(c1, name, claz2(bool = True, date = Date(2010,9,10))) > > >> You can just forego the "inherits" flag and map each class to the > >> appropriate join or base table. claz1 would be mapped to a join of the > >> three tables involved. The difficult part of course is the query side, > >> if you're looking to query "base1" or "base2" and get back "claz1" > >> objects. > > >> Alternatively, each class can be mapped to one table only, and > >> relationship() used to link to other tables. Again if you don't use > >> the "inherits" flag, you can maintain the class hierarchy on the Python > >> side and use association proxies to provide local access to attributes > >> that are normally on the related class. This would still not give you > >> polymorphic loading but would grant a little more flexibility in which > >> tables are queried to start. > > >>> --------------------------------------------------------------------------- > >>> -------------------------------------- > > >>> I am still pretty new to SA. Can anyone give me any hints, tips, > >>> issues with this scheme (e.g. about how to do the mappings, > >>> descriptors, etc)? > > >>> The step after will be to write factory functions/metaclasses to > >>> generate these dynamically. > > >>> Multiple inheritance is very important for my use case. > > >>> Cheers, Lars > > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "sqlalchemy" group. > >>> To post to this group, send email to [email protected]. > >>> To unsubscribe from this group, send email to > >>> [email protected]. > >>> For more options, visit this group > >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
