sql_defs/tables.sql

   1 SET AUTOCOMMIT=0;
   2 START TRANSACTION;
   3
   4 DROP TABLE IF EXISTS `library_amazon_images`
   5 ;
   6 CREATE TABLE IF NOT EXISTS `library_amazon_images` (
   7     `image_id`      char(36) NOT NULL DEFAULT 'UUID()',
   8     `asin`          varchar(255) NOT NULL,
   9     `category`      varchar(255) NOT NULL,
  10     `size`          varchar(20) NOT NULL,
  11     `width`         int(11) NOT NULL,
  12     `height`        int(11) NOT NULL,
  13     `url`           text NOT NULL,
  14     `date_created`  datetime,
  15     `date_updated`  datetime,
  16     PRIMARY KEY (`image_id`),
  17     INDEX (`asin`),
  18     INDEX (`asin`,`size`)
  19 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
  20 ;
  21
  22 DROP TABLE IF EXISTS `library_people`
  23 ;
  24 CREATE TABLE IF NOT EXISTS `library_people` (
  25     `person_id` char(36) NOT NULL DEFAULT 'UUID()',
  26     `name`      text NOT NULL,
  27     PRIMARY KEY (`person_id`),
  28     FULLTEXT INDEX (`name`)
  29 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
  30 ;
  31
  32 DROP TABLE IF EXISTS `library_item_person_xrf`
  33 ;
  34 CREATE TABLE IF NOT EXISTS `library_item_person_xrf` (
  35     `item_id`       char(36) NOT NULL,
  36     `person_id`     char(36) NOT NULL,
  37     `person_type`   varchar(20),
  38     `date_created`  datetime,
  39     PRIMARY KEY (`item_id`,`person_id`),
  40     INDEX (`item_id`)
  41 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
  42 ;
  43
  44 DROP TABLE IF EXISTS `library_books`
  45 ;
  46 CREATE TABLE IF NOT EXISTS `library_books` (
  47     `book_id`         char(36) NOT NULL DEFAULT 'UUID()',
  48     `isbn`            varchar(10),
  49     `ean`             varchar(13),
  50     `asin`            varchar(255),
  51     `title`           text,
  52     `binding`         varchar(255),
  53     `dewey_decimal`   float,
  54     `publisher`       text,
  55     `published`       date,
  56     `released`        date,
  57     `studio`          text,
  58     `pages`           int(11),
  59     `height`          int(11),
  60     `height_units`    varchar(255),
  61     `length`          int(11),
  62     `length_units`    varchar(255),
  63     `width`           int(11),
  64     `width_units`     varchar(255),
  65     `weight`          int(11),
  66     `weight_units`    varchar(255),
  67     `detail_page_url` text,
  68     `date_created`    datetime,
  69     `date_updated`    datetime,
  70     PRIMARY KEY  (`book_id`),
  71     INDEX (`isbn`),
  72     INDEX (`ean`),
  73     INDEX (`asin`),
  74     INDEX (`date_updated`),
  75     FULLTEXT INDEX (`title`)
  76 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
  77 ;
  78
  79 DROP TABLE IF EXISTS `library_movies`;
  80 CREATE TABLE IF NOT EXISTS `library_movies` (
  81     `movie_id`                char(36) NOT NULL DEFAULT 'UUID()',
  82     `asin`                    varchar(255),
  83     `isbn`                    varchar(10),
  84     `ean`                     varchar(13),
  85     `upc`                     varchar(14),
  86     `title`                   text NOT NULL,
  87     `aspect_ratio`            varchar(10),
  88     `audience_rating`         varchar(50),
  89     `binding`                 varchar(255),
  90     `label`                   varchar(255),
  91     `manufacturer`            varchar(255),
  92     `studio`                  varchar(255),
  93     `publisher`               varchar(255),
  94     `theatrical_release_date` date,
  95     `original_release_date`   date,
  96     `release_date`            date,
  97     `region_code`             tinyint(4),
  98     `running_time`            int(11),
  99     `number_of_items`         int(11),
 100     `detail_page_url`         text,
 101     `package_height`          int(11),
 102     `package_length`          int(11),
 103     `package_width`           int(11),
 104     `package_weight`          int(11),
 105     `list_price_amount`       int(11),
 106     `currency_code`           varchar(3),
 107     `date_created`            datetime,
 108     `date_updated`            datetime,
 109     PRIMARY KEY  (`movie_id`),
 110     INDEX (`isbn`),
 111     INDEX (`ean`),
 112     INDEX (`asin`)
 113 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 114 ;
 115
 116 DROP TABLE IF EXISTS `library_movie_languages`
 117 ;
 118 CREATE TABLE IF NOT EXISTS `library_movie_languages` (
 119     `movie_id`  char(36) NOT NULL,
 120     `language`  varchar(255) NOT NULL,
 121     `type`      varchar(255) NOT NULL,
 122     INDEX (`movie_id`)
 123 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 124 ;
 125
 126 DROP TABLE IF EXISTS `library_friends`
 127 ;
 128 CREATE TABLE IF NOT EXISTS `library_friends` (
 129     `user_id`     char(36) NOT NULL,
 130     `friend_id`   char(36) NOT NULL,
 131     INDEX (`user_id`),
 132     INDEX (`friend_id`)
 133 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 134 ;
 135
 136 DROP TABLE IF EXISTS `library_user_library_items`
 137 ;
 138 CREATE TABLE IF NOT EXISTS `library_user_library_items` (
 139     `user_id`       char(36) NOT NULL,
 140     `item_id`       char(36) NOT NULL,
 141     `item_type`     varchar(25) NOT NULL,
 142     `own_item`      tinyint(1),
 143     `copies_owned`  int(11),
 144     `want_item`     tinyint(1),
 145     `offering_item` tinyint(1),
 146     `date_created`  datetime,
 147     PRIMARY KEY (`user_id`,`item_id`),
 148     INDEX (`user_id`),
 149     INDEX (`item_id`),
 150     INDEX (`item_type`)
 151 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 152 ;
 153
 154 DROP TABLE IF EXISTS `library_loaned_items`
 155 ;
 156 CREATE TABLE IF NOT EXISTS `library_loaned_itemss` (
 157     `from_user_id`  char(36) NOT NULL,
 158     `to_user_id`    char(36) NOT NULL,
 159     `item_id`       char(36) NOT NULL,
 160     `date_out`      date,
 161     `date_due`      date,
 162     `date_updated`  datetime,
 163     `date_created`  datetime,
 164     INDEX (`from_user_id`),
 165     INDEX (`to_user_id`)
 166 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 167 ;
 168
 169 DROP TABLE IF EXISTS `library_amazon_queue`
 170 ;
 171 CREATE TABLE IF NOT EXISTS `library_queue_amazon` (
 172     `key`           varchar(14) NOT NULL COMMENT 'ISBN/EAN/UPC to be looked up on Amazon.com',
 173     `type`          varchar(10) NOT NULL,
 174     `date_created`  datetime,
 175      PRIMARY KEY (`key`,`type`)
 176 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 177 ;
 178
 179 DROP TABLE IF EXISTS `library_amazon_log`
 180 ;
 181 CREATE TABLE IF NOT EXISTS `library_amazon_log` (
 182     `key`           varchar(14) NOT NULL,
 183     `type`          varchar(10) NOT NULL,
 184     `success`       tinyint(1),
 185     `date_created`  datetime,
 186     PRIMARY KEY (`key`,`type`)
 187 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 188 ;
 189
 190 DROP TABLE IF EXISTS `library_user_recommendations`
 191 ;
 192 CREATE TABLE IF NOT EXISTS `library_user_recommendations` (
 193     `to_user_id`      char(36) NOT NULL,
 194     `from_user_id`    char(36) NOT NULL,
 195     `item_id`         varchar(36) NOT NULL,
 196     `item_type`       varchar(25) NOT NULL,
 197     `comment`         text NOT NULL,
 198     `read`            tinyint(1) NOT NULL DEFAULT '0',
 199     `date_created`    datetime,
 200     PRIMARY KEY (`to_user_id`,`from_user_id`,`item_id`),
 201     INDEX (`to_user_id`),
 202     INDEX (`from_user_id`)
 203 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 204 ;
 205
 206 DROP TABLE IF EXISTS `library_users`
 207 ;
 208 CREATE TABLE IF NOT EXISTS `library_users` (
 209     `user_id`               varchar(36) NOT NULL DEFAULT 'UUID()',
 210     `login_id`              varchar(255) NOT NULL,
 211     `password`              varchar(128) NOT NULL,
 212     `email`                 varchar(500) NOT NULL,
 213     `email_is_private`      tinyint(1),
 214     `display_name`          varchar(500) NOT NULL,
 215     `amazon_associate_id`   varchar(40),
 216     `date_updated`          datetime,
 217     `date_created`          datetime,
 218     PRIMARY KEY (`user_id`),
 219     INDEX (`login_id`)
 220 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
 221 ;
 222
 223 COMMIT;