Как можно догадаться из названия, речь в данной статье пойдет об операциях обмена данными между различными базами данных. В большинстве случаев целью ставится именно импорт данных, например из MySQL в Oracle. Однако поскольку напрямую, т.е. без некоторых посредников, подобная операция практически неосуществима, получаем предварительную операцию – экспорт. Откуда и получаем связку экспорт-импорт. Для начала попробуем выделить несколько основных типов таких операций, а потом – осветить основные проблемы, которые необходимо решить.

Наиболее часто в программировании для Интернет приходится решать задачи двух видов: обмен данными между двумя SQL-основанными базами данных и обмен между не-SQL (MSExcel) и SQL системами. Для каждого из этих видов экспорта-импорта можно выделить две разные задачи – начальная инициализация и регулярный обмен (обновление/дополнение прайс-листов). В общем, цели определены, задачи поставлены…

Экспорт-импорт между MS Excel и SQL базами данных.
Данная операция является, наверное, самой распространенной. Очевидно, что прямой импорт из xls-файла путем его побайтового разбора – самый надежный с точки зрения точности преобразования информации, но в то же время чрезвычайно трудоемкий. Наиболее простым представляется вариант экспорта из формата Excel в текстовый файл с разделителями (точка с запятой, табуляция) и последующим чтением из файла посредством некоего скриптового языка, разбором строки «по составу» и последующим выполнением операция INSERT. В этом случае программисту приходится решать ряд проблем, сложность которых возрастает с увеличением объема и частоты операций. Точнее, увеличивается необходимость автоматизации этих процессов.

Одной из основных проблем является «человеческий фактор» пользователя, поставляющего файл для импорта, который не читает инструкцию по созданию этого файла. Для корректной заливки информации из файла оно должна быть шаблонизирована: строго определенное количество колонок в определенном формате, плюс наличие ряда запрещенных символов (например символов, являющихся разделителями). Объяснить эти правила одному пользователю и то достаточно сложно, а уж если их несколько – это практически невозможно.

Второй крупной проблемой является высокое самомнение MS Excel, который производит экспорт в текст как ему в данный момент захочется (вообще-то – в зависимости от форматов полей, но поскольку форматов этих – великое множество и на взгляд не всегда можно определить какой это формат, то …). Так, например, он очень любит самовольно расставлять двойные кавычки (алгоритма расстановки пока уловить не удалось). Еще следует заметить, что преобразование происходит в соответствии с текущими национальными установками, что самым пагубным образом отражается на представлении полей типа Дата и чисел с плавающей точкой.

И, наконец, следует упомянуть еще об одной особенности Excel – при импорте в текст с разделителями он может присоединить к строчке поля, которые визуально кажутся пустыми, но в которых когда-то давно была информация. Самым надежным методом является следующий – выделить ТОЛЬКО смысловую часть, скопировать ее и вставить в новый файл, из которого и производить затем импорт.

Как же со всем этим бороться? Есть два пути – воспитывать пользователя и воспитывать скрипт, который читает файл загоняет его в базу данных. Если импортеров больше одного, то почти наверняка наиболее удобной окажется схема, когда пользователь представляет файл в формате Excel, а заливает его после предварительной визуальной проверки уже администратор. Во всяком случае, администратору намного проще преобразовать даты к необходимому формату, чем писать скрипт с проверкой всех возможных вариантов. Во-вторых, администратору много проще разобраться в ошибках, которые выдает программа заливки, и, возможно, самостоятельно их исправить и повторить попытку импорта. Или подробно и популярно объяснить пользователю, КАК именно нужно заполнять шаблоны.

«Воспитанный» скрипт заливки как минимум должен выдавать номера строк, которые он не смог добавить, и общее количество обработанных/добавленных строк. Как правило, эти строки просто не принимает база данных. Такая ситуация может быть следствием нескольких причин: появление лишнего символа разделителя, в результате чего может получиться самая непредсказуемая строка, или наличие символа возврата каретки в тексте одного из полей, и последняя – несоответствие формата поля формату в БД. С первой и второй проблемами приходится бороться скорей всего именно администратору – выделением «чисто информации» и проверкой, чтобы все необходимые поля были заполнены (хотя бы значениями по умолчанию). Можно еще попробовать написать свой макрос, выполняющий экспорт в текст и использующий некие хитрые разделители полей и строк. Преобразование скриптом форматов полей достаточно просто осуществляется только для числовых полей (удаление лишних разделителей групп и замена запятой на точку).

