PostgreSQL module for newLISP

[Lutz]

Many thanks to Ted Walther for the postgres.lsp PostgreSQL module:



http://www.newlisp.org/downloads/development/postgres.lsp">http://www.newlisp.org/downloads/develo ... stgres.lsp">http://www.newlisp.org/downloads/development/postgres.lsp



I think Ted tested on Mac OS X. But there are other platform installers here:



http://www.enterprisedb.com/products/pgdownload.do">http://www.enterprisedb.com/products/pgdownload.do



To generate documentation do:

Code Select Expand

newlispdoc postgres.lsp

then open index.html from the current directory in a browser.



Thanks to Ted, the API is largely compatible with the MySQL API in mysql.lsp.

[TedWalther]

Only thing really remaining is to make the :fields function match that of the MySQL module, and also make the :fetch-value function figure out the type of the returned result and convert it appropriately using int and float; right now everything is a string.

[TedWalther]

One thing that would be nice is to register a callback.  There is one error that a query can throw that we can't catch, it instead is delivered to a callback function.  The default callback prints an error message to stdout.  That is nice for debugging, but it would be even nicer to be able to register our own callback function to retrieve the error message and let us handle it.



Lutz?  How can we do this?  Is it documented somewhere?



And I have to give a big thanks to Lutz for his simple FFI; I never imagined it could be so simple to interface to a library from another language.  I thought this would take weeks, instead it took less than three days.

[Lutz]

About 'callback' in newLISP:



http://www.newlisp.org/newlisp_manual.html#callback">http://www.newlisp.org/newlisp_manual.html#callback



Look at the signature the callback function must have. There may be pointers to strings or structures you can then serve with 'get-string' for string pointers and 'unpack' to get structure members from a structure pointer.



There are simple examples for extracting data from structures here:



http://www.newlisp.org/CodePatterns.html#extending">http://www.newlisp.org/CodePatterns.html#extending

[m35]

For Windows XP, the installer I used put everything here by default

C:Program FilesPostgreSQL8.3bin

Of course the version may vary.



I haven't actually used the module on Windows yet (and I may end up not needing to), but if I do I'll report back.

[TedWalther]

For Windows XP, the installer I used put everything here by default

C:Program FilesPostgreSQL8.3bin

Of course the version may vary.



I haven't actually used the module on Windows yet (and I may end up not needing to), but if I do I'll report back.

Thanks.  If you could tell me where the installer stashes the DLL file for libpq, I'd really appreciate it, and will include it in the module right away.



Lutz, on the topic of callbacks, I think I understand the reason for only allowing 16, because of the ORO garbage collection, it could allow memory leaks.  Is that correct?



Ted

[Lutz]

The number of callbacks is independent of memory management, just trying to conserve resources.



About the m35's Win32 path: I added it to the module:



http://www.newlisp.org/downloads/development/postgres.lsp">http://www.newlisp.org/downloads/develo ... stgres.lsp">http://www.newlisp.org/downloads/development/postgres.lsp

[TedWalther]

The number of callbacks is independent of memory management, just trying to conserve resources.



About the m35's Win32 path: I added it to the module:



http://www.newlisp.org/downloads/development/postgres.lsp">http://www.newlisp.org/downloads/develo ... stgres.lsp">http://www.newlisp.org/downloads/development/postgres.lsp

I guess I'm missing something.  If there is no risk of memory leaks, wouldn't it save a little memory to allocate a new callback with each call of callback?  Most programs don't need more than one or two callbacks.



I'm reminded of Chuck Moore who only allows the stacks on his FORTH chips to go 8 deep; he is a great enough programmer that is enough for him.  If I need more than 1 or 2 callbacks though, I might need more than 16.



It sort of troubles me to access callbacks by an index number.

[Lutz]

The initial implementation had 8 slots and that turned out to be insufficient. Allocating these slots dynamically is not trivial. The current 16 entry points are hardcoded, pre-compiled and served by simple, fast code.



The numbered slots let you reuse a callback for a different purpose with one newLISP statement, without the need to free up a specific slot in a different statement or with a different syntax.



Here is a complete working example with 6 callbacks, which on Mac OS X (Intel) works out of the box, without anything else to install:



http://www.newlisp.org/syntax.cgi?downloads/OpenGL/opengl-demo-lsp.txt">http://www.newlisp.org/syntax.cgi?downl ... mo-lsp.txt">http://www.newlisp.org/syntax.cgi?downloads/OpenGL/opengl-demo-lsp.txt



drag the mouse or hit the keyboard and see the callbacks in the console window scrolling by.



On Win32 you would have to install this:



http://www.newlisp.org/downloads/OpenGL/glut32.dll">http://www.newlisp.org/downloads/OpenGL/glut32.dll

[m35]

I ran a quick test of postgres.lsp.



The http://www.turnkeylinux.org/appliances/postgresql">Turnkey PostgreSQL Appliance is installed in a VMware machine which I connect to using PostgreSQL 8.3 on WinXP Pro SP3.



I have a large table with 27 million rows of data.



My first query was just "SELECT * FROM...", which did lots of processing and disk reading for over an hour but never returned anything before I killed it.



Tried again with a small limiter "SELECT * FROM ... WHERE ID BETWEEN X AND Y" and it ran quickly. I fetched a few rows without any problems.