Attention: World-2DPAGE is no longer maintained. Previously submitted data can still be queried at World-2DPAGE Repository.

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

core

analyte
analytepreparation
analyzable
author
authorgroup
authorparent
biologicalprocess
biosource
biosourceinformation
biosourcepreparation
book
cellularcomponent
citer
commententry2d
commententryfreetext
commententryparent
commenttopic
contact
dynamicremotemake2ddbinterface
entry
entrygelimage
entrygelmaster
entrygene
entrytheoreticalpimw
entryversion2d
entryversiongeneral
entryversionparent
entryxrefdb
entryxrefdbdynamic
entryxrefdbparent
enzymenomenclature
externalmainxrefdata
gel
gelcomputabledynamic
geldynamic
gelimage
gelinformatics
gelpreparation
geltissuesp
genename
geneontologyparent
geneorderedlocus
geneorf
generaltopicdefinition
generaltopicentrydata
genesynonym
journal
make2ddbtool
mappingtopicdefinition
molecularfunction
organism
project
reference
referencedentry
referencedgel
referencedobjectparent
referencedproject
referencedsample
referencelocationbook
referencelocationjournal
referencelocationother
referencelocationparent
referencelocationpatent
referencelocationsubmission
referencelocationthesis
referencelocationunpubobservations
referencetype
referencework
release
sample
samplepreparation
secondaryac
spot
spotdataaacid
spotdataother
spotdataparent
spotdatapeptmassf
spotdatatandemms
spotentry
spotentrygeneraltopic
spotentrymappingtopic
spotentrytopicparent
spotidentificationaacid
spotidentificationother
spotidentificationparent
spotidentificationpeptmassf
spotidentificationtandemms
spotidentificationtandemmssubset
study
studygroup
tissue
tissuesp
tissuespaliase
tissuesptissuemapping
tmp_last_entry
viewentry
viewmapentrylist
viewref
viewspotentry
viewspotentrylast
xrefdb
xrefdbdynamic
xrefdbparent
drop_class( bpchar, character varying, character varying )
drop_function( character varying )
make2db_backup_views( boolean )
make2db_construct_entry( character varying )
make2db_export_ascii_entries( character varying, integer, text, text )
make2db_map_protein_list( )
make2db_reunit_refs( integer )
make2db_reunit_refs_ac( character varying )
make2db_reunit_spots( character varying )
make2db_rl_reconstruct( integer, character varying )
make2db_showflagswitcher( boolean )
make2db_update( integer, integer )
make2db_update_internal_de( integer )
make2db_update_internal_ec( )
make2db_update_internal_gn( integer )
make2db_update_internal_id( integer )
make2db_update_internal_kw( integer )
make2db_update_internal_primaryac( integer )
make2db_update_sp_trembl_dr( )
make2db_update_version( boolean )
trigger_analyte_sampleidtracker( )
trigger_databaserelease_unto_release( )
trigger_entry_ac_uppercase( )
trigger_entry_annotationchanged( )
trigger_entry_synchronizegeneralandmapannotationflags( )
trigger_entryversionparent_annotationchanged_true( )
trigger_entryxrefdb_annotationchanged( )
trigger_entryxrefdb_no_dynamic_redundancy( )
trigger_gel_shortname_melanieid( )
trigger_geltissuesp_uc( )
trigger_showflagswitchtrue( )
trigger_spot_insert( )
trigger_spotdataparent_nextval_dataexpid( )
trigger_spotidentificationparent_nextval_identificationid( )
trigger_tissuesp_uc( )
trigger_tissuespaliase_uc( )
trigger_tissuesptissuemapping_uc( )
trigger_userstamp_update( )
trigger_xrefdb_insensitivename( )
trigger_xrefdbdynamic_insensitivename( )

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))))

analytepreparation Structure
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()

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
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:

analyte

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:

sample

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:

biosource

studygroup

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

Index - Schema core

Table: biosourcepreparation

biosourcepreparation Structure
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:

biosource

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

Index - Schema core

Table: book

book Structure
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:

referencelocationbook

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
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:

commententry2d

commententryfreetext

commententryparent

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
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:

gelinformatics

gelpreparation

project

spotdataparent

spotidentificationparent

study

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
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:

biologicalprocess

cellularcomponent

commententry2d

commententryfreetext

commententryparent

entrygelimage

entrygelmaster

entrygene

