ORM Revision Control

ORM_Revision.php

<?php

class ORM_Revision extends Kohana_ORM {

        // revision table prefix (default _revision_)
        protected $_revision_table_prefix = '_revision_';

        // are we going for draft or published
        protected $_draft = FALSE;

        // create table revision control
        // note: you can run this function each time you change your table structure,
        // but remember to change also the _revision_(tablename) structure too.
        public function rev()
        {
                $columns = [];

                // get original columns
                foreach (DB::query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$this->_table_name, FALSE)->execute() AS $row)
                {
                        if (strpos($row['Field'], '_revision') !== 0)
                                $columns[$row['Field']] = $row['Type'];
                }

try {
                echo "[ ] cloning original table".PHP_EOL;

                // create table like original
                DB::query(Database::INSERT, 'CREATE TABLE `'.$this->_revision_table_prefix.$this->_table_name.'` LIKE `'.$this->_table_name.'`;')
                ->execute();

                echo "[ ] altering cloned table".PHP_EOL;

                // alter revision table
                DB::query(Database::UPDATE, 'ALTER TABLE `'.$this->_revision_table_prefix.$this->_table_name.'`'.PHP_EOL
                 . ' CHANGE `id` `id` INT(11) UNSIGNED NOT NULL,'.PHP_EOL
                 . ' DROP PRIMARY KEY,'.PHP_EOL
                 . ' ADD `_revision` INT(11) UNSIGNED AUTO_INCREMENT,'.PHP_EOL
                 . ' ADD `_revision_publish` TINYINT(1) UNSIGNED NULL,'.PHP_EOL
                 . ' ADD `_revision_user_id` INT(11) UNSIGNED NULL,'.PHP_EOL
                 . ' ADD `_revision_timestamp` DATETIME NULL DEFAULT NULL,'.PHP_EOL
                 . ' ADD `_revision_comment` TEXT NULL,'.PHP_EOL
                 . ' ADD PRIMARY KEY (`_revision`),'.PHP_EOL
                 . ' ADD INDEX `org_primary` (`id`)'
                )
                ->execute();

                echo "[ ] fill cloned table".PHP_EOL;

                // fill revision table with original rows
                DB::query(Database::INSERT, 'INSERT INTO `'.$this->_revision_table_prefix.$this->_table_name.'` SELECT *, NULL, NULL, NULL, NOW(), \'Initial row revision\' FROM `'.$this->_table_name.'`;')
                ->execute();

                echo "[ ] alter original table".PHP_EOL;

                // alter original table
                DB::query(Database::UPDATE, 'ALTER TABLE `'.$this->_table_name.'`'.PHP_EOL
                 . ' ADD `_revision` INT(11) UNSIGNED NULL,'.PHP_EOL
                 . ' ADD `_revision_publish` TINYINT(1) UNSIGNED NULL,'.PHP_EOL
                 . ' ADD `_revision_user_id` INT(11) UNSIGNED NULL,'.PHP_EOL
                 . ' ADD `_revision_comment` TEXT NULL,'.PHP_EOL
                 . ' ADD UNIQUE INDEX (`_revision`)'
                )
                ->execute();

} catch (Database_Exception $e) {

                echo "[ ] skipping table work".PHP_EOL;

}

                // create insert trigger
                $it = 'CREATE TRIGGER `'.$this->_table_name.'_before_insert` BEFORE INSERT ON `'.$this->_table_name.'`'.PHP_EOL
                    . ' FOR EACH ROW BEGIN'.PHP_EOL
                    . '  INSERT INTO `'.$this->_revision_table_prefix.$this->_table_name.'` (`'.implode('`, `', array_keys($columns)).'`, `_revision_comment`, `_revision_user_id`, `_revision_timestamp`) VALUES (NEW.`'.implode('`, NEW.`', array_keys($columns)).'`, NEW.`_revision_comment`, NEW.`_revision_user_id`, NOW());'.PHP_EOL
                    . '  SET NEW.`_revision` = LAST_INSERT_ID();'.PHP_EOL
                    . '  SET NEW.`_revision_publish` = NULL;'.PHP_EOL
                    . '  SET NEW.`_revision_comment` = NULL;'.PHP_EOL
                    . ' END'.PHP_EOL;

