POSIT:PositwebDatabase

From Notes

Jump to: navigation, search

Contents

Introduction

The dao.php class manages all interactions between the Positweb server and its MySQL database. It is written in PHP. As the data access object (DAO) it represents the model portion of the server's [model-view-controller (MVC) architecture].

Object Oriented Design

The DAO mediates interactions between the server's control code and its database. A DAO object is created as a global variable. Here's an example of using the DAO from the application layer to check a user's login:

        $loginId = $_SESSION["loginId"];
	if($loginId) {
	        $loginUser = $dao->getUser($loginId);

                if($loginUser["privileges"] == "admin")
                        $loginHasAdmin = true;
                else
                        $loginHasAdmin = false;
	} 

If this example the DAO's getUser() function is invoked to fetch a user's privileges.

The DAO uses PHP's object-orientation capabilities (PHP 5.0) to form database queries. It uses PHP's PDO extension. A PDO is special object that makes a connection between the application (in this case dao.php) and the underlying database (in this case a MySQL database).

The PDO is initialized whenever the DAO connects to the database. This occurs in the dbConnect() function, which must be called before any database access. It returns a reference to the PDO object.

function dbConnect() {
        $host           = DB_HOST;
        $user           = DB_USER;
        $pass           = DB_PASS;
        $db_name        = DB_NAME;

        try {
            $db = new PDO("mysql:host=$host;dbname=$db_name", $user, $pass);
        } catch (PDOException $e) {
            errorMessage("Database error: ". $e->getMessage());
            die();
	}
        mysql_connect($host, $user, $pass);
        mysql_select_db($db_name);
        return $db;
}

The PDO object (db) can then be used to construct and execute queries on the database. Here's a simple example that shows the implementation of the getUser() function that was seen in the example above. The function retrieves a user's data from the user table given the user's Id:

        function getUser($userId) {
		$this->createLog("I","getUser","$userId");

                $stmt = $this->db->prepare(
			"SELECT email, first_name, last_name, privileges, create_time FROM user WHERE id = :userId"
                );

		$stmt->bindValue(":userId", $userId);
		$stmt->execute();

		return $stmt->fetch(PDO::FETCH_ASSOC);
        }

The this->db->prepare function creates a query object (stmt) from a query string. It then binds values to the variables in the query object and executes the query. The query's result can then be fetched from the query object and returned to the calling function.

Here is another typical DAO function, in this case a function to check a user's login credentials:

        function checkLogin($email, $pass) {
                $stmt = $this->db->prepare(
                        "SELECT id, first_name, last_name
                         FROM user
                         WHERE email = :email AND password = SHA1(:pass)"
                ) or print_r($this->db->errorInfo()) && die();
                $stmt->bindValue(':email', $email);
                $stmt->bindValue(':pass', $pass);

                $stmt->execute();

                if($result = $stmt->fetch(PDO::FETCH_ASSOC))
                        return $result;
                else
                        return false;
        }

A non-null result represents a successful query -- in this case, a successful query means that a record was found in the user table for that particular email and password.

The Positweb Database

Some of the tables in the server's database correspond to tables in the [[POSIT:PositDbHelper|phone's database]. The find table corresponds to the phone's finds table and has the following structure:

CREATE TABLE `find` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `description` varchar(100) NOT NULL,
  `guid` varchar(64) DEFAULT NULL,
  `name` varchar(32) NOT NULL,
  `add_time` datetime NOT NULL,
  `modify_time` datetime NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `revision` int(11) NOT NULL,
  `imei` varchar(50) NOT NULL,
  `auth_key` varchar(32) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

When a new Find is inserted into the table, a new row ID (id) will be automatically generated. The find itself has a globally unique ID (its guid), which is generated on the phone and makes it very likely that Finds gathered by different phones will have distinct IDs. Each find is associated with a project (project_id). And each find contains data from the phone such as name, description, latitude, and longitude.

In addition to the Find's data, there are a number of fields used to manage finds. The imei identifies the phone that submitted the Find. The auth_key is the authorization key possessed by that phone. The add_time and modify_time fields record changes to the Find and the 'revision field keeps track of the number of changes made. Finally, a Find is marked deleted rather than actually removing it from the database. Deleted finds are not displayed.

The photo table, which corresponds to the phone's photos table, stores all photos associated with a Find.

CREATE TABLE `photo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guid` varchar(64) NOT NULL,
  `project_id` int(11) NOT NULL,
  `identifier` bigint(17) NOT NULL,
  `mime_type` varchar(32) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `data_full` blob NOT NULL,
  `data_thumb` blob NOT NULL,
  `imei` varchar(50) NOT NULL,
  `auth_key` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `guid` (`guid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Photos are associated with a find through their guid. In addition, each photo has a unique identifier created by the phone. The photo itself as well as its thumbnail are stored as MySQL blobs.

Expeditions and points are stored in a pair of tables that closely match those on the phone:

CREATE TABLE `expedition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT 'Expedition',
  `project_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `description` text,
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `gps_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sample_time` datetime NOT NULL,
  `expedition_id` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `altitude` double NOT NULL,
  `swath` int(11) NOT NULL,
  `time` bigint(17) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Expeditions are associated with a given project (project_id) and the points (called gps_samples) associated with an expedition are linked to it through the expedition_id field.

As on the phone's database, the find_history and sync_history tables are used to manage synchronization between client and server. All changes made to a Find are recorded and time stamped in the find_history table. And all synchronizations are recorded and time stamped in the sync history table. Finds needing synchronization are identified by search for all finds that have changed since the last synchronization. These finds are transmitted to the client.

CREATE TABLE `find_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `find_guid` varchar(64) NOT NULL,
  `action` varchar(20) NOT NULL,
  `imei` varchar(50) NOT NULL,
  `auth_key` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

CREATE TABLE `sync_history` (

 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `imei` varchar(50) NOT NULL,
 `auth_key` varchar(64) NOT NULL,
 PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;


Tables Unique to the Server

In addition to the above tables, the server's database contains a number of tables that are used to manage communication with the phones. The user table records data about registered user, using an email address to uniquely identify the user.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(32) NOT NULL,
  `password` varchar(40) NOT NULL,
  `first_name` varchar(32) NOT NULL,
  `last_name` varchar(32) NOT NULL,
  `privileges` set('normal','admin') NOT NULL DEFAULT 'normal',
  `validated` tinyint(1) DEFAULT '0',
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

The device table stores information about the phone and its user.

CREATE TABLE `device` (
  `imei` varchar(16) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `auth_key` varchar(32) NOT NULL,
  `add_time` datetime NOT NULL,
  `status` set('pending','ok') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`auth_key`),
  UNIQUE KEY `imei` (`imei`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The imei is the device's unique ID number--short for International Mobile Equipment Identity. The auth_key is the key required for all accesses to the server. All phones are associated with a registered user -- a phone cannot be registered until its user has established an account on the server.

The project table stores data about Projects created by the user. A Project might be a search expedition and could involve a number of searchers. Project's are identified by their id and also by name.

CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `description` text NOT NULL,
  `create_time` datetime NOT NULL,
  `permission_type` set('open','closed') NOT NULL DEFAULT 'open',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Projects that are open can be shared by their creators, as noted in the user_project table. This table associates all users that have access to a given project.

CREATE TABLE `user_project` (
  `user_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `role` set('owner','user') DEFAULT NULL,
  KEY `user_id` (`user_id`,`project_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Personal tools
NSF K-12