proposed gather.sql

gather.sql

BEGIN;

DROP TABLE IF EXISTS `gather_list`;
CREATE TABLE `gather_list` (
  -- Primary key
  `list_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -- Key to user.user_id
  `list_user_id` INT UNSIGNED NOT NULL,
  -- List title
  `list_title` VARCHAR(255) BINARY NOT NULL DEFAULT '',
  -- List description
  `list_description` VARCHAR(140) BINARY DEFAULT '',
  -- Privacy setting
  `list_public` BOOLEAN NOT NULL DEFAULT 1,
  -- Timestamp last updated
  `list_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  -- Timestamp when user was last sent a notification e-mail;
  -- cleared when the user visits the page.
  `list_notificationtimestamp` VARBINARY(14) DEFAULT NULL
);

CREATE INDEX `list_id` ON `gather_list` (list_id);
CREATE INDEX `list_user_id` ON `gather_list` (list_user_id);

DROP TABLE IF EXISTS `gather_list_item`;
CREATE TABLE `gather_list_item` (
  -- Primary key
  `item_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -- Id of list
  `list_id` INT NOT NULL,
  -- Key to page_namespace
  `item_namespace` INT NOT NULL DEFAULT 0,
  -- Key to page_title
  `item_title` VARCHAR(255) BINARY NOT NULL DEFAULT '',
  -- Sort order
  `item_order` INT DEFAULT 0
);

CREATE INDEX `list_id` ON `gather_list_item` (list_id);

-- DUMMY DATA

INSERT INTO `gather_list` (list_user_id, list_title, list_description, list_public) VALUES (2,'Animals I like','Animals I really like',1);
INSERT INTO `gather_list_item` (list_id, item_namespace, item_title, item_order) VALUES (1,0,'Duck', 0);
INSERT INTO `gather_list_item` (list_id, item_namespace, item_title, item_order) VALUES (1,1,'Cow', 1);
INSERT INTO `gather_list_item` (list_id, item_namespace, item_title, item_order) VALUES (1,1,'Bird', 2);
INSERT INTO `gather_list_item` (list_id, item_namespace, item_title, item_order) VALUES (2,1,'Bad', 2);

COMMIT;