-
Notifications
You must be signed in to change notification settings - Fork 177
/
ecommerce-db-mysql-myisam.sql
181 lines (170 loc) · 8.04 KB
/
ecommerce-db-mysql-myisam.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
-- Parse::SQL::Dia version 0.30
-- Documentation http://search.cpan.org/dist/Parse-Dia-SQL/
-- Environment Perl 5.028001, /home/ramortegui/perl5/perlbrew/perls/perl-5.28.1/bin/perl
-- Architecture x86_64-linux
-- Target Database mysql-myisam
-- Input file ECommerceDB.dia
-- Generated at Tue Dec 25 22:21:45 2018
-- Typemap for mysql-myisam not found in input file
-- get_constraints_drop
alter table sales_orders drop constraint fk_session_sales_order ;
-- get_permissions_drop
-- get_view_drop
-- get_schema_drop
drop table if exists users;
drop table if exists roles;
drop table if exists user_roles;
drop table if exists categories;
drop table if exists products;
drop table if exists tags;
drop table if exists sales_orders;
drop table if exists coupons;
drop table if exists product_tags;
drop table if exists cc_transactions;
drop table if exists sessions;
drop table if exists product_statuses;
drop table if exists product_categories;
drop table if exists order_products;
-- get_smallpackage_pre_sql
-- get_schema_create
create table users (
id serial not null ,
email varchar(255) not null ,
first_name varchar(255) not null ,
last_name varchar(255) not null ,
active bool default true,
inserted_at timestamp with time zone not null ,
updated_at timestamp with time zone not null ,
constraint pk_users primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table roles (
id serial not null,
name varchar(255) not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_roles primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table user_roles (
user_id integer not null,
role_id integer not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_user_roles primary key (user_id,role_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table categories (
id serial not null,
name varchar(255) not null,
parent_id integer ,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_categories primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table products (
id serial not null ,
sku varchar(255) not null ,
name varchar(255) not null ,
description text ,
product_status_id integer not null ,
regular_price numeric default 0 ,
discount_price numeric default 0 ,
quantity integer default 0 ,
taxable bool default false,
inserted_at timestamp with time zone not null ,
updated_at timestamp with time zone not null ,
constraint pk_products primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table tags (
id serial not null,
name varchar(255) not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_tags primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table sales_orders (
id serial not null,
order_date date not null,
total numeric not null,
coupon_id integer ,
session_id varchar(255) not null,
user_id integer not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_sales_orders primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table coupons (
id serial not null ,
code varchar(255) not null ,
description text ,
active bool default true ,
value numeric ,
multiple bool default false,
start_date timestamp with time zone ,
end_date timestamp with time zone ,
inserted_at timestamp with time zone not null ,
updated_at timestamp with time zone not null ,
constraint pk_coupons primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table product_tags (
product_id integer not null,
tag_id integer not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_product_tags primary key (product_id,tag_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table cc_transactions (
id serial not null,
code varchar(255) ,
order_id integer not null,
transdate timestamp with time zone ,
processor varchar(255) not null,
processor_trans_id varchar(255) not null,
amount numeric not null,
cc_num varchar(255) ,
cc_type varchar(255) ,
response text ,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_cc_transactions primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table sessions (
id varchar(255) not null,
data text ,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_sessions primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table product_statuses (
id serial not null,
name varchar(255) not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_product_statuses primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table product_categories (
category_id integer not null,
product_id integer not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_product_categories primary key (category_id,product_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table order_products (
id serial not null,
order_id integer ,
sku varchar(255) not null,
name varchar(255) not null,
description text ,
price numeric not null,
quantity integer not null,
subtotal numeric not null,
inserted_at timestamp with time zone not null,
updated_at timestamp with time zone not null,
constraint pk_order_products primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- get_view_create
-- get_permissions_create
-- get_inserts
-- get_smallpackage_post_sql
-- get_associations_create
alter table sales_orders add constraint fk_session_sales_order
foreign key (session_id)
references sessions (id) ;