Напредна тема: Пропагација на податоци помеѓу централна и локална база

Опис на напредната тема

Како напредна тема во рамки на проектот SCS - Smart City Security е имплементирана локална симулација на пропагација на податоци помеѓу централна и локална база на податоци.

Идејата на оваа имплементација е да се симулира реален Smart City систем во кој постои една централна база, на ниво на град или институција, и една локална база, која може да припаѓа на полициска станица, локална зона или друг организациски дел од системот.

Поради ограничување да не се прават дополнителни измени на факултетската база, пропагацијата е имплементирана локално преку две PostgreSQL бази:

• scs_central - централна база
• scs_local - локална база

Централната база ги прима новите податоци, додека локалната база ги добива податоците преку механизам за пропагација.

Користени табели

За демонстрација на напредната тема е направена мини верзија на проектната база со основните табели потребни за Smart City Security сценариото:

• gragjanin
• vozilo
• kamera
• prekrsok
• kazna

Во централната база дополнително е креирана табелата:

• propagation_log

Табелата propagation_log служи за евиденција на сите промени што треба да се префрлат од централната во локалната база.

Улога на propagation_log табелата

Табелата propagation_log претставува меѓутабела во која се запишуваат сите нови записи што треба да се пропагираат.

Секој запис во оваа табела содржи информации за:

• табелата во која е направена промената
• типот на операција
• идентификаторот на записот
• payload со вредностите на записот
• статус на обработка

Статусот може да биде:

• PENDING - записот чека да биде префрлен во локалната база
• DONE - записот е успешно префрлен
• FAILED - настанала грешка при префрлањето

Trigger-и

Во централната база се креирани trigger-и за табелите:

• gragjanin
• vozilo
• kamera
• prekrsok
• kazna

Trigger-ите се активираат по секој INSERT во соодветната табела. Тие не ги префрлаат директно податоците во локалната база, туку автоматски додаваат запис во propagation_log со статус PENDING.

Со ова се овозможува асинхрона пропагација, бидејќи податоците прво се евидентираат, а потоа се обработуваат со посебна процедура.

Функција за логирање на промени

За trigger-ите е креирана функцијата:

fn_log_propagation_insert()

Оваа функција го зема новиот внесен ред преку NEW, го претвора во JSONB формат и го запишува во propagation_log.

Користењето на JSONB овозможува целиот запис да се зачува како payload, без потреба да се креира посебна log табела за секоја табела од системот.

Процедура за пропагација

За префрлање на податоците од централната во локалната база е креирана процедурата:

sp_propagiraj_vo_lokalna_baza()

Процедурата ги зема сите записи од propagation_log со статус PENDING. За секој запис проверува од која табела доаѓа, го чита payload-от и го внесува соодветниот запис во локалната база scs_local.

Ако внесувањето е успешно, статусот во propagation_log се менува во DONE. Ако настане грешка, статусот се менува во FAILED и се запишува пораката за грешка.

Користење на dblink

Бидејќи scs_central и scs_local се две различни PostgreSQL бази, за комуникација помеѓу нив е користен PostgreSQL extension:

dblink

Со dblink централната база може да извршува INSERT команди во локалната база.

Тек на пропагацијата

Целиот процес се одвива во следните чекори:

1. Се внесува нов запис во некоја од табелите во scs_central.
2. Trigger автоматски се активира по INSERT операцијата.
3. Trigger-от ја повикува функцијата fn_log_propagation_insert().
4. Во propagation_log се додава запис со статус PENDING.
5. Се повикува процедурата sp_propagiraj_vo_lokalna_baza().
6. Процедурата ги префрла податоците во scs_local.
7. Статусот во propagation_log се менува во DONE.

Заклучок

Со оваа напредна тема е имплементирана симулација на пропагација на податоци помеѓу централна и локална база. Решението користи trigger-и, log табела, stored procedure и dblink extension.

Овој пристап е соодветен за Smart City Security систем бидејќи овозможува централно внесување на податоци и нивна контролирана синхронизација кон локални бази, што е применливо во системи каде различни институции или зони треба да имаат локален пристап до релевантни податоци.