273 lines
9.2 KiB
PHP
273 lines
9.2 KiB
PHP
<?php
|
|
|
|
$err = array();
|
|
$msg = array();
|
|
|
|
$missingConfig = false;
|
|
|
|
if($_SERVER['REMOTE_ADDR'] != "127.0.0.1"){
|
|
$err[] = "You need to use this page from localhost";
|
|
$fatalErr = true;
|
|
}
|
|
|
|
// CREATE USER IN DATABASE
|
|
|
|
|
|
// CREATE TABLES
|
|
$sql = "CREATE OR REPLACE TABLE `user` (
|
|
`user_id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`md5_id` VARCHAR(200) UNIQUE,
|
|
`full_name` TINYTEXT,
|
|
`user_email` VARCHAR(220) NOT NULL UNIQUE,
|
|
`user_level` TINYINT(4) NOT NULL DEFAULT 1,
|
|
`pwd` VARCHAR(220),
|
|
`date` DATE NOT NULL DEFAULT(CURRENT_DATE)
|
|
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
|
|
|
|
CREATE OR REPLACE TABLE `user_login` (
|
|
user_id bigint not null,
|
|
ckey varchar(220) not null,
|
|
ctime varchar(220) not null,
|
|
expire smallint default 0 not null,
|
|
agent varchar(255) null,
|
|
primary key (user_id, ctime),
|
|
constraint user_id_FK foreign key (user_id) references user (user_id)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `option` (
|
|
`option` varchar(100) not null primary key
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `user_option` (
|
|
user_id BIGINT(20) NOT NULL,
|
|
`option` varchar(100) NOT NULL,
|
|
value varchar(100) NOT NULL,
|
|
PRIMARY KEY (user_id,`option`),
|
|
CONSTRAINT user_options_FK FOREIGN KEY (user_id) REFERENCES `user`(user_id),
|
|
CONSTRAINT option_FK FOREIGN KEY (`option`) REFERENCES `option`(`option`)
|
|
)
|
|
|
|
CREATE OR REPLACE TABLE `plan_space` (
|
|
`space_id` INT auto_increment PRIMARY KEY,
|
|
`space_name` tinytext,
|
|
`owner_id` BIGINT(20),
|
|
`space_type` enum('STORE','CHECK','CALORIES') NOT NULL DEFAULT 'STORE', # not sure if needed
|
|
CONSTRAINT plan_space_owner_FK FOREIGN KEY (`owner_id`) REFERENCES `user`(`user_id`)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `plan_space_member` (
|
|
`space_id` INT auto_increment NOT NULL,
|
|
`member_id` BIGINT(20) NOT NULL,
|
|
`timestamp` DATETIME default current_timestamp() NOT NULL,
|
|
PRIMARY KEY (`space_id`, `member_id`),
|
|
CONSTRAINT space_member_FK FOREIGN KEY (`member_id`) REFERENCES `user`(`user_id`),
|
|
CONSTRAINT space_member_space_FK FOREIGN KEY (`space_id`) REFERENCES `plan_space`(`space_id`)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `plan_store` (
|
|
`plan_store_id` INT auto_increment,
|
|
`space_id` INT NOT NULL,
|
|
`name` varchar(100) NOT NULL,
|
|
`created` DATETIME default current_timestamp() NOT NULL,
|
|
`state` ENUM('planning', 'shopping', 'closed') default 'planning',
|
|
PRIMARY KEY (plan_store_id),
|
|
CONSTRAINT plan_store_user_FK FOREIGN KEY (`space_id`) REFERENCES `plan_space`(`space_id`)
|
|
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
|
|
|
|
CREATE OR REPLACE TABLE `plan_store_item` (
|
|
`plan_item_id` INT auto_increment NOT NULL,
|
|
`plan_store_id` INT NOT NULL,
|
|
`pos` tinyint(3) unsigned,
|
|
`name` varchar(200) NOT NULL,
|
|
`price` decimal(8,2) NOT NULL,
|
|
`amount` tinyint(3) unsigned DEFAULT 1,
|
|
`checked` BOOLEAN default 0,
|
|
PRIMARY KEY (plan_item_id),
|
|
CONSTRAINT plan_store_item_FK FOREIGN KEY (plan_store_id) REFERENCES `plan_store`(`plan_store_id`)
|
|
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
|
|
|
|
|
|
## PRODUCTS ##
|
|
CREATE OR REPLACE TABLE `product_group` (
|
|
group_id int(11) auto_increment NOT NULL,
|
|
name varchar(100) NOT NULL,
|
|
PRIMARY KEY (group_id)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `product` (
|
|
`product_id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`product_group_id` int(11) NOT NULL DEFAULT 1,
|
|
`name` varchar(100) NOT NULL,
|
|
PRIMARY KEY (`product_id`, `product_group_id`),
|
|
CONSTRAINT `product_FK` FOREIGN KEY (`product_group_id`) REFERENCES `product_group` (`group_id`)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `product_variant` (
|
|
`product_id` bigint(20) NOT NULL,
|
|
`variant_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(100) DEFAULT NULL,
|
|
`storebrand` tinyint(1) DEFAULT 0,
|
|
`EAN` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`variant_id`,`product_id`),
|
|
KEY `product_variant_FK` (`product_id`),
|
|
CONSTRAINT `product_variant_FK` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `product_price` (
|
|
`product_id` bigint(20) NOT NULL,
|
|
`date` date NOT NULL DEFAULT curdate(),
|
|
`price` decimal(8,2) NOT NULL,
|
|
`product_variant` int(11) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`product_id`,`date`,`product_variant`),
|
|
CONSTRAINT `product_price_FK` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `nutrition` (
|
|
`nutrition_id` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`nutrition` varchar(100) NOT NULL,
|
|
`parent` mediumint(9) DEFAULT NULL,
|
|
PRIMARY KEY (`nutrition_id`),
|
|
KEY `nutrition_FK` (`parent`),
|
|
CONSTRAINT `nutrition_FK` FOREIGN KEY (`parent`) REFERENCES `nutrition` (`nutrition_id`)
|
|
);
|
|
|
|
INSERT INTO nutrition (nutrition_id, nutrition, parent) VALUES
|
|
(1, 'Energy', NULL),
|
|
(2, 'Fat', NULL), (3, 'of which saturates', 2),
|
|
(4, 'Carbohydrate', NULL), (5, 'of which sugars', 4), (6, 'of which starch', 4),
|
|
(7, 'Protein', NULL),
|
|
(8, 'Salt', NULL);
|
|
|
|
CREATE OR REPLACE TABLE `product_nutrition` (
|
|
`product_id` bigint(20) NOT NULL,
|
|
`variant_id` int(11) NOT NULL,
|
|
`nutrition_id` mediumint(9) NOT NULL,
|
|
`value` smallint(6) NOT NULL,
|
|
`units` enum('g','ml','cups','%') NOT NULL DEFAULT 'g',
|
|
PRIMARY KEY (`product_id`,`variant_id`,`nutrition_id`),
|
|
KEY `product_nutrition_FK` (`nutrition_id`),
|
|
CONSTRAINT `product_nutrition_FK` FOREIGN KEY (`nutrition_id`) REFERENCES `nutrition` (`nutrition_id`)
|
|
);
|
|
";
|
|
|
|
$recipeSQL = "
|
|
DROP TABLE `recipe_item`;
|
|
|
|
CREATE OR REPLACE TABLE `recipe` (
|
|
`recipe_id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
|
|
`name` TEXT NOT NULL,
|
|
`author` BIGINT(20) NOT NULL,
|
|
`portions` SMALLINT DEFAULT 1,
|
|
`public` BOOLEAN DEFAULT 0,
|
|
CONSTRAINT author_FK FOREIGN KEY (`author`) REFERENCES user(`user_id`)
|
|
);
|
|
|
|
CREATE OR REPLACE TABLE `recipe_item` (
|
|
`recipe_id` INT NOT NULL,
|
|
`recipe_item_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`item_num` SMALLINT NOT NULL,
|
|
`name` TEXT NOT NULL,
|
|
`price` DECIMAL(8,2) DEFAULT 0.00,
|
|
`amount` INT DEFAULT 1,
|
|
`item_id` BIGINT(20),
|
|
|
|
CONSTRAINT recipe_FK FOREIGN KEY (`recipe_id`) REFERENCES recipe(`recipe_id`)
|
|
);";
|
|
|
|
if(!empty($_POST) && !isset($fatalErr)){
|
|
include "init.php";
|
|
$db = database();
|
|
|
|
if(isset($_POST['setupDB'])){
|
|
|
|
$db->multi_query($sql);
|
|
if($error = $db->error){
|
|
$err[] = $error;
|
|
}
|
|
else {
|
|
$msg[] = "Database tables are setup/reset.";
|
|
}
|
|
}
|
|
elseif (isset($_POST['recipeTables'])){
|
|
$db->multi_query($recipeSQL);
|
|
if($error = $db->error){
|
|
$err[] = $error;
|
|
}
|
|
else {
|
|
$msg[] = "Recipe tables are setup/reset.";
|
|
}
|
|
}
|
|
elseif (isset($_POST['goProject'])){
|
|
header("Location: ".getConfig('projectRoot')."/");
|
|
}
|
|
|
|
$db->close();
|
|
}
|
|
|
|
?><!DOCTYPE html>
|
|
<html lang="en">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta http-equiv="X-UA-Compatible" content="IE=edge">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
<title>Setup</title>
|
|
<style>
|
|
.alert-danger {
|
|
color: #842029;
|
|
background-color: #f8d7da;
|
|
border-color: #f5c2c7;
|
|
}
|
|
|
|
.alert-success {
|
|
color: #0f5132;
|
|
background-color: #d1e7dd;
|
|
border-color: #badbcc;
|
|
}
|
|
|
|
.alert {
|
|
position: relative;
|
|
padding: 1rem 1rem;
|
|
margin-bottom: 1rem;
|
|
border: 1px solid transparent;
|
|
border-radius: .25rem;
|
|
}
|
|
</style>
|
|
</head>
|
|
<body>
|
|
|
|
<div style='max-width: 500px; margin: auto; text-align: center;'>
|
|
<h1>Setup</h1>
|
|
<?php
|
|
if(!empty($err)){
|
|
foreach ($err as $e) {
|
|
echo "<div class='alert alert-danger'>$e</div>";
|
|
}
|
|
}
|
|
if(!empty($msg)){
|
|
foreach ($msg as $m) {
|
|
echo "<div class='alert alert-success'>$m</div>";
|
|
}
|
|
}
|
|
|
|
if(isset($fatalErr)){
|
|
die();
|
|
}
|
|
?>
|
|
<form action='setup.php' method='POST'>
|
|
<?php if($missingConfig){ ?>
|
|
<p>Configuration-file is missing or lacks content. Fill out the data here:</p>
|
|
<h4>Users Database</h4>
|
|
<p><label>Host: <input type='text' name='DatabaseUser-host' value='<?=$co['DatabaseUser']['host']??''?>' placeholder='localhost:3306' required></label></p>
|
|
<p><label>User: <input type='text' name='DatabaseUser-user' value='<?=$co['DatabaseUser']['user']??''?>' placeholder='LuxFictus' required></label></p>
|
|
<p><label>Password: <input type='password' name='DatabaseUser-password' value='<?=$co['DatabaseUser']['password']??''?>' placeholder='******' required></label></p>
|
|
<p><label>Database: <input type='text' name='DatabaseUser-database' value='<?=$co['DatabaseUser']['database']??''?>' placeholder='journal' required></label></p>
|
|
|
|
<input type='submit' value="Save" name='setup'>
|
|
<?php } ?>
|
|
<p><button name="setupDB">Setup tables</button></p>
|
|
<p><button name="recipeTables">Setup recipe tables</button></p>
|
|
<br>
|
|
<p><button name="goProject">Go to project</button></p>
|
|
</form>
|
|
</body>
|
|
</html>
|