World-2DPAGE Home

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

Dumped on 2006-07-18

The Make2D-DB II Tool

Index of database structure for schema: 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"()
update timestamp without time zone DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core


Table: core.tissue

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core


Table: core.tissuesp

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core


Table: core.tissuespaliase

* Applied Trigger: Trigger_TissueSPAliase_uc *

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

 

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

Index - Schema core


Table: core.tissuesptissuemapping

* Applied Trigger: Trigger_TissueSPTissueMapping_uc *

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

 

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

Index - Schema core


Table: core.tmp_last_entry

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

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

 

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

Index - Schema core


Table: core.viewentry

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

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

 

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

Index - Schema core


Table: core.viewmapentrylist

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

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

 

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

Index - Schema core


Table: core.viewref

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

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

 

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

Index - Schema core


Table: core.viewspotentry

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

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

Specific data sets are separated by
aa_version integer
msms text

Specific data sets are separated by
ms_version integer
peptidesequences text

Specific data sets are separated by
peptseq_version integer
peptidemasses text

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

 

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

Index - Schema core


Table: core.viewspotentrylast

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

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

 

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

Index - Schema core


Table: core.xrefdb

* Applied Trigger: Trigger_XrefDB_insensitiveName *

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

 

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core


Table: core.xrefdbdynamic

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

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

Tables referencing this one via Foreign Key Constraints:

 

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

Index - Schema core


Table: core.xrefdbparent

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

 

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

Index - Schema core


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

Returns: boolean

Language: PLPGSQL

FUNCTION TO DROP A VARIABLE CLASS

 



-- FUNCTION TO DROP A VARIABLE CLASS  

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



  DECLARE



      class_type ALIAS for $1;

      class_name_init ALIAS for $2;

      class_name VARCHAR(128);

      class_table_init ALIAS for $3;

      class_table VARCHAR(128);



  BEGIN



      -- !! Handle with EXTREME CARE !!



      class_name = lower(class_name_init);

      class_table = lower(class_table_init);



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

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

           THEN EXECUTE 'DROP TABLE ' || class_name;

           RETURN 'true';

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

           THEN EXECUTE 'DROP VIEW ' || class_name;

           RETURN 'true';

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

           THEN EXECUTE 'DROP INDEX ' || class_name;

           RETURN 'true';

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

           THEN EXECUTE 'DROP RULE ' || class_name;

           RETURN 'true';

           END IF;

           END IF;

           END IF;

           END IF;

      END IF;



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

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

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

           RETURN 'true';

           END IF;

      END IF;



  RETURN 'false';



  END;




Function: core.drop_function( character varying )

Returns: boolean

Language: PLPGSQL

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





-- FUNCTION TO DROP ALL NAMED FUNCTIONS 

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



  DECLARE



      function_name_init ALIAS for $1;

      function_name VARCHAR(128);

      my_record RECORD;



  BEGIN



      function_name = lower(function_name_init);



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

      THEN RETURN 'false';

      END IF;



      FOR my_record IN

            SELECT pg_proc.OID AS proc_OID FROM pg_proc

                   WHERE lower(proname) = function_name::NAME

      LOOP

            DELETE FROM pg_proc WHERE OID = my_record.proc_OID;

      END LOOP;





  RETURN 'true';



  END;




Function: core.make2db_backup_views( boolean )

Returns: boolean

Language: PLPGSQL

FUNCTION TO BACKUP OR RESTORE THE CURRENT VIEWS VIA TEMPORARY TABLES





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

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



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



   DECLARE



     my_argument ALIAS FOR $1;

     my_backup_view TEXT;

     my_status BOOLEAN;

     my_record RECORD;



   BEGIN



   my_status = FALSE;

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



   FOR my_record IN

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

   LOOP

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

     IF (my_argument IS TRUE) THEN

       my_status = TRUE;

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

         EXECUTE 'DROP TABLE ' || my_backup_view;

       END IF;

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

     ELSE

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

         my_status = TRUE;

         EXECUTE 'DELETE FROM ' || my_record.tablename;

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

       END IF;

     END IF;

   END LOOP;



   RETURN my_status;



   END;

Function: core.make2db_construct_entry( character varying )

Returns: boolean

Language: PLPGSQL

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





-- FUNCTION FOR ENTRY/IES "VIEWS" CONSTRUCTION  

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



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



   DECLARE



            my_ac ALIAS for $1;    

            my_limit_search_ac VARCHAR(32);

            void_operation BOOLEAN;

            my_record RECORD;

 



   BEGIN



      SET DATESTYLE TO 'POSTGRES, EUROPEAN';



      void_operation := 'false';



      IF my_ac = '' THEN RETURN 'false';

      END IF;



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



      IF my_ac = 'all'

      THEN 

        RAISE NOTICE 'Analyzing tables...';

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

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

        THEN

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

';

          void_operation := 'true';

        END IF;

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

        THEN

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

';

          void_operation := 'true';

        END IF;      

        my_limit_search_ac := '.';



      ELSE

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

        THEN

          IF NOT (SELECT make2db_reunit_refs(0))

          THEN

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

';

            RETURN 'false';

          END IF; 

        END IF;

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

        THEN

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

', my_ac;

          RETURN 'false';

        END IF;

	my_limit_search_ac := my_ac;



      END IF; 





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

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

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







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

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



      SELECT INTO my_record databaseName FROM common.Database LIMIT 1;

      IF (NOT FOUND) THEN

        my_record.databaseName := 'the current database';

      END IF;



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

      THEN

        DELETE FROM buffer_make2db_construct_entry;

      ELSE

        CREATE TEMPORARY TABLE buffer_make2db_construct_entry (

                                                           identifier VARCHAR(32),

                                                           id_method  VARCHAR(48),

                                                           accession_number VARCHAR(32),

                                                           secondary_identifiers TEXT,

                                                           creation TEXT,

                                                           version_2d TEXT,

							   version_general TEXT,

                                                           description TEXT,

                                                           genes TEXT,

                                                           organism TEXT,

                                                           organism_classification TEXT,

                                                           taxonomy_cross_reference TEXT,

                                                           masters TEXT,

                                                           images TEXT,

                                                           free_comments TEXT,

                                                           reference_lines TEXT,

                                                           one_d_comments TEXT, 

                                                           one_d_blocks TEXT,

                                                           two_d_comments TEXT,

                                                           two_d_blocks TEXT,

                                                           database_cross_reference TEXT

                                                        );



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





      INSERT INTO buffer_make2db_construct_entry

      SELECT       Entry.ID AS identifier,

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

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

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

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

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

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

                   Entry.geneNames AS genes,

                   Organism.organismSpecies AS organism,

                   Organism.organismClassification AS organism_classification,

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

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

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

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

                   make2db_reunit_refs_ac(Entry.AC) AS reference_lines,

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

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

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

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

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





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



      WHERE        Entry.releaseCreation = creation.releaseNum

                   AND Entry.AC = EntryVersion2D.AC

		   AND Entry.AC = EntryVersionGeneral.AC

                   AND Entry.organismID = Organism.organismID

                   AND Organism.taxonomyXrefDBCode = XrefDB.XrefDBCode

                   AND Entry.showFlagSwitch IS TRUE

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

                   AND Entry.AC ~* my_limit_search_ac



      ORDER BY 3;



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

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







      IF NOT EXISTS (SELECT * FROM buffer_make2db_construct_entry LIMIT 1)

      THEN

          void_operation := 'true';

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

      ELSE

          void_operation := 'false';

      END IF;





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

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

      CREATE TABLE tmp_buffer_entries AS SELECT * FROM buffer_make2db_construct_entry;

      COMMENT ON TABLE tmp_buffer_entries IS 

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

      GRANT SELECT ON tmp_buffer_entries TO select2d;





      IF my_ac = 'all'

      THEN

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

          ALTER TABLE  tmp_buffer_entries RENAME TO ViewEntry;

          CREATE INDEX ViewEntry_ac_idx ON ViewEntry(accession_number);

          CREATE INDEX ViewEntry_id_idx ON ViewEntry(identifier);

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

';

      ELSE

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

          INSERT INTO ViewEntry (SELECT * FROM buffer_make2db_construct_entry);

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

          ALTER TABLE tmp_buffer_entries RENAME TO tmp_last_entry;

      END IF;



      DELETE FROM buffer_make2db_construct_entry;

   

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

  



      IF (void_operation) THEN

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

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

Returns: boolean

Language: PLPGSQL

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





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

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

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



   DECLARE



            my_ac VARCHAR(32);    

            my_line_length INT;

            my_hide_private TEXT;

	    my_file TEXT;

	    my_file_command TEXT;

            my_record RECORD;

         -- my_database_name TEXT;



   BEGIN



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

       my_line_length := $2;

       IF my_line_length <25 THEN my_line_length = 75;

       END IF;



       my_ac := $1;



       my_hide_private := $4;



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

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

      THEN

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

      ELSE

        CREATE TEMPORARY TABLE buffer_export_ascii_entries (make2db_ascii_entry TEXT);

      END IF;



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



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

         LOOP

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

         END LOOP;

	 

       ELSE

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

       END IF;



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

       EXECUTE my_file_command;

       EXECUTE 'DELETE FROM buffer_export_ascii_entries';



       RETURN 'true';



   END;

Function: core.make2db_map_protein_list( )

Returns: boolean

Language: PLPGSQL

FUNCTION TO BUILD PROTEINS LIST FOR EACH MAP





-- FUNCTION TO BUILD PROTEINS LIST FOR EACH MAP

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



   DECLARE



            my_record RECORD;

            my_record2 RECORD;

            my_record3 RECORD;

            my_record_maps_number RECORD;

            my_record_maps_name RECORD;

            my_array_length INT2;

            my_description_string TEXT;

            my_mapping_string TEXT;

            my_results_string TEXT;

            my_RN_string TEXT;

            my_RF_string TEXT;

            my_this_reference TEXT;

            my_variable_string TEXT;

            void_operation BOOLEAN;





   BEGIN



      void_operation := 'false';



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



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



      IF NOT EXISTS (SELECT AC FROM EntryGelImage LIMIT 1)

      THEN

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

';

        void_operation := 'true';

      END IF;





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

      THEN 

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

      ELSE

        CREATE TEMPORARY TABLE buffer_make2db_map_protein_list

            ( gelID INT4,

              genes VARCHAR(1024),

              description TEXT,

              spotID VARCHAR(16),

              AC VARCHAR(32),

              ID VARCHAR(32),

              pI NUMERIC(4,2),

              mw NUMERIC(6,3),

              volumeRelative FLOAT4,

              odRelative FLOAT4,

	      fragment BOOLEAN,

              topicDescription TEXT,

              mappingTechniques TEXT,

              availableResults TEXT,

              RN_References TEXT,

              referenceIDs TEXT,

              showFlag BOOLEAN NOT NULL DEFAULT 'true',

              showFlagSwitch BOOLEAN NOT NULL DEFAULT 'true'

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

             );

      END IF;



      IF NOT (void_operation) THEN

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

      END IF;



      INSERT INTO buffer_make2db_map_protein_list 

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

               Entry.AC, Entry.ID as ID,

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

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

                        ON (SpotEntry.AC = Entry.AC),

                        Spot

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

               AND SpotEntry.showFlagSwitch IS TRUE

        );



      DELETE FROM buffer_make2db_map_protein_list

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



      IF NOT (void_operation) THEN

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

';

      END IF;



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



      UPDATE buffer_make2db_map_protein_list SET showFlag = Entry.showFlag

        FROM ENTRY

        WHERE buffer_make2db_map_protein_list.AC = Entry.AC;



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



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

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

';

        void_operation := 'true';

      END IF;







      -- Loop Over Each Map



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



        FOR maps_ii IN 1..my_record_maps_number.max_number



        LOOP



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



          IF my_record_maps_name.shortName IS NOT NULL THEN

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

          END IF;



          FOR my_record IN



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



          LOOP  -- For Every Line of the Selected Map 



                my_description_string := '';

                my_mapping_string := '';

                my_results_string := '';

                my_RN_string := '';

                my_RF_string := '';





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

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

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

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

                   )



                THEN



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



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

                         WHERE SpotEntryGeneralTopic.spotID = my_record.spotID

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

                         AND SpotEntryGeneralTopic.topicDataID = GeneralTopicEntryData.topicDataID 

                         AND GeneralTopicEntryData.generalTopicID = GeneralTopicDefinition.generalTopicID

                      -- AND ambiguousReference = 'false'

                         ORDER BY GeneralTopicDefinition.topicName, GeneralTopicEntryData.topicDataText

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



                  LOOP

                        IF my_description_string !~ my_record2.topicName

                        THEN

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

                        END IF;

                        FOR ii IN 1..99

                        LOOP

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

                          THEN

                            my_RN_string := '1, ';

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

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

                            EXIT;

                          END IF;

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

                          END IF;

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

                          IF my_RN_string !~ my_this_reference

                          THEN

                            my_RN_string := my_RN_string || my_this_reference;

                            SELECT INTO my_record3 referenceID FROM ReferencedEntry 

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

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

                          END IF;

                        END LOOP;

                  END LOOP;



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

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



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

                         ambiguousReference FROM GeneralTopicEntryData, SpotEntryMappingTopic

                         WHERE SpotEntryMappingTopic.spotID = my_record.spotID

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

                         AND SpotEntryMappingTopic.topicDataID = GeneralTopicEntryData.topicDataID 

                      -- AND ambiguousReference = 'false'



                  LOOP

                        IF my_description_string !~ my_record2.topicName

                        THEN

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

                        END IF;

                        FOR ii IN 1..99

                        LOOP

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

                          THEN

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

                            my_RN_string :=  '1, ' || my_RN_string;

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

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

                            EXIT;

                          END IF;

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

                          END IF;

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

                          IF my_RN_string !~ my_this_reference

                          THEN

                            my_RN_string := my_RN_string || my_this_reference;

                            SELECT INTO my_record3 referenceID FROM ReferencedEntry 

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

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

                          END IF;

                        END LOOP;

                  END LOOP;







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

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

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



                END IF;





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

                IF my_RN_string is NULL or my_RN_string = ''

                THEN

                  my_RN_string :=  '1';

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

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

                END IF;





            -- Concatenate mapping methods

                my_mapping_string := my_mapping_string ||

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





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

               my_results_string := '(';

               my_variable_string := '';       

               IF (length(my_mapping_string) > 0) THEN

                 my_variable_string := ' ';

               END IF;

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

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

                THEN

                   my_results_string := my_results_string || 'PMF';

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

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

                     my_variable_string := ' ';

                   END IF;

                END IF;

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

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

                THEN

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

                   END IF;

                   my_results_string := my_results_string || 'Aa';

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

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

                     my_variable_string := ' ';

                   END IF;

                END IF;

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

                             AND gelID = my_record.gelID 

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

                THEN

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

                   END IF;

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

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

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

                   END IF;

                END IF;



                my_results_string := my_results_string ||  ')';



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

                END IF;

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

                END IF;

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

                END IF;



                UPDATE buffer_make2db_map_protein_list

                SET topicDescription = my_description_string, mappingTechniques = my_mapping_string,

                    availableResults = my_results_string, RN_References = my_RN_string, referenceIDs = my_RF_string

                    WHERE buffer_make2db_map_protein_list.spotID = my_record.spotID

                    AND buffer_make2db_map_protein_list.gelID = my_record.gelID

                    AND buffer_make2db_map_protein_list.AC = my_record.AC;





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



        END LOOP;



      END IF;



      -- END Loop Over Each Map













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

        void_operation := 'true';

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

      ELSE

        void_operation := 'false';

      END IF;





      -- Move the buffer table to the permanent one:

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

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



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

      THEN

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

        DELETE FROM ViewMapEntryList;

        INSERT INTO ViewMapEntryList SELECT * FROM buffer_make2db_map_protein_list ORDER by 1;



      ELSE

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

        CREATE INDEX ViewMapEntryList_gelID ON ViewMapEntryList (gelID);

        CREATE INDEX ViewMapEntryList_spotID ON ViewMapEntryList (spotID);

        CREATE INDEX ViewMapEntryList_AC_idx ON ViewMapEntryList (AC);

        COMMENT ON TABLE ViewMapEntryList IS

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

        GRANT SELECT ON ViewMapEntryList TO select2d;

      END IF;





      DELETE FROM buffer_make2db_map_protein_list;



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







      IF (void_operation) THEN  

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: core.make2db_reunit_refs( integer )

Returns: boolean

Language: PLPGSQL

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





-- FUNCTION TO REUNIFY GENERAL REFERENCE BLOCKS 

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



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



   DECLARE



            my_article ALIAS for $1;  

            my_record RECORD;

            my_record2 RECORD;

            my_record3 RECORD;

            my_max_author_group_priority INT2;

            my_author_group_string VARCHAR(2048);

            my_max_author_priority INT2;

            my_author_string VARCHAR(8192);

            my_rx_array TEXT;

            ii INT2;

            void_operation BOOLEAN;





   BEGIN



      void_operation := 'false';



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

      THEN

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

      ELSE

        CREATE TEMPORARY TABLE buffer_make2db_reunit_refs 

              ( referenceID INT4 UNIQUE,  referenceWorkDescription VARCHAR(64),

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

               );



      END IF;





      IF my_article = 0

      THEN

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

      ELSE

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

      END IF; 





      FOR my_record IN



            SELECT referenceID FROM buffer_make2db_reunit_refs ORDER BY 1 



      LOOP



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

                   FROM Reference, ReferenceWork

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

                   AND buffer_make2db_reunit_refs.referenceID = my_record.referenceID;



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

            THEN

                my_rx_array := '';

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

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

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

                       WHERE ReferenceLocationJournal.referenceID = my_record.referenceID;

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

		-- FOR ii IN 1..5

                LOOP

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

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

                      END IF;

                END LOOP;

		IF (length(my_rx_array) > 0) THEN

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

		END IF;

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

            END IF;



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

            my_author_group_string := '';

	    IF my_max_author_group_priority > 0 THEN

              FOR ii IN 1..my_max_author_group_priority

              LOOP

                    FOR my_record2 IN

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

                    LOOP

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

                    END LOOP;

              END LOOP;

              IF my_author_group_string <> '' THEN

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

              END IF;

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

            END IF;



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

            my_author_string := '';

            FOR ii IN 1..my_max_author_priority

            LOOP

                  FOR my_record2 IN

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

                  LOOP

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

                  END LOOP;

            END LOOP;

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

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



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

                           FROM Reference

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

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

                           WHERE buffer_make2db_reunit_refs.referenceID = my_record.referenceID;



      END LOOP;









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

        void_operation := 'true';

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

      ELSE

        void_operation := 'false';

      END IF;





      -- Move the buffer table to the permanent one:

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

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



      IF my_article = 0

      THEN  

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

        THEN

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

          DELETE FROM ViewRef;

          INSERT INTO ViewRef SELECT * FROM buffer_make2db_reunit_refs ORDER by 1;

        ELSE

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

          CREATE INDEX ViewRef_referenceID_idx ON ViewRef(referenceID);

          COMMENT ON TABLE ViewRef IS

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

          GRANT SELECT ON ViewRef TO select2d;

        END IF;

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



      ELSE

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

        THEN

          LOCK TABLE ViewRefLast IN ACCESS EXCLUSIVE MODE;

          DELETE FROM ViewRefLast;

          INSERT INTO ViewRefLast SELECT * FROM buffer_make2db_reunit_refs;

        ELSE

          CREATE TABLE ViewRefLast AS SELECT * FROM buffer_make2db_reunit_refs;

          COMMENT ON TABLE ViewRefLast IS

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

          GRANT SELECT ON ViewRefLast TO select2d;

        END IF;



      END IF; 



      DELETE FROM buffer_make2db_reunit_refs;







      IF (void_operation) THEN

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: core.make2db_reunit_refs_ac( character varying )

Returns: text

Language: PLPGSQL

FUNCTION TO REUNIFY REFERENCE BLOCKS FOR A GIVEN ENTRY





-- FUNCTION TO REUNIFY REFERENCE BLOCKS FOR A GIVEN ENTRY 

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



   DECLARE



            my_ac ALIAS for $1;  

            my_record RECORD;

            my_record2 RECORD;

            my_ref_blocks TEXT;

            ii INT2;

 

   BEGIN 



      my_ref_blocks = '';

      FOR my_record IN



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



      LOOP



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

                   WHERE referenceID = my_record.referenceID;

            IF my_ref_blocks <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

';

            END IF;

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

RP   ' || my_record2.referenceWorkDescription;

            IF my_record2.crossReferences <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

RX   ' || my_record2.crossReferences;

            END IF;

            IF my_record2.authorsGroup <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

RG   ' || my_record2.authorsGroup;

            END IF;

            my_ref_blocks := my_ref_blocks || '

RA   ' || my_record2.authors;

            IF my_record2.referenceTitle <> '' THEN

                  my_ref_blocks := my_ref_blocks || '

RT   ' || my_record2.referenceTitle;

            END IF;

            my_ref_blocks := my_ref_blocks || '

RL   ' || my_record2.referenceLocation;



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

                           FROM Reference, ReferenceWork

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

                           AND ViewRef.referenceID = my_record.referenceID;





 

      END LOOP; 



   RETURN my_ref_blocks;





   END;

Function: core.make2db_reunit_spots( character varying )

Returns: boolean

Language: PLPGSQL

FUNCTION TO REUNIFY ENTRIES 1/2D BLOCKS





