Manufar wannan labarin shine a yi amfani da misalin ɗakin karatu mai tsare-tsare nuna kayan aikin da za su iya sauƙaƙe tsarin haɓaka bayanai a cikin ayyukan PHP ta amfani da PostgreSQL DBMS.

Bayanin daga wannan labarin zai, da farko, zai zama da amfani ga masu haɓakawa waɗanda ke son yin amfani da mafi yawan damar PostgreSQL, amma suna fuskantar matsalolin kiyaye dabarun kasuwanci da aka sanya a cikin bayanan.

Wannan labarin ba zai bayyana fa'idodi ko rashin amfanin adana dabaru na kasuwanci a cikin bayanan ba. Ana tsammanin cewa mai karatu ya riga ya yi zaɓin.

Za a yi la'akari da waɗannan tambayoyi masu zuwa:

1. A wane nau'i ne ya kamata a adana jujjuya tsarin bayanai a cikin tsarin sarrafa sigar (nan gaba ana kiransa VCS)
2. Yadda ake bin diddigin canje-canje a tsarin bayanan bayanan bayan adana juji
3. Yadda za a canja wurin canje-canje a cikin tsarin bayanai zuwa wasu wurare ba tare da rikici da manyan fayilolin ƙaura ba
4. Yadda za a tsara tsarin aiki na layi daya akan aikin da yawa masu haɓakawa
5. Yadda za a aminta da tura ƙarin canje-canje a tsarin bayanai zuwa yanayin samarwa
   

   Mai Tsara Tsara tsara don aiki tare da adana hanyoyin da aka rubuta a cikin harshe PL/pgSQL. Ba a gudanar da gwaji tare da wasu harsuna ba, don haka amfani ba zai yi tasiri ba ko kuma ba zai yiwu ba.

Yadda ake adana jujjuya tsarin bayanai a cikin VCS

Laburare mai tsare-tsare yana ba da aiki saveDump, wanda ke adana tsarin duk abubuwa daga ma'ajin bayanai azaman fayilolin rubutu daban. Fitarwa shine kundin adireshi mai ɗauke da tsarin bayanai, wanda aka raba zuwa gaɓar fayiloli waɗanda za'a iya ƙarawa cikin sauƙi zuwa VCS.

Bari mu kalli jujjuya abubuwa daga rumbun adana bayanai zuwa fayiloli ta amfani da misalai da yawa:

Nau'in abu
Makircin
Title
Hanyar dangi zuwa fayil

tebur
jama'a
asusun
./public/tables/accounts.txt

Hanyar da aka adana
jama'a
auth (hash bigint)
./public/functions/auth(int8).sql

Gabatarwar
booking
tariffs
./booking/views/tariffs.txt

Abubuwan da ke cikin fayilolin wakilcin rubutu ne na tsarin takamaiman abu na bayanai. Misali, don hanyoyin da aka adana, abubuwan da ke cikin fayil ɗin zasu zama cikakkiyar ma'anar hanyar da aka adana, farawa tare da toshe. CREATE OR REPLACE FUNCTION.

Kamar yadda ake iya gani daga teburin da ke sama, hanyar zuwa fayil ɗin tana adana bayanai game da nau'in, tsari da sunan abu. Wannan hanyar tana ba da sauƙin kewaya ta hanyar juji da sake duba lambobi na canje-canje a cikin bayanan.

tsawo .sql don fayiloli tare da lambar tushen hanyar da aka adana, an zaɓi wannan don IDE ta atomatik tana ba da kayan aikin mu'amala tare da bayanan lokacin da aka buɗe fayil ɗin.

Yadda ake bin diddigin canje-canje a tsarin bayanan bayanan bayan adana juji

Ta hanyar adana juji na tsarin bayanai na yanzu a cikin VCS, muna samun damar bincika ko an yi canje-canje ga tsarin bayanan bayan an ƙirƙiri juji. A cikin ɗakin karatu mai tsare-tsare don gano canje-canje a cikin tsarin bayanai, an samar da aiki verifyDump, wanda ke mayar da bayanai game da bambance-bambance ba tare da illa ba.

Wata madadin hanyar dubawa ita ce sake kiran aikin saveDump, ƙayyadaddun kundin adireshi ɗaya, kuma duba cikin VCS don canje-canje. Tunda an adana duk abubuwa daga ma'ajin bayanai a cikin fayiloli daban, VCS kawai za ta nuna abubuwan da aka canza.
Babban rashin lahani na wannan hanyar shine buƙatar sake rubuta fayiloli don ganin canje-canje.

