<?php

$db = new mysqli( 'localhost', 'user', 'pass' );
$db->select_db( 'user' );
$db->set_charset( 'utf8' );

function query( $sql )
{
    global $db;
    echo "$sql\n";
    $r = mysqli_query( $db, $sql ) or die( mysqli_error($db) );
    return $r;
}

function cleanup()
{
    foreach( array( "a_test_myisam",
                    "c_test_innodb",
                    "b_test",
                    "c_test_myisam",
                    "a_test_innodb" ) as $table )
        query( "DROP TABLE IF EXISTS $table;" );
}

function shutdown()
{
    cleanup();
    exit(0);
}

// start on clean db
cleanup();

// destroy tables after Ctrl-C to make restoring of test backup easier
declare(ticks = 1);
register_shutdown_function('shutdown');  // Catch Fatal Error (Rollback)
pcntl_signal(SIGTERM, 'shutdown');      // Catch Ctrl+C, kill and SIGTERM
pcntl_signal(SIGINT, 'shutdown');

// create test tables
query( "CREATE TABLE a_test_myisam (
    a_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    x INTEGER NOT NULL DEFAULT 0
) ENGINE=MyISAM;");

query( "CREATE TABLE a_test_innodb (
    a_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    x INTEGER NOT NULL DEFAULT 0
) ENGINE=InnoDB;");

query( "CREATE TABLE b_test (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    x INTEGER NOT NULL DEFAULT 0
) ENGINE=MyISAM;");

query( "CREATE TABLE c_test_myisam (
    c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    x INTEGER NOT NULL DEFAULT 0
) ENGINE=MyISAM;");

query( "CREATE TABLE c_test_innodb (
    c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    a_id INTEGER NOT NULL,
    FOREIGN KEY (a_id) REFERENCES a_test_innodb( a_id ),
    KEY( a_id )
) ENGINE=InnoDB;
");

// fill b_test with some dummy rows
query( "INSERT INTO b_test (x) VALUES (0);" );

for( $i=0; $i<18; $i++ )
    query( "INSERT INTO b_test (x) SELECT x FROM b_test;" );

$r = mysqli_fetch_row(query( "SELECT count(*) FROM b_test" ));
echo "b_test contains ${r[0]} rows\n";
echo "start backup now and ctrl-c this program when backup is finished\n";

for( $i=0; $i<1000000; $i++ )
{
    // myisam test
    mysqli_query( $db, "LOCK TABLES a_test_myisam WRITE, c_test_myisam WRITE" ) or die( mysqli_error($db) );
    mysqli_query( $db, "INSERT INTO a_test_myisam (x) VALUES ($i)" ) or die( mysqli_error($db) );
    mysqli_query( $db, "INSERT INTO c_test_myisam (x) VALUES ($i)" ) or die( mysqli_error($db) );
    mysqli_query( $db, "UNLOCK TABLES" ) or die( mysqli_error($db) );

    // innodb test
    mysqli_query( $db, "BEGIN" ) or die( mysqli_error($db) );
    mysqli_query( $db, "INSERT INTO a_test_innodb (x) VALUES (0)" ) or die( mysqli_error($db) );
    $id = mysqli_insert_id($db);
    mysqli_query( $db, "INSERT INTO c_test_innodb (a_id) VALUES ($id)" ) or die( mysqli_error($db) );
    mysqli_query( $db, "COMMIT" ) or die( mysqli_error($db) );
}