path: root/test/it_dbi.t

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

#  Tests for it_dbi.class

$opts = it::getopt("
	Usage: it_dbi.t [OPTIONS]
	  --db=S        DB to use [lib_search_ch]
	  --user=S      User
	  --pw=S        Password
	  --subclass=S  Subclass to test [it_dbi]
");

# Initialize DB
$db = it::filter_keys($opts, 'db,user,pw');# + ['safety' => 0];
$dbi = new $opts['subclass']($db);

$opts['subclass']::createclasses();
$tables = $dbi->tables();
ok(count($tables) > 0, 'there are some existing tables in the db');
ok(new $tables[0], 'classes for tables exist');

$autoid = $opts['subclass'] == 'it_dbi_postgres' ? '"ID" Serial' : 'ID int not null auto_increment';
$dyncols = $opts['subclass'] == 'it_dbi_postgres' ? 'dyncols JSONB' : 'dyncols JSON';
$primarykey = $dbi->escape_name('ID');
$dbi->query("create temporary table it_dbi_test (
	$autoid,
	x int,
	y float,
	z bigint,
	foo varchar(42),
	flag boolean,
	$dyncols,
	primary key($primarykey)
);");
$opts['subclass']::createclass(['table' => "it_dbi_test", 'forcecreate' => true]);

$record = new it_dbi_test;
$GLOBALS['it_defaultconfig']['fatal_throws_exception'] = true;

is($record->query("SYNTAX ERROR", ['safety' => 0]), false, "Suppress failures with safety 0");
try {
	is(@$record->select("SYNTAX ERROR"), "Exception", "Syntax triggers exception for fatal_throws_exception mode");
} catch (Exception $e) {
	is(it::match('syntax', $e->getMessage()), 'syntax', "Syntax error triggers error");
}

$record->insert(['x' => 42, 'y' => 0.0001, 'z' => 0, 'foo' => null, 'flag' => 1]);
$record->insert(['foo' => "bar", 'y' => 1e-10, 'z' => 0, 'flag' => 0]);
$record->insert(['x' => 64738, 'y' => 1, 'z' => 0, 'foo' => "q'uux"]);

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

try {
	@$record->insert(['ID' => 3]);
} catch (Exception $e) {
	is($e->getMessage(), false, "Silently ignore duplicate inserts with default safety");
}

$record->read(1);
is(
	[$record->_key, $record->x, $record->y, $record->foo],
	[1, 42, 0.0001, null],
	"read"
);
ok(!!$record->flag, "read true boolean");
$record->read(2);
ok(!$record->flag, "read false boolean");

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

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

$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(['x >' => 0], "ORDER BY x DESC");
is(
	$record->x,
	64738,
	"constructor of created class with multiple select parameters"
);

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

$record = new it_dbi_test;
is(
	$record->x,
	null,
	"constructor without parameters"
);

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

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

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

$record->update(['flag' => "astring"]);
is($record->flag, 0, "use non-numeric string to set tinyint flag");

$record->update(['flag' => true]);
is($record->flag, 1, "use boolean true to set tinyint flag");

$record->update(['flag' => false]);
is($record->flag, 0, "use boolean false to set tinyint flag");

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

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

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

$value = pow(2, 24) + 1;
$record->update(['x' => $value]);
is($record->x, $value, "update 32bit integer without float representation");
is($record->select(['x' => $value]), 1, "select 32bit integer without float representation");

$value = pow(2, 53) + 1;
$record->update(['z' => $value]);
is($record->z, $value, "update 64bit integer without double representation");
is($record->select(['z' => $value]), 1, "select 64bit integer without double representation");

$record->update(['foo' => "10", 'x' => 10]);
is($record->select(['x <' => 9]), 0, "always use integer comparision for int field");
is($record->select(['x <' => '9']), 0, "always use integer comparision for int field");
is($record->select(['foo <' => 9]), 1, "always use string comparision for varchar field");
is($record->select(['foo <' => '9']), 1, "always use string comparision for varchar field");

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

$record->update(['foo' => '']);
ok(!$record->_set(['foo' => ""]), 'update: _set optimization no update "" => ""');
ok($record->_set(['foo' => NULL]), 'update: _set optimization do update "" => NULL');
$record->update(['foo' => NULL]);
ok($record->_set(['foo' => ""]), 'update: _set optimization do update NULL => ""');
ok(!$record->_set(['foo' => NULL]), 'update: _set optimization no update NULL => NULL');

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

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

$record->read(3);
is(
	[$record->_key, $record->x, $record->foo],
	[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 $opts['subclass']($db + ['table' => 'it_dbi_test']) as $id => $record)
{
	$count++;
	is($record->_key, $id, "Iterator id $id (it_dbi object)");
}
is($count, 3, "Iterator without select (it_dbi_object)");

$count = 0;
foreach (new it_dbi_test(['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(['ID' => 5, 'x' => 6]);
like(
	$record->_sqllog[1]['query'],
	"REPLACE|ON CONFLICT",
	"store => REPLACE for new entries"
);
$record->clear();
@$record->read(5);
is(
	$record->x,
	6,
	"saving with store"
);

$record->_sqllog = [];
@$record->store(['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 = [];
@$record->store(['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)

#TODO this is not a proper test and the charset is currently used as dyncol
#$record = new it_dbi_test(['charset' => 'latin1']);
#$record->select(['foo' => "\xc3\x28"]);

# Test field localization feature

$dbi->query("create temporary table it_dbi_testlocalized (
	$autoid,
	foobar_de varchar(42),
	foobar_fr varchar(42),
	primary key($primarykey)
);");


$record = new $opts['subclass']($db + ['table' => "it_dbi_testlocalized"]);
$record->insert(['foobar_de' => "deutsch", 'foobar_fr' => "franz"]);
$record->insert(['foobar_de' => "deutsch2", 'foobar_fr' => "franz2"]);

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

T_set_language('fr');
$record->read(1);
is(
	[$record->_key, $record->foobar],
	[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' => 42]);               is($r->key1, 42, "Preserve type int on dyncols insert");
$r->update(['key1' => 43]);                           is($r->key1, 43, "Preserve type int on dyncols update");
$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 LIMIT
$count = $r->select(['ID >' => 0, "ORDER BY $primarykey DESC", 'LIMIT' => 1]);
is($count, 1, "Simple LIMIT: count");
is($r->ID, 6, "Simple LIMIT: id");
$count = $r->select(['ID >' => 0, "ORDER BY $primarykey DESC", 'LIMIT' => "1, 2"]);
is($count, 2, "LIMIT with offset: count");
is($r->ID, 5, "LIMIT with offset: id");
is($r->select(['ID >' => 0, "ORDER BY $primarykey DESC", 'LIMIT' => false]), 5, "no limit");

# Check if iterator clears dynfields left over from previous record
$r->delete(["WHERE TRUE"]);
$r->replace(['ID' => 1, 'key1' => "a"]);
$r->replace(['ID' => 2, 'key2' => "b"]);
$r->replace(['ID' => 3]);
$r->clear(false);
$r->select("WHERE TRUE ORDER BY $primarykey");
$r->iterate();
is($r->key1, "a"); is($r->key2, null);
$r->iterate();
is($r->key1, null); is($r->key2, "b");
$r->iterate();
is($r->_dyndata, [], '_dyndata for record with empty dyncols should be empty');
$r->update(['key3' => 'c']);
is($r->key3, 'c', 'dynamic column for record with empty dyncols whould be correctly created');

$r->clear(false);
is($r->select(['-key1 IS NOT' => 'NULL']), 1, 'only one entry has a value for key1');
is($r->select(['-key1 IS' => 'NULL']), 2, "two entries don't have a value for key1");
is($r->select(['key1' => null]), 2, "same behaviour without - syntax");
$r->read(1);
$r->update(['key1' => null]);
is($r->key1, null, 'setting dyncol to null should make it be null');
is($r->select(['-key1 IS NOT' => 'NULL']), 0, 'no entry should have a value for key1 anymore');

# 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");

is($r->_where(['LOWER(a)' => 'x']), "WHERE LOWER(a) = 'x'", "don't do JSON_EXTRACT when we use SQL functions");

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

$record = new it_dbi_test;
$record->delete(['WHERE TRUE' ]);
$record->upsert(['ID' => 1, 'foo' => "a"]);
$record->upsert(['ID' => 2, 'foo' => "b"]);
$record->upsert(['ID' => 2, 'foo' => "B"]);
is($record->delete_untouched(), [], '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 ]), [], 'delete_untouched with query');
is(allrecs(), '[{"ID":1,"foo":"A"},{"ID":2,"foo":"B"},{"ID":3,"foo":"c"}]', 'data after delete_untouched with query');

# Test it::map with it_dbi (clone vs. reference issue)
is(it::map('$v->foo', new it_dbi_test(['ID' => [1, 2, 3]])), [1 => 'A', 2 => 'B', 3 => 'c'], 'it_dbi with it::map');
is(it::map('$v->foo', iterator_to_array(new it_dbi_test(['ID' => [1, 2, 3]]))), [1 => 'A', 2 => 'B', 3 => 'c'], 'it_dbi with iterator_to_array');

$record->upsert(['ID' => 3, 'foo' => "C"]);
is(it::sort($record->delete_untouched()), [1, 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(), [3], '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(), [], 'delete_untouched after upsert without changes');
is(allrecs(), '[{"ID":4,"foo":"C"}]', 'data after delete_untouched after upsert without changes');

#
# Test _read_postprocess and _write_preprocess
#
class my_dbi_test extends it_dbi_test
{

static function _read_postprocess($data)
{
	$data['x']--;
	$data['foo'] = json_decode($data['foo'], true);
	return $data;
}

static function _write_preprocess($data)
{
	if (isset($data['x']))
		$data['x']++;
	if (isset($data['foo']))
		$data['foo'] = json_encode($data['foo']);
	return $data;
}

}

$record = new my_dbi_test;
$record->delete(['WHERE TRUE' ]);
$record->upsert(['ID' => 1, 'x' => 42, 'y' => 2.5, 'foo' => ['value' => "a"], 'flag' => 0]);
$record->read(1);
is($record->x, 42, 'value of x after reading with _read_postprocess');
is($record->_data,  ["ID" => 1, "x" => 42, 'y' => 2.5, 'z' => NULL, 'foo' => '{"value":"a"}', 'flag' => 0], 'data after reading with _read_postprocess');
is($record->foo,  ['value' => "a"], 'field after reading with _read_postprocess');

$record2 = new it_dbi_test;
$record2->read(1);
is($record2->x, 43, 'raw value of x after writing with _write_preprocess');
is($record2->_data, ["ID" => 1, "x" => 43, 'y' => 2.5, 'z' => NULL, 'foo' => '{"value":"a"}', 'flag' => 0], 'raw data after writing with _write_preprocess');

$record->update(['foo' => ['value' => "b"]]);
is($record->_data, ["ID" => 1, "x" => 42, 'y' => 2.5, 'z' => NULL, 'foo' => '{"value":"b"}', 'flag' => 0], 'raw data after updating with _write_preprocess');
is($record->foo, ['value' => "b"], 'field data after updating with _write_preprocess');