public function testNull()
{
$oDB = new \LibPostgres\LibPostgresDriver(array('host' => getenv('TEST_HOST') ?: 'localhost', 'port' => getenv('TEST_PORT') ?: 5432, 'user_name' => getenv('TEST_USER_NAME'), 'user_password' => getenv('TEST_PASSWORD'), 'db_name' => getenv('TEST_DB_NAME')));
// ?d
$iFirst = 1;
$iSecond = 2;
$aResult = $oDB->selectRecord("\n SELECT (?d IS NULL)::integer AS is_null,\n (?d IS NOT NULL)::integer AS is_not_null,\n (SELECT sum(COALESCE(x, 0)) FROM unnest(array[?d]::integer[]) AS x) AS sum_coalesce\n ", null, $iFirst, array($iFirst, null, $iSecond));
$this->assertEquals(1, $aResult['is_null']);
$this->assertEquals(1, $aResult['is_not_null']);
$this->assertEquals($iFirst + $iSecond, $aResult['sum_coalesce']);
// ?f
$fFirst = 1.1;
$fSecond = 2.2;
$aResult = $oDB->selectRecord("\n SELECT (?f IS NULL)::integer AS is_null,\n (?f IS NOT NULL)::integer AS is_not_null,\n (SELECT sum(COALESCE(x, 0)) FROM unnest(array[?f]::numeric[]) AS x) AS sum_coalesce\n ", null, $fFirst, array($fFirst, null, $fSecond));
$this->assertEquals(1, $aResult['is_null']);
$this->assertEquals(1, $aResult['is_not_null']);
$this->assertTrue(bccomp($fFirst + $fSecond, $aResult['sum_coalesce'], 2) === 0);
// ?j / ?jb / ?h
$aResult = $oDB->selectRecord("\n SELECT (?j IS NULL)::integer AS is_j_null,\n (?jb IS NULL)::integer AS is_jb_null,\n (?h IS NULL)::integer AS is_h_null,\n ((?h->'i_am_null') IS NULL)::integer AS is_null_inside_hstore,\n ((?h->'i_am_not_null') IS NOT NULL)::integer AS is_not_null_inside_hstore\n ", null, null, null, array('i_am_null' => null, 'i_am_not_null' => 1), array('i_am_null' => null, 'i_am_not_null' => 1));
$this->assertEquals(1, $aResult['is_j_null']);
$this->assertEquals(1, $aResult['is_jb_null']);
$this->assertEquals(1, $aResult['is_jb_null']);
$this->assertEquals(1, $aResult['is_null_inside_hstore']);
$this->assertEquals(1, $aResult['is_not_null_inside_hstore']);
// ?w
$sString1 = null;
$sString2 = "2'2\r";
$sStringDefault = "3'3\n";
$aResult = $oDB->selectRecord("\n SELECT (?w IS NULL)::integer AS is_null,\n (?w IS NOT NULL)::integer AS is_not_null,\n COALESCE(?w, ?w) || ?w AS concat,\n (SELECT string_agg(COALESCE(s, ?w), '') FROM unnest(array[?w]::varchar[]) AS s) AS concat_coalesce\n\n ", $sString1, $sString2, $sString1, $sStringDefault, $sString2, $sStringDefault, array($sString1, $sString2, $sString1));
$this->assertEquals(1, $aResult['is_null']);
$this->assertEquals(1, $aResult['is_not_null']);
$this->assertEquals($sStringDefault . $sString2, $aResult['concat']);
$this->assertEquals($sStringDefault . $sString2 . $sStringDefault, $aResult['concat_coalesce']);
}
public function testPgPconnectGeneral()
{
// pg_pconnect
$oDB1 = new \LibPostgres\LibPostgresDriver(array('host' => getenv('TEST_HOST') ?: 'localhost', 'port' => getenv('TEST_PORT') ?: 5432, 'user_name' => getenv('TEST_USER_NAME'), 'user_password' => getenv('TEST_PASSWORD'), 'db_name' => getenv('TEST_DB_NAME'), 'persistance' => 1));
$iMin = 105;
$iMax = 201;
$iResult = $oDB1->selectField("\n SELECT sum(t)\n FROM generate_series(?d, ?d) AS t;\n ", $iMin, $iMax);
$this->assertEquals($iResult, ($iMax + $iMin) * (($iMax - $iMin + 1) / 2));
// pg_pconnect(..., PGSQL_CONNECT_FORCE_NEW)
$oDB2 = new \LibPostgres\LibPostgresDriver(array('host' => getenv('TEST_HOST') ?: 'localhost', 'port' => getenv('TEST_PORT') ?: 5432, 'user_name' => getenv('TEST_USER_NAME'), 'user_password' => getenv('TEST_PASSWORD'), 'db_name' => getenv('TEST_DB_NAME'), 'persistance' => PGSQL_CONNECT_FORCE_NEW));
$iMin = 405;
$iMax = 1201;
$iResult = $oDB2->selectField("\n SELECT sum(t)\n FROM generate_series(?d, ?d) AS t;\n ", $iMin, $iMax);
$this->assertEquals($iResult, ($iMax + $iMin) * (($iMax - $iMin + 1) / 2));
}
public function testSelectGeneral()
{
$oDB = new \LibPostgres\LibPostgresDriver(array('host' => getenv('TEST_HOST') ?: 'localhost', 'port' => getenv('TEST_PORT') ?: 5432, 'user_name' => getenv('TEST_USER_NAME'), 'user_password' => getenv('TEST_PASSWORD'), 'db_name' => getenv('TEST_DB_NAME')));
// selectField and ?d
$iMin = 100;
$iMax = 200;
$iResult = $oDB->selectField("\n SELECT max(t)\n FROM generate_series(?d, ?d) AS t;\n ", $iMin, $iMax);
$this->assertEquals($iResult, $iMax);
// selectRecord and ?w
$aResult = $oDB->selectRecord("\n SELECT ?w || t::varchar AS field, md5(?w || t::varchar) AS md5\n FROM generate_series(1, 10) AS t\n ORDER BY t DESC\n LIMIT 1\n ", "STR'ING", "STR'ING");
$this->assertEquals($aResult['field'], "STR'ING10");
$this->assertEquals($aResult['md5'], md5("STR'ING10"));
// selectColumn and ?h
$aColumn = $oDB->selectColumn("\n WITH hs AS (\n SELECT ?h || ('count => ' || t)::hstore AS h\n FROM generate_series(0, 10) AS t\n )\n SELECT (h->'field')::varchar || (h->'\"foo\"')::varchar || (h->'baz')::varchar || (h->'count')::varchar\n FROM hs\n ORDER BY (h->'count')::integer;\n ", array('field' => '"FIELD"', '"foo"' => '\\"bar\\"', 'baz' => "\n\t", 'normal' => str_repeat('1', 100)));
$this->assertEquals($aColumn[3], '"FIELD"\\"bar\\"' . "\n\t" . '3');
// selectField and ?j / ?jb
$sString1 = "é";
$sString2 = "bla'bla\"? абв";
$sResult = $oDB->selectField("\n SELECT (?jb->>'first') || (?j->>'second')\n ", array('first' => $sString1), array('second' => $sString2));
$this->assertEquals($sResult, $sString1 . $sString2);
}
