Chinangwa chechinyorwa chino ndechekushandisa muenzaniso weraibhurari schema-muchengeti ratidza maturusi anogona kurerutsa maitiro ekugadzira dhatabhesi mukati mePHP mapurojekiti uchishandisa iyo PostgreSQL DBMS.

Ruzivo rwechinyorwa ichi, chekutanga, ruchabatsira kune vanogadzira vanoda kushandisa zvakanyanya zvePostgreSQL kugona, asi vakatarisana nematambudziko ekuchengetedza bhizinesi pfungwa yakaiswa mudhatabhesi.

Ichi chinyorwa hachitsanangure zvakanakira kana kuipa kwekuchengetera bhizinesi pfungwa mudhatabhesi. Zvinofungidzirwa kuti sarudzo yakatoitwa nemuverengi.

Mibvunzo inotevera ichakurukurwa:

1. Ndeipi fomu iyo dhatabhesi yedhizaini inofanirwa kuchengetwa mushanduro yekudzora system (inozonzi VCS)
2. Maitiro ekutevera shanduko mune dhatabhesi chimiro mushure mekuchengetedza dump
3. Maitiro ekufambisa shanduko mune dhatabhesi chimiro kune mamwe nharaunda pasina kupokana uye hofori mafaera ekufambisa
4. Maitiro ekuronga maitiro ebasa rakafanana purojekiti nevagadziri vakati wandei
5. Maitiro ekuisa zvakachengetedzeka shanduko dzakawanda mudhatabhesi chimiro kune nzvimbo yekugadzira
   

   SchemaKeeper yakagadzirirwa kushanda nemaitirwo akachengetwa akanyorwa mumutauro PL/pgSQL. Kuedza nemimwe mitauro hakuna kuitwa, saka kushandiswa kunogona kusashanda kana kusagoneka.

Maitiro ekuchengeta dhatabhesi chimiro chekurasa muVCS

raibhurari schema-muchengeti inopa basa saveDump, iyo inochengetedza chimiro chezvinhu zvese kubva mudhatabhesi semafaira ezvinyorwa zvakasiyana. Iyo inobuda idhairekitori rine iyo dhatabhesi chimiro, yakakamurwa kuita mumapoka mafaira anogona kuwedzerwa nyore kuVCS.

Ngatitarisei kushandura zvinhu kubva mudhatabhesi kuita mafaera tichishandisa akati wandei mienzaniso:

Object type
The scheme
Title
Yakabatana nzira yefaira

tafura
ruzhinji rwevanhu
nhoroondo
./public/tables/accounts.txt

Kuchengetedzwa maitiro
ruzhinji rwevanhu
auth (hash bigint)
./public/functions/auth(int8).sql

Nhanganyaya
Réservation
tariffs
./booking/views/tariffs.txt

Zviri mukati memafaira iratidziro yezvinyorwa zvechimiro chechimwe chinhu che database. Semuenzaniso, kune akachengetwa maitiro, zviri mukati mefaira zvichave tsananguro yakazara yemaitiro akachengetwa, kutanga ne block. CREATE OR REPLACE FUNCTION.

Sezvinoonekwa kubva patafura iri pamusoro, nzira inoenda kufaira inochengetedza ruzivo rwemhando, schema uye zita rechinhu. Iyi nzira inoita kuti zvive nyore kufamba kuburikidza nekurasira uye kodhi ongororo yekuchinja mune dhatabhesi.

ekisitenjeni .sql kune mafaera ane akachengetwa maitiro source code, izvi zvakasarudzwa kuitira kuti iyo IDE ingope maturusi ekudyidzana nedhatabhesi kana faira ravhurwa.

Maitiro ekutevera shanduko mune dhatabhesi chimiro mushure mekuchengetedza dump

Nekuchengetedza kuraswa kweiyo ikozvino dhatabhesi chimiro muVCS, tinowana mukana wekutarisa kana shanduko dzakaitwa kune iyo dhatabhesi chimiro mushure mekurasira kwagadzirwa. Muraibhurari schema-muchengeti kuona shanduko mune dhatabhesi chimiro, basa rinopihwa verifyDump, iyo inodzorera ruzivo pamusoro pekusiyana pasina migumisiro.

Imwe nzira yekutarisa ndeye kufonera basa zvakare saveDump, uchitsanangura dhairekitori rimwechete, uye tarisa muVCS kuti uchinje. Sezvo zvinhu zvese kubva mudhatabhesi zvakachengetwa mumafaira akasiyana, VCS inongoratidza zvakachinja zvinhu.
Chinhu chikuru chakashata cheiyi nzira ndiko kudiwa kwekunyora mafaera kuitira kuti uone shanduko.

