Kostra projektu a modelyProject skeleton and models

Today the data model turns into real tables in the database and the first code of the project. You get a ready-made skeleton: Composer, one database connection, a parent class for lookup tables and an example for the main entity. You add the SQL for your tables, test data and the models for your topic. By the end of the lab you have a page in your web space on sigma.tuke.sk that lists the records of every table. The terms are in the table of terms.

Why the skeleton looks the way it does

The structure you get was not invented for this course. It is the shape of every modern PHP project: Laravel, Symfony, and every package you download from Packagist (the public repository of PHP libraries). Everywhere you find the same things:

• composer.json in the root – the project description and the rule where the classes are. Laravel has "App\\": "app/" in it, Symfony "App\\": "src/"; we have the same as Symfony.
• src/ (or app/) – the project’s own code, split into subfolders by role: Model/ for data, from week 5 Controller/ for decisions and View/ for output. Symfony and Laravel have folders literally called Controller and Model.
• vendor/ – third-party code, never touched by hand, always generated by Composer.
• one entry file index.php, which loads the autoloader and starts the application. In frameworks it is public/index.php; ours lies in the root because the web space on sigma.tuke.sk has no separate public folder, which is exactly why we protect the code with .htaccess.
• configuration outside the code – passwords and server settings in a file that is not in the repository (Laravel and Symfony use .env, we use config.php; the principle is the same).
• SQL in the repository – the database schema is part of the project, not something that exists only in the database. Frameworks call these migrations (week 9); schema.sql is their simplest form.

Why it is worth learning: whoever knows this shape opens somebody else’s Laravel or Symfony project and knows where to look. And the other way round: whoever has built the skeleton with their own hands understands what a framework does automatically. At the defence we will ask: why is vendor/ in .gitignore, why is config.php not in the repository, why does the class App\Model\X live exactly in src/Model/X.php.

From the data model to tables

Last week’s data model is a design. Tables are created with CREATE TABLE, one for each table. The order matters: a table with a foreign key can only be created once the table it points to exists. That is why lookup tables come first, then the main entity, and the pivot tables last. For dropping, the order is reversed.

Every table has ENGINE=InnoDB (the only table type in MariaDB that really checks foreign keys) and the utf8mb4 encoding (Slovak and emoji). The primary key is id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY: a non-negative integer the database assigns itself. A foreign key is written as FOREIGN KEY (difficulty_id) REFERENCES difficulties(id) ON DELETE RESTRICT; the ON DELETE rule is the one you justified in the README.

You do not type the SQL for the tables into Adminer and forget it. It is stored in the repository as the file sql/schema.sql, and the test data as sql/seed.sql. If you break your database, you run both files again and have a clean state. When somebody else opens your project, they build the same database from these two files. That is why schema.sql starts with DROP TABLE IF EXISTS for every table: the file can be run repeatedly.

More: W3Schools: SQL FOREIGN KEY

JOIN: two tables in one query

The table recipes has the column difficulty_id with a number. The user wants to see “medium”, not “2”. The text is in the table difficulties. JOIN tells the database to attach to every recipe row the difficulty row that belongs to it:

SELECT r.title, r.prep_time, d.name AS difficulty_name
FROM recipes r
JOIN difficulties d ON d.id = r.difficulty_id
ORDER BY r.id DESC;

How to read it:

• FROM recipes r – we start from the recipes; r is an alias so that we do not write recipes. before every column.
• JOIN difficulties d ON d.id = r.difficulty_id – to every recipe attach the difficulty whose id equals the recipe’s difficulty_id. The condition after ON is always “primary key of one table = foreign key of the other”.
• d.name AS difficulty_name – the column name from the difficulty is renamed so that it is not confused with another name in PHP; $row['difficulty_name'] then holds the text.
• The result has as many rows as there are recipes; each has the difficulty columns in addition.

