MySQLTest::testCanExecutePreparedDMLStatement() - Code Metrics - Inspection of "Add unit tests for EntityAdapter (ORM Tool) and so..." - Pleets/dronephp - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 84ef81...00604e )

by Darío

created 2019-03-21 23:19 UTC

MySQLTest::testCanExecutePreparedDMLStatement() A

↳ Parent: MySQLTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	15
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	9
nc	1
nop	0
dl	0
loc	15
rs	9.9666
c	0
b	0
f	0

<?php
/**
 * DronePHP (http://www.dronephp.com)
 *
 * @link      http://github.com/Pleets/DronePHP
 * @copyright Copyright (c) 2016-2018 Pleets. (http://www.pleets.org)
 * @license   http://www.dronephp.com/license
 * @author    Darío Rivera <[email protected]>
 */

namespace DroneTest\Util;

use Drone\Db\Driver\MySQL;
use Drone\Db\Driver\Exception\ConnectionException;
use Drone\Db\Driver\Exception\InvalidQueryException;
use PHPUnit\Framework\TestCase;

class MySQLTest extends TestCase
{
    /**
     * Database parameters
     */
    private $options = [
        "dbhost"       => "localhost",
        "dbuser"       => "root",
        "dbpass"       => "",
        "dbname"       => "test",
        "dbchar"       => "utf8",
        "dbport"       => "3306",
        "auto_connect" => false
    ];

    /*
    |--------------------------------------------------------------------------
    | Stablishing connections
    |--------------------------------------------------------------------------
    |
    | The following tests are related to the connection methods and its
    | exceptions and returned values.
    |
    */

    /**
     * Tests if we can connect to the database server
     *
     * @return null
     */
    public function testCanStablishConnection()
    {
        $conn = new MySQL($this->options);

        $mysqliObject = $conn->connect();

        $this->assertInstanceOf('\mysqli', $mysqliObject);
        $this->assertTrue($conn->isConnected());
    }

    /**
     * Tests if we can disconnect from the database server
     *
     * @return null
     */
    public function testCanDownConnection()
    {
        $conn = new MySQL($this->options);

        $conn->connect();
        $result = $conn->disconnect();

        $this->assertNotTrue($conn->isConnected());
        $this->assertTrue($result);
    }

    /**
     * Tests if we can disconnect from server when there is not a connection stablished
     *
     * @return null
     */
    public function testCannotDisconectWhenNotConnected()
    {
        $conn = new MySQL($this->options);

        $errorObject = null;

        try {
            $conn->disconnect();
        }
        catch (\Exception $e)
        {
            $errorObject = ($e instanceof \LogicException);
        }
        finally
        {
            $this->assertNotTrue($conn->isConnected());
            $this->assertTrue($errorObject, $e->getMessage());

        }
    }

    /**
     * Tests if we can reconnect to the database server
     *
     * @return null
     */
    public function testCanStablishConnectionAgain()
    {
        $conn = new MySQL($this->options);

        $conn->connect();
        $mysqliObject = $conn->reconnect();

        $this->assertInstanceOf('\mysqli', $mysqliObject);
        $this->assertTrue($conn->isConnected());
    }

    /**
     * Tests if we can reconnect to the database server when there is not a connection stablished
     *
     * @return null
     */
    public function testCannotStablishReconnection()
    {
        $conn = new MySQL($this->options);

        $errorObject = null;

        try {
            $conn->reconnect();
        }
        catch (\Exception $e)
        {
            $errorObject = ($e instanceof \LogicException);
        }
        finally
        {
            $this->assertTrue($errorObject, $e->getMessage());

            $this->assertNotTrue($conn->isConnected());
        }
    }

    /**
     * Tests if a failed connection throws a ConnectionException
     *
     * @return null
     */
    public function testCannotStablishConnection()
    {
        $options = $this->options;
        $options["dbhost"] = 'myserver';   // this server does not exists

        $conn = new MySQL($options);

        $errorObject = null;

        $message = "No exception";

        try
        {
            $conn->connect();
        }
        catch (\Exception $e)
        {
            $errorObject = ($e instanceof ConnectionException);
            $message = $e->getMessage();
        }
        finally
        {
            $this->assertTrue($errorObject, $message);
            $this->assertNotTrue($conn->isConnected());
        }
    }

