stock/stock.sql

70 rivejä
1.8 KiB
SQL

\c postgres;
drop database tetastock;
drop role tetastock;
create role tetastock with LOGIN ENCRYPTED PASSWORD 'tetastock';
create database tetastock;
\c tetastock;
CREATE TABLE stock_users (
id serial primary key,
mail text not NULL,
password text not NULL,
name text not NULL
);
CREATE TABLE stock_kits (
id serial primary key,
name text not NULL unique,
designation text not NULL
);
CREATE TABLE stock_providers (
id serial primary key,
name text unique not NULL,
address text not NULL,
mail text not NULL,
url text not NULL,
comment text not NULL
);
CREATE TABLE stock_componants (
id serial primary key,
reference varchar(20) unique not NULL,
designation varchar(100) not NULL,
last_price NUMERIC not NULL default 0,
mean_price NUMERIC not NULL default 0,
quantity NUMERIC not NULL default 0,
min_quantity NUMERIC not NULL default 0,
place varchar(15) not NULL,
provider_id integer REFERENCES stock_providers(id)
);
CREATE TABLE stock_kit_compositions (
id serial primary key,
kit_id integer REFERENCES Stock_kits(id),
componant_id integer REFERENCES stock_componants(id),
quantity integer not NULL
);
CREATE TABLE stock_orders (
id serial primary key,
componant_id integer REFERENCES stock_componants(id),
quantity integer not NULL,
price NUMERIC not NULL default 0,
date timestamp not NULL
);
alter table stock_users owner to tetastock;
alter table stock_kits owner to tetastock;
alter table stock_providers owner to tetastock;
alter table stock_componants owner to tetastock;
alter table stock_kit_compositions owner to tetastock;
alter table stock_orders owner to tetastock;
alter database tetastock owner to tetastock;
insert into stock_users (mail, password, name) values ('test', '$2b$08$OkfihuGRyLdpftBpGhnpeeeUhUTQS0oXvR2NFByC.65XCKKvPBWHS', 'test');