-- FUNCTION TO REUNIFY ENTRIES 1/2D BLOCKS

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



   DECLARE



            my_ac ALIAS for $1;

            my_record RECORD;

            my_record2 RECORD;

            my_record3 RECORD;

            my_max_version INT4;

            my_amino_acids TEXT;

            my_msms TEXT;

            my_parentIon_mass FLOAT;

            my_parentIon_charge INT2;

            my_chargeSign VARCHAR(32);

            my_parentIon_text TEXT;

            my_peptide_sequences TEXT;

            my_peptide_sequences_subset TEXT;

            my_peptide_masses TEXT;

            my_array_upper_dim INT4;

            my_enzyme TEXT;

            

            my_last_data_doc TEXT;

            my_last_data_doc_uri TEXT;

            my_last_identification_doc TEXT;

            my_last_identification_doc_uri TEXT;



            my_show_flag BOOLEAN;

            void_operation BOOLEAN;





   BEGIN



      void_operation := 'false';



      my_show_flag := 'true';



      IF my_ac = '' THEN RETURN 'false';

      END IF;



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

      THEN

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

      ELSE

      CREATE TEMPORARY TABLE buffer_make2db_reunit_spots

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

              fragment BOOLEAN,

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

              AminoAcidList TEXT DEFAULT NULL,

              aa_version INT4 DEFAULT NULL,

              msms TEXT DEFAULT NULL,

              ms_version INT4 DEFAULT NULL,

              peptideSequences TEXT DEFAULT NULL,

              peptseq_version INT4 DEFAULT NULL,

              --peptideMasses FLOAT[][] DEFAULT NULL,

              --enzyme VARCHAR(32) DEFAULT NULL,

              peptideMasses TEXT DEFAULT NULL,

              pmf_version INT4 DEFAULT NULL,

              showFlag BOOLEAN NOT NULL DEFAULT 'true',

              showFlagSwitch BOOLEAN NOT NULL DEFAULT 'true'

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

              UNIQUE (spotID, gelID, AC)

             );



      END IF;





      IF my_ac = 'all'

      THEN

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

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

      ELSE

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

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

      END IF;







      FOR my_record IN

          SELECT spotID, gelID, AC FROM buffer_make2db_reunit_spots ORDER BY 1 

      LOOP





            -- Amino Acid Composition part --

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



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

                                              AND AC = my_record.AC;

            IF my_max_version IS NOT NULL

            THEN

               my_amino_acids = '';

               FOR my_record2 IN

                 SELECT SpotIdentificationAAcid.AminoAcidList, SpotIdentificationAAcid.showFlag

                 FROM SpotIdentificationAAcid

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

                 AND SpotIdentificationAAcid.AC = my_record.AC

                 -- AND SpotIdentificationAAcid.version = my_max_version 

                 ORDER BY SpotIdentificationAAcid.version DESC

               LOOP

                 my_amino_acids := my_amino_acids || my_record2.AminoAcidList ;

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

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

                 END IF;

                 my_amino_acids := my_amino_acids || '

';

               END LOOP;

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



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

               END IF;



               UPDATE buffer_make2db_reunit_spots SET AminoAcidList = my_amino_acids, aa_version = my_max_version

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

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

            END IF;





            -- Mass Spectrometry part (Tandem MS) --

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

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



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

                                              AND AC = my_record.AC;

            IF my_max_version IS NOT NULL

            THEN

               my_msms := '';

               my_last_data_doc := '';

               my_last_data_doc_uri := '';

               my_last_identification_doc := '';

               my_last_identification_doc_uri := '';

               FOR my_record2 IN

                 SELECT SpotIdentificationTandemMS.ionMasses, SpotIdentificationTandemMS.dataExpID,

                 SpotIdentificationTandemMS.showFlag, SpotIdentificationTandemMS.allExperimentData,

                 SpotIdentificationTandemMS.identificationDocument, SpotIdentificationTandemMS.URI

                 FROM SpotIdentificationTandemMS

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

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

                 -- AND SpotIdentificationTandemMS.version = my_max_version 

                 ORDER BY SpotIdentificationTandemMS.version, SpotIdentificationTandemMS.identificationDocument,

                   SpotIdentificationTandemMS.URI DESC

               LOOP

                 my_parentIon_mass := 0.0;

                 my_parentIon_charge := 0;

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

                   WHERE SpotDataTandemMS.dataExpID = my_record2.dataExpID

                   AND SpotDataTandemMS.spotID = my_record.spotID

                   AND SpotDataTandemMS.gelID = my_record.gelID

                   LIMIT 1;

                 my_array_upper_dim = array_upper(my_record2.ionMasses,1);

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

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

                     AND

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

                    ) THEN

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

                   IF (my_record3.URI IS NOT NULL) THEN

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

                     my_last_data_doc_uri := my_record3.URI;

                   END IF;

                   IF (my_record3.dataDocument IS NOT NULL) THEN

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

                     my_last_data_doc := my_record3.dataDocument;

                   END IF;

                   IF (my_record2.URI IS NOT NULL) THEN

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

                     my_last_identification_doc_uri := my_record2.URI;

                   END IF;

                   IF (my_record2.identificationDocument IS NOT NULL) THEN

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

                     my_last_identification_doc := my_record2.identificationDocument;

                   END IF;

                   my_msms := my_msms  || '

';

                 END IF;

                 IF (my_array_upper_dim > 0) THEN

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

                     my_parentIon_text := '';

                     IF (my_record3.parentMass > 0) THEN

                       my_parentIon_text := my_record3.parentMass::TEXT;

                     END IF;

                     IF (my_record3.parentCharge IS NOT NULL) THEN

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

                       my_chargeSign = ' -> charge not reported';

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

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

                       END IF; END IF;

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

                       my_parentIon_text := my_parentIon_text || my_parentIon_charge || my_chargeSign;

                     END IF;

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

                   END IF;

                   FOR ii in 1..my_array_upper_dim

                   LOOP

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

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

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

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

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

                       END IF;

                       my_msms := my_msms || '; ';

                     END IF;

                   END LOOP;

                   my_array_upper_dim = array_upper(my_record3.ionMasses,1);

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

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

                     FOR ii in 1..my_array_upper_dim

                     LOOP

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

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

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

                       END IF;

                       my_msms := my_msms  || '; ';

                     END LOOP;

                   END IF;

                 END IF;

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

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

                 END IF;

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

                 my_msms := my_msms || '

'; 

               END LOOP;

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



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

               ELSE

                 my_msms = NULL;

               END IF;



               UPDATE buffer_make2db_reunit_spots SET msms = my_msms, ms_version = my_max_version

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

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

               my_msms := '';

            END IF;





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

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



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

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

            IF my_max_version IS NOT NULL -- identifiedPeptideSequence

            THEN

               my_peptide_sequences := '';

               FOR my_record2 IN

                 SELECT SpotIdentificationTandemMS.identificationID, SpotIdentificationTandemMS.showFlag

                 FROM SpotIdentificationTandemMS

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

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

                 -- AND SpotIdentificationTandemMS.version = my_max_version 

                 ORDER BY SpotIdentificationTandemMS.version DESC

               LOOP



                 my_peptide_sequences_subset := '';

                 FOR my_record3 IN

                  SELECT SpotIdentificationTandemMSSubset.identifiedPeptideSequence, SpotIdentificationTandemMSSubset.sequenceStartPosition,

                         SpotIdentificationTandemMSSubset.sequenceENDPosition

                  FROM SpotIdentificationTandemMSSubset

                  WHERE SpotIdentificationTandemMSSubset.identificationID = my_record2.identificationID

                  ORDER BY 2,3,1

                 LOOP

                   my_peptide_sequences_subset := my_peptide_sequences_subset || my_record3.identifiedPeptideSequence;

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

                   THEN

                     my_peptide_sequences_subset := my_peptide_sequences_subset || ',' ||

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

                   END IF;

                   my_peptide_sequences_subset := my_peptide_sequences_subset || '; ';

                 END LOOP;



                 IF (length(my_peptide_sequences_subset) > 0) THEN

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

                   IF my_record2.showFlag IS FALSE THEN

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

                   END IF;

                   my_peptide_sequences := my_peptide_sequences || my_peptide_sequences_subset || '

';

                 END IF;



               END LOOP;



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



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

               END IF;



               UPDATE buffer_make2db_reunit_spots SET peptideSequences = my_peptide_sequences, peptseq_version = my_max_version

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

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

            END IF;





            -- Peptide Masses part --

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



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

                                              AND AC = my_record.AC;

            IF my_max_version IS NOT NULL

            THEN

               my_peptide_masses := '';

               my_msms := '';

               my_last_data_doc := '';

               my_last_data_doc_uri := '';

               my_last_identification_doc := '';

               my_last_identification_doc_uri := '';

               FOR my_record2 IN

                 SELECT SpotIdentificationPeptMassF.peptideMasses, SpotIdentificationPeptMassF.dataExpID,

                 SpotIdentificationPeptMassF.showFlag, SpotIdentificationPeptMassF.allExperimentData,

                 SpotIdentificationPeptMassF.identificationDocument, SpotIdentificationPeptMassF.URI

                 FROM SpotIdentificationPeptMassF

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

                 AND SpotIdentificationPeptMassF.AC = my_record.AC

                 -- AND SpotIdentificationPeptMassF.version = my_max_version 

                 ORDER BY SpotIdentificationPeptMassF.version, SpotIdentificationPeptMassF.identificationDocument,

                   SpotIdentificationPeptMassF.URI DESC

               LOOP

                 my_enzyme := '';

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

                   WHERE SpotDataPeptMassF.dataExpID = my_record2.dataExpID

                   AND SpotDataPeptMassF.spotID = my_record.spotID

                   AND SpotDataPeptMassF.gelID = my_record.gelID

                   LIMIT 1;

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

                   my_enzyme := my_record3.enzyme;

                 ELSE

                   my_enzyme := '(Undefined Enzyme)';

                 END IF;

                 my_array_upper_dim = array_upper(my_record2.peptideMasses,1);

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

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

                     AND

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

                    ) THEN

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

                   IF (my_record3.URI IS NOT NULL) THEN

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

                     my_last_data_doc_uri := my_record3.URI;

                   END IF;

                   IF (my_record3.dataDocument IS NOT NULL) THEN

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

                     my_last_data_doc := my_record3.dataDocument;

                   END IF;

                   IF (my_record2.URI IS NOT NULL) THEN

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

                     my_last_identification_doc_uri := my_record2.URI;

                   END IF;

                   IF (my_record2.identificationDocument IS NOT NULL) THEN

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

                     my_last_identification_doc := my_record2.identificationDocument;

                   END IF;

                   my_peptide_masses := my_peptide_masses  || '

';

                 END IF;

                 IF (my_array_upper_dim > 0) THEN

                   FOR ii in 1..my_array_upper_dim

                   LOOP

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

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

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

                     END IF;

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

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

                     END IF;

                     my_peptide_masses := my_peptide_masses  || '; ';

                   END LOOP;

                   my_array_upper_dim = array_upper(my_record3.peptideMasses,1);

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

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

                     FOR ii in 1..my_array_upper_dim

                     LOOP

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

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

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

                       END IF;

                       my_peptide_masses := my_peptide_masses  || '; ';

                     END LOOP;

                   END IF;

                   IF (length(my_peptide_masses) > 2) THEN

                     my_peptide_masses := my_peptide_masses || my_record3.enzyme;

                   END IF;

                 END IF;

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

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

                 END IF;

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

                 my_peptide_masses := my_peptide_masses || '

'; 

               END LOOP;

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



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

               END IF;



               UPDATE buffer_make2db_reunit_spots SET peptideMasses = my_peptide_masses, pmf_version = my_max_version

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

                 AND buffer_make2db_reunit_spots.AC = my_record.AC;

            END IF;





      END LOOP; 









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

        void_operation := 'true';

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

      ELSE

        void_operation := 'false';

      END IF;





      -- Move the buffer table to the permanent one:

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

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



      IF my_ac = 'all'

      THEN

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

        THEN

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

          DELETE FROM ViewSpotEntry;

          INSERT INTO ViewSpotEntry SELECT * FROM buffer_make2db_reunit_spots ORDER by 1;

        ELSE

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

          CREATE INDEX ViewSpotEntry_spotID_idx ON ViewSpotEntry(spotID);

          CREATE INDEX ViewSpotEntry_gelID_idx ON ViewSpotEntry(gelID);

          CREATE INDEX ViewSpotEntry_ac_idx ON ViewSpotEntry(AC);

          COMMENT ON TABLE ViewSpotEntry IS

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

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

