Friday, December 4, 2009

Prepare a SQL statement


In this example, we prepare and execute SQL statements.

Components used in this example
Preparing and executing a SQL statement
  • We define the set of the available fetching modes.
  • We define the set of the available cursor modes.

class MyDBStatement
{
    
// We define the set of the available fetching modes.
    
private $_fetchModes = array(
        
null => null,
        
'FETCH_ASSOC' => Zend_Db::FETCH_ASSOC,
        
'FETCH_NUM' => Zend_Db::FETCH_NUM,
        
'FETCH_BOTH' => Zend_Db::FETCH_BOTH,
        
'FETCH_COLUMN' => Zend_Db::FETCH_COLUMN,
        
'FETCH_OBJ' => Zend_Db::FETCH_OBJ,
    );

    
// We define the set of the available cursor modes.
    
private $_cursor = array(
        
null => null,
        
'FETCH_ORI_ABS' => Zend_Db::FETCH_ORI_ABS,
        
'FETCH_ORI_FIRST' => Zend_Db::FETCH_ORI_FIRST,
        
'FETCH_ORI_LAST' => Zend_Db::FETCH_ORI_LAST,
        
'FETCH_ORI_NEXT' => Zend_Db::FETCH_ORI_NEXT,
        
'FETCH_ORI_PRIOR' => Zend_Db::FETCH_ORI_PRIOR,
        
'FETCH_ORI_REL' => Zend_Db::FETCH_ORI_REL,
    );
Processing a query
  • We get the method, the row details, and the fetching parameters from the GET request.
  • We create the database in memory. There is one table with 10 entries.
  • We get the possible values of each column.
  • We fetch the selected rows.
  • If we catch an exception, we return the error message.

    public function process()
    {
        
// We get the method, the row details, and the fetching parameters from the GET request.
        
list($id$description$status$reportedBy$createdOn,
             
$method$mode$cursor$offset$skip) = $this->_getParameters();

        
$descriptionList $statusList $reportedByList $createdOnList = array();

        try {
            
// We create the database in memory. There is one table with 10 entries.
            
$db $this->_create();

            
// We get the possible values of each column.
            
list($idList$descriptionList$statusList$reportedByList$createdOnList) =
                 
$this->_fetchColumns($db);

            
// We fetch the selected rows.
            
$result $this->_fetchRows($db,
                
$id$description$status$reportedBy$createdOn,
                
$method$mode$cursor$offset$skip);

        } catch (
Exception $e) {
            
// If we catch an exception, we return the error message.
            
$result $e->getMessage();
        }

        return array(
            
$id$description$status$reportedBy$createdOn,
            
$method$mode$cursor$offset$skip,
            
$idList$descriptionList$statusList$reportedByList$createdOnList,
            
$result);
    }
Extraction of the parameters from the GET request

    private function _getParameters()
    {
        
$id = isset($_GET['id'])? $_GET['id'] : null;
        
$description = isset($_GET['description'])? $_GET['description'] : null;
        
$status = isset($_GET['status'])? $_GET['status'] : null;
        
$reportedBy = isset($_GET['reported_by'])? $_GET['reported_by'] : null;
        
$createdOn = isset($_GET['created_on'])? $_GET['created_on'] : null;

        
$method = isset($_GET['method'])? $_GET['method'] : null;
        
$mode = isset($_GET['mode'])? $_GET['mode'] : null;
        
$cursor = isset($_GET['cursor'])? $_GET['cursor'] : null;
        
$offset = empty($_GET['offset'])? $_GET['offset'];

        
$skip = empty($_GET['skip'])? $_GET['skip'];
        
$skip >= and $skip 100 or $skip 0;

        return array(
$id$description$status$reportedBy$createdOn,
            
$method$mode$cursor$offset$skip);
    }
Creation of the database
  • We create the table named bug.
  • We add 10 rows to the table.
  • We return the database connection.

    private function _create()
    {
        
$db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));

        
// We create the table named bug.
        
$query 'CREATE TABLE bugs ('
            
'id INTEGER NOT NULL PRIMARY KEY, '
            
'description TEXT NOT NULL UNIQUE, '
            
'status TEXT DEFAULT "flying", '
            
'reported_by TEXT DEFAULT "unknown", '
            
'created_on TEXT DEFAULT CURRENT_DATE)';
        
$db->query($query);

        
// We add 10 rows to the table.
        
$sql "INSERT INTO bugs (description, status, reported_by, created_on) VALUES (?, ?, ?, ?)";
        
$stmt = new Zend_Db_Statement_Pdo($db$sql);
        
$stmt->execute(array('fly''flying''john''2009'));
        
$stmt->execute(array('bee''home''jane''2008'));
        
$stmt->execute(array('mosquito''dead''john''2007'));
        
$stmt->execute(array('ant''crawling''jane''2006'));
        
$stmt->execute(array('spider''crawling''jane''2009'));
        
$stmt->execute(array('wasps''home''john''2008'));
        
$stmt->execute(array('mite''dead''jane''2007'));
        
$stmt->execute(array('beetle''flying''john''2006'));
        
$stmt->execute(array('earwig''crawling''jane''2009'));
        
$stmt->execute(array('tick''home''john''2008'));

        
// We return the database connection.
        
return $db;
    }
Fetching all columns

    private function _fetchColumns($db)
    {
        
$idList $this->_fetchColumn($db'id');
        
$descriptionList $this->_fetchColumn($db'description');
        
$statusList $this->_fetchColumn($db'status');
        
$reportedByList $this->_fetchColumn($db'reported_by');
        
$createdOnList $this->_fetchColumn($db'created_on');

        return array(
$idList$descriptionList$statusList$reportedByList$createdOnList);
    }
