World-2DPAGE Home
To improve security and privacy, we are moving our web pages and services from HTTP to HTTPS.
To give users of web services time to transition to HTTPS, we will support separate HTTP and HTTPS services until the end of 2017.
From January 2018 most HTTP traffic will be automatically redirected to HTTPS. [more...]
View this page in https

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