Skip to content

Instantly share code, notes, and snippets.

@chrisforrence
Last active March 20, 2017 09:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save chrisforrence/8303815 to your computer and use it in GitHub Desktop.
Save chrisforrence/8303815 to your computer and use it in GitHub Desktop.
CLI script to create revision table and associated triggers for specified table
<?php
/**
* Creates a revision table for an already-existing table.
* TODO: Better error checking/handling
* TODO: Sanitize table name input
*
* Usage:
* [user@host]$ php MySQLRevision.php
* // Table name: <enter table name>
*
* The MIT License (MIT)
*
* Copyright (c) 2014 Chris Forrence
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in all
* copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
* SOFTWARE.
*
*/
const DB_TYPE = 'mysql';
const DB_NAME = '';
const DB_HOST = '';
const DB_USER = '';
const DB_PASS = '';
try {
$dbh = new PDO(DB_TYPE . ":dbname=" . DB_NAME . ";host=" . DB_HOST, DB_USER, DB_PASS, array(PDO::ATTR_PERSISTENT => true));
}
catch(PDOException $e){
$dbh = null;
}
if($dbh == null) {
echo 'Could not get connection to database. Exiting.' . PHP_EOL;
exit();
}
echo '// Table name: ';
$tn = trim(fgets(STDIN));
if(strlen($tn) > 0) {
$toRet = array();
list($query, $columns) = generateRevisionTable($dbh, $tn);
if(is_array($columns) && count($columns) > 0) {
$toRet[] = 'DROP TRIGGER IF EXISTS `revisions_' . $tn . '_trigger_i`;';
$toRet[] = 'DROP TRIGGER IF EXISTS `revisions_' . $tn . '_trigger_u`;';
$toRet[] = 'DROP TRIGGER IF EXISTS `revisions_' . $tn . '_trigger_d`;';
$toRet[] = 'DROP TABLE IF EXISTS `revisions_' . $tn . '`;';
$toRet[] = $query;
unset($query);
$toRet[] = 'DELIMITER ;;';
$toRet[] = generateInsert($tn, $columns);
$toRet[] = generateUpdate($tn, $columns);
$toRet[] = generateDelete($tn, $columns);
$toRet[] = 'DELIMITER ;';
}
else {
$toRet[] = '// Could not find columns';
}
foreach($toRet as $q) {
echo PHP_EOL . $q . PHP_EOL;
}
}
$dbh = null;
unset($dbh);
function generateRevisionTable($dbh, $tn) {
try {
$q = $dbh->prepare("SHOW COLUMNS FROM $tn");
$q->execute();
$toRet = array();
$table_fields = $q->fetchAll();
$c = 'CREATE TABLE `revisions_' . $tn . '` (' . PHP_EOL;
$cl = array();
foreach($table_fields as $t) {
$c .= ' `' . $t['Field'] . '` ' . ($t['Type'] == 'timestamp' ? 'datetime' : $t['Type']) . ',' . PHP_EOL;
$cl[] = $t['Field'];
}
$c .= " `revision_action` ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL," . PHP_EOL;
$c .= " `revision_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," . PHP_EOL;
$c .= " `revised_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," . PHP_EOL;
$c .= " PRIMARY KEY(`revision_id`)" . PHP_EOL . ");";
}
catch(PDOException $e) {
echo $e->getMessage() . PHP_EOL;
$dbh = null;
exit();
}
return array($c, $cl);
}
function generateDelete($tn, $cl) {
$c = "CREATE TRIGGER `revisions_${tn}_trigger_d` BEFORE DELETE ON `${tn}`" . PHP_EOL;
$c .= " FOR EACH ROW BEGIN INSERT INTO revisions_${tn} (";
foreach($cl as $cls) {
$c .= '`' . $cls . '`, ';
}
$c .= 'revision_action) VALUES (';
foreach($cl as $cls) {
$c .= 'OLD.`' . $cls . '`, ';
}
$c .= "'DELETE'); END;;" . PHP_EOL;
return $c;
}
function generateUpdate($tn, $cl) {
$c = "CREATE TRIGGER `revisions_${tn}_trigger_u` AFTER UPDATE ON `${tn}`" . PHP_EOL;
$c .= " FOR EACH ROW BEGIN INSERT INTO revisions_${tn} (";
foreach($cl as $cls) {
$c .= '`' . $cls . '`, ';
}
$c .= 'revision_action) VALUES (';
foreach($cl as $cls) {
$c .= 'NEW.`' . $cls . '`, ';
}
$c .= "'UPDATE'); END;;" . PHP_EOL;
return $c;
}
function generateInsert($tn, $cl) {
$c = "CREATE TRIGGER `revisions_${tn}_trigger_i` AFTER INSERT ON `${tn}`" . PHP_EOL;
$c .= " FOR EACH ROW BEGIN INSERT INTO revisions_${tn} (";
foreach($cl as $cls) {
$c .= $cls . ', ';
}
$c .= 'revision_action) VALUES (';
foreach($cl as $cls) {
$c .= 'NEW.`' . $cls . '`, ';
}
$c .= "'INSERT'); END;;" . PHP_EOL;
return $c;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment