Skip to content

Dev.Pi\Db\DbGateway

Jump to bottom Edit New page
linzongshu edited this page Oct 17, 2015 · 15 revisions

How to get this instance via Service

You can access its instance by:

// Use connection config in service.database.php
$dbGetway = Pi::db();
$dbGetway = Pi::service('database')->loadDb();


// Use custom connection config
$options = [
    'connection' => [
        'driver'    => 'pdo',
        'dsn'       => 'mysql:host=localhost;dbname=test',
        'username'  => 'test',
        'password'  => 'test',
    ],
    'schema'     => ...,
    ...
];
$dbGetway = Pi::db($options);
// Or
$dbGetway = Pi::service('database')->loadDb($options);

APIs

getAdapter

getAdapter($type = null)

Get Db connection adapter instance.

Parameters

  • type

Get master or slave database connection adapter when type value is master or slave, if type is given. Or else the database will both be master and slave and its connection adapter instance will be returned.

Return

Return value is a Pi\Db\Adapter\Adapter instance.

Examples

$dbAdapter = Pi::db()->getAdapter('slave');
$dbAdapter = Pi::db()->getAdapter();

adapter

adapter($type = null)

As same as getAdapter(), please refer to this API.

setTablePrefix

setTablePrefix($prefix)

Set table prefix. This string will prepend before table name, this is a common process in Pi, if your custom table donnot contain prefix, just ignore this method.

Note: use Pi::db()->setTablePrefix() to change table prefix will occur system error.

Parameters

  • prefix

Table prefix string, such as pi_, x30_.

Return

Gateway instance.

Examples

Pi::db()->setTablePrefix('pi_');

getTablePrefix

getTablePrefix()

Get table prefix already set.

Return

Prefix string.

Examples

$tablePrefix = Pi::db()->getTablePrefix();

setCorePrefix

setCorePrefix($prefix)

Pi engine has module table and system table, with table name format as

and
core
respectively, and the core_ string is the default core prefix, it just affect the system tables.

Note: use Pi::db()->setCorePrefix() to change core prefix will occur system error.

Parameters

  • prefix

Core prefix string, such as core_, demo_.

Return

Gateway instance.

Examples

$options = [
    'connection' => [...],
];
$dbGateway = Pi::db($options)->setTablePrefix('')->setCorePrefix('demo_');
$table     = $dbGateway->model('test')->getTable();

Result is:

demo_test

getCorePrefix

getCorePrefix()

Get core prefix already set.

Return

Prefix string.

Examples

$corePrefix = Pi::db()->getCorePrefix();

Result is:

core_

setSchema

setSchema($schema)

Set database schema.

Note: use Pi::db()->setSchema() to change schema will occur system error.

Parameters

  • schema

Database schema name.

Return

Gateway instance.

Examples

$options = [
    'connection' => [...],
];
$dbGateway = Pi::db($options)->setSchema('demo');
$model     = $dbGateway->model('test');

getSchema

getSchema()

Get database schema.

Return

Schema name.

Examples

$schema = Pi::db()->getSchema();

createAdapter

createAdapter(array $config, $platform = null)

Create a db adapter instance, as well as add Pi DbProfiler to it for sql execution analyzing, and then the adapter can be used to operate database.

Parameters

  • config

Storage connect configuration.

  • platform

Storage platform, such as Mysql, SqlServer, Oracle, Sqlite, Postgresql, IbmDb2.

Return

Adapter instance inherit from Zend\Db\Adapter\AdapterInterface, which is Zend\Db\Adapter\Adapter.

Examples

$options = [
    'connection' => [
        'driver'    => 'pdo',
        'dsn'       => 'mysql:host=localhost;dbname=test',
        'username'  => 'test',
        'password'  => 'test',
    ],
    'schema'     => ...,
    ...
];
$adapter = Pi::db()->createAdapter($options);
$adapter = Pi::db()->createAdapter($options, 'mysql');

setAdapter

setAdapter(Adapter $adapter, $type = null)

Set database adapter.

Parameters

adapter

