Database Architecture
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The Database Architecture of the Duplicator project is designed to support multi-tenant-like capabilities for print collective reproduction management. It employs a flexible system that supports both MySQL and SQLite engines, though the primary implementation in the Electron-caddy context leans heavily on SQLite for portability. The architecture allows administrators to manage multiple database instances (Production, Development, Test, and Staging), perform backups, and execute migrations to maintain schema consistency across versions.
The core purpose of this system is to track printing runs (tirages), consumable changes (ink, masters, toner), machine configurations, and accounting data for both duplicators and photocopiers.
Sources: app/models/admin/DatabaseManager.php:7-14, app/models/admin/SQLiteDatabaseManager.php:7-15, README.md
Core Database Managers
The project uses two primary manager classes to handle database operations depending on the environment configuration: AdminDatabaseManager (for MySQL) and SQLiteDatabaseManager (for SQLite).
MySQL Management (AdminDatabaseManager)
In MySQL mode, the system manages actual database schemas within a MySQL server. It includes features for:
- Database Creation: Creates new schemas and applies templates like
structure_completeor copies fromduplinew. - Database Switching: Updates an
active_databasetable in a master database (duplinew) to route the application to different environments. - Renaming/Deletion: Handles schema drops and reconstructions for renaming.
Sources: app/models/admin/DatabaseManager.php:20-130
SQLite Management (SQLiteDatabaseManager)
In the portable Electron version, the system manages .sqlite files within a specific directory.
- File Handling: Directly manipulates files on the filesystem using
glob,copy, andunlink. - Config Synchronization: Updates the
conf.phpfile dynamically when switching databases by rewriting the$sqlite_db_pathvariable.
Sources: app/models/admin/SQLiteDatabaseManager.php:20-140
Logic Flow for Database Switching
The following diagram illustrates how the application switches between different database files or schemas.
The system ensures that only databases starting with a letter and containing alphanumeric/underscores are allowed for security.
Sources: app/models/admin/DatabaseManager.php:83-110, app/models/admin/SQLiteDatabaseManager.php:97-128
Schema Structure
The database schema is divided into several functional areas: machine management, accounting, content (news/help), and system configuration.
Essential Tables
| Table Name | Description | Key Fields |
|---|---|---|
duplicopieurs | Master data for duplicator machines | marque, modele, tambours (JSON) |
photocopieurs | Master data for photocopiers | marque, modele, type_encre (encre/toner) |
dupli | Records of print runs on duplicators | contact, master_av/ap, passage_av/ap, prix |
photocop | Records of print runs on photocopiers | contact, nb_f, prix, rv |
cons | Log of consumable changes | machine, type (ink/master/etc), nb_p, nb_m |
prix | Pricing configuration per machine/type | machine_type, type, unite, pack |
site_settings | General application configuration | setting_name, setting_value |
admin_passwords | Security storage for admin access | password_hash |
Sources: app/models/admin/DatabaseManager.php:235-420, app/models/admin/SQLiteDatabaseManager.php:237-375
Entity Relationship Overview
The following diagram represents the core relationships between machines and their associated records.
Migration and Maintenance System
The DatabaseMigrationManager ensures the schema evolves without data loss. It uses a schema_migrations table to track which updates have been applied.
Migration Workflow
- Backup: Before any migration, a full backup is created via
BackupManager. - Transaction: Changes are wrapped in a database transaction.
- Execution: SQL commands (like
ALTER TABLEorCREATE VIRTUAL TABLE) are executed. - Logging: Success is recorded in the
schema_migrationstable.
Sources: app/models/migrations/DatabaseMigrationManager.php:15-70
Search Optimization (FTS5)
For document management (Library/Bibliotheque), the system implements SQLite FTS5 (Full-Text Search).
- Virtual Table:
bibliotheque_files_ftsmaps tobibliotheque_files. - Triggers: Automatic triggers (
AFTER INSERT,AFTER DELETE,AFTER UPDATE) keep the search index synchronized with the actual file records.
Sources: app/models/migrations/DatabaseMigrationManager.php:123-170
Identifiers Generation
The architecture utilizes a globally unique, human-readable identifier for print runs: tirage_global_id.
- Format:
YYYY-MM-DD_HH-MM-SS_ContactName_MachineName - Implementation: Generated via
generateTirageGlobalIdwhich normalizes strings by removing special characters and converting to lowercase.
Sources: app/models/migrations/DatabaseMigrationManager.php:248-285
Conclusion
The database architecture provides a robust foundation for the Duplicator project by decoupling the storage engine (MySQL vs. SQLite) from the business logic. It prioritizes data integrity through automatic backups before migrations and enhances user experience with optimized search indexes. The management of multiple environments (Dev/Prod) directly through the administration interface makes it highly suitable for both local collective use and development scenarios.