diff options
| author | lonkaars <loek@pipeframe.xyz> | 2023-05-08 12:39:19 +0200 |
|---|---|---|
| committer | lonkaars <loek@pipeframe.xyz> | 2023-05-08 12:39:19 +0200 |
| commit | 25b6b8926d8cb3c4cabc8e7764e613d3bb4e4ed7 (patch) | |
| tree | f53a14278d32e1986cb7e323e886bebafb50905f | |
| parent | c4acffbebf278a07fbe82c9f1c09b73cd5d00086 (diff) | |
add database init sql scripts
| -rw-r--r-- | db/.gitignore | 2 | ||||
| -rw-r--r-- | db/data.sql | 1 | ||||
| -rw-r--r-- | db/init.sql | 55 | ||||
| -rw-r--r-- | db/makefile | 35 | ||||
| -rw-r--r-- | db/reset.sql | 6 | ||||
| -rw-r--r-- | public/cart.php | 2 |
6 files changed, 100 insertions, 1 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; 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 @@ <html> <head> <?php include 'head.php' ?> - <title>dit is product</title> + <title>mand</title> <link rel="stylesheet" href="cart.css"> </head> <body> |