README

A powerful extension for Nextras ORM that provides automatic database structure management for MySQL databases. This library allows you to define database tables and views using PHP code, with automatic migration support and structure synchronization.

Overview
Installation
Configuration
Architecture
Table Management
View Management
API Reference
Migration Support
Testing
Development with Docker
Best Practices
Examples

Overview

This library extends Nextras ORM with automatic database structure management capabilities. It allows you to:

Define database tables using fluent PHP API
Create database views with custom SQL queries
Automatically manage database migrations
Synchronize database structure with your code definitions
Handle foreign key relationships and constraints
Support for indexes, unique constraints, and full-text search

Installation

composer require attreid/orm-structure

Requirements

PHP 8.4 or higher
Nextras ORM 5.x
Nette DI 3.x or 4.x
MariaDB 10 database

Configuration

Add the extension to your config.neon:

extensions:
    structure: Attreid\OrmStructure\DI\StructureExtension

structure:
    autoManageDb: true  # Automatically manage database structure on application startup

Architecture

Core Components

Structure - Main orchestrator class that manages all mappers and database operations
TableMapper - Abstract base class for defining database tables
ViewMapper - Abstract base class for defining database views
Table - Represents a database table with columns, indexes, and constraints
View - Represents a database view with custom SQL queries
Column - Represents individual table columns with type definitions

Key Features

Automatic Structure Management: Tables and views are automatically created/updated based on your mapper definitions
Migration Support: Built-in migration system for handling schema changes
Type Safety: Strongly typed column definitions with fluent API
Relationship Management: Easy foreign key and many-to-many relationship handling
Index Management: Support for various index types (unique, fulltext, regular)

Table Management

Basic Table Definition

<?php

use Attreid\OrmStructure\TableMapper;
use Attreid\OrmStructure\Structure\Table;

class UserMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        // Primary key with auto-increment
        $table->addPrimaryKey('id')
            ->int()
            ->setAutoIncrement();
        
        // String columns
        $table->addColumn('username')
            ->varChar(50)
            ->setUnique();
        
        $table->addColumn('email')
            ->varChar(255)
            ->setUnique();
        
        $table->addColumn('password_hash')
            ->varChar(255);
        
        // Boolean column
        $table->addColumn('is_active')
            ->boolean()
            ->setDefault(true);
        
        // Timestamps
        $table->addColumn('created_at')
            ->timestamp();
        
        $table->addColumn('updated_at')
            ->timestamp(true); // with ON UPDATE CURRENT_TIMESTAMP
    }
}

Foreign Key Relationships

<?php

class PostMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')
            ->int()
            ->setAutoIncrement();
        
        // Foreign key to User table
        $table->addForeignKey('user_id', UserMapper::class);
        
        $table->addColumn('title')
            ->varChar(255);
        
        $table->addColumn('content')
            ->text();
        
        $table->addColumn('published_at')
            ->datetime()
            ->setDefault(null);
        
        // Self-referencing foreign key
        $table->addForeignKey('parent_id', $table)
            ->setDefault(null);
    }
}

Many-to-Many Relationships

<?php

class PostTagMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')
            ->int()
            ->setAutoIncrement();
        
        $table->addForeignKey('post_id', PostMapper::class);
        $table->addForeignKey('tag_id', TagMapper::class);
        
        // Composite unique constraint
        $table->addUnique('post_id', 'tag_id');
    }
}

// Alternative: Create relation table from PostMapper
class PostMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        // ... existing columns ...
        
        // Create many-to-many relation table
        $relationTable = $table->createRelationTable(TagMapper::class);
        $relationTable->addForeignKey('post_id', $table);
        $relationTable->addForeignKey('tag_id', TagMapper::class);
        $relationTable->setPrimaryKey('post_id', 'tag_id');
    }
}

Indexes and Constraints

<?php

class ProductMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')
            ->int()
            ->setAutoIncrement();
        
        $table->addColumn('name')
            ->varChar(255);
        
        $table->addColumn('description')
            ->text();
        
        $table->addColumn('price')
            ->decimal(10, 2);
        
        $table->addColumn('category_id')
            ->int();
        
        // Regular index
        $table->addKey('category_id');
        
        // Unique index
        $table->addUnique('name');
        
        // Full-text index
        $table->addFulltext('name', 'description');
        
        // Composite unique constraint
        $table->addUnique('category_id', 'name');
    }
}

