Functions | |
| chado_get_table_max_rank ($tablename, $where_options) | |
| hook_chado_connection_alter (&$settings) | |
| chado_set_active ($dbname='default') | |
| chado_insert_record ($table, $values, $options=[]) | |
| chado_update_record ($table, $match, $values, $options=NULL) | |
| chado_delete_record ($table, $match, $options=NULL) | |
| chado_select_record ($table, $columns, $values, $options=NULL) | |
| chado_select_record_check_value_type (&$op, &$value, $type) | |
| chado_query ($sql, $args=[], $options=[]) | |
| hook_chado_query_alter (&$sql, &$args) | |
| chado_pager_query ($query, $args, $limit, $element, $count_query='') | |
| chado_pager_get_count ($element) | |
| chado_schema_get_foreign_key ($table_desc, $field, $values, $options=NULL) | |
| chado_get_schema_name ($schema='chado') | |
| hook_chado_get_schema_name_alter ($schema_name, $context) | |
| chado_db_select ($table, $alias=NULL, array $options=[]) | |
| static | ChadoPrefixExtender::select ($table, $alias=NULL, array $options=[]) |
| ChadoPrefixExtender::join ($table, $alias=NULL, $condition=NULL, $arguments=[]) | |
| ChadoPrefixExtender::innerJoin ($table, $alias=NULL, $condition=NULL, $arguments=[]) | |
| ChadoPrefixExtender::rightJoin ($table, $alias=NULL, $condition=NULL, $arguments=[]) | |
Detailed Description
Provides an API for querying of chado including inserting, updating, deleting and selecting from specific chado tables. There is also a generic function, chado_query(), to execute and SQL statement on chado. It is ideal to use these functions to interact with chado in order to keep your module compatible with both local & external chado databases. Furthermore, it ensures connection to the chado database is taken care of for you.
Generic Queries to a specifc chado table:
chado_select_record( [table name], [columns to select], [specify record to select], [options*] ) This function allows you to select various columns from the specified chado table. Although you can only select from a single table, you can specify the record to select using values from related tables through use of a nested array. For example, the following code shows you how to select the name and uniquename of a feature based on it's type and source organism.
chado_insert_record( [table name], [values to insert], [options*] ) This function allows you to insert a single record into a specific table. The values to insert are specified using an associative array where the keys are the column names to insert into and they point to the value to be inserted into that column. If the column is a foreign key, the key will point to an array specifying the record in the foreign table and then the primary key of that record will be inserted in the column. For example, the following code will insert a feature and for the type_id, the cvterm.cvterm_id of the cvterm record will be inserted and for the organism_id, the organism.organism_id of the organism_record will be inserted.
chado_update_record( [table name], [specify record to update], [values to change], [options*] ) This function allows you to update records in a specific chado table. The record(s) you wish to update are specified the same as in the select function above and the values to be update are specified the same as the values to be inserted were. For example, the following code species that a feature with a given uniquename, organism_id, and type_id (the unique constraint for the feature table) will be updated with a new name, and the type changed from a gene to an mRNA.
chado_delete_record( [table name], [specify records to delete], [options*] ) This function allows you to delete records from a specific chado table. The record(s) to delete are specified the same as the record to select/update was above. For example, the following code will delete all genes from the organism Citrus sinensis.
Generic Queries for any SQL:
Often it is necessary to select from more then one table in chado or to execute other complex queries that cannot be handled efficiently by the above functions. It is for this reason that the chado_query( [sql string], [arguments to sub-in to the sql] ) function was created. This function allows you to execute any SQL directly on the chado database and should be used with care. If any user input will be used in the query make sure to put a placeholder in your SQL string and then define the value in the arguments array. This will make sure that the user input is sanitized and safe through type-checking and escaping. The following code shows an example of how to use user input resulting from a form and would be called withing the form submit function.
If you are going to need more then a couple fields, you might want to use the Chado Variables API (specifically chado_generate_var()) to select all of the common fields needed including following foreign keys.
Loading of Variables from chado data:
These functions, chado_generate_var() and chado_expand_var(), generate objects containing the full details of a record(s) in chado. These should be used in all theme templates.
This differs from the objects returned by chado_select_record in so far as all foreign key relationships have been followed meaning you have more complete details. Thus this function should be used whenever you need a full variable and chado_select_record should be used if you only case about a few columns.
The initial variable is generated by the chado_generate_var([table], [filter criteria], [optional options]) function. An example of how to use this function is:
This will return an object if there is only one feature with the name Medtr4g030710 or it will return an array of feature objects if more than one feature has that name.
Some tables and fields are excluded by default. To have those tables & fields added to your variable you can use the chado_expand_var([chado variable], [type], [what to expand], [optional options]) function. An example of how to use this function is:
Function Documentation
◆ chado_db_select()
| chado_db_select | ( | $table, | |
$alias = NULL, |
|||
| array | $options = [] |
||
| ) |
A replacement for db_select when querying Chado.
Use this function instead of db_select when querying Chado tables.
- Parameters
-
$table The base table for this query. May be a string or another SelectQuery object. If a query object is passed, it will be used as a subselect. $alias The alias for the base table of this query. $options An array of options to control how the query operates.
- Returns
- A new SelectQuery object for this connection.
- See also
- \ChadoPrefixExtender::select()
◆ chado_delete_record()
| chado_delete_record | ( | $table, | |
| $match, | |||
$options = NULL |
|||
| ) |
Provides a generic function for deleting a record(s) from any chado table.
Use this function to delete a record(s) in any Chado table. The first argument specifies the table to delete from and the second is an array of values to match for locating the record(s) to be deleted. The arrays are mutli-dimensional such that foreign key lookup values can be specified.
- Parameters
-
$table The name of the chado table for inserting. $match An associative array containing the values for locating a record to update. $options Currently there are no options.
- Returns
- On success this function returns TRUE. On failure, it returns FALSE.
Example usage:
The above code species that a feature with a given uniquename, organism_id, and type_id (the unique constraint for the feature table) will be deleted. The organism_id is specified as a nested array that uses the organism_id foreign key constraint to lookup the specified values to find the exact organism_id. The same nested struture is also used for specifying the values to update. The function will find all records that match the columns specified and delete them.
@TODO: Support Complex filtering as is done in chado_select_record();
◆ chado_get_schema_name()
| chado_get_schema_name | ( | $schema = 'chado' | ) |
Retrieve the name of the PostgreSQL schema housing Chado or Drupal.
- Parameters
-
$schema Wehter you want the schema name for 'chado' or 'drupal'. Chado is the default.
- Returns
- The name of the PostgreSQL schema housing the $schema specified.
◆ chado_get_table_max_rank()
| chado_get_table_max_rank | ( | $tablename, | |
| $where_options | |||
| ) |
Get max rank for a given set of criteria.
This function was developed with the many property tables in chado in mind but will work for any table with a rank.
- Parameters
-
tablename the name of the chado table you want to select the max rank from this table must contain a rank column of type integer. where_options array( <column_name> => array( 'type' => <type of column: INT/STRING>, 'value' => <the value you want to filter on>, 'exact' => <if TRUE use =; if FALSE use ~>, ) ) where options should include the id and type for that table to correctly group a set of records together where the only difference are the value and rank.
- Returns
- The maximum rank.
◆ chado_insert_record()
| chado_insert_record | ( | $table, | |
| $values, | |||
$options = [] |
|||
| ) |
Provides a generic routine for inserting into any Chado table
Use this function to insert a record into any Chado table. The first argument specifies the table for inserting and the second is an array of values to be inserted. The array is mutli-dimensional such that foreign key lookup values can be specified.
- Parameters
-
$table The name of the chado table for inserting $values An associative array containing the values for inserting. $options An array of options such as: - skip_validation: TRUE or FALSE. If TRUE will skip all the validation steps and just try to insert as is. This is much faster but results in unhandled non user-friendly errors if the insert fails.
- return_record: by default, the function will return the record but with the primary keys added after insertion. To simply return TRUE on success set this option to FALSE
- Returns
- On success this function returns the inserted record with the new primary keys added to the returned array. On failure, it returns FALSE.
Example usage:
The above code inserts a record into the feature table. The $values array is nested such that the organism is selected by way of the organism_id foreign key constraint by specifying the genus and species. The cvterm is also specified using its foreign key and the cv_id for the cvterm is nested as well.
◆ chado_pager_get_count()
| chado_pager_get_count | ( | $element | ) |
A function to retrieve the total number of records for a pager that was generated using the chado_pager_query() function.
- Parameters
-
$element The $element argument that was passed to the chado_pager_query function.
◆ chado_pager_query()
| chado_pager_query | ( | $query, | |
| $args, | |||
| $limit, | |||
| $element, | |||
$count_query = '' |
|||
| ) |
Use this function instead of pager_query() when selecting a subset of records from a Chado table.
- Parameters
-
$query The SQL statement to execute, this is followed by a variable number of args used as substitution values in the SQL statement. $args The array of arguments for the query. They keys are the placeholders $limit The number of query results to display per page. $element An numeric identifier used to distinguish between multiple pagers on one page. $count_query An SQL query used to count matching records.
- Returns
- A database query result resource or FALSE if the query was not executed correctly
◆ chado_query()
| chado_query | ( | $sql, | |
$args = [], |
|||
$options = [] |
|||
| ) |
A substitute for db_query() when querying from Chado.
This function is needed to avoid switching databases when making query to the chado database.
Will use a chado persistent connection if it already exists.
- Parameters
-
$sql The sql statement to execute. When referencing tables in chado, table names should be surrounded by curly brackets (e.g. { and }). If Drupal tables need to be included in the query, surround those by sqaure brackets (e.g. [ and ]). This follows Drupal conventions for resolving table names. It also supports a multi-chado installation. $args The array of arguments, with the same structure as passed to the db_query() function of Drupal. $options An array of options to control how the query operates.
- Returns
- DatabaseStatementInterface A prepared statement object, already executed.
Example usage:
◆ chado_schema_get_foreign_key()
| chado_schema_get_foreign_key | ( | $table_desc, | |
| $field, | |||
| $values, | |||
$options = NULL |
|||
| ) |
Gets the value of a foreign key relationship.
This function is used by chado_select_record, chado_insert_record, and chado_update_record to iterate through the associate array of values that gets passed to each of those routines. The values array is nested where foreign key constraints are used to specify a value that. See documentation for any of those functions for further information.
- Parameters
-
$table_desc A table description for the table with the foreign key relationship to be identified generated by hook_chado_<table name>_schema() $field The field in the table that is the foreign key. $values An associative array containing the values $options An associative array of additional options where the key is the option and the value is the value of that option. These options are passed on to chado_select_record.
Additional Options Include:
- case_insensitive_columns An array of columns to do a case insensitive search on.
- regex_columns An array of columns where the value passed in should be treated as a regular expression
- Returns
- A string containg the results of the foreign key lookup, or FALSE if failed.
Example usage:
The above code selects a record from the feature table using the three fields that uniquely identify a feature. The $columns array simply lists the columns to select. The $values array is nested such that the organism is identified by way of the organism_id foreign key constraint by specifying the genus and species. The cvterm is also specified using its foreign key and the cv_id for the cvterm is nested as well.
◆ chado_select_record()
| chado_select_record | ( | $table, | |
| $columns, | |||
| $values, | |||
$options = NULL |
|||
| ) |
Provides a generic routine for selecting data from a Chado table.
Use this function to perform a simple select from any Chado table.
- Parameters
-
$table The name of the chado table for inserting $columns An array of column names $values An associative array containing the values for filtering the results. In the case where multiple values for the same time are to be selected an additional entry for the field should appear for each value. If you need to filter results using more complex methods see the 'Complex Filtering' section below. $options An associative array of additional options where the key is the option and the value is the value of that option.
Additional Options Include:
- has_record Set this argument to 'TRUE' to have this function return a numeric value for the number of records rather than the array of records. this can be useful in 'if' statements to check the presence of particula records.
- return_sql Set this to 'TRUE' to have this function return an array where the first element is the sql that would have been run and the second is an array of arguments.
- case_insensitive_columns An array of columns to do a case insensitive search on.
- regex_columns An array of columns where the value passed in should be treated as a regular expression
- order_by An associative array containing the column names of the table as keys and the type of sort (i.e. ASC, DESC) as the values. The results in the query will be sorted by the key values in the direction listed by the value
- is_duplicate: TRUE or FALSE. Checks the values submited to see if they violate any of the unique constraints. If not, the record is returned, if so, FALSE is returned.
- pager: Use this option if it is desired to return only a subset of results so that they may be shown with in a Drupal-style pager. This should be an array with two keys: 'limit' and 'element'. The value of 'limit' should specify the number of records to return and 'element' is a unique integer to differentiate between pagers when more than one appear on a page. The 'element' should start with zero and increment by one for each pager. -limit: Specifies the number of records to return. -offset: Indicates the number of records to skip before returning records.
- Returns
- An array of results, FALSE if the query was not executed correctly, an empty array if no records were matched, or the number of records in the dataset if $has_record is set. If the option 'is_duplicate' is provided and the record is a duplicate it will return the duplicated record. If the 'has_record' option is provided a value of TRUE will be returned if a record exists and FALSE will bee returned if there are not records.
Example usage:
The above code selects a record from the feature table using the three fields that uniquely identify a feature. The $columns array simply lists the columns to select. The $values array is nested such that the organism is identified by way of the organism_id foreign key constraint by specifying the genus and species. The cvterm is also specified using its foreign key and the cv_id for the cvterm is nested as well. In the example above, two different species are allowed to match
Complex Filtering: All of the documentation above supports filtering based on 'is equal to' or 'is NULL'. If your criteria doesn't fall into one of these two categories then you need to provide an array with additional details such as the operator as well as the value. An example follows and will be discussed in detail.
The above code example will return all of the name, start and end of all the features that start within MtChr1:15-100bp. Note that complex filtering can be used in conjunction with basic filtering and that multiple criteria, even for the same field can be entered.
◆ chado_select_record_check_value_type()
| chado_select_record_check_value_type | ( | & | $op, |
| & | $value, | ||
| $type | |||
| ) |
Helper Function: check that the value is the correct type.
This function is used by chado_select_record() when building the $where clause array to ensure that any single values are the correct type based on the table definition. Furthermore, it ensures that NULL's are caught changing the operator to 'IS NULL'.
- Parameters
-
$op The operator being used. This is mostly passed in to allow it to be changed if a NULL value is detected. $value The value to be checked and adjusted. $type The type from the table definition that's used to determine the type of value.
◆ chado_set_active()
| chado_set_active | ( | $dbname = 'default' | ) |
Set the Tripal Database
The chado_set_active function is used to prevent namespace collisions when Chado and Drupal are installed in the same database but in different schemas. It is also used when using Drupal functions such as db_table_exists().
The connection settings can be altered through the hook hook_chado_connection_alter.
Current active connection name is stored in the global variable $GLOBALS['chado_active_db'].
- See also
- hook_chado_connection_alter()
- Parameters
-
$dbname
- Returns
- Global variable $GLOBALS['chado_active_db'].
◆ chado_update_record()
| chado_update_record | ( | $table, | |
| $match, | |||
| $values, | |||
$options = NULL |
|||
| ) |
Provides a generic routine for updating into any Chado table.
Use this function to update a record in any Chado table. The first argument specifies the table for inserting, the second is an array of values to matched for locating the record for updating, and the third argument give the values to update. The arrays are mutli-dimensional such that foreign key lookup values can be specified.
- Parameters
-
$table The name of the chado table for inserting. $match An associative array containing the values for locating a record to update. $values An associative array containing the values for updating. $options An array of options such as: - return_record: by default, the function will return the TRUE if the record was succesfully updated. However, set this option to TRUE to return the record that was updated. The returned record will have the fields provided but the primary key (if available for the table) will be added to the record.
- Returns
- On success this function returns TRUE. On failure, it returns FALSE.
Example usage:
The above code species that a feature with a given uniquename, organism_id, and type_id (the unique constraint for the feature table) will be updated. The organism_id is specified as a nested array that uses the organism_id foreign key constraint to lookup the specified values to find the exact organism_id. The same nested struture is also used for specifying the values to update. The function will find the record that matches the columns specified and update the record with the avlues in the $uvalues array.
@TODO: Support Complex filtering as is done in chado_select_record();
◆ hook_chado_connection_alter()
| hook_chado_connection_alter | ( | & | $settings | ) |
Alter Chado connection settings.
This hook is useful for multi-chado instances. Tripal core functions call the chado_set_active() function (e.g. chado_query) but there is no opportunity elsewhere to set the active database. This is useful in two cases: 1) Users are managed at the database level as in the case of SouthGreen Bioinformatics Platform tools (e.g. Banana Genone Hub). This allows custom modules to change the database connections on a per-user basis, and each user permissions is managed at the database level. Users are managed at the database level to provid the same access restrictions across various tools that use Chado (e,g, Artemis) 2) When there are simply two Chado instances housed in different Chado databases and the module needs to control which one is being used at any given time.
- Parameters
-
$settings An array containing
- See also
- chado_set_active()
◆ hook_chado_get_schema_name_alter()
| hook_chado_get_schema_name_alter | ( | $schema_name, | |
| $context | |||
| ) |
Alter the name of the schema housing Chado and/or Drupal.
This example implementation shows a solution for the case where your chado database was well established in the "public" schema and you added Drupal later in a "drupal" schema. Please note that this has not been tested and while we can ensure that Tripal will work as expected, we have no control over whether Drupal is compatible with not being in the public schema. That's why we recommened the organization we have (ie: Chado in a "chado" schema and Drupal in the "public schema).
- Parameters
-
$schema_name The current name of the schema as known by Tripal. This is likely the default set in chado_get_schema_name() but in the case of multiple alter hooks, it might be different. $context This is an array of items to provide context. - schema: this is the schema that was passed to chado_get_schema_name() and will be either "chado" or "drupal". This should be used to determine you are changing the name of the correct schema.
◆ hook_chado_query_alter()
| hook_chado_query_alter | ( | & | $sql, |
| & | $args | ||
| ) |
This hook provides a way for module developers to alter any/all queries on the chado schema by Tripal.
Example: a module developer might want to remove schema prefixing from queries and rely on the search path. This alter hook would allow them to do that by implementing mymodule_chado_query_alter($sql, $args) and using a regular expression to remove table prefixing from the query.
- Parameters
-
$sql A string describing the SQL query to be executed by Tripal. All parameters should be indicated by :tokens with values being in the $args array and all tables should be prefixed with the schema name described in chado_get_schema_name(). $args An array of arguments where the key is the token used in $sql (for example, :value) and the value is the value you would like substituted in.
◆ innerJoin()
| ChadoPrefixExtender::innerJoin | ( | $table, | |
$alias = NULL, |
|||
$condition = NULL, |
|||
$arguments = [] |
|||
| ) |
Overwrites the innerJoin to prefix table names.
- Parameters
-
string $table Table to join. string $alias Alias for joined table. string $condition Operation for joining. array $arguments Additional arguments.
- Returns
- $this The current object.
◆ join()
| ChadoPrefixExtender::join | ( | $table, | |
$alias = NULL, |
|||
$condition = NULL, |
|||
$arguments = [] |
|||
| ) |
Overwrites the join to prefix table names.
- Parameters
-
string $table Table to join. string $alias Alias for joined table. string $condition Operation for joining. array $arguments Additional arguments.
- Returns
- $this The current object.
◆ rightJoin()
| ChadoPrefixExtender::rightJoin | ( | $table, | |
$alias = NULL, |
|||
$condition = NULL, |
|||
$arguments = [] |
|||
| ) |
Overwrites the rightJoin to prefix table names.
- Parameters
-
string $table Table to join. string $alias Alias for joined table. string $condition Operation for joining. array $arguments Additional arguments.
- Returns
- $this The current object.
◆ select()
|
static |
A replacement for db_select when querying Chado.
Use this function instead of db_select when querying Chado tables.
- Parameters
-
string | \SelectQuery $table The base table for this query. May be a string or another SelectQuery object. If a query object is passed, it will be used as a subselect. string $alias The alias for the base table of this query. array $options An array of options to control how the query operates.
- Returns
- \SelectQuery A new SelectQuery object for this connection.
