BAOBAB
Public Member Functions | Static Public Member Functions | Protected Member Functions | List of all members
CO_Main_Data_DB Class Reference
Inheritance diagram for CO_Main_Data_DB:
Collaboration diagram for CO_Main_Data_DB:

Public Member Functions

 __construct ( $in_pdo_object, $in_access_object=NULL)
 
 item_exists ( $in_id, $in_visibility_test=false)
 
 get_all_visible_users ()
 
 see_if_user_exists ( $in_login_id)
 
 tag0_is_unique ( $in_tag0, $in_classname='CO_KeyValue_CO_Collection')
 
 generic_search ( $in_search_parameters=NULL, $or_search=false, $page_size=0, $initial_page=0, $and_writeable=false, $count_only=false, $ids_only=false)
 
- Public Member Functions inherited from A_CO_DB
 _instantiate_record ( $in_db_result)
 
 execute_query ( $in_sql, $in_parameters=NULL, $exec_only=false)
 
 get_db_backup ()
 
 get_access_class_by_id ( $in_id)
 
 can_i_see_this_record ( $in_id)
 
 get_single_raw_row_by_id ( $in_id, $and_write=false)
 
 get_single_record_by_id ( $in_id, $and_write=false)
 
 get_multiple_records_by_id ( $in_id_array, $and_write=false)
 
 get_all_readable_records ( $open_only=false, $in_this_id=NULL)
 
 get_all_writeable_records ( $in_this_id=NULL)
 
 lock_record ( $in_record_id)
 
 write_record ( $params_associative_array)
 
 delete_record ( $id)
 

Static Public Member Functions

static get_accurate_distance ( $lat1, $lon1, $lat2, $lon2)
 Uses the Vincenty calculation to determine the distance (in Kilometers) between the two given lat/long pairs (in Degrees). More...
 

Protected Member Functions

 _location_predicate ( $in_longitude, $in_latitude, $in_radius_in_km, $and_writeable=false, $count_only=false)
 
 _parse_tags ( $in_value)
 
 _parse_integer_parameter ( $in_db_key, $in_value)
 
 _parse_string_parameter ( $in_db_key, $in_value)
 
 _parse_parameters ( $in_search_parameters=NULL, $or_search=false)
 
 _build_sql_query ( $in_search_parameters=NULL, $or_search=false, $page_size=0, $initial_page=0, $and_writeable=false, $count_only=false, $ids_only=false)
 
- Protected Member Functions inherited from A_CO_DB
 _create_read_security_predicate ()
 
 _create_write_security_predicate ()
 
 _create_security_predicate ( $write=false)
 

Additional Inherited Members

- Public Attributes inherited from A_CO_DB
 $access_object
 
 $class_description
 
 $error
 
 $table_name
 
- Protected Attributes inherited from A_CO_DB
 $_pdo_object
 
 $_existing_record_objects
 

Detailed Description

This is the instance class for the main "data" database.

Definition at line 38 of file co_main_data_db.class.php.

Constructor & Destructor Documentation

◆ __construct()

CO_Main_Data_DB::__construct (   $in_pdo_object,
  $in_access_object = NULL 
)

The initializer.

Parameters
$in_pdo_objectThe PDO object for this database, initialized and ready.
$in_access_objectThe access object for the database. If NULL, then no login.

Reimplemented from A_CO_DB.

Definition at line 524 of file co_main_data_db.class.php.

526  {
527  parent::__construct($in_pdo_object, $in_access_object);
528 
529  $this->table_name = 'co_data_nodes';
530 
531  $this->class_description = 'The main data database class.';
532  }

Member Function Documentation

◆ _build_sql_query()

CO_Main_Data_DB::_build_sql_query (   $in_search_parameters = NULL,
  $or_search = false,
  $page_size = 0,
  $initial_page = 0,
  $and_writeable = false,
  $count_only = false,
  $ids_only = false 
)
protected

This builds up an SQL query, based on the input from the user.

Returns
an array of instances that match the search parameters.
Parameters
$in_search_parametersThis is an associative array of terms to define the search. The keys should be:
  • 'id' This should be accompanied by an array of one or more integers, representing specific item IDs.
  • 'access_class' This should be accompanied by an array, containing one or more PHP class names.
  • 'name' This will contain a case-insensitive array of strings to check against the object_name column.
  • 'owner' This should be accompanied by an array of one or more integers, representing specific item IDs for "owner" objects.
  • 'tags' This should be accompanied by an array (up to 10 elements) of one or more case-insensitive strings, representing specific tag values.
  • 'location' This requires that the parameter be a 3-element associative array of floating-point numbers:
    • 'longtude' This is the search center location longitude, in degrees.
    • 'latitude' This is the search center location latitude, in degrees.
    • 'radius' This is the search radius, in Kilometers.
