Use luapgsql connect postgresql-CSDN博客

lua连接数据库的动态库很多, 这里以luapgsql为例, 讲解如何使用luapgsql连接postgresql.

luapgsql详细用法参考 :

http://stpeters.github.io/luapgsql/

http://www.postgresql.org/docs/9.3/static/libpq-exec.html

环境 :

Lua 5.2
CentOS 5.x x64
PostgreSQL 9.3.2

首先下载luapgsql的源码 :

git clone https://github.com/mbalmer/luapgsql
cd luapgsql

luapgsql依赖postgresql, libbsd-devel包, 如下 :


#include <postgres.h>
#include <libpq-fe.h>
#include <libpq/libpq-fs.h>
#include <pg_config.h>
#include <catalog/pg_type.h>
#ifdef __linux__
#include <bsd/bsd.h>
#endif

从README得知, 因为是在Linux下使用luapgsql, 所以会用到GNUmakefile, 修改 GNUmakefile, LDADD 加入 -L/opt/pgsql9.3.2/lib, CFLAGS加入 -I/opt/pgsql9.3.2/include/server -I/opt/pgsql9.3.2/include

视你的postgresql环境情况而定.

vi GNUmakefile
SRCS=           luapgsql.c
LIB=            pgsql

LUAVER=         `lua -v 2>&1 | cut -c 5-7`

CFLAGS+=        -O3 -Wall -fPIC -I/usr/include -I/usr/include/lua${LUAVER} \
                -I/opt/pgsql9.3.2/include/server -I/opt/pgsql9.3.2/include -D_GNU_SOURCE
LDADD+=         -L/usr/lib -L/opt/pgsql9.3.2/lib -lpq -lbsd

LIBDIR=         /usr/lib
LUADIR=         /usr/lib/lua/${LUAVER}

${LIB}.so:      ${SRCS:.c=.o}
                cc -shared -o ${LIB}.so ${CFLAGS} ${SRCS:.c=.o} ${LDADD}

clean:
                rm -f *.o *.so
install:
        install -d ${DESTDIR}${LIBDIR}
        install -m 755 ${LIB}.so ${DESTDIR}${LUADIR}/${LIB}.so

安装

make
make install
make install时可能会报错, 因为找不到5.2的lua lib目录. 手工将生成的pgsql.so动态链接库拷贝到你的lua 5.2 lib目录即可
cp pgsql.so /usr/local/lib/lua/5.2/

使用luapgsql

root@db-172-16-3-33-> lua
Lua 5.2.3  Copyright (C) 1994-2013 Lua.org, PUC-Rio
> pgsql = require "pgsql"
> conn = pgsql.connectdb('hostaddr=127.0.0.1 port=5432 dbname=digoal user=digoal password=digoal')
> print(conn:errorMessage())

> print(conn:status())
0
> print ( pgsql.CONNECTION_OK)
0
> res = conn:exec('select info from userinfo limit 10')
> print(res)
userdata: 0x1364ced8
> print(res:ntuples())
10
> print(res:getvalue(1,1))
60bbdab7f524c333a2d7ef38f06444c2

使用print_tbl打印这个模块的内容.

function print_tbl(v_s) 
  if type(v_s) == "table" then
    print(v_s, "contents:-----------------------------------------------")
    for k,v in pairs(v_s) do 
      if type(v_s[k]) == "table" 
      then 
        print(v_s,k,v)
        print_tbl(v_s[k]) 
      else 
        print(v_s,k,v) 
      end 
    end
  else
    error("please enter a table variable.")
  end