It is the same JOIN you know: JOIN (precisely INNER JOIN) returns only recipes that have a difficulty – for us always all of them, because difficulty_id is NOT NULL. LEFT JOIN would also return recipes without a difficulty with NULL in difficulty_name; we use it in week 9 for statistics, where we also want lookup values with a count of zero. With pivot tables (M:N) three tables are joined in a row: recipes → recipe_ingredients → ingredients; that comes in week 5 with the recipe detail.

More: W3Schools: SQL JOIN

Composer and the vendor folder

Composer is a command-line program that manages third-party code in a PHP project. It is for PHP what npm is for JavaScript or pip for Python. It is already installed in Kubeflow; we never run it on sigma.tuke.sk, only the result is copied there.

Composer does two things:

1. It downloads libraries. When we need Twig in week 6, we neither write it ourselves nor download it by hand; a line "twig/twig": "^3.0" is added to composer.json and Composer downloads it together with everything Twig itself needs.
2. It generates the autoloader. The file vendor/autoload.php, which teaches PHP where to find classes (explained below under PSR-4). This week we use only this second thing.

Everything Composer needs to know is in the file composer.json in the project root. Ours has four parts:

Commands you will use:

The vendor/ folder is a result, not a source. It is not put into the repository (it is in .gitignore), because it can be regenerated from composer.json at any time – just as a compiled program is not put into a repository, only its source code. The consequence for our workflow: after every clone you run composer install, and since vendor/ is not created in the editor, you get it to sigma.tuke.sk with Upload Folder. composer install also creates composer.lock – a record of the exact versions installed; that one does belong in the repository so that everybody has the same versions.

More: getcomposer.org: Basic usage

Namespace: a class has an address

Every class has a name, for example RecipeModel. In a small project that is enough. In a bigger project with third-party libraries, two classes can have the same name (every library may have its own User class) and PHP would not know which one to use. That is why a class has, besides its name, an address, called a namespace. It is written at the top of the file:

namespace App\Model;

class RecipeModel
{
    // ...
}

The full name of this class is App\Model\RecipeModel: the class RecipeModel in the namespace App\Model. Read it like a path: project App, part Model, class RecipeModel. Two User classes in different namespaces no longer clash, because their full names differ.

When you want to use the class in another file, you write use with the full name at the top, and then the short name is enough:

use App\Model\RecipeModel;

$model = new RecipeModel();

Without use you would have to write new \App\Model\RecipeModel() in full every time. Both work; use is shorter and used everywhere.

PSR-4: the address is also the path to the file

Namespaces solve names. The remaining question is how PHP finds the file the class is in. Until now you did it by hand: require_once 'RecipeModel.php' in every file that needed the class. With ten classes that is ten lines; with a hundred it is unmaintainable.

PSR-4 is the convention that the namespace matches the folder and the class name matches the file name. In composer.json there is one rule:

"autoload": {
    "psr-4": { "App\\": "src/" }
}

It says: everything starting with App\ is in the src/ folder. The rest of the address is the path. So:

From this rule Composer generates vendor/autoload.php. That file registers a function in PHP which runs whenever the code uses a class PHP does not know yet: from the full name it builds the path to the file and loads it. You are left with a single require in the whole application, in index.php:

require __DIR__ . '/vendor/autoload.php';

From then on every class “finds itself”. All require_once lines disappear. Two rules you must keep, otherwise the autoloader will not find the file: one class = one file, and the file name matches the class name exactly, including capital letters (CategoryModel.php, not categorymodel.php).

More: PHP-FIG: PSR-4

Where the login details are and how the connection is made

The database login details – server, database name, user, password – are in one single place: the file config.php in the project root. It looks like this (with your details):

<?php
return [
    'host'   => 'localhost',        // the database runs on the same server as PHP
    'dbname' => 'jana_novakova',    // your database – the same as your Adminer login
    'user'   => 'jana_novakova',    // database login
    'pass'   => 'password-from-lab',
    'debug'  => true,               // true = errors shown in the browser (during development)
];

