{"id":83649,"date":"2020-06-02T07:42:21","date_gmt":"2020-06-02T05:42:21","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/database-as-sode-experience"},"modified":"2020-06-02T07:42:21","modified_gmt":"2020-06-02T05:42:21","slug":"database-as-sode-experience","status":"publish","type":"post","link":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/database-as-sode-experience","title":{"rendered":"Experien\u021ba \u201eDatabase as Code\u201d","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Experien\u021ba \u201eDatabase as Code\u201d\" src=\"\/wp-content\/uploads\/2020\/06\/a1ac23deeded97559fbe787ed09184a5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>SQL, ce poate fi mai simplu? Fiecare dintre noi poate scrie o interogare simpl\u0103 \u2014 introducem <strong><em>select<\/em><\/strong>, enumer\u0103m coloanele necesare, apoi <strong><em>from<\/em><\/strong>, numele tabelului, pu\u021bine condi\u021bii \u00een <strong><em>unde<\/em><\/strong> \u0219i gata \u2014 datele utile sunt \u00een m\u00e2n\u0103, iar (aproape) indiferent de baza de date care ruleaz\u0103 \u00een acest moment (sau poate c\u0103 <noindex><a rel=\"nofollow\" href=\"https:\/\/osquery.io\/\">nu este o baz\u0103 de date deloc<\/a><\/noindex>). Prin urmare, lucrul cu practic orice surs\u0103 de date (rela\u021bional\u0103 sau nu) poate fi privit din perspectiva unui cod obi\u0219nuit (cu toate implica\u021biile \u2014 controlul versiunilor, revizarea codului, analiza static\u0103, teste automate \u0219i a\u0219a mai departe). \u0218i acest lucru se refer\u0103 nu doar la datele \u00een sine, schemele \u0219i migra\u021bia, ci \u00een general la \u00eentreaga activitate a depozitului. \u00cen acest articol vom discuta despre sarcinile \u0219i problemele zilnice \u00een lucrul cu diverse baze de date sub lupa &quot;database as code&quot;.<\/p>\n<p><\/p>\n<p>\u0218i vom \u00eencepe direct cu <noindex><a rel=\"nofollow\" href=\"https:\/\/www.yegor256.com\/2014\/12\/01\/orm-offensive-anti-pattern.html\">ORM<\/a><\/noindex>. Primele confrunt\u0103ri de tip &quot;SQL vs ORM&quot; au fost observate \u00eenc\u0103 \u00een <noindex><a rel=\"nofollow\" href=\"https:\/\/www.sql.ru\/forum\/904343\/orm-vs-sql\">Rusiei pre-petroliere<\/a><\/noindex>.<\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h5 id=\"obektno-relyacionnyy-maping\">Maparea obiect-rela\u021bie<\/h5>\n<p><\/p>\n<p>Sus\u021bin\u0103torii ORM apreciaz\u0103 tradi\u021bional viteza \u0219i simplitatea dezvolt\u0103rii, independen\u021ba de baza de date \u0219i cur\u0103\u021benia codului. Pentru mul\u021bi dintre noi, codul de lucru cu baza de date (\u0219i adesea \u00eens\u0103\u0219i baza de date)<\/p>\n<p><\/p>\n<p>                        <b class=\"spoiler_title\">de obicei arat\u0103 cam a\u0219a\u2026<\/b><\/p>\n<pre><code class=\"java\">@Entity\n@Table(name = &quot;stock&quot;, catalog = &quot;maindb&quot;, uniqueConstraints = {\n        @UniqueConstraint(columnNames = &quot;STOCK_NAME&quot;),\n        @UniqueConstraint(columnNames = &quot;STOCK_CODE&quot;) })\npublic class Stock implements java.io.Serializable {\n\n    @Id\n    @GeneratedValue(strategy = IDENTITY)\n    @Column(name = &quot;STOCK_ID&quot;, unique = true, nullable = false)\n    public Integer getStockId() {\n        return this.stockId;\n    }\n  ...<\/code><\/pre>\n<p><\/p>\n<p>Modelul este \u00eembog\u0103\u021bit cu anota\u021bii inteligente, iar undeva \u00een spatele scenei, bravul ORM genereaz\u0103 \u0219i execut\u0103 tone de cod SQL. A\u0219a cum este de spus, dezvoltatorii \u00eencearc\u0103 din r\u0103sputeri s\u0103 se izoleze de baza de date cu kilometri de abstrac\u021bii, ceea ce sugereaz\u0103 o oarecare <noindex><a rel=\"nofollow\" href=\"https:\/\/www.sql.ru\/forum\/1303231\/prichiny-nenavisti-k-yazyku-sql\">&quot;SQL al urii&quot;<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>De cealalt\u0103 parte a baricadei, sus\u021bin\u0103torii SQL-ului &quot;handmade&quot; subliniaz\u0103 posibilitatea de a extrage tot ce poate de la SGBD-ul lor f\u0103r\u0103 straturi \u0219i abstrac\u021bii suplimentare. Drept rezultat, apar proiecte &quot;data-centric&quot; unde baza este gestionat\u0103 de persoane special instruite (numite \u0219i &quot;bazi\u0219ti&quot;, &quot;bazovici&quot;, &quot;bazdeni&quot; etc.), iar dezvoltatorii r\u0103m\u00e2n doar s\u0103 &quot;trag\u0103&quot; view-uri gata f\u0103cute \u0219i proceduri stocate, f\u0103r\u0103 a se implica \u00een detalii.<\/p>\n<p><\/p>\n<p>\u0218i ce ar fi dac\u0103 am lua ce e mai bun din ambele lumi? A\u0219a cum este realizat \u00een acest instrument minunat cu un nume optimist <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/krisajenkins\/yesql\">Yesql<\/a><\/noindex>. Voi prezenta c\u00e2teva r\u00e2nduri din conceptul general \u00een traducerea mea liber\u0103, iar pentru mai multe detalii cu acesta se poate face cuno\u0219tin\u021b\u0103 <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/krisajenkins\/yesql#rationale\">aici<\/a><\/noindex>.<\/p>\n<p><\/p>\n<blockquote><p>Clojure este un limbaj excelent pentru crearea de DSL-uri, dar SQL-ul este deja un DSL excelent, \u0219i nu avem nevoie de unul nou. S-expresiile sunt frumoase, dar aici nu ofer\u0103 nimic nou. \u00cen cele din urm\u0103, ob\u021binem paranteze pentru paranteze. Nu e\u0219ti de acord? Apoi, a\u0219teapt\u0103 momentul \u00een care abstrac\u021bia deasupra bazei de date va \u00eencepe s\u0103 curg\u0103, \u0219i vei \u00eencepe lupta cu func\u021bia. <em>(raw-sql)<\/em><\/p>\n<p>\u0218i ce facem? S\u0103 l\u0103s\u0103m SQL-ul s\u0103 fie un SQL obi\u0219nuit \u2014 un fi\u0219ier pentru fiecare interogare:<\/p><\/blockquote>\n<p><\/p>\n<pre><code class=\"sql\">-- name: users-by-country\nselect *\n  from users\n where country_code = :country_code<\/code><\/pre>\n<p><\/p>\n<blockquote><p>\u2026 \u0219i apoi cite\u0219te acest fi\u0219ier, transform\u00e2ndu-l \u00eentr-o func\u021bie Clojure obi\u0219nuit\u0103:<\/p><\/blockquote>\n<p><\/p>\n<pre><code class=\"lisp\">(defqueries &quot;some\/where\/users_by_country.sql&quot;\n   {:connection db-spec})\n\n;;; O func\u021bie cu numele `users-by-country` a fost creat\u0103.\n;;; S\u0103 o folosim:\n(users-by-country {:country_code &quot;GB&quot;})\n;=&gt; ({:name &quot;Kris&quot; :country_code &quot;GB&quot; ...} ...)<\/code><\/pre>\n<p><\/p>\n<blockquote><p>Respect\u00e2nd principiul &quot;SQL separat, Clojure separat&quot;, ob\u021bii:<\/p>\n<ul>\n<li>F\u0103r\u0103 surprize de sintax\u0103. Baza ta de date (la fel ca orice alt\u0103 baz\u0103 de date) nu respect\u0103 standardul SQL \u00een propor\u021bie de 100% \u2014 dar pentru Yesql acest lucru nu conteaz\u0103. Nu vei pierde niciodat\u0103 timp c\u0103ut\u00e2nd func\u021bii cu o sintax\u0103 echivalent\u0103 cu SQL. Nu va trebui niciodat\u0103 s\u0103 te \u00eentorci la func\u021bia <em>(raw-sql &quot;some (&#8216;funky&#8217; :: SYNTAX)&quot;))<\/em>.<\/li>\n<li>Cea mai bun\u0103 sus\u021binere din partea editorului. Editorul t\u0103u deja are un suport excelent pentru SQL. P\u0103str\u00e2nd SQL ca SQL, po\u021bi pur \u0219i simplu s\u0103-l folose\u0219ti.<\/li>\n<li>Compatibilitate \u00een echip\u0103. DBA-ii t\u0103i pot citi \u0219i scrie SQL-ul pe care \u00eel folose\u0219ti \u00een proiectul t\u0103u Clojure.<\/li>\n<li>Configurare de performan\u021b\u0103 mai simpl\u0103. Trebuie s\u0103 construie\u0219ti un plan pentru o interogare problematic\u0103? Nu este o problem\u0103 atunci c\u00e2nd interogarea ta este pur \u0219i simplu SQL obi\u0219nuit.<\/li>\n<li>Reutilizarea interog\u0103rilor. Po\u021bi s\u0103 tragi acelea\u0219i fi\u0219iere SQL \u00een alte proiecte, deoarece este doar vechiul SQL \u2014 pur \u0219i simplu \u00eemp\u0103rt\u0103\u0219e\u0219te-l.<\/li>\n<\/ul>\n<p>\n<\/p><\/blockquote>\n<p>Din punctul meu de vedere, ideea este foarte grozav\u0103 \u0219i \u00een acela\u0219i timp extrem de simpl\u0103, ceea ce a dus la ob\u021binerea multor <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/krisajenkins\/yesql#other-languages\">urm\u0103ritorilor<\/a><\/noindex> \u00een diverse limbi. Aici vom \u00eencerca s\u0103 aplic\u0103m o filosofie similar\u0103 de separare a codului SQL de restul, mult dincolo de ORM.<\/p>\n<p><\/p>\n<h5 id=\"ide--db-menedzhery\">IDE &amp; DB-gestionari<\/h5>\n<p><\/p>\n<p>S\u0103 \u00eencepem cu o sarcin\u0103 zilnic\u0103 simpl\u0103. Adesea, trebuie s\u0103 c\u0103ut\u0103m obiecte \u00een baza de date, de exemplu, s\u0103 g\u0103sim o tabel\u0103 \u00een schem\u0103 \u0219i s\u0103 studiem structura acesteia (ce coloane, chei, indici, constr\u00e2ngeri etc. sunt folosite). De la orice IDE grafic sau un minim de DB-manager, ne a\u0219tept\u0103m \u00een primul r\u00e2nd la aceste capabilit\u0103\u021bi. S\u0103 fie rapid \u0219i s\u0103 nu fie nevoie s\u0103 a\u0219tept\u0103m zeci de minute p\u00e2n\u0103 c\u00e2nd se deschide fereastra cu informa\u021biile necesare (mai ales cu o conexiune lent\u0103 la baza de date la distan\u021b\u0103), \u0219i \u00een acela\u0219i timp, s\u0103 fie informa\u021biile proaspete \u0219i actuale, nu doar un vechi cache. Cu c\u00e2t baza de date este mai complex\u0103 \u0219i mai mare, iar num\u0103rul acestora este mai mare, cu at\u00e2t este mai greu de realizat acest lucru.<\/p>\n<p><\/p>\n<p>Dar de obicei \u00eemi pun deoparte mouse-ul \u0219i pur \u0219i simplu scriu cod. S\u0103 presupunem c\u0103 trebuie s\u0103 aflu ce tabele (\u0219i cu ce propriet\u0103\u021bi) sunt \u00een schem\u0103 &quot;HR&quot;. \u00cen majoritatea SGBD-urilor, rezultatul dorit poate fi ob\u021binut cu aceast\u0103 interogare simpl\u0103 din information_schema:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select table_name\n     , ...\n  from information_schema.tables\n where schema = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>De la o baz\u0103 de date la alta, con\u021binutul acestor mese de referin\u021b\u0103 variaz\u0103 \u00een func\u021bie de capabilit\u0103\u021bile fiec\u0103rei SGBD. \u0218i, de exemplu, pentru MySQL, din acela\u0219i director, se pot ob\u021bine parametrii specifici pentru aceast\u0103 SGBD:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select table_name\n     , storage_engine -- Motorul folosit (&quot;MyISAM&quot;, &quot;InnoDB&quot; etc)\n     , row_format     -- Formatul r\u00e2ndului (&quot;Fixed&quot;, &quot;Dynamic&quot; etc)\n     , ...\n  from information_schema.tables\n where schema = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Oracle nu are information_schema, dar are <noindex><a rel=\"nofollow\" href=\"https:\/\/en.wikipedia.org\/wiki\/Oracle_metadata\">metadata Oracle<\/a><\/noindex>, \u0219i nu apar mari probleme:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select table_name\n     , pct_free       -- Minim de spa\u021biu liber \u00een blocul de date (%)\n     , pct_used       -- Minim de spa\u021biu utilizat \u00een blocul de date (%)\n     , last_analyzed  -- Data ultimei analiz\u0103ri a statisticilor\n     , ...\n  from all_tables\n where owner = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Nu face excep\u021bie nici ClickHouse:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select name\n     , engine -- Motorul folosit (&quot;MergeTree&quot;, &quot;Dictionary&quot; etc)\n     , ...\n  from system.tables\n where database = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>O interogare similar\u0103 poate fi realizat\u0103 \u0219i \u00een Cassandra (unde exist\u0103 columnfamilies \u00een loc de tabele \u0219i keyspace-uri \u00een loc de scheme):<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select columnfamily_name\n     , compaction_strategy_class  -- Strategia de colectare a de\u0219eurilor\n     , gc_grace_seconds           -- Timpul de via\u021b\u0103 al de\u0219eurilor\n     , ...\n  from system.schema_columnfamilies\n where keyspace_name = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Pentru majoritatea celorlalte baze de date, se pot inventa interog\u0103ri similare (chiar \u0219i \u00een Mongo exist\u0103 <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.mongodb.com\/manual\/reference\/system-collections\/#%3Cdatabase%3E.system.namespaces\">o colec\u021bie sistemic\u0103 special\u0103<\/a><\/noindex>, care con\u021bine informa\u021bii despre toate colec\u021biile din sistem).<\/p>\n<p><\/p>\n<p>Desigur, \u00een acest fel putem ob\u021bine informa\u021bii nu doar despre tabele, ci despre orice obiect. Din c\u00e2nd \u00een c\u00e2nd, oameni de bine \u00eemp\u0103rt\u0103\u0219esc astfel de cod pentru diferite baze de date, a\u0219a cum ar fi \u00een seria de articole de pe Habra &quot;Func\u021bii pentru documentarea bazelor de date PostgreSQL&quot; (<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/415575\">aib<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/415897\">ben<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/418597\">gim<\/a><\/noindex>). Desigur, a \u021bine toat\u0103 aceast\u0103 mare de interog\u0103ri \u00een minte \u0219i a le tasta constant - este un &quot;pl\u0103cere&quot; deosebit\u0103, a\u0219a c\u0103, \u00een IDE-ul\/editorul meu preferat, am un set preg\u0103tit de snippet-uri pentru interog\u0103rile frecvent folosite, \u0219i r\u0103m\u00e2ne doar s\u0103 introduc numele obiectelor \u00een \u0219ablon.<\/p>\n<p><\/p>\n<p>\u00cen final, acest mod de navigare \u0219i c\u0103utare a obiectelor este mult mai flexibil, economise\u0219te mult timp \u0219i permite ob\u021binerea exact informa\u021biilor necesare \u00een formatul dorit (a\u0219a cum este descris \u00een postarea <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/company\/JetBrains\/blog\/342094\">&quot;Exportul datelor din baza de date \u00een orice format: ce pot face IDE-urile de pe platforma IntelliJ&quot;<\/a><\/noindex>).<\/p>\n<p><\/p>\n<h5 id=\"operacii-s-obektami\">Opera\u021bii cu obiecte<\/h5>\n<p><\/p>\n<p>Dup\u0103 ce am g\u0103sit \u0219i studiat obiectele necesare, este momentul s\u0103 facem ceva util cu ele. Desigur, f\u0103r\u0103 a ne desprinde de tastatur\u0103.<\/p>\n<p><\/p>\n<p>Nu este un secret c\u0103 simpla \u0219tergere a unei tabele va ar\u0103ta aproape identic \u00een toate bazele de date:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">drop table hr.persons<\/code><\/pre>\n<p><\/p>\n<p>Dar crearea unei tabele este deja mai interesant\u0103. Practic orice SGBD (inclusiv multe NoSQL) poate efectua \u00een diverse forme opera\u021biunea de &quot;create table&quot;, iar partea sa principal\u0103 va diferi pu\u021bin (numele, lista coloanelor, tipurile de date), dar celelalte detalii pot varia semnificativ \u0219i depind de structura intern\u0103 \u0219i capacit\u0103\u021bile fiec\u0103rui SGBD \u00een parte. Exemplul meu preferat \u2014 \u00een documenta\u021bia Oracle, se g\u0103sesc doar c\u00e2teva &quot;BNF-uri goale&quot; pentru sintaxa &quot;create table&quot; <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/sql-language-reference.pdf\">ocup\u0103 31 de pagini<\/a><\/noindex>. Alte SGBD au capacit\u0103\u021bi mai modeste, dar fiecare dintre ele are, de asemenea, o mul\u021bime de caracteristici interesante \u0219i unice pentru crearea tabelelor (<noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/sql-createtable.html\">postgres<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/create-table.html\">mysql<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.cockroachlabs.com\/docs\/stable\/create-table.html#expanded\">cockroach<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.datastax.com\/en\/cql\/3.3\/cql\/cql_reference\/cqlCreateTable.html\">cassandra<\/a><\/noindex>). Este pu\u021bin probabil ca vreo &quot;asistent\u0103&quot; grafic\u0103 dintr-o IDE (mai ales una universal\u0103) s\u0103 acopere complet toate aceste func\u021bionalit\u0103\u021bi, iar dac\u0103 o va face, va fi o priveli\u0219te nu pentru cei slabi de inim\u0103. \u00cen acela\u0219i timp, un operator scris corect \u0219i la momentul potrivit <strong><em>create table<\/em><\/strong> va permite s\u0103 profita\u021bi u\u0219or de toate acestea, f\u0103c\u00e2nd stocarea \u0219i accesul la datele dumneavoastr\u0103 fiabile, optime \u0219i c\u00e2t mai pl\u0103cute posibil.<\/p>\n<p><\/p>\n<p>De asemenea, \u00een multe SGBD exist\u0103 tipuri specifice de obiecte care lipsesc \u00een alte SGBD. De fapt, putem efectua opera\u021biuni nu doar asupra obiectelor bazei de date, ci \u0219i asupra SGBD-ului \u00eensu\u0219i, de exemplu, s\u0103 &quot;omor\u00e2m&quot; un proces, s\u0103 eliber\u0103m o anumit\u0103 zon\u0103 de memorie, s\u0103 activ\u0103m trasarea, s\u0103 trecem \u00een modul &quot;read only&quot; \u0219i multe altele.<\/p>\n<p><\/p>\n<h5 id=\"a-teper-nemnogo-porisuem\">Acum hai s\u0103 desen\u0103m pu\u021bin.<\/h5>\n<p><\/p>\n<p>Una dintre cele mai frecvente sarcini este s\u0103 construim un diagram\u0103 cu obiectele bazei de date, s\u0103 vedem pe o imagine frumoas\u0103 obiectele \u0219i rela\u021biile dintre ele. Practic orice IDE grafic\u0103, utilitare separate de tipul \u201ecommand line\u201d, instrumente \u0219i modelatoare grafice specializate pot face acest lucru. Acestea v\u0103 vor desena ceva &quot;cum \u0219tiu&quot;, iar influen\u021barea acestui proces se poate face doar cu ajutorul c\u00e2torva parametrii \u00een fi\u0219ierul de configurare sau bifelor din interfa\u021b\u0103.<\/p>\n<p><\/p>\n<p>\u00cens\u0103 aceast\u0103 problem\u0103 poate fi rezolvat\u0103 mult mai simplu, mai flexibil \u0219i mai elegant, \u0219i desigur, cu ajutorul codului. Pentru construirea diagramelor de orice complexitate, avem la dispozi\u021bie mai multe limbaje de marcare specializate (DOT, GraphML etc), \u0219i o mul\u021bime de aplica\u021bii (GraphViz, PlantUML, Mermaid) care pot s\u0103 citeasc\u0103 astfel de instruc\u021biuni \u0219i s\u0103 vizualizeze \u00een cele mai variate formate. Iar informa\u021biile despre obiecte \u0219i rela\u021biile dintre ele le \u0219tim deja cum s\u0103 le ob\u021binem.<\/p>\n<p><\/p>\n<p>S\u0103 d\u0103m un mic exemplu despre cum ar putea ar\u0103ta, folosind PlantUML \u0219i <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/postgrespro\/blog\/316428\/\">o baz\u0103 de date demonstrativ\u0103 pentru PostgreSQL.<\/a><\/noindex> (\u00een st\u00e2nga interogarea SQL care va genera instruc\u021biunea necesar\u0103 pentru PlantUML, iar \u00een dreapta rezultatul):<\/p>\n<p>\n<img decoding=\"async\" alt=\"Experien\u021ba \u201eDatabase as Code\u201d\" src=\"\/wp-content\/uploads\/2020\/06\/c5cb2a138df1527abe47cbf8d695cb36.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"sql\">select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all\nselect distinct ccu.table_name || ' --|&gt; ' ||\n       tc.table_name as val\n  from table_constraints as tc\n  join key_column_usage as kcu\n    on tc.constraint_name = kcu.constraint_name\n  join constraint_column_usage as ccu\n    on ccu.constraint_name = tc.constraint_name\n where tc.constraint_type = 'FOREIGN KEY'\n   and tc.table_name ~ '.*' union all\nselect '@enduml'<\/code><\/pre>\n<p><\/p>\n<p>\u0218i dac\u0103 te str\u0103duie\u0219ti pu\u021bin, pe baza <noindex><a rel=\"nofollow\" href=\"https:\/\/gist.github.com\/QuantumGhost\/0955a45383a0b6c0bc24f9654b3cb561\">\u0219ablonului ER pentru PlantUML<\/a><\/noindex> po\u021bi ob\u021bine ceva foarte asem\u0103n\u0103tor cu o diagram\u0103 ER real\u0103:<\/p>\n<p><\/p>\n<p>                        <b class=\"spoiler_title\">Interogarea SQL e pu\u021bin mai complex\u0103.<\/b><\/p>\n<pre><code class=\"sql\">-- Antet\nselect &#039;@startuml\n        !define Table(name,desc) class name as &quot;desc&quot; &lt;&lt; (T,#FFAAAA) &gt;&amp;gt;\n        !define primary_key(x) &lt;b&gt;x&lt;\/b&gt;\n        !define unique(x) &lt;color:green&gt;x&lt;\/color&gt;\n        !define not_null(x) &lt;u&gt;x&lt;\/u&gt;\n        hide methods\n        hide stereotypes&#039;\n union all\n-- Tabele\nselect format(&#039;Table(%s, &quot;%s n information about %s&quot;) {&#039;||chr(10), table_name, table_name, table_name) ||\n       (select string_agg(column_name || &#039; &#039; || upper(udt_name), chr(10))\n          from information_schema.columns\n         where table_schema = &#039;public&#039;\n           and table_name = t.table_name) || chr(10) || &#039;}&#039;\n  from information_schema.tables t\n where table_schema = &#039;public&#039;\n union all\n-- Rela\u021bii &icirc;ntre tabele\nselect distinct ccu.table_name || &#039; &quot;1&quot; --&amp;gt; &quot;0..N&quot; &#039; || tc.table_name || format(&#039; : &quot;A %s may haven many %s&quot;&#039;, ccu.table_name, tc.table_name)\n  from information_schema.table_constraints as tc\n  join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name\n  join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name\n where tc.constraint_type = &#039;FOREIGN KEY&#039;\n   and ccu.constraint_schema = &#039;public&#039;\n   and tc.table_name ~ &#039;.*&#039;\n union all\n-- Subsol\nselect &#039;@enduml&#039;<\/code><\/pre>\n<p>\n<img decoding=\"async\" alt=\"Experien\u021ba \u201eDatabase as Code\u201d\" src=\"\/wp-content\/uploads\/2020\/06\/bd45420a4fd1bdfcf20cbe74a0638c7b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>Dac\u0103 te ui\u021bi atent, multe dintre uneltele de vizualizare folosesc la fel de bine interog\u0103ri asem\u0103n\u0103toare sub capot\u0103. Totu\u0219i, aceste interog\u0103ri sunt de obicei ad\u00e2nc <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/pgmodeler\/pgmodeler\/blob\/9c615c0b0871df3cd649983ce61e802b0ff0137b\/schemas\/catalog\/table.sch\">&quot;\u00eenglobate&quot; \u00een codul aplica\u021biei \u00een sine \u0219i sunt complexe pentru a fi \u00een\u021belese<\/a><\/noindex>, dar\u0103mite modificarea lor.<\/p>\n<p><\/p>\n<h5 id=\"metriki-i-monitoring\">Metrii \u0219i monitorizare.<\/h5>\n<p><\/p>\n<p>S\u0103 trecem la o tem\u0103 tradi\u021bional complicat\u0103 - monitorizarea performan\u021bei Bazei de Date. \u00cemi amintesc o mic\u0103 poveste adev\u0103rat\u0103, spus\u0103 de \"unul dintre prietenii mei\". \u00centr-un proiect, exista un DBA puternic, iar pu\u021bini dintre dezvoltatori l-au cunoscut personal, ba chiar l-au v\u0103zut vreodat\u0103 fa\u021b\u0103 \u00een fa\u021b\u0103 (de\u0219i se spunea c\u0103 lucra undeva \u00een cl\u0103direa al\u0103turat\u0103). La ora \"X\", c\u00e2nd sistemul de produc\u021bie al unui retailer mare \u00eencepea s\u0103 \"se simt\u0103 r\u0103u\" din nou, el trimitea \u00een t\u0103cere capturi de ecran cu grafice din Oracle Enterprise Manager, pe care eviden\u021bia critic zonele cu un marker ro\u0219u pentru \"claritate\" (ceea ce, ca s\u0103 fim bl\u00e2nzi, ajuta foarte pu\u021bin). A\u0219a c\u0103, pe baza acelei \"fotografii\", trebuia s\u0103 trat\u0103m problema. \u00cen plus, nimeni nu avea acces la pre\u021biosul (\u00een ambele sensuri ale cuv\u00e2ntului) Enterprise Manager, deoarece sistemul era complicat \u0219i costisitor, de team\u0103 c\u0103 \"dezvoltatorii ar putea da peste ceva \u0219i totul s-ar strica\". A\u0219adar, dezvoltatorii g\u0103seau prin metode \"empirice\" locul \u0219i cauza \u00eent\u00e2rzierilor \u0219i lansau un patch. Dac\u0103 o scrisoare \u00eenfrico\u0219\u0103toare de la DBA nu mai venea \u00een scurt timp, to\u021bi respirau u\u0219ura\u021bi \u0219i se \u00eentorceau la sarcinile lor curente (p\u00e2n\u0103 la noua Scrisoare).<\/p>\n<p><\/p>\n<p>Dar procesul de monitorizare poate ar\u0103ta mult mai vesel \u0219i prietenos, iar cel mai important - accesibil \u0219i transparent pentru to\u021bi. M\u0103car partea sa de baz\u0103, ca o completare la sistemele principale de monitorizare (care sunt f\u0103r\u0103 \u00eendoial\u0103 utile \u0219i \u00een multe cazuri indispensabile). Orice SGBD este dispus s\u0103 \u00eemp\u0103rt\u0103\u0219easc\u0103 liber \u0219i f\u0103r\u0103 plat\u0103 informa\u021bii despre starea sa actual\u0103 \u0219i performan\u021ba sa. \u00cen aceea\u0219i \"s\u00e2ngeroas\u0103\" baz\u0103 de date Oracle, aproape orice informa\u021bie despre performan\u021b\u0103 poate fi ob\u021binut\u0103 din vederi sistemice, \u00eencep\u00e2nd de la procese \u0219i sesiuni \u0219i termin\u00e2nd cu starea memoriei tampon (de exemplu, <noindex><a rel=\"nofollow\" href=\"https:\/\/oracle-base.com\/dba\/scripts\">Scripturi DBA<\/a><\/noindex>, sec\u021biunea \"Monitoring\"). \u00cen PostgreSQL exist\u0103, de asemenea, o mul\u021bime de vederi sistemice pentru <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html\">monitorizarea activit\u0103\u021bii Bazei de Date<\/a><\/noindex>, \u00een special unele indispensabile \u00een via\u021ba de zi cu zi a oric\u0103rui DBA, cum ar fi <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW\">pg_stat_activity<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html#PG-STAT-DATABASE-VIEW\">pg_stat_database<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html#PG-STAT-BGWRITER-VIEW\">pg_stat_bgwriter<\/a><\/noindex>. \u00cen MySQL, pentru acest scop, exist\u0103 chiar o schem\u0103 separat\u0103 <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/performance-schema-table-descriptions.html\">performance_schema<\/a><\/noindex>. Iar \u00een Mongo, exist\u0103 un <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.mongodb.com\/manual\/tutorial\/manage-the-database-profiler\/\">profiler<\/a><\/noindex> care agreg\u0103 datele de performan\u021b\u0103 \u00eentr-o colec\u021bie sistemic\u0103 <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.mongodb.com\/manual\/tutorial\/manage-the-database-profiler\/#view-profiler-data\">system.profile<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>A\u0219adar, echip\u00e2ndu-ne cu un colector de metrici (Telegraf, Metricbeat, Collectd), care poate efectua interog\u0103ri SQL personalizate, un depozit pentru aceste metrici (InfluxDB, Elasticsearch, Timescaledb) \u0219i un vizualizator (Grafana, Kibana), putem ob\u021bine un sistem de monitorizare destul de u\u0219or \u0219i flexibil, care va fi str\u00e2ns integrat cu alte metrici sistemice generale (ob\u021binute, de exemplu, de la serverul aplica\u021biilor, de la OS \u0219i altele). De exemplu, a\u0219a cum este implementat \u00een pgwatch2, unde se utilizeaz\u0103 combina\u021bia InfluxDB + Grafana \u0219i un set de interog\u0103ri la vizualiz\u0103rile sistemului, la care se pot ad\u0103uga <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/cybertec-postgresql\/pgwatch2#adding-metrics\">interog\u0103ri personalizate<\/a><\/noindex>.<\/p>\n<p><\/p>\n<h2 id=\"itogo\">\u00cen concluzie<\/h2>\n<p><\/p>\n<p>\u0218i aceasta este doar o list\u0103 aproximativ\u0103 a ceea ce se poate face cu baza noastr\u0103 de date prin intermediul codului SQL obi\u0219nuit. Sunt sigur c\u0103 pot exista multe alte aplica\u021bii, scrie\u021bi \u00een comentarii. Iar despre cum (\u0219i, cel mai important, de ce) s\u0103 automatiz\u0103m totul \u0219i s\u0103-l integr\u0103m \u00een pipeline-ul nostru CI\/CD, vom vorbi data viitoare.<\/p>\n<p>Sursa: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/426833\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435? \u041a\u0430\u0436\u0434\u044b\u0439 \u0438\u0437 \u043d\u0430\u0441 \u043c\u043e\u0436\u0435\u0442 \u043d\u0430\u043f\u0438\u0441\u0430\u0442\u044c \u043f\u0440\u043e\u0441\u0442\u0435\u043d\u044c\u043a\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u2014 \u043d\u0430\u0431\u0438\u0440\u0430\u0435\u043c select, \u043f\u0435\u0440\u0435\u0447\u0438\u0441\u043b\u044f\u0435\u043c \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u044b\u0435 \u043a\u043e\u043b\u043e\u043d\u043a\u0438, \u0437\u0430\u0442\u0435\u043c from, \u0438\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u0443\u0441\u043b\u043e\u0432\u0438\u0439 \u0432 where \u0438 \u0432\u0441\u0435 \u2014 \u043f\u043e\u043b\u0435\u0437\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u0443 \u043d\u0430\u0441 \u0432 \u043a\u0430\u0440\u043c\u0430\u043d\u0435, \u043f\u0440\u0438\u0447\u0435\u043c (\u043f\u043e\u0447\u0442\u0438) \u043d\u0435\u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e \u043e\u0442 \u0442\u043e\u0433\u043e \u043a\u0430\u043a\u0430\u044f \u0421\u0423\u0411\u0414 \u0432 \u044d\u0442\u043e \u0432\u0440\u0435\u043c\u044f \u043d\u0430\u0445\u043e\u0434\u0438\u0442\u0441\u044f \u043f\u043e\u0434 \u043a\u0430\u043f\u043e\u0442\u043e\u043c (\u0430 \u043c\u043e\u0436\u0435\u0442 \u0438 \u043d\u0435 \u0421\u0423\u0411\u0414 \u0432\u043e\u0432\u0441\u0435). \u0412 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":83650,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-83649","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.5.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435?\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/database-as-sode-experience\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"ro_RO\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u00abDatabase as \u0421ode\u00bb Experience | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435?\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/database-as-sode-experience\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-06-02T05:42:21+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-06-02T05:42:21+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47\u00abDatabase as Code\u00bb Experience | ProHoster","description":"SQL, ce ar putea fi mai simplu?","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/database-as-sode-experience","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u00abDatabase as \u0421ode\u00bb Experience | ProHoster","og:description":"SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435?","og:url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/database-as-sode-experience","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-06-02T05:42:21+00:00","article:modified_time":"2020-06-02T05:42:21+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"83649","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 15:13:28","updated":"2022-10-02 10:23:46","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/83649","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=83649"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/83649\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/83650"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=83649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=83649"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=83649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}