53 lines
2.6 KiB
SQL
53 lines
2.6 KiB
SQL
SET AUTOCOMMIT=1;
|
|
|
|
CREATE DATABASE IF NOT EXISTS botiga_php_db charset utf8;
|
|
|
|
USE botiga_php_db;
|
|
|
|
DROP TABLE IF EXISTS bp_products;
|
|
DROP TABLE IF EXISTS bp_categories;
|
|
DROP TABLE IF EXISTS bp_users;
|
|
|
|
CREATE TABLE IF NOT EXISTS botiga_php_db.bp_users (
|
|
bp_user_id INT AUTO_INCREMENT,
|
|
bp_user_name VARCHAR(32) NOT NULL UNIQUE,
|
|
bp_user_pass VARCHAR(64) NOT NULL UNIQUE,
|
|
bp_user_addDate DATE NOT NULL,
|
|
PRIMARY KEY (bp_user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS botiga_php_db.bp_categories (
|
|
bp_category_id INT AUTO_INCREMENT,
|
|
bp_category_name VARCHAR(32) NOT NULL UNIQUE,
|
|
PRIMARY KEY (bp_category_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS botiga_php_db.bp_products (
|
|
bp_product_id INT AUTO_INCREMENT,
|
|
bp_product_name VARCHAR(32) NOT NULL,
|
|
bp_product_info VARCHAR(50),
|
|
bp_product_price DOUBLE NOT NULL,
|
|
bp_product_category INT,
|
|
bp_product_img_01 VARCHAR(32),
|
|
bp_product_img_02 VARCHAR(32),
|
|
bp_product_img_03 VARCHAR(32),
|
|
bp_product_addDate DATE NOT NULL,
|
|
bp_product_owner INT,
|
|
PRIMARY KEY (bp_product_id),
|
|
FOREIGN KEY (bp_product_category) REFERENCES bp_categories(bp_category_id),
|
|
FOREIGN KEY (bp_product_owner) REFERENCES bp_users(bp_user_id)
|
|
);
|
|
|
|
INSERT INTO `bp_categories` (`bp_category_id`, `bp_category_name`) VALUES (NULL, 'Wooden'), (NULL, 'Brick'), (NULL, 'Stone'), (NULL, 'Alimium'), (NULL, 'Mix');
|
|
|
|
INSERT INTO bp_users VALUES (NULL, 'jones', '$2y$10$cQ/dbUCwpn70GxHyzLLG8O0KH0o67uagM8x0UASYXToTVplCFq4.m', '2021-12-09');
|
|
INSERT INTO bp_users VALUES (NULL, 'jack', '$2y$10$nkeFH50KmNCQKxKrsE/NQu1gx5YMqPxrQyL9e/eRyY1p9j8GrOTe.', '2021-09-12');
|
|
INSERT INTO bp_users VALUES (NULL, 'john', '$2y$10$c60U1n5hhploFWTGgnM3SOB5oSH70/f2sGvz1S5H3EZIeEul5x4ZK', '2021-10-11');
|
|
INSERT INTO bp_users VALUES (NULL, 'jerry', '$2y$10$gtQrwpRjdydYJTTXLa5jWOK3/CYriXNDuUuHtFqrlQ1o2w7a7893u', '2021-11-10');
|
|
|
|
|
|
INSERT INTO bp_products VALUES (NULL, 'Hanni Koroma', 'Japanese designed wooden nordic house', '95000', '1', 'house01.jpg', 'house02.jpg', 'house03.jpg', '2001-11-22', 1);
|
|
INSERT INTO bp_products VALUES (NULL, 'Fiskarhedenvillan', 'Classic 20th century English brick house', '200000', '2', 'house04.jpg', 'house05.jpg', 'house06.jpg', '1958-06-20', 2);
|
|
INSERT INTO bp_products VALUES (NULL, 'Barn House', 'American aluminium suspended house', '65000', '5', 'house07.jpg', 'house08.jpg', 'house09.jpg', '1994-09-11', 3);
|
|
INSERT INTO bp_products VALUES (NULL, 'Hygge', 'Nordic house built with a mix of natural materials', '150000', '5', 'house10.jpg', 'house11.jpg', 'house12.jpg', '2021-11-28', 4);
|