db-dao.md 8.78 KB
Newer Older
1 2 3
Database basics
===============

4
> Note: This section is under development.
Qiang Xue committed
5

6
Yii has a database access layer built on top of PHP's [PDO](http://www.php.net/manual/en/book.pdo.php). It provides
7 8 9
uniform API and solves some inconsistencies between different DBMS. By default Yii supports the following DBMS:

- [MySQL](http://www.mysql.com/)
10
- [MariaDB](https://mariadb.com/)
11 12
- [SQLite](http://sqlite.org/)
- [PostgreSQL](http://www.postgresql.org/)
Qiang Xue committed
13
- [CUBRID](http://www.cubrid.org/): version 9.1.0 or higher.
14
- [Oracle](http://www.oracle.com/us/products/database/overview/index.html)
Qiang Xue committed
15 16
- [MSSQL](https://www.microsoft.com/en-us/sqlserver/default.aspx): version 2012 or above is required if you
  want to use LIMIT/OFFSET.
17

18

19 20 21 22 23 24 25
Configuration
-------------

In order to start using database you need to configure database connection component first by adding `db` component
to application configuration (for "basic" web application it's `config/web.php`) like the following:

```php
Alexander Makarov committed
26
return [
27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=mydatabase', // MySQL, MariaDB
            //'dsn' => 'sqlite:/path/to/database/file', // SQLite
            //'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
            //'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
            //'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase', // MS SQL Server, sqlsrv driver
            //'dsn' => 'dblib:host=localhost;dbname=mydatabase', // MS SQL Server, dblib driver
            //'dsn' => 'mssql:host=localhost;dbname=mydatabase', // MS SQL Server, mssql driver
            //'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],
    // ...
Alexander Makarov committed
46
];
47
```
48

49 50 51 52 53 54
There is a peculiarity when you want to work with the database through the `ODBC` layer. When using `ODBC`,
connection `DSN` doesn't indicate uniquely what database type is being used. That's why you have to override
`driverName` property of [[yii\db\Connection]] class to disambiguate that:

```php
'db' => [
55 56 57 58 59
    'class' => 'yii\db\Connection',
    'driverName' => 'mysql',
    'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
    'username' => 'root',
    'password' => '',
60 61 62
],
```

63 64
Please refer to the [PHP manual](http://www.php.net/manual/en/function.PDO-construct.php) for more details
on the format of the DSN string.
65

66
After the connection component is configured you can access it using the following syntax:
67 68 69 70 71

```php
$connection = \Yii::$app->db;
```

72
You can refer to [[yii\db\Connection]] for a list of properties you can configure. Also note that you can define more
73
than one connection component and use both at the same time if needed:
74 75 76 77 78 79

```php
$primaryConnection = \Yii::$app->db;
$secondaryConnection = \Yii::$app->secondDb;
```

80
If you don't want to define the connection as an application component you can instantiate it directly:
81 82

```php
Alexander Makarov committed
83
$connection = new \yii\db\Connection([
84 85 86
    'dsn' => $dsn,
     'username' => $username,
     'password' => $password,
Alexander Makarov committed
87
]);
88 89 90
$connection->open();
```

91

92 93 94 95 96
> **Tip**: if you need to execute additional SQL queries right after establishing a connection you can add the
> following to your application configuration file:
>
```php
return [
97 98 99 100 101 102 103 104 105 106 107 108
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            // ...
            'on afterOpen' => function($event) {
                $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
            }
        ],
    ],
    // ...
109 110 111
];
```

112 113 114
Basic SQL queries
-----------------

115
Once you have a connection instance you can execute SQL queries using [[yii\db\Command]].
116 117 118 119 120 121

### SELECT

When query returns a set of rows:

```php
122
$command = $connection->createCommand('SELECT * FROM post');
123 124 125 126 127 128
$posts = $command->queryAll();
```

When only a single row is returned:

```php
129
$command = $connection->createCommand('SELECT * FROM post WHERE id=1');
130
$post = $command->queryOne();
131 132 133 134 135
```

When there are multiple values from the same column:

```php
136
$command = $connection->createCommand('SELECT title FROM post');
137 138 139 140 141 142
$titles = $command->queryColumn();
```

When there's a scalar value:

```php
143
$command = $connection->createCommand('SELECT COUNT(*) FROM post');
144 145 146 147 148 149 150 151
$postCount = $command->queryScalar();
```

### UPDATE, INSERT, DELETE etc.

If SQL executed doesn't return any data you can use command's `execute` method:

```php
152
$command = $connection->createCommand('UPDATE post SET status=1 WHERE id=1');
153 154 155
$command->execute();
```

156
Alternatively the following syntax that takes care of proper table and column names quoting is possible:
157 158 159

```php
// INSERT
160
$connection->createCommand()->insert('user', [
161 162
    'name' => 'Sam',
    'age' => 30,
Alexander Makarov committed
163
])->execute();
164 165

// INSERT multiple rows at once
166
$connection->createCommand()->batchInsert('user', ['name', 'age'], [
167 168 169
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
Alexander Makarov committed
170
])->execute();
171 172

// UPDATE
173
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
174 175

// DELETE
176
$connection->createCommand()->delete('user', 'status = 0')->execute();
177 178
```

179 180 181
Quoting table and column names
------------------------------

182
Most of the time you would use the following syntax for quoting table and column names:
183 184

```php
Alexander Makarov committed
185
$sql = "SELECT COUNT([[$column]]) FROM {{table}}";
186 187 188
$rowCount = $connection->createCommand($sql)->queryScalar();
```

189
In the code above `[[X]]` will be converted to properly quoted column name while `{{Y}}` will be converted to properly
190 191
quoted table name.

192 193 194
For table names there's a special variant `{{%Y}}` that allows you to automatically appending table prefix if it is set:

```php
Alexander Makarov committed
195
$sql = "SELECT COUNT([[$column]]) FROM {{%table}}";
196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214
$rowCount = $connection->createCommand($sql)->queryScalar();
```

The code above will result in selecting from `tbl_table` if you have table prefix configured like the following in your
config file:

```php
return [
    // ...
    'components' => [
        // ...
        'db' => [
            // ...
            'tablePrefix' => 'tbl_',
        ],
    ],
];
```

215 216
The alternative is to quote table and column names manually using [[yii\db\Connection::quoteTableName()]] and
[[yii\db\Connection::quoteColumnName()]]:
217 218 219 220 221 222 223

```php
$column = $connection->quoteColumnName($column);
$table = $connection->quoteTableName($table);
$sql = "SELECT COUNT($column) FROM $table";
$rowCount = $connection->createCommand($sql)->queryScalar();
```
224 225 226 227 228 229 230

Prepared statements
-------------------

In order to securely pass query parameters you can use prepared statements:

```php
231
$command = $connection->createCommand('SELECT * FROM post WHERE id=:id');
232 233 234 235 236 237 238
$command->bindValue(':id', $_GET['id']);
$post = $command->query();
```

Another usage is performing a query multiple times while preparing it only once:

```php
239
$command = $connection->createCommand('DELETE FROM post WHERE id=:id');
240 241 242 243 244 245 246 247 248 249 250 251
$command->bindParam(':id', $id);

$id = 1;
$command->execute();

$id = 2;
$command->execute();
```

Transactions
------------

252
You can perform transactional SQL queries like the following:
253 254 255 256

```php
$transaction = $connection->beginTransaction();
try {
257 258 259 260
    $connection->createCommand($sql1)->execute();
     $connection->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
    $transaction->commit();
261
} catch(Exception $e) {
262
    $transaction->rollBack();
263 264 265
}
```

266 267 268 269 270 271
You can also nest multiple transactions, if needed:

```php
// outer transaction
$transaction1 = $connection->beginTransaction();
try {
272 273 274 275 276 277 278 279 280 281 282 283
    $connection->createCommand($sql1)->execute();

    // inner transaction
    $transaction2 = $connection->beginTransaction();
    try {
        $connection->createCommand($sql2)->execute();
        $transaction2->commit();
    } catch (Exception $e) {
        $transaction2->rollBack();
    }

    $transaction1->commit();
284
} catch (Exception $e) {
285
    $transaction1->rollBack();
286 287 288 289
}
```


290 291 292 293 294
Working with database schema
----------------------------

### Getting schema information

295
You can get a [[yii\db\Schema]] instance like the following:
296 297 298 299 300 301 302 303 304 305 306

```php
$schema = $connection->getSchema();
```

It contains a set of methods allowing you to retrieve various information about the database:

```php
$tables = $schema->getTableNames();
```

307
For the full reference check [[yii\db\Schema]].
308 309 310

### Modifying schema

311
Aside from basic SQL queries [[yii\db\Command]] contains a set of methods allowing to modify database schema:
312 313 314 315 316 317 318 319 320 321

- createTable, renameTable, dropTable, truncateTable
- addColumn, renameColumn, dropColumn, alterColumn
- addPrimaryKey, dropPrimaryKey
- addForeignKey, dropForeignKey
- createIndex, dropIndex

These can be used as follows:

```php
322
// CREATE TABLE
323
$connection->createCommand()->createTable('post', [
324 325 326
    'id' => 'pk',
    'title' => 'string',
    'text' => 'text',
Alexander Makarov committed
327
]);
328 329
```

330
For the full reference check [[yii\db\Command]].