Skip to content

Dev.Pi\Db\DbGateway

Jump to bottom Edit New page
linzongshu edited this page Oct 19, 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 <table prefix>_<module name>_<table name> and <table prefix>_core_<table name> 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:

<prefix>: set table prefix.

<name>: table name.

<adapter>: adapter instance.

<type>: 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