7.1 Database MySQL Config

All the database config should be located at config file dbs.php.

It looks like:

<?php
return array(
    /**
     * Database Servers
     */
    'servers' => array(
        'db_demo' => array(                         // server ID
            'host'      => 'localhost',             // database host
            'name'      => 'phalapi',               // database name
            'user'      => 'root',                  // database username
            'password'  => '',                      // database password
            'port'      => '3306',                  // database port
            'charset'   => 'UTF8',                  // database charset
        ),
    ),
    /**
     * Customing Table Routes
     */
    'tables' => array(
        // Common Defatult Routes
        '__default__' => array(
            'prefix' => 'tbl_',
            'key' => 'id',
            'map' => array(
                array('db' => 'db_demo'),
            ),
        ),
    ),
);

In above, all our database tables should start with prefix tbl_, and primary key id. Futhermore, all data store into database db_demo, which is MySQL with host localhost, name phalapi, user root, etc.

7.2 NotORM

We use NotORM to operate database in PhalApi. Whether you are familiar with NotORM or not, it's easy to learn.

Related: NotORM - PHP library for simple working with data in the database

(1) Register NotROM

It's simple to register NotORM into DI.

DI()->notorm = new PhalApi_DB_NotORM(DI()->config->get('dbs'), !empty($_GET['__sql__']));

We use the config above, and start database debug mode if $_GET['__sql__'] is not empty.

(2) Database Debug Mode

Show SQL

As we talk before, if $_GET['__sql__'] is not empty, it will print all the sql sentences that excuted in the request.

For example, if we request ?service=User.getBaseInfo with &__sql__=1.

http://demo.phalapi.net/?service=User.getBaseInfo&user_id=1&__sql__=1

It will show:

[1 - 0.00035s]SELECT * FROM tbl_user WHERE (id = ?); -- 1<br>
{"ret":200,"data":{"code":0,"msg":"","info":{"id":"1","name":"dogstar","note":"oschina"}},"msg":""}

Show Exception

Usually, when database fail to connect, we will get:

{
    "ret": 500,
    "data": [],
    "msg": "can not connect to database: db_demo"
}

It is safe to simplify exception message in production environment, but not helpful to find out what really going on in development environment. Instead we need to enable debug mode.

There are tow way to enable debug mode.

• 1. Add parameter &__debug__=1 in per request.
• 1. Set debug => true, in file ./Config/sys.php.

Then when exception again, we will see more details.

{
    "ret": 500,
    "data": [],
    "msg": "can not connect to database: db_demo, code: ：1045, cause: SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: NO)"
}

(3) How To Get NotORM

By now, we have already know how to configure database and register NotORM. What's next is to get NotORM so that we can do something interesting on database.

We can get a notorm instance in tow ways.

• Get NotORM by DI anywhere, but NOT recommend

  $user = DI()->notorm->user; 

• Get NotORM inside model, and STRONG recommend

  <?php
  class Model_User extends PhalApi_Model_NotORM {
  public function doSth() {
      $user = $this->getORM(); // inside model
  }
  }

NOTE: The subclass of PhalApi_Model_NotORM should be Model_{table_name_to_lower} at default. If you need a different table name, you can override method getTableName($id) and return custom table name.

<?php
class Model_User extends PhalApi_Model_NotORM {
protected function getTableName($id) {
return 'my_user'; 
}
}

We can get the instance for table tbl_user by $user = DI()->notorm->user; or $user = $this->getORM();.

Table tbl_user is just a simple table as beblow.

CREATE TABLE `tbl_user` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `note` varchar(45) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Keep NotORM Query Criteria Or Not

Instances of NotORM are mutable, so they can keep all the query criteria in the same instance. For example:

$user = DI()->notorm->user;
$user->where('age > ?', 18);  // age > 18
$user->where('name LIKE ?', '%dog%');  // age > 18 AND name LIKE '%dog%'

If we do not want to keep the state, we should create an new instance.

$user = DI()->notorm->user;
$user->where('age > ?', 18);

$user = DI()->notorm->user;
$user->where('name LIKE ?', '%dog%');  // name LIKE '%dog%'

Inside the model, $this->getORM(); will return an new instance each time.

7.3 Databse CURD

By far, we move forward a futher more step. We have an instance for table tbl_user now! Let's explore what we can do with it.

(0) Base CURD With Model Using Primary Key

$model = new Model_User();

// Retrieve
$row = $model->get(1);
$row = $model->get(1, 'id, name');
$row = $model->get(1, array('id', 'name'));

// Upgrade
$data = array('name' => 'test', 'update_time' => time());
$model->update(1, $data);

// Insert
$data = array('name' => 'phalapi');
$id = $model->insert($data);
//$id = $model->insert($data, 5);

// Delete
$model->delete(1);

(1) Database Creation

Insert Data

