Annotation of embedaddon/php/ext/mysqli/tests/mysqli_last_insert_id.phpt, revision 1.1
1.1 ! misho 1: --TEST--
! 2: API vs. SQL LAST_INSERT_ID()
! 3: --SKIPIF--
! 4: <?php
! 5: require_once('skipif.inc');
! 6: require_once('skipifconnectfailure.inc');
! 7: ?>
! 8: --FILE--
! 9: <?php
! 10: /*
! 11: CAUTION: the insert_id() API call is not supposed to return
! 12: the same value as a call to the LAST_INSERT_ID() SQL function.
! 13: It is not necessarily a bug if API and SQL function return different
! 14: values. Check the MySQL C API reference manual for details.
! 15: */
! 16: require_once("connect.inc");
! 17:
! 18: function get_sql_id($link) {
! 19: if (!($res = $link->query("SELECT LAST_INSERT_ID() AS _id"))) {
! 20: printf("[003] [%d] %s\n", $link->errno, $link->error);
! 21: return NULL;
! 22: }
! 23: $row = $res->fetch_assoc();
! 24: $res->close();
! 25:
! 26: return $row['_id'];
! 27: }
! 28:
! 29: if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket))
! 30: printf("[001] Cannot connect to the server using host=%s, user=%s, passwd=***, dbname=%s, port=%s, socket=%s\n",
! 31: $host, $user, $db, $port, $socket);
! 32:
! 33: if (!$link->query("DROP TABLE IF EXISTS test") ||
! 34: !$link->query("CREATE TABLE test (id INT auto_increment, label varchar(10) not null, PRIMARY KEY (id)) ENGINE=MyISAM") ||
! 35: !$link->query("INSERT INTO test (id, label) VALUES (null, 'a')")) {
! 36: printf("[002] [%d] %s\n", $link->errno, $link->error);
! 37: }
! 38:
! 39: $api_id = $link->insert_id;
! 40: $sql_id = get_sql_id($link);
! 41: printf("API: %d, SQL: %d\n", $api_id, $sql_id);
! 42:
! 43: if ($api_id < 1)
! 44: printf("[004] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error) ;
! 45: if ($api_id != $sql_id)
! 46: printf("[005] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
! 47:
! 48: // Not an INSERT, API value must become 0
! 49: if (!($res = $link->query("SELECT 1 FROM DUAL")))
! 50: printf("[006] [%d] %s\n", $link->errno, $link->error);
! 51: else
! 52: $res->close();
! 53:
! 54: $api_id = $link->insert_id;
! 55: $new_sql_id = get_sql_id($link);
! 56: if (0 !== $api_id) {
! 57: printf("[007] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n",
! 58: $api_id, $new_sql_id);
! 59: }
! 60: if ($new_sql_id != $sql_id) {
! 61: printf("[008] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
! 62: }
! 63:
! 64: // Insert fails, LAST_INSERT_ID shall not change, API shall return 0
! 65: if ($link->query("INSERT INTO test (id, label) VALUES (null, null)")) {
! 66: printf("[009] The INSERT did not fail as planned, [%d] %s\n", $link->errno, $link->error);
! 67: }
! 68: $api_id = $link->insert_id;
! 69: $new_sql_id = get_sql_id($link);
! 70:
! 71: if (0 !== $api_id) {
! 72: printf("[010] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n",
! 73: $api_id, $new_sql_id);
! 74: }
! 75: if ($new_sql_id != $sql_id) {
! 76: printf("[011] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
! 77: }
! 78:
! 79: // Sequence counter pattern...
! 80: if (!$link->query("UPDATE test SET id=LAST_INSERT_ID(id+1)"))
! 81: printf("[012] [%d] %s\n", $link->errno, $link->error);
! 82:
! 83: $api_id = $link->insert_id;
! 84: $new_sql_id = get_sql_id($link);
! 85: if ($api_id < 1)
! 86: printf("[013] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error) ;
! 87: if ($api_id != $new_sql_id)
! 88: printf("[014] SQL id %d should be equal to API id %d\n", $new_sql_id, $api_id);
! 89: if ($sql_id == $new_sql_id)
! 90: printf("[015] SQL id %d should have had changed, got %d\n", $sql_id, $new_sql_id);
! 91:
! 92: $sql_id = $new_sql_id;
! 93:
! 94: // Not an INSERT (after UPDATE), API value must become 0
! 95: if (!$link->query("SET @myvar=1"))
! 96: printf("[016] [%d] %s\n", $link->errno, $link->error);
! 97:
! 98: $api_id = $link->insert_id;
! 99: $new_sql_id = get_sql_id($link);
! 100: if (0 !== $api_id) {
! 101: printf("[017] API id should have been reset to 0 because previous query was SET, got API %d, SQL %d\n",
! 102: $api_id, $new_sql_id);
! 103: }
! 104: if ($new_sql_id != $sql_id) {
! 105: printf("[018] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
! 106: }
! 107:
! 108: if (!$link->query("INSERT INTO test(id, label) VALUES (LAST_INSERT_ID(id + 1), 'b')"))
! 109: printf("[019] [%d] %s\n", $link->errno, $link->error);
! 110:
! 111: $api_id = $link->insert_id;
! 112: $sql_id = get_sql_id($link);
! 113: if ($api_id != $sql_id)
! 114: printf("[020] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
! 115:
! 116: if (!$link->query("INSERT INTO test(label) VALUES ('c')"))
! 117: printf("[021] [%d] %s\n", $link->errno, $link->error);
! 118:
! 119: $api_id = $link->insert_id;
! 120: $sql_id = get_sql_id($link);
! 121: if ($api_id != $sql_id)
! 122: printf("[022] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
! 123:
! 124: if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC")))
! 125: printf("[023] [%d] %s\n", $link->errno, $link->error);
! 126:
! 127: printf("Dumping table contents before INSERT...SELECT experiments...\n");
! 128: while ($row = $res->fetch_assoc()) {
! 129: printf("id = %d, label = '%s'\n", $row['id'], $row['label']);
! 130: }
! 131: $res->close();
! 132:
! 133: if (!$link->query("INSERT INTO test(label) SELECT CONCAT(label, id) FROM test ORDER BY id ASC"))
! 134: printf("[024] [%d] %s\n", $link->errno, $link->error);
! 135:
! 136: $api_id = $link->insert_id;
! 137: $sql_id = get_sql_id($link);
! 138: if ($api_id != $sql_id)
! 139: printf("[025] SQL id %d should be equal to API id %d\n", $sql_id, $api_id);
! 140:
! 141: if ($link->query("INSERT INTO test(id, label) SELECT id, CONCAT(label, id) FROM test ORDER BY id ASC"))
! 142: printf("[026] INSERT should have failed because of duplicate PK value, [%d] %s\n", $link->errno, $link->error);
! 143:
! 144: $api_id = $link->insert_id;
! 145: $new_sql_id = get_sql_id($link);
! 146: if (0 !== $api_id) {
! 147: printf("[027] API id should have been reset to 0 because previous query failed, got API %d, SQL %d\n",
! 148: $api_id, $new_sql_id);
! 149: }
! 150: if ($new_sql_id != $sql_id) {
! 151: printf("[028] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
! 152: }
! 153:
! 154: /* API insert id will be 101 because of UPDATE, SQL unchanged */
! 155: if (!$link->query(sprintf("INSERT INTO test(id, label) VALUES (%d, 'z') ON DUPLICATE KEY UPDATE id = 101", $sql_id) ))
! 156: printf("[029] [%d] %s\n", $link->errno, $link->error);
! 157:
! 158: $api_id = $link->insert_id;
! 159: $new_sql_id = get_sql_id($link);
! 160: if ($api_id != 101)
! 161: printf("[030] API id should be %d got %d\n", $sql_id, $api_id);
! 162: if ($new_sql_id != $sql_id) {
! 163: printf("[031] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id);
! 164: }
! 165:
! 166: if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC")))
! 167: printf("[032] [%d] %s\n", $link->errno, $link->error);
! 168:
! 169: printf("Dumping table contents after INSERT...SELECT...\n");
! 170: while ($row = $res->fetch_assoc()) {
! 171: printf("id = %d, label = '%s'\n", $row['id'], $row['label']);
! 172: }
! 173: $res->close();
! 174:
! 175: print "done!";
! 176: ?>
! 177: --CLEAN--
! 178: <?php
! 179: require_once("clean_table.inc");
! 180: ?>
! 181: --EXPECTF--
! 182: API: %d, SQL: %d
! 183: Dumping table contents before INSERT...SELECT experiments...
! 184: id = %d, label = 'b'
! 185: id = %d, label = 'a'
! 186: id = %d, label = 'c'
! 187: Dumping table contents after INSERT...SELECT...
! 188: id = %d, label = 'b'
! 189: id = %d, label = 'a'
! 190: id = %d, label = 'c'
! 191: id = %d, label = 'a%d'
! 192: id = %d, label = 'c%d'
! 193: id = 101, label = 'b%d'
! 194: done!
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to mysqli_last_insert_id.phpt CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / mysqli / tests