redbeanify

redbeanify.readme.txt

INTRODUCTION -- BE SURE TO READ THIS
====================================

If you're the kind of person who just likes to jump in to a new application and not read the manual, feel free to skip down to the section on installing redbeanify, and feel free to unleash it ON A PLAY DATABASE.  I even provide a downloadable play database, if you don't have one of your own.  But, please, please, NEVER use redbeanify on a production or important database without having first read this document ENTIRELY, understood the consequences of running redbeanify, and PRACTICED FIRST on a development database.  Redbeanify will CHANGE THE STRUCTURE OF YOUR DATABASE.  It will very likely require that you (manually) MAKE CHANGES TO LEGACY SOFTWARE.  Failure to read this document, and failure to understand the consequences, could be CATASTROPHIC.  Redbeanify doesn't make any structural changes that can't be manually backed out, if need be, but that will be very messy and very time-consuming, at the very least, and even then, that's assuming that you really know what you're doing.  Knowledge, understanding, and practice are your best friends.  After a good, reliable backup, that is. :)

Redbeanify and all related software is provided on an "as is" basis without warranty of any kind, express or implied.  Under no circumstances shall Gregory Smith or any associates be liable to you or to any other person for any indirect, special, incidental, or consequential damages of any character including, without limitation, damage resulting from loss or corruption of data, or for any and all other damages or losses.  If you do not accept these terms, do not use the software.

The files and file contents that make up the redbeanify utility are Copyright 2012 by Gregory Smith.

Redbeanify is free for all uses, private and commercial.  You may redistribute it; I would appreciate being given credit, but I don't require it.  You may alter it in any way you see fit, but if you do, you must change the name, using something other than 'redbeanify', so it doesn't get confused with the original.

You may send questions and comments to gregorinator@gmail.com.

WHAT IS REDBEANIFY?
===================

RedBeanPHP is an Object Relational Manager (ORM) that allows PHP programs to access certain relational databases using an object model.  One of the great features of RedBean is that it doesn't require any mapping of relational data to object data.  However, to achieve this, RedBean requires that the relational database conform to certain structural rules.  Because of the challenges of changing the structure of an existing relational database, RedBean is often used only for new projects, where a new database is being created.

