Changes between Initial Version and Version 1 of OtherTopics

Timestamp:

09/25/26 05:48:36 (6 days ago)

Author:

223306

Comment:

--

OtherTopics

@@ +1 @@
+= Other Topics =
+
+== SQL Performance ==
+
+Во оваа фаза беше направена анализа на перформансите на SQL пребарувања кои се користат во BiblioPremium. Анализата беше направена со EXPLAIN ANALYZE пред и после креирање на предложените индекси.
+
+=== 1. Анализа на продажба по книги ===
+
+Анализирано е комплексно пребарување кое ги прикажува вкупно продадените примероци и вкупниот приход по книга за завршени нарачки.
+
+{{{
+EXPLAIN ANALYZE
+SELECT
+    k.kniga_id,
+    k.naslov,
+    SUM(s.kolicina) AS vkupno_prodadeni,
+    SUM(s.kolicina * s.edinechna_cena) AS vkupen_prihod
+FROM project.knigi k
+JOIN project.sodrzi s
+    ON k.kniga_id = s.kniga_id
+JOIN project.naracki n
+    ON s.naracka_id = n.naracka_id
+WHERE n.status = 'ZAVRSENA'
+GROUP BY k.kniga_id, k.naslov
+ORDER BY vkupen_prihod DESC;
+}}}
+
+Пред креирање на индексот, execution plan покажа Sequential Scan (Seq Scan) на табелата naracki при филтрирањето според статусот ZAVRSENA.
+
+За оптимизација беше предложен и креиран следниот индекс:
+
+{{{
+CREATE INDEX idx_naracki_status
+ON project.naracki(status);
+}}}
+
+По креирањето на индексот повторно беше извршен EXPLAIN ANALYZE.
+
+PostgreSQL и понатаму избра Sequential Scan на табелата naracki и индексот idx_naracki_status не беше употребен.
+
+Причината е малиот број записи во табелата. За мала табела PostgreSQL проценува дека директното секвенцијално читање е поевтино од пристап преку индекс. Индексот може да стане покорисен со значително зголемување на бројот на нарачки.
+
+Заклучок: индексот е соодветен кандидат за поголема количина на податоци, но кај моменталната мала тест база оптимизаторот правилно избира Sequential Scan.
+
+=== 2. Препораки според расположение ===
+
+Беше анализирано пребарувањето за пронаоѓање достапни книги поврзани со избрано расположение.
+
+{{{
+EXPLAIN ANALYZE
+SELECT
+    k.kniga_id,
+    k.naslov,
+    k.cena,
+    k.kolicina_na_zaliha
+FROM project.knigi k
+JOIN project.povrzana_so ps
+    ON k.kniga_id = ps.kniga_id
+WHERE ps.raspolozenie_id = 1
+  AND k.kolicina_na_zaliha > 0
+ORDER BY k.naslov;
+}}}
+
+Execution plan покажа дека PostgreSQL веќе користи постоечки индекс:
+
+{{{
+Bitmap Index Scan on povrzana_so_pkey
+Index Cond: (raspolozenie_id = 1)
+}}}
+
+Измереното време на извршување беше приближно 0.132 ms.
+
+Поради тоа не беше креиран дополнителен индекс, бидејќи постоечкиот индекс веќе се користи при пребарувањето. Додавање непотребен индекс би создало дополнителен трошок при INSERT/UPDATE операции без значителна корист за моменталниот обем на податоци.
+
+=== 3. Пребарување книги по наслов ===
+
+Апликацијата овозможува пребарување на книги според наслов. Беше анализирано пребарување со делумно совпаѓање на текстот:
+
+{{{
+EXPLAIN ANALYZE
+SELECT
+    kniga_id,
+    naslov,
+    cena,
+    kolicina_na_zaliha
+FROM project.knigi
+WHERE LOWER(naslov) LIKE '%work%'
+ORDER BY naslov;
+}}}
+
+Пред креирањето на индексот беше добиен:
+
+{{{
+Seq Scan on knigi
+Execution Time: 0.075 ms
+}}}
+
+За поддршка на пребарување со шаблон од типот '%text%' беше активирана PostgreSQL екстензијата pg_trgm:
+
+{{{
+CREATE EXTENSION IF NOT EXISTS pg_trgm;
+}}}
+
+Потоа беше креиран GIN trigram индекс:
+
+{{{
+CREATE INDEX idx_knigi_naslov_trgm
+ON project.knigi
+USING gin (LOWER(naslov) gin_trgm_ops);
+}}}
+
+По креирањето на индексот повторно беше извршен истиот EXPLAIN ANALYZE.
+
+Резултат:
+
+{{{
+Seq Scan on knigi
+Execution Time: 0.063 ms
+}}}
+
+Индексот не беше употребен во execution plan. Табелата моментално содржи многу мал број книги, поради што PostgreSQL проценува дека Sequential Scan е поевтин.
+
+Иако измереното време после креирањето на индексот беше 0.063 ms наспроти 0.075 ms пред индексот, оваа разлика не се припишува на индексот бидејќи execution plan покажува дека индексот не бил употребен.
+
+Со значително поголем каталог на книги, trigram индексот е наменет да овозможи поефикасно пребарување со делумно совпаѓање на текст.
+
+== Security measures ==
+
+=== Application security ===
+
+Во апликацијата се применети повеќе мерки за заштита на пристапот до базата и податоците.
+
+'''1. Заштита од SQL Injection'''
+
+SQL пребарувањата користат параметризирани вредности преку psycopg2 placeholders (%s), наместо директно спојување на корисничкиот внес со SQL текстот.
+
+Пример:
+
+{{{
+cursor.execute("""
+    SELECT kniga_id, naslov, cena, opis, godina_izdavanje
+    FROM project.knigi
+    WHERE naslov ILIKE %s
+    ORDER BY kniga_id;
+""", (f"%{search}%",))
+}}}
+
+На овој начин внесот од корисникот се испраќа како SQL параметар, а не како дел од SQL командата.
+
+'''2. Автентикација и кориснички сесии'''
+
+По успешна најава во Flask session се зачувуваат идентификаторот и улогата на корисникот. Заштитените функционалности проверуваат дали постои најавен корисник.
+
+'''3. Авторизација според улога'''
+
+Административните страници и операции се достапни само за корисници со улога ADMIN.
+
+Пример:
+
+{{{
+if "korisnik_id" not in session:
+    return redirect(url_for("login"))
+
+if session.get("uloga") != "ADMIN":
+    return redirect(url_for("home"))
+}}}
+
+Оваа проверка се користи кај административниот панел и административните операции за книги, жанрови, категории, расположенија, нарачки и плаќања.
+
+'''4. Ограничување на пристап до кориснички податоци'''
+
+При прикажување на нарачките, податоците се филтрираат според korisnik_id од активната сесија.
+
+{{{
+WHERE n.korisnik_id = %s
+}}}
+
+И при прикажување на успешно купување се проверуваат и naracka_id и korisnik_id, со што најавениот корисник не може преку промена на ID во URL да пристапи до нарачка на друг корисник.
+
+'''5. Трансакциска заштита'''
+
+Кај операции кои менуваат повеќе табели, како купување книга и зачувување преференции, се користат commit и rollback.
+
+При успешна операција:
+
+{{{
+conn.commit()
+}}}
+
+При грешка:
+
+{{{
+conn.rollback()
+}}}
+
+Со ова се спречува делумно зачувување на податоци при неуспешна операција.
+
+'''6. Заштита на параметрите за поврзување'''
+
+Податоците за поврзување со PostgreSQL и SSH (корисничко име, лозинка и други параметри) се земаат од environment variables преку .env датотека, наместо да бидат директно запишани во изворниот код.
+
+=== Database security ===
+
+Апликацијата пристапува до проектната PostgreSQL база преку автентициран database account.
+
+Со:
+
+{{{
+SELECT current_user;
+}}}
+
+беше потврдено дека конекцијата се извршува преку проектниот database owner account.
+
+Пристапот до PostgreSQL се реализира преку SSH tunnel, а credentials се вчитуваат од environment variables.
+
+SQL командите кои содржат вредности добиени од апликацијата користат параметризирани SQL изрази. Во апликацијата не се користи динамичко составување SQL команди преку директно додавање на кориснички внес.
+
+Дополнително, интегритетот на податоците се поддржува со примарни и надворешни клучеви, ограничувања и релации дефинирани во базата.
+
+=== Ограничувања и можни идни подобрувања ===
+
+Во моменталниот прототип корисничките лозинки не се хешираат пред зачувување. Во продукциска верзија треба да се користи сигурен password hashing механизам.
+
+Flask secret key моментално е дефиниран во апликацискиот код. Во продукциска околина треба да се премести во environment variable и да се користи силна случајно генерирана вредност.
+
+Исто така, апликацијата моментално се поврзува преку проектниот database owner account. Дополнително подобрување би било креирање посебна database улога за апликацијата со минималните потребни привилегии.
+
+== Other developments ==
+
+Во рамките на претходните фази се имплементирани дополнителни механизми кои придонесуваат за перформанси, интегритет и сигурност на системот:
+
+* connection pooling за повторна употреба на PostgreSQL конекции;
+* трансакции со commit/rollback;
+* trigger за проверка и намалување на залиха;
+* database views за продажба и следење на ниска залиха;
+* database function за препорака на книги според расположение;
+* административна авторизација според улога;
+* проверки пред бришење на податоци кои се поврзани со други записи.