Lua 5.1 Use luadbi connect to postgresql-CSDN博客

使用Lua 连接postgresql的驱动很多, 前面我介绍了一下luasql, luapgsql的使用, 其中luasql由于不支持prepared sql, 所以在大量SQL请求的情况下, 效率会比较低. luapgsql则支持prepared sql, 效率接近pgbench, 还是不错的.

本文将介绍一下luadbi这个驱动, 它同样支持多个数据库种类, 其中postgresql这个也是使用libpq来支持的.

luadbi这个 0.5版本驱动在5.1下面可以正常使用, 但是Lua 5.2下面会报错.

首先要安装Lua 5.1, 详见INSTALL文件描述.

wget http://www.lua.org/ftp/lua-5.1.5.tar.gz
tar -zxvf lua-5.1.5.tar.gz
cd lua-5.1.5
make linux
make install INSTALL_TOP=/opt/lua5.1

然后要安装luadbi, 详细说明参考INSTALL文件.

mkdir luadbi
cd luadbi
wget https://luadbi.googlecode.com/files/luadbi.0.5.tar.gz
tar -zxvf luadbi.0.5.tar.gz
修改Makefile, 包含正确的lua include目录, postgresql include目录, postgresql lib目录.
vi Makefile
CFLAGS=-g -pedantic -Wall -O2 -shared -fpic -I /opt/lua5.1/include -I /usr/include/mysql -I /opt/pgsql9.3.2/include -I /opt/pgsql9.3.2/include/server -I /opt/ibm/db2exc/V9.5/include/ -I /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/public/ -I .
PSQL_LDFLAGS=$(COMMON_LDFLAGS) -L/opt/pgsql9.3.2/lib -lpq 
生成so文件. 会生成dbdpostgresql.so文件.
make psql
把so文件和DBI.lua拷贝到lua 5.1的lib目录.
cp dbdpostgresql.so DBI.lua /usr/local/lib/lua/5.1/

测试

[root@db-172-16-3-33 luadbi]# /opt/lua5.1/bin/lua
Lua 5.1.5  Copyright (C) 1994-2012 Lua.org, PUC-Rio
> require "DBI"
> dbh = assert(DBI.Connect('PostgreSQL', 'digoal', 'digoal', 'digoal', '127.0.0.1', '5432'))
> dbh:autocommit(true)
> res = DBI.Do(dbh, "create table test(id int, info text, crt_time timestamp)")
> insert = assert(dbh:prepare('insert into test(id,info,crt_time) values(?,?,?)'))
> insert:execute(1, 'test', 'now()')

digoal=> select * from test;
 id | info |          crt_time          
----+------+----------------------------
  1 | test | 2014-02-22 13:04:42.488098
(1 row)

在Lua 5.2中使用会报错.

> DBI = require "DBI"
> DBI.Connect('PostgreSQL', dbname, dbuser, dbpassword, dbhost, dbport)
/usr/local/lib/lua/5.2/DBI.lua:52: Cannot load driver PostgreSQL. Available drivers are: (None)
stack traceback:
        [C]: in function 'error'
        /usr/local/lib/lua/5.2/DBI.lua:52: in function 'Connect'
        stdin:1: in main chunk
        [C]: in ?
来自DBI.lua
-- Driver to module mapping
name_to_module = {
    MySQL = 'dbd.mysql',
    PostgreSQL = 'dbd.postgresql',
    SQLite3 = 'dbd.sqlite3',
    DB2 = 'dbd.db2',
    Oracle = 'dbd.oracle',
}

string = require('string')

-- Returns a list of available drivers
-- based on run time loading
function available_drivers()
    local available = {}

    for driver, modulefile in pairs(name_to_module) do
        local m, err = pcall(require, modulefile)

        if m then
            table.insert(available, driver)
        end
    end

    -- no drivers available
    if table.maxn(available) < 1 then
        available = {'(None)'}
    end

    return available
end

 -- High level DB connection function
 -- This should be used rather than DBD.{Driver}.New
function _M.Connect(driver, ...)
    local modulefile = name_to_module[driver]

    if not modulefile then
        local available = table.concat(available_drivers(), ',')
        error(string.format("Driver '%s' not found. Available drivers are: %s", driver, available))
    end

如果要支持Lua 5.2, 需要对DBI.lua作出修改才行 .

[参考]

1. https://code.google.com/p/luadbi/wiki/DBI