Доступ к базе данных для записи и выборки данных является необходимостью почти всех приложений и плагинов, которые интегрируются с Invision Community. Класс \IPS\Db обрабатывает соединения с базой данных и расширяет стандартный класс mysqli в основной библиотеке PHP.
Подключение к базе данных
Стандартное соединение с базой данных (обозначенное деталями подключения в файле conf_global.php) может быть установлено автоматически с помощью вызова метода \IPS\Db::i(). Если соединение еще не было установлено, это будет сделано немедленно на лету, когда будет вызван этот метод. Стандартное соединение использует utf8 (или utf8mb4 в зависимости от вашей конфигурации), и все таблицы и столбцы базы данных должны быть настроены на utf8. Это обычно обрабатывается Invision Community, но об этом всё равно важно знать и помнить!
Если вам нужно установить соединение с удаленной базой данных, это можно сделать, передав параметры методу i() класса \IPS\Db. Первый параметр - произвольный идентификатор строкового соединения, а второй параметр - массив с настройками соединения.
$connection = \IPS\Db::i( 'external', array(
'sql_host' => 'localhost',
'sql_user' => 'username',
'sql_pass' => 'password',
'sql_database' => 'database_name',
'sql_port' => 3306,
'sql_socket' => '/var/lib/mysql.sock',
'sql_utf8mb4' => true,
) );
Вам нужно только указать параметры, необходимые для вашего подключения. Вы также можете автоматически поддерживать разделение чтения и записи, передавая те же параметры во второй раз с префиксом «sql_read_» вместо «sql_», указывая на ваш экземпляр MySQL только для чтения.
Выборка данных
Выборка данных из базы данных является одной из наиболее распространенных задач при взаимодействии с базой данных.
/**
* Build SELECT statement
*
* @param array|string $columns The columns (as an array) to select or an expression
* @param array|string $table The table to select from. Either (string) table_name or (array) ( name, alias ) or \IPS\Db\Select object
* @param array|string|NULL $where WHERE clause - see \IPS\Db::compileWhereClause() for details
* @param string|NULL $order ORDER BY clause
* @param array|int $limit Rows to fetch or array( offset, limit )
* @param string|NULL|array $group Column(s) to GROUP BY
* @param array|string|NULL $having HAVING clause (same format as WHERE clause)
* @param int $flags Bitwise flags
* @li \IPS\Db::SELECT_DISTINCT Will use SELECT DISTINCT
* @li \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS Will add SQL_CALC_FOUND_ROWS
* @li \IPS\Db::SELECT_MULTIDIMENSIONAL_JOINS Will return the result as a multidimensional array, with each joined table separately
* @li \IPS\Db::SELECT_FROM_WRITE_SERVER Will send the query to the write server (if read/write separation is enabled)
* @return \IPS\Db\Select
*
*/
public function select( $columns=NULL, $table, $where=NULL, $order=NULL, $limit=NULL, $group=NULL, $having=NULL, $flags=0 )
Вы можете вызвать метод select() для выполнения SELECT запроса к вашей базе данных. Метод возвращает \IPS\Db\Select объект, который позволяет вам в дальнейшем усовершенствовать SELECT запрос. Например, в этом классе есть методы, позволяющие принудительно использовать определенный индекс, делать JOIN запросы к другим таблицам и указывать, какие поля ключ/значение использовать для результатов.
// Получить объект выборки $select = \IPS\Db::i()->select( '*', 'some_table', array( 'field=?', 1 ), 'some_column DESC', array( 0, 10 ) ); // Использовать указанный индекс для запроса $select = $select->forceIndex( 'some_index' ); // Присоединить выборку из другой таблицы $select = $select->join( 'other_table_name', 'other_table_name.column=some_table.column', 'LEFT' ); // Теперь вернем количество полученных результатов $results = count( $select ); // Сообщить итератору, что ключ должны быть столбцом 'column_a', а значения 'column_b' $select = $select->setKeyField( 'column_a' )->setValueField( 'column_b' ); // Наконец, перейдем к результатам foreach( $select as $columnA => $columnB ) { print $columnA . ': ' . $columnB . '<br>'; }
Здесь есть некоторые важные моменты.
- Параметр WHERE принимает много разных форматов, о которых вы должны знать. Вы можете передать строку в качестве оператора WHERE some_column='some value', или вы можете передать массив с первым элементом WHERE, используя ? в качестве плейсхолдеров для значений, а затем каждый плейсхолдер заменяется на последующие записи массива. Это использует подготовленные операторы в MySQL, чтобы избежать проблем с SQL-инъекциями и это рекомендуется array( 'some_column=? OR some_column=?', 'first value', 'second value' ), или, наконец, вы можете передать массив операторов, которые будут объединены вместе AND array( array( 'some_column=?', 'test value' ), array( 'other_column=?', 1 ) ).
- Вы можете вызвать setKeyField() без вызова setValueField(). Вместо значения, являющегося строкой в этом случае, это будет просто массив всех выбранных столбцов (или если выбран только один столбец, значение будет строкой с значением этого столбца). Обратите внимание, что вы должны выбрать столбцы, которые вы хотите использовать для setKeyField и/или setValueField.
- Определение метода для join() требует, чтобы первый параметр был название таблицы, к которой вы хотите присоединить данные, затем оператор 'on', затем тип присоединения (по умолчанию LEFT). Вы также можете использовать оператор для присоединения данных в качестве четвёртого параметра, если необходимо. Для контролирования по каким столбцам сделать выборку, вы должны изменить первый параметр оператора SELECT (если вы передаёте '*' MySQL вернёт все столбцы из всех таблиц, выбранных из и/или присоединения).
- \IPS\Db\Select реализует Iterator и Countable. Это означает, что вы можете рассматривать его как массив и использовать цикл foreach() для обработки результатов, а также вы можете вызвать count() для объекта, чтобы получить количество результатов. Помните, однако, что count() по умолчанию только возвращает количество результатов, возвращаемых запросом. Если у вас 1000 строк в таблице и вы используйте оператор limit, чтобы возвращать только 100 результатов, тогда count() покажет 100. Если вы хотите получить общее количество строк в таблице, как буд-то оператор limit не используется, вы можете передать флаг \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS в стандартный метод select(), как отмечено блоком phpdoc выше.
Часто вы выбираете только одну строку (то есть при выполнении запроса COUNT(*)), встроенный метод first() облегчает это. Однако имейте в виду, что если строка не существует, генерируется исключение UnderflowException, поэтому вы должны перенести такие запросы в оператор try/catch.
try
{
$row = \IPS\Db::i()->select( '*', 'table', array( 'id=?', 2 ) )->first();
}
catch( \UnderflowException $e )
{
// В таблице нет строки с id = 2
}
Вставка, обновление и удаление строк
Вы также захотите периодически вставлять, обновлять и удалять строки в MySQL. Для этого вы можете использовать соответствующие методы insert(), update() и delete().
// Вставить строку
$new_id = \IPS\Db::i()->insert( 'some_table', array( 'column_one' => 'value', 'column_two' => 'value2' ) );
// Обновить эту строку
\IPS\Db::i()->update( 'some_table', array( 'column_two' => 'other value' ), array( 'id_column=?', $new_id ) );
// Удалить строку
\IPS\Db::i()->delete( 'some_table', array( 'id_column=?', $new_id ) );
Первым параметром метода добавления строки является имя таблицы, далее следует ассоциативный массив названий столбцов => значения. Метод возвращает новый автоинкрементный (если применимо) ID.
Обратите внимание, что существует также метод replace(), который ведет себя как insert(), только будет выполнено запрос REPLACE INTO вместо INSERT INTO (в этом случае, если встречается повторяющийся уникальный индекс, оригинал будет заменен новой строкой).
Метод update() ожидает, что первым параметром будет имя таблицы, второй параметр будет ассоциативным массивом имен столбцов => значений, а третьим параметром будет оператор WHERE (если необходимо). Кроме того, при необходимости вы можете передать массив присоединений таблиц (оператор JOIN) в качестве четвёртого параметра, если он необходим, массив, представляющий оператор limit как пятый параметр и флаги для изменения запроса в качестве последнего параметра, в том числе:
\IPS\Db::LOW_PRIORITY Использует LOW_PRIORITY
\IPS\Db::IGNORE Использует IGNORE
Метод delete() обычно вызывается только с первым параметром, именем таблицы, для удаления всей таблицы, а также со вторым параметром оператором where для удаления определенных строк. Метод дополнительно принимает третий параметр для управления упорядочивания результатов для запроса DELETE, четвёртый параметр для ограничения количества результатов удаления, и пятый столбце, который указывает столбец оператора, если оператор WHERE является оператором.
О структуре базы данных
Вы можете создавать таблицы базы данных, добавлять, изменять и удалять столбцы из существующих таблиц, изменять и удалять индекс из существующих таблиц с помощью библиотеки \IPS\Db. Кроме того, существуют методы, позволяющие определить, существует ли таблица, столбец или индекс, прежде чем использовать его.
/** * Существует ли таблица? * * @param string $name Table Name * @return bool */ public function checkForTable( $name ) /** * Существует ли столбец? * * @param string $name Table Name * @param string $column Column Name * @return bool */ public function checkForColumn( $name, $column ) /** * Существует ли индекс? * * @param string $name Table Name * @param string $index Index Name * @return bool */ public function checkForIndex( $name, $index ) /** * Создать таблицу * * @code \IPS\Db::createTable( array( 'name' => 'table_name', // Table name 'columns' => array( ... ), // Column data - see \IPS\Db::compileColumnDefinition for details 'indexes' => array( ... ), // (Optional) Index data - see \IPS\Db::compileIndexDefinition for details 'comment' => '...', // (Optional) Table comment 'engine' => 'MEMORY', // (Optional) Engine to use - will default to not specifying one, unless a FULLTEXT index is specified, in which case MyISAM is forced 'temporary' => TRUE, // (Optional) Will sepcify CREATE TEMPORARY TABLE - defaults to FALSE 'if_not_exists' => TRUE, // (Optional) Will sepcify CREATE TABLE name IF NOT EXISTS - defaults to FALSE ) ); * @endcode * @param array $data Table Definition (see code sample for details) * @throws \IPS\Db\Exception * @return void|string */ public function createTable( $data ) /** * Создать копию структуры таблицы * * @param string $table The table name * @param string $newTableName Name of table to create * @throws \IPS\Db\Exception * @return void|string */ public function duplicateTableStructure( $table, $newTableName ) /** * Переименовать таблицу * * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/rename-table.html'>Rename Table</a> * @param string $oldName The current table name * @param string $newName The new name * @return void * @see <a href='http://stackoverflow.com/questions/12856783/best-practice-with-mysql-innodb-to-rename-huge-table-when-table-with-same-name-a'>Renaming huge InnoDB tables</a> * @see <a href='http://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/'>Performance problem dropping huge InnoDB tables</a> * @note A race condition can occur sometimes with InnoDB + innodb_file_per_table so we can't drop then rename...see above links */ public function renameTable( $oldName, $newName ) /** * Изменить таблицу * Можно обновлять комментарии и движок * @note This will not examine key lengths and adjust. * * @param string $table Table name * @param string|null $comment Table comment. NULL to not change * @param string|null $engine Engine to use. NULL to not change * @return void */ public function alterTable( $table, $comment=NULL, $engine=NULL ) /** * Удалить таблицу * * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/drop-table.html'>DROP TABLE Syntax</a> * @param string|array $table Table Name(s) * @param bool $ifExists Adds an "IF EXISTS" clause to the query * @param bool $temporary Table is temporary? * @return mixed */ public function dropTable( $table, $ifExists=FALSE, $temporary=FALSE ) /** * Добавить столбец в таблицу в базе данных * * @see \IPS\Db::compileColumnDefinition * @param string $table Table name * @param array $definition Column Definition (see \IPS\Db::compileColumnDefinition for details) * @return void */ public function addColumn( $table, $definition ) /** * Изменить существующий столбец * * @see \IPS\Db::compileColumnDefinition * @param string $table Table name * @param string $column Column name * @param array $definition New column definition (see \IPS\Db::compileColumnDefinition for details) * @return void */ public function changeColumn( $table, $column, $definition ) /** * Удалить столбец * * @param string $table Table name * @param string|array $column Column name * @return void */ public function dropColumn( $table, $column ) /** * Добавить индекс в таблицу в базе данных * * @see \IPS\Db::compileIndexDefinition * @param string $table Table name * @param array $definition Index Definition (see \IPS\Db::compileIndexDefinition for details) * @param bool $discardDuplicates If adding a unique index, should duplicates be discarded? (If FALSE and there are any, an exception will be thrown) * @return void */ public function addIndex( $table, $definition, $discardDuplicates=TRUE ) /** * Изменить существующий индекс * * @see \IPS\Db::compileIndexDefinition * @param string $table Table name * @param string $index Index name * @param array $definition New index definition (see \IPS\Db::compileIndexDefinition for details) * @return void */ public function changeIndex( $table, $index, $definition ) /** * Удалить индекс * * @param string $table Table name * @param string|array $index Column name * @return mixed */ public function dropIndex( $table, $index )
Большинство из этих методов описаны в phpdoc и редко используются, за исключением случаев использования центра разработчика для добавления запросов на обновлений.
Разное
Наконец, в классе есть несколько методов и свойств, которые могут оказаться полезными или актуальными при работе с драйвером базы данных. Если вам нужно получить префикс базы данных, используемый у таблиц (он же sql_tbl_prefix в conf_global.php), вы можете сделать это, вызвав \IPS\Db::i()->prefix . Если вы создаете запросы для запуска вручную, вам нужно будет добавить его к именам ваших таблиц.
Если вам нужно построить SQL оператор и вернуть её вместо запуска, вы можете установить \IPS\Db::i()->returnQuery = TRUE перед вызовом драйвера для построения запроса.
Для запуска MySQL запроса, который был полностью построен и представляет из себя строку, вы можете можете вызвать метод query(). Например:
\IPS\Db::i()->query( "UPDATE some_table SET field_a='value' WHERE id_field=1" );
Обычно вам следует избегать непосредственного использования метода query(), поскольку другие встроенные методы автоматически обрабатывают такие вещи, как экранирование значений, добавление префикса таблицы базы данных и т.д.
Если вам нужно создать инструкцию UNION, для облегчения этого метода также существует способ.
/**
* Построение инструкции UNION
*
* @param array $selects Array of \IPS\Db\Select objects
* @param string|NULL $order ORDER BY clause
* @param array|int $limit Rows to fetch or array( offset, limit )
* @param string|null $group Group by clause
* @param bool $unionAll TRUE to perform a UNION ALL, FALSE (default) to perform a regular UNION
* @param int $flags Bitwise flags
* @param array|string|NULL $where WHERE clause (see example)
* @param string $querySelect Custom select for the outer query
* @li \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS Will add SQL_CALC_FOUND_ROWS
* @return \IPS\Db|Select
*/
public function union( $selects, $order, $limit, $group=NULL, $unionAll=FALSE, $flags=0, $where=NULL, $querySelect='*' )
Для построения оператора FIND_IN_SET(), который позволяет запросу искать определенные значения в поле MySQL, которое содержит значения, разделенные запятыми, вы можете использовать метод findInSet().
/**
* FIND_IN_SET
* Generates a WHERE clause to determine if any value from a column containing a comma-delimined list matches any value from an array
*
* @param string $column Column name (which contains a comma-delimited list)
* @param array $values Acceptable values
* @param bool $reverse If true, will match cases where NO values from $column match any from $values
* @return string Where clause
* @see \IPS\Db::in() More efficient equivilant for columns that do not contain comma-delimited lists
*/
public function findInSet( $column, $values, $reverse=FALSE )
Аналогичным образом вы можете построить оператор IN() с помощью метода in():
/**
* IN
* Generates a WHERE clause to determine if the value of a column matches any value from an array
*
* @param string $column Column name
* @param array $values Acceptable values
* @param bool $reverse If true, will match cases where $column does NOT match $values
* @return string Where clause
* @see \IPS\Db::findInSet() For columns that contain comma-delimited lists
*/
public function in( $column, $values, $reverse=FALSE )
Если вы выполняете запрос по побитовому столбцу и вам нужно проверить значение, вы можете использовать метод bitwiseWhere (или просто построить оператор WHERE вручную).
/**
* Bitwise WHERE clause
*
* @param array $definition Bitwise keys as defined by the class
* @param string $key The key to check for
* @param bool $value Value to check for
* @return string
* @throws \InvalidArgumentException
*/
public function bitwiseWhere( $definition, $key, $value=TRUE )
Вы обнаружите, что большинство этих разных методов не так часто используются, как методы ядра insert, update, delete, replace и select.