PHP code example of migears / mitable

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

migears / mitable example snippets

$users = new MySQLTable($pdo, 'users');   // MySQL or MariaDB
$rows  = new SQLiteTable($pdo, 'rows');   // SQLite

use MiGears\MiTable\MySQLTable;

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass');
$users = new MySQLTable($pdo, 'users');

// Create table
$users->create([
    'id' => 'INT UNSIGNED AUTO_INCREMENT PRIMARY KEY',
    'username' => 'VARCHAR(50) NOT NULL',
    'email' => 'VARCHAR(255) NOT NULL UNIQUE',
    'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
]);

// Insert
$id = $users->insert([
    'username' => 'alice',
    'email' => '[email protected]',
]);

// Bulk insert
$users->bulkInsert([
    ['username' => 'bob', 'email' => '[email protected]'],
    ['username' => 'charlie', 'email' => '[email protected]'],
]);

// Find
$user = $users->find(['id' => $id]);

// Query
$activeUsers = $users->where(['status' => 'active'], 'id DESC', 10);

// Update
$users->update(['status' => 'inactive'], ['id' => $id]);

// Delete
$users->delete(['id' => $id]);

// Count
$total = $users->count();
$activeCount = $users->count(['status' => 'active']);

// Backfill a derived column for a set of domains
$users->update(
    ['channel' => 'personal'],
    ['email' => ['like', '%@gmail.com']]
);

// Archive a batch by id
$users->update(['archived' => 1], ['id' => ['in', [101, 102, 103]]]);

// Work on a date range
$orders->where(['created_at' => ['between', ['2024-01-01', '2024-12-31']]], 'id ASC');

// Repair rows missing a value
$users->where(['phone' => null]);

$users->withPageSize(1000);

foreach ($users as $row) {
    processUser($row);
}

$events->withCursorKey('event_id')->withPageSize(500);

$users->withPageSize(1000)->withCursorStart($checkpoint);

foreach ($users as $row) {
    migrate($row);
    $checkpoint = $users->cursor();
}

saveCheckpoint($checkpoint);

$columns = $users->showColumns();

if (!isset($columns['phone'])) {
    $users->addColumn('phone', 'VARCHAR(20) NULL', 'email');
}

if (!$columns['email']['unique']) {
    $users->addUniqueIndex('uniq_email', ['email']);
}

$indexes = $users->showIndexes();

if (!isset($indexes['idx_email'])) {
    $users->addIndex('idx_email', ['email']);
} elseif (!$indexes['idx_email']['unique']) {
    $users->dropIndex('idx_email');
    $users->addUniqueIndex('idx_email', ['email']);
}

// Check if table exists
if (!$users->exists()) {
    $users->create([...]);
}

// Add a column
$users->addColumn('bio', 'TEXT', after: 'email');

// Drop a column
$users->dropColumn('old_field');

// Modify a column
$users->modifyColumn('username', 'VARCHAR(100) NOT NULL');

// Add an index
$users->addIndex('idx_email', ['email']);
$users->addUniqueIndex('uniq_username', ['username']);

// Drop an index
$users->dropIndex('idx_email');

// Add primary key
$users->addPrimaryKey(['id']);

// Rename table
$renamed = $users->rename('app_users');

// Truncate
$users->truncate();

// Drop
$users->drop();

$users->withTransaction(function () use ($users) {
    $users->update(['channel' => 'personal'], ['email' => ['like', '%@gmail.com']]);
    $users->update(['channel' => 'work'], ['email' => ['like', '%@example.com']]);
});

$users = new MySQLTable($pdo, 'users');   // MySQL 或 MariaDB
$rows  = new SQLiteTable($pdo, 'rows');   // SQLite

use MiGears\MiTable\MySQLTable;

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass');
$users = new MySQLTable($pdo, 'users');

// 创建表
$users->create([
    'id' => 'INT UNSIGNED AUTO_INCREMENT PRIMARY KEY',
    'username' => 'VARCHAR(50) NOT NULL',
    'email' => 'VARCHAR(255) NOT NULL UNIQUE',
    'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
]);

// 插入
$id = $users->insert([
    'username' => 'alice',
    'email' => '[email protected]',
]);

// 批量插入
$users->bulkInsert([
    ['username' => 'bob', 'email' => '[email protected]'],
    ['username' => 'charlie', 'email' => '[email protected]'],
]);

// 查询单条
$user = $users->find(['id' => $id]);

// 查询多条
$activeUsers = $users->where(['status' => 'active'], 'id DESC', 10);

// 更新
$users->update(['status' => 'inactive'], ['id' => $id]);

// 删除
$users->delete(['id' => $id]);

// 统计
$total = $users->count();
$activeCount = $users->count(['status' => 'active']);

// 按邮箱域名批量回填派生列
$users->update(
    ['channel' => 'personal'],
    ['email' => ['like', '%@gmail.com']]
);

// 按 id 集合归档一批数据
$users->update(['archived' => 1], ['id' => ['in', [101, 102, 103]]]);

// 处理某个时间范围
$orders->where(['created_at' => ['between', ['2024-01-01', '2024-12-31']]], 'id ASC');

// 修复缺失字段的行
$users->where(['phone' => null]);

$users->withPageSize(1000);

foreach ($users as $row) {
    processUser($row);
}

$events->withCursorKey('event_id')->withPageSize(500);

$users->withPageSize(1000)->withCursorStart($checkpoint);

foreach ($users as $row) {
    migrate($row);
    $checkpoint = $users->cursor();
}

saveCheckpoint($checkpoint);

$columns = $users->showColumns();

if (!isset($columns['phone'])) {
    $users->addColumn('phone', 'VARCHAR(20) NULL', 'email');
}

if (!$columns['email']['unique']) {
    $users->addUniqueIndex('uniq_email', ['email']);
}

$indexes = $users->showIndexes();

if (!isset($indexes['idx_email'])) {
    $users->addIndex('idx_email', ['email']);
} elseif (!$indexes['idx_email']['unique']) {
    $users->dropIndex('idx_email');
    $users->addUniqueIndex('idx_email', ['email']);
}

// 检查表是否存在
if (!$users->exists()) {
    $users->create([...]);
}

// 添加列
$users->addColumn('bio', 'TEXT', after: 'email');

// 删除列
$users->dropColumn('old_field');

// 修改列
$users->modifyColumn('username', 'VARCHAR(100) NOT NULL');

// 添加索引
$users->addIndex('idx_email', ['email']);
$users->addUniqueIndex('uniq_username', ['username']);

// 删除索引
$users->dropIndex('idx_email');

// 添加主键
$users->addPrimaryKey(['id']);

// 重命名表
$renamed = $users->rename('app_users');

// 清空表
$users->truncate();

// 删除表
$users->drop();

$users->withTransaction(function () use ($users) {
    $users->update(['channel' => 'personal'], ['email' => ['like', '%@gmail.com']]);
    $users->update(['channel' => 'work'], ['email' => ['like', '%@example.com']]);
});