Data Import and Initial Data

<?php

class CategoryMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->setDefaultDataFile(__DIR__ . '/import.sql');
        
        $table->addPrimaryKey('id')
            ->int()
            ->setAutoIncrement();
        
        $table->addColumn('name')
            ->varChar(100);
        
        $table->addColumn('slug')
            ->varChar(100)
            ->setUnique();
        
        // Add initial data
        $table->addOnCreate([
            ['name' => 'Technology', 'slug' => 'technology'],
            ['name' => 'Sports', 'slug' => 'sports'],
            ['name' => 'News', 'slug' => 'news'],
        ]);
    }
}

View Management

Basic View Definition

<?php

use Attreid\OrmStructure\ViewMapper;
use Nextras\Dbal\QueryBuilder\QueryBuilder;

class UserStatsMapper extends ViewMapper
{
    public function createDefinition(QueryBuilder $builder): void
    {
        $builder
            ->addSelect('u.id')
            ->addSelect('u.username')
            ->addSelect('COUNT(p.id) as post_count')
            ->addSelect('MAX(p.created_at) as last_post_date')
            ->from('user', 'u')
            ->leftJoin('post', 'p', 'p.user_id = u.id')
            ->groupBy('u.id, u.username');
    }

    protected function getPrimaryKey(): array
    {
        return ['id'];
    }

    protected function getMapping(): array
    {
        return [
            'postCount' => 'post_count',
            'lastPostDate' => 'last_post_date'
        ];
    }
}

Complex View with Window Functions

<?php

class ProductRankingMapper extends ViewMapper
{
    public function createDefinition(QueryBuilder $builder): void
    {
        $builder
            ->addSelect('ROW_NUMBER() OVER (ORDER BY p.price ASC) as rank')
            ->addSelect('p.id')
            ->addSelect('p.name')
            ->addSelect('p.price')
            ->addSelect('c.name as category_name')
            ->from('product', 'p')
            ->join('category', 'c', 'c.id = p.category_id')
            ->where('p.is_active = 1');
    }

    protected function getPrimaryKey(): array
    {
        return ['rank'];
    }

    protected function getMapping(): array
    {
        return [
            'categoryName' => 'category_name'
        ];
    }
}

API Reference

Table Class Methods

Column Management

addColumn(string $name): Column - Add a new column
addPrimaryKey(string $name): Column - Add primary key column
addForeignKey(string $name, string|Table $mapperClass, ?bool $onDelete = true, ?bool $onUpdate = false): Column - Add foreign key
removeColumn(string $name): void - Remove column

Index Management

addKey(string ...$names): self - Add regular index
addUnique(string ...$names): self - Add unique index
addFulltext(string ...$names): self - Add full-text index

Table Configuration

setEngine(string $engine): self - Set table engine (default: InnoDB)
setCharset(string $charset): self - Set character set (default: utf8mb4)
setCollate(string $collate): self - Set collation
setDefaultDataFile(string $file): self - Set SQL file for initial data
addOnCreate(array $data): void - Add initial data array

Relationship Management

createRelationTable(string|Table $tableName): Table - Create many-to-many relation table
setPrimaryKey(string ...$keys): self - Set composite primary key

Column Class Methods

Data Types

int(int $size = 11): self - Integer column
bigint(int $size = 20): self - Big integer column
varChar(int $size = 255, string $charset = null): self - Variable character column
text(string $charset = null): self - Text column
boolean(): self - Boolean column (tinyint(1))
decimal(int $total, int $decimal): self - Decimal column
datetime(): self - DateTime column
timestamp(bool $onUpdate = false): self - Timestamp column

Column Configuration

setDefault(mixed $default = false, bool $empty = false): self - Set default value
setAutoIncrement(): self - Set auto-increment
setUnique(): self - Add unique constraint
setKey(): self - Add index
setFulltext(): self - Add full-text index
renameFrom(string ...$names): self - Rename from existing columns

View Class Methods

Query Building

getQueryBuilder(): QueryBuilder - Get Nextras Dbal QueryBuilder instance

Migration Support

