Skip to content

Commit aeb9019

Browse files
osopromadzeosopromadze
committed
updated blogapi.sql file
1 parent d0969bf commit aeb9019

1 file changed

Lines changed: 48 additions & 19 deletions

File tree

‎src/main/resources/blogapi.sql‎

Lines changed: 48 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,7 @@
1+
UNLOCK TABLES;
2+
3+
DROP TABLE IF EXISTS `post_tag`;
4+
DROP TABLE IF EXISTS `tags`;
15
DROP TABLE IF EXISTS `user_role`;
26
DROP TABLE IF EXISTS `roles`;
37
DROP TABLE IF EXISTS `comments`;
@@ -10,12 +14,22 @@ DROP TABLE IF EXISTS `address`;
1014
DROP TABLE IF EXISTS `company`;
1115
DROP TABLE IF EXISTS `geo`;
1216

17+
CREATE TABLE `tags` (
18+
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
19+
`name` varchar(255) NOT NULL,
20+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
21+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
22+
`created_by` bigint(19) unsigned NOT NULL,
23+
`updated_by` bigint(19) unsigned NOT NULL,
24+
PRIMARY KEY (`id`)
25+
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
26+
1327
CREATE TABLE `geo` (
1428
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
1529
`lat` varchar(255),
1630
`lng` varchar(255),
17-
`created_at` timestamp,
18-
`updated_at` timestamp,
31+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
32+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1933
`created_by` bigint(19) unsigned DEFAULT NULL,
2034
`updated_by` bigint(19) unsigned DEFAULT NULL,
2135
PRIMARY KEY (`id`)
@@ -26,8 +40,8 @@ CREATE TABLE `company` (
2640
`name` varchar(255),
2741
`catch_phrase` varchar(255),
2842
`bs` varchar(255),
29-
`created_at` timestamp,
30-
`updated_at` timestamp,
43+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
44+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3145
`created_by` bigint(19) unsigned DEFAULT NULL,
3246
`updated_by` bigint(19) unsigned DEFAULT NULL,
3347
PRIMARY KEY (`id`)
@@ -40,8 +54,8 @@ CREATE TABLE `address` (
4054
`city` varchar(255),
4155
`zipcode` varchar(255),
4256
`geo_id` bigint(19) unsigned DEFAULT NULL,
43-
`created_at` timestamp,
44-
`updated_at` timestamp,
57+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
58+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
4559
`created_by` bigint(19) unsigned DEFAULT NULL,
4660
`updated_by` bigint(19) unsigned DEFAULT NULL,
4761
PRIMARY KEY (`id`),
@@ -60,8 +74,8 @@ CREATE TABLE `users` (
6074
`phone` varchar(255),
6175
`website` varchar(255),
6276
`company_id` bigint(19) unsigned DEFAULT NULL,
63-
`created_at` timestamp,
64-
`updated_at` timestamp,
77+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
78+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
6579
PRIMARY KEY (`id`),
6680
KEY `fk_address` (`address_id`),
6781
KEY `fk_company` (`company_id`),
@@ -74,8 +88,8 @@ CREATE TABLE `todos` (
7488
`title` varchar(255) NOT NULL,
7589
`completed` boolean default false,
7690
`user_id` bigint(19) unsigned DEFAULT NULL,
77-
`created_at` timestamp,
78-
`updated_at` timestamp,
91+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
92+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
7993
`created_by` bigint(19) unsigned DEFAULT NULL,
8094
`updated_by` bigint(19) unsigned DEFAULT NULL,
8195
PRIMARY KEY (`id`),
@@ -87,8 +101,8 @@ CREATE TABLE `albums` (
87101
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
88102
`title` varchar(255) NOT NULL,
89103
`user_id` bigint(19) unsigned DEFAULT NULL,
90-
`created_at` timestamp,
91-
`updated_at` timestamp,
104+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
105+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
92106
`created_by` bigint(19) unsigned DEFAULT NULL,
93107
`updated_by` bigint(19) unsigned DEFAULT NULL,
94108
PRIMARY KEY (`id`),
@@ -102,8 +116,8 @@ CREATE TABLE `photos` (
102116
`url` varchar(255) NOT NULL,
103117
`thumbnail_url` varchar(255) NOT NULL,
104118
`album_id` bigint(19) unsigned DEFAULT NULL,
105-
`created_at` timestamp,
106-
`updated_at` timestamp,
119+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
120+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
107121
`created_by` bigint(19) unsigned DEFAULT NULL,
108122
`updated_by` bigint(19) unsigned DEFAULT NULL,
109123
PRIMARY KEY (`id`),
@@ -116,24 +130,35 @@ CREATE TABLE `posts` (
116130
`title` varchar(255) NOT NULL,
117131
`body` text NOT NULL,
118132
`user_id` bigint(19) unsigned DEFAULT NULL,
119-
`created_at` timestamp,
120-
`updated_at` timestamp,
133+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
134+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
121135
`created_by` bigint(19) unsigned DEFAULT NULL,
122136
`updated_by` bigint(19) unsigned DEFAULT NULL,
123137
PRIMARY KEY (`id`),
124138
KEY `fk_user_post` (`user_id`),
125139
CONSTRAINT `fk_user_post` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
126140
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
127141

142+
CREATE TABLE `post_tag` (
143+
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
144+
`post_id` bigint(19) unsigned NOT NULL,
145+
`tag_id` bigint(19) unsigned NOT NULL,
146+
PRIMARY KEY (`id`),
147+
KEY `fk_posttag_post_id` (`post_id`),
148+
KEY `fk_posttag_tag_id` (`tag_id`),
149+
CONSTRAINT `fk_posttag_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
150+
CONSTRAINT `fk_posttag_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
151+
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
152+
128153
CREATE TABLE `comments` (
129154
`id` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
130155
`name` varchar(255) NOT NULL,
131156
`email` varchar(255) NOT NULL,
132157
`body` text NOT NULL,
133158
`post_id` bigint(19) unsigned DEFAULT NULL,
134159
`user_id` bigint(19) unsigned DEFAULT NULL,
135-
`created_at` timestamp,
136-
`updated_at` timestamp,
160+
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
161+
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
137162
`created_by` bigint(19) unsigned NOT NULL,
138163
`updated_by` bigint(19) unsigned NOT NULL,
139164
PRIMARY KEY (`id`),
@@ -158,4 +183,8 @@ CREATE TABLE `user_role` (
158183
KEY `fk_security_role_id` (`role_id`),
159184
CONSTRAINT `fk_security_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
160185
CONSTRAINT `fk_security_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
161-
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
186+
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
187+
188+
LOCK TABLES `roles` WRITE;
189+
INSERT INTO `roles` VALUES (1,'ROLE_ADMIN'),(2,'ROLE_USER');
190+
UNLOCK TABLES;

0 commit comments

Comments
 (0)