PHP mysqlnd svn commit: r1262 - trunk/tests/ext/mysqli

[uwendel]

Author: uwendel
Date: 2008-02-07 14:13:16 +0100 (Thu, 07 Feb 2008)
New Revision: 1262

Added:
   trunk/tests/ext/mysqli/mysqli_affected_rows_update.phpt
Modified:
   trunk/tests/ext/mysqli/mysqli_affected_rows.phpt
   trunk/tests/ext/mysqli/mysqli_affected_rows_oo.phpt
Log:
Fixing tests. MyISAM and others can return different values 
for affected rows but InnoDB. We might want to add a hint
to http://dev.mysql.com/doc/refman/6.0/en/mysql-affected-rows.html for 
users like me... Docs are correct but the pitfall is a bit "hidden".



Modified: trunk/tests/ext/mysqli/mysqli_affected_rows.phpt
===================================================================
--- trunk/tests/ext/mysqli/mysqli_affected_rows.phpt	2008-02-06 16:38:24 UTC (rev 1261)
+++ trunk/tests/ext/mysqli/mysqli_affected_rows.phpt	2008-02-07 13:13:16 UTC (rev 1262)
@@ -1,7 +1,7 @@
 --TEST--
 mysqli_affected_rows()
 --SKIPIF--
-<?php 
+<?php
 	require_once('skipif.inc');
 	require_once('skipifemb.inc');
 	require_once('skipifconnectfailure.inc');
@@ -102,28 +102,20 @@
 			printf("[027] Expecting int/0 got %s/%s\n", gettype($tmp), $tmp);
 	}
 
