attreid/orm-structure

Nextras/ORM Structure extension

Installs: 60

Dependents: 0

Suggesters: 0

Security: 0

Stars: 0

Watchers: 1

Forks: 0

Open Issues: 0

pkg:composer/attreid/orm-structure

3.0.0 2025-10-16 07:43 UTC

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.

Table of Contents

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

  1. Structure - Main orchestrator class that manages all mappers and database operations
  2. TableMapper - Abstract base class for defining database tables
  3. ViewMapper - Abstract base class for defining database views
  4. Table - Represents a database table with columns, indexes, and constraints
  5. View - Represents a database view with custom SQL queries
  6. 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

  1. Clone the repository:

    git clone <repository-url>
    cd orm-structure
  2. Start the development environment:

    docker-compose up -d
  3. Install dependencies:

    docker-compose exec php composer install
  4. 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.