Re: SQL syntax in Lua?

[PA <petite.abeille@...>]

On Mar 08, 2006, at 18:05, Tomas wrote:

> And( Where( 'id', '>', 0 ), Where( 'id', '<', 10 ) )
>
> 	Correct me if I'm wrong, but I think the above code will
> produce the string:
>
> "where id > 0 and id < 10"
>
> 	Am I correct?

Yes. Assuming it's 'rendered' to a SQL database  of some  sort.

Alternatively, it could get evaluated in memory:

function self:evaluate( anObject )
        local someConditions = self:conditions()

        if someConditions ~= nil then
                if someConditions:hasData() == true then
                        for _, aCondition in someConditions:iterator() 
do
                                if aCondition:evaluate( anObject ) == 
false then
                                        return false
                                end
                        end

                        return true
                end

                error( ( "%s.evaluate: empty conditions" ):format( 
self:className() ) )
        end

        error( ( "%s.evaluate: nil conditions" ):format( 
self:className() ) )
end

Where 'aCondition:evaluate()' might look like the following:

function self:evaluate( anObject )
        local aKey = self:key()

        if aKey ~= nil then
                local anOperator = self:operator()

                if anOperator ~= nil then
                        local anOperation = self:operations()[ 
anOperator ]

                        if anOperation ~= nil then
                                local anObjectValue = anObject

                                if LUObject:isObject( anObject ) == 
true then
                                        anObjectValue = 
anObject:invoke( aKey )
                                end

                                return anOperation( anObjectValue, 
self:value() )
                        end

                        error( ( "%s.evaluate: unknown operator %q" 
):format( self:className(), anOperator ) )
                end

                error( ( "%s.evaluate: nil operator" ):format( 
self:className() ) )
        end

        error( ( "%s.evaluate: nil key" ):format( self:className() ) )
end

Where 'anOperation' might be something like this:

local function GreaterThan( aValue, anotherValue )
        if aValue ~= nil and anotherValue ~= nil then
                return aValue > anotherValue
        end

        return false
end


> 	Some years ago we start an effort in that direction but it
> fails on this kind of stuff.

I see.


> Now we changed that approach to a set of
> functions to solve the most common cases, like:
>
> sql.select (table_name, fields_list, optional_where, optional_extra)
>
> 	The word 'where' is added automatically if optional_where is
> given; optional_extra if given, is added to the end of the statement.
> This solution does not solve all problems, but the most common cases.
> We also have other functions to other common SQL statements (delete,
> insert and update; these last two functions receive tables representing
> pairs of field = value).  If someone is interested, we can share our 
> code.

What I  really would like to figure out is a way to "naturally" express 
a "structured query" in plain Lua. Once I have such a structure, I can 
then choose to resolve it against a database or in memory or wherever 
it might make sense.

Cheers

--
PA, Onnay Equitursay
http://alt.textdrive.com/