add database init sql scripts

5 files changed, 99 insertions, 0 deletions

diff --git a/db/.gitignore b/db/.gitignore
new file mode 100644
index 0000000..7b59807
--- /dev/null
+++ b/db/.gitignore
@@ -0,0 +1,2 @@
+base.sql
+full.sql
diff --git a/db/data.sql b/db/data.sql
new file mode 100644
index 0000000..4ba2805
--- /dev/null
+++ b/db/data.sql
@@ -0,0 +1 @@
+--
diff --git a/db/init.sql b/db/init.sql
new file mode 100644
index 0000000..f1c3506
--- /dev/null
+++ b/db/init.sql
@@ -0,0 +1,55 @@
+create schema if not exists webs;
+
+create table if not exists webs.category (
+  `ID` int not null auto_increment,
+  `name` varchar(45) not null,
+  primary key (`ID`)
+);
+
+create table if not exists webs.product (
+  `ID` int not null auto_increment,
+  `name` varchar(45) not null,
+  `price` decimal(5, 2) not null,
+  `image` mediumblob null default null,
+	`category` int not null,
+  primary key (`ID`),
+  constraint `product_category_fk`
+    foreign key (`category`)
+    references webs.category (`ID`)
+    on update cascade
+);
+
+create table if not exists webs.customer (
+  `ID` int not null auto_increment,
+  `name` varchar(45) not null,
+  primary key (`ID`)
+);
+
+create table if not exists webs.cart (
+  `ID` int not null auto_increment,
+  `product` int not null,
+  `customer` int not null,
+	`count` int not null default 1,
+  primary key (`ID`),
+  constraint `cart_product_fk`
+    foreign key (`product`)
+    references webs.product (`ID`)
+    on update cascade,
+  constraint `cart_customer_fk`
+    foreign key (`customer`)
+    references webs.customer (`ID`)
+    on update cascade
+);
+
+create table if not exists webs.promotion (
+  `ID` int not null auto_increment,
+  `product` int not null,
+  `count_buff` int not null default 1,
+	`price_buff` decimal(4, 3) not null default 1.0,
+  primary key (`ID`),
+  constraint `promotion_product_fk`
+    foreign key (`product`)
+    references webs.product (`ID`)
+    on update cascade
+);
+
diff --git a/db/makefile b/db/makefile
new file mode 100644
index 0000000..0802fa6
--- /dev/null
+++ b/db/makefile
@@ -0,0 +1,35 @@
+SQL = mysql
+USER = $(shell id -un)
+HOST = localhost
+
+.PHONY: clean permissions base data full
+
+all: full
+
+# delete old tables and create new tables
+base: base.sql
+	$(SQL) < $<
+
+# fill tables with data
+data: data.sql
+	$(SQL) < $<
+
+# do everything
+full: full.sql
+	$(SQL) < $<
+
+base.sql: reset.sql init.sql
+	cat $^ > $@
+
+full.sql: base.sql data.sql
+	cat $^ > $@
+
+# grant all database permissions to current user (development only)
+permissions:
+	echo "grant all privileges on webs.* to '$(USER)'@'$(HOST)';" | sudo $(SQL)
+# echo "grant file on *.* to '$(USER)'@'$(HOST)';" | sudo $(SQL)
+
+# delete generated sql files
+clean:
+	$(RM) full.sql base.sql
+
diff --git a/db/reset.sql b/db/reset.sql
new file mode 100644
index 0000000..5338395
--- /dev/null
+++ b/db/reset.sql
@@ -0,0 +1,6 @@
+drop schema if exists webs;
+drop table if exists webs.product;
+drop table if exists webs.customer;
+drop table if exists webs.cart;
+drop table if exists webs.promotion;
+drop table if exists webs.category;