Yadda za a canja wurin canje-canje a cikin tsarin bayanai zuwa wasu wurare ba tare da rikici da manyan fayilolin ƙaura ba

Godiya ga aikin deployDump Ana iya gyara lambar tushe na hanyoyin da aka adana daidai daidai da lambar tushen aikace-aikacen yau da kullun. Kuna iya ƙara/share sabbin layiyoyi a cikin lambar tsari da aka adana kuma nan da nan tura canje-canje zuwa sarrafa sigar, ko ƙirƙira/share hanyoyin da aka adana ta ƙirƙira/share fayilolin da suka dace a cikin kundin juji.

Misali, don ƙirƙirar sabuwar hanyar da aka adana a cikin tsari public kawai ƙirƙirar sabon fayil tare da tsawo .sql a cikin directory public/functions, sanya lambar tushe na hanyar da aka adana a ciki, gami da toshe CREATE OR REPLACE FUNCTION, sannan kira aikin deployDump. Gyarawa da share hanyar da aka adana yana faruwa ta hanya ɗaya. Don haka, lambar tana shiga cikin VCS da ma'ajin bayanai a lokaci guda.

Idan kuskure ya bayyana a cikin lambar tushe na kowace hanya da aka adana, ko rashin daidaituwa tsakanin sunayen fayil ɗin da tsarin da aka adana, to. deployDump zai gaza, yana nuna kuskuren rubutu. Rashin daidaituwar hanyoyin da aka adana tsakanin juji da bayanan da ake ciki yanzu ba zai yiwu ba lokacin amfani deployDump.

Lokacin ƙirƙirar sabuwar hanyar da aka adana, babu buƙatar shigar da sunan fayil daidai da hannu. Ya isa fayil ɗin ya sami tsawo .sql. Bayan kiran deployDump rubutun kuskure zai ƙunshi suna daidai, wanda za'a iya amfani dashi don sake suna fayil ɗin.

deployDump yana ba ku damar canza sigogin aiki ko nau'in dawowa ba tare da ƙarin ayyuka ba, yayin da tare da tsarin gargajiya dole ne ku yi
aiwatar da farko DROP FUNCTION, sannan kawai CREATE OR REPLACE FUNCTION.

Abin takaici, akwai wasu yanayi inda deployDump kasa aiwatar da canje-canje ta atomatik. Misali, idan an cire aikin faɗakarwa wanda aƙalla faɗaɗa ɗaya ke amfani da shi. Ana warware irin waɗannan yanayi da hannu ta amfani da fayilolin ƙaura.

Idan kuna da alhakin ƙaura canje-canje zuwa hanyoyin da aka adana mai tsare-tsare, to dole ne a yi amfani da fayilolin ƙaura don canja wurin wasu canje-canje a cikin tsarin. Misali, kyakkyawan ɗakin karatu don aiki tare da ƙaura shine koyaswar / ƙaura.

Dole ne a yi amfani da ƙaura kafin ƙaddamarwa deployDump. Wannan yana ba ku damar yin duk canje-canje ga tsarin da warware matsalolin matsala ta yadda canje-canje a cikin hanyoyin da aka adana daga baya an canza su ba tare da matsala ba.

Za a yi bayanin yin aiki tare da ƙaura dalla-dalla a cikin sassan da ke gaba.

Yadda za a tsara tsarin aiki na layi daya akan aikin da yawa masu haɓakawa

Wajibi ne a ƙirƙiri rubutun don cikakken ƙaddamar da bayanan, wanda mai haɓakawa zai ƙaddamar da shi akan injin aikinsa, yana kawo tsarin tsarin bayanan gida daidai da juji da aka adana a cikin VCS. Hanya mafi sauƙi ita ce a raba farawar rumbun adana bayanan gida zuwa matakai 3:

1. Shigo da fayil tare da tsari na asali wanda za a kira shi misali. base.sql
2. Aiwatar da Hijira
3. Kira deployDump

base.sql shine mafarin farko wanda ake yin hijira da aiwatar da shi deployDump, wato base.sql + миграции + deployDump = актуальная структура БД. Kuna iya ƙirƙirar irin wannan fayil ta amfani da kayan aiki pg_dump. Amfani base.sql keɓance lokacin farawa da bayanai daga karce.