';

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

';

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

';

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

';

          GRANT SELECT ON ViewSpotEntry TO select2d;

        END IF;

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



      ELSE

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

        THEN

          LOCK TABLE ViewSpotEntryLast IN ACCESS EXCLUSIVE MODE;

          DELETE FROM ViewSpotEntryLast;

          INSERT INTO ViewSpotEntryLast SELECT * FROM buffer_make2db_reunit_spots;

        ELSE

          CREATE TABLE ViewSpotEntryLast AS SELECT * FROM buffer_make2db_reunit_spots;

          COMMENT ON TABLE ViewSpotEntryLast IS

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

          GRANT SELECT ON ViewSpotEntryLast TO select2d;

        END IF; 

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

        INSERT INTO ViewSpotEntry (SELECT * FROM buffer_make2db_reunit_spots);



      END IF;



      DELETE FROM buffer_make2db_reunit_spots;







      IF (void_operation) THEN

        RETURN 'false';

      ELSE

        RETURN 'true';

      END IF;





   END;

Function: core.make2db_rl_reconstruct( integer, character varying )

Returns: text

Language: PLPGSQL

FUNCTION TO CONSTRUCT THE REFERENCE LOCATION (RL LINE)





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

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



  DECLARE



      rl_id ALIAS for $1;

      table_type_input ALIAS for $2;

      table_type VARCHAR(32);

      my_record RECORD;

      my_record2 RECORD;

      my_rl_line TEXT;



      my_max_citer_priority INT2;

      my_citer_string VARCHAR(800);

      my_day INT2;

      my_month INT2;

      my_month_letter TEXT;

      my_year INT2;



  BEGIN



      SET DATESTYLE TO 'POSTGRES, EUROPEAN';



      table_type := table_type_input;



     /* if table_type is not given */

      IF table_type = '' 

      THEN

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

            THEN table_type := 'Journal';

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

            THEN table_type := 'Unpublished_results';

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

            THEN table_type := 'Book';

            ELSE IF EXISTS (SELECT referenceID FROM ReferenceLocationUnpubObservations

                            WHERE referenceID = rl_id)

            THEN table_type := 'Unpublished observations';

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

            THEN table_type := 'Thesis';

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

            THEN table_type := 'Patent application';

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

            THEN table_type := 'Submission';

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

            THEN table_type := 'OTHER';

            END IF;	

            END IF;

            END IF;

            END IF;

            END IF;

            END IF;

            END IF;

            END IF;



      END IF;







     /* Category is Journal / Unpublished results */



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

      THEN

            SELECT INTO my_record Journal.journalName, Journal.url,

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

                        ReferenceLocationJournal.year, ReferenceLocationJournal.unpublished



                   FROM ReferenceLocationJournal, Journal

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



            IF NOT FOUND THEN

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

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

            END IF;



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

            IF my_record.subVolume IS NOT NULL

            THEN

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

            END IF;

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

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



            IF my_record.unpublished = 'true' THEN 

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

                  my_citer_string := '';

                  FOR ii IN 1..my_max_citerPriority

                  LOOP

                        FOR my_record2 IN

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

                        LOOP

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

                        END LOOP;

                  END LOOP;

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

                  my_rl_line := 'Unpublished results, cited by:

RL   ' || my_citer_string || '

' || my_rl_line;

            END IF;







     /* Category is Book */



      ELSE IF table_type = 'Book'

      THEN

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

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

                   FROM ReferenceLocationBook, Book

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



            IF NOT FOUND THEN

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

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

            END IF;



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

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

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

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







     /* Category is Unpublished observations */



      ELSE IF table_type = 'Unpublished observations'

      THEN

            SELECT INTO my_record ReferenceLocationUnpubObservations.month, ReferenceLocationUnpubObservations.year

                   FROM ReferenceLocationUnpubObservations

                   WHERE ReferenceLocationUnpubObservations.referenceID = rl_id;



            IF NOT FOUND THEN

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

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

            END IF;



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







     /* Category is Thesis */



      ELSE IF table_type = 'Thesis'

      THEN

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

                   FROM referenceLocationThesis

                   WHERE referenceLocationThesis.referenceID = rl_id;



            IF NOT FOUND THEN

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

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

            END IF;



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

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







     /* Category is Patent applictions */



      ELSE IF table_type = 'Patent application'

      THEN

            SELECT INTO my_record referenceLocationPatent.patent_publication_number, referenceLocationPatent.date

                   FROM referenceLocationPatent

                   WHERE referenceLocationPatent.referenceID = rl_id;



            IF NOT FOUND THEN

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

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

            END IF;



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

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

            my_month_letter := get_month(my_month);

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

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

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







     /* Category is Submission */



      ELSE IF table_type = 'Submission'

      THEN

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

                        XrefDB.XrefDBName, XrefDB.url

                   FROM referenceLocationSubmission, XrefDB

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



            IF NOT FOUND THEN

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

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

            END IF;



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

                          my_record.XrefDBName || ' database.';







     /* Category is OTHER */



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

      THEN

            SELECT INTO my_record referenceLocationOther.text

                   FROM referenceLocationOther

                   WHERE referenceLocationOther.referenceID = rl_id;



            IF NOT FOUND THEN

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

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

            END IF;



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











      END IF;

      END IF;

      END IF;

      END IF;

      END IF;

      END IF;

      END IF;





      RETURN my_rl_line; 



  END; 




Function: core.make2db_showflagswitcher( boolean )

Returns: boolean

Language: PLPGSQL

FUNCTION TO SWITCH THE showFlagSwitcher on/off





-- FUNCTION TO SWITCH THE showFlagSwitcher on/off 

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



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

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



   DECLARE



     my_switch ALIAS FOR $1;

     my_switch_text TEXT;

     my_tables TEXT;

     my_table TEXT;

     my_record RECORD;



   BEGIN



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

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

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



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

   IF (my_switch) THEN

     my_switch_text := 'TRUE';

   ELSE

     my_switch_text := 'FALSE';

   END IF;



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



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

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



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



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

     IF (my_switch) THEN

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

     END IF;



   END LOOP;



   RETURN TRUE;



   END;

Function: core.make2db_update( integer, integer )

Returns: boolean

Language: PLPGSQL

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





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

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



-- The alogrythm is:

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

--   if (export) -> switch off showFlagSwitchs

--   if (update internal) -> update internals

--   construct Views

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

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

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





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

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

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

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



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

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



   DECLARE



     update_internal_data ALIAS for $1;

     export_to_public ALIAS for $2;

     import_external_data_partial INT;

     import_external_data_full INT;



     my_core_schema VARCHAR(16);

     my_core_table VARCHAR(256);

     my_public_schema VARCHAR(16);

     my_public_table VARCHAR(256);

     my_record_core_tables RECORD;



     my_record_container RECORD;



   BEGIN



      SET DATESTYLE TO 'POSTGRES, EUROPEAN';



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

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

        RETURN 'true';

      END IF;



      -- Backup Views, Switch off showFlagSwitchs

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

      IF (export_to_public > 0 AND update_internal_data = 0) THEN

        SELECT INTO my_record_container make2db_backup_views(TRUE) AS status;

        IF (my_record_container.status IS NOT TRUE) THEN

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

          RETURN 'false';

        END IF;

      END IF;



      -- Switch off showFlagSwitchs

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

      IF (export_to_public > 0) THEN

        SELECT INTO my_record_container make2db_showFlagSwitcher (FALSE); 

      END IF;





      -- UPDATE INTERNALS

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

      IF (update_internal_data > 0) THEN

        import_external_data_partial := 0; 

        import_external_data_full := 0;

        IF (update_internal_data > 1) THEN

          import_external_data_partial := 1;

          IF (update_internal_data > 2) THEN

            import_external_data_full := 1;

          END IF;     

        END IF;       

      -- UPDATE INTERNAL DATA:

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

         -- PERFORM make2db_update_internal_primaryAC (import_external_data_full);

         SELECT INTO my_record_container make2db_update_internal_primaryAC (import_external_data_full);

         -- PERFORM make2db_update_internal_id  (import_external_data_full);

         SELECT INTO my_record_container make2db_update_internal_id  (import_external_data_full);

         -- PERFORM make2db_update_internal_de  (import_external_data_full);

         SELECT INTO my_record_container make2db_update_internal_de  (import_external_data_full);

         -- PERFORM make2db_update_internal_gn  (import_external_data_partial);

         SELECT INTO my_record_container make2db_update_internal_gn  (import_external_data_partial);

         -- PERFORM make2db_update_internal_kw  (import_external_data_partial);

         SELECT INTO my_record_container make2db_update_internal_kw  (import_external_data_partial);

         -- PERFORM make2db_update_internal_ec  ();

         SELECT INTO my_record_container make2db_update_internal_ec  ();

         -- PERFORM make2db_update_sp_trembl_dr ();

         SELECT INTO my_record_container make2db_update_sp_trembl_dr ();

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



      END IF;







      -- CONSTRUCT VIEWS

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



      -- LAUNCH FULL ENTRIES CONSTRUCTION:

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

      -- PERFORM make2db_construct_entry('all');

         SELECT INTO my_record_container make2db_construct_entry('all');

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



      -- BUILD PROTEINS LISTS FOR EVERY MAP:

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

      -- PERFORM make2db_map_protein_list();

         SELECT INTO my_record_container make2db_map_protein_list();

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







      -- Export to public schema, Switch on showFlagSwitchs

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

      IF (export_to_public > 0) THEN



        my_core_schema := 'core';

        my_public_schema := 'public';



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

        LOOP

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

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



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

           THEN

              EXECUTE 'DELETE FROM ' || my_public_table;

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

           ELSE

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

              RETURN 'false';  

           END IF;



        END LOOP;



        SELECT INTO my_record_container make2db_showFlagSwitcher (TRUE);



      END IF;





      -- RE-CONSTRUCT VIEWS

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

      IF (update_internal_data > 0 AND export_to_public > 0) THEN



      -- LAUNCH FULL ENTRIES CONSTRUCTION:

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

      -- PERFORM make2db_construct_entry('all');

         SELECT INTO my_record_container make2db_construct_entry('all');

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



      -- BUILD PROTEINS LISTS FOR EVERY MAP:

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

      -- PERFORM make2db_map_protein_list();

         SELECT INTO my_record_container make2db_map_protein_list();

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

      END IF;





      -- Restore Views

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

      IF (export_to_public > 0 AND update_internal_data = 0) THEN

        SELECT INTO my_record_container make2db_backup_views(FALSE) AS status;

        IF (my_record_container.status IS NOT TRUE) THEN

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

          RETURN 'false';

        END IF;

      END IF;





      RAISE NOTICE 'Data Updates Performed With Success!';

      RETURN 'true';



   END;

Function: core.make2db_update_internal_de( integer )

Returns: boolean

Language: PLPGSQL

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





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

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



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

-- If argument > 0 : all is replaced



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

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

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtDescription IS NOT NULL

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

       LOOP

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

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

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

            END IF;



	    IF my_argument > 0 THEN

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

	      my_status = 'true';

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

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

	      my_status = 'true';

	    END IF;

	    END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: core.make2db_update_internal_ec( )

Returns: boolean

Language: PLPGSQL

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





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

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



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

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

       -- The EC table is automatically updated once

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

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

       LOOP

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

	  THEN

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

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

	    my_status = 'true';

	  END IF;

       END LOOP;	 



       RETURN my_status;



   END;

