i seemed to have stumped SO, so i am trying with a repost here.
here is an example of what our postgres database does (obviously, this
is simplified):
CREATE TABLE system (system_id SERIAL PRIMARY KEY,
system_name VARCHAR(24) NOT NULL);
CREATE TABLE file_entry(file_entry_id SERIAL,
file_entry_msg VARCHAR(256) NOT NULL,
file_entry_system_name VARCHAR(24) REFERENCES
system(system_name) NOT NULL);
CREATE TABLE ops_file_entry(CONSTRAINT ops_file_entry_id_pkey PRIMARY
KEY (file_entry_id),
CONSTRAINT ops_system_name_check CHECK ((file_entry_system_name =
'ops'::bpchar))) INHERITS (file_entry);
CREATE TABLE eng_file_entry(CONSTRAINT eng_file_entry_id_pkey PRIMARY
KEY (file_entry_id),
CONSTRAINT eng_system_name_check CHECK ((file_entry_system_name =
'eng'::bpchar)) INHERITS (file_entry);
CREATE INDEX ops_file_entry_index ON ops_file_entry USING btree
(file_entry_system_id);
CREATE INDEX eng_file_entry_index ON eng_file_entry USING btree
(file_entry_system_id);
And then the inserts would be done with a trigger, so that they were
properly inserted into the child databases. Something like:
CREATE FUNCTION file_entry_insert_trigger() RETURNS "trigger"
AS $$
DECLARE
BEGIN
IF NEW.file_entry_system_name = 'eng' THEN
INSERT INTO eng_file_entry(file_entry_id, file_entry_msg,
file_entry_type, file_entry_system_name) VALUES (NEW.file_entry_id,
NEW.file_entry_msg, NEW.file_entry_type, NEW.file_entry_system_name);
ELSEIF NEW.file_entry_system_name = 'ops' THEN
INSERT INTO ops_file_entry(file_entry_id, file_entry_msg,
file_entry_type, file_entry_system_name) VALUES (NEW.file_entry_id,
NEW.file_entry_msg, NEW.file_entry_type, NEW.file_entry_system_name);
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
in summary, i have a parent table with a foreign key to another table.
then i have 2 child tables that exist, and the inserts are done based
upon a given value.
in my example above, if file_entry_system_name is 'ops', then the row
goes into the ops_file_entry table; 'eng' goes into
eng_file_entry_table. we have hundreds of children tables in our
production environment, and considering the amount of data, it really
speeds things up, so i would like to keep this same structure. i can
query the parent, and as long as i give it the right 'system_name', it
immediately knows which child table to look into.
my desire is to emulate this with sqlalchemy, but i can't find any
examples that go into this much detail. i look at the sql generated by
sqlalchemy by examples, and i can tell it is not doing anything
similar to this on the database side.
the best i can come up with is something like:
class System(_Base):
__tablename__ = 'system'
system_id = Column(Integer, Sequence('system_id_seq'), primary_key
= True)
system_name = Column(String(24), nullable=False)
def __init(self, name)
self.system_name = name
class FileEntry(_Base):
__tablename__ = 'file_entry'
file_entry_id = Column(Integer, Sequence('file_entry_id_seq'),
primary_key=True)
file_entry_msg = Column(String(256), nullable=False)
file_entry_system_name = Column(String(24), nullable=False,
ForeignKey('system.system_name'))
__mapper_args__ = {'polymorphic_on': file_entry_system_name}
def __init__(self, msg, name)
self.file_entry_msg = msg
self.file_entry_system_name = name
class ops_file_entry(FileEntry):
__tablename__ = 'ops_file_entry'
ops_file_entry_id = Column(None,
ForeignKey('file_entry.file_entry_id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity': 'ops_file_entry'}
in the end, what am i missing? how do i tell sqlalchemy to associate
anything that is inserted into FileEntry with a system name of 'ops'
to go to the 'ops_file_entry' table? is my understanding way off?
when i implement this and look at the sql, no inheritance is being
used on the postgres side.
--
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.
