تجربه "پایگاه داده به عنوان کد".

SQL، چه چیزی می تواند ساده تر باشد؟ هر یک از ما می توانیم یک درخواست ساده بنویسیم - ما تایپ می کنیم را انتخاب کنید، ستون های مورد نیاز را فهرست کنید، سپس از جانب، نام جدول، برخی شرایط در جایی که و این همه است - داده های مفید در جیب ما هستند، و (تقریبا) صرف نظر از اینکه کدام DBMS در آن زمان زیر پوشش قرار دارد (یا شاید) اصلا DBMS نیست). در نتیجه، کار با تقریباً هر منبع داده ای (رابطه ای و غیرمرتبط) را می توان از دیدگاه کد معمولی در نظر گرفت (با تمام آنچه که در بر دارد - کنترل نسخه، بررسی کد، تجزیه و تحلیل استاتیک، تست های خودکار، و این همه). و این نه تنها در مورد خود داده ها، طرحواره ها و مهاجرت ها، بلکه به طور کلی برای کل عمر ذخیره سازی صدق می کند. در این مقاله در مورد وظایف روزمره و مشکلات کار با پایگاه داده های مختلف تحت لنز "پایگاه داده به عنوان کد" صحبت خواهیم کرد.

و بیایید درست از آن شروع کنیم ORM. اولین نبردها از نوع "SQL vs ORM" دوباره مورد توجه قرار گرفتند پیش از پترین روسیه.

نگاشت شی - رابطه ای

حامیان ORM به طور سنتی برای سرعت و سهولت توسعه، استقلال از DBMS و کد تمیز ارزش قائل هستند. برای بسیاری از ما، کد کار با پایگاه داده (و اغلب خود پایگاه داده)

معمولا چیزی شبیه این به نظر می رسد ...

@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;
    }
  ...

این مدل با حاشیه نویسی های هوشمندانه آویزان شده است، و جایی در پشت صحنه یک ORM شجاع تعداد زیادی کد SQL را تولید و اجرا می کند. به هر حال، توسعه دهندگان تمام تلاش خود را می کنند تا با کیلومترها انتزاع خود را از پایگاه داده خود جدا کنند، که نشان دهنده برخی "نفرت SQL".

در طرف دیگر موانع، طرفداران SQL «دست ساز» خالص به توانایی بیرون کشیدن تمام آب از DBMS خود بدون لایه‌ها و انتزاع‌های اضافی اشاره می‌کنند. در نتیجه، پروژه‌های «داده محور» ظاهر می‌شوند، که در آن افراد آموزش‌دیده ویژه در پایگاه داده درگیر هستند (آنها همچنین «بنی‌گرا» هستند، همچنین «بنی‌گرا» هستند، آنها همچنین «بنی‌گرا» هستند، و غیره) و توسعه‌دهندگان فقط باید نماهای آماده و رویه های ذخیره شده را بدون پرداختن به جزئیات "کشش" کرد.

چه می شد اگر ما بهترین های هر دو دنیا را داشتیم؟ چگونه این کار در یک ابزار فوق العاده با نامی تأیید کننده زندگی انجام می شود Yesql. من چند خط از مفهوم کلی را در ترجمه آزاد خود بیان می کنم و شما می توانید با جزئیات بیشتر با آن آشنا شوید. اینجا.

Clojure یک زبان جالب برای ایجاد DSL است، اما SQL خود یک DSL جالب است و ما به دیگری نیاز نداریم. S-expressions عالی هستند، اما چیز جدیدی در اینجا اضافه نمی کنند. در نتیجه به خاطر براکت ها براکت می گیریم. موافق نیستم؟ سپس منتظر لحظه ای باشید که انتزاع روی پایگاه داده شروع به نشت کند و شما شروع به مبارزه با تابع کنید (خام-sql)

پس من باید چه کار کنم؟ بیایید SQL را به عنوان SQL معمولی بگذاریم - یک فایل در هر درخواست:

