Phalcon 框架 笔记(2)

6、操作数据库

1、DAL，数据库抽象层。 Phalcon\Db

1. Adapter

Adaptor是通过pdo来实现的，主要是 mysql, postgresql和sqlite

加载Adapter的时候呢，我们通过 Phalcon\Db\Adapter\Pdo\Factory 来load:

use Phalcon\Db\Adapter\Pdo\Factory;
$options = [
    'host' => 'localhost',
    'dbname' => 'testdb',
    'port' => 3306,
    'username'=> 'hefish',
    'password' => '123456',
    'adapter' => 'mysql',
];
$db = Factory::load($options);

2. 连接数据库

两种方法，一种通过配置文件，直接new连接，另一种，通过Factory来new。

第一种：

$config = [
    'host'     => '127.0.0.1',
    'username' => 'mike',
    'password' => 'sigma',
    'dbname'   => 'test_db',
    'options'  => [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
            PDO::ATTR_CASE               => PDO::CASE_LOWER,
        ], 
];

// Optional
$config['persistent'] = false;

// Create a connection
$connection = new \Phalcon\Db\Adapter\Pdo\Mysql($config);

第二种：在 services.php里面：

$di->set(
    'db',
    function () {
        return Factory::load($this->config->database);
    }
);

3、操作

有了数据库连接，那就玩呗：

$rs = $conn -> query($sql);
$result->setFetchMode(Phalcon\Db::FETCH_ASSOC);
while( $row = $rs->fetch() ) {
...
}

$rs = $conn -> fetchAll($sql);
foreach( $rs as $row) {
...
}

$row = $conn -> fetchOne($sql);

也可以用类似prepare的方法来操作query，这样安全有保障啊：

$sql    = 'SELECT * FROM robots WHERE name = ? ORDER BY name';
$result = $connection->query(
    $sql,
    [
        'Wall-E',
    ]
);

// Binding with named placeholders
$sql     = 'INSERT INTO `robots`(name`, year) VALUES (:name, :year)';
$success = $connection->query(
    $sql,
    [
        'name' => 'Astro Boy',
        'year' => 1952,
    ]
);

当然还可以用$conn -> execute(), insert()， insertAsDict(), update()， updateAsDict(), delete() 等操作，具体参见文档

事务操作呢？

自然也是可以的：

try {
    // Start a transaction
    $connection->begin();
    // Execute some SQL statements
    $connection->execute('DELETE `robots` WHERE `id` = 101');
    $connection->execute('DELETE `robots` WHERE `id` = 102');
    $connection->execute('DELETE `robots` WHERE `id` = 103');
    // Commit if everything goes well
    $connection->commit();
} catch (Exception $e) {
    // An exception has occurred rollback the transaction
    $connection->rollback();
}

我想给数据库操作做个日志……好的，没问题：

这里要用到一个EventManager，对于数据库操作，相应的event有：afterConnect, beforeQuery, afterQuery, beforeDisconnect, beginTransaction, rollbackTransaction, commitTransaction。参考下面的例子：

use Phalcon\Logger;
use Phalcon\Events\Event;
use Phalcon\Events\Manager as EventsManager;
use Phalcon\Logger\Adapter\File as FileLogger;
$eventsManager = new EventsManager();
$logger = new FileLogger('app/logs/db.log');
$eventsManager->attach(
    'db:beforeQuery',
    function (Event $event, $connection) use ($logger) {
        $sql = $connection->getSQLStatement();
        $logger->log($sql, Logger::INFO);
    }
);

// Assign the eventsManager to the db adapter instance
$connection->setEventsManager($eventsManager);
// Execute some SQL statement
$connection->insert(
    'products',
    [
        'Hot pepper',
        3.50,
    ],
    [
        'name',
        'price',
    ]
);

2、Model， 这个就是类似ORM的操作了。步骤大致如下：

首先，继承自Phalcon\Mvc\Model类：

namespace App\Model;
use Phalcon\Mvc\Model;
class Member extends Model {
}

然后将类，绑定到某个表，用方法 setSource()来完成。

namespace App\Model;
use Phalcon\Mvc\Model;

class Member extends Model {
    public function initialize() {
        $this->setSource("members");
    }
}

接下来就操作吧：

$members = Member::find(); //返回所有member
$member = Member::find(
    [
        "id > 0",
        'order' =>'last_login DESC, name ASC ' ,
        'limit' => 10,
    ]);  //返回name=admin的member

具体，参见：https://docs.phalconphp.com/en/3.2/db-models 有更详细解释，还能prepare的。