O'Reilly logo

HBase: The Definitive Guide by Lars George

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Appendix E. Hush SQL Schema

Here is the HBase URL Shortener, or Hush, schema expressed in SQL:

CREATE TABLE user (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  username CHAR(20) NOT NULL,
  credentials CHAR(12) NOT NULL,
  roles CHAR(10) NOT NULL, // could be a separate table "userroles", but \
    for the sake of brevity it is folded in here, eg. "AU" == "Admin,User"
  firstname CHAR(20),
  lastname CHAR(30),
  email VARCHAR(60),
  CONSTRAINT pk_user PRIMARY KEY (id),
  CONSTRAINT idx_user_username UNIQUE INDEX (username)
);

CREATE TABLE url (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  url VARCHAR(4096) NOT NULL,
  refShortId CHAR(8),
  title VARCHAR(200),
  description VARCHAR(400),
  content TEXT,
  CONSTRAINT pk_url (id),
)

CREATE TABLE shorturl (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  userId INTEGER,
  urlId INTEGER,
  shortId CHAR(8) NOT NULL,
  refShortId CHAR(8),
  description VARCHAR(400),
  CONSTRAINT pk_shorturl (id),
  CONSTRAINT idx_shorturl_shortid UNIQUE INDEX (shortId),
  FOREIGN KEY fk_user (userId) REFERENCES user (id),
  FOREIGN KEY fk_url (urlId) REFERENCES url (id)
)

CREATE TABLE click (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  datestamp DATETIME,
  shortId CHAR(8) NOT NULL,
  category CHAR(2),
  dimension CHAR(4),
  counter INTEGER UNSIGNED,
  CONSTRAINT pk_clicks (id),
  FOREIGN KEY fk_shortid (shortId) REFERENCES shortid (id);
)

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required