-- name: users-by-country
select *
  from users
 where country_code = :country_code

... و سپس این فایل را بخوانید و آن را به یک تابع 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" ...} ...)

با رعایت اصل "SQL به خودی خود، Clojure به خودی خود"، دریافت می کنید:

• بدون شگفتی نحوی. پایگاه داده شما (مانند سایر اطلاعات) 100% با استاندارد SQL مطابقت ندارد - اما این برای Yesql اهمیتی ندارد. شما هرگز وقت خود را برای جستجوی توابع با نحو معادل SQL تلف نخواهید کرد. شما هرگز مجبور نخواهید بود به یک تابع برگردید (raw-sql "some('funky'::SYNTAX)")).
• بهترین پشتیبانی ویرایشگر ویرایشگر شما در حال حاضر از پشتیبانی عالی SQL برخوردار است. با ذخیره SQL به عنوان SQL می توانید به سادگی از آن استفاده کنید.
• سازگاری تیم DBA های شما می توانند SQL را که در پروژه Clojure خود استفاده می کنید بخوانند و بنویسند.
• تنظیم عملکرد آسان تر آیا نیاز به ایجاد یک طرح برای یک پرس و جو مشکل دار دارید؟ وقتی پرس و جو شما SQL معمولی باشد، این مشکلی نیست.
• استفاده مجدد از پرس و جوها همان فایل‌های SQL را بکشید و در پروژه‌های دیگر رها کنید زیرا فقط SQL قدیمی است - فقط آن را به اشتراک بگذارید.

به نظر من، ایده بسیار جالب و در عین حال بسیار ساده است، که به لطف آن پروژه بسیاری را به دست آورده است پیروان به زبان های مختلف و در مرحله بعد سعی خواهیم کرد فلسفه مشابهی را برای جدا کردن کد SQL از هر چیز دیگری به مراتب فراتر از ORM اعمال کنیم.

مدیران IDE و DB

بیایید با یک کار ساده روزمره شروع کنیم. غالباً باید برخی از اشیاء را در پایگاه داده جستجو کنیم، به عنوان مثال، یک جدول در طرحواره پیدا کنیم و ساختار آن را مطالعه کنیم (از چه ستون‌ها، کلیدها، شاخص‌ها، محدودیت‌ها و غیره استفاده می‌شود). و از هر IDE گرافیکی یا یک مدیر DB کوچک، اول از همه، دقیقاً این توانایی ها را انتظار داریم. به طوری که سریع است و نیازی نیست نیم ساعت منتظر بمانید تا پنجره ای با اطلاعات لازم رسم شود (مخصوصاً با اتصال کند به پایگاه داده از راه دور) و در عین حال اطلاعات دریافتی تازه و مرتبط باشد. و آشغال های ذخیره نشده. علاوه بر این، هرچه پایگاه داده پیچیده تر و بزرگتر باشد و تعداد آنها بیشتر باشد، انجام این کار دشوارتر است.

اما معمولاً ماوس را دور می اندازم و فقط کد می نویسم. فرض کنید باید دریابید که کدام جداول (و با کدام ویژگی) در طرح "HR" موجود است. در اکثر DBMS ها، با این کوئری ساده از information_schema می توان به نتیجه دلخواه رسید:

select table_name
     , ...
  from information_schema.tables
 where schema = 'HR'

از پایگاه داده به پایگاه داده، محتویات چنین جداول مرجع بسته به قابلیت های هر DBMS متفاوت است. و به عنوان مثال، برای MySQL، از همان کتاب مرجع می توانید پارامترهای جدول مخصوص این DBMS را دریافت کنید:

select table_name
     , storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
     , row_format     -- Формат строки ("Fixed", "Dynamic" etc)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle اطلاعات_schema را نمی شناسد، اما می داند فراداده اوراکل، و هیچ مشکل بزرگی ایجاد نمی شود:

