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!
';

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

Table: spotdatatandemms