Изворен код

​GitHub линк

Индекси

USERS → email

Бидејќи полето email често ќе се користи за пребарување или автентикација, креирањето индекс на ова поле ќе ја забрза побарувачката:

CREATE INDEX idx_users_email ON USERS(email);

CLIENTS → name

Композитен индекс на name и country може да ја забрза побарувачката на клиенти што филтрираат или сортираат по овие колони:

CREATE INDEX idx_clients_name_country ON CLIENTS (name, country);

BATCHES → batch_code

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

CREATE INDEX idx_batches_batch_code ON BATCHES (batch_code);

ORDERS → status

Креирање индекс на status во табелата ORDERS може да ја забрза побарувачката кога се пребаруваат нарачки според статусот (на пример, "pending", "completed").

CREATE INDEX idx_orders_status ON ORDERS (status);

PAYMENTS → payment_status

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

CREATE INDEX idx_payments_payment_status ON PAYMENTS (payment_status);

Тригери

Негативни/нулти количини и цени во ORDER_BATCHES

Кога се извршува INSERT или UPDATE на ORDER_BATCHES, осигурај quantity > 0 и price_per_unit >= 0; пресметај total_price = quantity * price_per_unit и постави timestamps.

CREATE OR REPLACE FUNCTION trg_order_batches_validate() RETURNS trigger AS $
BEGIN
  IF NEW.quantity IS NULL OR NEW.quantity <= 0 THEN
    RAISE EXCEPTION 'quantity must be > 0';
  END IF;
  IF NEW.price_per_unit IS NULL OR NEW.price_per_unit < 0 THEN
    RAISE EXCEPTION 'price_per_unit must be >= 0';
  END IF;
  NEW.total_price := NEW.quantity * NEW.price_per_unit;
  NEW.updated_at := NOW();
  IF TG_OP = 'INSERT' THEN
    NEW.created_at := NOW();
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER order_batches_validate
BEFORE INSERT OR UPDATE ON order_batches
FOR EACH ROW EXECUTE FUNCTION trg_order_batches_validate();

Нарачки со истечени серии

Пред INSERT или UPDATE на ORDER_BATCHES, провери дали повиканата BATCH не е истечена според датумот на нарачката (orders.date). Се фрла exception ако expiration_date < orders.date.

CREATE OR REPLACE FUNCTION trg_order_batches_no_expired() RETURNS trigger AS $
DECLARE
  ord_date DATE;
  batch_exp DATE;
BEGIN
  SELECT date INTO ord_date FROM orders WHERE id = NEW.order_id;
  SELECT expiration_date INTO batch_exp FROM batches WHERE id = NEW.batch_id;
  IF ord_date IS NULL OR batch_exp IS NULL THEN
    RAISE EXCEPTION 'order or batch not found';
  END IF;
  IF batch_exp < ord_date THEN
    RAISE EXCEPTION 'cannot add expired batch to order (batch % expired %)', NEW.batch_id, batch_exp;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER order_batches_no_expired
BEFORE INSERT OR UPDATE ON order_batches
FOR EACH ROW EXECUTE FUNCTION trg_order_batches_no_expired();

Бришење на PRODUCERS/PRODUCTS/BATCHES кои се реферирани

Забрането бришење на:

• producer ако постојат products
• product ако постојат batches
• batch ако е искористен во order_batches.

CREATE OR REPLACE FUNCTION trg_prevent_fk_deletes() RETURNS trigger AS $
BEGIN
  IF TG_TABLE_NAME = 'producers' THEN
    IF EXISTS (SELECT 1 FROM products WHERE producer_id = OLD.id) THEN
      RAISE EXCEPTION 'cannot delete producer with products';
    END IF;
  ELSIF TG_TABLE_NAME = 'products' THEN
    IF EXISTS (SELECT 1 FROM batches WHERE product_id = OLD.id) THEN
      RAISE EXCEPTION 'cannot delete product with batches';
    END IF;
  ELSIF TG_TABLE_NAME = 'batches' THEN
    IF EXISTS (SELECT 1 FROM order_batches WHERE batch_id = OLD.id) THEN
      RAISE EXCEPTION 'cannot delete batch referenced in orders';
    END IF;
  END IF;
  RETURN OLD;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_delete_producers
BEFORE DELETE ON producers
FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();

CREATE TRIGGER prevent_delete_products
BEFORE DELETE ON products
FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();

CREATE TRIGGER prevent_delete_batches
BEFORE DELETE ON batches
FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();

Автоматско ажурирање на ORDER.status според плаќања

Кога PAYMENT се означува како paid (payment_status = 'paid' и payment_date не е NULL), провери SUM(payments.amount) >= orders.total_amount, а потоа постави orders.status = 'paid'.

CREATE OR REPLACE FUNCTION trg_payments_update_order_status() RETURNS trigger AS $
DECLARE
  paid_sum NUMERIC;
  ord_total NUMERIC;
BEGIN
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;

  SELECT COALESCE(SUM(amount),0) INTO paid_sum FROM payments WHERE order_id = NEW.order_id AND payment_status = 'paid';
  SELECT COALESCE(total_amount,0) INTO ord_total FROM orders WHERE id = NEW.order_id;

  IF paid_sum >= ord_total AND ord_total > 0 THEN
    UPDATE orders SET status = 'paid' WHERE id = NEW.order_id;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER payments_update_order_status
AFTER INSERT OR UPDATE ON payments
FOR EACH ROW EXECUTE FUNCTION trg_payments_update_order_status();

Известувања за промена на статус на нарачка

Известување кога нарачката го променила статусот (ORDER.status) — испраќа RAISE NOTICE со ордедена порака

CREATE OR REPLACE FUNCTION trg_order_status_change_fun()
RETURNS trigger AS $
BEGIN
  IF TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status THEN
    RAISE NOTICE 'Order % changed it's status: % → %', NEW.id, OLD.status, NEW.status;
    IF NEW.status = 'shipped' OR NEW.status = 'delivered' THEN
      RAISE NOTICE 'Good news! Order % is %!', NEW.id, NEW.status;
    END IF;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_order_status_change ON orders;
CREATE TRIGGER trg_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION trg_order_status_change_fun();

Трансакции