Simple PostgreSQL client library

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Simple PostgreSQL client library

Michael Broughton
Hello,

This is the library I have been working on. I probably should have written some better documentation and done more testing before releasing it, but for those that requested some sample code earlier, you should have no problem finding the interesting parts.

I have been using LuaSQL with my own patches and an abstraction layer (written in Lua) which resembles this library. It works quite well for my purposes, but it isn't quite release quality. For example, I had to integrated support for some non-privative data types that I use. This library has a mechanism for installing custom data type handlers without hacking the internals of the library.

Anyway, here is an example of a basic query using my library:

local stmt = conn:statement"SELECT a, b, c FROM t WHERE d > $1 AND e = $2"
stmt:setInteger(1, 1234)
stmt:setString(2, "abcd")
local rs, count = stmt:selectResultSet()

Which might return something like:

rs = {
   {a=7, b="word", c=true},
   {a=2, b="eofe", c=false},
   ...
   {a=5, b="sesg", c=false},
}
count = 23

Nothing revolutionary, but I find it much nicer than working with cursors and doing a bunch of string concatenations to write queries. Besides result set's you can also select individual results, individual fields and key-value pair tables. I included a slightly more detailed example with the source.

If there is any interest in this, I can post more about other features. Questions and comments are welcome.

Mike


Attachment: luapostgres-1.0.0.tar.bz2
Description: Binary data

Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

eugeny gladkih
>>>>> "MB" == Michael Broughton <[hidden email]> writes:

 MB> Anyway, here is an example of a basic query using my library:

 MB> local stmt = conn:statement"SELECT a, b, c FROM t WHERE d > $1 AND e = $2"
 MB> stmt:setInteger(1, 1234)
 MB> stmt:setString(2, "abcd")
 MB> local rs, count = stmt:selectResultSet()

what about more flexible idea - using separate statement and
bindings?

local stmt = conn:statement( "SELECT a, b, c FROM t WHERE d > $d AND e = $e" )
local rs, count = stmt:selectResultSet( { d=1; e="abc" } )

-- 
Yours sincerely, Eugeny.
Doctor Web, Ltd. http://www.drweb.com

Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Tomás Guisasola-2
In reply to this post by Michael Broughton
	Hi Michael

> local stmt = conn:statement"SELECT a, b, c FROM t WHERE d > $1 AND e = $2"
> stmt:setInteger(1, 1234)
> stmt:setString(2, "abcd")
> local rs, count = stmt:selectResultSet()
	But the above code could be done in Lua don't you think?
If you are only creating SQL statements...

> Nothing revolutionary, but I find it much nicer than working with 
> cursors and doing a bunch of string concatenations to write queries. 
> Besides result set's you can also select individual results, individual 
> fields and key-value pair tables. I included a slightly more detailed 
> example with the source.
	I don't like to work with cursors either, neither checking every
statement if there was an error.  And of course assembling SQL statements
is boring.  But I've done all these jobs in Lua.  The result is something
like:

local dado = require"dado" -- my database abstraction
local db = dado.connect("database", "user", "pass", "postgres")

local cond = string.format ("d > %s and e = '%s'", 1234, "abcd")
for a, b, c in db:select("a,b,c", "t", cond) do
	-- do anything you want with the results
end

	In this example I have to be careful when assembling the SQL
statement, but I have some facilities for the common cases like:

print (table.fullconcat ({a = 1, b = 2, c = "a'bc"}, "=", " AND ", nil,
	string.quote))
a='1' AND c='a\'bc' AND b='2'

	What I'm really looking for is a uniform way to do Prepared
Statements in LuaSQL, which I think would really be a step further.
Have you thought about that?

	Regards,
		Tomás


Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Michael Broughton
In reply to this post by eugeny gladkih
I get the impression that you think my library is doing string substitutions for the parameters. Am I mistaken?

Query parameters are actually sent as an array, separately from the query string. I am just using the query param syntax that Postgres wants.

Mike


eugeny gladkih wrote:
what about more flexible idea - using separate statement and
bindings?

local stmt = conn:statement( "SELECT a, b, c FROM t WHERE d > $d AND e = $e" )
local rs, count = stmt:selectResultSet( { d=1; e="abc" } )


Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Michael Broughton
In reply to this post by Tomás Guisasola-2
Tomas Guisasola Gorham wrote:
	But the above code could be done in Lua don't you think?
If you are only creating SQL statements...

My original library was actually a layer of Lua sitting on top of LuaSQL. However, I did have to patch LuaSQL to make it support sending query parameters separately from the query string.


	I don't like to work with cursors either, neither checking every
statement if there was an error.  And of course assembling SQL statements
is boring.  But I've done all these jobs in Lua.  The result is something
like:

