使用dbi-link在PG里访问异构数据库

目的

因为工作需要，不得不面对纷繁复杂的众多的不同类型的数据库，为了降低难度，尤其是数据转存的复杂性，我们可以充分利用伟大的CPAN和同样伟大的 PostgreSQL 提供的接口，在PG里面，用PG的语法访问不同的数据库。这个工具就是PG的一个组件包 dbi-link。

dbi-link

PG的dbi-link实现了SQL2003的跨数据库访问异构数据库的一个子集，其基本原理是用Perl的DBI和相应数据库的DBD来访问异构数据库，然后结合了PG已有的SQL对象，利用PG来维护这些异构的数据库。

要求

1. postgresql 编译的时候需要支持Perl，也就是要给 postgresql 的 configure 添加 --with-perl 的选项。
2. 要有Perl的YAML CPAN包
3. 要有Perl的DBI CPAN包
4. 要有Perl的对应你需要访问的数据库的DBD包，比如DBD::Mysql

下载

dbi-link是一个pgfoundry项目，可以在下面的地址下载到：

下载的页面：

安装

dbi-link本身的安装很简单，但是需要一些额外的包，主要是Perl的一些包，我这里列一些大多数系统都没有的，比如YAML，我们需要这样安装：

安装CPAN的YAML包

  sudo su -  #需要root给全局安装
  perl -MCPAN -e 'shell' # 如果你还没配置过CPAN，请先运行这个命令配置
  perl -MCPAN -e 'install YAML'

然后就OK了。

安装DBD::Mysql驱动

然后，可以根据需要安装对应的DBD，比如Mysql的：

  perl -MCPAN -e 'install DBD::Mysql'

安装Oracle的DBD驱动

安装Oracle的DBD驱动的手脚要多一些，首先，需要安装Oracle的客户端库。我们可以去 Oracle下载站的相关位置找到 oracle 提供的linux安装包。注意，我们只需要安装Oracle客户端即可。

设置环境为安装Oracle做准备

以最难以搞定的Slackware为例（Oracle官方支持一些商业的Linux发行版，但是并不支持Slackware，因此，基本上在Slackware上的安装可以涵盖所有Oracle安装的基本概念）。下面是一个基本的步骤：

• 设置系统环境，欺骗oracle安装程序（root）权限

  ln -s /usr/lib/libgcc_s.so.1 /lib/libgcc_s.so.1
  ln -s /usr/i486-slackware-linux/lib/libstdc++.so.5.0.7 /usr/lib/libstdc++.so.5

Oracle 的安装程序在链接阶段使用了几个绝对路径来寻找上述两个库文件，所以最好做个符号链接。

  echo "redhat-3" > /etc/redhat-release

Oracle 的安装程序会检测系统类型，如果不在支持列表里的系统会拒绝执行，这里生成一个 /etc/redhat-release 文件用于欺骗安装程序。

  echo "export ORACLE_HOME=/home/oracle/product/10.2.0.1" >> /etc/profile
  echo "export LD_LIBRARY_PATH=/home/oracle/product/10.2.0.1/lib" >> /etc/profile

给系统设置两个环境变量，DBD::Oracle 需要这两个环境变量来制作。

• 安装oracle

我们可以用普通用户权限安装oracle，比如就是自己的用户，也可以先创建一个oracle数据库专用的用户。其它安装过程基本按照标准的过程执行，大致的步骤如下：

1. 接压缩安装包，进入安装目录
2. 启动 X
3. 在安装源文件目录中运行：runInstaller &
4. runInstaller会调用java，启动一个图形界面
5. 按照图形界面的提示，一步步往下走即可

对于dbi-link来说，我们实际上只需要安装客户端即可。

安装CPAN的DBD::Oracle

方法非常简单，只要确认自己的环境变量ORACLE_HOME设置正确即可进行如下操做（root身份）：

  perl -MCPAN -e 'install DBD::Oracle'

或者，在有些小错误的情况下，使用：

  perl -MCPAN -e 'shell'
  CPAN>force install DBD::Oracle

强制安装之。

安装dbi-link本身

安装完这些东西之后，我们安装 dbi-link，(需要PG的超级用户权限)步骤是：

  # cd　dbi-link-2.0.0
  # createdb dbi_link_test  #创建一个测试数据库
  # createlang plperlu -d dbi_link_test #给这个数据库创建perlu语言，dbi_link需要这个
  # psql -f dbi_link.sql -d dbi_link_test

这样dbi-link就装好了。

使用

初始化对应数据库

使用之前需要初始化对应的数据库。

更新系统表

   UPDATE
      pg_catalog.pg_settings
   SET
      setting =
          CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
          THEN setting
          ELSE 'dbi_link,' || setting
          END
  WHERE
       name = 'search_path';

设置连接参数

Mysql

SELECT make_accessor_functions('dbi:mysql:database=test;host=localhost', 'root', 'anything',
'---
AutoCommit: 1
RaiseError: 1
',NULL,NULL,NULL,'mysql_test');

Oracle

SELECT
make_accessor_functions('dbi:Oracle:laser;host=10.0.0.1;sid=dw1;port=1521',
'laser', 'oracle', '---
AutoCommit: 1
RaiseError: 1
',NULL,NULL,NULL,'oracle_dw1');

注意事项

1. 第四个字段是YAML的域，必须有回车，并且行开头不要有空格，也就是每行必须顶着行开头
2. 最后的'mysql_test'这个，是准备在PG里面通过模式的方法访问mysql数据库的模式，可以根据自己需要命名，建议是mysql数据库的名称
3. 第一个参数含义应该是一目了然的
4. 第二个参数是连接到mysql数据库的用户名
5. 第三个参数是连接到mysql数据库的密码，没有的话为

查询！！！

OK，现在就可以发出查询啦！！！先确信我们的mysql数据库是启动并且正常运行。然后，假设我的mysql的test数据库里头有这个表：

  wp_categories

那么现在我可以用这个办法在PG里直接查询它：

  select * from mysql_test.wp_categories;

试试看？是不是很爽？发散一下，我们就可以用这种方法倒数据：

  create table categories as select * from mysql_test.wp_categories;

试试看，是不是很爽？！