В этой серии мы рассмотрим использование пользовательских таблиц базы данных. Мы расскажем о том, как создавать, поддерживать и удалять таблицы, а также о том, как безопасно и эффективно добавлять, удалять и запрашивать данные. В этой первой статье мы рассмотрим, когда пользовательские таблицы могут быть подходящими, плюсы и минусы их использования и как создать таблицу.
К счастью, WordPress предоставляет довольно существенный API, который делает создание пользовательских таблиц и взаимодействие с ними немного проще. В частности: класс $wpdb и dbDelta() которых мы еще поговорим в этой серии. Несмотря на это, однако, создание пользовательской таблицы означает создание чего-то чуждого WordPress — и вы потеряете большую часть инфраструктуры, которая окружает собственные таблицы. По этой причине вы как автор плагина несете ответственность за безопасное и эффективное взаимодействие с ним. Поэтому, прежде чем приступить к обсуждению, вы должны тщательно продумать, подходит ли это использование существующей базовой таблицы.
Недостатки использования пользовательской таблицы
Как уже упоминалось, пользовательские таблицы находятся за пределами обычной платформы WordPress — и по большей части это является основной причиной их недостатков:
- Не существует встроенных функций добавления, удаления, обновления или запросов для взаимодействия с таблицей.
- Пользовательский интерфейс должен быть построен с нуля.
- Санитарная обработка и кэширование зависит от вас (хотя WordPress предоставляет большую помощь в этом отношении).
- Другие плагины и сам WordPress не ожидают, что ваша таблица будет там. С другой стороны, если ваши данные относятся к пользовательскому типу постов, то большинство хорошо разработанных сторонних плагинов будут работать вместе с ними.
- WordPress — как и многие другие связанные плагины — создаст резервную копию или экспортирует вашу таблицу. (На самом деле довольно много плагинов для резервного копирования поддерживают неосновные таблицы, но экспорт / импорт не так прост)
- Вы несете ответственность за наиболее эффективную настройку структуры пользовательских таблиц, включая выбор наиболее подходящего типа данных для столбцов.
- Вы несете ответственность за написание без ошибок и эффективных запросов SQL.
Когда подходит создание пользовательской таблицы?
На это нет «правильного» ответа, и требуется разумное суждение о плюсах и минусах. Тем не менее, в предыдущем разделе описаны некоторые серьезные недостатки, связанные с тем, что вы не используете существующую схему WordPress. Поэтому, если вы не уверены, обычно лучше избегать создания таблицы. Кроме того, подход с использованием пользовательских таблиц требует большой работы и предоставляет широкие возможности для поиска ошибок. Но с учетом этого, когда может быть подходящей пользовательская таблица?
Структура данных
Одним из наиболее важных аргументов для пользовательских таблиц является необходимость структурирования данных таким образом, который не подходит для собственных таблиц. Таблица *_posts изначально ориентирована на сообщения и страницы, которые могут быть совершенно непригодны для ваших данных. На самом деле ваши данные лучше всего распределить по нескольким таблицам с взаимосвязями между ними. Это может быть даже не так сложно: плагин Posts 2 Posts использует собственную таблицу для хранения связей типа «многие ко многим» между типами записей. Это может быть сделано с помощью API-интерфейса таксономии (и изначально был) или мета-API — но ни один из них не особенно эффективен — и хотя он может подойти для небольших сайтов, он плохо масштабируется. Scribu перемещал сообщения 2 публикации в пользовательскую реализацию таблицы, чтобы можно было хранить информацию об отношениях.
Хотя большинство случаев можно «втиснуть» в *_posts с помощью post meta, это может не обеспечить наиболее эффективный маршрут: таблица meta meta использует столбец неиндексированных значений для хранения данных. Он невероятно быстр при извлечении метаданных поста (WordPress использует здесь и кеширование), но сложные запросы с использованием мета-таблицы могут быть неэффективными или почти невозможными.
Сложные Запросы
С вышеизложенным связаны сложные запросы, которые могут быть не разработаны для эффективного выполнения собственных таблиц. Например, в Event Organizer событие — это сообщение с датами события, которые хранятся в отдельной таблице. Хотя было бы возможно сохранить эти даты как мета-пост, так как в случае, когда события имеют более одной даты, любые запросы на основе даты будут чрезвычайно сложными и неэффективными, особенно если столбец мета-значений не проиндексирован.
Масштаб
Если вы используете wp_posts и ваши данные достаточно велики (более 100 000 сообщений), это может снизить производительность в зависимости от того, какие запросы вы выполняете. Этот аргумент сам по себе довольно слабый, поскольку существует много неизвестных, которые повлияют на его обоснованность. Тем не менее, в целом, базы данных быстры в своих действиях, а окружающая среда WordPress позволяет максимально оптимизировать запросы. Однако в сочетании с двумя другими факторами вы можете обнаружить, что настраиваемая таблица представляет наиболее разумный вариант.
Создание таблицы
Как только вы решили, что пользовательская таблица необходима, нам нужно создать таблицу. Прежде чем мы это сделаем, мы сохраним имя нашей пользовательской таблицы в $wpdb . Этот глобальный объект содержит всю информацию, относящуюся к базе данных для текущего блога (она будет меняться от сайта к сайту при использовании нескольких сайтов). Мы добавим имя нашей таблицы к этому глобальному. Это совсем не обязательно, но делает наш код немного аккуратнее:
|
1
2
3
4
5
6
7
|
add_action( ‘init’, ‘wptuts_register_activity_log_table’, 1 );
add_action( ‘switch_blog’, ‘wptuts_register_activity_log_table’ );
function wptuts_register_activity_log_table() {
global $wpdb;
$wpdb->wptuts_activity_log = «{$wpdb->prefix}wptuts_activity_log»;
}
|
Приведенный выше код использует $wpdb->prefix для добавления префикса к имени таблицы. Префикс по умолчанию wp_ но может быть изменен пользователем в wp-config.php . Это необходимо, если у вас может быть более одной установки WordPress с использованием одной и той же базы данных, но это также может быть изменено по другим причинам. Таким образом, вы не можете предполагать, что префикс wp_ . Как и в случае с функциями, классами, настройками и т. Д., Вы должны убедиться, что имя вашей таблицы уникально.
На протяжении всей этой серии мы вернемся к следующему примеру. Представим, что мы создаем таблицу для регистрации активности пользователей (обновление или удаление сообщений, изменение настроек, загрузка изображений и т. Д.).
Соглашения об именовании столбцов
Существуют различные соглашения о том, как вы называете свои столбцы (и ваши таблицы в этом отношении), но независимо от того, как вы их называете, важно быть последовательным . Я бы рекомендовал использовать только строчные символы, так как в некоторых ситуациях имена столбцов могут быть чувствительны к регистру, а наложение этого правила снижает вероятность ошибок и улучшает читабельность. Как мы увидим позже в этой серии, это также полезно, когда вам нужно добавить столбцы в белый список. Вы должны разделять слова в именах столбцов (например, post_data , post_content ) для удобства чтения, но вы должны делать это с подчеркиванием, а не пробелами.
Вы также должны избегать зарезервированных слов . Если столбец ссылается на внешнюю таблицу, то рекомендуется использовать имя этого внешнего столбца (например, user_id , наш пример).
В нашем примере мы будем называть наши столбцы:
-
log_id— идентификатор журнала. -
user_id— идентификатор пользователя, которому соответствует журнал. -
activity— активность, которая произошла. -
object_id— идентификатор объекта (например, идентификатор записи, идентификатор пользователя, идентификатор комментария и т. д.), который был предметом активности пользователя. -
object_type— тип объекта (например, «post», «user», «comment» и т. д.). -
activity_date— дата и время действия.
Выбор типа столбца
Прежде чем идти дальше, вам необходимо определить типы данных столбцов, которые будет иметь ваша таблица. Типы столбцов можно разделить на три категории: строки, числа и даты. Для каждого из них есть много вариантов. Вы можете найти полную ссылку здесь .
Важно выбрать подходящий тип данных для вашей таблицы, так как это повлияет на эффективность ваших запросов. Некоторые типы данных позволяют вам установить ограничение (например, varchar(40) — который позволяет хранить до 40 символов). Ограничение является необязательным, но рекомендуется, поскольку оно может повысить производительность — поэтому вам нужно будет решить для каждого столбца, какое максимальное количество символов потребуется для столбца. Обратите внимание, что для числовых типов данных длина относится к количеству цифр, а не к максимальному (например, INT(10) допускает неотрицательные целые числа до 10 цифр — до 4 294 967 295).
При хранении дат вы почти всегда должны использовать тип данных DATETIME (хранится как 2012-11-05 14:55:10) — и, конечно, не удобное для человека представление даты (например, 5 ноября 2012 г. 14:55). Значения DATETIME могут быть легко отформатированы в удобочитаемой для человека форме с помощью таких функций, как mysql2date() . Вы должны хранить даты в часовом поясе UTC и при необходимости переключать их на другой часовой пояс на выходе.
В нашем примере мы будем иметь:
-
log_id— bigint (20) -
user_id— bigint (20) -
activity— varchar (20) -
object_id— bigint (20) -
object_type— varchar (20) -
date— datetime
Индексирование столбцов
Далее вам нужно решить, какие столбцы индексировать — они будут объявлены как KEY , одним из которых будет PRIMARY KEY . Первичный ключ — это столбец, в котором каждая строка имеет уникальную запись — обычно это просто целое число с автоинкрементом, по сути, «номер строки».
Значения других проиндексированных столбцов не обязательно должны быть уникальными, но это значение должно определять относительно небольшой набор записей. Идея индексирования заключается в улучшении запросов на чтение. Без индекса поиск должен был бы прочитать всю таблицу, чтобы найти совпадающие строки. Если столбец индексируется и является частью запроса — тогда он может быстро найти строки, соответствующие этому столбцу, а затем можно сопоставить меньшее подмножество совпадающих строк с запросом (аналогия представляет собой индекс для книги).
Таким образом, если вы не выполняете запрос по этому столбцу, то индексация этого столбца не поможет (если вы никогда не найдете слово в индексе книги, его может и не быть). Кроме того, если многие записи имеют одинаковое значение, например столбец «пол», это не принесет значительных улучшений при полном сканировании таблицы (представьте индекс книги, в котором содержится слово, которое появляется на каждой другой странице).
Индексация также не является бесплатной: столбцы, объявленные как KEY действительно снижают производительность записи (чтобы продолжить аналогию, вам нужно будет обновить индекс книги, когда индексированное слово добавляется или удаляется), и поэтому вам нужно будет решить, какой правильный баланс для вашей установки. Более подробную информацию можно найти здесь .
Поскольку, скорее всего, мы захотим сделать запрос по пользователю (чтобы увидеть его недавнюю активность), мы будем индексировать этот столбец и использовать log_id в качестве первичного ключа.
Создание таблицы
Мы поместим код для создания пользовательской таблицы в следующую функцию:
|
1
2
3
4
5
6
|
function wptuts_create_tables() {
// Code for creating a table goes here
}
// Create tables on plugin activation
register_activation_hook( __FILE__, ‘wptuts_create_tables’ );
|
Эту функцию нужно будет вызывать в хуке активации плагина, а также в любое время, когда мы захотим внести какие-либо изменения в таблицу — например, добавить столбцы или изменить их тип данных (мы рассмотрим почему позже).
Тот факт, что с помощью ловушки активации, wptuts_create_tables() может быть вызван, когда таблица уже существует, не является упущением — и снова мы рассмотрим причину позже в этой серии.
Внутри этой функции мы включаем wp-admin / includes / upgrade.php, чтобы установить несколько констант и загрузить функцию dbDelta() . Обратите внимание, что когда плагин активирован, он пропускает ловушку init , поэтому wptuts_register_activity_log_table() должен вызываться вручную.
|
1
2
3
4
5
|
require_once( ABSPATH . ‘wp-admin/includes/upgrade.php’ );
global $wpdb;
global $charset_collate;
// Call this manually as we may have missed the init hook
wptuts_register_activity_log_table();
|
Глобальный $charset_collate содержит набор символов и параметры сортировки, используемые родными таблицами WordPress. В общем, они определяют кодировки символов и то, как они сравниваются — учитывая, что WordPress используется во многих разных языках, важно использовать правильное сопоставление для вашей таблицы.
Помимо параметров сортировки, оператор SQL должен объявлять имя таблицы вместе с каждым столбцом, его тип и значение по умолчанию, а также любые столбцы KEY , включая столбец PRIMARY KEY . Обычно это будет иметь вид:
|
1
2
3
4
5
6
|
CREATE TABLE [table name] (
[primary key column] bigint(20) unsigned NOT NULL auto_increment,
[column name] [data type] [default],
PRIMARY KEY ([column name]) ,
KEY key_name ([column name])
) [collation];
|
Чтобы создать эту таблицу, мы добавляем следующее в нашу wptuts_create_tables() :
|
01
02
03
04
05
06
07
08
09
10
11
12
|
$sql_create_table = «CREATE TABLE {$wpdb->wptuts_activity_log} (
log_id bigint(20) unsigned NOT NULL auto_increment,
user_id bigint(20) unsigned NOT NULL default ‘0’,
activity varchar(20) NOT NULL default ‘updated’,
object_id bigint(20) unsigned NOT NULL default ‘0’,
object_type varchar(20) NOT NULL default ‘post’,
activity_date datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (log_id),
KEY user_id (user_id)
) $charset_collate;
dbDelta( $sql_create_table );
|
Функция dbDelta() выполняет нашу команду CREATE TABLE . Он может быть довольно строг в отношении данного ему SQL-выражения. Например, между PRIMARY KEY и столбцом первичного ключа должно быть два пробела. и ключам нужно дать имя.
Отладка
Если при активации вы обнаружите, что получаете сообщение об ошибке « У вас X символ неожиданного вывода … » — скорее всего, в вашем операторе SQL есть ошибка. Иногда это из-за dbDelta() . Если вы добавите wp_die(); после dbDelta() это убивает обработку и (с `WP_DEBUG` установленным в true) выдаст любые сообщения об ошибках.
Резюме
В этой статье мы рассмотрели причины, по которым вам следует и не следует использовать пользовательские таблицы, а также детали, которые вам необходимо учитывать, и, наконец, как создать таблицу. Следующая часть этой серии статей посвящена очистке, анализу SQL-инъекций и способам защиты от них. Код в этой статье доступен в этом репозитории GitHub и будет обновляться по мере продолжения серии.