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: common core log public 

Schema common

Table: common.database

To do: generate and send automatically a unique - hidden - database identifier from ExPASy to the remote database, then LOCK table. Make also this identifier queriable from the remote main interface.

common.database Structure
F-Key Name Type Description
databaseidentifier character varying(32) NOT NULL
databasename character varying(128) NOT NULL
databasedescription text
databaserelease integer NOT NULL DEFAULT make2db_last_release()
databasesubrelease smallint
databasereleasedate date NOT NULL DEFAULT ('now'::text)::date
databasereleasenotes text
databasemainsummary text
databaseinterfaceuri text
databaseinterfaceurinumber smallint DEFAULT 1
copyright text
contact text

 

Permissions which apply to common.database
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema common

Function: common.array_dim_length( text[], character varying[], integer[], double precision[], integer )

Returns: smallint

Language: PLPGSQL

FUNCTION TO RETURN THE LENGTH OF THE N-DIMENSION OF AN ARRAY Needed with postgreSQL prior to 7.4, otherwise use array_upper (array_lower) Call function by 2 arguments, and use void {} for the others (there is a postgreSQL problem casting _TYPES[]) ex: array_dim_length(CAST('{}' AS TEXT[]), CAST(my_mappingTechniques AS VARCHAR[]), CAST('{}' AS INTEGER[]), CAST('{}' AS FLOAT[]), 1); to evaluate the first dimension of an INT array!! (prior to potsgres 7.4)



-- FUNCTION TO RETURN THE LENGTH OF THE N-DIMENSION OF AN ARRAY 
---------------------------------------------------------------

-- Needed with postgreSQL prior to 7.4, otherwise use array_upper (array_lower)

-- Call function by 2 arguments, and use void {} for the others (there is a postgreSQL problem casting _TYPES[])
-- ex:  array_dim_length(CAST('{}' AS TEXT[]), CAST(my_mappingTechniques AS VARCHAR[]), CAST('{}' AS INTEGER[]), CAST('{}' AS FLOAT[]), 1);
--      to evaluate the first dimension of an INT array!! (prior to potsgres 7.4)

   DECLARE

      my_mappingTechniques_text ALIAS for $1;
      my_mappingTechniques_varchar ALIAS for $2;
      my_mappingTechniques_int ALIAS for $3;
      my_mappingTechniques_float ALIAS for $4;
      my_dimension ALIAS for $5;
      my_ar_length INT2;
      my_ar_length_string TEXT;
      my_pos1 INT2;
      my_pos2 INT2;

   BEGIN

      -- Since postgreSQL 7.4: use Numerical array_upper and array_lower rather then array_dims
      IF  array_dims(my_mappingTechniques_text) IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_text);
      ELSE IF my_mappingTechniques_varchar IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_varchar);
      ELSE IF my_mappingTechniques_int IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_int);
      ELSE IF my_mappingTechniques_float IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_float);
      ELSE RETURN NULL;
      END IF; END IF; END IF; END IF;

      IF my_ar_length_string IS NULL THEN RETURN NULL;
      END IF;

      IF my_dimension > 1 THEN
        FOR ii IN 1..(my_dimension-1)
        LOOP
          my_pos1 :=  strpos(my_ar_length_string,']');
          my_ar_length_string := substr(my_ar_length_string, my_pos1 + 1);
        END LOOP;
      END IF;

      my_pos1 := strpos(my_ar_length_string,':');
      my_pos2 := strpos(my_ar_length_string,']');
      my_ar_length_string := substr(my_ar_length_string, my_pos1 +1, my_pos2 - my_pos1 -1);
      IF my_ar_length_string !~ '^[0-9]+$' THEN RETURN NULL;
      END IF;
      my_ar_length := my_ar_length_string::INT2;

   RETURN my_ar_length;

   END;

Function: common.get_month( integer )

Returns: bpchar

Language: PLPGSQL

FUNCTION TO CONVERT d MONTHS in MMM FORMAT



-- FUNCTION TO CONVERT d MONTHS in MMM FORMAT 
---------------------------------------------- 

  DECLARE

      month_number ALIAS for $1;
      month CHAR(3);

  BEGIN

  IF month_number = 1 THEN RETURN 'JAN'; ELSE IF month_number = 2 THEN RETURN 'FEB';
  ELSE IF month_number = 3 THEN RETURN 'MAR'; ELSE IF month_number = 4 THEN RETURN 'APR';
  ELSE IF month_number = 5 THEN RETURN 'MAY'; ELSE IF month_number = 6 THEN RETURN 'JUN';
  ELSE IF month_number = 7 THEN RETURN 'JUL'; ELSE IF month_number = 8 THEN RETURN 'AUG';
  ELSE IF month_number = 9 THEN RETURN 'SEP'; ELSE IF month_number = 10 THEN RETURN 'OCT';
  ELSE IF month_number = 11 THEN RETURN 'NOV'; ELSE IF month_number = 12 THEN RETURN 'DEC';
  ELSE RETURN 'XXX';

  END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF;

  END;

Function: common.hex_to_int( character varying )

Returns: bigint

Language: PLPGSQL

FUNCTION TO CONVERT HEXADECIMAL TO INTEGER



-- FUNCTION TO CONVERT HEXADECIMAL TO INTEGER
---------------------------------------------

   DECLARE
 
            my_hex ALIAS for $1;
            my_hex_str VARCHAR(64);
            my_digit TEXT;
            my_int INT8;
            ii INT8;

   BEGIN
 
         my_hex_str = my_hex;
         my_int := 0;
         ii := 1;

         WHILE length(my_hex_str) > 0

         LOOP

                my_digit := substr(my_hex_str, length(my_hex_str));
                IF           my_digit = 'A' THEN my_digit := '10';
                ELSE IF my_digit = 'B' THEN my_digit := '11';
                ELSE IF my_digit = 'C' THEN my_digit := '12';
                ELSE IF my_digit = 'D' THEN my_digit := '13';
                ELSE IF my_digit = 'E' THEN my_digit := '14';
                ELSE IF my_digit = 'F' THEN my_digit := '15';
                END IF; END IF; END IF; END IF; END IF; END IF;

                my_hex_str :=  substr(my_hex_str, 1, length(my_hex_str) - 1);
                my_int := my_int + (my_digit::INT4) * ii;
                ii := ii  * 16;

         END LOOP;

   RETURN my_int;

   END;

Function: common.int_to_hex( bigint )

Returns: character varying

Language: PLPGSQL

FUNCTION TO CONVERT INTEGER TO HEXADECIMAL



-- FUNCTION TO CONVERT INTEGER TO HEXADECIMAL
---------------------------------------------

   DECLARE
 
            my_int ALIAS for $1;
            my_int_num INT8;
            my_digit INT2;
            my_car CHAR(1);
            my_hex VARCHAR(64);
            ii INT2;

   BEGIN
 
         my_int_num = my_int::TEXT;
         my_hex := '';
         ii := 16;

         WHILE  my_int_num > 0

         LOOP

                my_digit := mod(my_int_num, ii);
                my_int_num := (my_int_num::FLOAT - my_digit::FLOAT) / ii;
                IF           my_digit = 10 THEN my_car := 'A';
                ELSE IF my_digit = 11 THEN my_car := 'B';
                ELSE IF my_digit = 12 THEN my_car := 'C';
                ELSE IF my_digit = 13 THEN my_car := 'D';
                ELSE IF my_digit = 14 THEN my_car := 'E';
                ELSE IF my_digit = 15 THEN my_car := 'F';
                ELSE my_car := my_digit::TEXT;
                END IF; END IF; END IF; END IF; END IF; END IF; 

                my_hex := my_car::VARCHAR || my_hex;

         END LOOP;

   RETURN my_hex;

   END;

Function: common.make2db_ascii_entry( character varying, integer, text )