Maitiro ekufambisa shanduko mune dhatabhesi chimiro kune mamwe nharaunda pasina kupokana uye hofori mafaera ekufambisa

Kutenda kune basa deployDump Iyo kodhi kodhi yemaitirwo akachengetwa anogona kugadziridzwa nenzira yakafanana neyenguva dzose yekushandisa sosi kodhi. Iwe unogona kuwedzera / kudzima mitsara mitsva mune yakachengetwa maitiro kodhi uye nekukasira shanduko kune vhezheni kutonga, kana kugadzira / kudzima maitiro akachengetwa nekugadzira / kudzima mafaera anoenderana mune yekurasa dhairekitori.

Semuenzaniso, kugadzira nzira nyowani yakachengetwa mune schema public ingogadzira faira idzva nekuwedzera .sql mudhairekitori public/functions, isa iyo kodhi yekodhi yemaitiro akachengetwa mairi, kusanganisira block CREATE OR REPLACE FUNCTION, wobva wafonera basa racho deployDump. Kugadzirisa uye kudzima nzira yakachengetwa inoitika nenzira imwecheteyo. Saka, iyo kodhi inopinda muVCS uye dhatabhesi panguva imwe chete.

Kana kukanganisa kuchionekwa mune kodhi kodhi yechero nzira yakachengetwa, kana mutsauko pakati pemazita efaira uye maitiro akachengetwa, ipapo deployDump ichakundikana, kuratidza kukanganisa mavara. Kusawirirana kwemaitiro akachengetwa pakati pekuraswa uye dhatabhesi iripo hazvigoneke kana uchishandisa deployDump.

Paunenge uchigadzira nzira nyowani yakachengetwa, hapana chikonzero chekuisa nemaoko zita rakakodzera refaira. Zvakakwana kuti faira ive nekuwedzera .sql. Mushure mekufona deployDump iwo mameseji ekukanganisa achange aine zita chairo, iro rinogona kushandiswa kutumidza zita refaira.

deployDump inokutendera kuti uchinje maparamendi ebasa kana rudzi rwekudzoka pasina mamwe zviito, nepo neiyo classical nzira yaunofanirwa kuita.
kutanga DROP FUNCTION, uye chete ipapo CREATE OR REPLACE FUNCTION.

Zvinosuruvarisa, kune mamwe mamiriro ezvinhu apo deployDump haikwanise kuisa shanduko otomatiki. Semuyenzaniso, kana chinokonzeresa chinoshandiswa neinenge chimwe chinokonzeresa chikabviswa. Mamiriro ezvinhu akadaro anogadziriswa nemaoko uchishandisa mafaera ekufambisa.

Kana iwe uine mhosva yekutama shanduko kune yakachengetwa maitiro schema-muchengeti, ipapo mafaera ekufambisa anofanira kushandiswa kuendesa dzimwe shanduko muchimiro. Semuenzaniso, raibhurari yakanaka yekushanda nekutama ndeye dzidziso/kutama.

Kutama kunofanirwa kuiswa kusati kwatanga deployDump. Izvi zvinokutendera iwe kuti uite shanduko dzese kune chimiro uye kugadzirisa mamiriro ane dambudziko kuitira kuti shanduko mumatanho akachengetwa aendeswe pasina matambudziko.

Kushanda nevanotama kunozotsanangurwa zvakadzama muzvikamu zvinotevera.

Maitiro ekuronga maitiro ebasa rakafanana purojekiti nevagadziri vakati wandei

Izvo zvinodikanwa kugadzira script yekutanga kuzere kweiyo dhatabhesi, iyo inozotangwa nemugadziri pamushini wake webasa, achiunza chimiro chedhatabhesi yemunharaunda zvinoenderana nekuraswa kwakachengetwa muVCS. Nzira iri nyore ndeyekuparadzanisa kutanga kwedhatabhesi yemunharaunda kuita matanho matatu:

1. Pinza faira rine chimiro chakakosha chinozodaidzwa kuti e.g. base.sql
2. Kushandisa Migrations
3. Dambudziko deployDump

base.sql ndiyo nzvimbo yekutanga pamusoro peiyo migration inoshandiswa uye kuitwa deployDump, ndizvo base.sql + миграции + deployDump = актуальная структура БД. Iwe unogona kugadzira faira rakadaro uchishandisa utility pg_dump. Used base.sql chete kana uchitanga dhatabhesi kubva pakutanga.

