From 5e3ac2abe7471c83f8db5980440325c51cd9bb4b Mon Sep 17 00:00:00 2001 From: "Michael J. Rubinsky" Date: Sun, 18 Oct 2009 14:40:11 -0400 Subject: [PATCH] Very basic backend support for storing event location data. Generic abstract storage driver for location data, with concrete drivers for mySQL using GIS support, and a generic sql driver with no special GIS abilities (will not support any spatial searches etc..) --- kronolith/lib/Driver/Geo.php | 27 ++++ kronolith/lib/Driver/Geo/Mysql.php | 96 +++++++++++ kronolith/lib/Driver/Geo/Sql.php | 177 +++++++++++++++++++++ kronolith/lib/Kronolith.php | 2 + kronolith/scripts/sql/kronolith.mysql.sql | 22 +++ .../upgrades/2009-10_10_add_geodata.mysql.sql | 6 + .../scripts/upgrades/2009-10_10_add_geodata.sql | 6 + 7 files changed, 336 insertions(+) create mode 100644 kronolith/lib/Driver/Geo.php create mode 100644 kronolith/lib/Driver/Geo/Mysql.php create mode 100644 kronolith/lib/Driver/Geo/Sql.php create mode 100644 kronolith/scripts/upgrades/2009-10_10_add_geodata.mysql.sql create mode 100644 kronolith/scripts/upgrades/2009-10_10_add_geodata.sql diff --git a/kronolith/lib/Driver/Geo.php b/kronolith/lib/Driver/Geo.php new file mode 100644 index 000000000..afff45825 --- /dev/null +++ b/kronolith/lib/Driver/Geo.php @@ -0,0 +1,27 @@ + + * + * @package Kronolith + */ +abstract class Kronolith_Driver_Geo +{ + protected $_params; + + public function __construct($params = array()) + { + $this->_params = $params; + } + + abstract public function setLocation($event_id, $point); + abstract public function getLocation($event_id); + abstract public function removeLocation($event_id); + abstract public function search($criteria); +} \ No newline at end of file diff --git a/kronolith/lib/Driver/Geo/Mysql.php b/kronolith/lib/Driver/Geo/Mysql.php new file mode 100644 index 000000000..a1d102f11 --- /dev/null +++ b/kronolith/lib/Driver/Geo/Mysql.php @@ -0,0 +1,96 @@ + + * + * @package Kronolith + */ +class Kronolith_Driver_Geo_Mysql extends Kronolith_Driver_Geo_Sql +{ + // Rouughly 69 miles per distance unit + private $_conversionFactor = 69; + + /** + * Set the location of the specified event _id + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#setLocation($event_id, $point) + */ + public function setLocation($event_id, $point) + { + /* First make sure it doesn't already exist */ + $sql = "SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ('" . $event_id . "')"; + $count = $this->_db->getOne($sql); + if ($count instanceof PEAR_Error) { + throw new Horde_Exception($count->getMessage()); + } + /* INSERT or UPDATE */ + if ($count) { + $sql = "UPDATE kronolith_events_geo SET coordinates = GeomFromText('POINT(" . $point['lat'] . " " . $point['lng'] . ")') WHERE event_id = '" . $event_id . "'"; + } else { + $sql = "INSERT into kronolith_events_geo (event_id, coordinates) VALUES('" . $event_id . "', GeomFromText('POINT(" . $point['lat'] . " " . $point['lng'] . ")'))"; + } + $result = $this->_write_db->query($sql); + if ($result instanceof PEAR_Error) { + throw new Horde_Exception($result->getMessage()); + } + + return $result; + } + + /** + * Get the location of the provided event_id. + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#getLocation($event_id) + */ + public function getLocation($event_id) + { + $sql = "SELECT x(coordinates) as lat, y(coordinates) as lng FROM kronolith_events_geo WHERE event_id = '" . $event_id . "'"; + $result = $this->_db->getRow($sql, null, DB_FETCHMODE_ASSOC); + if ($result instanceof PEAR_Error) { + throw new Horde_Exception($result->getMessage()); + } + return $result; + } + + /** + * Search for events "close to" a given point. + * + * TODO: If all we really use the geodata for is distance, it really doesn't + * make sense to use the GIS extensions since the distance calculations + * are done with Euclidian geometry ONLY ... and therefore will give + * incorrect results when done on a geocentric coordinate system... + * They might be useful if we eventually want to do searches on + * MBRs + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#search($criteria) + */ + public function search($criteria) + { + $point = $criteria['point']; + $limit = empty($criteria['limit']) ? 10 : $criteria['limit']; + $radius = empty($criteria['radius']) ? 10 : $criteria['radius']; + + /* Allow overriding the default conversion factor */ + $factor = empty($criteria['factor']) ? $this->_conversionFactor : $criteria['factor']; + + // ... if this works it will be a miracle ;) + $sql = "SELECT event_id, " + . "GLength(LINESTRINGFromWKB(LineString(coordinates, GeomFromText('POINT(" . $point['lat'] . " " . $point['lng'] . ")')))) * " . $factor . " as distance, " + . "x(coordinates) as lat, y(coordinates) as lng FROM kronolith_events_geo HAVING distance < " . $radius . " ORDER BY distance ASC LIMIT " . $limit; + + $results = $this->_db->getAssoc($sql, false, null, DB_FETCHMODE_ASSOC); + if ($results instanceof PEAR_Error) { + throw new Horde_Exception($results->getMessage()); + } + + return $results; + + } +} \ No newline at end of file diff --git a/kronolith/lib/Driver/Geo/Sql.php b/kronolith/lib/Driver/Geo/Sql.php new file mode 100644 index 000000000..d60f4b821 --- /dev/null +++ b/kronolith/lib/Driver/Geo/Sql.php @@ -0,0 +1,177 @@ + + * + * @package Kronolith + */ +class Kronolith_Driver_Geo_Sql extends Kronolith_Driver_Geo +{ + protected $_write_db; + protected $_db; + + /** + * Still needs to return a PEAR_Error since Kronolith_Driver still expects it + * + * @return mixed boolean || PEAR_Error + */ + public function initialize() + { + Horde::assertDriverConfig($this->_params, 'calendar', array('phptype')); + + if (!isset($this->_params['database'])) { + $this->_params['database'] = ''; + } + if (!isset($this->_params['username'])) { + $this->_params['username'] = ''; + } + if (!isset($this->_params['hostspec'])) { + $this->_params['hostspec'] = ''; + } + if (!isset($this->_params['table'])) { + $this->_params['table'] = 'kronolith_events_geo'; + } + + /* Connect to the SQL server using the supplied parameters. */ + $this->_write_db = DB::connect($this->_params, + array('persistent' => !empty($this->_params['persistent']), + 'ssl' => !empty($this->_params['ssl']))); + if (is_a($this->_write_db, 'PEAR_Error')) { + return $this->_write_db; + } + $this->_initConn($this->_write_db); + + /* Check if we need to set up the read DB connection + * seperately. */ + if (!empty($this->_params['splitread'])) { + $params = array_merge($this->_params, $this->_params['read']); + $this->_db = DB::connect($params, + array('persistent' => !empty($params['persistent']), + 'ssl' => !empty($params['ssl']))); + if (is_a($this->_db, 'PEAR_Error')) { + return $this->_db; + } + $this->_initConn($this->_db); + } else { + /* Default to the same DB handle for the writer too. */ + $this->_db = $this->_write_db; + } + + return true; + } + + protected function _initConn(&$db) + { + // Set DB portability options. + switch ($db->phptype) { + case 'mssql': + $db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS | DB_PORTABILITY_RTRIM); + break; + default: + $db->setOption('portability', DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_ERRORS); + } + + /* Handle any database specific initialization code to run. */ + switch ($db->dbsyntax) { + case 'oci8': + $query = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"; + + /* Log the query at a DEBUG log level. */ + Horde::logMessage(sprintf('Kronolith_Driver_Sql::_initConn(): user = "%s"; query = "%s"', + Horde_Auth::getAuth(), $query), + __FILE__, __LINE__, PEAR_LOG_DEBUG); + + $db->query($query); + break; + + case 'pgsql': + $query = "SET datestyle TO 'iso'"; + + /* Log the query at a DEBUG log level. */ + Horde::logMessage(sprintf('Kronolith_Driver_Sql::_initConn(): user = "%s"; query = "%s"', + Horde_Auth::getAuth(), $query), + __FILE__, __LINE__, PEAR_LOG_DEBUG); + + $db->query($query); + break; + } + } + + /** + * Set the location of the specified event _id + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#setLocation($event_id, $point) + */ + public function setLocation($event_id, $point) + { + /* First make sure it doesn't already exist */ + $sql = "SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ('" . $event_id . "')"; + $count = $this->_db->getOne($sql); + if ($count instanceof PEAR_Error) { + throw new Horde_Exception($count->getMessage()); + } + + /* INSERT or UPDATE */ + $params = array($point['lat'], $point['lng'], $event_id); + if ($count) { + $sql = 'UPDATE kronolith_events_geo SET event_lat = ?, event_lng = ? WHERE event_id = ?'; + } else { + $sql = 'INSERT into kronolith_events_geo (event_lat, event_lng, event_id) VALUES(?, ?, ?)'; + } + $result = $this->_write_db->query($sql, $params); + if ($result instanceof PEAR_Error) { + throw new Horde_Exception($result->getMessage()); + } + + return $result; + } + + /** + * Get the location of the provided event_id. + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#getLocation($event_id) + */ + public function getLocation($event_id) + { + $sql = 'SELECT event_lat as lat, event_lng as lng FROM kronolith_events_geo WHERE event_id = ?'; + $result = $this->_db->getRow($sql, array($event_id), DB_FETCHMODE_ASSOC); + if ($result instanceof PEAR_Error) { + throw new Horde_Exception($result->getMessage()); + } + + return $result; + } + + /** + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#removeLocation($event_id) + */ + public function removeLocation($event_id) + { + $sql = 'DELETE FROM kronolith_events_geo WHERE event_id = ?'; + $this->_write_db->query($sql, array($event_id)); + } + + /** + * Search for events "close to" a given point. + * + * TODO: If all we really use the geodata for is distance, it really doesn't + * make sense to use the GIS extensions since the distance calculations + * are done with Euclidian geometry ONLY ... and therefore will give + * incorrect results when done on a geocentric coordinate system... + * They might be useful if we eventually want to do searches on + * MBRs + * + * @see kronolith/lib/Driver/Kronolith_Driver_Geo#search($criteria) + */ + public function search($criteria) + { + throw new Horde_Exception(_("Searching requires a GIS enabled database.")); + } +} \ No newline at end of file diff --git a/kronolith/lib/Kronolith.php b/kronolith/lib/Kronolith.php index 06e8dd641..d0c644856 100644 --- a/kronolith/lib/Kronolith.php +++ b/kronolith/lib/Kronolith.php @@ -1800,6 +1800,8 @@ class Kronolith switch ($driver) { case 'Sql': case 'Resource': + case 'Geo_Mysql': + case 'Geo_Sql': $params = Horde::getDriverConfig('calendar', 'sql'); break; diff --git a/kronolith/scripts/sql/kronolith.mysql.sql b/kronolith/scripts/sql/kronolith.mysql.sql index e8ef3d708..b2f1354f9 100644 --- a/kronolith/scripts/sql/kronolith.mysql.sql +++ b/kronolith/scripts/sql/kronolith.mysql.sql @@ -79,3 +79,25 @@ CREATE TABLE kronolith_shares_users ( CREATE INDEX kronolith_shares_users_share_id_idx ON kronolith_shares_users (share_id); CREATE INDEX kronolith_shares_users_user_uid_idx ON kronolith_shares_users (user_uid); CREATE INDEX kronolith_shares_users_perm_idx ON kronolith_shares_users (perm); + +CREATE TABLE kronolith_resources ( + resource_id INT NOT NULL, + resource_name VARCHAR(255), + resource_calendar VARCHAR(255), + resource_description TEXT, + resource_category VARCHAR(255) DEFAULT '', + resource_response_type INT DEFAULT 0, + resource_type VARCHAR(255) NOT NULL, + resource_members BLOB, + + PRIMARY KEY (resource_id) +); + +CREATE TABLE kronolith_events_geo ( + event_id VARCHAR(32) NOT NULL, + coordinates POINT, + PRIMARY KEY (event_id), + SPATIAL KEY (coordinates) + ); + + \ No newline at end of file diff --git a/kronolith/scripts/upgrades/2009-10_10_add_geodata.mysql.sql b/kronolith/scripts/upgrades/2009-10_10_add_geodata.mysql.sql new file mode 100644 index 000000000..d7de489da --- /dev/null +++ b/kronolith/scripts/upgrades/2009-10_10_add_geodata.mysql.sql @@ -0,0 +1,6 @@ +CREATE TABLE kronolith_events_geo ( + event_id VARCHAR(32) NOT NULL, + event_lat INT(11), + event_lng INT(11), + PRIMARY KEY (event_id), + ); diff --git a/kronolith/scripts/upgrades/2009-10_10_add_geodata.sql b/kronolith/scripts/upgrades/2009-10_10_add_geodata.sql new file mode 100644 index 000000000..2592cc061 --- /dev/null +++ b/kronolith/scripts/upgrades/2009-10_10_add_geodata.sql @@ -0,0 +1,6 @@ +CREATE TABLE kronolith_events_geo ( + event_id VARCHAR(32) NOT NULL, + coordinates POINT, + PRIMARY KEY (event_id), + SPATIAL KEY (coordinates) + ); -- 2.11.0