                // create update trigger
                $ut =  'CREATE TRIGGER `'.$this->_table_name.'_before_update` BEFORE UPDATE ON `'.$this->_table_name.'`'.PHP_EOL
                    .  ' FOR EACH ROW BEGIN'.PHP_EOL; foreach ($columns as $column => $type)
                $ut .= '  DECLARE `var_'.$column.'` '.$type.';'.PHP_EOL;
                $ut .= '  DECLARE revisionCursor CURSOR FOR SELECT `'.implode('`, `', array_keys($columns)).'` FROM `'.$this->_revision_table_prefix.$this->_table_name.'` WHERE `_revision` = OLD.`_revision` AND `id` = OLD.`id` LIMIT 1;'.PHP_EOL
                    .  '  IF NEW.`_revision_publish` IS NULL THEN'.PHP_EOL
                    .  '   INSERT INTO `'.$this->_revision_table_prefix.$this->_table_name.'` (`'.implode('`, `', array_keys($columns)).'`, `_revision_comment`, `_revision_user_id`, `_revision_timestamp`) VALUES (NEW.`'.implode('`, NEW.`', array_keys($columns)).'`, NEW.`_revision_comment`, NEW.`_revision_user_id`, NOW());'.PHP_EOL
                    .  '   SET NEW.`_revision` = LAST_INSERT_ID();'.PHP_EOL; foreach ($columns as $column => $type)
                $ut .= '   SET NEW.`'.$column.'` = OLD.`'.$column.'`;'.PHP_EOL;
                $ut .= '  ELSE'.PHP_EOL
                    .  '   OPEN revisionCursor;'.PHP_EOL
                    .  '    FETCH revisionCursor INTO `var_'.implode('`, `var_', array_keys($columns)).'`;'.PHP_EOL
                    .  '   CLOSE revisionCursor;'.PHP_EOL
                    .  '   SET NEW.`_revision_publish` = NULL;'.PHP_EOL; foreach ($columns as $column => $type)
                $ut .= '   SET NEW.`'.$column.'` = `var_'.$column.'`;'.PHP_EOL;
                $ut .= '  END IF;'.PHP_EOL
                    .  'END'.PHP_EOL;

                echo "[ ] dropping triggers".PHP_EOL;

                DB::query(Database::DELETE, "DROP TRIGGER IF EXISTS `".$this->_table_name."_before_insert`")->execute();
                DB::query(Database::DELETE, "DROP TRIGGER IF EXISTS `".$this->_table_name."_before_update`")->execute();

                echo "[ ] creating triggers".PHP_EOL;

                DB::query(Database::INSERT, $it)->execute();
                DB::query(Database::INSERT, $ut)->execute();

                echo "[ ] done!";
        }

        // get all revisions for record
        public function list_revisions()
        {
                if ( ! $this->_loaded)
                        throw new Kohana_Exception('Cannot list :model model revisions because it is not loaded.', array(':model' => $this->_object_name));

                $list = DB::select(
                        ['_revision', 'revision'],
                        ['_revision_action', 'state'],
                        ['_revision_user_id', 'user_id'],
                        ['_revision_timestamp', 'timestamp'],
                        ['_revision_comment', 'comment']
                )
                ->from($this->_revision_table_prefix.$this->_table_name)
                ->where('id', '=', $this->id)
                ->order_by('revision', 'ASC')
                ->as_object();

                try
                {
                        return $list->execute($this->_db)->as_array();
                }
                catch (Database_Exception $e)
                {
                        throw (strpos($e->getMessage(), 'doesn\'t exist')) ? new Kohana_Exception('Revision table was not found for model.') : $e;
                }
        }

        // set draft flag
        public function draft()
        {
                $this->_draft = TRUE;
        }

        // get published
        public function published()
        {
                $this->_draft = FALSE;
        }

        public function publish()
        {
                if ($this->_saved)
                {
                        $this->_revision_publish = 1;
                        $this->save();
                }
        }

        public function find_all()
        {
                $tmp = $this->_table_name;

                if ($this->_draft)
                {
                        $this->_table_name = $this->_revision_table_prefix.$tmp;

                        $latest = DB::select('id', [DB::expr('MAX(`_revision`)'), 'latest'])
                        ->from($this->_table_name)
                        ->group_by('id');

                        $this->join([$latest, 'l'], 'INNER')
                        ->on($this->_object_name.'.id', '=', 'l.id')
                        ->on($this->_object_name.'._revision', '=', 'l.latest');
                }

                $res = parent::find_all();

                $this->_table_name = $tmp;

                return $res;
        }

        public function find()
        {
                $tmp = $this->_table_name;

                if ($this->_draft)
                {
                        $this->_table_name = $this->_revision_table_prefix.$tmp;
                        $this->order_by('_revision', 'DESC');
                }

                $res = parent::find();

                $this->_table_name = $tmp;

                return $res;
        }

} // End ORM_Revision