CQL - Caffeinated Query Language
(or "SQL on Caffeine")
This may have been somewhat overly ambitious. Here is an attempt to write SQL(ish) declarative code in CoffeeScript. This requires the ECMAScript 6 Proxy feature. You can test it in node with --harmony-proxies.
Let's set up a template for defining proxies. (Taken from Benvie's comment on this issue)
forward = (->
_slice = Array.prototype.slice
_bind = Function.prototype.bind
_apply = Function.prototype.apply
_hasOwn = Object.prototype.hasOwnProperty
Forwarder = (target) ->
@target = target
this
Forwarder.prototype =
getOwnPropertyNames: -> Object.getOwnPropertyNames(@target)
keys: -> Object.keys(@target)
enumerate: ->
i = 0
keys = []
for value of @target
keys[i++] = value
keys
getPropertyDescriptor: (key) ->
o = @target;
while o
desc = Object.getOwnPropertyDescriptor o, key
if desc
desc.configurable = true;
return desc;
o = Object.getPrototypeOf o
getOwnPropertyDescriptor: (key) ->
desc = Object.getOwnPropertyDescriptor @target, key
if desc
desc.configurable = true
desc
defineProperty: (key, desc) -> Object.defineProperty @target, key, desc
get: (receiver, key) -> @target[key]
set: (receiver, key, value) ->
@target[key] = value;
true
has: (key) -> key of @target
hasOwn: (key) -> _hasOwn.call @target, key
delete: (key) ->
delete @target[key]
true
apply: (receiver, args) -> _apply.call @target, receiver, args
construct: (args) -> new (_bind.apply @target, [null].concat args);
forward = (target, overrides) ->
handler = new Forwarder target;
for k of Object overrides
handler[k] = overrides[k]
if typeof target is 'function'
return Proxy.createFunction handler,
-> handler.apply this, _slice.call arguments,
-> handler.construct _slice.call arguments
else
return Proxy.create handler, Object.getPrototypeOf Object target
forward
)();
Now define a proxy object and some suspicious global variables and functions:
sql = forward {
tables: {}
finalize: ->
if typeof @activeRows isnt 'function'
@result = []
for row in @activeRows
@result.push (val for val, i in row when @activeTable.columns[i] in @activeColumns)
delete @activeRows
delete @activeColumns
delete @activeTable
run: (q) ->
q.call(this)
@finalize()
result = @result
delete @result
if typeof result isnt 'function' then console.log result
return result
}, {
get: (o,name) ->
if name of @target
return @target[name];
(args...) -> {
name
args
}
}
int = Number
varchar = (l) -> String
TABLE = (x) -> x
INTO = (x) -> x
CREATE = (tableData) ->
name = tableData.name
table =
columns: []
column = tableData.args[0]
table[column.name] = []
table.columns.push(column.name)
while column = column.args[1]
table[column.name] = []
table.columns.push(column.name)
sql.tables[name] = table
sql.result = "Created table '#{name}'"
INSERT = (table) -> sql.activeTable = sql.tables[table().name]
VALUES = (rows...) ->
for row in rows
for val, i in row
column = sql.activeTable.columns[i]
sql.activeTable[column].push val
sql.result = "Inserted #{rows.length} rows"
FROM = (table) ->
sql.activeTable = sql.tables[table().name]
SELECT = (columns...) ->
sql.activeColumns = []
for col in columns
if typeof col is 'function'
col = col()
sql.activeColumns.push col.name
sql.activeRows = []
for val in sql.activeTable[sql.activeTable.columns[0]]
sql.activeRows.push []
for col in sql.activeTable.columns
for val, i in sql.activeTable[col]
sql.activeRows[i].push val
IN = (list) -> { op: 'in', list }
WHERE = (column) ->
i = sql.activeTable.columns.indexOf(column.name)
if column.args[0].op is 'in'
list = column.args[0].list
sql.activeRows = (row for row in sql.activeRows when row[i] in list)
else
console.log 'Not supported!'
ASC = 'asc'
DESC = 'desc'
BY = (x) -> x
ORDER = (column) ->
i = sql.activeTable.columns.indexOf(column.name)
order = if column.args[0] is sql.ASC then 1 else -1
sql.activeRows.sort (a,b) ->
if a[i] < b[i]
return -order
else if a[i] > b[i]
return order
else
return 0
Well that was quite a lot of setup! But now we can do the following (input/output in a console style):
> sql.run ->
CREATE TABLE @books(
@title varchar(255),
@author varchar(255),
@year int
);
Create Table 'books'
> sql.run ->
INSERT INTO @books
VALUES ['The C++ Programming Language', 'Bjarne Stroustrup', 1985],
['Effective C++', 'Scott Meyers', 1992],
['Exceptional C++', 'Herb Sutter', 2000],
['Effective STL', 'Scott Meyers', 2001];
Inserted 4 rows
> sql.run ->
SELECT @title, @year FROM @books
WHERE @author IN ['Bjarne Stroustrup', 'Scott Meyers']
ORDER BY @year DESC;
[ [ 'Effective STL', 2001 ],
[ 'Effective C++', 1992 ],
[ 'The C++ Programming Language', 1985 ] ]
It's not an actual polyglot, but that's not really the point. I know that @ is used for variables in SQL, but I need all the @s for column and table names because I haven't found a way to proxy the global object (and I wouldn't be surprised if it's really not possible - and for a good reason).
I also changed some parentheses into brackets (in particular after VALUES and IN). Unfortunately, what I couldn't figure out at all is a way to allow normal conditionals like year > 2000, because they would evaluate to a boolean right away.
Still this looks a lot like SQL and is definitely more declarative than imperative/functional/object-oriented so it should qualify nicely for the question. I'm actually thinking if I polished the code a bit and supported a few more features, this could be a useful CoffeeScript module.
Anyway, this was fun! :)
For those not too familiar with CoffeeScript, the SQL queries compile to the following JavaScript:
sql.run(function() {
return CREATE(
TABLE(
this.books(
this.title(varchar(255),
this.author(varchar(255),
this.year(int)))
)
)
);
});
sql.run(function() {
INSERT(INTO(this.books));
return VALUES([...], ['Effective C++', 'Scott Meyers', 1992], [...], [...]);
});
sql.run(function() {
SELECT(this.title, this.year(FROM(this.books)));
WHERE(this.author(IN(['Bjarne Stroustrup', 'Scott Meyers'])));
return ORDER(BY(this.year(thisESC)));
});
{...}blocks with statements without CS complaining because it expects an object description. – m.buettner Mar 20 at 20:36.litcoffee. It might help. – Ismael Miguel Mar 20 at 22:53