<?php

/*
a blob tester for compressed tables.
creates a single table with autoinc pk
and a longblob field.  then performs
insert/delete/update based on the 
probabilities specified..
higher is less often, 1 is every loop
*/


define("TABLE_ENGINE","innodb");
define("TABLE_FORMAT","dynamic");
define("TABLE_KEY_BLOCK_SIZE",16);

define("INSERT_PROBABILITY",2);
define("UPDATE_PROBABILITY",2);
define("DELETE_PROBABILITY",9);
define("SELECT_PROBABILITY",2);

define("MIN_STRING_LENGTH",0);
define("MAX_STRING_LENGTH",50000);

define("NUMBER_OF_ITERATIONS",100000000);
define("SIZE_OF_RANDOM_DATA",1024*2*1024);
define("USE_ASCII",TRUE);//aka dont compress...

define("HOST","127.0.0.1");
define("PORT",3306);
define("DB","test");
define("USER","root");
define("PASSWORD","");

#$seed=12345;
#mt_srand($seed);


$link1=mysqli_connect(HOST,USER,PASSWORD,DB,PORT) or die(mysqli_connect_error());
do_the_query($link1,"set global max_allowed_packet=1024*1024*1024");
do_the_query($link1,"set global innodb_flush_log_at_trx_commit=0");
do_the_query($link1,"set names utf8");
mysqli_close($link1);
$link1=mysqli_connect(HOST,USER,PASSWORD,DB,PORT) or die(mysqli_connect_error());


/*
create a random buffer of compressed data
to use in our app.
*/
print "creating random data...\n";
$len=0;
$zbuff="";
while($len < SIZE_OF_RANDOM_DATA)
{
        $str=make_random_string_value(MIN_STRING_LENGTH,MAX_STRING_LENGTH);
        if(USE_ASCII===FALSE)
        {
                $z=gzcompress($str,9);
                $len+=strlen($z);
                $zbuff.=$z;
        }
        else
        {
                $zbuff.=$str;
                $len+=strlen($str);       
        }
        print ".";
        print "len = $len bytes \n";
}
print "random data size is " . $len . " bytes...\n\n";

do_the_query($link1,"drop table if exists `blobtest`");
if(FALSE===do_the_query($link1,"create table if not exists `blobtest`(`id` bigint unsigned not null auto_increment primary key,`data` longblob,key(`data`(1)),key(`data`(100)),unique key(`data`(40)),key(`data`(255))) engine=".TABLE_ENGINE . " /*row_format=".TABLE_FORMAT." key_block_size=".TABLE_KEY_BLOCK_SIZE." */"))
{
        die("cannot create table!\n");       
}
$pk=0;
$updated=0;
$deleted=0;
$inserted=0;
$selected=0;
for($i=0;$i<NUMBER_OF_ITERATIONS;$i++)
{
        //print "iteration $i ";
        switch(mt_rand(0,10))
        {
                case 0:
                do_the_query($link1,"start transaction with consistent snapshot");break;
                case 1:
                do_the_query($link1,"commit");break;
                case 2:
                do_the_query($link1,"rollback");break;
                case 3:
                do_the_query($link1,"set transaction isolation level read uncommitted");break;
                case 4:
                do_the_query($link1,"set transaction isolation level read committed");break;
		case 5:
                do_the_query($link1,"set transaction isolation level repeatable read");break;
		case 6:
                do_the_query($link1,"set transaction isolation level serializable");break;
		default:
			break;
        };
        //do_the_query($link1,"set session read_buffer_size=".mt_rand(0,1024*1024)) or die("error setting read buffer\n");
        if(mt_rand(1,INSERT_PROBABILITY) == INSERT_PROBABILITY)
        {
                $cnt=mt_rand(MIN_STRING_LENGTH,MAX_STRING_LENGTH);
                $start=mt_rand(0,($len-$cnt));
                $s=substr($zbuff,$start,$cnt);
                $sql="replace into `blobtest` set `data`='".mysqli_real_escape_string($link1,$s)."'";
                do_the_query($link1,$sql) or print("error replacing\n");
                $pk=mysqli_insert_id($link1) + mt_rand(-3,3); //good for parrallel runs .. touch another connections PK
                $inserted+=mysqli_affected_rows($link1);
        }
        if(strcasecmp(TABLE_ENGINE,"archive") && mt_rand(1,UPDATE_PROBABILITY) == UPDATE_PROBABILITY)
        {
                $cnt=mt_rand(MIN_STRING_LENGTH,MAX_STRING_LENGTH);
                $start=mt_rand(0,($len-$cnt));
                $s=substr($zbuff,$start,$cnt);
		if(mt_rand(0,5)!=0)
		{
	                $sql="update ignore `blobtest` set `data`='".mysqli_real_escape_string($link1,$s)."' where `id`<=".$pk /*mt_rand(1,$pk)*/ . " limit 2";
	                do_the_query($link1,$sql) or print("error updating\n");
	                $updated+=mysqli_affected_rows($link1);
		}
		else
		{
			$sql="update ignore `blobtest` set `data`='".mysqli_real_escape_string($link1,$s)."' where `data` like 'bbb%' limit 2";
	                do_the_query($link1,$sql) or print("error updating\n");
                	$updated+=mysqli_affected_rows($link1);
		}
        }
        if(mt_rand(1,SELECT_PROBABILITY) == SELECT_PROBABILITY)
        {
                $sql="select * from `blobtest` where `id`<=".mt_rand(1,$pk) . " limit 1";
                do_the_query($link1,$sql) or print ("error selecting\n");
                $selected++;
        }
        if(strcasecmp(TABLE_ENGINE,"archive") && mt_rand(1,DELETE_PROBABILITY) == DELETE_PROBABILITY)
        {
		if(mt_rand(0,5)!=0)
		{                
			$sql="delete from `blobtest` where `id`<=". $pk /*mt_rand(1,$pk)*/." limit 2";
	                do_the_query($link1,$sql) or print("error deleting\n");
	                $deleted+=mysqli_affected_rows($link1);
		}
		else
		{
			$sql="delete from `blobtest` where `data` like 'aaa%' limit 2";
	                do_the_query($link1,$sql) or print("error deleting\n");
                	$deleted+=mysqli_affected_rows($link1);
		}
        }
        //do_the_query($link1,"check table blobtest");
        //do_the_query($link1,"repair table blobtest");
        //do_the_query($link1,"commit") or die("error committing\n");
        print "#inserted: $inserted  updated: $updated   deleted:  $deleted  selected: $selected   pk: $pk\n";
}


mysqli_close($link1);
exit;

//returns FALSE on error
function do_the_query($link1,$sql,$return_results=FALSE)
{
        $ret=TRUE;
        if($sql=="") return TRUE;
        $rs1=mysqli_query($link1,$sql);
        if(FALSE===$rs1)
        {
                print("/*error: ".mysqli_error($link1)."  thread_id: ".mysqli_thread_id($link1)."  "/*.$sql*/." ;*/\n");
                return FALSE;
        }

        if(TRUE===$return_results)
        {
                $ret=array();
                while($row=mysqli_fetch_array($rs1))
                {
                        array_push($ret,$row);
                }
        }
        else if(mysqli_field_count($link1) > 0)
        {
                while($row=mysqli_fetch_array($rs1))
                {
                        //print_r($row);    
                }
                mysqli_free_result($rs1);
        }
        return $ret;
}


function make_random_string_value($minlengt,$maxlengt)
{
        $len=mt_rand($minlengt,$maxlengt);
        $str="'";
        for($i=0;$i<$len;$i++)
        {
                $str.=chr(mt_rand(ord('a'),ord('z')));
        }
        $str.="'";
        return $str;
}

?>