 |
|
|
|
Реинкарнация данных
Реинкарнация данных Трудно представить себе предприятие или организацию, где не использовались бы базы данных для хранения информации. Это и списки инвентаризации основных средств, и базы абонентов – пользователей услуг предприятия, и т. п. На российских просторах все еще огромной популярностью пользуется формат DBF, в частности, именно в нем хранит свои таблицы старый добрый FoxPro. Тем не менее жизнь не стоит на месте, рано или поздно возникает вопрос о переносе всех накопленных данных на более функциональные СУБД. В данной статье рассматривается несколько способов осуществления такой миграции из FoxPro в БД PostgreSQL. Способы различаются как сложностью реализации (хотя это довольно субъективный критерий), так и степенью автоматизации. Естественно, перечень возможных реализаций никоим образом не ограничивается приведенным здесь. Итак, задача – перенести информацию, хранящуюся в формате DBF (СУБД FoxPro 2.6), в базу данных PostgreSQL (используемая версия сервера – 8.0.1, операционная система – FreeBSD 5.3). Прежде чем приступать к решениям, рассмотрим, что представляет собой DBF-файл. Формат DBF Формат DBF достаточно прост: 32 байта заголовка файла, «оглавление» таблицы, данные. В таблице представлен формат заголовка с кратким описанием каждого поля: Таблица 1. Структура заголовка DBF-файла
Далее следует оглавление таблицы – каждые 32 байта описывают одно поле данных (имя поля, тип данных, длину поля и т. д.). Таблица 2. Структура «оглавления» таблицы
«Оглавление» завершается символом 0x0D, после которого следуют записи данных. Самый первый байт каждой записи – признак удаления. Если запись удаляется командой delete, она физически остается в файле, а в поле признака удаления заносится символ 0x2A («*»). Такая запись считается помеченной на удаление и по умолчанию в операциях не участвует. Физическое удаление осуществляется только в результате так называемой упаковки (pack) таблицы данных. Таким образом, нужно помнить, что длина каждой записи будет определяться суммой длин полей плюс один байт. Импорт внешних файлов в PostgreSQL СУБД PostgreSQL позволяет осуществлять загрузку данных из внешних файлов и экспорт во внешние файлы с помощью команды COPY. Ее синтаксис следующий: serg=> h copy
Для загрузки данных из внешних файлов используется команда COPY FROM. Таблица tablename, в которую будет осуществляться загрузка, должна существовать и иметь поля, тип которых соответствует формату полей загружаемого текстового файла. Обратите внимание, что исходная таблица и таблица PostgreSQL не обязательно должны иметь одинаковую структуру – в процессе подготовки данных, как будет показано ниже, и количество и типы экспортируемых полей могут меняться. Например, в процессе экспорта исходное поле типа Date может быть записано в файл в виде «2005-04-04», что позволит в дальнейшем загрузить его также в поле даты. А может принять вид «4 апреля 2005 года», и его уже можно будет импортировать только в текстовое поле. Опции BINARY и OIDS в данном случае нас не интересуют – они могут быть полезны, когда и импорт, и экспорт выполняется в PostgreSQL. Текстовый формат загружаемого файла достаточно прост – каждая запись должна размещаться в одной строке, поля разделяются с помощью одиночного символа «delimiter». Если символ-разделитель встречается внутри поля данных, он должен экранироваться символом «». Одиночный символ «», встречающийся в данных, должен также экранироваться, чтобы исключить его специальную трактовку. Начиная с версии PostgreSQL 8.0 поддерживается также загрузка из CSV-файла, который является, по сути, файлом с разделителем (обычно – запятая), с той разницей, что поля данных для исключения неоднозначности при обработке символов могут заключаться в кавычки. Еще один параметр, NULL AS, задает символьную строку, которая будет рассматриваться как значение NULL. По умолчанию используется последовательность «N». Нужно заметить, что при работе в терминале psql доступны две команды: COPY и COPY. Первая – это SQL-команда, выполняемая сервером, вторая – команда интерактивного клиента. Разница между ними в правах, с которыми они выполняются. В первом случае возможности доступа определяются правами процесса postmaster, во втором – правами пользователя, запустившего psql. Обратите внимание, что если в процессе загрузки возникает ошибка (например, в какой-то строке не хватает поля или имеется лишнее, формат данных не соответствует типу поля, в которое осуществляется запись и т. д.), то ни одна из записей в итоговую таблицу не попадает, то есть импорт выполняется как единый транзакционный блок. Несколько слов о кодировках Как всегда, если речь идет о переносе данных между различными системами, встает вопрос о преобразовании используемых кодировок. FoxPro обычно хранит данные в cp866 (FoxPro for DOS) или в cp1251 (FoxPro for Windows). В то же время кодировка баз PostgreSQL может быть иной (у меня, например, это koi8-r). Решить эту проблему можно одним из трех способов:
Первые два способа особых сложностей вызвать не должны, поэтому в дальнейшем сосредоточимся на третьем, и будем осуществлять преобразование данных из cp866 в koi8-r в процессе переноса данных. Замечание о memo-полях FoxPro использует поля типа MEMO для хранения больших текстовых данных неопределенного размера во внешнем файле (обычно используется расширение fpt). Работа с этими полями имеет ряд особенностей, но о них поговорим в следующей части статьи, поэтому сейчас будем полагать, что экспортируемые таблицы полей такого типа не содержат. Путь 1: подготовка данных с помощью FoxPro СУБД FoxPro предоставляет пользователю достаточно мощный язык программирования, чем мы и воспользуемся. В данном разделе мы напишем несложную программу, которая будет формировать текстовый файл с разделителями, пригодный для импорта в PostgreSQL с помощью команды COPY. В качестве разделителя будем использовать символ табуляции. Листинг 1. Программа dbf2pg.prg на FoxPro * В FoxPro комментарии начинаются символом «*» * Устанавливаем формат даты SET DATE TO YMD SET CENTURY ON * Открываем таблицу для экспорта USE s_cats ALIAS tab * Определяем разделитель как символ табуляции m.delimiter = CHR(9) * Определяем строки для перекодировки в koi8 m.koiabcb = 'стўчфхЎ·щъыьэюяЁЄєЇїцшу■√¤ ∙°№рё' m.koiabcs = '┴┬╫╟─┼╓┌╔╩╦╠═╬╧╨╥╙╘╒╞╚├▐█▌▀┘╪▄└╤' m.koiabc = m.koiabcb + m.koiabcs m.dosabcb = 'АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ' m.dosabcs = 'абвгдежзийклмнопрстуфхцчшщъыьэюя' m.dosabc = m.dosabcb + m.dosabcs * Указываем кодировку m.dstcp = 'koi8' * Создаем результирующий файл и открываем его на запись (дескриптор – в переменной m.txf) m.txf = FCREATE('table.txt') * Делаем таблицу активной SELECT tab * Цикл по всем записям таблицы SCAN * Формируем строку, удаляя лишние пробелы m.temp = ALLTRIM(uniqueid) + m.delimiter + ; ALLTRIM(user) + m.delimiter + ; ALLTRIM(city) + m.delimiter + ; ALLTRIM(address) + m.delimiter + ; ALLTRIM(tariff) + m.delimiter + ; ALLTRIM(category) + m.delimiter + ; IIF(EMPTY(date), '\N', ; STRTRAN(DTOC(date), '/', '-')) * Если встречается табуляция – меняем на 4 пробела m.temp = STRTRAN(m.temp, CHR(9), SPACE(4)) IF m.dstcp == 'koi8' * Если в koi8, то используем свою функцию m.temp = dos2koi(m.temp) ELSE * иначе – пользуемся штатной функцией перекодировки m.temp = CPCONVERT(866, m.dstcp, m.temp) ENDIF * Экранируем символы '\' m.temp = STRTRAN(m.temp, '\', '\\') * Запись полученной строки в файл =FPUTS(m.txf, m.temp) ENDSCAN =FCLOSE(m.txf)
USE IN tab *=============================================== * Функция перекодировки из cp866 в koi8-r PROCEDURE dos2koi PARAMETER m.string m.res = '' FOR m.i = 1 TO LEN(m.string) m.pos = AT(SUBSTR(m.string, m.i, 1), m.dosabc) IF m.pos > 0 m.res = m.res + SUBSTR(m.koiabc, m.pos, 1) ELSE m.res = m.res + SUBSTR(m.string, m.i, 1) ENDIF ENDFOR RETURN m.res Все здесь достаточно просто – сканируем таблицу, для каждой записи формируем строку, содержащую данные, разделенные указанным в начале программы символом. Если в данных встречается символ табуляции – заменяем его четырьмя пробелами (не самое красивое решение, но вполне пригодное). Повозиться приходится с перекодировкой. Если PostgreSQL работает в cp1251, то для преобразования кириллицы можно воспользоваться штатной функцией FoxPro CPCONVERT. Перекодировать в koi8-r она, к сожалению, не умеет, поэтому здесь придется использовать самописную функцию dos2koi. Смысл ее в том, чтобы посимвольно заменить все, что входит в строку m.dosabc символами из m.koiabc, расположенными на тех же местах. Вообще, чтобы не возиться с перекодировкой силами FoxPro (к слову, приведенная выше функция будет работать очень медленно), можно сформировать файл в исходной кодировке, а затем пропустить его через внешний перекодировщик (когда-то мне попадался замечательный плагин к FAR). После того как текстовый файл сформирован, готовим таблицу в базе PostgreSQL и выполняем команду COPY: serg=> create table s_cats(uid char(10), catname char(35));
serg=> copy s_cats from "/usr/home/serg/test/psql/TABLE.TXT"
serg=> select * from s_cats;
К достоинствам рассмотренного способа можно отнести то, что на этапе подготовки файла мы можем выполнить любую предварительную обработку данных – разбить одно поле (например, «Абонент») на несколько («Фамилия», «Имя», «Отчество»), объединить несколько полей в одно, выбрать для экспорта только некоторые поля. Недостатки – достаточно большой объем ручной работы, поскольку программу dbf2pg.prg каждый раз нужно править под конкретные поля экспортируемой таблицы. Кроме того, обработку всего, что не касается данных в таблицах, FoxPro выполняет сравнительно медленно. Например, преобразование таблицы абонентов, содержащей 6 символьных полей и около 5000 записей, выполняется порядка 40 секунд. Путь 2: формирование файла с разделителями с помощью сценария на Python Если у вас нет под рукой FoxPro или вы с ним не очень дружны, то преобразование можно выполнить с помощью любого другого языка программирования, правда, в этом случае DBF-файл придется разбирать вручную, отыскивая поля данных в соответствии с информацией заголовка и «оглавления». В данном разделе мы воспользуемся языком Python, а в следующем аналогичные действия реализуем на Perl. Из информации заголовка (первые 32 байта) интерес для нас представляют только количество записей в таблице и смещение первой записи, чтобы не вычислять эти значения лишний раз. Сценарий будет выглядеть следующим образом: Листинг 2. Сценарий dbf2pg.py – ручной разбор файла DBF #!/usr/local/bin/python # Класс для хранения информации о полях таблицы class field: def __init__(self): self.name = '' self.type = 'C' self.len = 0 self.dec = 0 self.pos = 0 # Функция формирования файла с разделителями def dbf2pg(fname): # Открываем dbf-Файл в бинарном режиме dbf = open(fname, 'rb') # Считываем заголовок head = dbf.read(32) # Выбираем из заголовка число записей dblen = ord(head[7]) * 16777216 + ord(head[6]) * 65536 + ord(head[5]) * 256 + ord(head[4]) # и позицию первой записи posfirst = ord(head[9]) * 256 + ord(head[8]) wexit = 0 num = 0 totlen = 1 fields = [] while not wexit: # Читаем информацию о полях таблицы line = dbf.read(1) if ord(line) == 0x0D: # Если первый символ – 0x0D, то выход – «оглавление» закончено wexit = 1 else: # Иначе дочитываем до 32-х символов line = line + dbf.read(31) # Новый объект класса field fld = field() # Имя поля – сначала до первого символа 0x00 fld.name = line[:line.find('\0')] # Тип поля – 11-й байт fld.type = line[11] # Смещение поля данных от начала записи fld.pos = ord(line[15]) * 16777216 + ord(line[14]) * 65536 + ord(line[13]) * 256 + ord(line[12]) # Длина поля fld.len = ord(line[16]) # Длина дробной части fld.dec = ord(line[17]) # Заносим информацию в массив fields.append(fld) num = num + 1 totlen = totlen + fld.len if withcommand: # Если переменная истинна, то формируем команду на создание таблицы cmd = 'CREATE TABLE %s (' % tablename for i in range(num): fld = fields[i] cmd = cmd + fld.name + ' ' if fld.type == 'C': cmd = cmd + 'char(' + str(fld.len) + '), ' elif fld.type == 'D': cmd = cmd + 'date, ' elif fld.type == 'N' : cmd = cmd + 'numeric(' + str(fld.len) + ',' + str(fld.dec) + '), ' # Последнюю запятую и пробел меняем на закрывающую скобку и точку с запятой cmd = cmd[:-2] + ');' print cmd # Формируем команду чтения данных из потока ввода cmd = 'COPY users FROM stdin;' print cmd # Формируем сам поток ввода dbf.seek(posfirst) for rec in range(dblen): if dbf.read(1) != '*': # Если запись не помечена на удаление, то обработка: line = '' for i in range(num): # Цикл по всем полям fld = dbf.read(fields[i].len) # Если дата – преобразуем в формат YYYY-MM-DD из формата YYYYMMDD if fields[i].type == 'D': fld = fld[:4] + '-' + fld[4:6] + '-' + fld[6:] # Если дата пустая, меняем ее на значение NULL if fld == ' - - ': fld = '\N' # Для пустых числовых полей ставим значение 0 if fields[i].type == 'N' ї and fld[-1] == ' ': fld = '0' # Экранируем символ ‘\’, если таковой встречается в конце поля, т.к. он экранирует разделитель if fld[-1] == '\\': fld = fld + '\\' # Для строковых полей выполняем перекодировку if fields[i].type == 'C': fld = unicode(fld, 'cp866').encode('koi8-r') # Дописываем к строке записи через разделитель line = line + fld + delimiter # Выводим полученную строку, отрезав последний разделитель print line[:-1] else: # Если запись помечена на удаление, то просто дочитываем ее остаток, ничего не обрабатывая dbf.read(totlen - 1) dbf.close # Если формируются команды для \i, то заканчиваем данные строкой ‘\.’ if withcommand: print '\\.' # Устанавливаем настроечные переменные delimiter = "\t" withcommand = 1 tablename = 'users' # вызываем функцию преобразования dbf2pg('users.dbf') Вывод в данном случае направляется в стандартный поток stdout, поэтому для формирования файла следует использовать перенаправление: $ ./dbd2pg.py > USERS.TXT При переменной withcommand, установленной в 0, мы получаем такой же файл с разделителями, как и ранее с помощью FoxPro. Соответственно импортироваться в PostgreSQL информация будет аналогично. Однако обратите внимание, что теперь мы имеем всю информацию о полях данных, включая тип поля и его название. Так почему бы не воспользоваться этим для автоматического формирования таблицы PostgreSQL нужной структуры? Именно эта идея и реализуется, если переменной withcommand присвоить значение 1. При этом загрузка данных будет осуществляться по тому же принципу, какой используется при восстановлении из текстовой резервной копии: при выполнении команды «i файл» в терминале psql содержимое указанного файла будет рассматриваться как ввод с клавиатуры в интерактивном режиме. Таким образом, мы формируем команду CREATE TABLE в соответствии с информацией о названиях и типах полей таблицы, а затем команду COPY .. FROM stdin, которая будет считывать последующие данные, пока не обнаружит строку «.». Здесь следует обратить внимание на то, что имена полей должны быть допустимы в PostgreSQL. Так, например, имя USER, вполне нормальное для FoxPro, PostgreSQL рассматривает как служебное слово, не позволяя создавать таблицу с таким столбцом. Впрочем, это ограничение можно обойти, если имена полей заключить в кавычки – в таком случае любое имя будет восприниматься «как есть», правда, и в дальнейшей работе нужно будет использовать кавычки и строго соблюдать регистр символов. Еще одно замечание – теперь, поскольку таблицу мы создаем автоматически, на момент импорта она не должна существовать. Рассмотренный способ хотя и требует несколько больших усилий на разработку сценария, но заметно упрощает перенос данных, автоматизируя создание нужных таблиц. При этом работа идет заметно быстрее – на обработку той же таблицы абонентов тратится менее секунды. Путь 3: непосредственная запись в базу данных Рассмотренные выше способы так или иначе привязаны к команде COPY. А что, если мы не можем записать текстовый файл на диск в том месте, где он будет доступен процессу postmaster? Или нет доступа к клиенту psql? В этом случае можно непосредственно формировать команды INSERT серверу БД вместо создания промежуточных файлов. Конечно, это будет работать заметно медленнее, но в некоторых ситуациях такой путь может оказаться единственно возможным. Рассмотрим этот способ на примере. Для разнообразия воспользуемся языком Perl. Алгоритм разбора DBF-файла остался прежним, даже синтаксис похож. Только на этот раз нужно будет для каждой записи формировать команду INSERT и отсылать ее на сервер СУБД: Листинг 3. Сценарий dbf2pg.pl #!/usr/bin/perl -w # Подключаем модули для перекодировки и работы с СУБД use Lingua::RU::Charset qw(alt2koi); use DBI; # Устанавливаем соединение с БД $dbh = DBI->connect('dbi:Pg:dbname=serg', 'serg', ''); # Исходный файл и имя итоговой таблицы $fname = 'S_CATS.DBF'; $tabname = 's_cats_pl'; # Открываем файл DBF в бинарном режиме open(DBF, "$fname"); binmode(DBF); read(DBF, $head, 32); $dblen = ord(substr($head, 7, 1)) * 16777216 + ord(substr($head, 6, 1)) * 65536 + ord(substr($head, 5, 1)) * 256 + \ ord(substr($head, 4, 1)); $posfirst = ord(substr($head, 9, 1)) * 256 + ord(substr($head, 8, 1)); $wexit = 0; $num = 0; $totlen = 1; while(! $wexit) { read(DBF, $first, 1); if(ord($first) == 13) { $wexit = 1; } else { # Далее читаем файл сразу в переменные read(DBF, $name, 10); $name = $first . $name; # Вырезаем из имени символы 0x00 (здесь ^@ - не два символа, просто 0x00 так выглядит в vi) $name =~ s/^@//g; read(DBF, $type, 1); read(DBF, $tmp, 4); read(DBF, $tmp, 1); $len = ord($tmp); read(DBF, $tmp, 1); $dec = ord($tmp); read(DBF, $tmp, 14); $fields[$num]{name} = $name; $fields[$num]{type} = $type; $fields[$num]{len} = $len; $fields[$num]{dec} = $dec; $num++; $totlen += $len; } } # Формируем команду создания таблицы $sqlcommand = "CREATE TABLE $tabname ("; for($i = 0; $i < $num; $i++) { $sqlcommand .= $fields[$i]{name} . ' '; if($fields[$i]{type} eq 'C') { $tmp = 'char(' . $fields[$i]{len}; } elsif($fields[$i]{type} eq 'D') { $tmp = 'date'; } elsif($fields[$i]{type} eq 'N') { $tmp = 'numeric(' . $fields[$i]{len} . ',' . $fields[$i]{dec}; } $sqlcommand .= $tmp . '), '; } $sqlcommand = substr($sqlcommand, 0, length($sqlcommand) - 2); $sqlcommand .= ');'; # Отправляем ее на сервер $sth = $dbh->prepare($sqlcommand); $sth->execute; # Переход на начало данных seek(DBF, $posfirst, 0); for($rec = 0; $rec < $dblen; $rec++) { read(DBF, $first, 1); if($first ne '*') { $sqlcommand = "INSERT INTO $tabname VALUES ("; for($i = 0; $i < $num; $i++) { read(DBF, $fld, $fields[$i]{len}); if($fields[$i]{type} eq 'C') { ($fld) = &alt2koi($fld); $fld = "'" . $fld . "'"; } else { $fld =~ s/\s//g; } if($fields[$i]{type} eq 'D') { if($fld ne '') { $fld = '"' . substr($fld, 0, 4) . '-' . substr($fld, 4, 2) . '-' . substr($fld, 6, 2) . '"'; } else { $fld = 'NULL'; } } if(($fields[$i]{type} eq 'N') && ($fld eq '')) { $fld = '0'; } $sqlcommand .= $fld . ', '; } $sqlcommand = substr($sqlcommand, 0, length($sqlcommand) - 2); $sqlcommand .= ');'; # Отправляем команду INSERT $sth = $dbh->prepare($sqlcommand); $sth->execute; } else { read(DBF, $tmp, 31); } } close(DBF); $dbh->disconnect; Рассмотренный способ универсален как в плане доступа к базе данных (достаточно иметь возможность отправлять на сервер SQL-команды), так и в плане использования конкретной СУБД. При минимальных синтаксических изменениях данный сценарий можно использовать для работы практически с любой системой управления базами данных – MySQL, Oralce и т. д. К тому же в данном случае каждая запись переносится отдельно от других, и если при этом возникнет ошибка, на импорт других записей это никак не повлияет. Правда, если предъявляются высокие требования к целостности данных, то подход «все или ничего» более предпочтителен. За указанные удобства приходится расплачиваться быстродействием – та же тестовая таблица из 5000 записей переносится почти одну минуту. Завершение Итак, мы рассмотрели несколько способов переноса данных из формата DBF в таблицу PostgreSQL. Каждый из них имеет свои особенности, и я надеюсь, вам удастся найти тот, который лучше всего подойдет именно к вашим условиям. |
СЕРГЕЙ СУПРУНОВКомментарии отсутствуют
| Добавить комментарий |
|
Комментарии могут оставлять только зарегистрированные пользователи |
|
