path: root/test/it_dbi.t

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

#  Tests for it_dbi.class

# Initialize DB
$db = ['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)
);');
it_dbi::createclass(['table' => "it_dbi_test", 'forcecreate' => true]);

$record = new it_dbi_test;

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

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

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

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

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

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

$record->update(['foo' => NULL]);
is (
	$record->_set(['foo' => ""]),
	"SET `foo`=''",
	'update: _set optimization with NULL => ""'
);
$record->update(['foo' => "bar"]);
$record->update(['foo' => ""]);
is (
	$record->_set(['foo' => NULL]),
	"SET `foo`=NULL",
	'update: _set optimization with "" => 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 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",
	"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)

$record = new it_dbi_test(['charset' => 'latin1']);
$record->select(['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 + ['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' => "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 ID DESC', 'LIMIT' => 1]);
is($count, 1, "Simple LIMIT: count");
is($r->ID, 6, "Simple LIMIT: id");
$count = $r->select(['ID >' => 0, 'ORDER BY ID 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 ID DESC', 'LIMIT' => false]), 5, "no limit");

# 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->replace(['ID' => 3]);
$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");
$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 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(), [], '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');

$record->upsert(['ID' => 3, 'foo' => "C"]);
is($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']--;
	return $data;
}

static function _write_preprocess($data)
{
	$data['x']++;
	return $data;
}

}

$record = new my_dbi_test;
$record->delete(['WHERE 1' ]);
$record->upsert(['ID' => 1, 'x' => 42, 'foo' => "a"]);
$record->read(1);
is($record->x, 42, 'value of x after reading with _read_postprocess');
is($record->_data,  ["ID" => 1, "x" => 42, 'foo' => "a"], 'data 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, 'foo' => "a"], 'raw data after writing with _write_preprocess');