fold all codes

从mysql迁移到oracle

项目开始阶段使用mysql数据库开发，后期改成oracle。数据抽象Db类对sql语句进行了封装，所以我主要工作就是参照mysql驱动的接口写一个oracle驱动。

基本的siud操作两者差别不是特别大，只有个别地方需要做一些工作：

  • 在oracle中建立相应的序列和触发器，实现mysql中的auto_increment字段
  • 使用子查询，实现mysql的limit以及sql_calc_found_rows功能
  • 对sql语句进行替换，避免mysql不出问题的字符或关键字在oracle中出问题

最后有两个问题比较致命：

  • 联表查询的时候会出现“列定义不明确”的错误
  • 分组查询的时候会出现“不是一个group查询”的错误

对于前者，原因就是a表中有字段xx，b表中也有字段xx，然后select a.*, b.*就会出现错误了。

对于后者，就是oracle中对group查询有这样的限制，select的字段、order中使用的字段，要么是出现在group by中，要么是被统计函数作用。而在mysql中却没有这样的限制。

这两个致命问题，我开始时是想要在oracle驱动上对sql语句进行hack，但是测试了一些例子发现没有什么规律而言，而且数据库驱动不维持表的元信息，所以写起来比较困难，就算最后写出来，执行效率也不高。最后只能返回去改程序，同时也给自己长记性，要改改在mysql中养成的那些臭毛病。

此外还有一些需要注意的地方，比如：

  • 字段类型的转换
  • NULL的问题
  • 单双引号

 最后上代码：

  1 <?php
  2 define('_TS', microtime(true));
  3 error_reporting(E_ALL);
  4 header('Content-type: text/plain; charset=utf-8');
  5 
  6 $_db = 'dbname';
  7 $_prefix = 'pre_';
  8 $_new_prefix = 'pre2_';
  9 
 10 $_sql_table = "select table_name, auto_increment, table_comment from information_schema.tables where table_schema = '".$_db."' and table_name like '".$_prefix."%';";
 11 $_sql_column = "select column_name, column_default, is_nullable, column_type, column_key, extra, column_comment from information_schema.columns where table_schema = '".$_db."' and table_name = '".$_prefix."%s' order by ordinal_position;";
 12 $_sql_checknull = "select count(*) from %s where %s = '' or %s is null";
 13 
 14 $db = mysql_connect('localhost', 'root', '');
 15 mysql_select_db($_db);
 16 mysql_query('set names utf8;');
 17 
 18 function query($sql, $mode = MYSQL_ASSOC)
 19 {
 20     $q = mysql_query($sql);
 21     if (!$q)
 22         exit('query error: '. mysql_error());
 23 
 24     $ret = array();
 25     while ($tmp = mysql_fetch_array($q, $mode))
 26         $ret[] = $tmp;
 27 
 28     return $ret;
 29 }
 30 
 31 echo 'SET DEFINE OFF;'. PHP_EOL;
 32 
 33 foreach (query($_sql_table) as $table)
 34 {
 35     $t = substr($table['table_name'], strlen($_prefix));
 36     if (!$t)
 37         exit("unknown table: {$t}");
 38 
 39     $new_table_name = $_new_prefix. $t;
 40 
 41     echo '-- '. $t. PHP_EOL;
 42     echo 'DROP TABLE '. $new_table_name. ';'. PHP_EOL;
 43     echo 'CREATE TABLE '. $new_table_name. PHP_EOL;
 44     echo '('. PHP_EOL;
 45 
 46     $pks = array();
 47     $idx = array();
 48     $aic = '';
 49     $comments = '';
 50 
 51     $cArr = query(sprintf($_sql_column, $t));
 52     $i = count($cArr);
 53     foreach ($cArr as $c)
 54     {
 55         if ($c['extra'] == 'auto_increment')
 56             $aic = $c['column_name'];
 57 
 58         echo $c['column_name']. ' ';
 59         if (!preg_match('/^(\w+)\b[^\d]*(\d+)?[^\d]*(\d+)?/', $c['column_type'], $match))
 60             exit("cannot match: {$t}.{$c['column_name']}");
 61 
 62         array_shift($match);
 63         $type = strtolower(array_shift($match));
 64         switch ($type)
 65         {
 66         case 'mediumint':
 67         case 'int':
 68         case 'tinyint':
 69             echo 'NUMBER('. ($match[0] < 5 ? 5 : $match[0]). ')';
 70             break;
 71         case 'decimal':
 72             echo 'NUMBER('. $match[0]. ','. $match[1]. ')';
 73             break;
 74         case 'char':
 75         case 'varchar':
 76             $match[0] *= 2;
 77             echo 'VARCHAR2('. ($match[0] > 4000 ? 4000 : $match[0]). ')';
 78             break;
 79         case 'date':
 80         case 'enum':
 81             echo 'VARCHAR2(10)';
 82             break;
 83         case 'text':
 84             echo 'NCLOB';
 85             break;
 86         default:
 87             exit("unknown type: {$c['column_name']} {$type}");
 88         }
 89 
 90         if ($c['column_default'] !== null)
 91             echo " DEFAULT '". $c['column_default']. "'";
 92 
 93         switch (strtoupper($c['column_key']))
 94         {
 95         case 'PRI':
 96             $pks[] = $c['column_name'];
 97             break;
 98         case 'MUL':
 99             $idx[$c['column_name']] = 'INDEX';
100             break;
101         case 'UNI':
102             $idx[$c['column_name']] = 'UNIQUE INDEX';
103             break;
104         }
105 
106         if (strtoupper($c['is_nullable']) == 'NO')
107         {
108             $checknull = query(sprintf($_sql_checknull, $table['table_name'], $c['column_name'], $c['column_name']), MYSQL_NUM);
109             if (!(int) $checknull[0][0])
110                 echo ' NOT NULL';
111         }
112 
113         if (--$i)
114             echo ',';
115 
116         echo PHP_EOL;
117 
118         $comments .= 'COMMENT ON COLUMN '. $new_table_name. '.'. $c['column_name'];
119         $comments .= " IS '". $c['column_comment']. "';". PHP_EOL;
120     }
121 
122     if (count($pks))
123         echo ', PRIMARY KEY ('.implode(', ', $pks).')'. PHP_EOL;
124 
125     echo ');'. PHP_EOL;
126 
127     foreach ($idx as $k => $v)
128         echo 'CREATE '. $v. ' '. $new_table_name. '_'. $k. ' ON '. $new_table_name. ' ('. $k. ');'. PHP_EOL;
129 
130     echo 'COMMENT ON TABLE '. $new_table_name. " IS '". addslashes($table['table_comment']). "';". PHP_EOL;
131     echo $comments;
132 
133     foreach (query("select * from {$table['table_name']}") as $row)
134     {
135         echo 'INSERT INTO '. $new_table_name. ' VALUES (';
136         foreach ($row as & $ri)
137             $ri = "'".addslashes($ri)."'";
138 
139         echo implode(',', $row);
140         echo ');'. PHP_EOL;
141     }
142 
143     if ($aic)
144     {
145         echo 'DROP SEQUENCE SEQU_'. $t. ';'. PHP_EOL;
146         echo 'CREATE SEQUENCE SEQU_'. $t. ' MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1'. PHP_EOL;
147         echo 'START WITH '. $table['auto_increment']. ' NOCACHE ORDER NOCYCLE ;'. PHP_EOL;
148         echo 'CREATE OR REPLACE TRIGGER TRIG_'. $t. ''. PHP_EOL;
149         echo 'BEFORE INSERT ON '. $new_table_name. ''. PHP_EOL;
150         echo 'FOR EACH ROW'. PHP_EOL;
151         echo 'BEGIN'. PHP_EOL;
152         echo '  SELECT SEQU_'. $t. '.NEXTVAL INTO :NEW.'.$aic.' FROM DUAL;'. PHP_EOL;
153         echo 'END;'. PHP_EOL;
154         echo '/'. PHP_EOL;
155     }
156 
157     echo PHP_EOL;
158 }
159 
160 mysql_close($db);
161 echo '-- ';
162 printf('%.3f', microtime(true) - _TS);

 

