Cholinga cha nkhaniyi ndi kugwiritsa ntchito chitsanzo cha laibulale schema-wosunga onetsani zida zomwe zitha kufewetsa kwambiri njira yopangira nkhokwe mkati mwa mapulojekiti a PHP pogwiritsa ntchito PostgreSQL DBMS.

Zomwe zili m'nkhaniyi, choyamba, zidzakhala zothandiza kwa omanga omwe akufuna kugwiritsa ntchito luso la PostgreSQL, koma akukumana ndi mavuto osunga malingaliro abizinesi oyikidwa mu database.

Nkhaniyi sifotokoza ubwino kapena kuipa kosunga mfundo zabizinesi munkhokwe. Zimaganiziridwa kuti chisankhocho chapangidwa kale ndi wowerenga.

Mafunso otsatirawa adzayankhidwa:

1. Kodi dambo la dawunilodi liyenera kusungidwa munjira yotani (yomwe imatchedwa VCS)
2. Momwe mungayang'anire kusintha kwadongosolo la database mukasunga zotayira
3. Momwe mungasamutsire zosintha zadongosolo la database kupita kumadera ena popanda mikangano ndi mafayilo akulu osamuka
4. Momwe mungapangire ntchito yofananira pa projekiti ndi opanga angapo
5. Momwe mungatumizire motetezeka kusintha kochulukira mudongosolo la database kumalo opangira
   

   SchemaKeeper opangidwa kuti azigwira ntchito ndi njira zosungidwa zolembedwa m'chinenerocho PL/pgSQL. Kuyesa ndi zilankhulo zina sikunachitike, chifukwa chake kugwiritsa ntchito sikungakhale kothandiza kapena kosatheka.

Momwe mungasungire dambo la database mu VCS

Library schema-wosunga imapereka ntchito saveDump, yomwe imasunga mawonekedwe a zinthu zonse kuchokera ku database ngati mafayilo osiyana. Zomwe zimatuluka ndi chikwatu chomwe chili ndi database, yogawidwa m'magulu omwe amatha kuwonjezeredwa ku VCS.

Tiyeni tiwone kusintha zinthu kuchokera ku database kukhala mafayilo pogwiritsa ntchito zitsanzo zingapo:

Mtundu wa chinthu
Chiwembu
Mutu
Njira yofananira ndi fayilo

tebulo
boma
nkhani
./public/tables/accounts.txt

Ndondomeko yosungidwa
boma
auth (hash bigant)
./public/functions/auth(int8).sql

Mau oyamba
kusungitsa
malonda
./booking/views/tariffs.txt

Zomwe zili m'mafayilo ndizomwe zimayimira zolemba zachinthu chachinsinsi. Mwachitsanzo, pamachitidwe osungidwa, zomwe zili mufayilo zidzakhala tanthauzo lonse la njira yosungidwa, kuyambira ndi block. CREATE OR REPLACE FUNCTION.

Monga momwe tawonera patebulo pamwambapa, njira yopita ku fayilo imasunga zambiri za mtundu, schema ndi dzina la chinthucho. Njirayi imapangitsa kuti zikhale zosavuta kuyendayenda potaya ndi kubwereza ndondomeko za kusintha kwa database.

kutambasuka .sql Kwa mafayilo omwe ali ndi code source source code, izi zidasankhidwa kuti IDE ipereke zida zolumikizirana ndi nkhokwe fayilo ikatsegulidwa.

Momwe mungayang'anire kusintha kwadongosolo la database mukasunga zotayira

Posunga kutayidwa kwazomwe zilipo mu VCS, timapeza mwayi wowona ngati zosintha zidachitika pakutayako zitapangidwa. Mu library schema-wosunga kuti muwone kusintha kwadongosolo la database, ntchito imaperekedwa verifyDump, yomwe imabweza zambiri zokhudzana ndi kusiyana popanda zotsatirapo.

Njira ina yowonera ndikuyimbiranso ntchitoyi saveDump, kutchula chikwatu chomwechi, ndikuyang'ana VCS kuti musinthe. Popeza kuti zinthu zonse zochokera ku database zimasungidwa m'mafayilo osiyana, VCS imangowonetsa zinthu zosinthidwa.
Choyipa chachikulu cha njirayi ndikufunika kolemba mafayilo kuti muwone kusintha.

Momwe mungasamutsire zosintha zadongosolo la database kupita kumadera ena popanda mikangano ndi mafayilo akulu osamuka