Function: core.make2db_update_internal_gn( integer )

Returns: boolean

Language: PLPGSQL

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





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

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



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

-- If argument > 0 : all is replaced



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

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

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtGeneNames IS NOT NULL

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

       LOOP

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

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

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

	    END IF;

	    IF my_argument > 0 THEN

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

	      my_status = 'true';

	    ELSE IF my_record.geneNames IS NULL THEN

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

	      my_status = 'true';

	    END IF;

	    END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: core.make2db_update_internal_id( integer )

Returns: boolean

Language: PLPGSQL

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





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

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



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

-- If argument > 0 : all is replaced



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

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

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtID IS NOT NULL

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

       LOOP 

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

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

            THEN

	      IF my_argument > 0 THEN

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

	        my_status = 'true';

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

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

	        my_status = 'true';

	      END IF;

	      END IF;

            END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: core.make2db_update_internal_kw( integer )

Returns: boolean

Language: PLPGSQL

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





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

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



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

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



   DECLARE



            my_record RECORD;

	    my_record2 RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

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

                          WHERE Entry.AC =  ExternalMainXrefData.AC

                          AND ExternalMainXrefData.uniProtCategoryKeywords IS NOT NULL

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

       LOOP

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

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

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

	    END IF;

	    IF my_argument > 0 THEN

	      UPDATE Entry SET keywords = my_record2.uniProtCategoryKeywords WHERE AC = my_record.AC;

	      my_status = 'true';

	    ELSE IF my_record.keywords IS NULL THEN

	      UPDATE Entry SET keywords = my_record2.uniProtCategoryKeywords WHERE AC = my_record.AC;

	      my_status = 'true';

	    END IF;

	    END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: core.make2db_update_internal_primaryac( integer )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE LOCAL PRIMARY ACs REGARDING UNIPROT PRIMARY ACs. THE OLD ACs THEN BECOME LOCAL SECONDARY ACs. If argument > 0 : All ACs are updated to their corresponding UniProt Primary AC. If argument = 0 : No AC replacement. Only a potential new local secondary AC (corresponding to the Primary UniProt AC) is added.





-- FUNCTION TO UPDATE PRIMARY AC FROM THE EXTERNAL DATA TABLE REGARDING THE MAIN ENTRY TABLE

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





   DECLARE



            my_record RECORD;

	    my_status BOOLEAN;

	    my_argument ALIAS for $1;



   BEGIN





       SET search_path TO core,common;

       my_status = 'false';

       

       FOR my_record IN SELECT ExternalMainXrefData.AC, ExternalMainXrefData.uniProtAC, ExternalMainXrefData.uniProtID

                          FROM ExternalMainXrefData

                          WHERE ExternalMainXrefData.AC = ANY (ExternalMainXrefData.uniProtSecondaryAC)

       LOOP



            IF NOT EXISTS (SELECT AC FROM Entry WHERE AC = my_record.uniProtAC)

            THEN

              IF my_argument = 0 THEN

                IF NOT EXISTS (SELECT * FROM SecondaryAC WHERE secondaryAC = my_record.uniProtAC AND AC = my_record.AC) THEN

                  INSERT INTO SecondaryAC (secondaryAC, AC) VALUES (my_record.uniProtAC, my_record.AC);

                  my_status = 'true';

                END IF;

              ELSE

                DELETE FROM SecondaryAC WHERE secondaryAC = my_record.uniProtAC AND AC = my_record.AC;

                UPDATE Entry SET AC = my_record.uniProtAC WHERE AC = my_record.AC;

                INSERT INTO SecondaryAC (secondaryAC, AC) VALUES (my_record.AC, my_record.uniProtAC);

                IF NOT EXISTS (SELECT ID FROM Entry WHERE ID = my_record.uniProtID)

                THEN

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

                END IF;

                my_status = 'true';

              END IF;

            END IF;

	    

       END LOOP;	 



       RETURN my_status;



   END;

Function: core.make2db_update_sp_trembl_dr( )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE EntryXrefDB content regarding ExternalMainXrefData for Swiss-Prot & TrEMBL





-- FUNCTION TO UPDATE EntryXrefDB content regarding ExternalMainXrefData for Swiss-Prot & TrEMBL

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



   DECLARE



            my_record RECORD;

	    my_status BOOLEAN;



   BEGIN



       SET search_path TO core,common;

       my_status = 'false';

       

       -- for more consistency, add a rule clause (AC, 1 or 2) in EntryXrefDB table / 1 is SP, 2 is TrEMBL

       

       FOR my_record IN SELECT * FROM ExternalMainXrefData

       LOOP

       

          IF my_record.uniProtAC IS NOT NULL AND my_record.SPorTrEMBL THEN -- Swiss-Prot reference

	      IF EXISTS (SELECT AC FROM EntryXrefDB WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2) LIMIT 1)

              THEN

	          DELETE FROM EntryXrefDB WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2);

              END IF;                   

	      INSERT INTO EntryXrefDB (AC, XrefDBCode, XrefPrimaryIdentifier, XrefSecondaryIdentifier)

		   values (my_record.AC, 1, my_record.uniProtAC, my_record.uniProtID);

	      my_status = 'true';                  

	                                

          ELSE IF my_record.uniProtAC IS NOT NULL AND NOT my_record.SPorTrEMBL THEN -- TrEMBL reference

	      IF EXISTS (SELECT AC FROM EntryXrefDB WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2) LIMIT 1)

              THEN

	          DELETE FROM EntryXrefDB

                  -- test XrefDBCode 1, as automatically added DR for AC matching SP pattern assume the reference to be SP

	          WHERE AC = my_record.AC AND (XrefDBCode = 1 OR XrefDBCode = 2);

                END IF;                 

	        INSERT INTO EntryXrefDB (AC, XrefDBCode, XrefPrimaryIdentifier, XrefSecondaryIdentifier)

		    values (my_record.AC, 2, my_record.uniProtAC, my_record.uniProtID);

	        my_status = 'true';                 

                                 

	  END IF;	                      

	  END IF;                       

	                                

       END LOOP;	                

                                 

       RETURN my_status;

                                 

   END;

Function: core.make2db_update_version( boolean )

Returns: boolean

Language: PLPGSQL

FUNCTION TO UPDATE ENTRY VERSIONS WHEN annotationChanged IS TRUE. If argument is TRUE => Update Entry View No version will hold a 0





-- FUNCTION TO UPDATE ENTRY VERSIONS WHEN annotationChanged IS TRUE 

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



-- Argument: True -> update entry view



   DECLARE



     my_update_view ALIAS FOR $1;

     my_record RECORD;

     my_record_container RECORD;



   BEGIN



     SET DATESTYLE TO 'POSTGRES, EUROPEAN';



     -- initialisation part: we do not want any version to be zero (min 1) / if annotationChanged is true, the increment is treated after

     -- (this loop will be mainly executed at the beginning of the database life)

     FOR my_record IN

       SELECT AC FROM EntryVersionParent* WHERE version = 0 AND annotationChanged IS FALSE

     LOOP

       UPDATE EntryVersionParent* SET version = 1 WHERE version = 0 AND annotationChanged IS FALSE;

       IF (my_update_view IS TRUE) THEN

         SELECT INTO my_record_container make2db_construct_entry(my_record.AC); -- function defined in the final views

       END IF;

     END LOOP;





     SELECT INTO my_record AC FROM EntryVersionParent* WHERE EntryVersionParent.annotationChanged = TRUE LIMIT 1;

     IF NOT FOUND THEN

       RETURN FALSE;

     END IF;



     IF (my_update_view IS FALSE OR my_update_view IS NULL) THEN

       UPDATE EntryVersionParent SET version = version + 1, versionDate = CURRENT_DATE, annotationChanged = FALSE

         WHERE annotationChanged = TRUE;

       RETURN TRUE;

     END IF;



     FOR my_record IN

       SELECT AC FROM EntryVersionParent* WHERE annotationChanged = TRUE

     LOOP

       UPDATE EntryVersionParent* SET version = version + 1,

              versionDate = CURRENT_DATE, annotationChanged = FALSE

         WHERE annotationChanged = TRUE AND AC = my_record.AC;

       SELECT INTO my_record_container core.make2db_construct_entry(my_record.AC); -- function defined in the final views

     END LOOP;



     RETURN TRUE;



   END;

