Menu
▾
▴
[Gmod-schema-cmts] schema/XMLTools/XORT/Config ddl.properties,1.3,1.4 function_view.sql,1.3,1.4
|
From: <pi...@us...> - 2003-05-28 17:49:38
|
Update of /cvsroot/gmod/schema/XMLTools/XORT/Config In directory sc8-pr-cvs1:/tmp/cvs-serv21656/XORT/Config Modified Files: ddl.properties function_view.sql Log Message: Index: ddl.properties =================================================================== RCS file: /cvsroot/gmod/schema/XMLTools/XORT/Config/ddl.properties,v retrieving revision 1.3 retrieving revision 1.4 diff -C2 -d -r1.3 -r1.4 *** ddl.properties 16 May 2003 15:41:04 -0000 1.3 --- ddl.properties 28 May 2003 17:28:40 -0000 1.4 *************** *** 2,6 **** dbxref=accession version dbxrefdescription dbname dbxref_id - dbxref_primary_key=dbxref_id dbxref_data_type=accession:varchar;version:varchar;dbxrefdescription:text;dbname:varchar;dbxref_id:serial --- 2,5 ---- *************** *** 11,15 **** tableinfo=tableinfo_id database_id name is_updateable modification_date is_view superclass_table_id table_type is_versioned view_on_table_id - tableinfo_primary_key=30 tableinfo_data_type=tableinfo_id:serial;database_id:int;name:varchar;is_updateable:int;modification_date:date;is_view:int;superclass_table_id:int;table_type:varchar;is_versioned:int;view_on_table_id:int --- 10,13 ---- *************** *** 19,23 **** projectinfo=name description projectinfo_id - projectinfo_primary_key=projectinfo_id projectinfo_data_type=name:varchar;description:varchar;projectinfo_id:serial --- 17,20 ---- *************** *** 27,31 **** cv=cvdefinition cvname cv_id - cv_primary_key=cv_id cv_data_type=cvdefinition:text;cvname:varchar;cv_id:serial --- 24,27 ---- *************** *** 35,39 **** cvterm=termdefinition name cv_id cvterm_id dbxref_id - cvterm_primary_key=cvterm_id cvterm_data_type=termdefinition:text;name:varchar;cv_id:int;cvterm_id:serial;dbxref_id:int --- 31,34 ---- *************** *** 43,47 **** cvrelationship=subjterm_id reltype_id objterm_id cvrelationship_id - cvrelationship_primary_key=cvrelationship_id cvrelationship_data_type=subjterm_id:int;reltype_id:int;objterm_id:int;cvrelationship_id:serial --- 38,41 ---- *************** *** 51,55 **** cvpath=pathdistance subjterm_id reltype_id cvpath_id cv_id objterm_id - cvpath_primary_key=cvpath_id cvpath_data_type=pathdistance:int;subjterm_id:int;reltype_id:int;cvpath_id:serial;cv_id:int;objterm_id:int --- 45,48 ---- *************** *** 59,64 **** cvtermsynonym=termsynonym cvterm_id ! ! cvtermsynonym_primary_key=cvpath_id cvtermsynonym_data_type=termsynonym:varchar;cvterm_id:int cvtermsynonym_unique=cvterm_id termsynonym --- 52,56 ---- cvtermsynonym=termsynonym cvterm_id ! cvtermsynonym_primary_key= cvtermsynonym_data_type=termsynonym:varchar;cvterm_id:int cvtermsynonym_unique=cvterm_id termsynonym *************** *** 67,71 **** cvterm_dbxref=cvterm_id dbxref_id cvterm_dbxref_id - cvterm_dbxref_primary_key=cvterm_dbxref_id cvterm_dbxref_data_type=cvterm_id:int;dbxref_id:int;cvterm_dbxref_id:serial --- 59,62 ---- *************** *** 75,79 **** organism=common_name taxgroup genus abbrev comment species organism_id - organism_primary_key=organism_id organism_data_type=common_name:varchar;taxgroup:varchar;genus:varchar;abbrev:varchar;comment:text;species:varchar;organism_id:serial --- 66,69 ---- *************** *** 83,87 **** organism_dbxref=organism_id organism_dbxref_id dbxref_id - organism_dbxref_primary_key=organism_dbxref_id organism_dbxref_data_type=organism_id:int;organism_dbxref_id:serial;dbxref_id:int --- 73,76 ---- *************** *** 91,95 **** pub=pages pyear title pub_id is_obsolete volume issue volumetitle miniref series_name pubplace publisher type_id - pub_primary_key=pub_id pub_data_type=pages:varchar;pyear:varchar;title:text;pub_id:serial;is_obsolete:boolean;volume:varchar;issue:varchar;volumetitle:text;miniref:varchar;series_name:varchar;pubplace:varchar;publisher:varchar;type_id:int --- 80,83 ---- *************** *** 100,104 **** pub_relationship=pub_relationship_id obj_pub_id type_id subj_pub_id - pub_relationship_primary_key=pub_relationship_id pub_relationship_data_type=pub_relationship_id:serial;obj_pub_id:int;type_id:int;subj_pub_id:int --- 88,91 ---- *************** *** 108,112 **** pub_dbxref=pub_id pub_dbxref_id dbxref_id - pub_dbxref_primary_key=pub_dbxref_id pub_dbxref_data_type=pub_id:int;pub_dbxref_id:serial;dbxref_id:int --- 95,98 ---- *************** *** 116,120 **** author=surname suffix givennames author_id - author_primary_key=author_id author_data_type=surname:varchar;suffix:varchar;givennames:varchar;author_id:serial --- 102,105 ---- *************** *** 124,128 **** pub_author=pub_id arank pub_author_id editor author_id - pub_author_primary_key=pub_author_id pub_author_data_type=pub_id:int;arank:int;pub_author_id:serial;editor:boolean;author_id:int --- 109,112 ---- *************** *** 133,138 **** pubprop=pval pub_id prank pkey_id ! ! pubprop_primary_key=pub_author_id pubprop_data_type=pval:text;pub_id:int;prank:integer;pkey_id:int pubprop_unique=pub_id pkey_id pval --- 117,121 ---- pubprop=pval pub_id prank pkey_id ! pubprop_primary_key= pubprop_data_type=pval:text;pub_id:int;prank:integer;pkey_id:int pubprop_unique=pub_id pkey_id pval *************** *** 141,145 **** feature=timeaccessioned name timelastmodified residues dbxref_id feature_id uniquename seqlen md5checksum organism_id type_id is_analysis - feature_primary_key=feature_id feature_data_type=timeaccessioned:timestamp;name:varchar;timelastmodified:timestamp;residues:text;dbxref_id:int;feature_id:serial;uniquename:text;seqlen:int;md5checksum:char;organism_id:int;type_id:int;is_analysis:boolean --- 124,127 ---- *************** *** 152,156 **** featureloc=srcfeature_id phase is_nbeg_partial nend featureloc_id feature_id locgroup rank residue_info nbeg is_nend_partial strand - featureloc_primary_key=featureloc_id featureloc_data_type=srcfeature_id:int;phase:int;is_nbeg_partial:boolean;nend:int;featureloc_id:serial;feature_id:int;locgroup:int;rank:int;residue_info:text;nbeg:int;is_nend_partial:boolean;strand:smallint --- 134,137 ---- *************** *** 164,168 **** feature_pub=feature_id pub_id feature_pub_id - feature_pub_primary_key=feature_pub_id feature_pub_data_type=feature_id:int;pub_id:int;feature_pub_id:serial --- 145,148 ---- *************** *** 172,176 **** featureprop=feature_id featureprop_id pval prank pkey_id - featureprop_primary_key=featureprop_id featureprop_data_type=feature_id:int;featureprop_id:serial;pval:text;prank:int;pkey_id:int --- 152,155 ---- *************** *** 182,186 **** featureprop_pub=featureprop_id pub_id featureprop_pub_id - featureprop_pub_primary_key=featureprop_pub_id featureprop_pub_data_type=featureprop_id:int;pub_id:int;featureprop_pub_id:serial --- 161,164 ---- *************** *** 190,194 **** feature_dbxref=feature_id feature_dbxref_id is_current dbxref_id - feature_dbxref_primary_key=feature_dbxref_id feature_dbxref_data_type=feature_id:int;feature_dbxref_id:serial;is_current:boolean;dbxref_id:int --- 168,171 ---- *************** *** 199,203 **** feature_relationship=feature_relationship_id objfeature_id subjfeature_id type_id relrank - feature_relationship_primary_key=feature_relationship_id feature_relationship_data_type=feature_relationship_id:serial;objfeature_id:int;subjfeature_id:int;type_id:int;relrank:int --- 176,179 ---- *************** *** 207,211 **** feature_cvterm=feature_id feature_cvterm_id pub_id cvterm_id - feature_cvterm_primary_key=feature_cvterm_id feature_cvterm_data_type=feature_id:int;feature_cvterm_id:serial;pub_id:int;cvterm_id:int --- 183,186 ---- *************** *** 215,219 **** synonym=name synonym_id type_id synonym_sgml - synonym_primary_key=synonym_id synonym_data_type=name:varchar;synonym_id:serial;type_id:int;synonym_sgml:varchar --- 190,193 ---- *************** *** 223,227 **** feature_synonym=is_internal feature_id synonym_id pub_id feature_synonym_id is_current - feature_synonym_primary_key=feature_synonym_id feature_synonym_data_type=is_internal:boolean;feature_id:int;synonym_id:int;pub_id:int;feature_synonym_id:serial;is_current:boolean --- 197,200 ---- *************** *** 232,236 **** synonym_pub=synonym_id pub_id synonym_pub_id - synonym_pub_primary_key=synonym_pub_id synonym_pub_data_type=synonym_id:int;pub_id:int;synonym_pub_id:serial --- 205,208 ---- *************** *** 240,244 **** genotype=description genotype_id - genotype_primary_key=genotype_id genotype_data_type=description:varchar;genotype_id:serial --- 212,215 ---- *************** *** 248,252 **** feature_genotype=feature_id feature_genotype_id genotype_id - feature_genotype_primary_key=feature_genotype_id feature_genotype_data_type=feature_id:int;feature_genotype_id:serial;genotype_id:int --- 219,222 ---- *************** *** 256,260 **** phenotype=phenotype_id pub_id description background_genotype_id statement_type - phenotype_primary_key=phenotype_id phenotype_data_type=phenotype_id:serial;pub_id:int;description:text;background_genotype_id:int;statement_type:int --- 226,229 ---- *************** *** 264,268 **** feature_phenotype=phenotype_id feature_id feature_phenotype_id - feature_phenotype_primary_key=feature_phenotype_id feature_phenotype_data_type=phenotype_id:int;feature_id:int;feature_phenotype_id:serial --- 233,236 ---- *************** *** 272,276 **** phenotype_cvterm=phenotype_id phenotype_cvterm_id prank cvterm_id - phenotype_cvterm_primary_key=phenotype_cvterm_id phenotype_cvterm_data_type=phenotype_id:int;phenotype_cvterm_id:serial;prank:int;cvterm_id:int --- 240,243 ---- *************** *** 280,284 **** interaction=phenotype_id pub_id description interaction_id background_genotype_id - interaction_primary_key=interaction_id interaction_data_type=phenotype_id:int;pub_id:int;description:text;interaction_id:serial;background_genotype_id:int --- 247,250 ---- *************** *** 288,292 **** interaction_subj=feature_id interaction_id interaction_subj_id - interaction_subj_primary_key=interaction_subj_id interaction_subj_data_type=feature_id:int;interaction_id:int;interaction_subj_id:serial --- 254,257 ---- *************** *** 296,300 **** interaction_obj=feature_id interaction_id interaction_obj_id - interaction_obj_primary_key=interaction_obj_id interaction_obj_data_type=feature_id:int;interaction_id:int;interaction_obj_id:serial --- 261,264 ---- *************** *** 304,308 **** analysis=program analysis_id sourceuri name sourcename description timeexecuted queryfeature_id programversion sourceversion algorithm - analysis_primary_key=analysis_id analysis_data_type=program:varchar;analysis_id:serial;sourceuri:text;name:varchar;sourcename:varchar;description:text;timeexecuted:timestamp;queryfeature_id:int;programversion:varchar;sourceversion:varchar;algorithm:varchar --- 268,271 ---- *************** *** 313,317 **** analysisprop=analysis_id analysisprop_id pval pkey_id - analysisprop_primary_key=analysisprop_id analysisprop_data_type=analysis_id:int;analysisprop_id:serial;pval:text;pkey_id:int --- 276,279 ---- *************** *** 321,325 **** analysisfeature=normscore analysis_id feature_id identity significance analysisfeature_id rawscore - analysisfeature_primary_key=analysisfeature_id analysisfeature_data_type=normscore:double;analysis_id:int;feature_id:int;identity:double;significance:double;analysisfeature_id:serial;rawscore:double --- 283,286 ---- *************** *** 329,333 **** expression=description expression_id - expression_primary_key=expression_id expression_data_type=description:text;expression_id:serial --- 290,293 ---- *************** *** 337,341 **** feature_expression=feature_id expression_id feature_expression_id - feature_expression_primary_key=feature_expression_id feature_expression_data_type=feature_id:int;expression_id:int;feature_expression_id:serial --- 297,300 ---- *************** *** 345,349 **** expression_cvterm=rank expression_id expression_cvterm_id cvterm_id - expression_cvterm_primary_key=expression_cvterm_id expression_cvterm_data_type=rank:int;expression_id:int;expression_cvterm_id:serial;cvterm_id:int --- 304,307 ---- *************** *** 353,357 **** expression_pub=expression_pub_id pub_id expression_id - expression_pub_primary_key=expression_pub_id expression_pub_data_type=expression_pub_id:serial;pub_id:int;expression_id:int --- 311,314 ---- *************** *** 361,365 **** eimage=eimage_type eimage_data image_uri eimage_id - eimage_primary_key=eimage_id eimage_data_type=eimage_type:varchar;eimage_data:text;image_uri:varchar;eimage_id:serial --- 318,321 ---- *************** *** 369,373 **** expression_image=eimage_id expression_id expression_image_id - expression_image_primary_key=expression_image_id expression_image_data_type=eimage_id:int;expression_id:int;expression_image_id:serial --- 325,328 ---- *************** *** 377,381 **** featuremap=mapdesc mapunit featuremap_id mapname - featuremap_primary_key=featuremap_id featuremap_data_type=mapdesc:varchar;mapunit:varchar;featuremap_id:serial;mapname:varchar --- 332,335 ---- *************** *** 385,389 **** featurerange=feature_id leftstartf_id leftendf_id featurerange_id rangestr rightstartf_id featuremap_id rightendf_id - featurerange_primary_key=featurerange_id featurerange_data_type=feature_id:int;leftstartf_id:int;leftendf_id:int;featurerange_id:serial;rangestr:varchar;rightstartf_id:int;featuremap_id:int;rightendf_id:int --- 339,342 ---- *************** *** 393,397 **** featurepos=mappos feature_id featurepos_id map_feature_id featuremap_id - featurepos_primary_key=featurepos_id featurepos_data_type=mappos:float;feature_id:int;featurepos_id:serial;map_feature_id:int;featuremap_id:serial --- 346,349 ---- *************** *** 401,405 **** featuremap_pub=pub_id featuremap_pub_id featuremap_id - featuremap_pub_primary_key=featuremap_pub_id featuremap_pub_data_type=pub_id:int;featuremap_pub_id:serial;featuremap_id:int --- 353,356 ---- *************** *** 409,413 **** feature_evidence=feature_id feature_evidence_id evidence_id - feature_evidence_primary_key=feature_evidence_id feature_evidence_data_type=feature_id:int;feature_evidence_id:varchar;evidence_id:int --- 360,363 ---- *************** *** 417,421 **** _appdata=_appdata_id - _appdata_primary_key=_appdata_id _appdata_data_type=_appdata_id:not --- 367,370 ---- *************** *** 423,426 **** --- 372,376 ---- _appdata_non_null_cols= _appdata_non_null_default= + all_table=dbxref tableinfo projectinfo cv cvterm cvrelationship cvpath cvtermsynonym cvterm_dbxref organism organism_dbxref pub pub_relationship pub_dbxref author pub_author pubprop feature featureloc feature_pub featureprop featureprop_pub feature_dbxref feature_relationship feature_cvterm synonym feature_synonym synonym_pub genotype feature_genotype phenotype feature_phenotype phenotype_cvterm interaction interaction_subj interaction_obj analysis analysisprop analysisfeature expression feature_expression expression_cvterm expression_pub eimage expression_image featuremap featurerange featurepos featuremap_pub feature_evidence _appdata *************** *** 641,643 **** synonym_pub:synonym_id_ref_table=synonym ! table_pseudo=feature_evidence _appdata \ No newline at end of file --- 591,593 ---- synonym_pub:synonym_id_ref_table=synonym ! table_pseudo= \ No newline at end of file Index: function_view.sql =================================================================== RCS file: /cvsroot/gmod/schema/XMLTools/XORT/Config/function_view.sql,v retrieving revision 1.3 retrieving revision 1.4 diff -C2 -d -r1.3 -r1.4 *** function_view.sql 16 May 2003 15:41:04 -0000 1.3 --- function_view.sql 28 May 2003 17:28:40 -0000 1.4 *************** *** 26,27 **** --- 26,99 ---- (srcfeature_id,min,max); + + + + + create or replace function fn_feature_del() RETURNS TRIGGER AS ' + DECLARE + f_type cvterm.name%TYPE; + f_id_gene feature.feature_id%TYPE; + f_id_transcript feature.feature_id%TYPE; + f_id_exon feature.feature_id%TYPE; + f_id_exon_temp feature.feature_id%TYPE; + f_id_protein feature.feature_id%TYPE; + f_id_allele feature.feature_id%TYPE; + fr_objfeature_id feature.feature_id%TYPE; + f_type_gene CONSTANT varchar :=''gene''; + f_type_exon CONSTANT varchar :=''exon''; + f_type_transcript CONSTANT varchar :=''mRNA''; + f_type_protein CONSTANT varchar :=''protein''; + f_type_allele CONSTANT varchar :=''allele''; + f_return feature.feature_id%TYPE; + f_row feature%ROWTYPE; + fr_row_transcript feature_relationship%ROWTYPE; + fr_row_exon feature_relationship%ROWTYPE; + fr_row_protein feature_relationship%ROWTYPE; + BEGIN + f_return:=OLD.feature_id; + SELECT INTO f_type c.name from feature f, cvterm c where f.feature_id=OLD.feature_id and f.type_id=c.cvterm_id; + IF f_type=f_type_gene THEN + SELECT INTO f_id_allele f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.subjfeature_id and fr.objfeature_id=OLD.feature_id and f.type_id=c.cvterm_id and c.name=f_type_allele; + IF NOT FOUND THEN + FOR fr_row_transcript IN SELECt * from feature_relationship fr where fr.objfeature_id=OLD.feature_id LOOP + SELECT INTO f_id_transcript f.feature_id from feature f, cvterm c where f.feature_id=fr_row_transcript.subjfeature_id and f.type_id=c.cvterm_id and c.name=f_type_transcript; + SELECT INTO f_id_gene f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.objfeature_id and fr.subjfeature_id=f_id_transcript and f.type_id=c.cvterm_id and c.name=f_type_gene and f.feature_id !=OLD.feature_id; + IF f_id_gene IS NULL and f_id_transcript IS NOT NULL THEN + RAISE NOTICE ''delete lonely transcript:%'', f_id_transcript; + delete from feature where feature_id=f_id_transcript; + ELSIF f_id_gene IS NOT NULL AND F_id_transcript IS NOT NULL THEN + RAISE NOTICE ''There is another transcript:% associated with this gene:%, so this transcript will be kept'', f_id_transcript,f_id_gene; + END IF; + END LOOP; + ELSE + RAISE NOTICE ''there is other allele associated with this gene:%'', f_id_allele; + return NULL; + END IF; + ELSIF f_type=f_type_transcript THEN + FOR fr_row_exon IN SELECT * from feature_relationship fr where fr.objfeature_id=OLD.feature_id LOOP + select INTO f_id_exon f.feature_id from feature f, cvterm c where f.feature_id=fr_row_exon.subjfeature_id and f.type_id=c.cvterm_id and c.name=f_type_exon; + SELECT INTO f_id_transcript f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.objfeature_id and fr.subjfeature_id=f_id_exon and f.type_id=c.cvterm_id and c.name=f_type_transcript and f.feature_id!=OLD.feature_id; + IF f_id_transcript IS NULL and f_id_exon IS NOT NULL THEN + RAISE NOTICE ''delete lonely exon:%'', f_id_exon; + delete from feature where feature_id=f_id_exon; + ELSIF f_id_transcript IS NOT NULL and f_id_exon IS NOT NULL THEN + RAISE NOTICE ''There is another transcript:% associated with this exon:%, so this exon will be kept'', f_id_transcript, f_id_exon; + END IF; + END LOOP; + + FOR fr_row_protein IN SELECT * from feature_relationship fr where fr.objfeature_id=OLD.feature_id LOOP + SELECT INTO f_id_protein f.feature_id from feature f, cvterm c where f.feature_id=fr_row_protein.subjfeature_id and f.type_id=c.cvterm_id and c.name=f_type_protein; + SELECT INTO f_id_transcript f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.objfeature_id and fr.subjfeature_id=f_id_protein and f.type_id=c.cvterm_id and c.name=f_type_exon and f.feature_id !=OLD.feature_id; + IF f_id_transcript IS NULL and f_id_protein IS NOT NULL THEN + RAISE NOTICE ''delete lonely protein:%'', f_id_protein; + delete from feature where feature_id=f_id_protein; + ELSIF f_id_transcript IS NOT NULL and f_id_protein IS NOT NULL THEN + RAISE NOTICE ''There is another transcript:% associated with this protein:%, so this exon will be kept'', f_id_transcript, f_id_protein; + END IF; + END LOOP; + END IF; + RETURN OLD; + END; + 'LANGUAGE 'plpgsql'; + + CREATE TRIGGER tr_feature_del BEFORE DELETE ON feature for EACH ROW EXECUTE PROCEDURE fn_feature_del(); \ No newline at end of file |