path: root/test/it_dbi.t

#!/www/server/bin/php -qC
<?php

#  Tests for it_dbi.class

# Initialize DB
$db = array('db' => "lib_search_ch", 'safety' => 0);
$dbi = new it_dbi($db);
$dbi->query('create temporary table it_dbi_test (
	ID int not null auto_increment,
	x int,
	foo varchar(42),
	dyncols JSON,
	primary key(ID)
);');

$record = new it_dbi($db + array('table' => "it_dbi_test"));

$record->insert(array('x' => 42, 'foo' => null));
$record->insert(array('foo' => "bar"));
$record->insert(array('x' => 64738, 'foo' => "q'uux"));

is(
	$record->ID,
	3,
	"auto_increment"
);

$record->read(1);
is(
	array($record->_key, $record->x, $record->foo),
	array(1, 42, null),
	"read"
);

is(
	$record->select(),
	3,
	"select without parameters select all"
);

is(
	$record->select(array('foo <>' => ""), "LIMIT 1"),
	1,
	"select with multiple parameters (LIMIT part)"
);
is(
	$record->foo,
	"bar",
	"select with multiple parameters (foo part)"
);
is(
	$record->select(array('ID IN' => array(2,3))),
	2,
	"select with IN"
);
is(
	$record->select(array('ID NI' => array(2,3))),
	2,
	"select with NI"
);
is(
	$record->select(array('ID NOT IN' => array(2,3))),
	1,
	"select with NOT IN"
);
is(
	$record->select(array('ID IN' => array())),
	0,
	"select with empty IN"
);
is(
	$record->select(array('ID NOT IN' => array())),
	3,
	"select with empty NOT IN"
);
is(
	$record->select(array('ID' => array(2,3))),
	2,
	"select with implicit IN"
);

it_dbi::createclass(array('table' => "it_dbi_test", 'forcecreate' => true));

$record = new it_dbi_test;
is(
	$record->x,
	null,
	"constructor of created class without argument"
);

$record = new it_dbi_test(2);
is(
	$record->ID,
	2,
	"constructor of created class with id parameter"
);

$record = new it_dbi_test(array('x >' => 0), "ORDER BY x DESC");
is(
	$record->x,
	64738,
	"constructor of created class with multiple select parameters"
);

$record = new it_dbi_test(array('foo' => 'bar'));
is(
	$record->ID,
	2,
	"constructor of created class with single array parameter"
);

$record = new it_dbi($db + array('table' => "it_dbi_test"));
is(
	$record->x,
	null,
	"constructor without parameters"
);

$record = new it_dbi($db + array('table' => "it_dbi_test"), array('x >' => 0), "ORDER BY x DESC");
is(
	$record->x,
	64738,
	"constructor with multiple select parameters"
);

$record->select(array('x' => 64738));
is(
	array($record->_key, $record->x, $record->foo),
	array(3, 64738, "q'uux"),
	"select"
);

$record->update(array('x' => 17));
is(
	array($record->_key, $record->x, $record->foo),
	array(3, 17, "q'uux"),
	"update"
);

is(
	$record->update(array('x' => 18), array('x' => 17)),
	1,
	"return affected rows"
);

is(
	$record->update(array('x' => 18), array('x' => 17)),
	0,
	"return zero affected rows"
);

$record->update(array('-x' => 'POW(2,2) * 10'));
is(
	array($record->_key, $record->x, $record->foo),
	array(3, 40, "q'uux"),
	"update with function"
);

$record->update(array('foo' => "00"));
$rand = $record->x;
is (
	$record->_set(array('x' => $rand, 'foo' => "0")),
	"SET `foo`='0'",
	'update: _set optimization'
);

$record->update(array('foo' => NULL));
is (
	$record->_set(array('foo' => "")),
	"SET `foo`=''",
	'update: _set optimization with NULL => ""'
);
$record->update(array('foo' => "bar"));
$record->update(array('foo' => ""));
is (
	$record->_set(array('foo' => NULL)),
	"SET `foo`=NULL",
	'update: _set optimization with "" => NULL'
);