select table_name
     , pct_free       -- Минимум свободного места в блоке данных (%)
     , pct_used       -- Минимум используемого места в блоке данных (%)
     , last_analyzed  -- Дата последнего сбора статистики
     , ...
  from all_tables
 where owner = 'HR'

ClickHouse نیز از این قاعده مستثنی نیست:

select name
     , engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
     , ...
  from system.tables
 where database = 'HR'

چیزی مشابه را می توان در Cassandra انجام داد (که به جای جداول دارای ستون خانواده ها و به جای طرحواره ها دارای فضای کلیدی است):

select columnfamily_name
     , compaction_strategy_class  -- Стратегия сборки мусора
     , gc_grace_seconds           -- Время жизни мусора
     , ...
  from system.schema_columnfamilies
 where keyspace_name = 'HR'

برای اکثر پایگاه های داده دیگر، می توانید پرس و جوهای مشابهی را نیز ارائه دهید (حتی Mongo نیز دارد مجموعه سیستم ویژه، که حاوی اطلاعات مربوط به تمام مجموعه های موجود در سیستم است).

البته از این طریق می توانید نه تنها در مورد جداول، بلکه در مورد هر شیئی به طور کلی اطلاعات کسب کنید. هر از گاهی، افراد مهربان چنین کدهایی را برای پایگاه های داده مختلف به اشتراک می گذارند، به عنوان مثال، در سری مقالات habra "توابع برای مستندسازی پایگاه های داده PostgreSQL" (ایب, بن, سالن ورزش). البته، نگه داشتن کل این کوه از پرس و جوها در ذهنم و تایپ مداوم آنها بسیار لذت بخش است، بنابراین در IDE/ویرایشگر مورد علاقه من مجموعه ای از قطعات از پیش آماده شده برای پرس و جوهای پرکاربرد دارم و تنها چیزی که باقی می ماند تایپ کردن است. نام اشیاء در قالب

در نتیجه، این روش پیمایش و جستجوی اشیا بسیار انعطاف پذیرتر است، زمان زیادی را صرفه جویی می کند و به شما امکان می دهد دقیقاً اطلاعات را به شکلی که اکنون لازم است (به عنوان مثال، در پست توضیح داده شده است) دریافت کنید. "صادر کردن داده ها از یک پایگاه داده در هر قالب: آنچه که IDE ها می توانند در پلت فرم IntelliJ انجام دهند").

عملیات با اشیاء

بعد از اینکه اشیاء لازم را پیدا کردیم و مطالعه کردیم، وقت آن است که کار مفیدی با آنها انجام دهیم. به طور طبیعی، همچنین بدون برداشتن انگشتان از صفحه کلید.

بر کسی پوشیده نیست که به سادگی حذف یک جدول تقریباً در همه پایگاه های داده یکسان به نظر می رسد:

drop table hr.persons

اما با ایجاد جدول جالب تر می شود. تقریباً هر DBMS (از جمله بسیاری از NoSQL) می‌تواند به هر شکلی جدول ایجاد کند، و بخش اصلی آن حتی کمی متفاوت است (نام، فهرست ستون‌ها، انواع داده)، اما سایر جزئیات می‌توانند به‌طور چشمگیری متفاوت باشند و به آن بستگی دارد. دستگاه داخلی و قابلیت های یک DBMS خاص. مثال مورد علاقه من این است که در اسناد اوراکل فقط BNF های "لخت" برای دستور "ایجاد جدول" وجود دارد. 31 صفحه را اشغال می کند. سایر DBMS ها دارای قابلیت های متوسط ​​تری هستند، اما هر کدام از آنها ویژگی های جالب و منحصر به فردی برای ایجاد جداول نیز دارند.postgres, خروجی زیر, سوسک, کاساندرا). بعید است که هر "جادوگر" گرافیکی از یک IDE دیگر (به ویژه یک جهانی) بتواند تمام این توانایی ها را به طور کامل پوشش دهد، و حتی اگر بتواند، تماشایی برای افراد ضعیف نخواهد بود. در عین حال یک بیانیه مکتوب صحیح و به موقع ایجاد جدول به شما این امکان را می دهد که به راحتی از همه آنها استفاده کنید، ذخیره سازی و دسترسی به داده های خود را قابل اعتماد، بهینه و راحت تر کنید.

