Inhloso yalesi sihloko ukusebenzisa isibonelo somtapo wolwazi umgcini we-schema bonisa amathuluzi angenza kube lula kakhulu inqubo yokuthuthukisa imininingwane yolwazi ngaphakathi kwamaphrojekthi we-PHP kusetshenziswa i-PostgreSQL DBMS.

Ulwazi oluvela kulesi sihloko, okokuqala, luzoba usizo kubathuthukisi abafuna ukusebenzisa ngokugcwele amakhono e-PostgreSQL, kodwa ababhekene nezinkinga zokulondoloza umqondo webhizinisi obekwe kusizindalwazi.

Lesi sihloko ngeke sichaze okuhle noma okubi kokugcina umqondo webhizinisi kusizindalwazi. Kucatshangwa ukuthi ukukhetha sekuvele kwenziwa ngumfundi.

Imibuzo elandelayo izocatshangelwa:

1. Ukulahlwa kwesakhiwo sesizindalwazi kufanele kugcinwe ngaluphi uhlobo kusistimu yokulawula inguqulo (ngemuva kwalokhu ebizwa ngokuthi i-VCS)
2. Uzilandelela kanjani izinguquko kusakhiwo sesizindalwazi ngemva kokulondoloza ukulahlwa
3. Uzidlulisela kanjani izinguquko kusakhiwo sesizindalwazi kwezinye izindawo ngaphandle kwezingxabano namafayela amakhulu okufuduka
4. Indlela yokuhlela inqubo yomsebenzi ofanayo kuphrojekthi ngabathuthukisi abaningana
5. Uzithumela kanjani ngokuphephile izinguquko ezengeziwe kusakhiwo sedathabheyisi endaweni yokukhiqiza
   

   I-SchemaKeeper yakhelwe ukusebenza ngezinqubo ezigciniwe ezibhalwe ngolimi PL/pgSQL. Ukuhlola ngezinye izilimi akuzange kwenziwe, ngakho-ke ukusetshenziswa kungase kungasebenzi kahle noma kungenzeki.

Ukugcina kanjani ukulahlwa kwesakhiwo sesizindalwazi ku-VCS

umtapo umgcini we-schema inikeza umsebenzi saveDump, elondoloza ukwakheka kwazo zonke izinto kusizindalwazi njengamafayela ombhalo ahlukene. Okuphumayo kuyinkomba equkethe isakhiwo sedathabheyisi, ehlukaniswe ngamafayela aqoqwe angangezwa kalula ku-VCS.

Ake sibheke ukuguqula izinto zisuka kusizindalwazi zibe amafayela sisebenzisa izibonelo ezimbalwa:

Uhlobo lwento
Lolu hlelo
Isihloko
Indlela ehlobene yefayela

Ithebula
umphakathi
Ama-akhawunti
./public/tables/accounts.txt

Inqubo egciniwe
umphakathi
i-auth (hash bigint)
./public/functions/auth(int8).sql

Isingeniso
yokubhuka
izindleko
./booking/views/tariffs.txt

Okuqukethwe kwamafayela kuwumfanekiso ongokombhalo wesakhiwo sento ethile yesizindalwazi. Isibonelo, ngezinqubo ezigciniwe, okuqukethwe kwefayela kuzoba incazelo egcwele yenqubo egciniwe, kuqala nge-block. CREATE OR REPLACE FUNCTION.

Njengoba kubonakala kuthebula elingenhla, indlela eya efayeleni igcina ulwazi mayelana nohlobo, i-schema negama lento. Le ndlela yenza kube lula ukuzulazula ekulahleni nasekubuyekezweni kwekhodi yezinguquko kusizindalwazi.

Isandiso .sql kumafayela anekhodi yomthombo wenqubo egciniwe, lokhu kukhethiwe ukuze i-IDE inikeze ngokuzenzakalelayo amathuluzi okusebenzelana nesizindalwazi lapho ifayela livulwa.

Uzilandelela kanjani izinguquko kusakhiwo sesizindalwazi ngemva kokulondoloza ukulahlwa

Ngokulondoloza ukulahlwa kwesakhiwo sesizindalwazi samanje ku-VCS, sithola ithuba lokuhlola ukuthi ingabe izinguquko zenziwe kusakhiwo sesizindalwazi ngemva kokudalwa kokulahlwa. Emtatsheni wezincwadi umgcini we-schema ukuthola izinguquko kusakhiwo sesizindalwazi, umsebenzi unikeziwe verifyDump, ebuyisela ulwazi mayelana nokwehluka ngaphandle kwemiphumela emibi.

Enye indlela yokuhlola ukushayela umsebenzi futhi saveDump, ecacisa uhla lwemibhalo olufanayo, bese uhlola i-VCS ukuze uthole izinguquko. Njengoba zonke izinto ezivela kusizindalwazi zigcinwa kumafayela ahlukene, i-VCS izobonisa kuphela izinto ezishintshile.
Ububi obukhulu bale ndlela yisidingo sokubhala phezu kwamafayela ukuze ubone izinguquko.