Chifukwa cha ntchito deployDump Khodi yochokera kwa njira zosungidwa ikhoza kusinthidwa chimodzimodzi ndi code yoyambira yogwiritsira ntchito. Mutha kuwonjezera / kufufuta mizere yatsopano pamakina osungidwa ndikukankhira zosintha nthawi yomweyo kuwongolera mtundu, kapena kupanga / kufufuta njira zosungidwa popanga / kufufuta mafayilo ofananirako mu bukhu lotaya.

Mwachitsanzo, kupanga njira yatsopano yosungidwa mu schema public ingopangani fayilo yatsopano ndikuwonjezera .sql mu directory public/functions, ikani magwero a ndondomeko yosungidwa mmenemo, kuphatikizapo chipika CREATE OR REPLACE FUNCTION, ndiye imbani ntchito deployDump. Kusintha ndi kuchotsa ndondomeko yosungidwa kumachitika chimodzimodzi. Chifukwa chake, code imapita ku VCS ndi database nthawi imodzi.

Ngati cholakwika chikuwoneka mu code source ya njira iliyonse yosungidwa, kapena kusiyana pakati pa mayina a fayilo ndi ndondomeko yosungidwa, ndiye deployDump idzalephera, kusonyeza malemba olakwika. Kusagwirizana kwa njira zosungidwa pakati pa kutaya ndi nkhokwe yaposachedwa sikutheka mukamagwiritsa ntchito deployDump.

Mukapanga njira yatsopano yosungidwa, palibe chifukwa cholowetsa dzina lolondola la fayilo. Ndikokwanira kuti fayilo ikhale ndi zowonjezera .sql. Atatha kuyimba deployDump zolakwikazo zidzakhala ndi dzina lolondola, lomwe lingagwiritsidwe ntchito kutchulanso fayilo.

deployDump amakulolani kusintha magawo a ntchito kapena mtundu wobwerera popanda zochita zina, pomwe ndi njira yachikale muyenera kutero.
kuchitira poyamba DROP FUNCTION, ndipo kenako CREATE OR REPLACE FUNCTION.

Tsoka ilo, pali zochitika zina pomwe deployDump simungathe kuyika zosintha zokha. Mwachitsanzo, ngati choyambitsa ntchito chomwe chimagwiritsidwa ntchito ndi choyambitsa chimodzi chachotsedwa. Zinthu zoterezi zimathetsedwa pamanja pogwiritsa ntchito mafayilo osamuka.

Ngati muli ndi udindo wosamutsira zosintha kumachitidwe osungidwa schema-wosunga, ndiye kuti mafayilo osamukira ayenera kugwiritsidwa ntchito kusamutsa zosintha zina mu kapangidwe kake. Mwachitsanzo, laibulale yabwino ntchito ndi kusamuka ndi chiphunzitso/kusamuka.

Kusamuka kuyenera kukhazikitsidwa musanayambike deployDump. Izi zimakupatsani mwayi wosintha zonse pamapangidwewo ndikuthetsa zovuta kuti zosintha zamachitidwe osungidwa zimasamutsidwe popanda mavuto.

Kugwira ntchito ndi kusamuka kudzafotokozedwa mwatsatanetsatane m'magawo otsatirawa.

Momwe mungapangire ntchito yofananira pa projekiti ndi opanga angapo

Ndikofunikira kupanga script kuti akhazikitse nkhokwe, yomwe idzayambitsidwe ndi wopanga pamakina ake ogwirira ntchito, ndikubweretsa mawonekedwe a database yakomweko molingana ndi kutaya kosungidwa mu VCS. Njira yosavuta ndikugawanitsa kuyambika kwa database yakomweko m'masitepe atatu:

1. Lowetsani fayilo yokhala ndi dongosolo lofunikira lomwe lidzatchedwa mwachitsanzo. base.sql
2. Kugwiritsa Ntchito Zosamuka
3. Chovuta deployDump

base.sql ndiye poyambira pamwamba pomwe kusamuka kumagwiritsidwa ntchito ndikuchitidwa deployDump, ndiko base.sql + миграции + deployDump = актуальная структура БД. Mutha kupanga fayilo yotere pogwiritsa ntchito zofunikira pg_dump. Zogwiritsidwa ntchito base.sql pokhapokha poyambitsa nkhokwe kuyambira pachiyambi.

Tiyeni tiyimbe script kuti tiyambitse zonse za database refresh.sh. Njira ya ntchito ikhoza kuwoneka motere:

