Read Sean

Read me, read Sean.
posts - 508, comments - 655, trackbacks - 9, articles - 4

最近尝试把一个Oracle数据库,连同构建在这个数据库上的Java应用移植到PostgreSQL环境。在移植过程中,总结了一些要点,一方面作为笔记备忘,一方面也给有类似任务需要处理而又无从下手的朋友作为参考。

1- 首先是准备PostgreSQL环境。有条件的话,最好是找一台空闲的PC机作为测试服务器,安装Linux或BSD,然后从源码编译最新的PostgreSQL 8.3.0。编译时,通过configure指定--with-perl和--with-python以支持PL/Perl和PL/Python。因为绝大多数Linux发行版都已自带Perl和Python,不必额外安装。

2- 如果是Windows环境,又需要Perl和Python,则必须额外安装,Python的话,可以方便的找到2.5 for Windows的安装包,Perl的话,推荐ActivePerl,相对麻烦一点,为了后面用到的一些便利的功能顺利加载,Perl版本尽量选5.8.8。

3- 创建数据库和用户。通过initdb初始化数据目录,配置postgresql.conf指定主机IP、端口等等信息,配置pg_hba.conf指定访问权限,通过pg_ctl -D <数据目录> -l <日志文件> start启动postmaster,然后createdb、createuser创建数据库和用户。数据库建好之后,就可以createlang -d <数据库名> [plperl|plperlu|plpython|plpythonu]开启PL/Perl和PL/Python。具体命令行参数可通过各命令加--help查看。

4- 安装PostgreSQL客户端pgAdminIII,最新版是1.8.2,有条件的话,也可以下载源码自己编译。

5- 安装Oracle客户端,需要在PostgreSQL同一台机器,以便Perl用于连接数据库的DBI和Oracle驱动DBD::Oracle模块顺利安装。如果是Windows上的ActivePerl,则可以通过ppm install DBD-Oracle,如果是Linux/BSD,则可以通过CPAN来安装,如perl -MCPAN -e shell进入CPAN Shell,通过install <模块名>或force install <模块名>安装DBI和DBD::Oracle。

6- 数据库的移植,可以选择ora2pg来帮忙,目前的版本是4.7。ora2pg是一个用于读取Oracle数据库schema、数据,并生成PostgreSQL脚本或直接导入PostgreSQL数据库的Perl工具。用法很简单,就是通过.conf文件指定数据库连接信息包括NLS_LANG、需要导出导入的schema、table、view、data等等,然后执行一个pl脚本。这是目前相对比较成熟的一个方案,但是遇到schema复杂、约束较强的数据库,需要手工处理的地方还是不少。建议不要直接写入PostgreSQL,而是生成SQL脚本,验证无误后再执行。ora2pg默认会把Oracle中名称的大写转换成小写,因为PostgreSQL在解析SQL时,除非""括起来,默认都是转成小写。schema、table、view、sequence、data等等,基本用ora2pg,加上一些手工调整即可搞定。至于function、stored procedure等,还是手工移吧,偷不得懒。除了ora2pg,其实也可以配置DBI-Link,将Oracle数据库挂到PostgreSQL数据库作为一组独立的"schema",然后用create table xxx as select ... from ...这样的语法来倒表和数据。PostgreSQL的contrib包也附带有一个dblink,不过是连接其他PostgreSQL数据库的,如果需要连接非PostgreSQL, 还是考虑DBI-Link,任何可以通过Perl的DBI接口访问的数据库,都能link进PostgreSQL。

7- 接下来就是Java应用本身了,我这次移的这个应用是Spring+iBatis架构的,很多SQL语句都是明文,好在DAO层的基础部分(CRUD)的SQLMap是工具自动生成,且都是符合ANSI SQL92标准的,不需要修改即可使用。其余的高级查询SQL,需要调整的地方不少,一些常见的修改列举如下:

i. SELECT出来的column(包括子查询),如果有别名,必须加AS,比如 select null as some_column from some_table;
ii. PostgreSQL没有dual表,类似select 0 from dual的语句,写成select 0即可;
iii. DECODE函数需要重构成(case when some_column = 'some_value' then 'some_other_value' when ... then ... else 'some_default_value' end ) as some_column;
iv. NVL()函数,PostgreSQL中相对应的是coalesce(),其实几乎所有主流DBMS都支持coalesce,包括Oracle,这才是标准写法;
v. 比较日期,在PostgreSQL中,建议使用date_trunc('day', SOME_DATE) = date_trunc('day', #enteredDate#)这样的写法,其中'day'位置可选字段包括有year、month、week、hour、minute、second等等;
vi. SYSDATE,对应到PostgreSQL是current_timestamp,可以根据需要使用current_date;
vii. ROWNUM,通常我们用ROWNUM都是为了限制查询出来的记录数,PostgreSQL没有这个关键字,需要改成在SELECT语句最后添加 LIMIT语句,如LIMIT 100;
viii. (+)这样的外连接写法需要调整为SQL标准的 table1 [LEFT|RIGHT|FULL] OUTER JOIN table2 ON (...);
ix. CONNECT BY ... START WITH ... 递归查询可以参考 http: //www.postgresql.org/docs/8.3/static/tablefunc.html 的connectby()函数.

最后再多提一点,PostgreSQL自带的过程语言是PL/pgSQL,在PostgreSQL上写function,除了用plpqsql,还支持sql、plperl(u)、plpython(u)等等。如果你对SQL天生过敏,看类似PL/pgSQL的代码都很吃力,别说是写了,你完全可以用你喜欢的语言来表达函数和存储过程的逻辑。有了PL/Python,你还怕什么呢?你几乎能做任何事。

[更新 20080313] 把JDBC驱动的部分漏掉了,移植Java应用时,除了改SQL,还需要拿PostgreSQL的JDBC驱动放到classpath下面,如WEB-INF/lib,然后修改数据库连接URL,改成jdbc:postgresql://<ip>:<port>/<dbname>即可。

[更新 20080323] 移植schema和数据时,比ora2pg更方便的一种方式是利用EnterpriseDB的Migration Tool,将Oracle的JDBC驱动ojdbc14.jar拷贝到EnterpriseDB安装路径下的jre/lib/ext下后,启动Developer Studio即可建立Oracle连接,选中schema后,可以通过右键Online Migration将schema、数据、函数包等等一次性通通导入EnterpriseDB。如果要继续往"纯"PostgreSQL移,从EDB做backup,然后到PostgreSQL下做restore,这样会丢掉函数包,因为毕竟EDB在PostgreSQL基础上做了相当改造以和Oracle兼容,不过函数包之类还是手工移比较稳妥。



只有注册用户登录后才能发表评论。


网站导航: