ontology_annotator.cgi - Main file with code for ontology_annotator. CGI for curators to load and make curations through a web browser (tested with Mozilla's Firefox).
Place on webserver with ontology_annotator.css, ontology_annotator.js, helperOA.pm, yui/ and images/ directories, and MOD-specific perl module (e.g. testOA.pm , wormOA.pm). Create appropriate MOD-specific perl module for your MOD, in that module add settings for each datatype to curate, and create a postgres table for each datatypes's fields. Requires web server, CGI, and postgreSQL.
CGI file for curators to load through a web browser. When using for a new MOD, create a MOD-specific perl module (see testOA.pm for template and instructions, or wormOA.pm for example and instructions). In this file add to :
ontology_annotator.css REQUIRED has the css properties to display one row in each dataTable, show red squares in dataTable cells that have data not showing, extra padding on autocomplete results div html elements.
ontology_annotator.js REQUIRED has all the Ontology Annotator-specific javascript code.
helperOA.pm REQUIRED perl module that has helper subroutines for the main CGI and MOD-specific perl modules. Get and untaint form variables. Get timestamps in postgres format.
yui/ REQUIRED directory to hold local copies of the necessary YUI's .js files. YUI's website is fast and reliable, but just in case keep a local copy.
images/ REQUIRED directory to hold necessary images. The red square for dataTable cells with more data than showing. Loading image when querying.
MOD-specific perl modules. REQUIRED. See testOA.pm, wormOA.pm for examples.
docs/ OPTIONAL directory to hold html files documenting how scripts, modules, and the CGI work. Generated by scripts/generateDocs.pl .
scripts/ OPTIONAL directory to hold perl scripts to generate tables, update ontologies, generate documentation.
There are general postgres tables required for all MODs and datatypes, for curator settings. There are datatype-specific postgres tables for each MOD's datatypes's field. See below for specific formats.
Be sure to GRANT ALL on each table to postgres and the webserver user.
Note: It's very likely that a different flavor of SQL may be used with minor code change, but this has not been tested.
For the OA to remember a given curator-datatype-field dataTable column's width, order, and whether it should show or be hidden, these tables are required :
If some MOD really doesn't want this, &updatePostgresColumn could probably be edited to only do this for specific $configLoaded , and this code change would have to be propagated to other MODs.
create_oac_columns.pl is a script to delete and re-create these column postgres tables.
When creating a new datatype, each field should have its own postgres table and postgres history table. The datatype by convention is a three-letter code. The format of each table would be '<datatype>_<field name>' and for history tables '<datatype>_<field name>_hst', so e.g. : 'app_term' 'app_term_hst', 'int_variationone' 'int_variationone_hst', 'trp_driven_by_gene' 'trp_driven_by_gene_hst'.
Columns are joinkey, <table_name>, <datatype>_timestamp . The <datatype>_timestamp column is a timestamp with default now, all others are text. The <table_name> column is the same as the name of the table. e.g. the table app_term has columns named joinkey, app_term, app_timestamp ; and app_term_hst has columns named joinkey, app_term_hst, app_timestamp .
There are four different ways of storing data :
create_datatype_tables.pl is a script to create postgres data tables and history tables for a given datatype and tables.
When creating an ontology or multiontology field, a set of custom subroutines can be created in the MOD-specific perl module (called by &getAnySpecific...), or the local &getGenericObo... subroutines, which work off generic obo_ tables in postgres. To use the latter subroutines, in the MOD-specific perl module, set the fields's %fields to have 'ontology_type' = 'obo' and 'ontology_table' = '<name of ontology type>' . The name of the ontology type is lowercase by convention. These two values are set to all fields that use that given ontology for the same MOD ; multiple datatypes can use it, and/or multiple fields in the same datatype. Also to use these subroutines, create 3 tables for each set of ontologies :
update_obo_oa_ontologies.pl is a script to download generic .obo files and update these 3 tables for each corresponding obo ontology type.
$configLoaded is a variable to specify which MOD-specific perl module has been loaded. Refer to appropriate subroutine in &asyncTermInfo &asyncValidValue &autocompleteXHR &getTableDisplayData &showLogin &showMain &initFields .
$filtersMaxAmount is the amount of filters to put in the OA's control frame. This could be stored in a postgres table at some point, and code rewritten for it. The control frame shows one filter by default, and more can be shown by changing the number in the dropdown.
%fieldIdToValue is a hash to map IDs of data in postgres to what should be displayed in the dataTable when querying. When a query will load a lot of values into the dataTable it's wasteful to query each ID to displayValue conversion multiple times for a single ID, so the values are stored in this hash and not re-queried if they already exist. The first key is the dropdown_type for dropdown / multidropdown fields ; for ontology / multiontology fields with simple obo_ tables, it's the 'obo_name_<ontology_table> ; for ontology / multiontology fields with a custom specific lookup, it's the ontology type. The second key is the ID of the value. The value of the hash is what should be displayed, "display_value<span style='display: none'>id</span>" ; the display_value is usually the name of the object, but it could be the ID in cases like WBPapers ; the id is the ID of the object ; the <span>s are html element tags to keep the id hidden in the dataTable display, and are used for getting the IDs in the cell when a dataTable row is clicked and they should be loaded to the OA's editor frame's corresponding fields (some display_values map to multiple IDs in some datatypes).
%fields stores options for the datatype configuration's individual fields, in the format $fields{<datatype>}{<field_name>}{<option>} . Its values are initialized from the MOD-specific perl module, and returned here.
%datatypes stores options for each datatype configuration as a whole, in the format $datatypes{<datatype>}{<option>} . Its values are initialized from the MOD-specific perl module, and returned here.
$dbh is the DBI database handle for postgreSQL queries.
$query is the CGI query object.
$var is a placeholder variable when getting values from the form.
$action is the action performed by the curator on the form.
&showLogin if there's no curator $action, show a login page, call &initFields and the appropriate subroutine for the curator's $action . &showLogin has an html form that gets the login options from the MOD-specific perl module, and also shows gives options for max_per_query and batch_unsafe_flag . max_per_query is the maximum amount of entries to get from postgres when querying for previously curated data ; returning too many entries would slow down or freeze the browser depending on the user's computer and the amount of entries ; a curator can query multiple times to keep getting more values. batch_unsafe_flag is a toggle for the javascript to use the batchUnsafeUpdateDataTableValues function instead of the defaul normalSafeUpdateDataTableValues ; the normal way sends an AJAX call for each row to change, gives individual errors, and updates the dataTable as appropriate ; the batch_unsafe way is a faster way to update multiple postgres entries / dataTable rows, with a single AJAX call for all of them, a single error for all entries, and does not reflect changes in the dataTable ; if this value is toggled, the OA's editor frame has a different background and warning text.
&initFields calls the MOD-specific subroutine to initialize %fields and %datatypes for that curator-datatype. It also queries the oac_ postgres tables for that curator-datatype.
&showMain gets the login variables, IP address, calls the MOD-specific login subroutine, and creates an html page with four frames :
&showEditor shows the OA editor frame. Gets user values and makes hidden inputs for the ontology_annotator.js to get them as needed. If the batch_unsafe_flag is set, the background color of the frame is different. Creates an html table, and for each %fields's datatype field, it sets some variables :
and creates a table row, calling &generateEditorLabelTd to generate and append the field's label to the row, then depending on the $field_type it calls the appropriate &showEditor... subroutine(s) to create the html elements to display in the table. Fields that are neither multidropdown nor multiontology also generate and print the value from &generateEditorSpantypeQuerybuttonTds , because these fields are a single table row so their creation subroutines don't add this. Creates a 'Reset' button that floats right, which calls ontology_annotator.cgi function resetButton which reloads the obo frame, removes all rows from the dataTable, and blanks all editor values. Shows the configuration loaded from $datatypes{$datatype}{label}. Creates a tab html button for each tab in %tabs which when clicked shows only table rows corresponding to that tab. If batch_unsafe_flag is set, a warning text is displayed in an html span element.
If the $field_type corresponds to something that should show multiple fields in one row, $colspan changes to more values will fit, and the appropriate separate &showEditor... subroutines are called.
&generateEditorLabelTd generates an html td element, passing in the field and datatype, and returning the td generated td element. Gets <label> from $fields{<datatype>}{<field>}{'label'} . Creates a td element with id 'label_<field>' and <label> for text, the returns it.
&generateEditorSpantypeQuerybuttonTds generates two html td elements, passing in the field, border_style, and span_type ; and returning the created td elements. The first td element has a span with id 'type_<field>', html style's border-style <border_style> and border-color 'grey', and text of <span_type>. The second td element has a button with id 'button_<field>' and text 'query'.
&showEditorText creates an html td element for normal input fields, passing in the field, datatype, input_size, and colspan ; and returning the created td element. Creates a td element of fixed 150 width, colspan <colspan>, and an input field with id 'input_<field>' and size <input_size>. The width is fixed because of some auto-sizing to nothing if there is no size.
&showEditorTextarea is like &showEditorText but is a textarea instead of an input html element.
&showEditorBigtext is like &showEditorText but has an additional html div element with id 'container_bigtext_<field>', which contains a textarea with id 'textarea_bigtext_<field>', with rows <rows_size> or 20 if no value given, cols <cols_size> or <input_size> if no value given, and default style 'display:none'. When the input field is clicked, the ontology_annotator.js transfers the value to the textarea, hides the input, and shows the textarea. When blurring the textarea, the ontology_annotator.js transfers the value to the input, hides the textarea, and shows the input. This allows editing and seeing a large amount of text in a textarea, but normally only seeing a smaller input that takes up less space.
&showEditorQueryonly creates an html td element for query only fields, passing in the field, datatype, colspan ; and returning the created td element. Creates a td element with id 'queryonly_<field>', colspan <colspan>, align 'center', and width of <colspan> / 3 * 100% which makes it take up the full width. When queried jsonFieldQuery gets the custom query from $fields{<datatype>}{<field>}{queryonlySub} instead of the field's non-existent postgres table.
&showEditorToggle creates an html td element for query only fields, passing in the field, datatype, colspan ; and returning the created td element. Creates a td element with id 'toggle_<field>', colspan <colspan>, align 'center', and width of <colspan> / 3 * 100% which makes it take up the full width. Background color is 'white' to show 'false' and when clicked, ontology_annotator.js toggles the background color to 'red' to show 'true'.
&showEditorDropdown creates an html td element for dropdown fields, passing in the field, datatype, input_size, colspan, freeForced ; and returning the created td element. Creates a td element of fixed width 150 and colspan <colspan> containing an html span of id 'container<freeForced><field>AutoComplete', used by YUI to create autocomplete fields. This span contains an html div element with id '<freeForced><field>AutoComplete' and class 'div-autocomplete', which contains an html input element with id 'input_<field>' and size <input_size>, and another html div element with id '<freeForced><field>Container'. Anything typed in the input field will have the ontology_annotator.js make an AJAX call to the CGI with action 'autocompleteXHR' to get matching values for the corresponding datatype-field-curator, and display them in the container div. Dropdown fields can click the span_type to pass a blank as the query value, returning all values for that datatype-field-curator.
&showEditorOntology is identical to &showEditorDropdown but has a separate subroutine, should the display need to become different. The ontology_annotator.js does not set the span_type to query for all values, since ontology fields have too many values for this to be practical.
&showEditorMultidropdown creates html td and tr elements for multidropdown fields, passing in the field, datatype, input_size, span_type, colspan, freeForced ; and returning the created td and tr elements. Gets <tab> from $fields{<datatype>}{<field>}{'tab'} . Generates a dropdown td element with &showEditorDropdown . Generates html span_type and query button with &generateEditorSpantypeQuerybuttonTds . Closes the html tr element. Creates a new html tr element with class <tab>. Adds to it a blank html td element for indentation to show it is part of the same field despite being a new row. Adds to it another html td element containing an html select element with id 'select_<field>' of size 1 allowing multiple values to be selected. Adds another html td element, containing an html button with id 'button_remove_<field>' and label 'remove'. When adding a new value in the autocomplete input element, ontology_annotator.js adds it to the select element, and resizes the select element to a size equal to the elements in the field so all values always show in it. Clicking the remove button calls ontology_annotator.js removeSelectFieldEntriesListener which removes all selected values from the select element and resizes the select element to a size equal to the elements in the select field so it takes up no more space than needed to show all values.
&showEditorMultiontology is identical to &showEditorMultidropdown but has a separate subroutine, should the display need to become different. The ontology_annotator.js does not set the span_type to query for all values, since ontology fields have too many values for this to be practical.
&showObo shows the OA obo / term information frame. Gets text, term_id, datatype, field, obotable from the form. Creates an html div element with id 'termInfo' with <term_data> as text. Creates an html div element with id 'myObo' as a placeholder for debugging messages. The <term_data> comes from &getAnyTermInfo . The ac_value is the '<term_name> ( <term_id> ) ' from <term_data>. Links are created and added at the top : an arrow left (html ⇐ character) to go back a frame ; an arrow right (html ⇒ character) to go forward a frame ; a "check mark" (html √ character) to set the value of the 'input_<field>' to <ac_value>, focus, and blur (to trigger the ontology_annotator.js blur action to update postgres and the dataTable. The value of the name tag is moved inside the span to bold it.
&getAnyTermInfo generates term_data, passing in datatype, field, term_id ; and returning the term data. If the field's ontology_type is 'obo', it calls &getGenericOboTermInfo to get data from generic postgres obo_ tables. Otherwise it checks the $configLoaded and calls the MOD-specific subroutine to get term info ; if any are loaded it calls &getAnySpecificTermInfo .
&getGenericOboTermInfo generates term_data from generic postgres obo_ tables, passing in datatype, field, userValue ; and returning the term data. Gets the curator from the form. Get the obotable from $fields{<datatype>}{<field>}{'ontology_table'} . Gets the obo_ joinkey from the userValue. Query postgres obo_data_<obotable>. Return blank '' if there is no data. Replace newlines with html br elements + newlines. If the data has 'action=oboFrame', globally replace it with same + "field=<field>&datatype=<datatype>&curator_two=<curator>", which is necessary to have the proper values in the obo frame when traveling forward and back, and selecting a value. Return
&showControls shows the OA controls frame.
Gets the datatype from the form.
Creates an html div element with id 'loadingImage' to hold the image when loading data from a postgres query.
Creates an html button element with id 'newRow' to create a new dataTable row ; when clicked the ontology_annotator.js calls newRowButtonListener which does an AJAX call to the CGI with action 'newRow', creating and getting a new pgid / joinkey, then doing another AJAX call to the CGI with action jsonFieldQuery passing the new pgid and getting the created row to load and highlight in the dataTable.
Creates an html button element with id 'duplicateRow' to create a new dataTable row for each dataTable row that is selected, all values except for pgid / joinkey are duplicated ; when clicked the ontology_annotator.js calls duplicateRowButtonListener which does an AJAX call to the CGI with action 'duplicateByPgids', getting the newly created pgids, doing another AJAX call to the CGI with action jsonFieldQuery passing the new pgids and getting the created rows to load and highlight in the dataTable.
Creates an html button element with id 'deleteRow' to delete selected dataTable rows ; when clicked the ontology_annotator.js calls deleteRowButtonListener which does an AJAX call to the CGI with action 'deleteByPgids', deleting the entries from the postgres tables and from the dataTable.
Creates an html button element with id 'checkData' to optionally check data in the dataTable against datatype-specific constraints ; when clicked the ontology_annotator.js calls checkDataButtonListener which does an AJAX call to the CGI with action 'checkDataByPgids', if return values are not 'OK', a popup window is openend and focused, loading the messages from the return value.
Creates a hidden html input element with id 'filtersMaxAmount' that stores the maximum amount of filters.
Creates an html select element with id 'filtersAmount', and options 1 through the maximum amount of filters. When the form loads or this value changes, the ontology_annotator.js calls updateFiltersAmount which shows only that amount of filtering pairs (see next paragraph).
For the maximum amount of filters, creates pairs of html elements. An html select element with id 'filterType<count>' with options 'all' and each of the fields from $fields{<datatype>}. An html input element with id 'filterValue<count>' for the curator's text to filter on. When something is typed into an input, the ontology_annotator.js will show only the dataTable rows that contain the typed text in the corresponding 'filterType<count>' column.
&showTable shows the OA dataTable frame. This frame loads all the required .css and .js files into the html head element ; when reloading the form, be sure to reload this frame to update those files.
Gets the datatype from the form.
The html body element needs to have class 'yui-skin-sam'. When resized, the ontology_annotator.js calls resizeDataTable which sets the height of html div elements with class 'yui-dt-bd', because they tend to be too big and create scrollbars.
For each field from $fields{<datatype>}, create an html input element of class 'fields' with value <field>. For each field's tag, get data from $fields{<datatype>}{<field>}{<tag>} and create "'<tag>' : '<data>'" pairs to join with commas, and store in an html input field with id 'data_<field>'. The ontology_annotator.js looks up all the fields from the inputs of class 'fields', and gets the fieldsData from each field's 'data_<field>' html input element.
&jsonFieldQuery generates a text page for returning AJAX queries for postgres data. Gets userValue, datatype, field, maxPerQuery, allDataTableIds from form ; and returns JSON of return message and entries for data for each postgres joinkey / dataTable row. userValue is what to query for, datatype is the datatype to query for, field is the datatype's field to query on, maxPerQuery is the maximum rows to return, allDataTableIds are the joinkeys already in the dataTable that should be skipped when querying. Fields of type dropdown or multidropdown check the MOD-specific perl module for the subroutine to have their values added to $fieldType{<dropdown_type>}{<id>} = <autocomplete_value> where the autocomplete_value has the id hidden in a 'display: none' html span element, for loading into the dataTable. The pgtable to query is the '<datatype>_<field>', but if the field is 'id', it should instead query the tables from $datatypes{<datatype>}{highestPgidTables} . For each table to query : If the field type is multiontology or multidropdown, the value to query should have doublequotes around it ; query for the userValue on the table where the joinkey is not already in the dataTable in descending timestamp order. If the field type is ontology or dropdown ; query for the userValue on the table where the joinkey is not already in the dataTable in descending timestamp order. If the field type is neither, and the userValue to query has three or more characters, do an exact query ; if it has less than three characters do a substring query ; query for the userValue on the table where the joinkey is not already in the dataTable in descending timestamp order. If the field type is queryonly, remove the crafted queries, because queryonly fields do not correspond to postgres tables, and instead get the postgres query from $fields{<datatype>}{<field>}{queryonlySub} . Query postgres for those queries, and get the joinkeys / pgids, ignoring results over the maxPerQuery limit ; create returnMessage of values the query found. For each $fields{<datatype>} that has a postgres table, query for the data corresponding to the joinkeys to return, filtering the data through &getTableDisplayData and storing it in $hash{<joinkey>}{<field>} . Print to text page JSON format representation of hash, with the returnMessage as the first entry, and each joinkey's data as the next entries.
&getTableDisplayData converts data to proper JSON-format data, and if appropriate to the MOD-specific perl module adds any specific ontology or multiontology to the %fieldIdToValue. Passes in datatype, field, data ; and returns formatted data. If the field is of type dropdown or multidropdown, get the dropdown_type from $fields{<datatype>}{<field>}{dropdown_type} ; if data exists in $fieldIdToValue{<dropdown_type>}{<data>} use that as the data value. If the field is of type multiontology or ontology, and the field's ontology_type is 'obo', call &getGenericOboIdToValue ; otherwise check $configLoaded and call the appropriate subroutine ; if any are loaded it calls &getAnySpecificIdToValue and updates %fieldIdToValue from values there. Any data is cleaned through &parseJson .
&getGenericOboIdToValue converts generic obo values from postgres values (ids) to what someone types for dataTable display, passing in datatype, field, data ; and returning the parsed data. Get ontology_table from $fields{<datatype>}{<field>}{ontology_table} . Get obotable as 'obo_name_<ontology_table>'. Strip data of doublequotes (if an Id has a doublequote the lookup here will fail), split user data on commas in case it's multiontology data, and for each id : Check if it has already been looked up in $fieldIdToValue{<obotable>}{<id>} , using that value if so ; otherwise query the postgres table <obotable> for a joinkey of value <id>, and if matching create a value being the postgres table value + an html span element with style 'display: none' containing the <id> / joinkey ; if the field's type is multiontology add surrounding doublequotes ; assign this id-value pair to $fieldIdToValue{<obotable>}{<id>} for future lookup, and to the results to return. Join results with commas and return.
&getAnyMultidropdownIdToValue converts multidropdown postgres values (ids) to what someone types for dataTable display, passing in dropdown_type and data ; and returning the parsed data. Strip data of doublequotes (if an Id has a doublequote the lookup here will fail), split user data on commas in case it's multidropdown data, and for each id : Check if it has already been looked up in $fieldIdToValue{<dropdown_type>}{<id>} , using that value if so. Join results with doublequote-comma-doublequote, wrap in doublequotes, and return.
&parseJson escapes some xml characters, passing in data and returning the cleaned data. Newlines are globally replaced with spaces. Doublequotes are espaced with backslashes. Literal newlines are stipped.
&autocompleteXHR generates a text page for returning AJAX queries for autocompletion fields. From the form gets datatype, field, and words from the default YUI autocomplete field 'query' ; and prints rows of autocomplete values for the container html div element. Words are lower cased. If the field type is dropdown or multidropdown call &getAnySimpleAutocomplete . If the field type is ontology or multiontology, and the field's ontology_type is 'obo', call &getGenericOboAutocomplete ; otherwise check $configLoaded and call the appropriate subroutine ; if any are loaded it calls &getAnySpecificAutocomplete . Print result matches.
&getAnySimpleAutocomplete generates ordered autocomplete matches for dropdown or multidropdown fields, passing in datatype, field, words ; and returning the result matches. Get dropdown_type from $fields{<datatype>}{<field>}{dropdown_type} . Call the MOD-specific perl module subroutine to get the values for the dropdown_type. Return matching values.
&getGenericOboAutocomplete generates autocomplete matches from generic postgres obo_ tables, passing in datatype, field, words ; and returning the result matches. Get ontology_table from $fields{<datatype>}{<field>}{ontology_table} . max_results is the maximum amount of autocomplete objects to return, by default it is 20, but if there are 5 or more characters typed it becomes 500 because some GO ontologies have hundreds of terms with the same words. Singlequotes are escaped for postgres queries. The three tabletypes of postgres obo_ tables are name (id to name mapping), syn (id to synonyms), data (id to full data for term info display). Results are stored in a tied hash to maintain order. For each tabletype obotable is 'obo_<tabletype>_<ontology_table>'. The column to match on is the table name, but for tabletype 'data' query the joinkey column. Query the table matching the column for lower cased values starting with <words>, sorted by column, with a limit of max_results to avoid getting too many results that won't show anyway. Get up to max_results, getting the data as '<data> ( <id> ) '. If the tabletype is 'syn' or 'data' query the name tabletype to get the object's name. If the tabletype is 'syn' append a '[<name>]' ; if the tabletype is 'data', replace the match with '<name> ( <id> ) '. Query again the same way matching for the word anywhere in the field except for in the beginning. If there are more matches than max_results replace the last value with 'more ...' to let the user know there are more values. Return matches.
&asyncTermInfo generates a text page for returning AJAX queries for term information of fields of type ontology or multiontology. From the form gets datatype, field, userValue ; and prints term information for the termInfo html div element of the OA's obo frame. Calls &getAnyTermInfo (see SHOW OBO SUBROUTINES ).
&asyncValidValue generates a text page for returning AJAX queries to check if an autocomplete value is valid. From the form gets datatype, field, userValue ; and prints whether it is 'OK' or has an error message. If the field type is dropdown or multidropdown call &getAnySimpleValidValue . If the field type is ontology or multiontology, and the field's ontology_type is 'obo', call &getGenericOboValidValue ; otherwise check $configLoaded and call the appropriate subroutine ; if any are loaded it calls &getAnySpecificValidValue . Print result matches.
&getAnySimpleValidValue checks whether a value exists in a dropdown or multidropdown fields, passing in datatype, field, userValue ; and returning 'true' or 'false'. Get dropdown_type from $fields{<datatype>}{<field>}{dropdown_type} . Call the MOD-specific perl module subroutine to set the values for the dropdown_type, then loop through them returning 'true' if found, returning false if there is no match.
&getGenericOboValidValue checks whether a value exists in a generic postgres obo_ tables, passing in datatype, field, userValue ; and returning 'true' or 'false'. Get ontology_table from $fields{<datatype>}{<field>}{ontology_table} . Get obotable as 'obo_name_<ontology_table>'. Get the value and joinkey from the userValue. Convert userValue from URL to postgres format with &fromUrlToPostgres for postgres query. If both joinkey and value have an entry in the postgre obotable return 'true', otherwise return 'false'.
&updatePostgresTableField generates a text page for returning AJAX updates, called when an editor field is blurred, to update corresponding postgres tables. From the form gets datatype, field, pgid, newValue ; and prints 'OK' if successful or error message. If the field's type is multiontology or multidropdown get the ids from within the html span elements, join them with doublequotes, and wrap them in doublequotes ; otherwise if the field has an html span element, get the id from within it ; otherwise the value stays the same. The postgres table is '<datatype>_<field>', call &updatePostgresByTableJoinkeyNewvalue and print whether it is 'OK' or has an error message.
&updatePostgresByTableJoinkeyNewvalue updates postgres data tables for a given table and pgids, passing in table, pgid, newValue ; and returning 'OK' if successful or error message. Convert newValue from URL to postgres format with &fromUrlToPostgres for postgres command, wrap in singlequotes, and convert to utf8 if it is not ; if there's no newValue set it to NULL. ontology_annotator.js has a batchUnsafeUpdateDataTableValues mode to update multiple table entries at once for multiple pgids, so split on comma to get array of pgids. For each pgid delete that pgid's entry from the main table, insert NULL or value into the postgres history table, and if there's a value insert into the postgres data table. If there are errors return them, otherwise return 'OK'.
&updatePostgresColumn generates a text page for returning AJAX updates, called when a dataTable column setting changes, to update corresponding postgres tables. From the form gets datatype, table, curator, column action, newValue ; and prints 'OK' if successful or error message. Set the pgtable depending on the column action. Delete from the table for the curator, datatype, and table. For columnWidth and columnShowHide insert new value. For columnOrder get the tables in order and insert them with count. Print whether it is 'OK' or has an error message.
&newRow generates a text page for returning AJAX updates of new datatype object creation of dataTable / postgres table data. From the form gets datatype and curator ; and prints whether the creation is 'OK' or has an error message. Get the highest used pgid from &getHighestPgid . If it's not a digit give an error. Make a new pgid by adding one to the highest one. Call the subroutine from $datatypes{<datatype>}{newRowSub} to create a new datatype object in the postgres data tables, getting back the new pgid or error message. If the value returned is an integer the return message is 'OK', otherwise it's the value returned. If there are errors print them, otherwise print 'OK' ; followed by distinct divider for ontology_annotator.js newRowButtonListener to separate data ; followed by the new pgid.
&getHighestPgid gets the highest used pgid from the postgres tables, passing in the datatype ; and returning the highest pgid. Gets the postgres tables to check from $datatypes{<datatype>}{highestPgidTables} and queries for the highest joinkey as an integer, returning it, or gives an error message.
&duplicateByPgids generates a text page for returning AJAX updates of duplications of dataTable / postgres table data. From the form gets datatype and idsToDuplicate ; and prints whether the duplications are 'OK' or have an error message, as well as a distinct divider and the newly duplicated pgids. Get the highest used pgid from &getHighestPgid (see NEW ROW SUBROUTINES ). If it's not a digit give an error. Split idsToDuplicate on comma to get array of pgids. For each pgid get a new pgid to create and loop through each datatype's field, skipping 'id' field and fields of type 'queryonly', making the postgres table as '<datatype>_<field>', querying the pgid to duplicate for data, and if it has data calling &updatePostgresByTableJoinkeyNewvalue on the table, new pgid, and retrieved data. If there are errors print them, otherwise print 'OK' ; followed by distinct divider for ontology_annotator.js duplicateRowButtonListener to separate data ; followed by comma-joined duplicated pgids.
&deleteByPgids generates a text page for returning AJAX updates of deletions from dataTable / postgres table data. From the form gets datatype and idsToDelete ; and prints whether the deletions are 'OK' or have an error message. Split idsToDelete on comma to get array of pgids. For each pgid loop through each datatype's field, skipping 'id' field and fields of type 'queryonly', making the postgres table as '<datatype>_<field>', querying it for a row with that pgid, and if it has some call &updatePostgresByTableJoinkeyNewvalue on the table, pgid, and blank '' data. If there are errors print them, otherwise print 'OK'. This should never print anything because postgres deletes don't give errors and NULL inserts are skipped.
&checkDataByPgids generates a text page for returning AJAX queries to check if dataTable data passes tests for the datatype ; one test can be whether specific tables require data, the other is for custom constraints (e.g. a field's data fits a format, or any of N fields have data). From the form gets datatype, allDataTableIds ; and prints whether it is 'OK' or has an error message. Checks $datatypes{<datatype>}{constraintTablesHaveData} to see if there is a list of tables that must have data for the datatype, calling &checkDataInPgTable . Checks $datatypes{$datatype}{constraintSub} to see if there is a special constraint subroutine for additional checks, calling it. If either check gets an error message, print the error message(s), otherwise print 'OK'.
&checkDataInPgTable checks whether all pgids in the dataTable have data for a list of tables for the datatype, passing in datatype, arrayref, and allDataTableIds ; and returning any error messages or blank ''. arrayref is the reference to the array of tables that should have data in $datatypes{<datatype>}{constraintTablesHaveData} . allDataTableIds are the comma-separated pgids passed in from the AJAX call to &checkDataByPgids . Gets list of ids by splitting on comma, creates joinkeys by joining on singlequote-comma-singlequote for postgres query. If arrayref has no tables returns (blank). For each table makes the postgres table as '<datatype>_<table>', querying the table and joinkeys, hashing all joinkeys and tables. For each pgid to check, if there's no hash entry adds error to returnMessage ; otherwise checks each table for id-table in the hash, if there's no value add to list of tablesMissingData, then add all pgid-tablesMissingData to returnMessage. Return returnMessage.