Narzędzia bazodanowe

Przestrzeń nazw Xmf\Database zapewnia klasy upraszczające zadania konserwacji bazy danych związane z instalacją i aktualizacją modułów XOOPS. Te narzędzia obsługują migracje schematów, modyfikacje tabel i początkowe ładowanie danych.

Overview

The database utilities include:

Tables - Building and executing DDL statements for table modifications
Migrate - Synchronizing database schema between module versions
TableLoad - Loading initial data into tables

Xmf\Database\Tables

The Tables class simplifies creating and modifying database tables. It builds a work queue of DDL (Data Definition Language) statements that are executed together.

Key Features

Loads current schema from existing tables
Queues changes without immediate execution
Considers current state when determining work to do
Automatically handles XOOPS table prefix

Getting Started

use Xmf\Database\Tables;

// Create a new Tables instance
$tables = new Tables();

// Load an existing table or start new schema
$tables->addTable('mymodule_items');

// For existing tables only (fails if table doesn't exist)
$tables->useTable('mymodule_items');

Table Operations

Rename a Table

$tables = new Tables();
$tables->addTable('mymodule_old_name');
$tables->renameTable('mymodule_old_name', 'mymodule_new_name');
$tables->executeQueue();

Set Table Options

$tables->addTable('mymodule_items');
$tables->setTableOptions('mymodule_items', 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$tables->executeQueue();

Drop a Table

$tables->addTable('mymodule_temp');
$tables->dropTable('mymodule_temp');
$tables->executeQueue();

Copy a Table

// Copy structure only
$tables->copyTable('mymodule_items', 'mymodule_items_backup', false);

// Copy structure and data
$tables->copyTable('mymodule_items', 'mymodule_items_backup', true);
$tables->executeQueue();

Working with Columns

Add a Column

$tables = new Tables();
$tables->addTable('mymodule_items');

$tables->addColumn(
    'mymodule_items',
    'status',
    "TINYINT(1) NOT NULL DEFAULT '1'"
);

$tables->executeQueue();

Alter a Column

$tables->useTable('mymodule_items');

// Change column attributes
$tables->alterColumn(
    'mymodule_items',
    'title',
    "VARCHAR(255) NOT NULL DEFAULT ''"
);

// Rename and modify column
$tables->alterColumn(
    'mymodule_items',
    'old_column_name',
    "VARCHAR(100) NOT NULL",
    'new_column_name'
);

$tables->executeQueue();

Get Column Attributes

$tables->useTable('mymodule_items');
$attributes = $tables->getColumnAttributes('mymodule_items', 'title');
// Returns: "VARCHAR(255) NOT NULL DEFAULT ''"

Drop a Column

$tables->useTable('mymodule_items');
$tables->dropColumn('mymodule_items', 'obsolete_field');
$tables->executeQueue();

Working with Indexes

Get Table Indexes

$tables->useTable('mymodule_items');
$indexes = $tables->getTableIndexes('mymodule_items');

// Returns array like:
// [
//     'PRIMARY' => ['columns' => 'item_id', 'unique' => true],
//     'idx_category' => ['columns' => 'category_id', 'unique' => false]
// ]

Add Primary Key

$tables->addTable('mymodule_items');
$tables->addPrimaryKey('mymodule_items', 'item_id');

// Composite primary key
$tables->addPrimaryKey('mymodule_item_tags', 'item_id, tag_id');
$tables->executeQueue();

Add Index

$tables->useTable('mymodule_items');

// Simple index
$tables->addIndex('idx_category', 'mymodule_items', 'category_id');

// Unique index
$tables->addIndex('idx_slug', 'mymodule_items', 'slug', true);

// Composite index
$tables->addIndex('idx_cat_status', 'mymodule_items', 'category_id, status');

$tables->executeQueue();

Drop Index

$tables->useTable('mymodule_items');
$tables->dropIndex('idx_old_index', 'mymodule_items');
$tables->executeQueue();

Drop All Non-Primary Indexes

// Useful for cleaning up auto-generated index names
$tables->dropIndexes('mymodule_items');
$tables->executeQueue();

Drop Primary Key

$tables->dropPrimaryKey('mymodule_items');
$tables->executeQueue();

Data Operations

Insert Data

$tables->useTable('mymodule_categories');

$tables->insert('mymodule_categories', [
    'category_id' => 1,
    'name' => 'General',
    'weight' => 0
]);

// Without automatic quoting (for expressions)
$tables->insert('mymodule_logs', [
    'created' => 'NOW()',
    'message' => "'Test message'"
], false);

$tables->executeQueue();

Update Data

$tables->useTable('mymodule_items');

// Update with criteria object
$criteria = new Criteria('status', 0);
$tables->update('mymodule_items', ['status' => 1], $criteria);

// Update with string criteria
$tables->update('mymodule_items', ['hits' => 0], 'hits IS NULL');

$tables->executeQueue();

Delete Data

$tables->useTable('mymodule_items');

// Delete with criteria
$criteria = new Criteria('status', -1);
$tables->delete('mymodule_items', $criteria);

// Delete with string criteria
$tables->delete('mymodule_items', 'created < DATE_SUB(NOW(), INTERVAL 1 YEAR)');

$tables->executeQueue();

Truncate Table

$tables->useTable('mymodule_cache');
$tables->truncate('mymodule_cache');
$tables->executeQueue();

Work Queue Management

Execute Queue

// Normal execution (respects HTTP method safety)
$result = $tables->executeQueue();

// Force execution even on GET requests
$result = $tables->executeQueue(true);

if (!$result) {
    echo 'Error: ' . $tables->getLastError();
}

Reset Queue

// Clear queue without executing
$tables->resetQueue();

Add Raw SQL

// Add custom SQL to the queue
$tables->addToQueue('ALTER TABLE ' . $GLOBALS['xoopsDB']->prefix('mymodule_items') . ' CONVERT TO CHARACTER SET utf8mb4');
$tables->executeQueue();

Error Handling

$tables = new Tables();

if (!$tables->addTable('mymodule_items')) {
    $error = $tables->getLastError();
    $errno = $tables->getLastErrNo();
    // Handle error
}

Xmf\Database\Migrate

The Migrate class simplifies synchronizing database changes between module versions. It extends Tables with schema comparison and automatic synchronization.

Basic Usage

use Xmf\Database\Migrate;

// Create migrate instance for a module
$migrate = new Migrate('mymodule');

// Synchronize database with target schema
$migrate->synchronizeSchema();

In Module Update

Typically called in the module’s xoops_module_pre_update_* function:

function xoops_module_pre_update_mymodule($module, $previousVersion)
{
    $migrate = new \Xmf\Database\Migrate('mymodule');

    // Perform any pre-sync actions (renames, etc.)
    // ...

    // Synchronize schema
    return $migrate->synchronizeSchema();
}

Getting DDL Statements

For large databases or command-line migrations:

$migrate = new Migrate('mymodule');
$statements = $migrate->getSynchronizeDDL();

// Execute statements in batches or from CLI
foreach ($statements as $sql) {
    // Process each statement
}

Pre-Sync Actions

Some changes require explicit handling before synchronization. Extend Migrate for complex migrations:

class MyModuleMigrate extends \Xmf\Database\Migrate
{
    public function preSyncActions()
    {
        // Rename a table before sync
        $this->useTable('mymodule_old_name');
        $this->renameTable('mymodule_old_name', 'mymodule_new_name');
        $this->executeQueue();

        // Rename a column
        $this->useTable('mymodule_items');
        $this->alterColumn(
            'mymodule_items',
            'old_column',
            'VARCHAR(255) NOT NULL',
            'new_column'
        );
        $this->executeQueue();
    }
}

// Usage
$migrate = new MyModuleMigrate('mymodule');
$migrate->preSyncActions();
$migrate->synchronizeSchema();

Schema Management

Get Current Schema

$migrate = new Migrate('mymodule');
$currentSchema = $migrate->getCurrentSchema();

Get Target Schema

$targetSchema = $migrate->getTargetDefinitions();

Save Current Schema

For module developers to capture schema after database changes:

$migrate = new Migrate('mymodule');
$migrate->saveCurrentSchema();
// Saves schema to module's sql/migrate.yml

Developer Note: Always make changes to the database first, then run saveCurrentSchema(). Do not manually edit the generated schema file.

Xmf\Database\TableLoad

The TableLoad class simplifies loading initial data into tables. Useful for seeding tables with default data during module installation.

Loading Data from Arrays

use Xmf\Database\TableLoad;

$data = [
    ['category_id' => 1, 'name' => 'General', 'weight' => 0],
    ['category_id' => 2, 'name' => 'News', 'weight' => 10],
    ['category_id' => 3, 'name' => 'Events', 'weight' => 20]
];

$count = TableLoad::loadTableFromArray('mymodule_categories', $data);
echo "Inserted {$count} rows";

Loading Data from YAML

// Load from YAML file
$count = TableLoad::loadTableFromYamlFile(
    'mymodule_categories',
    XOOPS_ROOT_PATH . '/modules/mymodule/sql/categories.yml'
);

YAML format:

-
  category_id: 1
  name: General
  weight: 0
-
  category_id: 2
  name: News
  weight: 10

Extracting Data

Count Rows

// Count all rows
$total = TableLoad::countRows('mymodule_items');

// Count with criteria
$criteria = new Criteria('status', 1);
$activeCount = TableLoad::countRows('mymodule_items', $criteria);

Extract Rows

// Extract all rows
$rows = TableLoad::extractRows('mymodule_items');

// Extract with criteria
$criteria = new Criteria('category_id', 5);
$rows = TableLoad::extractRows('mymodule_items', $criteria);

// Skip certain columns
$rows = TableLoad::extractRows('mymodule_items', null, ['password', 'token']);

Saving Data to YAML

// Save all data
TableLoad::saveTableToYamlFile(
    'mymodule_categories',
    '/path/to/categories.yml'
);

// Save filtered data
$criteria = new Criteria('is_default', 1);
TableLoad::saveTableToYamlFile(
    'mymodule_settings',
    '/path/to/default_settings.yml',
    $criteria
);

// Save without certain columns
TableLoad::saveTableToYamlFile(
    'mymodule_items',
    '/path/to/items.yml',
    null,
    ['created', 'modified']
);

Truncate Table

// Empty a table
$affectedRows = TableLoad::truncateTable('mymodule_cache');

Complete Migration Example

xoops_version.php

$modversion['sqlfile']['mysql'] = 'sql/mysql.sql';
$modversion['tables'] = [
    'mymodule_items',
    'mymodule_categories',
    'mymodule_settings'
];

include/onupdate.php

<?php
use Xmf\Database\Migrate;
use Xmf\Database\Tables;
use Xmf\Database\TableLoad;

function xoops_module_pre_update_mymodule($module, $previousVersion)
{
    // Create custom migrate class
    $migrate = new MyModuleMigrate('mymodule');

    // Handle version-specific migrations
    if ($previousVersion < 120) {
        // Version 1.2.0 renamed a table
        $migrate->renameOldTable();
    }

    if ($previousVersion < 130) {
        // Version 1.3.0 renamed a column
        $migrate->renameOldColumn();
    }

    // Synchronize schema
    return $migrate->synchronizeSchema();
}

function xoops_module_update_mymodule($module, $previousVersion)
{
    // Post-update data migrations
    if ($previousVersion < 130) {
        // Load new default settings
        TableLoad::loadTableFromYamlFile(
            'mymodule_settings',
            XOOPS_ROOT_PATH . '/modules/mymodule/sql/new_settings.yml'
        );
    }

    return true;
}

class MyModuleMigrate extends Migrate
{
    public function renameOldTable()
    {
        if ($this->useTable('mymodule_posts')) {
            $this->renameTable('mymodule_posts', 'mymodule_items');
            $this->executeQueue();
        }
    }

    public function renameOldColumn()
    {
        if ($this->useTable('mymodule_items')) {
            $this->alterColumn(
                'mymodule_items',
                'post_title',
                "VARCHAR(255) NOT NULL DEFAULT ''",
                'title'
            );
            $this->executeQueue();
        }
    }
}

API Reference

Xmf\Database\Tables

Method	Description
`addTable($table)`	Load or create table schema
`useTable($table)`	Load existing table only
`renameTable($table, $newName)`	Queue table rename
`setTableOptions($table, $options)`	Queue table options change
`dropTable($table)`	Queue table drop
`copyTable($table, $newTable, $withData)`	Queue table copy
`addColumn($table, $column, $attributes)`	Queue column addition
`alterColumn($table, $column, $attributes, $newName)`	Queue column change
`getColumnAttributes($table, $column)`	Get column definition
`dropColumn($table, $column)`	Queue column drop
`getTableIndexes($table)`	Get index definitions
`addPrimaryKey($table, $column)`	Queue primary key
`addIndex($name, $table, $column, $unique)`	Queue index
`dropIndex($name, $table)`	Queue index drop
`dropIndexes($table)`	Queue all index drops
`dropPrimaryKey($table)`	Queue primary key drop
`insert($table, $columns, $quote)`	Queue insert
`update($table, $columns, $criteria, $quote)`	Queue update
`delete($table, $criteria)`	Queue delete
`truncate($table)`	Queue truncate
`executeQueue($force)`	Execute queued operations
`resetQueue()`	Clear queue
`addToQueue($sql)`	Add raw SQL
`getLastError()`	Get last error message
`getLastErrNo()`	Get last error code

Xmf\Database\Migrate

Method	Description
`__construct($dirname)`	Create for module
`synchronizeSchema()`	Sync database to target
`getSynchronizeDDL()`	Get DDL statements
`preSyncActions()`	Override for custom actions
`getCurrentSchema()`	Get current database schema
`getTargetDefinitions()`	Get target schema
`saveCurrentSchema()`	Save schema for developers

Xmf\Database\TableLoad

Method	Description
`loadTableFromArray($table, $data)`	Load from array
`loadTableFromYamlFile($table, $file)`	Load from YAML
`truncateTable($table)`	Empty table
`countRows($table, $criteria)`	Count rows
`extractRows($table, $criteria, $skip)`	Extract rows
`saveTableToYamlFile($table, $file, $criteria, $skip)`	Save to YAML

Narzędzia bazodanowe

Overview

Xmf\Database\Tables

Key Features

Getting Started

Table Operations

Rename a Table

Set Table Options

Drop a Table

Copy a Table

Working with Columns

Add a Column

Alter a Column

Get Column Attributes

Drop a Column

Working with Indexes

Get Table Indexes

Add Primary Key

Add Index

Drop Index

Drop All Non-Primary Indexes

Drop Primary Key

Data Operations

Insert Data

Update Data

Delete Data

Truncate Table

Work Queue Management

Execute Queue

Reset Queue

Add Raw SQL

Error Handling

Xmf\Database\Migrate

Basic Usage

In Module Update

Getting DDL Statements

Pre-Sync Actions

Schema Management

Get Current Schema

Get Target Schema

Save Current Schema

Xmf\Database\TableLoad

Loading Data from Arrays

Loading Data from YAML

Extracting Data

Count Rows

Extract Rows

Saving Data to YAML

Truncate Table

Complete Migration Example

xoops_version.php

include/onupdate.php

API Reference

Xmf\Database\Tables

Xmf\Database\Migrate

Xmf\Database\TableLoad

See Also