ED php framework :: Tutorial #8
Database configuration
First let's see how you should configure your database layer. This is done in the appconfig.inc file.

snippet/database/
appconfig.inc
 
    $dbRef = TPostgreSql::create("host=localhost dbname=mydb user=user1 password=pass1");
    //$dbRef = TOracle::create("192.168.0.18/orcl", "user1", "pass1", "mydb");
    //$dbRef = TMySql::create("localhost", "mydb", "user1", "pass1");
    $dbRef->setAutoConnect(TRUE);
    $dbRef->setExceptionMode(TRUE);
    //$dbRef->setDebugMode(TRUE);
    //$dbRef->setPersistent(TRUE);
Now let's see how you should use the instantiated database object.
snippet/database/
example.php.script
       // On click button event
       public function btnQueryDatabase_OnClick() {
           $db = TApplication::getInstance()->getDatabase();
           $db = db(); // Do the same as the above line. Just a shortcut.
 
           $db->insert(...
           $db->update(...
           $db->delete(...
           $db->select(...
           $db->call(...
       }


$dbRef->setAutoConnect(TRUE);
If auto connect is TRUE then you go and directly call insert, update, etc. The connection to the database is created automatically by the framework. If the value is FALSE then you have to open and close the connection manually.
    $db = db();
    $db->connect();
    $db->insert(...;
    $db->free();
We recommend the first choice.



$dbRef->setExceptionMode(TRUE);
If exception mode is TRUE then on failed operation the method will throw TDatabaseException.
    $db = db();
    $db->insert("tbl_doesn't_exist", ...; // throws TDatabaseException
    $db->update("tbl_exists", ...; // this line is never reached
If exception mode is FALSE then all failed operations will add their error codes in an internal list of errors and after each operation you have to check for errors.
    $db = db();
    $db->insert("tbl_doesn't_exist", ...;
    $db->update("tbl_exists", ...;

    if ($db->hasErrors()) {
        $this->addError("The operation has failed. See error.log");
        return;
    }




$dbRef->setDebugMode(TRUE);
If debug mode is TRUE then all operations like insert, update, etc. will log their sql statements in "protected/log/system.log". For production environments this must be always FALSE.



$dbRef->setPersistent(TRUE);
If persistent is TRUE then the connection to the database is done through mysql_pconnect, oci_pconnect or pg_pconnect instead of mysql_connect, oci_new_connect or pg_connect. Basically it uses a database pool and is much faster. For more details check the PHP documentation.


Database "INSERT" operation
    $db = db();

    $personID = $db->nextSequence("seq_default");

    $data = TSqlParams::create();
    $data->set_integer("person_id", $personID);
    $data->set_string("person_fname", "Joe");
    $data->set_string("person_lname", "O'Con\\nel\\");
    $data->set_string("country_id", "uk");
    $data->set_date("birthday", "1974-12-30");
    $data->set_number("salary", "6500.56");
    $data->set_boolean("single", TRUE);
    $data->set_blob("photo", file_get_contents("jpg_photo.jpg"));
    $data->set_timestamp("last_updated", "2010-01-30 23:30");

    $db->insert("tbl_temp", $data);


Database "UPDATE" operation
    $db = db();

    $data = TSqlParams::create();
    $data->set_string("person_fname", "Joe");
    $data->set_string("person_lname", "Myers");
    $data->set_string("country_id", "de");
    $data->set_date("birthday", "1910-11-13");
    $data->set_number("salary", "9250.00");
    $data->set_boolean("single", FALSE);
    $data->set_blob("photo", file_get_contents("png_photo.png"));
    $data->set_timestamp("last_updated", NULL);

    $filter = TSqlParams::create();
    $filter->set_integer("person_id", 234234);

    $affected = $db->update("tbl_temp", $data, $filter);


Database "DELETE" operation
    $db = db();

    $filter = TSqlParams::create();
    $filter->set_integer("person_id", 242343);
    $filter->set_date("birthday", "1910-11-13");
    $filter->set_number("salary", "9250.00");

    $affected = $db->delete("tbl_temp", $filter);


Database "EXECUTE" operation
    $db = db();

    $sql = TSqlStatement::sql("update tbl_temp set person_lname=[:last] where person_id=[:id]");
    $sql->bindString(":last", "Myers");
    $sql->bindInteger(":id", 24234);
    $affected = $db->execute($sql);


Call "stored procedure"
    $db = db();

    $params = TSqlParams::create();
    $params->set_string(0, "The result is: ");
    $params->set_integer(1, 8);
    $params->set_integer(2, 4);
    $params->set_timestamp(3, "2003-11-18 23:30:00");
    $funcReturnValue = $db->call("multiply", $params);
    // The result is: 32, called on 2003-11-18 23:30:00
    # MYSQL function multiply
    DELIMITER $$
    CREATE FUNCTION multiply(aLabel VARCHAR(32), aX INT, aY INT, aTimestamp TIMESTAMP) RETURNS VARCHAR(64) DETERMINISTIC
    BEGIN
       RETURN concat(aLabel, aX*aY, ', called on ', aTimestamp);
    END $$
    DELIMITER ;
    
    # POSTGRESQL function multiply
    CREATE or REPLACE FUNCTION multiply(text, int4, int4, timestamp) RETURNS text AS $$
    DECLARE
        label              alias for $1;
        x                  alias for $2;
        y                  alias for $3;
        aTimestamp         alias for $4;
    BEGIN
        return label || x*y || ', called on ' || aTimestamp;
    END;
    $$ LANGUAGE plpgsql;



Database "TRANSACTION" operation
    $db = db();

    $$db->startTransaction();
    $db->insert(...   ;
    $db->update(...   ;
    $db->endTransaction();

    if ($db->hasErrors()) {
        $this->addError("The operation has failed. See error.log");
        return;
    }
If exception mode is FALSE you have to check for errors. But if it is TRUE and let's say the update throws exception the framework will automatically rollback the transaction.


Database "SELECT" operation
In order not to mess our code with sql statements it's very convinient to store them in an external file. All these external files should be located under the "protected/sql" directory. We will create a file called person.sql with the following content.
    SQL_PERSONS_LIST=
      select p.person_id, p.person_fname, p.person_lname, p.birthday,
             p.country_id, p.single, c.name
      from persons p, countries c
      where p.country_id = c.country_id
            and (p.person_fname = [:first] or p.person_lname = [:last])
    ;
    
    
    SQL_PERSONS_LIST_CRITERIA=
      select p.person_id, p.person_fname, p.person_lname, p.birthday,
             p.country_id, p.single, p.salary, p.last_updated, c.name as country_name
      from persons p, countries c
      where p.country_id = c.country_id [criteria]
      order by [orderString]
    ;
    $db = db();
    $sql = TSqlStatement::sql("person.sql", "SQL_PERSONS_LIST");
    $sql->bindString(":first", "Joe");
    $sql->bindString(":last", "Myers");
    $dataSource = $db->select($sql);


Criteria API
The ED Framework provides powerful and easy functionality to create queries with complex "where clause", so it can vary depending on the value of the data we pass. Imagine a "choose your search criteria" screen with 20 fields, where the user chooses randomly only 5 of them. Building the final query could be a nightmare using complex conditional logic. The framework provides elegant way to handle such scenarios.
    $sql = TSqlStatement::sql("person.sql", "SQL_PERSONS_LIST_CRITERIA");

    $fieldPlayerId      = TSqlCriteria::field('p.person_id', 'number')
                                ->equals($this->ftPersonId->getValue());

    $fieldFirstName     = TSqlCriteria::field('p.person_fname', 'string')
                                ->contains($this->ftFirstName->getValue());

    $fieldLastName      = TSqlCriteria::field('p.person_lname', 'string')
                                ->contains($this->ftLastName->getValue());

    $fieldCountry       = TSqlCriteria::field('p.country_id', 'array')
                                ->in(array_keys($this->ftCountry->selectedItems()));

    $fieldSalaryFrom    = TSqlCriteria::field('p.salary', 'number')
                                ->greaterEqual($this->ftSalaryFrom->getValue());

    $fieldSalaryTo      = TSqlCriteria::field('p.salary', 'number')
                                ->lessEqual($this->ftSalaryTo->getValue());

    $fieldBornAfter     = TSqlCriteria::field('p.birthday', 'date')
                                ->greaterEqual($this->ftBornAfter->getValue());

    $fieldBornBefore    = TSqlCriteria::field('p.birthday', 'date')
                                ->lessEqual($this->ftBornBefore->getValue());

    $fieldLUAfter       = TSqlCriteria::field('p.last_updated', 'timestamp')
                                ->greaterEqual($this->ftLastUpdatedAfter->getValue());

    $fieldLUBefore      = TSqlCriteria::field('p.last_updated', 'timestamp')
                                ->lessEqual($this->ftLastUpdatedBefore->getValue());

    $fieldSingle        = TSqlCriteria::field('p.single', 'bool')
                                ->equals($this->ftBoolItems->getItemValue("single"));


    $group = TSqlCriteria::group('and');
    $group->add($fieldPlayerId);
    $group->add($fieldFirstName);
    $group->add($fieldLastName);
    $group->add($fieldCountry);
    $group->add($fieldSalaryFrom)->add($fieldSalaryTo);
    $group->add($fieldBornAfter)->add($fieldBornBefore);
    $group->add($fieldLUAfter)->add($fieldLUBefore);
    $group->add($fieldSingle);

    $group2 = TSqlCriteria::group('or');
    $group2->add($fieldFirstName);
    $group2->add($fieldLastName);

    $criteria = TSqlCriteria::create();
    $criteria->setFirstGroup($group);
    $criteria->addGroup('or', $group2);

    $strCriteria = $criteria->toSql();
    $sql->replace("[criteria]", $strCriteria ? "and " . $strCriteria : "");
The final sql will look something like this:
select 
  p.person_id, p.person_fname, p.person_lname, p.birthday, p.country_id, 
  p.single, p.salary, p.last_updated, c.name as country_name
from
  persons p, countries c
where 
  p.country_id = c.country_id 
  and ((p.person_fname like '%Joe%' and p.person_lname like '%Myers%' 
        and p.country_id in ('ar','de','uk') and p.birthday >= '1945-01-10' 
        and p.single = true) 
  or (p.person_fname like '%Joe%' or p.person_lname like '%Myers%'))


MySql Sequence(s)
PostgreSQL and Oracle provide us with built-in sequence mechanism. First we create a sequence and then use it through:
    $db->nextSequence("seqName");
MySQL doesn't have this, so in order to use the above function we create 2 extra database objects:
    # table tbl_sequences
    CREATE TABLE tbl_sequences (
        name VARCHAR(32),
        last_value INT UNSIGNED NOT NULL,
    
        PRIMARY KEY(name),
        CONSTRAINT uq_name UNIQUE (name)
    
    ) TYPE=innodb;
    
    
    
    
    # function nextSequence
    DELIMITER $$
    CREATE FUNCTION nextSequence(seqName VARCHAR(32)) RETURNS INT UNSIGNED MODIFIES SQL DATA DETERMINISTIC
    BEGIN
       DECLARE affected, val, lockVal INT UNSIGNED;
    
       SELECT GET_LOCK(seqName, 10) INTO lockVal;
    
       update tbl_sequences set last_value=last_value+1 where name = seqName;
       SET affected = row_count();
       IF affected = 0 THEN
          insert into tbl_sequences(name, last_value) values(seqName, 1000);
       END IF;
       SELECT last_value INTO val FROM tbl_sequences WHERE name=seqName limit 1; 
    
       SELECT RELEASE_LOCK(seqName) INTO lockVal;
    
       RETURN val;
    END $$
    DELIMITER ;