http://qs1969.pair.com?node_id=226082


in reply to SQL Joins across Subclasses

I've always been partial to allowing the DB to do the grunt of the work, joins would be fine in my opinion.

-Nitrox

Replies are listed 'Best First'.
Re^2: SQL Joins across Subclasses
by diotalevi (Canon) on Jan 11, 2003 at 17:01 UTC

    If you are using PostgreSQL then there's an explicit syntax to enable this sort of thing. PostgreSQL will then allow you to query on the assets table and see all the "objects" that are Assets. If you want to see just the DocumentAsset objects then use document_asset table. So far this schema by default won't create inconsistant Asset objects &em; the asset_id is controlled by a single sequence so if that's enough assurance you can proceed and know that asset_id is unique without explicitly constraining it.

    CREATE SEQUENCE asset_seq; CREATE TABLE assets ( asset_id integer not null default nextval('asset_seq'), desk text not null, status text not null, priority text not null, deadline date not null ); CREATE TABLE documentasset ( doc_id integer not null unique, label text not null ) INHERITS (assets); CREATE TABLE mediaasset ( media_id integer not null unique ) INHERITS (assets);

    If you really have to ensure that asset_id is unique then you have to step outside of SQL. PostgreSQL has these nice inheritance features but doesn't yet allow you to constrain them declaratively. If you add on this PL/pgSQL code then it will constrain the inheritance for you. All of thsi is obviously just a start but it should get you thinking of the possibilities.

    /* From your command prompt type createlang plpgsql <database name> It generates the following code automatically so it isn't nessessary to remember this next bit */ CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/usr/local/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

    And now to create a PL/pgSQL function and hook it into the right place. Please be aware I just wrote this into a window and didn't try to run it first or even verify that it's syntactically correct. You'll want to read up on PostgreSQL's triggers and look into either implementing this sort of function in PL/pgSQL or maybe it's TCL or Perl implementations.

    CREATE FUNCTION asset_id_ins_upd () RETURNS OPAQUE AS ' DECLARE found INTEGER; BEGIN IF TG_OP = ''UPDATE'' THEN -- Return OK for unchanging asset_id on -- update operations IF NEW.asset_id = OLD.asset_id THEN RETURN NEW; END IF; END IF; SELECT 1 INTO found FROM assets WHERE assets.asset_id = NEW.asset_id; IF FOUND THEN RAISE EXCEPTION END IF; END; ' LANGUAGE 'plpgsql' WITH (isstrict); CREATE TRIGGER assets_asset_id_ins_upd BEFORE INSERT OR UPDATE ON ass +ets FOR EACH ROW EXECUTE PROCEDURE asset_id_ins_upd();

    Fun Fun Fun in the Fluffy Chair

      INHERITS, yes! But unfortunately, I'm not using Postgres. I've got Oracle 8i, and after searching their docs for "inherit", "derive", "subclass", "under" and "child" I can't find an equivalent keyword. All in all that's not a huge loss though, as we've tried to stay DB agnostic in our SQL to as great a degree as possible.

      What are your thoughts on the principle of the matter as far as using joins here though? pg points out below that denormalization for performance is an accepted practice, but this application isn't really going to be hit that hard. Rather I'm concerned more with ease of maintenance and extensibility, working on the assumption that the child tables will expand down the road. Which, upon reflection, favors joins strongly, views a little less so IMO. Hmmm. /me dreams of INHERITS...