SQLSync

SQLSync is a feature of EMPS that allows you to define your project’s database structure in a plain SQL file that resides within your source code. The sqlsync mechanism parses this file and automatically synchronizes your actual MySQL database with the defined structure. In other words, it ensures that your real database tables match the design declared in your SQL file.

Key Benefit:
Instead of writing numerous migration scripts, you simply maintain one or more SQL files that define your table structures. When run, sqlsync updates the database structure as needed—without worrying about the types and properties of fields or manual migration steps.

How SQLSync Works

1. The SQL File

Your project’s SQL file contains the definitions of your tables. For example, you might have an SQL file with entries like:

-- table
CREATE TEMPORARY TABLE `temp_companies` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `owner_id` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  `status` int(11) NOT NULL,
  `sel_dt` bigint NOT NULL,
  `cdt` bigint NOT NULL,
  `dt` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_id` (`owner_id`),
  KEY `name` (`name`),
  KEY `status` (`status`),
  KEY `cdt` (`cdt`),
  KEY `dt` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- table
CREATE TEMPORARY TABLE `temp_positions` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  `md5` varchar(255) NOT NULL,
  `invite_email` varchar(255) NOT NULL,
  `type` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `sel_dt` bigint NOT NULL,
  `cdt` bigint NOT NULL,
  `dt` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `name` (`name`),
  KEY `invite_email` (`invite_email`),
  KEY `md5` (`md5`),
  KEY `type` (`type`),
  KEY `status` (`status`),
  KEY `sel_dt` (`sel_dt`),
  KEY `cdt` (`cdt`),
  KEY `dt` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Each table is preceded by a comment marker (-- table), which sqlsync uses as a delimiter to identify separate table definitions.

The SQLSync Process

When you run the sqlsync script, EMPS follows these general steps:

  1. Load the SQL File(s):
    The script reads the main SQL file (and optionally additional SQL files defined in configuration or a module list). This file is usually stored within your project source, so it’s version-controlled alongside your code.

  2. Extract Table Definitions:
    The script splits the file using the -- table delimiter. Each block that contains a CREATE TEMPORARY TABLE statement is considered a table definition.

  3. Determine the Real Table Name:
    Temporary table names (e.g., temp_companies) are converted to the real table names using a simple naming rule (often by removing the temp_ prefix with the actual table prefix used in the project, usually c_, defined in the TP constant).

  4. Check for Table Existence:
    The script queries the current database to see if the real table already exists.

  5. Synchronize Structure:

    • If the table exists:
      • The script uses helper functions (like collect_columns() and collect_indices()) to gather the current table’s structure and indices.
      • The function sync_structure() compares each field from the SQL definition with the current table. If differences are found (e.g., changes in type, nullability, or default values), an ALTER TABLE statement is issued to modify the table accordingly.
      • Similarly, sync_indices() ensures that indexes (including primary keys, unique indexes, and fulltext indexes) match the SQL file.
    • If the table does not exist:
      • The script adjusts the SQL code (replacing the temporary table name with the real name, and removing the TEMPORARY keyword) and creates the table by executing the SQL statement.

  6. Logging Changes:
    As the script processes each table, it echoes details about the changes being made (such as “create index” or “type change”), so you can see what modifications are applied to the database.

Advantages of SQLSync

  • Simplicity:
    Instead of managing numerous migration scripts (as seen in some frameworks like Laravel), you only need to maintain a single SQL file (or a set of them) that defines your entire database schema.

  • Readability:
    The SQL file is a clear, human-readable representation of your table structures. This can make it easier to understand and review changes over time.

  • Standard Syntax:
    Use any standard SQL syntax keywords to define the structure exactly as it needs to be, use all available data types and field properties, table engines, collations, etc.

  • Ease of Synchronization:
    SQLSync automatically detects differences between your defined structure and the live database. It then issues the appropriate SQL commands (such as ALTER TABLE or CREATE TABLE) to bring your database in line with your project’s definition.

  • Version Control Integration:
    Since the SQL file is part of your source code, changes to your database schema can be tracked alongside your code changes, enabling better collaboration and rollback if necessary.

How to Use SQLSync

1. Prepare Your SQL File

Create an SQL file (for example, module.sql) in your project’s source directory with your table definitions. Ensure you use the -- table delimiter to separate each table’s definition.

For example, you want to define the tables for a news module. Create a file at modules/news/sql/module.sql. It's enough to open the project's /sqlsync/news/ URL to invoke SQLSync with that SQL file. So, the general pattern is this:

  • modules/{module_name}/sql/module.sql for the file path
  • /sqlsync/{module_name}/ for the invoking URL

2. Invoke the SQLSync Script

The easiest way to invoke the SQL synchronization is to open the appropriate internal URL, such as /sqlsync/news/ (as described in the previous step).

If you have many modules in your project and you need to make sure the database structure stays synchronized after every call to /sqlsync/ (which a heartbeat script and is called every minute if the heartbeat is enabled), you can list the module names in the modules/_common/config/sqlsync.txt of your project. Just list the module names separated by commas with no spaces, e.g. news,projects,items.

3. Review the Output

The script outputs text messages indicating:

  • Which tables were updated.
  • What changes were made (such as new indices or modifications to column types).
  • Whether a table was created for the first time.

4. Repeat as Needed

Any time you update your SQL file with new table definitions or modifications, simply run the sqlsync script again. The process is idempotent, meaning it only applies changes when necessary.

Comparison with Laravel Migrations

While Laravel migrations require you to write PHP code for every change in your database schema, sqlsync relies on a single SQL file that mirrors the desired state of your database. This approach has several benefits:

  • Less Boilerplate:
    You’re not writing individual migration classes or methods.
  • Faster Setup:
    New projects can quickly import their schema without running a series of migrations.
  • Easier Maintenance:
    A single file (or a few files) keeps track of the entire database structure in one place, making it easier to see the “big picture.”

Conclusion

SQLSync is a powerful, developer-friendly feature in EMPS that simplifies database schema management. By allowing you to maintain a single (or few) SQL file(s) to define your entire database structure, sqlsync makes it easy to keep your live database in sync with your source code. This approach offers a streamlined alternative to the more complex migration systems found in other frameworks.

For developers who prefer the simplicity and clarity of working directly with SQL, SQLSync provides an efficient, maintainable solution for schema management in EMPS.