SQL, apa yang lebih sederhana? Masing-masing dari kita dapat menulis permintaan sederhana - kita mengetik memilih, lalu daftarkan kolom yang diperlukan dari, nama tabel, beberapa kondisi di dimana dan itu saja - data berguna ada di saku kita, dan (hampir) terlepas dari DBMS mana yang ada saat itu (atau mungkin ). Hasilnya, bekerja dengan hampir semua sumber data (relasional dan tidak) dapat dilihat dari sudut pandang kode biasa (dengan segala implikasinya - kontrol versi, tinjauan kode, analisis statis, pengujian otomatis, dan itu saja). Dan ini tidak hanya berlaku untuk data itu sendiri, skema, dan migrasi, tetapi secara umum untuk seluruh umur penyimpanan. Pada artikel ini kita akan berbicara tentang tugas sehari-hari dan masalah bekerja dengan berbagai database di bawah lensa “database sebagai kode”.
Dan mari kita mulai dari . Pertarungan pertama tipe "SQL vs ORM" terlihat kembali .
Pemetaan objek-relasional
Pendukung ORM secara tradisional menghargai kecepatan dan kemudahan pengembangan, kemandirian dari DBMS, dan kode yang bersih. Bagi banyak dari kita, kode untuk bekerja dengan database (dan seringkali database itu sendiri)
biasanya terlihat seperti ini...
@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 ini dilengkapi dengan anotasi cerdas, dan di balik layar, ORM yang gagah berani menghasilkan dan mengeksekusi banyak kode SQL. Omong-omong, pengembang mencoba yang terbaik untuk mengisolasi diri mereka dari database mereka dengan abstraksi berkilo-kilometer, yang menunjukkan beberapa hal .
Di sisi lain barikade, penganut SQL "buatan tangan" murni mencatat kemampuan untuk memeras semua manfaat dari DBMS mereka tanpa lapisan dan abstraksi tambahan. Akibatnya, proyek-proyek “data-centric” muncul, di mana orang-orang yang terlatih khusus terlibat dalam database (mereka juga “basicist”, mereka juga “basicist”, mereka juga “basdeners”, dll.), dan para pengembang hanya perlu "menarik" tampilan yang sudah jadi dan prosedur tersimpan, tanpa menjelaskan secara detail.
Bagaimana jika kita mendapatkan yang terbaik dari kedua dunia? Bagaimana hal ini dilakukan dalam alat yang luar biasa dengan nama yang meneguhkan kehidupan . Saya akan memberikan beberapa baris dari konsep umum dalam terjemahan gratis saya, dan Anda dapat mengenalnya lebih detail .
Clojure adalah bahasa yang keren untuk membuat DSL, tetapi SQL sendiri adalah DSL yang keren, dan kita tidak memerlukan yang lain. Ekspresi S memang bagus, tetapi tidak menambahkan sesuatu yang baru di sini. Hasilnya, kami mendapatkan tanda kurung demi tanda kurung. Tidak setuju? Kemudian tunggu saat abstraksi pada database mulai bocor dan Anda mulai berkelahi dengan fungsinya (mentah-sql)
Jadi apa yang harus aku lakukan? Biarkan SQL sebagai SQL biasa - satu file per permintaan:
-- name: users-by-country
select *
from users
where country_code = :country_code... dan kemudian membaca file ini, mengubahnya menjadi fungsi Clojure biasa:
(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" ...} ...)Dengan mengikuti prinsip "SQL dengan sendirinya, Clojure dengan sendirinya", Anda mendapatkan:
- Tidak ada kejutan sintaksis. Basis data Anda (seperti yang lainnya) tidak 100% sesuai dengan standar SQL - tetapi ini tidak menjadi masalah bagi Yesql. Anda tidak akan pernah membuang waktu mencari fungsi dengan sintaks setara SQL. Anda tidak perlu kembali ke suatu fungsi (raw-sql "beberapa('funky'::SYNTAX)")).
- Dukungan editor terbaik. Editor Anda sudah memiliki dukungan SQL yang sangat baik. Dengan menyimpan SQL sebagai SQL Anda cukup menggunakannya.
- Kompatibilitas tim. DBA Anda dapat membaca dan menulis SQL yang Anda gunakan dalam proyek Clojure Anda.
- Penyetelan kinerja yang lebih mudah. Perlu membuat rencana untuk kueri yang bermasalah? Ini bukan masalah jika kueri Anda adalah SQL biasa.
- Menggunakan kembali kueri. Seret dan lepas file SQL yang sama ke proyek lain karena ini hanyalah SQL lama - bagikan saja.
Menurut pendapat saya, idenya sangat keren dan sekaligus sangat sederhana, berkat proyek ini telah memperoleh banyak manfaat dalam berbagai bahasa. Dan selanjutnya kami akan mencoba menerapkan filosofi serupa dalam memisahkan kode SQL dari segala hal lain yang jauh melampaui ORM.
Manajer IDE & DB
Mari kita mulai dengan tugas sederhana sehari-hari. Seringkali kita harus mencari beberapa objek di database, misalnya mencari tabel di skema dan mempelajari strukturnya (kolom, kunci, indeks, batasan, dll. Apa yang digunakan). Dan dari IDE grafis atau manajer DB kecil apa pun, pertama-tama, kami mengharapkan kemampuan ini. Sehingga cepat dan Anda tidak perlu menunggu setengah jam hingga sebuah jendela dengan informasi yang diperlukan ditampilkan (terutama dengan koneksi yang lambat ke database jarak jauh), dan pada saat yang sama, informasi yang diterima segar dan relevan, dan bukan sampah yang di-cache. Selain itu, semakin kompleks dan besar database serta semakin besar jumlahnya, semakin sulit melakukan hal ini.
Tapi biasanya saya membuang mouse dan hanya menulis kode. Katakanlah Anda perlu mencari tahu tabel mana (dan properti mana) yang terdapat dalam skema "HR". Di sebagian besar DBMS, hasil yang diinginkan dapat dicapai dengan kueri sederhana ini dari information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Dari database ke database, isi tabel referensi tersebut berbeda-beda tergantung pada kemampuan masing-masing DBMS. Dan, misalnya, untuk MySQL, dari buku referensi yang sama Anda bisa mendapatkan parameter tabel khusus untuk DBMS ini:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle tidak mengetahui information_schema, tetapi ia tahu , dan tidak ada masalah besar yang muncul:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse tidak terkecuali:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Hal serupa dapat dilakukan di Cassandra (yang memiliki keluarga kolom, bukan tabel, dan ruang kunci, bukan skema):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Untuk sebagian besar database lain, Anda juga dapat mengajukan pertanyaan serupa (bahkan Mongo pun punya , yang berisi informasi tentang semua koleksi dalam sistem).
Tentu saja, dengan cara ini Anda bisa mendapatkan informasi tidak hanya tentang tabel, tetapi tentang objek apa pun secara umum. Dari waktu ke waktu, orang-orang baik hati membagikan kode tersebut untuk database yang berbeda, seperti, misalnya, dalam rangkaian artikel habra “Fungsi untuk mendokumentasikan database PostgreSQL” (, , ). Tentu saja, mengingat segunung pertanyaan ini di kepala saya dan terus-menerus mengetiknya adalah suatu kesenangan, jadi di IDE/editor favorit saya, saya memiliki kumpulan cuplikan yang sudah disiapkan sebelumnya untuk pertanyaan yang sering digunakan, dan yang tersisa hanyalah mengetikkan nama objek ke dalam template.
Hasilnya, metode navigasi dan pencarian objek ini jauh lebih fleksibel, menghemat banyak waktu, dan memungkinkan Anda mendapatkan informasi persis dalam bentuk yang diperlukan saat ini (seperti, misalnya, dijelaskan dalam postingan ).
Operasi dengan objek
Setelah kita menemukan dan mempelajari benda-benda yang diperlukan, sekarang saatnya melakukan sesuatu yang berguna dengannya. Wajar saja, juga tanpa melepaskan jari dari keyboard.
Bukan rahasia lagi bahwa menghapus tabel saja akan terlihat sama di hampir semua database:
drop table hr.personsNamun dengan dibuatnya tabel menjadi lebih menarik. Hampir semua DBMS (termasuk banyak NoSQL) dapat “membuat tabel” dalam satu bentuk atau lainnya, dan bagian utamanya bahkan akan sedikit berbeda (nama, daftar kolom, tipe data), namun detail lainnya dapat sangat berbeda dan bergantung pada perangkat internal dan kemampuan DBMS tertentu. Contoh favorit saya adalah bahwa dalam dokumentasi Oracle hanya ada BNF “telanjang” untuk sintaks “buat tabel”. . DBMS lain memiliki kemampuan yang lebih sederhana, tetapi masing-masing DBMS juga memiliki banyak fitur menarik dan unik untuk membuat tabel (, , , ). Tidak mungkin ada “penyihir” grafis dari IDE lain (terutama yang universal) yang mampu sepenuhnya mencakup semua kemampuan ini, dan bahkan jika bisa, itu tidak akan menjadi tontonan bagi orang yang lemah hati. Pada saat yang sama, pernyataan tertulis yang benar dan tepat waktu buat tabel akan memungkinkan Anda menggunakan semuanya dengan mudah, menjadikan penyimpanan dan akses ke data Anda andal, optimal, dan senyaman mungkin.
Selain itu, banyak DBMS yang memiliki tipe objek spesifiknya sendiri yang tidak tersedia di DBMS lain. Selain itu, kita dapat melakukan operasi tidak hanya pada objek database, tetapi juga pada DBMS itu sendiri, misalnya, “mematikan” suatu proses, mengosongkan sebagian area memori, mengaktifkan penelusuran, beralih ke mode “hanya baca”, dan banyak lagi.
Sekarang mari kita menggambar sedikit
Salah satu tugas yang paling umum adalah membuat diagram dengan objek database dan melihat objek serta hubungan di antara objek tersebut dalam gambar yang indah. Hampir semua IDE grafis, utilitas “baris perintah” terpisah, alat grafis khusus, dan pemodel dapat melakukan hal ini. Mereka akan menggambar sesuatu untuk Anda "sebaik yang mereka bisa", dan Anda dapat sedikit memengaruhi proses ini hanya dengan bantuan beberapa parameter di file konfigurasi atau kotak centang di antarmuka.
Namun masalah ini dapat diselesaikan dengan lebih sederhana, lebih fleksibel dan elegan, dan tentunya dengan bantuan kode. Untuk membuat diagram dengan kompleksitas apa pun, kami memiliki beberapa bahasa markup khusus (DOT, GraphML, dll), dan untuk itu terdapat banyak aplikasi (GraphViz, PlantUML, Mermaid) yang dapat membaca instruksi tersebut dan memvisualisasikannya dalam berbagai format. . Nah, kita sudah mengetahui cara mendapatkan informasi tentang objek dan hubungan antar objek.
Berikut adalah contoh kecil tampilannya, menggunakan PlantUML dan (di sebelah kiri adalah kueri SQL yang akan menghasilkan instruksi yang diperlukan untuk PlantUML, dan di sebelah kanan adalah hasilnya):
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'Dan jika Anda mencoba sedikit, maka berdasarkan Anda bisa mendapatkan sesuatu yang sangat mirip dengan diagram ER nyata:
Kueri SQL sedikit lebih rumit
-- Шапка
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'
Jika Anda perhatikan lebih dekat, banyak alat visualisasi juga menggunakan kueri serupa. Benar, permintaan ini biasanya sangat mendalam , belum lagi modifikasinya.
Metrik dan pemantauan
Mari beralih ke topik yang biasanya rumit - pemantauan kinerja basis data. Saya ingat sebuah kisah nyata kecil yang diceritakan kepada saya oleh “salah satu teman saya.” Di proyek lain hiduplah seorang DBA yang kuat, dan hanya sedikit pengembang yang mengenalnya secara pribadi, atau pernah melihatnya secara langsung (terlepas dari kenyataan bahwa, menurut rumor, dia bekerja di suatu tempat di gedung sebelah). Pada jam "X", ketika sistem produksi pengecer besar mulai "merasa tidak enak" sekali lagi, dia diam-diam mengirimkan tangkapan layar grafik dari Oracle Enterprise Manager, di mana dia dengan hati-hati menyorot tempat-tempat penting dengan penanda merah agar "dapat dipahami" ( ini, secara halus, tidak banyak membantu). Dan berdasarkan “kartu foto” ini saya harus mentraktirnya. Pada saat yang sama, tidak ada seorang pun yang memiliki akses ke Manajer Perusahaan yang berharga (dalam kedua arti tersebut), karena sistemnya rumit dan mahal, tiba-tiba “pengembang tersandung sesuatu dan menghancurkan segalanya.” Oleh karena itu, pengembang “secara empiris” menemukan lokasi dan penyebab rem dan merilis patch. Jika surat ancaman dari DBA tidak sampai lagi dalam waktu dekat, maka semua orang akan bernapas lega dan kembali ke tugasnya saat ini (sampai Surat baru).
Namun proses pemantauan dapat terlihat lebih menyenangkan dan bersahabat, dan yang paling penting, dapat diakses dan transparan oleh semua orang. Setidaknya bagian dasarnya, sebagai tambahan terhadap sistem pemantauan utama (yang tentunya berguna dan dalam banyak kasus tidak tergantikan). Setiap DBMS bebas dan gratis untuk berbagi informasi tentang keadaan dan kinerjanya saat ini. Dalam Oracle DB "berdarah" yang sama, hampir semua informasi tentang kinerja dapat diperoleh dari tampilan sistem, mulai dari proses dan sesi hingga status buffer cache (misalnya, , bagian "Pemantauan"). Postgresql juga memiliki banyak tampilan sistem , khususnya yang sangat diperlukan dalam kehidupan sehari-hari DBA mana pun, seperti , , . MySQL bahkan memiliki skema terpisah untuk ini. . A Di Mongo bawaan mengumpulkan data kinerja ke dalam kumpulan sistem .
Jadi, berbekal semacam pengumpul metrik (Telegraf, Metricbeat, Collectd) yang dapat melakukan kueri sql khusus, penyimpanan metrik ini (InfluxDB, Elasticsearch, Timescaledb) dan visualisator (Grafana, Kibana), Anda bisa mendapatkan yang cukup mudah dan sistem pemantauan fleksibel yang akan terintegrasi erat dengan metrik seluruh sistem lainnya (diperoleh, misalnya, dari server aplikasi, dari OS, dll.). Misalnya, hal ini dilakukan di pgwatch2, yang menggunakan kombinasi InfluxDB + Grafana dan serangkaian kueri ke tampilan sistem, yang juga dapat diakses .
Total
Dan ini hanyalah daftar perkiraan tentang apa yang dapat dilakukan dengan database kami menggunakan kode SQL biasa. Saya yakin Anda dapat menemukan lebih banyak kegunaan, tulis di komentar. Dan kita akan membahas tentang bagaimana (dan yang terpenting mengapa) mengotomatiskan semua ini dan memasukkannya ke dalam pipeline CI/CD Anda di lain waktu.
Sumber: www.habr.com