end
> con = require "pgsql"
> pgsql = require "pgsql"
> print_tbl(pgsql)
table: 0x1ec8c30        contents:-----------------------------------------------
table: 0x1ec8c30        _COPYRIGHT      Copyright (C) 2009 - 2014 by micro systems marc balmer
table: 0x1ec8c30        PQPING_NO_ATTEMPT       3
table: 0x1ec8c30        PG_DIAG_SOURCE_FILE     70
table: 0x1ec8c30        PGRES_COPY_BOTH 8
table: 0x1ec8c30        SEEK_END        2
table: 0x1ec8c30        connectStart    function: 0x7f4ad61d5610
table: 0x1ec8c30        ping    function: 0x7f4ad61d55c0
table: 0x1ec8c30        CONNECTION_AWAITING_RESPONSE    4
table: 0x1ec8c30        PG_DIAG_SOURCE_FUNCTION 82
table: 0x1ec8c30        PGRES_POLLING_FAILED    0
table: 0x1ec8c30        SEEK_CUR        1
table: 0x1ec8c30        PG_DIAG_INTERNAL_QUERY  113
table: 0x1ec8c30        PGRES_POLLING_WRITING   2
table: 0x1ec8c30        PQPING_REJECT   1
table: 0x1ec8c30        PGRES_SINGLE_TUPLE      9
table: 0x1ec8c30        encryptPassword function: 0x7f4ad61d5540
table: 0x1ec8c30        CONNECTION_STARTED      2
table: 0x1ec8c30        CONNECTION_BAD  1
table: 0x1ec8c30        INV_READ        262144
table: 0x1ec8c30        CONNECTION_AUTH_OK      5
table: 0x1ec8c30        INV_WRITE       131072
table: 0x1ec8c30        PG_DIAG_SEVERITY        83
table: 0x1ec8c30        PQERRORS_TERSE  0
table: 0x1ec8c30        PG_DIAG_CONTEXT 87
table: 0x1ec8c30        PG_DIAG_MESSAGE_PRIMARY 77
table: 0x1ec8c30        PQERRORS_VERBOSE        2
table: 0x1ec8c30        CONNECTION_MADE 3
table: 0x1ec8c30        PQERRORS_DEFAULT        1
table: 0x1ec8c30        PG_DIAG_MESSAGE_HINT    72
table: 0x1ec8c30        PGRES_EMPTY_QUERY       0
table: 0x1ec8c30        _DESCRIPTION    PostgreSQL binding for Lua
table: 0x1ec8c30        PGRES_NONFATAL_ERROR    6
table: 0x1ec8c30        PQPING_OK       0
table: 0x1ec8c30        SEEK_SET        0
table: 0x1ec8c30        PG_DIAG_SOURCE_LINE     76
table: 0x1ec8c30        PG_DIAG_INTERNAL_POSITION       112
table: 0x1ec8c30        PQTRANS_UNKNOWN 4
table: 0x1ec8c30        PG_DIAG_MESSAGE_DETAIL  68
table: 0x1ec8c30        PQTRANS_IDLE    0
table: 0x1ec8c30        _VERSION        pgsql 1.2.3
table: 0x1ec8c30        PG_DIAG_STATEMENT_POSITION      80
table: 0x1ec8c30        PGRES_POLLING_READING   1
table: 0x1ec8c30        PQTRANS_ACTIVE  1
table: 0x1ec8c30        PQTRANS_INTRANS 2
table: 0x1ec8c30        PG_DIAG_SQLSTATE        67
table: 0x1ec8c30        PGRES_BAD_RESPONSE      5
table: 0x1ec8c30        connectdb       function: 0x7f4ad61d5670
table: 0x1ec8c30        PQTRANS_INERROR 3
table: 0x1ec8c30        PQPING_NO_RESPONSE      2
table: 0x1ec8c30        PGRES_POLLING_OK        3
table: 0x1ec8c30        PGRES_COPY_IN   4
table: 0x1ec8c30        CONNECTION_OK   0
table: 0x1ec8c30        PGRES_COMMAND_OK        1
table: 0x1ec8c30        CONNECTION_SETENV       6
table: 0x1ec8c30        libVersion      function: 0x7f4ad61d55f0
table: 0x1ec8c30        CONNECTION_SSL_STARTUP  7
table: 0x1ec8c30        PGRES_COPY_OUT  3
table: 0x1ec8c30        PGRES_TUPLES_OK 2
table: 0x1ec8c30        PGRES_FATAL_ERROR       7