public function testDataType()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE copy_to_test(field1 TEXT NOT NULL, field2 INTEGER, field3 DATE)") === false) {
$this->markTestSkipped("Create table for copy_to_test failed");
return;
}
$data = [];
$s = getDB()->prepare("INSERT INTO copy_to_test(field1,field2) VALUES (:field1, :field2)");
for ($i = 0; $i < 100; $i++) {
if ($i != 50) {
$data[] = "text_{$i}\t{$i}";
$s->execute([":field1" => "text_{$i}", ":field2" => $i]);
} else {
$data[] = "text_{$i}\t\\N";
$s->execute([":field1" => "text_{$i}", ":field2" => null]);
}
}
$this->assertTrue(getDB()->copyToFile("copy_from_test", __TESTS_TEMP_DIR__ . '/copy_to_test.csv', null, null, ['field1', 'field2']));
if (!($f = file_get_contents(__TESTS_TEMP_DIR__ . '/copy_to_test.csv'))) {
$this->markTestSkipped("Unable to load the file needed for copy to test");
return;
}
$this->assertSame(implode("\n", $data) . "\n", $f);
}
public function testDataType()
{
if (skipTest('9.3')) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE json(field JSON NOT NULL)") === false) {
$this->markTestSkipped("Create table with json data type failed");
}
$s = getDB()->prepare("INSERT INTO json VALUES(:json)");
$this->assertInstanceOf('\\PgBabylon\\PDOStatement', $s, "Asserting pdo::prepare returns a pgbabylon statement");
$p = ["key_1" => "val_1", "key_2" => 2];
$s->bindParam(":json", $p, PDO::PARAM_JSON);
$r = $s->execute();
$this->assertTrue($r, "Testing json insert using PHP array");
$s = getDB()->prepare("SELECT field AS json_col FROM json");
$s->bindColumn("json_col", $val, PDO::PARAM_JSON);
$r = $s->execute();
$this->assertTrue($r, "Testing json select");
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertSame(['json_col' => $p], $r, "Asserting fetch return an array after deserializing the pgsql json");
$this->assertSame($p, $val, "Asserting fetch sets the previously bound variable as json");
$s = getDB()->prepare("SELECT field AS json_col FROM json", PDO::AUTO_COLUMN_BINDING);
$r = $s->execute();
$this->assertTrue($r, "Testing json select with auto column binding");
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertSame(['json_col' => $p], $r, "Asserting fetch return an array after deserializing the pgsql json with auto column binding");
$p = ["key_2" => "val_2", "key_3" => 3];
$s = getDB()->prepare("INSERT INTO json VALUES(:json)");
$r = $s->execute([':json' => PgBabylon\DataTypes\JSON($p)]);
$this->assertTrue($r, "Testing json insert using PHP array directly in execute");
}
public function testDataType()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE copy_from_test(field1 TEXT NOT NULL, field2 INTEGER, field3 DATE)") === false) {
$this->markTestSkipped("Create table for copy_from_test failed");
return;
}
$data = [];
for ($i = 0; $i < 100; $i++) {
if ($i != 50) {
$data[] = "text_{$i}\t{$i}";
} else {
$data[] = "text_{$i}\t\\N";
}
}
if (!file_put_contents(__TESTS_TEMP_DIR__ . '/copy_from_test.csv', implode("\n", $data))) {
$this->markTestSkipped("Unable to save the file needed for copy from test");
return;
}
$this->assertTrue(getDB()->copyFromFile("copy_from_test", __TESTS_TEMP_DIR__ . '/copy_from_test.csv', null, null, ['field1', 'field2']));
$r = getDB()->query("SELECT count(field1) AS f1, count(field2) AS f2 FROM copy_from_test")->fetch(PDO::FETCH_ASSOC);
$this->assertEquals($r['f1'], 100);
$this->assertEquals($r['f2'], 99);
}
public function testQuery()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE test_query(int_field INTEGER NOT NULL, ts_field TIMESTAMP)") === false) {
$this->markTestSkipped("Create table for testing query method of PDO");
}
$sampleData = [];
for ($i = 0; $i < 3; $i++) {
$sampleData[] = [':int' => $i, ':datetime' => (new \DateTime("2015-09-01 00:00:00"))->add(new DateInterval("P{$i}D"))];
}
$s = getDB()->prepare("INSERT INTO test_query(int_field, ts_field) VALUES(:int, :datetime)");
for ($i = 0; $i < 3; $i++) {
$s->bindParam(":int", $sampleData[$i][':int']);
$s->bindParam(":datetime", $sampleData[$i][':datetime'], PDO::PARAM_DATETIME);
$this->assertEquals(1, $s->execute());
}
$s = getDB()->query("SELECT * FROM test_query");
$s->setColumnTypes(['ts_field' => PDO::PARAM_DATETIME]);
$idx = 0;
foreach ($s as $rIdx => $r) {
$this->assertEquals($sampleData[$idx][':int'], $r['int_field']);
$this->assertEquals($sampleData[$idx][':datetime'], $r['ts_field']);
$idx++;
}
$this->assertEquals(3, $idx);
}
public function testOperator()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE in_test(date_field DATE NOT NULL, int_field INTEGER )") === false) {
$this->markTestSkipped("Create table for in test failed");
}
$s = getDB()->prepare("INSERT INTO in_test(date_field, int_field) VALUES(:date, :int)");
for ($i = 0; $i < 3; $i++) {
$r = $s->execute([':int' => $i, ':date' => DataTypes\Date((new \DateTime("2015-09-01 00:00:00"))->add(new DateInterval("P{$i}D")))]);
$this->assertEquals(1, $r);
}
// Test in with native PDO datatypes
$v = [1, 2];
$s = getDB()->prepare("SELECT * FROM in_test WHERE int_field IN :int");
$s->bindParam(':int', $v, PDO::PARAM_IN);
$s->execute();
$s->setColumnTypes(['date_field' => PDO::PARAM_DATE]);
$idx = 1;
foreach ($s as $r) {
$this->assertEquals($idx, $r['int_field']);
$this->assertEquals((new \DateTime("2015-09-01 00:00:00"))->add(new DateInterval("P{$idx}D")), $r['date_field']);
$idx++;
}
// Test in with native PgBabylon
$v = [DataTypes\Date(new \DateTime("2015-09-02 00:00:00"))];
$s = getDB()->prepare("SELECT * FROM in_test WHERE date_field IN :date");
$s->bindParam(':date', $v, PDO::PARAM_IN);
$s->execute();
$s->setColumnTypes(['date_field' => PDO::PARAM_DATE]);
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertEquals(1, $r['int_field']);
$this->assertEquals(new \DateTime("2015-09-02 00:00:00"), $r['date_field']);
// Test using in directly as execute parameter
$s = getDB()->prepare("SELECT * FROM in_test WHERE int_field IN :int");
$s->bindParam(':int', $v, PDO::PARAM_IN);
$s->execute([':int' => Operators\IN([1, 2])]);
$s->setColumnTypes(['date_field' => PDO::PARAM_DATE]);
$idx = 1;
foreach ($s as $r) {
$this->assertEquals($idx, $r['int_field']);
$this->assertEquals((new \DateTime("2015-09-01 00:00:00"))->add(new DateInterval("P{$idx}D")), $r['date_field']);
$idx++;
}
// Test using in with NULL values
$s = getDB()->prepare("SELECT * FROM in_test WHERE int_field IN :int");
$s->execute([':int' => Operators\IN(null)]);
$this->assertSame(0, $s->rowCount());
}
public function testDataType()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE test_date(field DATE NOT NULL, field2 TIMESTAMP)") === false) {
$this->markTestSkipped("Create table with date data type failed");
}
$s = getDB()->prepare("INSERT INTO test_date(field, field2) VALUES(:datetime, :second_datetime)");
$this->assertInstanceOf('\\PgBabylon\\PDOStatement', $s, "Asserting pdo::prepare returns a pgbabylon statement");
$d1 = \DateTime::createFromFormat('Y-m-d', '2015-09-01');
$d2 = \DateTime::createFromFormat('Y-m-d H:i:s.u', '2015-09-05 19:15:10.123456');
$n = null;
$s->bindParam(":datetime", $d1, PDO::PARAM_DATE);
$s->bindParam(":second_datetime", $n, PDO::PARAM_DATETIME);
$r = $s->execute();
$this->assertTrue($r, "Testing first date insert using PHP DateTime");
$s->bindParam(":datetime", $d2, PDO::PARAM_DATE);
$s->bindParam(":second_datetime", $d2, PDO::PARAM_DATE);
$r = $s->execute();
$this->assertTrue($r, "Testing second datetime insert using PHP DateTime");
$s = getDB()->prepare("SELECT field, field2 FROM test_date WHERE field <= :ts");
$ts = \DateTime::createFromFormat('Y-m-d H:i:s', '2015-09-02 23:59:00');
$s->bindParam(":ts", $ts, PDO::PARAM_DATE);
$s->bindColumn("field", $val, PDO::PARAM_DATE);
$s->bindColumn("field2", $val2, PDO::PARAM_DATE);
$r = $s->execute();
$this->assertTrue($r, "Testing DateTime select");
$r = $s->fetch(PDO::FETCH_ASSOC);
$tempD1 = clone $d1;
$tempD1->setTime(0, 0, 0);
$this->assertEquals($tempD1, $r['field'], "Asserting fetch returns a DateTime object");
$this->assertEquals($tempD1, $val, "Asserting fetch returns a DateTime object");
$this->assertEquals(null, $r['field2'], "Asserting fetch returns null");
$this->assertEquals(null, $val2, "Asserting fetch returns null");
$s = getDB()->prepare("SELECT field, field2 FROM test_date WHERE field <= :ts", PDO::AUTO_COLUMN_BINDING);
$ts = \DateTime::createFromFormat('Y-m-d H:i:s', '2015-09-02 23:10:00');
$s->bindParam(":ts", $ts, PDO::PARAM_DATE);
$r = $s->execute();
$this->assertTrue($r, "Testing DateTime select with auto column binding");
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertEquals($tempD1, $r['field'], "Asserting fetch return a DateTime object with auto column binding");
$s = getDB()->prepare("INSERT INTO test_date(field, field2) VALUES(:datetime, :second_datetime)");
$r = $s->execute([':datetime' => PgBabylon\DataTypes\Date(new DateTime()), ':second_datetime' => null]);
$this->assertTrue($r, "Testing date insert using PHP DateTime directly in execute");
}
public function testIssue5()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE test_issue5(field1 DATE NOT NULL, field2 TIMESTAMP)") === false) {
$this->markTestSkipped("Create table for testing issue 5");
return;
}
$s = getDB()->prepare("INSERT INTO test_issue5(field1, field2) VALUES(?, ?)");
$this->assertInstanceOf('\\PgBabylon\\PDOStatement', $s, "Asserting pdo::prepare returns a pgbabylon statement");
$d1 = \DateTime::createFromFormat('Y-m-d', '2015-09-01')->setTime(0, 0, 0);
$d2 = \DateTime::createFromFormat('Y-m-d H:i:s.u', '2015-09-05 19:15:10.123456');
$n = null;
$this->assertTrue($s->execute([DataTypes\Date($d1), DataTypes\DateTime($d2)]));
$this->assertEquals(1, $s->rowCount());
$s = getDb()->prepare("SELECT field1 FROM test_issue5 WHERE field1 = ?");
$this->assertTrue($s->execute([DataTypes\Date($d1)]));
$s->setColumnType('field1', PDO::PARAM_DATE);
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertEquals($d1, $r['field1']);
}
public function testIssue7()
{
if (skipTest('9.0')) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE issue7(txt_arr TEXT[] NOT NULL, idx INTEGER)") === false) {
$this->markTestSkipped("Create table for array testing issue7");
}
// Issue #7
$stmt = getDb()->prepare('INSERT INTO issue7 (txt_arr) VALUES (:myarray)');
$stmt->execute([':myarray' => DataTypes\PhpArray(['die(foo)'])]);
// Test Select
$s = getDB()->prepare("SELECT * FROM issue7");
$s->bindColumn("txt_arr", $txt_val, PDO::PARAM_ARRAY);
$r = $s->execute();
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertSame(['die(foo)'], $r['txt_arr']);
}
