gistfile1.txt

<?php
/**
 * Add tours_users table to track user progress on various kera tours
 *
 * @author rgallagher
 * @version 1.0
 *
 */
class Add_tours_users_table
{
    function up()
    {
        echo 'Creating `tours_users` table...<br />';

        execute_sql("CREATE TABLE `tours_users` (
            `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
            `tour_id` int(11) unsigned NOT NULL,
            `user_id` int(11) unsigned NOT NULL,
            `started_at` timestamp NULL DEFAULT NULL,
            `completed_at` timestamp NULL DEFAULT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");

        execute_sql("ALTER TABLE `tours_users` ADD CONSTRAINT `tours_users_user` FOREIGN KEY (`user_id`) REFERENCES `ed_user_master` (`id`) ON DELETE SET NULL ON UPDATE CASCADE");

        execute_sql("ALTER TABLE `tours_users` ADD CONSTRAINT `tours_users_tour` FOREIGN KEY (`tour_id`) REFERENCES `tours` (`id`) ON DELETE SET NULL ON UPDATE CASCADE");

        execute_sql("CREATE TRIGGER `tours_users_started_date` BEFORE INSERT ON `tours_users`
                       FOR EACH ROW
                       BEGIN
                         SET NEW.started_at = CURRENT_TIMESTAMP;
                       END;");

        echo 'Done';
    }

    function down()
    {
        echo 'Dropping `tours_users` table...<br />';
        drop_table('tours_users');

        echo 'Done';
    }
}