<?php
// 比较关键的一些操作

// connect
$linkId = oci_new_connect('user', 'pass', 'host/sid', 'utf8');

// query
$stmt = oci_parse($linkId, $sql);
$mode = false ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS;
if (!oci_execute($stmt, $mode))
    throw new Exception('query error: '. $sql);

// fetch
$ret = array();
while ($tmp = oci_fetch_array($stmt, OCI_BOTH + OCI_RETURN_NULLS + OCI_RETURN_LOBS))
    $ret[] = array_change_key_case($tmp);

// select
$sql = 'select %s ';
$sql .= 'from %s ';
$sql .= 'left join %s on %s ';
$sql .= 'where %s ';
$sql .= 'group by %s ';
$sql .= 'having %s ';
$sql .= 'order by %s ';

$sqlCalcFoundRows = 'select max(rownum) from ('. $sql. ')';
$sql = 'select * from (select a.*, rownum r from ('. $sql. ') a where rownum <= 10) b where r > 0';

// insert
$sql = 'insert into %s (%s) values (%s)';
$sqlInsertId = 'select sequ_%s.currval from %s';

// update
$sql = 'update %s set %s ';

// delete
$sql = 'delete from %s %s';

// replace
$sql = 'merge into %s a using (select %s from dual where rownum < 2) b on (a.%s = b.%s) when matched then update set %s when not matched then insert (%s) values (%s)';

// startTransaction

// commitTransaction
oci_commit($linkId);

// rollbackTransaction
oci_rollback($linkId);

 

posted @ 2012-08-13 16:54  hylent  阅读(2174)  评论(0)  编辑  收藏  举报