Организация базы данных

Mar 16, 01:52 pm Категория:

В публикации про роутер я не писал, что он по сути является частью абстракции контроллер и что вызываемые из него контроллеры не являеются тем, чем кажутся. А сейчас я напишу о базе данных и тоже не предупрежу, что она не является моделью. И не напишу, что строка кода use Illuminate\Database\Eloquent\Model; – это не совсем та модель, что думает начинающий кодер. И что в MVC модель не класс или объект, а слой абстракции, содержащий в себе бизнес-логику, проверку данных, хранение и поиск данных я тоже умолчу. Я много про что не напишу, т.к. сейчас я хочу просто перенести данные из Textpattern и не хочу заморачиваться на понимаение места миграций и классов в общей картине мира.

База данных Textpattern не очень сложна для понимания. Вот скриншот какого-то произвольного инстанса:

С помощью любого клиента, поддерживающего MySQL, можно посмотреть структуру таблиц и “покурив” код понять, что, куда и для чего. Но есть способ лучше: перечень и состав таблиц базы данных TxP. С помощью этой нужной ссылки можно легко отделить таблицы плагинов от таблиц самого TxP и узнать, какие поля что содержат. Даже скучно. Точнее – обидно. Обидно, что вместо поиска структуры базы данных я занимался её обратной разработкой. В качестве постижения дао продукта методика отличная, но зачем было постигать то, от чего отказываешься?

Перед началом переноса данных советую вспомнить, что делали сторонние дополнения и зачем. Если ничего нужного, то про таблицы плагинов можно забыть. Если с их помощью была реализована какая-то бизнес-логика, то предстоит вспомнить, формализовать и реализовать в виде связей с таблицами Laravel и добавить в магию кода нужные буквы. Теперь можно переходить и к основным таблицам.

Сейчас будет грустно: писать универсальный инструмент для автоматического переноса сайта c TxP не имеет смысла. Сайтов таких мало, новых не делают, а скоро и совсем не останется. Быстрее и эффективнее модифицировать заготовку скрипта под каждый конкретный случай. Алгоритм примерно такой:

  • Определиться со списком и составом переносимых данных;
  • Спроектировать базу данных нового сайта;
  • Определить источник и цель данных;
  • Написать функции преобразования исходных данных:
  • Написать функции генерации отсутствующих данных.

Самый сложный вопрос, это степень модификации сайта: дизайн, навигация, данные, сценарии использования и т.п. Если сайт не является памятником сетевой архитектуры, то, скорее всего, его нужно будет серьёзно перелопатить внешне. Не все исходные данные потребуется переносить, а что-то, наоборот, нужно будет создать.

Для примера возьмём систему комментирования. У TxP она состоит из 3-х таблиц: комментарии, блокировки, антиспам: txp_discuss, txp_discuss_ipban и txp_discuss_nonce. Последняя таблица не нужна. Вторая таблица – это тюрьма, где содержится пара имя/ip заблокированного пользователя, а так же дата блокировки и на какой статье нежданчик случился. Нужно? Место в новой базе зарезервировано? Переносим. Первая таблица – это и есть “каменты”: уникальный id, id статьи, статус открыт/закрыт, ip с которого комментарий оставлен и поля, которые пользователь заполнял, имя, email, сайт и текст комментария. Все поля будут использоваться? Поля будут использоваться в том же виде/формате, как и в исходной таблице? Возьмём, к примеру, видимость комментария: 0/1 в исходнике и, возможно, “ждёт”/“виден”/“срыт”/“удалён” в новой базе. Отвечаем на вопросы и переносим.

Поехали

Список таблиц, которые я лично планирую проверять на наличие данных и что я из них буду брать:

  • textpattern – основная таблица, содержащая статьи, теги, категории и часть бизнес-логики;
  • txp_category – таблица категорий статей, файлов, ссылок и картинок;
  • txp_discuss – комментарии к статьям;
  • txp_image – таблица изображений;
  • txp_section – секции сайта;
  • txp_users – таблица пользователей.

txp_users