$or_searchIf true, then the search is very wide (OR), as opposed to narrow (AND), by default. If you specify a location, then that will always be AND, but the other fields can be OR.
$page_sizeIf specified with a 1-based integer, this denotes the size of a "page" of results. NOTE: This is only applicable to MySQL or Postgres, and will be ignored if the DB is not MySQL or Postgres.
$initial_pageThis is ignored unless $page_size is greater than 0. If so, then this 0-based index will specify which page of results to return.
$and_writeableIf true, then we only want records we can modify.
$count_onlyIf true (default is false), then only a single integer will be returned, with the count of items that fit the search.
$ids_onlyIf true (default is false), then the return array will consist only of integers (the object IDs). If $count_only is true, this is ignored.

Definition at line 421 of file co_main_data_db.class.php.

447  {
448  $ret = Array('sql' => '', 'params' => Array());
449 
450  $closure = ''; // This will be the suffix for the SQL.
451  $location_search = false; // We use this as a semaphore, so we don't shortcut location searches you can't refine to only IDs, because of the syntax of the SQL.
452  $link = '';
453 
454  // If we are doing a location/radius search, the predicate is a lot more complicated.
455  if (isset($in_search_parameters['location']) && isset($in_search_parameters['location']['longitude']) && isset($in_search_parameters['location']['latitude']) && isset($in_search_parameters['location']['radius'])) {
456  // We expand the radius by 5%, because we'll be triaging the results with the more accurate Vincenty calculation afterwards.
457  $predicate_temp = $this->_location_predicate($in_search_parameters['location']['longitude'], $in_search_parameters['location']['latitude'], floatval($in_search_parameters['location']['radius']) * 1.02, $and_writeable, $count_only);
458  $sql = $predicate_temp['sql'];
459  $ret['params'] = $predicate_temp['params'];
460  $closure = $count_only ? ')' : ') ORDER BY distance,id';
461  $location_search = true;
462  $link = ' AND ';
463  } else {
464  $predicate = $this->_create_security_predicate($and_writeable);
465 
466  if (!$predicate) {
467  $predicate = 'true'; // If we are in "God Mode," we could get no predicate, so we just go with "1".
468  }
469 
470  $sql = $count_only ? 'SELECT COUNT(*) FROM (' : '';
471  $sql .= 'SELECT * FROM '.$this->table_name.' WHERE ('.$predicate.' AND (';
472  $closure = $count_only ? ')' : ') ORDER BY id';
473  }
474 
475  // At this point, we have the "prefix" for the SQL query. That includes the security predicate, and any Haversine "triage" for location.
476  // We now add the actual parameters that specialize the search.
477 
478  if (isset($in_search_parameters) && is_array($in_search_parameters) && count($in_search_parameters)) {
479  // This function will parse the parameters, and return an associative array with the SQL WHERE clause, along with the relevant parameters for the prepared statement.
480  $param_ret = $this->_parse_parameters($in_search_parameters, $or_search);
481 
482  if ($param_ret['sql']) {
483  $sql .= $link.$param_ret['sql'];
484  if (count($param_ret['params'])) {
485  $ret['params'] = array_merge($ret['params'], $param_ret['params']);
486  }
487  }
488  } else {
489  $sql .= 'true';
490  }
491 
492  $closure = ")$closure";
493 
494  $page_size = intval($page_size);
495  // This only applies for MySQL or Postgres.
496  if (0 < $page_size) {
497  $initial_page = intval($initial_page);
498  $start = $initial_page * $page_size;
499  // Slightly different syntax for MySQL and Postgres.
500  if ( (('mysql' == $this->_pdo_object->driver_type) || ('mysqli' == $this->_pdo_object->driver_type))) {
501  $closure .= ' LIMIT '.$start.', '.$page_size;
502  } elseif ('pgsql' == $this->_pdo_object->driver_type) {
503  $closure .= ' LIMIT '.$page_size.' OFFSET '.$start;
504  }
505  }
506 
507  if ($count_only) {
508  $closure .= ') AS count';
509  } elseif ($ids_only && !$location_search) { // IDs only, we simply ask for only the ID.
510  $replacement = 'SELECT (id)';
511  $sql = preg_replace('|^SELECT \*|', $replacement, $sql);
512  }
513 
514  $ret['sql'] = $sql.$closure;
515 
516  return $ret;
517  }
A_CO_DB\_create_security_predicate
_create_security_predicate( $write=false)
Definition: a_co_db.class.php:167
CO_Main_Data_DB\_location_predicate
_location_predicate( $in_longitude, $in_latitude, $in_radius_in_km, $and_writeable=false, $count_only=false)
Definition: co_main_data_db.class.php:124
CO_Main_Data_DB\_parse_parameters
_parse_parameters( $in_search_parameters=NULL, $or_search=false)
Definition: co_main_data_db.class.php:349

