SQL syntax in Lua?

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

SQL syntax in Lua?

Petite Abeille
Hello,

I would like to, hmmm, use some SQL like syntax in Lua and I was
wondering if anyone had any thoughts on such endeavor?

My current thinking is to chain function calls which mimic SQL
constructs. Something along these lines:

DB:Select( 'name' ):
         From( aSource ):
         Where( 'name', 'like', '%joe%' ):
         Where( 'city', '=', 'NYC' ):
         Order( 'name' )()

Or perhaps wrap the above in a function call which would execute in a
custom environment (providing additional substitution magic along the
way):

local aStatement = function()
         Select( name )
         From( aSource )
         Where( name, like, '%joe%' )
         Where( city, eq, 'NYC' )
         Order( name )
end

DB:run( aStatement )

Any thoughts on such constructs?

Thanks for any insights!

Cheers

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

Reply | Threaded
Open this post in threaded view
|

RE: SQL syntax in Lua?

Dowling, Brendan
See the Lua SQLite project for an excellent example of a way to do this.  

http://luasqlite.luaforge.net/lsqlite.html

SQLite is an embedded SQL database that is a perfect match for an embedded scripting language like Lua.  It basically lets you do cool things like:

db = sqlite.open("somefile")
for row in db:rows("SELECT * FROM users WHERE id>0") do
    print(unpack(row))
end
db:exec("some sql statement")


et cetera.  If you're comfortable with Lua, you'll like SQLite.  




-----Original Message-----
From: [hidden email] on behalf of PA
Sent: Tue 3/7/2006 11:29 PM
To: Lua list
Cc:
Subject: SQL syntax in Lua?
Hello,

I would like to, hmmm, use some SQL like syntax in Lua and I was
wondering if anyone had any thoughts on such endeavor?

My current thinking is to chain function calls which mimic SQL
constructs. Something along these lines:

DB:Select( 'name' ):
         From( aSource ):
         Where( 'name', 'like', '%joe%' ):
         Where( 'city', '=', 'NYC' ):
         Order( 'name' )()

Or perhaps wrap the above in a function call which would execute in a
custom environment (providing additional substitution magic along the
way):

local aStatement = function()
         Select( name )
         From( aSource )
         Where( name, like, '%joe%' )
         Where( city, eq, 'NYC' )
         Order( name )
end

DB:run( aStatement )

Any thoughts on such constructs?

Thanks for any insights!

Cheers

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





winmail.dat (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille
On 3/8/06, Dowling, Brendan <[hidden email]> wrote:

for row in db:rows("SELECT * FROM users WHERE id>0") do

The part I would like to express directly in Lua is the SQL statement itself. The statement could then be applied to a variety of 'sources' ( e.g. in memory tables, objects, traditional relational databases, file systems, what not).

Something along the lines of JoSQL (SQL for Java Objects), but without the need for a full blown SQL parser:

http://josql.sourceforge.net/

Thoughts?


Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Lisa Parratt
PA wrote:
> Thoughts?

Clone Apples CoreData Entity-Relationship modeling system? It's pretty
much intended to do exactly as you've described.

--
Lisa
Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille


On 3/8/06, Lisa Parratt <[hidden email]> wrote:
PA wrote:
> Thoughts?

Clone Apples CoreData Entity-Relationship modeling system? It's pretty
much intended to do exactly as you've described.




--
Cheers

--
PA
http://alt.textdrive.com/

Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Fabian Peña
PA wrote:


On 3/8/06, Lisa Parratt <[hidden email]> wrote:
PA wrote:
> Thoughts?

Clone Apples CoreData Entity-Relationship modeling system? It's pretty
much intended to do exactly as you've described.




--
Cheers

--
PA
http://alt.textdrive.com/

like this ( not finished and tested of curse)




function DB(filename)
    self = self or {}
    function self:Select(selectParam)
        return {
            Where = function (self,wherest)
                self.where = wherest
                return self
            end,
            From =  function (self,fromst)
                self.from = fromst
                return self
            end,
            OrderBy = function (self,orderst)
                self.order = orderst
                return self
            end,
            Go = function (self)

                self.order = self.order or ""
                self.from = self.from or ""
                self.where = self.where or ""
                selectParam = selectParam or "*"
                db = sqlite.open(filename)
                for row in db:rows("SELECT ".. self.selectst .. "FROM " .. self.from .. "WHERE " .. self.where .. "ORDER BY " .. self.order) do
                        print(unpack(row))
                end
            end
        }
    end
    return self
end



DB('somefile'):Select('name,phone'):From('employes'):Where('ID >0'):OrderBy('name'):Go()


Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille


On 3/8/06, Fabian Peña <[hidden email]> wrote:
DB('somefile'):Select('name,phone'):From('employes'):Where('ID >0'):OrderBy('name'):Go()


Yes, something along those lines.

One thing though:

Shouldn't the 'where' clause be 'exploded' in its components (e.g. key, operator, value)?

Where( 'id', '>', 0 )

Otherwise you will have to parse it to be able to make any sense of it, no?

Also, what about 'and', 'or' and such? If you pass them as literal string, you will need to parse them. Perhaps something like this, where each argument is a simple where clause:

And( Where( 'id', '>', 0 ), Where( 'id', '<', 10 ) )

--
Cheers

--
PA
http://alt.textdrive.com/

Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Lisa Parratt
PA wrote:
> And( Where( 'id', '>', 0 ), Where( 'id', '<', 10 ) )

I'd prefer the Scheme version ;) It's all getting a little... baroque :/

--
Lisa
Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Tomas-14
In reply to this post by Petite Abeille
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?
  Some years ago we start an effort in that direction but it
fails on this kind of stuff.  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.

  Tomas
Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille
In reply to this post by Lisa Parratt

On Mar 08, 2006, at 17:32, Lisa Parratt wrote:

> PA wrote:
>> And( Where( 'id', '>', 0 ), Where( 'id', '<', 10 ) )
>
> I'd prefer the Scheme version ;) It's all getting a little... baroque
> :/

Fair enough :)

