Partie 2. Structure du SGBD, tables et types de données

Première partie Nous continuons à créer notre simple émulateur boursier. Voici ce que nous ferons :

• Créons un diagramme d'organisation de base de données.
• Nous décrirons quoi, comment et où il est stocké.
• Découvrons comment les données sont liées les unes aux autres.
• Commençons par apprendre les bases de SQL en utilisant l'exemple de la commande de création de table SQL CREATE TABLE , Data Definition Language ( DDL ) du langage SQL.
• Continuons à écrire le programme Java. Nous implémentons les principales fonctions du SGBD en termes de java.sql pour créer notre base de données par programme, en utilisant JDBC et une architecture à trois niveaux.

Ces deux parties se sont avérées plus volumineuses, puisqu'il faut se familiariser de l'intérieur avec les bases de SQL et l'organisation d'un SGBD, et faire des analogies avec Java. Afin de ne pas vous ennuyer avec les listes de codes, à la fin il y a des liens vers le référentiel github de commit correspondant avec le programme.

Conception de SGBD

Description de l'application

Vous avez déjà entendu dire que l'organisation du stockage des données fait partie intégrante de la programmation. Je vous rappelle que le but de notre application est l'émulation d'échange la plus simple :

• Il existe des actions dont la valeur peut changer au cours de la journée de bourse selon des règles données ;
• il y a des commerçants avec un capital initial ;
• les traders peuvent acheter et vendre des actions selon leur algorithme.

L'échange fonctionne en ticks - périodes de temps fixes (dans notre cas - 1 minute). Au cours d'un tick, le cours de l'action peut changer, puis le trader peut acheter ou vendre des actions.

Structure des données d'émulation Exchange

Appelons modèles d'entités d'échange individuelles. Pour éviter les erreurs d'arrondi, nous travaillerons avec des montants financiers via une classe BigDecimal(les détails peuvent être trouvés dans le lien en fin d'article). Décrivons plus en détail la structure de chaque modèle : Promotion : Cours de l'action : Commerçant: Actions du commerçant : Pour garantir l'unicité de chaque modèle, nous ajouterons un attribut idde type long . Cet attribut sera unique au sein des instances de modèle et l'identifiera de manière unique. Les attributs qui référencent d'autres modèles (trader, action, cours de bourse) peuvent utiliser celui-ci idpour identifier de manière unique le modèle correspondant. L'idée nous vient immédiatement à l'esprit de ce que nous pourrions utiliser Map<Long, Object> pour stocker de telles données, où Objectse trouve le modèle correspondant. Cependant, essayez de l'implémenter dans le code dans les conditions suivantes :

• la taille des données dépasse largement la quantité de RAM disponible ;
• l'accès aux données est attendu depuis une douzaine d'endroits différents ;
• la capacité de modifier et de lire simultanément les données est requise ;
• il est nécessaire de garantir des règles de formation et d'intégrité des données ;

...et vous serez confronté à des tâches qui nécessitent des qualifications appropriées et du temps pour être mises en œuvre. Il n'est pas nécessaire de réinventer la roue". Beaucoup de choses ont déjà été pensées et écrites pour nous. Nous utiliserons donc ce qui a déjà été testé au fil des années.

Stockage des données en Java

Considérons l'action. En Java, nous avons créé une classe spécifique pour ce modèle Shareavec les champs name, changeProbability, startPrice, delta. Et de nombreux partages ont été stockés sous la forme Map<Long, Share>, où la clé est un identifiant unique pour chaque partage.

public class Share {
    private String name;
    private BigDecimal startPrice;
    private int changeProbability;
    private int delta;
}
Map<Long, Share> shares = new HashMap<>();
shares.put(1L, new Share("ibm", BigDecimal.valueOf(20.0), 15, 10));
shares.put(2L, new Share("apple", BigDecimal.valueOf(14.0), 25, 15));
shares.put(3L, new Share("google", BigDecimal.valueOf(12.0), 20, 8));
...
shares.put(50L, new Share("microsoft", BigDecimal.valueOf(17.5), 10,4 ));

Pour accéder à la promotion souhaitée par identifiant, utilisez la méthode shares.get(id). Pour trouver une action par nom ou par prix, nous parcourons tous les enregistrements à la recherche de celle dont nous avons besoin, et ainsi de suite. Mais nous irons dans l'autre sens et stockerons les valeurs dans le SGBD.

Stockage des données dans un SGBD

Formulons un premier ensemble de règles de stockage de données pour un SGBD :

