pop_denorm.js

var redisql = require("./redisql");
var client  = redisql.createClient();
var init    = true;
var verbose = false;

redisql.debug_mode = true;

function print_response(obj) {
    var lines = [];
    Object.keys(obj).forEach(function (key) {
        lines.push("   " + key + ": " + obj[key].toString());
    });
    console.log("Response hash: \n" + lines.join("\n"));
}

function run_test() {
    if (init) {
        console.log("Initializing");
        client.flushdb();
        if (verbose) {
            console.log("First populate user:id:[name,age,status]");
        }
        client.set("user:1:name",   "bill");
        client.set("user:1:age",    "33");
        client.set("user:1:status", "member");

        if (verbose) {
            console.log("Then  populate user:id:address[street,city,zipcode]");
        }
        client.set("user:1:address:street",  "12345 main st");
        client.set("user:1:address:city",    "capitol city");
        client.set("user:1:address:zipcode", "55566");

        if (verbose) {
            console.log("Then  populate user:id:payment[type,account]");
        }
        client.set("user:1:payment:type",    "credit card");
        client.set("user:1:payment:account", "1234567890");

        client.set("user:2:name",           "jane");
        client.set("user:2:age",            "22");
        client.set("user:2:status",         "premium");
        client.set("user:2:address:street", "345 side st");
        client.set("user:2:address:city",    "capitol city");
        client.set("user:2:address:zipcode", "55566");
        client.set("user:2:payment:type",    "checking");
        client.set("user:2:payment:account", "44441111");

        client.set("user:3:name",            "ken");
        client.set("user:3:age",             "44");
        client.set("user:3:status",          "guest");
        client.set("user:3:address:street",  "876 big st");
        client.set("user:3:address:city",    "houston");
        client.set("user:3:address:zipcode", "87654");
        client.set("user:3:payment:type",    "cash");

        if (verbose) {
            console.log("Keys are now populated");
            console.log("");
            console.log("Finally search through all redis keys using ");
            console.log("  the primary wildcard:\"user\" ");
            console.log("  and then search through those results using:");
            console.log("    1.) the secondary wildcard: \"*:address\" ");
            console.log("    2.) the secondary wildcard: \"*:payment\" ");
            console.log("    3.) non matching stil match the primary wildcard");
            console.log("");
            console.log("The 3 results will be normalised into the tables:");
            console.log("  1.) user_address");
            console.log("  2.) user_payment");
            console.log("  3.) user");
        }
    }

    client.norm("user", "address,payment", function (err, res) {
        if (err) { throw err; }
        console.log("Response: " + res);
        process.exit();
    });
    
    client.select("user.pk,user.name,user.status,user_address.city,user_address.street,user_address.pk,user_address.zipcode", "user,user_address", "user.pk = user_address.pk AND user.pk BETWEEN 1 AND 5", redisql.print);

    if (verbose) {
        console.log("\n\n");
        console.log("If pure lookup speed of a SINGLE column is the dominant use case");
        console.log("We can now denorm the redisql tables into redis hash-tables");
        console.log("which are faster for this use-case");
        console.log("");
        console.log("denorm user \user:* ");
    }
    client.denorm("user", 'user:*', redisql.print);

    console.log("HGETALL user:1 ");
    client.hgetall("user:1", function (err, res) {
        if (err) {
            throw err;
        }
        console.log("Printing response for user:1");
        print_response(res);
    });

    console.log("denorm user_payment \user:*:payment ");
    client.denorm("user_payment", 'user:*:payment', redisql.print);
    console.log("HGETALL user:2:payment ");
    client.hgetall("user:2:payment", function (err, res) {
        if (err) {
            throw err;
        }
        print_response(res);
    });

    console.log("denorm user \user:*:address ");
    client.denorm("user_address", 'user:*:address', redisql.print);
    console.log("HGETALL user:3:address ");
    client.hgetall("user:3:address", function (err, res) {
        if (err) {
            throw err;
        }
        print_response(res);
        client.quit();
    });
}

console.log("Connecting to RediSQL server...");
client.on("connect", run_test);
client.on("error", function (e) {
    console.warn("Error connecting to RediSQL server: " + e);
    process.exit(1);
});

redisql.js

var redis = require("redis"),
    util = require("util"),
    commands;

exports.debug_mode = redis.debug_mode;

// helper borrowed from node_redis
function to_array(args) {
    var i;
    var len = args.length;
    var arr = new Array(len);
    for (i = 0; i < len; i += 1) {
        arr[i] = args[i];
    }
    return arr;
}

// new commands that we'll be adding
commands = [
    "CHANGEDB",
    "DUMP", "DESC",
    "NORM", "DENORM"
];

// merge these in with the RedisClient prototype in both upper and lower case
commands.forEach(function (command) {
    redis.RedisClient.prototype[command] = function () {
        var args = to_array(arguments);
        args.unshift(command); // put command at the beginning

        // remove this when everything works
        console.log("Sending new command " + command +
                    " with args " + JSON.stringify(args));

        this.send_command.apply(this, args);
    };
    redis.RedisClient.prototype[command.toLowerCase()] =
        redis.RedisClient.prototype[command];
});


// CREATE
redis.RedisClient.prototype["CREATE"] = function () {
    var args = to_array(arguments), mod_args, 
        first_arg = args[0].toLowerCase(), command, sargs;

    if (first_arg === "table") {
        mod_args = "TABLE " + args[1] + " (" + args[2] + ")";
    } else if (first_arg === "index") {
        mod_args = "INDEX " + args[1] + " ON " + args[2] + " (" + args[3] + ")";
    } else { // usage error
        throw new Error("Bad args to \"CREATE\" " + args[0] +
                         ", must be either \"TABLE\" OR \"INDEX\"");
    }

    // TODO - error on args.length > 4 || args.length < 2;

    command = "CREATE";
    sargs = mod_args.split(' ');
    sargs.unshift(command); // put command at the beginning
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }

    //console.log("Sending " + command + " with args ", util.inspect(sargs));
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));

    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["create"] = redis.RedisClient.prototype["CREATE"];

