World-2DPAGE Home

A Make2D-DB II DATABASE: version 2.50, built 18-Jul-2006

Dumped on 2006-07-18

The Make2D-DB II Tool

Index of database structure for schema: core 


Table: analyte

analyte Structure
F-Key Name Type Description
analyteid serial PRIMARY KEY
sample.sampleid sampleid integer

A trigger inserts sampleID from parent if child has parent (analyteParentID), Should be "Not NULL", but is not for more flexibility * Applied Trigger: Trigger_Analyte_sampleIDTracker *
analytepreparation.analytepreparationid analytepreparationid integer
description text
analyte.analyteid analyteparentid integer
analyzable.analyzableid fromanalyzablesource integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

analyte Constraints
Name Constraint
analyte_check CHECK ((((fromanalyzablesource IS NULL) AND (analyteparentid IS NULL)) OR ((fromanalyzablesource IS NOT NULL) AND (analyteparentid IS NOT NULL))))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to analyte
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: analytepreparation

analytepreparation Structure
F-Key Name Type Description
analytepreparationid serial PRIMARY KEY
preparationprotocol text
uri character varying(4096)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to analytepreparation
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: analyzable

In the future: with Analyte children referencing an "Analyzable" class (super-class?) that containis all various "subAnalyzable" (e.g. Gel, LC,..) from which the children are obtained. Otherwise, use just a simple relational correspondance.

analyzable Structure
F-Key Name Type Description
analyzableid serial PRIMARY KEY
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to analyzable
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: author

author Structure
F-Key Name Type Description
author character varying(1024) UNIQUE#1 NOT NULL
reference.referenceid article integer UNIQUE#1 NOT NULL
authorpriority smallint NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to author
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: authorgroup

authorgroup Structure
F-Key Name Type Description
author character varying(1024) UNIQUE#1 NOT NULL
reference.referenceid article integer UNIQUE#1 NOT NULL

Article reference could be limited only to e.g. ReferenceLocationSubmission and ReferenceLocationJournal
authorpriority smallint NOT NULL DEFAULT 1
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to authorgroup
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: authorparent

authorparent Structure
F-Key Name Type Description
author character varying(1024) NOT NULL
reference.referenceid article integer NOT NULL
authorpriority smallint NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to authorparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: biologicalprocess

biologicalprocess Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
goid character varying(32) UNIQUE#1 NOT NULL
goname character varying(256)
gosynoymsnames character varying(1024)
godefinition character varying(1024)
lastupdate timestamp without time zone
mappedenzymeclassification character varying(16)
mappedtransportclassification character varying(16)
goparents character varying(32)[]
gochildren character varying(32)[]
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

biologicalprocess Constraints
Name Constraint
geneontologyparent_mappedenzymeclassification_check CHECK (((mappedenzymeclassification)::text ~ '^[0-9](.([0-9]+|-)){3}$'::text))

 

Permissions which apply to biologicalprocess
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: biosource

biosource Structure
F-Key Name Type Description
biosourceid serial PRIMARY KEY
studygroup.studygroupid studygroupid integer
biosourceinformation.biosourceinformationid biosourceinformationid integer NOT NULL
biosourcepreparation.biosourcepreparationid biosourcepreparationid integer NOT NULL
description text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to biosource
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: biosourceinformation

Biological source can be defined at various levels (for more flexibility).

biosourceinformation Structure
F-Key Name Type Description
biosourceinformationid serial PRIMARY KEY
organism.organismid organismid integer NOT NULL
organismstrain character varying(256)
straindetailsdescription text
individualscount character varying(256)
genotype character varying(16)
developmentalstage character varying(256)
anatomy character varying(256)
tissue.tissueid tissueid integer
cell character varying(256)
cellline character varying(256)
measuredproperties text
environmet character varying(256)
treatment text
phenotype text
behaviour text
pathology text
provider character varying(256)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to biosourceinformation
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: biosourcepreparation

biosourcepreparation Structure
F-Key Name Type Description
biosourcepreparationid serial PRIMARY KEY
preparationprotocol text
uri character varying(4096)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to biosourcepreparation
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: book

book Structure
F-Key Name Type Description
bookid serial PRIMARY KEY
booktitle character varying(256) UNIQUE NOT NULL
editors character varying(256) NOT NULL
publisher character varying(128) NOT NULL
city character varying(64) NOT NULL
year smallint NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

book Constraints
Name Constraint
book_year_check CHECK (("year" > 0))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to book
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: cellularcomponent

cellularcomponent Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
goid character varying(32) UNIQUE#1 NOT NULL
goname character varying(256)
gosynoymsnames character varying(1024)
godefinition character varying(1024)
lastupdate timestamp without time zone
mappedenzymeclassification character varying(16)
mappedtransportclassification character varying(16)
goparents character varying(32)[]
gochildren character varying(32)[]
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

cellularcomponent Constraints
Name Constraint
geneontologyparent_mappedenzymeclassification_check CHECK (((mappedenzymeclassification)::text ~ '^[0-9](.([0-9]+|-)){3}$'::text))

 

Permissions which apply to cellularcomponent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: citer

FOREIGN KEY (article, unpublished) REFERENCES ReferenceLocationJournal(referenceID, unpublished) -> but not defined as UNIQUE in ReferenceLocationJournal

citer Structure
F-Key Name Type Description
author character varying(1024) UNIQUE#1 NOT NULL
referencelocationjournal.referenceid article integer UNIQUE#1 NOT NULL
authorpriority smallint NOT NULL
unpublished boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

citer Constraints
Name Constraint
citer_unpublished_check CHECK ((unpublished = true))

 

Permissions which apply to citer
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: commententry2d

commententry2d Structure
F-Key Name Type Description
entry.ac ac character varying(32)
commenttopic.commenttopicid commenttopicid integer
commentfreetext text NOT NULL
geldimension smallint DEFAULT 2
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

commententry2d Constraints
Name Constraint
commententry2d_geldimension_check CHECK (((geldimension = 1) OR (geldimension = 2)))

 

Permissions which apply to commententry2d
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: commententryfreetext

commententryfreetext Structure
F-Key Name Type Description
entry.ac ac character varying(32)
commenttopic.commenttopicid commenttopicid integer
commentfreetext text NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to commententryfreetext
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: commententryparent

commententryparent Structure
F-Key Name Type Description
entry.ac ac character varying(32)
commenttopic.commenttopicid commenttopicid integer
commentfreetext text NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to commententryparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: commenttopic

commenttopic Structure
F-Key Name Type Description
commenttopicid serial PRIMARY KEY
commenttopicname character varying(64) UNIQUE NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to commenttopic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: contact

Contact person/institution is different and distinct than references. Currently applied (optional) on projects, samples, gel preparations, gel informatics and spot analysis

contact Structure
F-Key Name Type Description
contactid serial PRIMARY KEY
name character varying(256) NOT NULL
email character varying(256)
institution character varying(256)
address text
remark text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to contact
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: dynamicremotemake2ddbinterface

ID (0) is the default local interface. To do: extend to all available interfaces via ExPASy

dynamicremotemake2ddbinterface Structure
F-Key Name Type Description
interfaceid serial PRIMARY KEY
interfaceuri text UNIQUE#1 NOT NULL
dbnumber integer UNIQUE#1 DEFAULT 1
dbname character varying(128)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to dynamicremotemake2ddbinterface
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entry

The term "Entry" has been prefered more than the term "Protein". The Entry entity defines an alternative perspective (the widely used protein based view) to represent data. * Applied Trigger: Trigger_Entry_AC_upperCase * * Applied Trigger, DEPRECATED!: Trigger_Entry_synchronizeGeneralAndMapAnnotationFlags * * Applied Trigger: Trigger_Entry_annotationChanged * * Applied Trigger: Trigger_Entry_showFlagSwitchTrue *

entry Structure
F-Key Name Type Description
ac character varying(32) PRIMARY KEY
id character varying(32) UNIQUE NOT NULL
entryclass character varying(32) DEFAULT 'STANDARD'::character varying
identmethod character varying(16) DEFAULT '2DG'::character varying
description text
release.releasenum#1 releasecreation integer NOT NULL DEFAULT 1
release.subrelease#1 subreleasecreation integer NOT NULL
genenames character varying(1024)
keywords character varying(1024)
organism.organismid organismid integer

At the protein level, the annotated organism is not forcibly the same as for the analyzed sample
organismstrain character varying(256)
entrychecksum character(16)
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to entry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entrygelimage

Corresponds to the SWISS-2DPAGE "IM" (IMage) line. * Applied Trigger: Trigger_EntryGelImage_showFlagSwitchTrue *

entrygelimage Structure
F-Key Name Type Description
entry.ac ac character varying(32) PRIMARY KEY
gel.gelid gelid integer PRIMARY KEY
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entrygelimage
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entrygelmaster

Corresponds to the SWISS-2DPAGE "MT" (MasTer) line [Specific to SWISS-2DPAGE]. * Applied Trigger: Trigger_EntryGelMaster_showFlagSwitchTrue *

entrygelmaster Structure
F-Key Name Type Description
entry.ac ac character varying(32) PRIMARY KEY
gel.gelid gelid integer PRIMARY KEY
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entrygelmaster
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entrygene

This table content is not used *for the moment* by the entry views! (to do: Entry.geneNames dynamically constructed from this table)

entrygene Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
genename.geneid geneid integer UNIQUE#1 NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entrygene
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entrytheoreticalpimw

entrytheoreticalpimw Structure
F-Key Name Type Description
entry.ac ac character varying(32) NOT NULL
modifiedproteindescription text
theoreticalpi numeric(4,2) NOT NULL
theoreticalmw integer NOT NULL
algorithmoriginandversion text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entrytheoreticalpimw
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entryversion2d

entryversion2d Structure
F-Key Name Type Description
entry.ac ac character varying(32) PRIMARY KEY
version integer NOT NULL DEFAULT 1
versiondate date NOT NULL DEFAULT ('now'::text)::date
annotationchanged boolean DEFAULT false
annotationchecksum character(16)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entryversion2d
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entryversiongeneral

entryversiongeneral Structure
F-Key Name Type Description
entry.ac ac character varying(32) PRIMARY KEY
version integer NOT NULL DEFAULT 1
versiondate date NOT NULL DEFAULT ('now'::text)::date
annotationchanged boolean DEFAULT false
annotationchecksum character(16)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entryversiongeneral
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entryversionparent

* Applied Trigger: Trigger_EntryVersionParent_annotationChanged_true *

entryversionparent Structure
F-Key Name Type Description
entry.ac ac character varying(32) NOT NULL
version integer NOT NULL DEFAULT 1
versiondate date NOT NULL DEFAULT ('now'::text)::date
annotationchanged boolean DEFAULT false
annotationchecksum character(16)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entryversionparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entryxrefdb

* Applied Trigger: Trigger_EntryXrefDB_annotationChanged * * Applied Rules: Rule_EntryXrefDB_annotationChanged_ins and _del *

entryxrefdb Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
xrefdb.xrefdbcode xrefdbcode integer UNIQUE#1 NOT NULL
xrefprimaryidentifier character varying(32) UNIQUE#1 NOT NULL
xrefsecondaryidentifier character varying(1024)
xreftertiaryidentifier character varying(1024)
xrefotheridentifiers character varying(1024)
activated boolean DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entryxrefdb
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entryxrefdbdynamic

* Applied Trigger: Trigger_EntryXrefDB_no_dynamic_redundancy *

entryxrefdbdynamic Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
xrefdbdynamic.xrefdbcode xrefdbcode integer UNIQUE#1 NOT NULL
xrefprimaryidentifier character varying(32) UNIQUE#1 NOT NULL
xrefsecondaryidentifier character varying(1024)
xreftertiaryidentifier character varying(1024)
xrefotheridentifiers character varying(1024)
activated boolean DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entryxrefdbdynamic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: entryxrefdbparent

entryxrefdbparent Structure
F-Key Name Type Description
ac character varying(32)
xrefdbcode integer
xrefprimaryidentifier character varying(32)
xrefsecondaryidentifier character varying(1024)
xreftertiaryidentifier character varying(1024)
xrefotheridentifiers character varying(1024)
activated boolean DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to entryxrefdbparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: enzymenomenclature

Recommendations of the Nomenclature Committee of the International Union of Biochemistry and Molecular Biology.

enzymenomenclature Structure
F-Key Name Type Description
enzymecode character varying(16) UNIQUE#1 NOT NULL
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

enzymenomenclature Constraints
Name Constraint
enzymenomenclature_enzymecode_check CHECK (((enzymecode)::text ~ '^[0-9](.([0-9]+|-)){3}$'::text))

 

Permissions which apply to enzymenomenclature
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: externalmainxrefdata

The Main Index is typically UniProt (Swiss-Prot/TrEMBL). Sequence changes imply entry changes: CHECK (uniProtSeqUpDate >= uniProtEntryUpDate), # not yet decided for Swiss-Prot <=> EBI/TrEMBL CHECK (uniProtSeqUpVersion >= uniProtEntryUpVersion) # not yet decided for Swiss-Prot <=> EBI/TrEMBL

externalmainxrefdata Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 PRIMARY KEY
uniprotextractiondate date DEFAULT now()
uniprotac character varying(32) UNIQUE#1 NOT NULL
uniprotsecondaryac character varying(32)[]
uniprotid character varying(32)
uniprotversion numeric(5,2)
uniprotentryincorporateddate date
uniprotsequpdate date
uniprotsequpversion smallint
uniprotentryupdate date
uniprotentryupversion smallint
uniprotdescription text
uniprotenzymecode character varying(16)
uniprotgenenames character varying(1024)
uniprotorganelleplasmid character varying(256)
uniprotcategorykeywords character varying(1024)
uniprotxrefs character varying(1024)[]
sportrembl boolean DEFAULT true

Swiss-Prot or TrEMBL? true => Swiss-Prot, false => TrEMBL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

