diff options
Diffstat (limited to 'backend/migrations/2020-12-13-140215_all_tables')
-rw-r--r-- | backend/migrations/2020-12-13-140215_all_tables/down.sql | 11 | ||||
-rw-r--r-- | backend/migrations/2020-12-13-140215_all_tables/up.sql | 47 |
2 files changed, 58 insertions, 0 deletions
diff --git a/backend/migrations/2020-12-13-140215_all_tables/down.sql b/backend/migrations/2020-12-13-140215_all_tables/down.sql new file mode 100644 index 0000000..9c554a2 --- /dev/null +++ b/backend/migrations/2020-12-13-140215_all_tables/down.sql | |||
@@ -0,0 +1,11 @@ | |||
1 | -- This file should undo anything in `up.sql` | ||
2 | |||
3 | drop table customer; | ||
4 | |||
5 | drop table product; | ||
6 | |||
7 | drop table cart_items; | ||
8 | |||
9 | drop table rating; | ||
10 | |||
11 | drop table transaction; | ||
diff --git a/backend/migrations/2020-12-13-140215_all_tables/up.sql b/backend/migrations/2020-12-13-140215_all_tables/up.sql new file mode 100644 index 0000000..527b926 --- /dev/null +++ b/backend/migrations/2020-12-13-140215_all_tables/up.sql | |||
@@ -0,0 +1,47 @@ | |||
1 | -- Your SQL goes here | ||
2 | |||
3 | create table customer ( | ||
4 | id integer primary key auto_increment, | ||
5 | username varchar(255) not null unique, | ||
6 | password varchar(255) not null, | ||
7 | phone_number varchar(10) not null, | ||
8 | email_id varchar(255) not null, | ||
9 | address text(500) | ||
10 | ); | ||
11 | |||
12 | create table product ( | ||
13 | id integer primary key auto_increment, | ||
14 | name varchar(255) not null, | ||
15 | kind varchar(255), | ||
16 | price float not null, | ||
17 | description varchar(255) | ||
18 | ); | ||
19 | |||
20 | create table cart_items ( | ||
21 | cart_id integer, | ||
22 | product_id integer, | ||
23 | constraint cart_items_pk primary key (cart_id, product_id), | ||
24 | foreign key (cart_id) references customer(id), | ||
25 | foreign key (product_id) references product(id) | ||
26 | ); | ||
27 | |||
28 | create table rating ( | ||
29 | id integer primary key auto_increment, | ||
30 | comment_text text(500), | ||
31 | comment_date date default curdate(), | ||
32 | product_id integer, | ||
33 | customer_id integer, | ||
34 | |||
35 | stars integer check (stars >= 0 AND stars <= 5), | ||
36 | foreign key (customer_id) references customer(id), | ||
37 | foreign key (product_id) references product(id) | ||
38 | ); | ||
39 | |||
40 | create table transaction ( | ||
41 | id integer primary key auto_increment, | ||
42 | payment_type varchar(255) not null, | ||
43 | amount float not null, | ||
44 | customer_id integer, | ||
45 | |||
46 | foreign key (customer_id) references customer(id) | ||
47 | ); | ||