Using features from one schema in another schema

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Using features from one schema in another schema


I'm trying to resolve a problem in the CHADO database which I would like to share on this forum to be sure on the implications of my possible solution in terms of not breaking too many rules of how the database is structured.

I need to be able to use features(in this case DNA constructs) from one schema within a different schema.
This request is justified as the DNA constructs from one species(schema) can be used to test the regulation of the gene in other species(schema).
- each species has it's own schema(this is how the database was initially designed) +  public things that are in common like publications etc.

The solution I'm trying to implement works properly with the custom CMS we're using, but I want to be sure I'm not breaking the rules so the database still remains CHADO compatible.

construct relations schema
This is a small schema just to describe the relations between the features in our feature_relationship table

What I did is I copied all the features which are constructs into the public schema so they can be available to all other schemas, but the main problem then is the FK(foreign key) constraint within the feature_relationship, featureprop, feature_pub and feature_expression tables, which is then trying to reference a feature_id not present within the particular schema because it's copied to public.

So first I created a function which returns true or false and does mostly what a FK constraint would do except it would look in the particular schema plus public schema, and in that way return true or false if the record is present within schema_name or public(for constructs), this way the constructs would also be referenced being in the public schema

------------------------------------------------------------------FUNCTION CHECK FEATURES----------------------------------------------------------
CREATE FUNCTION schema_name.check_features(integer)
RETURNS boolean
AS $$
key       ALIAS FOR  $1;

    IF     EXISTS (SELECT feature_id FROM schema_name.feature WHERE feature_id=key) OR EXISTS (SELECT feature_id FROM public.feature WHERE feature_id=key) THEN
        RETURN true;
        RETURN false;
    END IF;
LANGUAGE plpgsql;

After that I would remove the foreign keys referencing schema.feature_id from the tables linked to feature within the particular schema, and add a CHECK constraint calling the previously defined function check_features

ALTER TABLE schema_name.feature_relationship
DROP CONSTRAINT feature_relationship_subject_id_fkey
DROP CONSTRAINT feature_relationship_object_id_fkey
ADD CONSTRAINT feature_relationship_subject_id_check CHECK (schema_name.check_features(subject_id))
ADD CONSTRAINT feature_relationship_object_id_check CHECK (schema_name.check_features(object_id))

ALTER TABLE schema_name.featureprop
DROP CONSTRAINT featureprop_feature_id_fkey
ADD CONSTRAINT featureprop_feature_id_fkey_check CHECK (schema_name.check_features(feature_id))

ALTER TABLE schema_name.feature_pub
DROP CONSTRAINT feature_pub_feature_id_fkey
ADD CONSTRAINT feature_pub_feature_id_fkey_check CHECK (schema_name.check_features(feature_id))

ALTER TABLE schema_name.feature_expression
DROP CONSTRAINT feature_expression_feature_id_fkey
ADD CONSTRAINT feature_expression_feature_id_fkey_check CHECK (schema_name.check_features(feature_id))

In this way all the records would stay connected and the links between tables more or less the same, there is no need to add extra tables or views, which makes it an easy solution in terms of not touching the "model" layer above in the CMS.

There are of course alternative solutions , for example making an extra table in the public schema containing all feature_ids(with triggers on INPUT UPDATE DELETE) from all species and then pointing the foreign keys to that table, but as the feature table is one of the most populated in the whole CHADO schema that would make a bit of extra load.

Another alternative would be making a whole layer of views and then querying the views instead of the underneath tables which would be ok at the database level but would require significant changes in the layers above.

I'd be thankful on your thoughts and suggestions on this problem and solution(s).

If some of my descriptions are not clear feel free to ask.

all the best,

Go from Idea to Many App Stores Faster with Intel(R) XDK
Give your users amazing mobile app experiences with Intel(R) XDK.
Use one codebase in this all-in-one HTML5 development environment.
Design, debug & build mobile apps & 2D/3D high-impact games for multiple OSs.
Gmod-schema mailing list
[hidden email]