همچنین، بسیاری از DBMS ها انواع خاصی از اشیاء خود را دارند که در سایر DBMS ها موجود نیستند. علاوه بر این، ما می‌توانیم نه تنها بر روی اشیاء پایگاه داده، بلکه بر روی خود DBMS نیز عملیات انجام دهیم، به عنوان مثال، یک فرآیند را "کشتن" کنیم، مقداری از حافظه را آزاد کنیم، ردیابی را فعال کنیم، به حالت "فقط خواندنی" تغییر دهیم و موارد دیگر.

حالا بیایید کمی ترسیم کنیم

یکی از متداول ترین کارها ساختن نمودار با آبجکت های پایگاه داده و دیدن اشیا و ارتباطات بین آنها در یک تصویر زیبا است. تقریباً هر IDE گرافیکی، ابزارهای جداگانه "خط فرمان"، ابزارهای گرافیکی تخصصی و مدل سازها می توانند این کار را انجام دهند. آنها چیزی را برای شما "به بهترین شکل ممکن" ترسیم می کنند، و شما می توانید فقط با کمک چند پارامتر در فایل پیکربندی یا چک باکس های رابط، کمی روی این فرآیند تأثیر بگذارید.

اما این مشکل بسیار ساده تر، انعطاف پذیرتر و ظریف تر و البته با کمک کد قابل حل است. برای ایجاد نمودارهایی با هر پیچیدگی، ما چندین زبان نشانه گذاری تخصصی (DOT، GraphML و غیره) و برای آنها پراکنده کامل از برنامه ها (GraphViz، PlantUML، Mermaid) داریم که می توانند چنین دستورالعمل هایی را بخوانند و آنها را در قالب های مختلف تجسم کنند. . خوب، ما قبلاً می دانیم که چگونه اطلاعاتی در مورد اشیا و ارتباطات بین آنها بدست آوریم.

در اینجا یک مثال کوچک از آنچه می تواند به نظر برسد، با استفاده از PlantUML و پایگاه داده آزمایشی برای PostgreSQL (در سمت چپ یک کوئری SQL وجود دارد که دستورالعمل مورد نیاز را برای PlantUML ایجاد می کند و در سمت راست نتیجه است):

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'

و اگر کمی تلاش کنید، بر اساس آن قالب ER برای PlantUML شما می توانید چیزی بسیار شبیه به یک نمودار ER واقعی دریافت کنید:

پرس و جوی SQL کمی پیچیده تر است

-- Шапка
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'

اگر به دقت نگاه کنید، بسیاری از ابزارهای تجسم در زیر هود نیز از جستارهای مشابه استفاده می کنند. درست است، این درخواست ها معمولا عمیق هستند به کد خود برنامه متصل شده و درک آنها دشوار استبدون ذکر هیچ گونه تغییری در آنها.

متریک و نظارت