Для импорта инициализации частенько можно использовать специальные команды (LOAD DATA INFILE в MySQL) или внешние программы импорта (SQL Loader в Oracle). Следует помнить, что такие инструменты работают только с файлами, находящимися на том же сервере (железном), что и  сервер баз данных. Плюс ко всему, возможны всяки неудобства с доступом к файлам (в Unix-системах). Для регулярного импорта указанные средства также можно применять, только они несколько менее информативны при описании ошибок, да и преобразования форматов в них или отсутствует напрочь, или весьма незначительно.

Способом, альтернативным использованию текстовых файлов, является использование ODBC, когда и создается специальная программа, использующая для чтения из файла Excel соответствующий драйвер ODBC. Для записи в SQL базу можно использовать как «родной» драйвер, так и то же ODBC. Преимущества того или иного метода пока не выявлены, но можно предположить, что использование ODBC предпочтительней (более полное соответствие форматов).

Основным преимуществом данного подхода является минимальная несовместимость форматов полей и отсутствие проблем с разделителями и переводами строки. К области недостатков можно отнести то, что подобная система скорей всего будет корректно работать только под Windows. Хотя, если честно, у автора нет отрицательного опыта работы с ODBC под Unix, равно как и положительного. С другой стороны, под Windows достаточно просто реализовать систему с развитым визуальным интерфейсом экспорта-импорта с возможностями редактирования данных, или использовать уже существующие инструменты типа Access. Поклонников же ASP/IIS этот момент вообще не остановит. Им можно даже посоветовать не обращать внимания на все выше изложенное про текстовые файлы.

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

Обмен между разными SQL базами данных.
Сразу оговорюсь, что обмен данными между двумя отдельно действующими базами данных не является предметом рассмотрения в данной статье, поскольку эта тема сама по себе более чем значительная и относится к области распределенных баз данных. Мы же рассмотрим ситуацию, возникающую при переходе от одной базы данных к другой, с сохранением функциональности скриптов, работающих с этими данными. Т.е. расмотрим вариант, когда необходимо инициализировать новую БД данными из старой. Чаще всего происходит переход от менее мощной СУБД к более мощной. В качестве крайнего случая можно привести пример перехода MySQL – Oracle. Обратный же переход представляется менее вероятным, не смотря на то, что NASA именно такой вариант и осуществило. Такой переход, если он вообще будет возможен, означает, что СУБД изначально была выбрана неправильно.

Итак, с какими же проблемами мы сталкиваемся в этом случае? Если использовать в качестве промежуточного хранилища текстовые файлы, то получаем приблизительно тот же вариант, что и при экспорте в Excel. Плюс ряд проблем, связанных именно с тем, что осуществляется импорт целой базы данных (схемы данных), в общем случае предполагающей наличие некоторых связей между таблицами.

Различные СУБД в области хранения данных отличаются друг от друга по трем основным показателям (способам хранения данных определенного типа): хранение даты/времени, хранение больших объектов (BLOB), и способ формирования/хранения ключевых полей. Ну, для борьбы с датой сложно придумать что-либо более умное, чем ручная конвертация в скрипте. Или при использовании текстового файла и внешней программы импорта можно задать формат конвертирования данного поля в дату (Oracle SQL Loader). Работа с большими объектами может создать проблемы не столько на уровне непосредственно экспорта-импорта, сколько при переделке скриптов, работающих с этими данными – эти функции обычно отличаются наиболее сильно.