References A_CO_DB\_create_security_predicate(), _location_predicate(), and _parse_parameters().

Referenced by generic_search().

Here is the call graph for this function:
Here is the caller graph for this function:

◆ _location_predicate()

CO_Main_Data_DB::_location_predicate (   $in_longitude,
  $in_latitude,
  $in_radius_in_km,
  $and_writeable = false,
  $count_only = false 
)
protected

This method creates a special SQL header that has an embedded Haversine formula. You use this in place of the security predicate.

The Haversine formula is not as accurate as the Vincenty Calculation, but is a lot less computationally intense, so we use this in SQL for a "triage."

Returns
an SQL query that will specify a Haversine search. It will include the security predicate.
Parameters
$in_longitudeThe search center longitude, in degrees.
$in_latitudeThe search center latitude, in degrees.
$in_radius_in_kmThe search radius, in Kilometers.
$and_writeableIf true, then we only want records we can modify.
$count_onlyIf true (default is false), then only a single integer will be returned, with the count of items that fit the search.

Definition at line 124 of file co_main_data_db.class.php.

129  {
130  $ret = Array('sql' => '', 'params' => Array());
131 
132  $predicate = $this->_create_security_predicate($and_writeable);
133 
134  if (!$predicate) {
135  $predicate = 'true'; // If we are in "God Mode," we could get no predicate, so we just go with "1".
136  }
137 
138  $ret['sql'] = $count_only ? 'SELECT COUNT(*) FROM (' : '';
139  $ret['sql'] .= "SELECT * FROM (
140  SELECT z.*,
141  p.radius,
142  p.distance_unit
143  * DEGREES(ACOS(COS(RADIANS(p.latpoint))
144  * COS(RADIANS(z.latitude))
145  * COS(RADIANS(p.longpoint - z.longitude))
146  + SIN(RADIANS(p.latpoint))
147  * SIN(RADIANS(z.latitude)))) AS distance
148  FROM ".$this->table_name." AS z
149  JOIN ( /* these are the query parameters */
150  SELECT ".floatval($in_latitude)." AS latpoint, ".floatval($in_longitude)." AS longpoint,
151  ".floatval($in_radius_in_km)." AS radius, 111.045 AS distance_unit
152  ) AS p ON 1=1
153  WHERE z.latitude
154  BETWEEN p.latpoint - (p.radius / p.distance_unit)
155  AND p.latpoint + (p.radius / p.distance_unit)
156  AND z.longitude
157  BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
158  AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
159  ) AS d
160  WHERE ($predicate AND ((distance <= radius)";
161 
162  return $ret;
163  }

References A_CO_DB\_create_security_predicate().

Referenced by _build_sql_query().

Here is the call graph for this function:
Here is the caller graph for this function:

◆ _parse_integer_parameter()

CO_Main_Data_DB::_parse_integer_parameter (   $in_db_key,
  $in_value 
)
protected

This method will return an SQL statement and an empty set of parameters for an integer table column value.

Returns
an SQL statement that acts as a WHERE clause for a integer.
Parameters
$in_db_keyThe table column name.
$in_valueThe value

Definition at line 277 of file co_main_data_db.class.php.

279  {
280  $ret = Array('sql' => '', 'params' => Array());
281 
282  if (isset($in_value) && is_array($in_value) && count($in_value)) {
283  $in_value = array_unique(array_map('intval', $in_value)); // Make sure we don't have repeats.
284 
285  $sql_array = Array();
286 
287  foreach ($in_value as $value) {
288  if (NULL !== $value) {
289  $sql_array[] = strval($in_db_key).'=?';
290  array_push($ret['params'], $value);
291  }
292  }
293 
294  $ret['sql'] = '('.implode(') OR (', $sql_array).')';
295  } else {
296  $ret['sql'] = ''.strval($in_db_key).'=?';
297  array_push($ret['params'], $in_value);
298  }
299 
300  return $ret;
301  }

Referenced by _parse_parameters().

Here is the caller graph for this function:

◆ _parse_parameters()

CO_Main_Data_DB::_parse_parameters (   $in_search_parameters = NULL,
  $or_search = false 
)
protected

This parses the provided parameters, and returns a WHERE clause for them.

Returns
an SQL statement that acts as a WHERE clause for the given parameters.
Parameters
$in_search_parametersThis is an associative array of terms to define the search. The keys should be:
  • 'id' This should be accompanied by an array of one or more integers, representing specific item IDs.
  • 'access_class' This should be accompanied by an array, containing one or more PHP class names (case-insensitive strings).
  • 'name' This will contain a case-insensitive array of strings to check against the object_name column.
  • 'owner' This should be accompanied by an array of one or more integers, representing specific item IDs for "owner" objects.
  • 'tags' This should be accompanied by an array (up to 10 elements) of one or more case-insensitive strings, representing specific tag values.
$or_searchIf true, then the search is very wide (OR), as opposed to narrow (AND), by default. If you specify a location, then that will always be AND, but the other fields can be OR.

Definition at line 349 of file co_main_data_db.class.php.

362  {
363  $ret = Array('sql' => '', 'params' => Array());
364 
365  if (isset($in_search_parameters) && is_array($in_search_parameters) && count ($in_search_parameters)) {
366  $sql_array = Array();
367  $param_array = Array();
368 
369  foreach ($in_search_parameters as $key => $value) {
370  $temp = NULL;
371 
372  switch ($key) {
373  case 'id':
374  $temp = $this->_parse_integer_parameter('id', $value);
375  break;
376 
377  case 'access_class':
378  $temp = $this->_parse_string_parameter('access_class', $value);
379  break;
380 
381  case 'name':
382  $temp = $this->_parse_string_parameter('object_name', $value);
383  break;
384 
385  case 'owner':
386  $temp = $this->_parse_integer_parameter('owner', $value);
387  break;
388 
389  case 'tags':
390  $temp = $this->_parse_tags($value);
391  break;
392 
393  default:
394  break;
395  }
396 
397  if (isset($temp) && is_array($temp) && count($temp)) {
398  $sql_array[] = $temp['sql'];
399  $ret['params'] = array_merge($ret['params'], $temp['params']);
400  }
401  }
402 
403  if (1 < count($sql_array)) {
404  $link = $or_search ? ') OR (' : ') AND (';
405 
406  $ret['sql'] = '(('.implode($link, $sql_array).'))';
407  } elseif (count($sql_array)) {
408  $ret['sql'] = $sql_array[0];
409  }
410  }
411 
412  return $ret;
413  }
CO_Main_Data_DB\_parse_string_parameter
_parse_string_parameter( $in_db_key, $in_value)
Definition: co_main_data_db.class.php:309
CO_Main_Data_DB\_parse_integer_parameter
_parse_integer_parameter( $in_db_key, $in_value)
Definition: co_main_data_db.class.php:277

References _parse_integer_parameter(), _parse_string_parameter(), and _parse_tags().

Referenced by _build_sql_query().

Here is the call graph for this function:
Here is the caller graph for this function:

◆ _parse_string_parameter()

CO_Main_Data_DB::_parse_string_parameter (   $in_db_key,
  $in_value 
)
protected

This method will return an SQL statement and a set of parameters for a case-insensitive string table column value.

Returns
an SQL statement that acts as a WHERE clause for a string.

Definition at line 309 of file co_main_data_db.class.php.

311  {
312  $ret = Array('sql' => '', 'params' => Array());
313 
314  if (isset($in_value) && is_array($in_value) && count($in_value)) {
315  $use_like = false;
316 
317  if (isset($in_value['use_like'])) {
318  $use_like = true;
319  unset($in_value['use_like']);
320  }
321 
322  $in_value = array_unique(array_map(function($in){return strtolower(trim(strval($in)));}, $in_value)); // Make sure we don't have repeats.
323  $sql_array = Array();
324 
325  foreach ($in_value as $value) {
326  if ((NULL != $value) && ('%' != $value)) {
327  $sql_array[] = 'LOWER('.strval($in_db_key).')'.($use_like ? ' LIKE ' : '=').'LOWER(?)';
328  array_push($ret['params'], $value);
329  } elseif ('%' == $value) {
330  $sql_array[] = '('.strval($in_db_key).'<>\'\')';
331  }
332  }
333 
334  $ret['sql'] = '(('.implode(') OR (', $sql_array).'))';
335  } else {
336  $ret['sql'] = 'LOWER('.strval($in_db_key).')=LOWER(?)';
337  $ret['params'][0] = $in_value;
338  }
339 
340  return $ret;
341  }

Referenced by _parse_parameters().

Here is the caller graph for this function:

◆ _parse_tags()

CO_Main_Data_DB::_parse_tags (   $in_value)
protected

This method will return an SQL statement and a set of parameters for the tags.

Returns
an SQL statement that acts as a WHERE clause for the tags.
Parameters
$in_valueThis should be an array of string. You can provide just one string, but that will always be applied to tag0.

Definition at line 171 of file co_main_data_db.class.php.

172  {
173  $ret = Array('sql' => '', 'params' => Array());
174  if (isset($in_value) && is_array($in_value) && count($in_value)) {
175  $use_like = false;
176 
177  if (isset($in_value['use_like'])) {
178  $use_like = true;
179  unset($in_value['use_like']);
180  }
181 
182  $sql_temp = Array();
183 
184  for ($i = 0; $i < count($in_value); $i++) {
185  $sql_temp[$i] = '';
186  $value = $in_value[$i];
187 
188  if ((NULL !== $value) && ('%' != $value)) {
189  if (is_array($value) && count($value)) {
190  $use_like_old = $use_like;
191 
192  if (isset($value['use_like'])) {
193  $use_like = true;
194  unset($value['use_like']);
195  }
196 
197  $inner_array = Array();
198  foreach ($value as $val) {
199  if (NULL != $val) {
200  $val = trim(strval($val));
201 
202  if ('' == $val) {
203  $inner_array[] = '((tag'.intval($i).' IS NULL) OR (tag'.intval($i).'=\'\'))';
204  } elseif ('%' == $val) {
205  $inner_array[] = '(tag'.intval($i).'<>\'\')';
206  } else {
207  $like_me = (false !== strpos($val, '%')) && $use_like;
208 
209  $inner_array[] = 'LOWER(tag'.intval($i).')'.($like_me ? ' LIKE ' : '=').'LOWER(?)';
210  array_push($ret['params'], $val);
211  }
212  }
213  }
214 
215  if (1 < count($inner_array)) {
216  $sql_temp[$i] = '('.implode(') OR (', $inner_array).')';
217  } elseif (count($inner_array)) {
218  $sql_temp[$i] = $inner_array[0];
219  }
220 
221  $use_like = $use_like_old;
222  } else {
223  if (NULL !== $value) {
224  $value = trim(strval($value));
225 
226  if ('' == $value) {
227  $sql_temp[$i] = '((tag'.intval($i).' IS NULL) OR (tag'.intval($i).'=\'\'))';
228  } else {
229  $like_me = (false !== strpos($value, '%')) && $use_like;
230 
231  $sql_temp[$i] = 'LOWER(tag'.intval($i).')'.($like_me ? ' LIKE ' : '=').'LOWER(?)';
232  array_push($ret['params'], strval($value));
233  }
234  }
235  }
236  } elseif ('%' == $value) {
237  $sql_temp[$i] = '(tag'.intval($i).'<>\'\')';
238  }
239  }
240 
241  $temp_array = Array();
242 
243  // Can't just do an array_filter, because PHP likes to keep the filtered elements at their original indexes.
244  foreach ($sql_temp as $array_element) {
245  if ('' != $array_element) {
246  array_push($temp_array, $array_element);
247  }
248  }
249  if (1 < count($temp_array)) {
250  $ret['sql'] = '(('.implode(') AND (', $temp_array).'))';
251  } elseif (1 == count($temp_array)) {
252  $ret['sql'] = $temp_array[0];
253  }
254  } else {
255  $in_value = trim(strval($in_value));
256  if (NULL !== $in_value) {
257  if ('' == $in_value) {
258  $ret['sql'] = '((tag0 IS NULL) OR (tag0=\'\'))';
259  } else {
260  $like_me = (false !== strpos($in_value, '%')) && $use_like;
261 
262  $ret['sql'] = '(LOWER(tag0)'.($like_me ? ' LIKE ' : '=').'LOWER(?))';
263  array_push($ret['params'], strval($value));
264  }
265  }
266  }
267 
268  return $ret;
269  }

Referenced by _parse_parameters().

Here is the caller graph for this function:

◆ generic_search()

CO_Main_Data_DB::generic_search (   $in_search_parameters = NULL,
  $or_search = false,
  $page_size = 0,
  $initial_page = 0,
  $and_writeable = false,
  $count_only = false,
  $ids_only = false 
)

This is a "generic" data database search. It can be called from external user contexts, and allows a fairly generalized search of the "data" database. Sorting will be done for the values by the ID of the searched objects. "location" will be by distance from the center.

Returns
an array of instances that match the search parameters.
Parameters
$in_search_parametersThis is an associative array of terms to define the search. The keys should be:
  • 'id' This should be accompanied by an array of one or more integers, representing specific item IDs.
  • 'access_class' This should be accompanied by an array, containing one or more PHP class names.
  • 'name' This will contain a case-insensitive array of strings to check against the object_name column.
  • 'owner' This should be accompanied by an array of one or more integers, representing specific item IDs for "owner" objects.
  • 'tags' This should be accompanied by an array (up to 10 elements) of one or more case-insensitive strings, representing specific tag values. The position in the array denotes which tag to match, so unchecked tags should still be in the array, but empty. You don't match empty tags. You can specify an array for the values, which allows you to do an OR search for the values.
  • 'location' This is only relevant if we are searching for subclasses (or instances) of CO_LL_Location This requires that the parameter be a 3-element associative array of floating-point numbers:
    • 'longitude' This is the search center location longitude, in degrees.
    • 'latitude' This is the search center location latitude, in degrees.
    • 'radius' This is the search radius, in Kilometers.

You can specify an array for any one of the values, which allows you to do an OR search for those values ($or_search does not apply. It is only for the combination of main values). If you add an element called 'use_like' ('use_like' => 1) to the end of 'access_class', 'name' or one of the 'tags', then you can use SQL-style "wildcards" (%) in your matches. If you have 'use_like', and put just a single wildcard in quotes (''), then you are saying "not-empty." NOTE: Although this is an optional parameter, failing to provide anything could return the entire readable database.

Parameters
$or_searchIf true, then the search is very wide (OR), as opposed to narrow (AND), by default. If you specify a location, then that will always be AND, but the other fields can be OR.
$page_sizeIf specified with a 1-based integer, this denotes the size of a "page" of results. NOTE: This is only applicable to MySQL or Postgres, and will be ignored if the DB is not MySQL or Postgres.
$initial_pageThis is ignored unless $page_size is greater than 0. If so, then this 0-based index will specify which page of results to return.
$and_writeableIf true, then we only want records we can modify.
$count_onlyIf true (default is false), then only a single integer will be returned, with the count of items that fit the search.
$ids_onlyIf true (default is false), then the return array will consist only of integers (the object IDs). If $count_only is true, this is ignored.

Definition at line 650 of file co_main_data_db.class.php.

684  {
685  $ret = NULL;
686 
687  // These are semaphores that we'll consult when the dust settles.
688  $location_count = $count_only;
689  $location_ids_only = $ids_only;
690  $location_search = (isset($in_search_parameters['location']) && isset($in_search_parameters['location']['longitude']) && isset($in_search_parameters['location']['latitude']) && isset($in_search_parameters['location']['radius']));
691 
692  if ($location_search) { // We're forced to use the regular search for count-only and IDs location, as we need that Vincenty filter.
693  $count_only = false;
694  $ids_only = false;
695  }
696 
697  $sql_and_params = $this->_build_sql_query($in_search_parameters, $or_search, $page_size, $initial_page, $and_writeable, $count_only, $ids_only);
698  $sql = $sql_and_params['sql'];
699  $params = $sql_and_params['params'];
700 
701  if ($sql) {
702  $temp = $this->execute_query($sql, $params);
703 // Commented out, but useful for debugging.
704 // echo('SQL:<pre>'.htmlspecialchars(print_r($sql, true)).'</pre>');
705 // echo('PARAMS:<pre>'.htmlspecialchars(print_r($params, true)).'</pre>');
706 // echo('RESULT:<pre>'.htmlspecialchars(print_r($temp, true)).'</pre>');
707  if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
708  if ($count_only) { // Different syntax for MySQL and Postgres
709  if (isset($temp[0]['count(*)'])) {
710  $ret = intval($temp[0]['count(*)']);
711  } else {
712  if (isset($temp[0]['count'])) {
713  $ret = intval($temp[0]['count']);
714  }
715  }
716  } else {
717  $ret = Array();
718  foreach ($temp as $result) {
719  $result = $ids_only ? intval($result['id']) : $this->_instantiate_record($result);
720  if ($result) {
721  array_push($ret, $result);
722  }
723  }
724 
725  // If we do a distance search, then we filter and sort the results with the more accurate Vincenty algorithm, and we also give each record a "distance" parameter.
726  if ($location_search) {
727  $ret_temp = Array();
728  $count = 0;
729 
730  foreach ($ret as $item) {
731  $latitude = floatval($item->raw_latitude()); // This allows logins with the rights to see accurate locations an accurate response.
732  $longitude = floatval($item->raw_longitude());
733 
734  $accurate_distance = self::get_accurate_distance(floatval($in_search_parameters['location']['latitude']), floatval($in_search_parameters['location']['longitude']), $latitude, $longitude);
735  if ($accurate_distance <= floatval($in_search_parameters['location']['radius'])) {
736  $item->distance = $accurate_distance;
737  array_push($ret_temp, $item);
738  $count++;
739  }
740  }
741 
742  if ($location_count) {
743  $ret_temp = $count;
744  } else {
745  usort($ret_temp, function($a, $b){return ($a->distance > $b->distance);});
746 
747  if ($location_ids_only) {
748  $ret_temp = array_map(function($in_item) { return $in_item->id(); }, $ret_temp);
749  }
750  }
751 
752  $ret = $ret_temp;
753  }
754  }
755  }
756  }
757 
758  return $ret;
759  }
A_CO_DB\execute_query
execute_query( $in_sql, $in_parameters=NULL, $exec_only=false)
Definition: a_co_db.class.php:292
A_CO_DB\_instantiate_record
_instantiate_record( $in_db_result)
Definition: a_co_db.class.php:181
CO_Main_Data_DB\_build_sql_query
_build_sql_query( $in_search_parameters=NULL, $or_search=false, $page_size=0, $initial_page=0, $and_writeable=false, $count_only=false, $ids_only=false)
Definition: co_main_data_db.class.php:421
CO_Main_Data_DB\get_accurate_distance
static get_accurate_distance( $lat1, $lon1, $lat2, $lon2)
Uses the Vincenty calculation to determine the distance (in Kilometers) between the two given lat/lon...
Definition: co_main_data_db.class.php:50