entrytheoreticalpimw

entryversion2d

entryversiongeneral

entryversionparent

entryxrefdb

entryxrefdbdynamic

enzymenomenclature

externalmainxrefdata

geneontologyparent

molecularfunction

referencedentry

secondaryac

spotentry

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
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:

entrygelimage

entrygelmaster

gelimage

geltissuesp

referencedgel

spot

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
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
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:

entrygene

geneorderedlocus

geneorf

genesynonym

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
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:

generaltopicentrydata

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:

spotentrygeneraltopic

spotentrymappingtopic

spotentrytopicparent

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
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:

referencelocationjournal

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
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
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:

biosourceinformation

entry

genename

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:

referencedproject

study

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:

author

authorgroup

authorparent

referencedentry

referencedgel

referencedobjectparent

referencedproject

referencedsample

referencelocationbook

referencelocationjournal

referencelocationother

referencelocationparent

referencelocationpatent

referencelocationsubmission

referencelocationthesis

referencelocationunpubobservations

spotdataaacid

spotdataother

spotdataparent

spotdatapeptmassf

spotdatatandemms

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:

citer

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
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:

reference

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

Index - Schema core

Table: referencework

referencework Structure
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:

reference

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

Index - Schema core

Table: release

release Structure
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:

entry

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:

analyte

referencedsample

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

Index - Schema core

Table: samplepreparation

samplepreparation Structure
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:

sample

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:

spotdataaacid

spotdataother

spotdataparent

spotdatapeptmassf

spotdatatandemms

spotentry

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:

spotidentificationaacid

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:

spotidentificationother

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:

spotidentificationpeptmassf

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:

spotidentificationtandemms

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:

spotentrygeneraltopic

spotentrymappingtopic

spotentrytopicparent

spotidentificationaacid

spotidentificationother

spotidentificationparent

spotidentificationpeptmassf

spotidentificationtandemms

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:

spotidentificationtandemmssubset

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:

studygroup

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:

biosource

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:

biosourceinformation

tissue

tissuesptissuemapping

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
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:

geltissuesp

tissuespaliase

tissuesptissuemapping

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
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
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:

entryxrefdb

organism

referencelocationsubmission

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
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:

entryxrefdbdynamic

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

Index - Schema core

Table: xrefdbparent

xrefdbparent Structure
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

Attention: World-2DPAGE is no longer maintained. Previously submitted data can still be queried at World-2DPAGE Repository.

The Make2D-DB II Tool

Index of database structure for schema: core

Table: analyte

Table: analytepreparation

Table: analyzable

Table: author

Table: authorgroup

Table: authorparent

Table: biologicalprocess

Table: biosource

Table: biosourceinformation

Table: biosourcepreparation

Table: book

Table: cellularcomponent

Table: citer

Table: commententry2d

Table: commententryfreetext

Table: commententryparent

Table: commenttopic

Table: contact

Table: dynamicremotemake2ddbinterface

Table: entry

Table: entrygelimage

Table: entrygelmaster

Table: entrygene

Table: entrytheoreticalpimw

Table: entryversion2d

Table: entryversiongeneral

Table: entryversionparent

Table: entryxrefdb

Table: entryxrefdbdynamic

Table: entryxrefdbparent

Table: enzymenomenclature

Table: externalmainxrefdata

Table: gel

Table: gelcomputabledynamic

Table: geldynamic

Table: gelimage

Table: gelinformatics

Table: gelpreparation

Table: geltissuesp

Table: genename

Table: geneontologyparent

Table: geneorderedlocus

Table: geneorf

Table: generaltopicdefinition

Table: generaltopicentrydata

Table: genesynonym

Table: journal

Table: make2ddbtool

Table: mappingtopicdefinition

Table: molecularfunction

Table: organism

Table: project

Table: reference

Table: referencedentry

Table: referencedgel

Table: referencedobjectparent

Table: referencedproject

Table: referencedsample

Table: referencelocationbook

Table: referencelocationjournal

Table: referencelocationother

Table: referencelocationparent

Table: referencelocationpatent

Table: referencelocationsubmission

Table: referencelocationthesis

Table: referencelocationunpubobservations

Table: referencetype

Table: referencework

Table: release

Table: sample

Table: samplepreparation

Table: secondaryac

Table: spot

Table: spotdataaacid

Table: spotdataother

Table: spotdataparent

Table: spotdatapeptmassf