=> array( * 'type' => , * 'value' => , * 'exact' => , * ) * ) * 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 * @return the maximum rank * * @ingroup tripal_chado_api */ function chado_get_table_max_rank($tablename, $where_options) { $where_clauses = array(); $where_args = array(); //generate the where clause from supplied options // the key is the column name $i = 0; $sql = " SELECT max(rank) as max_rank, count(rank) as count FROM {".$tablename."} WHERE "; foreach ($where_options as $key => $value) { $where_clauses[] = "$key = :$key"; $where_args[":$key"] = $value; } $sql .= implode($where_clauses, ' AND '); $result = chado_query($sql, $where_args)->fetchObject(); if ($result->count > 0) { return $result->max_rank; } else { return -1; } } /** * Retrieve a property for a given base table record * * @param $basetable * The base table for which the property should be retrieved. Thus to retrieve a property * for a feature the basetable=feature and property is retrieved from featureprop * @param $record_id * The foriegn key field of the base table. This should be in integer. * @param $property * The cvterm name describing the type of properties to be retrieved * @param $cv_name * The name of the cv that the above cvterm is part of * * @return * An array in the same format as that generated by the function * chado_generate_var(). If only one record is returned it * is a single object. If more than one record is returned then it is an array * of objects * * @ingroup tripal_chado_api */ function chado_get_property($basetable, $record_id, $property, $cv_name, $property_id = FALSE) { // get the foreign key for this property table $table_desc = chado_get_schema($basetable . 'prop'); $fkcol = key($table_desc['foreign keys'][$basetable]['columns']); // construct the array of values to be selected $values = array( $fkcol => $record_id, 'type_id' => array( 'cv_id' => array( 'name' => $cv_name, ), 'name' => $property, 'is_obsolete' => 0 ), ); // if we have the unique property_id make sure to add that to the values if ($property_id) { $property_pkey = $table_desc['primary key'][0]; $values[$property_pkey] = $property_id; } $results = chado_generate_var($basetable . 'prop', $values); if ($results) { $results = chado_expand_var($results, 'field', $basetable . 'prop.value'); } return $results; } /** * Insert a property for a given base table. By default if the property already * exists a new property is added with the next available rank. If * $update_if_present argument is specified then the record will be updated if it * exists rather than adding a new property. * * @param $basetable * The base table for which the property should be inserted. Thus to insert a property * for a feature the basetable=feature and property is inserted into featureprop * @param $record_id * The foriegn key value of the base table. This should be in integer. * @param $property * The cvterm name describing the type of properties to be inserted * @param $cv_name * The name of the cv that the above cvterm is part of * @param $value * The value of the property to be inserted (can be empty) * @param $update_if_present * A boolean indicating whether an existing record should be updated. If the * property already exists and this value is not specified or is zero then * a new property will be added with the next largest rank. * * @return * Return True on Insert/Update and False otherwise * * @ingroup tripal_chado_api */ function chado_insert_property($basetable, $record_id, $property, $cv_name, $value, $update_if_present = 0) { // first see if the property already exists, if the user want's to update // then we can do that, but otherwise we want to increment the rank and // insert $props = chado_get_property($basetable, $record_id, $property, $cv_name); if (!is_array($props) and $props) { $props = array($props); } $rank = 0; if (count($props) > 0) { if ($update_if_present) { return chado_update_property($basetable, $record_id, $property, $cv_name, $value); } else { // iterate through the properties returned and check to see if the // property with this value already exists if not, get the largest rank // and insert the same property but with this new value foreach ($props as $p) { if ($p->rank > $rank) { $rank = $p->rank; } if (strcmp($p->value, $value) == 0) { return TRUE; } } // now add 1 to the rank $rank++; } } // make sure the cvterm exists. Otherwise we'll get an error with // prepared statements not matching $values = array( 'cv_id' => array( 'name' => $cv_name, ), 'name' => $property, ); $options = array(); $term = chado_select_record('cvterm', array('cvterm_id'), $values, $options); if (!$term or count($term) == 0) { tripal_report_error('tripal_core', TRIPAL_ERROR, "Cannot find property '%prop_name' in vocabulary '%cvname'.", array('%prop_name' => $property, '%cvname' => $cv_name)); return FALSE; } // get the foreign key for this property table $table_desc = chado_get_schema($basetable . 'prop'); $fkcol = key($table_desc['foreign keys'][$basetable]['columns']); // construct the array of values to be inserted $values = array( $fkcol => $record_id, 'type_id' => array( 'cv_id' => array( 'name' => $cv_name, ), 'name' => $property, ), 'value' => $value, 'rank' => $rank, ); $options = array(); $result = chado_insert_record($basetable . 'prop', $values, $options); return $result; } /** * Update a property for a given base table record and property name. This * function should be used only if one record of the property will be present. * If the property name can have multiple entries (with increasing rank) then * use the function named chado_update_propertyID * * @param $basetable * The base table for which the property should be updated. The property table * is constructed using a combination of the base table name and the suffix * 'prop' (e.g. basetable = feature then property tabie is featureprop). * @param $record_id * The foreign key of the basetable to update a property for. This should be in integer. * For example, if the basetable is 'feature' then the $record_id should be the feature_id * @param $property * The cvterm name of property to be updated * @param $cv_name * The name of the cv that the above cvterm is part of * @param $value * The value of the property to be inserted (can be empty) * @param $insert_if_missing * A boolean indicating whether a record should be inserted if one doesn't exist to update * * Note: The property to be updated is select via the unique combination of $record_id and * $property and then it is updated with the supplied value * * @return * Return True on Update/Insert and False otherwise * * @ingroup tripal_chado_api */ function chado_update_property($basetable, $record_id, $property, $cv_name, $value, $insert_if_missing = FALSE, $property_id = FALSE) { // first see if the property is missing (we can't update a missing property $prop = chado_get_property($basetable, $record_id, $property, $cv_name, $property_id); if (count($prop)==0) { if ($insert_if_missing) { return chado_insert_property($basetable, $record_id, $property, $cv_name, $value); } else { return FALSE; } } // get the foreign key for this property table $table_desc = chado_get_schema($basetable . 'prop'); $fkcol = key($table_desc['foreign keys'][$basetable]['columns']); // construct the array that will match the exact record to update $match = array( $fkcol => $record_id, 'type_id' => array( 'cv_id' => array( 'name' => $cv_name, ), 'name' => $property, ), ); // If we have the unique property_id, make sure to use it in the match to ensure // we get the exact record. Doesn't rely on there only being one property of that type if ($property_id) { $property_pkey = $table_desc['primary key'][0]; $match = array( $property_pkey => $property_id ); } // construct the array of values to be updated $values = array( 'value' => $value, ); // If we have the unique property_id then we can also update the type // thus add it to the values to be updated if ($property_id) { $values['type_id'] = array( 'cv_id' => array( 'name' => $cv_name, ), 'name' => $property, ); } return chado_update_record($basetable . 'prop', $match, $values); } /** * Deletes a property for a given base table record using the property name * * @param $basetable * The base table for which the property should be deleted. Thus to deleted a property * for a feature the basetable=feature and property is deleted from featureprop * @param $record_id * The primary key of the basetable to delete a property for. This should be in integer. * @param $property * The cvterm name describing the type of property to be deleted * @param $cv_name * The name of the cv that the above cvterm is part of * * Note: The property to be deleted is select via the unique combination of $record_id and $property * * @return * Return True on Delete and False otherwise * * @ingroup tripal_chado_api */ function chado_delete_property($basetable, $record_id, $property, $cv_name) { // get the foreign key for this property table $table_desc = chado_get_schema($basetable . 'prop'); $fkcol = key($table_desc['foreign keys'][$basetable]['columns']); // construct the array that will match the exact record to update $match = array( $fkcol => $record_id, 'type_id' => array( 'cv_id' => array( 'name' => $cv_name, ), 'name' => $property, ), ); return chado_delete_record($basetable . 'prop', $match); }