· 9 years ago · Nov 19, 2016, 04:34 PM
1-- MySQL Script generated by MySQL Workbench
2-- 11/19/16 16:32:23
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema upframe
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema upframe
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `upframe` DEFAULT CHARACTER SET utf8 ;
18USE `upframe` ;
19
20-- -----------------------------------------------------
21-- Table `upframe`.`products`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `upframe`.`products` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `name` VARCHAR(128) NOT NULL,
26 `description` TEXT NOT NULL,
27 `price` INT NOT NULL,
28 `picture` VARCHAR(128) NOT NULL,
29 `deactivated` TINYINT(1) NOT NULL DEFAULT 0,
30 PRIMARY KEY (`id`),
31 INDEX `deactivated` (`deactivated` ASC))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `upframe`.`promocodes`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `upframe`.`promocodes` (
39 `id` INT NOT NULL AUTO_INCREMENT,
40 `code` VARCHAR(45) NOT NULL,
41 `expires` DATETIME NOT NULL,
42 `discount` INT NOT NULL DEFAULT 0,
43 `percentage` TINYINT(1) NOT NULL DEFAULT 0,
44 `deactivated` TINYINT(1) NOT NULL DEFAULT 0,
45 PRIMARY KEY (`id`),
46 UNIQUE INDEX `id_UNIQUE` (`id` ASC),
47 UNIQUE INDEX `code_UNIQUE` (`code` ASC),
48 INDEX `deactivated` (`deactivated` ASC))
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `upframe`.`users`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `upframe`.`users` (
56 `id` INT NOT NULL AUTO_INCREMENT,
57 `first_name` VARCHAR(45) NOT NULL,
58 `last_name` VARCHAR(45) NOT NULL,
59 `email` VARCHAR(128) NOT NULL,
60 `address` TEXT NULL DEFAULT NULL,
61 `invites` INT NOT NULL DEFAULT 0,
62 `credit` INT NOT NULL DEFAULT 0,
63 `confirmed` TINYINT(1) NOT NULL DEFAULT 0,
64 `referral` VARCHAR(64) NOT NULL,
65 `referrer` INT NULL,
66 `password_salt` VARCHAR(64) NOT NULL,
67 `password_hash` VARCHAR(128) NOT NULL,
68 `admin` TINYINT(1) NOT NULL DEFAULT 0,
69 `deactivated` TINYINT(1) NOT NULL DEFAULT 0,
70 PRIMARY KEY (`id`),
71 UNIQUE INDEX `referrer_hash_UNIQUE` (`referral` ASC),
72 UNIQUE INDEX `email_UNIQUE` (`email` ASC),
73 UNIQUE INDEX `id_UNIQUE` (`id` ASC),
74 INDEX `deactivated` (`deactivated` ASC),
75 CONSTRAINT `referred_by`
76 FOREIGN KEY (`referrer`)
77 REFERENCES `upframe`.`users` (`id`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION)
80ENGINE = InnoDB;
81
82
83-- -----------------------------------------------------
84-- Table `upframe`.`orders`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `upframe`.`orders` (
87 `id` INT NOT NULL AUTO_INCREMENT,
88 `user_id` INT NOT NULL,
89 `promocode_id` INT NULL,
90 `value` INT NOT NULL,
91 `status` VARCHAR(120) NOT NULL,
92 `paypal` TEXT NOT NULL COMMENT 'Can hold the PayPal or the Link if the payment wasn\'t made yet.',
93 `credits` INT NOT NULL DEFAULT 0,
94 PRIMARY KEY (`id`),
95 UNIQUE INDEX `id_UNIQUE` (`id` ASC),
96 CONSTRAINT `order_promocode_id`
97 FOREIGN KEY (`promocode_id`)
98 REFERENCES `upframe`.`promocodes` (`id`)
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION,
101 CONSTRAINT `order_user_id`
102 FOREIGN KEY (`user_id`)
103 REFERENCES `upframe`.`users` (`id`)
104 ON DELETE NO ACTION
105 ON UPDATE NO ACTION)
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `upframe`.`links`
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS `upframe`.`links` (
113 `hash` VARCHAR(64) NOT NULL,
114 `user_id` INT NOT NULL,
115 `path` VARCHAR(45) NOT NULL,
116 `used` TINYINT(1) NOT NULL DEFAULT 0,
117 `time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
118 `expires` DATETIME NOT NULL,
119 PRIMARY KEY (`hash`),
120 UNIQUE INDEX `hash_UNIQUE` (`hash` ASC),
121 CONSTRAINT `links_user_id`
122 FOREIGN KEY (`user_id`)
123 REFERENCES `upframe`.`users` (`id`)
124 ON DELETE NO ACTION
125 ON UPDATE NO ACTION)
126ENGINE = InnoDB;
127
128
129-- -----------------------------------------------------
130-- Table `upframe`.`orders_products`
131-- -----------------------------------------------------
132CREATE TABLE IF NOT EXISTS `upframe`.`orders_products` (
133 `id` INT NOT NULL AUTO_INCREMENT,
134 `order_id` INT NOT NULL,
135 `product_id` INT NOT NULL,
136 `quantity` INT NOT NULL DEFAULT 1,
137 PRIMARY KEY (`id`),
138 UNIQUE INDEX `id_UNIQUE` (`id` ASC),
139 CONSTRAINT `orders_product_order_id`
140 FOREIGN KEY (`order_id`)
141 REFERENCES `upframe`.`orders` (`id`)
142 ON DELETE NO ACTION
143 ON UPDATE NO ACTION,
144 CONSTRAINT `orders_products_product_id`
145 FOREIGN KEY (`product_id`)
146 REFERENCES `upframe`.`products` (`id`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION)
149ENGINE = InnoDB;
150
151
152SET SQL_MODE=@OLD_SQL_MODE;
153SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
154SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;