From 640b8cfa6e428926f2efae356808ba0ed194a78f Mon Sep 17 00:00:00 2001 From: "Michael J. Rubinsky" Date: Sat, 29 May 2010 19:39:31 -0400 Subject: [PATCH] lazy load the geoLocation data. Don't query the geoLocation datastore for every event, only query the datastore when we actually ask for the data. Also add logging for the geo related queries. This, along with the previous commits for the way tag queries are done should significantly improve performance when calling listEvents(), especially for large datasets. --- kronolith/lib/Event.php | 45 +++++++++++++++++++++++++++++---------- kronolith/lib/Geo/Mysql.php | 9 ++++++++ kronolith/lib/Geo/Sql.php | 8 +++++++ kronolith/lib/Resource/Single.php | 2 +- 4 files changed, 52 insertions(+), 12 deletions(-) diff --git a/kronolith/lib/Event.php b/kronolith/lib/Event.php index 6c9db6394..8b002f8c2 100644 --- a/kronolith/lib/Event.php +++ b/kronolith/lib/Event.php @@ -117,8 +117,9 @@ abstract class Kronolith_Event /** * Geolocation * + * @var arary */ - public $geoLocation = null; + protected $_geoLocation; /** * Whether this is the event on the first day of a multi-day event. @@ -299,14 +300,6 @@ abstract class Kronolith_Event if (!is_null($eventObject)) { $this->fromDriver($eventObject); - - /* Get geolocation data */ - if ($gDriver = Kronolith::getGeoDriver()) { - try { - $this->geoLocation = $gDriver->getLocation($this->id); - } catch (Exception $e) { - } - } } } @@ -366,7 +359,10 @@ abstract class Kronolith_Event return $this->{'_' . $name}; case 'tags': return $this->getTags(); + case 'geoLocation': + return $this->getGeolocation(); } + $trace = debug_backtrace(); trigger_error('Undefined property via __set(): ' . $name . ' in ' . $trace[0]['file'] @@ -2254,8 +2250,8 @@ abstract class Kronolith_Event $this->_tags = Horde_Util::getFormData('tags', $this->tags); // Geolocation - $this->geoLocation = array('lat' => Horde_Util::getFormData('lat'), - 'lon' => Horde_Util::getFormData('lon')); + $this->setGeoLocation(array('lat' => Horde_Util::getFormData('lat'), + 'lon' => Horde_Util::getFormData('lon'))); $this->initialized = true; } @@ -2737,6 +2733,33 @@ abstract class Kronolith_Event return $this->_tags; } + /** + * Setter for geo data + * + * @param array $data An array of lat/lng data. + */ + public function setGeoLocation($data) + { + $this->_geoLocation = $data; + } + + /** + * Getter for geo data + * + * @return array An array of lat/lng data. + */ + public function getGeolocation() + { + /* Get geolocation data */ + if (($gDriver = Kronolith::getGeoDriver()) && !isset($this->_geoLocation)) { + try { + $this->_geoLocation = $gDriver->getLocation($this->id); + } catch (Exception $e) {} + } + + return $this->_geoLocation; + } + private function _formIDEncode($id) { return str_replace(array('[', ']'), diff --git a/kronolith/lib/Geo/Mysql.php b/kronolith/lib/Geo/Mysql.php index db36c8a2c..8333e47af 100644 --- a/kronolith/lib/Geo/Mysql.php +++ b/kronolith/lib/Geo/Mysql.php @@ -27,6 +27,8 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql { /* First make sure it doesn't already exist */ $sql = 'SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ?'; + Horde::logMessage(sprintf('Kronolith_Geo_Mysql::setLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, $event_id), 'DEBUG'); $count = $this->_db->getOne($sql, array($event_id)); if ($count instanceof PEAR_Error) { Horde::logMessage($count, 'ERR'); @@ -48,6 +50,8 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql } else { $sql = sprintf('INSERT into kronolith_events_geo (event_id, event_coordinates) VALUES(?, GeomFromText(\'POINT(%F %F)\'))', $point['lat'], $point['lon']); } + Horde::logMessage(sprintf('Kronolith_Geo_Mysql::setLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, $event_id), 'DEBUG'); $result = $this->_write_db->query($sql, array($event_id)); if ($result instanceof PEAR_Error) { Horde::logMessage($result, 'ERR'); @@ -66,6 +70,8 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql public function getLocation($event_id) { $sql = 'SELECT x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_geo WHERE event_id = ?'; + Horde::logMessage(sprintf('Kronolith_Geo_Mysql::getLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, $event_id), 'DEBUG'); $result = $this->_db->getRow($sql, array($event_id), DB_FETCHMODE_ASSOC); if ($result instanceof PEAR_Error) { Horde::logMessage($result, 'ERR'); @@ -101,6 +107,9 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql . "GLength(LINESTRINGFromWKB(LineString(event_coordinates, GeomFromText('POINT(" . (float)$point['lat'] . " " . (float)$point['lon'] . ")')))) * ? as distance, " . "x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_geo HAVING distance < ? ORDER BY distance ASC LIMIT ?"; + Horde::logMessage(sprintf('Kronolith_Geo_Mysql::search(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, print_r($params, true)), 'DEBUG'); + $results = $this->_db->getAssoc($sql, false, $params, DB_FETCHMODE_ASSOC); if ($results instanceof PEAR_Error) { Horde::logMessage($results, 'ERR'); diff --git a/kronolith/lib/Geo/Sql.php b/kronolith/lib/Geo/Sql.php index 0ee67ffde..9774dee05 100644 --- a/kronolith/lib/Geo/Sql.php +++ b/kronolith/lib/Geo/Sql.php @@ -87,6 +87,8 @@ class Kronolith_Geo_Sql extends Kronolith_Geo { /* First make sure it doesn't already exist */ $sql = 'SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ?'; + Horde::logMessage(sprintf('Kronolith_Geo_Sql::setLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, $event_id), 'DEBUG'); $count = $this->_db->getOne($sql, array($event_id)); if ($count instanceof PEAR_Error) { Horde::logMessage($count, 'ERR'); @@ -111,6 +113,8 @@ class Kronolith_Geo_Sql extends Kronolith_Geo } else { $sql = 'INSERT into kronolith_events_geo (event_lat, event_lon, event_id) VALUES(?, ?, ?)'; } + Horde::logMessage(sprintf('Kronolith_Geo_Sql::setLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, print_r($params, true)), 'DEBUG'); $result = $this->_write_db->query($sql, $params); if ($result instanceof PEAR_Error) { Horde::logMessage($result, 'ERR'); @@ -129,6 +133,8 @@ class Kronolith_Geo_Sql extends Kronolith_Geo public function getLocation($event_id) { $sql = 'SELECT event_lat as lat, event_lon as lon FROM kronolith_events_geo WHERE event_id = ?'; + Horde::logMessage(sprintf('Kronolith_Geo_Sql::getLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, $event_id), 'DEBUG'); $result = $this->_db->getRow($sql, array($event_id), DB_FETCHMODE_ASSOC); if ($result instanceof PEAR_Error) { Horde::logMessage($result, 'ERR'); @@ -150,6 +156,8 @@ class Kronolith_Geo_Sql extends Kronolith_Geo public function deleteLocation($event_id) { $sql = 'DELETE FROM kronolith_events_geo WHERE event_id = ?'; + Horde::logMessage(sprintf('Kronolith_Geo_Sql::deleteLocation(): user = "%s"; query = "%s"; values = "%s"', + Horde_Auth::getAuth(), $sql, $event_id), 'DEBUG'); $result = $this->_write_db->query($sql, array($event_id)); if ($result instanceof PEAR_Error) { Horde::logMessage($result, 'ERR'); diff --git a/kronolith/lib/Resource/Single.php b/kronolith/lib/Resource/Single.php index cc2991e32..daf7f06a2 100644 --- a/kronolith/lib/Resource/Single.php +++ b/kronolith/lib/Resource/Single.php @@ -157,7 +157,7 @@ class Kronolith_Resource_Single extends Kronolith_Resource_Base $to->description = $from->description; $to->url = $from->url; $to->setTags($from->tags); - $to->geoLocation = $from->geoLocation; + $to->setGeoLocation($from->geoLocation); $to->first = $from ->first; $to->last = $from->last; $to->start = $from->start; -- 2.11.0