So how would you express the following SQL statement using regular Lua
syntax?

"where id > 0 and id < 10"

Perhaps something like what KirbyBase does in Ruby:

result_set = plane_tbl.select { |r| r.country == 'USA' and r.speed >
350 }

http://www.netpromi.com/files/kirbybaserubymanual.html#select-method

Cheers

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

Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille
In reply to this post by Tomas-14

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/

Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Fabian Peña
In reply to this post by Petite Abeille
mmmm ...




Where("id",GREATER,0,AND,"id",LESS_OR_EQUAL,10)            :-P









PA wrote:


On 3/8/06, Fabian Peña <[hidden email]> wrote:
DB('somefile'):Select('name,phone'):From('employes'):Where('ID >0'):OrderBy('name'):Go()


Yes, something along those lines.

One thing though:

Shouldn't the 'where' clause be 'exploded' in its components (e.g. key, operator, value)?

Where( 'id', '>', 0 )

Otherwise you will have to parse it to be able to make any sense of it, no?

Also, what about 'and', 'or' and such? If you pass them as literal string, you will need to parse them. Perhaps something like this, where each argument is a simple where clause:

And( Where( 'id', '>', 0 ), Where( 'id', '<', 10 ) )

--
Cheers

--
PA
http://alt.textdrive.com/


Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille

On Mar 08, 2006, at 18:33, Fabian Peña wrote:

> Where("id",GREATER,0,AND,"id",LESS_OR_EQUAL,10)            :-P

Hmmm... what about grouping?

( ( id > 0 and id < 10 ) or ( date = today ) )

Perhaps one could use something like Han Zhao's Lip syntax:

{ { id, gt, 0 }, And { id, lt, 10 }, Or { date, eq, today } }

http://luaforge.net/forum/forum.php?forum_id=709

Cheers

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

Reply | Threaded
Open this post in threaded view
|

Re: SQL syntax in Lua?

Petite Abeille

On Mar 08, 2006, at 18:51, PA wrote:

> Perhaps one could use something like Han Zhao's Lip syntax:
>
> { { id, gt, 0 }, And { id, lt, 10 }, Or { date, eq, today } }
>
> http://luaforge.net/forum/forum.php?forum_id=709

Well, talking of which:

"CLSQL is a Common Lisp interface to SQL databases"

;; employees named Lenin
(clsql:select 'employee :where [= [slot-value 'employee 'last-name]
                                 "Lenin"])

http://clsql.b9.com/manual/csql-find.html

Cheers

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