MagicMapper - Dynamic Schema-Based Table Management

Overview

The MagicMapper is a standalone service that provides dynamic table creation and management based on JSON schema definitions. Instead of storing all objects in a single generic table, it creates dedicated tables for each schema, providing significant performance improvements and better data organization.

Key Features

🚀 Dynamic Table Creation

Automatically creates database tables based on JSON schema properties
Supports all major database systems (MySQL, MariaDB, PostgreSQL)
Intelligent SQL type mapping from JSON schema types

📊 Metadata Integration

All ObjectEntity metadata columns included with underscore prefix
Maintains compatibility with existing RBAC and multitenancy systems
Preserves all existing functionality (files, relations, authorization, etc.)

⚡ Performance Optimizations

Schema-specific tables (faster queries, better indexing)
Automatic index creation for frequently filtered fields
Optimized storage with proper SQL column types
Better database query planning and statistics

🔄 Schema Evolution

Automatic table updates when schemas change
Version tracking for change detection
Safe column additions without data loss

🔍 Advanced Search

Native SQL search within schema-specific tables
Support for complex filtering and pagination
Returns ObjectEntity objects for compatibility

Table Structure

Naming Convention

oc_openregister_table_{schema_slug}

Column Types

Metadata Columns: All ObjectEntity properties prefixed with _
Schema Columns: JSON schema properties mapped to appropriate SQL types

Example Table: `oc_openregister_table_users`

-- Metadata columns (from ObjectEntity)
_id BIGINT PRIMARY KEY AUTO_INCREMENT,
_uuid VARCHAR(36) UNIQUE NOT NULL,
_register VARCHAR(255) NOT NULL,
_schema VARCHAR(255) NOT NULL,
_owner VARCHAR(64),
_organisation VARCHAR(36),
_name VARCHAR(255),
_created DATETIME,
_updated DATETIME,
_published DATETIME,
_files JSON,
_relations JSON,
-- ... all other ObjectEntity metadata

-- Schema columns (from JSON schema)
name VARCHAR(255) NOT NULL,        -- string property
email VARCHAR(320),               -- string with email format
age SMALLINT,                     -- integer with range
isActive BOOLEAN,                 -- boolean property
profile JSON,                     -- object property
tags JSON,                        -- array property
createdAt DATETIME                -- string with date-time format

Usage Examples

Basic Usage

use OCA\OpenRegister\Service\MagicMapper;

// Initialize MagicMapper (usually via dependency injection)
$magicMapper = new MagicMapper($db, $objectMapper, $schemaMapper, $registerMapper, $config, $logger);

// 1. Create table for schema
$schema = $schemaMapper->find('users');
$magicMapper->ensureTableForSchema($schema);

// 2. Save objects to schema table
$objects = [
    [
        '@self' => [
            'uuid' => 'user-123',
            'register' => 'main-register',
            'schema' => 'users',
            'owner' => 'admin',
            'organisation' => 'org-456'
        ],
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'age' => 30,
        'isActive' => true,
        'profile' => ['theme' => 'dark', 'language' => 'en']
    ]
];

$savedUuids = $magicMapper->saveObjectsToSchemaTable($objects, $schema);

// 3. Search in schema table
$query = [
    'name' => 'John%',           // LIKE search
    'age' => ['gt' => 18],       // Greater than
    '@self' => ['owner' => 'admin'], // Metadata filter
    '_limit' => 50,              // Pagination
    '_offset' => 0
];

$results = $magicMapper->searchObjectsInSchemaTable($query, $schema);

Advanced Queries

// Complex filtering
$query = [
    'isActive' => true,
    'age' => ['gte' => 21, 'lte' => 65],
    'email' => '%@company.com',
    '@self' => [
        'organisation' => 'org-123',
        'published' => ['gt' => '2024-01-01']
    ],
    '_order' => ['name' => 'ASC', '@self.created' => 'DESC'],
    '_limit' => 100
];

$results = $magicMapper->searchObjectsInSchemaTable($query, $schema);

Schema Configuration

Enable Magic Mapping

Per Schema (recommended):

{
  "configuration": {
    "magicMapping": true
  }
}

Globally:

$config->setAppValue('openregister', 'magic_mapping_enabled', 'true');

When to Enable Magic Mapping

Enable for schemas with:

High volume: >10,000 objects
Frequent searches: Heavy filtering and querying
Performance requirements: Sub-second response times needed
Complex filtering: Multiple property filters

Keep disabled for:

Low volume: <1,000 objects
Simple schemas: Few properties, minimal filtering
Development: When schema structure changes frequently

Performance Benefits

Query Performance

Schema-specific tables: No need to filter by schema ID
Proper indexing: Indexes tailored to schema properties
Optimized storage: SQL types instead of generic JSON storage
Better statistics: Database can optimize queries more effectively

