Re: Automatic Mapping Lua tables to a database tables

["Aaron B." <aaron@...>]

On Fri, 31 Jan 2014 08:29:01 -0800
Milind Gupta <milind.gupta@gmail.com> wrote:

> Thanks for the info. I was looking at the DBI google project page. In the
> examples it still seems you use some SQL to do operations. My aim for
> example is something like this:x =
> 
> tableDB.open('myDB')
> 
> x.key1 = {a= "hello world", b=12}
> 
> x.y = 23
> 
> x.qwerty = true
> 

DBI doesn't do this, it's much like LuaSQL. What I was talking about
does what you are asking but is currently unreleased code, a module I
call SqlTable. I'll try to push it out this weekend.

It basically does this:

==== code ====

-- select
local row = sql_table[25]

-- insert
sql_table[ sql.next ] = { a = 1, b = true, c = "stringy" }
local key = sql.last_insert_id(sql_table)

-- update
sql_table[ key ] = { b = false }

-- delete
sql_table[ key ] = nil

-- iterate rows
for key, row in sql.where( sql_table, "b = true" ) do
	f(row) 
end

==== /code ====

There is more, such an 'escape valve' that lets you write raw SQL, plus
a some helper functions for common operations. Part of the reason I
never got it out was getting it documented.


> NOTE: Here myDB is a database with a defined structure (defined by calling
> the createDB function by passing it a template table previously) having a
> table with columns key1 (table) and y (number) and another table with
> columns a (string),b (number)
> 
> 
> Now doing x:save() saves the table x into the database and so it is
> possible to do this:
> 
> y = tableDB.open('myDB')
> print(y.key1)   -- table: 0001234
> print(y.y) -- 23
> print(y.key1.a)  -- "hello world"
> print(y.qwerty) -- true    (even though not originally defined in the DB
> structure still it preserves the value)
> 
> is it possible to use DBI the same way?
>

Again, LuaDBI doesn't do this. Sorry for the confusion.

My module only handles querying and manipulating data. It wouldn't be
hard to add, just was never in scope.


> Another thing is that when accessing the values only the relevant values
> from the database are read and cached and the whole table does not need to
> be present in the memory for accessing the table.
> 

Any hit to one of the controlled tables results in a SQL query. There's
no caching whatsoever in the current version - I haven't run into
performance issues, so no need to improve it yet ;)


-- 
Aaron B. <aaron@zadzmo.org>