diff options
| -rw-r--r-- | engine/lib/annotations.php | 305 | ||||
| -rw-r--r-- | engine/lib/deprecated-1.8.php | 108 | ||||
| -rw-r--r-- | engine/lib/metadata.php | 188 | ||||
| -rw-r--r-- | engine/lib/metastrings.php | 320 |
4 files changed, 473 insertions, 448 deletions
diff --git a/engine/lib/annotations.php b/engine/lib/annotations.php index d61173ef6..a3f8f0bb9 100644 --- a/engine/lib/annotations.php +++ b/engine/lib/annotations.php @@ -183,7 +183,7 @@ function update_annotation($annotation_id, $name, $value, $value_type, $owner_gu * * annotation_case_sensitive => BOOL Overall Case sensitive * - * annotation_owner_guids => NULL|ARR guids for metadata owners + * annotation_owner_guids => NULL|ARR guids for annotation owners * * annotation_created_time_lower => INT Lower limit for created time. * @@ -191,302 +191,31 @@ function update_annotation($annotation_id, $name, $value, $value_type, $owner_gu * * annotation_calculation => STR Perform the MySQL function on the annotation values returned. * - * @return array + * @return mixed * @since 1.8.0 */ -function elgg_get_annotations($options = array()) { - $defaults = array( - // entities - 'types' => ELGG_ENTITIES_ANY_VALUE, - 'subtypes' => ELGG_ENTITIES_ANY_VALUE, - 'type_subtype_pairs' => ELGG_ENTITIES_ANY_VALUE, - - 'guids' => ELGG_ENTITIES_ANY_VALUE, - 'owner_guids' => ELGG_ENTITIES_ANY_VALUE, - 'container_guids' => ELGG_ENTITIES_ANY_VALUE, - 'site_guids' => get_config('site_guid'), - - 'modified_time_lower' => ELGG_ENTITIES_ANY_VALUE, - 'modified_time_upper' => ELGG_ENTITIES_ANY_VALUE, - 'created_time_lower' => ELGG_ENTITIES_ANY_VALUE, - 'created_time_upper' => ELGG_ENTITIES_ANY_VALUE, - - // annotations - // options are normalized to the plural in case we ever add support for them. - 'annotation_names' => ELGG_ENTITIES_ANY_VALUE, - 'annotation_values' => ELGG_ENTITIES_ANY_VALUE, - //'annotation_name_value_pairs' => ELGG_ENTITIES_ANY_VALUE, - //'annotation_name_value_pairs_operator' => 'AND', - - 'annotation_case_sensitive' => TRUE, - //'order_by_annotation' => array(), - 'annotation_calculation' => ELGG_ENTITIES_NO_VALUE, - - 'annotation_created_time_lower' => ELGG_ENTITIES_ANY_VALUE, - 'annotation_created_time_upper' => ELGG_ENTITIES_ANY_VALUE, - - 'annotation_owner_guids' => ELGG_ENTITIES_ANY_VALUE, - - // sql - 'order_by' => 'a.time_created asc', - 'limit' => 10, - 'offset' => 0, - 'count' => FALSE, - 'selects' => array(), - 'wheres' => array(), - 'joins' => array(), - - 'callback' => 'row_to_elggannotation', +function elgg_get_annotations(array $options = array()) { + // map the annotation_* options to metastring_* options + $map = array( + 'annotation_names' => 'metastring_names', + 'annotation_values' => 'metastring_values', + 'annotation_case_sensitive' => 'metastring_case_sensitive', + 'annotation_owner_guids' => 'metastring_owner_guids', + 'annotation_created_time_lower' => 'metastring_created_time_lower', + 'annotation_created_time_upper' => 'metastring_created_time_upper', + 'annotation_calculation' => 'metastring_calculation' ); - $options = array_merge($defaults, $options); - - // can't use helper function with type_subtype_pair because - // it's already an array...just need to merge it - if (isset($options['type_subtype_pair'])) { - if (isset($options['type_subtype_pairs'])) { - $options['type_subtype_pairs'] = array_merge($options['type_subtype_pairs'], - $options['type_subtype_pair']); - } else { - $options['type_subtype_pairs'] = $options['type_subtype_pair']; - } - } - - $singulars = array('type', 'subtype', 'guid', 'owner_guid', 'container_guid', 'site_guid', - 'annotation_name', 'annotation_value' - ); + $singulars = array('annotation_name', 'annotation_value'); $options = elgg_normalise_plural_options_array($options, $singulars); - if (!$options) { - return false; - } - - $db_prefix = elgg_get_config('dbprefix'); - - // evaluate where clauses - if (!is_array($options['wheres'])) { - $options['wheres'] = array($options['wheres']); - } - - $wheres = $options['wheres']; - - // entities - $wheres[] = elgg_get_entity_type_subtype_where_sql('e', $options['types'], - $options['subtypes'], $options['type_subtype_pairs']); - - $wheres[] = elgg_get_guid_based_where_sql('e.guid', $options['guids']); - $wheres[] = elgg_get_guid_based_where_sql('e.owner_guid', $options['owner_guids']); - $wheres[] = elgg_get_guid_based_where_sql('e.container_guid', $options['container_guids']); - $wheres[] = elgg_get_guid_based_where_sql('e.site_guid', $options['site_guids']); - - $wheres[] = elgg_get_entity_time_where_sql('e', $options['created_time_upper'], - $options['created_time_lower'], $options['modified_time_upper'], $options['modified_time_lower']); - - // annotations - $annotation_clauses = elgg_get_annotation_sql('a', $options['annotation_names'], - $options['annotation_values'], $options['annotation_case_sensitive']); - - $wheres = array_merge($wheres, $annotation_clauses['wheres']); - - $wheres[] = elgg_get_entity_time_where_sql('a', $options['annotation_created_time_upper'], - $options['annotation_created_time_lower'], null, null); - - $wheres[] = elgg_get_guid_based_where_sql('a.owner_guid', $options['annotation_owner_guids']); - - // remove identical where clauses - $wheres = array_unique($wheres); - - // see if any functions failed - // remove empty strings on successful functions - foreach ($wheres as $i => $where) { - if ($where === FALSE) { - return FALSE; - } elseif (empty($where)) { - unset($wheres[$i]); - } - } - - // evaluate join clauses - if (!is_array($options['joins'])) { - $options['joins'] = array($options['joins']); - } - - $joins = $options['joins']; - - $joins = array_merge($joins, $annotation_clauses['joins']); - $joins[] = "JOIN {$db_prefix}entities e ON a.entity_guid = e.guid"; - $joins[] = "JOIN {$db_prefix}metastrings n on a.name_id = n.id"; - $joins[] = "JOIN {$db_prefix}metastrings v on a.value_id = v.id"; - - - // remove identical join clauses - $joins = array_unique($joins); - - foreach ($joins as $i => $join) { - if ($join === FALSE) { - return FALSE; - } elseif (empty($join)) { - unset($joins[$i]); - } - } - - // evalutate selects - if ($options['selects']) { - $selects = ''; - foreach ($options['selects'] as $select) { - $selects .= ", $select"; - } - } else { - $selects = ''; - } - - // check for calculations - if ($options['count']) { - $options['annotation_calculation'] = 'count'; - } - - if ($options['annotation_calculation'] === ELGG_ENTITIES_NO_VALUE) { - $query = "SELECT DISTINCT a.*, n.string as name, v.string as value FROM {$db_prefix}annotations a"; - } else { - $query = "SELECT DISTINCT v.string as value, {$options['annotation_calculation']}(v.string) as calculation FROM {$db_prefix}annotations a"; - } - - // add joins - foreach ($joins as $j) { - $query .= " $j "; - } - - // add wheres - $query .= ' WHERE '; - - foreach ($wheres as $w) { - $query .= " $w AND "; - } - - // Add access controls - $query .= get_access_sql_suffix('e'); - if ($options['annotation_calculation'] === ELGG_ENTITIES_NO_VALUE) { - if ($options['group_by'] = sanitise_string($options['group_by'])) { - $query .= " GROUP BY {$options['group_by']}"; - } - - if ($options['order_by'] = sanitise_string($options['order_by'])) { - $query .= " ORDER BY {$options['order_by']}"; + foreach ($map as $ann => $ms) { + if (isset($options[$ann])) { + $options[$ms] = $options[$ann]; } - - if ($options['limit']) { - $limit = sanitise_int($options['limit']); - $offset = sanitise_int($options['offset']); - $query .= " LIMIT $offset, $limit"; - } - - $dt = get_data($query, $options['callback']); - return $dt; - } else { - $result = get_data_row($query); - return $result->calculation; - } -} - -/** - * Returns an array of joins and wheres for use in annotations. - * - * @note The $pairs is reserved for name/value pairs if we want to implement those. - * - * @param string $table The annotation table name or alias - * @param array $names An array of names - * @param array $values An array of values - * @param array $pairs Name / value pairs. Not currently used. - * @param bool $case_sensitive Should name and values be case sensitive? - * - * @return array - */ -function elgg_get_annotation_sql($table, $names = null, $values = null, - $pairs = null, $case_sensitive = false) { - - if ((!$names && $names !== 0) - && (!$values && $values !== 0) - && (!$pairs && $pairs !== 0)) { - - return ''; - } - - $db_prefix = elgg_get_config('dbprefix'); - - // join counter for incremental joins. - $i = 1; - - // binary forces byte-to-byte comparision of strings, making - // it case- and diacritical-mark- sensitive. - // only supported on values. - $binary = ($case_sensitive) ? ' BINARY ' : ''; - - $access = get_access_sql_suffix($table); - - $return = array ( - 'joins' => array (), - 'wheres' => array() - ); - - $wheres = array(); - - // get names wheres and joins - $names_where = ''; - if ($names !== NULL) { - if (!is_array($names)) { - $names = array($names); - } - - $sanitised_names = array(); - foreach ($names as $name) { - // normalise to 0. - if (!$name) { - $name = '0'; - } - $sanitised_names[] = '\'' . sanitise_string($name) . '\''; - } - - if ($names_str = implode(',', $sanitised_names)) { - $return['joins'][] = "JOIN {$db_prefix}metastrings msn on $table.name_id = msn.id"; - $names_where = "(msn.string IN ($names_str))"; - } - } - - // get values wheres and joins - $values_where = ''; - if ($values !== NULL) { - if (!is_array($values)) { - $values = array($values); - } - - $sanitised_values = array(); - foreach ($values as $value) { - // normalize to 0 - if (!$value) { - $value = 0; - } - $sanitised_values[] = '\'' . sanitise_string($value) . '\''; - } - - if ($values_str = implode(',', $sanitised_values)) { - $return['joins'][] = "JOIN {$db_prefix}metastrings msv on $table.value_id = msv.id"; - $values_where = "({$binary}msv.string IN ($values_str))"; - } - } - - if ($names_where && $values_where) { - $wheres[] = "($names_where AND $values_where AND $access)"; - } elseif ($names_where) { - $wheres[] = "($names_where AND $access)"; - } elseif ($values_where) { - $wheres[] = "($values_where AND $access)"; - } - - if ($where = implode(' AND ', $wheres)) { - $return['wheres'][] = "($where)"; } - return $return; + return elgg_get_metastring_based_objects($options, 'annotations'); } diff --git a/engine/lib/deprecated-1.8.php b/engine/lib/deprecated-1.8.php index 37f9d7fdd..4e8b02d28 100644 --- a/engine/lib/deprecated-1.8.php +++ b/engine/lib/deprecated-1.8.php @@ -3107,8 +3107,8 @@ function clear_all_plugin_settings($plugin_id = "") { function get_annotations($entity_guid = 0, $entity_type = "", $entity_subtype = "", $name = "", $value = "", $owner_guid = 0, $limit = 10, $offset = 0, $order_by = "asc", $timelower = 0, $timeupper = 0, $entity_owner_guid = 0) { - global $CONFIG; + elgg_deprecated_notice('get_annotations() is deprecated by elgg_get_annotations()', 1.8); $options = array(); if ($entity_guid) { @@ -3139,7 +3139,7 @@ $timeupper = 0, $entity_owner_guid = 0) { $options['offset'] = $offset; if ($order_by == 'desc') { - $options['order_by'] = 'a.time_created desc'; + $options['order_by'] = 'n_table.time_created desc'; } if ($timelower) { @@ -3181,7 +3181,7 @@ function list_annotations($entity_guid, $name = "", $limit = 25, $asc = true) { $options = array( 'guid' => $entity_guid, 'limit' => $limit, - 'order_by' => "a.time_created $asc" + 'order_by' => "n_table.time_created $asc" ); return elgg_list_annotations($options); @@ -3233,7 +3233,7 @@ $timeupper = 0, $calculation = '') { } if ($order_by == 'desc') { - $options['order_by'] = 'a.time_created desc'; + $options['order_by'] = 'n_table.time_created desc'; } if ($timelower) { @@ -3479,4 +3479,102 @@ function set_view_location($view, $location, $viewtype = '') { function register_entity_url_handler($function_name, $entity_type = "all", $entity_subtype = "all") { elgg_deprecated_notice("register_entity_url_handler() was deprecated by elgg_register_entity_url_handler()", 1.8); return elgg_register_entity_url_handler($entity_type, $entity_subtype, $function_name); -}
\ No newline at end of file +} + + +/** + * Get the metadata where the entities they are referring to match a given criteria. + * + * @param mixed $meta_name Metadata name + * @param mixed $meta_value Metadata value + * @param string $entity_type The type of entity to look for, eg 'site' or 'object' + * @param string $entity_subtype The subtype of the entity. + * @param int $limit Limit + * @param int $offset Offset + * @param string $order_by Optional ordering. + * @param int $site_guid Site GUID. 0 for current, -1 for any + * + * @return mixed + * @deprecated 1.8 Use elgg_get_metadata() + */ +function find_metadata($meta_name = "", $meta_value = "", $entity_type = "", $entity_subtype = "", + $limit = 10, $offset = 0, $order_by = "", $site_guid = 0) { + + elgg_deprecated_notice('get_metadata() is deprecated by elgg_get_metadata()', 1.8); + + $options = array(); + + if ($meta_name) { + $options['annotation_name'] = $meta_name; + } + + if ($meta_value) { + $options['annotation_value'] = $meta_value; + } + + if ($entity_type) { + $options['type'] = $entity_type; + } + + if ($entity_subtype) { + $options['subtype'] = $entity_subtype; + } + + $options['limit'] = $limit; + $options['offset'] = $offset; + + if ($order_by == 'desc') { + $options['order_by'] = 'n_table.time_created desc'; + } + + if ($site_guid) { + $options['site_guid'] = $site_guid; + } + + return elgg_get_metadata($options); +} + + +/** + * Get metadata objects by name. + * + * @param int $entity_guid Entity GUID + * @param string $meta_name Metadata name + * + * @return mixed ElggMetadata object, an array of ElggMetadata or false. + * @deprecated 1.8 Use elgg_get_metadata() + */ +function get_metadata_byname($entity_guid, $meta_name) { + elgg_deprecated_notice('get_metadata_byname() is deprecated by elgg_get_metadata()', 1.8); + + $options = array( + 'guid' => $entity_guid, + 'metadata_name' => $meta_name + ); + + $md = elgg_get_metadata($options); + + if ($md && count($md) == 1) { + return $md[0]; + } + + return $md; +} + +/** + * Return all the metadata for a given GUID. + * + * @param int $entity_guid Entity GUID + * + * @return mixed + * @deprecated 1.8 Use elgg_get_metadata() + */ +function get_metadata_for_entity($entity_guid) { + elgg_deprecated_notice('get_metadata_for_entity() is deprecated by elgg_get_metadata()', 1.8); + + $options = array( + 'guid' => $entity_guid + ); + + return elgg_get_metadata($options); +} diff --git a/engine/lib/metadata.php b/engine/lib/metadata.php index 5935df43b..a9be5a962 100644 --- a/engine/lib/metadata.php +++ b/engine/lib/metadata.php @@ -322,174 +322,52 @@ function delete_metadata($id) { } /** - * Get metadata objects by name. + * Returns metadata. Accepts all elgg_get_entities() options for entity + * restraints. * - * @param int $entity_guid Entity GUID - * @param string $meta_name Metadata name + * @see elgg_get_entities * - * @return mixed ElggMetadata object, an array of ElggMetadata or false. - */ -function get_metadata_byname($entity_guid, $meta_name) { - global $CONFIG; - - $meta_name = get_metastring_id($meta_name); - - if (empty($meta_name)) { - return false; - } - - $entity_guid = (int)$entity_guid; - $access = get_access_sql_suffix("e"); - $md_access = get_access_sql_suffix("m"); - - // If memcache is available then cache this (cache only by name for now - // since this is the most common query) - $meta = null; - static $metabyname_memcache; - if ((!$metabyname_memcache) && (is_memcache_available())) { - $metabyname_memcache = new ElggMemcache('metabyname_memcache'); - } - if ($metabyname_memcache) { - $meta = $metabyname_memcache->load("{$entity_guid}:{$meta_name}"); - } - if ($meta) { - return $meta; - } - - $query = "SELECT m.*, n.string as name, v.string as value" - . " from {$CONFIG->dbprefix}metadata m" - . " JOIN {$CONFIG->dbprefix}entities e ON e.guid = m.entity_guid" - . " JOIN {$CONFIG->dbprefix}metastrings v on m.value_id = v.id" - . " JOIN {$CONFIG->dbprefix}metastrings n on m.name_id = n.id" - . " where m.entity_guid=$entity_guid and m.name_id='$meta_name'" - . " and $access and $md_access ORDER BY m.id ASC" ; - - $result = get_data($query, "row_to_elggmetadata"); - - if (!$result) { - return false; - } - - // Cache if memcache available - if ($metabyname_memcache) { - if (count($result) == 1) { - $r = $result[0]; - } else { - $r = $result; - } - // This is a bit of a hack - we shorten the expiry on object - // metadata so that it'll be gone in an hour. This means that - // deletions and more importantly updates will filter through eventually. - $metabyname_memcache->setDefaultExpiry(3600); - $metabyname_memcache->save("{$entity_guid}:{$meta_name}", $r); - } - if (count($result) == 1) { - return $result[0]; - } - - return $result; -} - -/** - * Return all the metadata for a given GUID. + * @param array $options Array in format: * - * @param int $entity_guid Entity GUID + * metadata_names => NULL|ARR metadata names * - * @return mixed - */ -function get_metadata_for_entity($entity_guid) { - global $CONFIG; - - $entity_guid = (int)$entity_guid; - $access = get_access_sql_suffix("e"); - $md_access = get_access_sql_suffix("m"); - - $query = "SELECT m.*, n.string as name, v.string as value - from {$CONFIG->dbprefix}metadata m - JOIN {$CONFIG->dbprefix}entities e ON e.guid = m.entity_guid - JOIN {$CONFIG->dbprefix}metastrings v on m.value_id = v.id - JOIN {$CONFIG->dbprefix}metastrings n on m.name_id = n.id - where m.entity_guid=$entity_guid and $access and $md_access"; - - return get_data($query, "row_to_elggmetadata"); -} - -/** - * Get the metadata where the entities they are referring to match a given criteria. + * metadata_values => NULL|ARR metadata values + * + * metadata_case_sensitive => BOOL Overall Case sensitive * - * @param mixed $meta_name Metadata name - * @param mixed $meta_value Metadata value - * @param string $entity_type The type of entity to look for, eg 'site' or 'object' - * @param string $entity_subtype The subtype of the entity. - * @param int $limit Limit - * @param int $offset Offset - * @param string $order_by Optional ordering. - * @param int $site_guid Site GUID. 0 for current, -1 for any + * metadata_owner_guids => NULL|ARR guids for metadata owners + * + * metadata_created_time_lower => INT Lower limit for created time. + * + * metadata_created_time_upper => INT Upper limit for created time. + * + * metadata_calculation => STR Perform the MySQL function on the metadata values returned. * * @return mixed + * @since 1.8.0 */ -function find_metadata($meta_name = "", $meta_value = "", $entity_type = "", $entity_subtype = "", - $limit = 10, $offset = 0, $order_by = "", $site_guid = 0) { - global $CONFIG; - - $meta_n = get_metastring_id($meta_name); - $meta_v = get_metastring_id($meta_value); - - $entity_type = sanitise_string($entity_type); - $entity_subtype = get_subtype_id($entity_type, $entity_subtype); - $limit = (int)$limit; - $offset = (int)$offset; - if ($order_by == "") { - $order_by = "e.time_created desc"; - } - - $order_by = sanitise_string($order_by); - $site_guid = (int) $site_guid; - if ($site_guid == 0) { - $site_guid = $CONFIG->site_guid; - } - - $where = array(); - - if ($entity_type != "") { - $where[] = "e.type='$entity_type'"; - } +function elgg_get_metadata($options) { + // map the metadata_* options to metastring_* options + $map = array( + 'metadata_names' => 'metastring_names', + 'metadata_values' => 'metastring_values', + 'metadata_case_sensitive' => 'metastring_case_sensitive', + 'metadata_owner_guids' => 'metastring_owner_guids', + 'metadata_created_time_lower' => 'metastring_created_time_lower', + 'metadata_created_time_upper' => 'metastring_created_time_upper', + 'metadata_calculation' => 'metastring_calculation' + ); - if ($entity_subtype) { - $where[] = "e.subtype=$entity_subtype"; - } + $singulars = array('metadata_name', 'metadata_value'); + $options = elgg_normalise_plural_options_array($options, $singulars); - if ($meta_name != "") { - if (!$meta_v) { - // The value is set, but we didn't get a value... so something went wrong. - return false; - } - $where[] = "m.name_id='$meta_n'"; - } - if ($meta_value != "") { - // The value is set, but we didn't get a value... so something went wrong. - if (!$meta_v) { - return false; + foreach ($map as $ann => $ms) { + if (isset($options[$ann])) { + $options[$ms] = $options[$ann]; } - $where[] = "m.value_id='$meta_v'"; - } - if ($site_guid > 0) { - $where[] = "e.site_guid = {$site_guid}"; - } - - $query = "SELECT m.*, n.string as name, v.string as value from {$CONFIG->dbprefix}entities e - JOIN {$CONFIG->dbprefix}metadata m on e.guid = m.entity_guid - JOIN {$CONFIG->dbprefix}metastrings v on m.value_id = v.id - JOIN {$CONFIG->dbprefix}metastrings n on m.name_id = n.id where"; - - foreach ($where as $w) { - $query .= " $w and "; } - $query .= get_access_sql_suffix("e"); // Add access controls - $query .= ' and ' . get_access_sql_suffix("m"); // Add access controls - $query .= " order by $order_by limit $offset, $limit"; // Add order and limit - return get_data($query, "row_to_elggmetadata"); + return elgg_get_metastring_based_objects($options, 'metadata'); } /** diff --git a/engine/lib/metastrings.php b/engine/lib/metastrings.php index 28f8475da..6009f0882 100644 --- a/engine/lib/metastrings.php +++ b/engine/lib/metastrings.php @@ -200,3 +200,323 @@ function delete_orphaned_metastrings() { return delete_data($query); } + + +/** + * Returns an array of either ElggAnnotation or ElggMetadata objects. + * Accepts all elgg_get_entities() options for entity restraints. + * + * @see elgg_get_entities + * + * @param array $options Array in format: + * + * metastring_names => NULL|ARR metastring names + * + * metastring_values => NULL|ARR metastring values + * + * metastring_case_sensitive => BOOL Overall Case sensitive + * + * metastring_owner_guids => NULL|ARR guids for metadata owners + * + * metastring_created_time_lower => INT Lower limit for created time. + * + * metastring_created_time_upper => INT Upper limit for created time. + * + * metastring_calculation => STR Perform the MySQL function on the metastring values returned. + * + * @param string $type Either metadata or annotations + * @return mixed + * @access private + */ +function elgg_get_metastring_based_objects($options, $type = 'metadata') { + + if ($type != 'metadata' && $type != 'annotations') { + return false; + } + + $defaults = array( + // entities + 'types' => ELGG_ENTITIES_ANY_VALUE, + 'subtypes' => ELGG_ENTITIES_ANY_VALUE, + 'type_subtype_pairs' => ELGG_ENTITIES_ANY_VALUE, + + 'guids' => ELGG_ENTITIES_ANY_VALUE, + 'owner_guids' => ELGG_ENTITIES_ANY_VALUE, + 'container_guids' => ELGG_ENTITIES_ANY_VALUE, + 'site_guids' => get_config('site_guid'), + + 'modified_time_lower' => ELGG_ENTITIES_ANY_VALUE, + 'modified_time_upper' => ELGG_ENTITIES_ANY_VALUE, + 'created_time_lower' => ELGG_ENTITIES_ANY_VALUE, + 'created_time_upper' => ELGG_ENTITIES_ANY_VALUE, + + // options are normalized to the plural in case we ever add support for them. + 'metastring_names' => ELGG_ENTITIES_ANY_VALUE, + 'metastring_values' => ELGG_ENTITIES_ANY_VALUE, + //'metastring_name_value_pairs' => ELGG_ENTITIES_ANY_VALUE, + //'metastring_name_value_pairs_operator' => 'AND', + + 'metastring_case_sensitive' => TRUE, + //'order_by_metastring' => array(), + 'metastring_calculation' => ELGG_ENTITIES_NO_VALUE, + + 'metastring_created_time_lower' => ELGG_ENTITIES_ANY_VALUE, + 'metastring_created_time_upper' => ELGG_ENTITIES_ANY_VALUE, + + 'metastring_owner_guids' => ELGG_ENTITIES_ANY_VALUE, + + // sql + 'order_by' => 'n_table.time_created asc', + 'limit' => 10, + 'offset' => 0, + 'count' => FALSE, + 'selects' => array(), + 'wheres' => array(), + 'joins' => array(), + + 'callback' => ($type == 'annotations') ? 'row_to_elggannotation' : 'row_to_elggmetadata' + ); + + $options = array_merge($defaults, $options); + + // can't use helper function with type_subtype_pair because + // it's already an array...just need to merge it + if (isset($options['type_subtype_pair'])) { + if (isset($options['type_subtype_pairs'])) { + $options['type_subtype_pairs'] = array_merge($options['type_subtype_pairs'], + $options['type_subtype_pair']); + } else { + $options['type_subtype_pairs'] = $options['type_subtype_pair']; + } + } + + $singulars = array('type', 'subtype', 'guid', 'owner_guid', 'container_guid', 'site_guid', + 'metastring_name', 'metastring_value' + ); + $options = elgg_normalise_plural_options_array($options, $singulars); + + if (!$options) { + return false; + } + + $db_prefix = elgg_get_config('dbprefix'); + + // evaluate where clauses + if (!is_array($options['wheres'])) { + $options['wheres'] = array($options['wheres']); + } + + $wheres = $options['wheres']; + + // entities + $wheres[] = elgg_get_entity_type_subtype_where_sql('e', $options['types'], + $options['subtypes'], $options['type_subtype_pairs']); + + $wheres[] = elgg_get_guid_based_where_sql('e.guid', $options['guids']); + $wheres[] = elgg_get_guid_based_where_sql('e.owner_guid', $options['owner_guids']); + $wheres[] = elgg_get_guid_based_where_sql('e.container_guid', $options['container_guids']); + $wheres[] = elgg_get_guid_based_where_sql('e.site_guid', $options['site_guids']); + + $wheres[] = elgg_get_entity_time_where_sql('e', $options['created_time_upper'], + $options['created_time_lower'], $options['modified_time_upper'], $options['modified_time_lower']); + + + $wheres[] = elgg_get_entity_time_where_sql('n_table', $options['metastring_created_time_upper'], + $options['metastring_created_time_lower'], null, null); + + $wheres[] = elgg_get_guid_based_where_sql('n_table.owner_guid', $options['metastring_owner_guids']); + + // remove identical where clauses + $wheres = array_unique($wheres); + + // see if any functions failed + // remove empty strings on successful functions + foreach ($wheres as $i => $where) { + if ($where === FALSE) { + return FALSE; + } elseif (empty($where)) { + unset($wheres[$i]); + } + } + + // evaluate join clauses + if (!is_array($options['joins'])) { + $options['joins'] = array($options['joins']); + } + + $joins = $options['joins']; + + $joins[] = "JOIN {$db_prefix}entities e ON n_table.entity_guid = e.guid"; + $joins[] = "JOIN {$db_prefix}metastrings n on n_table.name_id = n.id"; + $joins[] = "JOIN {$db_prefix}metastrings v on n_table.value_id = v.id"; + + + // remove identical join clauses + $joins = array_unique($joins); + + foreach ($joins as $i => $join) { + if ($join === FALSE) { + return FALSE; + } elseif (empty($join)) { + unset($joins[$i]); + } + } + + // metastrings + $metastring_clauses = elgg_get_metastring_sql('n_table', $options['metastring_names'], + $options['metastring_values'], $options['metastring_case_sensitive']); + + if ($metastring_clauses) { + $wheres = array_merge($wheres, $metastring_clauses['wheres']); + $joins = array_merge($joins, $metastring_clauses['joins']); + } + + // check for calculations + if ($options['count']) { + $options['metastring_calculation'] = 'count'; + } + + if ($options['metastring_calculation'] === ELGG_ENTITIES_NO_VALUE) { + $query = "SELECT DISTINCT n_table.*, n.string as name, v.string as value FROM {$db_prefix}$type n_table"; + } else { + $query = "SELECT DISTINCT v.string as value, {$options['metastring_calculation']}(v.string) as calculation FROM {$db_prefix}$type n_table"; + } + + // add joins + foreach ($joins as $j) { + $query .= " $j "; + } + + // add wheres + $query .= ' WHERE '; + + foreach ($wheres as $w) { + $query .= " $w AND "; + } + + // Add access controls + $query .= get_access_sql_suffix('e'); + if ($options['metastring_calculation'] === ELGG_ENTITIES_NO_VALUE) { + if ($options['group_by'] = sanitise_string($options['group_by'])) { + $query .= " GROUP BY {$options['group_by']}"; + } + + if ($options['order_by'] = sanitise_string($options['order_by'])) { + $query .= " ORDER BY {$options['order_by']}, n_table.id"; + } + + if ($options['limit']) { + $limit = sanitise_int($options['limit']); + $offset = sanitise_int($options['offset']); + $query .= " LIMIT $offset, $limit"; + } + + $dt = get_data($query, $options['callback']); + return $dt; + } else { + $result = get_data_row($query); + return $result->calculation; + } +} + + +/** + * Returns an array of joins and wheres for use in metastrings. + * + * @note The $pairs is reserved for name/value pairs if we want to implement those. + * + * @param string $table The annotation or metadata table name or alias + * @param array $names An array of names + * @param array $values An array of values + * @param array $pairs Name / value pairs. Not currently used. + * @param bool $case_sensitive Should name and values be case sensitive? + * + * @return array + */ +function elgg_get_metastring_sql($table, $names = null, $values = null, + $pairs = null, $case_sensitive = false) { + + if ((!$names && $names !== 0) + && (!$values && $values !== 0) + && (!$pairs && $pairs !== 0)) { + + return ''; + } + + $db_prefix = elgg_get_config('dbprefix'); + + // join counter for incremental joins. + $i = 1; + + // binary forces byte-to-byte comparision of strings, making + // it case- and diacritical-mark- sensitive. + // only supported on values. + $binary = ($case_sensitive) ? ' BINARY ' : ''; + + $access = get_access_sql_suffix($table); + + $return = array ( + 'joins' => array (), + 'wheres' => array() + ); + + $wheres = array(); + + // get names wheres and joins + $names_where = ''; + if ($names !== NULL) { + if (!is_array($names)) { + $names = array($names); + } + + $sanitised_names = array(); + foreach ($names as $name) { + // normalise to 0. + if (!$name) { + $name = '0'; + } + $sanitised_names[] = '\'' . sanitise_string($name) . '\''; + } + + if ($names_str = implode(',', $sanitised_names)) { + $return['joins'][] = "JOIN {$db_prefix}metastrings msn on $table.name_id = msn.id"; + $names_where = "(msn.string IN ($names_str))"; + } + } + + // get values wheres and joins + $values_where = ''; + if ($values !== NULL) { + if (!is_array($values)) { + $values = array($values); + } + + $sanitised_values = array(); + foreach ($values as $value) { + // normalize to 0 + if (!$value) { + $value = 0; + } + $sanitised_values[] = '\'' . sanitise_string($value) . '\''; + } + + if ($values_str = implode(',', $sanitised_values)) { + $return['joins'][] = "JOIN {$db_prefix}metastrings msv on $table.value_id = msv.id"; + $values_where = "({$binary}msv.string IN ($values_str))"; + } + } + + if ($names_where && $values_where) { + $wheres[] = "($names_where AND $values_where AND $access)"; + } elseif ($names_where) { + $wheres[] = "($names_where AND $access)"; + } elseif ($values_where) { + $wheres[] = "($values_where AND $access)"; + } + + if ($where = implode(' AND ', $wheres)) { + $return['wheres'][] = "($where)"; + } + + return $return; +} |