References _build_sql_query(), A_CO_DB\_instantiate_record(), A_CO_DB\execute_query(), and get_accurate_distance().

Here is the call graph for this function:

◆ get_accurate_distance()

static CO_Main_Data_DB::get_accurate_distance (   $lat1,
  $lon1,
  $lat2,
  $lon2 
)
static

Uses the Vincenty calculation to determine the distance (in Kilometers) between the two given lat/long pairs (in Degrees).

The Vincenty calculation is more accurate than the Haversine calculation, as it takes into account the "un-spherical" shape of the Earth, but is more computationally intense. We use this calculation to refine the Haversine "triage" in SQL.

Returns
a Float with the distance, in Kilometers.
Parameters
$lat1This is the first point latitude (degrees).
$lon1This is the first point longitude (degrees).
$lat2This is the second point latitude (degrees).
$lon2This is the second point longitude (degrees).

Definition at line 50 of file co_main_data_db.class.php.

55  {
56  if (($lat1 == $lat2) && ($lon1 == $lon2)) { // Just a quick shortcut.
57  return 0;
58  }
59 
60  $a = 6378137;
61  $b = 6356752.3142;
62  $f = 1/298.257223563; // WGS-84 ellipsiod
63  $L = ($lon2-$lon1)/57.2957795131;
64  $U1 = atan((1.0-$f) * tan($lat1/57.2957795131));
65  $U2 = atan((1.0-$f) * tan($lat2/57.2957795131));
66  $sinU1 = sin($U1);
67  $cosU1 = cos($U1);
68  $sinU2 = sin($U2);
69  $cosU2 = cos($U2);
70 
71  $lambda = $L;
72  $lambdaP = $L;
73  $iterLimit = 100;
74 
75  do {
76  $sinLambda = sin($lambda);
77  $cosLambda = cos($lambda);
78  $sinSigma = sqrt(($cosU2*$sinLambda) * ($cosU2*$sinLambda) + ($cosU1*$sinU2-$sinU1*$cosU2*$cosLambda) * ($cosU1*$sinU2-$sinU1*$cosU2*$cosLambda));
79  if ($sinSigma==0) {
80  return 0; // co-incident points
81  }
82 
83  $cosSigma = $sinU1*$sinU2 + ($cosU1*$cosU2*$cosLambda);
84  $sigma = atan2($sinSigma, $cosSigma);
85  $sinAlpha = ($cosU1 * $cosU2 * $sinLambda) / $sinSigma;
86  $cosSqAlpha = 1.0 - $sinAlpha*$sinAlpha;
87 
88  if (0 == $cosSqAlpha) {
89  return 0;
90  }
91 
92  $cos2SigmaM = $cosSigma - 2.0*$sinU1*$sinU2/$cosSqAlpha;
93 
94  $divisor = (16.0*$cosSqAlpha*(4.0+$f*(4.0-3.0*$cosSqAlpha)));
95 
96  if (0 == $divisor) {
97  return 0;
98  }
99 
100  $C = $f/$divisor;
101 
102  $lambdaP = $lambda;
103  $lambda = $L + (1.0-$C) * $f * $sinAlpha * ($sigma + $C*$sinSigma*($cos2SigmaM+$C*$cosSigma*(-1.0+2.0*$cos2SigmaM*$cos2SigmaM)));
104  } while (abs($lambda-$lambdaP) > 1e-12 && --$iterLimit>0);
105 
106  $uSq = $cosSqAlpha * ($a*$a - $b*$b) / ($b*$b);
107  $A = 1.0 + $uSq/16384.0*(4096.0+$uSq*(-768.0+$uSq*(320.0-175.0*$uSq)));
108  $B = $uSq/1024.0 * (256.0+$uSq*(-128.0+$uSq*(74.0-47.0*$uSq)));
109  $deltaSigma = $B*$sinSigma*($cos2SigmaM+$B/4.0*($cosSigma*(-1.0+2.0*$cos2SigmaM*$cos2SigmaM)-$B/6.0*$cos2SigmaM*(-3.0+4.0*$sinSigma*$sinSigma)*(-3.0+4.0*$cos2SigmaM*$cos2SigmaM)));
110  $s = $b*$A*($sigma-$deltaSigma);
111 
112  return ( abs ( round ( $s ) / 1000.0 ) );
113  }

