1462024985.sql
2.36 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
CREATE TABLE IF NOT EXISTS `Clients` (
`idCarte` char(8) NOT NULL,
`solde` float(7,2) DEFAULT NULL,
`decouvert` tinyint(1) DEFAULT '0',
PRIMARY KEY (`idCarte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Prix` (
`produit` char(30) DEFAULT NULL,
`prix` float(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Prix` (`produit`, `prix`) VALUES
('biere', 1.80);
CREATE TABLE IF NOT EXISTS `Sessions` (
`jeton` char(30) NOT NULL,
`utilisateur` char(30) DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`jeton`),
KEY `utilisateur` (`utilisateur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Transactions` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`type` char(15) DEFAULT NULL,
`client` char(8) DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`montant` float(7,2) DEFAULT NULL,
`quantite` int(2) DEFAULT NULL,
`utilisateur` char(30) DEFAULT NULL,
`valide` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
KEY `client` (`client`),
KEY `utilisateur` (`utilisateur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Utilisateurs` (
`login` char(30) NOT NULL,
`mdp` char(255) DEFAULT NULL,
`idCarte` char(8) DEFAULT NULL,
`droit` int(11) DEFAULT '0',
PRIMARY KEY (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Utilisateurs` (`login`, `mdp`, `idCarte`, `droit`) VALUES
('default', '$2y$10$rc6Cht/F2DM4grcOcRAGBOtd.BD56kJgkC/IiGcnV6Oqa8hjRCxSW', 'OHCEE7UH', 3);
ALTER TABLE `Sessions`
ADD CONSTRAINT `Sessions_utilisateur` FOREIGN KEY (`utilisateur`) REFERENCES `Utilisateurs` (`login`);
ALTER TABLE `Transactions`
ADD CONSTRAINT `Transactions_client` FOREIGN KEY (`client`) REFERENCES `Clients` (`idCarte`),
ADD CONSTRAINT `Transactions_utilisateur` FOREIGN KEY (`utilisateur`) REFERENCES `Utilisateurs` (`login`);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;