The file does nothing except return an array (return [...]). Whoever needs the details writes $c = require 'config.php'; and has them in $c['host'], $c['user'] and so on. In the whole application only one class needs them: Database.

Why a separate file: the password must not be in code that goes into the repository. That is why config.php is in .gitignore. The repository only has config.example.php – the same file with an empty password, so that everybody sees which items to fill in. After the clone you create config.php as a copy (cp config.example.php config.php), fill in the details and save; saving in the editor also gets it to sigma.tuke.sk. It never goes to GitHub.

The database connection is made in the class Database in src/Database.php. It is a singleton: a class that allows only one object to be created. The constructor is private, so new Database() from elsewhere is not possible; the only way is Database::getInstance(). On the first call it reads config.php, builds the DSN from it and creates the PDO; on every later call it returns the same PDO, which it keeps in the static property $instance. The whole application thus shares one connection, no matter where and how many times getInstance() is called – every model asks for it in its constructor.

A DSN (data source name) is the string that tells PDO where to connect: mysql:host=localhost;dbname=jana_novakova;charset=utf8mb4. It is built from the values in config.php; the user and the password go to PDO as separate parameters, not into the DSN. On sigma.tuke.sk the database is on the same server as PHP, hence host is localhost.

Models: a recap of objects and what happens in the skeleton

A model is a class that works with one table. Everything else in the application (pages, forms, later the API) reaches the data only through models, never with direct SQL. Let us recall what a class consists of, because all of it is used in the skeleton:

The interface ModelInterface says what every model must be able to do: getAll(), getById(), getCount(), delete(), describe(). It has no code, only signatures. What it is good for: index.php goes through all models in one loop and calls describe() and getAll() on each, without knowing which is which. PHP guarantees that every class with implements ModelInterface has those methods – otherwise the file would not even load.

Lookup models inherit from BaseModel. All lookup tables are alike: a table with id and name, always the same four queries. That is why those queries are written once, in the abstract class BaseModel, and every lookup model inherits them. The child has five lines of its own code: it sets $table and writes describe(). $table is protected, not private, precisely so that the child can set it and the parent’s methods can see it. BaseModel is abstract because without $table it makes no sense; describe() is abstract because every lookup table describes itself differently.

The main entity does not inherit. Its getAll() needs a JOIN so that instead of difficulty_id = 2 it also returns the text “medium”. So does getById(). Almost nothing of BaseModel would remain, and inheriting for the sake of two methods you override anyway is worse than writing them yourself. That is why the main entity implements ModelInterface directly. The rule: inherit when code is really shared, not just because it is possible.

Prepared statements (you know them from OOP): the SQL with a named placeholder :id goes to the database separately, the value separately in execute([':id' => $id]). The database never treats the value as SQL, so SQL injection is not possible. Queries without user input (SELECT * FROM … ORDER BY id) go through query(). The table name in {$this->table} is not user input – the class code sets it – so it may be in the string.

What must not be visible from the web

The whole project lies in your web space, so in principle every file can be opened in the browser. src/, vendor/ and sql/ are not pages, and config.php contains the password. The skeleton handles this in .htaccess: RedirectMatch 404 for the code folders and <Files "config.php"> Require all denied </Files> for the configuration. The server answers 404 or 403 before it reaches the file. Verified on sigma.tuke.sk; in task 3.7 you check it yourself.

The most common errors and what they mean

Example 1: the skeleton structure

The skeleton is available as kostra.zip; unpacked into the project folder it looks like this:

cv02/
├── .htaccess              src/, vendor/, sql/ and config.php are not reachable from the web
├── .gitignore             config.php, vendor/, .vscode/ do not go into the repository
├── composer.json          PSR-4 autoload: App\ -> src/
├── config.example.php     configuration example without the password (in the repository)
├── config.php             your configuration with the password (only in Kubeflow and on sigma.tuke.sk)
├── index.php              check page
├── sql/
│   ├── schema.sql         CREATE TABLE for all tables
│   └── seed.sql           test data
├── src/
│   ├── Database.php       App\Database – one PDO connection
│   └── Model/
│       ├── ModelInterface.php
│       ├── BaseModel.php  parent of lookup models
│       ├── LookupModel.php  lookup table example (delete once you have your own)
│       └── MainModel.php    main entity example (delete once you have your own)
└── vendor/                created by composer install (not in the repository)
    └── autoload.php

Example 2: schema.sql of the sample project

The recipe tables from last week, in the order they can be created. Note that DROP TABLE goes in the reverse order of CREATE TABLE.

-- Dropping in reverse order: pivot tables first, then the main entity, lookup tables last.
DROP TABLE IF EXISTS recipe_ingredients;
DROP TABLE IF EXISTS recipe_categories;
DROP TABLE IF EXISTS recipes;
DROP TABLE IF EXISTS units;
DROP TABLE IF EXISTS ingredients;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS difficulties;

-- Lookup table: recipe difficulty (easy, medium, hard).
CREATE TABLE difficulties (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lookup table: meal category (soup, main course, dessert).
CREATE TABLE categories (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lookup table: ingredient.
CREATE TABLE ingredients (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lookup table: unit of measure (g, ml, pcs).
CREATE TABLE units (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(30) NOT NULL,
    abbreviation VARCHAR(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Main entity: recipe.
CREATE TABLE recipes (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(120) NOT NULL,
    servings      TINYINT UNSIGNED NOT NULL DEFAULT 4,
    prep_time     SMALLINT UNSIGNED NOT NULL,          -- minutes
    instructions  TEXT NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    difficulty_id INT UNSIGNED NOT NULL,
    -- 1:N to a lookup table. RESTRICT: a difficulty cannot be deleted while a recipe uses it.
    FOREIGN KEY (difficulty_id) REFERENCES difficulties(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pivot table without attributes: recipe × category.
CREATE TABLE recipe_categories (
    recipe_id   INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (recipe_id, category_id),
    -- CASCADE: when a recipe is deleted, its links go with it.
    FOREIGN KEY (recipe_id)   REFERENCES recipes(id)    ON DELETE CASCADE,
    -- RESTRICT: a category cannot be deleted while a recipe uses it.
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pivot table with attributes: recipe × ingredient, with amount and unit.
CREATE TABLE recipe_ingredients (
    recipe_id     INT UNSIGNED NOT NULL,
    ingredient_id INT UNSIGNED NOT NULL,
    amount        DECIMAL(8,2) NOT NULL,
    note          VARCHAR(100) NULL,
    unit_id       INT UNSIGNED NOT NULL,
    PRIMARY KEY (recipe_id, ingredient_id),
    FOREIGN KEY (recipe_id)     REFERENCES recipes(id)     ON DELETE CASCADE,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE RESTRICT,
    FOREIGN KEY (unit_id)       REFERENCES units(id)       ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Example 3: composer.json

{
    "name": "tuke/it-projekt",
    "description": "Semester project – Information Technologies",
    "type": "project",
    "require": {
        "php": ">=8.1"
    },
    "autoload": {
        "psr-4": {
            "App\\": "src/"
        }
    },
    "config": {
        "platform": {
            "php": "8.1.2"
        }
    }
}

The only thing that changes in it during the semester is require: when we add Twig in week 6, a line is added and composer install downloads it.

Example 4: Database.php – one connection

<?php
declare(strict_types=1);

namespace App;

use PDO;

final class Database
{
    private static ?PDO $instance = null;

    // Private constructor: new Database() is not possible.
    private function __construct()
    {
    }

    public static function getInstance(): PDO
    {
        if (self::$instance === null) {
            $c   = require __DIR__ . '/../config.php';
            $dsn = "mysql:host={$c['host']};dbname={$c['dbname']};charset=utf8mb4";

            self::$instance = new PDO($dsn, $c['user'], $c['pass'], [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]);
        }

        return self::$instance;
    }
}

What each line in Database.php does:

• declare(strict_types=1); – PHP will not silently convert types (string to number etc.); a type error shows up immediately. We write it in every file.
• namespace App; – the class address; the full name is App\Database, the file is src/Database.php.
• use PDO; – PDO is a built-in PHP class without a namespace; since we are inside the namespace App, we have to say we mean “the global one”.
• final class – the class cannot be inherited from; for a singleton that is intentional.
• private static ?PDO $instance = null; – the only place where the connection lives. static = belongs to the class, not to an object; ?PDO = PDO or null; null at the start because the connection does not exist yet.
• private function __construct() {} – private constructor: new Database() from outside ends with an error. This is what makes the class a singleton.
• if (self::$instance === null) – self:: accesses a static property of the class itself. The connection is created only the first time.
• require __DIR__ . '/../config.php' – __DIR__ is the folder of this file (src/), .. goes one level up, where config.php is. require here returns the array from return.
• new PDO($dsn, $c['user'], $c['pass'], [...]) – the connection itself. The third and fourth parameters are the user and the password from config.php.
• ATTR_ERRMODE => ERRMODE_EXCEPTION – an SQL error throws an exception (PDOException), which you see in the browser when debug is on; without it errors would stay silent.
• ATTR_DEFAULT_FETCH_MODE => FETCH_ASSOC – rows from the database come as associative arrays ($row['title']), without numeric keys as well.
• ATTR_EMULATE_PREPARES => false – prepared statements are handled by the database, not by PHP; safer and the types match.
• return self::$instance; – always the same PDO.

Example 5: ModelInterface and BaseModel

<?php
declare(strict_types=1);

namespace App\Model;

interface ModelInterface
{
    public function getAll(): array;
    public function getById(int $id): ?array;
    public function getCount(): int;
    public function delete(int $id): bool;
    public function describe(): string;
}

<?php
declare(strict_types=1);

namespace App\Model;

use App\Database;
use PDO;

abstract class BaseModel implements ModelInterface
{
    protected PDO $pdo;
    protected string $table;   // set by every child

    public function __construct()
    {
        $this->pdo = Database::getInstance();
    }

    public function getAll(): array
    {
        return $this->pdo->query("SELECT * FROM {$this->table} ORDER BY id")->fetchAll();
    }

    public function getById(int $id): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM {$this->table} WHERE id = :id");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    public function getCount(): int
    {
        return (int) $this->pdo->query("SELECT COUNT(*) FROM {$this->table}")->fetchColumn();
    }

    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM {$this->table} WHERE id = :id");
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    abstract public function describe(): string;
}

What each line in BaseModel.php does:

• abstract class BaseModel implements ModelInterface – the parent of lookup models; it commits to the interface but cannot be created itself.
• protected PDO $pdo; – the connection all methods will use; protected so that children see it too.
• protected string $table; – the table name, deliberately without a value: every child fills it in.
• $this->pdo = Database::getInstance(); – in the constructor the model asks for the shared connection. The child does not write a constructor; it inherits this one.
• getAll(): query() runs a query without parameters, fetchAll() returns all rows as an array of arrays. {$this->table} is inserted into the string.
• getById(): prepare() prepares the query with :id, execute([':id' => $id]) supplies the value, fetch() returns one row or false when nothing was found. Hence return $row === false ? null : $row; – the method promises ?array, i.e. an array or null, not false.
• getCount(): fetchColumn() returns the first column of the first row – with COUNT(*) that is the number itself; (int) converts it from a string to an integer, because the method promises int.
• delete(): after execute(), rowCount() says how many rows were deleted; > 0 gives true/false. If another table uses the record with ON DELETE RESTRICT, the database refuses the delete and throws an exception – that is correct behaviour, not a bug.
• abstract public function describe(): string; – without a body; every child must write it.

Example 6: the sample project’s models

A lookup model has five lines of its own code. The main entity does not inherit, because its queries differ: a JOIN adds the difficulty name.

<?php
declare(strict_types=1);

namespace App\Model;

class DifficultyModel extends BaseModel
{
    protected string $table = 'difficulties';

    public function describe(): string
    {
        return 'Difficulty lookup table';
    }
}

<?php
declare(strict_types=1);

namespace App\Model;

use App\Database;
use PDO;

class RecipeModel implements ModelInterface
{
    private PDO $pdo;

    public function __construct()
    {
        $this->pdo = Database::getInstance();
    }

    // Recipes with the difficulty name, newest first.
    public function getAll(): array
    {
        $sql = "SELECT r.*, d.name AS difficulty_name
                FROM recipes r
                JOIN difficulties d ON d.id = r.difficulty_id
                ORDER BY r.id DESC";
        return $this->pdo->query($sql)->fetchAll();
    }

    public function getById(int $id): ?array
    {
        $sql = "SELECT r.*, d.name AS difficulty_name
                FROM recipes r
                JOIN difficulties d ON d.id = r.difficulty_id
                WHERE r.id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    public function getCount(): int
    {
        return (int) $this->pdo->query("SELECT COUNT(*) FROM recipes")->fetchColumn();
    }

    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM recipes WHERE id = :id");
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    public function describe(): string
    {
        return 'Recipes';
    }
}

What each line in the models does:

• DifficultyModel extends BaseModel – inherits the constructor, $pdo and the four methods. protected string $table = 'difficulties'; overrides the parent’s empty property with a value. describe() is the only method it must write, because it is abstract in the parent. The whole class has five lines of its own code – that is the point of inheritance.
• RecipeModel implements ModelInterface – does not inherit, so it has its own private PDO $pdo; (it may be private here, nobody inherits from it) and its own constructor, which asks for the connection just like BaseModel.
• SELECT r.*, d.name AS difficulty_name FROM recipes r JOIN difficulties d ON d.id = r.difficulty_id – r and d are table aliases. r.* = all columns of the recipe, d.name AS difficulty_name = plus the difficulty name under a readable name. JOIN … ON pairs the recipe row with the difficulty row whose id equals difficulty_id. In the result $row['difficulty_name'] is “medium” instead of the number 2.
• ORDER BY r.id DESC – newest recipes first; lookup tables use ORDER BY id, because insertion order is also display order.
• getById() with the same JOIN and WHERE r.id = :id – with aliases you must say which table’s id you mean.
• getCount() and delete() are the same as in BaseModel, only with a fixed table name.

In index.php the models are then used the same way, whether they inherit or not:

$models = [
    new \App\Model\DifficultyModel(),
    new \App\Model\CategoryModel(),
    new \App\Model\RecipeModel(),
];

What the skeleton’s index.php (the check page) does:

• require __DIR__ . '/vendor/autoload.php'; – the only require in the application; from this line on PHP finds every App\… class itself.
• $config = require __DIR__ . '/config.php'; and if ($config['debug']) { ini_set('display_errors', '1'); error_reporting(E_ALL); } – sigma.tuke.sk has error display switched off; during development we want to see errors, so we switch them on when debug is true in the configuration.
• use App\Database; and $pdo = Database::getInstance(); – the first and only creation of the connection; the models get the same one.
• $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN) – the list of tables in the database as a plain array of names; works even before you have any model.
• $models = [ new \App\Model\CategoryModel(), … ]; – the list of your models; you add one line per class. The full name with a leading \ because index.php is in no namespace and we used use only for Database.
• foreach ($models as $model) – for every model describe(), getCount() and the first five rows of getAll() via array_slice. The table header comes from array_keys($rows[0]) – the column names of the first row.
• htmlspecialchars(...) at every output – values from the database are not printed directly, so that a < in the data cannot break the page; protection against XSS, in more detail in week 6.

Example 7: the prompt for tables and test data

Below is the data model of my application (tables, columns, relationships, ON DELETE rules).
Create two files from it for MariaDB 10.6.

1. schema.sql
- At the beginning DROP TABLE IF EXISTS for all tables in the order they can be dropped
  (pivot tables, then the main entity, then lookup tables).
- CREATE TABLE in the order they can be created (lookup tables, main entity, pivot tables).
- Every table: ENGINE=InnoDB, DEFAULT CHARSET=utf8mb4, COLLATE=utf8mb4_unicode_ci.
- Primary key: id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY.
- Foreign keys as FOREIGN KEY ... REFERENCES ... with the ON DELETE rule exactly as in the model.
- A short SQL comment (-- ...) at every table and every foreign key saying what the table holds
  and why the given ON DELETE rule applies.
- Do not write CREATE DATABASE or USE.

2. seed.sql
- INSERT into every table, 3 to 5 rows, meaningful values for my topic.
- Insert order: lookup tables, main entity, pivot tables; foreign keys refer to existing ids.

Data model:
[PASTE THE DATA MODEL FROM YOUR README HERE]

Why the prompt is written this way:

• The drop and create order is spelled out, because the AI often gets it wrong with pivot tables and schema.sql then fails on a foreign key.
• ENGINE, CHARSET and the primary key form are prescribed so that all tables in the group look the same and can be compared with the sample.
• We ask for comments at ON DELETE because you explain every rule at the defence.
• CREATE DATABASE and USE are forbidden because you already have a database and another one could not be created on sigma.tuke.sk.
• Seed data with real values for your topic: the listing page has something to show and at the defence it is clear you understand the data.

Example 8: the prompt for models

Attached is the skeleton of a PHP project (namespace App\Model, PSR-4 autoload via Composer):
ModelInterface.php, BaseModel.php, LookupModel.php (lookup table example), MainModel.php
(main entity example) and my schema.sql.

Create model classes for my tables, one file each into src/Model/:
- for every lookup table a class following LookupModel: extends BaseModel, set $table,
  describe() returns a short description; add nothing else,
- for the main entity a class following MainModel: implements ModelInterface, getAll() and
  getById() with a JOIN to the lookup table connected by the 1:N relationship (return its name
  under a readable alias), getCount(), delete(), describe(); prepared statements with named
  placeholders.
Class names: English, singular, suffix Model (categories -> CategoryModel).
Every class uses PHP 8.1 syntax: declare(strict_types=1), parameter and return types.
A one-line comment above every method saying what it does.
At the end write the lines I should add to the $models array in index.php.

[PASTE THE CONTENTS OF THE FOUR SKELETON FILES AND SCHEMA.SQL HERE]

Why the prompt is written this way:

• You attach the skeleton, not a description of it: the AI sees the exact namespace, method names and style, and the result fits the skeleton without changes.
• “Add nothing else” for lookup tables stops the AI from writing its own getAll() into a five-line class, which would make inheritance pointless.
• The class naming rule is there because the file name must match the class name, otherwise the PSR-4 autoloader will not find the file.
• PHP 8.1 is stated explicitly because the AI otherwise likes newer constructs that do not run on sigma.tuke.sk.
• The $models lines at the end save typos in the full class names.

You also get the tasks as the assignment cv02 in Classroom 50; they are in the file README.md in your repository. The repository has only README.md at first; you add the skeleton in task 3.2.

Link to the assignment cv02: accept the assignment (applies to B. Stehlíková’s groups; other groups get the link from their own lab teacher)

code-server --install-extension Natizyskunk.sftp --install-extension yandeu.five-server --install-extension bmewburn.vscode-intelephense-client --install-extension mblode.twig-language-2

cd ~/web/it/cv02
curl -O https://beata.stehlikova.website.tuke.sk/it/kostra.zip
unzip kostra.zip && rm kostra.zip
composer install
cp config.example.php config.php