Referenced by CO_LL_Location\_km_per_degree(), and generic_search().

Here is the caller graph for this function:

◆ get_all_visible_users()

CO_Main_Data_DB::get_all_visible_users ( )

This is an extremely simple function that gets the ID and name (usually the display name) of user instances visible to the current login. It's goal is to be extremely fast and result in a relatively small response.

Returns
an associative array (if users are found) of (id => name)

Definition at line 575 of file co_main_data_db.class.php.

575  {
576  $ret = array();
577  $sql = 'SELECT id,object_name FROM '.$this->table_name.' WHERE';
578 
579  $predicate = $this->_create_read_security_predicate();
580 
581  if ($predicate) {
582  $sql = "$sql $predicate AND";
583  }
584 
585  $sql = "$sql access_class='CO_User_Collection'";
586 
587  $temp = $this->execute_query($sql, Array());
588  if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
589  foreach($temp as $value) {
590  $ret[$value["id"]] = $value["object_name"];
591  }
592  }
593 
594  return $ret;
595  }
A_CO_DB\_create_read_security_predicate
_create_read_security_predicate()
Definition: a_co_db.class.php:104

References A_CO_DB\_create_read_security_predicate(), and A_CO_DB\execute_query().

Here is the call graph for this function:

◆ item_exists()