Fetching one column

    private function _fetchColumn($db$column)
    {
        
$stmt $db->query("SELECT DISTINCT $column FROM bugs ORDER BY $column");

        return 
$stmt->fetchAll(Zend_Db::FETCH_COLUMN0);
    }
Fetching the table rows
  • We build and we run the query.
  • We would normally set the scrollable cursor at this point. But setting attributes with SQLite triggers an exception.
  • If the user requested to fetch only one table row, we attempts to fetch the rows that meet the criteria and we return the first row. If the user requested to skip a given number of rows, we return the row following those rows.
  • We use a custom fetch method because the scrollable cursor is not available.
  • If the user requested to fetch all the rows, we fetch the rows that meet the criteria and we return them.

    private function _fetchRows($db,
        
$id$description$status$reportedBy$createdOn,
        
$method$mode$cursor$offset$skip)
    {
        
// We build and we run the query.
        
$sql 'SELECT * FROM bugs';
        list(
$where$bind) =
            
$this->_setWhere($id$description$status$reportedBy$createdOn);
        empty(
$where) or $sql .= ' WHERE ' implode(' AND '$where);
        
$stmt $db->query($sql$bind);

        
// We would normally set the scrollable cursor at this point.
        // But setting attributes with SQLite triggers an exception.
        // $stmt->setAttribute(Zend_Db::ATTR_CURSOR, Zend_Db::CURSOR_SCROLL);

        
$fetchMode $this->_fetchModes[$mode];

        if (
$method == 'fetch') {
            
// If the user requested to fetch only one table row, we attempts to fetch the rows
            // that meet the criteria and we return the first row.
            // If the user requested to skip a given number of rows,
            // we return the row following those rows.
            
while($skip--) {
                
// We use a custom fetch method because the scrollable cursor is not available.
                // $stmt->fetch();
                
$this->_myfetch($stmt$fetchMode);
            }
            
// $rows = $stmt->fetch($fetchMode, $this->_cursor[$cursor], $offset);
            
$rows $this->_myfetch($stmt$fetchMode$this->_cursor[$cursor], $offset);
        } else {
            
// If the user requested to fetch all the rows,
            // we fetch the rows that meet the criteria and we return them.
            
$rows $stmt->fetchAll($fetchMode);
        }

        return 
$rows;
    }
Building the where clause to fetch data

    private function _setWhere($id$description$status$reportedBy$createdOn)
    {
        
$where = array();
        
$bind = array();

        if (
$id) {
         
$where[]= 'id = :id';
         
$bind[':id'] = $id;
        }
        if (
$description) {
         
$where[]= 'description = :description';
         
$bind[':description'] = $description;
        }
        if (
$status) {
            
$where[]= 'status = :status';
            
$bind[':status'] = $status;
        }
        if (
$reportedBy) {
            
$where[]= 'reported_by = :reported_by';
            
$bind[':reported_by'] = $reportedBy;
        }
        if (
$createdOn) {
            
$where[]= 'created_on = :created_on';
            
$bind[':created_on'] = $createdOn;
        }

        return array(
$where$bind);
    }
Implementing the fetch method with a scrolling cursor
  • We fetch all the table rows on the first call.
  • We fetch a table row based on its absolute position.
  • Or we fetch the first row of the table.
  • Or we fetch the last row pf the table.
  • Or we fetch the previous row.
  • Or we fetch the table row relative to the current position.
  • Or we fetch the next row.

    private function _myfetch($stmt$mode null$cursor null$offset 0)
    {
        
// We fetch all the table rows on the first call.
        
static $rows;
        empty(
$rows) and $rows $stmt->fetchAll($mode);

        if (empty(
$rows)) {
            return array();
        }

        switch(
$cursor) {
            case 
Zend_Db::FETCH_ORI_ABS:
                
// We fetch a table row based on its absolute position.
                
reset($rows);
                
$row $this->_myfetch($stmt$modeZend_Db::FETCH_ORI_REL$offset);
                break;

            case 
Zend_Db::FETCH_ORI_FIRST:
                
// Or we fetch the first row of the table.
                
$row reset($rows);
                break;

            case 
Zend_Db::FETCH_ORI_LAST:
                
// Or we fetch the last row pf the table.
                
$row end($rows);
                break;

            case 
Zend_Db::FETCH_ORI_PRIOR:
                
// Or we fetch the previous row.
                
$row prev($rows) or $this->_exception();
                break;

            case 
Zend_Db::FETCH_ORI_REL:
                
// Or we fetch the table row relative to the current position.
                
if ($offset 0) {
                    while(
$offset--) {
                        
$row next($rows) or $this->_exception();
                    }
                } else if (
$offset 0) {
                    
$offset = -$offset;
                    while(
$offset--) {
                        
$row prev($rows) or $this->_exception();
                    }
                } else {
                    
$row current($rows) or $this->_exception();
                }
                break;

            case 
Zend_Db::FETCH_ORI_NEXT:
            default:
                
// Or we fetch the next row.
                
$row current($rows) or $this->_exception();
                
next($rows);
        }

        return 
$row;
    }
Triggering an exception
  • We return an exception if the cursor is out of bound. We use the same error message identified in the source file: php/ext/pdo/pdo_sqlstate.c.

    private function _exception()
    {
        
// We return an exception if the cursor is out of bound.
        // We use the same error message identified in the source file:
        // php/ext/pdo/pdo_sqlstate.c.
        
throw new Zend_Db_Statement_Exception('SQLSTATE[HY109]: Invalid cursor position');
    }

}

No comments:

Post a Comment