Bari mu kira rubutun don cikakken farawa bayanai refresh.sh. Tsarin aiki na iya zama kamar haka:

1. Mai haɓakawa ya ƙaddamar a cikin muhallinsa refresh.sh kuma yana samun tsarin bayanai na yanzu
2. Mai haɓakawa ya fara aiki akan aikin da ke hannun, yana gyara bayanan gida don saduwa da buƙatun sabon aikin (ALTER TABLE ... ADD COLUMN da sauransu)
3. Bayan kammala aikin, mai haɓakawa ya kira aikin saveDumpdon aiwatar da canje-canjen da aka yi a cikin bayanan bayanai a cikin VCS
4. Mai haɓakawa ya sake buɗewa refresh.shto, verifyDumpwanda yanzu yana nuna jerin canje-canjen da zasu haɗa cikin ƙaura
5. Mai haɓakawa yana canza duk canje-canjen tsarin zuwa fayil ɗin ƙaura, yana sake gudana refresh.sh и verifyDump, kuma, idan an haɗa ƙaura daidai. verifyDump ba zai nuna wani bambance-bambance tsakanin rumbun adana bayanai na gida da juji da aka ajiye ba

Tsarin da aka bayyana a sama ya dace da ka'idodin gitflow. Kowane reshe a cikin VCS zai ƙunshi nau'in juji na kansa, kuma lokacin da ake haɗa rassan, za a haɗa jujjuyawar. A mafi yawan lokuta, babu wani ƙarin mataki da ake buƙatar ɗauka bayan haɗuwa, amma idan an yi canje-canje a rassa daban-daban, alal misali, zuwa tebur guda, rikici na iya tasowa.

Bari mu yi la'akari da yanayin rikici ta amfani da misali: akwai reshe ci gaba, daga wanda reshe biyu reshe: alama1 и alama2, wadanda ba su da sabani da su ci gaba, amma suna da rikici da juna. Ayyukan shine haɗa rassan biyu zuwa cikin ci gaba. Don wannan yanayin, ana ba da shawarar fara haɗa ɗaya daga cikin rassan cikin ci gabasannan a hade ci gaba zuwa ga sauran reshe, magance rikice-rikice a cikin ragowar reshe, sannan a haɗa reshe na ƙarshe zuwa ci gaba. A lokacin lokacin warware rikici, ƙila ku gyara fayil ɗin ƙaura a reshe na ƙarshe don ya dace da juji na ƙarshe, wanda ya haɗa da sakamakon haɗuwa.

Yadda za a aminta da tura ƙarin canje-canje a tsarin bayanai zuwa yanayin samarwa

Godiya ga kasancewar juji na tsarin bayanai na yanzu a cikin VCS, yana yiwuwa a bincika bayanan samarwa don daidaitaccen tsarin da ake buƙata. Wannan yana tabbatar da cewa duk canje-canjen da masu haɓakawa suka yi niyya an yi nasarar canza su zuwa tushen samarwa.

Tun DDL a cikin PostgreSQL shine ma'amala, Ana ba da shawarar yin biyayya da umarnin turawa mai zuwa, don haka, idan akwai kuskuren da ba zato ba tsammani, zaku iya aiwatar da "ba tare da jin zafi ba". ROLLBACK:

1. Fara ciniki
2. Yi duk ƙaura a cikin ma'amala
3. A cikin ma'amala iri ɗaya, aiwatar deployDump
4. Ba tare da kammala ciniki ba, aiwatar verifyDump. Idan babu kurakurai, gudu COMMIT. Idan akwai kurakurai, gudu ROLLBACK

Ana iya haɗa waɗannan matakan cikin sauƙi cikin hanyoyin da ake da su don tura aikace-aikacen, gami da lokacin faɗuwar sifili.

ƙarshe

Godiya ga hanyoyin da aka bayyana a sama, yana yiwuwa a matse matsakaicin aiki daga ayyukan "PHP + PostgreSQL", yayin da ake sadaukar da ɗan ƙaramin jin daɗin ci gaba idan aka kwatanta da aiwatar da duk dabarun kasuwanci a cikin babban lambar aikace-aikacen. Haka kuma, sarrafa bayanai a PL/pgSQL sau da yawa ya fi kyan gani kuma yana buƙatar ƙasa da lamba fiye da aikin da aka rubuta a cikin PHP.

source: www.habr.com