Column Renaming

<?php

class UserMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        // Rename column from 'login' to 'username'
        $table->addColumn('username')
            ->varChar(50)
            ->renameFrom('login');
    }
}

Data Migration

<?php

class PostMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        // ... existing columns ...
        
        // Migration for existing data
        if (!$table->exists) {
            $table->migration[] = function (Row $row, Connection $connection) {
                // Migrate old data format to new format
                if (isset($row->old_status)) {
                    $newStatus = $this->convertStatus($row->old_status);
                    $connection->query(
                        'UPDATE %table SET status = %s WHERE id = %i',
                        $table->name,
                        $newStatus,
                        $row->id
                    );
                }
            };
        }
    }
    
    private function convertStatus(string $oldStatus): string
    {
        return match($oldStatus) {
            'active' => 'published',
            'inactive' => 'draft',
            default => 'draft'
        };
    }
}

Testing

This library includes comprehensive unit tests to ensure reliability and correctness. The test suite covers all core functionality including table creation, view management, column definitions, and relationship handling.

Running Tests

Using Composer Scripts

# Run all tests
composer test

# Run tests with coverage report
composer test:coverage

# Stop on first failure
composer test:stop

# Run specific test categories
composer test:structure    # Structure-related tests
composer test:mapper       # Mapper tests
composer test:integration  # Integration tests

Direct PHP Execution

# Run all unit tests
php run-tests.php

# Run specific test file
php run-tests.php tests/Unit/TableTest.php

# Run tests with coverage
php run-tests.php --coverage

# Stop on first failure
php run-tests.php --stop-on-failure

Test Structure

The test suite is organized into several categories:

Unit Tests (tests/Unit/): Test individual components in isolation
Structure Tests (tests/Structure/): Test database structure operations
Integration Tests (tests/Integration/): Test component interactions

Test Coverage

The library maintains high test coverage across all major components:

✅ Column definitions and data types
✅ Table creation and modification
✅ View creation and query building
✅ Foreign key relationships
✅ Index and constraint management
✅ Migration support
✅ Mapper functionality

Writing Tests

When adding new features, ensure you include appropriate tests:

<?php

namespace Tests\Unit;

use Attreid\OrmStructure\Structure\Table;
use Attreid\OrmStructure\Structure\Column;

class MyFeatureTest extends \PHPUnit\Framework\TestCase
{
    public function testMyFeature(): void
    {
        $table = new Table('test_table');
        
        // Test your feature
        $table->addColumn('test_column')->varChar(100);
        
        // Assert expected behavior
        $this->assertCount(1, $table->getColumns());
    }
}

Development with Docker

This project includes Docker configuration for easy development setup. The Docker environment provides a consistent development experience with PHP 8.4, MariaDB 10, and all necessary extensions.

Prerequisites

Docker and Docker Compose installed
Git (for cloning the repository)

Quick Start

Clone the repository:

git clone <repository-url>
cd orm-structure

Start the development environment:
```
docker-compose up -d
```

Install dependencies:

docker-compose exec php composer install

Run tests:
```
docker-compose exec php composer test
```

Docker Services

The docker-compose.yml includes the following services:

php: PHP 8.4 with Apache, includes all necessary extensions
mariadb: MariaDB 10 database for testing
composer: Composer service for dependency management

Database Configuration

The MariaDB service is configured with:

Host: mariadb (from within containers) or localhost:3307 (from host)
Database: test_db
Username: test_user
Password: test_pass
Root Password: root

Development Commands

# Access PHP container
docker-compose exec php bash

# Run tests inside container
docker-compose exec php php run-tests.php

# Install/update dependencies
docker-compose exec php composer install
docker-compose exec php composer update

# Access MariaDB database
docker-compose exec mariadb mysql -u test_user -ptest_pass test_db

# View logs
docker-compose logs php
docker-compose logs mariadb

# Stop services
docker-compose down

# Rebuild containers
docker-compose up --build

Environment Variables

The PHP container is configured with the following environment variables:

DB_HOST=mysql
DB_PORT=3306
DB_NAME=test_db
DB_USER=test_user
DB_PASSWORD=test_pass

Volume Mounts

Project files are mounted to /app in the PHP container
MySQL data is persisted in a Docker volume
Composer cache is shared between runs

