F4a72zrA

1# --- First database schema
2
3# --- !Ups
4
5create table profile (
6  id                    bigint not null,
7  username              varchar(255),
8  password		varchar(255),
9  created               timestamp,
10  email                 varchar(255)
11  constraint pk_profile primary key (id))
12;
13
14create table story (
15  id                    bigint not null,
16  story                 text,
17  profileId             bigint not null,
18  created               timestamp,
19  votes                 int(11) default 0,
20  constraint pk_story primary key (id));
21
22create table comments (
23  id                    bigint not null,
24  commentText 	        text,
25  profileId             bigint not null,
26  storyId               bigint not null,
27  parentComment         bigint,
28  created               timestamp,
29  votes                 int(11) default 0,
30  constraint pk_comments primary key(id));
31
32create table votes (
33  id                    bigint not null,
34  storyId               bigint not null,
35  commentId             bigint,
36  created               timestamp,
37  profileId             bigint not null,
38  constraint pk_votes primary key(id));
39
40);
41
42
43alter table story add constraint fk_story_profile_1 foreign key (profileId) references profile (id) on delete restrict on update restrict;
44alter table comments add constraint fk_comments_profile_1 foreign key (profileId) references profile (id) on delete restrict on update restrict;
45alter table comments add constraint fk_comments_parent_2 foreign key (parentComment) references comments (id) on delete restrict on update restrict;
46alter table comments add constraint fk_comments_story_3 foreign key (storyId) references story (id) on delete restrict on update restrict;
47alter table votes add constraint fk_votes_profile_1 foreign key (profileId) references profile (id) on delete restrict on update restrict;
48alter table votes add constraint fk_votes_comment_2 foreign key (commentId) references comments (id) on delete restrict on update restrict;
49alter table votes add constraint fk_comments_votes_3 foreign key (storyId) references story (id) on delete restrict on update restrict;
50
51# --- !Downs
52
53SET REFERENTIAL_INTEGRITY FALSE;
54
55drop table if exists profile;
56drop table if exists story;
57drop table if exists comments;