129 lines
5.4 KiB
SQL
129 lines
5.4 KiB
SQL
-- =====================================================================
|
|
# Schéma de la base « bolsa » — version de référence (snake_case)
|
|
-- =====================================================================
|
|
# Tables : login, actions, societe, compte, ordre, ordre_pied
|
|
# Encodage : utf8mb4 / utf8mb4_0900_ai_ci
|
|
# Ce fichier est exécuté automatiquement par Docker sur une base vierge
|
|
# (monté dans /docker-entrypoint-initdb.d/). Sur une base existante, les
|
|
# CREATE TABLE IF NOT EXISTS sont sans effet.
|
|
-- =====================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS `login` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`login` varchar(50) NOT NULL,
|
|
`nom` varchar(100) NOT NULL,
|
|
`prenom` varchar(100) NOT NULL,
|
|
`mail` varchar(255) NOT NULL,
|
|
`pass` varchar(255) NOT NULL,
|
|
`actif` tinyint(1) DEFAULT NULL,
|
|
`creer_le` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`modifie_le` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`reset_token` varchar(255) DEFAULT NULL,
|
|
`reset_token_created_at` timestamp NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_login` (`login`),
|
|
UNIQUE KEY `idx_mail` (`mail`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `actions` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`isin` varchar(50) NOT NULL,
|
|
`ticker` varchar(50) DEFAULT NULL,
|
|
`company_name` varchar(255) DEFAULT NULL,
|
|
`exchange` varchar(50) NOT NULL,
|
|
`pays` varchar(50) NOT NULL,
|
|
`currency` varchar(10) NOT NULL,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `isin` (`isin`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `societe` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`nom` varchar(60) NOT NULL,
|
|
`forme` varchar(10) NOT NULL,
|
|
`capital` int NOT NULL,
|
|
`siren` varchar(20) NOT NULL,
|
|
`siret` varchar(20) NOT NULL,
|
|
`rcs` varchar(50) NOT NULL,
|
|
`tva` varchar(50) NOT NULL,
|
|
`naf` varchar(10) NOT NULL,
|
|
`tel` varchar(30) NOT NULL,
|
|
`web` varchar(100) NOT NULL,
|
|
`mail` varchar(100) NOT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `compte` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`id_societe` int NOT NULL,
|
|
`date_operation` date NOT NULL,
|
|
`debit` decimal(15,2) DEFAULT '0.00',
|
|
`credit` decimal(15,2) DEFAULT '0.00',
|
|
`libelle` text,
|
|
`source` enum('manuel','ordre') NOT NULL DEFAULT 'manuel',
|
|
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_compte_societe` (`id_societe`),
|
|
CONSTRAINT `fk_compte_societe` FOREIGN KEY (`id_societe`)
|
|
REFERENCES `societe` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `ordre` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`id_societe` int NOT NULL,
|
|
`isin` varchar(50) NOT NULL,
|
|
`plus_value` decimal(19,4) NOT NULL DEFAULT '0.0000',
|
|
`date_plus_value` date DEFAULT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `fk_ordre_societe` (`id_societe`),
|
|
KEY `fk_ordre_actions` (`isin`),
|
|
CONSTRAINT `fk_ordre_societe` FOREIGN KEY (`id_societe`)
|
|
REFERENCES `societe` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT `fk_ordre_actions` FOREIGN KEY (`isin`)
|
|
REFERENCES `actions` (`isin`) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `ordre_pied` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`id_entete` int NOT NULL,
|
|
`date_op` date NOT NULL,
|
|
`quantite` int NOT NULL,
|
|
`prix_brut` decimal(19,4) DEFAULT NULL,
|
|
`frais_broker` decimal(19,4) DEFAULT NULL,
|
|
`frais_etat` decimal(19,4) DEFAULT NULL,
|
|
`frais_autre` decimal(19,4) DEFAULT NULL,
|
|
`sens` enum('achat','vente') NOT NULL,
|
|
`position` enum('long','short') NOT NULL,
|
|
`etat` enum('actif','soldé','annulé') NOT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_ordre_pied_entete` (`id_entete`),
|
|
CONSTRAINT `fk_ordre_pied_entete` FOREIGN KEY (`id_entete`)
|
|
REFERENCES `ordre` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `historique` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`isin` varchar(50) NOT NULL,
|
|
`date` date NOT NULL,
|
|
`price` decimal(19,4) DEFAULT NULL,
|
|
`open` decimal(19,4) DEFAULT NULL,
|
|
`hight` decimal(19,4) DEFAULT NULL,
|
|
`low` decimal(19,4) DEFAULT NULL,
|
|
`vol` int DEFAULT NULL,
|
|
`change` decimal(19,4) DEFAULT NULL,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_isin_date` (`isin`, `date`),
|
|
CONSTRAINT `fk_historique_actions` FOREIGN KEY (`isin`)
|
|
REFERENCES `actions` (`isin`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|