понедельник, июня 18, 2007

Миграция с MySQL 5.X на Oracle 10.X с обратной совместимостью на платформе php

Этая серия статей направлена на помощь при миграция с MySQL 5.X на Oracle 10.X на платформе php для разработчиков, которые еще не сталкивались СУБД Oracle. Она была написана в ходе собственных проб и ошибок при переносе данных в 700 Мб.
Данную серию статей рассматривайте как рекомендации для новичков, естесственно в них есть удачные и не удачные решения. И тем не мение процесс миграции прошел успешно с поддержкой двух СУБД.

Содержание:

  1. Установка и настройка Oracle
  2. Разработка ПО на php под Oracle 10.X и MySQL
  3. Перенос данных с Mysql на Oracle
Автор: Kirill Danilov на 14:42:00 0 коммент.

воскресенье, июня 17, 2007

Установка и настройка Oracle Client 10.X

Другие статьи:

  1. Установка и настройка Oracle
  2. Разработка ПО на php под Oracle 10.X и MySQL
  3. Перенос данных с Mysql на Oracle
Автор: Kirill Danilov на 15:00:00 0 коммент.
Тэги: , , ,

суббота, июня 16, 2007

Разработка ПО на php под Oracle 10.X и MySQL

Даты, время

Используйте CURRENT_TIMESTAMP для совместимости с MySQL вместо NOW(), SYSDATE().


Oracle> select CURRENT_TIMESTAMP from dual;
CURRENT_TIMESTAMP
------------------------------------------
14-JUN-07 02.47.52.987970 PM +04:00


mysql> select CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2007-06-14 14:58:23 |
+---------------------+
1 row in set (0.00 sec)

Установите стандартное время для MySQL в формате 'YYYY-MM-DD HH24:MI:SS'
Oracle> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.

Старайтесь выкручиваться разными приёмами:
mysql> select hour(timediff(now(), t_timestamp)) from table1;
Oracle> select CEIL(SYSDATE - t_timestamp) from table1;

если неудаётся, то ...
Создайте недостающие функции:

FIND_IN_SET
CREATE OR REPLACE FUNCTION
FIND_IN_SET(TWHAT VARCHAR2, TWHERE VARCHAR2) RETURN INTEGER
IS
BEGIN
DECLARE
CDATA varchar2(4000);
CURSOR TDATA IS
SELECT REGEXP_REPLACE(REGEXP_SUBSTR(TWHERE, ','||TWHAT||',|^'||TWHAT||',|^'||TWHAT||'$|,'||TWHAT||'$'),',','') FROM DUAL;
FROM DUAL;
BEGIN
open TDATA;
FETCH TDATA into CDATA;
close TDATA;
IF (TO_CHAR(CDATA) = TWHAT) THEN
return 1;
ELSE
return 0;
END IF;
END;
END FIND_IN_SET;
/


Тесты:

Oracle> SELECT table1.field1 from table1
WHERE find_in_set('5',table1.field1) >= 1;

table1.field1
-------------
',5,1,2,3,4,'

mysql> SELECT table1.field1 from table1
WHERE find_in_set('5',table1.field1) >= 1;

+---------------+
| table1.field1 |
+---------------+
| ,5,1,2,3,4, |
+---------------+
1 row in set (0.00 sec)


CONCAT_WS

Oracle> CREATE OR REPLACE FUNCTION
CONCAT_WS(PATTERN VARCHAR2,
STR1 VARCHAR2 DEFAULT '',
STR2 VARCHAR2 DEFAULT '',
STR3 VARCHAR2 DEFAULT '',
STR4 VARCHAR2 DEFAULT '',
STR5 VARCHAR2 DEFAULT '',
STR6 VARCHAR2 DEFAULT '',
STR7 VARCHAR2 DEFAULT '',
STR8 VARCHAR2 DEFAULT '',
STR9 VARCHAR2 DEFAULT '',
STR10 VARCHAR2 DEFAULT '',
STR11 VARCHAR2 DEFAULT '',
STR12 VARCHAR2 DEFAULT ''
) RETURN VARCHAR2
IS
BEGIN
DECLARE
CDATA varchar2(4000):='';
BEGIN
IF (LENGTH(STR1) > 0) THEN
CDATA := STR1;
END IF;
IF (LENGTH(STR2) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR2);
END IF;
IF (LENGTH(STR3) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR3);
END IF;
IF (LENGTH(STR4) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR4);
END IF;
IF (LENGTH(STR5) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR5);
END IF;
IF (LENGTH(STR6) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR6);
END IF;
IF (LENGTH(STR7) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR7);
END IF;
IF (LENGTH(STR8) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR8);
END IF;
IF (LENGTH(STR9) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR9);
END IF;
IF (LENGTH(STR10) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR10);
END IF;
IF (LENGTH(STR11) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR11);
END IF;
IF (LENGTH(STR12) > 0) THEN
CDATA := CONCAT(CDATA,PATTERN);
CDATA := CONCAT(CDATA,STR12);
END IF;
return CDATA;
END;
END;
/

