L'outil de gestion de stock de composants électroniques du Tetalab.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

stock.sql 1.8KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869
  1. \c postgres;
  2. drop database tetastock;
  3. drop role tetastock;
  4. create role tetastock with LOGIN ENCRYPTED PASSWORD 'tetastock';
  5. create database tetastock;
  6. \c tetastock;
  7. CREATE TABLE stock_users (
  8. id serial primary key,
  9. mail text not NULL,
  10. password text not NULL,
  11. name text not NULL
  12. );
  13. CREATE TABLE stock_kits (
  14. id serial primary key,
  15. name text not NULL unique,
  16. designation text not NULL
  17. );
  18. CREATE TABLE stock_providers (
  19. id serial primary key,
  20. name text unique not NULL,
  21. address text not NULL,
  22. mail text not NULL,
  23. url text not NULL,
  24. comment text not NULL
  25. );
  26. CREATE TABLE stock_componants (
  27. id serial primary key,
  28. reference varchar(20) unique not NULL,
  29. designation varchar(100) not NULL,
  30. last_price NUMERIC not NULL default 0,
  31. mean_price NUMERIC not NULL default 0,
  32. quantity NUMERIC not NULL default 0,
  33. min_quantity NUMERIC not NULL default 0,
  34. place varchar(15) not NULL,
  35. provider_id integer REFERENCES stock_providers(id)
  36. );
  37. CREATE TABLE stock_kit_compositions (
  38. id serial primary key,
  39. kit_id integer REFERENCES Stock_kits(id),
  40. componant_id integer REFERENCES stock_componants(id),
  41. quantity integer not NULL
  42. );
  43. CREATE TABLE stock_orders (
  44. id serial primary key,
  45. componant_id integer REFERENCES stock_componants(id),
  46. quantity integer not NULL,
  47. price NUMERIC not NULL default 0,
  48. date timestamp not NULL
  49. );
  50. alter table stock_users owner to tetastock;
  51. alter table stock_kits owner to tetastock;
  52. alter table stock_providers owner to tetastock;
  53. alter table stock_componants owner to tetastock;
  54. alter table stock_kit_compositions owner to tetastock;
  55. alter table stock_orders owner to tetastock;
  56. alter database tetastock owner to tetastock;
  57. insert into stock_users (mail, password, name) values ('test', '$2b$08$OkfihuGRyLdpftBpGhnpeeeUhUTQS0oXvR2NFByC.65XCKKvPBWHS', 'test');