124 lines
4.5 KiB
Markdown
124 lines
4.5 KiB
Markdown
# Database Schema
|
|
|
|
**Analysis Date:** 2026-04-27
|
|
|
|
## Overview
|
|
|
|
MySQL database on mysql7.ceti.pl. Custom DAL layer; no ORM. All migrations are manual SQL files with idempotent PHP runners in `_rejestracja/sql/`.
|
|
|
|
## Core Tables
|
|
|
|
### `mf_participant`
|
|
|
|
Primary registration table. One row per conference registrant.
|
|
|
|
| Column | Type | Notes |
|
|
|--------|------|-------|
|
|
| `id_mf_participant` | INT PK AUTO_INCREMENT | Primary key |
|
|
| `name` | VARCHAR | First name |
|
|
| `surname` | VARCHAR | Last name |
|
|
| `degree` | VARCHAR | Academic title/degree |
|
|
| `position` | VARCHAR | Job position |
|
|
| `phone` | VARCHAR | Phone number |
|
|
| `fax` | VARCHAR | Fax number |
|
|
| `email` | VARCHAR | Email address |
|
|
| `institution` | VARCHAR | Institution name |
|
|
| `address` | VARCHAR | Street address |
|
|
| `post_code` | VARCHAR | Postal code |
|
|
| `city` | VARCHAR | City |
|
|
| `nip` | VARCHAR | Tax ID (NIP) |
|
|
| `additional_info` | TEXT NULL | Free-text notes — added 2026-04-27 |
|
|
| `referat` | TINYINT | Presentation flag (0/1/2) |
|
|
| `poster` | TINYINT | Poster flag (0/1/2) |
|
|
| `message` | TEXT | Presentation topic |
|
|
| `autor` | VARCHAR | Co-authors |
|
|
| `participation_option` | VARCHAR | `full` / `one_day_lodging` / `one_day_no_lodging` |
|
|
| `participation_days` | VARCHAR | Selected days (for one-day option) |
|
|
| `diet` | TINYINT | Dietary preference (1=standard, 2=special) |
|
|
| `diet_special` | VARCHAR | Special diet description |
|
|
| `fee_full` | TEXT | Serialized fee selection array |
|
|
| `fee_one_day` | VARCHAR | One-day fee value |
|
|
| `price` | DECIMAL | Calculated total price |
|
|
| `agree1` | TINYINT | Data processing consent |
|
|
| `agree2` | TINYINT | Image consent |
|
|
| `status` | TINYINT | Payment status (1=no, 2=yes) |
|
|
| `location` | TINYINT | Conference location (1=aktualia, 2=PAN) |
|
|
| `date_add` | DATETIME | Registration timestamp |
|
|
|
|
**Model:** `_rejestracja/core/model/MfParticipant.class.php`
|
|
**DAL:** `_rejestracja/core/model/MfParticipantDAL.class.php`
|
|
|
|
### `mf_parameters`
|
|
|
|
Pricing/fee configuration. Each row is a fee option (accommodation, conference fee, etc.).
|
|
|
|
| Column | Type | Notes |
|
|
|--------|------|-------|
|
|
| `id_mf_parameters` | INT PK | Primary key |
|
|
| `name` | VARCHAR | Display name |
|
|
| `opis` | TEXT | Description |
|
|
| `price` | DECIMAL | Regular price (net) |
|
|
| `price_prom` | DECIMAL | Promotional/discount price |
|
|
| `price_progres` | TINYINT | Progressive pricing flag |
|
|
| `count_progres` | INT | Progressive pricing count threshold |
|
|
| `unit` | VARCHAR | Unit label |
|
|
| `link_id` | INT | Grouping link ID |
|
|
| `type` | TINYINT | Parameter type category |
|
|
| `sort` | INT | Display order |
|
|
| `publication` | TINYINT | Published/active flag |
|
|
|
|
**Model:** `_rejestracja/core/model/MfParameters.class.php`
|
|
**DAL:** `_rejestracja/core/model/MfParametersDAL.class.php`
|
|
|
|
### `mf_dictionary`
|
|
|
|
Key-value store for all translatable/editable text strings visible in the UI.
|
|
|
|
| Column | Type | Notes |
|
|
|--------|------|-------|
|
|
| `id_mf_dictionary` | INT PK | Primary key |
|
|
| `keyword` | VARCHAR | Lookup key (e.g., `registration_yes`) |
|
|
| `value` | TEXT | Display value |
|
|
| `location` | TINYINT | Site location (1/2) |
|
|
|
|
**Model:** `_rejestracja/core/model/MfDictionary.class.php`
|
|
**DAL:** `_rejestracja/core/model/MfDictionaryDAL.class.php`
|
|
**Usage:** `{translate word='key'}` Smarty plugin → `MfDictionaryDAL` lookup
|
|
|
|
### Supporting Tables
|
|
|
|
- `wp_setup` — scalar/list settings for the registration form (Phase 3 form settings)
|
|
- `mf_article` / `mf_article_content` — content management (HomeSite admin)
|
|
- Additional tables inferred from DAL files in `core/model/`
|
|
|
|
## Migration Pattern
|
|
|
|
**SQL file:** `_rejestracja/sql/YYYY-MM-DD-description.sql`
|
|
**Runner:** `_rejestracja/sql/apply-YYYY-MM-DD-description.php`
|
|
|
|
Runner pattern:
|
|
1. Check `INFORMATION_SCHEMA` (or similar) before altering
|
|
2. Requires `?run=YYYYMMDD` query param (or `--run` CLI flag) to execute
|
|
3. Prints "already exists / skipped" or "success" output
|
|
4. Web-accessible — see CONCERNS.md for security note
|
|
|
|
**Applied migrations:**
|
|
- `2026-04-24-registration-form-settings` — `wp_setup` table (Phase 3, Plan 01)
|
|
- `2026-04-27-additional-info-field` — `additional_info` column on `mf_participant` (Phase 3, Plan 02)
|
|
|
|
## DAL Query Pattern
|
|
|
|
```php
|
|
$dalData = MfParticipantDAL::GetDalDataObj();
|
|
$dalData->addCondition('location', 1);
|
|
$dalData->setSortBy('id_mf_participant DESC'); // value injected directly into ORDER BY
|
|
$results = MfParticipantDAL::GetResult($dalData);
|
|
```
|
|
|
|
`setSortBy()` value is injected verbatim into `ORDER BY` — include `DESC` in the string.
|
|
|
|
---
|
|
|
|
*Schema analysis: 2026-04-27*
|
|
*Update when tables or columns change*
|