Changes between Initial Version and Version 1 of WikiStart/Optimization/prva_verzija

Timestamp:

04/01/25 21:45:53 (10 days ago)

Author:

203206

Comment:

db2

WikiStart/Optimization/prva_verzija

@@ +1 @@
+=== Оптимизиран SQL-код, кој работи поедноставно и побрзо
+
+{{{
+create table account(
+    id serial primary key,
+    username varchar(255) not null,
+    email varchar(255) not null,
+    password varchar(255) not null,
+    balance decimal(15, 2) not null 
+);
+
+create table "transaction"(
+    id serial primary key,
+    amount decimal(15, 2) not null,
+    currency varchar(255) not null,
+    type varchar(50) not null,
+    timestamp timestamp default current_timestamp,
+    account_id int not null,
+    foreign key (account_id) references account(id)
+);
+
+create table transfer(
+    id serial primary key,
+    s_id int not null,
+    r_id int not null, 
+    foreign key(s_id) references account(id),
+    foreign key(r_id) references
+    account(id)
+
+);
+
+create table deposit(
+    id serial primary key,
+    account_id int not null,
+    amount decimal(15,2) not null,
+    timestamp timestamp default current_timestamp,
+    foreign key(account_id) references account(id)
+);
+
+create table withdraw(
+    id serial primary key,
+    account_id int not null,
+    amount decimal(15,2) not null,
+    timestamp timestamp default current_timestamp,
+    foreign key(account_id) references account(id)
+);
+
+create table transferred_money(
+    id serial primary key, 
+    s_account_id int not null, 
+    r_account_id int not null, 
+    t_id int not null, 
+    currency varchar(50) not null, 
+    foreign key (s_account_id) references account(id),
+    foreign key (r_account_id) references account(id),
+    foreign key (t_id) references transaction(id)
+);
+
+create or replace function after_transaction_trigger()
+returns trigger as $$
+begin
+
+        if new.type = 'Deposit' then
+                insert into Deposit(account_id,amount, timestamp)
+                values (new.account_id, new.amount, new.timestamp);
+        
+        elsif new.type = 'Withdrawal' then
+                insert into Withdraw(account_id,amount, timestamp)
+                values (new.account_id, new.amount, new.timestamp);
+        
+        elsif new.type like '(MKD) Transfer Out to%' then
+                insert into transferred_money(s_account_id, r_account_id, t_id, currency)
+                select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
+                        new.id, new.currency; 
+
+        elsif new.type like '(USD) Transfer Out to%' then
+                insert into transferred_money(s_account_id, r_account_id, t_id, currency)
+                select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
+                        new.id, new.currency;
+        
+        elsif new.type like '(EUR) Transfer Out to%' then
+                insert into transferred_money(s_account_id, r_account_id, t_id, currency)
+                select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
+                        new.id, new.currency;
+                        
+        elsif new.type like '(GBP) Transfer Out to%' then
+                insert into transferred_money(s_account_id, r_account_id, t_id, currency)
+                select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
+                        new.id, new.currency;
+
+        end if;
+        return new;
+end;
+$$ language plpgsql;
+
+create trigger after_transaction
+after insert on transaction
+for each row
+execute function after_transaction_trigger();
+
+
+CREATE OR REPLACE FUNCTION after_transaction_trigger2()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.type = 'Deposit' THEN
+        -- Log deposit as a self-transfer
+        INSERT INTO transfer(s_id, r_id)
+        VALUES (NEW.account_id, NEW.account_id);
+
+    ELSIF NEW.type = 'Withdrawal' THEN
+        -- Log withdrawal as a self-transfer
+        INSERT INTO transfer(s_id, r_id)
+        VALUES (NEW.account_id, NEW.account_id);
+
+    ELSIF NEW.type LIKE '(MKD) Transfer Out to%' OR 
+          NEW.type LIKE '(USD) Transfer Out to%' OR 
+          NEW.type LIKE '(EUR) Transfer Out to%' OR 
+          NEW.type LIKE '(GBP) Transfer Out to%' THEN
+          
+        -- Extract recipient username and insert into transfer table
+        INSERT INTO transfer(s_id, r_id)
+        SELECT NEW.account_id, 
+               (SELECT id FROM account 
+                WHERE username = TRIM(SUBSTRING(NEW.type FROM POSITION('Transfer Out to ' IN NEW.type) + 15)) 
+                LIMIT 1);
+
+    END IF;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER after_transaction2
+AFTER INSERT ON transaction
+FOR EACH ROW
+EXECUTE FUNCTION after_transaction_trigger2();
+
+
+
+select * from account;
+select * from "transaction";
+select * from transfer;
+select * from transferred_money;
+select * from deposit;
+select * from withdraw;
+
+SELECT 
+    tm.s_account_id AS sender_id,
+        a_s.username AS sender_username,
+        tm.r_account_id AS receiver_id,
+        a_r.username AS receiver_username,
+    tm.t_id,
+        t.timestamp,
+        t.amount
+    
+FROM 
+    transferred_money tm
+JOIN 
+    account a_s ON tm.s_account_id = a_s.id
+JOIN 
+    account a_r ON tm.r_account_id = a_r.id
+JOIN
+    "transaction" t ON tm.t_id = t.id;
+
+
+SELECT 
+    tm.s_account_id AS sender_id,
+        a_s.username AS sender_username,
+        tm.r_account_id AS receiver_id,
+        a_r.username AS receiver_username,
+    tm.t_id,
+        t.timestamp,
+        t.amount
+    
+FROM 
+    transferred_money tm
+JOIN 
+    account a_s ON tm.s_account_id = a_s.id
+JOIN 
+    account a_r ON tm.r_account_id = a_r.id
+JOIN
+    "transaction" t ON tm.t_id = t.id
+WHERE
+      timestamp<'2025-03-23 20:30:00.000000';
+
+SELECT 
+    tm.s_account_id AS sender_id,
+        a_s.username AS sender_username,
+        tm.r_account_id AS receiver_id,
+        a_r.username AS receiver_username,
+    tm.t_id,
+        t.timestamp,
+        t.amount,
+        tm.currency
+    
+FROM 
+    transferred_money tm
+JOIN 
+    account a_s ON tm.s_account_id = a_s.id
+JOIN 
+    account a_r ON tm.r_account_id = a_r.id
+JOIN
+    "transaction" t ON tm.t_id = t.id
+WHERE tm.currency='(EUR)'
+ORDER BY timestamp;
+
+SELECT 
+        max(t.amount) as maximum,
+        avg(t.amount) as "average",
+        min(t.amount) as minimum
+    
+FROM 
+    transferred_money tm
+JOIN 
+    account a_s ON tm.s_account_id = a_s.id
+JOIN 
+    account a_r ON tm.r_account_id = a_r.id
+JOIN
+    "transaction" t ON tm.t_id = t.id
+WHERE tm.currency='(USD)';
+
+}}}