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.

- <module name>/<table name>: class 'Module\<module name>\Model\<table name>' will be initialized if it exists. 
- <table name>: 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.

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);
$expression = Pi::db()->expression('from_unixtime(?)', ['2015-09-01'], ['identifier']);

hiii 2

Add a custom sidebar

Clone this wiki locally