    /*
    |--------------------------------------------------------------------------
    | Quering and Transactions
    |--------------------------------------------------------------------------
    |
    | The following tests are related to query and transaction operations and its
    | exceptions and returned values.
    |
    */

    /**
     * Tests if we can execute DDL statements
     *
     * @return null
     */
    public function testCanExecuteDLLStatement()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $sql = "CREATE TABLE MYTABLE (ID INTEGER(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, DESCRIPTION VARCHAR(100))";
        $result = $conn->execute($sql);

        # mysqli
        $this->assertTrue(is_object($result));

        # properties modified by execute() method
        $this->assertEquals(0, $conn->getNumRows());
        $this->assertEquals(0, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());
    }

    /**
     * Tests if we can execute DML statements
     *
     * @return null
     */
    public function testCanExecuteDMLStatement()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('Hello world!')";
        $result = $conn->execute($sql);

        $this->assertTrue(is_object($result));

        # properties modified by execute() method
        $this->assertEquals(0, $conn->getNumRows());
        $this->assertEquals(0, $conn->getNumFields());
        $this->assertEquals(1, $conn->getRowsAffected());
    }

    /**
     * Tests if we can execute a prepared DML statement
     *
     * @return null
     */
    public function testCanExecutePreparedDMLStatement()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $sql = "UPDATE MYTABLE SET DESCRIPTION = ? WHERE ID = ?";
        $result = $conn->execute($sql, ["Bonjour le monde!", 1]);

        $this->assertTrue(is_object($result));

        # properties modified by execute() method
        $this->assertEquals(0, $conn->getNumRows());
        $this->assertEquals(0, $conn->getNumFields());
        $this->assertEquals(1, $conn->getRowsAffected());
    }

    /**
     * Tests if a wrong query execution throws an InvalidQueryException
     *
     * @return null
     */
    public function testGettingInvalidQueryException()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);

        $errorObject = null;
        $message = "No exception";

        try
        {
            $sql = "INSERT INTO MYTABLE (DESCRIPTION, WRONG) VALUES ('Hello world!')";
            $conn->execute($sql);
        }
        catch (\Exception $e)
        {
            $errorObject = ($e instanceof InvalidQueryException);
            $message = $e->getMessage();
        }
        finally
        {
            $this->assertTrue($errorObject, $message);
        }
    }

    /**
     * Tests getting results
     *
     * @return null
     */
    public function testGettingResults()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $sql = "SELECT * FROM MYTABLE LIMIT 2";
        $conn->execute($sql);

        # properties modified by execute() method
        $this->assertEquals(1, $conn->getNumRows());
        $this->assertEquals(2, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());

        $rowset = $conn->getArrayResult();    # array with results
        $row = array_shift($rowset);

        $this->assertArrayHasKey("ID", $row);
        $this->assertArrayHasKey("DESCRIPTION", $row);
    }

    /**
     * Tests if we can commit transactions
     *
     * @return null
     */
    public function testCommitBehavior()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $conn->autocommit(false);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_1')";
        $conn->execute($sql);

        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'COMMIT_ROW_1'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 1));    # the row is available for now

        # properties modified by execute() method
        $this->assertEquals(1, $conn->getNumRows());
        $this->assertEquals(2, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());    # nothing affected (autocommit = false)

        $this->assertTrue($conn->commit());

        # now let's to verify if the record exists after commit
        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'COMMIT_ROW_1'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 1));    # the row is available
    }

    /**
     * Tests if we can rollback transactions
     *
     * @return null
     */
    public function testRollbackBehavior()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $conn->autocommit(false);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_1')";
        $conn->execute($sql);

        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'ROLLBACK_ROW_1'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 1));    # the row is available for now

        # properties modified by execute() method
        $this->assertEquals(1, $conn->getNumRows());
        $this->assertEquals(2, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());    # nothing affected (autocommit = false)

        $this->assertTrue($conn->rollback());

        # now let's to verify if the record exists after commit
        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'ROLLBACK_ROW_1'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertNotTrue(($rowcount === 1));    # the row is not available
    }

    /**
     * Tests if we can do a transaction with commiting changes
     *
     * @return null
     */
    public function testTransactionConfirmation()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $conn->autocommit(false);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_1')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_2')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_3')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_4')";
        $conn->execute($sql);

        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'COMMIT_ROW_TRANSACTION_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 4));    # the rows are available for now

        # properties modified by execute() method
        $this->assertEquals(4, $conn->getNumRows());
        $this->assertEquals(2, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());    # nothing affected (autocommit = false)

        $this->assertTrue($conn->commit());

        # now let's to verify if the record exists after commit
        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'COMMIT_ROW_TRANSACTION_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 4));    # the rows are available
    }

    /**
     * Tests if we can do a transaction with reverting changes
     *
     * @return null
     */
    public function testTransactionReversion()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);
        $conn->autocommit(false);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_1')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_2')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_3')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_4')";
        $conn->execute($sql);

        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'ROLLBACK_ROW_TRANSACTION_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 4));    # the rows are available for now

        # properties modified by execute() method
        $this->assertEquals(4, $conn->getNumRows());
        $this->assertEquals(2, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());    # nothing affected (autocommit = false)

        $this->assertTrue($conn->rollback());

        # now let's to verify if the record exists after commit
        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'ROLLBACK_ROW_TRANSACTION_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertNotTrue(($rowcount === 4));    # the rows are not available
    }

    /**
     * Tests if we can do a transaction with the shortcut method
     *
     * @return null
     */
    public function testTransactionConfirmationShortcut()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);

        # not necessary!
        # $conn->autocommit(false);

        # starts the transaction
        $conn->beginTransaction();

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('TRANSACTION_SHORTCUT_1')";
        $conn->execute($sql);

        $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('TRANSACTION_SHORTCUT_1')";
        $conn->execute($sql);

        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'TRANSACTION_SHORTCUT_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 2));    # the rows are available for now

        # properties modified by execute() method
        $this->assertEquals(2, $conn->getNumRows());
        $this->assertEquals(2, $conn->getNumFields());
        $this->assertEquals(0, $conn->getRowsAffected());    # nothing affected (autocommit = false)

        # ends the transaction
        $conn->endTransaction();

        # now let's to verify if the record exists after endTransaction()
        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'TRANSACTION_SHORTCUT_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertTrue(($rowcount === 2));    # the row is available
    }

    /**
     * Tests if we can do a transaction with reverting changes
     *
     * @return null
     */
    public function testTransactionReversionShortcut()
    {
        $options = $this->options;
        $options["auto_connect"] = true;

        $conn = new MySQL($options);

        # not necessary!
        # $conn->autocommit(false);

        # starts the transaction
        $conn->beginTransaction();

        try
        {
            $sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('TRANS_SHORTCUT_1')";
            $conn->execute($sql);

            $sql = "INSERT INTO MYTABLE (DESCRIPTION, WRONG) VALUES ('TRANS_SHORTCUT_2')";
            $conn->execute($sql);
        }
        catch (InvalidQueryException $e)
        {
            $message = $e->getMessage();

            #·not necessary!
            # $this->assertTrue($conn->rollback());
        }

        # starts the transaction
        $conn->endTransaction();

        # now let's to verify if the record exists after endTransaction()
        $sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'TRANS_SHORTCUT_%'";
        $conn->execute($sql);
        $rowcount = count($conn->getArrayResult());

        $this->assertNotTrue(($rowcount === 0));    # the rows are not available

        # remove all work
        $conn->execute("DROP TABLE MYTABLE");
    }
}

