Евалуација

Примери на индекси

Индекс врз колона што често се филтрира: s_date:

-- За филтрирање по датум
CREATE INDEX idx_policy_sdate ON project.policy(s_date);

-- За пребарување според email
CREATE INDEX idx_customer_email ON project.customer(email);

Во кодот, не морашаме да пишуваме USE INDEX (како во MySQL), бидејќи PostgreSQL автоматски ќе го користи индексот ако query planner процени дека е корисен.

WHERE p.s_date BETWEEN '{0}' AND '{1}' {2}
➡️ Овој дел директно ќе го искористи idx_policy_sdate

А ако во dropdown_params ставаме филтер:

AND c.email = 'test@example.com'


➡️ Овој дел ќе го искористи idx_customer_email.

Final:

string query = @"
    SELECT p.p_id, 
           CASE 
               WHEN v.pol_id IS NOT NULL THEN 'Auto Policy'
               WHEN t.pol_id IS NOT NULL THEN 'Travel Health'
               ELSE 'Property Policy'
           END AS PolicyType,
           c.name AS CustomerName,
           p.s_date AS StartDate,
           p.e_date AS EndDate,
           p.package AS PackageCode,
           pkg.title AS PackageTitle,
           pkg.total AS PackageTotal
    FROM project.policy p
    LEFT JOIN project.Auto_pol v ON p.p_id = v.pol_id
    LEFT JOIN project.Travel_pol t ON p.p_id = t.pol_id
    LEFT JOIN project.property_pol pp ON p.p_id = pp.pr_id 
    LEFT JOIN project.pol_dog pd ON p.p_id = pd.policy
    LEFT JOIN project.customer c ON pd.c_id = c.c_id
    LEFT JOIN project.package pkg ON p.package = pkg.code
    WHERE p.sdate BETWEEN @datef AND @datem
      AND (@dropdown IS NULL OR c.email = @dropdown);";

using var cmd = new NpgsqlCommand(query, sqlcon);
cmd.Parameters.AddWithValue("datef", datef);
cmd.Parameters.AddWithValue("datem", datem);
cmd.Parameters.AddWithValue("dropdown", (object)dropdown ?? DBNull.Value);

Имаме создаден индекс врз policy.sdate и customer.email.

Го користиме тој индекс во реален query (бидејќи филтрираме по тие полиња).

Примери за тригери

1. Проверка на датумска логика

   CREATE OR REPLACE FUNCTION project.validate_policy_dates()
   RETURNS TRIGGER AS $$
   BEGIN
       IF NEW.e_date <= NEW.s_date THEN
           RAISE EXCEPTION 'End date must be after start date. Start: %, End: %', NEW.s_date, NEW.e_date;
       END IF;
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_validate_policy_dates
   BEFORE INSERT OR UPDATE ON project.policy
   FOR EACH ROW EXECUTE FUNCTION project.validate_policy_dates();
   
   

2. Да спречи внесување на плаќање (Payment) кое е поголемо од осигурената вредност

   CREATE OR REPLACE FUNCTION project.validate_payment_amount()
   RETURNS TRIGGER AS $$
   DECLARE
       max_amount NUMERIC;
   BEGIN
       -- земи ја максималната дозволена вредност од пакетот
       SELECT pkg.total INTO max_amount
       FROM project.policy pol
       JOIN project.package pkg ON pol.package = pkg.code
       WHERE pol.p_id = NEW.policy;
   
       -- ако не постои поврзана полиса или пакет, кревај грешка
       IF max_amount IS NULL THEN
           RAISE EXCEPTION 'Cannot validate payment. No associated policy/package found for policy ID: %', NEW.policy;
       END IF;
   
       -- ако износот на плаќање е поголем од дозволениот, кревај грешка
       IF NEW.p_amount > max_amount THEN
           RAISE EXCEPTION 'Payment amount (%.2f) exceeds maximum allowed (%.2f) for policy %', NEW.p_amount, max_amount, NEW.policy;
       END IF;
   
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_validate_payment_amount
   BEFORE INSERT OR UPDATE ON project.payment
   FOR EACH ROW
   EXECUTE FUNCTION project.validate_payment_amount();
   
   

3. Формат на табели за колата

        CREATE OR REPLACE FUNCTION validate_license_plate() 
        RETURNS TRIGGER AS $$
        BEGIN
            -- Check if the license plate matches the expected format (XX-1234-XX)
            IF NEW.license_plate !~ '^[A-Z]{2}-\d{4}-[A-Z]{2}$' THEN
                RAISE EXCEPTION 'Invalid license plate format: %', NEW.license_plate;
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;


CREATE TRIGGER trigger_validate_license_plate
BEFORE INSERT OR UPDATE ON vehicles
FOR EACH ROW
EXECUTE FUNCTION validate_license_plate_format();

Погледи

--View
-- project.packageview source

CREATE OR REPLACE VIEW project.packageview
AS SELECT c.cov_amount,
    c.cov_type,
    p.title AS packagetitle,
    p.total AS packagetotal,
    p.valuet AS packagevalue,
        CASE
            WHEN p.type_pol = 3 THEN 'Auto Policy'::text
            WHEN p.type_pol = 1 THEN 'Travel Health'::text
            ELSE 'Property Policy'::text
        END AS policytype
   FROM project.covers c
     JOIN project.package p ON c.package::integer = p.code;
    
    

Трансакции

Трансакциите се применуваат во сите методи кои се користат за купување полиса во системот. Ако процесот на купување не биде успешен, промените не се зачувуваат во базата на податоци, осигурувајќи дека само целосно завршени и успешни трансакции ќе бидат внесени во системот.