-	if (!mysqli_query($link, "UPDATE test SET label = 'a' WHERE id = 2")) {
+	if (!mysqli_query($link, "UPDATE test SET label = 'a' WHERE id = 100")) {
 		printf("[028] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
 	}
 
 	if (0 !== ($tmp = mysqli_affected_rows($link)))
 		printf("[029] Expecting int/0, got %s/%s\n", gettype($tmp), $tmp);
 
-	if (!mysqli_query($link, "UPDATE test SET label = 'a' WHERE id = 100")) {
-		printf("[030] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
-	}
-
-	if (0 !== ($tmp = mysqli_affected_rows($link)))
-		printf("[031] Expecting int/0, got %s/%s\n", gettype($tmp), $tmp);
-
 	if (!mysqli_query($link, 'DROP TABLE IF EXISTS test'))
-		printf("[032] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
+		printf("[030] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
 
-
 	mysqli_close($link);
 
 	if (NULL !== ($tmp = @mysqli_affected_rows($link)))
-		printf("[033] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp);
+		printf("[031] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp);
 
 	print "done!";
 ?>

Modified: trunk/tests/ext/mysqli/mysqli_affected_rows_oo.phpt
===================================================================
--- trunk/tests/ext/mysqli/mysqli_affected_rows_oo.phpt	2008-02-06 16:38:24 UTC (rev 1261)
+++ trunk/tests/ext/mysqli/mysqli_affected_rows_oo.phpt	2008-02-07 13:13:16 UTC (rev 1262)
@@ -82,27 +82,20 @@
 	if (1 !== ($tmp = $mysqli->affected_rows))
 		printf("[020] Expecting int/1, got %s/%s\n", gettype($tmp), $tmp);
 
-	if (!$mysqli->query("UPDATE test SET label = 'a' WHERE id = 2")) {
+	if (!$mysqli->query("UPDATE test SET label = 'a' WHERE id = 100")) {
 		printf("[021] [%d] %s\n",  $mysqli->errno, $mysqli->error);
 	}
 
 	if (0 !== ($tmp = $mysqli->affected_rows))
 		printf("[022] Expecting int/0, got %s/%s\n", gettype($tmp), $tmp);
 
-	if (!$mysqli->query("UPDATE test SET label = 'a' WHERE id = 100")) {
-		printf("[023] [%d] %s\n",  $mysqli->errno, $mysqli->error);
-	}
-
-	if (0 !== ($tmp = $mysqli->affected_rows))
-		printf("[024] Expecting int/0, got %s/%s\n", gettype($tmp), $tmp);
-
 	if (!$mysqli->query('DROP TABLE IF EXISTS test'))
-		printf("[025] [%d] %s\n", $mysqli->errno, $mysqli->error);
+		printf("[023] [%d] %s\n", $mysqli->errno, $mysqli->error);
 
 	$mysqli->close();
 
 	if (NULL !== ($tmp = @$mysqli->affected_rows))
-		printf("[026] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp);
+		printf("[024] Expecting NULL, got %s/%s\n", gettype($tmp), $tmp);
 
 	print "done!";
 ?>

Added: trunk/tests/ext/mysqli/mysqli_affected_rows_update.phpt
===================================================================
--- trunk/tests/ext/mysqli/mysqli_affected_rows_update.phpt	                        (rev
0)
+++ trunk/tests/ext/mysqli/mysqli_affected_rows_update.phpt	2008-02-07 13:13:16 UTC (rev
1262)
@@ -0,0 +1,113 @@
+--TEST--
+mysqli_affected_rows() - UPDATE without changing value
+--SKIPIF--
+<?php
+	require_once('skipif.inc');
+	require_once('skipifemb.inc');
+	require_once('skipifconnectfailure.inc');
+?>
+--FILE--
+<?php
+	include "connect.inc";
+
+	$tmp    = NULL;
+	$link   = NULL;
+
+	if (!$link = mysqli_connect($host, $user, $passwd, $db, $port, $socket))
+		printf("[001] Cannot connect to the server using host=%s, user=%s, passwd=***,
dbname=%s, port=%s, socket=%s\n",
+			$host, $user, $db, $port, $socket);
+
+	if (!mysqli_query($link, 'DROP TABLE IF EXISTS test'))
+		printf("[002] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
+
+	if (!mysqli_query($link, 'CREATE TABLE test(id INT, label CHAR(1), PRIMARY KEY(id))
ENGINE = ' . $engine))
+		printf("[003] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
+
+	if (!mysqli_query($link, 'INSERT INTO test(id, label) VALUES (1, "a")'))
+		printf("[004] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
+
+	if (1 !== ($tmp = mysqli_affected_rows($link)))
+		printf("[005] Expecting int/1, got %s/%s\n", gettype($tmp), $tmp);
+
+	if (!mysqli_query($link, "UPDATE test SET label = 'a' WHERE id = 1")) {
+		printf("[006] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
+	}
+
+	/*
+	NOTE: By default affected_rows returns the number of rows actually changed.
+	Some engines (e.g. InnoDB) will change the row no matter if you change the rows value
+	or not. Some engines (e.g. MyISAM, CSV, Memory) will not change the row.
+	Therefore the result value of the following statement varies with the storage
+	engine.
+	*/
+	$affected = mysqli_affected_rows($link);
+	$engine = strtolower(trim($engine));
+	switch ($engine) {
+		case 'innodb':
+			// PBXT likely belongs in this MVCC group as well
+			// Same about Falcon and future Maria versions (01/2008)
+			$expected = 1;
+			break;
+
+		case 'myisam':
+		case 'csv':
+		case 'memory':
+			$expected = 0;
+			break;
+
+		default:
+			$expected = 0;
+			break;
+	}
+
+	if ($expected !== $affected)
+		printf("[007] Expecting %s/%s with engine '%s', got %s/%s\n",
+			gettype($expected), $expected, $engine, gettype($affected), $affected);
+
+	if (!mysqli_query($link, "UPDATE test SET label = 'b' WHERE id = 1")) {
+		printf("[008] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
+	}
+
+	if (1 !== ($tmp = mysqli_affected_rows($link)))
+		printf("[009] Expecting int/1, got %s/%s\n", gettype($tmp), $tmp);
+
+	mysqli_close($link);
+
+
+	/*
+	Note: Now we have a different situation. MYSQLI_CLIENT_FOUND_ROWS makes the
+	Server return the number of rows matched by the UPDATE. And that's 1, regardless
+	of any engine settings etc.
+	*/
+
+	$link = mysqli_init();
+
+	if (!mysqli_real_connect($link, $host, $user, $passwd, $db, $port, $socket,
MYSQLI_CLIENT_FOUND_ROWS))
+		printf("[011] Cannot connect to the server using host=%s, user=%s, passwd=***,
dbname=%s, port=%s, socket=%s\n",
+			$host, $user, $db, $port, $socket);
+
+	if (!mysqli_query($link, 'DROP TABLE IF EXISTS test'))
+		printf("[012] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
+
+	if (!mysqli_query($link, 'CREATE TABLE test(id INT, label CHAR(1), PRIMARY KEY(id))
ENGINE = ' . $engine))
+		printf("[013] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
+
+	if (!mysqli_query($link, 'INSERT INTO test(id, label) VALUES (1, "a")'))
+		printf("[014] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
+
+	if (1 !== ($tmp = mysqli_affected_rows($link)))
+		printf("[015] Expecting int/1, got %s/%s\n", gettype($tmp), $tmp);
+
+	if (!mysqli_query($link, "UPDATE test SET label = 'a' WHERE id = 1")) {
+		printf("[016] [%d] %s\n",  mysqli_errno($link), mysqli_error($link));
+	}
+
+	if (1 !== ($tmp = mysqli_affected_rows($link)))
+		printf("[017] Expecting int/1, got %s/%s\n", gettype($tmp), $tmp);
+
+	mysqli_close($link);
+
+	print "done!";
+?>
+--EXPECTF--
+done!
\ No newline at end of file