Repository files navigation

https://github.com/johnae/relativity

Currently I'd say this is a bit experimental and while quite extensive I have yet to actually use it the way I thought I might. Hopefully someone finds itinteresting.

First off - this is a bitraw and may need a bit of redesign (perhaps more Luaisms in places if possible). It should be usable however.

Relativity is sort ofarel but forMoonScript andLua. As far as I know there is nothing like it (other than this) for Lua/MoonScript. This project owes alot to Rubys Arel and also to the nodejs project calledrel. It also shares some caveats with that project, namely:

• No database connections, it only builds queries.
• Where Ruby can do funky stuff, Lua sometimes can. When possibleLua-isms are used, otherwise it's a method/function (well - it probably is anyway behind the scenes).

The point of this (as with arel) is to ease the generation of complex SQL queries. This does NOT adapt to different RDBMS systems (yet at least). I only care about Postgres. Shouldn't be that difficult to extend though.

This could be used to create an ORM just like Arel is the ActiveRecord enabler in many ways. I might do something like this later.

To be honest, I'm only sure that this works properly withLuaJIT 2.x+. It should work with other Lua implementations too however - but I haven't tried. The CircleCI tests run on LuaJIT. The reason is that I'm only interested in LuaJIT (and OpenResty). Please test and help out if you feel like it.

Query generation hasn't been benchmarkedat all. I don't know whether there's a bottleneck, memory issue or something else hiding. I think there may be performance gains in increasing the amount of local use - a well known optimization for Lua. I'll hold off on any such optimizations until it's clear that they'd help.

This library is using my take on a class implementation for MoonScript:Classy.

See spec/relativity/integration_spec.moon for more examples.

    users=Relativity.table'users'    users\project(Relativity.star)\to_sql!

Generates

SELECT*FROM"users"

    users\where(users'name'\eq'Einstein')\to_sql!

Generates

SELECT*FROM"users"WHERE"users"."name"='Einstein'

The selection in SQL contains what you want from the database, this is calledaprojection.

    users\project users'id'-- => SELECT "users"."id" FROM "users"

Joins look like this:

    users\join(photos)\on users'id'\eq photos'user_id'-- => SELECT * FROM "users" INNER JOIN "photos" ON "users"."id" = "photos"."user_id"

Note that some of the above is taking advantage of MoonScript allowing to skip the parentheses in certain instances. Eg. this:

users'id'\eq photos'user_id'

Can also be written like:

users('id')\eq(photos('user_id'))

It doesn't matter but I just figured I'd point out that these are function calls.

Limit and offset are calledtake andskip:

    users\take5-- => SELECT * FROM "users" LIMIT 5    users\skip4-- => SELECT * FROM "users" OFFSET 4

GROUP BY is calledgroup:

    users\group users'name'-- => SELECT * FROM "users" GROUP BY "users"."name"

All operators are chainable:

    users\where(users'name'\eq'ricky')\project users'id'-- => SELECT "users"."id" FROM "users" WHERE "users"."name" = 'ricky'

    users\where(users'name'\eq'linus')\where users'age'\lt25

Multiple arguments can be given too:

    users\where users'name'\eq'linus', users'age'\lt25

OR works like this:

    users\where users'name'\eq'linus'\Or users'age'\lt25

Unfortunately 'or' is a reserved keyword in MoonScript/Lua and cannot be used. For now I've resorted to title case for these. It's ugly i.m.o. Therefore I've (for now) also added LPeg:ish operators. If you don't know LPeg, you can read more about it here:http://www.inf.puc-rio.br/~roberto/lpeg/.

So OR can also be written like this:

    users\where users'name'\eq'linus'+ users'age'\lt25

AS works in a similar fashion but is lowercased since it isn't reserved:

    users\project users'id'\as'user_id'-- => SELECT "users"."id" AS "user_id" FROM "users"

AND has the same problem as OR and must be written in title case or, as above, using an LPeg:ish operator - like this:

    users\where users'name'\eq'linus'* users'age'\lt25

And is less often used since it's assumed in a where.

There's also Not which is a reserved keyword, but that has been made into a getter which can be used lowercase. For now bothof the below do the same thing:

Using title case:

users\where users'id'\eq(10).not

Or lpeg:ish

users\where-users'id'\eq10

Both generate this:

SELECTFROM"users"WHERE NOT ("users"."id"=10)

Since I mostly care about Postgres, more advanced queries (Postgres specific) are possible, such as:

Relativity=require'relativity'Nodes=Relativity.Nodesusers=Relativity.table'users'others=Relativity.table'others'any=Relativity.func'ANY'coalesce=Relativity.func'COALESCE'array_agg=Relativity.func'array_agg'json_build_object=Relativity.func'json_build_object'to_json=Relativity.func'to_json'json_select=Relativity.select!json_select\from othersjson_object= json_build_object'id', others'id','name', others'name'json_select\project array_agg(json_object)\as'list'json_select\where others'id'\eq any users'things'json_select=Relativity.alias json_select,'things'things= to_json(Relativity.table'things''list')\as'things'user_employer= coalesce(users'employer','none')\as'employer'u= users\project users.star, user_employer, thingsu\join(json_select,Nodes.InnerJoinLateral)\ontrueu\where users'name'\like'%berg%'

Generates (via calling \to_sql! on the relation of course):

SELECT"users".*,       COALESCE("users"."employer",'none')AS"employer",       to_json("things"."list")AS"things"FROM"users"INNER JOIN LATERAL (SELECT array_agg(json_build_object('id',"others"."id",'name',"others"."name"))AS"list"FROM"others"WHERE"others"."id"= ANY("users"."things"))"things"ON't'WHERE"users"."name"LIKE'%berg%'

Running the tests requires bustedhttps://github.com/Olivine-Labs/busted and luasserthttps://github.com/Olivine-Labs/luassert.Since luassert comes with busted, only busted needs to be installed really. It also requires that moonscript is installed.

On Ubuntu you might go about it like this:

sudo apt-get install luarocks luajitsudo luarocks install bustedsudo luarocks install moonscript

To run the specs, runbusted spec.

I appreciate all feedback and help. If there's a problem, create an issue or pull request. Thanks!