SaiAdmin Management System

SaiAdmin is a high-quality, out-of-the-box mid-to-back-office management system. Key features include:

• User & Access Management: User management (CRUD), department/organization tree structures, job/position management, and a powerful role management system supporting menu, button, and data permissions.
• System Configuration: Menu management, dictionary management, system settings, and attachment management.
• Monitoring & Logs: Operation logs, login logs, and service monitoring (server status and PHP environment).
• Maintenance & Automation: Database table maintenance (fragment cleaning/optimization), scheduled tasks (online scheduling with execution logs), and code generation (PHP, Vue, JS, SQL).
• File & Communication: Built-in email service and file upload support for local storage, Qiniu Cloud, Alibaba Cloud, Tencent Cloud, and S3.
• Extensibility: Developed as a webman plugin, making it non-intrusive and compatible with other webman application plugins. It also features an App Market for official and community plugins.

When writing migrations for PostgreSQL, follow these specific rules to maintain parity with MySQL and ensure compatibility with think-orm and Eloquent:

• Primary Keys: Disable Phinx's automatic id column in tableOptions(). Explicitly declare the primary key with 'identity' => true, 'generated' => null. This allows PostgreSQL to generate SERIAL (using nextval), which is required for think-orm to recognize auto-incrementing columns.
• Primary Key Type: Use SaiSchema::pkType() to get the type. In PostgreSQL, this will always be biginteger (int8), ensuring consistent width across all tables.
• Index Naming: Do not pass a name to addIndex(). PostgreSQL requires unique index names within a schema, so letting the driver generate default names prevents collisions.
• Timestamp Precision: Always include 'precision' => 0 for datetime or timestamp columns to ensure second-level precision matches MySQL and prevents microsecond display issues in the UI.
• Custom Functions: For think-orm compatibility, the CreatePgsqlHelpers migration creates essential functions like pgsql_type() and table_msg().

When writing application code that interacts with the database, follow these patterns to ensure compatibility with both MySQL and PostgreSQL:

1. Use Portable SQL

Avoid database-specific functions. For example, instead of MySQL's FIND_IN_SET, use whereRaw("CONCAT(',', level, ',') LIKE ?") for comma-separated list matching.

2. Case-Insensitive Searching

Never use where($field, 'like', "%$value%") for fuzzy searches, as PostgreSQL's LIKE is case-sensitive. Instead, use Eloquent's whereLike($field, $pattern) method. This automatically compiles to ILIKE on PostgreSQL and LIKE on MySQL.

3. Metadata and Schema Queries

• Column Existence: Use the schema builder: Db::getSchemaBuilder()->hasColumn($table, $field). Do not use SHOW COLUMNS.
• Dialect Branching: For operations like checking table status, optimizing tables, or cleaning fragments, use plugin\saiadmin\utils\DbType::get() or isPgsql($source) to branch implementation logic.

4. Mapping PostgreSQL types to MySQL terminology

When building tools that rely on field metadata (like code generators), map PostgreSQL types back to standard MySQL-like names:

• character varying $\rightarrow$ varchar
• timestamp without time zone $\rightarrow$ datetime

To add new tables or changes, first generate a migration file using the Phinx CLI.

Note on Naming: Migration filenames must follow the pattern version_snake_case.php (e.g., 20260901000000_add_foo.php). The class name is automatically derived from this filename (e.g., AddFoo).

Workflow

1. Generate the file:

   vendor/bin/phinx create NewFeatureTables -c plugin/saiadmin/db/phinx.php

2. Edit the generated file by implementing the change() or up() methods.
3. Execute the migration:

   php webman sai:migrate

Best Practices

• Use Portable APIs: Only use Phinx's portable methods like addColumn, addIndex, and addForeignKey. Avoid raw SQL ($this->execute(...)) to ensure compatibility between MySQL and PostgreSQL.
• Handle Dialects: If you must use database-specific logic, use SaiSchema::isPgsql() to branch your code.

vendor/bin/phinx create NewFeatureTables -c plugin/saiadmin/db/phinx.php
# ... edit file ...
php webman sai:migrate

