Projection, please help

[siscia]

I believe there is a lot of interest into mixing futher Redis and SQLite.

I want to create an abstraction called Projection that will contains the best of both Redis AND SQL.

We project on a table.

Given the table

CREATE TABLE user VALUES(
    ID PRIMARY KEY, 
    username TEXT, 
    points INT)

We can use that table as a standard SQL table but also project its values on the redis key space.

We could access the values of username or points using the following syntax:
REDISQL.GET user:ID:username or REDISQL.GET user:ID:points

Then we could also set values using a similar syntax:
REDISQL.SET user:ID:username "siscia" or REDISQL.SET user:ID:points 12 this changes will be reported into the SQL table.

> SELECT points FROM user WHERE username = 'siscia';
12

And of course it will work the other way around.

REDISQL.EXEC DB "UPDATE user SET points = 24 WHERE username = 'siscia' and ID = 1234"
> OK
REDISQL.GET username:1234:points
> 24

The syntax that for now seems to make sense for me to create a projection is something like:

REDISQL.PROJECT DB table

after this command the SQL table and the redis key space should appear in sync.
Then it would be possible to remove the projection with

REDISQL.REMOVE_PROJECTION table

Clearly this means that tables with the same name in different databases cannot be projected at the same time.
This could be easily fixed adding as prefix, the name of the database, but I believe it will bring more problem than benefits.

[siscia]

I re-manage a little bit the syntax.

I believe it could be interesting to explore a different syntax and semantic.

I am thinking about commands like:

REDISQL.GET tableName:[index=>value]:[fields]
REDISQL.INSERT tableName:[fields] [values]
REDISQL.SET tableName:[index=>value]:[fields] [value]
REDISQL.DELETE tableName:[index=>value]

This is interesting because it is possible to define a regular grammar for it and use that for parsing:

GET = tableName:tableName sep 
	ind:((indexes sep fields) / (fields sep indexes)) 
    multipleSpace values:values {
    return {
    	tableName: tableName,
        indexes: ind[0]["indexes"],
        fields: ind[2]["fields"],
        values: values
    }
}
SET = tableName sep ((indexes sep fields) / (fields sep indexes)) multipleSpace values
INSERT = tableName sep fields multipleSpace values
DELETE = tableName sep indexes

sep = ':'
arrow = '=>'

tableName = ch:[a-zA-Z0-9]* {return ch.join("")}
indexName = ch:[a-zA-Z0-9]* {return ch.join("")}
indexValue = ch:[a-zA-Z0-9]* {return ch.join("")}
field = ch:[a-zA-Z0-9]* {return [ch.join("")]}
value = ch:[a-zA-Z0-9]* {return [ch.join("")]}
space = ' '
multipleSpace = ' '* {return ' '}

index = name:indexName arrow value:indexValue {return [[name, value]]}
indexes = head:index tail:(sep index)* {
	return {indexes: tail.reduce(function (result, element) {
    	return result.concat(element[1])
    }, head)}
} 

fields = head:field tail:(sep field)* {
	return {fields: tail.reduce(function (result, element) {
    	return result.concat(element[1])
    }, head)}
}

values = head:value tail:(space value)* {
	return tail.reduce(function (result, element) {
    	return result.concat(element[1])
    }, head)
}

[siscia]

I decide that projections are not a good idea yet.

I will skip this for now.