[Obo-svn-commit] SF.net SVN: obo:[2203] OBDAPI/trunk/sql

[<cmu...@us...>]

Revision: 2203
          http://obo.svn.sourceforge.net/obo/?rev=2203&view=rev
Author:   cmungall
Date:     2008-11-05 00:08:09 +0000 (Wed, 05 Nov 2008)

Log Message:
-----------


Modified Paths:
--------------
    OBDAPI/trunk/sql/api/obd-mutable-api.plpgsql
    OBDAPI/trunk/sql/obd-core-functions.plpgsql
    OBDAPI/trunk/sql/util/realize-relations-as-views.plpgsql

Added Paths:
-----------
    OBDAPI/trunk/sql/obd-birn-views.sql

Modified: OBDAPI/trunk/sql/api/obd-mutable-api.plpgsql
===================================================================
--- OBDAPI/trunk/sql/api/obd-mutable-api.plpgsql	2008-11-04 15:57:07 UTC (rev 2202)
+++ OBDAPI/trunk/sql/api/obd-mutable-api.plpgsql	2008-11-05 00:08:09 UTC (rev 2203)
@@ -583,4 +583,41 @@
   RETURN 1;
  END;
 $$
-LANGUAGE 'plpgsql';
\ No newline at end of file
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION reify_link(INT,INT) RETURNS VOID AS
+$$
+  UPDATE link SET reiflink_node_id=$2 WHERE link_id=$1;
+$$ LANGUAGE 'sql';
+
+CREATE OR REPLACE FUNCTION reify_links_by_predicate(INT,INT) RETURNS VOID AS
+$$
+  UPDATE link SET reiflink_node_id=$2 WHERE predicate_id=$1 AND is_inferred='f';
+$$ LANGUAGE 'sql';
+
+CREATE OR REPLACE FUNCTION reify_links_by_predicate(VARCHAR,VARCHAR) RETURNS VOID AS
+$$
+  UPDATE link SET reiflink_node_id=get_node_id($2) WHERE predicate_id=get_node_id($1) AND is_inferred='f';
+$$ LANGUAGE 'sql';
+
+CREATE OR REPLACE FUNCTION store_genus_differentium(INT,INT,INT) RETURNS INT AS
+$$
+ DECLARE
+  v_genus_id  ALIAS FOR $1;
+  v_relation_id  ALIAS FOR $2;
+  v_filler_id  ALIAS FOR $3;
+  v_xp_uid VARCHAR;
+  v_is_a_id INT;
+  v_node_id INT;
+ BEGIN
+  SELECT INTO v_xp_uid node_uid(v_genus_id) || '^' || node_uid(v_relation_id) || '(' || node_uid(v_filler_id) || ')';
+  SELECT INTO v_node_id node_id FROM node WHERE uid=v_xp_uid;
+  IF NOT FOUND THEN
+    SELECT INTO v_node_id store_node(v_xp_uid);
+    SELECT INTO v_is_a_id node_id FROM is_a_relation;
+    INSERT INTO link (node_id,predicate_id,object_id,combinator) VALUES (v_node_id,v_is_a_id,v_genus_id,'I');
+    INSERT INTO link (node_id,predicate_id,object_id,combinator) VALUES (v_node_id,v_relation_id,v_filler_id,'I');
+  END IF;
+  RETURN v_node_id;
+ END
+$$ LANGUAGE 'plpgsql';
\ No newline at end of file