CREATE TABLE IF NOT EXISTS `txp_users` (
  `user_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `pass` varchar(128) NOT NULL,
  `RealName` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `privs` tinyint(2) NOT NULL DEFAULT '1',
  `last_access` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `nonce` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

В этой таблице мне могут понадобиться только name, RealName и email в виде данных, а user_id и privs для определения авторства и роли. Этого вполне достаточно для создания новых пользователей и разрешений для них. Ну а восстановить пароли пользователи смогут сами. И это при условии, что пользователей хотя бы несколько десятков. Если их меньше пяти, то можно и руками создать или подключить механизм регистрации.

txp_section

CREATE TABLE IF NOT EXISTS `txp_section` (
  `name` varchar(128) NOT NULL,
  `page` varchar(128) NOT NULL DEFAULT '',
  `css` varchar(128) NOT NULL DEFAULT '',
  `is_default` int(2) NOT NULL DEFAULT '0',
  `in_rss` int(2) NOT NULL DEFAULT '1',
  `on_frontpage` int(2) NOT NULL DEFAULT '1',
  `searchable` int(2) NOT NULL DEFAULT '1',
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

В таблице секций мне нужны поля name и title. Остальные поля содержат абсолютно ненужные на новом сайте данные: какой шаблон страницы использовать, какие стили подключать, использовать секцию по умолчанию, выдавать в rss, показывать статьи секции на главной странице, включать секцию в поиск по сайту. Информация важная, но не сейчас.

txp_image

CREATE TABLE IF NOT EXISTS `txp_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `category` varchar(255) NOT NULL DEFAULT '',
  `ext` varchar(20) NOT NULL DEFAULT '',
  `w` int(8) NOT NULL DEFAULT '0',
  `h` int(8) NOT NULL DEFAULT '0',
  `alt` varchar(255) NOT NULL DEFAULT '',
  `caption` text NOT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `author` varchar(255) NOT NULL DEFAULT '',
  `thumbnail` int(2) NOT NULL DEFAULT '0',
  `thumb_w` int(8) NOT NULL DEFAULT '0',
  `thumb_h` int(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `author_idx` (`author`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;

Эту таблицу я сначала не хотел экспортировать. Имена хранимых на диске файлов соответствуют id в таблице и в минимальном виде этого хватает. Но, если подумать, превью, размеры, подписи, исходное имя изображения – всё это может пригодиться и практически без изменений может быть перенесено.

txp_discuss

CREATE TABLE IF NOT EXISTS `txp_discuss` (
  `discussid` int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `parentid` int(8) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(50) NOT NULL DEFAULT '',
  `web` varchar(255) NOT NULL DEFAULT '',
  `ip` varchar(100) NOT NULL DEFAULT '',
  `posted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `message` text NOT NULL,
  `visible` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`discussid`),
  KEY `parentid` (`parentid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

Выше я разбирал, что находится в этой таблице. Единственный вопрос: в какой вид преобразовывать статус комментария? Использование 0/1 мне не очень нравится, т.к. статусов комментариев может быть больше. А может и не быть.

txp_category

CREATE TABLE IF NOT EXISTS `txp_category` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `type` varchar(64) NOT NULL DEFAULT '',
  `parent` varchar(64) NOT NULL DEFAULT '',
  `lft` int(6) NOT NULL DEFAULT '0',
  `rgt` int(6) NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

Таблица категорий – самое загадочное место TxP. На первый взгляд ясно, что есть 4 типа медиа-объектов и, соответственно, категории тоже бывают 4-х типов. Кроме того, категория имеет имя и наименование для URL. Не вызывают вопросов поля id и parent, идентификатор и родительская категория. Но ввиду того, что типов категорий используется несколько, в иерархии используются не индексы, а имена для URL, причём для корневой используется имя root, которое из интерфейса не видно, а в поле parent содержится пустая строка. Вроде всё есть для построения иерархии. Пусть и не самым оптимальным способом, но зато на операциях вставки/удаления он хорош. И вот тут обнаруживаются два поля, содержащие данные для обеспечения работы алгоритма MPTT, который строит дерево гораздо оптимальнее, только вот обновлять дерево в нём не так удобно. А параллельное использование обоих способов не даёт никаких выигрышей, зато проигрыши даёт оба. Вот зачем? А, да, отсюда я буду брать только имена категорий статей. Остальное обычно не использую.

textpattern

CREATE TABLE IF NOT EXISTS `textpattern` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Posted` datetime NOT NULL DEFAULT '0000-00-00 00 :00 :00',
  `Expires` datetime NOT NULL DEFAULT '0000-00-00 00 :00:00',
  `AuthorID` varchar(64) NOT NULL DEFAULT '',
  `LastMod` datetime NOT NULL DEFAULT '0000-00-00 00 :00 :00',
  `LastModID` varchar(64) NOT NULL DEFAULT '',
  `Title` varchar(255) NOT NULL DEFAULT '',
  `Title_html` varchar(255) NOT NULL DEFAULT '',
  `Body` mediumtext NOT NULL,
  `Body_html` mediumtext NOT NULL,
  `Excerpt` text NOT NULL,
  `Excerpt_html` mediumtext NOT NULL,
  `Image` varchar(255) NOT NULL DEFAULT '',
  `Category1` varchar(128) NOT NULL DEFAULT '',
  `Category2` varchar(128) NOT NULL DEFAULT '',
  `Annotate` int(2) NOT NULL DEFAULT '0',
  `AnnotateInvite` varchar(255) NOT NULL DEFAULT '',
  `comments_count` int(8) NOT NULL DEFAULT '0',
  `Status` int(2) NOT NULL DEFAULT '4',
  `textile_body` int(2) NOT NULL DEFAULT '1',
  `textile_excerpt` int(2) NOT NULL DEFAULT '1',
  `Section` varchar(64) NOT NULL DEFAULT '',
  `override_form` varchar(255) NOT NULL DEFAULT '',
  `Keywords` varchar(255) NOT NULL DEFAULT '',
  `url_title` varchar(255) NOT NULL DEFAULT '',
  `custom_1` varchar(255) NOT NULL DEFAULT '',
  `custom_2` varchar(255) NOT NULL DEFAULT '',
  `custom_3` varchar(255) NOT NULL DEFAULT '',
  `custom_4` varchar(255) NOT NULL DEFAULT '',
  `custom_5` varchar(255) NOT NULL DEFAULT '',
  `custom_6` varchar(255) NOT NULL DEFAULT '',
  `custom_7` varchar(255) NOT NULL DEFAULT '',
  `custom_8` varchar(255) NOT NULL DEFAULT '',
  `custom_9` varchar(255) NOT NULL DEFAULT '',
  `custom_10` varchar(255) NOT NULL DEFAULT '',
  `uid` varchar(32) NOT NULL DEFAULT '',
  `feed_time` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`ID`),
  KEY `categories_idx` (`Category1`(10),`Category2`(10)),
  KEY `Posted` (`Posted`),
  KEY `section_status_idx` (`Section`,`Status`),
  KEY `Expires_idx` (`Expires`),
  KEY `author_idx` (`AuthorID`),
  FULLTEXT KEY `searching` (`Title`,`Body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

Это самая главная таблица. В некоторых случаях можно обойтись только ей. Классический блог с одним автором и облачными комментариями – первый кандидат на “упрощёнку”. Разбираемся.

ID – идентификатор, который часто используется в URL статей и в режиме messy (беспорядок). Если индекс не используется в статье, то можно его не экспортировать. Posted, Expires, LastMod – время публикации, “протухания” и последней редакции. TxP используется datetime, Laravel timestamp – нужно учитывать при переносе. AuthorID, LastModID – автор публикации и автор последней правки. Используется не iser_id, а name из таблицы txp_users. Поэтому я писал выше, что пользователей можно не переносить, а руками добавить, а потом статьи связать с нормальным id пользователя.

Теперь контент: Title, Body, Excerpt представлены в двух ипостасях, как написано здесь и с суффиксом _html. Это исходная версия заголовка, тела и выдержки статьи и преобразованная в html. Можно взять только html вариант и с ним работать с помощью какого-нибудь визуального редактора. Но можно взять обе версии, прикрутить к Laravel textile и быть реально крутым. Разумеется, если в textile_body и textile_excerpt будет единичка. Если нет, то значит исходники либо простой текст, либо уже готовый html, либо одно из трёх и решайте сами, как это побороть. Лучше соглашайтесь только на html.

Немного осталось. Image – номер картинки в… текстовом виде. Расширения нет и если картинки публиковались в png, gif и jpg с захватом jpeg, то есть смысл предварительно обрабатывать таблицу с картинками. Category1 и Category2 – это имя категории, используемое в URL, два штука. Annotate и AnnotateInvite – разрешено ли комментирование статьи и какое приглашение получает пользователь, чтобы оставить comments_count – количество комментариев к статье. Status – статус статьи. Сразу скажу, что 4 – это активная статья, а 1 – черновик. Остальные статусы в детальном описании базы.

Последний рывок. Section – секция. Не путать с категориями. Часто секции используются для создания статических страниц, которые нужно переносить в особом порядке или не переносить. Keywords – ключевые слова или теги. Список разделённых запятой слов и выражений в нихнем регистре. Извлечь, разделить, подумать и записать в таблицу tags в Laravel, связав через pivot со статьями. Такая идея. Ну и очень важное поле url_title – это так называемые ЧПУ (человеко-понятные урлы). Остался десяток полей вида custom_X – эти поля использовались для хранения дополнительных данных, для организации дополнительной бизнес-логики, в качестве фильтров и ещё много для чего. Использовали? Вот и выкручивайтесь. Остальные поля этой таблицы мало кому нужны за пределами TxP.

Это была программа минимум. Для активных и развесистых сайтов могут потребоваться таблицы:

  • txp_discuss_ipban – “баня”;
  • txp_file – файлы:
  • txp_link – ссылки.

Какие-то данные могут храниться в таблицах плагинов: подписчики на какие-нибудь рассылки, отправленные твиты и т.п. Я не буду описывать необязательную программу. Итак уже написал в полтора раза больше, чем планировал, а ещё не добрался и до середины. Никуда не уходите, я скоро продолжу.

Теги этой статьи: ,

 

Комментарии

2017-12-11 1:17 am , Оставь комментарий