• Les données d'un SGBD sont organisées en tables ( TABLE ), qui sont un ensemble d'enregistrements.
• Tous les enregistrements comportent les mêmes ensembles de champs. Ils sont définis lors de la création du tableau.
• Le champ peut être défini sur une valeur par défaut ( DEFAULT ).
• Pour une table, vous pouvez définir des contraintes ( CONSTRAINT ) qui décrivent les exigences relatives à ses données afin de garantir leur intégrité. Cela peut être fait au stade de la création de la table ( CREATE TABLE ) ou ajouté ultérieurement ( ALTER TABLE ... ADD CONSTRAINT ).
• La CONTRAINTE la plus courante :
  • La clé primaire est PRIMARY (Id dans notre cas).
  • Champ de valeur unique UNIQUE (VIN pour la table du véhicule).
  • Vérification du champ CHECK (la valeur en pourcentage ne peut pas être supérieure à 100). L'une des restrictions privées sur un champ est NOT NULL ou NULL , qui interdit/autorise le stockage de NULL dans un champ de table.
  • Lien vers une table tierce FOREIGN KEY (lien vers une action dans le tableau des cours de bourse).
  • Index INDEX (indexation d'un champ pour accélérer la recherche de valeurs qu'il contient).
  • La modification d'un enregistrement ( INSERT , UPDATE ) n'aura pas lieu si les valeurs de ses champs contredisent les restrictions (CONSTRAINT).
