Re: Making LuaSQL roar?

[Klaus Ripke <paul-lua@...>]

hi

On Sun, Aug 14, 2005 at 03:52:22PM +0200, PA wrote:
> >What would such a tool do, mapping which object system?
> Forget 'objects'. Think plain Lua tables then.
great plan

> >Isn't most of the OR/M work about type mapping,
> No. They are mostly about relationships mapping and object graphs.
yes, well, type including classes, which we do not have.
which is good.

> >which is mostly a non-issue with Lua?
> Types? From Lua's point of view, they are not that interesting indeed  
> as they are mostly reduced to string vs number.
so we have no objects and no mapping, only relations.

> >Or would you want to set up set methods derived
> >from constraints found in the DB?
> Yes.
I see. Nice but difficult.
Usually stops where things are starting to get interesting.
To learn the full truth you usually do not get by
with the standard information_schema.
And if contraints involves expressions using non-standard
SQL functions or even one of the DBs builtin languages,
you are out of luck.


> >Or have Lua errors trigger DB rollbacks?
> Hmmm...?!?
> 
> >Or have "finally" commit?
> Autocommit?
well, where your "transaction block" ends.
unless, of course, an error rolled back.
But I would really just use a pcall.


> >Use yet more new syntax for transactions?
> Hopefully not.
*nod*

> Automatic relationship resolution perhaps?
I see. Nice but difficult, too.
Again, what you learn from the information schema is sufficient
only in simple cases. And even then only where the DB was set up
carefully. Nothing interesting is automagic anyway.

> Say you have two (SQL) tables: Movie and Studio. With a one-to-one  
> relationship from 'Movie' to 'Studio' and a reverse one-to-many  
> relationship between Studio and Movie.
so this is simply a one-to-many relationship

> After fetching, you could now query your (Lua) table like such:
> 
> local aStudio = aMovie.studio
claro
> local someMovies = aStudio.movies
much less clear.
what do we get - a table, even for millions of movies?
an iterator ("cursor"), possibly keeping a read lock on
the table for some indeterminate time?
a subquery, allowing for further selection?

One can create a table or userdata with a nifty metatable
providing for all of this, but it's not that transparent
in general. 

> Furthermore, you could say:
> 
> aMovie.studio = anotherStudio
> 
> And have the library transparently handle the resulting  
> updates/deletes, etc...
... more or less transparently.

To make it work in a complex environment, you still will
need to apply higher level rules for your application
like how to deal with or avoid deadlocks, how to cope
with phantom inserts and so on, which vary with the
application environment (e.g. single writer/full lock
vs. fine grained parallelity, transaction based access
affecting only a few rows at once vs. analytical acces
over whole tables and so on).
DB metadata does not have the slightest clue of this.

> >Wouldn't we need a "full fledge" object system first?
> 
> Forget the "object system". Lua has none. Lets stay focus on the T/RM  
> mapping for a second :)
d'accord
yet there is not only no object, there also is usually no mapping
of data but the trivial.

> >Could you give hints what could be improved in plain lua-style
> >use of LuaSQL?
> 
> Mostly automatic relationships resolution, manipulation and related  
> nastiness like row uniquing, faulting and such:
> 
> http://developer.apple.com/documentation/Cocoa/Conceptual/CoreData/ 
> Articles/cdFaultUniquing.html
I see. Clearly wrapping SQL is very useful.
Cannot even remember when I last wrote a DB app not starting
with a function to give my "INSERT OR UPDATE".

Anyway, I'd plea to forget the information_schema and instead
configure the wrapper in Lua, which will actually be much more
portable. After all, the crossword question "flexible and
versatile configuration language" with 3 letters does not
resolve to "SQL".


cheers