Uzidlulisela kanjani izinguquko kusakhiwo sesizindalwazi kwezinye izindawo ngaphandle kwezingxabano namafayela amakhulu okufuduka

Sibonga umsebenzi deployDump Ikhodi yomthombo yezinqubo ezigciniwe ingahlelwa ngendlela efanayo ncamashi nekhodi yomthombo wohlelo lokusebenza evamile. Ungangeza/ususe imigqa emisha kukhodi yenqubo egciniwe bese ucindezela ngokushesha izinguquko kusilawuli senguqulo, noma udale/ususe izinqubo ezigciniwe ngokudala/ukususa amafayela ahambisanayo ohlwini lwemibhalo lokulahla.

Isibonelo, ukudala inqubo entsha egciniwe ku-schema public vele udale ifayela elisha ngesandiso .sql ohlwini lwemibhalo public/functions, beka ikhodi yomthombo yenqubo egcinwe kuyo, kuhlanganise ne-block CREATE OR REPLACE FUNCTION, bese ushayela uhlelo deployDump. Ukushintsha nokususa inqubo egciniwe kwenzeka ngendlela efanayo. Ngakho-ke, ikhodi ingena kukho kokubili i-VCS kanye nesizindalwazi ngesikhathi esisodwa.

Uma iphutha livela kukhodi yomthombo yanoma iyiphi inqubo egciniwe, noma umehluko phakathi kwamagama efayela kanye nenqubo egciniwe, khona-ke deployDump izohluleka, ibonisa umbhalo wephutha. Ukungafani kwezinqubo ezigciniwe phakathi kokulahla kanye nesizindalwazi samanje akunakwenzeka uma usebenzisa deployDump.

Lapho udala inqubo entsha egciniwe, asikho isidingo sokufaka mathupha igama lefayela elilungile. Kwanele ukuthi ifayela libe nesandiso .sql. Ngemva kocingo deployDump umbhalo wephutha uzoqukatha igama elilungile, elingasetshenziswa ukuqamba kabusha ifayela.

deployDump ikuvumela ukuthi uguqule imingcele yomsebenzi noma uhlobo lokubuyisela ngaphandle kwezenzo ezengeziwe, kuyilapho ngendlela yakudala kuzodingeka
yenza kuqala DROP FUNCTION, futhi ngemva kwalokho CREATE OR REPLACE FUNCTION.

Ngeshwa, kukhona ezinye izimo lapho deployDump ayikwazi ukufaka izinguquko ngokuzenzakalelayo. Isibonelo, uma umsebenzi wokucupha osetshenziswa okungenani isibangeli esisodwa ususiwe. Izimo ezinjalo zixazululwa ngesandla kusetshenziswa amafayela okuthutha.

Uma unesibopho sokuthutha izinguquko ezinqubweni ezigciniwe umgcini we-schema, khona-ke amafayela okuthutha kufanele asetshenziselwe ukudlulisa ezinye izinguquko esakhiweni. Isibonelo, umtapo wolwazi omuhle wokusebenza ngokufuduka imfundiso/ukufuduka.

Ukufuduka kufanele kusetshenziswe ngaphambi kokwethulwa deployDump. Lokhu kukuvumela ukuthi wenze zonke izinguquko esakhiweni futhi uxazulule izimo eziyinkinga ukuze izinguquko ezinqubweni ezigciniwe zidluliselwe ngaphandle kwezinkinga.

Ukusebenza nabafuduki kuzochazwa kabanzi ezigabeni ezilandelayo.

Indlela yokuhlela inqubo yomsebenzi ofanayo kuphrojekthi ngabathuthukisi abaningana

Kudingekile ukwakha iskripthi sokuqalisa okuphelele kwe-database, ezokwethulwa umthuthukisi emshinini wakhe womsebenzi, eletha ukwakheka kwesizindalwazi sendawo ngokuhambisana nokulahlwa okulondolozwe ku-VCS. Indlela elula ukuhlukanisa ukuqaliswa kwesizindalwazi sendawo ngezinyathelo ezi-3:

1. Ngenisa ifayela elinesakhiwo esiyisisekelo esizobizwa ngokuthi isib. base.sql
2. Ukusebenzisa Ukufuduka
3. Shayela deployDump

base.sql indawo yokuqala phezu kwayo lapho ukuthutha kusetshenziswa futhi kwenziwa deployDump, okungukuthi base.sql + миграции + deployDump = актуальная структура БД. Ungakha ifayela elinjalo usebenzisa insiza pg_dump. Okusetshenzisiwe base.sql kuphela lapho uqalisa isizindalwazi kusukela ekuqaleni.