• Chaque table peut avoir un champ clé (ou plusieurs) qui peut être utilisé pour identifier de manière unique un enregistrement. Un tel champ (ou des champs, s'ils forment une clé composite) constituent la clé primaire de la table - PRIMARY KEY .
  • La clé primaire garantit l'unicité d'un enregistrement dans la table ; un index est créé dessus, ce qui donne un accès rapide à l'intégralité de l'enregistrement en fonction de la valeur de la clé.
  • Avoir une clé primaire facilite grandement la création de liens entre les tables. Ensuite, nous utiliserons une clé primaire artificielle : pour le premier enregistrement id = 1, chaque enregistrement suivant sera inséré dans la table avec la valeur id augmentée de un. Cette clé est souvent appelée AutoIncrement ou AutoIdentity .

En fait, un tableau des stocks : est-il possible d'utiliser le nom du stock comme clé dans ce cas ? Dans l'ensemble, oui, mais il est possible qu'une société émette différentes actions et ne les appelle que par son propre nom. Dans ce cas, il n’y aura plus d’unicité. En pratique, une clé primaire artificielle est assez souvent utilisée. D'accord, l'utilisation d'un nom complet comme clé unique dans une table contenant des enregistrements de personnes ne garantira pas l'unicité. En plus d'utiliser une combinaison de nom complet et de date de naissance.

Types de données dans le SGBD

Comme tout autre langage de programmation, SQL permet le typage des données. Voici les types de données SQL les plus courants : Types entiers Réel Chaîne date et l'heure Stockage de gros volumes de données

Style d'écriture SQL

Pour de nombreuses langues, il existe des directives de formatage du code. En règle générale, ces documents contiennent des règles pour nommer les variables, les constantes, les méthodes et autres structures de langage. Ainsi, pour Python, il existe PEP8, pour Java - Oracle Code Conventions for Java . Plusieurs ensembles différents ont été créés pour SQL, qui sont légèrement différents les uns des autres. Quoi qu’il en soit, vous devez prendre l’habitude de suivre des règles lors du formatage de votre code, surtout si vous travaillez en équipe. Les règles pourraient être, par exemple, les suivantes (bien sûr, vous pouvez développer vous-même un ensemble de règles différent, l'essentiel est de s'y tenir à l'avenir) :

• Les mots clés et mots réservés, y compris les commandes et opérateurs, doivent être écrits en majuscules : CREATE TABLE, CONSTRAINT...
• Les noms des tables, champs et autres objets ne doivent pas coïncider avec les mots-clés du langage SQL (voir le lien en fin d'article), mais peuvent en contenir.
• Les noms des tables doivent refléter leur objectif. Ils sont écrits en lettres minuscules. Les mots du nom sont séparés les uns des autres par des traits de soulignement. Le mot à la fin doit être au pluriel : traders (traders), share_rates (share rate).
• Les noms des champs de table doivent refléter leur objectif. Ils doivent être écrits en lettres minuscules, les mots du nom doivent être formatés en style Camel Case et le mot à la fin doit être utilisé au singulier : name (nom), share_rates (taux de partage).
• Les champs de clé artificielle doivent contenir le mot id.
• Les noms de CONSTRAINT doivent suivre les conventions de dénomination des tables. Ils doivent également inclure les champs et les tables impliqués, commencer par un préfixe sémantique : check_ (vérification de la valeur du champ), pk_ (clé primaire), fk_ (clé étrangère), uniq_ (unicité du champ), idx_ (index). Exemple : pk_traider_share_actions_id (clé primaire sur le champ id de la table trader_share_actions).
• Et ainsi de suite, au fur et à mesure que vous étudiez SQL, la liste des règles sera reconstituée/modifiée.

Conception de SGBD

Immédiatement avant de créer un SGBD, celui-ci doit être conçu. Le schéma final contient des tables, un ensemble de champs, des CONTRAINTES, des clés, des conditions par défaut pour les champs, des relations entre les tables et d'autres entités de base de données. Sur Internet, vous pouvez trouver de nombreux concepteurs gratuits en ligne/hors ligne pour concevoir de petits SGBD. Essayez de taper quelque chose comme « Concepteur de base de données gratuit » dans un moteur de recherche. De telles applications ont des propriétés supplémentaires utiles :

• Peut générer des commandes SQL pour créer un SGBD.
• Affichez visuellement les paramètres sur le schéma.
• Vous permet de déplacer les tableaux pour une meilleure visualisation.
• Affichez les clés, les index, les relations, les valeurs par défaut, etc. sur le diagramme.
• Ils peuvent stocker à distance le schéma du SGBD.

Par exemple, dbdiffo.com met en évidence les clés, affiche les champs non vides et les compteurs AI (AutoIncrement) avec l'étiquette NN :

Créer des tables dans un SGBD

Nous avons donc un schéma. Passons maintenant à la création de tables (CREATE TABLE). Pour ce faire, il nous convient de disposer de données préliminaires :

• nom de la table
• noms et types de champs
• restrictions (CONSTRAINTES) sur les champs
• valeurs par défaut pour les champs (si disponibles)
• clé primaire (PRIMARY KEY) si disponible
• connexions entre tables (FOREIGN KEY)

Nous n'étudierons pas en détail toutes les options de la commande CREATE TABLE, nous examinerons les bases de SQL à l'aide de l'exemple de création d'une table pour les traders :

CREATE TABLE traiders(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	freqTiсk INTEGER NOT NULL,
	cash  DECIMAL(15,2) NOT NULL DEFAULT 1000,
	tradingMethod INTEGER NOT NULL,
	changeProbability INTEGER NOT NULL DEFAULT 50,
	about VARCHAR(255) NULL
);
ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod
	CHECK(tradingMethod IN (1,2,3));
ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability
	CHECK(changeProbability <= 100 AND changeProbability > 0)

Regardons de plus près:

• CREATE TABLE traiders(description du champ) - crée une table avec le nom spécifié ; dans la description, les champs sont séparés par une virgule. Toute commande se termine par un point-virgule.
• La description du champ commence par son nom, suivi de son type, de sa CONSTRAINT et de sa valeur par défaut.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– le champ id de type entier est une clé primaire et un compteur incrémental (pour chaque nouvel enregistrement pour le champ id, une valeur sera générée supérieure d'un à celle créée précédemment pour cette table).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– champ espèces, décimal, 15 chiffres avant la virgule et deux après (données financières, par exemple, dollars et cents). Impossible d'accepter les valeurs NULL. Si aucune valeur n'est donnée, il obtiendra la valeur 1000.
• about VARCHAR(255) NULL– le champ À propos, une chaîne de 255 caractères maximum, peut accepter des valeurs vides.

Notez que nous pouvons définir une partie des conditions CONSTRAINT après avoir créé la table. Considérons la construction pour modifier la structure de la table et ses champs : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte CHECK (condition) à l'aide d'exemples :

• CHECK(tradingMethod IN (1,2,3))– le champ tradingMethod ne peut prendre que les valeurs 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– le champ changeProbability peut prendre des valeurs entières comprises entre 1 et 100

Relations entre les tables

Pour analyser la description des relations entre les tables, regardons la création de share_rates :

CREATE TABLE share_rates(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	operDate datetime NOT NULL,
	share BIGINT NOT NULL,
	rate DECIMAL(15,2) NOT NULL
);
ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id)

Une référence aux valeurs d'une autre table peut être définie comme suit : ALTER TABLEtable_from_which_referred ADD FOREIGN KEY(field_that_referred) REFERENCEStable_to_which_referred (field_that_referred to) Laissez dans les actions nous avons des enregistrements sur les actions, par exemple, pour id=50 nous stockons les actions Microsoft avec un prix initial de 17,5, un delta de 20 et une chance de changement de 4 %. Pour la table share_rates , nous obtenons trois propriétés principales :

• Il suffit de stocker la valeur de la clé id de la table des partages dans le champ partage afin de l'utiliser pour obtenir les informations restantes (nom, etc.) de la table des partages.
• Nous ne pouvons pas créer un tarif pour une promotion inexistante. Vous ne pouvez pas insérer une valeur inexistante dans le champ partage (pour lequel il n'y a aucun enregistrement dans la table des partages avec cet identifiant), puisqu'il n'y aura pas de correspondance entre les tables.
• Nous ne pouvons pas supprimer une entrée de partage dans des actions pour lesquelles les taux sont définis dans share_rates.

Les deux derniers points servent à garantir l'intégrité des données stockées. Vous pouvez voir la création de tables SQL de notre émulation et des exemples de requêtes SQL dans l'implémentation Java des méthodes des classes correspondantes en utilisant le lien vers le référentiel github à la fin de l'article. La troisième partie