Added: OBDAPI/trunk/sql/obd-birn-views.sql
===================================================================
--- OBDAPI/trunk/sql/obd-birn-views.sql	                        (rev 0)
+++ OBDAPI/trunk/sql/obd-birn-views.sql	2008-11-05 00:08:09 UTC (rev 2203)
@@ -0,0 +1,104 @@
+-- execute this AFTER data population
+-- SELECT realize_all_relations();
+
+
+
+CREATE OR REPLACE VIEW phenotype_of AS
+ SELECT
+  ph.uid,
+  ph.label,
+  ph.source_id AS node_source_id,
+  ph.metatype,
+  inh.*
+ FROM
+  BIRN_PDPO.phenotype AS ph
+  INNER JOIN OBO_REL.inheres_in AS inh USING(node_id);
+
+
+CREATE OR REPLACE VIEW inheres_in_population_with AS
+ SELECT
+  inh.node_id,
+  po.object_id,
+  popn.node_id AS population_id
+ FROM 
+  OBO_REL.inheres_in AS inh
+  INNER JOIN sao.sao2254405550 AS popn ON (inh.object_id=popn.node_id)
+  INNER JOIN OBO_REL.part_of AS po ON (inh.object_id=po.node_id);
+
+CREATE OR REPLACE VIEW inheres_in_with_types AS
+ SELECT
+  ni.object_id AS node_type_id,
+  oi.object_id AS object_type_id,
+  inh.*
+ FROM
+  OBO_REL.inheres_in AS inh
+  INNER JOIN asserted_OBO_REL.instance_of  AS ni ON (inh.node_id=ni.node_id)
+  INNER JOIN asserted_OBO_REL.instance_of  AS oi ON (inh.object_id=oi.node_id);
+
+CREATE OR REPLACE VIEW inheres_in_with_types_c AS
+ SELECT
+  ni.object_id AS node_type_id,
+  oi.object_id AS object_type_id,
+  count(DISTINCT inh.node_id) AS num_instances
+ FROM
+  OBO_REL.inheres_in AS inh
+  INNER JOIN asserted_OBO_REL.instance_of  AS ni ON (inh.node_id=ni.node_id)
+  INNER JOIN asserted_OBO_REL.instance_of  AS oi ON (inh.object_id=oi.node_id)
+ GROUP BY
+  ni.object_id,
+  oi.object_id;
+
+CREATE OR REPLACE VIEW has_part_with_types_c AS
+ SELECT
+  ni.object_id AS node_type_id,
+  oi.object_id AS object_type_id,
+  count(DISTINCT inh.node_id) AS num_instances
+ FROM
+  OBO_REL.has_part AS inh
+  INNER JOIN asserted_OBO_REL.instance_of  AS ni ON (inh.node_id=ni.node_id)
+  INNER JOIN asserted_OBO_REL.instance_of  AS oi ON (inh.object_id=oi.node_id)
+ GROUP BY
+  ni.object_id,
+  oi.object_id;
+
+-- note that in the above asserted instance_of is used: for a wider variety
+-- consider inferred
+CREATE OR REPLACE FUNCTION create_birn_xps() RETURNS SETOF RECORD AS
+$$
+ SELECT store_genus_differentium(node_type_id,get_node_id('OBO_REL:inheres_in'),object_type_id) FROM inheres_in_with_types_c
+ UNION
+ SELECT store_genus_differentium(node_type_id,get_node_id('OBO_REL:has_part'),object_type_id) FROM has_part_with_types_c
+$$ LANGUAGE 'sql';
+
+-- select reify_links_by_predicate('BIRN_PDPO:bears','BIRN:generic_annotation');
+
+-- local closed-world axioms
+CREATE SCHEMA cwaxiom;
+
+
+CREATE OR REPLACE VIEW phenotype_born_by AS
+ SELECT
+  ph.uid,
+  ph.label,
+  ph.source_id AS node_source_id,
+  ph.metatype,
+  ibb.*
+ FROM
+  BIRN_PDPO.phenotype AS ph INNER JOIN BIRN_PDPO.is_born_by AS ibb ON (ph.node_id=ibb.node_id);
+
+
+CREATE OR REPLACE VIEW cwaxiom.unsat_all_phenotype_born_by_something AS
+ SELECT
+  ph.*
+ FROM
+  BIRN_PDPO.phenotype AS ph
+ WHERE NOT EXISTS (SELECT * FROM BIRN_PDPO.is_born_by AS ibb WHERE ibb.node_id=ph.node_id);
+
+SELECT realize_class('birnlex_ubo:birnlex_2');
+
+CREATE OR REPLACE VIEW cwaxiom.unsat_all_phenotype_born_by_some_organism AS
+ SELECT
+  ph.*
+ FROM
+  BIRN_PDPO.phenotype AS ph
+ WHERE NOT EXISTS (SELECT * FROM BIRN_PDPO.is_born_by AS ibb WHERE ibb.node_id=ph.node_id AND birnlex_ubo.birnlex_2(ibb.object_id));

