SQL, co może być prostszego? Każdy z nas może napisać prostą prośbę - wpisujemy wybierać, a następnie wypisz wymagane kolumny od, nazwa tabeli, niektóre warunki w gdzie i tyle – przydatne dane mamy w kieszeni i (prawie) niezależnie od tego, jaki DBMS aktualnie znajduje się pod maską (a może ). W rezultacie pracę z niemal dowolnym źródłem danych (relacyjnym i nie) można rozpatrywać z punktu widzenia zwykłego kodu (ze wszystkim, co się z tym wiąże – kontrolą wersji, przeglądaniem kodu, analizą statyczną, autotestami i to wszystko). I dotyczy to nie tylko samych danych, schematów i migracji, ale ogólnie całego życia magazynu. W tym artykule omówimy codzienne zadania i problemy pracy z różnymi bazami danych w perspektywie „bazy danych jako kodu”.
I zacznijmy od . Ponownie dostrzeżono pierwsze bitwy typu „SQL vs ORM”. .
Mapowanie obiektowo-relacyjne
Zwolennicy ORM tradycyjnie cenią szybkość i łatwość programowania, niezależność od DBMS i czysty kod. Dla wielu z nas kod do pracy z bazą danych (a często i samą bazą danych)
zwykle wygląda to mniej więcej tak...
@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;
}
...Model jest opatrzony sprytnymi adnotacjami, a gdzieś za kulisami dzielny ORM generuje i wykonuje mnóstwo kodu SQL. Nawiasem mówiąc, programiści starają się odizolować od swojej bazy danych kilometrami abstrakcji, co wskazuje na pewne .
Po drugiej stronie barykad zwolennicy czystego „ręcznie robionego” SQL zauważają możliwość wyciśnięcia całego soku ze swojego systemu DBMS bez dodatkowych warstw i abstrakcji. W rezultacie pojawiają się projekty „data-centric”, w których w bazę danych zaangażowane są specjalnie przeszkolone osoby (są także „podstawistami”, są też „podstawistami”, są też „basdenerami” itp.), a programiści wystarczy „wyciągnąć” gotowe widoki i procedury składowane, bez wchodzenia w szczegóły.
Co by było, gdybyśmy mieli to, co najlepsze z obu światów? Jak to się robi w cudownym narzędziu o nazwie potwierdzającej życie . W moim wolnym tłumaczeniu podam kilka linijek z ogólnej koncepcji i możesz zapoznać się z nią bardziej szczegółowo .
Clojure to fajny język do tworzenia DSL, ale sam SQL jest fajnym DSL i nie potrzebujemy kolejnego. Wyrażenia S są świetne, ale nie wnoszą tu nic nowego. W rezultacie otrzymujemy nawiasy ze względu na nawiasy. Nie zgadzam się? Następnie poczekaj na moment, kiedy abstrakcja po bazie danych zacznie przeciekać i zaczniesz walczyć z funkcją (surowy-sql)
Więc co powinienem zrobić? Zostawmy SQL jako zwykły SQL - jeden plik na żądanie:
-- name: users-by-country
select *
from users
where country_code = :country_code... a następnie przeczytaj ten plik, zamieniając go w zwykłą funkcję 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" ...} ...)Trzymając się zasady „sam SQL, sam Clojure” otrzymujesz:
- Żadnych syntaktycznych niespodzianek. Twoja baza danych (jak każda inna) nie jest w 100% zgodna ze standardem SQL - ale dla Yesql nie ma to znaczenia. Nigdy nie będziesz tracić czasu na szukanie funkcji o składni równoważnej SQL. Nigdy nie będziesz musiał wracać do funkcji (raw-sql "jakiś('funky'::SYNTAX)")).
- Najlepsze wsparcie edytora. Twój edytor ma już doskonałą obsługę SQL. Zapisując SQL jako SQL, możesz po prostu go użyć.
- Kompatybilność zespołu. Twoi administratorzy baz danych mogą czytać i zapisywać kod SQL używany w projekcie Clojure.
- Łatwiejsze dostrajanie wydajności. Chcesz stworzyć plan dla problematycznego zapytania? Nie stanowi to problemu, jeśli zapytanie jest zwykłym SQL.
- Ponowne użycie zapytań. Przeciągnij i upuść te same pliki SQL do innych projektów, ponieważ jest to po prostu stary SQL - po prostu go udostępnij.
Moim zdaniem pomysł jest bardzo fajny i jednocześnie bardzo prosty, dzięki czemu projekt zyskał wielu w różnych językach. Następnie spróbujemy zastosować podobną filozofię oddzielania kodu SQL od wszystkiego, co wykracza daleko poza ORM.
Menedżerowie IDE i DB
Zacznijmy od prostego, codziennego zadania. Często musimy poszukać jakichś obiektów w bazie danych, np. znaleźć w schemacie tabelę i przestudiować jej strukturę (jakie kolumny, klucze, indeksy, ograniczenia itp. są użyte). A od dowolnego graficznego IDE lub małego menedżera DB oczekujemy przede wszystkim dokładnie tych umiejętności. Żeby było szybko i nie trzeba było czekać pół godziny na wyświetlenie okna z niezbędnymi informacjami (szczególnie przy wolnym połączeniu ze zdalną bazą danych), a jednocześnie otrzymywane informacje były świeże i istotne, a nie buforowane śmieci. Co więcej, im bardziej złożona i większa jest baza danych oraz im większa jest ich liczba, tym trudniej jest to zrobić.
Ale zwykle wyrzucam mysz i po prostu piszę kod. Załóżmy, że musisz dowiedzieć się, które tabele (i jakie właściwości) są zawarte w schemacie „HR”. W większości systemów DBMS pożądany wynik można osiągnąć za pomocą tego prostego zapytania z Information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'W zależności od bazy danych zawartość takich tabel referencyjnych różni się w zależności od możliwości każdego systemu DBMS. I na przykład dla MySQL z tej samej książki referencyjnej można uzyskać parametry tabeli specyficzne dla tego DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle nie zna schematu_informacji, ale tak i nie pojawiają się żadne większe problemy:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse nie jest wyjątkiem:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Coś podobnego można zrobić w Cassandrze (która ma rodziny kolumn zamiast tabel i przestrzenie klawiszy zamiast schematów):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'W przypadku większości innych baz danych możesz również wymyślić podobne zapytania (nawet Mongo ma , który zawiera informacje o wszystkich kolekcjach w systemie).
Oczywiście w ten sposób można uzyskać informacje nie tylko o tabelach, ale w ogóle o każdym obiekcie. Od czasu do czasu życzliwi ludzie udostępniają taki kod dla różnych baz danych, jak np. w serii artykułów habra „Funkcje dokumentowania baz danych PostgreSQL” (, , ). Oczywiście trzymanie tej całej góry zapytań w głowie i ciągłe ich wpisywanie to ogromna przyjemność, dlatego w moim ulubionym IDE/edytorze mam przygotowany zestaw fragmentów często używanych zapytań i pozostaje tylko wpisać nazwy obiektów do szablonu.
Dzięki temu ten sposób nawigacji i wyszukiwania obiektów jest znacznie bardziej elastyczny, pozwala zaoszczędzić mnóstwo czasu i pozwala uzyskać dokładnie te informacje w takiej formie, w jakiej są obecnie potrzebne (jak opisano np. w poście ).
Operacje na obiektach
Po znalezieniu i przestudiowaniu niezbędnych przedmiotów nadszedł czas, aby zrobić z nimi coś pożytecznego. Oczywiście także bez odrywania palców od klawiatury.
Nie jest tajemnicą, że samo usunięcie tabeli będzie wyglądać tak samo w prawie wszystkich bazach danych:
drop table hr.personsAle wraz ze stworzeniem stołu staje się bardziej interesujące. Prawie każdy system DBMS (w tym wiele NoSQL) może „utworzyć tabelę” w takiej czy innej formie, a jej główna część będzie nawet nieznacznie się różnić (nazwa, lista kolumn, typy danych), ale inne szczegóły mogą się znacznie różnić i zależą od urządzenie wewnętrzne i możliwości konkretnego systemu DBMS. Moim ulubionym przykładem jest to, że w dokumentacji Oracle istnieją tylko „nagie” BNF dla składni „utwórz tabelę” . Inne systemy DBMS mają skromniejsze możliwości, ale każdy z nich ma także wiele ciekawych i unikalnych funkcji tworzenia tabel (, , , ). Jest mało prawdopodobne, aby jakikolwiek „kreator” graficzny z innego IDE (zwłaszcza uniwersalnego) był w stanie w pełni obsłużyć wszystkie te możliwości, a nawet gdyby to zrobił, nie będzie to spektakl dla osób o słabych nerwach. Jednocześnie prawidłowo i terminowo napisane oświadczenie utwórz stół pozwoli Ci z łatwością korzystać z nich wszystkich, sprawi, że przechowywanie i dostęp do Twoich danych będzie niezawodne, optymalne i maksymalnie komfortowe.
Ponadto wiele systemów DBMS ma własne, specyficzne typy obiektów, które nie są dostępne w innych systemach DBMS. Co więcej, możemy wykonywać operacje nie tylko na obiektach bazy danych, ale także na samym systemie DBMS, na przykład „zabić” proces, zwolnić część pamięci, włączyć śledzenie, przejść do trybu „tylko do odczytu” i wiele więcej.
Teraz trochę narysujmy
Jednym z najczęstszych zadań jest zbudowanie diagramu z obiektami bazy danych i zobaczenie obiektów i połączeń między nimi na pięknym obrazie. Może to zrobić prawie każde graficzne IDE, oddzielne narzędzia „wiersza poleceń”, wyspecjalizowane narzędzia graficzne i narzędzia do modelowania. Narysują coś dla Ciebie „najlepiej jak potrafią”, a Ty możesz choć trochę wpłynąć na ten proces za pomocą kilku parametrów w pliku konfiguracyjnym lub checkboxów w interfejsie.
Ale ten problem można rozwiązać znacznie prościej, bardziej elastycznie i elegancko, i oczywiście za pomocą kodu. Do tworzenia diagramów o dowolnej złożoności mamy kilka wyspecjalizowanych języków znaczników (DOT, GraphML itp.), a dla nich cały wachlarz aplikacji (GraphViz, PlantUML, Mermaid), które potrafią czytać takie instrukcje i wizualizować je w różnych formatach . Cóż, wiemy już, jak uzyskać informacje o obiektach i powiązaniach między nimi.
Oto mały przykład tego, jak mogłoby to wyglądać przy użyciu PlantUML i (po lewej stronie znajduje się zapytanie SQL, które wygeneruje wymaganą instrukcję dla PlantUML, a po prawej wynik):
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'A jeśli spróbujesz trochę, to na podstawie możesz uzyskać coś bardzo podobnego do prawdziwego diagramu ER:
Zapytanie SQL jest nieco bardziej skomplikowane
-- Шапка
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'
Jeśli przyjrzysz się uważnie, pod maską wiele narzędzi do wizualizacji również korzysta z podobnych zapytań. To prawda, że te prośby są zwykle głębokie , nie mówiąc już o jakichkolwiek ich modyfikacjach.
Metryki i monitorowanie
Przejdźmy do tradycyjnie złożonego tematu – monitorowania wydajności baz danych. Pamiętam krótką, prawdziwą historię opowiedzianą mi przez „jednego z moich przyjaciół”. Przy innym projekcie mieszkał pewien potężny DBA i niewielu deweloperów znało go osobiście, lub kiedykolwiek widziało go osobiście (mimo, że według plotek pracował gdzieś w sąsiednim budynku). W godzinie „X”, gdy system produkcji dużego detalisty znów zaczął „czuć się źle”, po cichu przesłał zrzuty ekranu z wykresami z Oracle Enterprise Manager, na których starannie podkreślił miejsca krytyczne czerwonym znacznikiem dla „zrozumiałości” ( to, delikatnie mówiąc, niewiele pomogło). I na podstawie tej „fotokartki” musiałam leczyć. Jednocześnie nikt nie miał dostępu do cennego (w obu znaczeniach tego słowa) Enterprise Managera, bo system jest złożony i kosztowny, nagle „programiści natrafiają na coś i wszystko psują”. Dlatego twórcy „empirycznie” znaleźli lokalizację i przyczynę hamulców i wypuścili łatkę. Jeżeli w najbliższym czasie groźny list od DBA nie dotrze ponownie, wszyscy odetchną z ulgą i powrócą do swoich bieżących zadań (do czasu nowego Listu).
Ale proces monitorowania może wyglądać bardziej zabawnie i przyjaźnie, a co najważniejsze, przystępnie i przejrzyście dla każdego. Przynajmniej jego podstawowa część, jako dodatek do głównych systemów monitoringu (które z pewnością są przydatne, a w wielu przypadkach niezastąpione). Każdy DBMS może swobodnie i całkowicie bezpłatnie udostępniać informacje o swoim aktualnym stanie i wydajności. W tej samej „cholernej” bazie danych Oracle prawie każdą informację o wydajności można uzyskać z widoków systemowych, począwszy od procesów i sesji po stan pamięci podręcznej bufora (na przykład , rozdział „Monitorowanie”). Postgresql ma także całą masę widoków systemowych , w szczególności te, które są niezbędne w codziennym życiu każdego DBA, jak np , , . MySQL ma nawet do tego osobny schemat. . A Wbudowany w Mongo agreguje dane dotyczące wydajności w zbiór systemowy .
Tak więc, uzbrojony w pewnego rodzaju moduł zbierający metryki (Telegraf, Metricbeat, Collectd), który może wykonywać niestandardowe zapytania SQL, pamięć tych metryk (InfluxDB, Elasticsearch, Timescaledb) i wizualizator (Grafana, Kibana), możesz uzyskać dość łatwe oraz elastyczny system monitorowania, który będzie ściśle zintegrowany z innymi metrykami ogólnosystemowymi (uzyskanymi na przykład z serwera aplikacji, z systemu operacyjnego itp.). Tak jak robi się to na przykład w pgwatch2, który wykorzystuje kombinację InfluxDB + Grafana i zestaw zapytań do widoków systemowych, do których również można uzyskać dostęp .
Razem
A to tylko przybliżona lista tego, co można zrobić z naszą bazą danych przy użyciu zwykłego kodu SQL. Jestem pewien, że znajdziesz o wiele więcej zastosowań, napisz w komentarzach. Porozmawiamy o tym, jak (i co najważniejsze, dlaczego) zautomatyzować to wszystko i następnym razem włączyć to do swojego rurociągu CI/CD.
Źródło: www.habr.com