Zend\Db\Adapter\AdapterInterface instance.

type

Master or slave adapter, its value is master or slave.

Return

Gateway instance.

Examples

$options = [
    'connection' => [
        'hostname' => '192.168.0.1',
        ...
    ]
];   
$adapter = new \Zend\Db\Adapter\Adapter($options);
Pi::db()->setAdapter($adapter, 'master');
$options['connection']['hostname'] = '192.168.0.2';
Pi::db()->setAdapter($adapter, 'slave');

// Set adapter for only on database
Pi::db()->setAdapter($adapter);

getAdapter

getAdapter($type = null)

Get database adapter already initialized.

Parameters

type

Get a master or slave adapter.

Return

Adapter instance.

Examples

$adapter = Pi::db()->getAdapter();

adapter

adapter($type = null)

As same as getAdapter(), for more information about how adapter uses, please refer Db Adapter.

prefix

prefix($table = '', $type = '')

Get table name according to Pi rule.

Parameters

table

Table name.

type

core value indicated getting a system table, or module name indicated getting a module table.

Return

Table full name.

Examples

$table = Pi::db()->prefix('test', 'demo');
$table = Pi::db()->prefix('test');

Result will be as follows if table prefix is pi_:

pi_demo_test
pi_core_test

model

model($name, $options = array())

Get database model instance.

Parameters

name

Name for initializing class name.

  • /
: class 'Module<module name>\Model<table name>' will be initialized if it exists.
    • : class 'Pi\Application\Model\Model' will be used as table instance if `type` field of `$options` is not set.

    options

    Optional data, fields can be:

    • : set table prefix.
    • : table name.
    • : adapter instance.
    • : use for generating class name, for example, testOne will be map to class 'Pi\Application\Model\Test\One'.

    Return

    Model instance.

    Examples

    // Instance of core test table
$model = Pi::db()->model('test');
// Instance of test table of demo module
$model = Pi::db()->model('demo/test');
// Instance of user account instance
$model = Pi::db()->model('test', ['type' => 'User\\Account']);

    metadata

    metadata()

    Get Zend\Db\Metadata\Metadata instance, metadata class is used to cache databases and tables details, for more information about metadata, please refer Zend Metadata.

    Return

    Zend\Db\Metadata\Metadata instance.

    Examples

    // Get all schemas of the mysql server
$schemas = Pi::db()->metadata()->getSchemas();
// Get all table names of demo schema
$tableNames = Pi::db()->metadata()->getTableNames('demo');

    where

    where($predicate = null)

    Create a where object, it is will be very useful to assemble complex sql.

    Parameters

    predicate

    Where condition.

    Return

    Pi\Db\Sql\Where instance.

    Examples

    // SELECT * FROM `core_config` WHERE `category` = 'general' AND `module` = 'system' OR (`category` = 'user' AND `name` LIKE 'uname_%')
$whereObj1 = Pi::db()->where("`category` = 'user' and `name` like 'uname_%'");
$whereObj2 = Pi::db()->where(['category' => 'general', 'module' => 'system']);
$whereObj2->orPredicate($whereObj1);
$rowset = Pi::model('config')->select($whereObj2);

    expression

    expression(
    $expression = '',
    $parameters = null,
    array $types = array()
)

    Create a sql expression object.

    Parameters

    expression

    Expression string, such as 'count(*)'.

    parameters

    Parameters to replace ? in expression.

    types

    Parameter types, should match with parameters.

    • identifier: means the replaced value is column
    • value: means the replaced value is value
    • literal: means the replaced value is a part of sql statement

    Return

    Pi\Db\Sql\Expression instance.

    Examples

    $model = Pi::model('module');

// SELECT count(*) AS `count` FROM `core_module`;
$expression = Pi::db()->expression('count(*)');
$select = $model->select()->columns(['count' => $expression]);
$model->selectWith($select);

// SELECT sum(id) AS `sum` FROM `core_module`
$expression = Pi::db()->expression('sum(?)', 'id', ['identifier']);
$select = $model->select()->columns(['sum' => $expression]);
$model->selectWith($select);

