[Gmod-schema] Using features from one schema in another schema

[Matija B. <mat...@cr...>]

Hello,

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.

THE PROBLEM:
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:
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 $$
DECLARE
key       ALIAS FOR  $1;

BEGIN
     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;
     ELSE
         RETURN false;
     END IF;
END;
$$
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

------------------------------------------------------------------------------------------------------
1. FEATURE RELATIONSHIP
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))

2. FEATUREPROP
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))

3. FEATURE_PUB
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))

4. FEATURE_EXPRESSION
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,
Matija