SQL, que podería ser máis sinxelo? Cada un de nós pode escribir unha solicitude sinxela: escribimos seleccionar, enumere as columnas necesarias, entón de, nome da táboa, algunhas condicións en onde e iso é todo: os datos útiles están no noso peto e (case) independentemente de que DBMS estea baixo o capó nese momento (ou quizais ). Como resultado, traballar con case calquera fonte de datos (relacional e non así) pódese considerar desde o punto de vista do código común (con todo o que implica: control de versións, revisión de código, análise estática, probas automáticas e iso é todo). E isto aplícase non só aos propios datos, esquemas e migracións, senón en xeral a toda a vida útil do almacenamento. Neste artigo falaremos de tarefas cotiás e problemas de traballo con varias bases de datos baixo a lente de "base de datos como código".
E comecemos desde . As primeiras batallas do tipo "SQL vs ORM" volvéronse notar .
Mapeo relacional obxecto
Os seguidores de ORM valoran tradicionalmente a velocidade e a facilidade de desenvolvemento, a independencia do DBMS e o código limpo. Para moitos de nós, o código para traballar coa base de datos (e moitas veces a propia base de datos)
normalmente parece algo así...
@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;
}
...O modelo está colgado con anotacións intelixentes, e nalgún lugar detrás das escenas un valiente ORM xera e executa toneladas de código SQL. Por certo, os desenvolvedores están facendo todo o posible para illarse da súa base de datos con quilómetros de abstraccións, o que indica algúns .
Do outro lado das barricadas, os seguidores do SQL puro "feito a man" sinalan a capacidade de espremer todo o zume do seu DBMS sen capas e abstraccións adicionais. Como resultado, aparecen proxectos “centrados en datos”, nos que na base de datos interveñen persoas especialmente formadas (tamén son “basicistas”, tamén son “basicistas”, tamén son “basdeners”, etc.), e os desenvolvedores. só hai que "tirar" as vistas e os procedementos almacenados listos, sen entrar en detalles.
E se tivésemos o mellor dos dous mundos? Como se fai isto nunha ferramenta marabillosa cun nome que afirma a vida . Vou dar un par de liñas do concepto xeral na miña tradución gratuíta, e podes familiarizarte con el con máis detalle .
Clojure é unha linguaxe xenial para crear DSL, pero o propio SQL é un DSL xenial e non necesitamos outro. As expresións S son xeniais, pero non engaden nada novo aquí. Como resultado, obtemos corchetes en aras de corchetes. Non estás de acordo? A continuación, agarde o momento no que a abstracción sobre a base de datos comece a filtrarse e comece a loitar coa función (raw-sql)
Entón, que debería facer? Deixemos SQL como SQL normal: un ficheiro por solicitude:
-- name: users-by-country
select *
from users
where country_code = :country_code... e despois le este ficheiro, converténdoo nunha función normal de Clojure:
(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" ...} ...)Ao unirse ao principio "SQL por si mesmo, Clojure por si mesmo", obtén:
- Sen sorpresas sintácticas. A súa base de datos (como calquera outra) non cumpre 100 % co estándar SQL, pero isto non importa para Yesql. Nunca perderás o tempo buscando funcións con sintaxe equivalente a SQL. Nunca terás que volver a unha función (raw-sql "some('funky'::SYNTAX)")).
- Mellor soporte de editor. O teu editor xa ten un excelente soporte SQL. Ao gardar SQL como SQL simplemente podes usalo.
- Compatibilidade de equipos. Os teus DBA poden ler e escribir o SQL que usas no teu proxecto Clojure.
- Axuste do rendemento máis sinxelo. Necesitas crear un plan para unha consulta problemática? Isto non é un problema cando a súa consulta é SQL normal.
- Reutilizando consultas. Arrastra e solta eses mesmos ficheiros SQL noutros proxectos porque é un SQL antigo, só compárteo.
Na miña opinión, a idea é moi chula e ao mesmo tempo moi sinxela, grazas á cal o proxecto gañou moitos nunha variedade de linguas. E a continuación trataremos de aplicar unha filosofía similar de separar o código SQL de todo o que vai máis aló do ORM.
Xestores de IDE e DB
Imos comezar cunha tarefa cotiá sinxela. Moitas veces temos que buscar algúns obxectos na base de datos, por exemplo, atopar unha táboa no esquema e estudar a súa estrutura (que columnas, claves, índices, restricións, etc. se utilizan). E de calquera IDE gráfico ou un pequeno xestor de base de datos, en primeiro lugar, esperamos exactamente estas habilidades. Para que sexa rápido e non teñas que esperar media hora ata que se debuxa unha xanela coa información necesaria (especialmente cunha conexión lenta a unha base de datos remota), e ao mesmo tempo, a información recibida sexa fresca e relevante, e non lixo almacenado na caché. Ademais, canto máis complexa e grande sexa a base de datos e canto maior sexa o número delas, máis difícil é facelo.
Pero normalmente tiro o rato e só escribo código. Digamos que cómpre descubrir que táboas (e con que propiedades) están contidas no esquema "HR". Na maioría dos DBMS, o resultado desexado pódese conseguir con esta sinxela consulta de information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'De base de datos a base de datos, o contido destas táboas de referencia varía dependendo das capacidades de cada DBMS. E, por exemplo, para MySQL, dende o mesmo libro de referencia podes obter parámetros de táboa específicos para este DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle non coñece information_schema, pero si , e non hai grandes problemas:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse non é unha excepción:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Algo semellante pódese facer en Cassandra (que ten familias de columnas en lugar de táboas e espazos de teclas en lugar de esquemas):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Para a maioría das outras bases de datos, tamén podes facer consultas similares (mesmo Mongo ten , que contén información sobre todas as coleccións do sistema).
Por suposto, deste xeito pode obter información non só sobre táboas, senón sobre calquera obxecto en xeral. De cando en vez, persoas amables comparten ese código para diferentes bases de datos, como, por exemplo, na serie de artigos de habra "Funcións para documentar bases de datos PostgreSQL" (, , ). Por suposto, manter toda esta montaña de consultas na miña cabeza e teclealas constantemente é un pracer, polo que no meu IDE/editor favorito teño un conxunto de fragmentos preparados previamente para consultas de uso frecuente, e só queda escribir o nomes de obxectos no modelo.
Como resultado, este método de navegación e busca de obxectos é moito máis flexible, aforra moito tempo e permítelle obter exactamente a información na forma na que agora é necesaria (como, por exemplo, se describe na publicación). ).
Operacións con obxectos
Despois de atopar e estudar os obxectos necesarios, é hora de facer algo útil con eles. Por suposto, tamén sen quitar os dedos do teclado.
Non é ningún segredo que simplemente eliminar unha táboa terá o mesmo aspecto en case todas as bases de datos:
drop table hr.personsPero coa creación da mesa faise máis interesante. Case calquera DBMS (incluíndo moitos NoSQL) pode "crear táboas" dunha ou outra forma, e a súa parte principal incluso diferirá lixeiramente (nome, lista de columnas, tipos de datos), pero outros detalles poden diferir drasticamente e depender do dispositivo interno e capacidades dun DBMS específico. O meu exemplo favorito é que na documentación de Oracle só hai BNF "naked" para a sintaxe "create table" . Outros DBMS teñen capacidades máis modestas, pero cada un deles tamén ten moitas características interesantes e únicas para crear táboas (, , , ). É improbable que calquera "mago" gráfico doutro IDE (especialmente un universal) poida cubrir por completo todas estas habilidades, e aínda que poida, non será un espectáculo para os débiles de corazón. Ao mesmo tempo, unha declaración escrita correcta e oportuna crear táboa permitirache utilizar todos eles facilmente, facer que o almacenamento e o acceso aos teus datos sexan fiables, óptimos e o máis cómodos posible.
Ademais, moitos DBMS teñen os seus propios tipos específicos de obxectos que non están dispoñibles noutros DBMS. Ademais, podemos realizar operacións non só en obxectos de base de datos, senón tamén no propio DBMS, por exemplo, "matar" un proceso, liberar algunha área de memoria, habilitar o rastrexo, cambiar ao modo "só lectura" e moito máis.
Agora imos debuxar un pouco
Unha das tarefas máis comúns é construír un diagrama con obxectos de base de datos e ver os obxectos e as conexións entre eles nunha fermosa imaxe. Case calquera IDE gráfico, utilidades separadas de "liña de comandos", ferramentas gráficas especializadas e modeladores poden facelo. Debuxarán algo para ti "o mellor que poidan" e podes influír un pouco neste proceso só coa axuda duns poucos parámetros no ficheiro de configuración ou nas caixas de verificación da interface.
Pero este problema pódese resolver moito máis sinxelo, flexible e elegante, e por suposto coa axuda do código. Para crear diagramas de calquera complexidade, dispoñemos de varios linguaxes de marcado especializados (DOT, GraphML, etc.), e para eles toda unha dispersión de aplicacións (GraphViz, PlantUML, Mermaid) que poden ler tales instrucións e visualizalas nunha variedade de formatos. . Pois xa sabemos como conseguir información sobre obxectos e conexións entre eles.
Aquí tes un pequeno exemplo de como podería ser isto, usando PlantUML e (á esquerda hai unha consulta SQL que xerará a instrución necesaria para PlantUML, e á dereita está o resultado):
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'E se probas un pouco, entón en base a podes obter algo moi parecido a un diagrama ER real:
A consulta SQL é un pouco máis complicada
-- Шапка
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'
Se miras con atención, moitas ferramentas de visualización tamén usan consultas similares. É certo que estas peticións adoitan ser profundas , sen esquecer ningunha modificación dos mesmos.
Métricas e seguimento
Pasemos a un tema tradicionalmente complexo: o seguimento do rendemento da base de datos. Lembro unha pequena historia real que me contou "un dos meus amigos". Noutro proxecto vivía un certo DBA poderoso, e poucos dos desenvolvedores coñecíano persoalmente, ou xa o viron en persoa (a pesar de que, segundo os rumores, traballaba nalgún lugar do edificio seguinte). Á hora "X", cando o sistema de produción dun gran comerciante comezou a "sentirse mal" unha vez máis, enviou en silencio capturas de pantalla de gráficos de Oracle Enterprise Manager, nos que destacaba coidadosamente os lugares críticos cun marcador vermello para "comprensibilidade" ( isto, por dicilo suavemente, non axudou moito). E baseándome nesta "tarxeta fotográfica" tiven que tratar. Ao mesmo tempo, ninguén tivo acceso ao precioso (en ambos os sentidos da palabra) Enterprise Manager, porque o sistema é complexo e caro, de súpeto "os desenvolvedores tropezan con algo e rompen todo". Polo tanto, os desenvolvedores atoparon "empíricamente" a localización e a causa dos freos e lanzaron un parche. Se a ameazadora carta do DBA non chegase de novo nun futuro próximo, entón todos respirarían aliviados e volverían ás súas tarefas actuais (ata a nova Carta).
Pero o proceso de seguimento pode parecer máis divertido e amigable e, o máis importante, accesible e transparente para todos. Polo menos a súa parte básica, como complemento aos principais sistemas de vixilancia (que son certamente útiles e en moitos casos insubstituíbles). Calquera DBMS é libre e absolutamente gratuíto para compartir información sobre o seu estado e rendemento actuais. Na mesma base de datos de Oracle "ensangrentada", pódese obter case calquera información sobre o rendemento das vistas do sistema, dende procesos e sesións ata o estado da caché do búfer (por exemplo, , sección "Vixilancia"). Postgresql tamén ten unha morea de vistas do sistema para , en particular aqueles que son indispensables na vida cotiá de calquera DBA, como , , . MySQL incluso ten un esquema separado para isto. . A En Mongo empotrado agrega datos de rendemento nunha colección do sistema .
Así, armado con algún tipo de colector de métricas (Telegraf, Metricbeat, Collectd) que pode realizar consultas sql personalizadas, un almacenamento destas métricas (InfluxDB, Elasticsearch, Timescaledb) e un visualizador (Grafana, Kibana), podes conseguir un sistema bastante sinxelo. e un sistema de vixilancia flexible que estará estreitamente integrado con outras métricas de todo o sistema (obtidas, por exemplo, do servidor de aplicacións, do SO, etc.). Como, por exemplo, isto faise en pgwatch2, que utiliza a combinación InfluxDB + Grafana e un conxunto de consultas ás vistas do sistema, ás que tamén se pode acceder .
En total
E esta é só unha lista aproximada do que se pode facer coa nosa base de datos usando código SQL normal. Seguro que podes atopar moitos máis usos, escribe nos comentarios. E falaremos de como (e o máis importante por que) automatizar todo isto e incluílo na túa canalización de CI/CD a próxima vez.
Fonte: www.habr.com