$record->update(array('foo' => "bar"));
$record->select(array('foo' => "bar"));
$record->iterate();
is(
	array($record->_key, $record->x, $record->foo),
	array(2, null, "bar"),
	"iterate record 2"
);
$record->update(array('foo' => "qux"));
$record->iterate();
is(
	array($record->_key, $record->x, $record->foo),
	array(3, $rand, "bar"),
	"iterate record 3"
);
$record->update(array('foo' => "quux"));

$record->read(2);
is(
	array($record->_key, isset($record->x), $record->foo),
	array(2, false, "qux"),
	"iterate update record 2"
);

$record->read(3);
is(
	array($record->_key, $record->x, $record->foo),
	array(3, $rand, "quux"),
	"iterate update record 3"
);

is(
	gettype($record->_key) . "/" . gettype($record->x) . "/" . gettype($record->_data['x']) . "/" . gettype($record->foo),
	"integer/integer/integer/string",
	"automatic type detection"
);

$count = 0;
foreach (new it_dbi_test as $id => $record)
{
	$count++;
	is($record->_key, $id, "Iterator id $id");
}
is($count, 3, "Iterator without select");

$count = 0;
foreach (new it_dbi_test(array('foo <>' => "")) as $id => $record)
{
	$count++;
	is($record->_key, $id, "Iterator id $id");
}
is($count, 2, "Iterator with select");

$count = 0;
foreach ($record as $dummy_rec)
	$count++;
is($count, 2, "Iterator reused");

$GLOBALS['debug_sqllog'] = true;
@$record->store(array('ID' => 5, 'x' => 6));
like(
	$record->_sqllog[1]['query'],
	"REPLACE",
	"store => REPLACE for new entries"
);
$record->clear();
@$record->read(5);
is(
	$record->x,
	6,
	"saving with store"
);

$record->_sqllog = array();
@$record->store(array('ID' => 5, 'x' => 7));
like(
	$record->_sqllog[1]['query'],
	"UPDATE",
	"store => UPDATE for existing entries"
);
$record->clear();
@$record->read(5);
is(
	$record->x,
	7,
	"updating with store"
);

$record->_sqllog = array();
@$record->store(array('ID' => 5, 'x' => 7));
is(
	$record->_sqllog[1]['query'],
	null,	# Only SELECT, no UPDATE
	"Optimized away UPDATE with same values"
);
$GLOBALS['debug_sqllog'] = false;

# test latin1 (produces warnings on stderr for failing)

$record = new it_dbi(array('table' => 'it_dbi_test', 'charset' => 'latin1'));
$record->select(array('foo' => "\xc3\x28"));

# Test field localization feature

$dbi->query('create temporary table it_dbi_testlocalized (
	ID int not null auto_increment,
	foobar_de varchar(42),
	foobar_fr varchar(42),
	primary key(ID)
);');

$record = new it_dbi($db + array('table' => "it_dbi_testlocalized"));
$record->insert(array('foobar_de' => "deutsch", 'foobar_fr' => "franz"));
$record->insert(array('foobar_de' => "deutsch2", 'foobar_fr' => "franz2"));

T_set_language('de');
$record->select(array());
$record->iterate();
is(
	array($record->_key, $record->foobar),
	array(1, "deutsch"),
	"localized field foobar_de"
);
$record->iterate();
is(
	array($record->_key, $record->foobar),
	array(2, "deutsch2"),
	"localized field foobar_de iterate"
);

T_set_language('fr');
$record->read(1);
is(
	array($record->_key, $record->foobar),
	array(1, "franz"),
	"localized field foobar_fr"
);

#
# Test saving/retrieving/matching values in dynamically created columns
#

# REPLACE (without and with quoting)
$r = new it_dbi_test;
$r->replace(['ID' => 5, '-key1' => "2*2"]);           is($r->key1, 4);
$r->replace(['ID' => 5, 'key1' => "val0'"]);          is($r->key1, "val0'");

# UPDATE with WHERE (without and with quoting)
$r->update(['-key1' => "2*2"], ['key1' => "val0'"]);  is($r->key1, 4);
$r->update(['key1' => "val1'"], ['key1' => "4"]);     is($r->key1, "val1'");