1. Wopangayo akuyamba mu chilengedwe chake refresh.sh ndikupeza mawonekedwe a database
2. Wopanga mapulogalamu akuyamba kugwira ntchito yomwe ali nayo, kusintha nkhokwe yakumaloko kuti akwaniritse zosowa za magwiridwe antchito atsopano (ALTER TABLE ... ADD COLUMN ndi zina)
3. Pambuyo pomaliza ntchitoyo, wopanga amayitana ntchitoyi saveDumpkupanga zosintha zomwe zidapangidwa ku database ya VCS
4. Kukhazikitsanso kwa Madivelopa refresh.shndiye verifyDumpzomwe tsopano zikuwonetsa mndandanda wa zosintha kuti ziphatikizidwe pakusamuka
5. Wopanga mapulogalamu amasamutsa zosintha zonse ku fayilo yosamuka, imayambiranso refresh.sh и verifyDump, ndipo, ngati kusamukako kusamuka bwino, verifyDump sichidzawonetsa kusiyana pakati pa nkhokwe yapafupi ndi malo osungidwa osungidwa

Njira yomwe tafotokozayi ikugwirizana ndi mfundo za gitflow. Nthambi iliyonse mu VCS idzakhala ndi mtundu wake wa kutaya, ndipo pophatikiza nthambi, zotayirazo zidzaphatikizidwa. Nthawi zambiri, palibe chowonjezera chomwe chiyenera kuchitidwa pambuyo pa kuphatikizika, koma ngati kusintha kunapangidwa mu nthambi zosiyana, mwachitsanzo, pa tebulo lomwelo, mkangano ukhoza kuwuka.

Tiyeni tiganizire za kusamvana pogwiritsa ntchito chitsanzo: pali nthambi kukhala, kuchokera ku nthambi ziwiri: mawonekedwe1 и mawonekedwe2, zomwe zilibe zotsutsana nazo kukhala, koma kukangana wina ndi mzake. Ntchito ndikuphatikiza nthambi zonse ziwiri kukhala. Pachifukwa ichi, tikulimbikitsidwa kuti muyambe kuphatikiza imodzi mwa nthambizo kukhalandiyeno phatikizani kukhala ku nthambi yotsalayo, kuthetsa mikangano mu nthambi yotsalayo, ndiyeno kuphatikiza nthambi yomaliza kukhala kukhala. Panthawi yothetsa mikangano, mungafunike kukonza fayilo yosamukira munthambi yomaliza kuti igwirizane ndi kutaya komaliza, komwe kumaphatikizapo zotsatira za kuphatikiza.

Momwe mungatumizire motetezeka kusintha kochulukira mudongosolo la database kumalo opangira

Chifukwa cha kupezeka kwa kutayidwa kwa nkhokwe yamakono mu VCS, zimakhala zotheka kuyang'ana nkhokwe zopangira kuti zitsatidwe ndendende ndi zomwe zimafunikira. Izi zimawonetsetsa kuti zosintha zonse zomwe opanga adafuna zidasamutsidwa bwino kumalo opangira.

chifukwa DDL mu PostgreSQL ndi zamalonda, tikulimbikitsidwa kutsatira dongosolo lotsatirali, kuti, ngati cholakwika chosayembekezereka, mutha kuchita "mopanda ululu" ROLLBACK:

1. Yambani kuchitapo kanthu
2. Limbikitsani kusamuka konse pakugulitsa
3. Muzochitika zomwezo, gwirani deployDump
4. Popanda kumaliza ntchitoyo, yambitsani verifyDump. Ngati palibe zolakwika, thamangani COMMIT. Ngati pali zolakwika, thamangani ROLLBACK

Masitepewa amatha kuphatikizidwa mosavuta kunjira zomwe zilipo kale pakuyika ntchito, kuphatikiza zero-downtime.

Pomaliza

Chifukwa cha njira zomwe tafotokozazi, ndizotheka kufinya magwiridwe antchito kwambiri pama projekiti a "PHP + PostgreSQL", pomwe mukupereka mwayi wocheperako poyerekeza ndikugwiritsa ntchito malingaliro onse abizinesi mu code yofunsira. Komanso, data processing mu PL/pgSQL nthawi zambiri zimawoneka zowonekera kwambiri ndipo zimafunikira ma code ochepa kuposa momwe zimalembedwera mu PHP.

Source: www.habr.com