<?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",1);
define("DELETE_PROBABILITY",5);
define("SELECT_PROBABILITY",3);

define("MIN_STRING_LENGTH",0);
define("MAX_STRING_LENGTH",50000);

define("NUMBER_OF_ITERATIONS",10000000);
define("SIZE_OF_RANDOM_DATA",1024*1*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,4))
        {
                case 0:
                do_the_query($link1,"start transaction") or die("error starting trx\n");break;
                case 1:
                do_the_query($link1,"commit") or die("error commit trx\n");break;
                case 2:
                do_the_query($link1,"rollback") or die("error rollback trx\n");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;
        };
        //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);
                $sql="update ignore `blobtest` set `data`='".mysqli_real_escape_string($link1,$s)."' where `id`<=".mt_rand(1,$pk) . " limit 5";
                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 5";
                $r=do_the_query($link1,$sql,TRUE)/* or die("error selecting\n")*/;
                $selected+=count($r);
        }
        if(strcasecmp(TABLE_ENGINE,"archive") && mt_rand(1,DELETE_PROBABILITY) == DELETE_PROBABILITY)
        {
                $sql="delete from `blobtest` where `id`<=".mt_rand(1,$pk)." limit 5";
                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;
}

?>