Returns: text

Language: PLPGSQL

FUNCTION TO FORMAT A VIEW (tmp) ENTRY FOR ASCII OUTPUT. Call function by 3 arguments: (AC, line length, hide private annotations), (output line length is formatted by make2db_format_entry(output, line length, hide private annotations))



-- FUNCTION TO FORMAT A VIEW (tmp) ENTRY FOR ASCII OUTPUT 
---------------------------------------------------------

-- Call function by 3 arguments: (AC, line length, hide private annotations)
--(output line length is formatted by make2db_format_entry(output, line length, hide private annotations))

   DECLARE

            my_ac VARCHAR(32);
            my_entry TEXT;
            my_record RECORD;
            my_line_length INT;
            my_hide_private TEXT;
            my_month TEXT;
            my_month_converted TEXT;
            my_ac_first RECORD;

   BEGIN

       SET DATESTYLE TO 'POSTGRES, EUROPEAN';

       my_ac := $1;
       IF my_ac  = '' THEN
           RETURN 'No entry was given. Please, try again.
';
       END IF;

       my_line_length := $2;
       IF my_line_length <25 THEN
             my_line_length = 75;
       END IF;

       my_hide_private := $3;


       SELECT INTO my_ac_first common.make2db_primary_accession(my_ac) AS AC;
       IF NOT FOUND THEN
          RETURN 'There is currently no entry ' || my_ac || '. Please, try again.
';
       ELSE
          SELECT INTO my_record * FROM ViewEntry WHERE accession_number::VARCHAR(32) = my_ac_first.AC;
       END IF;


       my_entry := 'ID   ' || my_record.identifier || ';';
       IF (length(my_record.id_method) > 0) THEN
         my_entry := my_entry || ' ' || my_record.id_method || '.';
       END IF;
       my_entry :=  my_entry || '
AC   ' || my_record.accession_number || ';';
       IF my_record.secondary_identifiers <> '--' AND my_record.secondary_identifiers <> '' THEN
           my_entry := my_entry || ' ' || my_record.secondary_identifiers || ';';
       END IF;

       IF my_record.creation ~ ' [0-9]D' THEN  -- escaped parenthesis with[0-9]) cause a BUG in postgres 7.4!!
           my_record.creation := substr(my_record.creation, 1, 17) || '0' || substr(my_record.creation, 18);
       END IF;
       my_month := substr(my_record.creation, 4, 2);
       my_month_converted := '-' || get_month(my_month::INT) || '-';
       my_month := '-' || my_month || '-';
       my_record.creation = substitute_text(my_record.creation, my_month, my_month_converted);
       /*my_month = get_month(my_month::INT);
       my_record.creation = substr(my_record.creation, 1, 3) || my_month || substr(my_record.creation, 6, length(my_record.creation) - 6) || ', Created)';*/
       my_entry := my_entry || '
DT   ' || my_record.creation || '.';

       my_month := substr(my_record.version_2d, 4, 2);
       my_month_converted := '-' || get_month(my_month::INT) || '-';
       my_month := '-' || my_month || '-';
       my_record.version_2d = substitute_text(my_record.version_2d, my_month, my_month_converted);
       my_entry := my_entry || '
DT   ' || my_record.version_2d || '.';

       my_month := substr(my_record.version_general, 4, 2);
       my_month_converted := '-' || get_month(my_month::INT) || '-';
       my_month := '-' || my_month || '-';
       my_record.version_general = substitute_text(my_record.version_general, my_month, my_month_converted);
       my_entry := my_entry || '
DT   ' || my_record.version_general || '.';


       IF my_record.description <> '--' AND my_record.description <> '' THEN
           my_entry := my_entry || '
DE   ' || my_record.description || '.';
       END IF;

       IF my_record.genes <> '--' AND my_record.genes <> '' THEN
           my_entry := my_entry || '
GN   ' || substitute_text(my_record.genes, ' and ','
GN   and
GN   ') || ';';
       END IF;

       my_entry := my_entry || '
OS   ' || my_record.organism || '.
OC   ' || my_record.organism_classification || '.';

       IF my_record.taxonomy_cross_reference <> '--' AND my_record.taxonomy_cross_reference <> '' THEN
           my_entry := my_entry || '
OX   ' || my_record.taxonomy_cross_reference || ';';
       END IF;

       IF my_record.masters <> '--' AND my_record.masters <> '' THEN
           my_entry := my_entry || '
MT   ' || my_record.masters || '.';
       END IF;       

       my_entry :=  my_entry || '
IM   ' || my_record.images || '.';

       my_entry :=  my_entry || '
' ||  my_record.reference_lines;

       IF my_record.free_comments <> '--' AND my_record.free_comments <> '' THEN
           my_entry := my_entry || '
CC   ' ||  substitute_text(my_record.free_comments,'
','
CC   ');
       END IF;

       IF my_record.one_d_comments <> '--' AND my_record.one_d_comments <> '' THEN
           my_entry := my_entry || '
1D   ' || substitute_text(my_record.one_d_comments,'
','
1D   ');
       END IF;
       IF my_record.one_d_blocks <> '--' AND my_record.one_d_blocks <> '' THEN
           my_entry := my_entry || '
1D   ' || substitute_text(my_record.one_d_blocks,'
','
1D   ');
       END IF;
       IF my_record.two_d_comments <> '--' AND my_record.two_d_comments <> '' THEN
           my_entry := my_entry || '
2D   ' || substitute_text(my_record.two_d_comments,'
','
2D   ');
       END IF;
       IF my_record.two_d_blocks <> '--' AND my_record.two_d_blocks <> '' THEN
           my_entry := my_entry || '