// DROP
redis.RedisClient.prototype["DROP"] = function () {
    var args = to_array(arguments);
    var mod_args;
    if (args[0].toLowerCase() === "table") {
        mod_args = "TABLE " + args[1];
    } else if (args[0].toLowerCase() === "index") {
        mod_args = "INDEX " + args[1];
    } else { // usage error
        throw new Error("Bad args to \"DROP\" " + args[0] +
                         ", must be either \"TABLE\" OR \"INDEX\"");
    }
    var command = "DROP";
    var sargs   = mod_args.split(' ');;
    sargs.unshift(command); // put command at the beginning
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));
    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["drop"] = redis.RedisClient.prototype["DROP"];

// SELECT
redis.RedisClient.prototype["SELECT"] = function () {
    var args = to_array(arguments);
    var mod_args;
    if (args.length != 1) { // rewrite Redisql SELECT * FROM tbl WHERE id = 4
        mod_args = args[0] + " FROM "  + args[1] + " WHERE " + args[2];
    } else { // redis SELECT DB
        mod_args = arguments;
    }
    var command = "SELECT";
    var sargs   = mod_args.split(' ');;
    sargs.unshift(command); // put command at the beginning
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));
    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["select"] = redis.RedisClient.prototype["SELECT"];

// SCANSELECT
redis.RedisClient.prototype["SCANSELECT"] = function () {
    var args     = to_array(arguments);
    var mod_args = args[0] + " FROM "  + args[1];;
    if (args.length > 3) {
        mod_args += " WHERE " + args[2];
    }
    var command = "SCANSELECT";
    var sargs   = mod_args.split(' ');;
    sargs.unshift(command); // put command at the beginning
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));
    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["scanselect"] = redis.RedisClient.prototype["SCANSELECT"];

// INSERT
redis.RedisClient.prototype["INSERT"] = function () {
    var args = to_array(arguments),
        mod_args = "INTO " + args[0] + " VALUES",
        command  = "INSERT",
        sargs    = mod_args.split(' ');;
        
    sargs.unshift(command); // put command at the beginning
    sargs.push(args[1]);    // put val_list at end as single argument
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }
    // TODO - error if args length is invalid
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));
    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["insert"] = redis.RedisClient.prototype["INSERT"];

// DELETE
redis.RedisClient.prototype["DELETE"] = function () {
    var args     = to_array(arguments);
    var mod_args = "FROM " + args[0] + " WHERE "  + args[1];
    var command  = "DELETE";
    var sargs    = mod_args.split(' ');;
    sargs.unshift(command); // put command at the beginning
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));
    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["delete"] = redis.RedisClient.prototype["DELETE"];

// UPDATE
redis.RedisClient.prototype["UPDATE"] = function () {
    var args     = to_array(arguments);
    var mod_args = args[0] + " SET"
    var sargs    = mod_args.split(' ');;
    sargs.push(args[1]); // push val_list at end as single argument
    sargs.push("WHERE");
    var wargs = args[2].split(' ');
    for (var i = 0; i < wargs.length; i++) {
        sargs.push(wargs[i]);
    };
    var command = "UPDATE";
    sargs.unshift(command); // put command at the beginning
    if (typeof args[args.length - 1] === "function") {
        sargs.push(args[args.length - 1]);
    }
    console.log("Sending " + command + " with args " + JSON.stringify(sargs));
    this.send_command.apply(this, sargs);
};
redis.RedisClient.prototype["update"] = redis.RedisClient.prototype["UPDATE"];

exports.createClient = function (port_arg, host_arg) {
    return redis.createClient(port_arg, host_arg);
};

exports.print = redis.print;

redisql_examples.js

var redis  = require("./redisql"),
    sys    = require('sys'),
    client = redis.createClient();

function last_command(err, res) {
    console.log("Last result: " + res.toString());
    client.quit();
}

client.flushdb(redis.print);

client.create("TABLE", "worker", "id int,division int,health int,salary TEXT, name TEXT", redis.print);

client.create("INDEX", "worker:division:index", "worker", "division", redis.print);
client.create("INDEX", "worker:health:index", "worker", "health", redis.print);

client.insert("worker", "(1,11,2,60000.66,jim)", redis.print);
client.insert("worker", "(2,22,1,30000.33,jack)", redis.print);
client.insert("worker", "(3,33,4,90000.99,bob)", redis.print);
client.insert("worker", "(4,44,3,70000.77,bill)", redis.print);
client.insert("worker", "(6,66,1,12000.99,jan)", redis.print);
client.insert("worker", "(7,66,1,11000.99,beth)", redis.print);
client.insert("worker", "(8,11,2,68888.99,mac)", redis.print);
client.insert("worker", "(9,22,1,31111.99,ken)", redis.print);
client.insert("worker", "(10,33,4,111111.99,seth)", redis.print);
 
client.scanselect("*", "worker", redis.print);
client.scanselect("*", "worker", "name=bill", redis.print);

client.select("*", "worker", "id=1", redis.print);
client.update("worker", "name=JIM", "id = 1", redis.print);
client.select("*", "worker", "id = 1", redis.print);
 
client.delete("worker", "id = 2", redis.print);
client.select("*", "worker", "id = 2", redis.print);
 
client.desc("worker", redis.print);
client.dump("worker",  redis.print);
 
client.drop("index", "worker:health:index",  redis.print);

client.drop("table", "worker", last_command);