externalmainxrefdata Constraints
Name Constraint
externalmainxrefdata_check CHECK (((uniprotversion IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check1 CHECK (((uniprotsequpdate IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check10 CHECK (((uniprotxrefs IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check2 CHECK (((uniprotsequpversion IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check3 CHECK (((uniprotentryupdate IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check4 CHECK (((uniprotentryupversion IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check5 CHECK (((uniprotdescription IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check6 CHECK (((uniprotenzymecode IS NULL) OR ((uniprotac IS NOT NULL) AND ((uniprotenzymecode)::text ~ '^[0-9](.([0-9]+|-)){3}$'::text))))
externalmainxrefdata_check7 CHECK (((uniprotgenenames IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check8 CHECK (((uniprotorganelleplasmid IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_check9 CHECK (((uniprotcategorykeywords IS NULL) OR (uniprotac IS NOT NULL)))
externalmainxrefdata_uniprotac_check CHECK ((uniprotac IS NOT NULL))

 

Permissions which apply to externalmainxrefdata
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: gel

Gel description will be restructed according to the forthcoming PSI recomandations - No multiple image sets scanned from one gel and analyzed separately, or merged together for one master image, is within this structure yet! (to do: consider image sets) * Applied Trigger: Trigger_Gel_shortName_melanieID * * Applied Trigger: Trigger_Gel_showFlagSwitchTrue *

gel Structure
F-Key Name Type Description
gelid serial PRIMARY KEY
melaniegeneratedid character varying(64) UNIQUE
shortname character varying(32) UNIQUE NOT NULL
fullname character varying(256)
dimension smallint DEFAULT 2
startmw bigint
endmw bigint
startpi numeric(4,2)
endpi numeric(4,2)
organism.organismid organismid integer NOT NULL
organismstrain character varying(256)
gel.gelid gelparentid integer

Caution: this will imply an additional reference to preparation, causing a possible reference to another Analyte! (to be restructed)
gelpreparation.gelpreparationid gelpreparationid integer

Should be "Not NULL", but is not for more flexibility
gelinformatics.gelinformaticsid gelinformaticsid integer

Should be "Not NULL", but is not for more flexibility
biosourceinformation.biosourceinformationid biosourceinformationid integer

Set for flexibility (organism is redundant)
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

gel Constraints
Name Constraint
gel_check CHECK ((endmw >= startmw))
gel_check1 CHECK ((endpi >= startpi))
gel_dimension_check CHECK (((dimension = 1) OR (dimension = 2)))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to gel
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: gelcomputabledynamic

Reserved for computable map positions on remote databases

gelcomputabledynamic Structure
F-Key Name Type Description
databasename character varying(64) NOT NULL
gelshortname character varying(32) NOT NULL
gelfullname character varying(256)
organismspecies text
taxonomycode integer
gelcomputableurl text NOT NULL

URL with arguments is sent from ExPASy as a signal with params __MAP__ and __AC__
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to gelcomputabledynamic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: geldynamic

geldynamic Structure
F-Key Name Type Description
databasename character varying(64) NOT NULL
gelshortname character varying(32) NOT NULL
gelfullname character varying(256)
organismspecies text
taxonomycode integer
tissuename character varying(256)
tissuespname character varying(256)
gelurl text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to geldynamic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: gelimage

gelimage Structure
F-Key Name Type Description
gel.gelid gelid serial PRIMARY KEY
masterimage oid
imageurl text
imagepath text
imagetype character varying(3) DEFAULT 'jpg'::character varying
smallimageurl text
smallimagepath text
smallimagetype character varying(3) DEFAULT 'jpg'::character varying
xpixelsize smallint
ypixelsize smallint
xratio numeric(6,4) NOT NULL DEFAULT 1.0

xRatio and yRatio are < 1 if the displayed image has a smaller size than the original/reference image
yratio numeric(6,4) NOT NULL DEFAULT 1.0
xpixelshift smallint
ypixelshift smallint
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

gelimage Constraints
Name Constraint
gelimage_xpixelsize_check CHECK ((xpixelsize >= 0))
gelimage_ypixelsize_check CHECK ((ypixelsize >= 0))

 

Permissions which apply to gelimage
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: gelinformatics

Gel acquisition/informatics refers here to techniques not analysis, this will be restructed according to the forthcoming PSI recomandations

gelinformatics Structure
F-Key Name Type Description
gelinformaticsid serial PRIMARY KEY
uri character varying(4096)

This may include URLs to local or external gel informatics documents (e.g. "PSI::MIAPE")
informaticsdocument text
informaticsdescription text
soft text
contact.contactid contactid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to gelinformatics
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: gelpreparation

The Gel design will be highly restructed according to the forthcoming PSI recomandations!!

gelpreparation Structure
F-Key Name Type Description
gelpreparationid serial PRIMARY KEY
analyte.analyteid analyteid integer
uri character varying(4096)

This may include URLs to local or external gel preparation protocols (e.g. "PSI::MIAPE")
preparationdocument text
preparationdescription text
contact.contactid contactid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to gelpreparation
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: geltissuesp

* Applied Trigger: Trigger_GelTissueSP_uc *

geltissuesp Structure
F-Key Name Type Description
gel.gelid gelid integer UNIQUE#1
tissuesp.tissuespname tissuespname character varying(256) UNIQUE#1 NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to geltissuesp
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: genename

genename Structure
F-Key Name Type Description
geneid serial PRIMARY KEY
genename character varying(16) UNIQUE#1 NOT NULL
organism.organismid organismid integer UNIQUE#1
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to genename
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: geneontologyparent

geneontologyparent Structure
F-Key Name Type Description
entry.ac ac character varying(32) NOT NULL
goid character varying(32) NOT NULL
goname character varying(256)
gosynoymsnames character varying(1024)
godefinition character varying(1024)
lastupdate timestamp without time zone
mappedenzymeclassification character varying(16)

Synchronise with EnzymeNomenclature
mappedtransportclassification character varying(16)
goparents character varying(32)[]
gochildren character varying(32)[]
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

geneontologyparent Constraints
Name Constraint
geneontologyparent_mappedenzymeclassification_check CHECK (((mappedenzymeclassification)::text ~ '^[0-9](.([0-9]+|-)){3}$'::text))

 

Permissions which apply to geneontologyparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: geneorderedlocus

geneorderedlocus Structure
F-Key Name Type Description
genename.geneid geneid integer UNIQUE#1 NOT NULL
orderedlocusname character varying(32) UNIQUE#1 NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to geneorderedlocus
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: geneorf

geneorf Structure
F-Key Name Type Description
genename.geneid geneid integer UNIQUE#1 NOT NULL
orfname character varying(32) UNIQUE#1 NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to geneorf
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: generaltopicdefinition

generaltopicdefinition Structure
F-Key Name Type Description
generaltopicid serial PRIMARY KEY
topicname character varying(64) UNIQUE
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to generaltopicdefinition
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: generaltopicentrydata

generaltopicentrydata Structure
F-Key Name Type Description
topicdataid serial PRIMARY KEY
generaltopicdefinition.generaltopicid generaltopicid integer
topicdatatext text
allreferences integer[]
ambiguousreference boolean DEFAULT false
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

generaltopicentrydata Constraints
Name Constraint
generaltopicentrydata_check CHECK ((((ambiguousreference = false) AND (allreferences[1] IS NOT NULL)) OR ((ambiguousreference = true) AND (allreferences[1] IS NULL))))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to generaltopicentrydata
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: genesynonym

genesynonym Structure
F-Key Name Type Description
genename.geneid geneid integer UNIQUE#1 NOT NULL
synonym character varying(16) UNIQUE#1 NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to genesynonym
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: journal

journal Structure
F-Key Name Type Description
journalid serial PRIMARY KEY
journalname character varying(256) UNIQUE NOT NULL
url text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to journal
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: make2ddbtool

To do: include a Trigger to check that for the "new" option, new version > max(version)

make2ddbtool Structure
F-Key Name Type Description
version numeric(4,2) NOT NULL DEFAULT 0.0
subversion character varying(64)
versiondate date
postgresqlversion text
action text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

make2ddbtool Constraints
Name Constraint
make2ddbtool_action_check CHECK (((("action" = 'create'::text) OR ("action" = 'transform'::text)) OR ("action" = 'update'::text)))

 

Permissions which apply to make2ddbtool
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: mappingtopicdefinition

mappingtopicdefinition Structure
F-Key Name Type Description
mappingtechnique character varying(8) PRIMARY KEY
techniquedescription character varying(256)
containingregexp text NOT NULL
excludingregexp text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to mappingtopicdefinition
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: molecularfunction

molecularfunction Structure
F-Key Name Type Description
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
goid character varying(32) UNIQUE#1 NOT NULL
goname character varying(256)
gosynoymsnames character varying(1024)
godefinition character varying(1024)
lastupdate timestamp without time zone
mappedenzymeclassification character varying(16)
mappedtransportclassification character varying(16)
goparents character varying(32)[]
gochildren character varying(32)[]
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

molecularfunction Constraints
Name Constraint
geneontologyparent_mappedenzymeclassification_check CHECK (((mappedenzymeclassification)::text ~ '^[0-9](.([0-9]+|-)){3}$'::text))

 

Permissions which apply to molecularfunction
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: organism

We will define a more precise way to describe strains, etc.. -> organism. For the moment, strains can be described - additionaly - at the BioSource or the Gel level.

organism Structure
F-Key Name Type Description
organismid serial PRIMARY KEY
organismspecies text UNIQUE NOT NULL
organismclassification text NOT NULL
xrefdb.xrefdbcode taxonomyxrefdbcode integer UNIQUE#1
taxonomycode integer UNIQUE#1

For several strains: for the moment use a double NULL pair, or use some personal taxonomyXref DB.
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to organism
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: project

project Structure
F-Key Name Type Description
projectid serial PRIMARY KEY
description text
uri character varying(4096)
contact.contactid contactid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to project
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: reference

reference Structure
F-Key Name Type Description
referenceid serial PRIMARY KEY
referencework.referenceworkid referenceworkid integer
referencetitle text
referencetype.referencetypeid referencetypeid integer NOT NULL DEFAULT 1
referencechecksum character(16) UNIQUE NOT NULL

Update with views or skip? (deprecated)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to reference
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencedentry

Spots experimental References are integrated within the spots experimental Tables.

referencedentry Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY
entry.ac ac character varying(32) UNIQUE#1 PRIMARY KEY
rndisplayedvalue smallint UNIQUE#1 DEFAULT 1

This field is kept for compatibility/historical purpose
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to referencedentry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencedgel

referencedgel Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY
gel.gelid gelid integer PRIMARY KEY
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to referencedgel
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencedobjectparent

referencedobjectparent Structure
F-Key Name Type Description
reference.referenceid referenceid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to referencedobjectparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencedproject

referencedproject Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY
project.projectid projectid integer PRIMARY KEY
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to referencedproject
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencedsample

referencedsample Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY
sample.sampleid sampleid integer PRIMARY KEY
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to referencedsample
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationbook

referencelocationbook Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationbook_referenceid_seq'::regclass)
book.bookid bookid integer NOT NULL
volume smallint
pagefirst integer NOT NULL
pagelast integer NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationbook Constraints
Name Constraint
referencelocationbook_check CHECK (((pagelast >= pagefirst) AND (pagefirst > 0)))
referencelocationbook_referenceid_check CHECK ((common.make2db_rl_verify_type(referenceid, 'Book'::character varying) = true))

 

Permissions which apply to referencelocationbook
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationjournal

referencelocationjournal Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationjournal_referenceid_seq'::regclass)
journal.journalid journalid integer NOT NULL
volume smallint NOT NULL
subvolume smallint
pagefirst integer NOT NULL
pagelast integer NOT NULL
year smallint NOT NULL
crossreferences character varying(128)[]
unpublished boolean DEFAULT false
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationjournal Constraints
Name Constraint
referencelocationjournal_check CHECK ((((pagelast >= pagefirst) AND (pagefirst > 0)) OR ((pagefirst = 0) AND (pagelast = 0))))
referencelocationjournal_referenceid_check CHECK (((common.make2db_rl_verify_type(referenceid, 'Journal'::character varying) = true) OR (common.make2db_rl_verify_type(referenceid, 'Unpublished results'::character varying) = true)))
referencelocationjournal_subvolume_check CHECK ((subvolume >= 0))
referencelocationjournal_volume_check CHECK ((volume >= 0))
referencelocationjournal_year_check CHECK (("year" >= 0))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to referencelocationjournal
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationother

referencelocationother Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationother_referenceid_seq'::regclass)
text text NOT NULL
personalcomment character varying(256)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationother Constraints
Name Constraint
referencelocationother_referenceid_check CHECK ((common.make2db_rl_verify_type(referenceid, 'OTHER'::character varying) = true))

 

Permissions which apply to referencelocationother
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationparent

referencelocationparent Structure
F-Key Name Type Description
reference.referenceid referenceid serial NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to referencelocationparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationpatent

referencelocationpatent Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationpatent_referenceid_seq'::regclass)
patent_publication_number character varying(16) NOT NULL
date date NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationpatent Constraints
Name Constraint
referencelocationpatent_referenceid_check CHECK ((common.make2db_rl_verify_type(referenceid, 'Patent applications'::character varying) = true))

 

Permissions which apply to referencelocationpatent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationsubmission

referencelocationsubmission Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationsubmission_referenceid_seq'::regclass)
month smallint NOT NULL
year smallint NOT NULL
xrefdb.xrefdbcode xrefdbcode integer NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationsubmission Constraints
Name Constraint
referencelocationsubmission_month_check CHECK ((("month" > 0) AND ("month" < 13)))
referencelocationsubmission_referenceid_check CHECK ((common.make2db_rl_verify_type(referenceid, 'Submitted'::character varying) = true))
referencelocationsubmission_year_check CHECK (("year" > 0))

 

Permissions which apply to referencelocationsubmission
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationthesis

referencelocationthesis Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationthesis_referenceid_seq'::regclass)
year smallint NOT NULL
institution character varying(128) NOT NULL
country character varying(64) NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationthesis Constraints
Name Constraint
referencelocationthesis_referenceid_check CHECK ((common.make2db_rl_verify_type(referenceid, 'Thesis'::character varying) = true))
referencelocationthesis_year_check CHECK (("year" > 0))

 

Permissions which apply to referencelocationthesis
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencelocationunpubobservations

referencelocationunpubobservations Structure
F-Key Name Type Description
reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationunpubobservations_referenceid_seq'::regclass)
month smallint NOT NULL
year smallint NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

referencelocationunpubobservations Constraints
Name Constraint
referencelocationunpubobservations_month_check CHECK ((("month" > 0) AND ("month" < 13)))
referencelocationunpubobservations_referenceid_check CHECK ((common.make2db_rl_verify_type(referenceid, 'Unpublished observations'::character varying) = true))
referencelocationunpubobservations_year_check CHECK (("year" > 0))

 

Permissions which apply to referencelocationunpubobservations
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencetype

referencetype Structure
F-Key Name Type Description
referencetypeid serial PRIMARY KEY
referencetype character varying(32) UNIQUE NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to referencetype
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: referencework

referencework Structure
F-Key Name Type Description
referenceworkid serial PRIMARY KEY
referenceworkdescription character varying(64) NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to referencework
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: release

release Structure
F-Key Name Type Description
releasenum integer PRIMARY KEY
subrelease smallint PRIMARY KEY DEFAULT 1
releasedate date NOT NULL DEFAULT ('now'::text)::date
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to release
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: sample

Consider merging Sample and SamplePreparation, or rename the latter to SamplePreparationProtocol

sample Structure
F-Key Name Type Description
sampleid serial PRIMARY KEY
biosource.biosourceid biosourceid integer

Should be "Not NULL", but is not for more flexibility
uri character varying(4096)
samplepreparation.samplepreparationid samplepreparationid integer
description text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to sample
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: samplepreparation

samplepreparation Structure
F-Key Name Type Description
samplepreparationid serial PRIMARY KEY
uri character varying(4096)
samplepreparationdocument text
samplepreparationprotocol text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to samplepreparation
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: secondaryac

secondaryac Structure
F-Key Name Type Description
secondaryac character varying(32) NOT NULL
entry.ac ac character varying(32) NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to secondaryac
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spot

UNIQUE (mw, pI, gelID) and UNIQUE (xCoordinate, yCoordiante, gelID) constraints have not been added. * Applied Trigger: Trigger_Spot_insert *

spot Structure
F-Key Name Type Description
spotid character varying(16) PRIMARY KEY
gel.gelid gelid integer PRIMARY KEY
mw integer NOT NULL

By convention, set to -1 for large-scale non-identified spots
pi numeric(4,2)

By convention, set to -1.00 for large-scale non-identified spots
xcoordinate integer
ycoordinate integer
odrelative real
volumerelative real
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spot
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotdataaacid

spotdataaacid Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdataaacid_dataexpid_seq'::regclass)
humanidentifier character varying(256)
spot.spotid#1 spotid character varying(16) PRIMARY KEY
spot.gelid#1 gelid integer PRIMARY KEY
reference.referenceid referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
aminoacidlist text NOT NULL
relateddata text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotdataaacid Constraints
Name Constraint
spotdataparent_appreciation_check CHECK (((appreciation > 0) AND (appreciation < 11)))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spotdataaacid
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotdataother

spotdataother Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdataother_dataexpid_seq'::regclass)
humanidentifier character varying(256)
spot.spotid#1 spotid character varying(16) PRIMARY KEY
spot.gelid#1 gelid integer PRIMARY KEY
reference.referenceid referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
results text NOT NULL
relateddata text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotdataother Constraints
Name Constraint
spotdataparent_appreciation_check CHECK (((appreciation > 0) AND (appreciation < 11)))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spotdataother
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotdataparent

* Applied Trigger: Trigger_SpotDataParent_nextval_dataExpID * * Applied Trigger: Trigger_SpotDataParent_showFlagSwitchTrue *

spotdataparent Structure
F-Key Name Type Description
dataexpid serial NOT NULL
humanidentifier character varying(256)

This field is to hold an identifier given by data producers to identify their data experiment (different from the database generated dataExpID Primary Key, often a file name). It is not UNIQUE (e.g. for MS/MS file output has a serie of spectra), and it may be NULL, as no such identifier is systematically given by users
spot.spotid#1 spotid character varying(16)
spot.gelid#1 gelid integer
reference.referenceid referenceid integer
contact.contactid contactid integer
uri character varying(4096)

This may include URLs to local or external experiment data/description files (e.g. "PSI::MzData")
datadocument text

This may correspond in the sub-classes to, e.g., "PSI:mzData": give a files system path (no storage as large objects)
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotdataparent Constraints
Name Constraint
spotdataparent_appreciation_check CHECK (((appreciation > 0) AND (appreciation < 11)))

 

Permissions which apply to spotdataparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotdatapeptmassf

spotdatapeptmassf Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdatapeptmassf_dataexpid_seq'::regclass)
humanidentifier character varying(256)
spot.spotid#1 spotid character varying(16) PRIMARY KEY
spot.gelid#1 gelid integer PRIMARY KEY
reference.referenceid referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
peptidemasses double precision[] NOT NULL

[mass][intensity]
enzyme character varying(32) NOT NULL DEFAULT 'TRYPSIN'::character varying
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotdatapeptmassf Constraints
Name Constraint
spotdataparent_appreciation_check CHECK (((appreciation > 0) AND (appreciation < 11)))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spotdatapeptmassf
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotdatatandemms

spotdatatandemms Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdatatandemms_dataexpid_seq'::regclass)

dataExpID combined with gelID and AC -> we can trace back the corresponding parent peptide origin from the SpotDataPeptMassF table
humanidentifier character varying(256)
spot.spotid#1 spotid character varying(16) PRIMARY KEY
spot.gelid#1 gelid integer PRIMARY KEY
reference.referenceid referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
parentmass double precision
parentcharge smallint
ionmasses double precision[]
relateddata text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotdatatandemms Constraints
Name Constraint
spotdataparent_appreciation_check CHECK (((appreciation > 0) AND (appreciation < 11)))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spotdatatandemms
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotentry

* Applied Trigger: Trigger_SpotEntry_showFlagSwitchTrue *

spotentry Structure
F-Key Name Type Description
spot.spotid#1 spotid character varying(16) UNIQUE#1
spot.gelid#1 gelid integer UNIQUE#1
entry.ac ac character varying(32) UNIQUE#1 NOT NULL
fragment boolean DEFAULT false
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spotentry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotentrygeneraltopic

spotentrygeneraltopic Structure
F-Key Name Type Description
generaltopicentrydata.topicdataid topicdataid integer NOT NULL
spotentry.spotid#1 spotid character varying(16)
spotentry.gelid#1 gelid integer
spotentry.ac#1 ac character varying(32)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to spotentrygeneraltopic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotentrymappingtopic

spotentrymappingtopic Structure
F-Key Name Type Description
generaltopicentrydata.topicdataid topicdataid integer NOT NULL
spotentry.spotid#1 spotid character varying(16)
spotentry.gelid#1 gelid integer
spotentry.ac#1 ac character varying(32)
mappingtechnique character varying(8)[] DEFAULT '{N/A}'::character varying[]

A Check function verify reference to MappingTopicDefinition (Should add a Trigger that updates values if definition changes)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotentrymappingtopic Constraints
Name Constraint
spotentrymappingtopic_mappingtechnique_check CHECK (common.make2db_verify_mappingtechnique(mappingtechnique))

 

Permissions which apply to spotentrymappingtopic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotentrytopicparent

spotentrytopicparent Structure
F-Key Name Type Description
generaltopicentrydata.topicdataid topicdataid integer NOT NULL
spotentry.spotid#1 spotid character varying(16)
spotentry.gelid#1 gelid integer
spotentry.ac#1 ac character varying(32)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to spotentrytopicparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotidentificationaacid

spotidentificationaacid Structure
F-Key Name Type Description
identificationid integer PRIMARY KEY DEFAULT nextval('spotidentificationaacid_identificationid_seq'::regclass)
humanidentifier character varying(256)
hassubset boolean DEFAULT false
spotdataaacid.dataexpid#2 dataexpid integer
spotdataaacid.spotid#2 spotentry.spotid#1 spotid character varying(16) NOT NULL
spotdataaacid.gelid#2 spotentry.gelid#1 gelid integer NOT NULL
spotentry.ac#1 ac character varying(32)
isoform text
version integer DEFAULT 1
contactid integer
uri character varying(4096)
identificationdocument text
identificationdescription text
allexperimentdata boolean DEFAULT true
appreciation numeric(2,1)
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
aminoacidlist text NOT NULL

(In theory) ensure the amino acids subset is equal to the whole SpotDataAAcid.aminoAcidList
relateddata text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotidentificationaacid Constraints
Name Constraint
spotidentificationaacid_hassubset_check CHECK ((hassubset = false))
spotidentificationparent_appreciation_check CHECK (((appreciation > (0)::numeric) AND (appreciation < (10)::numeric)))

 

Permissions which apply to spotidentificationaacid
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotidentificationother

spotidentificationother Structure
F-Key Name Type Description
identificationid integer PRIMARY KEY DEFAULT nextval('spotidentificationother_identificationid_seq'::regclass)
humanidentifier character varying(256)
hassubset boolean DEFAULT false
spotdataother.dataexpid#2 dataexpid integer
spotdataother.spotid#2 spotentry.spotid#1 spotid character varying(16) NOT NULL
spotdataother.gelid#2 spotentry.gelid#1 gelid integer NOT NULL
spotentry.ac#1 ac character varying(32)
isoform text
version integer DEFAULT 1
contactid integer
uri character varying(4096)
identificationdocument text
identificationdescription text
allexperimentdata boolean DEFAULT true
appreciation numeric(2,1)
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
results text NOT NULL
relateddata text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotidentificationother Constraints
Name Constraint
spotidentificationother_hassubset_check CHECK ((hassubset = false))
spotidentificationparent_appreciation_check CHECK (((appreciation > (0)::numeric) AND (appreciation < (10)::numeric)))

 

Permissions which apply to spotidentificationother
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotidentificationparent

* Applied Trigger: Trigger_SpotIdentificationParent_nextval_identificationID * * Applied Trigger: Trigger_SpotIdentificationParent_showFlagSwitchTrue * SpotID and GelID are needed, as DataParent may miss!

spotidentificationparent Structure
F-Key Name Type Description
identificationid serial NOT NULL
humanidentifier character varying(256)

This field is to hold an identifier given by data analyzers to identify a specific identification (different from the database generated identificationID Primary Key). It is NOT UNIQUE (as the same analysis may refer to several identified proteins). It may be NULL, as no such identifier is systematically given by users
hassubset boolean DEFAULT false
dataexpid integer
spotentry.spotid#1 spotid character varying(16) NOT NULL
spotentry.gelid#1 gelid integer NOT NULL
spotentry.ac#1 ac character varying(32)
isoform text

Check for forthcoming annotations of varsplices, variants and conflicts according to UniProt
version integer DEFAULT 1

This field version corresponds idealy to the identified entry (new) version when the identification has been performed
contact.contactid contactid integer
uri character varying(4096)

This may include URLs to local or external identification description files (e.g. "PSI::AnalysisXML")
identificationdocument text

This may correspond in the sub-classes to, e.g., "PSI:AnalysisXML": give a file system path (no storage as large objects)
identificationdescription text
allexperimentdata boolean DEFAULT true
appreciation numeric(2,1)
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotidentificationparent Constraints
Name Constraint
spotidentificationparent_appreciation_check CHECK (((appreciation > (0)::numeric) AND (appreciation < (10)::numeric)))

 

Permissions which apply to spotidentificationparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotidentificationpeptmassf

spotidentificationpeptmassf Structure
F-Key Name Type Description
identificationid integer PRIMARY KEY DEFAULT nextval('spotidentificationpeptmassf_identificationid_seq'::regclass)
humanidentifier character varying(256)
hassubset boolean DEFAULT false
spotdatapeptmassf.dataexpid#2 dataexpid integer
spotdatapeptmassf.spotid#2 spotentry.spotid#1 spotid character varying(16) NOT NULL
spotdatapeptmassf.gelid#2 spotentry.gelid#1 gelid integer NOT NULL
spotentry.ac#1 ac character varying(32)
isoform text
version integer DEFAULT 1
contactid integer
uri character varying(4096)
identificationdocument text
identificationdescription text
allexperimentdata boolean DEFAULT true
appreciation numeric(2,1)
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
peptidemasses double precision[] NOT NULL

[mass][intensity]; (In theory) ensure this is a subset of SpotDataPeptMassF.peptideMasses
relateddata text
xxac character varying(32)

The "xx" fields are specific to SWISS-2DPAGE
xxdirectory character varying(256)
xxfile character varying(64)
xxprogversion character varying(64)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotidentificationpeptmassf Constraints
Name Constraint
spotidentificationparent_appreciation_check CHECK (((appreciation > (0)::numeric) AND (appreciation < (10)::numeric)))
spotidentificationpeptmassf_hassubset_check CHECK ((hassubset = false))

 

Permissions which apply to spotidentificationpeptmassf
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotidentificationtandemms

This identification category has a Subset of identified elements

spotidentificationtandemms Structure
F-Key Name Type Description
identificationid integer PRIMARY KEY DEFAULT nextval('spotidentificationtandemms_identificationid_seq'::regclass)
humanidentifier character varying(256)
hassubset boolean DEFAULT false

hasSubset is true for TandemMS
spotdatatandemms.dataexpid#2 dataexpid integer
spotdatatandemms.spotid#2 spotentry.spotid#1 spotid character varying(16) NOT NULL
spotdatatandemms.gelid#2 spotentry.gelid#1 gelid integer NOT NULL
spotentry.ac#1 ac character varying(32)
isoform text
version integer DEFAULT 1
contactid integer
uri character varying(4096)
identificationdocument text
identificationdescription text
allexperimentdata boolean DEFAULT true
appreciation numeric(2,1)
datadisplayer text
showflag boolean NOT NULL DEFAULT true
showflagswitch boolean NOT NULL DEFAULT true
ionmasses double precision[]

[mass][intensity]; (In theory) ensure this is a subset of SpotDataTandemMS.ionMasses
relateddata text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotidentificationtandemms Constraints
Name Constraint
spotidentificationparent_appreciation_check CHECK (((appreciation > (0)::numeric) AND (appreciation < (10)::numeric)))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to spotidentificationtandemms
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: spotidentificationtandemmssubset

spotidentificationtandemmssubset Structure
F-Key Name Type Description
spotidentificationtandemms.identificationid identificationid integer NOT NULL
ionmasses double precision[]

(In theory) ensure this is a subset of SpotIdentificationTandemMS.ionMasses
identifiedpeptidesequence text NOT NULL
sequencestartposition smallint
sequenceendposition smallint
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

spotidentificationtandemmssubset Constraints
Name Constraint
spotidentificationtandemmssubset_check CHECK ((sequenceendposition >= sequencestartposition))

 

Permissions which apply to spotidentificationtandemmssubset
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: study

study Structure
F-Key Name Type Description
studyid serial PRIMARY KEY
project.projectid projectid integer
description text
contact.contactid contactid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to study
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: studygroup

studygroup Structure
F-Key Name Type Description
studygroupid serial PRIMARY KEY
study.studyid studyid integer NOT NULL
description text
biosourceinformation.biosourceinformationid biosourceinformationid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to studygroup
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: tissue

Expecting a forthcoming PSI proposition, probably based on a hierarchical onthology

tissue Structure
F-Key Name Type Description
tissueid serial PRIMARY KEY
tissuename character varying(256) NOT NULL
tissue.tissueid tissueparentid integer
tissuecomment character varying(1024)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to tissue
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: tissuesp

TissueSP is a very general designation of the cell organ/tissue/liquid as proposed by Swiss-Prot... Not to be edited by users! the tisslist.list is part of the package, it can be automatically loaded from the ExPASy server (the SWISS-PROT tisslist.txt). Recently a new Swiss-Prot identifier has been defined, tissueSPName is used as a unique identifier for the moment! Mapping with the Tissue table is expected in the future. * Applied Trigger: Trigger_TissueSP_uc *

tissuesp Structure
F-Key Name Type Description
tissuespname character varying(256) PRIMARY KEY
tissuespdisplayedname character varying(256) NOT NULL
tissueindate date NOT NULL DEFAULT '2006-06-27'::date
tissuecomment character varying(1024)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to tissuesp
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: tissuespaliase

* Applied Trigger: Trigger_TissueSPAliase_uc *

tissuespaliase Structure
F-Key Name Type Description
tissuesp.tissuespname tissuespname character varying(256) NOT NULL
alias character varying(256) NOT NULL
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to tissuespaliase
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: tissuesptissuemapping

* Applied Trigger: Trigger_TissueSPTissueMapping_uc *

tissuesptissuemapping Structure
F-Key Name Type Description
tissuesp.tissuespname tissuespname character varying(256)
tissue.tissueid tissueid integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to tissuesptissuemapping
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: tmp_last_entry

THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)

tmp_last_entry Structure
F-Key Name Type Description
identifier character varying(32)
id_method character varying(48)
accession_number character varying(32)
secondary_identifiers text
creation text
version_2d text
version_general text
description text
genes text
organism text
organism_classification text
taxonomy_cross_reference text
masters text
images text
free_comments text
reference_lines text
one_d_comments text
one_d_blocks text
two_d_comments text
two_d_blocks text
database_cross_reference text

 

Permissions which apply to tmp_last_entry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: viewentry

THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)

viewentry Structure
F-Key Name Type Description
identifier character varying(32)
id_method character varying(48)
accession_number character varying(32)
secondary_identifiers text
creation text
version_2d text
version_general text
description text
genes text
organism text
organism_classification text
taxonomy_cross_reference text
masters text
images text
free_comments text
reference_lines text
one_d_comments text
one_d_blocks text
two_d_comments text
two_d_blocks text
database_cross_reference text

 

Permissions which apply to viewentry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: viewmapentrylist

THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)

viewmapentrylist Structure
F-Key Name Type Description
gelid integer
genes character varying(1024)
description text
spotid character varying(16)
ac character varying(32)
id character varying(32)
pi numeric(4,2)
mw numeric(6,3)
volumerelative real
odrelative real
fragment boolean
topicdescription text
mappingtechniques text
availableresults text
rn_references text
referenceids text
showflag boolean
showflagswitch boolean

 

Permissions which apply to viewmapentrylist
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: viewref

THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)

viewref Structure
F-Key Name Type Description
referenceid integer
referenceworkdescription character varying(64)
crossreferences character varying(256)
authorsgroup character varying(2048)
authors character varying(8192)
referencetitle text
referencelocation text

 

Permissions which apply to viewref
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: viewspotentry

THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)

viewspotentry Structure
F-Key Name Type Description
spotid character varying(16)
gelid integer
ac character varying(32)
fragment boolean
aminoacidlist text

Specific data sets are separated by
aa_version integer
msms text

Specific data sets are separated by
ms_version integer
peptidesequences text

Specific data sets are separated by
peptseq_version integer
peptidemasses text

Specific data sets are separated by
pmf_version integer
showflag boolean
showflagswitch boolean

 

Permissions which apply to viewspotentry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: viewspotentrylast

THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)

viewspotentrylast Structure
F-Key Name Type Description
spotid character varying(16)
gelid integer
ac character varying(32)
fragment boolean
aminoacidlist text
aa_version integer
msms text
ms_version integer
peptidesequences text
peptseq_version integer
peptidemasses text
pmf_version integer
showflag boolean
showflagswitch boolean

 

Permissions which apply to viewspotentrylast
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: xrefdb

* Applied Trigger: Trigger_XrefDB_insensitiveName *

xrefdb Structure
F-Key Name Type Description
xrefdbcode integer PRIMARY KEY DEFAULT nextval('xrefdb_xrefdbcode_seq'::regclass)
xrefdbname character varying(64) UNIQUE NOT NULL
description text
url text
displayerurl text
displayerparameters text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

xrefdb Constraints
Name Constraint
xrefdb_check CHECK (((((((xrefdbcode > 5) OR ((xrefdbcode = 1) AND ((xrefdbname)::text ~* '^Swiss-?Prot$'::text))) OR ((xrefdbcode = 2) AND ((xrefdbname)::text ~* '^TrEMBL$'::text))) OR ((xrefdbcode = 3) AND ((xrefdbname)::text ~* '^SWISS-?2DPAGE$'::text))) OR ((xrefdbcode = 4) AND ((xrefdbname)::text ~* '^Swiss-?Prot:SRS$'::text))) OR ((xrefdbcode = 5) AND ((xrefdbname)::text ~* '^GO$'::text))))

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to xrefdb
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: xrefdbdynamic

By convention: any database not present in XrefDB has an XrefDBCode > 1000, this is ensured by the update interface! * Applied Trigger: Trigger_XrefDBDynamic_insensitiveName *

xrefdbdynamic Structure
F-Key Name Type Description
xrefdbcode integer PRIMARY KEY DEFAULT nextval('xrefdbdynamic_xrefdbcode_seq'::regclass)
xrefdbname character varying(64) UNIQUE NOT NULL
description text
url text
displayerurl text
displayerparameters text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

Permissions which apply to xrefdbdynamic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Table: xrefdbparent

xrefdbparent Structure
F-Key Name Type Description
xrefdbcode serial NOT NULL
xrefdbname character varying(64)
description text
url text
displayerurl text
displayerparameters text
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

Permissions which apply to xrefdbparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema core


Function: drop_class( bpchar, character varying, character varying )

Returns: boolean

Language: PLPGSQL

FUNCTION TO DROP A VARIABLE CLASS

 



-- FUNCTION TO DROP A VARIABLE CLASS  

------------------------------------



  DECLARE



      class_type ALIAS for $1;

      class_name_init ALIAS for $2;

      class_name VARCHAR(128);

      class_table_init ALIAS for $3;

      class_table VARCHAR(128);



  BEGIN



      -- !! Handle with EXTREME CARE !!



      class_name = lower(class_name_init);

      class_table = lower(class_table_init);



      IF EXISTS (SELECT relname FROM pg_class WHERE relname = class_name::NAME)

      THEN IF class_type = 'T' OR class_type = 't'

           THEN EXECUTE 'DROP TABLE ' || class_name;

           RETURN 'true';

           ELSE IF class_type = 'V' OR class_type = 'v'

           THEN EXECUTE 'DROP VIEW ' || class_name;

           RETURN 'true';

           ELSE IF class_type = 'I' OR class_type = 'i'

           THEN EXECUTE 'DROP INDEX ' || class_name;

           RETURN 'true';

           ELSE IF class_type = 'R' OR class_type = 'r'

           THEN EXECUTE 'DROP RULE ' || class_name;

           RETURN 'true';

           END IF;

           END IF;

           END IF;

           END IF;

      END IF;



      IF EXISTS (SELECT tgname FROM pg_trigger WHERE tgname = class_name::NAME)

      THEN IF class_type = 'G' OR class_type = 'g'

           THEN EXECUTE 'DROP TRIGGER ' || class_name || ' ON ' || class_table;

           RETURN 'true';

           END IF;

      END IF;



  RETURN 'false';



  END;




Function: drop_function( character varying )

Returns: boolean

Language: PLPGSQL

FUNCTION TO DROP ALL NAMED FUNCTIONS (deprecated since postgreSQL 7.1 REPLACE command)





-- FUNCTION TO DROP ALL NAMED FUNCTIONS 

---------------------------------------



  DECLARE



      function_name_init ALIAS for $1;

      function_name VARCHAR(128);

      my_record RECORD;



  BEGIN



      function_name = lower(function_name_init);



      IF NOT EXISTS (SELECT pg_proc.OID AS proc_OID FROM pg_proc WHERE proname = function_name::NAME)

      THEN RETURN 'false';

      END IF;



      FOR my_record IN

            SELECT pg_proc.OID AS proc_OID FROM pg_proc

                   WHERE lower(proname) = function_name::NAME

      LOOP

            DELETE FROM pg_proc WHERE OID = my_record.proc_OID;

      END LOOP;





  RETURN 'true';



  END;




Function: make2db_backup_views( boolean )

Returns: boolean

Language: PLPGSQL

FUNCTION TO BACKUP OR RESTORE THE CURRENT VIEWS VIA TEMPORARY TABLES





-- FUNCTION TO BACKUP OR RESTORE THE CURRENT VIEWS VIA TEMPORARY TABLES 

-----------------------------------------------------------------------



-- Argument: True -> Backup, False -> Restore



   DECLARE



     my_argument ALIAS FOR $1;

     my_backup_view TEXT;

     my_status BOOLEAN;

     my_record RECORD;



   BEGIN



   my_status = FALSE;

   IF (my_argument IS NULL) THEN RETURN my_status; END IF; 



   FOR my_record IN

     SELECT tablename FROM pg_tables WHERE tablename ~* '^View' AND lower(schemaname) = 'core'

   LOOP

     my_backup_view := lower(my_record.tablename || '_backup');

     IF (my_argument IS TRUE) THEN

       my_status = TRUE;

       IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = my_backup_view) THEN

         EXECUTE 'DROP TABLE ' || my_backup_view;

       END IF;

       EXECUTE 'CREATE TEMPORARY TABLE ' || my_backup_view  || ' AS SELECT * FROM ' || my_record.tablename;

     ELSE

       IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = my_backup_view) THEN

         my_status = TRUE;

         EXECUTE 'DELETE FROM ' || my_record.tablename;

         EXECUTE 'INSERT INTO ' || my_record.tablename  || ' SELECT * FROM ' || my_backup_view;

       END IF;

     END IF;

   END LOOP;



   RETURN my_status;



   END;

Function: make2db_construct_entry( character varying )

Returns: boolean

Language: PLPGSQL

FUNCTION FOR ENTRY/IES "VIEWS" CONSTRUCTION (Arguments: an entry accession number, or "all" to construct all entries)





-- FUNCTION FOR ENTRY/IES "VIEWS" CONSTRUCTION  

----------------------------------------------



-- (Arguments: an entry accession number, or "all" to construct all entries)



   DECLARE



            my_ac ALIAS for $1;    

            my_limit_search_ac VARCHAR(32);

            void_operation BOOLEAN;

            my_record RECORD;

 



   BEGIN



      SET DATESTYLE TO 'POSTGRES, EUROPEAN';



      void_operation := 'false';



      IF my_ac = '' THEN RETURN 'false';

      END IF;



      RAISE NOTICE 'Entry construction in progress...';



      IF my_ac = 'all'

      THEN 

        RAISE NOTICE 'Analyzing tables...';

     -- ANALYZE; -- KHM (server shuts down in mordor!!)

        IF NOT (SELECT make2db_reunit_refs(0))      -- Construct General Reference Blocks in ViewRef -> general: (0)

        THEN

          RAISE NOTICE 'No result performing make2db_reunit_refs function on all entries. Entry construction is partially incomplete!

';

          void_operation := 'true';

        END IF;

        IF NOT (SELECT make2db_reunit_spots('all'))    -- Construct ACs 1D and 2D Blocks in ViewSpotEntry -> general:("all")

        THEN

          RAISE NOTICE 'No result performing make2db_reunit_spots function on all entries. Entry construction is partially incomplete!

';

          void_operation := 'true';

        END IF;      

        my_limit_search_ac := '.';



      ELSE

        IF NOT EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'viewref')

        THEN

          IF NOT (SELECT make2db_reunit_refs(0))

          THEN

            RAISE NOTICE 'No result performing make2db_reunit_refs function. Entry construction is interrupted!

';

            RETURN 'false';

          END IF; 

        END IF;

        IF NOT (SELECT make2db_reunit_spots(my_ac))    -- Construct ACs 1D and 2D Blocks in ViewSpotEntryLast -> general:("all")

        THEN

          RAISE NOTICE 'No result performing make2db_reunit_spots function for entry %. Entry construction is interrupted!

', my_ac;

          RETURN 'false';

        END IF;

	my_limit_search_ac := my_ac;



      END IF; 





      -- ...for each specified entry, we extract blocks using make2db_reunit_refs_ac(AC)

      -- use SELECT make2db_reunit_refs("article_id") for a given article -> ViewRefLast

      -- use PERFORM make2db_reunit_spots("accession_number") for a given Entry -> ViewSpotEntryLast







      -- EXECUTE does not support SELECT yet, no way to use a (DYNAMIC) TEMPORARY TABLE with CREATE AS..!!

      -- NON dynamic temporary tables are compiled ONCE and all reference to them fail in consequent calls.



      SELECT INTO my_record databaseName FROM common.Database LIMIT 1;

      IF (NOT FOUND) THEN

        my_record.databaseName := 'the current database';

      END IF;



      IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_make2db_construct_entry')

      THEN

        DELETE FROM buffer_make2db_construct_entry;

      ELSE

        CREATE TEMPORARY TABLE buffer_make2db_construct_entry (

                                                           identifier VARCHAR(32),

                                                           id_method  VARCHAR(48),

                                                           accession_number VARCHAR(32),

                                                           secondary_identifiers TEXT,

                                                           creation TEXT,

                                                           version_2d TEXT,

							   version_general TEXT,

                                                           description TEXT,

                                                           genes TEXT,

                                                           organism TEXT,

                                                           organism_classification TEXT,

                                                           taxonomy_cross_reference TEXT,

                                                           masters TEXT,

                                                           images TEXT,

                                                           free_comments TEXT,

                                                           reference_lines TEXT,

                                                           one_d_comments TEXT, 

                                                           one_d_blocks TEXT,

                                                           two_d_comments TEXT,

                                                           two_d_blocks TEXT,

                                                           database_cross_reference TEXT

                                                        );



      END IF;    -- Any change to buffer_make2d_construct_entry is to be reported in both upper and lower blocks





      INSERT INTO buffer_make2db_construct_entry

      SELECT       Entry.ID AS identifier,

                   Entry.entryClass || '; ' || Entry.IdentMethod AS id_method,

                   Entry.AC AS accession_number, make2db_reunit_line(Entry.AC,'AC') AS secondary_identifiers,

                   creation.releaseDate || ', integrated into ' || my_record.databaseName || ' (release ' || creation.releaseNum || ')' AS creation,

                   EntryVersion2D.versionDate || ', 2D annotation version ' || EntryVersion2D.version AS version_2d,

                   EntryVersionGeneral.versionDate || ', general annotation version ' ||  EntryVersionGeneral.version AS version_general,

		   make2db_reunit_line(Entry.AC,'DE') AS description,

                   Entry.geneNames AS genes,

                   Organism.organismSpecies AS organism,

                   Organism.organismClassification AS organism_classification,

                   XrefDB.XrefDBName || '='  || Organism.taxonomyCode AS taxonomy_cross_reference,

		   make2db_reunit_line(Entry.AC,'MT') AS masters, -- Specific to Swiss2D-PAGE

                   make2db_reunit_line(Entry.AC,'IM') AS images,

                   make2db_reunit_line(Entry.AC,'CC') AS free_comments,

                   make2db_reunit_refs_ac(Entry.AC) AS reference_lines,

                   make2db_reunit_line(Entry.AC,'1C') AS one_d_comments,

                   make2db_reunit_line(Entry.AC,'1D') AS one_d_blocks,

                   make2db_reunit_line(Entry.AC,'2C') AS two_d_comments,

                   make2db_reunit_line(Entry.AC,'2D') AS two_d_blocks,

                   make2db_reunit_line(Entry.AC,'DR') AS database_cross_reference





      FROM         Entry, Release creation, EntryVersion2D, EntryVersionGeneral, XrefDB, Organism



      WHERE        Entry.releaseCreation = creation.releaseNum

                   AND Entry.AC = EntryVersion2D.AC

		   AND Entry.AC = EntryVersionGeneral.AC

                   AND Entry.organismID = Organism.organismID

                   AND Organism.taxonomyXrefDBCode = XrefDB.XrefDBCode

                   AND Entry.showFlagSwitch IS TRUE

                   /* to limit construction to the desired AC(s) */

                   AND Entry.AC ~* my_limit_search_ac



      ORDER BY 3;



      -- Entry.entryClass and Entry.IdentMethod could have been void

      UPDATE buffer_make2db_construct_entry SET id_method = '' WHERE id_method = '; ';







      IF NOT EXISTS (SELECT * FROM buffer_make2db_construct_entry LIMIT 1)

      THEN

          void_operation := 'true';

          RAISE NOTICE 'No data was found to construct text view for entry/ies: %', my_ac;

      ELSE

          void_operation := 'false';

      END IF;





      /* We move the updated entries to the entries table in just one operation*/

      PERFORM drop_class('T', 'tmp_buffer_entries','');

      CREATE TABLE tmp_buffer_entries AS SELECT * FROM buffer_make2db_construct_entry;

      COMMENT ON TABLE tmp_buffer_entries IS 

       'THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)';

      GRANT SELECT ON tmp_buffer_entries TO select2d;





      IF my_ac = 'all'

      THEN

          PERFORM drop_class('T', 'ViewEntry', ''); -- Indexes are automatically droped        

          ALTER TABLE  tmp_buffer_entries RENAME TO ViewEntry;

          CREATE INDEX ViewEntry_ac_idx ON ViewEntry(accession_number);

          CREATE INDEX ViewEntry_id_idx ON ViewEntry(identifier);

          RAISE NOTICE 'Full Entries Table has been constructed/updated!

';

      ELSE

          DELETE FROM ViewEntry WHERE accession_number::VARCHAR(32) = my_ac;

          INSERT INTO ViewEntry (SELECT * FROM buffer_make2db_construct_entry);

          PERFORM drop_class('T', 'tmp_last_entry', '');

          ALTER TABLE tmp_buffer_entries RENAME TO tmp_last_entry;

      END IF;



      DELETE FROM buffer_make2db_construct_entry;

   

   -- ANALYZE ViewEntry; -- KHM (server shuts down in mordor!!)

  



      IF (void_operation) THEN

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: make2db_export_ascii_entries( character varying, integer, text, text )

Returns: boolean

Language: PLPGSQL

FUNCTION TO EXPORT ASCII ENTRY(IES) IN STANDARD TEXT FORMAT. Ouput will have litteral \n sequences, so the file should be parsed to convert those sequences into real \n characters





-- FUNCTION TO EXPORT ASCII ENTRY(IES) IN STANDARD TEXT FORMAT 

--------------------------------------------------------------

-- Output will have litteral \n sequences, so the file should be parsed to convert thoses sequences into real \n characters



   DECLARE



            my_ac VARCHAR(32);    

            my_line_length INT;

            my_hide_private TEXT;

	    my_file TEXT;

	    my_file_command TEXT;

            my_record RECORD;

         -- my_database_name TEXT;



   BEGIN



       my_file := '''' || $3 || '''';

       my_line_length := $2;

       IF my_line_length <25 THEN my_line_length = 75;

       END IF;



       my_ac := $1;



       my_hide_private := $4;



      -- CREATE TEMPORARY TABLE export_ascii_entries (make2db_ascii_entry TEXT); !! still no (select...into) with execute

      IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_export_ascii_entries')

      THEN

        DELETE FROM buffer_export_ascii_entries; -- No need to lock table (serializable level is set on)

      ELSE

        CREATE TEMPORARY TABLE buffer_export_ascii_entries (make2db_ascii_entry TEXT);

      END IF;



       IF my_ac  = '' OR my_ac = 'all' THEN    



         FOR my_record IN SELECT AC FROM entry WHERE showFlagSwitch = 'true' ORDER BY ID

         LOOP

            INSERT INTO buffer_export_ascii_entries SELECT make2db_ascii_entry(my_record.AC, my_line_length, my_hide_private);

         END LOOP;

	 

       ELSE

	    INSERT INTO buffer_export_ascii_entries SELECT make2db_ascii_entry(my_ac, my_line_length, my_hide_private);

       END IF;



       my_file_command := 'COPY buffer_export_ascii_entries TO ' || my_file;

       EXECUTE my_file_command;

       EXECUTE 'DELETE FROM buffer_export_ascii_entries';



       RETURN 'true';



   END;

Function: make2db_map_protein_list( )

Returns: boolean

Language: PLPGSQL

FUNCTION TO BUILD PROTEINS LIST FOR EACH MAP





-- FUNCTION TO BUILD PROTEINS LIST FOR EACH MAP

-----------------------------------------------



   DECLARE



            my_record RECORD;

            my_record2 RECORD;

            my_record3 RECORD;

            my_record_maps_number RECORD;

            my_record_maps_name RECORD;

            my_array_length INT2;

            my_description_string TEXT;

            my_mapping_string TEXT;

            my_results_string TEXT;

            my_RN_string TEXT;

            my_RF_string TEXT;

            my_this_reference TEXT;

            my_variable_string TEXT;

            void_operation BOOLEAN;





   BEGIN



      void_operation := 'false';



      RAISE NOTICE 'Protein list in progress...';



   -- ANALYSE; -- KHM (server shuts down in mordor!!)



      IF NOT EXISTS (SELECT AC FROM EntryGelImage LIMIT 1)

      THEN

        RAISE NOTICE 'No entries found to construct protein lists on maps. List will be empty!

';

        void_operation := 'true';

      END IF;





      IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_make2db_map_protein_list')

      THEN 

        DELETE FROM buffer_make2db_map_protein_list; -- No need to lock table (serializable level is set on)

      ELSE

        CREATE TEMPORARY TABLE buffer_make2db_map_protein_list

            ( gelID INT4,

              genes VARCHAR(1024),

              description TEXT,

              spotID VARCHAR(16),

              AC VARCHAR(32),

              ID VARCHAR(32),

              pI NUMERIC(4,2),

              mw NUMERIC(6,3),

              volumeRelative FLOAT4,

              odRelative FLOAT4,

	      fragment BOOLEAN,

              topicDescription TEXT,

              mappingTechniques TEXT,

              availableResults TEXT,

              RN_References TEXT,

              referenceIDs TEXT,

              showFlag BOOLEAN NOT NULL DEFAULT 'true',

              showFlagSwitch BOOLEAN NOT NULL DEFAULT 'true'

                CHECK ((showFlag IS TRUE AND showFlagSwitch IS TRUE) OR showFlag IS NOT TRUE)

             );

      END IF;



      IF NOT (void_operation) THEN

        RAISE NOTICE 'Protein Lists are being now processed for each Map. Please wait!...';

      END IF;



      INSERT INTO buffer_make2db_map_protein_list 

        SELECT DISTINCT SpotEntry.gelID, Entry.geneNames as genes, Entry.description, SpotEntry.spotID,

               Entry.AC, Entry.ID as ID,

               Spot.pI, (Spot.mw)/1000.0, Spot.volumeRelative, Spot.odRelative, SpotEntry.fragment

        FROM SpotEntry JOIN (Entry JOIN EntryGelImage ON (Entry.AC = EntryGelImage.AC ))

                        ON (SpotEntry.AC = Entry.AC),

                        Spot

        WHERE (Spot.spotID = SpotEntry.spotID AND Spot.gelID = SpotEntry.gelID

               AND SpotEntry.showFlagSwitch IS TRUE

        );



      DELETE FROM buffer_make2db_map_protein_list

        WHERE buffer_make2db_map_protein_list.gelID IN (SELECT gelID FROM Gel WHERE showFlagSwitch IS FALSE);



      IF NOT (void_operation) THEN

        RAISE NOTICE '... Protein lists for the different maps are still in progress...

';

      END IF;



      UPDATE buffer_make2db_map_protein_list SET genes = '-' WHERE genes IS NULL;



      UPDATE buffer_make2db_map_protein_list SET showFlag = Entry.showFlag

        FROM ENTRY

        WHERE buffer_make2db_map_protein_list.AC = Entry.AC;



      SELECT INTO my_record_maps_number max(gelID) AS max_number FROM Gel;



      IF (my_record_maps_number.max_number IS NULL) AND NOT (void_operation) THEN

        RAISE NOTICE 'No existing maps have been found to construct protein lists. Process in interrupted!

';

        void_operation := 'true';

      END IF;







      -- Loop Over Each Map



      IF (my_record_maps_number.max_number > 0) AND NOT (void_operation) THEN



        FOR maps_ii IN 1..my_record_maps_number.max_number



        LOOP



          SELECT INTO my_record_maps_name shortName FROM Gel WHERE Gel.gelID = maps_ii AND Gel.showFlagSwitch IS TRUE;



          IF my_record_maps_name.shortName IS NOT NULL THEN

            RAISE NOTICE '... % is being processed', my_record_maps_name.shortName;

          END IF;



          FOR my_record IN



                SELECT gelID, spotID, AC FROM buffer_make2db_map_protein_list WHERE gelID = maps_ii



          LOOP  -- For Every Line of the Selected Map 



                my_description_string := '';

                my_mapping_string := '';

                my_results_string := '';

                my_RN_string := '';

                my_RF_string := '';





                IF ( EXISTS ( SELECT * FROM SpotEntryGeneralTopic WHERE spotID  = my_record.spotID

                                    AND gelID = my_record.gelID AND AC = my_record.AC LIMIT  1 )

                  OR EXISTS ( SELECT * FROM SpotEntryMappingTopic WHERE spotID = my_record.spotID

                                    AND gelID = my_record.gelID AND AC = my_record.AC LIMIT  1 )

                   )



                THEN



                  FOR my_record2 IN   -- grouped General topic description, possibly over several lines



                       SELECT topicName, allReferences, ambiguousReference FROM GeneralTopicDefinition, GeneralTopicEntryData, SpotEntryGeneralTopic

                         WHERE SpotEntryGeneralTopic.spotID = my_record.spotID

                         AND SpotEntryGeneralTopic.gelID = my_record.gelID AND SpotEntryGeneralTopic.AC = my_record.AC

                         AND SpotEntryGeneralTopic.topicDataID = GeneralTopicEntryData.topicDataID 

                         AND GeneralTopicEntryData.generalTopicID = GeneralTopicDefinition.generalTopicID

                      -- AND ambiguousReference = 'false'

                         ORDER BY GeneralTopicDefinition.topicName, GeneralTopicEntryData.topicDataText

                   --  UNION -> Does not work here, so we retype the entire querry for mapping methods



                  LOOP

                        IF my_description_string !~ my_record2.topicName

                        THEN

                          my_description_string := my_description_string || my_record2.topicName || ', ';

                        END IF;

                        FOR ii IN 1..99

                        LOOP

                          IF my_record2.allReferences[1] IS NULL AND (my_record2.ambiguousReference)

                          THEN

                            my_RN_string := '1, ';

                            SELECT INTO my_record3 referenceID FROM ReferencedEntry WHERE AC = my_record.AC AND RNdisplayedValue = 1 LIMIT 1; 

                            my_RF_string := my_record3.referenceID::TEXT || ', ';

                            EXIT;

                          END IF;

                          IF  my_record2.allReferences[ii] IS NULL THEN EXIT;

                          END IF;

                          my_this_reference :=  my_record2.allReferences[ii]::TEXT || ', ';

                          IF my_RN_string !~ my_this_reference

                          THEN

                            my_RN_string := my_RN_string || my_this_reference;

                            SELECT INTO my_record3 referenceID FROM ReferencedEntry 

                                   WHERE AC = my_record.AC AND RNdisplayedValue =  my_record2.allReferences[ii] LIMIT 1;

                            my_RF_string := my_RF_string || my_record3.referenceID::TEXT || ', ';

                          END IF;

                        END LOOP;

                  END LOOP;



                   --  UNION -> Does not work here, we retype the entire querry for mapping methods

                  FOR my_record2 IN   -- grouped General topic description, possibly over several lines



                      SELECT 'MAPPING'::VARCHAR(64) as topicName, allReferences,

                         ambiguousReference FROM GeneralTopicEntryData, SpotEntryMappingTopic

                         WHERE SpotEntryMappingTopic.spotID = my_record.spotID

                         AND SpotEntryMappingTopic.gelID = my_record.gelID AND SpotEntryMappingTopic.AC = my_record.AC

                         AND SpotEntryMappingTopic.topicDataID = GeneralTopicEntryData.topicDataID 

                      -- AND ambiguousReference = 'false'



                  LOOP

                        IF my_description_string !~ my_record2.topicName

                        THEN

                          my_description_string := my_description_string || my_record2.topicName || ', ';

                        END IF;

                        FOR ii IN 1..99

                        LOOP

                          IF my_record2.allReferences[1] IS NULL AND (my_record2.ambiguousReference)

                          THEN

                            IF  (my_RN_string ~ '^1,' OR my_RN_string ~ ' 1,') THEN EXIT; END IF;

                            my_RN_string :=  '1, ' || my_RN_string;

                            SELECT INTO my_record3 referenceID FROM ReferencedEntry WHERE AC = my_record.AC AND RNdisplayedValue = 1 LIMIT 1; 

                            my_RF_string :=  my_RF_string || my_record3.referenceID::TEXT || ', ';

                            EXIT;

                          END IF;

                          IF  my_record2.allReferences[ii] IS NULL THEN EXIT;

                          END IF;

                          my_this_reference :=  my_record2.allReferences[ii]::TEXT || ', ';

                          IF my_RN_string !~ my_this_reference

                          THEN

                            my_RN_string := my_RN_string || my_this_reference;

                            SELECT INTO my_record3 referenceID FROM ReferencedEntry 

                                   WHERE AC = my_record.AC AND RNdisplayedValue =  my_record2.allReferences[ii] LIMIT 1;

                            my_RF_string := my_RF_string || my_record3.referenceID::TEXT || ', ';

                          END IF;

                        END LOOP;

                  END LOOP;







                  my_description_string := substr(my_description_string, 1, length(my_description_string)-2);

                  my_RN_string := substr(my_RN_string, 1, length(my_RN_string)-2);

                  my_RF_string := substr(my_RF_string, 1, length(my_RF_string)-2);



                END IF;





                -- Now, if no associated Topic has been found for that spot, we refer it to the first defined article (RN=1) KHM

                IF my_RN_string is NULL or my_RN_string = ''

                THEN

                  my_RN_string :=  '1';

                  SELECT INTO my_record3 referenceID FROM ReferencedEntry WHERE AC = my_record.AC AND RNdisplayedValue = my_RN_string LIMIT 1; 

                  my_RF_string :=  my_RF_string || my_record3.referenceID::TEXT;

                END IF;





            -- Concatenate mapping methods

                my_mapping_string := my_mapping_string ||

                                            common.make2db_entryspot_mapping_methods_string(my_record.AC, my_record.spotID, my_record.gelID);





            -- Any Experimental results of PMF, Aa or MS/MS (PeptSeq)?

               my_results_string := '(';

               my_variable_string := '';       

               IF (length(my_mapping_string) > 0) THEN

                 my_variable_string := ' ';

               END IF;

               IF EXISTS ( SELECT * FROM SpotIdentificationPeptMassF WHERE spotID = my_record.spotID AND gelID = my_record.gelID 

                            AND AC = my_record.AC AND showFlagSwitch = 'true' LIMIT 1)

                THEN

                   my_results_string := my_results_string || 'PMF';

                   IF (my_mapping_string !~ '[{,]PMF[,}]') THEN

                     my_mapping_string := my_mapping_string || my_variable_string || '{PMF}';

                     my_variable_string := ' ';

                   END IF;

                END IF;

                IF EXISTS ( SELECT * FROM SpotIdentificationAAcid WHERE spotID = my_record.spotID AND gelID = my_record.gelID 

                             AND AC = my_record.AC AND showFlagSwitch = 'true' LIMIT 1)

                THEN

                   IF length(my_results_string) > 2 THEN my_results_string := my_results_string || ',';

                   END IF;

                   my_results_string := my_results_string || 'Aa';

                   IF (my_mapping_string !~ '[{,]Aa[,}]') THEN

                     my_mapping_string := my_mapping_string || my_variable_string || '{Aa}';

                     my_variable_string := ' ';

                   END IF;

                END IF;

                IF EXISTS ( SELECT * FROM SpotIdentificationTandemMS WHERE spotID = my_record.spotID 

                             AND gelID = my_record.gelID 

                             AND AC = my_record.AC AND showFlagSwitch = 'true' LIMIT 1)

                THEN

                   IF length(my_results_string) > 2 THEN my_results_string := my_results_string || ',';

                   END IF;

                   my_results_string := my_results_string || 'MS/MS';

                   IF (my_mapping_string !~ '[{,]MS/MS[,}]') THEN

                     my_mapping_string := my_mapping_string || my_variable_string || '{MS/MS}';

                   END IF;

                END IF;



                my_results_string := my_results_string ||  ')';



                IF  my_description_string = '' THEN my_description_string := '-'; 

                END IF;

                IF  my_mapping_string = '' THEN my_mapping_string := '-'; 

                END IF;

                IF  length(my_results_string) < 4 THEN my_results_string := '-'; 

                END IF;



                UPDATE buffer_make2db_map_protein_list

                SET topicDescription = my_description_string, mappingTechniques = my_mapping_string,

                    availableResults = my_results_string, RN_References = my_RN_string, referenceIDs = my_RF_string

                    WHERE buffer_make2db_map_protein_list.spotID = my_record.spotID

                    AND buffer_make2db_map_protein_list.gelID = my_record.gelID

                    AND buffer_make2db_map_protein_list.AC = my_record.AC;





           END LOOP; -- END LOOP For Every Line of the Selected Map



        END LOOP;



      END IF;



      -- END Loop Over Each Map













      IF NOT EXISTS ( SELECT * FROM buffer_make2db_map_protein_list LIMIT 1 ) THEN

        void_operation := 'true';

        RAISE NOTICE 'No data to construct the map protein lists.';

      ELSE

        void_operation := 'false';

      END IF;





      -- Move the buffer table to the permanent one:

      -- EXECUTE statements do not support SELECT yet!! Till then, we adopt this locked *concurrential* solution

      -->(eachs exectuion deletes the result of the previous one!!)



      IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'viewmapentrylist')

      THEN

        LOCK TABLE ViewMapEntryList IN ACCESS EXCLUSIVE MODE; -- Any concurrential transaction will wait for this one

        DELETE FROM ViewMapEntryList;

        INSERT INTO ViewMapEntryList SELECT * FROM buffer_make2db_map_protein_list ORDER by 1;



      ELSE

        CREATE TABLE ViewMapEntryList AS SELECT * FROM buffer_make2db_map_protein_list ORDER by 1; 

        CREATE INDEX ViewMapEntryList_gelID ON ViewMapEntryList (gelID);

        CREATE INDEX ViewMapEntryList_spotID ON ViewMapEntryList (spotID);

        CREATE INDEX ViewMapEntryList_AC_idx ON ViewMapEntryList (AC);

        COMMENT ON TABLE ViewMapEntryList IS

         'THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)';

        GRANT SELECT ON ViewMapEntryList TO select2d;

      END IF;





      DELETE FROM buffer_make2db_map_protein_list;



   -- ANALYZE ViewMapEntryList; -- KHM (server shuts down in mordor!!)







      IF (void_operation) THEN  

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: make2db_reunit_refs( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO REUNIFY GENERAL REFERENCE BLOCKS (use make2db_reunit_refs(0) to reunify all articles, otherwise pass the article id)





-- FUNCTION TO REUNIFY GENERAL REFERENCE BLOCKS 

-----------------------------------------------



-- use make2db_reunit_refs(0) to reunify all articles, otherwise pass the article id



   DECLARE



            my_article ALIAS for $1;  

            my_record RECORD;

            my_record2 RECORD;

            my_record3 RECORD;

            my_max_author_group_priority INT2;

            my_author_group_string VARCHAR(2048);

            my_max_author_priority INT2;

            my_author_string VARCHAR(8192);

            my_rx_array TEXT;

            ii INT2;

            void_operation BOOLEAN;





   BEGIN



      void_operation := 'false';



      IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_make2db_reunit_refs') -- for postgres 7.3+ use schema (buffer.)

      THEN

        DELETE FROM buffer_make2db_reunit_refs; -- No need to lock table (serializable level is set on)

      ELSE

        CREATE TEMPORARY TABLE buffer_make2db_reunit_refs 

              ( referenceID INT4 UNIQUE,  referenceWorkDescription VARCHAR(64),

                crossReferences VARCHAR(256), authorsGroup VARCHAR(2048), authors VARCHAR(8192), referenceTitle TEXT, referenceLocation TEXT

               );



      END IF;





      IF my_article = 0

      THEN

          INSERT INTO buffer_make2db_reunit_refs (referenceID) SELECT referenceID FROM  Reference ORDER BY 1;

      ELSE

          INSERT INTO buffer_make2db_reunit_refs (referenceID) SELECT  referenceID FROM  Reference WHERE Reference.referenceID = my_article;

      END IF; 





      FOR my_record IN



            SELECT referenceID FROM buffer_make2db_reunit_refs ORDER BY 1 



      LOOP



            UPDATE buffer_make2db_reunit_refs SET referenceWorkDescription = ReferenceWork.referenceWorkDescription || '.'

                   FROM Reference, ReferenceWork

                   WHERE Reference.referenceID = my_record.referenceID AND ReferenceWork.referenceWorkID = Reference.referenceWorkID 

                   AND buffer_make2db_reunit_refs.referenceID = my_record.referenceID;



            IF  EXISTS (SELECT referenceID FROM ReferenceLocationJournal WHERE referenceID = my_record.referenceID)

            THEN

                my_rx_array := '';

                SELECT INTO my_record2 ReferenceLocationJournal.crossReferences FROM ReferenceLocationJournal WHERE ReferenceLocationJournal.referenceID = my_record.referenceID;

		-- Since postgreSQL 7.4: use Numerical array_upper and array_lower rather than array_dims

		SELECT INTO my_record3 array_upper(ReferenceLocationJournal.crossReferences, 1) AS rx_upper FROM ReferenceLocationJournal

                       WHERE ReferenceLocationJournal.referenceID = my_record.referenceID;

      		FOR ii IN 1..my_record3.rx_upper -- for postgres 7.4 and higher

		-- FOR ii IN 1..5

                LOOP

                      IF my_record2.crossReferences[ii][2] IS NOT NULL and my_record2.crossReferences[ii][2] <> '0' THEN

                            my_rx_array := my_rx_array ||  my_record2.crossReferences[ii][1] || '=' || my_record2.crossReferences[ii][2] || '; ';

                      END IF;

                END LOOP;

		IF (length(my_rx_array) > 0) THEN

		  my_rx_array = substr(my_rx_array, 0, length(my_rx_array));

		END IF;

                UPDATE buffer_make2db_reunit_refs SET crossReferences = my_rx_array WHERE buffer_make2db_reunit_refs.referenceID = my_record.referenceID;

            END IF;



            my_max_author_group_priority := max(authorPriority) FROM ONLY AuthorGroup WHERE article = my_record.referenceID;

            my_author_group_string := '';

	    IF my_max_author_group_priority > 0 THEN

              FOR ii IN 1..my_max_author_group_priority

              LOOP

                    FOR my_record2 IN

                          SELECT author FROM ONLY AuthorGroup WHERE AuthorGroup.article = my_record.referenceID AND AuthorGroup.authorPriority = ii

                    LOOP

                          my_author_group_string := my_author_group_string || my_record2.author || ' & ';

                    END LOOP;

              END LOOP;

              IF my_author_group_string <> '' THEN

                    my_author_group_string = substr(my_author_group_string, 0, length(my_author_group_string)-2) || ';';

              END IF;

              UPDATE buffer_make2db_reunit_refs SET authorsGroup = my_author_group_string WHERE buffer_make2db_reunit_refs.referenceID = my_record.referenceID;

            END IF;



            my_max_author_priority := max(authorPriority) FROM ONLY Author WHERE article = my_record.referenceID;

            my_author_string := '';

            FOR ii IN 1..my_max_author_priority

            LOOP

                  FOR my_record2 IN

                        SELECT author FROM ONLY Author WHERE Author.article = my_record.referenceID AND Author.authorPriority = ii

                  LOOP

                        my_author_string := my_author_string || my_record2.author || ', ';

                  END LOOP;

            END LOOP;

            my_author_string = substr(my_author_string, 0, length(my_author_string)-1) || ';';

            UPDATE buffer_make2db_reunit_refs SET authors = my_author_string WHERE buffer_make2db_reunit_refs.referenceID = my_record.referenceID;



            UPDATE buffer_make2db_reunit_refs SET referenceTitle = '''''' || Reference.referenceTitle || ''''';'

                           FROM Reference

                           WHERE Reference.referenceID = my_record.referenceID AND buffer_make2db_reunit_refs.referenceID = my_record.referenceID;

            UPDATE buffer_make2db_reunit_refs SET referenceLocation = make2db_rl_reconstruct(my_record.referenceID,'')

                           WHERE buffer_make2db_reunit_refs.referenceID = my_record.referenceID;



      END LOOP;









      IF NOT EXISTS (SELECT * FROM buffer_make2db_reunit_refs LIMIT 1) THEN

        void_operation := 'true';

        RAISE NOTICE 'No references found to fill in ViewRef.';

      ELSE

        void_operation := 'false';

      END IF;





      -- Move the buffer table to the permanent one:

      -- EXECUTE statements do not support SELECT yet!! Till then, we adopt this locked *concurrential* solution

      -->(eachs exectuion deletes the result of the previous one!!)



      IF my_article = 0

      THEN  

        IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'viewref')

        THEN

          LOCK TABLE ViewRef IN ACCESS EXCLUSIVE MODE; -- Any concurrential transaction will wait for this one

          DELETE FROM ViewRef;

          INSERT INTO ViewRef SELECT * FROM buffer_make2db_reunit_refs ORDER by 1;

        ELSE

          CREATE TABLE ViewRef AS SELECT * FROM buffer_make2db_reunit_refs ORDER by 1; 

          CREATE INDEX ViewRef_referenceID_idx ON ViewRef(referenceID);

          COMMENT ON TABLE ViewRef IS

           'THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)';

          GRANT SELECT ON ViewRef TO select2d;

        END IF;

    --  ANALYZE ViewRef; -- KHM (server shuts down in mordor!!)



      ELSE

        IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'viewreflast')

        THEN

          LOCK TABLE ViewRefLast IN ACCESS EXCLUSIVE MODE;

          DELETE FROM ViewRefLast;

          INSERT INTO ViewRefLast SELECT * FROM buffer_make2db_reunit_refs;

        ELSE

          CREATE TABLE ViewRefLast AS SELECT * FROM buffer_make2db_reunit_refs;

          COMMENT ON TABLE ViewRefLast IS

           'THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)';

          GRANT SELECT ON ViewRefLast TO select2d;

        END IF;



      END IF; 



      DELETE FROM buffer_make2db_reunit_refs;







      IF (void_operation) THEN

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: make2db_reunit_refs_ac( character varying )

Returns: text

Language: PLPGSQL

FUNCTION TO REUNIFY REFERENCE BLOCKS FOR A GIVEN ENTRY





-- FUNCTION TO REUNIFY REFERENCE BLOCKS FOR A GIVEN ENTRY 

---------------------------------------------------------



   DECLARE



            my_ac ALIAS for $1;  

            my_record RECORD;

            my_record2 RECORD;

            my_ref_blocks TEXT;

            ii INT2;

 

   BEGIN 



      my_ref_blocks = '';

      FOR my_record IN



            SELECT ReferencedEntry.referenceID, ReferencedEntry.RNdisplayedValue FROM ReferencedEntry WHERE ReferencedEntry.AC::VARCHAR(32) = my_ac ORDER BY 2



      LOOP



            SELECT INTO my_record2 referenceWorkDescription, crossReferences, authorsGroup, authors, referenceTitle, referenceLocation FROM ViewRef

                   WHERE referenceID = my_record.referenceID;

            IF my_ref_blocks <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

';

            END IF;

            my_ref_blocks := my_ref_blocks || 'RN   [' || my_record.RNdisplayedValue || ']

RP   ' || my_record2.referenceWorkDescription;

            IF my_record2.crossReferences <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

RX   ' || my_record2.crossReferences;

            END IF;

            IF my_record2.authorsGroup <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

RG   ' || my_record2.authorsGroup;

            END IF;

            my_ref_blocks := my_ref_blocks || '

RA   ' || my_record2.authors;

            IF my_record2.referenceTitle <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

RT   ' || my_record2.referenceTitle;

            END IF;

            my_ref_blocks := my_ref_blocks || '

RL   ' || my_record2.referenceLocation;



            UPDATE ViewRef SET referenceWorkDescription = ReferenceWork.referenceWorkDescription || '.'

                           FROM Reference, ReferenceWork

                           WHERE Reference.referenceID = my_record.referenceID AND ReferenceWork.referenceWorkID = Reference.referenceWorkID

                           AND ViewRef.referenceID = my_record.referenceID;





 

      END LOOP; 



   RETURN my_ref_blocks;





   END;

Function: make2db_reunit_spots( character varying )

Returns: boolean

Language: PLPGSQL

FUNCTION TO REUNIFY ENTRIES 1/2D BLOCKS





-- FUNCTION TO REUNIFY ENTRIES 1/2D BLOCKS

------------------------------------------



   DECLARE



            my_ac ALIAS for $1;

            my_record RECORD;

            my_record2 RECORD;

            my_record3 RECORD;

            my_max_version INT4;

            my_amino_acids TEXT;

            my_msms TEXT;

            my_parentIon_mass FLOAT;

            my_parentIon_charge INT2;

            my_chargeSign VARCHAR(32);

            my_parentIon_text TEXT;

            my_peptide_sequences TEXT;

            my_peptide_sequences_subset TEXT;

            my_peptide_masses TEXT;

            my_array_upper_dim INT4;

            my_enzyme TEXT;

            

            my_last_data_doc TEXT;

            my_last_data_doc_uri TEXT;

            my_last_identification_doc TEXT;

            my_last_identification_doc_uri TEXT;



            my_show_flag BOOLEAN;

            void_operation BOOLEAN;





   BEGIN



      void_operation := 'false';



      my_show_flag := 'true';



      IF my_ac = '' THEN RETURN 'false';

      END IF;



      IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_make2db_reunit_spots')

      THEN

        DELETE FROM buffer_make2db_reunit_spots; -- No need to lock table (serializable level is set on)

      ELSE

      CREATE TEMPORARY TABLE buffer_make2db_reunit_spots

            ( spotID VARCHAR(16), gelID INT4, AC VARCHAR(32),

              fragment BOOLEAN,

              -- Specific data sets are separated by a line feed / version is the maximum version

              AminoAcidList TEXT DEFAULT NULL,

              aa_version INT4 DEFAULT NULL,

              msms TEXT DEFAULT NULL,

              ms_version INT4 DEFAULT NULL,

              peptideSequences TEXT DEFAULT NULL,

              peptseq_version INT4 DEFAULT NULL,

              --peptideMasses FLOAT[][] DEFAULT NULL,

              --enzyme VARCHAR(32) DEFAULT NULL,

              peptideMasses TEXT DEFAULT NULL,

              pmf_version INT4 DEFAULT NULL,

              showFlag BOOLEAN NOT NULL DEFAULT 'true',

              showFlagSwitch BOOLEAN NOT NULL DEFAULT 'true'

                CHECK ((showFlag IS TRUE AND showFlagSwitch IS TRUE) OR showFlag IS NOT TRUE),

              UNIQUE (spotID, gelID, AC)

             );



      END IF;





      IF my_ac = 'all'

      THEN

          INSERT INTO buffer_make2db_reunit_spots (spotID, gelID, AC, fragment, showFlag) SELECT spotID, gelID, AC, fragment, showFlag

                 FROM SpotEntry WHERE SpotEntry.showFlagSwitch IS TRUE ORDER BY 1;

      ELSE

          INSERT INTO buffer_make2db_reunit_spots (spotID, gelID, AC, fragment, showFlag) SELECT spotID, gelID, AC, fragment, showFlag

                 FROM SpotEntry WHERE SpotEntry.AC::VARCHAR(32) = my_ac AND SpotEntry.showFlagSwitch IS TRUE ORDER BY 1;

      END IF;







      FOR my_record IN

          SELECT spotID, gelID, AC FROM buffer_make2db_reunit_spots ORDER BY 1 

      LOOP





            -- Amino Acid Composition part --

            -- should we allow to add more than one record, or just one from the last annotated entry version? --



            my_max_version := max(version) FROM SpotIdentificationAAcid WHERE spotID = my_record.spotID AND gelID = my_record.gelID

                                              AND AC = my_record.AC;

            IF my_max_version IS NOT NULL

            THEN

               my_amino_acids = '';

               FOR my_record2 IN

                 SELECT SpotIdentificationAAcid.AminoAcidList, SpotIdentificationAAcid.showFlag

                 FROM SpotIdentificationAAcid

                 WHERE SpotIdentificationAAcid.spotID = my_record.spotID AND SpotIdentificationAAcid.gelID = my_record.gelID

                 AND SpotIdentificationAAcid.AC = my_record.AC

                 -- AND SpotIdentificationAAcid.version = my_max_version 

                 ORDER BY SpotIdentificationAAcid.version DESC

               LOOP

                 my_amino_acids := my_amino_acids || my_record2.AminoAcidList ;

                 IF (my_record2.showFlag IS FALSE AND length(my_amino_acids) > 0) THEN

                   my_amino_acids := my_amino_acids || ' {private}';

                 END IF;

                 my_amino_acids := my_amino_acids || '

';

               END LOOP;

               IF (length(my_amino_acids) > 0) THEN -- erase the last 



                 my_amino_acids := substr(my_amino_acids, 0, length(my_amino_acids));

               END IF;



               UPDATE buffer_make2db_reunit_spots SET AminoAcidList = my_amino_acids, aa_version = my_max_version

                 WHERE buffer_make2db_reunit_spots.spotID = my_record.spotID AND buffer_make2db_reunit_spots.gelID = my_record.gelID

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

            END IF;





            -- Mass Spectrometry part (Tandem MS) --

            -- should we allow to add more than one record of the last annotated entry version? --

            -- no direct relation is established between the subsets (peptides sequences) and the MS data for this view



            my_max_version := max(version) FROM SpotIdentificationTandemMS WHERE spotID = my_record.spotID AND gelID = my_record.gelID

                                              AND AC = my_record.AC;

            IF my_max_version IS NOT NULL

            THEN

               my_msms := '';

               my_last_data_doc := '';

               my_last_data_doc_uri := '';

               my_last_identification_doc := '';

               my_last_identification_doc_uri := '';

               FOR my_record2 IN

                 SELECT SpotIdentificationTandemMS.ionMasses, SpotIdentificationTandemMS.dataExpID,

                 SpotIdentificationTandemMS.showFlag, SpotIdentificationTandemMS.allExperimentData,

                 SpotIdentificationTandemMS.identificationDocument, SpotIdentificationTandemMS.URI

                 FROM SpotIdentificationTandemMS

                 WHERE SpotIdentificationTandemMS.spotID = my_record.spotID AND SpotIdentificationTandemMS.gelID = my_record.gelID

                 AND SpotIdentificationTandemMS.AC = my_record.AC AND SpotIdentificationTandemMS.ionMasses IS NOT NULL

                 -- AND SpotIdentificationTandemMS.version = my_max_version 

                 ORDER BY SpotIdentificationTandemMS.version, SpotIdentificationTandemMS.identificationDocument,

                   SpotIdentificationTandemMS.URI DESC

               LOOP

                 my_parentIon_mass := 0.0;

                 my_parentIon_charge := 0;

                 SELECT INTO my_record3 parentMass, parentCharge, ionMasses, URI, dataDocument FROM SpotDataTandemMS

                   WHERE SpotDataTandemMS.dataExpID = my_record2.dataExpID

                   AND SpotDataTandemMS.spotID = my_record.spotID

                   AND SpotDataTandemMS.gelID = my_record.gelID

                   LIMIT 1;

                 my_array_upper_dim = array_upper(my_record2.ionMasses,1);

                 IF ( (my_record3.URI <> my_last_data_doc_uri OR my_record3.dataDocument <> my_last_data_doc OR 

                       my_record2.URI <> my_last_identification_doc_uri OR my_record2.identificationDocument <> my_last_identification_doc)

                     AND

                      (my_record3.URI IS NOT NULL OR my_record3.dataDocument IS NOT NULL OR my_record2.URI IS NOT NULL OR my_record2.identificationDocument IS NOT NULL)

                    ) THEN

                   my_msms := my_msms || ' [Documents] ';

                   IF (my_record3.URI IS NOT NULL) THEN

                     my_msms := my_msms || ' Data Document URI: ' || my_record3.URI; 

                     my_last_data_doc_uri := my_record3.URI;

                   END IF;

                   IF (my_record3.dataDocument IS NOT NULL) THEN

                     my_msms := my_msms || ' Local Data Document: ' || my_record3.dataDocument; 

                     my_last_data_doc := my_record3.dataDocument;

                   END IF;

                   IF (my_record2.URI IS NOT NULL) THEN

                     my_msms := my_msms || ' Identification Document URI: ' || my_record2.URI;

                     my_last_identification_doc_uri := my_record2.URI;

                   END IF;

                   IF (my_record2.identificationDocument IS NOT NULL) THEN

                     my_msms := my_msms || ' Local Identification Document: ' || my_record2.identificationDocument;

                     my_last_identification_doc := my_record2.identificationDocument;

                   END IF;

                   my_msms := my_msms  || '

';

                 END IF;

                 IF (my_array_upper_dim > 0) THEN

                   IF (my_record3.parentCharge IS NOT NULL) OR (my_record3.parentMass > 0) THEN

                     my_parentIon_text := '';

                     IF (my_record3.parentMass > 0) THEN

                       my_parentIon_text := my_record3.parentMass::TEXT;

                     END IF;

                     IF (my_record3.parentCharge IS NOT NULL) THEN

                       IF (length(my_parentIon_text) > 0) THEN my_parentIon_text := my_parentIon_text || ':'; END IF;

                       my_chargeSign = ' -> charge not reported';

                       IF (my_record3.parentCharge >0) THEN my_chargeSign = '+';

                       ELSE IF (my_record3.parentCharge <0) THEN my_chargeSign = '-';

                       END IF; END IF;

                       my_parentIon_charge = abs(my_record3.parentCharge)::TEXT;

                       my_parentIon_text := my_parentIon_text || my_parentIon_charge || my_chargeSign;

                     END IF;

                     my_msms :=  my_msms || '[' || my_parentIon_text || '] ';

                   END IF;

                   FOR ii in 1..my_array_upper_dim

                   LOOP

                     -- we skip zero values (0.0 by convention is for undefined data)

                     IF (my_record2.ionMasses[ii][1] > 0 OR my_record2.ionMasses[ii][2] > 0) THEN

                       my_msms := my_msms || my_record2.ionMasses[ii][1];

                       IF (my_record2.ionMasses[ii][2] > 0) THEN

                         my_msms := my_msms || ' (' || my_record2.ionMasses[ii][2] || ')';

                       END IF;

                       my_msms := my_msms || '; ';

                     END IF;

                   END LOOP;

                   my_array_upper_dim = array_upper(my_record3.ionMasses,1);

                   IF (my_record2.allExperimentData IS FALSE AND my_array_upper_dim > 0) THEN

                     my_msms := my_msms || ' All Experiment Data: ';

                     FOR ii in 1..my_array_upper_dim

                     LOOP

                       my_msms := my_msms || my_record3.ionMasses[ii][1];

                       IF (my_record3.ionMasses[ii][2] > 0) THEN

                         my_msms := my_msms || ' (' || my_record3.ionMasses[ii][2] || ')';

                       END IF;

                       my_msms := my_msms  || '; ';

                     END LOOP;

                   END IF;

                 END IF;

                 IF (my_record2.showFlag IS FALSE AND length(my_msms) > 0) THEN

                   my_msms := my_msms || ' {private}';

                 END IF;

                 IF (length(my_msms) < 3) THEN my_msms:= '--'; END IF;

                 my_msms := my_msms || '

'; 

               END LOOP;

               IF (length(my_msms) > 0) THEN -- erase the last 



                 my_msms := substr(my_msms, 0, length(my_msms));

               ELSE

                 my_msms = NULL;

               END IF;



               UPDATE buffer_make2db_reunit_spots SET msms = my_msms, ms_version = my_max_version

                 WHERE buffer_make2db_reunit_spots.spotID = my_record.spotID AND buffer_make2db_reunit_spots.gelID = my_record.gelID

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

               my_msms := '';

            END IF;





            -- Peptide Sequences (sub-set of Tandem MS) --

            -- should we allow to add more than one record from the last annotated entry version? --



            my_max_version := max(version) FROM SpotIdentificationTandemMS WHERE spotID = my_record.spotID AND gelID = my_record.gelID

                                              AND AC = my_record.AC AND SpotIdentificationTandemMS.hasSubset IS TRUE;

            IF my_max_version IS NOT NULL -- identifiedPeptideSequence

            THEN

               my_peptide_sequences := '';

               FOR my_record2 IN

                 SELECT SpotIdentificationTandemMS.identificationID, SpotIdentificationTandemMS.showFlag

                 FROM SpotIdentificationTandemMS

                 WHERE SpotIdentificationTandemMS.spotID = my_record.spotID AND SpotIdentificationTandemMS.gelID = my_record.gelID

                 AND SpotIdentificationTandemMS.AC = my_record.AC AND SpotIdentificationTandemMS.hasSubset IS TRUE

                 -- AND SpotIdentificationTandemMS.version = my_max_version 

                 ORDER BY SpotIdentificationTandemMS.version DESC

               LOOP



                 my_peptide_sequences_subset := '';

                 FOR my_record3 IN

                  SELECT SpotIdentificationTandemMSSubset.identifiedPeptideSequence, SpotIdentificationTandemMSSubset.sequenceStartPosition,

                         SpotIdentificationTandemMSSubset.sequenceENDPosition

                  FROM SpotIdentificationTandemMSSubset

                  WHERE SpotIdentificationTandemMSSubset.identificationID = my_record2.identificationID

                  ORDER BY 2,3,1

                 LOOP

                   my_peptide_sequences_subset := my_peptide_sequences_subset || my_record3.identifiedPeptideSequence;

                   IF (my_record3.sequenceStartPosition > 0 AND my_record3.sequenceEndPosition > 0)

                   THEN

                     my_peptide_sequences_subset := my_peptide_sequences_subset || ',' ||

                                                    CAST(my_record3.sequenceStartPosition AS TEXT) || '-' || CAST(my_record3.sequenceENDPosition AS TEXT);

                   END IF;

                   my_peptide_sequences_subset := my_peptide_sequences_subset || '; ';

                 END LOOP;



                 IF (length(my_peptide_sequences_subset) > 0) THEN

                   my_peptide_sequences_subset := substr(my_peptide_sequences_subset, 0, length(my_peptide_sequences_subset)-1);

                   IF my_record2.showFlag IS FALSE THEN

                      my_peptide_sequences_subset := my_peptide_sequences_subset || ' {private}';

                   END IF;

                   my_peptide_sequences := my_peptide_sequences || my_peptide_sequences_subset || '

';

                 END IF;



               END LOOP;



               IF (length(my_peptide_sequences) > 0) THEN -- erase the last 



                 my_peptide_sequences := substr(my_peptide_sequences, 0, length(my_peptide_sequences));

               END IF;



               UPDATE buffer_make2db_reunit_spots SET peptideSequences = my_peptide_sequences, peptseq_version = my_max_version

                 WHERE buffer_make2db_reunit_spots.spotID = my_record.spotID AND buffer_make2db_reunit_spots.gelID = my_record.gelID

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

            END IF;





            -- Peptide Masses part --

            -- should we allow to add more than one record of the last annotated entry version? --



            my_max_version := max(version) FROM SpotIdentificationPeptMassF WHERE spotID = my_record.spotID AND gelID = my_record.gelID

                                              AND AC = my_record.AC;

            IF my_max_version IS NOT NULL

            THEN

               my_peptide_masses := '';

               my_msms := '';

               my_last_data_doc := '';

               my_last_data_doc_uri := '';

               my_last_identification_doc := '';

               my_last_identification_doc_uri := '';

               FOR my_record2 IN

                 SELECT SpotIdentificationPeptMassF.peptideMasses, SpotIdentificationPeptMassF.dataExpID,

                 SpotIdentificationPeptMassF.showFlag, SpotIdentificationPeptMassF.allExperimentData,

                 SpotIdentificationPeptMassF.identificationDocument, SpotIdentificationPeptMassF.URI

                 FROM SpotIdentificationPeptMassF

                 WHERE SpotIdentificationPeptMassF.spotID = my_record.spotID AND SpotIdentificationPeptMassF.gelID = my_record.gelID

                 AND SpotIdentificationPeptMassF.AC = my_record.AC

                 -- AND SpotIdentificationPeptMassF.version = my_max_version 

                 ORDER BY SpotIdentificationPeptMassF.version, SpotIdentificationPeptMassF.identificationDocument,

                   SpotIdentificationPeptMassF.URI DESC

               LOOP

                 my_enzyme := '';

                 SELECT INTO my_record3 enzyme, peptideMasses, URI, dataDocument FROM SpotDataPeptMassF

                   WHERE SpotDataPeptMassF.dataExpID = my_record2.dataExpID

                   AND SpotDataPeptMassF.spotID = my_record.spotID

                   AND SpotDataPeptMassF.gelID = my_record.gelID

                   LIMIT 1;

                 IF (length(my_record3.enzyme ) > 0) THEN

                   my_enzyme := my_record3.enzyme;

                 ELSE

                   my_enzyme := '(Undefined Enzyme)';

                 END IF;

                 my_array_upper_dim = array_upper(my_record2.peptideMasses,1);

                 IF ( (my_record3.URI <> my_last_data_doc_uri OR my_record3.dataDocument <> my_last_data_doc OR 

                       my_record2.URI <> my_last_identification_doc_uri OR my_record2.identificationDocument <> my_last_identification_doc)

                     AND

                      (my_record3.URI IS NOT NULL OR my_record3.dataDocument IS NOT NULL OR my_record2.URI IS NOT NULL OR my_record2.identificationDocument IS NOT NULL)

                    ) THEN

                   my_peptide_masses := my_peptide_masses || ' [Documents] ';

                   IF (my_record3.URI IS NOT NULL) THEN

                     my_peptide_masses := my_peptide_masses || ' Data Document URI: ' || my_record3.URI; 

                     my_last_data_doc_uri := my_record3.URI;

                   END IF;

                   IF (my_record3.dataDocument IS NOT NULL) THEN

                     my_peptide_masses := my_peptide_masses || ' Local Data Document: ' || my_record3.dataDocument; 

                     my_last_data_doc := my_record3.dataDocument;

                   END IF;

                   IF (my_record2.URI IS NOT NULL) THEN

                     my_peptide_masses := my_peptide_masses || ' Identification Document URI: ' || my_record2.URI; 

                     my_last_identification_doc_uri := my_record2.URI;

                   END IF;

                   IF (my_record2.identificationDocument IS NOT NULL) THEN

                     my_peptide_masses := my_peptide_masses || ' Local Identification Document: ' || my_record2.identificationDocument;

                     my_last_identification_doc := my_record2.identificationDocument;

                   END IF;

                   my_peptide_masses := my_peptide_masses  || '

';

                 END IF;

                 IF (my_array_upper_dim > 0) THEN

                   FOR ii in 1..my_array_upper_dim

                   LOOP

                     -- we skip zero values (0.0 by convention is for undefined data)

                     IF (my_record2.peptideMasses[ii][1] > 0 OR my_record2.peptideMasses[ii][2] > 0) THEN

                       my_peptide_masses := my_peptide_masses || my_record2.peptideMasses[ii][1];

                     END IF;

                     IF (my_record2.peptideMasses[ii][2] > 0) THEN

                       my_peptide_masses := my_peptide_masses || ' (' || my_record2.peptideMasses[ii][2] || ')';

                     END IF;

                     my_peptide_masses := my_peptide_masses  || '; ';

                   END LOOP;

                   my_array_upper_dim = array_upper(my_record3.peptideMasses,1);

                   IF (my_record2.allExperimentData IS FALSE AND my_array_upper_dim > 0) THEN

                     my_peptide_masses := my_peptide_masses || ' All Experiment Data: ';

                     FOR ii in 1..my_array_upper_dim

                     LOOP

                       my_peptide_masses := my_peptide_masses || my_record3.peptideMasses[ii][1];

                       IF (my_record3.peptideMasses[ii][2] > 0) THEN

                         my_peptide_masses := my_peptide_masses || ' (' || my_record3.peptideMasses[ii][2] || ')';

                       END IF;

                       my_peptide_masses := my_peptide_masses  || '; ';

                     END LOOP;

                   END IF;

                   IF (length(my_peptide_masses) > 2) THEN

                     my_peptide_masses := my_peptide_masses || my_record3.enzyme;

                   END IF;

                 END IF;

                 IF (my_record2.showFlag IS FALSE AND length(my_peptide_masses) > 0) THEN

                   my_peptide_masses := my_peptide_masses || ' {private}';

                 END IF;

                 IF (length(my_peptide_masses) < 3) THEN my_peptide_masses := '--'; END IF;

                 my_peptide_masses := my_peptide_masses || '

'; 

               END LOOP;

               IF (length(my_peptide_masses) > 0) THEN -- erase the last 



                 my_peptide_masses := substr(my_peptide_masses, 0, length(my_peptide_masses)); 

               END IF;



               UPDATE buffer_make2db_reunit_spots SET peptideMasses = my_peptide_masses, pmf_version = my_max_version

                 WHERE buffer_make2db_reunit_spots.spotID = my_record.spotID AND buffer_make2db_reunit_spots.gelID = my_record.gelID

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

            END IF;





      END LOOP; 









      IF NOT EXISTS (SELECT * FROM buffer_make2db_reunit_spots LIMIT 1) THEN

        void_operation := 'true';

        RAISE NOTICE 'No spots data to fill in ViewSpotEntry.';

      ELSE

        void_operation := 'false';

      END IF;





      -- Move the buffer table to the permanent one:

      -- EXECUTE statements do not support SELECT yet!! Till then, we adopt this locked *concurrential* solution

      -->(each exectuion deletes the result of the previous one!!)



      IF my_ac = 'all'

      THEN

        IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'viewspotentry')

        THEN

          LOCK TABLE ViewSpotEntry IN ACCESS EXCLUSIVE MODE; -- Any concurrential transaction will wait for this one

          DELETE FROM ViewSpotEntry;

          INSERT INTO ViewSpotEntry SELECT * FROM buffer_make2db_reunit_spots ORDER by 1;

        ELSE

          CREATE TABLE ViewSpotEntry AS SELECT * FROM buffer_make2db_reunit_spots ORDER by 1; 

          CREATE INDEX ViewSpotEntry_spotID_idx ON ViewSpotEntry(spotID);

          CREATE INDEX ViewSpotEntry_gelID_idx ON ViewSpotEntry(gelID);

          CREATE INDEX ViewSpotEntry_ac_idx ON ViewSpotEntry(AC);

          COMMENT ON TABLE ViewSpotEntry IS

           'THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)';

          COMMENT ON COLUMN ViewSpotEntry.AminoAcidList IS 'Specific data sets are separated by 

';

          COMMENT ON COLUMN ViewSpotEntry.msms IS 'Specific data sets are separated by 

';

          COMMENT ON COLUMN ViewSpotEntry.peptideSequences IS 'Specific data sets are separated by 

';

          COMMENT ON COLUMN ViewSpotEntry.peptideMasses IS 'Specific data sets are separated by 

';

          GRANT SELECT ON ViewSpotEntry TO select2d;

        END IF;

      -- ANALYZE ViewSpotEntry; -- KHM (server shuts down in mordor!!)



      ELSE

        IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'viewspotentrylast')

        THEN

          LOCK TABLE ViewSpotEntryLast IN ACCESS EXCLUSIVE MODE;

          DELETE FROM ViewSpotEntryLast;

          INSERT INTO ViewSpotEntryLast SELECT * FROM buffer_make2db_reunit_spots;

        ELSE

          CREATE TABLE ViewSpotEntryLast AS SELECT * FROM buffer_make2db_reunit_spots;

          COMMENT ON TABLE ViewSpotEntryLast IS

           'THIS IS A VIEW-LIKE GENERATED TABLE INTENDED TO REPRESENT SOME DATA VIEWS! (NOT TO BE DIRECTLY EDITED)';

          GRANT SELECT ON ViewSpotEntryLast TO select2d;

        END IF; 

        DELETE FROM ViewSpotEntry WHERE ac::VARCHAR(32) = my_ac;

        INSERT INTO ViewSpotEntry (SELECT * FROM buffer_make2db_reunit_spots);



      END IF;



      DELETE FROM buffer_make2db_reunit_spots;







      IF (void_operation) THEN

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: make2db_rl_reconstruct( integer, character varying )

Returns: text

Language: PLPGSQL

FUNCTION TO CONSTRUCT THE REFERENCE LOCATION (RL LINE)





-- FUNCTION TO CONSTRUCT THE REFERENCE LOCATION (RL LINE)

---------------------------------------------------------



  DECLARE



      rl_id ALIAS for $1;

      table_type_input ALIAS for $2;

      table_type VARCHAR(32);

      my_record RECORD;

      my_record2 RECORD;

      my_rl_line TEXT;



      my_max_citer_priority INT2;

      my_citer_string VARCHAR(800);

      my_day INT2;

      my_month INT2;

      my_month_letter TEXT;

      my_year INT2;



  BEGIN



      SET DATESTYLE TO 'POSTGRES, EUROPEAN';



      table_type := table_type_input;



     /* if table_type is not given */

      IF table_type = '' 

      THEN

            IF EXISTS (SELECT referenceID FROM ReferenceLocationJournal WHERE referenceID = rl_id AND unpublished = 'false')

            THEN table_type := 'Journal';

            ELSE IF EXISTS (SELECT referenceID FROM ReferenceLocationJournal WHERE referenceID = rl_id AND unpublished = 'true')

            THEN table_type := 'Unpublished_results';

            ELSE IF EXISTS (SELECT referenceID FROM ReferenceLocationBook WHERE referenceID = rl_id)

            THEN table_type := 'Book';

            ELSE IF EXISTS (SELECT referenceID FROM ReferenceLocationUnpubObservations

                            WHERE referenceID = rl_id)

            THEN table_type := 'Unpublished observations';

            ELSE IF EXISTS (SELECT referenceLocationThesis.referenceID FROM referenceLocationThesis WHERE referenceID = rl_id)

            THEN table_type := 'Thesis';

            ELSE IF EXISTS (SELECT referenceID FROM referenceLocationPatent WHERE referenceID = rl_id)

            THEN table_type := 'Patent application';

            ELSE IF EXISTS (SELECT referenceID FROM referenceLocationSubmission WHERE referenceID = rl_id)

            THEN table_type := 'Submission';

            ELSE IF EXISTS (SELECT referenceID FROM referenceLocationOther WHERE referenceID = rl_id)

            THEN table_type := 'OTHER';

            END IF;	

            END IF;

            END IF;

            END IF;

            END IF;

            END IF;

            END IF;

            END IF;



      END IF;







     /* Category is Journal / Unpublished results */



      IF table_type = 'Journal' OR table_type = 'Unpublished results'

      THEN

            SELECT INTO my_record Journal.journalName, Journal.url,

                        ReferenceLocationJournal.volume, ReferenceLocationJournal.subVolume, ReferenceLocationJournal.pageFirst, ReferenceLocationJournal.pageLast,

                        ReferenceLocationJournal.year, ReferenceLocationJournal.unpublished



                   FROM ReferenceLocationJournal, Journal

                   WHERE ReferenceLocationJournal.referenceID = rl_id AND Journal.journalID = ReferenceLocationJournal.journalID;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN rl_journal', rl_id;

                  RETURN '!ERROR: NO REF. % in the journal / unpublished results table!';

            END IF;



            my_rl_line := my_record.journalName || ' ' || my_record.volume;

            IF my_record.subVolume IS NOT NULL

            THEN

              my_rl_line := my_rl_line || '(' || my_record.subVolume || ')';

            END IF;

            my_rl_line := my_rl_line || ':' || my_record.pageFirst || '-'

                                     || my_record.pageLast || '(' || my_record.year || ').';



            IF my_record.unpublished = 'true' THEN 

                  my_max_citer_priority = max(authorPriority) FROM Citer WHERE article = rl_id;            

                  my_citer_string := '';

                  FOR ii IN 1..my_max_citerPriority

                  LOOP

                        FOR my_record2 IN

                              SELECT author FROM Citer WHERE Citer.article = rl_id AND Citer.authorPriority = ii

                        LOOP

                              my_citer_string := my_citer_string || my_record2.author || ', ';

                        END LOOP;

                  END LOOP;

                  my_citer_string = substr(my_citer_string, '', length(my_citer_string)-1) || ';';

                  my_rl_line := 'Unpublished results, cited by:

RL   ' || my_citer_string || '

' || my_rl_line;

            END IF;







     /* Category is Book */



      ELSE IF table_type = 'Book'

      THEN

            SELECT INTO my_record Book.bookTitle, Book.editors, Book.publisher,

                        Book.city, Book.year, ReferenceLocationBook.volume, ReferenceLocationBook.pageFirst, ReferenceLocationBook.pageLast

                   FROM ReferenceLocationBook, Book

                   WHERE ReferenceLocationBook.referenceID = rl_id AND Book.bookID = ReferenceLocationBook.bookID;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN rl_book', rl_id;

                  RETURN '!ERROR: NO REF. % in the book table!';

            END IF;



            my_rl_line := '(IN) ' || my_record.editors || ' (eds.);

RL   ' || my_record.bookTitle || ', pp.' ||

                          my_record.pageFirst || '-' || my_record.pageLast || ', ' || my_record.publisher ||

                          ' (' || my_record.year || ').';







     /* Category is Unpublished observations */



      ELSE IF table_type = 'Unpublished observations'

      THEN

            SELECT INTO my_record ReferenceLocationUnpubObservations.month, ReferenceLocationUnpubObservations.year

                   FROM ReferenceLocationUnpubObservations

                   WHERE ReferenceLocationUnpubObservations.referenceID = rl_id;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN ReferenceLocationUnpubObservations', rl_id;

                  RETURN '!ERROR: NO REF. % in the unpublished observations table!';

            END IF;



            my_rl_line := 'Unpublished obseravtions ('|| get_month(my_record.month) || '- ' ||my_record.year || ').';







     /* Category is Thesis */



      ELSE IF table_type = 'Thesis'

      THEN

            SELECT INTO my_record referenceLocationThesis.year, referenceLocationThesis.institution, referenceLocationThesis.country

                   FROM referenceLocationThesis

                   WHERE referenceLocationThesis.referenceID = rl_id;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN referenceLocationThesis', rl_id;

                  RETURN '!ERROR: NO REF. % in the thesis table!';

            END IF;



            my_rl_line := 'Thesis (' || my_record.year || '), ' || my_record.institution || 

                          ', ' || my_record.country || '.';







     /* Category is Patent applictions */



      ELSE IF table_type = 'Patent application'

      THEN

            SELECT INTO my_record referenceLocationPatent.patent_publication_number, referenceLocationPatent.date

                   FROM referenceLocationPatent

                   WHERE referenceLocationPatent.referenceID = rl_id;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN referenceLocationPatent', rl_id;

                  RETURN '!ERROR: NO REF. % in the patent applications table!';

            END IF;



            my_day          := date_part('day', referenceLocationPatent.date);

            my_month        := date_part('month', referenceLocationPatent.date);

            my_month_letter := get_month(my_month);

            my_year         := date_part('year', referenceLocationPatent.date);

            my_rl_line := 'Patent number ' || my_record.patent_publication_number || ', ' ||

                          my_day || '-' || my_month_letter || '-' || my_year || '.';







     /* Category is Submission */



      ELSE IF table_type = 'Submission'

      THEN

            SELECT INTO my_record referenceLocationSubmission.month, referenceLocationSubmission.year, referenceLocationSubmission.XrefDBCode,

                        XrefDB.XrefDBName, XrefDB.url

                   FROM referenceLocationSubmission, XrefDB

                   WHERE referenceLocationSubmission.referenceID = rl_id AND XrefDB.XrefDBCode = referenceLocationSubmission.XrefDBCode;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN referenceLocationSubmission', rl_id;

                  RETURN '!ERROR: NO REF. % in the submission table!';

            END IF;



            my_rl_line := 'Submitted (' || get_month(my_record.month) || '-' || my_record.year || ') to the ' ||

                          my_record.XrefDBName || ' database.';







     /* Category is OTHER */



      ELSE IF table_type = 'OTHER' OR table_type = 'Personal communiction'

      THEN

            SELECT INTO my_record referenceLocationOther.text

                   FROM referenceLocationOther

                   WHERE referenceLocationOther.referenceID = rl_id;



            IF NOT FOUND THEN

                  RAISE EXCEPTION 'Error calling make2db_rl_reconstruct -> NO REF. % IN referenceLocationOther', rl_id;

                  RETURN '!ERROR: NO REF. % in the OTHER table!';

            END IF;



            my_rl_line := my_record.text || '.';











      END IF;

      END IF;

      END IF;

      END IF;

      END IF;

      END IF;

      END IF;





      RETURN my_rl_line; 



  END; 




Function: make2db_showflagswitcher( boolean )

Returns: boolean

Language: PLPGSQL

FUNCTION TO SWITCH THE showFlagSwitcher on/off





-- FUNCTION TO SWITCH THE showFlagSwitcher on/off 

-------------------------------------------------



-- Argument: True  -> Set switch to TRUE for False showFlags and erase hidden data from public tables

--           False -> Set switch to False for False showFlags



   DECLARE



     my_switch ALIAS FOR $1;

     my_switch_text TEXT;

     my_tables TEXT;

     my_table TEXT;

     my_record RECORD;



   BEGIN



   -- List of tables containing a showFlagSwitch: (separated by a single space, *the last space is mandatory*)

   -- It is preferable to list the tables in the decreasing order of dependencies (even if cascade references are on)

   my_tables := 'SpotIdentificationParent* SpotDataParent* SpotEntry EntryGelImage EntryGelImage EntryGelMasTer Entry Gel ';



   IF (my_switch IS NULL) THEN RETURN FALSE; END IF;

   IF (my_switch) THEN

     my_switch_text := 'TRUE';

   ELSE

     my_switch_text := 'FALSE';

   END IF;



   WHILE strpos(my_tables, ' ') <> 0 LOOP



     my_table := substr(my_tables, 0, strpos(my_tables, ' '));

     my_tables := substr(my_tables, strpos(my_tables, ' ') + 1, length(my_tables));



     EXECUTE 'UPDATE ' || my_table || ' SET showFlagSwitch = ' || my_switch_text || ' WHERE showFlag IS FALSE';



     -- Erase any records with a False showFlag from schema public

     IF (my_switch) THEN

       EXECUTE 'DELETE FROM public.' || my_table || ' WHERE showFlag IS FALSE';

     END IF;



   END LOOP;



   RETURN TRUE;



   END;

Function: make2db_update( integer, integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE INTERNAL DATA, VIEWS AND PUBLIC DATA First argument: (0) not any update to the core schema (1) to update the core schema views without inner data modification if inner data is not null, (2) to update the core schema views and import the external data replacing the inner data (no modification on ACs, IDs nor descriptions) (3) to update the core schema views and import the external data replacing the inner data (including ACs, IDs and descriptions) Second argument: (0) to not export to the public schema, (>0) to export all changes from the core schema to the public schema





-- FUNCTION TO UPDATE INTERNAL DATA, VIEWS AND PUBLIC DATA 

----------------------------------------------------------



-- The alogrythm is:

--   if (export and not update internal) -> backup Views

--   if (export) -> switch off showFlagSwitchs

--   if (update internal) -> update internals

--   construct Views

--   if (export) -> export to public schema, switch on showFlagSwitchs

--   if (export and update internal) -> re-construct Views

--   if (export and not update internal) -> restore Views





-- First argument:   (0) not any update to the core schema 

--                   (1) to update the core schema views without inner data modification if inner data is not null,

--                   (2) to update the core schema views and import the external data replacing the inner data (no modification on IDs or descriptions)

--                   (3) to update the core schema views and import the external data replacing the inner data (including IDs and descriptions)



-- Second argument:  (0) to not export to the public schema,

--                  (>0) to export all changes from the core schema to the public schema



   DECLARE



     update_internal_data ALIAS for $1;

     export_to_public ALIAS for $2;

     import_external_data_partial INT;

     import_external_data_full INT;



     my_core_schema VARCHAR(16);

     my_core_table VARCHAR(256);

     my_public_schema VARCHAR(16);

     my_public_table VARCHAR(256);

     my_record_core_tables RECORD;



     my_record_container RECORD;



   BEGIN



      SET DATESTYLE TO 'POSTGRES, EUROPEAN';



      IF (update_internal_data = 0) AND (export_to_public = 0) THEN

        RAISE NOTICE 'Update: nothing to do...';

        RETURN 'true';

      END IF;



      -- Backup Views, Switch off showFlagSwitchs

      -- ######################################## --

      IF (export_to_public > 0 AND update_internal_data = 0) THEN

        SELECT INTO my_record_container make2db_backup_views(TRUE) AS status;

        IF (my_record_container.status IS NOT TRUE) THEN

          RAISE NOTICE 'Update Error: Cannot backup Views!';

          RETURN 'false';

        END IF;

      END IF;



      -- Switch off showFlagSwitchs

      -- ########################## --

      IF (export_to_public > 0) THEN

        SELECT INTO my_record_container make2db_showFlagSwitcher (FALSE); 

      END IF;





      -- UPDATE INTERNALS

      -- ################ --         

      IF (update_internal_data > 0) THEN

        import_external_data_partial := 0; 

        import_external_data_full := 0;

        IF (update_internal_data > 1) THEN

          import_external_data_partial := 1;

          IF (update_internal_data > 2) THEN

            import_external_data_full := 1;

          END IF;     

        END IF;       

      -- UPDATE INTERNAL DATA:

      -- *********************************************************-- 

         -- PERFORM make2db_update_internal_primaryAC (import_external_data_full);

         SELECT INTO my_record_container make2db_update_internal_primaryAC (import_external_data_full);

         -- PERFORM make2db_update_internal_id  (import_external_data_full);

         SELECT INTO my_record_container make2db_update_internal_id  (import_external_data_full);

         -- PERFORM make2db_update_internal_de  (import_external_data_full);

         SELECT INTO my_record_container make2db_update_internal_de  (import_external_data_full);

         -- PERFORM make2db_update_internal_gn  (import_external_data_partial);

         SELECT INTO my_record_container make2db_update_internal_gn  (import_external_data_partial);

         -- PERFORM make2db_update_internal_kw  (import_external_data_partial);

         SELECT INTO my_record_container make2db_update_internal_kw  (import_external_data_partial);

         -- PERFORM make2db_update_internal_ec  ();

         SELECT INTO my_record_container make2db_update_internal_ec  ();

         -- PERFORM make2db_update_sp_trembl_dr ();

         SELECT INTO my_record_container make2db_update_sp_trembl_dr ();

      -- *********************************************************--



      END IF;







      -- CONSTRUCT VIEWS

      -- ############### --



      -- LAUNCH FULL ENTRIES CONSTRUCTION:

      -- ****************************************--

      -- PERFORM make2db_construct_entry('all');

         SELECT INTO my_record_container make2db_construct_entry('all');

      -- ****************************************--



      -- BUILD PROTEINS LISTS FOR EVERY MAP:

      -- **********************************--

      -- PERFORM make2db_map_protein_list();

         SELECT INTO my_record_container make2db_map_protein_list();

      -- **********************************--







      -- Export to public schema, Switch on showFlagSwitchs

      -- ################################################## --   

      IF (export_to_public > 0) THEN



        my_core_schema := 'core';

        my_public_schema := 'public';



        FOR my_record_core_tables IN SELECT tablename FROM pg_tables WHERE lower(schemaname) = my_core_schema

        LOOP

           my_core_table := my_core_schema || '.' || my_record_core_tables.tablename; 

           my_public_table := my_public_schema || '.' || my_record_core_tables.tablename;



           IF EXISTS (SELECT tablename FROM pg_tables WHERE tablename = my_record_core_tables.tablename AND lower(schemaname) = my_public_schema)

           THEN

              EXECUTE 'DELETE FROM ' || my_public_table;

              EXECUTE 'INSERT INTO ' || my_public_table || ' SELECT * FROM ' || my_core_table;

           ELSE

              RAISE NOTICE 'Data Update Failure! Could NOT FIND %', my_public_table;

              RETURN 'false';  

           END IF;



        END LOOP;



        SELECT INTO my_record_container make2db_showFlagSwitcher (TRUE);



      END IF;





      -- RE-CONSTRUCT VIEWS

      -- ################## --    

      IF (update_internal_data > 0 AND export_to_public > 0) THEN



      -- LAUNCH FULL ENTRIES CONSTRUCTION:

      -- ****************************************--

      -- PERFORM make2db_construct_entry('all');

         SELECT INTO my_record_container make2db_construct_entry('all');

      -- ****************************************--



      -- BUILD PROTEINS LISTS FOR EVERY MAP:

      -- **********************************--

      -- PERFORM make2db_map_protein_list();

         SELECT INTO my_record_container make2db_map_protein_list();

      -- **********************************--

      END IF;





      -- Restore Views

      -- ############# --

      IF (export_to_public > 0 AND update_internal_data = 0) THEN

        SELECT INTO my_record_container make2db_backup_views(FALSE) AS status;

        IF (my_record_container.status IS NOT TRUE) THEN

          RAISE NOTICE 'Update Error: Can not restore Views!';

          RETURN 'false';

        END IF;

      END IF;





      RAISE NOTICE 'Data Updates Performed With Success!';

      RETURN 'true';



   END;

Function: make2db_update_internal_de( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO INCORPORATE SP "DE" (description) VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE. If argument = 0 : Replacement is performed only if the Main Entry Table contains no data or if data is *identical* to the AC. If argument > 0 : all is replaced.





-- FUNCTION TO INCORPORATE SP "DE" (description) VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE

--------------------------------------------------------------------------------------------------------------



-- If argument = 0 : Replacement is performed only if the Main Entry Table contains no data or if data is *identical to the AC

-- If argument > 0 : all is replaced



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       FOR my_record IN SELECT Entry.AC, Entry.description FROM Entry, ExternalMainXrefData

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtDescription IS NOT NULL

			  AND (Entry.description != ExternalMainXrefData.uniProtDescription OR Entry.description IS NULL)

       LOOP

            SELECT INTO my_record2 uniProtDescription FROM ExternalMainXrefData WHERE AC = my_record.AC LIMIT 1;

            IF substr(my_record2.uniProtDescription, length(my_record2.uniProtDescription), length(my_record2.uniProtDescription)+1) = '.' THEN

              my_record2.uniProtDescription = substr(my_record2.uniProtDescription, 0, length(my_record2.uniProtDescription));

            END IF;



	    IF my_argument > 0 THEN

	      UPDATE Entry SET description = my_record2.uniProtDescription WHERE AC = my_record.AC;

	      my_status = 'true';

	    ELSE IF my_record.description IS NULL OR my_record.description = my_record.AC THEN

	      UPDATE Entry SET description = my_record2.uniProtDescription WHERE AC = my_record.AC;

	      my_status = 'true';

	    END IF;

	    END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: make2db_update_internal_ec( )

Returns: boolean

Language: PLPGSQL

FUNCTION TO INCORPORATE SP "EC" codes FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE





-- FUNCTION TO INCORPORATE SP "EC" codes FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE

-----------------------------------------------------------------------------------------------



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       -- FOR my_record IN SELECT Entry.AC FROM Entry WHERE Entry.ac NOT IN (SELECT EnzymeNomenclature.AC FROM EnzymeNomenclature)

       -- The EC table is automatically updated once

       -- (e.g. if does not exist initially in data), otherwise, direct manual update is required

       FOR my_record IN SELECT Entry.AC FROM Entry EXCEPT SELECT EnzymeNomenclature.AC FROM EnzymeNomenclature

       LOOP

          IF EXISTS (SELECT uniProtEnzymeCode FROM ExternalMainXrefData WHERE AC = my_record.AC AND uniProtEnzymeCode IS NOT NULL)

	  THEN

	    SELECT INTO my_record2 uniProtEnzymeCode FROM ExternalMainXrefData WHERE AC = my_record.AC LIMIT 1;

            INSERT INTO EnzymeNomenclature (AC, enzymeCode) VALUES (my_record.AC, my_record2.uniProtEnzymeCode);

	    my_status = 'true';

	  END IF;

       END LOOP;	 



       RETURN my_status;



   END;

Function: make2db_update_internal_gn( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO INCORPORATE SP "GN" VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE. If argument = 0 : Replacement is performed only if the Main Entry Table contains no data for GeneNames. If argument > 0 : all is replaced.





-- FUNCTION TO INCORPORATE SP "GN" VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE

------------------------------------------------------------------------------------------------



-- If argument = 0 : Replacement is performed only if the Main Entry Table contains no data for GeneNames

-- If argument > 0 : all is replaced



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       FOR my_record IN SELECT Entry.AC, Entry.geneNames FROM Entry, ExternalMainXrefData

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtGeneNames IS NOT NULL

			  AND (Entry.geneNames != ExternalMainXrefData.uniProtGeneNames OR Entry.geneNames IS NULL)

       LOOP

            SELECT INTO my_record2 uniProtGeneNames FROM ExternalMainXrefData WHERE AC = my_record.AC LIMIT 1;

	    IF substr(my_record2.uniProtGeneNames, length(my_record2.uniProtGeneNames), length(my_record2.uniProtGeneNames)+1) = '.' THEN

	        my_record2.uniProtGeneNames = substr(my_record2.uniProtGeneNames, 0, length(my_record2.uniProtGeneNames));

	    END IF;

	    IF my_argument > 0 THEN

	      UPDATE Entry SET geneNames = my_record2.uniProtGeneNames WHERE AC = my_record.AC;

	      my_status = 'true';

	    ELSE IF my_record.geneNames IS NULL THEN

	      UPDATE Entry SET geneNames = my_record2.uniProtGeneNames WHERE AC = my_record.AC;

	      my_status = 'true';

	    END IF;

	    END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: make2db_update_internal_id( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO INCORPORATE SP "ID" VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE. If argument = 0 : Replacement is performed only if the Main Entry Table contains no data or if data is *identical* to the AC. If argument > 0 : all is replaced.





-- FUNCTION TO INCORPORATE SP "ID" VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE

------------------------------------------------------------------------------------------------



-- If argument = 0 : Replacement is performed only if the Main Entry Table contains no data or if data is *identical to the AC

-- If argument > 0 : all is replaced



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       FOR my_record IN SELECT Entry.AC, Entry.ID FROM Entry, ExternalMainXrefData

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtID IS NOT NULL

			  AND (Entry.ID != ExternalMainXrefData.uniProtID OR Entry.ID IS NULL)

       LOOP 

            SELECT INTO my_record2 uniProtID FROM ExternalMainXrefData WHERE AC = my_record.AC LIMIT 1;

            IF NOT EXISTS (SELECT ID FROM ENTRY WHERE ID = my_record2.uniProtID LIMIT 1) -- ID should remain unique in Entry

            THEN

	      IF my_argument > 0 THEN

	        UPDATE Entry SET ID = my_record2.uniProtID WHERE AC = my_record.AC;

	        my_status = 'true';

	      ELSE IF my_record.ID IS NULL OR my_record.ID = my_record.AC THEN

	        UPDATE Entry SET ID = my_record2.uniProtID WHERE AC = my_record.AC;

	        my_status = 'true';

	      END IF;

	      END IF;

            END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: make2db_update_internal_kw( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO INCORPORATE SP "KW" VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE. If argument = 0 : Replacement is performed only if the Main Entry Table contains no data for keywords. If argument > 0 : all is replaced (use only this format to keep keywords Swiss-Prot compliant).





-- FUNCTION TO INCORPORATE SP "KW" VALUES FROM THE EXTERNAL DATA TABLE INTO THE MAIN ENTRY TABLE

------------------------------------------------------------------------------------------------



-- If argument = 0 : Replacement is performed only if the Main Entry Table contains no data for keywords

-- If argument > 0 : all is replaced (use only this format to keep keywords Swiss-Prot compliant)



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       FOR my_record IN SELECT Entry.AC, Entry.keywords FROM Entry, ExternalMainXrefData

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtCategoryKeywords IS NOT NULL

			  AND (Entry.keywords != ExternalMainXrefData.uniProtCategoryKeywords OR Entry.keywords IS NULL)

       LOOP

            SELECT INTO my_record2 uniProtCategoryKeywords FROM ExternalMainXrefData WHERE AC = my_record.AC LIMIT 1;

	    IF substr(my_record2.uniProtCategoryKeywords, length(my_record2.uniProtCategoryKeywords), length(my_record2.uniProtCategoryKeywords)+1) = '.' THEN

	        my_record2.uniProtCategoryKeywords = substr(my_record2.uniProtCategoryKeywords, 0, length(my_record2.uniProtCategoryKeywords));

	    END IF;

	    IF my_argument > 0 THEN

	      UPDATE Entry SET keywords = my_record2.uniProtCategoryKeywords WHERE AC = my_record.AC;

	      my_status = 'true';

	    ELSE IF my_record.keywords IS NULL THEN

	      UPDATE Entry SET keywords = my_record2.uniProtCategoryKeywords WHERE AC = my_record.AC;

	      my_status = 'true';

	    END IF;

	    END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: make2db_update_internal_primaryac( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE LOCAL PRIMARY ACs REGARDING UNIPROT PRIMARY ACs. THE OLD ACs THEN BECOME LOCAL SECONDARY ACs. If argument > 0 : All ACs are updated to their corresponding UniProt Primary AC. If argument = 0 : No AC replacement. Only a potential new local secondary AC (corresponding to the Primary UniProt AC) is added.





-- FUNCTION TO UPDATE PRIMARY AC FROM THE EXTERNAL DATA TABLE REGARDING THE MAIN ENTRY TABLE

--------------------------------------------------------------------------------------------





   DECLARE



            my_record RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN





       SET search_path TO core,common;

       my_status = 'false';

       

       FOR my_record IN SELECT ExternalMainXrefData.AC, ExternalMainXrefData.uniProtAC, ExternalMainXrefData.uniProtID

                          FROM ExternalMainXrefData

                          WHERE ExternalMainXrefData.AC = ANY (ExternalMainXrefData.uniProtSecondaryAC)

       LOOP



            IF NOT EXISTS (SELECT AC FROM Entry WHERE AC = my_record.uniProtAC)

            THEN

              IF my_argument = 0 THEN

                IF NOT EXISTS (SELECT * FROM SecondaryAC WHERE secondaryAC = my_record.uniProtAC AND AC = my_record.AC) THEN

                  INSERT INTO SecondaryAC (secondaryAC, AC) VALUES (my_record.uniProtAC, my_record.AC);

                  my_status = 'true';

                END IF;

              ELSE

                DELETE FROM SecondaryAC WHERE secondaryAC = my_record.uniProtAC AND AC = my_record.AC;

                UPDATE Entry SET AC = my_record.uniProtAC WHERE AC = my_record.AC;

                INSERT INTO SecondaryAC (secondaryAC, AC) VALUES (my_record.AC, my_record.uniProtAC);

                IF NOT EXISTS (SELECT ID FROM Entry WHERE ID = my_record.uniProtID)

                THEN

                  UPDATE Entry SET ID = my_record.uniProtID WHERE AC = my_record.AC;

                END IF;

                my_status = 'true';

              END IF;

            END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: make2db_update_sp_trembl_dr( )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE EntryXrefDB content regarding ExternalMainXrefData for Swiss-Prot & TrEMBL





-- FUNCTION TO UPDATE EntryXrefDB content regarding ExternalMainXrefData for Swiss-Prot & TrEMBL

------------------------------------------------------------------------------------------------



   DECLARE



            my_record RECORD;

	    my_status BOOLEAN;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       -- for more consistency, add a rule clause (AC, 1 or 2) in EntryXrefDB table / 1 is SP, 2 is TrEMBL

       

       FOR my_record IN SELECT * FROM ExternalMainXrefData

       LOOP

       

          IF my_record.uniProtAC IS NOT NULL AND my_record.SPorTrEMBL THEN -- Swiss-Prot reference

	      IF EXISTS (SELECT AC FROM EntryXrefDB WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2) LIMIT 1)

              THEN

	          DELETE FROM EntryXrefDB WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2);

              END IF;                   

	      INSERT INTO EntryXrefDB (AC, XrefDBCode, XrefPrimaryIdentifier, XrefSecondaryIdentifier)

		   values (my_record.AC, 1, my_record.uniProtAC, my_record.uniProtID);

	      my_status = 'true';                  

	                                

          ELSE IF my_record.uniProtAC IS NOT NULL AND NOT my_record.SPorTrEMBL THEN -- TrEMBL reference

	      IF EXISTS (SELECT AC FROM EntryXrefDB WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2) LIMIT 1)

              THEN

	          DELETE FROM EntryXrefDB

                  -- test XrefDBCode 1, as automatically added DR for AC matching SP pattern assume the reference to be SP

	          WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2);

                END IF;                 

	        INSERT INTO EntryXrefDB (AC, XrefDBCode, XrefPrimaryIdentifier, XrefSecondaryIdentifier)

		    values (my_record.AC, 2, my_record.uniProtAC, my_record.uniProtID);

	        my_status = 'true';                 

                                 

	  END IF;	                      

	  END IF;                       

	                                

       END LOOP;	                

                                 

       RETURN my_status;

                                 

   END;

Function: make2db_update_version( boolean )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE ENTRY VERSIONS WHEN annotationChanged IS TRUE. If argument is TRUE => Update Entry View No version will hold a 0





-- FUNCTION TO UPDATE ENTRY VERSIONS WHEN annotationChanged IS TRUE 

-------------------------------------------------------------------



-- Argument: True -> update entry view



   DECLARE



     my_update_view ALIAS FOR $1;

     my_record RECORD;

     my_record_container RECORD;



   BEGIN



     SET DATESTYLE TO 'POSTGRES, EUROPEAN';



     -- initialisation part: we do not want any version to be zero (min 1) / if annotationChanged is true, the increment is treated after

     -- (this loop will be mainly executed at the beginning of the database life)

     FOR my_record IN

       SELECT AC FROM EntryVersionParent* WHERE version = 0 AND annotationChanged IS FALSE

     LOOP

       UPDATE EntryVersionParent* SET version = 1 WHERE version = 0 AND annotationChanged IS FALSE;

       IF (my_update_view IS TRUE) THEN

         SELECT INTO my_record_container make2db_construct_entry(my_record.AC); -- function defined in the final views

       END IF;

     END LOOP;





     SELECT INTO my_record AC FROM EntryVersionParent* WHERE EntryVersionParent.annotationChanged = TRUE LIMIT 1;

     IF NOT FOUND THEN

       RETURN FALSE;

     END IF;



     IF (my_update_view IS FALSE OR my_update_view IS NULL) THEN

       UPDATE EntryVersionParent SET version = version + 1, versionDate = CURRENT_DATE, annotationChanged = FALSE

         WHERE annotationChanged = TRUE;

       RETURN TRUE;

     END IF;



     FOR my_record IN

       SELECT AC FROM EntryVersionParent* WHERE annotationChanged = TRUE

     LOOP

       UPDATE EntryVersionParent* SET version = version + 1,

              versionDate = CURRENT_DATE, annotationChanged = FALSE

         WHERE annotationChanged = TRUE AND AC = my_record.AC;

       SELECT INTO my_record_container core.make2db_construct_entry(my_record.AC); -- function defined in the final views

     END LOOP;



     RETURN TRUE;



   END;

Function: trigger_analyte_sampleidtracker( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Analyte: Track the parental sampleID for all cyclic - children - Analytes from their direct parent





-- TRIGGER ON Analyte: Track the parental sampleID for all cyclic - children - Analytes from their direct parent

----------------------------------------------------------------------------------------------------------------



-- This will update sampleID for children *once* when entered. If parent changes, sampleID will not be synchronized!



   DECLARE



            my_record RECORD;

   

   BEGIN

 

        IF new.analyteParentID IS NOT NULL THEN

	       SELECT INTO my_record sampleID FROM Analyte WHERE analyteID = new.analyteParentID;

	       new.sampleID = my_record.sampleID;

	END IF;	    



        RETURN new;



   END;

Function: trigger_databaserelease_unto_release( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON common.Database: Insert into (or update) the Release Table according to the last update release data on common.Database





-- TRIGGER ON common.Database: Insert into (or update) the Release Table accordingly

------------------------------------------------------------------------------------



   DECLARE



     my_record RECORD;



   BEGIN



     SELECT INTO my_record * FROM Release WHERE releaseNum = new.databaseRelease AND subRelease = new.databaseSubRelease;

     IF FOUND THEN -- side updates are performed over the database table!

       -- UPDATE Release SET releaseDate = new.databaseReleaseDate

       --  WHERE releaseNum = new.databaseRelease AND subRelease = new.databaseSubRelease; 

     ELSE

       INSERT INTO Release VALUES (new.databaseRelease, new.databaseSubRelease, new.databaseReleaseDate);

     END IF;



   return new;



   END;

Function: trigger_entry_ac_uppercase( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON ENTRY: Make sure AC's are in upper case (for case insensitivity)





-- TRIGGER ON ENTRY: Make sure AC's are in upper case (for case insensitivity)

------------------------------------------------------------------------------



   BEGIN

 

        new.AC = upper(new.AC);

        new.ID = upper(new.ID);

        new.description = common.substitute_text(new.description, ' precursor', '');



        IF new.AC = 'UNIDENTIFIED_SPOTS' THEN

          new.showFlag = 'false';

          -- new.showFlagSwitch = 'false';

        END IF;

        

        RETURN new;



   END;

Function: trigger_entry_annotationchanged( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Entry: Set *AnnotationChanged to true when appropriate



-- TRIGGER ON Entry: Set *AnnotationChanged to true when appropriate

---------------------------------------------------------------------

   BEGIN  -- We need to act only when the update changes effectively some selected values

     IF (new.AC <> old.AC OR new.ID <> old.ID OR new.entryClass <> old.entryClass OR

         new.description <> old.description OR new.geneNames <> old.geneNames OR new.keywords <> old.keywords OR

         new.organismID <> old.organismID OR new.organismStrain <> old.organismStrain)

     THEN UPDATE core.EntryVersionGeneral SET annotationChanged = 'true' WHERE AC = new.AC;

     END IF;

     IF (new.identMethod <> old.identMethod)

     THEN UPDATE core.EntryVersion2D SET annotationChanged = 'true' WHERE AC = new.AC;

     END IF;

     return new;

   END;

Function: trigger_entry_synchronizegeneralandmapannotationflags( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Entry, DEPRECATED!: Synchronize Version and Annotation Flags between Maps and General Updates





-- TRIGGER ON Entry: Synchronize Version and Annotation Flags between Maps and General Updates

----------------------------------------------------------------------------------------------

   

   BEGIN



        --IF new.releaseMapUpdate < new.releaseCreation THEN

	--  new.releaseMapUpdate = new.releaseCreation;

	--END IF;

   

        --IF new.releaseUpdate < new.releaseMapUpdate THEN

	--  new.releaseUpdate = new.releaseMapUpdate;

	--END IF;

	

	--IF new.mapAnnotationChanged IS TRUE THEN

	--  new.entryAnnotationChanged = 'true';

	--END IF;



        RETURN new;



   END;

Function: trigger_entryversionparent_annotationchanged_true( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON EntryVersionParent*: For updates, set annotationChanged to TRUE





-- TRIGGER ON EntryVersionParent*: For updates, set annotationChanged to TRUE

-----------------------------------------------------------------------------



   BEGIN



     -- new.version = old.version + 1;

     -- new.versionDate = CURRENT_DATE;

     new.annotationChanged = TRUE;

     return new;



   END;

Function: trigger_entryxrefdb_annotationchanged( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON EntryXrefDB: Set *AnnotationChanged to true when appropriate



-- TRIGGER ON EntryXrefDB: Set Entry.*AnnotationChanged to true when appropriate

---------------------------------------------------------------------------------

   BEGIN  -- We need to act only when the update changes effectively some selected values

     IF (new.XrefDBCode <> old.XrefDBCode OR new.XrefPrimaryIdentifier <> old.XrefPrimaryIdentifier OR

         new.XrefSecondaryIdentifier <> old.XrefSecondaryIdentifier OR new.XrefTertiaryIdentifier <> old.XrefTertiaryIdentifier)

     THEN UPDATE core.EntryVersionGeneral SET annotationChanged = 'true' WHERE AC = new.AC;

     END IF;

     return new;

   END;

Function: trigger_entryxrefdb_no_dynamic_redundancy( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON EntryXrefDBDynamic: Skip a Record on EntryXrefDBDynamic if same Xref exists on EntryXrefDB (update the later)





-- TRIGGER ON EntryXrefDBDynamic: Skip a Record if same Xref exists on EntryXrefDB (update the later)

------------------------------------------------------------------------------------------------------



   DECLARE

 

       my_record  RECORD;

       

   BEGIN

   

       SELECT INTO my_record XrefDBCode FROM XrefDB WHERE upper(XrefDBName) =

         (SELECT upper(XrefDBName) FROM XrefDBDynamic WHERE XrefDBCode = new.XrefDBCode LIMIT 1);

       IF my_record.XrefDBCode IS NOT NULL THEN

         IF EXISTS (SELECT * FROM EntryXrefDB WHERE AC = new.AC AND XrefDBCode = my_record.XrefDBCode

                     AND XrefPrimaryIdentifier = new.XrefPrimaryIdentifier AND activated IS TRUE) THEN

            UPDATE EntryXrefDB SET XrefSecondaryIdentifier = new.XrefSecondaryIdentifier WHERE

              AC = new.AC AND XrefDBCode = my_record.XrefDBCode AND XrefPrimaryIdentifier = new.XrefPrimaryIdentifier;

            RETURN NULL;

         ELSE

           RETURN new;

         END IF;

       ELSE

         RETURN new;

       END IF;

        

   END;

Function: trigger_gel_shortname_melanieid( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON GEL: Upper case shortName and replace melanieGeneratedID by shortName if melanieGeneratedID is NULL





-- TRIGGER ON GEL: Upper case shortName and replace melanieGeneratedID by shortName if melanieGeneratedID is NULL

------------------------------------------------------------------------------------------------------------------



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);



   BEGIN



        new.shortName = upper(new.shortName);

        

        IF new.melanieGeneratedID IS NOT NULL AND new.melanieGeneratedID <> '' THEN

          RETURN new;

        END IF;

        new.melanieGeneratedID = current_database() || ':' || new.shortName;



        RETURN new;



   END;

Function: trigger_geltissuesp_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON GelTissueSP: Upper case SP tissue names



-- TRIGGER ON GelTissueSP: Upper case SP tissue names

-----------------------------------------------------  

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        RETURN new;

   END;

Function: trigger_showflagswitchtrue( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER OON Tables containing ShowFlags: Turn showFlagSwitch to TRUE if showFlag becomes TRUE



-- TRIGGER ON Tables containing ShowFlags: Turn showFlagSwitch to TRUE if showFlag becomes TRUE

-----------------------------------------------------------------------------------------------



   BEGIN 

     IF (new.showFlag IS TRUE) THEN new.showFlagSwitch = 'true';

     END IF;

     return new;

   END;

Function: trigger_spot_insert( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Spot: Insert a Defined & Increased SPOT/BAND ID if ID IS Not Defined





-- TRIGGER ON Spot: Insert a Defined & Increased SPOT/BAND ID if ID IS Not Defined

----------------------------------------------------------------------------------

-- The ID starts with 1/2D-X100000 and is then incremental (hexadecimal)



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);



   BEGIN

 

        IF new.spotID <> '' THEN RETURN new;

        END IF;

 

        IF new.pI IS NOT NULL THEN

               SELECT INTO my_record spotID FROM Spot 

               WHERE pI IS NOT NULL AND spotID ~ '^2D-X' ORDER BY 1 DESC LIMIT 1;

               IF FOUND THEN my_last_spot := '2D-X' || int_to_hex(hex_to_int(substr(my_record.spotID, 5)) + 1);

               ELSE my_last_spot := '2D-X100000';

               END IF;    

        ELSE

               SELECT INTO my_record spotID FROM Spot 

               WHERE pI IS NULL AND spotID ~ '^1D-X' ORDER BY 1 DESC LIMIT 1;

               IF FOUND THEN my_last_spot := '1D-X' || int_to_hex(hex_to_int(substr(my_record.spotID, 5)) + 1);

               ELSE my_last_spot := '1D-X100000';

               END IF;          

        END IF;



        new.spotID = my_last_spot;



        RETURN new;



   END;

Function: trigger_spotdataparent_nextval_dataexpid( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON SpotDataParent*: Force dataExpID to hold nextval(make2db_spotDataExp)





-- TRIGGER ON SpotDataParent*: Force dataExpID to hold nextval(make2db_spotDataExp)

-----------------------------------------------------------------------------------



   BEGIN



     new.dataExpID = nextval(make2db_spotDataExp);

     return new;



   END;

Function: trigger_spotidentificationparent_nextval_identificationid( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON SpotIdentificationParent*: Force identificationID to hold nextval(make2db_spotIdentification)





-- TRIGGER ON SpotIdentificationParent*: Force identificationID to hold nextval(make2db_spotIdentification)

-----------------------------------------------------------------------------------------------------------



   BEGIN



     new.identificationID = nextval(make2db_spotIdentification);

     return new;



   END;

Function: trigger_tissuesp_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON TissueSP: Upper case SP tissue names



-- TRIGGER ON TissueSP: Upper case SP tissue names

--------------------------------------------------

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        RETURN new;

   END;

Function: trigger_tissuespaliase_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON TissueSPAliase: Upper case SP tissue names



-- TRIGGER ON TissueSPAliase: Upper case SP tissue names

--------------------------------------------------------

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        new.Alias = upper(new.Alias);

        RETURN new;

   END;

Function: trigger_tissuesptissuemapping_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON TissueSPTissueMapping: Upper case SP tissue names



-- TRIGGER ON TissueSPTissueMapping: Upper case SP tissue names

---------------------------------------------------------------  

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        RETURN new;

   END;

Function: trigger_userstamp_update( )

Returns: "trigger"

Language: PLPGSQL





-- Trigger function to automatically insert 'userStamp' and 'update' time values

-- This trigger is applied on every core table



   BEGIN



     new.userstamp = USER; new.update = now();

     return new;



   END;

Function: trigger_xrefdb_insensitivename( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive





-- TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive

----------------------------------------------------------------------



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);

	    my_max_iteration INT2;

	    my_new_db_name VARCHAR;



   BEGIN

   

	my_max_iteration = 99; 

      

        IF EXISTS (SELECT * FROM core.XrefDB WHERE lower(XrefDBName) = lower(new.XrefDBName)) THEN

	

	        RAISE NOTICE 'INSERTING AN ALREADY EXISTING Database Name (%) in XrefDB: This field should be case-insensitive!',

		                 new.XrefDBName;

				 

	        FOR my_iteration IN 1..my_max_iteration

	        LOOP

		  my_new_db_name = new.XrefDBName || '_' || my_iteration::VARCHAR;

		  IF NOT EXISTS (SELECT * FROM core.XrefDB WHERE lower(XrefDBName) = lower(my_new_db_name)) THEN

		    new.XrefDBName = my_new_db_name;

		    RAISE NOTICE 'The database name has been changed to: %', new.XrefDBName;

		    RETURN new;

		  END IF;

		END LOOP;

		

		RAISE EXCEPTION 'This database name has already been modified % times!', my_max_iteration;

	

	END IF;

	

	

        RETURN new;



   END;

Function: trigger_xrefdbdynamic_insensitivename( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive





-- TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive

----------------------------------------------------------------------



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);

	    my_max_iteration INT2;

	    my_new_db_name VARCHAR;



   BEGIN

   

	my_max_iteration = 99; 

      

        IF EXISTS (SELECT * FROM core.XrefDBDynamic WHERE lower(XrefDBName) = lower(new.XrefDBName)) THEN

	

	        RAISE NOTICE 'INSERTING AN ALREADY EXISTING Database Name (%) in XrefDB: This field should be case-insensitive!',

		                 new.XrefDBName;

				 

	        FOR my_iteration IN 1..my_max_iteration

	        LOOP

		  my_new_db_name = new.XrefDBName || '_' || my_iteration::VARCHAR;

		  IF NOT EXISTS (SELECT * FROM core.XrefDBDynamic WHERE lower(XrefDBName) = lower(my_new_db_name)) THEN

		    new.XrefDBName = my_new_db_name;

		    RAISE NOTICE 'The database name has been changed to: %', new.XrefDBName;

		    RETURN new;

		  END IF;

		END LOOP;

		

		RAISE EXCEPTION 'This database name has already been modified % times!', my_max_iteration;

	

	END IF;

	

	

        RETURN new;



   END;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict