217 lines
13 KiB
Markdown
217 lines
13 KiB
Markdown
# Database Schema
|
||
|
||
**Last updated:** 2026-04-30
|
||
**Source:** Inferred from ORM models, controllers, install script, and raw query analysis.
|
||
|
||
---
|
||
|
||
## Connection
|
||
|
||
| Setting | Value |
|
||
|----------------|------------------------|
|
||
| Driver | `mysqli` |
|
||
| Host | `localhost` |
|
||
| Database | `host420804_db` |
|
||
| User | `host420804_db` |
|
||
| Character set | `utf8` |
|
||
| Table prefix | _(none)_ |
|
||
| Persistent | `FALSE` |
|
||
| Config file | `application/config/database.php` |
|
||
|
||
---
|
||
|
||
## Tables
|
||
|
||
### `user`
|
||
|
||
**Purpose:** Admin user accounts. Used for CMS login and authentication. Only admin-role users access the back-end panel.
|
||
|
||
**Model:** `application/models/user.php` → `User_Model extends ORM`
|
||
- `$table_name = 'user'`
|
||
- `$primary_val = 'username'` (allows lookup by username string in addition to numeric `id`)
|
||
|
||
**Columns:**
|
||
|
||
| Column | Type | Nullable | Notes |
|
||
|-----------------|----------------|----------|-------------------------------------------------------------------|
|
||
| `id` | INT (PK, AI) | NO | Primary key, auto-increment (Kohana ORM default) |
|
||
| `role` | VARCHAR | NO | Role name, e.g. `'sysadmin'`, `'admin'`. Stored as string. |
|
||
| `username` | VARCHAR(20) | NO | Unique login name. Validated: 3–20 chars, `[a-zA-Z0-9_.]` |
|
||
| `email` | VARCHAR(50) | NO | Unique email address. Validated: 5–50 chars. |
|
||
| `salt` | VARCHAR | NO | MD5 random salt used when hashing the password. |
|
||
| `sha1_password` | VARCHAR(40) | NO | `SHA1(salt . plaintext_password)` |
|
||
| `password_date` | DATETIME | YES | Timestamp of last password change. Format: `Y-m-d H:i:s` |
|
||
| `is_active` | TINYINT / BOOL | NO | `1` = active account, `0` = disabled. Checked on login. |
|
||
| `last_success` | DATETIME | YES | Timestamp of last successful login. Updated on each login. |
|
||
| `last_failed` | DATETIME | YES | Timestamp of last failed login attempt. Updated on bad password. |
|
||
|
||
**Relationships:** None declared in ORM. Standalone table.
|
||
|
||
**Key usages:**
|
||
- `application/controllers/admin/user.php` — login, logout, password change
|
||
- `application/controllers/install.php` — seed rows for `sysadmin` and `admin`
|
||
- `application/controllers/admin/force.php` — forced login (reads `username`, `last_success`, `last_failed`)
|
||
|
||
---
|
||
|
||
### `page`
|
||
|
||
**Purpose:** CMS content pages served on the public front-end. Each row is a named page (e.g. `o-firmie`, `kontakt`, `serwis`). Content is edited via the admin panel with a TinyMCE rich-text editor.
|
||
|
||
**Model:** `application/models/page.php` → `Page_Model extends ORM`
|
||
- `$table_names_plural = FALSE` → table name is `page` (not `pages`)
|
||
- Lookup by string `name` supported via `unique_key()`.
|
||
|
||
**Columns:**
|
||
|
||
| Column | Type | Nullable | Notes |
|
||
|-------------------|---------------|----------|-----------------------------------------------------------------------|
|
||
| `id` | INT (PK, AI) | NO | Primary key, auto-increment. |
|
||
| `name` | VARCHAR | NO | URL slug / unique identifier. e.g. `'o-firmie'`, `'kontakt'`. Unique.|
|
||
| `title` | VARCHAR(95) | YES | HTML `<title>` tag value and displayed page title. |
|
||
| `header` | VARCHAR(95) | YES | Heading shown inside the page `<h1>`. May differ from `title`. |
|
||
| `content` | MEDIUMTEXT | YES | Main HTML body. Edited via TinyMCE. Can be empty. |
|
||
| `meta_description`| TEXT | YES | `<meta name="description">` content. |
|
||
| `meta_keywords` | TEXT | YES | `<meta name="keywords">` content. |
|
||
| `parent_id` | INT (FK) | YES | Self-referential parent page ID. `NULL` = top-level page. |
|
||
|
||
**Relationships:**
|
||
- Self-referential: `parent_id` references `page.id` (used by `categories` helper for hierarchical menu rendering).
|
||
- No ORM `$has_many`/`$belongs_to` declared — relationships are resolved manually in the `categories` helper (`application/helpers/categories.php`).
|
||
|
||
**Seed pages (from install script):**
|
||
|
||
| `name` | `title` |
|
||
|-----------------------------------------------|---------------------------------------------------|
|
||
| `o-firmie` | O firmie |
|
||
| `urzadzenia-biurowe-monochromatyczne` | Urządzenia biurowe monochromatyczne |
|
||
| `urzadzenia-biurowe-kolorowe` | Urządzenia biurowe kolorowe |
|
||
| `urzadzenia-uslugowe-monochromatyczne` | Urządzenia usługowe monochromatyczne |
|
||
| `urzadzenia-uslugowe-kolorowe` | Urządzenia usługowe kolorowe |
|
||
| `urzadzenia-produkcyjne-monochromatyczne` | Urządzenia produkcyjne monochromatyczne |
|
||
| `urzadzenia-produkcyjne-kolorowe` | Urządzenia produkcyjne kolorowe |
|
||
| `drukarki-monochromatyczne` | Drukarki monochromatyczne |
|
||
| `drukarki-kolorowe` | Drukarki kolorowe |
|
||
| `powielacze-cyfrowe-riso-dlaczego-riso` | Powielacze cyfrowe RISO - dlaczego RISO? |
|
||
| `powielacze-cyfrowe-riso-urzadzenia` | Powielacze cyfrowe RISO - urządzenia |
|
||
| `plotery` | Plotery |
|
||
| `finansowanie` | Finansowanie |
|
||
| `serwis` | Serwis |
|
||
| `uslugi` | Usługi |
|
||
| `kontakt` | Kontakt |
|
||
| `szybki-kontakt` | Szybki kontakt |
|
||
|
||
**Key usages:**
|
||
- `application/controllers/admin/page.php` — edit page content (title, header, content, meta_*)
|
||
- `application/controllers/front/page.php` — public display; `show($name)` and `contact()` methods
|
||
- `application/helpers/categories.php` — renders hierarchical navigation menus using `id`, `name`, `title`, `parent_id`
|
||
- `application/views/front/page_show.php` — renders `header` and `content`
|
||
- `application/views/front/page_contact.php` — renders contact page `header` and `content`
|
||
|
||
---
|
||
|
||
### `gallery`
|
||
|
||
**Purpose:** Photo gallery albums. Each gallery has a name/slug and a collection of images. Sorted by creation date ascending.
|
||
|
||
**Model:** `application/models/gallery.php` → `Gallery_Model extends ORM`
|
||
- `$table_names_plural = FALSE` → table name is `gallery`
|
||
- `$has_many = array('gallery_images')` → links to `gallery_image` table via `gallery_id` FK
|
||
- `$sorting = array('created_at' => 'ASC')` → default sort order
|
||
|
||
**Columns:**
|
||
|
||
| Column | Type | Nullable | Notes |
|
||
|--------------|---------------|----------|----------------------------------------------------------------|
|
||
| `id` | INT (PK, AI) | NO | Primary key, auto-increment. |
|
||
| `name` | VARCHAR | NO | URL slug / unique identifier. Used for lookup. |
|
||
| `title` | VARCHAR | YES | Human-readable gallery name displayed in UI. |
|
||
| `created_at` | DATETIME | YES | Creation timestamp. Used for ASC sort order. |
|
||
|
||
> **Note:** Additional columns (e.g. `description`, `cover_image`) may exist but are not referenced in source code. The column set above is confirmed by code evidence.
|
||
|
||
**Relationships:**
|
||
- `has_many` → `gallery_image` via `gallery_image.gallery_id`
|
||
|
||
**Key usages:**
|
||
- `application/models/gallery.php` — ORM definition
|
||
- No admin controller found in the current codebase snapshot — gallery management may not yet be implemented or may exist outside the explored scope.
|
||
|
||
---
|
||
|
||
### `gallery_image`
|
||
|
||
**Purpose:** Individual images belonging to a gallery album. Sorted by `id` ascending (insertion order).
|
||
|
||
**Model:** `application/models/gallery_image.php` → `Gallery_Image_Model extends ORM`
|
||
- `$table_names_plural = FALSE` → table name is `gallery_image`
|
||
- `$belongs_to = array('gallery')` → foreign key `gallery_id` references `gallery.id`
|
||
- `$sorting = array('id' => 'ASC')`
|
||
|
||
**Columns:**
|
||
|
||
| Column | Type | Nullable | Notes |
|
||
|--------------|---------------|----------|--------------------------------------------------------------------|
|
||
| `id` | INT (PK, AI) | NO | Primary key, auto-increment. |
|
||
| `gallery_id` | INT (FK) | NO | Foreign key → `gallery.id`. Populated by Kohana ORM convention. |
|
||
| `filename` | VARCHAR | YES | Stored filename of the uploaded image (inferred from upload config).|
|
||
| `title` | VARCHAR | YES | Optional image caption / alt text. |
|
||
|
||
> **Note:** Column names for `gallery_image` (beyond `id` and `gallery_id`) are not directly referenced in any controller or view in the explored source. `filename` and `title` are inferred from typical Kohana gallery patterns and the upload config presence. Verify against live database.
|
||
|
||
**Relationships:**
|
||
- `belongs_to` → `gallery` via `gallery_id` → `gallery.id`
|
||
|
||
---
|
||
|
||
### `news`
|
||
|
||
**Purpose:** News/blog articles. Sorted by creation date descending (newest first). No admin controller was found in the explored source — may be partially implemented or planned.
|
||
|
||
**Model:** `application/models/news.php` → `News_Model extends ORM`
|
||
- `$table_names_plural = FALSE` → table name is `news`
|
||
- `$sorting = array('created_at' => 'DESC')`
|
||
- Unique key lookup by `name` slug supported.
|
||
|
||
**Columns:**
|
||
|
||
| Column | Type | Nullable | Notes |
|
||
|-------------------|---------------|----------|---------------------------------------------------------------|
|
||
| `id` | INT (PK, AI) | NO | Primary key, auto-increment. |
|
||
| `name` | VARCHAR | NO | URL slug / unique identifier. Unique constraint expected. |
|
||
| `title` | VARCHAR | YES | Article headline. |
|
||
| `content` | MEDIUMTEXT | YES | Article body HTML. |
|
||
| `created_at` | DATETIME | YES | Publication/creation timestamp. Used for DESC sort. |
|
||
|
||
> **Note:** Only `name` and `created_at` are confirmed by model code. `title` and `content` are inferred from structural similarity to the `page` table and common news patterns. Additional columns may exist. Verify against live database.
|
||
|
||
**Relationships:** None declared in ORM.
|
||
|
||
---
|
||
|
||
## ORM Conventions (Kohana 2.x)
|
||
|
||
These conventions apply to all tables above and govern how Kohana resolves columns automatically:
|
||
|
||
| Convention | Detail |
|
||
|-----------------------------|------------------------------------------------------------------------|
|
||
| Primary key | `id` (INT, auto-increment) — default for all models |
|
||
| Timestamps | `created_at` and `updated_at` — auto-populated when present |
|
||
| Foreign keys | `{related_model}_id` — e.g. `gallery_id` in `gallery_image` |
|
||
| `has_many` target table | Uses the child model's `$table_name` and looks up `{parent}_id` FK |
|
||
| `$table_names_plural = FALSE`| Overrides default pluralisation — table name matches model name exactly|
|
||
|
||
---
|
||
|
||
## Confidence Levels
|
||
|
||
| Table | Confidence | Basis |
|
||
|-----------------|------------|----------------------------------------------------------|
|
||
| `user` | HIGH | All columns directly referenced in controllers/install |
|
||
| `page` | HIGH | All columns directly referenced in controllers/views |
|
||
| `gallery` | MEDIUM | `id`, `name`, `created_at` confirmed; other cols inferred|
|
||
| `gallery_image` | LOW-MEDIUM | Only `id` and `gallery_id` confirmed; rest inferred |
|
||
| `news` | LOW-MEDIUM | Only `id`, `name`, `created_at` confirmed; rest inferred |
|
||
|
||
> Columns marked as inferred should be verified against the live `host420804_db` MySQL database or a mysqldump before any schema migrations are written.
|