· 8 years ago · Dec 10, 2017, 05:16 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema enwage
9-- -----------------------------------------------------
10DROP SCHEMA IF EXISTS `enwage` ;
11
12-- -----------------------------------------------------
13-- Schema enwage
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `enwage` DEFAULT CHARACTER SET utf8 ;
16SHOW WARNINGS;
17USE `enwage` ;
18
19-- -----------------------------------------------------
20-- Table `enwage`.`user`
21-- -----------------------------------------------------
22DROP TABLE IF EXISTS `enwage`.`user` ;
23
24SHOW WARNINGS;
25CREATE TABLE IF NOT EXISTS `enwage`.`user` (
26 `username` VARCHAR(60) NOT NULL,
27 `email` VARCHAR(255) NOT NULL,
28 `password_hash` VARCHAR(256) NOT NULL,
29 `token` VARCHAR(256) NULL,
30 `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
31 `wallet_ngn` DECIMAL(11,11) NOT NULL DEFAULT 0 COMMENT 'User\'s Naira wallet',
32 `wallet_btc` DECIMAL(11,11) NOT NULL DEFAULT 0 COMMENT 'User\'s Naira wallet',
33 `skillset` TEXT NULL,
34 PRIMARY KEY (`username`),
35 UNIQUE INDEX `username_UNIQUE` (`username` ASC),
36 UNIQUE INDEX `email_UNIQUE` (`email` ASC))
37ENGINE = InnoDB;
38
39SHOW WARNINGS;
40
41-- -----------------------------------------------------
42-- Table `enwage`.`currencies`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `enwage`.`currencies` ;
45
46SHOW WARNINGS;
47CREATE TABLE IF NOT EXISTS `enwage`.`currencies` (
48 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
49 `abbr` VARCHAR(6) NOT NULL,
50 `name` VARCHAR(15) NOT NULL,
51 PRIMARY KEY (`id`),
52 UNIQUE INDEX `id_UNIQUE` (`id` ASC))
53ENGINE = InnoDB;
54
55SHOW WARNINGS;
56
57-- -----------------------------------------------------
58-- Table `enwage`.`job_categories`
59-- -----------------------------------------------------
60DROP TABLE IF EXISTS `enwage`.`job_categories` ;
61
62SHOW WARNINGS;
63CREATE TABLE IF NOT EXISTS `enwage`.`job_categories` (
64 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
65 `parent_id` INT NULL DEFAULT 0,
66 `title` VARCHAR(100) NULL,
67 `description` VARCHAR(256) NULL,
68 PRIMARY KEY (`id`))
69ENGINE = InnoDB;
70
71SHOW WARNINGS;
72
73-- -----------------------------------------------------
74-- Table `enwage`.`jobs`
75-- -----------------------------------------------------
76DROP TABLE IF EXISTS `enwage`.`jobs` ;
77
78SHOW WARNINGS;
79CREATE TABLE IF NOT EXISTS `enwage`.`jobs` (
80 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
81 `category_id` INT NULL,
82 `by` VARCHAR(45) NULL,
83 `title` VARCHAR(160) NOT NULL,
84 `description` TEXT NULL,
85 `create_time` TIMESTAMP NULL,
86 `currency` INT NOT NULL,
87 `payout_initial` DECIMAL(11,11) NULL,
88 `payout_final` DECIMAL(11,11) NULL,
89 PRIMARY KEY (`id`),
90 INDEX `currency_idx` (`currency` ASC),
91 INDEX `by_idx` (`by` ASC),
92 INDEX `category_id_idx` (`category_id` ASC),
93 CONSTRAINT `currency`
94 FOREIGN KEY (`currency`)
95 REFERENCES `enwage`.`currencies` (`id`)
96 ON DELETE RESTRICT
97 ON UPDATE CASCADE,
98 CONSTRAINT `by`
99 FOREIGN KEY (`by`)
100 REFERENCES `enwage`.`user` (`username`)
101 ON DELETE RESTRICT
102 ON UPDATE CASCADE,
103 CONSTRAINT `category_id`
104 FOREIGN KEY (`category_id`)
105 REFERENCES `enwage`.`job_categories` (`id`)
106 ON DELETE RESTRICT
107 ON UPDATE CASCADE)
108ENGINE = InnoDB;
109
110SHOW WARNINGS;
111
112-- -----------------------------------------------------
113-- Table `enwage`.`deposits`
114-- -----------------------------------------------------
115DROP TABLE IF EXISTS `enwage`.`deposits` ;
116
117SHOW WARNINGS;
118CREATE TABLE IF NOT EXISTS `enwage`.`deposits` (
119 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
120 `by` VARCHAR(60) NOT NULL,
121 `currency` INT NULL,
122 PRIMARY KEY (`id`),
123 INDEX `by_idx` (`by` ASC),
124 INDEX `currency_idx` (`currency` ASC),
125 CONSTRAINT `by`
126 FOREIGN KEY (`by`)
127 REFERENCES `enwage`.`user` (`username`)
128 ON DELETE RESTRICT
129 ON UPDATE CASCADE,
130 CONSTRAINT `currency`
131 FOREIGN KEY (`currency`)
132 REFERENCES `enwage`.`currencies` (`id`)
133 ON DELETE RESTRICT
134 ON UPDATE CASCADE)
135ENGINE = InnoDB;
136
137SHOW WARNINGS;
138
139SET SQL_MODE=@OLD_SQL_MODE;
140SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
141SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;