Database Table Migrations

This example shows the basics of manipulating database tables using XMF.

We will cycle through creating a table, then renaming and altering it, and finally, dropping everything so we can start over.

We will show the working state of our table at each stage, and if the logger is active, you can see any SQL statements that were executed. You may also want to explore the table with external tools, such as phpMyAdmin.


Create an Example Table

Right now, we do not have a table to work with. Click the button to get started.

Code


<?php

use Xmf\Debug;
use 
Xmf\Database\Tables;
use 
Xmf\Module\Helper;
use 
Xmf\Request;


$dir basename(__DIR__);
$helper Helper::getHelper($dir);
$modulePrefix $helper->getModule()->getVar('dirname');

$migrate = new Tables();

$uglyTableName "ugly_{$modulePrefix}_table";
$uglyTableExists $migrate->useTable($uglyTableName);

$mainTableName "{$modulePrefix}_widgets";
$mainTableExists $migrate->useTable($mainTableName);

$isPost = ('POST' == Request::getMethod());
$step 0;
if (!
$uglyTableExists && !$mainTableExists) {
    
$step $isPost 1;
} elseif (
$uglyTableExists) {
    
$step $isPost 3;
} elseif (
$mainTableExists) {
    
$step $isPost 5;
}

switch (
$step) {
    case 
1// no tables exist
        
$formCaption _MA_XMFDEMO_TABLE_FORM_TITLE_1;
        
$formMessage _MA_XMFDEMO_TABLE_FORM_MESSAGE_1;
        break;
    case 
2// create initial table
        
$migrate->addTable($uglyTableName);
        
$migrate->addColumn($uglyTableName'id''int(10) NOT NULL AUTO_INCREMENT');
        
$migrate->addColumn($uglyTableName'content''text NOT NULL');
        
$migrate->addColumn($uglyTableName'uid''int(10) NOT NULL DEFAULT \'0\'');
        
$migrate->addPrimaryKey($uglyTableName'id');
        
$migrate->addIndex('uidid'$uglyTableName'uid, id');
        
$migrate->executeQueue();
        
// fall through
    
case 3// have initial table
        
$formCaption _MA_XMFDEMO_TABLE_FORM_TITLE_3;
        
$formMessage _MA_XMFDEMO_TABLE_FORM_MESSAGE_3;
        break;
    case 
4// rename initial table
        
$migrate->useTable($uglyTableName);
        
$migrate->renameTable($uglyTableName$mainTableName);
        
$migrate->addColumn($mainTableName'update_time''int(10) NOT NULL DEFAULT \'0\'');
        
$migrate->dropIndexes($mainTableName);
        
$migrate->addIndex('uidupdate_time'$mainTableName'uid, update_time');
        
$migrate->executeQueue();
        
// fall through
    
case 5// have renamed table
        
$migrate->resetQueue(); // getting the changes for display
        
$migrate->useTable($mainTableName);
        
$formCaption _MA_XMFDEMO_TABLE_FORM_TITLE_5;
        
$formMessage _MA_XMFDEMO_TABLE_FORM_MESSAGE_5;
        break;
    case 
6// drop table and restart demo
        
$migrate->useTable($mainTableName);
        
$migrate->dropTable($mainTableName);
        
$migrate->executeQueue();

        
$formCaption _MA_XMFDEMO_TABLE_FORM_TITLE_1;
        
$formMessage _MA_XMFDEMO_TABLE_FORM_MESSAGE_1;
        break;
}

$form = new \XoopsThemeForm($formCaption'form''''POST');
$form->addElement(new \XoopsFormLabel(''$formMessage));
$form->addElement(new \XoopsFormButton('''submit'_MA_XMFDEMO_FORM_SUBMIT'submit'));
echo 
$form->render();

$tables $migrate->dumpTables();
if (!empty(
$tables)) {
    echo 
'<h5>' _MA_XMFDEMO_TABLE_CURRENT '</h5>';
    
Debug::dump($tables);
}


Back to the XMF Example List
Design by: XOOPS UI/UX Team