CO_Main_Data_DB::item_exists (   $in_id,
  $in_visibility_test = false 
)

This is a very "raw" function that simply checks to see if any item exists for a given integer ID.

This (usually) deliberately does not pass security vetting, so we're careful. It's meant to be used by collection classes for garbage collection.

Returns
true, if an item exists for the given ID (if $in_visibility_test is set to true, then the item also has to be visible for reading by the user. Otherwise, you get true, whether or not the user can see it).
Parameters
$in_idThe ID of the item.
$in_visibility_testIf true (default is false), then this will return false, even if the item exists, but cannot be seen by this user.

Definition at line 542 of file co_main_data_db.class.php.

544  {
545  $ret = NULL;
546 
547  $sql = 'SELECT id FROM '.$this->table_name.' WHERE ';
548 
549  if ($in_visibility_test) { // If we are only testing visibility, then we add a read security predicate.
550  $predicate = $this->_create_read_security_predicate();
551 
552  if ($predicate) {
553  $sql = "$sql$predicate AND ";
554  }
555  }
556 
557  // User collections work by having the login ID in tag 0, so we search for any collection records that have a tag 0 set to our login ID. Chances are good it's a user.
558  $sql .= 'id='.intval($in_id);
559 
560  $temp = $this->execute_query($sql, Array());
561  if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
562  $ret = true;
563  }
564 
565  return $ret;
566  }