Modified: OBDAPI/trunk/sql/obd-core-functions.plpgsql
===================================================================
--- OBDAPI/trunk/sql/obd-core-functions.plpgsql	2008-11-04 15:57:07 UTC (rev 2202)
+++ OBDAPI/trunk/sql/obd-core-functions.plpgsql	2008-11-05 00:08:09 UTC (rev 2203)
@@ -34,6 +34,17 @@
   node_id=$1
 $$ LANGUAGE 'sql';
 
+CREATE OR REPLACE FUNCTION get_node_id(VARCHAR) RETURNS INT
+ AS
+$$
+ SELECT
+  node_id
+ FROM
+  node
+ WHERE
+  uid=$1
+$$ LANGUAGE 'sql';
+
 CREATE OR REPLACE FUNCTION inst(INT) RETURNS RECORD
  AS
 $$
@@ -185,4 +196,5 @@
   END IF;
   RETURN v_bss;
  END
-$$ LANGUAGE 'plpgsql';
\ No newline at end of file
+$$ LANGUAGE 'plpgsql';
+

Modified: OBDAPI/trunk/sql/util/realize-relations-as-views.plpgsql
===================================================================
--- OBDAPI/trunk/sql/util/realize-relations-as-views.plpgsql	2008-11-04 15:57:07 UTC (rev 2202)
+++ OBDAPI/trunk/sql/util/realize-relations-as-views.plpgsql	2008-11-05 00:08:09 UTC (rev 2203)
@@ -107,6 +107,7 @@
    v_db                    VARCHAR;
    v_cmd                   VARCHAR;
    v_instance_of_id        INTEGER;
+   v_name                  VARCHAR;
  BEGIN
     SELECT INTO v_class uid FROM node WHERE node_id=$1;
     SELECT INTO v_instance_of_id node_id FROM node WHERE uid='OBO_REL:instance_of';
@@ -125,10 +126,14 @@
       EXECUTE 'CREATE SCHEMA asserted_' || v_db;
     END IF;
 
-    v_cmd :=  'CREATE OR REPLACE VIEW ' || v_db || '.' || quote_ident(v_local_id) || ' AS SELECT DISTINCT node.* FROM node INNER JOIN link USING (node_id) WHERE predicate_id=' || v_instance_of_id || ' AND object_id=' || v_class_id;
+    v_name := v_db || '.' || quote_ident(v_local_id);
+    v_cmd :=  'CREATE OR REPLACE VIEW ' || v_name || ' AS SELECT DISTINCT node.* FROM node INNER JOIN link USING (node_id) WHERE predicate_id=' || v_instance_of_id || ' AND object_id=' || v_class_id;
     RAISE NOTICE 'sql % ', v_cmd;
     EXECUTE v_cmd;
-    EXECUTE  'CREATE OR REPLACE VIEW asserted_' || v_db || '.' || quote_ident(v_local_id) || ' AS SELECT DISTINCT node.* FROM node INNER JOIN link USING (node_id) WHERE predicate_id=' || v_instance_of_id || ' AND object_id=' || v_class_id || ' AND is_inferred=''f''';
+    EXECUTE  'CREATE OR REPLACE VIEW asserted_' || v_name || ' AS SELECT DISTINCT node.* FROM node INNER JOIN link USING (node_id) WHERE predicate_id=' || v_instance_of_id || ' AND object_id=' || v_class_id || ' AND is_inferred=''f''';
+    v_cmd := 'CREATE OR REPLACE FUNCTION ' || v_name || '(INT) RETURNS BOOLEAN AS ''SELECT CAST(''''t'''' AS boolean) WHERE EXISTS (SELECT link.node_id FROM link WHERE predicate_id=' || v_instance_of_id || ' AND object_id=' || v_class_id || ' AND node_id=$1)'' LANGUAGE "sql"';
+    RAISE NOTICE 'sql % ', v_cmd;
+    EXECUTE v_cmd;
     RETURN;
  END;
 $$ LANGUAGE 'plpgsql';


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.