بیایید به یک موضوع سنتی پیچیده برویم - نظارت بر عملکرد پایگاه داده. من یک داستان واقعی کوچک را به یاد می آورم که «یکی از دوستانم» برایم تعریف کرد. در یک پروژه دیگر، DBA قدرتمند خاصی زندگی می کرد، و تعداد کمی از توسعه دهندگان او را شخصا می شناختند، یا تا به حال شخصاً او را دیده بودند (علیرغم اینکه، طبق شایعات، او در جایی در ساختمان بعدی کار می کرد). در ساعت "X"، زمانی که سیستم تولید یک خرده‌فروش بزرگ دوباره شروع به "احساس بد" کرد، او بی‌صدا اسکرین‌شات‌هایی از نمودارها را از Oracle Enterprise Manager فرستاد، که روی آن‌ها با دقت مکان‌های مهم را با یک نشانگر قرمز برای "قابلیت درک" برجسته کرد ( این، به بیان ملایم، کمک چندانی نکرد). و بر اساس این "کارت عکس" باید درمان می کردم. در عین حال، هیچ کس به مدیر گرانبها (به هر دو معنی کلمه) Enterprise Manager دسترسی نداشت، زیرا این سیستم پیچیده و گران است، ناگهان "توسعه دهندگان به چیزی برخورد می کنند و همه چیز را می شکنند." بنابراین، توسعه دهندگان به صورت تجربی محل و علت ترمزها را پیدا کردند و یک پچ منتشر کردند. اگر نامه تهدید آمیز DBA در آینده نزدیک دوباره به دست نمی آمد، همه نفس راحتی می کشیدند و به وظایف فعلی خود (تا نامه جدید) باز می گشتند.

اما فرآیند نظارت می‌تواند سرگرم‌کننده‌تر و دوستانه‌تر به نظر برسد و مهم‌تر از همه، در دسترس و شفاف‌تر برای همه به نظر برسد. حداقل بخش اساسی آن، به عنوان افزودنی به سیستم های نظارتی اصلی (که مطمئنا مفید و در بسیاری از موارد غیر قابل تعویض هستند). هر DBMS برای به اشتراک گذاشتن اطلاعات در مورد وضعیت فعلی و عملکرد خود کاملاً رایگان و رایگان است. در همان Oracle DB "خونین"، تقریباً هر اطلاعاتی در مورد عملکرد را می توان از نماهای سیستم به دست آورد، از فرآیندها و جلسات گرفته تا وضعیت حافظه نهان بافر (به عنوان مثال، اسکریپت های DBA، بخش "نظارت"). Postgresql همچنین دارای یک دسته کلی از نمای سیستم برای نظارت بر عملیات پایگاه داده، به ویژه آنهایی که در زندگی روزمره هر DBA ضروری هستند، مانند pg_stat_activity, pg_stat_base, pg_stat_bgwriter. MySQL حتی یک طرح مجزا برای این کار دارد. performance_schema. A In Mongo داخلی پروفایلر داده های عملکرد را در یک مجموعه سیستم جمع می کند system.profile.

بنابراین، با داشتن نوعی جمع‌آورنده معیارها (Telegraf، Metricbeat، Collectd) که می‌تواند پرس‌وجوهای سفارشی sql را انجام دهد، ذخیره‌سازی این معیارها (InfluxDB، Elasticsearch، Timescaledb) و تصویرساز (Grafana، Kibana)، می‌توانید نسبتاً آسانی دریافت کنید. و یک سیستم نظارتی منعطف که از نزدیک با سایر معیارهای کل سیستم (به عنوان مثال از سرور برنامه، از سیستم عامل و غیره به دست می آید) یکپارچه خواهد شد. به عنوان مثال، این کار در pgwatch2 انجام می شود که از ترکیب InfluxDB + Grafana و مجموعه ای از پرس و جوها برای نمایش های سیستم استفاده می کند که می توان به آنها نیز دسترسی داشت. پرس و جوهای سفارشی را اضافه کنید.

در کل

و این فقط یک لیست تقریبی از کارهایی است که می توان با پایگاه داده ما با استفاده از کد SQL معمولی انجام داد. من مطمئن هستم که می توانید کاربردهای بیشتری پیدا کنید، در نظرات بنویسید. و ما در مورد چگونگی (و مهمتر از همه چرا) صحبت خواهیم کرد که همه اینها را خودکار کنیم و دفعه بعد آن را در خط لوله CI/CD خود قرار دهیم.

منبع: www.habr.com