Hi All
Firstly, I know that SA does not support Postgres Inheritance "out of the
box". But the concepts for concrete inheritance should allow some support,
at least for what I am trying to do. I am new to SA and Python (about a
month of experience here) and so I don't know if I am barking up the wrong
tree. Here is what I think...
Each table has a unique "tableoid" column in Postgres. So lets assume this
table structure:
CREATE TABLE A (
> id SERIAL Primary Key,
> name text
> )
>
> CREATE TABLE B (
> id SERIAL Primary Key,
> name text,
> language text
> ) INHERITS(A)
>
Table B uses the postgres Inherits. Now lets assume that Postgres has given
table A an oid of 1, and table B an oid of 2. If I do...
SELECT A.tableoid, * FROM A
Then the rows that are from table B will have an oid of 2, while those from
A will have an oid of 1. So this mimics a polymorphism in a way *and I can
use this OID as a discriminator.*
Here is my question (finally): How can I accomplish this in SA using
concrete inheritance and polymorphism. What I want is that when querying
table A, a polymorphic call will load up the class representing B in the
appropriate cases. Here follows EXACTLY what I am trying to do (and the
code will follow after):
- There are three postgres tables in play: (1) A Users table, (2) An
Authentications table (3) A Password_Authentications table
- Users has a foreignkey into Authorisations
- Password_Authentications is inherited from Authentications
What I want is that even though the Users table has a foreign key for the
Authorisations table, the Password_Authorisations will load polymorphically
based on the tableoid discriminator. Here is the SQL for the above setup:
SQL
> CREATE TABLE users
> (
> id serial NOT NULL, -- primary key
> username text NOT NULL, -- The username of a twayd user. Besides having
> a not null constraint, there is no other constraint on this field. Instead,
> the child tables will have constraints on them.
> authentication_id bigint NOT NULL, -- Foreign key into the
> authentication table.
> status text NOT NULL DEFAULT 'active'::text,
> CONSTRAINT users_pkey PRIMARY KEY (id ),
> CONSTRAINT users_authentication_id_fkey FOREIGN KEY (authentication_id)
> REFERENCES authentications (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT users_username_key UNIQUE (username ),
> CONSTRAINT users_status_check CHECK (status ~ '^active|suspended$'::text)
> )
> WITH (
> OIDS=TRUE
> );
>
> CREATE TABLE authentications
> (
> id serial NOT NULL,
> CONSTRAINT authentications_pkey PRIMARY KEY (id )
> )
> WITH (
> OIDS=TRUE
> )
>
> CREATE TABLE password_authentications
> (
> -- Inherited from table authentications: id integer NOT NULL DEFAULT
> nextval('authentications_id_seq'::regclass),
> password text NOT NULL, -- The scrypt password hash
> max_time real NOT NULL DEFAULT 0.05, -- The max_time attribute for
> scrypt encryptions. This allows the scheme to be adaptive (i.e. one can
> increase the security as hardware power increases).
> CONSTRAINT password_authentications_pkey PRIMARY KEY (id ),
> CONSTRAINT password_authentications_max_time_check CHECK (max_time >
> 0::double precision)
> )
> INHERITS (authentications)
> WITH (
> OIDS=TRUE
> );
>
And the SA, well, I need help on that... ???
Thanks in advance
Barry
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/fHKSh9-_G9gJ.
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.