Function: core.trigger_analyte_sampleidtracker( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Analyte: Track the parental sampleID for all cyclic - children - Analytes from their direct parent





-- TRIGGER ON Analyte: Track the parental sampleID for all cyclic - children - Analytes from their direct parent

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



-- This will update sampleID for children *once* when entered. If parent changes, sampleID will not be synchronized!



   DECLARE



            my_record RECORD;

   

   BEGIN

 

        IF new.analyteParentID IS NOT NULL THEN

	       SELECT INTO my_record sampleID FROM Analyte WHERE analyteID = new.analyteParentID;

	       new.sampleID = my_record.sampleID;

	END IF;	    



        RETURN new;



   END;

Function: core.trigger_databaserelease_unto_release( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON common.Database: Insert into (or update) the Release Table according to the last update release data on common.Database





-- TRIGGER ON common.Database: Insert into (or update) the Release Table accordingly

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



   DECLARE



     my_record RECORD;



   BEGIN



     SELECT INTO my_record * FROM Release WHERE releaseNum = new.databaseRelease AND subRelease = new.databaseSubRelease;

     IF FOUND THEN -- side updates are performed over the database table!

       -- UPDATE Release SET releaseDate = new.databaseReleaseDate

       --  WHERE releaseNum = new.databaseRelease AND subRelease = new.databaseSubRelease; 

     ELSE

       INSERT INTO Release VALUES (new.databaseRelease, new.databaseSubRelease, new.databaseReleaseDate);

     END IF;



   return new;



   END;

Function: core.trigger_entry_ac_uppercase( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON ENTRY: Make sure AC's are in upper case (for case insensitivity)





-- TRIGGER ON ENTRY: Make sure AC's are in upper case (for case insensitivity)

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



   BEGIN

 

        new.AC = upper(new.AC);

        new.ID = upper(new.ID);

        new.description = common.substitute_text(new.description, ' precursor', '');



        IF new.AC = 'UNIDENTIFIED_SPOTS' THEN

          new.showFlag = 'false';

          -- new.showFlagSwitch = 'false';

        END IF;

        

        RETURN new;



   END;

Function: core.trigger_entry_annotationchanged( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Entry: Set *AnnotationChanged to true when appropriate



-- TRIGGER ON Entry: Set *AnnotationChanged to true when appropriate

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

   BEGIN  -- We need to act only when the update changes effectively some selected values

     IF (new.AC <> old.AC OR new.ID <> old.ID OR new.entryClass <> old.entryClass OR

         new.description <> old.description OR new.geneNames <> old.geneNames OR new.keywords <> old.keywords OR

         new.organismID <> old.organismID OR new.organismStrain <> old.organismStrain)

     THEN UPDATE core.EntryVersionGeneral SET annotationChanged = 'true' WHERE AC = new.AC;

     END IF;

     IF (new.identMethod <> old.identMethod)

     THEN UPDATE core.EntryVersion2D SET annotationChanged = 'true' WHERE AC = new.AC;

     END IF;

     return new;

   END;

Function: core.trigger_entry_synchronizegeneralandmapannotationflags( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Entry, DEPRECATED!: Synchronize Version and Annotation Flags between Maps and General Updates





-- TRIGGER ON Entry: Synchronize Version and Annotation Flags between Maps and General Updates

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

   

   BEGIN



        --IF new.releaseMapUpdate < new.releaseCreation THEN

	--  new.releaseMapUpdate = new.releaseCreation;

	--END IF;

   

        --IF new.releaseUpdate < new.releaseMapUpdate THEN

	--  new.releaseUpdate = new.releaseMapUpdate;

	--END IF;

	

	--IF new.mapAnnotationChanged IS TRUE THEN

	--  new.entryAnnotationChanged = 'true';

	--END IF;



        RETURN new;



   END;

Function: core.trigger_entryversionparent_annotationchanged_true( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON EntryVersionParent*: For updates, set annotationChanged to TRUE





-- TRIGGER ON EntryVersionParent*: For updates, set annotationChanged to TRUE

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



   BEGIN



     -- new.version = old.version + 1;

     -- new.versionDate = CURRENT_DATE;

     new.annotationChanged = TRUE;

     return new;



   END;

Function: core.trigger_entryxrefdb_annotationchanged( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON EntryXrefDB: Set *AnnotationChanged to true when appropriate



-- TRIGGER ON EntryXrefDB: Set Entry.*AnnotationChanged to true when appropriate

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

   BEGIN  -- We need to act only when the update changes effectively some selected values

     IF (new.XrefDBCode <> old.XrefDBCode OR new.XrefPrimaryIdentifier <> old.XrefPrimaryIdentifier OR

         new.XrefSecondaryIdentifier <> old.XrefSecondaryIdentifier OR new.XrefTertiaryIdentifier <> old.XrefTertiaryIdentifier)

     THEN UPDATE core.EntryVersionGeneral SET annotationChanged = 'true' WHERE AC = new.AC;

     END IF;

     return new;

   END;

Function: core.trigger_entryxrefdb_no_dynamic_redundancy( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON EntryXrefDBDynamic: Skip a Record on EntryXrefDBDynamic if same Xref exists on EntryXrefDB (update the later)





-- TRIGGER ON EntryXrefDBDynamic: Skip a Record if same Xref exists on EntryXrefDB (update the later)

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



   DECLARE

 

       my_record  RECORD;

       

   BEGIN

   

       SELECT INTO my_record XrefDBCode FROM XrefDB WHERE upper(XrefDBName) =

         (SELECT upper(XrefDBName) FROM XrefDBDynamic WHERE XrefDBCode = new.XrefDBCode LIMIT 1);

       IF my_record.XrefDBCode IS NOT NULL THEN

         IF EXISTS (SELECT * FROM EntryXrefDB WHERE AC = new.AC AND XrefDBCode = my_record.XrefDBCode

                     AND XrefPrimaryIdentifier = new.XrefPrimaryIdentifier AND activated IS TRUE) THEN

            UPDATE EntryXrefDB SET XrefSecondaryIdentifier = new.XrefSecondaryIdentifier WHERE

              AC = new.AC AND XrefDBCode = my_record.XrefDBCode AND XrefPrimaryIdentifier = new.XrefPrimaryIdentifier;

            RETURN NULL;

         ELSE

           RETURN new;

         END IF;

       ELSE

         RETURN new;

       END IF;

        

   END;

Function: core.trigger_gel_shortname_melanieid( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON GEL: Upper case shortName and replace melanieGeneratedID by shortName if melanieGeneratedID is NULL





-- TRIGGER ON GEL: Upper case shortName and replace melanieGeneratedID by shortName if melanieGeneratedID is NULL

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



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);



   BEGIN



        new.shortName = upper(new.shortName);

        

        IF new.melanieGeneratedID IS NOT NULL AND new.melanieGeneratedID <> '' THEN

          RETURN new;

        END IF;

        new.melanieGeneratedID = current_database() || ':' || new.shortName;



        RETURN new;



   END;

Function: core.trigger_geltissuesp_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON GelTissueSP: Upper case SP tissue names



-- TRIGGER ON GelTissueSP: Upper case SP tissue names

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

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        RETURN new;

   END;

Function: core.trigger_showflagswitchtrue( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER OON Tables containing ShowFlags: Turn showFlagSwitch to TRUE if showFlag becomes TRUE



-- TRIGGER ON Tables containing ShowFlags: Turn showFlagSwitch to TRUE if showFlag becomes TRUE

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



   BEGIN 

     IF (new.showFlag IS TRUE) THEN new.showFlagSwitch = 'true';

     END IF;

     return new;

   END;

Function: core.trigger_spot_insert( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON Spot: Insert a Defined & Increased SPOT/BAND ID if ID IS Not Defined





-- TRIGGER ON Spot: Insert a Defined & Increased SPOT/BAND ID if ID IS Not Defined

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

-- The ID starts with 1/2D-X100000 and is then incremental (hexadecimal)



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);



   BEGIN

 

        IF new.spotID <> '' THEN RETURN new;

        END IF;

 

        IF new.pI IS NOT NULL THEN

               SELECT INTO my_record spotID FROM Spot 

               WHERE pI IS NOT NULL AND spotID ~ '^2D-X' ORDER BY 1 DESC LIMIT 1;

               IF FOUND THEN my_last_spot := '2D-X' || int_to_hex(hex_to_int(substr(my_record.spotID, 5)) + 1);

               ELSE my_last_spot := '2D-X100000';

               END IF;    

        ELSE

               SELECT INTO my_record spotID FROM Spot 

               WHERE pI IS NULL AND spotID ~ '^1D-X' ORDER BY 1 DESC LIMIT 1;

               IF FOUND THEN my_last_spot := '1D-X' || int_to_hex(hex_to_int(substr(my_record.spotID, 5)) + 1);

               ELSE my_last_spot := '1D-X100000';

               END IF;          

        END IF;



        new.spotID = my_last_spot;



        RETURN new;



   END;

Function: core.trigger_spotdataparent_nextval_dataexpid( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON SpotDataParent*: Force dataExpID to hold nextval(make2db_spotDataExp)





-- TRIGGER ON SpotDataParent*: Force dataExpID to hold nextval(make2db_spotDataExp)

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



   BEGIN



     new.dataExpID = nextval(make2db_spotDataExp);

     return new;



   END;

Function: core.trigger_spotidentificationparent_nextval_identificationid( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON SpotIdentificationParent*: Force identificationID to hold nextval(make2db_spotIdentification)





-- TRIGGER ON SpotIdentificationParent*: Force identificationID to hold nextval(make2db_spotIdentification)

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



   BEGIN



     new.identificationID = nextval(make2db_spotIdentification);

     return new;



   END;

Function: core.trigger_tissuesp_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON TissueSP: Upper case SP tissue names



-- TRIGGER ON TissueSP: Upper case SP tissue names

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

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        RETURN new;

   END;

Function: core.trigger_tissuespaliase_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON TissueSPAliase: Upper case SP tissue names



-- TRIGGER ON TissueSPAliase: Upper case SP tissue names

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

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        new.Alias = upper(new.Alias);

        RETURN new;

   END;

Function: core.trigger_tissuesptissuemapping_uc( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON TissueSPTissueMapping: Upper case SP tissue names



-- TRIGGER ON TissueSPTissueMapping: Upper case SP tissue names

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

   BEGIN

        new.tissueSPName = upper(new.tissueSPName);

        RETURN new;

   END;

Function: core.trigger_userstamp_update( )

Returns: "trigger"

Language: PLPGSQL





-- Trigger function to automatically insert 'userStamp' and 'update' time values

-- This trigger is applied on every core table



   BEGIN



     new.userstamp = USER; new.update = now();

     return new;



   END;

Function: core.trigger_xrefdb_insensitivename( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive





-- TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive

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



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);

	    my_max_iteration INT2;

	    my_new_db_name VARCHAR;



   BEGIN

   

	my_max_iteration = 99; 

      

        IF EXISTS (SELECT * FROM core.XrefDB WHERE lower(XrefDBName) = lower(new.XrefDBName)) THEN

	

	        RAISE NOTICE 'INSERTING AN ALREADY EXISTING Database Name (%) in XrefDB: This field should be case-insensitive!',

		                 new.XrefDBName;

				 

	        FOR my_iteration IN 1..my_max_iteration

	        LOOP

		  my_new_db_name = new.XrefDBName || '_' || my_iteration::VARCHAR;

		  IF NOT EXISTS (SELECT * FROM core.XrefDB WHERE lower(XrefDBName) = lower(my_new_db_name)) THEN

		    new.XrefDBName = my_new_db_name;

		    RAISE NOTICE 'The database name has been changed to: %', new.XrefDBName;

		    RETURN new;

		  END IF;

		END LOOP;

		

		RAISE EXCEPTION 'This database name has already been modified % times!', my_max_iteration;

	

	END IF;

	

	

        RETURN new;



   END;

Function: core.trigger_xrefdbdynamic_insensitivename( )

Returns: "trigger"

Language: PLPGSQL

TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive





-- TRIGGER ON XrefDB: Make Database name (XrefDBName) case-insensitive

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



   DECLARE

 

            my_record RECORD;

            my_last_spot VARCHAR(16);

	    my_max_iteration INT2;

	    my_new_db_name VARCHAR;



   BEGIN

   

	my_max_iteration = 99; 

      

        IF EXISTS (SELECT * FROM core.XrefDBDynamic WHERE lower(XrefDBName) = lower(new.XrefDBName)) THEN

	

	        RAISE NOTICE 'INSERTING AN ALREADY EXISTING Database Name (%) in XrefDB: This field should be case-insensitive!',

		                 new.XrefDBName;

				 

	        FOR my_iteration IN 1..my_max_iteration

	        LOOP

		  my_new_db_name = new.XrefDBName || '_' || my_iteration::VARCHAR;

		  IF NOT EXISTS (SELECT * FROM core.XrefDBDynamic WHERE lower(XrefDBName) = lower(my_new_db_name)) THEN

		    new.XrefDBName = my_new_db_name;

		    RAISE NOTICE 'The database name has been changed to: %', new.XrefDBName;

		    RETURN new;

		  END IF;

		END LOOP;

		

		RAISE EXCEPTION 'This database name has already been modified % times!', my_max_iteration;

	

	END IF;

	

	

        RETURN new;



   END;

Schema log


Table: log.analyte

log.analyte Structure
F-Key Name Type Description
analyteid integer
sampleid integer
analytepreparationid integer
description text
analyteparentid integer
fromanalyzablesource integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.analyte Constraints
Name Constraint
analyte_modificationdate_check CHECK ((modificationdate IS NOT NULL))
analyte_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.analytepreparation

log.analytepreparation Structure
F-Key Name Type Description
analytepreparationid integer
preparationprotocol text
uri character varying(4096)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.analytepreparation Constraints
Name Constraint
analytepreparation_modificationdate_check CHECK ((modificationdate IS NOT NULL))
analytepreparation_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.analyzable

log.analyzable Structure
F-Key Name Type Description
analyzableid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.analyzable Constraints
Name Constraint
analyzable_modificationdate_check CHECK ((modificationdate IS NOT NULL))
analyzable_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.author

log.author Structure
F-Key Name Type Description
author character varying(1024)
article integer
authorpriority smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.author Constraints
Name Constraint
author_modificationdate_check CHECK ((modificationdate IS NOT NULL))
author_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.authorgroup

log.authorgroup Structure
F-Key Name Type Description
author character varying(1024)
article integer
authorpriority smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.authorgroup Constraints
Name Constraint
authorgroup_modificationdate_check CHECK ((modificationdate IS NOT NULL))
authorgroup_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.authorparent

log.authorparent Structure
F-Key Name Type Description
author character varying(1024)
article integer
authorpriority smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.authorparent Constraints
Name Constraint
authorparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
authorparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.biologicalprocess

log.biologicalprocess Structure
F-Key Name Type Description
ac character varying(32)
goid character varying(32)
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)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.biologicalprocess Constraints
Name Constraint
biologicalprocess_modificationdate_check CHECK ((modificationdate IS NOT NULL))
biologicalprocess_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.biosource

log.biosource Structure
F-Key Name Type Description
biosourceid integer
studygroupid integer
biosourceinformationid integer
biosourcepreparationid integer
description text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.biosource Constraints
Name Constraint
biosource_modificationdate_check CHECK ((modificationdate IS NOT NULL))
biosource_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.biosourceinformation

log.biosourceinformation Structure
F-Key Name Type Description
biosourceinformationid integer
organismid integer
organismstrain character varying(256)
straindetailsdescription text
individualscount character varying(256)
genotype character varying(16)
developmentalstage character varying(256)
anatomy character varying(256)
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)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.biosourceinformation Constraints
Name Constraint
biosourceinformation_modificationdate_check CHECK ((modificationdate IS NOT NULL))
biosourceinformation_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.biosourcepreparation

log.biosourcepreparation Structure
F-Key Name Type Description
biosourcepreparationid integer
preparationprotocol text
uri character varying(4096)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.biosourcepreparation Constraints
Name Constraint
biosourcepreparation_modificationdate_check CHECK ((modificationdate IS NOT NULL))
biosourcepreparation_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.book

log.book Structure
F-Key Name Type Description
bookid integer
booktitle character varying(256)
editors character varying(256)
publisher character varying(128)
city character varying(64)
year smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.book Constraints
Name Constraint
book_modificationdate_check CHECK ((modificationdate IS NOT NULL))
book_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.cellularcomponent

log.cellularcomponent Structure
F-Key Name Type Description
ac character varying(32)
goid character varying(32)
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)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.cellularcomponent Constraints
Name Constraint
cellularcomponent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
cellularcomponent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.citer

log.citer Structure
F-Key Name Type Description
author character varying(1024)
article integer
authorpriority smallint
unpublished boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.citer Constraints
Name Constraint
citer_modificationdate_check CHECK ((modificationdate IS NOT NULL))
citer_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.commententry2d

log.commententry2d Structure
F-Key Name Type Description
ac character varying(32)
commenttopicid integer
commentfreetext text
geldimension smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.commententry2d Constraints
Name Constraint
commententry2d_modificationdate_check CHECK ((modificationdate IS NOT NULL))
commententry2d_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.commententryfreetext

log.commententryfreetext Structure
F-Key Name Type Description
ac character varying(32)
commenttopicid integer
commentfreetext text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.commententryfreetext Constraints
Name Constraint
commententryfreetext_modificationdate_check CHECK ((modificationdate IS NOT NULL))
commententryfreetext_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.commententryparent

log.commententryparent Structure
F-Key Name Type Description
ac character varying(32)
commenttopicid integer
commentfreetext text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.commententryparent Constraints
Name Constraint
commententryparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
commententryparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.commenttopic

log.commenttopic Structure
F-Key Name Type Description
commenttopicid integer
commenttopicname character varying(64)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.commenttopic Constraints
Name Constraint
commenttopic_modificationdate_check CHECK ((modificationdate IS NOT NULL))
commenttopic_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.contact

log.contact Structure
F-Key Name Type Description
contactid integer
name character varying(256)
email character varying(256)
institution character varying(256)
address text
remark text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.contact Constraints
Name Constraint
contact_modificationdate_check CHECK ((modificationdate IS NOT NULL))
contact_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.dynamicremotemake2ddbinterface

log.dynamicremotemake2ddbinterface Structure
F-Key Name Type Description
interfaceid integer
interfaceuri text
dbnumber integer
dbname character varying(128)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.dynamicremotemake2ddbinterface Constraints
Name Constraint
dynamicremotemake2ddbinterface_modificationdate_check CHECK ((modificationdate IS NOT NULL))
dynamicremotemake2ddbinterface_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entry

log.entry Structure
F-Key Name Type Description
ac character varying(32)
id character varying(32)
entryclass character varying(32)
identmethod character varying(16)
description text
releasecreation integer
subreleasecreation integer
genenames character varying(1024)
keywords character varying(1024)
organismid integer
organismstrain character varying(256)
entrychecksum character(16)
showflag boolean
showflagswitch boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entry Constraints
Name Constraint
entry_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entry_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entrygelimage

log.entrygelimage Structure
F-Key Name Type Description
ac character varying(32)
gelid integer
showflag boolean
showflagswitch boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entrygelimage Constraints
Name Constraint
entrygelimage_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entrygelimage_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entrygelmaster

log.entrygelmaster Structure
F-Key Name Type Description
ac character varying(32)
gelid integer
showflag boolean
showflagswitch boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entrygelmaster Constraints
Name Constraint
entrygelmaster_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entrygelmaster_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entrygene

log.entrygene Structure
F-Key Name Type Description
ac character varying(32)
geneid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entrygene Constraints
Name Constraint
entrygene_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entrygene_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entrytheoreticalpimw

log.entrytheoreticalpimw Structure
F-Key Name Type Description
ac character varying(32)
modifiedproteindescription text
theoreticalpi numeric(4,2)
theoreticalmw integer
algorithmoriginandversion text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entrytheoreticalpimw Constraints
Name Constraint
entrytheoreticalpimw_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entrytheoreticalpimw_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entryversion2d

log.entryversion2d Structure
F-Key Name Type Description
ac character varying(32)
version integer
versiondate date
annotationchanged boolean
annotationchecksum character(16)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entryversion2d Constraints
Name Constraint
entryversion2d_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entryversion2d_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entryversiongeneral

log.entryversiongeneral Structure
F-Key Name Type Description
ac character varying(32)
version integer
versiondate date
annotationchanged boolean
annotationchecksum character(16)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entryversiongeneral Constraints
Name Constraint
entryversiongeneral_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entryversiongeneral_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entryversionparent

log.entryversionparent Structure
F-Key Name Type Description
ac character varying(32)
version integer
versiondate date
annotationchanged boolean
annotationchecksum character(16)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entryversionparent Constraints
Name Constraint
entryversionparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entryversionparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entryxrefdb

log.entryxrefdb 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
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entryxrefdb Constraints
Name Constraint
entryxrefdb_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entryxrefdb_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entryxrefdbdynamic

log.entryxrefdbdynamic 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
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entryxrefdbdynamic Constraints
Name Constraint
entryxrefdbdynamic_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entryxrefdbdynamic_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.entryxrefdbparent

log.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
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.entryxrefdbparent Constraints
Name Constraint
entryxrefdbparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
entryxrefdbparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.enzymenomenclature

log.enzymenomenclature Structure
F-Key Name Type Description
enzymecode character varying(16)
ac character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.enzymenomenclature Constraints
Name Constraint
enzymenomenclature_modificationdate_check CHECK ((modificationdate IS NOT NULL))
enzymenomenclature_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.externalmainxrefdata

log.externalmainxrefdata Structure
F-Key Name Type Description
ac character varying(32)
uniprotextractiondate date
uniprotac character varying(32)
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
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.externalmainxrefdata Constraints
Name Constraint
externalmainxrefdata_modificationdate_check CHECK ((modificationdate IS NOT NULL))
externalmainxrefdata_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.gel

log.gel Structure
F-Key Name Type Description
gelid integer
melaniegeneratedid character varying(64)
shortname character varying(32)
fullname character varying(256)
dimension smallint
startmw bigint
endmw bigint
startpi numeric(4,2)
endpi numeric(4,2)
organismid integer
organismstrain character varying(256)
gelparentid integer
gelpreparationid integer
gelinformaticsid integer
biosourceinformationid integer
showflag boolean
showflagswitch boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.gel Constraints
Name Constraint
gel_modificationdate_check CHECK ((modificationdate IS NOT NULL))
gel_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.gelcomputabledynamic

log.gelcomputabledynamic Structure
F-Key Name Type Description
databasename character varying(64)
gelshortname character varying(32)
gelfullname character varying(256)
organismspecies text
taxonomycode integer
gelcomputableurl text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.gelcomputabledynamic Constraints
Name Constraint
gelcomputabledynamic_modificationdate_check CHECK ((modificationdate IS NOT NULL))
gelcomputabledynamic_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.geldynamic

log.geldynamic Structure
F-Key Name Type Description
databasename character varying(64)
gelshortname character varying(32)
gelfullname character varying(256)
organismspecies text
taxonomycode integer
tissuename character varying(256)
tissuespname character varying(256)
gelurl text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.geldynamic Constraints
Name Constraint
geldynamic_modificationdate_check CHECK ((modificationdate IS NOT NULL))
geldynamic_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.gelimage

log.gelimage Structure
F-Key Name Type Description
gelid integer
masterimage oid
imageurl text
imagepath text
imagetype character varying(3)
smallimageurl text
smallimagepath text
smallimagetype character varying(3)
xpixelsize smallint
ypixelsize smallint
xratio numeric(6,4)
yratio numeric(6,4)
xpixelshift smallint
ypixelshift smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.gelimage Constraints
Name Constraint
gelimage_modificationdate_check CHECK ((modificationdate IS NOT NULL))
gelimage_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.gelinformatics

log.gelinformatics Structure
F-Key Name Type Description
gelinformaticsid integer
uri character varying(4096)
informaticsdocument text
informaticsdescription text
soft text
contactid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.gelinformatics Constraints
Name Constraint
gelinformatics_modificationdate_check CHECK ((modificationdate IS NOT NULL))
gelinformatics_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.gelpreparation

log.gelpreparation Structure
F-Key Name Type Description
gelpreparationid integer
analyteid integer
uri character varying(4096)
preparationdocument text
preparationdescription text
contactid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.gelpreparation Constraints
Name Constraint
gelpreparation_modificationdate_check CHECK ((modificationdate IS NOT NULL))
gelpreparation_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.geltissuesp

log.geltissuesp Structure
F-Key Name Type Description
gelid integer
tissuespname character varying(256)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.geltissuesp Constraints
Name Constraint
geltissuesp_modificationdate_check CHECK ((modificationdate IS NOT NULL))
geltissuesp_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.genename

log.genename Structure
F-Key Name Type Description
geneid integer
genename character varying(16)
organismid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.genename Constraints
Name Constraint
genename_modificationdate_check CHECK ((modificationdate IS NOT NULL))
genename_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.geneontologyparent

log.geneontologyparent Structure
F-Key Name Type Description
ac character varying(32)
goid character varying(32)
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)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.geneontologyparent Constraints
Name Constraint
geneontologyparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
geneontologyparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.geneorderedlocus

log.geneorderedlocus Structure
F-Key Name Type Description
geneid integer
orderedlocusname character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.geneorderedlocus Constraints
Name Constraint
geneorderedlocus_modificationdate_check CHECK ((modificationdate IS NOT NULL))
geneorderedlocus_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.geneorf

log.geneorf Structure
F-Key Name Type Description
geneid integer
orfname character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.geneorf Constraints
Name Constraint
geneorf_modificationdate_check CHECK ((modificationdate IS NOT NULL))
geneorf_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.generaltopicdefinition

log.generaltopicdefinition Structure
F-Key Name Type Description
generaltopicid integer
topicname character varying(64)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.generaltopicdefinition Constraints
Name Constraint
generaltopicdefinition_modificationdate_check CHECK ((modificationdate IS NOT NULL))
generaltopicdefinition_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.generaltopicentrydata

log.generaltopicentrydata Structure
F-Key Name Type Description
topicdataid integer
generaltopicid integer
topicdatatext text
allreferences integer[]
ambiguousreference boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.generaltopicentrydata Constraints
Name Constraint
generaltopicentrydata_modificationdate_check CHECK ((modificationdate IS NOT NULL))
generaltopicentrydata_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.genesynonym

log.genesynonym Structure
F-Key Name Type Description
geneid integer
synonym character varying(16)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.genesynonym Constraints
Name Constraint
genesynonym_modificationdate_check CHECK ((modificationdate IS NOT NULL))
genesynonym_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.journal

log.journal Structure
F-Key Name Type Description
journalid integer
journalname character varying(256)
url text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.journal Constraints
Name Constraint
journal_modificationdate_check CHECK ((modificationdate IS NOT NULL))
journal_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.make2ddbtool

log.make2ddbtool Structure
F-Key Name Type Description
version numeric(4,2)
subversion character varying(64)
versiondate date
postgresqlversion text
action text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.make2ddbtool Constraints
Name Constraint
make2ddbtool_modificationdate_check CHECK ((modificationdate IS NOT NULL))
make2ddbtool_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.mappingtopicdefinition

log.mappingtopicdefinition Structure
F-Key Name Type Description
mappingtechnique character varying(8)
techniquedescription character varying(256)
containingregexp text
excludingregexp text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.mappingtopicdefinition Constraints
Name Constraint
mappingtopicdefinition_modificationdate_check CHECK ((modificationdate IS NOT NULL))
mappingtopicdefinition_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.molecularfunction

log.molecularfunction Structure
F-Key Name Type Description
ac character varying(32)
goid character varying(32)
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)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.molecularfunction Constraints
Name Constraint
molecularfunction_modificationdate_check CHECK ((modificationdate IS NOT NULL))
molecularfunction_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.organism

log.organism Structure
F-Key Name Type Description
organismid integer
organismspecies text
organismclassification text
taxonomyxrefdbcode integer
taxonomycode integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.organism Constraints
Name Constraint
organism_modificationdate_check CHECK ((modificationdate IS NOT NULL))
organism_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.project

log.project Structure
F-Key Name Type Description
projectid integer
description text
uri character varying(4096)
contactid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.project Constraints
Name Constraint
project_modificationdate_check CHECK ((modificationdate IS NOT NULL))
project_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.reference

log.reference Structure
F-Key Name Type Description
referenceid integer
referenceworkid integer
referencetitle text
referencetypeid integer
referencechecksum character(16)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.reference Constraints
Name Constraint
reference_modificationdate_check CHECK ((modificationdate IS NOT NULL))
reference_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencedentry

log.referencedentry Structure
F-Key Name Type Description
referenceid integer
ac character varying(32)
rndisplayedvalue smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencedentry Constraints
Name Constraint
referencedentry_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencedentry_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencedgel

log.referencedgel Structure
F-Key Name Type Description
referenceid integer
gelid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencedgel Constraints
Name Constraint
referencedgel_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencedgel_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencedobjectparent

log.referencedobjectparent Structure
F-Key Name Type Description
referenceid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencedobjectparent Constraints
Name Constraint
referencedobjectparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencedobjectparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencedproject

log.referencedproject Structure
F-Key Name Type Description
referenceid integer
projectid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencedproject Constraints
Name Constraint
referencedproject_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencedproject_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencedsample

log.referencedsample Structure
F-Key Name Type Description
referenceid integer
sampleid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencedsample Constraints
Name Constraint
referencedsample_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencedsample_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationbook

log.referencelocationbook Structure
F-Key Name Type Description
referenceid integer
bookid integer
volume smallint
pagefirst integer
pagelast integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationbook Constraints
Name Constraint
referencelocationbook_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationbook_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationjournal

log.referencelocationjournal Structure
F-Key Name Type Description
referenceid integer
journalid integer
volume smallint
subvolume smallint
pagefirst integer
pagelast integer
year smallint
crossreferences character varying(128)[]
unpublished boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationjournal Constraints
Name Constraint
referencelocationjournal_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationjournal_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationother

log.referencelocationother Structure
F-Key Name Type Description
referenceid integer
text text
personalcomment character varying(256)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationother Constraints
Name Constraint
referencelocationother_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationother_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationparent

log.referencelocationparent Structure
F-Key Name Type Description
referenceid integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationparent Constraints
Name Constraint
referencelocationparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationpatent

log.referencelocationpatent Structure
F-Key Name Type Description
referenceid integer
patent_publication_number character varying(16)
date date
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationpatent Constraints
Name Constraint
referencelocationpatent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationpatent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationsubmission

log.referencelocationsubmission Structure
F-Key Name Type Description
referenceid integer
month smallint
year smallint
xrefdbcode integer
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationsubmission Constraints
Name Constraint
referencelocationsubmission_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationsubmission_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationthesis

log.referencelocationthesis Structure
F-Key Name Type Description
referenceid integer
year smallint
institution character varying(128)
country character varying(64)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationthesis Constraints
Name Constraint
referencelocationthesis_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationthesis_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencelocationunpubobservations

log.referencelocationunpubobservations Structure
F-Key Name Type Description
referenceid integer
month smallint
year smallint
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencelocationunpubobservations Constraints
Name Constraint
referencelocationunpubobservations_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencelocationunpubobservations_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencetype

log.referencetype Structure
F-Key Name Type Description
referencetypeid integer
referencetype character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencetype Constraints
Name Constraint
referencetype_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencetype_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.referencework

log.referencework Structure
F-Key Name Type Description
referenceworkid integer
referenceworkdescription character varying(64)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.referencework Constraints
Name Constraint
referencework_modificationdate_check CHECK ((modificationdate IS NOT NULL))
referencework_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.release

log.release Structure
F-Key Name Type Description
releasenum integer
subrelease smallint
releasedate date
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.release Constraints
Name Constraint
release_modificationdate_check CHECK ((modificationdate IS NOT NULL))
release_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.sample

log.sample Structure
F-Key Name Type Description
sampleid integer
biosourceid integer
uri character varying(4096)
samplepreparationid integer
description text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.sample Constraints
Name Constraint
sample_modificationdate_check CHECK ((modificationdate IS NOT NULL))
sample_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.samplepreparation

log.samplepreparation Structure
F-Key Name Type Description
samplepreparationid integer
uri character varying(4096)
samplepreparationdocument text
samplepreparationprotocol text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.samplepreparation Constraints
Name Constraint
samplepreparation_modificationdate_check CHECK ((modificationdate IS NOT NULL))
samplepreparation_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.secondaryac

log.secondaryac Structure
F-Key Name Type Description
secondaryac character varying(32)
ac character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.secondaryac Constraints
Name Constraint
secondaryac_modificationdate_check CHECK ((modificationdate IS NOT NULL))
secondaryac_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spot

log.spot Structure
F-Key Name Type Description
spotid character varying(16)
gelid integer
mw integer
pi numeric(4,2)
xcoordinate integer
ycoordinate integer
odrelative real
volumerelative real
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spot Constraints
Name Constraint
spot_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spot_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotdataaacid

log.spotdataaacid Structure
F-Key Name Type Description
dataexpid integer
humanidentifier character varying(256)
spotid character varying(16)
gelid integer
referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean
showflagswitch boolean
aminoacidlist text
relateddata text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotdataaacid Constraints
Name Constraint
spotdataaacid_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotdataaacid_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotdataother

log.spotdataother Structure
F-Key Name Type Description
dataexpid integer
humanidentifier character varying(256)
spotid character varying(16)
gelid integer
referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean
showflagswitch boolean
results text
relateddata text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotdataother Constraints
Name Constraint
spotdataother_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotdataother_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotdataparent

log.spotdataparent Structure
F-Key Name Type Description
dataexpid integer
humanidentifier character varying(256)
spotid character varying(16)
gelid integer
referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean
showflagswitch boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotdataparent Constraints
Name Constraint
spotdataparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotdataparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotdatapeptmassf

log.spotdatapeptmassf Structure
F-Key Name Type Description
dataexpid integer
humanidentifier character varying(256)
spotid character varying(16)
gelid integer
referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean
showflagswitch boolean
peptidemasses double precision[]
enzyme character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotdatapeptmassf Constraints
Name Constraint
spotdatapeptmassf_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotdatapeptmassf_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotdatatandemms

log.spotdatatandemms Structure
F-Key Name Type Description
dataexpid integer
humanidentifier character varying(256)
spotid character varying(16)
gelid integer
referenceid integer
contactid integer
uri character varying(4096)
datadocument text
experimentdescription text
experimentdate date
appreciation smallint
datadisplayer text
showflag boolean
showflagswitch boolean
parentmass double precision
parentcharge smallint
ionmasses double precision[]
relateddata text
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotdatatandemms Constraints
Name Constraint
spotdatatandemms_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotdatatandemms_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotentry

log.spotentry Structure
F-Key Name Type Description
spotid character varying(16)
gelid integer
ac character varying(32)
fragment boolean
showflag boolean
showflagswitch boolean
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotentry Constraints
Name Constraint
spotentry_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotentry_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotentrygeneraltopic

log.spotentrygeneraltopic Structure
F-Key Name Type Description
topicdataid integer
spotid character varying(16)
gelid integer
ac character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotentrygeneraltopic Constraints
Name Constraint
spotentrygeneraltopic_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotentrygeneraltopic_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotentrymappingtopic

log.spotentrymappingtopic Structure
F-Key Name Type Description
topicdataid integer
spotid character varying(16)
gelid integer
ac character varying(32)
mappingtechnique character varying(8)[]
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotentrymappingtopic Constraints
Name Constraint
spotentrymappingtopic_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotentrymappingtopic_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotentrytopicparent

log.spotentrytopicparent Structure
F-Key Name Type Description
topicdataid integer
spotid character varying(16)
gelid integer
ac character varying(32)
userstamp character varying(128)
modificationdate timestamp without time zone DEFAULT now()
updatedordeleted character(1)
usermodifiername character varying(128)

 

log.spotentrytopicparent Constraints
Name Constraint
spotentrytopicparent_modificationdate_check CHECK ((modificationdate IS NOT NULL))
spotentrytopicparent_updatedordeleted_check CHECK (((updatedordeleted = 'U'::bpchar) OR (updatedordeleted = 'D'::bpchar)))

Index - Schema log


Table: log.spotidentificationaacid

log.spotidentificationaacid Structure
F-Key Name Type Description
identificationid integer
humanidentifier character varying(256)
hassubset boolean
dataexpid integer
spotid character varying(16)
gelid integer
ac character varying(32)
isoform text
version integer
contactid integer
uri character varying(4096)
identificationdocument text
identificationdescription text
allexperimentdata boolean
appreciation numeric(2,1)
datadisplayer text
showflag boolean
showflagswitch boolean
aminoacidlist text
relateddata text
userstamp ch