# UPDATE (without and with quoting)
$r->update(['key1' => "val1'", 'key2' => "ö"]);       is($r->key1, "val1'"); is($r->key2, "ö"); # check mariadb bug
$r->update(['-key2' => "1*2"]);                       is($r->key1, "val1'"); is($r->key2, 2);
$r->update(['key2' => "val2'"]);                      is($r->key1, "val1'"); is($r->key2, "val2'");
$r->update(['-key3' => "1*3"]);                       is($r->key1, "val1'"); is($r->key2, "val2'"); is($r->key3, 3);
$r->update(['key1' => "val0'"]);                      is($r->key1, "val0'"); is($r->key2, "val2'"); is($r->key3, 3);
$r->update(['key1' => "val/'"]);                      is($r->key1, "val/'"); # check json encoding

# Check for mistakenly inherited fields
$r->replace(['ID' => 6, 'key4' => "val4'"]);          is($r->key4, "val4'");
$r->select(['key2' => "val2'"]);                      is($r->key2, "val2'"); is($r->key4, null, "cleared previous fields?");

# Check removal of unnecessary writes
$r->_writes = 0;
$r->select(['ID' => 6]);
$r->update(['key4' => "val4'"]);                      is($r->_writes, 0); is($r->key4, "val4'");
$r->update(['key4' => "val4'", 'key5' => "val5'"]);   is($r->_writes, 1); is($r->key4, "val4'"); is($r->key5, "val5'");
$r->update(['key4' => "val4'", 'key5' => "val5'"]);   is($r->_writes, 1); is($r->key4, "val4'"); is($r->key5, "val5'");

# Check if iterator clears dynfields left over from previous record
$r->delete(["WHERE 1"]);
$r->replace(['ID' => 1, 'key1' => "a"]);
$r->replace(['ID' => 2, 'key2' => "b"]);
$r->clear(false);
$r->select("WHERE 1 ORDER BY ID");
$r->iterate();
is($r->key1, "a"); is($r->key2, null);
$r->iterate();
is($r->key1, null); is($r->key2, "b");

# don't do dyncols stuff in where when we select form multiple tables (column names are unknown then)
is($r->_where(['FROM' => 'it_dbi_test AS a, it_dbi_test AS b', '-a.ID' => 'b.ID']), 'WHERE a.ID = b.ID', "don't do JSON_EXTRACT when we have multiple tables in FROM");
is($r->_where(['JOIN' => 'it_dbi_test AS b', '-a.ID' => 'b.ID']), 'WHERE a.ID = b.ID', "don't do JSON_EXTRACT when we JOIN");

#
# Test tracked update
#
function allrecs()
{
	foreach (new it_dbi_test([0 => "ORDER BY ID"]) as $r)
		$result[] = ['ID' => $r->ID, 'foo' => $r->foo];
	return json_encode($result);
}

$record = new it_dbi_test;
$record->delete(['WHERE 1' ]);
$record->upsert(['ID' => 1, 'foo' => "a"]);
$record->upsert(['ID' => 2, 'foo' => "b"]);
$record->upsert(['ID' => 2, 'foo' => "B"]);
is($record->delete_untouched(), 0, 'delete_untouched');
is(allrecs(), '[{"ID":1,"foo":"a"},{"ID":2,"foo":"B"}]', 'data after delete_untouched');

$record->upsert(['ID' => 1, 'foo' => "A"]);
$record->upsert(['ID' => 3, 'foo' => "c"]);
is($record->delete_untouched([ 'ID >' => 2 ]), 0, 'delete_untouched with query');
is(allrecs(), '[{"ID":1,"foo":"A"},{"ID":2,"foo":"B"},{"ID":3,"foo":"c"}]', 'data after delete_untouched with query');

$record->upsert(['ID' => 3, 'foo' => "C"]);
is($record->delete_untouched(), 2, 'delete_untouched with query');
is(allrecs(), '[{"ID":3,"foo":"C"}]', 'data after delete_untouched with query');

$record->replace(['ID' => 4, 'foo' => "C"]);
is($record->delete_untouched(), 1, 'delete_untouched after replace');
is(allrecs(), '[{"ID":4,"foo":"C"}]', 'data after delete_untouched after replace');

$record->upsert(['ID' => 4, 'foo' => "C"]);
is($record->delete_untouched(), 0, 'delete_untouched after upsert without changes');
is(allrecs(), '[{"ID":4,"foo":"C"}]', 'data after delete_untouched after upsert without changes');