// INSERT INTO tbl_user (id, name, age, note) VALUES (4, 'PhalApi', 1, 'framework')
$data = array('id' => 4, 'name' => 'PhalApi', 'age' => 1, 'note' => 'framework');
$user->insert($data);
$id = $user->insert_id(); // must be the same instance
var_dump($id); // new ID

// inside Model
$model = new Model_User();
var_dump($model->insert($data)); // new ID

Insert Multi Data

// INSERT INTO tbl_user (name, age, note) VALUES ('A君', 12, 'AA'), ('B君', 14, 'BB'), ('C君', 16, 'CC')
$rows = array(
    array('name' => 'A君', 'age' => 12, 'note' => 'AA'),
    array('name' => 'B君', 'age' => 14, 'note' => 'BB'),
    array('name' => 'C君', 'age' => 16, 'note' => 'CC'),
);
$rs = $user->insert_multi($rows);
var_dump($rs);

// output
int(3) // the number of new entry

Insert/Upgrade

// INSERT INTO tbl_user (id, name, age, note) VALUES (8, 'PhalApi', 1, 'framework') ON DUPLICATE KEY UPDATE age = 2
$unique = array('id' => 8);
$insert = array('id' => 8, 'name' => 'PhalApi', 'age' => 1, 'note' => 'framework');
$update = array('age' => 2);
$rs = $user->insert_update($unique, $insert, $update);
var_dump($rs); // affect rows

(2) Database Upgrade

Upgrade

// UPDATE tbl_user SET age = 2 WHERE (name = 'PhalApi');
$data = array('age' => 2);
$rs = $user->where('name', 'PhalApi')->update($data);
var_dump($rs);

// output
int(1) // affect rows if success
int(0) // nothing affect if nothing change
boolean(false) // exception

Increment

// UPDATE tbl_user SET age = age + 1 WHERE (name = 'PhalApi')
$rs = $user->where('name', 'PhalApi')->update(array('age' => new NotORM_Literal("age + 1")));
var_dump($rs); // affect rows

(3) Database Retrieve

SELECT

Single table field:

// SELECT id FROM `tbl_user`
$user->select('id') 

Multi table fields:

// SELECT id, name, age FROM `tbl_user`
$user->select('id, name, age') 

Field alias:

// SELECT id, name, MAX(age) AS max_age FROM `tbl_user`
$user->select('id, name, MAX(age) AS max_age') 

All fileds:

// SELECT * FROM `tbl_user`
$user->select('*') 

WHERE

Single parameter:

// WHERE id = 1
$user->where('id', 1)
$user->where('id = ?', 1)
$user->where(array('id', 1))

Multi parameters:

// WHERE id > 1 AND age > 18
$user->where('id > ?', 1)->where('age > ?', 18)
$user->and('id > ?', 1)->and('age > ?', 18)
$user->where('id > ? AND age > ?', 1, 18)
$user->where(array('id > ?' => 1, 'age > ?' => 10))

// WHERE name = 'dogstar' AND age = 18
$user->where(array('name' => 'dogstar', 'age' => 18))

// WHERE name = 'dogstar' OR age = 18
$user->or('name', 'dogstar')->or('age', 18)

// WHERE ((name = ? OR id = ?)) AND (note = ?) -- 'dogstar', '1', 'xxx'
# use AND
$user->where('(name = ? OR id = ?)', 'dogstar', '1')->and('note = ?', 'xxx')
# or use WHERE without array 
$user->where('(name = ? OR id = ?) AND note = ?', 'dogstar', '1', 'xxx')
# or use WHERE with array
$user->where('(name = ? OR id = ?) AND note = ?', array('dogstar', '1', 'xxx'))
# or use WHERE with array
$user->where('(name = :name OR id = :id) AND note = :note', array(':name' => 'dogstar', ':id' => '1', ':note' => 'xxx'))

IN query:

// WHERE id IN (1, 2, 3)
$user->where('id', array(1, 2, 3))

// WHERE id NOT IN (1, 2, 3)
$user->where('NOT id', array(1, 2, 3))

// WHERE (id, age) IN ((1, 18), (2, 20))
$user->where('(id, age)', array(array(1, 18), array(2, 20)))

Like query:

// WHERE name LIKE '%dog%'
$user->where('name LIKE ?', '%dog%')

NULL query:

// WHERE (name IS NULL)
$user->where('name', null)

NOT NULL query:

// WHERE (name IS NOT ?) LIMIT 1; -- NULL
$user->where('name IS NOT ?', null)

ORDER BY

Sort by single field:

// ORDER BY age
$user->order('age')

// ORDER BY age DESC
$user->order('age DESC')

Sort by multi fileds:

// ORDER BY id, age DESC
$user->order('id')->order('age DESC')
$user->order('id, age DESC')

LIMIT

Limit amount:

// LIMIT 10
$user->limit(10)

Limit amount and offset:

// LIMIT 2,10
$user->limit(2, 10)

NOTE: offset first, then amount.

GROUP BY, HAVING

No HAVING:

// GROUP BY note
$user->group('note')

With HAVING:

