Db
- Type
- Class
- Namespace
- craft\helpers
- Inherits
- craft\helpers\Db
- Since
- 3.0.0
Class Db
# Public Methods
Method | Description |
---|---|
areColumnTypesCompatible() | Returns whether two column type definitions are relatively compatible with each other. |
batchInsert() | Creates and executes a batch INSERT SQL statement. |
delete() | Creates and executes a DELETE SQL statement. |
deleteIfExists() | Creates and executes a DELETE SQL statement, but only if there are any rows to delete, avoiding deadlock issues when deleting data from large tables. |
escapeParam() | Escapes commas and asterisks in a string so they are not treated as special characters in craft\helpers\Db::parseParam(). |
getMaxAllowedValueForNumericColumn() | Returns the maximum number allowed for a given column type. |
getMinAllowedValueForNumericColumn() | Returns the minimum number allowed for a given column type. |
getNumericalColumnType() | Returns a number column type, taking the min, max, and number of decimal points into account. |
getSimplifiedColumnType() | Returns a simplified version of a given column type. |
getTextualColumnStorageCapacity() | Returns the maximum number of bytes a given textual column type can hold for a given database. |
getTextualColumnTypeByContentLength() | Given a length of a piece of content, returns the underlying database column type to use for saving. |
idByUid() | Returns the id of a row in the given table by its uid . |
idsByUids() | Returns an array uid :id pairs from a given table, by their uid s. |
insert() | Creates and executes an INSERT SQL statement. |
isNumericColumnType() | Returns whether the given column type is numeric. |
isTextualColumnType() | Returns whether the given column type is textual. |
isTypeSupported() | Returns whether a given DB connection’s schema supports a column type. |
parseBooleanParam() | Parses a query param value for a boolean column and returns a yii\db\QueryInterface::where()-compatible condition. |
parseColumnLength() | Parses a column type definition and returns just the column length/size. |
parseColumnType() | Parses a column type definition and returns just the column type, if it can be determined. |
parseDateParam() | Parses a query param value for a date/time column, and returns a yii\db\QueryInterface::where()-compatible condition. |
parseDsn() | Parses a DSN string and returns an array with the driver and any driver params, or just a single key. |
parseParam() | Parses a query param value and returns a yii\db\QueryInterface::where()-compatible condition. |
prepareDateForDb() | Prepares a date to be sent to the database. |
prepareValueForDb() | Prepares a value to be sent to the database. |
prepareValuesForDb() | Prepares an array or object’s values to be sent to the database. |
replace() | Creates and executes a SQL statement for replacing some text with other text in a given table column. |
reset() | Resets the memoized database connection. |
uidById() | Returns the uid of a row in the given table by its id . |
uidsByIds() | Returns an array id :uid pairs from a given table, by their id s. |
update() | Creates and executes an UPDATE SQL statement. |
upsert() | Creates and executes a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do. |
url2config() | Generates a DB config from a database connection URL. |
# areColumnTypesCompatible()
Returns whether two column type definitions are relatively compatible with each other.
Arguments
Returns
# batchInsert()
- Since
- 3.5.0
Creates and executes a batch INSERT
SQL statement.
The method will properly escape the column names, and bind the values to be inserted.
Arguments
$table
(string) – The table that new rows will be inserted into$columns
(array) – The column names$rows
(array) – The rows to be batch inserted into the table$includeAuditColumns
(boolean) – WhetherdateCreated
,dateUpdated
, anduid
values should be added to $columns$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – The number of rows affected by the execution
Throws
- yii\db\Exception
if execution failed
# delete()
- Since
- 3.5.0
Creates and executes a DELETE
SQL statement.
Arguments
$table
(string) – The table where the data will be deleted from$condition
(array, string) – The conditions that will be put in theWHERE
part. Please refer to craft\db\Query::where() on how to specify conditions.$params
(array) – The parameters to be bound to the query.$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – The number of rows affected by the execution
Throws
- yii\db\Exception
if execution failed
# deleteIfExists()
- Since
- 3.0.12
Creates and executes a DELETE
SQL statement, but only if there are any rows to delete, avoiding deadlock issues
when deleting data from large tables.
Arguments
$table
(string) – The table where the data will be deleted from$condition
(string, array) – The condition that will be put in theWHERE
part. Please refer to craft\db\Query::where() on how to specify condition.$params
(array) – The parameters to be bound to the command$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – Number of rows affected by the execution
Throws
- yii\db\Exception
execution failed
# escapeParam()
Escapes commas and asterisks in a string so they are not treated as special characters in craft\helpers\Db::parseParam().
Arguments
$value
(string) – The param value.
Returns
string – The escaped param value.
# getMaxAllowedValueForNumericColumn()
Returns the maximum number allowed for a given column type.
Arguments
$columnType
(string)
Returns
integer, false – The max allowed number, or false if it can't be determined
# getMinAllowedValueForNumericColumn()
Returns the minimum number allowed for a given column type.
Arguments
$columnType
(string)
Returns
integer, false – The min allowed number, or false if it can't be determined
# getNumericalColumnType()
Returns a number column type, taking the min, max, and number of decimal points into account.
Arguments
Returns
Throws
- yii\base\Exception
if no column types can contain this
# getSimplifiedColumnType()
Returns a simplified version of a given column type.
Arguments
$columnType
(string)
Returns
# getTextualColumnStorageCapacity()
Returns the maximum number of bytes a given textual column type can hold for a given database.
Arguments
$columnType
(string) – The textual column type to check$db
(craft\db\Connection, null) – The database connection
Returns
integer, null, false – The storage capacity of the column type in bytes, null if unlimited, or false or it can't be determined.
# getTextualColumnTypeByContentLength()
Given a length of a piece of content, returns the underlying database column type to use for saving.
Arguments
$contentLength
(integer)$db
(craft\db\Connection, null) – The database connection
Returns
Throws
- yii\base\Exception
if using an unsupported connection type
# idByUid()
- Since
- 3.1.0
Returns the id
of a row in the given table by its uid
.
Arguments
$table
(string)$uid
(string)$db
(craft\db\Connection, null) – The database connection to use
Returns
# idsByUids()
- Since
- 3.1.0
Returns an array uid
:id
pairs from a given table, by their uid
s.
Arguments
$table
(string)$uids
(string[])$db
(craft\db\Connection, null) – The database connection to use
Returns
string[]
# insert()
- Since
- 3.5.0
Creates and executes an INSERT
SQL statement.
The method will properly escape the column names, and bind the values to be inserted.
Arguments
$table
(string) – The table that new rows will be inserted into$columns
(array) – The column data (name=>value) to be inserted into the table$includeAuditColumns
(boolean) – Whether to include the data for the audit columns (dateCreated
,dateUpdated
, anduid
)$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – The number of rows affected by the execution
Throws
- yii\db\Exception
if execution failed
# isNumericColumnType()
Returns whether the given column type is numeric.
Arguments
$columnType
(string)
Returns
# isTextualColumnType()
Returns whether the given column type is textual.
Arguments
$columnType
(string)
Returns
# isTypeSupported()
Returns whether a given DB connection’s schema supports a column type.
Arguments
$type
(string)$db
(craft\db\Connection, null)
Returns
Throws
# parseBooleanParam()
- Since
- 3.4.15
Parses a query param value for a boolean column and returns a yii\db\QueryInterface::where()-compatible condition.
The follow values are supported:
true
orfalse
:empty:
or:notempty:
(normalizes tofalse
andtrue
)'not x'
or'!= x'
(normalizes to the opposite of the boolean value ofx
)- Anything else (normalizes to the boolean value of itself)
If $defaultValue
is set, and it matches the normalized $value
, then the resulting condition will match any
null
values as well.
Arguments
$column
(string) – The database column that the param is targeting.$value
(string, boolean) – The param value$defaultValue
(boolean, null) – Hownull
values should be treated
Returns
mixed
# parseColumnLength()
Parses a column type definition and returns just the column length/size.
Arguments
$columnType
(string)
Returns
# parseColumnType()
Parses a column type definition and returns just the column type, if it can be determined.
Arguments
$columnType
(string)
Returns
# parseDateParam()
Parses a query param value for a date/time column, and returns a yii\db\QueryInterface::where()-compatible condition.
Arguments
$column
(string) – The database column that the param is targeting.$value
(string, array, DateTime) – The param value$defaultOperator
(string) – The default operator to apply to the values (can benot
,!=
,<=
,>=
,<
,>
, or=
)
Returns
mixed
# parseDsn()
- Since
- 3.4.0
Parses a DSN string and returns an array with the driver
and any driver params, or just a single key.
Arguments
Returns
array, string, false – The full array, or the specific key value, or false
if $key
is a param that
doesn’t exist in the DSN string.
Throws
- yii\base\InvalidArgumentException
if $dsn is invalid
# parseParam()
Parses a query param value and returns a yii\db\QueryInterface::where()-compatible condition.
If the $value
is a string, it will automatically be converted to an array, split on any commas within the
string (via craft\helpers\ArrayHelper::toArray()). If that is not desired behavior, you can escape the comma
with a backslash before it.
The first value can be set to either 'and'
or 'or'
to define whether all of the values must match, or
any. If it’s neither 'and'
nor 'or'
, then 'or'
will be assumed.
Values can begin with the operators 'not '
, '!='
, '<='
, '>='
, '<'
, '>'
, or '='
. If they don’t,
'='
will be assumed.
Values can also be set to either ':empty:'
or ':notempty:'
if you want to search for empty or non-empty
database values. (An “empty” value is either NULL
or an empty string of text).
Arguments
$column
(string) – The database column that the param is targeting.$value
(string, integer, array) – The param value(s).$defaultOperator
(string) – The default operator to apply to the values (can benot
,!=
,<=
,>=
,<
,>
, or=
)$caseInsensitive
(boolean) – Whether the resulting condition should be case-insensitive$columnType
(string, null) – The database column type the param is targeting
Returns
mixed
# prepareDateForDb()
Prepares a date to be sent to the database.
Arguments
$date
(mixed
) – The date to be prepared$stripSeconds
(boolean) – Whether the seconds should be omitted from the formatted string
Returns
string, null – The prepped date, or null
if it could not be prepared
# prepareValueForDb()
Prepares a value to be sent to the database.
Arguments
$value
(mixed
) – The value to be prepared
Returns
mixed
– The prepped value
# prepareValuesForDb()
Prepares an array or object’s values to be sent to the database.
Arguments
$values
(mixed
) – The values to be prepared
Returns
array – The prepared values
# replace()
- Since
- 3.5.0
Creates and executes a SQL statement for replacing some text with other text in a given table column.
Arguments
$table
(string) – The table to be updated$column
(string) – The column to be searched$find
(string) – The text to be searched for$replace
(string) – The replacement text$condition
(string, array) – The condition that will be put in theWHERE
part. Please refer to craft\db\Query::where() on how to specify condition.$params
(array) – The parameters to be bound to the command$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – The number of rows affected by the execution
Throws
- yii\db\Exception
if execution failed
# reset()
- Since
- 3.5.12.1
Resets the memoized database connection.
# uidById()
- Since
- 3.1.0
Returns the uid
of a row in the given table by its id
.
Arguments
$table
(string)$id
(integer)$db
(craft\db\Connection, null) – The database connection to use
Returns
# uidsByIds()
- Since
- 3.1.0
Returns an array id
:uid
pairs from a given table, by their id
s.
Arguments
$table
(string)$ids
(integer[])$db
(craft\db\Connection, null) – The database connection to use
Returns
string[]
# update()
- Since
- 3.5.0
Creates and executes an UPDATE
SQL statement.
The method will properly escape the column names and bind the values to be updated.
Arguments
$table
(string) – The table to be updated$columns
(array) – The column data (name => value) to be updated$condition
(string, array) – The condition that will be put in theWHERE
part. Please refer to craft\db\Query::where() on how to specify condition$params
(array) – The parameters to be bound to the command$includeAuditColumns
(boolean) – Whether thedateUpdated
value should be added to $columns$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – The number of rows affected by the execution
Throws
- yii\db\Exception
if execution failed
# upsert()
- Since
- 3.5.0
Creates and executes a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.
The method will properly escape the column names, and bind the values to be inserted.
Arguments
$table
(string) – The table that new rows will be inserted into/updated in$insertColumns
(array, craft\db\Query) – The column data (name => value) to be inserted into the table or instance of craft\db\Query to performINSERT INTO ... SELECT
SQL statement$updateColumns
(array, boolean) – The column data (name => value) to be updated if they already existIf
true
is passed, the column data will be updated to match the insert column data.If
false
is passed, no update will be performed if the column data already exists.$params
(array) – The parameters to be bound to the command$includeAuditColumns
(boolean) – WhetherdateCreated
,dateUpdated
, anduid
values should be added to $columns$db
(craft\db\Connection, null) – The database connection to use
Returns
integer – The number of rows affected by the execution
Throws
- yii\db\Exception
if execution failed
# url2config()
- Since
- 3.4.0
Generates a DB config from a database connection URL.
This can be used from config/db.php
:
Arguments
$url
(string)
Returns
Example
$url = craft\helpers\App::env('DB_URL');
return craft\helpers\Db::url2config($url);
# Constants
Constant | Description |
---|---|
SIMPLE_TYPE_NUMERIC | |
SIMPLE_TYPE_TEXTUAL |