Expected Improvements

Search speed: 3-5x faster for large datasets
Memory usage: 40-60% reduction for bulk operations
Index efficiency: Targeted indexes vs. generic JSON indexes
Query planning: Better execution plans from database optimizer

JSON Schema to SQL Type Mapping

JSON Schema Type	SQL Type	Notes
`string`	`VARCHAR(n)` or `TEXT`	Based on maxLength
`string` (email)	`VARCHAR(320)`	RFC 5321 limit
`string` (uuid)	`VARCHAR(36)`	Standard UUID length
`string` (date-time)	`DATETIME`	Indexed for performance
`integer`	`SMALLINT`, `INT`, or `BIGINT`	Based on min/max range
`number`	`DECIMAL(10,2)`	Configurable precision
`boolean`	`BOOLEAN`	Native boolean type
`array`	`JSON`	Complex data as JSON
`object`	`JSON`	Complex data as JSON

Error Handling

Graceful Degradation

Falls back to regular ObjectService on errors
Comprehensive logging for troubleshooting
Non-blocking failures (continues with standard workflow)

Common Issues

Table name conflicts: Automatic sanitization and truncation
Schema changes: Automatic table updates with version tracking
Database limitations: Fallback to JSON for unsupported types

Monitoring and Debugging

Logging

All operations are logged with structured data:

// Table creation
$logger->info('Creating new schema table', [
    'schemaId' => 123,
    'tableName' => 'oc_openregister_table_users',
    'columnCount' => 25
]);

// Search operations  
$logger->debug('Schema table search completed', [
    'tableName' => 'oc_openregister_table_users',
    'resultCount' => 150,
    'queryFilters' => ['name', 'age', '@self.owner']
]);

Cache Management

// Clear table existence cache
$magicMapper->clearCache();

// Get existing schema tables
$tables = $magicMapper->getExistingSchemaTables();

Testing

Unit Tests

# Run MagicMapper tests (when Nextcloud test environment is available)
./vendor/bin/phpunit tests/Unit/Service/MagicMapperTest.php

# Syntax validation
php -l lib/Service/MagicMapper.php

Integration Testing

# Run demo script
php examples/magic_mapper_demo.php

# Test with real schemas (requires Nextcloud environment)
# See examples in the demo script

Security Considerations

SQL Injection Prevention

All queries use parameterized statements
Table and column names are sanitized
Input validation for all user data

Access Control

Integrates with existing RBAC system
Respects organisation-based multitenancy
Owner-based permissions maintained

Future Enhancements

Potential Improvements

Relationship columns: Direct foreign key relationships
Custom indexes: Schema-defined index strategies
Partitioning: Time-based or organization-based partitioning
Materialized views: For complex aggregations
Full-text search: Schema-aware text search indexes

Migration Path

Gradual migration: Enable per schema as needed
Data migration tools: Move existing objects to schema tables
Parallel operation: Run alongside existing system during transition

Implementation Status

✅ Completed Features:

Dynamic table creation from JSON schemas
Metadata column integration from ObjectEntity
Schema-to-SQL type mapping for all common types
Table naming and sanitization logic
Search operations with filtering and pagination
Error handling and fallback scenarios
Comprehensive unit tests
Documentation and examples

✅ Validation Enhancement:

SaveObjects handler now requires schema and register IDs
Proper error messages for missing required parameters

🚧 Standalone Status:

MagicMapper is implemented as a standalone component
No integration with main ObjectService workflow yet
Ready for independent testing and evaluation
Can be integrated when ready for production use

Next Steps

Evaluate Performance: Test with real schemas and large datasets
Validate Integration Points: Ensure compatibility with existing workflows
Production Testing: Test in safe environment with real data
Gradual Rollout: Enable for specific high-volume schemas first
Monitor Performance: Compare query times before/after implementation

Schemas - Schema documentation
Schema Technical Documentation - Technical schema details

Overview​

Key Features​

🚀 Dynamic Table Creation​

📊 Metadata Integration​

⚡ Performance Optimizations​

🔄 Schema Evolution​

🔍 Advanced Search​

Table Structure​

Naming Convention​

Column Types​

Example Table: oc_openregister_table_users​

Usage Examples​

Basic Usage​

Advanced Queries​

Schema Configuration​

Enable Magic Mapping​

When to Enable Magic Mapping​

Performance Benefits​

Query Performance​

Expected Improvements​

JSON Schema to SQL Type Mapping​

Error Handling​

Graceful Degradation​

Common Issues​

Monitoring and Debugging​

Logging​

Cache Management​

Testing​

Unit Tests​

Integration Testing​

Security Considerations​

SQL Injection Prevention​

Access Control​

Future Enhancements​

Potential Improvements​

Migration Path​

Implementation Status​

Next Steps​

Related Documentation​