// GROUP BY note HAVING age > 10
$user->group('note', 'age > 10')

(4) Database Deletion

Delete Some Records

// DELETE FROM tbl_user WHERE (id = 404);
$user->where('id', 404)->delete();

Delete All Records

// Exception: sorry, you can not delete the whole table
$user->delete();

NOTE: It's not allowed to delete all the records in table to proected our data.

(5) Database Transaction

Transaction in NotORM way, but NOT recommend:

// Step 1. get an instance
$user = DI()->notorm->user;

// Step 2. start transaction
DI()->notorm->transaction = 'BEGIN';

// Step 3. do something
$user->insert(array('name' => 'test1',));
$user->insert(array('name' => 'test2',));

// Step 4. commit/rollback
DI()->notorm->transaction = 'COMMIT';
//DI()->notorm->transaction = 'ROLLBACK';

Transaction in PhalApi way:

    public function testTransactionCommit()
    {
        // Step 1. start transaction
        DI()->notorm->beginTransaction('db_demo');

        // Step 2. do something
        DI()->notorm->user>insert(array('name' => 'test1'));
        DI()->notorm->user>insert(array('name' => 'test2'));

        // Step 3. commit/rollback
        DI()->notorm->commit('db_demo');
        //DI()->notorm->rollback('db_demo');
    }

7.4 Multi Tables/Database

We can use several different databases in the project. We even can use different databases for different tables. All what we need to do is just configure out database config, and PhalApi will handle it with magic.

For example, there are tow database servers: db_A, db_B, i.e:

<?php
return array(
    /**
     * Database Servers
     */
    'servers' => array(
        'db_A' => array(                              // db_A
            'host'      => '192.168.0.1',             // database host
            // ... ...
        ),
        'db_B' => array(                              // db_B
            'host'      => '192.168.0.2',             // database host
            // ... ...
        ),
    ),

    //... ...

And tables a_table_user and a_table_friends are stored intto db_A, while tables b_table_article and b_table_comments are stored intto db_B, i.e:

<?php
return array(

    //... ...

    /**
     * Customing Table Routes
     */
    'tables' => array(
        // Common Defatult Routes
        '__default__' => array(
            'prefix' => 'a_',
            'key' => 'id',
            'map' => array(
                array('db' => 'db_A'),  // use db_A default
            ),
        ),

        'table_article' => array(                                     // b_table_article
            'prefix' => 'b_',                                         
            'key' => 'id',                                            
            'map' => array(                                           
                array('db' => 'db_B'),                                // b_table_article use db_B
            ),
        ),

        'table_comments' => array(                                    // b_table_article
            'prefix' => 'b_',                                         
            'key' => 'id',                                            
            'map' => array(                                           
                array('db' => 'db_B'),                                // b_table_comments use db_B
            ),
        ),
    ),

7.6 Build SQL

Table structure without primary key should locate at folder ./Data.

Assume we have save the follow SQL into ./Data/demo.sql:

      `name` VARCHAR(45) NULL,

If we configure multi tables for user in config file:

    'tables' => array(
        '__default__' => array(
            'prefix' => 'tbl_',
            'key' => 'id',
            'map' => array(
                array('db' => 'db_demo'),
            ),
        ),
        'demo' => array(
            'prefix' => 'tbl_',
            'key' => 'id',
            'map' => array(
                array('db' => 'db_demo'),
                array('start' => 0, 'end' => 2, 'db' => 'db_demo'),
            ),
        ),
    ),

Then we can run:

$ php ./PhalApi/phalapi-buildsqls ./Config/dbs.php user_session

It will print:

/**
 * DB: localhost  db_demo
 */

CREATE TABLE `demo` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(11) DEFAULT NULL,
    `ext_data` text COMMENT 'json data here',
     PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/**
 * DB: localhost  db_demo
 */

CREATE TABLE `tpl_demo_0` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(11) DEFAULT NULL,
    `ext_data` text COMMENT 'json data here',
     PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tpl_demo_1` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(11) DEFAULT NULL,
    `ext_data` text COMMENT 'json data here',
     PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tpl_demo_2` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(11) DEFAULT NULL,
    `ext_data` text COMMENT 'json data here',
     PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7.7 Extends Your DB With Other Database

We can also connect to Oracle, postgreSQL, MS SQL, SQLite, etc.

Firstly, we need to override method PhalApi_DB_NotORM::createPDOBy() in our subclass.

<?php
class Common_MyDB extends PhalApi_DB_NotORM {

    protected function createPDOBy($dbCfg) {
        /* Connect to an ODBC database using driver invocation */
    $dsn = 'uri:file:///usr/local/dbconnect';
    return new PDO($dsn, $dbCfg['user'], $dbCfg['password']);
    }
}

Secondly, re-register DI()->notorm in file ./Public/init.php.

DI()->notorm = function() {
    $debug = !empty($_GET['__sql__']) ? true : false;
    return new Common_MyDB(DI()->config->get('dbs'), $debug);
};