2D   ' || substitute_text(my_record.two_d_blocks,'
','
2D   ');
       END IF;

       IF my_record.database_cross_reference <>'--' AND my_record.database_cross_reference <> '' THEN
           my_record.database_cross_reference := substitute_text(my_record.database_cross_reference,'
','
DR   ');
           my_entry := my_entry || '
DR   ' || my_record.database_cross_reference;
       END IF;

       my_entry := make2db_format_entry(my_entry, my_line_length, my_hide_private);
       my_entry := my_entry || '//';

   RETURN my_entry;

   END;

Function: common.make2db_entryspot_mapping_methods_string( character varying, character varying, integer )

Returns: text

Language: PLPGSQL

FUNCTION TO GET A STRING OF ALL MAPPING METHODS FOR A SPECIFIC ENTRY SPOT



-- FUNCTION TO GET A STRING OF ALL MAPPING METHODS FOR A SPECIFIC ENTRY SPOT
----------------------------------------------------------------------------

  DECLARE

    my_ac VARCHAR(32);
    my_spotID VARCHAR(16);
    my_gelID INT;
    my_record RECORD;
    my_mapping_method_string TEXT;

  BEGIN


       my_ac := $1;
       IF my_ac  = '' THEN
           RETURN '';
       END IF;
       my_spotID := $2;
       IF my_spotID  = '' THEN
           RETURN '';
       END IF;
       my_gelID := $3;
       IF my_gelID  < 1 THEN
           RETURN '';
       END IF;

       my_mapping_method_string := '';

       FOR my_record IN   -- grouped Mapping Methods, possibly over several lines
           SELECT '{' || array_to_string(mappingtechnique,',') || '} ' AS mappingTechniqueString
           FROM spotentrymappingtopic
           WHERE ac = my_ac AND spotID = my_spotID AND gelID = my_gelID AND mappingTechnique IS NOT NULL ORDER BY 1
       LOOP

           my_mapping_method_string := my_mapping_method_string || my_record.mappingTechniqueString;

       END LOOP;

       IF (length(my_mapping_method_string) > 0) THEN
         my_mapping_method_string := substr(my_mapping_method_string, 0, length(my_mapping_method_string));
       END IF;

       return my_mapping_method_string;     

   END;

Function: common.make2db_format_entry( text, integer, text )

Returns: text

Language: PLPGSQL

FUNCTION TO FORMAT ENTRIES TO X CHARACTERS PER LINE. Call function by 3 arguments: (full text, line length, hide private annotations)



-- FUNCTION TO FORMAT ENTRIES TO X CHARACTERS PER LINE
------------------------------------------------------

-- Call function by 3 arguments: (full text, line length, hide private annotations)

   DECLARE

            my_full_entry TEXT;
            my_new_entry TEXT;
            my_old_line TEXT;
            my_old_line_copy TEXT;
            my_assembled_part TEXT;
            my_keyword VARCHAR(11);
            my_line_length INT2;
            my_spaces TEXT;
            my_hide_private TEXT;
            ii INT2;
            jj INT2;

   BEGIN

       my_full_entry := $1;
       my_line_length := $2;
       my_hide_private := $3;
       my_new_entry := '';
       my_spaces := '      ';


       IF substr(my_full_entry,length(my_full_entry)) <> '
' THEN
       my_full_entry := my_full_entry || '
';
       END IF;

       IF my_line_length < 25 THEN
             my_line_length = 75;
       END IF;
       -- substract the 5 caracters for the keyword and the spaces, add one to include the 

       my_line_length := my_line_length - 4;

       ii := 0;
       WHILE strpos(my_full_entry, '
') <> 0 AND ii < 999

       LOOP 

           ii := ii +1;
           my_old_line := substr(my_full_entry, 1, strpos(my_full_entry, '
'));
           my_full_entry := substr(my_full_entry, strpos(my_full_entry, '
') +1);

           my_old_line := substr(my_old_line, 1, length(my_old_line) - 1);
           my_old_line_copy := my_old_line;
           my_assembled_part := '';

           IF (length(my_hide_private) > 0) AND (my_old_line_copy ~ my_hide_private)
           THEN
             my_old_line := ''; my_old_line_copy := '';

           ELSE

             IF substr(my_old_line, 3, 3) <> '   ' THEN
                   RETURN $1;
             END IF;

             -- remove any head spaces --
             WHILE substr(my_old_line_copy,6,1) = ' '
             LOOP my_old_line_copy = substr(my_old_line, 1, 5) || substr(my_old_line_copy, 7);
             END LOOP;

             my_keyword := substr(my_old_line, 1, 5);

             my_old_line_copy := substr(my_old_line_copy, 6);

             IF strpos(my_old_line_copy,' ') = 0 OR length(my_old_line_copy) <= my_line_length THEN
                   my_new_entry := trim(my_new_entry) || my_keyword || my_old_line_copy || '
';

             ELSE
                   jj := 0;
                   WHILE strpos(my_old_line_copy, ' ') <> 0 AND jj < 999

                   LOOP 

                         IF length(my_assembled_part) + (strpos(my_old_line_copy, ' ')) > my_line_length
                               AND my_assembled_part <> ''
                         THEN
                               my_new_entry := trim(my_new_entry || my_keyword  || my_assembled_part) || '
';
                               my_spaces := '      ';
                               IF my_assembled_part ~* '^-!- [a-z]' THEN my_spaces = '    ';
                               END IF;
                               IF (my_keyword ~* '^[1-9]D' OR (my_keyword~* '^CC')) 
                                   AND length(my_keyword) = 5 THEN
                                     IF my_keyword~* '^CC' THEN my_spaces := my_spaces || '  ';
                                     END IF;
                                     my_keyword := my_keyword || my_spaces;
                               END IF;
                               my_assembled_part := '';
                         ELSE
                               my_assembled_part := my_assembled_part || 
                                                    substr(my_old_line_copy, 1, strpos(my_old_line_copy, ' '));
                               my_old_line_copy := substr(my_old_line_copy, strpos(my_old_line_copy, ' ') + 1);
                         END IF;




                   END LOOP;

                   my_new_entry := trim(my_new_entry || my_keyword || my_assembled_part);
                   IF length(my_old_line_copy) > 0 THEN 
                         my_new_entry := my_new_entry || ' ' || my_old_line_copy || '
';
                   END IF;

             END IF;

           END IF;

           IF substr(my_new_entry, length(my_new_entry), 1) <> '
' THEN
                 my_new_entry := my_new_entry || '
';
           END IF;

       END LOOP;


   RETURN my_new_entry;


   END;

Function: common.make2db_last_release( )

Returns: integer

Language: PLPGSQL

FUNCTION TO GET LAST GENERAL RELEASE NUMBER



-- FUNCTION TO GET LAST GENERAL RELEASE NUMBER 
----------------------------------------------

  BEGIN

      RETURN max(Release.releaseNum) FROM Release;

  END;

Function: common.make2db_primary_accession( character varying )

Returns: character varying

Language: PLPGSQL

FUNCTION TO GET THE PRIMARY ACCESSION NUMBER



-- FUNCTION TO GET THE PRIMARY ACCESSION NUMBER 
-----------------------------------------------

  DECLARE

    ac_arg_init ALIAS for $1;
    ac_arg VARCHAR(32);
    my_record RECORD;

  BEGIN

      ac_arg = upper(ac_arg_init);

      SELECT INTO my_record Entry.AC FROM Entry WHERE upper(Entry.AC) = ac_arg::NAME;
        IF my_record.AC IS NOT NULL THEN RETURN ac_arg; END IF;
      SELECT INTO my_record Entry.AC, Entry.ID FROM Entry WHERE upper(Entry.ID) = ac_arg::NAME;
        IF my_record.AC IS NOT NULL THEN RETURN my_record.AC; END IF;
      -- with SP entry spliting we may have several AC for the same econdary AC (return a list?)
      SELECT INTO my_record SecondaryAC.AC FROM SecondaryAC WHERE upper(SecondaryAC.secondaryAC) = ac_arg::NAME LIMIT 1;
        IF my_record.AC IS NOT NULL THEN RETURN my_record.AC; END IF;

      RETURN NULL;

  END;

Function: common.make2db_release_of_date( date, boolean, boolean )

Returns: real

Language: PLPGSQL

FUNCTION TO RETURN THE DATABASE RELEASE (SUB-RELEASE) FOR A GIVEN DATE



-- FUNCTION TO RETURN THE DATABASE RELEASE (and SUB-RELEASE) OF A GIVEN DATE
----------------------------------------------------------------------------

-- Call function by 3 arguments:
-- (DATE, TRUE for rather the next version or FALSE for rather the previous version, TRUE to include sub-release)
-- Returns Real format: version.subVersion (Where subVersion is [dd] - 0 filled - if subVersion >0 and <100)


   DECLARE

            my_date ALIAS for $1;
            my_after ALIAS for $2;
            my_return_data ALIAS for $3;
            my_record RECORD;
            my_pre_subRelease TEXT;
            my_old_text TEXT;
            my_new_text TEXT;

   BEGIN

         IF my_after IS FALSE THEN
           SELECT INTO my_record releaseNum, subRelease FROM Release WHERE releaseDate <= my_date
           ORDER BY (releaseNum *10000 + subRelease) DESC LIMIT 1;
         ELSE
           SELECT INTO my_record releaseNum, subRelease FROM Release WHERE releaseDate >= my_date
           ORDER BY (releaseNum *10000 + subRelease) LIMIT 1;
         END IF;

         IF my_return_data IS FALSE THEN
           RETURN my_record.releaseNum::REAL;
         END IF;
         IF NOT FOUND THEN
           IF my_after IS FALSE THEN
             SELECT INTO my_record min(releaseNum) as releaseNum, min(subRelease) as subRelease
             FROM Release WHERE releaseNum = (SELECT min(releaseNum) FROM Release);
           ELSE
             SELECT INTO my_record max(releaseNum) as releaseNum, max(subRelease) as subRelease
             FROM Release WHERE releaseNum = (SELECT max(releaseNum) FROM Release);
           END IF;
         END IF;

         my_pre_subRelease := '';
         IF (my_record.subRelease > 0 AND my_record.subRelease < 10) THEN
           my_pre_subRelease :=  '0';
         END IF;
         RETURN (my_record.releaseNum || '.' || my_pre_subRelease || my_record.subRelease)::REAL;


   END;

Function: common.make2db_reunit_line( character varying, bpchar )

Returns: text

Language: PLPGSQL

FUNCTION TO RETRIEVE AND CONCATENATE A LIST OF ENTRY FIELDS



-- FUNCTION TO RETRIEVE AND CONCATENATE A LIST OF ENTRY FIELDS 
--------------------------------------------------------------


   DECLARE

	    my_AC ALIAS for $1;
            my_line ALIAS for $2;

            my_reunited TEXT;
            my_record RECORD;
            my_record2 RECORD; 

            ii INT2;
 
            my_temp1_txt TEXT;
            my_temp2_txt TEXT;
            my_temp2_txt_aa TEXT;
            my_temp2_txt_pmf TEXT;
            my_temp2_txt_msms TEXT;
            my_temp2_txt_pepseq TEXT; 
            my_temp3_txt TEXT;
            my_temp4_txt TEXT;
            my_temp5_txt TEXT;
            my_spot_nature TEXT;
            my_maps_record RECORD;
            my_last_master VARCHAR(32); -- used in 2D blocks
            my_last_2d_method TEXT;

            my_substituted_text TEXT;


   BEGIN


        IF my_line = 'AC' THEN
       /************************/

                FOR my_record IN
                          SELECT secondaryAC
                          FROM SecondaryAC
                          WHERE CAST(AC AS varchar(32)) = my_AC
                          ORDER BY 1
                LOOP  
                          IF my_reunited IS NULL 
                          THEN
                               my_reunited := CAST(my_record.secondaryAC AS varchar(20));
                          ELSE
                               my_reunited :=  my_reunited || '; ' || CAST(my_record.secondaryAC AS varchar(50));
                          END IF;
                          --RAISE NOTICE 'stored: %', my_reunited; 
                END LOOP; 



	
        ELSE IF my_line = 'DE' THEN
       /*****************************/


                SELECT INTO my_record description FROM Entry WHERE CAST(Entry.AC AS varchar(32)) = my_AC;
                my_reunited := my_record.description;
		SELECT INTO my_record enzymeCode FROM EnzymeNomenclature WHERE CAST(EnzymeNomenclature.AC AS varchar(32)) = my_AC;
		IF my_record.enzymeCode IS NOT NULL AND my_reunited !~ my_record.enzymeCode
		THEN
		     my_reunited := my_reunited || ' (updated EC ' || my_record.enzymeCode || ')';
		END IF;


	
        ELSE IF my_line = 'IM' THEN
       /*****************************/

                FOR my_record IN
                          SELECT Gel.shortName
                          FROM Gel, EntryGelImage
                          WHERE CAST(EntryGelImage.AC AS varchar(32)) = my_AC AND EntryGelImage.gelID = Gel.gelID
                          AND Gel.showFlagSwitch IS TRUE AND EntryGelImage.showFlagSwitch IS TRUE
                          ORDER BY 1
                LOOP  
                          IF my_reunited IS NULL 
                          THEN
                               my_reunited := CAST(my_record.shortName AS varchar(50));
                          ELSE
                               my_reunited :=  my_reunited || ', ' || CAST(my_record.shortName AS varchar(50));
                          END IF;
                          --RAISE NOTICE '%', my_record.shortName;
                          --RAISE NOTICE 'stored: %', my_reunited; 
                END LOOP; 



        ELSE IF my_line = 'MT' THEN
       /*****************************/

	        FOR my_record IN
                          SELECT Gel.shortName
                          FROM Gel, EntryGelMasTer
                          WHERE CAST(EntryGelMasTer.AC AS varchar(32)) = my_AC AND EntryGelMasTer.gelID = Gel.gelID
                          AND Gel.showFlagSwitch IS TRUE AND EntryGelMaster.showFlagSwitch IS TRUE
                          ORDER BY 1
                LOOP  
                          IF my_reunited IS NULL 
                          THEN
                               my_reunited := CAST(my_record.shortName AS varchar(50));
                          ELSE
                               my_reunited :=  my_reunited || ', ' || CAST(my_record.shortName AS varchar(50));
                          END IF;
                END LOOP; 



        ELSE IF my_line = 'CC' THEN
       /*****************************/

                FOR my_record IN
                          SELECT CommentTopic.CommentTopicName, CommentEntryFreeText.commentFreeText
                          FROM CommentTopic, CommentEntryFreeText
                          WHERE CAST(CommentEntryFreeText.AC AS varchar(32)) = my_AC
                                AND CommentTopic.commentTopicID = CommentEntryFreeText.commentTopicID
                          ORDER BY 1,2
                LOOP
                          IF my_reunited IS NULL
                          THEN
                               my_reunited := '-!- ' || CAST(my_record.commentTopicName AS varchar(64)) || ': ' || my_record.commentFreeText;
                          ELSE
                               my_reunited :=  my_reunited || '
-!- ' || CAST(my_record.commentTopicName AS varchar(64))
                                                           || ': ' || my_record.commentFreeText;
                          END IF;
                END LOOP;



        ELSE IF my_line = '1C' OR my_line = '2C' THEN
       /*************************************************/

                IF my_line = '1C' THEN ii =1;
                ELSE IF my_line = '2C' THEN ii =2;
                END IF;
                END IF;
                FOR my_record IN
                          SELECT CommentTopic.commentTopicName, CommentEntry2D.commentFreeText
                          FROM CommentTopic, CommentEntry2D
                          WHERE CAST(CommentEntry2D.AC AS varchar(32)) = my_AC
                                AND CommentTopic.commentTopicID = CommentEntry2D.commentTopicID AND CommentEntry2D.gelDimension = ii
                          ORDER BY 1,2
                LOOP
                          IF my_reunited IS NULL
                          THEN
                               my_reunited := '-!- ' || CAST(my_record.commentTopicName AS varchar(64)) || ': ' || my_record.commentFreeText;
                          ELSE
                               my_reunited :=  my_reunited || '
-!- ' || CAST(my_record.commentTopicName AS varchar(64))
                                                           || ': ' || my_record.commentFreeText;
                          END IF;
                END LOOP;




        ELSE IF (my_line = '2D' OR my_line ='1D') THEN
       /**************************************************/

                IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_make2db_reunit_2d_topics')
                THEN
                   DELETE FROM buffer_make2db_reunit_2d_topics; -- No need to lock table (serializable level is set on)
                ELSE
                   CREATE TEMPORARY TABLE buffer_make2db_reunit_2d_topics ( stockSpot VARCHAR(16), stockMe1 TEXT, stockMe2 TEXT, mapping BOOLEAN );
                END IF;

                my_reunited := '';
                my_last_master := '';

                FOR my_maps_record IN SELECT Gel.shortName, Gel.gelID FROM Gel, EntryGelImage
                                      WHERE CAST(EntryGelImage.AC AS varchar(32)) = my_AC AND EntryGelImage.gelID = Gel.gelID
                                      AND Gel.showFlagSwitch IS TRUE AND EntryGelImage.showFlagSwitch IS TRUE
                                      ORDER BY 1 
                LOOP /* loop over each master */ 

                   my_temp1_txt := '';
                   my_temp2_txt := '';
                   my_temp2_txt_aa := '';
                   my_temp2_txt_pmf := '';
                   my_temp2_txt_msms := '';
                   my_temp2_txt_pepseq := '';
                   my_temp3_txt := '';
                   my_temp4_txt := '';
                   my_temp5_txt := '';

                   FOR my_record IN

                          -- -- MASS SPECTROMETRY data is not showed within the entry itself
                          SELECT Gel.shortName, ViewSpotEntry.spotID, ViewSpotEntry.gelID, ViewSpotEntry.AC,
                                 ViewSpotEntry.fragment, ViewSpotEntry.AminoAcidList, ViewSpotEntry.aa_version,
                                 ViewSpotEntry.peptideSequences, ViewSpotEntry.peptseq_version,
                                 ViewSpotEntry.peptideMasses, ViewSpotEntry.pmf_version,
                                 ViewSpotEntry.msms, ViewSpotEntry.ms_version,                                 
                                 Spot.mw, Spot.pI

                          FROM Gel, ViewSpotEntry, Spot
                          WHERE Gel.shortName = my_maps_record.shortName
                                AND Gel.gelID = Spot.gelID 
                                AND CAST(ViewSpotEntry.AC AS varchar(32)) = my_AC
                                AND ViewSpotEntry.spotID = Spot.spotID
                                AND ViewSpotEntry.gelID = Spot.gelID
                          ORDER BY ViewSpotEntry.spotID


                   LOOP /* lines of current master */
                          IF (my_record.shortName = '' 
                          OR (my_line = '1D' AND  my_record.pI IS NOT NULL)
                          OR (my_line = '2D' AND  my_record.pI IS NULL))
                          THEN EXIT;
                          END IF;
 
                          IF my_last_master <> my_maps_record.shortName THEN
                               my_reunited := my_reunited || my_temp2_txt;
                               my_temp1_txt := '';
                               my_temp2_txt := '';
                               my_temp2_txt_aa := '';
                               my_temp2_txt_pmf := '';
                               my_temp2_txt_msms := '';
                               my_temp2_txt_pepseq := '';
                               my_temp3_txt := '';

                               my_reunited := my_reunited || '
-!- MASTER: ' ||  my_record.shortName || ';';
                               my_last_master := my_record.shortName;
                          END IF;

                          IF my_line = '2D' THEN
                              IF trunc(my_record.pI) < 10  THEN my_temp1_txt := substr(to_char(my_record.pI, '0d00'),2);
                              ELSE my_temp1_txt := substr(to_char(my_record.pI, '00d00'),2);
                              END IF;
                              my_spot_nature := 'SPOT';
                              my_reunited := my_reunited || '
-!-   PI/MW: SPOT ' || my_record.spotID || '=';
                              my_reunited := my_reunited ||  my_temp1_txt || '/';
                              my_reunited := my_reunited || CAST(my_record.mw AS varchar(8)) || ';';
                          ELSE
                              my_spot_nature := 'BAND';
                              my_reunited := my_reunited || '
-!-   MW: BAND ' || my_record.spotID || '=';
                              my_reunited := my_reunited || CAST(my_record.mw AS varchar(8)) || ';';
                          END IF;

                          IF my_record.fragment = 'true' THEN my_reunited := my_reunited || ' !FRAGMENT!';
                          END IF;



                          /* Identification Methods */
                             -- a line-feed is the separator for several data sets
                             -- MASS SPECTROMETRY data is not showed within the entry itself

                          IF my_record.AminoAcidList IS NOT NULL THEN
                               my_record.AminoAcidList := '
' || my_record.AminoAcidList;
                               my_substituted_text = ';
-!-   AMINO ACID COMPOSITION: ' || my_spot_nature || ' ' || my_record.spotID || ': '; 
                               my_record.AminoAcidList := common.substitute_text(my_record.AminoAcidList,'
', my_substituted_text);
                               my_temp2_txt_aa := my_temp2_txt_aa || substr(my_record.AminoAcidList,2) || ';';
                          END IF;
                          
                          IF my_record.peptideMasses IS NOT NULL THEN
                               IF (strpos(my_record.peptideMasses,' [Documents] ') >0) THEN
                                 -- when [Documents] was at the end of the field:
                                 -- my_record.peptideMasses := substr(my_record.peptideMasses, 1, (strpos(my_record.peptideMasses,' [Documents] ')-1));
                                 -- now, it is on its beginning, on a separate line:
                                 my_record.peptideMasses := substr(my_record.peptideMasses, (strpos(my_record.peptideMasses,'
')+1));
                                 IF (length(my_record.peptideMasses) < 1) THEN -- only documents
                                   my_record.peptideMasses := '0';
                                 END IF;
                               END IF;
                               my_record.peptideMasses := '
' || my_record.peptideMasses;
                               my_substituted_text = '.
-!-   PEPTIDE MASSES: ' || my_spot_nature || ' ' || my_record.spotID || ': '; 
                               my_record.peptideMasses := common.substitute_text(my_record.peptideMasses,'
', my_substituted_text);
                               my_temp2_txt_pmf := my_temp2_txt_pmf || substr(my_record.peptideMasses,2) || '.';
                          END IF;

                          IF my_record.msms IS NOT NULL THEN
                               IF (strpos(my_record.msms,' [Documents] ') >0) THEN
                                 -- my_record.msms := substr(my_record.msms, 1, (strpos(my_record.msms,' [Documents] ')-1));
                                 my_record.msms := substr(my_record.msms, (strpos(my_record.msms,'
')+1));
                                 IF (length(my_record.msms) < 1) THEN -- only documents
                                   my_record.msms := '[0:0]';
                                 END IF;
                               END IF;
                               my_record.msms := '
' || my_record.msms;
                               my_substituted_text = '
-!-   TANDEM MASS SPECTROMETRY: ' || my_spot_nature || ' ' || my_record.spotID || ': '; 
                               my_record.msms := common.substitute_text(my_record.msms,'
', my_substituted_text);
                               my_temp2_txt_msms := my_temp2_txt_msms || substr(my_record.msms,1);
                          END IF;

                          IF my_record.peptideSequences IS NOT NULL THEN
                               my_record.peptideSequences := '
' || my_record.peptideSequences;
                               my_substituted_text = '.
-!-   PEPTIDE SEQUENCES: ' || my_spot_nature || ' ' || my_record.spotID || ': '; 
                               my_record.peptideSequences := common.substitute_text(my_record.peptideSequences,'
', my_substituted_text);
                               my_temp2_txt_pepseq := my_temp2_txt_pepseq || substr(my_record.peptideSequences,2) || '.';
                          END IF;


                          /* stock topics of current spot */
                          IF EXISTS ( SELECT * FROM SpotEntryGeneralTopic WHERE spotID = my_record.spotID AND gelID = my_record.gelID
                                      AND CAST(AC AS VARCHAR(32)) = my_AC LIMIT 1)
                          THEN
                              INSERT INTO buffer_make2db_reunit_2d_topics (stockSpot, stockMe1, stockMe2, mapping)
                                 SELECT my_record.spotID::VARCHAR(16), GeneralTopicDefinition.topicName::TEXT, GeneralTopicEntryData.topicDataText::TEXT, FALSE
                                 FROM SpotEntryGeneralTopic, GeneralTopicEntryData, GeneralTopicDefinition
                                 WHERE SpotEntryGeneralTopic.spotID = my_record.spotID
                                       AND SpotEntryGeneralTopic.gelID = my_record.gelID
                                       AND CAST(SpotEntryGeneralTopic.AC AS VARCHAR(32)) =  my_AC
                                       AND GeneralTopicEntryData.topicDataID = SpotEntryGeneralTopic.topicDataID
                                       AND GeneralTopicEntryData.generalTopicID = GeneralTopicDefinition.generalTopicID
                                 ORDER BY GeneralTopicDefinition.topicName, GeneralTopicEntryData.topicDataText;
                              -- we will not use regular expressions over my_temp4.txt as the fields may contain special characters (no function to escape them in PL/pgsql)
                          END IF;

                          /* stock mapping methods of current spot */
                          IF EXISTS ( SELECT * FROM SpotEntryMappingTopic WHERE spotID = my_record.spotID AND gelID = my_record.gelID
                                      AND CAST(AC AS VARCHAR(32)) = my_AC LIMIT 1)
                          THEN

                                INSERT INTO buffer_make2db_reunit_2d_topics (stockSpot, stockMe1, stockMe2, mapping)
                                 SELECT my_record.spotID::VARCHAR(16), GeneralTopicDefinition.topicName::TEXT, GeneralTopicEntryData.topicDataText::TEXT, TRUE
                                 FROM SpotEntryMappingTopic, GeneralTopicEntryData, GeneralTopicDefinition
                                 WHERE SpotEntryMappingTopic.spotID = my_record.spotID
                                       AND SpotEntryMappingTopic.gelID = my_record.gelID
                                       AND CAST(SpotEntryMappingTopic.AC AS VARCHAR(32)) =  my_AC
                                       AND GeneralTopicEntryData.topicDataID = SpotEntryMappingTopic.topicDataID
                                       AND GeneralTopicEntryData.generalTopicID = GeneralTopicDefinition.generalTopicID
                                 ORDER BY GeneralTopicDefinition.topicName, GeneralTopicEntryData.topicDataText;
                              -- we will not use regular expressions over my_temp5.txt as the fields may contain special characters (no 
                          END IF;


                   END LOOP; /* end loop over lines of current master */
                   
                   my_temp2_txt := my_temp2_txt_aa || my_temp2_txt_pmf || my_temp2_txt_msms || my_temp2_txt_pepseq;

                   /* write topics of current spot */
                   my_last_2d_method := '';
                   FOR my_record2 IN
                     SELECT DISTINCT stockMe1, stockMe2 FROM buffer_make2db_reunit_2d_topics WHERE mapping IS FALSE
                       ORDER BY stockMe1, stockMe2
                   LOOP
                   IF (my_record2.stockMe1 = my_last_2d_method) THEN
                     my_temp4_txt := substr(my_temp4_txt, 0, length(my_temp4_txt));
                     my_temp4_txt := my_temp4_txt || '; ' || my_record2.stockMe2 || '.';
                   ELSE
                     my_temp4_txt := my_temp4_txt || '
-!-   ' || my_record2.stockMe1 || ': ' || my_record2.stockMe2 || '.';
                   END IF;
                   my_last_2d_method := my_record2.stockMe1;
                   END LOOP;

                    /* write mapping methods of current spot */
                   my_last_2d_method := '';
                   FOR my_record2 IN
                     SELECT DISTINCT stockMe1, stockMe2 FROM buffer_make2db_reunit_2d_topics WHERE mapping IS TRUE
                       ORDER BY stockMe1, stockMe2
                   LOOP
                   IF (my_record2.stockMe1 = my_last_2d_method) THEN
                     my_temp5_txt := substr(my_temp5_txt, 0, length(my_temp5_txt));
                     my_temp5_txt := my_temp5_txt || '; ' || my_record2.stockMe2 || '.';
                   ELSE
                     my_temp5_txt := my_temp5_txt || '
-!-   ' || my_record2.stockMe1 || ': ' || my_record2.stockMe2 || '.';
                   END IF;
                   my_last_2d_method := my_record2.stockMe1;
                   END LOOP;

                   DELETE FROM buffer_make2db_reunit_2d_topics;

                   my_reunited := my_reunited || my_temp2_txt || my_temp3_txt || my_temp4_txt || my_temp5_txt;


                END LOOP; /* end loop over each master */

                -- DROP TABLE buffer_make2db_reunit_2d_topics;

                my_reunited := substr(my_reunited, 2, length(my_reunited)); -- cut off first 




        ELSE IF my_line = 'DR' THEN
       /*****************************/

                FOR my_record IN
                          SELECT XrefDB.XrefDBName, EntryXrefDB.XrefPrimaryIdentifier, EntryXrefDB.XrefSecondaryIdentifier,
			         EntryXrefDB.XrefTertiaryIdentifier, EntryXrefDB.XrefOtherIdentifiers
                          FROM XrefDB, EntryXrefDB
                          WHERE CAST(EntryXrefDB.AC AS varchar(32)) = my_AC
                                AND EntryXrefDB.XrefDBCode = XrefDB.XrefDBCode AND activated IS TRUE
                          UNION
                         (SELECT XrefDBDynamic.XrefDBName, EntryXrefDBDynamic.XrefPrimaryIdentifier, EntryXrefDBDynamic.XrefSecondaryIdentifier,
			         EntryXrefDBDynamic.XrefTertiaryIdentifier, EntryXrefDBDynamic.XrefOtherIdentifiers
                          FROM XrefDBDynamic, EntryXrefDBDynamic
                          WHERE CAST(EntryXrefDBDynamic.AC AS varchar(32)) = my_AC
                                AND EntryXrefDBDynamic.XrefDBCode = XrefDBDynamic.XrefDBCode AND activated = 'true'
                          ORDER BY 1)
                          ORDER BY 1
                LOOP
		
                          IF my_reunited IS NULL
                          THEN
                               my_reunited := my_record.XrefDBName || '; ' || CAST (my_record.XrefPrimaryIdentifier AS varchar(32));
 
                          ELSE
                               my_reunited :=  my_reunited || '
' || my_record.XrefDBName || '; ' 
                                                           || CAST (my_record.XrefPrimaryIdentifier AS varchar(32));

			  END IF;

                          IF my_record.XrefSecondaryIdentifier IS NOT NULL
                          THEN 
                               my_reunited := my_reunited || '; ' || CAST (my_record.XrefSecondaryIdentifier AS varchar(64));
                          END IF;
			  
			  IF my_record.XrefTertiaryIdentifier IS NOT NULL
                          THEN 
                               my_reunited := my_reunited || '; ' || CAST (my_record.XrefTertiaryIdentifier AS varchar(64));
                          END IF;
			  
			  IF my_record.XrefOtherIdentifiers IS NOT NULL
                          THEN 
                               my_reunited := my_reunited || '; ' || CAST (my_record.XrefOtherIdentifiers AS varchar(64));
                          END IF;
			  
                          my_reunited := my_reunited || '.';
			  
                 END LOOP;

                 -- This is the last line for a given entry
                 RAISE NOTICE '...entry % is processed', my_AC;
 



        END IF;
        END IF;
        END IF;
        END IF;
        END IF;
        END IF;
	END IF;
        END IF;

   IF my_reunited IS NULL OR my_reunited = '' THEN my_reunited := '--';
   END IF;


   RETURN my_reunited;

   END;

Function: common.make2db_rl_verify_type( integer, character varying )

Returns: boolean

Language: PLPGSQL

FUNCTION TO CHECK THAT referenceLocationID ARE DIFFERENT FROM referenceType IN ReferenceLocation TABLES



-- FUNCTION TO CHECK THAT referenceLocationID ARE DIFFERENT FROM referenceType IN ReferenceLocation TABLES  
----------------------------------------------------------------------------------------------------------

  DECLARE

      rl_id ALIAS for $1;
      table_type ALIAS for $2;
      my_type VARCHAR(32); -- ReferenceType.referenceType%TYPE; (produces a bug with postgreSQL 8.0 beta4!, even preceeded by core.)

  BEGIN
 
      SELECT INTO my_type ReferenceType.referenceType FROM ReferenceType, Reference
      WHERE Reference.referenceID = rl_id AND Reference.referenceTypeID = ReferenceType.referenceTypeID
            AND ReferenceType.referenceType = table_type;
      IF NOT FOUND THEN
            IF table_type = 'OTHER' THEN
         -- any other defined category with no associated table and found in "OTHER" is also OK!
                  SELECT INTO my_type ReferenceType.referenceType FROM ReferenceType, Reference
                  WHERE Reference.referenceID = rl_id;
                  IF FOUND THEN RETURN 'true';
                  END IF;
            END IF;
            RETURN 'false';
      END IF;

      RETURN 'true';
      
  END;

Function: common.make2db_verify_mappingtechnique( character varying[] )

Returns: boolean

Language: PLPGSQL

FUNCTION TO VERIFY THAT METHODS IN THE ARRAY OF MAPPING METHODS ARE LISTED IN MappingTopicDefinition Used as a CHECK constraint in SpotEntryMappingTopic



-- FUNCTION TO VERIFY THAT METHODS IN THE ARRAY OF MAPPING METHODS ARE LISTED IN MappingTopicDefinition
-------------------------------------------------------------------------------------------------------

-- Used as a CHECK constraint in SpotEntryMappingTopic

   DECLARE
      my_mappingTechniques ALIAS for $1;
      my_ar_length INT2;

   BEGIN

      -- extract the length of the first dimension 
      my_ar_length := 
         common.array_dim_length(CAST('{}' AS TEXT[]), CAST(my_mappingTechniques AS VARCHAR[]), CAST('{}' AS INTEGER[]), CAST('{}' AS FLOAT[]), 1);

      IF (my_ar_length IS NULL OR my_ar_length < 1) THEN RETURN 'false';
      END IF;

      FOR ii IN 1..(my_ar_length)
      LOOP
        IF NOT EXISTS (SELECT * FROM MappingTopicDefinition WHERE mappingTechnique = my_mappingTechniques[ii])
        THEN
          RETURN 'false';
        END IF;
      END LOOP;

   RETURN 'true';

   END;

Function: common.substitute_text( text, character varying, character varying )

Returns: text

Language: PLPGSQL

FUNCTION TO SUBSTITUTE A PATTERN BY ANOTHER ONE IN A TEXT



-- FUNCTION TO SUBSTITUTE A PATTERN BY ANOTHER ONE IN A TEXT 
------------------------------------------------------------

   DECLARE

            my_pattern_1 ALIAS for $2;
            my_pattern_2 ALIAS for $3;
            my_old_text TEXT;
            my_new_text TEXT;

   BEGIN
         my_old_text := $1;
         IF strpos(my_old_text, my_pattern_1) = 0 THEN
               RETURN my_old_text;
         END IF;
         my_new_text := '';
         WHILE strpos(my_old_text, my_pattern_1) <> 0
         LOOP
               my_new_text := my_new_text || substr(my_old_text, 1, strpos(my_old_text, my_pattern_1) -1) 
                              || my_pattern_2;
               my_old_text := substr(my_old_text, strpos(my_old_text, my_pattern_1) + length(my_pattern_1),
                              length(my_old_text));
         END LOOP;
   RETURN my_new_text || my_old_text;


   END;

Schema core

To apply any changes performed on the main core tables on the views (View* tables) and the public schema, execute the core.make2db_update(int,int) function with: SELECT core.make2db_update(1,1) -- see comments on this function for arguments description.

Table: core.analyte

core.analyte Structure
F-Key Name Type Description
analyteid serial PRIMARY KEY
core.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 *
core.analytepreparation.analytepreparationid analytepreparationid integer
description text
core.analyte.analyteid analyteparentid integer
core.analyzable.analyzableid fromanalyzablesource integer
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.analytepreparation

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.analyzable

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.author

core.author Structure
F-Key Name Type Description
author character varying(1024) UNIQUE#1 NOT NULL
core.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 core.author
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.authorgroup

core.authorgroup Structure
F-Key Name Type Description
author character varying(1024) UNIQUE#1 NOT NULL
core.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 core.authorgroup
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.authorparent

core.authorparent Structure
F-Key Name Type Description
author character varying(1024) NOT NULL
core.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 core.authorparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.biologicalprocess

core.biologicalprocess Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.biosource

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.biosourceinformation

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

core.biosourceinformation Structure
F-Key Name Type Description
biosourceinformationid serial PRIMARY KEY
core.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)
core.tissue.tissueid tissueid integer
cell character varying(256)
cellline character varying(256)
measuredproperties text
environmet character varying(256)
treatment text
phenotype text
behaviour text
pathology text
provider character varying(256)
userstamp character varying(128) DEFAULT "current_user"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.biosourcepreparation

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.book

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

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.cellularcomponent

core.cellularcomponent Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.citer

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

core.citer Structure
F-Key Name Type Description
author character varying(1024) UNIQUE#1 NOT NULL
core.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()

 

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

 

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

Index - Schema core

Table: core.commententry2d

core.commententry2d Structure
F-Key Name Type Description
core.entry.ac ac character varying(32)
core.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()

 

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

 

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

Index - Schema core

Table: core.commententryfreetext

core.commententryfreetext Structure
F-Key Name Type Description
core.entry.ac ac character varying(32)
core.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 core.commententryfreetext
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.commententryparent

core.commententryparent Structure
F-Key Name Type Description
core.entry.ac ac character varying(32)
core.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 core.commententryparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.commenttopic

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.contact

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.dynamicremotemake2ddbinterface

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

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

 

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

Index - Schema core

Table: core.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 *

core.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
core.release.releasenum#1 releasecreation integer NOT NULL DEFAULT 1
core.release.subrelease#1 subreleasecreation integer NOT NULL
genenames character varying(1024)
keywords character varying(1024)
core.organism.organismid organismid integer

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.entrygelimage

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

core.entrygelimage Structure
F-Key Name Type Description
core.entry.ac ac character varying(32) PRIMARY KEY
core.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 core.entrygelimage
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entrygelmaster

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

core.entrygelmaster Structure
F-Key Name Type Description
core.entry.ac ac character varying(32) PRIMARY KEY
core.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 core.entrygelmaster
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entrygene

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

core.entrygene Structure
F-Key Name Type Description
core.entry.ac ac character varying(32) UNIQUE#1 NOT NULL
core.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 core.entrygene
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entrytheoreticalpimw

core.entrytheoreticalpimw Structure
F-Key Name Type Description
core.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 core.entrytheoreticalpimw
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entryversion2d

core.entryversion2d Structure
F-Key Name Type Description
core.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 core.entryversion2d
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entryversiongeneral

core.entryversiongeneral Structure
F-Key Name Type Description
core.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 core.entryversiongeneral
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entryversionparent

* Applied Trigger: Trigger_EntryVersionParent_annotationChanged_true *

core.entryversionparent Structure
F-Key Name Type Description
core.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 core.entryversionparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entryxrefdb

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

core.entryxrefdb Structure
F-Key Name Type Description
core.entry.ac ac character varying(32) UNIQUE#1 NOT NULL
core.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 core.entryxrefdb
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entryxrefdbdynamic

* Applied Trigger: Trigger_EntryXrefDB_no_dynamic_redundancy *

core.entryxrefdbdynamic Structure
F-Key Name Type Description
core.entry.ac ac character varying(32) UNIQUE#1 NOT NULL
core.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 core.entryxrefdbdynamic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.entryxrefdbparent

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

 

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

Index - Schema core

Table: core.enzymenomenclature

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

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

 

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

 

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

Index - Schema core

Table: core.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

core.externalmainxrefdata Structure
F-Key Name Type Description
core.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()

 

core.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 core.externalmainxrefdata
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.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 *

core.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)
core.organism.organismid organismid integer NOT NULL
organismstrain character varying(256)
core.gel.gelid gelparentid integer

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

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

Should be "Not NULL", but is not for more flexibility
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.gelcomputabledynamic

Reserved for computable map positions on remote databases

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

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

 

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

Index - Schema core

Table: core.geldynamic

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

 

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

Index - Schema core

Table: core.gelimage

core.gelimage Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.gelinformatics

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

core.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
core.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 core.gelinformatics
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.gelpreparation

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

core.gelpreparation Structure
F-Key Name Type Description
gelpreparationid serial PRIMARY KEY
core.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
core.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 core.gelpreparation
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.geltissuesp

* Applied Trigger: Trigger_GelTissueSP_uc *

core.geltissuesp Structure
F-Key Name Type Description
core.gel.gelid gelid integer UNIQUE#1
core.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 core.geltissuesp
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.genename

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.geneontologyparent

core.geneontologyparent Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.geneorderedlocus

core.geneorderedlocus Structure
F-Key Name Type Description
core.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 core.geneorderedlocus
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.geneorf

core.geneorf Structure
F-Key Name Type Description
core.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 core.geneorf
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.generaltopicdefinition

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.generaltopicentrydata

core.generaltopicentrydata Structure
F-Key Name Type Description
topicdataid serial PRIMARY KEY
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.genesynonym

core.genesynonym Structure
F-Key Name Type Description
core.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 core.genesynonym
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.journal

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.make2ddbtool

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

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

 

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

 

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

Index - Schema core

Table: core.mappingtopicdefinition

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

 

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

Index - Schema core

Table: core.molecularfunction

core.molecularfunction Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.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.

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.project

core.project Structure
F-Key Name Type Description
projectid serial PRIMARY KEY
description text
uri character varying(4096)
core.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 core.project
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.reference

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.referencedentry

Spots experimental References are integrated within the spots experimental Tables.

core.referencedentry Structure
F-Key Name Type Description
core.reference.referenceid referenceid integer PRIMARY KEY
core.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 core.referencedentry
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.referencedgel

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

 

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

Index - Schema core

Table: core.referencedobjectparent

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

 

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

Index - Schema core

Table: core.referencedproject

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

 

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

Index - Schema core

Table: core.referencedsample

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

 

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

Index - Schema core

Table: core.referencelocationbook

core.referencelocationbook Structure
F-Key Name Type Description
core.reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationbook_referenceid_seq'::regclass)
core.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()

 

core.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 core.referencelocationbook
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.referencelocationjournal

core.referencelocationjournal Structure
F-Key Name Type Description
core.reference.referenceid referenceid integer PRIMARY KEY DEFAULT nextval('referencelocationjournal_referenceid_seq'::regclass)
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.referencelocationother

core.referencelocationother Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.referencelocationparent

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

 

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

Index - Schema core

Table: core.referencelocationpatent

core.referencelocationpatent Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.referencelocationsubmission

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

 

core.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 core.referencelocationsubmission
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.referencelocationthesis

core.referencelocationthesis Structure
F-Key Name Type Description
core.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()

 

core.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 core.referencelocationthesis
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.referencelocationunpubobservations

core.referencelocationunpubobservations Structure
F-Key Name Type Description
core.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()

 

core.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 core.referencelocationunpubobservations
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.referencetype

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.referencework

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.release

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.sample

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

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.samplepreparation

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.secondaryac

core.secondaryac Structure
F-Key Name Type Description
secondaryac character varying(32) NOT NULL
core.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 core.secondaryac
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.spot

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

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

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotdataaacid

core.spotdataaacid Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdataaacid_dataexpid_seq'::regclass)
humanidentifier character varying(256)
core.spot.spotid#1 spotid character varying(16) PRIMARY KEY
core.spot.gelid#1 gelid integer PRIMARY KEY
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotdataother

core.spotdataother Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdataother_dataexpid_seq'::regclass)
humanidentifier character varying(256)
core.spot.spotid#1 spotid character varying(16) PRIMARY KEY
core.spot.gelid#1 gelid integer PRIMARY KEY
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotdataparent

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

core.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
core.spot.spotid#1 spotid character varying(16)
core.spot.gelid#1 gelid integer
core.reference.referenceid referenceid integer
core.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()

 

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

 

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

Index - Schema core

Table: core.spotdatapeptmassf

core.spotdatapeptmassf Structure
F-Key Name Type Description
dataexpid integer PRIMARY KEY DEFAULT nextval('spotdatapeptmassf_dataexpid_seq'::regclass)
humanidentifier character varying(256)
core.spot.spotid#1 spotid character varying(16) PRIMARY KEY
core.spot.gelid#1 gelid integer PRIMARY KEY
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotdatatandemms

core.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)
core.spot.spotid#1 spotid character varying(16) PRIMARY KEY
core.spot.gelid#1 gelid integer PRIMARY KEY
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotentry

* Applied Trigger: Trigger_SpotEntry_showFlagSwitchTrue *

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotentrygeneraltopic

core.spotentrygeneraltopic Structure
F-Key Name Type Description
core.generaltopicentrydata.topicdataid topicdataid integer NOT NULL
core.spotentry.spotid#1 spotid character varying(16)
core.spotentry.gelid#1 gelid integer
core.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 core.spotentrygeneraltopic
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.spotentrymappingtopic

core.spotentrymappingtopic Structure
F-Key Name Type Description
core.generaltopicentrydata.topicdataid topicdataid integer NOT NULL
core.spotentry.spotid#1 spotid character varying(16)
core.spotentry.gelid#1 gelid integer
core.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()

 

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

 

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

Index - Schema core

Table: core.spotentrytopicparent

core.spotentrytopicparent Structure
F-Key Name Type Description
core.generaltopicentrydata.topicdataid topicdataid integer NOT NULL
core.spotentry.spotid#1 spotid character varying(16)
core.spotentry.gelid#1 gelid integer
core.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 core.spotentrytopicparent
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.spotidentificationaacid

core.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
core.spotdataaacid.dataexpid#2 dataexpid integer
core.spotdataaacid.spotid#2 core.spotentry.spotid#1 spotid character varying(16) NOT NULL
core.spotdataaacid.gelid#2 core.spotentry.gelid#1 gelid integer NOT NULL
core.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()

 

core.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 core.spotidentificationaacid
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.spotidentificationother

core.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
core.spotdataother.dataexpid#2 dataexpid integer
core.spotdataother.spotid#2 core.spotentry.spotid#1 spotid character varying(16) NOT NULL
core.spotdataother.gelid#2 core.spotentry.gelid#1 gelid integer NOT NULL
core.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()

 

core.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 core.spotidentificationother
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.spotidentificationparent

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

core.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
core.spotentry.spotid#1 spotid character varying(16) NOT NULL
core.spotentry.gelid#1 gelid integer NOT NULL
core.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
core.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()

 

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

 

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

Index - Schema core

Table: core.spotidentificationpeptmassf

core.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
core.spotdatapeptmassf.dataexpid#2 dataexpid integer
core.spotdatapeptmassf.spotid#2 core.spotentry.spotid#1 spotid character varying(16) NOT NULL
core.spotdatapeptmassf.gelid#2 core.spotentry.gelid#1 gelid integer NOT NULL
core.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()

 

core.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 core.spotidentificationpeptmassf
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.spotidentificationtandemms

This identification category has a Subset of identified elements

core.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
core.spotdatatandemms.dataexpid#2 dataexpid integer
core.spotdatatandemms.spotid#2 core.spotentry.spotid#1 spotid character varying(16) NOT NULL
core.spotdatatandemms.gelid#2 core.spotentry.gelid#1 gelid integer NOT NULL
core.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()

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core

Table: core.spotidentificationtandemmssubset

core.spotidentificationtandemmssubset Structure
F-Key Name Type Description
core.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()

 

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

 

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

Index - Schema core

Table: core.study

core.study Structure
F-Key Name Type Description
studyid serial PRIMARY KEY
core.project.projectid projectid integer
description text
core.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 core.study
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
♦
♦
♦
♦
♦
♦
♦
select2d
♦

Index - Schema core

Table: core.studygroup

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