local dado = require"dado" -- my database abstraction
local db = dado.connect("database", "user", "pass", "postgres")

local cond = string.format ("d > %s and e = '%s'", 1234, "abcd")
for a, b, c in db:select("a,b,c", "t", cond) do
	-- do anything you want with the results
end

	In this example I have to be careful when assembling the SQL
statement, but I have some facilities for the common cases like:

print (table.fullconcat ({a = 1, b = 2, c = "a'bc"}, "=", " AND ", nil,
	string.quote))
a='1' AND c='a\'bc' AND b='2'

	What I'm really looking for is a uniform way to do Prepared
Statements in LuaSQL, which I think would really be a step further.
Have you thought about that?

Well, every database is slightly different when it comes to prepared statements... As far as I know, anyway.

As far as Postgres is concerned, prepared statements would be a fairly simple addition to my library. I have not fully thought out the semantics but I sort of imagined something like this:

local stmt = conn:statement"SELECT ..."
stmt:setInteger(1)
stmt:setString(2)
local prep = stmt:prepare"name"

Then you can start using the prepared statement directly, or create a new object latter by referencing the name:

local prep = conn:prepare"name"
prep:setInteger(1, 100)
prep:setString(2, "woenwe")
local res, count = prep:selectResult()

I am only really familiar with the Postgres client API, but I will just go ahead and guess that this probably won't work for most other databases. That is the main reason why I left this out, for now.

Mike




Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Tomás Guisasola-2
	Hi Michael

> My original library was actually a layer of Lua sitting on top of 
> LuaSQL. However, I did have to patch LuaSQL to make it support sending 
> query parameters separately from the query string.
	But why you need to use query parameters?  Don't you solve that
in Lua with `string.format' or something like that?

	Regards,
		Tomás


Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Petite Abeille

On Apr 02, 2007, at 19:18, Tomas Guisasola Gorham wrote:

	But why you need to use query parameters?  Don't you solve that
in Lua with `string.format' or something like that?

Yes, you could generate a brand new statement each and every single time... and force the db to parse the same statement over and over again... on the other hand... using so called "prepared" statement allow you to declare the statement once... and bind it to different parameters when needed... saving a bit of work all around...


Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Petite Abeille
In reply to this post by eugeny gladkih

On Apr 02, 2007, at 09:32, eugeny gladkih wrote:

local stmt = conn:statement( "SELECT a, b, c FROM t WHERE d > $d AND e = $e" )
local rs, count = stmt:selectResultSet( { d=1; e="abc" } )

Well... you could as well use one statement...

local aConnection = DB( 'postgress://user:password@localhost' )
local aResult = aConnection( 'select a, b , c from t where d >', d, ' and e = ', e )

for aRow in aResult do
    print( aRow.a )
end


Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

Michael Broughton
In reply to this post by Tomás Guisasola-2
Good question.

Personally, I find that string formatting and concatenation can be tedious and error prone. Also, it makes a big mess of my code, even for simple queries. In my experience, programming errors are much easier to spot and fix when using query parameters.

One of the main argument given for using query parameters is to prevent SQL injection attacks. For example:

string.format("SELECT * FROM t WHERE a = %s", mystring)

Wouldn't it be nice if some malicious person where to substitute the following for 'mystring':

mystring = "'aaa'; DELETE FROM t;"

With query parameters, this kind of attack is easily preventable.

Mike



Tomas Guisasola Gorham wrote:
	Hi Michael

My original library was actually a layer of Lua sitting on top of LuaSQL. However, I did have to patch LuaSQL to make it support sending query parameters separately from the query string.
	But why you need to use query parameters?  Don't you solve that
in Lua with `string.format' or something like that?

	Regards,
		Tomás


Reply | Threaded
Open this post in threaded view
|

Re: Simple PostgreSQL client library

eugeny gladkih
In reply to this post by Michael Broughton
>>>>> "MB" == Michael Broughton <[hidden email]> writes:

 MB> I get the impression that you think my library is doing string
 MB> substitutions for the parameters. Am I mistaken?

 MB> Query parameters are actually sent as an array, separately from the
 MB> query string. I am just using the query param syntax that Postgres
 MB> wants.

I'm thinking about perl DBD/DBI analogue mostly, even *DBC 

 MB> Mike


 MB> eugeny gladkih wrote:
 >> what about more flexible idea - using separate statement and
 >> bindings?
 >> 
 >> local stmt = conn:statement( "SELECT a, b, c FROM t WHERE d > $d AND e = $e" )
 >> local rs, count = stmt:selectResultSet( { d=1; e="abc" } )
 >> 
 >> 

-- 
Yours sincerely, Eugeny.
Doctor Web, Ltd. http://www.drweb.com