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
Requires
- php: >= 8.4
- ext-json: *
- nette/di: ^3.2 || ^4.0
- nextras/orm: ^5.0
Requires (Dev)
- nette/tester: ~2.4
- tracy/tracy: ~2.8
- dev-master / 3.x-dev
- 3.0.0
- 2.3.2
- 2.3.1
- 2.3.0
- 2.2.3
- 2.2.2
- 2.2.1
- 2.2.0
- 2.1.0
- 2.0.4
- 2.0.3
- 2.0.2
- 2.0.1
- 2.0.0
- 1.11.0
- 1.10.1
- 1.10.0
- 1.9.1
- 1.9.0
- 1.8.4
- 1.8.3
- 1.8.2
- 1.8.1
- 1.8.0
- 1.7.6
- 1.7.5
- 1.7.4
- 1.7.3
- 1.7.2
- 1.7.1
- 1.7.0
- 1.6.13
- 1.6.12
- 1.6.11
- 1.6.10
- 1.6.9
- 1.6.8
- 1.6.7
- 1.6.6
- 1.6.5
- 1.6.4
- 1.6.3
- 1.6.2
- 1.6.1
- 1.6.0
- 1.5.12
- 1.5.11
- 1.5.10
- 1.5.9
- 1.5.8
- 1.5.7
- 1.5.6
- 1.5.5
- 1.5.4
- 1.5.3
- 1.5.2
- 1.5.1
- 1.5.0
- 1.4.0
- 1.3.6
- 1.3.5
- 1.3.4
- 1.3.3
- 1.3.2
- 1.3.1
- 1.3.0
- 1.2.7
- 1.2.6
- 1.2.5
- 1.2.4
- 1.2.3
- 1.2.2
- 1.2.1
- 1.2.0
- 1.1.1
- 1.1.0
- 1.0.13
- 1.0.12
- 1.0.11
- 1.0.10
- 1.0.9
- 1.0.8
- 1.0.7
- 1.0.6
- 1.0.5
- 1.0.4.1
- 1.0.4
- 1.0.3
- 1.0.2
- 1.0.1
- 1.0.0
This package is auto-updated.
Last update: 2025-10-16 07:51:44 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
- 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 columnaddPrimaryKey(string $name): Column
- Add primary key columnaddForeignKey(string $name, string|Table $mapperClass, ?bool $onDelete = true, ?bool $onUpdate = false): Column
- Add foreign keyremoveColumn(string $name): void
- Remove column
Index Management
addKey(string ...$names): self
- Add regular indexaddUnique(string ...$names): self
- Add unique indexaddFulltext(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 collationsetDefaultDataFile(string $file): self
- Set SQL file for initial dataaddOnCreate(array $data): void
- Add initial data array
Relationship Management
createRelationTable(string|Table $tableName): Table
- Create many-to-many relation tablesetPrimaryKey(string ...$keys): self
- Set composite primary key
Column Class Methods
Data Types
int(int $size = 11): self
- Integer columnbigint(int $size = 20): self
- Big integer columnvarChar(int $size = 255, string $charset = null): self
- Variable character columntext(string $charset = null): self
- Text columnboolean(): self
- Boolean column (tinyint(1))decimal(int $total, int $decimal): self
- Decimal columndatetime(): self
- DateTime columntimestamp(bool $onUpdate = false): self
- Timestamp column
Column Configuration
setDefault(mixed $default = false, bool $empty = false): self
- Set default valuesetAutoIncrement(): self
- Set auto-incrementsetUnique(): self
- Add unique constraintsetKey(): self
- Add indexsetFulltext(): self
- Add full-text indexrenameFrom(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) orlocalhost: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.