However, I wanted the option to be able to use RedBeanPHP for an existing, legacy database.  Hence, "RedBean-ify" (rhymes with "terrify" :).  Redbeanify operates on an existing relational database (currently only MySQL is supported, though I'd like to add SQLite) to alter its structure automatically, to the greatest extent possible, to make it RedBean-ready.  Redbeanify also tries reduce the need to change legacy software that accesses the database, though some software changes are almost inevitable.

WHO IS A CANDIDATE FOR REDBEANIFY?
==================================

What might make a legacy database a good candidate for redbeanify-ing?  First, as of this writing, it has to be a MySQL database.  After that, redbeanify is going to work best on what you might call "classically structured" relational databases that use actual data columns for the primary keys -- USER_ID, for example, or SALES_ORDER_NUMBER, or (USER_ID, SALES_ORDER_NUMBER).  Redbeanify can't convert a table if it already contains a column named 'id', or if it already contains an AUTO_INCREMENT column.  Redbeanify will detect these situations and, after notifying you about them, skip these tables, leaving you to make whatever RedBean-compliant changes need to be made manually.  But if you have a lot of such tables in your database, that may be more work than you want to undertake.  When you run redbeanify, it will run a preliminary check for these situations and present you with a report, to aid you in making a decision.

Also, a legacy application might be a bad candidate for redbeanify if it would require a lot of changes to legacy software.  This will be discussed in more detail below.

WHAT DOES REDBEANIFY DO?
========================

Redbeanify starts by presenting a form where you provide database connection information.  It then runs an analysis of the database to see what tables cannot be automatically converted.  If you review the problem report and decide to proceed, redbeanify will:

 1. Creates a new UNIQUE KEY to correspond to all existing PRIMARY KEY indexes -- this ensures that any legacy applications that depend on the database to enforce uniqueness, or require an index, will continue to work.  It also ensures that any existing referencing FOREIGN KEY constraints will have an index to use. 

 2. Drops all existing PRIMARY KEY indexes.

 3. Creates a new 'id' auto-increment column to be the new primary key in all tables.

 4. Assuming that you want a RedBean 'own' relationship between all tables that have legacy FOREIGN KEY relationships, redbeanify creates an 'own' _id column in all referencing tables, creates a FOREIGN KEY linking it back to the referenced table, and populates it with the referenced table's id values.
 
That's it.

WHAT IMPACT WILL REDBEANIFYING MY DATABASE HAVE ON MY LEGACY SOFTWARE?
======================================================================

Redbeanify changes your existing primary keys into unique indexes, so there should be little impact on performance, and the database will continue to enforce uniqueness.  Also, foreign key references to the former primary key will still work.  If you have legacy software that reads, updates, and deletes rows using the former primary key columns, they should continue to work with no modification.

The new RedBean 'id' column initializes itself on INSERT, so there shouldn't be any need to code for it in your legacy applications.

The one complication that will affect your legacy software is the addition of the 'own' _id columns to referencing tables.  Anywhere you had a foreign key linking a referencing table to a referenced table, redbeanify created a new column with the name {referencing table}_id, and RedBeanPHP expects that this column's value will be the 'id' of the corresponding row in the referenced table.  If your legacy code inserts rows into a referencing table, or updates the foreign key columns, it will have to read the 'id' of the referenced row and set that value into the {referenced table}_id column.  An example might make it more clear:

You had two database tables, like so:

CREATE TABLE OWNER
(OWNER_NAME VARCHAR (60) NOT NULL PRIMARY KEY,
 OWNER_TELEPHONE_NUMBER VARCHAR(30) NULL
 );

CREATE TABLE PET
(PET_NAME VARCHAR (60) NOT NULL PRIMARY KEY,
 OWNER_NAME VARCHAR (60) NOT NULL,
 FOREIGN KEY (OWNER_NAME) REFERENCES OWNER(OWNER_NAME)
 );

So the PET table (the referencing table) has a foreign key referencing the OWNER table (the referenced table).  Redbeanify thus assumes that you want a RedBean 'own' relationship between the two tables.  After redbeanify-ing, your database will look like this:

CREATE TABLE OWNER
(OWNER_NAME VARCHAR (60) NOT NULL UNIQUE KEY,
 OWNER_TELEPHONE_NUMBER VARCHAR(30) NULL,
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 );

CREATE TABLE PET
(PET_NAME VARCHAR (60) NOT NULL UNIQUE KEY,
 OWNER_NAME VARCHAR (60) NOT NULL,
 FOREIGN KEY (OWNER_NAME) REFERENCES OWNER(OWNER_NAME),
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 owner_id INT,
 FOREIGN KEY (owner_id) REFERENCES OWNER(id)
 );

The one change that affects your legacy programs is that column owner_id that got added to the PET table.  Any legacy program that (a) inserts a row into PET, or (b) updates the OWNER_NAME column in PET (changing the OWNER row referenced by the PET row) has to be modified to get the 'id' column from OWNER and set that value into owner_id in the PET row.  If you don't, RedBeanPHP won't recognize the 'own' relationship between that individual owner and that individual pet.

If you try to work around this problem using database triggers, just remember that any inserts or updates performed by RedBeanPHP itself will have already provided values for the 'own' _id columns.

Oh, and I said this would be the "one" thing that affects your legacy apps.  Maybe... not.  There's one other possible complication, and it can be a big one:  If your legacy applications examine the structure of your database tables and take certain actions based on the primary key columns, that will be a problem, because redbeanify changes the primary key columns.  For example, your legacy application may have generalized code for displaying the contents of a table row in a form, and it may automatically examine the table structure to determine the table's primary key columns, and display those in the form's header -- a nice feature!  But it won't work if the table has been redbeanified, because the primary key columns will have been changed.  Your only options are to give up on redbeanify-ing this database, or come up with an alternative method for achieving this functionality and implementing it in your legacy software.

WHAT IMPACT WILL MY LEGACY DATABASE HAVE ON USING RedBeanPHP?
==============================================================

When using RedBeanPHP on a brand new project, you probably don't think much about case conventions for column names.  For example, if you want a 'Customer' table with a 'Name" column, you just use:

	$customer = R::dispense('customer');
	$customer->FirstName = 'Greg';
	
...and it just "works".  When you redbeanify an existing legacy database, however, someone else (or maybe it was you) created the column names in advance.  And one thing I have found is that, when you refer to them using RedBeanPHP, you must use the column names exactly as they are in the database definition, including using the correct letter case.  For example, it's common in "COBOL style" databases to use all upper-case for column names; CUST_FIRST_NAME, for example.  After you redbeanify this database, you must reference this column like this:

	$customer->CUST_FIRST_NAME

$customer->cust_first_name will not work.  Neither will $customer->Cust_First_Name.  The RedBeanPHP property must match the database column name exactly.

The example database that I have provided for practice and illustration uses several different column naming conventions in different tables.  If you look at the provided demo PHP program that uses RedBean to access the demo database, you'll see how the different columns in the different tables must be referenced.

WHAT TABLES WILL REDBEANIFY NOT CONVERT?
========================================

Redbeanify will not convert tables where it finds one or more of these four conditions:

 1. The table already has a column named 'id'.  Obviously, a table can't have two 'id' columns, and redbeanify doesn't know what to make of the one you already have.  For example, perhaps it's where your legacy application stores a user's login id.  In that case, if you want to be able to use RedBeanPHP to read this table, you'll have to rename your 'id' column to something like Login_Id and make the corresponding changes in your legacy software first.

 2. The table already has an AUTO_INCREMENT column.  A MySQL table can't have two AUTO_INCREMENT columns, and since redbeanify doesn't know what role this column plays in your legacy applications, it can't change it's AUTO_INCREMENT status.

 3. The table has two or more foreign keys which both reference the same referenced table.  For example, a MENTOR_RELATIONSHIP table has two columns, MENTOR and MENTOREE, which both have foreign keys that reference the EMPLOYEE_MASTER table.  RedBeanPHP is perfectly capable of handling this situation with its aliasing feature, but naming the relationships requires human interaction, so you have to set up the RedBean columns and relationships manually.

 4. The table is okay in itself, but has a foreign key that references another table, and that other table has a problem.  Since redbeanify won't create an 'id' column in the referenced table, it can't create a corresponding 'own' _id column in this table, so this table will be skipped.

Note that when redbeanify identifies tables it can't convert (and it will list them for you), you have a few options.  One is to manually alter your database structure to remove the problem.  Note, however, that this will probably mean altering your legacy software.  A second option is to go ahead with the redbeanify -- the rest of the tables will still be converted -- and simply acknowledge that you won't be able to use RedBeanPHP to access these tables, on the skipped list.  That might be a compromise that you're willing to make.

The third option, if the skipped tables are too numerous or critical, and the burden of altering them is too great, is to simply abandon the idea of redbeanify-ing your database, and using RedBeanPHP as your ORM for this application.

WHAT ABOUT VIEWS?
=================

Redbeanify does not touch views.  Views may represent subsets of columns or aggregations of rows, they may contain computed columns, and they may merge multiple tables.  Analyzing views and deciding which need RedBean columns, and which RedBean columns they need, requires human knowledge and intelligence.  I suggest that after you practice your redbeanify-ing on the development copy of your production database, you look at the new table structure, and at any views that are based on those tables, and decide for yourself if or how to incorporate the new RedBean columns.

HOW DO I INSTALL REDBEANIFY?
============================
 
Redbeanify is a single, self-contained file.  It isn't pretty, but I stuffed everything into a single file to make it easy to install and use.  Simply download it and save it where you keep your Web docs.  It's named redbeanify.php, but you can rename it to whatever you want, if you want.

There is also a redbeanify demo database.  The SQL to build the database in your own MySQL server is in redbeanify.demo.sql.  Once you've built the redbeanify demo database and redbeanified it, the redbeanify demo RedBeanPHP program, redbeanify.demo.php, verifies that the converted database can in fact be used with RedBeanPHP.  See next section.

WHAT IS THE REDBEANIFY DEMO DATABASE?
=====================================

I've created simple (and whimsical) MySQL database that demonstrates how redbeanify will convert many legacy relational constructs.  It also contains tables that will deliberately trigger all of the redbeanify error conditions, so you can see what the error reporting looks like.  The demo database includes several column naming conventions seen in relational databases, like ALL_CAPS, MixedCase, and alllowercase.

To set up the demo database, simply download redbeanify.demo.sql, fire up your favorite MySQL database editing tool, such as Toad for MySQL, and execute the SQL script.  The demo database is named 'redbeanify'.  Take a look at its structure before you redbeanify it -- you'll want to compare the tables before to the tables after, to see how redbeanify changed the database, both in columns added, and in indexes and foreign keys.

Follow the instructions in the next section to redbeanify the 'redbeanify' database.  Then look at the structures and compare them to how they were.

There's also a PHP program, redbeanify.demo.php, that uses RedBeanPHP to add a few rows to the 'redbeanify' database and do some reporting.  Simply save it to your Web server's docs folder, and point your favorite Web browser at it.  It verifies that the conversion process worked, and demonstrates how to access legacy tables using their legacy table names.  It requires that the RedBeanPHP class be present in the same directory.  If you don't like this, you can edit redbeanify.demo.php to point to where the redbean class resides.

HOW DO I USE REDBEANIFY?
========================

 1. Create a copy of your production database on your development server and try redbeanify there first (skipping down to step 5), to discover any issues you may encounter.

 2. When you're ready to redbeanify your production database, back up the database first.
 
 3. Make a second backup copy.
 
 4. It wouldn't hurt to have a third backup.  Seriously, if something goes wrong while redbeanify is running, you could be left with a really messed up database that will take some doing to untangle.
 
 5. Launch your favorite Web browser and use it to open redbeanify.php, or whatever you called the program when you downloaded and saved it.
 
 6. Redbeanify will display a form asking you for your database connection information.  The password defaults to 'root' and the database name defaults to 'redbeanify', which is the name of the demo database provided for this application.  When you click [Connect and Check Database], redbeanify will open the database and check to see if there are any tables that are not candidates for conversion.  NO CHANGES TO THE DATABASE ARE MADE IN THIS STEP.
 
 7. Redbeanify will now display a page listing all the tables it cannot convert, and the reasons why.  If you're using the demo database, you'll see one of each of the four possible error conditions.  At this time, you have to decide whether or not you want to proceed to convert the rest of the tables in your database, or fix some of the error conditions before proceeding, or just give up altogether, if there are too many errors.  If you stop now, nothing will have been changed.
 
 8. If you want to continue to convert the database, click [Redbeanify (Convert) Database].  Eligible tables will be converted.  Your database is being changed.  Obviously, it would be a very bad idea to close your Web browser or do anything else to disturb this process before it completes.  If you've already practiced on your development server (see step 1), you should have confidence that all will go well.
 
 9. Redbeanify displays the word "Completed".  You're ready to start using RedBeanPHP with this database.

redbeanify.php

<?php

// Communicate with the spirits of pages past and future

session_start();

$action = $_POST['action'];

if (!isset($action))
{
    print "<html><head></head><body><form method='post' action='{$_SERVER['REQUEST_URI']}'><label>Server:</label><input type='text' id='server' name='server' value='localhost' /><br /><label>Login ID:</label><input type='text' id='loginid' name='loginid' value='root' /><br /><label>Password:</label><input type='password' id='password' name='password' value='root' /><br /><label>Database:</label><input type='text' id='databaseName' name='databaseName' value='redbeanify' /><br /><label><input type='checkbox' name='disableTimeout' id='disableTimeout' checked />Disable PHP timeout (assumes not in safe mode)</label><br /><input type='hidden' id='action' name='action' value='check' /><input type='submit' value='Connect and Check Database' /></form><p>redbeanify version 1.0, 25 April 2012</p></body></html>";
    exit();
}
else if ($action == "check")
{
    $server = $_POST['server'];
    $loginId = $_POST['loginid'];
    $password = $_POST['password'];
    $databaseName = $_POST['databaseName'];
    $disableTimeout = isset($_POST['disableTimeout']);
    try
    {
        if ($disableTimeout)
        {
            set_time_limit(0);
        }
        $database = new Database($server, $loginId, $password, $databaseName);
        $checkResults = $database->ReturnCheckResults();
        $_SESSION['server'] = $server;
        $_SESSION['loginid'] = $loginId;
        $_SESSION['password'] = $password;
        $_SESSION['databaseName'] = $databaseName;
        $_SESSION['disableTimeout'] = $disableTimeout;
        $_SESSION['database'] = $database; // Will this work?
    }
    catch (Exception $ce)
    {
        die ($ce->getMessage());
    }
    print "<html><head><style type=\"text/css\">table,th,td {border: 1px solid black;padding: 0.5em;}</style></head><body><h1>Check Results</h1>";
    if (count($checkResults) == 0)
    {
        print "<h3>No problems found!  Maybe you should buy a lottery ticket today.  It could be your lucky day.</h3>";
    }
    else
    {
        print "<table><tr><th>Table</th><th>Problem</th><th>Action</th></tr>";
        foreach ($checkResults as $checkResult)
        {
            print "<tr><td>{$checkResult['Table']}</td><td>{$checkResult['Problem']}</td><td>{$checkResult['Action']}</td></tr>";
        }
        print "</table>";
    }
    print "<form method='post' action='{$_SERVER['REQUEST_URI']}'><input type='hidden' id='action' name='action' value='convert' /><input type='submit' value='RedBeanify (Convert) Database' /></form></body></html>";
    exit();
}
else if ($action == "convert")
{
    $database = $_SESSION['database'];
    if (!isset($database))
    {
        die ('SESSION variable does not contain database object');
    }
    try
    {
        if ($_SESSION['disableTimeout'])
        {
            set_time_limit(0);
        }
        $database->RefreshDatabaseConnection($_SESSION['server'], $_SESSION['loginid'], $_SESSION['password'], $_SESSION['databaseName']);
        $database->redbeanify();
    }
    catch (Exception $ce)
    {
        die ($ce->getMessage());
    }
    print "<html><head></head><body><h1>Completed</h1></body></html>";
}
else
{
    die ("Unrecognized action {$action}");
}

/***********
 * Classes *
 ***********/

class Database
{
    private $mysqliConnection;
    private $databaseName;
    private $activated = false;
    private $tables = array();
    private $checkResults = array();

    private $sql;

    function __construct($server, $loginId, $password, $databaseName)
    {
        $this->OpenDatabaseConnection($server, $loginId, $password, $databaseName);
        $this->databaseName = $databaseName;

        $this->GetTables();
    }

    private function OpenDatabaseConnection($server, $loginId, $password, $databaseName)
    {
        $this->mysqliConnection = new mysqli($server, $loginId, $password, $databaseName);
        if (mysqli_connect_errno())
        {
            throw new Exception("Connect to database failed: " . mysqli_connect_error() . " ({$server}, {$loginId}, {$databaseName})");
        }
    }

    function RefreshDatabaseConnection($server, $loginId, $password, $databaseName)
    {
        $this->OpenDatabaseConnection($server, $loginId, $password, $databaseName);
        foreach ($this->tables as $table)
        {
            $table->SetDatabaseConnection($this->mysqliConnection);
        }
    }

    function ReturnCheckResults()
    {
        // Check out each table's' internals
        foreach ($this->tables as $table)
        {
            $tableCheckResults = $table->ReturnExceptionMessages();
            foreach ($tableCheckResults as $tableCheckResult)
            {
                $this->checkResults[] = $tableCheckResult;
            }
        }
        // Now check to see if any included table references an excluded table
        foreach ($this->tables as $table)
        {
            if ($table->skipThisTable)
            {
                continue;
            }
            foreach ($table->referencedTables as $referencedTable)
            {
                if ($this->tables[$referencedTable["Referenced Table"]]->skipThisTable)
                {
                    $table->skipThisTable = true;
                    $this->checkResults[] = array ( "Table" => $table->tableName,
                    "Problem" => "Table references table {$referencedTable["Referenced Table"]}, which is excluded", "Action" => "Skip table" );
                }
            }
        }

        return $this->checkResults;
    }

    private function GetTables()
    {
        try
        {
            $sql = "SHOW TABLES";
            $tableRows = $this->mysqliConnection->query($sql);
            while ($tableRow = $tableRows->fetch_row())
            {
                $table = new Table($this->mysqliConnection, $this->databaseName, $tableRow[0]);
                $this->tables[$table->tableName] = $table;
            }
            $tableRows->free();
        }
        catch (Exception $ge)
        {
            throw new Exception($ge->getMessage() . "<p>" . $sql);
        }
    }

    function redbeanify()
    {
        try
        {
            $this->ConvertPrimaryKeys();
            $this->AddForeignKeys();
        }
        catch (Exception $re)
        {
            throw new Exception($re->getMessage());
        }
    }

    function ConvertPrimaryKeys()
    {
        try
        {
            foreach ($this->tables as $table)
            {
                $table->ConvertPrimaryKey();
            }
        }
        catch (Exception $ke)
        {
            throw $ke;
        }
    }

    function AddForeignKeys()
    {
        try
        {
            foreach ($this->tables as $table)
            {
                $table->AddForeignKey();
            }
        }
        catch (Exception $ke)
        {
            throw $ke;
        }
    }
}

class Table
{
    var $tableName;
    var $referencedTables = array();
    var $skipThisTable = false;

    private $mysqliConnection;
    private $databaseName;

    private $hasPrimaryKey = false;
    private $primaryKeyColumns = array();
    private $hasColumnNamedId = false;
    private $hasAutoIncrementColumn = false;
    private $hasMultipleFksToSamePk = false;

    private $sql;

    function __construct($mysqliConnection, $databaseName, $tableName)
    {
        $this->mysqliConnection = $mysqliConnection;
        $this->databaseName = $databaseName;
        $this->tableName = $tableName;

        $this->GetColumnMetaData();
    }

    function SetDatabaseConnection($mysqliConnection)
    {
        $this->mysqliConnection = $mysqliConnection;
    }

    private function GetColumnMetaData()
    {
        try
        {
            $sql = "SHOW COLUMNS FROM " . $this->tableName;
            $columnRows = $this->mysqliConnection->query($sql);
            while ($columnRow = $columnRows->fetch_assoc())
            {
                if (strtolower($columnRow["Field"]) == "id")
                {
                    $this->hasColumnNamedId = true;
                }
                if ($columnRow["Key"] == "PRI")
                {
                    $this->primaryKeyColumns[] = $columnRow["Field"];
                    $this->hasPrimaryKey = true;
                }
                if ($columnRow["Extra"] == "auto_increment")
                {
                    $this->hasAutoIncrementColumn = true;
                }
            }
            $columnRows->free();

            // set $this->referencedTables.
            $sql = "SELECT CONSTRAINT_NAME,COLUMN_NAME,ORDINAL_POSITION,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME  " .
                "FROM information_schema.KEY_COLUMN_USAGE " .
                "WHERE CONSTRAINT_SCHEMA = '{$this->databaseName}' AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = '{$this->tableName}' " .
                "ORDER BY CONSTRAINT_NAME,ORDINAL_POSITION";
            // This array has one entry per foreign key, identifying referenced table and all pairs of columns in the FK
            // array ( "Referenced Table" => $referencedTableRow['REFERENCED_TABLE_NAME'], "Constraint Name" => $referencedTableRow['CONSTRAINT_NAME'],
            //      "Column Pairs" => array ( array ( "Referencing Column" => $referencedTableRow['COLUMN_NAME'], "Referenced Column" => $referencedTableRow['REFERENCED_COLUMN_NAME'] ), ... ) )
            $referencedTablesQuery = $this->mysqliConnection->query($sql);
            $previousConstraintName = "!@#%^";
            while ($referencedTableRow = $referencedTablesQuery->fetch_assoc())
            {
                if ($referencedTableRow["CONSTRAINT_NAME"] != $previousConstraintName)
                {
                    if ($previousConstraintName != "!@#%^")
                    {
                        $nextConstraint["Column Pairs"] = $columnPairs;
                        $this->referencedTables[] = $nextConstraint;
                        // Check new table name:  If a referenced table shows up in the list more than once, that's a problem.
                        foreach ($this->referencedTables as $referencedTable)
                        {
                            if ($referencedTable["Referenced Table"] == $referencedTableRow["REFERENCED_TABLE_NAME"])
                            {
                                $this->hasMultipleFksToSamePk = true;
                                break;
                            }
                        }
                    }
                    $previousConstraintName = $referencedTableRow["CONSTRAINT_NAME"];
                    $nextConstraint = array ( "Referenced Table" => $referencedTableRow['REFERENCED_TABLE_NAME'],
                                            "Constraint Name" => $referencedTableRow['CONSTRAINT_NAME'] );
                    $columnPairs = array();
                }
                $columnPairs[] = array ( "Referencing Column" => $referencedTableRow['COLUMN_NAME'], "Referenced Column" => $referencedTableRow['REFERENCED_COLUMN_NAME'] );
            }
            // Flush last value to array
            if ($previousConstraintName != "!@#%^")
            {
                $nextConstraint["Column Pairs"] = $columnPairs;
                $this->referencedTables[] = $nextConstraint;
            }
            $referencedTablesQuery->free();
        }
        catch (Exception $ge)
        {
            throw new Exception($ge->getMessage() . "<p>" . $sql);
        }
    }

    function ReturnExceptionMessages()
    {
        $exceptionMessages = array();
        if ($this->hasColumnNamedId)
        {
            $exceptionMessages[] = array ( "Table" => $this->tableName, "Problem" => "Table already has a column named 'id'", "Action" => "Skip table" );
            $this->skipThisTable = true;
        }
        if ($this->hasAutoIncrementColumn)
        {
            $exceptionMessages[] = array ( "Table" => $this->tableName, "Problem" => "Table already has an autoincrement column", "Action" => "Skip table" );
            $this->skipThisTable = true;
        }
        if ($this->hasMultipleFksToSamePk)
        {
            $exceptionMessages[] = array ( "Table" => $this->tableName,
                "Problem" => "Table has a referencing table with more than one foreign key (need aliasing)", "Action" => "Skip table" );
            $this->skipThisTable = true;
        }
        return $exceptionMessages;
    }

    function ConvertPrimaryKey()
    {
        if ($this->skipThisTable)
        {
            return;
        }

        try
        {
            if ($this->hasPrimaryKey)
            {
                //  Add a UNIQUE KEY that duplicates this PRIMARY KEY
                $sql = "ALTER TABLE {$this->tableName} ADD UNIQUE KEY (";
                foreach ($this->primaryKeyColumns as $primaryKeyColumn)
                {
                    $sql .= $primaryKeyColumn . ",";
                }
                $sql = trim($sql, ",") . ")";
                $this->mysqliConnection->query($sql);
                // Drop the PRIMARY KEY
                $sql = "ALTER TABLE {$this->tableName} DROP PRIMARY KEY";
                $this->mysqliConnection->query($sql);
            }
            // Add a new PRIMARY KEY column for the id
            $sql = "ALTER TABLE {$this->tableName} ADD id INT AUTO_INCREMENT PRIMARY KEY";
            $this->mysqliConnection->query($sql);
        }
        catch (Exception $pke)
        {
            throw new Exception($pke->getMessage() . "<p>" . $sql);
        }
    }

    function AddForeignKey()
    {
        if ($this->skipThisTable)
        {
            return;
        }

        try
        {
            // $this->referencedTables is an array with one entry per foreign key, identifying referenced table and all pairs of columns in the FK
            // array ( "Referenced Table" => $referencedTableRow['REFERENCED_TABLE_NAME'], "Constraint Name" => $referencedTableRow['CONSTRAINT_NAME'],
            //      "Column Pairs" => array ( array ( "Referencing Column" => $referencedTableRow['COLUMN_NAME'], "Referenced Column" => $referencedTableRow['REFERENCED_COLUMN_NAME'] ), ... ) )
            foreach ($this->referencedTables as $referencedTable)
            {
                $referencedTableName = $referencedTable['Referenced Table'];
                // For each FOREIGN KEY, add a new column pointing back to the PRIMARY KEY
                $sql = "ALTER TABLE {$this->tableName} ADD {$referencedTableName}_id INT NULL";
                $this->mysqliConnection->query($sql);
                // Add a new FOREIGN KEY to link the two tables
                $sql = "ALTER TABLE {$this->tableName} ADD FOREIGN KEY ({$referencedTableName}_id) REFERENCES {$referencedTableName} (id)";
                $this->mysqliConnection->query($sql);
                // Initialize the new FOREIGN KEY column(s)
                $sql = "UPDATE {$this->tableName},{$referencedTableName} SET {$referencedTableName}_id = {$referencedTableName}.id WHERE ";
                foreach ($referencedTable['Column Pairs'] as $columnPair)
                {
                    $sql .= "{$this->tableName}.{$columnPair['Referencing Column']} = {$referencedTableName}.{$columnPair['Referenced Column']} AND ";
                }
                $sql = substr($sql, 0, strlen($sql) - 5); // trim off the last AND
                $this->mysqliConnection->query($sql);
            }
        }
        catch (Exception $fke)
        {
            throw new Exception($fke->getMessage() . "<p>" . $sql);
        }
    }
}

?>

redbeanify.readme.txt	Text file explaining what redbeanify is, what it does, how it works, and what the consequences are. Do not use redbeanify without reading and understanding the contents of this file.	Download	View
redbeanify.php	The redbeanify conversion utility. This utility will change the structure of your database. Do not use before reading the "read me" file, and practicing on a test copy of your production database first.	Download	View
redbeanify.demo.sql (optional)	SQL to create a demo database called 'redbeanify'. If you like, you can create the database, and then run redbeanify.php against it, and then study it to see what the effects of redbeanify are.	Download	View
redbeanify.demo.php (optional)	Optional utility that goes with the demo database. After you redbeanify the demo database, this utility demonstrates using RedBeanPHP to interact with it.	Download	View

redbeanify

Disclaimer, Distribution, and Support