Наиболее важно корректно провести экспорт именно ключевых полей, связывающих таблицы. Для автоматического формирования ключевых значений различные СУБД чаще всего используют одну из двух технологий. В первом варианте полю таблицы присваивается свойство, указывающее на необходимость автоматической генерации уникального значения для этого поля при вставке новой строки (MySQL, MS SQL, Sybase). Во втором случае поле объявляется как первичный ключ (т.е. все значения уникальны), а само значение берется из последовательности (sequence), которая гарантирует генерацию уникальных значений (Oracle, PostrgeSQL). Совмещение механизма триггеров и последовательностей позволяет создать схему, эквивалентную первому варианту по простоте использования (программист в общем случае также не заботится о формировании первичных ключей), и в то же время более гибкую.

При переносе связанных таблиц необходимо решить две задачи: сохранение собственно связности таблиц и обеспечение корректного формирования ключей впоследствии. Если хочет сделать «как проще», то в схеме с использованием последовательностей достаточно импортировать таблицы в правильном порядке – главная-подробности – дабы избежать ошибок по нарушению правила foreign key, а потом просто задать для последовательностей точку отсчета, равную максимальному значению текущего ключа. Однако желательно было бы все-таки доверить формирование ключей внутренним механизмам СУБД, тем более что описанный ниже метод, точнее два метода, являются общими для всех типов формирования первичных ключей.

Метод 1.

    Выполнить выборку из главной таблицы.
    Для каждой строчки выборки выполнить вставку в новую таблицу (в другой БД).
    Запомнить полученный ключ.
    Для каждой связанной таблицы выполнить выборку по старому первичному ключу и вставку с использованием нового.

Основными недостатками этого метода являются абсолютно жесткая привязка к данной схеме базы данных (т.е. применить готовые механизмы без основательной переделки для другой структуры весьма проблематично) и значительное усложнение скрипта импорта при уровне связности таблиц больше двух (Т1 связана с Т2, которая в свою очередь связана с Т3), получаем множество вложенных циклов. К тому же, данный метод сложно применять при использовании в качестве промежуточного этапа текстовые файлы. В этом случае приходится делать выборки, объединяющие все связанные таблицы и потом «разбрасывать» части строк по разным таблицам. В качестве преимущества метода можно указать то, что структура таблиц до и после импорта остается неизменной, в отличие от метода 2.

Метод 2. В основу данного метода положено создание временных столбцов для хранения старых ключей и использование механизма триггеров. В главной таблице создается поле Старый_Первичный_Ключ, а в подчиненной – Старый_Внешний_Ключ. Сначала вставляется главная таблица. Для каждой подчиненной таблицы пишется триггер, который проверяет, заполнено ли поле внешнего ключа. Если нет – производится поиск ключа в главной таблице по старым значениям ключа и подставляется найденное значение. При больших объемах данных для ускорения процесса имеет смысл создать индексы по полю Старый_Первичный_Ключ. По завершении импорта все служебные структуры удаляются.

Очевидно, что данный метод лишен основных недостатков предыдущего. Он позволяет работать с каждой таблицей по отдельности, что, в свою очередь, позволяет достаточно просто и эффективно использовать текстовые файлы и внешние инструменты для работы с ними. К тому же, алгоритм связывания двух таблиц аналогичен на любом уровне вложенности и включает в себя не более двух таблиц.

Заключение.
Во последних строках своего опуса хочу заметить, что переход с одного сервера баз данных на другой ради смены самого сервера, без значительной переделки среднего уровня всей архитектуры – скриптов, - не имеет особого смысла. Во-первых, скрипты переделывать все равно придется, потому как функции работы с базами данных (у того же PHP например) весьма и весьма различны. Во-вторых, при переходе типа MySQL-Oracle не использовать хотя-бы некоторые основные механизмы последнего, типа триггеров, встроенных процедур и транзакций – по крайней мере не разумно. Думаю, силы и деньги, потраченные на переход на другую СУБД, можно с большей пользой потратить на тюнинг текущей конфигурации (например, разделение данных на часто используемые и редко используемые на две таблицы с одинаковой структурой таким образом, чтобы «частая» таблица была значительно меньше по размеру и соответственно быстрей обрабатывалась).