SQL to JSQL-SX

Map SQL queries to JSQL-SX calls

Some examples on how SQL-like query strings can be mapped into the jsql-sx API programatic calls.

Create a database named mail

SQL

SQL> CREATE DATABASE mail;

JSQL-SX API

var db = jsql.DB.open("mail");

Create a table named inbox

SQL

SQL> CREATE TABLE inbox (
  id INT NOT NULL,
  date TEXT NOT NULL,
  from TEXT NOT NULL,
  to, TEXT NOT NULL,
  msg, TEXT NOT NULL,
  new, BIT NOT NULL,
  PRIMARY KEY(id)
);

JSQL-SX API

db.create("inbox", 
  [["id", "INTEGER", "PRIMARY KEY"],
   ["date", "TEXT", "NOT NULL"],
   ["from", "TEXT", "NOT NULL"],
   ["to", "TEXT", "NOT NULL"],
   ["msg", "TEXT", "NULL"],
   ["new", "BOOLEAN", "NOT NULL"]]
);

Add a new entry to inbox

SQL

SQL> INSERT INTO inbox VALUES (
  NULL, 
  "20151005",
  "richard@company.com", 
  "anthony@company.com", 
  "Hi There!",
  1
);

JSQL-SX API

var id = db.insert("inbox", 
  [null,
   "20151005",
   "richard@company.com",
   "anthony@company.com",
   "Hi There!",
   true]
);

Select entries from inbox

SQL

SQL> SELECT from, to FROM inbox WHERE (id > 10 AND new = 1);

JSQL-SX API

var result = db.select("inbox", ["from", "to"], [["id", ">", 10], "AND", ["new", "=", true]]);

SQL

SQL> SELECT * FROM inbox WHERE (
  (from = 'john@company.com' AND to = 'martha@company.com') OR 
  (date >= '20151001' AND new = 0)
);

JSQL-SX API

var result = db.select("inbox", ["*"], 
  [
    [["from", "=", "john@company.com"], "AND", ["to", "=", "martha@company.com"]], 
    "OR", 
    [["date", ">=", "20151001"], "AND", ["new", "=", false]]
  ]
);

Update entries in inbox

SQL

SQL> UPDATE inbox SET new = 0, date = "20151006" WHERE (id = 10 AND id = 7);

JSQL-SX API

var n = db.update("inbox", 
  ["new", false, "date", "20151006"], 
  [["id", "=", 10], "AND", ["id", "=", 7]]
);

Add a new column to inbox

SQL

SQL> ALTER TABLE inbox ADD tag TEXT;

JSQL-SX API

var success = db.alter("inbox", "add", "tag", "TEXT", "NULL");

Modify a column in inbox

SQL

SQL> ALTER TABLE inbox MODIFY tag TEXT NOT NULL;

JSQL-SX API

var success = db.alter("inbox", "MODIFY", "tag", "TEXT", "NOT NULL");

Drop a column from inbox

SQL

SQL> ALTER TABLE inbox DROP tag;

JSQL-SX API

var success = db.alter("inbox", "drop", "tag");

Delete entries from inbox

SQL

SQL> DELETE FROM inbox WHERE (date < '20140101' AND new = 0);

JSQL-SX API

var n = db.delete("inbox", [["date", "<", "20140101"], "AND", ["new", "=", false]]);

Drop the inbox table

SQL

SQL> DROP TABLE inbox;

JSQL-SX API

db.drop("inbox");

Drop the mail database

SQL

SQL> DROP DATABASE mail;

JSQL-SX API

// Invoke the 'drop' class method //
jsql.DB.drop("mail");