// SELECT * FROM `core_module` WHERE `title` = null
$expression = Pi::db()->expression('?');
$select = $model->select()->where(['title' => $expression]);
$model->selectWith($select);

// SELECT * FROM `core_module` WHERE `update` = from_unixtime('2015-09-01');
$expression = Pi::db()->expression('from_unixtime(?)', ['2015-09-01'], ['value']);
$select = $model->select()->where(['update' => $expression]);
$model->selectWith($select);

// Sql is as same as that above, change the first type from 'literal' to 'value' will occur error
$expression = Pi::db()->expression('?(?)', ['from_unixtime', '2015-09-01'], ['literal', 'value']);

    profiler

    profiler(DbProfiler $profiler = null)

    Set database profiler.

    Parameters

    profiler

    Database profiler instance, if this parameter is not given, default Pi\Log\DbProfiler will be used.

    Return

    Gateway instance.

    sql

    sql(Adapter $adapter = null, $table = '')

    Create Pi\Db\Sql\Sql instance, this class is an entry to organize SQL by using APIs.

    Parameters

    adapter

    Database adapter instance.

    table

    Table name to use.

    Return

    Pi\Db\Sql\Sql instance.

    Examples

    $adapter = Pi::db()->adapter();
$sql     = Pi::db()->sql($adapter, 'module');
// Alternative
$sql     = Pi::db()->sql(null, 'module');
// Result is module
$table   = $sql->getTable();
// Result is: SELECT * FROM `core_module` WHERE `id` = '1'
$sqlString = $sql->select()->where(['id' => 1])->getSqlString();

    For more information about how sql is organized by API, please refer Pi Database

    select

    select($table = '')

    Create Pi\Db\Sql\Select instance.

    Parameters

    table

    Table name to use.

    Return

    Pi\Db\Sql\Select instance.

    Examples

    $select = Pi::db()->select('module');
$sqlString = $select->where(['id' => 1])->getSqlString();
$select->columns(['id', 'update'])->offset(1)->limit(2);

    insert

    insert($table = '')

    Create Zend\Db\Sql\Insert instance.

    Parameters

    table

    Table name to use.

    Return

    Zend\Db\Sql\Insert instance.

    Examples

    // Insert from select
$insert = Pi::db()->insert('module');
$select = Pi::db()->select('module')->where(['id' => 1]);
// Result is INSERT INTO `core_module` SELECT * FROM `jdb_core_module` WHERE `id` = '1'
// This statement is surely can not be execute because duplicate of primary key id
$sqlString = $insert->select($select)->getSqlString();

// Common insert
// Result is INSERT INTO `core_module` ("name", "version") VALUES ('test', '1.0.0')
$insert->values([
    'name'    => 'test',
    'version' => '1.0.0'
]);
$sqlString = $insert->getSqlString();

    update

    update($table = '')

    Create Zend\Db\Sql\Update instance.

    Parameters

    table

    Table name to use.

    Return

    Zend\Db\Sql\Update instance.

    Examples

    // Result is UPDATE `core_module` SET `name` = 'test', `version` = '1.0.0' WHERE `id` = '1'
$update = Pi::db()->update('module');
$update->set([
    'name'    => 'test',
    'version' => '1.0.0'
])->where(['id' => 1]);
$sqlString = $update->getSqlString();

    delete

    delete($table = '')

    Create Zend\Db\Sql\Delete instance.

    Parameters

    table

    Table name to use.

    Return

    Zend\Db\Sql\Delete instance.

    Examples

    // Result is DELETE FROM `core_module` WHERE `name` = 'test'
$delete = Pi::db()->delete('module');
$delete->where(['name' => 'test']);
$sqlString = $delete->getSqlString();

    query

    query($sql)

    Execute a sql query.

    Parameters

    sql

    Sql to execute.

    Return

    Result instance.

    Examples

    $result = Pi::db()->query('select * from `core_module` where `id` = 1');

    hiii 2

    Add a custom sidebar

    Clone this wiki locally