Skip to content

Instantly share code, notes, and snippets.

@mranney

mranney/pop_denorm.js

Created October 27, 2010 03:46
Download ZIP

Revisions

  1. mranney revised this gist Oct 27, 2010. 2 changed files with 2 additions and 3 deletions.
    3 changes: 1 addition & 2 deletions Node.js client Redisql Oct 25 2010 → redisql.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,8 @@
    var redis = require("redis"),
    util = require("util"),
    client = redis.createClient(),
    commands;

    redis.debug_mode = true;
    exports.debug_mode = redis.debug_mode;

    // helper borrowed from node_redis
    function to_array(args) {
    2 changes: 1 addition & 1 deletion redisql_examples.js
    View file
    Original file line number Diff line number Diff line change
    @@ -13,7 +13,7 @@ client.create("TABLE", "worker", "id int,division int,health int,salary TEXT, na

    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);
  2. @JakSprats JakSprats created this gist Oct 26, 2010.
    192 changes: 192 additions & 0 deletions Node.js client Redisql Oct 25 2010
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,192 @@
    var redis = require("redis"),
    util = require("util"),
    client = redis.createClient(),
    commands;

    redis.debug_mode = true;

    // 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;
    128 changes: 128 additions & 0 deletions pop_denorm.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,128 @@
    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);
    });
    42 changes: 42 additions & 0 deletions redisql_examples.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    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);