restore_issue.php

<?php
/**
 * Generate SQL script to restore deleted Issues from a database backup.
 *
 * This helper program generates a SQL script to help recovering individual
 * issues from a database backup, e.g. after accidental deletion.
 *
 * Usage instructions:
 *
 * 1. Restore the backup into a new, temporary database
 *
 * 2. Identify the ID(s) of the issue(s) to recover, e.g.
 *    ```sql
 *    SELECT b1.id
 *    FROM production.mantis_bug_table b1
 *    LEFT JOIN backup.mantis_bug_table b2 ON b2.id = b1.id
 *    WHERE b1.id IS NULL;
 *    ```
 *
 * 3. Set up a temporary MantisBT instance
 *    - Copy of the production MantisBT instance's directory to the location
 *      of your choice,
 *    - Update config_inc.php to point to the DB restored in 1.
 *
 * 4. Generate the recovery script
 *    - Copy this program to the root of the temporary MantisBT instance,
 *    - Populate the $g_bug_list variable below with the list of issue IDs
 *      identified in step 2,
 *    - if necessary, update $g_filename to set the path to where the script
 *      should be saved (by default, `restore.sql` in the current directory),
 *    - Save the modified program,
 *    - Run it from CLI (`php restore_issue.php`).
 *
 * 4. Review the generated SQL script.
 *
 * 5. Backup the target database.
 *
 * 5. Manually execute the script in the production database.
 *
 * 6. Restore any file attachments from backup using the tarsnap command
 *
 * 6. Delete the temporary MantisBT instance and drop the restored database.
 */

/**
 * @global int[] $g_bug_list List of Issues to restore
 */
$g_bug_list = array(

);

/**
 * @global string $g_filename Path where to save the generated SQL script.
 */
$g_filename = 'restore.sql';

/**
 * Text for a bugnote to add to each restored bug.
 *
 * Set to empty string to skip adding bugnote.
 * If not empty, {@see $g_username} must be set.
 *
 * @global string $g_bugnote_message
 */
$g_bugnote_message = 'Issue restored from backup following accidental deletion.';

/**
 * @global string $g_username Author of the "Issue restored" bugnote.
 */
$g_username = '';


# ----------------------------------------------------------------------------
# No edit below this line
#

global $g_bypass_headers;
$g_bypass_headers = 1;

include 'core.php';

echo "Generating restore script...\n";

/** @noinspection PhpUnhandledExceptionInspection */
$t_user_id = user_get_id_by_name( $g_username );
if( $g_bugnote_message && ( !$g_username || !$t_user_id ) ) {
	echo "Set the '\$g_username' variable to a valid username\n";
	exit( 1 );
}

if( !$g_bug_list ) {
	echo "Update the '\$g_bug_list' array with the issues to restore\n";
	exit( 1 );
}
echo "Issues to restore: " . implode( ', ', $g_bug_list ) . "\n";

if( file_exists( $g_filename ) ) {
	/** @noinspection PhpComposerExtensionStubsInspection */
	$t_reply = readline( "File '$g_filename' already exists. Overwrite [y] ? " );
	if( strtolower( $t_reply[0] ?? 'y' ) !== 'y' ) {
		echo "Aborting." . PHP_EOL;
		exit( 1 );
	}
}

# List of tables to restore with corresponding key field for bug id
$t_tables = array(
	'bug'                 => 'id',
	'bug_text'            => 'id',            # requires special handling
	'bug_file'            => 'bug_id',
	'bugnote'             => 'bug_id',
	'bugnote_text'        => 'bug_id',        # requires special handling
	'bug_relationship'    => 'source_bug_id', # requires special handling
	'sponsorship'         => 'bug_id',
	'bug_revision'        => 'bug_id',
	'bug_history'         => 'bug_id',
	'bug_monitor'         => 'bug_id',
	'bug_tag'             => 'bug_id',
	'custom_field_string' => 'bug_id',
);
$t_attachments = [];

$t_file = fopen( $g_filename, 'w' );
fwrite( $t_file, "-- MantisBT Issue Restore script" . PHP_EOL );
fwrite( $t_file, "-- Generated by " . basename( __FILE__ )
	. " on " . date( "c" ) . PHP_EOL
);
fwrite( $t_file, "-- Issues to restore: " . implode( ', ', $g_bug_list ) . PHP_EOL . PHP_EOL );

