1
0

LegacyDBTest.php 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299
  1. <?php
  2. /**
  3. * Copyright (c) 2012 Bart Visscher <bartv@thisnet.nl>
  4. * This file is licensed under the Affero General Public License version 3 or
  5. * later.
  6. * See the COPYING-README file.
  7. */
  8. namespace Test\DB;
  9. use OC_DB;
  10. /**
  11. * Class LegacyDBTest
  12. *
  13. * @group DB
  14. */
  15. class LegacyDBTest extends \Test\TestCase {
  16. protected $backupGlobals = FALSE;
  17. protected static $schema_file;
  18. protected $test_prefix;
  19. public static function setUpBeforeClass() {
  20. self::$schema_file = \OC::$server->getTempManager()->getTemporaryFile();
  21. }
  22. /**
  23. * @var string
  24. */
  25. private $table1;
  26. /**
  27. * @var string
  28. */
  29. private $table2;
  30. /**
  31. * @var string
  32. */
  33. private $table3;
  34. /**
  35. * @var string
  36. */
  37. private $table4;
  38. /**
  39. * @var string
  40. */
  41. private $table5;
  42. /**
  43. * @var string
  44. */
  45. private $text_table;
  46. protected function setUp() {
  47. parent::setUp();
  48. $dbFile = \OC::$SERVERROOT.'/tests/data/db_structure.xml';
  49. $r = $this->getUniqueID('_', 4).'_';
  50. $content = file_get_contents( $dbFile );
  51. $content = str_replace( '*dbprefix*', '*dbprefix*'.$r, $content );
  52. file_put_contents( self::$schema_file, $content );
  53. OC_DB::createDbFromStructure(self::$schema_file);
  54. $this->test_prefix = $r;
  55. $this->table1 = $this->test_prefix.'cntcts_addrsbks';
  56. $this->table2 = $this->test_prefix.'cntcts_cards';
  57. $this->table3 = $this->test_prefix.'vcategory';
  58. $this->table4 = $this->test_prefix.'decimal';
  59. $this->table5 = $this->test_prefix.'uniconst';
  60. $this->text_table = $this->test_prefix.'text_table';
  61. }
  62. protected function tearDown() {
  63. OC_DB::removeDBStructure(self::$schema_file);
  64. unlink(self::$schema_file);
  65. parent::tearDown();
  66. }
  67. public function testQuotes() {
  68. $query = OC_DB::prepare('SELECT `fullname` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?');
  69. $result = $query->execute(array('uri_1'));
  70. $this->assertTrue((bool)$result);
  71. $row = $result->fetchRow();
  72. $this->assertFalse($row);
  73. $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (?,?)');
  74. $result = $query->execute(array('fullname test', 'uri_1'));
  75. $this->assertEquals(1, $result);
  76. $query = OC_DB::prepare('SELECT `fullname`,`uri` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?');
  77. $result = $query->execute(array('uri_1'));
  78. $this->assertTrue((bool)$result);
  79. $row = $result->fetchRow();
  80. $this->assertArrayHasKey('fullname', $row);
  81. $this->assertEquals($row['fullname'], 'fullname test');
  82. $row = $result->fetchRow();
  83. $this->assertFalse((bool)$row); //PDO returns false, MDB2 returns null
  84. }
  85. /**
  86. * @medium
  87. */
  88. public function testNOW() {
  89. $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (NOW(),?)');
  90. $result = $query->execute(array('uri_2'));
  91. $this->assertEquals(1, $result);
  92. $query = OC_DB::prepare('SELECT `fullname`,`uri` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?');
  93. $result = $query->execute(array('uri_2'));
  94. $this->assertTrue((bool)$result);
  95. }
  96. public function testUNIX_TIMESTAMP() {
  97. $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (UNIX_TIMESTAMP(),?)');
  98. $result = $query->execute(array('uri_3'));
  99. $this->assertEquals(1, $result);
  100. $query = OC_DB::prepare('SELECT `fullname`,`uri` FROM `*PREFIX*'.$this->table2.'` WHERE `uri` = ?');
  101. $result = $query->execute(array('uri_3'));
  102. $this->assertTrue((bool)$result);
  103. }
  104. public function testLastInsertId() {
  105. $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (?,?)');
  106. $result1 = OC_DB::executeAudited($query, array('insertid 1','uri_1'));
  107. $id1 = \OC::$server->getDatabaseConnection()->lastInsertId('*PREFIX*'.$this->table2);
  108. // we don't know the id we should expect, so insert another row
  109. $result2 = OC_DB::executeAudited($query, array('insertid 2','uri_2'));
  110. $id2 = \OC::$server->getDatabaseConnection()->lastInsertId('*PREFIX*'.$this->table2);
  111. // now we can check if the two ids are in correct order
  112. $this->assertGreaterThan($id1, $id2);
  113. }
  114. public function testUtf8Data() {
  115. $table = "*PREFIX*{$this->table2}";
  116. $expected = "Ћö雙喜\xE2\x80\xA2";
  117. $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)");
  118. $result = $query->execute(array($expected, 'uri_1', 'This is a vCard'));
  119. $this->assertEquals(1, $result);
  120. $actual = OC_DB::prepare("SELECT `fullname` FROM `$table`")->execute()->fetchOne();
  121. $this->assertSame($expected, $actual);
  122. }
  123. /**
  124. * Insert, select and delete decimal(12,2) values
  125. * @dataProvider decimalData
  126. */
  127. public function testDecimal($insert, $expect) {
  128. $table = "*PREFIX*" . $this->table4;
  129. $rowname = 'decimaltest';
  130. $query = OC_DB::prepare('INSERT INTO `' . $table . '` (`' . $rowname . '`) VALUES (?)');
  131. $result = $query->execute(array($insert));
  132. $this->assertEquals(1, $result);
  133. $query = OC_DB::prepare('SELECT `' . $rowname . '` FROM `' . $table . '`');
  134. $result = $query->execute();
  135. $this->assertTrue((bool)$result);
  136. $row = $result->fetchRow();
  137. $this->assertArrayHasKey($rowname, $row);
  138. $this->assertEquals($expect, $row[$rowname]);
  139. $query = OC_DB::prepare('DELETE FROM `' . $table . '`');
  140. $result = $query->execute();
  141. $this->assertTrue((bool)$result);
  142. }
  143. public function decimalData() {
  144. return [
  145. ['1337133713.37', '1337133713.37'],
  146. ['1234567890', '1234567890.00'],
  147. ];
  148. }
  149. public function testUpdateAffectedRowsNoMatch() {
  150. $this->insertCardData('fullname1', 'uri1');
  151. // The WHERE clause does not match any rows
  152. $this->assertSame(0, $this->updateCardData('fullname3', 'uri2'));
  153. }
  154. public function testUpdateAffectedRowsDifferent() {
  155. $this->insertCardData('fullname1', 'uri1');
  156. // The WHERE clause matches a single row and the value we are updating
  157. // is different from the one already present.
  158. $this->assertSame(1, $this->updateCardData('fullname1', 'uri2'));
  159. }
  160. public function testUpdateAffectedRowsSame() {
  161. $this->insertCardData('fullname1', 'uri1');
  162. // The WHERE clause matches a single row and the value we are updating
  163. // to is the same as the one already present. MySQL reports 0 here when
  164. // the PDO::MYSQL_ATTR_FOUND_ROWS flag is not specified.
  165. $this->assertSame(1, $this->updateCardData('fullname1', 'uri1'));
  166. }
  167. public function testUpdateAffectedRowsMultiple() {
  168. $this->insertCardData('fullname1', 'uri1');
  169. $this->insertCardData('fullname2', 'uri2');
  170. // The WHERE clause matches two rows. One row contains a value that
  171. // needs to be updated, the other one already contains the value we are
  172. // updating to. MySQL reports 1 here when the PDO::MYSQL_ATTR_FOUND_ROWS
  173. // flag is not specified.
  174. $query = OC_DB::prepare("UPDATE `*PREFIX*{$this->table2}` SET `uri` = ?");
  175. $this->assertSame(2, $query->execute(array('uri1')));
  176. }
  177. protected function insertCardData($fullname, $uri) {
  178. $query = OC_DB::prepare("INSERT INTO `*PREFIX*{$this->table2}` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)");
  179. $this->assertSame(1, $query->execute(array($fullname, $uri, $this->getUniqueID())));
  180. }
  181. protected function updateCardData($fullname, $uri) {
  182. $query = OC_DB::prepare("UPDATE `*PREFIX*{$this->table2}` SET `uri` = ? WHERE `fullname` = ?");
  183. return $query->execute(array($uri, $fullname));
  184. }
  185. public function testILIKE() {
  186. $table = "*PREFIX*{$this->table2}";
  187. $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)");
  188. $query->execute(array('fooBAR', 'foo', 'bar'));
  189. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?");
  190. $result = $query->execute(array('foobar'));
  191. $this->assertCount(0, $result->fetchAll());
  192. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?");
  193. $result = $query->execute(array('foobar'));
  194. $this->assertCount(1, $result->fetchAll());
  195. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?");
  196. $result = $query->execute(array('foo'));
  197. $this->assertCount(0, $result->fetchAll());
  198. }
  199. public function testILIKEWildcard() {
  200. $table = "*PREFIX*{$this->table2}";
  201. $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)");
  202. $query->execute(array('FooBAR', 'foo', 'bar'));
  203. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?");
  204. $result = $query->execute(array('%bar'));
  205. $this->assertCount(0, $result->fetchAll());
  206. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?");
  207. $result = $query->execute(array('foo%'));
  208. $this->assertCount(0, $result->fetchAll());
  209. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?");
  210. $result = $query->execute(array('%ba%'));
  211. $this->assertCount(0, $result->fetchAll());
  212. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?");
  213. $result = $query->execute(array('%bar'));
  214. $this->assertCount(1, $result->fetchAll());
  215. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?");
  216. $result = $query->execute(array('foo%'));
  217. $this->assertCount(1, $result->fetchAll());
  218. $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?");
  219. $result = $query->execute(array('%ba%'));
  220. $this->assertCount(1, $result->fetchAll());
  221. }
  222. /**
  223. * @dataProvider insertAndSelectDataProvider
  224. */
  225. public function testInsertAndSelectData($expected, $throwsOnMysqlWithoutUTF8MB4) {
  226. $table = "*PREFIX*{$this->text_table}";
  227. $config = \OC::$server->getConfig();
  228. $query = OC_DB::prepare("INSERT INTO `$table` (`textfield`) VALUES (?)");
  229. if ($throwsOnMysqlWithoutUTF8MB4 && $config->getSystemValue('dbtype', 'sqlite') === 'mysql' && $config->getSystemValue('mysql.utf8mb4', false) === false) {
  230. $this->markTestSkipped('MySQL requires UTF8mb4 to store value: ' . $expected);
  231. }
  232. $result = $query->execute(array($expected));
  233. $this->assertEquals(1, $result);
  234. $actual = OC_DB::prepare("SELECT `textfield` FROM `$table`")->execute()->fetchOne();
  235. $this->assertSame($expected, $actual);
  236. }
  237. public function insertAndSelectDataProvider() {
  238. return [
  239. ['abcdefghijklmnopqrstuvwxyzABCDEFGHIKLMNOPQRSTUVWXYZ', false],
  240. ['0123456789', false],
  241. ['äöüÄÖÜß!"§$%&/()=?#\'+*~°^`´', false],
  242. ['²³¼½¬{[]}\\', false],
  243. ['♡⚗', false],
  244. ['💩', true], # :hankey: on github
  245. ];
  246. }
  247. }