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>>

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/