resource logo

World-2DPAGE Constellation

Attention: World-2DPAGE is no longer maintained.

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 


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

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 database
User
Select
Insert
Update
Delete
Reference
Rule
Trigger
mostaguir
select2d

Index - Schema common


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

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict