Injongo yeli nqaku kukusebenzisa umzekelo wethala leencwadi umgcini-schema bonisa izixhobo ezinokwenza lula kakhulu inkqubo yokuphuhlisa isiseko sedatha ngaphakathi kweeprojekthi ze-PHP usebenzisa i-PostgreSQL DBMS.

Ulwazi oluvela kweli nqaku, okokuqala, luya kuba luncedo kubaphuhlisi abafuna ukwenza uninzi lwezakhono zePostgreSQL, kodwa bajongene neengxaki zokugcina ingqiqo yezoshishino ebekwe kwisiseko sedatha.

Eli nqaku aliyi kuchaza izibonelelo okanye ukungalungi kokugcina ingqiqo yezoshishino kwisiseko sedatha. Kucingelwa ukuba ukhetho sele lwenziwe ngumfundi.

Le mibuzo ilandelayo iya kuqwalaselwa:

1. Ingaba indawo yokulahla isiseko sesiseko sedatha igcinwe koluphi uhlobo kwinkqubo yolawulo loguqulelo (emva koku kubhekiselwa kuyo njengeVCS)
2. Ululandelela njani utshintsho kwisiseko sedatha emva kokugcina inkunkuma
3. Indlela yokudlulisa utshintsho kwisiseko sedatha kwezinye iindawo ngaphandle kweengxabano kunye neefayile ezinkulu zokufuduka
4. Indlela yokulungelelanisa inkqubo yomsebenzi ofanayo kwiprojekthi ngabaphuhlisi abaninzi
5. Indlela yokuhambisa ngokukhuselekileyo utshintsho olongezelelweyo kwisakhiwo sedatabase kwindawo yokuvelisa
   

   SchemaKeeper yenzelwe ukusebenza ngeenkqubo ezigciniweyo ezibhalwe ngolwimi PL/pgSQL. Uvavanyo ngezinye iilwimi alukenziwa, ngoko ke ukusetyenziswa kusenokungasebenzi okanye kungenzeki.

Uyigcina njani indawo yokulahla isiseko sedatabase kwiVCS

ilayibrari umgcini-schema inikeza umsebenzi saveDump, egcina ubume bezinto zonke kwisiseko sedatha njengeefayile ezibhaliweyo ezahlukeneyo. Imveliso nguvimba weefayili oqulethe ubume besiseko sedatha, owahlulwe kwiifayile ezidityanisiweyo ezinokongezwa ngokulula kwiVCS.

Makhe sijonge ekuguquleni izinto ukusuka kwisiseko sedatha kwiifayile usebenzisa imizekelo emininzi:

Uhlobo lwento
Iskimu
Isihloko
Indlela eya kwifayile

Itheyibhuli
kawonke wonke
akhawunti
./public/tables/accounts.txt

Inkqubo egciniweyo
kawonke wonke
auth(hash bigint)
./public/functions/auth(int8).sql

Intshayelelo
lebhola
ntlawulo
./booking/views/tariffs.txt

Imixholo yeefayile ngumboniso wombhalo wesakhiwo sento ethile yesiseko sedatha. Umzekelo, kwiinkqubo ezigciniweyo, imixholo yefayile iya kuba yinkcazo epheleleyo yenkqubo egciniweyo, ukuqala ngebhloko. CREATE OR REPLACE FUNCTION.

Njengoko kunokubonwa kwitheyibhile engentla, indlela eya kwifayile igcina ulwazi malunga nohlobo, i-schema kunye negama lento. Le ndlela yenza kube lula ukuhamba ngokulahla kunye nokuphononongwa kwekhowudi yotshintsho kwisiseko sedatha.

Ukwandiswa .sql kwiifayile ezinekhowudi yemvelaphi yenkqubo egciniweyo, oku kukhethwe ukuze i-IDE ibonelele ngokuzenzekelayo izixhobo zokusebenza kugcino lwedatha xa ifayile ivulwa.

Ululandelela njani utshintsho kwisiseko sedatha emva kokugcina inkunkuma

Ngokugcina ukulahlwa kwesakhiwo sesiseko sedatha kwi-VCS, sifumana ithuba lokujonga ukuba ngaba utshintsho lwenziwe kwisiseko sedatha emva kokudalwa kokulahla. Kwithala leencwadi umgcini-schema ukukhangela utshintsho kulwakhiwo lwedatha, umsebenzi unikiwe verifyDump, ebuyisela ulwazi malunga nokungafani ngaphandle kwemiphumo emibi.

Enye indlela yokukhangela kukufowunela umsebenzi kwakhona saveDump, ikhankanya ulawulo olufanayo, kwaye khangela kwiVCS yotshintsho. Ekubeni zonke izinto ezivela kwisiseko sedatha zigcinwe kwiifayile ezahlukeneyo, i-VCS iya kubonisa kuphela izinto ezitshintshileyo.
Eyona nto ingalunganga yale ndlela iyimfuneko yokubhala ngaphezulu kweefayile ukuze ubone utshintsho.

Indlela yokudlulisa utshintsho kwisiseko sedatha kwezinye iindawo ngaphandle kweengxabano kunye neefayile ezinkulu zokufuduka

Enkosi kumsebenzi deployDump Ikhowudi yemvelaphi yeenkqubo ezigciniweyo zinokuhlelwa ngendlela efanayo nekhowudi yomthombo wesicelo esiqhelekileyo. Unokongeza / ucime imigca emitsha kwikhowudi yenkqubo egciniweyo kwaye ngokukhawuleza utyhale utshintsho kulawulo loguqulelo, okanye udale / ucime iinkqubo ezigciniweyo ngokudala / ukucima iifayile ezihambelanayo kulawulo lokulahla.

Umzekelo, ukwenza inkqubo entsha egcinwe kwi-schema public yenza nje ifayile entsha kunye nolwandiso .sql kuluhlu public/functions, beka ikhowudi yomthombo wenkqubo egcinwe kuyo, kuquka ibhloko CREATE OR REPLACE FUNCTION, uze ufowunele umsebenzi deployDump. Ukuguqula kunye nokucima inkqubo egciniweyo kwenzeka ngendlela efanayo. Ngaloo ndlela, ikhowudi ingena kuzo zombini iVCS kunye nesiseko sedatha ngexesha elinye.

Ukuba impazamo ibonakala kwikhowudi yomthombo wayo nayiphi na inkqubo egciniweyo, okanye ukungafani phakathi kwamagama efayile kunye nenkqubo egciniweyo, ngoko deployDump iya kusilela, ibonisa impazamo yokubhaliweyo. Ukungafani kweenkqubo ezigciniweyo phakathi kwendawo yokulahla kunye nesiseko sedatha yangoku akunakwenzeka xa usebenzisa deployDump.

Xa udala inkqubo entsha egciniweyo, akukho mfuneko yokufaka ngesandla igama lefayile elichanekileyo. Kwanele ukuba ifayile ibe nolwandiso .sql. Emva kwefowuni deployDump okubhaliweyo kwemposiso kuzakuqulatha igama elichanekileyo, elinokusetyenziswa ukuqamba ngokutsha ifayile.

deployDump ikuvumela ukuba utshintshe iparameters zomsebenzi okanye ukubuyisela udidi ngaphandle kweentshukumo ezongezelelweyo, ngelixa ngendlela yeklasiki kuyakufuneka
yenza kuqala DROP FUNCTION, kwaye kuphela emva koko CREATE OR REPLACE FUNCTION.

Ngelishwa, kukho ezinye iimeko apho deployDump ayikwazi ukwenza utshintsho ngokuzenzekelayo. Umzekelo, ukuba i-trigger function esetyenziswa nokuba yi-trigger enye iyasuswa. Iimeko ezinjalo zisonjululwa ngesandla kusetyenziswa iifayile zokufuduka.

Ukuba unoxanduva lokufuduka utshintsho kwiinkqubo ezigciniweyo umgcini-schema, emva koko iifayile zokufuduka kufuneka zisetyenziswe ukudlulisa ezinye iinguqu kwisakhiwo. Umzekelo, ilayibrari elungileyo yokusebenza ngokufuduka imfundiso/ukufuduka.

Ukufuduka kufuneka kusetyenziswe ngaphambi kokuqaliswa deployDump. Oku kukuvumela ukuba wenze zonke iinguqu kwisakhiwo kwaye usombulule iimeko eziyingxaki ukuze utshintsho kwiinkqubo ezigciniweyo zidluliselwe emva koko ngaphandle kweengxaki.

Ukusebenza ngokufuduka kuya kuchazwa ngokubanzi kumacandelo alandelayo.

Indlela yokulungelelanisa inkqubo yomsebenzi ofanayo kwiprojekthi ngabaphuhlisi abaninzi

Kuyimfuneko ukwenza iskripthi sokuqaliswa ngokupheleleyo kwesiseko sedatha, esiza kuqaliswa ngumphuhlisi kumatshini wakhe womsebenzi, ukuzisa isakhiwo sedatha yendawo ngokuhambelana nokulahla okugcinwe kwiVCS. Eyona ndlela ilula kukwahlula isiqalo sesiseko sedata sasekhaya sibe ngamanyathelo ama-3:

1. Ngenisa ifayile enesiseko esisisiseko esiya kubizwa ngokuba umz. base.sql
2. Ukusebenzisa ukufuduka
3. Mngeni deployDump

base.sql yindawo yokuqala apho ufuduko lusetyenziswa kwaye lwenziwa deployDump, oko kukuthi base.sql + миграции + deployDump = актуальная структура БД. Unokwenza ifayile enjalo usebenzisa into eluncedo pg_dump. Isetyenzisiwe base.sql ngokukodwa xa uqalisa idatabase ukusuka ekuqaleni.

Masibize iscript ukuze sigqibezele ukuqaliswa kwesiseko sedata refresh.sh. Uluhlu lomsebenzi luya kubonakala ngolu hlobo:

1. Umphuhlisi uqalisa kwindawo yakhe refresh.sh kwaye ifumana ubume besiseko sedatha yangoku
2. Umphuhlisi uqala umsebenzi kumsebenzi okhoyo, elungisa isiseko sedatha sasekhaya ukuhlangabezana neemfuno zomsebenzi omtsha (ALTER TABLE ... ADD COLUMN njl)
3. Emva kokugqiba umsebenzi, umphuhlisi ubiza umsebenzi saveDumpukwenza utshintsho olwenziwe kwiziko ledatha kwiVCS
4. Ukuqaliswa kwakhona konjiniyela refresh.shke verifyDumpebonisa ngoku uluhlu lotshintsho oluza kubandakanywa kufuduko
5. Umphuhlisi udlulisela lonke utshintsho lwesakhiwo kwifayile yokufuduka, iqhuba kwakhona refresh.sh и verifyDump, kwaye, ukuba ukufuduka kuhlanganiswe ngokuchanekileyo, verifyDump ayizukubonisa mahluko phakathi kwesiseko sedata sasekhaya kunye nenkunkuma egciniweyo

Inkqubo echazwe ngasentla iyahambelana nemigaqo ye-gitflow. Isebe ngalinye kwi-VCS liya kuba nenguqulo yalo yokulahla, kwaye xa kuhlanganiswa amasebe, iindawo zokulahla ziya kudibaniswa. Kwiimeko ezininzi, akukho nyathelo elongezelelweyo elifunekayo emva kokudibanisa, kodwa ukuba utshintsho lwenziwe kumasebe ahlukeneyo, umzekelo, kwitheyibhile enye, ingxabano inokuvela.

Makhe siqwalasele imeko yongquzulwano sisebenzisa umzekelo: kukho isebe Phuhlisa, amasebe amabini avela kuwo; uphawu1 и uphawu2, ezingangqubaniyo nazo Phuhlisa, kodwa yibani neengxabano. Umsebenzi kukudibanisa omabini amasebe Phuhlisa. Kule meko, kuyacetyiswa ukuba uqale udibanise elinye lamasebe Phuhlisakwaye emva koko udibanise Phuhlisa kwisebe eliseleyo, ukusombulula iingxabano kwisebe eliseleyo, kwaye emva koko ukudibanisa isebe lokugqibela Phuhlisa. Ngexesha lesigaba sokusombulula ingxabano, unokulungisa ifayile yokufuduka kwisebe lokugqibela ukuze ihambelane nokulahla kokugqibela, okubandakanya iziphumo zokudibanisa.

Indlela yokuhambisa ngokukhuselekileyo utshintsho olongezelelweyo kwisakhiwo sedatabase kwindawo yokuvelisa

Ngombulelo kubukho bokulahla isiseko sedatha yangoku kwi-VCS, kunokwenzeka ukuba kuhlolwe isiseko sedatha yokuvelisa ukuthotyelwa ngokuchanekileyo kunye nesakhiwo esifunekayo. Oku kuqinisekisa ukuba zonke iinguqu ezijoliswe ngabaphuhlisi zidluliselwe ngempumelelo kwisiseko semveliso.

ukususela I-DDL kwiPostgreSQL yi intengiselwano, kucetyiswa ukuba unamathele kulo myalelo ulandelayo wokuthunyelwa, ukwenzela ukuba, xa kwenzeka iphutha elingalindelekanga, unokwenza "ngokungenabuhlungu" ROLLBACK:

1. Qala intengiselwano
2. Yenza konke ukufuduka kwintengiselwano
3. Kwintengiselwano efanayo, yenza deployDump
4. Ngaphandle kokugqiba intengiselwano, yenza verifyDump. Ukuba akukho ziphoso, qhuba COMMIT. Ukuba kukho iimpazamo, qhuba ROLLBACK

La manyathelo anokudityaniswa ngokulula kwiindlela ezikhoyo zokusasazwa kwesicelo, kubandakanywa i-zero-downtime.

isiphelo

Ndiyabulela kwiindlela ezichazwe ngasentla, kunokwenzeka ukucudisa ukusebenza okuphezulu kwiiprojekthi ze "PHP + PostgreSQL", ngelixa uncama uphuhliso oluncinci olulula xa kuthelekiswa nokuphumeza yonke ingqiqo yezoshishino kwikhowudi yesicelo esiphambili. Ngaphezu koko, ukusetyenzwa kwedatha kwi PL/pgSQL ihlala ibonakala ngokucacileyo kwaye ifuna ikhowudi encinci kunokusebenza okufanayo okubhalwe kwi-PHP.

umthombo: www.habr.com