Ngatishevedzei script kuti izere dhatabhesi kutanga refresh.sh. Chimiro chekushanda chinogona kutaridzika seizvi:

1. Mugadziri anotanga munzvimbo yake refresh.sh uye inowana yazvino dhatabhesi chimiro
2. Mugadziri anotanga kushanda pane basa riripo, achigadzirisa dhatabhesi yemunharaunda kuti isangane nezvinodiwa zvebasa idzva (ALTER TABLE ... ADD COLUMN etc)
3. Mushure mekupedza basa, mugadziri anodana basa saveDumpkuita shanduko dzakaitwa kune dhatabhesi muVCS
4. Developer reunch refresh.shipapo verifyDumpiyo ikozvino inoratidza runyoro rwekuchinja kunosanganisira mukutama
5. Mugadziri anotamisa shanduko dzese kune iyo migration faira, inomhanya zvakare refresh.sh и verifyDump, uye, kana kutama kwacho kwakanyorwa nenzira kwayo, verifyDump haizoratidze misiyano pakati pedhatabhesi renzvimbo nerasi rakachengetwa

Maitiro anotsanangurwa pamusoro anowirirana negitflow misimboti. Bazi rega rega muVCS rinenge riine vhezheni yaro yekurasa, uye kana uchibatanidza matavi, marara achabatanidzwa. Muzviitiko zvakawanda, hapana chiito chekuwedzera chinoda kutorwa mushure mekubatana, asi kana shanduko dzakaitwa mumatavi akasiyana, semuenzaniso, kune imwe tafura, kukakavara kunogona kubuda.

Ngatitarisei mamiriro ekukonana tichishandisa muenzaniso: pane bazi Kukura, pamatavi maviri kubva paari. chimiro1 и chimiro2, izvo zvisingapesani nazvo Kukura, asi muchinetsana. Basa ndere kubatanidza mapazi maviri ari maviri Kukura. Panyaya iyi, zvinokurudzirwa kutanga kubatanidza rimwe rematavi mukati Kukurawobva wabatanidza Kukura kubazi rasara, kugadzirisa kusawirirana mubazi rasara, uyezve kubatanidza bazi rekupedzisira kupinda Kukura. Munguva yechikamu chekugadzirisa kusawirirana, unogona kugadzirisa faira yekutama mubazi rekupedzisira kuitira kuti ienderane nekuraswa kwekupedzisira, kunosanganisira mhedzisiro yekubatanidzwa.

Maitiro ekuisa zvakachengetedzeka shanduko dzakawanda mudhatabhesi chimiro kune nzvimbo yekugadzira

Nekuda kwekuvapo kwekuraswa kweiyo ikozvino dhatabhesi chimiro muVCS, zvinoita kuti utarise dhatabhesi rekugadzira kuti inyatso kutevedza neinodiwa chimiro. Izvi zvinova nechokwadi chekuti shanduko dzese dzaidiwa nevagadziri dzakaendeswa zvakabudirira kune hwaro hwekugadzira.

kubva DDL muPostgreSQL ndizvo mutengapwe, zvinokurudzirwa kuomerera kune inotevera dhizaini kurongeka, kuitira kuti, kana paine chikanganiso chisingatarisirwi, unogona "zvisingarwadzi" kuita. ROLLBACK:

1. Tanga kutengeserana
2. Ita kutama kwese mukutengeserana
3. Muchiitiko chimwe chete, ita deployDump
4. Pasina kupedzisa kutengeserana, ita verifyDump. Kana pasina zvikanganiso, mhanya COMMIT. Kana pane kukanganisa, mhanya ROLLBACK

Aya matanho anogona kubatanidzwa nyore nyore munzira dziripo dzekutumira application, kusanganisira zero-downtime.

mhedziso

Nekuda kwemaitiro atsanangurwa pamusoro, zvinokwanisika kudzvanya kuita kwakanyanya kunze kwe "PHP + PostgreSQL" mapurojekiti, uku uchipa kurerukirwa kushoma kwekusimudzira kana uchienzaniswa nekuita zvese zvebhizinesi logic mukodhi huru yekushandisa. Zvakare, data processing in PL/pgSQL kazhinji inotaridzika zvakajeka uye inoda kodhi shoma pane imwechete kushanda kwakanyorwa muPHP.

Source: www.habr.com