SaiAdmin uses Phinx to manage database schemas. It supports both MySQL and PostgreSQL, switching based on the DB_TYPE (mysql | pgsql) value in your .env file.

Directory Structure

• migrations/: Contains table structure migrations (e.g., 20260822000000_init_base_tables.php).
• seeds/: Contains initial data. PureSeeder provides base data; DemoSeeder includes demo article data.
• data/: Pure PHP arrays (pure.php, demo.php) used by Seeders for cross-database compatibility.
• support/: Helper utilities like SaiSchema and SaiSeed used by migrations and seeders.
• phinx.php: Configuration file that resolves paths relative to __DIR__.

Migration Commands

Use the recommended webman wrapper to ensure .env is automatically loaded:

# View migration status
php webman sai:migrate status

# Execute pending migrations
php webman sai:migrate

# Run a specific Seeder
php webman sai:migrate seed --seed PureSeeder

# Rollback all migrations (WARNING: This deletes tables!)
php webman sai:migrate rollback -t 0

SaiAdmin plugins use Phinx for database migrations. To ensure compatibility and avoid fatal errors during the plugin installation process, follow these rules:

Namespace Convention

Migration classes must use the following namespace: plugin\{name}\db\migrations

Warning: If migration classes do not use this namespace, they may conflict with core or other plugin migrations, causing a fatal error during the require phase.

Using SaiSchema

To maintain database portability (MySQL/PostgreSQL), use the SaiSchema trait provided by the core. Do not include a local copy of the trait in your plugin; instead, require the core version:

require_once base_path() . '/plugin/saiadmin/db/support/SaiSchema.php';

final class CreateYourPluginTables extends AbstractMigration
{
    use \SaiSchema;

    public function up(): void
    {
        // Use portable Phinx API via SaiSchema
    }
}

Schema Writing Conventions

When using SaiSchema methods:

• Use portable Phinx APIs.
• Do not explicitly name indexes.
• For auto-increment columns, use ['identity' => true, 'generated' => null].
• For datetime columns, use ['null' => true, 'precision' => 0].
• Table names should include the sa_ prefix (Phinx does not automatically handle DB_PREFIX).

<?php

namespace plugin\my_plugin\db\migrations;

use Phinx\Migration\AbstractMigration;

require_once base_path() . '/plugin/saiadmin/db/support/SaiSchema.php';

final class CreateMyPluginTables extends AbstractMigration
{
    use \SaiSchema;

    public function up(): void
    {
        // Implementation goes here
    }

    public function down(): void
    {
        // Rollback implementation
    }
}

The OpenSpoutWriter class provides a simplified interface for generating XLSX files using the OpenSpout library. It handles file initialization, header creation, data insertion with optional filtering/styling, and column width adjustments.

Workflow

1. Initialize: Instantiate with a filename.
2. Configure Layout: Use setWidth() to adjust columns.
3. Set Header: Use setHeader() to define the first row (applies bold/centered/bordered style by default).
4. Set Data: Use setData() to add rows. You can provide a $filter array to map raw values to labels (e.g., converting IDs to human-readable names).
5. Finalize: Call returnFile() to close the file and get the absolute path to the generated file.

use plugin
oi
amespace	o\
OpenSpoutWriter;

// 1. Initialize
$writer = new OpenSpoutWriter('report.xlsx');

// 2. Set column widths (e.g., column A=20, B=30)
$writer->setWidth([20, 30]);

// 3. Set Header
$writer->setHeader(['ID', 'Name', 'Status']);

// 4. Set Data with value mapping (filter)
// The filter maps raw database values to display labels
$filter = [
    'Status' => [
        ['value' => 1, 'label' => 'Active'],
        ['value' => 0, 'label' => 'Inactive'],
    ]
];
$data = [
    ['ID' => 1, 'Name' => 'John Doe', 'Status' => 1],
    ['ID' => 2, 'Name' => 'Jane Doe', 'Status' => 0],
];
$writer->setData($data, null, $filter);

// 5. Finalize and get path
$filePath = $writer->returnFile();