Merge branch 'ng/postgresql'

1 files changed, 50 insertions, 38 deletions

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 2baf9e3..ba8eaeb 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -3,17 +3,34 @@
 
 #  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 = ['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,
+$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,
 	foo varchar(42),
-	dyncols JSON,
-	primary key(ID)
-);');
-it_dbi::createclass(['table' => "it_dbi_test", 'forcecreate' => true]);
+	$dyncols,
+	primary key($primarykey)
+);");
+$opts['subclass']::createclass(['table' => "it_dbi_test", 'forcecreate' => true]);
 
 $record = new it_dbi_test;
 
@@ -160,23 +177,16 @@ $record->update(['foo' => "00"]);
 $rand = $record->x;
 is (
 	$record->_set(['x' => $rand, 'foo' => "0"]),
-	"SET `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]);
-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'
-);
+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"]);
@@ -240,7 +250,7 @@ $GLOBALS['debug_sqllog'] = true;
 @$record->store(['ID' => 5, 'x' => 6]);
 like(
 	$record->_sqllog[1]['query'],
-	"REPLACE",
+	"REPLACE|ON CONFLICT",
 	"store => REPLACE for new entries"
 );
 $record->clear();
@@ -277,19 +287,21 @@ $GLOBALS['debug_sqllog'] = false;
 
 # test latin1 (produces warnings on stderr for failing)
 
-$record = new it_dbi_test(['charset' => 'latin1']);
-$record->select(['foo' => "\xc3\x28"]);
+#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 (
-	ID int not null auto_increment,
+$dbi->query("create temporary table it_dbi_testlocalized (
+	$autoid,
 	foobar_de varchar(42),
 	foobar_fr varchar(42),
-	primary key(ID)
-);');
+	primary key($primarykey)
+);");
+
 
-$record = new it_dbi($db + ['table' => "it_dbi_testlocalized"]);
+$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"]);
 
@@ -351,21 +363,21 @@ $r->update(['key4' => "val4'", 'key5' => "val5'"]);   is($r->_writes, 1); is($r-
 $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]);
+$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 ID DESC', 'LIMIT' => "1, 2"]);
+$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 ID DESC', 'LIMIT' => false]), 5, "no limit");
+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 1"]);
+$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 1 ORDER BY ID");
+$r->select("WHERE TRUE ORDER BY $primarykey");
 $r->iterate();
 is($r->key1, "a"); is($r->key2, null);
 $r->iterate();
@@ -395,13 +407,13 @@ is($r->_where(['LOWER(a)' => 'x']), "WHERE LOWER(a) = 'x'", "don't do JSON_EXTRA
 #
 function allrecs()
 {
-	foreach (new it_dbi_test([0 => "ORDER BY ID"]) as $r)
+	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 1' ]);
+$record->delete(['WHERE TRUE' ]);
 $record->upsert(['ID' => 1, 'foo' => "a"]);
 $record->upsert(['ID' => 2, 'foo' => "b"]);
 $record->upsert(['ID' => 2, 'foo' => "B"]);
@@ -414,7 +426,7 @@ 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(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"]);
@@ -450,7 +462,7 @@ static function _write_preprocess($data)
 }
 
 $record = new my_dbi_test;
-$record->delete(['WHERE 1' ]);
+$record->delete(['WHERE TRUE' ]);
 $record->upsert(['ID' => 1, 'x' => 42, 'foo' => ['value' => "a"]]);
 $record->read(1);
 is($record->x, 42, 'value of x after reading with _read_postprocess');