Changes between Version 2 and Version 3 of DatabaseProgramming


Ignore:
Timestamp:
06/07/26 23:17:05 (3 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v2 v3  
    5353
    5454
    55 Функција 3: цената треба автоматски да се менува кога ќе се случи нова трансакција
     55
     56
     57
     58== Процедури
     59
     60Процедура 1: Креирање на trade request од корисник
     61
     62{{{
     63CREATE OR REPLACE PROCEDURE create_trade_request(
     64    p_portfolio_id BIGINT,
     65    p_price FLOAT8,
     66    p_quantity INT,
     67    p_stock_symbol VARCHAR(20),
     68    p_type VARCHAR(10)
     69)
     70AS $$
     71DECLARE
     72    v_user_id BIGINT;
     73BEGIN
     74    SELECT user_id INTO v_user_id
     75    FROM portfolios
     76    WHERE id = p_portfolio_id;
     77
     78    IF p_type = 'BUY' THEN
     79        IF NOT check_portfolio_balance(p_portfolio_id, p_price, p_quantity) THEN
     80            RAISE EXCEPTION 'Недоволен баланс за купување. Portfolio ID: %', p_portfolio_id;
     81        END IF;
     82    END IF;
     83
     84    INSERT INTO trade_request (
     85        portfolio_id, price_per_unit, quantity,
     86        status, stock_symbol, timestamp, type
     87    )
     88    VALUES (
     89        p_portfolio_id, p_price, p_quantity,
     90        'OPEN', p_stock_symbol, NOW(), p_type
     91    );
     92
     93    RAISE NOTICE 'Trade request креиран за корисник % — % x % на %',
     94        v_user_id, p_type, p_quantity, p_stock_symbol;
     95END;
     96$$ LANGUAGE plpgsql;
     97}}}
     98
     99
     100Процедура 2: Одобрување на trade request од admin - креира broker order и settlement
     101
     102
     103{{{
     104CREATE OR REPLACE PROCEDURE approve_trade_request(
     105    p_trade_request_id BIGINT
     106)
     107AS $$
     108DECLARE
     109    v_tr trade_request%ROWTYPE;
     110    v_portfolio portfolios%ROWTYPE;
     111    v_broker_order_id BIGINT;
     112    v_stock_id BIGINT;
     113BEGIN
     114
     115    SELECT * INTO v_tr
     116    FROM trade_request
     117    WHERE id = p_trade_request_id AND status = 'OPEN';
     118
     119    IF NOT FOUND THEN
     120        RAISE EXCEPTION 'Trade request % не постои или не е OPEN', p_trade_request_id;
     121    END IF;
     122
     123    SELECT * INTO v_portfolio
     124    FROM portfolios
     125    WHERE id = v_tr.portfolio_id;
     126
     127
     128    SELECT id INTO v_stock_id
     129    FROM stock
     130    WHERE symbol = v_tr.stock_symbol;
     131
     132
     133    UPDATE trade_request
     134    SET status = 'CLOSED'
     135    WHERE id = p_trade_request_id;
     136
     137
     138    INSERT INTO broker_order (
     139        order_type, limit_price, quantity, status,
     140        executed_price, broker_fee, mse_reference_number,
     141        submitted_at, executed_at, trade_request_id
     142    )
     143    VALUES (
     144        v_tr.type, v_tr.price_per_unit, v_tr.quantity, 'EXECUTED',
     145        v_tr.price_per_unit, (random() * 50)::FLOAT8,
     146        'MSE-' || p_trade_request_id,
     147        v_tr.timestamp, NOW(), p_trade_request_id
     148    )
     149    RETURNING id INTO v_broker_order_id;
     150
     151
     152    INSERT INTO settlement_record (
     153        broker_order_id, user_id, stock_symbol, quantity,
     154        settled_price, total_cost, settlement_status, settled_at
     155    )
     156    VALUES (
     157        v_broker_order_id, v_portfolio.user_id, v_tr.stock_symbol,
     158        v_tr.quantity, v_tr.price_per_unit,
     159        v_tr.price_per_unit * v_tr.quantity,
     160        'SETTLED', NOW()
     161    );
     162
     163
     164    INSERT INTO trade_transaction (
     165        price, quantity, timestamp, type, origin, user_id, stock_id
     166    )
     167    VALUES (
     168        v_tr.price_per_unit, v_tr.quantity, NOW(),
     169        v_tr.type, 'INTERNAL', v_portfolio.user_id, v_stock_id
     170    );
     171
     172    RAISE NOTICE 'Trade request % одобрен — Broker Order % креиран',
     173        p_trade_request_id, v_broker_order_id;
     174END;
     175$$ LANGUAGE plpgsql;
     176}}}
     177
     178
     179
     180== Тригери
     181
     182Тригер 1: При SELL - провери дали корисникот има доволно акции во holdings
     183
     184
     185{{{
     186CREATE OR REPLACE FUNCTION check_holdings_before_sell()
     187RETURNS TRIGGER AS $$
     188DECLARE
     189    v_stock_id BIGINT;
     190    v_available_qty INT;
     191BEGIN
     192    IF NEW.type = 'SELL' THEN
     193        SELECT id INTO v_stock_id
     194        FROM stock WHERE symbol = NEW.stock_symbol;
     195
     196        SELECT quantity INTO v_available_qty
     197        FROM portfolio_holdings
     198        WHERE portfolio_id = NEW.portfolio_id
     199          AND stock_id = v_stock_id;
     200
     201        IF NOT FOUND OR v_available_qty < NEW.quantity THEN
     202            RAISE EXCEPTION 'Недоволно акции за продажба. Достапно: %, Побарано: %',
     203                COALESCE(v_available_qty, 0), NEW.quantity;
     204        END IF;
     205    END IF;
     206
     207    RETURN NEW;
     208END;
     209$$ LANGUAGE plpgsql;
     210
     211CREATE TRIGGER trg_check_holdings_before_sell
     212BEFORE INSERT ON trade_request
     213FOR EACH ROW
     214EXECUTE FUNCTION check_holdings_before_sell();
     215}}}
     216
     217
     218Тригер 2: цената треба автоматски да се менува кога ќе се случи нова трансакција
    56219
    57220{{{
    … …  
    76239}}}
    77240
    78 
    79 == Процедури
    80 
    81 Процедура 1: Креирање на trade request од корисник
    82 
    83 {{{
    84 CREATE OR REPLACE PROCEDURE create_trade_request(
    85     p_portfolio_id BIGINT,
    86     p_price FLOAT8,
    87     p_quantity INT,
    88     p_stock_symbol VARCHAR(20),
    89     p_type VARCHAR(10)
    90 )
    91 AS $$
    92 DECLARE
    93     v_user_id BIGINT;
    94 BEGIN
    95     SELECT user_id INTO v_user_id
    96     FROM portfolios
    97     WHERE id = p_portfolio_id;
    98 
    99     IF p_type = 'BUY' THEN
    100         IF NOT check_portfolio_balance(p_portfolio_id, p_price, p_quantity) THEN
    101             RAISE EXCEPTION 'Недоволен баланс за купување. Portfolio ID: %', p_portfolio_id;
    102         END IF;
    103     END IF;
    104 
    105     INSERT INTO trade_request (
    106         portfolio_id, price_per_unit, quantity,
    107         status, stock_symbol, timestamp, type
    108     )
    109     VALUES (
    110         p_portfolio_id, p_price, p_quantity,
    111         'OPEN', p_stock_symbol, NOW(), p_type
    112     );
    113 
    114     RAISE NOTICE 'Trade request креиран за корисник % — % x % на %',
    115         v_user_id, p_type, p_quantity, p_stock_symbol;
    116 END;
    117 $$ LANGUAGE plpgsql;
    118 }}}
    119 
    120 
    121 Процедура 2: Одобрување на trade request од admin - креира broker order и settlement
    122 
    123 
    124 {{{
    125 CREATE OR REPLACE PROCEDURE approve_trade_request(
    126     p_trade_request_id BIGINT
    127 )
    128 AS $$
    129 DECLARE
    130     v_tr trade_request%ROWTYPE;
    131     v_portfolio portfolios%ROWTYPE;
    132     v_broker_order_id BIGINT;
    133     v_stock_id BIGINT;
    134 BEGIN
    135 
    136     SELECT * INTO v_tr
    137     FROM trade_request
    138     WHERE id = p_trade_request_id AND status = 'OPEN';
    139 
    140     IF NOT FOUND THEN
    141         RAISE EXCEPTION 'Trade request % не постои или не е OPEN', p_trade_request_id;
    142     END IF;
    143 
    144     SELECT * INTO v_portfolio
    145     FROM portfolios
    146     WHERE id = v_tr.portfolio_id;
    147 
    148 
    149     SELECT id INTO v_stock_id
    150     FROM stock
    151     WHERE symbol = v_tr.stock_symbol;
    152 
    153 
    154     UPDATE trade_request
    155     SET status = 'CLOSED'
    156     WHERE id = p_trade_request_id;
    157 
    158 
    159     INSERT INTO broker_order (
    160         order_type, limit_price, quantity, status,
    161         executed_price, broker_fee, mse_reference_number,
    162         submitted_at, executed_at, trade_request_id
    163     )
    164     VALUES (
    165         v_tr.type, v_tr.price_per_unit, v_tr.quantity, 'EXECUTED',
    166         v_tr.price_per_unit, (random() * 50)::FLOAT8,
    167         'MSE-' || p_trade_request_id,
    168         v_tr.timestamp, NOW(), p_trade_request_id
    169     )
    170     RETURNING id INTO v_broker_order_id;
    171 
    172 
    173     INSERT INTO settlement_record (
    174         broker_order_id, user_id, stock_symbol, quantity,
    175         settled_price, total_cost, settlement_status, settled_at
    176     )
    177     VALUES (
    178         v_broker_order_id, v_portfolio.user_id, v_tr.stock_symbol,
    179         v_tr.quantity, v_tr.price_per_unit,
    180         v_tr.price_per_unit * v_tr.quantity,
    181         'SETTLED', NOW()
    182     );
    183 
    184 
    185     INSERT INTO trade_transaction (
    186         price, quantity, timestamp, type, origin, user_id, stock_id
    187     )
    188     VALUES (
    189         v_tr.price_per_unit, v_tr.quantity, NOW(),
    190         v_tr.type, 'INTERNAL', v_portfolio.user_id, v_stock_id
    191     );
    192 
    193     RAISE NOTICE 'Trade request % одобрен — Broker Order % креиран',
    194         p_trade_request_id, v_broker_order_id;
    195 END;
    196 $$ LANGUAGE plpgsql;
    197 }}}
    198 
    199 
    200 
    201 == Тригери
    202 
    203 Тригер 1: При SELL - провери дали корисникот има доволно акции во holdings
    204 
    205 
    206 {{{
    207 CREATE OR REPLACE FUNCTION check_holdings_before_sell()
    208 RETURNS TRIGGER AS $$
    209 DECLARE
    210     v_stock_id BIGINT;
    211     v_available_qty INT;
    212 BEGIN
    213     IF NEW.type = 'SELL' THEN
    214         SELECT id INTO v_stock_id
    215         FROM stock WHERE symbol = NEW.stock_symbol;
    216 
    217         SELECT quantity INTO v_available_qty
    218         FROM portfolio_holdings
    219         WHERE portfolio_id = NEW.portfolio_id
    220           AND stock_id = v_stock_id;
    221 
    222         IF NOT FOUND OR v_available_qty < NEW.quantity THEN
    223             RAISE EXCEPTION 'Недоволно акции за продажба. Достапно: %, Побарано: %',
    224                 COALESCE(v_available_qty, 0), NEW.quantity;
    225         END IF;
    226     END IF;
    227 
    228     RETURN NEW;
    229 END;
    230 $$ LANGUAGE plpgsql;
    231 
    232 CREATE TRIGGER trg_check_holdings_before_sell
    233 BEFORE INSERT ON trade_request
    234 FOR EACH ROW
    235 EXECUTE FUNCTION check_holdings_before_sell();
    236 }}}
    237 
    238 
    239 Тригер 2: цената треба автоматски да се менува кога ќе се случи нова трансакција
    240 
    241241{{{
    242242CREATE TRIGGER trg_update_stock_price
    … …  
    245245EXECUTE FUNCTION update_stock_price();
    246246}}}
     247
     248