# Main loop
foreach( $t_tables as $t_table => $t_field ) {
	# Build query to retrieve rows to recover
	$t_query = 'SELECT t.* FROM {' . $t_table . '} t';
	$t_where = '';
	switch( $t_table ) {
		case 'bug_text':
			$t_query .= ' JOIN {bug} b ON b.bug_text_id = t.id';
			$t_field = 'b.' . $t_field;
			break;
		case 'bugnote_text':
			$t_query .= ' JOIN {bugnote} n ON n.bugnote_text_id = t.id';
			$t_field = 'n.' . $t_field;
			break;
		case 'bug_relationship':
			$t_where = ' OR ' . where_clause( 'destination_bug_id' );
			break;
	}
	$t_query .= ' WHERE ' . where_clause( $t_field ) . $t_where;

	$t_result = db_query( $t_query );
	$t_row = db_fetch_array( $t_result );

	# Nothing to recover for this table
	if( !$t_row ) {
		continue;
	}

	# Generate Insert statement SQL
	fwrite( $t_file, 'INSERT INTO ' . db_get_table( $t_table ) . ' VALUES ' );
	while( true ) {
		fwrite( $t_file, PHP_EOL . insert_values( $t_row ) );

		# Keep track of attachments
		if( $t_table == 'bug_file' ) {
			$t_attachments[$t_row['id']] = $t_row;
		}

		# Get next row
		$t_row = db_fetch_array( $t_result );
		if( !$t_row ) {
			break;
		}

		fwrite( $t_file, ',' );
	}
	fwrite( $t_file, ';' . PHP_EOL . PHP_EOL );
}

# Insert a bugnote in each restored Issue to indicate it was restored
if( $g_bugnote_message ) {
	$C = 'constant';
	$t_ts = 'UNIX_TIMESTAMP()';
	fwrite( $t_file, "-- Inserting 'Issue Restored' notes \n" );
	foreach( $g_bug_list as $t_bug_id ) {
		fwrite( $t_file, 'INSERT INTO ' . db_get_table( 'bugnote_text' )
			. " (note) VALUES ('$g_bugnote_message');"
			. PHP_EOL
		);
		fwrite( $t_file, 'INSERT INTO ' . db_get_table( 'bugnote' )
			. "\n    (bug_id, reporter_id, bugnote_text_id, view_state, date_submitted, last_modified)"
			. "\n    VALUES ($t_bug_id, $t_user_id, LAST_INSERT_ID(), {$C('VS_PUBLIC')}, $t_ts, $t_ts );"
			. PHP_EOL
		);
		fwrite( $t_file, 'INSERT INTO ' . db_get_table( 'bug_history' )
			. "\n    (user_id, bug_id, date_modified, type, old_value, new_value, field_name)"
			. "\n    VALUES ($t_user_id, $t_bug_id, $t_ts, {$C('BUGNOTE_ADDED')}, LAST_INSERT_ID(), '', '');"
			. PHP_EOL
		);
		fwrite( $t_file, PHP_EOL );
	}
}

# Bump the restored Issues' last_updated date to more easily identify them
# from MantisBT UI in case post processing is needed
fwrite( $t_file, "-- Bumping restored Issues' last updated date \n" );
fwrite( $t_file, "UPDATE mantis_bug_table SET last_updated = UNIX_TIMESTAMP() "
	. 'WHERE ' . where_clause( 'id' ) . ";\n" );

fclose( $t_file );

echo "Restore script saved in: $g_filename\n";

# List attachments and sample Tarsnap command to restore them
if( $t_attachments ) {
	echo "WARNING: Issues with attachments - restore these to the file system manually\n";
	$t_tarsnap = "tarsnap -x -f mantisbt_org_XXXX -C /tmp ";
	foreach( $t_attachments as $t_attachment ) {
		extract( $t_attachment, EXTR_PREFIX_ALL, 'v' );
		$t_filename = str_replace( '/var/', '/srv/', $v_folder ) . $v_diskfile;
		$t_tarsnap .= ltrim( $t_filename, '/' ) . ' ';
		echo "- id: $v_id for bug $v_bug_id: $t_filename ($v_filename)\n";
	}
	echo "WARNING: Directory may be incorrect\n";
	echo "Sample Tarsnap restore command, adjust as appropriate\n";
	echo $t_tarsnap . PHP_EOL;
}


/**
 * Generate where clause to select issue IDs.
 *
 * @param string $p_field Field name for Bug ID
 *
 * @return string SQL where clause
 */
function where_clause( string $p_field ): string
{
	static $s_where;
	if( !$s_where ) {
		global $g_bug_list;
		$s_where = ' IN (' . implode( ',', $g_bug_list ) . ')';
	}
	return $p_field . $s_where;
}

/**
 * Returns row data ready for use in insert statement values.
 *
 * @param array $p_row Data row
 *
 * @return string Escaped list of values
 */
function insert_values( array $p_row ): string
{
	array_walk( $p_row,
		function( &$p_str ) {
			global $g_db;
			if( !is_numeric( $p_str ) ) {
				$p_str = $g_db->qStr( $p_str );
			}
		}
	);
	return '(' . implode( ',', $p_row ) . ')';
}