Function created.

Тесты:

Oracle> select concat_ws('::','1','2') from dual;
CONCAT_WS('::','1','2')
------------------------------------------------
1::2

Oracle> select concat_ws('::','1') from dual;
CONCAT_WS('::','1')
------------------------------------------------
1

mysql> select concat_ws('::','1','2');
+-------------------------+
| concat_ws('::','1','2') |
+-------------------------+
| 1::2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('::','1');
+---------------------+
| concat_ws('::','1') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)


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

PHP
Если множество решений для разработки приложений поддерживающих множество СУБД. Можно пойти:

1) путем обработки запроса регулярными выражениями подаваемого в php-класс под каждую СУБД(пример в исходных кодах phpbb.com, 3-я версия поддерживает mysql, oracle, postgresql).
Преимущества:

  • можно добавить поддержку СУБД на любом этапе разработки;

Недостатки

  • сложность в составлении регулярных выражениях, есть вероятность ошибки, т.е. в равно приходится возвращаться к старому коду;

2) путем обработки запроса перед тем как подать его в php-класс.
Преимущества:

  • надежность кода, вы всегда знаете как будет выполняться тот или иной запрос;

Недостатки

  • для добавления поддержки новой СУБД придется изменять все запросы во всем коде;

Я выбрал 2 способ:


if($config['driver'] == "mysql"){
    $db_limit = " LIMIT 1";
} else {
    if($['driver'] == "oracle"){
        $db_limit = " AND rownum = 1";
    }
}
$sql = "
    SELECT field1 f1,field2 f2
    FROM table1
    WHERE f1 = f2 ".$db_limit;

Если запрос сложнее, то необходимо обрабатывать его начиная с блока WHERE:
(заметьте, что в блоке GROUP BY для Oracle приходится перечислять все поля)


if($config['driver'] == "mysql"){
    $db_stuff = "
        WHERE f1 = f2
        GROUP BY field1
        ORDER BY field1 DESC
        LIMIT 1";
} else {
    if($['driver'] == "oracle"){
        $db_stuff = "
        WHERE f1 = f2 AND rownum = 1
        GROUP BY field1,field2
        ORDER BY field1 DESC";
    }
}
$sql = "
    SELECT field1 f1,field2 f2
    FROM table1 ".
    $db_stuff;

Еще пример:

if($config['driver'] == "mysql"){
    $db_hour = "hour(timediff(now(), t_timestamp))";
} else {
    if($config['driver'] == "oracle"){
        $db_hour = "CEIL(SYSDATE - t_timestamp)";
    }
}
$sql = "
    SELECT field1 f1,field2 f2, ".
    $db_hour." t_hour"
    ."FROM table1"


ORA ERRORS

ORA-00923: ключевое слово FROM не найдено там, где оно ожидалось.
возникает, если использовать ключевые слова: number, char, date , ...
в конструкциях:

sql> select somename number, somedate date from sometable;

ORA-00933: неверное завершение SQL-предложения

Возникает, если вы использовали посторонние символы.
Как правило это 'AS', Oracle не понимает эту конструкцию:

Oracle> SELECT table1.field1 AS f FROM table1;

Другие статьи:

  1. Установка и настройка Oracle
  2. Разработка ПО на php под Oracle 10.X и MySQL
  3. Перенос данных с Mysql на Oracle
Автор: Kirill Danilov на 14:55:00 0 коммент.
Тэги: , ,

пятница, июня 15, 2007

Перенос данных с Mysql на Oracle


Есть самый простой вариант - это MySQL Migration Toolkit(флэш-туториал), доступен под все платформы. Очень удобно и просто.

Но если вам нужна миграция под ваши потребности и вы хотите понять как все это работает, то предлагаю сделать все самими своими руками.

Для начала создайте копию структуры базыданных с MySQL в Oracle:

mysqldump --no-data -uUSER -pPASSWORD -hHOST DATABASE

понятно, что вместо USER, PASSWORD, HOST, DATABASE вписать свои значения.

Измените вручную описание таблиц и помните, что длина имен таблиц и полей не должна превышать 30 символов:

mysql> CREATE TABLE some_table (
table_id INT(11) NOT NULL AUTO_INCREMENT,
table_field_name1 INT(11) NOT NULL,
table_field_name2 VARCHAR(255) NULL DEFAULT 'some',
table_field_name3 TEXT NOT NULL,
PRIMARY KEY (table_id),INDEX(table_field_name1)
);
Query OK, 0 rows affected (0.05 sec)



oracle> CREATE TABLE some_table (
table_id NUMBER(11) NOT NULL,
table_field_name1 NUMBER(11) NOT NULL,
table_field_name2 VARCHAR2(255) NOT NULL,
table_field_name3 VARCHAR2(4000) NOT NULL
);
Table created.


