Changes between Version 1 and Version 2 of AdvancedTopic


Ignore:
Timestamp:
06/10/26 21:19:37 (10 hours ago)
Author:
231018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedTopic

    v1 v2  
    33== Опис на напредната тема ==
    44
    5 Како напредна тема во рамки на проектот '''SCS - Smart City Security''' е имплементирана локална симулација на пропагација на податоци помеѓу централна и локална база на податоци.
    6 
    7 Идејата на оваа имплементација е да се симулира реален Smart City систем во кој постои една централна база, на ниво на град или институција, и една локална база, која може да припаѓа на полициска станица, локална зона или друг организациски дел од системот.
    8 
    9 Поради ограничување да не се прават дополнителни измени на факултетската база, пропагацијата е имплементирана локално преку две PostgreSQL бази:
    10 
    11 * '''scs_central''' - централна база
    12 * '''scs_local''' - локална база
    13 
    14 Централната база ги прима новите податоци, додека локалната база ги добива податоците преку механизам за пропагација.
     5Како напредна тема во рамки на проектот '''SCS - Smart City Security''' е имплементиран механизам за пропагација на податоци помеѓу централна и локална база на податоци.
     6
     7Идејата на оваа имплементација е да се симулира Smart City Security систем во кој постои централна база, која ги содржи главните податоци за системот, и локална база, која добива копија од релевантните податоци.
     8
     9Во ваков систем централната база може да се користи на ниво на град или институција, додека локалната база може да припаѓа на одредена полициска станица, зона или организациски дел од системот.
     10
     11Целта на пропагацијата е при внесување нови податоци во централната база, тие контролирано да се префрлат и во локалната база.
    1512
    1613== Користени табели ==
    1714
    18 За демонстрација на напредната тема е направена мини верзија на проектната база со основните табели потребни за Smart City Security сценариото:
     15За демонстрација на пропагацијата се користат следните табели:
    1916
    2017* '''gragjanin'''
    … …  
    2825* '''propagation_log'''
    2926
    30 Табелата '''propagation_log''' служи за евиденција на сите промени што треба да се префрлат од централната во локалната база.
    31 
    32 == Улога на propagation_log табелата ==
    33 
    34 Табелата '''propagation_log''' претставува меѓутабела во која се запишуваат сите нови записи што треба да се пропагираат.
     27Оваа табела служи за евиденција на сите промени што треба да се префрлат од централната во локалната база.
     28
     29== Табела propagation_log ==
     30
     31Табелата '''propagation_log''' претставува лог табела во која се запишуваат сите нови записи што треба да се пропагираат.
    3532
    3633Секој запис во оваа табела содржи информации за:
    … …  
    4138* payload со вредностите на записот
    4239* статус на обработка
    43 
    44 Статусот може да биде:
     40* датум на креирање
     41* датум на обработка
     42* порака за грешка, доколку пропагацијата не успее
     43
     44{{{
     45CREATE TABLE propagation_log
     46(
     47log_id           SERIAL PRIMARY KEY,
     48tabela           VARCHAR(50) NOT NULL,
     49operacija        VARCHAR(20) NOT NULL,
     50zapis_id         VARCHAR(50) NOT NULL,
     51payload          JSONB NOT NULL,
     52status           VARCHAR(20) DEFAULT 'PENDING',
     53datum_kreiranje  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     54datum_obrabotka  TIMESTAMP,
     55poraka_greska    TEXT
     56);
     57}}}
     58
     59Статусот може да има една од следните вредности:
    4560
    4661* '''PENDING''' - записот чека да биде префрлен во локалната база
    4762* '''DONE''' - записот е успешно префрлен
    4863* '''FAILED''' - настанала грешка при префрлањето
     64
     65== Функција за логирање на промени ==
     66
     67За евидентирање на новите записи е креирана функцијата:
     68
     69{{{
     70fn_log_propagation_insert()
     71}}}
     72
     73Оваа функција се повикува преку trigger-и. Таа го зема новиот внесен ред преку '''NEW''', го претвора во JSONB формат и го запишува во '''propagation_log'''.
     74
     75{{{
     76CREATE OR REPLACE FUNCTION fn_log_propagation_insert()
     77RETURNS TRIGGER AS $$
     78DECLARE
     79pk_column TEXT;
     80pk_value  TEXT;
     81BEGIN
     82pk_column := TG_ARGV[0];
     83pk_value := to_jsonb(NEW) ->> pk_column;
     84
     85```
     86INSERT INTO propagation_log (
     87    tabela,
     88    operacija,
     89    zapis_id,
     90    payload
     91)
     92VALUES (
     93    TG_TABLE_NAME,
     94    'INSERT',
     95    pk_value,
     96    to_jsonb(NEW)
     97);
     98
     99RETURN NEW;
     100```
     101
     102END;
     103$$ LANGUAGE plpgsql;
     104}}}
     105
     106Функцијата како аргумент го добива името на primary key колоната за табелата на која се однесува trigger-от. На тој начин истата функција може да се користи за повеќе табели.
     107
     108Користењето на JSONB овозможува целиот запис да се зачува како payload, без потреба да се креира посебна log табела за секоја табела.
    49109
    50110== Trigger-и ==
    … …  
    60120Trigger-ите се активираат по секој INSERT во соодветната табела. Тие не ги префрлаат директно податоците во локалната база, туку автоматски додаваат запис во '''propagation_log''' со статус '''PENDING'''.
    61121
    62 Со ова се овозможува асинхрона пропагација, бидејќи податоците прво се евидентираат, а потоа се обработуваат со посебна процедура.
    63 
    64 == Функција за логирање на промени ==
    65 
    66 За trigger-ите е креирана функцијата:
    67 
    68 {{{
    69 fn_log_propagation_insert()
    70 }}}
    71 
    72 Оваа функција го зема новиот внесен ред преку '''NEW''', го претвора во JSONB формат и го запишува во '''propagation_log'''.
    73 
    74 Користењето на JSONB овозможува целиот запис да се зачува како payload, без потреба да се креира посебна log табела за секоја табела од системот.
     122{{{
     123DROP TRIGGER IF EXISTS trg_log_gragjanin_insert ON gragjanin;
     124DROP TRIGGER IF EXISTS trg_log_vozilo_insert ON vozilo;
     125DROP TRIGGER IF EXISTS trg_log_kamera_insert ON kamera;
     126DROP TRIGGER IF EXISTS trg_log_prekrsok_insert ON prekrsok;
     127DROP TRIGGER IF EXISTS trg_log_kazna_insert ON kazna;
     128
     129CREATE TRIGGER trg_log_gragjanin_insert
     130AFTER INSERT ON gragjanin
     131FOR EACH ROW
     132EXECUTE FUNCTION fn_log_propagation_insert('embg');
     133
     134CREATE TRIGGER trg_log_vozilo_insert
     135AFTER INSERT ON vozilo
     136FOR EACH ROW
     137EXECUTE FUNCTION fn_log_propagation_insert('registarska_oznaka');
     138
     139CREATE TRIGGER trg_log_kamera_insert
     140AFTER INSERT ON kamera
     141FOR EACH ROW
     142EXECUTE FUNCTION fn_log_propagation_insert('kamera_id');
     143
     144CREATE TRIGGER trg_log_prekrsok_insert
     145AFTER INSERT ON prekrsok
     146FOR EACH ROW
     147EXECUTE FUNCTION fn_log_propagation_insert('prekrsok_id');
     148
     149CREATE TRIGGER trg_log_kazna_insert
     150AFTER INSERT ON kazna
     151FOR EACH ROW
     152EXECUTE FUNCTION fn_log_propagation_insert('kazna_id');
     153}}}
     154
     155Со ова при секој нов INSERT во некоја од наведените табели автоматски се креира запис во '''propagation_log'''.
    75156
    76157== Процедура за пропагација ==
    … …  
    82163}}}
    83164
    84 Процедурата ги зема сите записи од '''propagation_log''' со статус '''PENDING'''. За секој запис проверува од која табела доаѓа, го чита payload-от и го внесува соодветниот запис во локалната база '''scs_local'''.
     165Процедурата ги зема сите записи од '''propagation_log''' со статус '''PENDING'''. За секој запис проверува од која табела доаѓа, го чита payload-от и го внесува соодветниот запис во локалната база.
    85166
    86167Ако внесувањето е успешно, статусот во '''propagation_log''' се менува во '''DONE'''. Ако настане грешка, статусот се менува во '''FAILED''' и се запишува пораката за грешка.
    87168
     169{{{
     170CREATE OR REPLACE PROCEDURE sp_propagiraj_vo_lokalna_baza()
     171LANGUAGE plpgsql
     172AS $$
     173DECLARE
     174r RECORD;
     175p JSONB;
     176conn TEXT := 'host=127.0.0.1 port=5430 dbname=scs_local user=postgres password=postgres';
     177BEGIN
     178FOR r IN
     179SELECT *
     180FROM propagation_log
     181WHERE status = 'PENDING'
     182ORDER BY log_id
     183LOOP
     184BEGIN
     185p := r.payload;
     186
     187```
     188        IF r.tabela = 'gragjanin' THEN
     189
     190            PERFORM dblink_exec(
     191                conn,
     192                format(
     193                    'INSERT INTO gragjanin
     194                     (embg, ime, prezime, adresa, grad, telefonski_broj, datum_ragjanje, pol)
     195                     VALUES (%L, %L, %L, %L, %L, %L, %L, %L)
     196                     ON CONFLICT (embg) DO NOTHING',
     197                    p ->> 'embg',
     198                    p ->> 'ime',
     199                    p ->> 'prezime',
     200                    p ->> 'adresa',
     201                    p ->> 'grad',
     202                    p ->> 'telefonski_broj',
     203                    p ->> 'datum_ragjanje',
     204                    p ->> 'pol'
     205                )
     206            );
     207
     208        ELSIF r.tabela = 'vozilo' THEN
     209
     210            PERFORM dblink_exec(
     211                conn,
     212                format(
     213                    'INSERT INTO vozilo
     214                     (registarska_oznaka, marka, model, boja, embg_sopstvenik)
     215                     VALUES (%L, %L, %L, %L, %L)
     216                     ON CONFLICT (registarska_oznaka) DO NOTHING',
     217                    p ->> 'registarska_oznaka',
     218                    p ->> 'marka',
     219                    p ->> 'model',
     220                    p ->> 'boja',
     221                    p ->> 'embg_sopstvenik'
     222                )
     223            );
     224
     225        ELSIF r.tabela = 'kamera' THEN
     226
     227            PERFORM dblink_exec(
     228                conn,
     229                format(
     230                    'INSERT INTO kamera
     231                     (kamera_id, lokacija, aktivna)
     232                     VALUES (%L, %L, %L)
     233                     ON CONFLICT (kamera_id) DO NOTHING',
     234                    p ->> 'kamera_id',
     235                    p ->> 'lokacija',
     236                    p ->> 'aktivna'
     237                )
     238            );
     239
     240        ELSIF r.tabela = 'prekrsok' THEN
     241
     242            PERFORM dblink_exec(
     243                conn,
     244                format(
     245                    'INSERT INTO prekrsok
     246                     (prekrsok_id, datum, opis, kamera_id, registarska_oznaka, embg_storitel)
     247                     VALUES (%L, %L, %L, %L, %L, %L)
     248                     ON CONFLICT (prekrsok_id) DO NOTHING',
     249                    p ->> 'prekrsok_id',
     250                    p ->> 'datum',
     251                    p ->> 'opis',
     252                    p ->> 'kamera_id',
     253                    p ->> 'registarska_oznaka',
     254                    p ->> 'embg_storitel'
     255                )
     256            );
     257
     258        ELSIF r.tabela = 'kazna' THEN
     259
     260            PERFORM dblink_exec(
     261                conn,
     262                format(
     263                    'INSERT INTO kazna
     264                     (kazna_id, prekrsok_id, datum, iznos_za_plakanje, rok_na_plakanje, status)
     265                     VALUES (%L, %L, %L, %L, %L, %L)
     266                     ON CONFLICT (kazna_id) DO NOTHING',
     267                    p ->> 'kazna_id',
     268                    p ->> 'prekrsok_id',
     269                    p ->> 'datum',
     270                    p ->> 'iznos_za_plakanje',
     271                    p ->> 'rok_na_plakanje',
     272                    p ->> 'status'
     273                )
     274            );
     275
     276        END IF;
     277
     278        UPDATE propagation_log
     279        SET status = 'DONE',
     280            datum_obrabotka = CURRENT_TIMESTAMP,
     281            poraka_greska = NULL
     282        WHERE log_id = r.log_id;
     283
     284    EXCEPTION WHEN OTHERS THEN
     285        UPDATE propagation_log
     286        SET status = 'FAILED',
     287            datum_obrabotka = CURRENT_TIMESTAMP,
     288            poraka_greska = SQLERRM
     289        WHERE log_id = r.log_id;
     290    END;
     291END LOOP;
     292```
     293
     294END;
     295$$;
     296}}}
     297
    88298== Користење на dblink ==
    89299
    90 Бидејќи '''scs_central''' и '''scs_local''' се две различни PostgreSQL бази, за комуникација помеѓу нив е користен PostgreSQL extension:
     300Бидејќи централната и локалната база се две одделни PostgreSQL бази, за комуникација помеѓу нив е користен PostgreSQL extension:
    91301
    92302{{{
    … …  
    94304}}}
    95305
    96 Со '''dblink''' централната база може да извршува INSERT команди во локалната база.
     306Овој extension овозможува од една база да се извршуваат SQL команди кон друга база.
     307
     308{{{
     309CREATE EXTENSION IF NOT EXISTS dblink;
     310}}}
     311
     312Во процедурата '''sp_propagiraj_vo_lokalna_baza()''' преку '''dblink_exec''' се извршуваат INSERT команди кон локалната база.
    97313
    98314== Тек на пропагацијата ==
    … …  
    100316Целиот процес се одвива во следните чекори:
    101317
    102 1. Се внесува нов запис во некоја од табелите во '''scs_central'''.
     3181. Се внесува нов запис во некоја од табелите во централната база.
    1033192. Trigger автоматски се активира по INSERT операцијата.
    1043203. Trigger-от ја повикува функцијата '''fn_log_propagation_insert()'''.
    1053214. Во '''propagation_log''' се додава запис со статус '''PENDING'''.
    1063225. Се повикува процедурата '''sp_propagiraj_vo_lokalna_baza()'''.
    107 6. Процедурата ги префрла податоците во '''scs_local'''.
     3236. Процедурата ги префрла податоците во локалната база.
    1083247. Статусот во '''propagation_log''' се менува во '''DONE'''.
    109325
     326== Пример за проверка ==
     327
     328За проверка на записите во лог табелата се користи следното барање:
     329
     330{{{
     331SELECT
     332log_id,
     333tabela,
     334operacija,
     335zapis_id,
     336status,
     337datum_kreiranje,
     338datum_obrabotka,
     339poraka_greska
     340FROM propagation_log
     341ORDER BY log_id;
     342}}}
     343
     344По успешна пропагација, записите во колоната '''status''' треба да имаат вредност '''DONE'''.
     345
     346За проверка дека податоците се префрлени во локалната база се прави SELECT од соодветните табели:
     347
     348{{{
     349SELECT * FROM gragjanin;
     350SELECT * FROM vozilo;
     351SELECT * FROM kamera;
     352SELECT * FROM prekrsok;
     353SELECT * FROM kazna;
     354}}}
     355
    110356== Заклучок ==
    111357
    112 Со оваа напредна тема е имплементирана симулација на пропагација на податоци помеѓу централна и локална база. Решението користи trigger-и, log табела, stored procedure и dblink extension.
    113 
    114 Овој пристап е соодветен за Smart City Security систем бидејќи овозможува централно внесување на податоци и нивна контролирана синхронизација кон локални бази, што е применливо во системи каде различни институции или зони треба да имаат локален пристап до релевантни податоци.
     358Со оваа напредна тема е имплементиран механизам за пропагација на податоци помеѓу централна и локална база. Решението користи trigger-и, log табела, stored procedure и dblink extension.
     359
     360Овој пристап е соодветен за Smart City Security систем бидејќи овозможува централно внесување на податоци и нивна контролирана синхронизација кон локални бази, што е применливо во системи каде различни институции или зони треба да имаат пристап до релевантни податоци.