= Изворен код

[https://github.com/dacc9/PharmaExport 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();
}}}

= Трансакции