From b038b45bd9df87fc3a22c8d3408ba62186ce0a91 Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 27 Aug 2020 15:32:05 +0200
Subject: test for automatic creation of table classes

---
 test/it_dbi.t | 1 +
 1 file changed, 1 insertion(+)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 2baf9e3..4b0c9a8 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -15,6 +15,7 @@ $dbi->query('create temporary table it_dbi_test (
 );');
 it_dbi::createclass(['table' => "it_dbi_test", 'forcecreate' => true]);
 
+ok(new T_sl_accesscount, 'classes for tables exist');
 $record = new it_dbi_test;
 
 $record->insert(['x' => 42, 'foo' => null]);
-- 
cgit v1.2.3


From e48afecf380719f48257ab2b6d964d57c931a7ae Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 10:39:29 +0200
Subject: add options to run tests on postgressql, adapt create table
 statements to used db, avoid dependency on specific table in db

---
 test/it_dbi.t | 45 +++++++++++++++++++++++++++++++--------------
 1 file changed, 31 insertions(+), 14 deletions(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 4b0c9a8..8528a86 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -3,19 +3,35 @@
 
 #  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]);
 
-ok(new T_sl_accesscount, 'classes for tables exist');
 $record = new it_dbi_test;
 
 $record->insert(['x' => 42, 'foo' => null]);
@@ -283,14 +299,15 @@ $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"]);
 
-- 
cgit v1.2.3


From e69d39a96cbf3f40745bfa2648870b5a14c0ae84 Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 11:09:33 +0200
Subject: make _set tests independent of exact sql syntax

---
 test/it_dbi.t | 19 ++++++-------------
 1 file changed, 6 insertions(+), 13 deletions(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 8528a86..4341991 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -177,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"]);
-- 
cgit v1.2.3


From 46280806236fedabb9a43e0517817c17c9b0d6bd Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 11:12:52 +0200
Subject: escape ID to avoid postgresql lowercasing it

---
 test/it_dbi.t | 10 +++++-----
 1 file changed, 5 insertions(+), 5 deletions(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 4341991..8039857 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -362,13 +362,13 @@ $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"]);
@@ -376,7 +376,7 @@ $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 1 ORDER BY $primarykey");
 $r->iterate();
 is($r->key1, "a"); is($r->key2, null);
 $r->iterate();
@@ -406,7 +406,7 @@ 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);
 }
-- 
cgit v1.2.3


From 4f973c644130d4dd0068206d253f6a707a180e6b Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 11:14:52 +0200
Subject: use WHERE TRUE in tests which is compatible with postgresql

---
 test/it_dbi.t | 8 ++++----
 1 file changed, 4 insertions(+), 4 deletions(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 8039857..6cc0ec1 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -371,12 +371,12 @@ 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 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 $primarykey");
+$r->select("WHERE TRUE ORDER BY $primarykey");
 $r->iterate();
 is($r->key1, "a"); is($r->key2, null);
 $r->iterate();
@@ -412,7 +412,7 @@ function allrecs()
 }
 
 $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"]);
@@ -461,7 +461,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');
-- 
cgit v1.2.3


From 9c9154157004dca26456c6216ef939449fdae020 Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 11:16:20 +0200
Subject: handle postgresql implementation of mysql replace

---
 test/it_dbi.t | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 6cc0ec1..42a230c 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -250,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();
-- 
cgit v1.2.3


From 99983d2d3ee03f24424d424ef6d8030ec55639d6 Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 11:21:52 +0200
Subject: dont depend on order of results

---
 test/it_dbi.t | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index 42a230c..ca5b537 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -425,7 +425,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"]);
-- 
cgit v1.2.3


From eaa3fd96f13640462f06c5e66f1601a045cdb02a Mon Sep 17 00:00:00 2001
From: Nathan Gass
Date: Thu, 3 Sep 2020 11:26:56 +0200
Subject: comment out broken test

---
 test/it_dbi.t | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

(limited to 'test')

diff --git a/test/it_dbi.t b/test/it_dbi.t
index ca5b537..ba8eaeb 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -287,8 +287,9 @@ $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
 
-- 
cgit v1.2.3