Проект: база от данни и заявки

Миналия час решихте какво правите. Днес в края на часа базата съществува, пълна е с данни и основните справки към нея връщат верни резултати. Без това следващият час няма върху какво да стъпи — AI функцията чете точно тази база.

Схемата от заданието става скрипт за създаване — файл shema.sql, който прави таблиците от нулата. Той трябва да работи на чиста машина и да може да се пуска многократно, затова започва с DROP TABLE IF EXISTS в обратен ред на зависимостите.

PRAGMA foreign_keys = ON;            -- само за SQLite

DROP TABLE IF EXISTS ai_zayavki;
DROP TABLE IF EXISTS zaemania;
DROP TABLE IF EXISTS knigi;
DROP TABLE IF EXISTS avtori;

CREATE TABLE avtori (
  id     INTEGER PRIMARY KEY AUTOINCREMENT,
  ime    TEXT NOT NULL,
  strana TEXT
);

CREATE TABLE knigi (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  zaglavie TEXT NOT NULL,
  avtor_id INTEGER NOT NULL REFERENCES avtori(id) ON DELETE CASCADE,
  godina   INTEGER CHECK (godina BETWEEN 1800 AND 2026),
  zhanr    TEXT NOT NULL
);

CREATE TABLE zaemania (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  kniga_id     INTEGER NOT NULL REFERENCES knigi(id) ON DELETE CASCADE,
  chitatel_kod TEXT NOT NULL,          -- код, не име
  ot_data      TEXT NOT NULL,
  do_data      TEXT
);

CREATE TABLE ai_zayavki (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  vapros    TEXT NOT NULL,
  sql_text  TEXT,
  ok        INTEGER NOT NULL DEFAULT 0,
  sazdadeno TEXT NOT NULL DEFAULT (datetime('now'))
);

• NOT NULL, CHECK и външни ключове — базата да отхвърля грешните редове, преди тестовете да ги хванат.
• ON DELETE CASCADE там, където дъщерните редове нямат смисъл без родителя (урок 1).
• Лични данни не влизат: читателят е chitatel_kod, не име и клас.

Реални данни имате, когато темата е ваша: каталогът на клубна библиотека, разписанието на училището, публичен набор от урок 9. Във всички останали случаи данните са синтетични — построени от вас така, че да приличат на истински. Това е нормално и се пише в документацията.

from google import genai
from google.genai import types
from google.colab import userdata
import json

client = genai.Client(api_key=userdata.get("GEMINI_API_KEY"))

shema = {
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "zaglavie": {"type": "string"},
            "avtor":    {"type": "string"},
            "godina":   {"type": "integer"},
            "zhanr":    {"type": "string"},
        },
        "required": ["zaglavie", "avtor", "godina", "zhanr"],
    },
}

otg = client.models.generate_content(
    model="gemini-2.5-flash",
    contents="Измисли 30 книги за училищна библиотека. Жанрове: роман, поезия, "
             "фантастика, история. Години между 1950 и 2024. Автори — измислени "
             "имена, не реални писатели. Заглавията да са различни.",
    config=types.GenerateContentConfig(
        temperature=1.0,
        response_mime_type="application/json",
        response_schema=shema,
    ),
)
knigi = json.loads(otg.text)
print(len(knigi), knigi[0])

ZHANROVE = {"роман", "поезия", "фантастика", "история"}
problemi = []
for k in knigi:
    if not 1950 <= k["godina"] <= 2024:
        problemi.append(("година извън обхвата", k["zaglavie"]))
    if k["zhanr"] not in ZHANROVE:
        problemi.append(("непознат жанр", k["zhanr"]))
zaglavia = [k["zaglavie"] for k in knigi]
if len(set(zaglavia)) != len(zaglavia):
    problemi.append(("повтарящи се заглавия", len(zaglavia) - len(set(zaglavia))))
print("проблеми:", problemi or "няма")

Три до пет заявки, които приложението или потребителят реално ще ползва. Поне една с JOIN, поне една с GROUP BY, и един изглед, който скрива съединението и е удобен източник за справка или за AI функцията.

-- 1. Кои книги са навън в момента (JOIN)
SELECT k.zaglavie, a.ime AS avtor, z.chitatel_kod, z.ot_data
FROM zaemania z
JOIN knigi k  ON k.id = z.kniga_id
JOIN avtori a ON a.id = k.avtor_id
WHERE z.do_data IS NULL
ORDER BY z.ot_data;

-- 2. Колко книги има по жанр и коя е най-новата (GROUP BY)
SELECT zhanr, COUNT(*) AS broy, MAX(godina) AS nay_nova
FROM knigi
GROUP BY zhanr
ORDER BY broy DESC;

-- 3. Автори с повече от една книга (HAVING)
SELECT a.ime, COUNT(*) AS knigi
FROM avtori a JOIN knigi k ON k.avtor_id = a.id
GROUP BY a.id, a.ime
HAVING COUNT(*) > 1
ORDER BY knigi DESC, a.ime;

-- 4. Изглед — една „плоска“ таблица за справки и за модела
CREATE VIEW v_knigi AS
SELECT k.id, k.zaglavie, a.ime AS avtor, a.strana, k.godina, k.zhanr
FROM knigi k JOIN avtori a ON a.id = k.avtor_id;

Заявка, която „връща нещо“, не е проверена заявка. Проверена е тази, чийто резултат сте сметнали предварително на ръка върху малко данни и той съвпада. Методът е същият, с който в урок 5 хващахте грешките на AI асистента — и ще го ползвате пак в урок 30 за AI компонента.

1. Изберете подмножество, което се обхваща с поглед: 5 книги, 3 автори, 4 заемания.
2. За всяка справка сметнете на хартия какво трябва да излезе: брой редове и стойностите в 1–2 от тях.
3. Пуснете заявката върху същите данни. Съвпада ли редът, броят, подредбата?
4. Пробвайте граничен случай: жанр без книги, автор без книги, заемане без връщане.
5. Запишете таблицата — тя влиза в документацията.

• Схемата живее в shema.sql — скрипт, който създава базата от нулата и може да се пуска многократно.
• Ограниченията NOT NULL, CHECK и външните ключове са първата проверка на данните.
• Синтетичните данни са нормални: правдоподобни граници, съгласувани връзки, без реални хора; винаги се проверяват преди INSERT.
• Основните справки: поне една с JOIN, една с GROUP BY и един изглед.
• Заявка е проверена само срещу ръчно сметнато очакване върху малки данни.
• В края на часа: база, данни, справки и трите файла в споделената папка.