Jump to content

Доступ к базе данных

Доступ к базе данных для записи и выборки данных является необходимостью почти всех приложений и плагинов, которые интегрируются с 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.




Messenger

    ×
    ×
    • Create New...

    Important Information

    By using our site you agree to our Privacy Policy