-
Notifications
You must be signed in to change notification settings - Fork 177
/
ecommerce-db-sqlite3fk.sql
192 lines (167 loc) · 7.41 KB
/
ecommerce-db-sqlite3fk.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
182
183
184
185
186
187
188
189
190
191
192
-- 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 sqlite3fk
-- Input file ECommerceDB.dia
-- Generated at Tue Dec 25 22:21:46 2018
-- Typemap for sqlite3fk not found in input file
-- get_constraints_drop
-- 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)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
) ;
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),
foreign key(session_id) references sessions(id)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
) ;
-- get_view_create
-- get_permissions_create
-- get_inserts
-- get_smallpackage_post_sql
-- get_associations_create