I am currently working on a very simple interface to PostgreSQL using
the libpq library. I have hacked up some code as a module to give me
the C to lisp interface (included in the first attachment).
I am not sure of the "correct" way to pass pointers back to the
user. The user does not actually do anything with the pointers, just
passes them on to other routines. However, it would be useful for
debugging to be able to print the numbers.
An example of this is the `PQconnectdb' function which returns a
connection pointer. This is then passed to the `PQexec' function to
actually perform the commands. I am currently turning the pointers
into integers by setting the lower bit. I am not sure this is the best
way to handle it, it is the simplest and fastest.
A second question is the best way to allocate structures. I would like
them to be normal lisp objects that GC etc., but that can be passed to
C functions. The opaque type looked perfect, except it has a warning
that the objects should never be passed up to lisp.
Thanks for any help or comments on the code,
Sean MacLennan
P.S. The second attachment contains some simple lisp wrappers for the
PQlib code.
#include <pgsql/libpq-fe.h>
#include <emodules.h>
/*
* This is an interface to libpq, the C library interface to
* PostgreSQL. I have attempted a direct 1:1 mapping of C to lisp.
*
* The current version is very incomplete and only implements the
* functions I needed for a very simple calendar program.
*/
#if 0
#define make_conn(conn) make_int((long)conn)
#define make_result(res) make_int((long)res)
#define PGCONN(conn) ((PGconn*)XINT(conn))
#define PGRESULT(res) ((PGresult*)XINT(res))
#else
#define make_conn(conn) ((Lisp_Object)(((long)conn) | Lisp_Type_Int_Bit))
#define make_result(res) ((Lisp_Object)(((long)res) | Lisp_Type_Int_Bit))
#define PGCONN(conn) ((PGconn*)((long)conn & ~Lisp_Type_Int_Bit))
#define PGRESULT(res) ((PGresult*)((long)res & ~Lisp_Type_Int_Bit))
#endif
DEFUN ("PQconnectdb", FPQconnectdb, 1, 1, "", /*
Connect to the PostgreSQL database.
Returns a connection or nil.
*/
(arg_string))
{
PGconn *conn;
CHECK_STRING(arg_string);
conn = PQconnectdb(XSTRING_DATA(arg_string));
return make_conn(conn);
}
DEFUN ("PQfinish", FPQfinish, 1, 1, "", /*
Disconnect from the SQL database.
*/
(conn))
{
CHECK_INT(conn);
PQfinish(PGCONN(conn));
return Qt;
}
DEFUN ("PQexec", FPQexec, 2, 2, "", /*
Exec call.
Returns a results index or nil.
*/
(conn, command))
{
PGresult *res;
CHECK_INT(conn);
CHECK_STRING(command);
res = PQexec(PGCONN(conn), XSTRING_DATA(command));
return make_result(res);
}
DEFUN ("PQclear", FPQclear, 1, 1, "", /*
Free up a result.
*/
(result))
{
CHECK_INT(result);
PQclear(PGRESULT(result));
return Qt;
}
DEFUN ("PQresultStatus", FPQresultStatus, 1, 1, "", /*
Return the result status as an integer.
*/
(result))
{
CHECK_INT(result);
return make_int(PQresultStatus(PGRESULT(result)));
}
DEFUN ("PQresStatus", FPQresStatus, 1, 1, "", /*
Given a result status as an integer, return a status string.
*/
(rc))
{
CHECK_INT(rc);
return build_string(PQresStatus(XINT(rc)));
}
DEFUN ("PQresultErrorMessage", FPQresultErrorMessage, 1, 1, "", /*
Return the result status as a string.
*/
(result))
{
CHECK_INT(result);
return build_string(PQresultErrorMessage(PGRESULT(result)));
}
DEFUN ("PQntuples", FPQntuples, 1, 1, "", /*
Return number of tuples in query result.
*/
(result))
{
CHECK_INT(result);
return make_int(PQntuples(PGRESULT(result)));
}
DEFUN ("PQbinaryTuples", FPQbinaryTuples, 1, 1, "", /*
Return number of binary tuples in query result.
*/
(result))
{
CHECK_INT(result);
return make_int(PQbinaryTuples(PGRESULT(result)));
}
DEFUN ("PQnfields", FPQnfields, 1, 1, "", /*
Return number of fields in query result.
*/
(result))
{
CHECK_INT(result);
return make_int(PQnfields(PGRESULT(result)));
}
DEFUN ("PQgetvalue", FPQgetvalue, 3, 3, "", /*
Get a value result.
*/
(result, tuple, field))
{
CHECK_INT(result);
CHECK_INT(tuple);
CHECK_INT(field);
return build_string(PQgetvalue(PGRESULT(result), XINT(tuple), XINT(field)));
}
void
syms_of_PostgreSQL()
{
DEFSUBR(FPQconnectdb);
DEFSUBR(FPQfinish);
DEFSUBR(FPQexec);
DEFSUBR(FPQclear);
DEFSUBR(FPQresultStatus);
DEFSUBR(FPQresStatus);
DEFSUBR(FPQresultErrorMessage);
DEFSUBR(FPQntuples);
DEFSUBR(FPQbinaryTuples);
DEFSUBR(FPQnfields);
DEFSUBR(FPQgetvalue);
}
void
vars_of_PostgreSQL()
{
}
(defun SQL-connect (db)
(PQconnectdb (concat "dbname=" db)))
(defun SQL-disconnect (conn)
(PQfinish conn))
(defun SQL-insert (conn name values)
(let ((insert (concat "INSERT INTO " name " VALUES ('" (car values) "'"))
res rc)
(dolist (value (cdr values))
(setq insert (concat insert ",'" value "'")))
(setq insert (concat insert ")"))
(setq res (PQexec conn insert))
(setq rc (PQresultStatus res))
(PQclear res)
(= rc 1)))
(defun SQL-delete (conn name &optional where)
(let ((delete (concat "DELETE FROM " name))
res)
(when where
(setq delete (concat delete " WHERE " where)))
(setq res (PQexec conn delete))
(setq rc (PQresultStatus res))
(PQclear res)
(= rc 1)))
(defun SQL-select (conn what name &optional where order)
(let ((select (concat "SELECT " what " FROM " name))
res tuples fields list sub-list)
(when where
(setq select (concat select " WHERE " where)))
(when order
(setq select (concat select " ORDER BY " order)))
(setq res (PQexec conn select))
(setq rc (PQresultStatus res))
(when (= rc 2)
(setq tuples (PQntuples res)
fields (PQnfields res))
(if (= fields 1)
(dotimes (tuple tuples)
(setq list (cons (PQgetvalue res tuple 0) list)))
(dotimes (tuple tuples)
(setq sub-list nil)
(dotimes (field fields)
(setq sub-list (cons (PQgetvalue res tuple field) sub-list)))
(setq list (cons (nreverse sub-list) list))))
(setq list (nreverse list)))
list))
XEMACS=/home/xemacs/xemacs
SHELL=/bin/sh
RM=rm -f
CC=$(XEMACS)/lib-src/ellcc
CFLAGS=-I. -I$(XEMACS)/src
LD=$(CC) --mode=link
MKINIT=$(CC) --mode=init
SRCS=postgresql.c
OBJS=$(SRCS:.c=.o)
LIBS=-lpq
.c.o:
$(CC) $(CFLAGS) -c $<
MODNAME=PostgreSQL
MODVER=1.0.0
MODTITLE="PostgreSQL loadable module"
all: $(MODNAME).ell
distclean: clean
clean:
$(RM) $(MODNAME).ell $(OBJS) postgresql_i.o postgresql_i.c core
$(MODNAME).ell: $(OBJS) postgresql_i.o $(LIBS)
$(LD) --mod-output=$@ $(OBJS) postgresql_i.o $(LIBS)
postgresql_i.o: postgresql_i.c
postgresql_i.c: $(SRCS)
ELLMAKEDOC=$(XEMACS)/lib-src/make-docfile $(MKINIT) --mod-output=$@ \
--mod-name=$(MODNAME) --mod-version=$(MODVER) \
--mod-title=$(MODTITLE) $(SRCS)