dit is product

From 25b6b8926d8cb3c4cabc8e7764e613d3bb4e4ed7 Mon Sep 17 00:00:00 2001 From: lonkaars Date: Mon, 8 May 2023 12:39:19 +0200 Subject: add database init sql scripts --- db/.gitignore | 2 ++ db/data.sql | 1 + db/init.sql | 55 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ db/makefile | 35 +++++++++++++++++++++++++++++++++++ db/reset.sql | 6 ++++++ public/cart.php | 2 +- 6 files changed, 100 insertions(+), 1 deletion(-) create mode 100644 db/.gitignore create mode 100644 db/data.sql create mode 100644 db/init.sql create mode 100644 db/makefile create mode 100644 db/reset.sql 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; diff --git a/public/cart.php b/public/cart.php index d02428d..1ad48cd 100644 --- a/public/cart.php +++ b/public/cart.php @@ -2,7 +2,7 @@ - dit is product + mand -- cgit v1.2.3