· 9 years ago · Feb 03, 2017, 07:02 PM
1-- MySQL Script generated by MySQL Workbench
2-- 02/03/17 16:59:20
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 pingshow
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `pingshow` ;
14
15-- -----------------------------------------------------
16-- Schema pingshow
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `pingshow` DEFAULT CHARACTER SET utf8 ;
19USE `pingshow` ;
20
21-- -----------------------------------------------------
22-- Table `pingshow`.`users`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `pingshow`.`users` (
25 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
26 `name` VARCHAR(60) NULL,
27 `login` VARCHAR(50) NULL,
28 `password` VARCHAR(255) NULL,
29 `ig_id` VARCHAR(255) NOT NULL,
30 `username` VARCHAR(255) NOT NULL,
31 `profile_picture` TEXT NULL,
32 `full_name` TEXT NULL,
33 `code` TEXT NULL,
34 `access_token` TEXT NULL,
35 `email` VARCHAR(70) NULL,
36 `singup_complete` TINYINT NULL,
37 `admin` TINYINT NULL DEFAULT 0,
38 `created` DATETIME NULL,
39 `modified` DATETIME NULL,
40 `active` TINYINT NULL DEFAULT 1,
41 PRIMARY KEY (`id`),
42 UNIQUE INDEX `ig_id_UNIQUE` (`ig_id` ASC),
43 UNIQUE INDEX `username_UNIQUE` (`username` ASC))
44ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48-- Table `pingshow`.`instagram_users`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `pingshow`.`instagram_users` (
51 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
52 `ig_id` VARBINARY(15) NOT NULL,
53 `username` VARCHAR(95) NULL,
54 `full_name` VARBINARY(300) NULL,
55 `profile_picture` TEXT NULL,
56 `bio` VARBINARY(1000) NULL,
57 `website` VARCHAR(150) NULL,
58 `counts_media` INT UNSIGNED NULL,
59 `counts_follows` INT UNSIGNED NULL,
60 `counts_followed_by` INT UNSIGNED NULL,
61 `created` DATETIME NULL,
62 `modified` DATETIME NULL,
63 PRIMARY KEY (`id`),
64 UNIQUE INDEX `ig_id_UNIQUE` (`ig_id` ASC),
65 UNIQUE INDEX `username_UNIQUE` (`username` ASC))
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `pingshow`.`feed_images`
71-- -----------------------------------------------------
72CREATE TABLE IF NOT EXISTS `pingshow`.`feed_images` (
73 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
74 `ig_id` VARBINARY(60) NOT NULL,
75 `location_latitude` DECIMAL(10,7) NULL,
76 `location_longitude` DECIMAL(10,7) NULL,
77 `location_name` VARCHAR(80) NULL,
78 `location_id` INT NULL,
79 `comments_count` INT NULL,
80 `filter` VARCHAR(60) NULL,
81 `link` VARCHAR(255) NULL,
82 `likes_count` INT NULL,
83 `images_thumbnail_url` TEXT NULL,
84 `images_thumbnail_width` INT NULL,
85 `images_thumbnail_height` INT NULL,
86 `images_low_url` TEXT NULL,
87 `images_low_width` INT NULL,
88 `images_low_height` INT NULL,
89 `images_standard_url` TEXT NULL,
90 `images_standard_width` INT NULL,
91 `images_standard_height` INT NULL,
92 `caption_created_time` TIMESTAMP NULL,
93 `caption_text` VARBINARY(1000) NULL,
94 `caption_username` VARCHAR(300) NULL,
95 `caption_id` TEXT NULL,
96 `user_has_liked` TINYINT NULL,
97 `created_time` TIMESTAMP NULL,
98 `created` DATETIME NULL,
99 `modified` DATETIME NULL,
100 `instagram_user_id` INT UNSIGNED NOT NULL,
101 PRIMARY KEY (`id`),
102 UNIQUE INDEX `ig_id_UNIQUE` (`ig_id` ASC),
103 INDEX `fk_feed_images_instagram_users1_idx` (`instagram_user_id` ASC),
104 CONSTRAINT `fk_feed_images_instagram_users1`
105 FOREIGN KEY (`instagram_user_id`)
106 REFERENCES `pingshow`.`instagram_users` (`id`)
107 ON DELETE NO ACTION
108 ON UPDATE NO ACTION)
109ENGINE = InnoDB;
110
111
112-- -----------------------------------------------------
113-- Table `pingshow`.`config`
114-- -----------------------------------------------------
115CREATE TABLE IF NOT EXISTS `pingshow`.`config` (
116 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
117 `name` VARCHAR(45) NULL,
118 `value` VARCHAR(400) NULL,
119 PRIMARY KEY (`id`),
120 UNIQUE INDEX `name_UNIQUE` (`name` ASC))
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `pingshow`.`design_show`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `pingshow`.`design_show` (
128 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
129 `name` VARCHAR(45) NULL,
130 `value` VARCHAR(25) NULL,
131 PRIMARY KEY (`id`))
132ENGINE = InnoDB;
133
134
135-- -----------------------------------------------------
136-- Table `pingshow`.`hashtags_searchers`
137-- -----------------------------------------------------
138CREATE TABLE IF NOT EXISTS `pingshow`.`hashtags_searchers` (
139 `id` INT UNSIGNED NOT NULL,
140 `name` VARCHAR(100) NULL,
141 `active` TINYINT NULL,
142 `date_begin` TIMESTAMP NULL,
143 `date_end` TIMESTAMP NULL,
144 `deleted` TINYINT NULL DEFAULT 0,
145 `moderation` TINYINT NULL DEFAULT 1,
146 `user_id` INT UNSIGNED NOT NULL,
147 `design_show_id` INT UNSIGNED NULL,
148 `enable_slideshow` TINYINT NULL DEFAULT 0,
149 `pag_next_url` VARCHAR(500) NULL,
150 `pag_last_complete_update` DATETIME NULL,
151 `created` TIMESTAMP NULL,
152 `modified` TIMESTAMP NULL,
153 PRIMARY KEY (`id`),
154 INDEX `fk_hashtags_searchers_users1_idx` (`user_id` ASC),
155 INDEX `fk_hashtags_searchers_design_show1_idx` (`design_show_id` ASC),
156 CONSTRAINT `fk_hashtags_searchers_users1`
157 FOREIGN KEY (`user_id`)
158 REFERENCES `pingshow`.`users` (`id`)
159 ON DELETE NO ACTION
160 ON UPDATE NO ACTION,
161 CONSTRAINT `fk_hashtags_searchers_design_show1`
162 FOREIGN KEY (`design_show_id`)
163 REFERENCES `pingshow`.`design_show` (`id`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION)
166ENGINE = InnoDB;
167
168
169-- -----------------------------------------------------
170-- Table `pingshow`.`log_monitor`
171-- -----------------------------------------------------
172CREATE TABLE IF NOT EXISTS `pingshow`.`log_monitor` (
173 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
174 `context` VARCHAR(255) NULL COMMENT 'Class HashTagMonitor - Function GetPic',
175 `message` TEXT NULL,
176 `level` VARCHAR(20) NULL COMMENT 'advertise\nerror\ncritical\n',
177 `created` DATETIME NULL,
178 PRIMARY KEY (`id`))
179ENGINE = InnoDB;
180
181
182-- -----------------------------------------------------
183-- Table `pingshow`.`tags_image`
184-- -----------------------------------------------------
185CREATE TABLE IF NOT EXISTS `pingshow`.`tags_image` (
186 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
187 `name` VARBINARY(500) NULL,
188 `created` TIMESTAMP NULL,
189 PRIMARY KEY (`id`),
190 UNIQUE INDEX `name_UNIQUE` (`name` ASC))
191ENGINE = InnoDB;
192
193
194-- -----------------------------------------------------
195-- Table `pingshow`.`feed_images_tags_image`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `pingshow`.`feed_images_tags_image` (
198 `feed_image_id` INT UNSIGNED NOT NULL,
199 `tags_image_id` INT UNSIGNED NOT NULL,
200 `created` TIMESTAMP NULL,
201 PRIMARY KEY (`feed_image_id`, `tags_image_id`),
202 INDEX `fk_feed_images_has_tags_image_tags_image1_idx` (`tags_image_id` ASC),
203 INDEX `fk_feed_images_has_tags_image_feed_images1_idx` (`feed_image_id` ASC),
204 CONSTRAINT `fk_feed_images_has_tags_image_feed_images1`
205 FOREIGN KEY (`feed_image_id`)
206 REFERENCES `pingshow`.`feed_images` (`id`)
207 ON DELETE NO ACTION
208 ON UPDATE NO ACTION,
209 CONSTRAINT `fk_feed_images_has_tags_image_tags_image1`
210 FOREIGN KEY (`tags_image_id`)
211 REFERENCES `pingshow`.`tags_image` (`id`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION)
214ENGINE = InnoDB;
215
216
217-- -----------------------------------------------------
218-- Table `pingshow`.`log_user`
219-- -----------------------------------------------------
220CREATE TABLE IF NOT EXISTS `pingshow`.`log_user` (
221 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
222 `description` TEXT NULL,
223 `created` DATETIME NULL,
224 `user_id` INT UNSIGNED NOT NULL,
225 PRIMARY KEY (`id`),
226 INDEX `fk_log_user_users1_idx` (`user_id` ASC),
227 CONSTRAINT `fk_log_user_users1`
228 FOREIGN KEY (`user_id`)
229 REFERENCES `pingshow`.`users` (`id`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION)
232ENGINE = InnoDB;
233
234
235-- -----------------------------------------------------
236-- Table `pingshow`.`hashtags_searchers_feed_images`
237-- -----------------------------------------------------
238CREATE TABLE IF NOT EXISTS `pingshow`.`hashtags_searchers_feed_images` (
239 `hashtags_searcher_id` INT UNSIGNED NOT NULL,
240 `feed_image_id` INT UNSIGNED NOT NULL,
241 `approved` TINYINT NULL DEFAULT 0,
242 `transmited_client` TINYINT NULL DEFAULT 0,
243 `showed_client` TINYINT NULL DEFAULT 0,
244 `created` DATETIME NULL,
245 `modified` DATETIME NULL,
246 PRIMARY KEY (`hashtags_searcher_id`, `feed_image_id`),
247 INDEX `fk_hashtags_searchers_has_feed_images_feed_images1_idx` (`feed_image_id` ASC),
248 INDEX `fk_hashtags_searchers_has_feed_images_hashtags_searchers1_idx` (`hashtags_searcher_id` ASC),
249 CONSTRAINT `fk_hashtags_searchers_has_feed_images_hashtags_searchers1`
250 FOREIGN KEY (`hashtags_searcher_id`)
251 REFERENCES `pingshow`.`hashtags_searchers` (`id`)
252 ON DELETE NO ACTION
253 ON UPDATE NO ACTION,
254 CONSTRAINT `fk_hashtags_searchers_has_feed_images_feed_images1`
255 FOREIGN KEY (`feed_image_id`)
256 REFERENCES `pingshow`.`feed_images` (`id`)
257 ON DELETE NO ACTION
258 ON UPDATE NO ACTION)
259ENGINE = InnoDB;
260
261
262-- -----------------------------------------------------
263-- Table `pingshow`.`promotions`
264-- -----------------------------------------------------
265CREATE TABLE IF NOT EXISTS `pingshow`.`promotions` (
266 `id` INT UNSIGNED NOT NULL,
267 `name` VARCHAR(65) NULL,
268 `date_begin` DATETIME NULL,
269 `active` TINYINT NULL,
270 `hashtags_searcher_id` INT UNSIGNED NOT NULL,
271 `created` DATETIME NULL,
272 `modified` DATETIME NULL,
273 PRIMARY KEY (`id`),
274 INDEX `fk_promotions_hashtags_searchers1_idx` (`hashtags_searcher_id` ASC),
275 CONSTRAINT `fk_promotions_hashtags_searchers1`
276 FOREIGN KEY (`hashtags_searcher_id`)
277 REFERENCES `pingshow`.`hashtags_searchers` (`id`)
278 ON DELETE NO ACTION
279 ON UPDATE NO ACTION)
280ENGINE = InnoDB;
281
282
283-- -----------------------------------------------------
284-- Table `pingshow`.`promotions_images_used`
285-- -----------------------------------------------------
286CREATE TABLE IF NOT EXISTS `pingshow`.`promotions_images_used` (
287 `promotion_id` INT UNSIGNED NOT NULL,
288 `feed_image_id` INT UNSIGNED NOT NULL,
289 `created` DATETIME NULL,
290 `type_randomly` TINYINT NULL DEFAULT 0,
291 PRIMARY KEY (`promotion_id`, `feed_image_id`),
292 INDEX `fk_promotions_has_feed_images_feed_images1_idx` (`feed_image_id` ASC),
293 INDEX `fk_promotions_has_feed_images_promotions1_idx` (`promotion_id` ASC),
294 CONSTRAINT `fk_promotions_has_feed_images_promotions1`
295 FOREIGN KEY (`promotion_id`)
296 REFERENCES `pingshow`.`promotions` (`id`)
297 ON DELETE NO ACTION
298 ON UPDATE NO ACTION,
299 CONSTRAINT `fk_promotions_has_feed_images_feed_images1`
300 FOREIGN KEY (`feed_image_id`)
301 REFERENCES `pingshow`.`feed_images` (`id`)
302 ON DELETE NO ACTION
303 ON UPDATE NO ACTION)
304ENGINE = InnoDB;
305
306
307-- -----------------------------------------------------
308-- Table `pingshow`.`user_config_generator`
309-- -----------------------------------------------------
310CREATE TABLE IF NOT EXISTS `pingshow`.`user_config_generator` (
311 `id` INT NOT NULL AUTO_INCREMENT,
312 `username` VARCHAR(255) NULL,
313 `config_name` VARCHAR(100) NULL,
314 `value` VARCHAR(100) NULL,
315 `used` TINYINT NULL,
316 `created` DATETIME NULL,
317 `modified` DATETIME NULL,
318 PRIMARY KEY (`id`))
319ENGINE = InnoDB;
320
321
322SET SQL_MODE=@OLD_SQL_MODE;
323SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
324SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
325
326-- -----------------------------------------------------
327-- Data for table `pingshow`.`config`
328-- -----------------------------------------------------
329START TRANSACTION;
330USE `pingshow`;
331INSERT INTO `pingshow`.`config` (`id`, `name`, `value`) VALUES (DEFAULT, 'client_id', 'bd04037159a84ab8b8c08a7db4a7ad35');
332INSERT INTO `pingshow`.`config` (`id`, `name`, `value`) VALUES (DEFAULT, 'client_secret', '7f57ca06c6dd4d0c9a029483037033ed');
333INSERT INTO `pingshow`.`config` (`id`, `name`, `value`) VALUES (DEFAULT, 'redirect_uri', 'http://pingshow.localhost/users/getcode');
334INSERT INTO `pingshow`.`config` (`id`, `name`, `value`) VALUES (DEFAULT, 'grant_type', 'authorization_code');
335INSERT INTO `pingshow`.`config` (`id`, `name`, `value`) VALUES (DEFAULT, 'scope', 'public_content');
336
337COMMIT;
338
339
340-- -----------------------------------------------------
341-- Data for table `pingshow`.`design_show`
342-- -----------------------------------------------------
343START TRANSACTION;
344USE `pingshow`;
345INSERT INTO `pingshow`.`design_show` (`id`, `name`, `value`) VALUES (DEFAULT, 'Padrão', 'show2');
346INSERT INTO `pingshow`.`design_show` (`id`, `name`, `value`) VALUES (DEFAULT, 'Fundo Colorido', 'show0');
347
348COMMIT;