References A_CO_DB\_create_read_security_predicate(), and A_CO_DB\execute_query().

Here is the call graph for this function:

◆ see_if_user_exists()

CO_Main_Data_DB::see_if_user_exists (   $in_login_id)

This is a very "raw" function that simply checks to see if a user collection exists for a given login ID.

This deliberately does not pass security vetting, so we're careful.

Returns
true, if a user exists for the given login ID.
Parameters
$in_login_idThe login ID of the user.

Definition at line 605 of file co_main_data_db.class.php.

606  {
607  $ret = NULL;
608 
609  // User collections work by having the login ID in tag 0, so we search for any collection records that have a tag 0 set to our login ID. Chances are good it's a user.
610  $sql = 'SELECT * FROM '.$this->table_name.' WHERE (access_class LIKE \'%_User_Collection\') AND (tag0=\''.intval($in_login_id).'\')';
611 
612  $temp = $this->execute_query($sql, Array($in_login_id));
613  if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
614  // We instantiate, as opposed to check the access_class, because we want to give the implementation the option of subclassing.
615  $result = $this->_instantiate_record($temp[0]);
616  if ($result instanceof CO_User_Collection) { // This will crash if we aren't looking at it from a CHAMELEON (at least) level. That's good.
617  $ret = true;
618  }
619  }
620 
621  return $ret;
622  }

◆ tag0_is_unique()

CO_Main_Data_DB::tag0_is_unique (   $in_tag0,
  $in_classname = 'CO_KeyValue_CO_Collection' 
)
Returns
true, if the tag1 is unique in the DB (regardless of whether or not we can see it).
Parameters
$in_tag0The key to test (a string).
$in_classnameThis is the class to search for the key. The default is the base class.

Definition at line 628 of file co_main_data_db.class.php.

630  {
631  $ret = true;
632 
633  $sql = 'SELECT id FROM '.$this->table_name.' WHERE (access_class=?) AND (tag0=?)';
634  $params = [$in_classname, $in_tag0];
635  $temp = $this->execute_query($sql, $params);
636  if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
637  $ret = false;
638  }
639 
640  return $ret;
641  }