protected function _executeTests($connection)
{
$result = $connection->query("SELECT * FROM personas LIMIT 3");
$this->assertTrue(is_object($result));
$this->assertEquals(get_class($result), 'Phalcon\\Db\\Result\\Pdo');
for ($i = 0; $i < 3; $i++) {
$row = $result->fetch();
$this->assertEquals(count($row), 22);
}
$row = $result->fetch();
$this->assertEquals($row, false);
$this->assertEquals($result->numRows(), 3);
$number = 0;
$result = $connection->query("SELECT * FROM personas LIMIT 5");
$this->assertTrue(is_object($result));
while ($row = $result->fetch()) {
$number++;
}
$this->assertEquals($number, 5);
$result = $connection->query("SELECT * FROM personas LIMIT 5");
$result->setFetchMode(Phalcon\Db::FETCH_NUM);
$row = $result->fetch();
$this->assertTrue(is_array($row));
$this->assertEquals(count($row), 11);
$this->assertTrue(isset($row[0]));
$this->assertFalse(isset($row['cedula']));
$this->assertFalse(isset($row->cedula));
$result = $connection->query("SELECT * FROM personas LIMIT 5");
$result->setFetchMode(Phalcon\Db::FETCH_ASSOC);
$row = $result->fetch();
$this->assertTrue(is_array($row));
$this->assertEquals(count($row), 11);
$this->assertFalse(isset($row[0]));
$this->assertTrue(isset($row['cedula']));
$this->assertFalse(isset($row->cedula));
$result = $connection->query("SELECT * FROM personas LIMIT 5");
$result->setFetchMode(Phalcon\Db::FETCH_OBJ);
$row = $result->fetch();
$this->assertTrue(is_object($row));
$this->assertTrue(isset($row->cedula));
$result = $connection->query("SELECT * FROM personas LIMIT 5");
$result->setFetchMode(Phalcon\Db::FETCH_BOTH);
$result->dataSeek(4);
$row = $result->fetch();
$row = $result->fetch();
$this->assertEquals($row, false);
$result = $connection->execute("DELETE FROM prueba");
$this->assertTrue($result);
$success = $connection->execute('INSERT INTO prueba(id, nombre, estado) VALUES (' . $connection->getDefaultIdValue() . ', ?, ?)', array("LOL 1", "A"));
$this->assertTrue($success);
$success = $connection->execute('UPDATE prueba SET nombre = ?, estado = ?', array("LOL 11", "R"));
$this->assertTrue($success);
$success = $connection->execute('DELETE FROM prueba WHERE estado = ?', array("R"));
$this->assertTrue($success);
$success = $connection->insert('prueba', array($connection->getDefaultIdValue(), "LOL 1", "A"));
$this->assertTrue($success);
$success = $connection->insert('prueba', array("LOL 2", "E"), array('nombre', 'estado'));
$this->assertTrue($success);
$success = $connection->insert('prueba', array("LOL 3", "I"), array('nombre', 'estado'));
$this->assertTrue($success);
$success = $connection->insert('prueba', array(new Phalcon\Db\RawValue('current_date'), "A"), array('nombre', 'estado'));
$this->assertTrue($success);
for ($i = 0; $i < 50; $i++) {
$success = $connection->insert('prueba', array("LOL " . $i, "F"), array('nombre', 'estado'));
$this->assertTrue($success);
}
$success = $connection->update('prueba', array("nombre", "estado"), array("LOL 1000", "X"), "estado='E'");
$this->assertTrue($success);
$success = $connection->update('prueba', array("nombre"), array("LOL 3000"), "estado='X'");
$this->assertTrue($success);
$success = $connection->update('prueba', array("nombre"), array(new Phalcon\Db\RawValue('current_date')), "estado='X'");
$this->assertTrue($success);
//test array syntax for $whereCondition
$success = $connection->insert('prueba', array("LOL array syntax", "E"), array('nombre', 'estado'));
$this->assertTrue($success);
$success = $connection->update('prueba', array("nombre", 'estado'), array("LOL array syntax 2", 'X'), array('conditions' => "nombre=? and estado = ?", 'bind' => array("LOL array syntax", "E"), 'bindTypes' => array(PDO::PARAM_STR, PDO::PARAM_STR)), array(PDO::PARAM_STR, PDO::PARAM_STR));
$this->assertTrue($success);
$row = $connection->fetchOne('select count(*) as cnt from prueba where nombre=? and estado=?', Phalcon\Db::FETCH_ASSOC, array("LOL array syntax 2", "X"));
$this->assertEquals($row['cnt'], 1);
$success = $connection->update('prueba', array("nombre", 'estado'), array("LOL array syntax 3", 'E'), array('conditions' => "nombre=? and estado = ?", 'bind' => array("LOL array syntax 2", "X")));
$this->assertTrue($success);
$row = $connection->fetchOne('select count(*) as cnt from prueba where nombre=? and estado=?', Phalcon\Db::FETCH_ASSOC, array("LOL array syntax 3", "E"));
$this->assertEquals($row['cnt'], 1);
//test insertAsDict and updateAsDict
$success = $connection->insertAsDict('prueba', array('nombre' => "LOL insertAsDict", 'estado' => "E"));
$this->assertTrue($success);
$row = $connection->fetchOne('select count(*) as cnt from prueba where nombre=? and estado=?', Phalcon\Db::FETCH_ASSOC, array("LOL insertAsDict", "E"));
$this->assertEquals($row['cnt'], 1);
$success = $connection->updateAsDict('prueba', array('nombre' => "LOL updateAsDict", 'estado' => "X"), "nombre='LOL insertAsDict' and estado = 'E'");
$this->assertTrue($success);
$row = $connection->fetchOne('select count(*) as cnt from prueba where nombre=? and estado=?', Phalcon\Db::FETCH_ASSOC, array("LOL updateAsDict", "X"));
$this->assertEquals($row['cnt'], 1);
$connection->delete("prueba", "estado='X'");
$this->assertTrue($success);
$connection->delete("prueba");
$this->assertTrue($success);
$this->assertEquals($connection->affectedRows(), 54);
$row = $connection->fetchOne("SELECT * FROM personas");
$this->assertEquals(count($row), 11);
$row = $connection->fetchOne("SELECT * FROM personas", Phalcon\Db::FETCH_NUM);
$this->assertEquals(count($row), 11);
$rows = $connection->fetchAll("SELECT * FROM personas LIMIT 10");
$this->assertEquals(count($rows), 10);
$rows = $connection->fetchAll("SELECT * FROM personas LIMIT 10", Phalcon\Db::FETCH_NUM);
$this->assertEquals(count($rows), 10);
$this->assertEquals(count($rows[0]), 11);
$id = $connection->fetchColumn("SELECT id FROM robots ORDER BY year DESC");
$this->assertEquals($id, 3);
$type = $connection->fetchColumn("SELECT * FROM robots where id=?", array(1), 2);
$this->assertEquals($type, 'mechanical');
$type = $connection->fetchColumn("SELECT * FROM robots where id=?", array(1), 'type');
$this->assertEquals($type, 'mechanical');
//Auto-Increment/Serial Columns
$sql = 'INSERT INTO subscriptores(id, email, created_at, status) VALUES (' . $connection->getDefaultIdValue() . ', ?, ?, ?)';
$success = $connection->execute($sql, array('[email protected]', "2011-01-01 12:59:13", "P"));
$this->assertTrue($success);
//Check for auto-increment column
$this->assertTrue($connection->lastInsertId('subscriptores_id_seq') > 0);
// Create View
$success = $connection->createView('phalcon_test_view', array('sql' => 'SELECT 1 AS one, 2 AS two, 3 AS three'));
$this->assertTrue($success);
//Check view exists
$success = $connection->viewExists('phalcon_test_view');
$this->assertTrue((bool) $success);
//Gets the list of all views.
$views = $connection->listViews();
$this->assertTrue(is_array($views));
$this->assertTrue(in_array('phalcon_test_view', $views));
//Execute created view
$row = $connection->fetchOne("SELECT * FROM phalcon_test_view");
$this->assertEquals(count($row), 3);
$this->assertTrue(array_key_exists('one', $row));
$this->assertEquals($row['two'], 2);
//Drop view
$success = $connection->dropView('phalcon_test_view');
$this->assertTrue($success);
//Transactions without savepoints.
$connection->setNestedTransactionsWithSavepoints(false);
$success = $connection->begin();
// level 1 - real
$this->assertTrue($success);
$success = $connection->begin();
// level 2 - virtual
$this->assertFalse($success);
$success = $connection->begin();
// level 3 - virtual
$this->assertFalse($success);
$success = $connection->rollback();
// level 2 - virtual
$this->assertFalse($success);
$success = $connection->commit();
// level 1 - virtual
$this->assertFalse($success);
$success = $connection->commit();
// commit - real
$this->assertTrue($success);
$success = $connection->begin();
// level 1 - real
$this->assertTrue($success);
$success = $connection->begin();
// level 2 - virtual
$this->assertFalse($success);
$success = $connection->commit();
// level 1 - virtual
$this->assertFalse($success);
$success = $connection->rollback();
// rollback - real
$this->assertTrue($success);
//Transactions with savepoints.
$connection->setNestedTransactionsWithSavepoints(true);
$success = $connection->begin();
// level 1 - begin transaction
$this->assertTrue($success);
$success = $connection->begin();
// level 2 - uses savepoint_1
$this->assertTrue($success);
$success = $connection->begin();
// level 3 - uses savepoint_2
$this->assertTrue($success);
$success = $connection->rollback();
// level 2 - uses rollback savepoint_2
$this->assertTrue($success);
$success = $connection->commit();
// level 1 - uses release savepoint_1
$this->assertTrue($success);
$success = $connection->commit();
// commit - real commit
$this->assertTrue($success);
$success = $connection->begin();
// level 1 - real begin transaction
$this->assertTrue($success);
$success = $connection->begin();
// level 2 - uses savepoint_1
$this->assertTrue($success);
$success = $connection->commit();
// level 1 - uses release savepoint_1
$this->assertTrue($success);
$success = $connection->rollback();
// rollback - real rollback
$this->assertTrue($success);
}