1			<?php
2			/**
3			* DronePHP (http://www.dronephp.com)
4			*
5			* @link http://github.com/Pleets/DronePHP
6			* @copyright Copyright (c) 2016-2018 Pleets. (http://www.pleets.org)
7			* @license http://www.dronephp.com/license
8			* @author Darío Rivera <[email protected]>
9			*/
10
11			namespace DroneTest\Util;
12
13			use Drone\Db\Driver\MySQL;
14			use Drone\Db\Driver\Exception\ConnectionException;
15			use Drone\Db\Driver\Exception\InvalidQueryException;
16			use PHPUnit\Framework\TestCase;
17
18			class MySQLTest extends TestCase
19			{
20			/**
21			* Database parameters
22			*/
23			private $options = [
24			"dbhost" => "localhost",
25			"dbuser" => "root",
26			"dbpass" => "",
27			"dbname" => "test",
28			"dbchar" => "utf8",
29			"dbport" => "3306",
30			"auto_connect" => false
31			];
32
33			/*
34			\|--------------------------------------------------------------------------
35			\| Stablishing connections
36			\|--------------------------------------------------------------------------
37			\|
38			\| The following tests are related to the connection methods and its
39			\| exceptions and returned values.
40			\|
41			*/
42
43			/**
44			* Tests if we can connect to the database server
45			*
46			* @return null
47			*/
48			public function testCanStablishConnection()
49			{
50			$conn = new MySQL($this->options);
51
52			$mysqliObject = $conn->connect();
53
54			$this->assertInstanceOf('\mysqli', $mysqliObject);
55			$this->assertTrue($conn->isConnected());
56			}
57
58			/**
59			* Tests if we can disconnect from the database server
60			*
61			* @return null
62			*/
63			public function testCanDownConnection()
64			{
65			$conn = new MySQL($this->options);
66
67			$conn->connect();
68			$result = $conn->disconnect();
69
70			$this->assertNotTrue($conn->isConnected());
71			$this->assertTrue($result);
72			}
73
74			/**
75			* Tests if we can disconnect from server when there is not a connection stablished
76			*
77			* @return null
78			*/
79			public function testCannotDisconectWhenNotConnected()
80			{
81			$conn = new MySQL($this->options);
82
83			$errorObject = null;
84
85			try {
86			$conn->disconnect();
87			}
88			catch (\Exception $e)
89			{
90			$errorObject = ($e instanceof \LogicException);
91			}
92			finally
93			{
94			$this->assertNotTrue($conn->isConnected());
95			$this->assertTrue($errorObject, $e->getMessage());
			0 ignored issues – show Comprehensibility Best Practice introduced 2018-12-10 13:47 UTC by Report Bug Copy Issue Report The variable `$e` does not seem to be defined for all execution paths leading up to this point. Loading history...
96			}
97			}
98
99			/**
100			* Tests if we can reconnect to the database server
101			*
102			* @return null
103			*/
104			public function testCanStablishConnectionAgain()
105			{
106			$conn = new MySQL($this->options);
107
108			$conn->connect();
109			$mysqliObject = $conn->reconnect();
110
111			$this->assertInstanceOf('\mysqli', $mysqliObject);
112			$this->assertTrue($conn->isConnected());
113			}
114
115			/**
116			* Tests if we can reconnect to the database server when there is not a connection stablished
117			*
118			* @return null
119			*/
120			public function testCannotStablishReconnection()
121			{
122			$conn = new MySQL($this->options);
123
124			$errorObject = null;
125
126			try {
127			$conn->reconnect();
128			}
129			catch (\Exception $e)
130			{
131			$errorObject = ($e instanceof \LogicException);
132			}
133			finally
134			{
135			$this->assertTrue($errorObject, $e->getMessage());
			0 ignored issues – show Comprehensibility Best Practice introduced 2018-12-10 13:47 UTC by Report Bug Copy Issue Report The variable `$e` does not seem to be defined for all execution paths leading up to this point. Loading history...
136			$this->assertNotTrue($conn->isConnected());
137			}
138			}
139
140			/**
141			* Tests if a failed connection throws a ConnectionException
142			*
143			* @return null
144			*/
145			public function testCannotStablishConnection()
146			{
147			$options = $this->options;
148			$options["dbhost"] = 'myserver'; // this server does not exists
149
150			$conn = new MySQL($options);
151
152			$errorObject = null;
153
154			$message = "No exception";
155
156			try
157			{
158			$conn->connect();
159			}
160			catch (\Exception $e)
161			{
162			$errorObject = ($e instanceof ConnectionException);
163			$message = $e->getMessage();
164			}
165			finally
166			{
167			$this->assertTrue($errorObject, $message);
168			$this->assertNotTrue($conn->isConnected());
169			}
170			}
171
172			/*
173			\|--------------------------------------------------------------------------
174			\| Quering and Transactions
175			\|--------------------------------------------------------------------------
176			\|
177			\| The following tests are related to query and transaction operations and its
178			\| exceptions and returned values.
179			\|
180			*/
181
182			/**
183			* Tests if we can execute DDL statements
184			*
185			* @return null
186			*/
187			public function testCanExecuteDLLStatement()
188			{
189			$options = $this->options;
190			$options["auto_connect"] = true;
191
192			$conn = new MySQL($options);
193			$sql = "CREATE TABLE MYTABLE (ID INTEGER(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, DESCRIPTION VARCHAR(100))";
194			$result = $conn->execute($sql);
195
196			# mysqli
197			$this->assertTrue(is_object($result));
198
199			# properties modified by execute() method
200			$this->assertEquals(0, $conn->getNumRows());
201			$this->assertEquals(0, $conn->getNumFields());
202			$this->assertEquals(0, $conn->getRowsAffected());
203			}
204
205			/**
206			* Tests if we can execute DML statements
207			*
208			* @return null
209			*/
210			public function testCanExecuteDMLStatement()
211			{
212			$options = $this->options;
213			$options["auto_connect"] = true;
214
215			$conn = new MySQL($options);
216			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('Hello world!')";
217			$result = $conn->execute($sql);
218
219			$this->assertTrue(is_object($result));
220
221			# properties modified by execute() method
222			$this->assertEquals(0, $conn->getNumRows());
223			$this->assertEquals(0, $conn->getNumFields());
224			$this->assertEquals(1, $conn->getRowsAffected());
225			}
226
227			/**
228			* Tests if we can execute a prepared DML statement
229			*
230			* @return null
231			*/
232			public function testCanExecutePreparedDMLStatement()
233			{
234			$options = $this->options;
235			$options["auto_connect"] = true;
236
237			$conn = new MySQL($options);
238			$sql = "UPDATE MYTABLE SET DESCRIPTION = ? WHERE ID = ?";
239			$result = $conn->execute($sql, ["Bonjour le monde!", 1]);
240
241			$this->assertTrue(is_object($result));
242
243			# properties modified by execute() method
244			$this->assertEquals(0, $conn->getNumRows());
245			$this->assertEquals(0, $conn->getNumFields());
246			$this->assertEquals(1, $conn->getRowsAffected());
247			}
248
249			/**
250			* Tests if a wrong query execution throws an InvalidQueryException
251			*
252			* @return null
253			*/
254			public function testGettingInvalidQueryException()
255			{
256			$options = $this->options;
257			$options["auto_connect"] = true;
258
259			$conn = new MySQL($options);
260
261			$errorObject = null;
262			$message = "No exception";
263
264			try
265			{
266			$sql = "INSERT INTO MYTABLE (DESCRIPTION, WRONG) VALUES ('Hello world!')";
267			$conn->execute($sql);
268			}
269			catch (\Exception $e)
270			{
271			$errorObject = ($e instanceof InvalidQueryException);
272			$message = $e->getMessage();
273			}
274			finally
275			{
276			$this->assertTrue($errorObject, $message);
277			}
278			}
279
280			/**
281			* Tests getting results
282			*
283			* @return null
284			*/
285			public function testGettingResults()
286			{
287			$options = $this->options;
288			$options["auto_connect"] = true;
289
290			$conn = new MySQL($options);
291			$sql = "SELECT * FROM MYTABLE LIMIT 2";
292			$conn->execute($sql);
293
294			# properties modified by execute() method
295			$this->assertEquals(1, $conn->getNumRows());
296			$this->assertEquals(2, $conn->getNumFields());
297			$this->assertEquals(0, $conn->getRowsAffected());
298
299			$rowset = $conn->getArrayResult(); # array with results
300			$row = array_shift($rowset);
301
302			$this->assertArrayHasKey("ID", $row);
303			$this->assertArrayHasKey("DESCRIPTION", $row);
304			}
305
306			/**
307			* Tests if we can commit transactions
308			*
309			* @return null
310			*/
311			public function testCommitBehavior()
312			{
313			$options = $this->options;
314			$options["auto_connect"] = true;
315
316			$conn = new MySQL($options);
317			$conn->autocommit(false);
318
319			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_1')";
320			$conn->execute($sql);
321
322			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'COMMIT_ROW_1'";
323			$conn->execute($sql);
324			$rowcount = count($conn->getArrayResult());
325
326			$this->assertTrue(($rowcount === 1)); # the row is available for now
327
328			# properties modified by execute() method
329			$this->assertEquals(1, $conn->getNumRows());
330			$this->assertEquals(2, $conn->getNumFields());
331			$this->assertEquals(0, $conn->getRowsAffected()); # nothing affected (autocommit = false)
332
333			$this->assertTrue($conn->commit());
334
335			# now let's to verify if the record exists after commit
336			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'COMMIT_ROW_1'";
337			$conn->execute($sql);
338			$rowcount = count($conn->getArrayResult());
339
340			$this->assertTrue(($rowcount === 1)); # the row is available
341			}
342
343			/**
344			* Tests if we can rollback transactions
345			*
346			* @return null
347			*/
348			public function testRollbackBehavior()
349			{
350			$options = $this->options;
351			$options["auto_connect"] = true;
352
353			$conn = new MySQL($options);
354			$conn->autocommit(false);
355
356			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_1')";
357			$conn->execute($sql);
358
359			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'ROLLBACK_ROW_1'";
360			$conn->execute($sql);
361			$rowcount = count($conn->getArrayResult());
362
363			$this->assertTrue(($rowcount === 1)); # the row is available for now
364
365			# properties modified by execute() method
366			$this->assertEquals(1, $conn->getNumRows());
367			$this->assertEquals(2, $conn->getNumFields());
368			$this->assertEquals(0, $conn->getRowsAffected()); # nothing affected (autocommit = false)
369
370			$this->assertTrue($conn->rollback());
371
372			# now let's to verify if the record exists after commit
373			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION = 'ROLLBACK_ROW_1'";
374			$conn->execute($sql);
375			$rowcount = count($conn->getArrayResult());
376
377			$this->assertNotTrue(($rowcount === 1)); # the row is not available
378			}
379
380			/**
381			* Tests if we can do a transaction with commiting changes
382			*
383			* @return null
384			*/
385			public function testTransactionConfirmation()
386			{
387			$options = $this->options;
388			$options["auto_connect"] = true;
389
390			$conn = new MySQL($options);
391			$conn->autocommit(false);
392
393			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_1')";
394			$conn->execute($sql);
395
396			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_2')";
397			$conn->execute($sql);
398
399			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_3')";
400			$conn->execute($sql);
401
402			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('COMMIT_ROW_TRANSACTION_4')";
403			$conn->execute($sql);
404
405			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'COMMIT_ROW_TRANSACTION_%'";
406			$conn->execute($sql);
407			$rowcount = count($conn->getArrayResult());
408
409			$this->assertTrue(($rowcount === 4)); # the rows are available for now
410
411			# properties modified by execute() method
412			$this->assertEquals(4, $conn->getNumRows());
413			$this->assertEquals(2, $conn->getNumFields());
414			$this->assertEquals(0, $conn->getRowsAffected()); # nothing affected (autocommit = false)
415
416			$this->assertTrue($conn->commit());
417
418			# now let's to verify if the record exists after commit
419			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'COMMIT_ROW_TRANSACTION_%'";
420			$conn->execute($sql);
421			$rowcount = count($conn->getArrayResult());
422
423			$this->assertTrue(($rowcount === 4)); # the rows are available
424			}
425
426			/**
427			* Tests if we can do a transaction with reverting changes
428			*
429			* @return null
430			*/
431			public function testTransactionReversion()
432			{
433			$options = $this->options;
434			$options["auto_connect"] = true;
435
436			$conn = new MySQL($options);
437			$conn->autocommit(false);
438
439			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_1')";
440			$conn->execute($sql);
441
442			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_2')";
443			$conn->execute($sql);
444
445			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_3')";
446			$conn->execute($sql);
447
448			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('ROLLBACK_ROW_TRANSACTION_4')";
449			$conn->execute($sql);
450
451			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'ROLLBACK_ROW_TRANSACTION_%'";
452			$conn->execute($sql);
453			$rowcount = count($conn->getArrayResult());
454
455			$this->assertTrue(($rowcount === 4)); # the rows are available for now
456
457			# properties modified by execute() method
458			$this->assertEquals(4, $conn->getNumRows());
459			$this->assertEquals(2, $conn->getNumFields());
460			$this->assertEquals(0, $conn->getRowsAffected()); # nothing affected (autocommit = false)
461
462			$this->assertTrue($conn->rollback());
463
464			# now let's to verify if the record exists after commit
465			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'ROLLBACK_ROW_TRANSACTION_%'";
466			$conn->execute($sql);
467			$rowcount = count($conn->getArrayResult());
468
469			$this->assertNotTrue(($rowcount === 4)); # the rows are not available
470			}
471
472			/**
473			* Tests if we can do a transaction with the shortcut method
474			*
475			* @return null
476			*/
477			public function testTransactionConfirmationShortcut()
478			{
479			$options = $this->options;
480			$options["auto_connect"] = true;
481
482			$conn = new MySQL($options);
483
484			# not necessary!
485			# $conn->autocommit(false);
486
487			# starts the transaction
488			$conn->beginTransaction();
489
490			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('TRANSACTION_SHORTCUT_1')";
491			$conn->execute($sql);
492
493			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('TRANSACTION_SHORTCUT_1')";
494			$conn->execute($sql);
495
496			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'TRANSACTION_SHORTCUT_%'";
497			$conn->execute($sql);
498			$rowcount = count($conn->getArrayResult());
499
500			$this->assertTrue(($rowcount === 2)); # the rows are available for now
501
502			# properties modified by execute() method
503			$this->assertEquals(2, $conn->getNumRows());
504			$this->assertEquals(2, $conn->getNumFields());
505			$this->assertEquals(0, $conn->getRowsAffected()); # nothing affected (autocommit = false)
506
507			# ends the transaction
508			$conn->endTransaction();
509
510			# now let's to verify if the record exists after endTransaction()
511			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'TRANSACTION_SHORTCUT_%'";
512			$conn->execute($sql);
513			$rowcount = count($conn->getArrayResult());
514
515			$this->assertTrue(($rowcount === 2)); # the row is available
516			}
517
518			/**
519			* Tests if we can do a transaction with reverting changes
520			*
521			* @return null
522			*/
523			public function testTransactionReversionShortcut()
524			{
525			$options = $this->options;
526			$options["auto_connect"] = true;
527
528			$conn = new MySQL($options);
529
530			# not necessary!
531			# $conn->autocommit(false);
532
533			# starts the transaction
534			$conn->beginTransaction();
535
536			try
537			{
538			$sql = "INSERT INTO MYTABLE (DESCRIPTION) VALUES ('TRANS_SHORTCUT_1')";
539			$conn->execute($sql);
540
541			$sql = "INSERT INTO MYTABLE (DESCRIPTION, WRONG) VALUES ('TRANS_SHORTCUT_2')";
542			$conn->execute($sql);
543			}
544			catch (InvalidQueryException $e)
545			{
546			$message = $e->getMessage();
			0 ignored issues – show Unused Code introduced 2018-12-23 18:19 UTC by Report Bug Copy Issue Report The assignment to `$message` is dead and can be removed. Loading history...
547			#·not necessary!
548			# $this->assertTrue($conn->rollback());
549			}
550
551			# starts the transaction
552			$conn->endTransaction();
553
554			# now let's to verify if the record exists after endTransaction()
555			$sql = "SELECT * FROM MYTABLE WHERE DESCRIPTION LIKE 'TRANS_SHORTCUT_%'";
556			$conn->execute($sql);
557			$rowcount = count($conn->getArrayResult());
558
559			$this->assertNotTrue(($rowcount === 0)); # the rows are not available
560
561			# remove all work
562			$conn->execute("DROP TABLE MYTABLE");
563			}
564			}

Pleets / dronephp

Push — master ( 84ef81...00604e )

MySQLTest::testCanExecutePreparedDMLStatement() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like