Ake sishayele umbhalo ukuze siqedele ukuqaliswa kwesizindalwazi refresh.sh. Uhlelo lokusebenza lungabukeka kanjena:

1. Unjiniyela wethula endaweni yakhe refresh.sh futhi ithola ukwakheka kwesizindalwazi samanje
2. Umthuthukisi uqala umsebenzi okhona, elungisa imininingwane yendawo ukuze ihlangabezane nezidingo zokusebenza okusha (ALTER TABLE ... ADD COLUMN njll)
3. Ngemva kokuqeda umsebenzi, umthuthukisi ubiza umsebenzi saveDumpukwenza izinguquko ezenziwe kusizindalwazi ku-VCS
4. Ukuqalisa kabusha unjiniyela refresh.shke verifyDumpmanje okubonisa uhlu lwezinguquko ezizofakwa ekuthuthweni
5. Umthuthukisi udlulisela zonke izinguquko zesakhiwo kufayela lokuthuthela, iqalisa futhi refresh.sh и verifyDump, futhi, uma ukufuduka kuhlanganiswe kahle, verifyDump ngeke ibonise mehluko phakathi kwesizindalwazi sendawo nokulahlwa okulondoloziwe

Inqubo echazwe ngenhla iyahambisana nezimiso ze-gitflow. Igatsha ngalinye ku-VCS lizoqukatha inguqulo yalo yokulahla, futhi lapho uhlanganisa amagatsha, okulahlwayo kuzohlanganiswa. Ezimweni eziningi, asikho isenzo esengeziwe okudingeka sithathwe ngemva kokuhlanganisa, kodwa uma izinguquko zenziwe emagatsheni ahlukene, isibonelo, etafuleni elifanayo, kungase kuphakame ukungqubuzana.

Ake sicabangele isimo sokungqubuzana sisebenzisa isibonelo: kukhona igatsha Ukuthuthukisa, okungamagatsha amabili avela kuwo; isici1 и isici2, ezingangqubuzani nazo Ukuthuthukisa, kodwa nixabene. Umsebenzi uwukuhlanganisa womabili amagatsha abe Ukuthuthukisa. Kulokhu, kunconywa ukuthi uqale uhlanganise elinye lamagatsha libe Ukuthuthukisabese uhlanganisa Ukuthuthukisa egatsheni elisele, ixazulula izingxabano egatsheni elisele, bese ihlanganisa igatsha lokugcina libe Ukuthuthukisa. Phakathi nesigaba sokuxazulula izingxabano, kungase kudingeke ukuthi ulungise ifayela lokuthuthela egatsheni lokugcina ukuze lifane nokulahlwa kokugcina, okuhlanganisa imiphumela yokuhlanganisa.

Uzithumela kanjani ngokuphephile izinguquko ezengeziwe kusakhiwo sedathabheyisi endaweni yokukhiqiza

Ngenxa yokuba khona kokulahlwa kwesakhiwo sedathabhesi yamanje ku-VCS, kuyenzeka ukuthi kubhekwe isizindalwazi sokukhiqiza ukuze sihambisane ncamashi nesakhiwo esidingekayo. Lokhu kuqinisekisa ukuthi zonke izinguquko ezihloswe onjiniyela zidluliselwe ngempumelelo kusisekelo sokukhiqiza.

Kusukela I-DDL ku-PostgreSQL kuyinto okwenziwayo, kunconywa ukuthi uhambisane nomyalelo wokuthunyelwa olandelayo, ukuze, uma kwenzeka iphutha elingalindelekile, ungakwazi ukukhipha "ngaphandle kobuhlungu" ROLLBACK:

1. Qala umsebenzi
2. Yenza konke ukuthutha emsebenzini
3. Ngokwenza okufanayo, sebenzisa deployDump
4. Ngaphandle kokuqedela okwenziwayo, sebenzisa verifyDump. Uma kungekho amaphutha, qalisa COMMIT. Uma kukhona amaphutha, gijima ROLLBACK

Lezi zinyathelo zingahlanganiswa kalula ezindleleni ezikhona zokusatshalaliswa kwezicelo, okuhlanganisa ne-zero-downtime.

isiphetho

Ngenxa yezindlela ezichazwe ngenhla, kungenzeka ukucindezela ukusebenza okuphezulu kumaphrojekthi we-“PHP + PostgreSQL”, kuyilapho udela ukuthuthukiswa okuncane uma kuqhathaniswa nokusebenzisa yonke ingqondo yebhizinisi kukhodi enkulu yohlelo lokusebenza. Ngaphezu kwalokho, ukucubungula idatha ku PL/pgSQL kuvame ukubukeka kukhanye kakhudlwana futhi kudinga ikhodi encane kunokusebenza okufanayo okubhalwe ku-PHP.

Source: www.habr.com