SQL, quoi de plus simple ? Chacun de nous peut rédiger une demande simple - nous tapons Sélectionner, listez les colonnes requises, puis de, nom de la table, certaines conditions dans De et c'est tout - les données utiles sont dans notre poche, et (presque) quel que soit le SGBD qui se trouve sous le capot à ce moment-là (ou peut-être ). En conséquence, travailler avec presque toutes les sources de données (relationnelles ou non) peut être envisagé du point de vue du code ordinaire (avec tout ce que cela implique - contrôle de version, révision du code, analyse statique, autotests, et c'est tout). Et cela s’applique non seulement aux données elles-mêmes, aux schémas et aux migrations, mais en général à toute la durée de vie du stockage. Dans cet article, nous parlerons des tâches quotidiennes et des problèmes liés au travail avec diverses bases de données sous le prisme de la « base de données en tant que code ».
Et commençons par . Les premières batailles de type « SQL vs ORM » ont été remarquées dès .
Mappage objet-relationnel
Les partisans d'ORM apprécient traditionnellement la rapidité et la facilité de développement, l'indépendance par rapport au SGBD et le code propre. Pour beaucoup d'entre nous, le code permettant de travailler avec la base de données (et souvent la base de données elle-même)
ça ressemble généralement à quelque chose comme ça...
@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
@UniqueConstraint(columnNames = "STOCK_NAME"),
@UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
return this.stockId;
}
...Le modèle est doté d'annotations intelligentes et, quelque part dans les coulisses, un vaillant ORM génère et exécute des tonnes de code SQL. D'ailleurs, les développeurs font de leur mieux pour s'isoler de leur base de données avec des kilomètres d'abstractions, ce qui indique que certains .
De l'autre côté des barricades, les adeptes du SQL pur « fait main » notent la possibilité d'extraire tout le jus de leur SGBD sans couches ni abstractions supplémentaires. En conséquence, des projets « data-centric » apparaissent, où des personnes spécialement formées sont impliquées dans la base de données (ils sont aussi « basicists », ils sont aussi « basicists », ils sont aussi « basdeners », etc.), et les développeurs il suffit de « tirer » les vues et les procédures stockées toutes faites, sans entrer dans les détails.
Et si nous avions le meilleur des deux mondes ? Comment cela se fait dans un outil merveilleux avec un nom affirmant la vie . Je vais donner quelques lignes du concept général dans ma traduction gratuite, et vous pourrez en prendre connaissance plus en détail .
Clojure est un langage sympa pour créer des DSL, mais SQL lui-même est un DSL sympa, et nous n'en avons pas besoin d'un autre. Les expressions S sont excellentes, mais elles n'ajoutent rien de nouveau ici. En conséquence, nous obtenons des parenthèses pour le plaisir des parenthèses. Ne pas être d'accord? Attendez ensuite le moment où l'abstraction sur la base de données commence à fuir et vous commencez à vous battre avec la fonction (brut-sql)
Donc qu'est ce que je devrais faire? Laissons SQL comme SQL normal - un fichier par requête :
-- name: users-by-country
select *
from users
where country_code = :country_code... puis lisez ce fichier, en le transformant en une fonction Clojure standard :
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)En adhérant au principe « SQL seul, Clojure seul », vous obtenez :
- Pas de surprise syntaxique. Votre base de données (comme toute autre) n'est pas conforme à 100 % au standard SQL - mais cela n'a pas d'importance pour Yesql. Vous ne perdrez jamais de temps à rechercher des fonctions avec une syntaxe équivalente à SQL. Vous n'aurez jamais à revenir à une fonction (raw-sql "some('funky'::SYNTAX)")).
- Meilleur support d'éditeur. Votre éditeur dispose déjà d'un excellent support SQL. En enregistrant SQL au format SQL, vous pouvez simplement l'utiliser.
- Compatibilité des équipes. Vos administrateurs de base de données peuvent lire et écrire le SQL que vous utilisez dans votre projet Clojure.
- Réglage des performances plus facile. Besoin de créer un plan pour une requête problématique ? Ce n'est pas un problème lorsque votre requête est du SQL standard.
- Réutilisation des requêtes. Faites glisser et déposez ces mêmes fichiers SQL dans d'autres projets, car il s'agit simplement de vieux SQL - partagez-le simplement.
À mon avis, l'idée est très cool et en même temps très simple, grâce à laquelle le projet a gagné de nombreux dans une variété de langues. Et nous essaierons ensuite d'appliquer une philosophie similaire consistant à séparer le code SQL de tout le reste bien au-delà de l'ORM.
Gestionnaires d'IDE et de bases de données
Commençons par une tâche simple du quotidien. Nous devons souvent rechercher certains objets dans la base de données, par exemple trouver une table dans le schéma et étudier sa structure (quels colonnes, clés, index, contraintes, etc. sont utilisés). Et de n'importe quel IDE graphique ou d'un petit gestionnaire de base de données, tout d'abord, nous attendons exactement ces capacités. Pour que ce soit rapide et que vous n'ayez pas à attendre une demi-heure jusqu'à ce qu'une fenêtre avec les informations nécessaires s'affiche (surtout avec une connexion lente à une base de données distante), et en même temps, les informations reçues soient fraîches et pertinentes, et pas de courrier indésirable mis en cache. De plus, plus la base de données est complexe et volumineuse et plus elle est nombreuse, plus il est difficile de le faire.
Mais généralement, je jette la souris et j'écris simplement du code. Disons que vous devez savoir quelles tables (et avec quelles propriétés) sont contenues dans le schéma « HR ». Dans la plupart des SGBD, le résultat souhaité peut être obtenu avec cette simple requête de information_schema :
select table_name
, ...
from information_schema.tables
where schema = 'HR'D'une base de données à l'autre, le contenu de ces tables de référence varie en fonction des capacités de chaque SGBD. Et, par exemple, pour MySQL, à partir du même ouvrage de référence, vous pouvez obtenir les paramètres de table spécifiques à ce SGBD :
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle ne connaît pas information_schema, mais il l'a , et aucun gros problème ne se pose :
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse ne fait pas exception :
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Quelque chose de similaire peut être fait dans Cassandra (qui a des familles de colonnes au lieu de tables et des espaces de clés au lieu de schémas) :
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Pour la plupart des autres bases de données, vous pouvez également proposer des requêtes similaires (même Mongo a , qui contient des informations sur toutes les collections du système).
Bien sûr, de cette manière, vous pouvez obtenir des informations non seulement sur les tables, mais également sur n'importe quel objet en général. De temps en temps, des personnes aimables partagent ce code pour différentes bases de données, comme par exemple dans la série d'articles Habra « Fonctions de documentation des bases de données PostgreSQL » (, , ). Bien sûr, garder toute cette montagne de requêtes en tête et les taper constamment est un tel plaisir, donc dans mon IDE/éditeur préféré, j'ai un ensemble d'extraits pré-préparés pour les requêtes fréquemment utilisées, et tout ce qui reste est de taper le noms d'objets dans le modèle.
De ce fait, cette méthode de navigation et de recherche d'objets est beaucoup plus flexible, fait gagner beaucoup de temps et permet d'obtenir exactement les informations sous la forme sous laquelle elles sont désormais nécessaires (comme, par exemple, décrites dans l'article ).
Opérations avec des objets
Après avoir trouvé et étudié les objets nécessaires, il est temps d’en faire quelque chose d’utile. Bien entendu, même sans retirer les doigts du clavier.
Ce n'est un secret pour personne que la simple suppression d'une table aura la même apparence dans presque toutes les bases de données :
drop table hr.personsMais avec la création du tableau cela devient plus intéressant. Presque tous les SGBD (y compris de nombreux NoSQL) peuvent « créer une table » sous une forme ou une autre, et la partie principale de celle-ci différera même légèrement (nom, liste des colonnes, types de données), mais d'autres détails peuvent différer considérablement et dépendre de la dispositif interne et capacités d'un SGBD spécifique. Mon exemple préféré est que dans la documentation Oracle, il n'y a que des BNF « nus » pour la syntaxe « créer une table ». . D'autres SGBD ont des capacités plus modestes, mais chacun d'eux possède également de nombreuses fonctionnalités intéressantes et uniques pour créer des tables (, , , ). Il est peu probable qu'un « assistant » graphique d'un autre IDE (surtout universel) soit capable de couvrir entièrement toutes ces capacités, et même s'il le peut, ce ne sera pas un spectacle pour les âmes sensibles. En même temps, une déclaration écrite correctement et dans les délais créer une table vous permettra de tous les utiliser facilement, de rendre le stockage et l'accès à vos données fiables, optimaux et aussi confortables que possible.
En outre, de nombreux SGBD possèdent leurs propres types d'objets spécifiques qui ne sont pas disponibles dans d'autres SGBD. De plus, nous pouvons effectuer des opérations non seulement sur les objets de la base de données, mais également sur le SGBD lui-même, par exemple « tuer » un processus, libérer de la zone mémoire, activer le traçage, passer en mode « lecture seule », et bien plus encore.
Maintenant, dessinons un peu
L'une des tâches les plus courantes consiste à créer un diagramme avec des objets de base de données et à visualiser les objets et les connexions entre eux dans une belle image. Presque n'importe quel IDE graphique, utilitaires de « ligne de commande » distincts, outils graphiques spécialisés et modélisateurs peuvent le faire. Ils dessineront quelque chose pour vous « du mieux qu'ils peuvent » et vous ne pourrez influencer un peu ce processus qu'à l'aide de quelques paramètres dans le fichier de configuration ou de cases à cocher dans l'interface.
Mais ce problème peut être résolu de manière beaucoup plus simple, plus flexible et plus élégante, et bien sûr à l’aide de code. Pour créer des diagrammes de toute complexité, nous disposons de plusieurs langages de balisage spécialisés (DOT, GraphML, etc.), et pour eux toute une série d'applications (GraphViz, PlantUML, Mermaid) capables de lire de telles instructions et de les visualiser dans une variété de formats. . Eh bien, nous savons déjà comment obtenir des informations sur les objets et les connexions entre eux.
Voici un petit exemple de ce à quoi cela pourrait ressembler, en utilisant PlantUML et (à gauche se trouve une requête SQL qui générera l'instruction requise pour PlantUML, et à droite se trouve le résultat) :
select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|> ' ||
tc.table_name as val
from table_constraints as tc
join key_column_usage as kcu
on tc.constraint_name = kcu.constraint_name
join constraint_column_usage as ccu
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and tc.table_name ~ '.*' union all
select '@enduml'Et si vous essayez un peu, alors basé sur vous pouvez obtenir quelque chose de très similaire à un vrai diagramme ER :
La requête SQL est un peu plus compliquée
-- Шапка
select '@startuml
!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>
hide methods
hide stereotypes'
union all
-- Таблицы
select format('Table(%s, "%s n information about %s") {'||chr(10), table_name, table_name, table_name) ||
(select string_agg(column_name || ' ' || upper(udt_name), chr(10))
from information_schema.columns
where table_schema = 'public'
and table_name = t.table_name) || chr(10) || '}'
from information_schema.tables t
where table_schema = 'public'
union all
-- Связи между таблицами
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %s"', ccu.table_name, tc.table_name)
from information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and ccu.constraint_schema = 'public'
and tc.table_name ~ '.*'
union all
-- Подвал
select '@enduml'
Si vous regardez attentivement, de nombreux outils de visualisation utilisent également des requêtes similaires. Il est vrai que ces demandes sont généralement profondément , sans parler de toute modification de ceux-ci.
Métriques et surveillance
Passons à un sujet traditionnellement complexe : la surveillance des performances des bases de données. Je me souviens d’une petite histoire vraie que m’a racontée « un de mes amis ». Sur un autre projet vivait un certain DBA puissant, et peu de développeurs le connaissaient personnellement ou l'avaient déjà vu en personne (malgré le fait que, selon les rumeurs, il travaillait quelque part dans le bâtiment voisin) . À l'heure « X », lorsque le système de production d'un grand détaillant a recommencé à « se sentir mal », il a envoyé silencieusement des captures d'écran de graphiques d'Oracle Enterprise Manager, sur lesquelles il a soigneusement souligné les endroits critiques avec un marqueur rouge pour « compréhension » ( cela, pour le moins, n'a pas beaucoup aidé). Et sur la base de cette « carte photo », j'ai dû traiter. Dans le même temps, personne n'avait accès au précieux (dans les deux sens du terme) Enterprise Manager, car le système est complexe et coûteux, du coup « les développeurs tombent sur quelque chose et cassent tout ». Par conséquent, les développeurs ont trouvé « empiriquement » l’emplacement et la cause des freins et ont publié un correctif. Si la lettre menaçante du DBA n'arrivait pas à nouveau dans un avenir proche, alors tout le monde pousserait un soupir de soulagement et retournerait à ses tâches actuelles (jusqu'à la nouvelle lettre).
Mais le processus de suivi peut paraître plus amusant et convivial, et surtout, accessible et transparent pour tous. Au moins sa partie fondamentale, en complément des principaux systèmes de surveillance (qui sont certainement utiles et dans de nombreux cas irremplaçables). Tout SGBD peut partager librement et absolument gratuitement des informations sur son état et ses performances actuels. Dans la même base de données Oracle « sanglante », presque toutes les informations sur les performances peuvent être obtenues à partir des vues système, allant des processus et sessions à l'état du cache tampon (par exemple, , rubrique « Surveillance »). Postgresql propose également tout un tas de vues système pour , notamment ceux qui sont indispensables dans la vie quotidienne de tout DBA, comme , , . MySQL a même un schéma distinct pour cela. . A In Mongo intégré regroupe les données de performances dans une collection de systèmes .
Ainsi, armé d'une sorte de collecteur de métriques (Telegraf, Metricbeat, Collectd) capable d'effectuer des requêtes SQL personnalisées, d'un stockage de ces métriques (InfluxDB, Elasticsearch, Timescaledb) et d'un visualiseur (Grafana, Kibana), vous pouvez obtenir un et un système de surveillance flexible qui sera étroitement intégré à d'autres mesures à l'échelle du système (obtenues, par exemple, à partir du serveur d'applications, du système d'exploitation, etc.). Comme cela se fait par exemple dans pgwatch2, qui utilise la combinaison InfluxDB + Grafana et un ensemble de requêtes vers les vues système, également accessibles .
En tout
Et ceci n'est qu'une liste approximative de ce qui peut être fait avec notre base de données en utilisant du code SQL standard. Je suis sûr que vous pouvez trouver de nombreuses autres utilisations, écrivez dans les commentaires. Et nous parlerons de comment (et surtout pourquoi) automatiser tout cela et de l'inclure dans votre pipeline CI/CD la prochaine fois.
Source: habr.com