Пропало описание AUTO_INCREMENT - в Oracle для этого существует sequences(последовательности), PRIMARY KEY и INDEX - ключи создаются иначе, DEFAULT 'some' - в Oracle не совместимо понятие NULL и DEFAULT, т.е. надо выбрать либо DEFAULT 'some', либо NULL.

Изменилось описание INT на NUMBER, VARCHAR - VARCHAR2, TEXT - VARCHAR2(4000). Таблицу соответствия можно посмотреть здесь

Далее сделайте дамп данных с MySQL:
span class="code">
mysqldump --add-drop-table --default-character-set=latin1 --no-create-info --single-transaction=TRUE --add-locks=FALSE --extended-insert=FALSE -uUSER -pPASSWORD -hHOST DATABASE > data.sql

Важным параметром является single-transaction=TRUE - создаст записи по одной строчке, что позволит по строчно обработать данные с помощью sed.


  1. cat './tables/table1.table' \
  2. | sed -e 's/[`]//g' \
  3. | sed -e 's/^\/\*/\-\-/' \
  4. | sed -e 's/^LOCK TABLES/\-\-/' \
  5. | sed -e 's/^UNLOCK TABLES/\-\-/' \
  6. | sed -e \"s/'\(\-\)\{0,1\}\([0-9]\{1,\}\)\,\([0-9]\{0,2\}\)'/'\1\2.\3'/g\" \
  7. | sed -e \"s/'0000-00-00 00:00:00'/NULL/\" > 'tables/table1.sql'



Пояснение:

  1. построчное чтение
  2. замена всех символов `
  3. замена коментариев начинающихся с /* на --
  4. комментирование строк начинающихся с LOCK TABLES
  5. комментирование строк начинающихся с UNLOCK TABLES
  6. замена строкового значения числа вида '99,99' на 99.99 (об этом ниже)
  7. замена '0000-00-00 00:00:00' на NULL


В базе данных MySQL были строковые поля к которых были числа в формате 99,99 и 99.99, было решено при переходе на Oracle привести эти поля в тип NUMBER и вставка '99,99' привела бы к ошибке.

После того как данные подготовлены, внести их можно из sqlplus:

sqlplus> @./tables/table1.sql

Теперь когда целостность данных сохранена, можно создать sequences(последовательности), которые заменяют AUTO_INCREMENT в MySQL.
Создадим одну последовательность на все таблицы, и чтобы небыло дублирования id-номеров, последовательность нужно начать с максимального значения id-поля среди всех таблиц.

<?php
$oracle = array(
'name'=>'DB_NAME',
'user'=>'USER_NAME',
'password'=>'PASSWORD',
'host'=>'XXX.XXX.XXX.XXX',
'port'=>'PORT');
$mode = OCI_COMMIT_ON_SUCCESS;

$config = "
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = PHP)
(PROTOCOL = TCP)
(Host = {$oracle['host']})
(Port = {$oracle['port']})
)
)
(CONNECT_DATA =
(SERVICE_NAME = {$oracle['name']})
)
)";

$link = oci_connect($oracle['user'],
$oracle['password'],
$config)
or die("Couldn't connect");

#Читаю список таблиц
$tables = file("tables.dat");
array_shift($tables);
$max_row_id = 1;
foreach($tables as $table){
$table = preg_replace("/[\r\n]/","",$table);
$sql = "SELECT * FROM $table WHERE rownum=1 ORDER BY 1 DESC \n";
$result = oci_parse($link, $sql);
oci_execute($result,$mode);
$row = oci_fetch_row($result);
if($row[0] > $max_row_id){
$max_row_id = $row[0];
}
$column_name = oci_field_name($result, 1);
#Создаем триггер для последовательности
$sql_trigger[] = "
CREATE OR REPLACE trigger \"TG_".strtoupper($table)."_BI\"
before insert on \"".strtoupper($table)."\"
for each row
begin
select \"MY_SEQ\".nextval into :NEW.".$column_name." from dual;
end TG_$table;
/";

}
print "Create sequence and start from $max_row_id+1\n";
#Удаляем последовательность
print $sql = "DROP sequence MY_SEQ ;/\n";
#Создаем последовательность
print $sql = "CREATE sequence MY_SEQ START WITH ".($max_row_id+1)." ;/\n";

#Создаем триггеры
foreach($sql_trigger as $sql){
print $sql."\n";
#$db->($sql);
}

oci_close($link);
?>

Все файлы для миграции можно скачать одним архивом здесь

Дополнительные ссылки:

  1. переход к другой кодировке в MySQL
  2. Мануал по sed


Другие статьи:

  1. Установка и настройка Oracle
  2. Разработка ПО на php под Oracle 10.X и MySQL
  3. Перенос данных с Mysql на Oracle
Автор: Kirill Danilov на 15:06:00 0 коммент.
Тэги: , , ,
Подписаться на: Сообщения (Atom)