Troubleshooting

Port conflicts: If port 3307 is already in use, modify the port mapping in docker-compose.yml:

ports:
  - "3308:3306"  # Change 3307 to 3308

Permission issues: On some systems, you may need to fix file permissions:

docker-compose exec php chown -R www-data:www-data /app

Database connection issues: Ensure MariaDB is fully started before running tests:

docker-compose exec mariadb mysqladmin ping -h localhost

Best Practices

1. Naming Conventions

Use descriptive table and column names
Follow consistent naming patterns (snake_case for database, camelCase for entities)
Use meaningful foreign key names

2. Index Strategy

Add indexes for frequently queried columns
Use composite indexes for multi-column queries
Consider full-text indexes for search functionality

3. Data Types

Choose appropriate data types for your use case
Use decimal for monetary values
Use timestamp for automatic date/time management

4. Foreign Keys

Always define foreign key constraints for data integrity
Consider cascade options carefully
Use nullable foreign keys for optional relationships

5. Migration Safety

Test migrations on development data first
Use transactions for complex migrations
Always backup data before major schema changes

Examples

Complete E-commerce Example

<?php

// User table
class UserMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')->int()->setAutoIncrement();
        $table->addColumn('email')->varChar(255)->setUnique();
        $table->addColumn('password_hash')->varChar(255);
        $table->addColumn('first_name')->varChar(100);
        $table->addColumn('last_name')->varChar(100);
        $table->addColumn('is_active')->boolean()->setDefault(true);
        $table->addColumn('created_at')->timestamp();
    }
}

// Product table
class ProductMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')->int()->setAutoIncrement();
        $table->addColumn('name')->varChar(255);
        $table->addColumn('description')->text();
        $table->addColumn('price')->decimal(10, 2);
        $table->addColumn('stock_quantity')->int()->setDefault(0);
        $table->addColumn('is_active')->boolean()->setDefault(true);
        $table->addColumn('created_at')->timestamp();
        
        // Indexes
        $table->addKey('is_active');
        $table->addFulltext('name', 'description');
    }
}

// Order table
class OrderMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')->int()->setAutoIncrement();
        $table->addForeignKey('user_id', UserMapper::class);
        $table->addColumn('total_amount')->decimal(10, 2);
        $table->addColumn('status')->varChar(50)->setDefault('pending');
        $table->addColumn('created_at')->timestamp();
        
        // Indexes
        $table->addKey('user_id');
        $table->addKey('status');
        $table->addKey('created_at');
    }
}

// Order items (many-to-many with quantity)
class OrderItemMapper extends TableMapper
{
    public function createTable(Table $table): void
    {
        $table->addPrimaryKey('id')->int()->setAutoIncrement();
        $table->addForeignKey('order_id', OrderMapper::class);
        $table->addForeignKey('product_id', ProductMapper::class);
        $table->addColumn('quantity')->int();
        $table->addColumn('unit_price')->decimal(10, 2);
        
        // Composite unique constraint
        $table->addUnique('order_id', 'product_id');
    }
}

Analytics View Example

<?php

class SalesAnalyticsMapper extends ViewMapper
{
    public function createDefinition(QueryBuilder $builder): void
    {
        $builder
            ->addSelect('DATE(o.created_at) as sale_date')
            ->addSelect('COUNT(o.id) as order_count')
            ->addSelect('SUM(o.total_amount) as total_revenue')
            ->addSelect('AVG(o.total_amount) as average_order_value')
            ->addSelect('COUNT(DISTINCT o.user_id) as unique_customers')
            ->from('order', 'o')
            ->where('o.status = %s', 'completed')
            ->groupBy('DATE(o.created_at)')
            ->orderBy('sale_date DESC');
    }

    protected function getPrimaryKey(): array
    {
        return ['sale_date'];
    }

    protected function getMapping(): array
    {
        return [
            'orderCount' => 'order_count',
            'totalRevenue' => 'total_revenue',
            'averageOrderValue' => 'average_order_value',
            'uniqueCustomers' => 'unique_customers'
        ];
    }
}

This comprehensive documentation should provide everything needed for AI-assisted development with the Attreid ORM Structure library.

attreid / orm-structure

Maintainers

Details