4
0

DatabaseDAOTest.php 6.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166
  1. <?php
  2. declare(strict_types=1);
  3. use PHPUnit\Framework\Attributes\DataProvider;
  4. final class DatabaseDAOTest extends \PHPUnit\Framework\TestCase {
  5. public function test_tablesAreCorrect_withPostgreSQL(): void {
  6. $dsn = getenv('FRESHRSS_TEST_PGSQL_DSN');
  7. if ($dsn === false || $dsn === '') {
  8. self::markTestSkipped('FRESHRSS_TEST_PGSQL_DSN is not configured.');
  9. }
  10. // A gateway login need not match the PostgreSQL role that owns the tables:
  11. // the DAO relies on grants to the visible tables, not on its own ownership.
  12. $pdo = new Minz_PdoPgsql($dsn, null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
  13. $pdo->setPrefix('test_');
  14. $pdo->beginTransaction();
  15. try {
  16. $pdo->exec(<<<'SQL'
  17. CREATE ROLE freshrss_test_app NOLOGIN;
  18. CREATE SCHEMA freshrss_test_visible;
  19. CREATE SCHEMA freshrss_test_hidden;
  20. SET LOCAL search_path TO freshrss_test_visible;
  21. GRANT USAGE ON SCHEMA freshrss_test_visible, freshrss_test_hidden TO freshrss_test_app;
  22. SQL);
  23. $pdo->exec(<<<'SQL'
  24. CREATE TABLE test_category (id integer);
  25. CREATE TABLE test_feed (id integer);
  26. CREATE TABLE test_entry (id integer);
  27. CREATE TABLE test_entrytmp (id integer);
  28. CREATE TABLE test_tag (id integer);
  29. CREATE TABLE test_entrytag (id integer);
  30. CREATE TABLE another_users_feed (id integer);
  31. GRANT SELECT ON ALL TABLES IN SCHEMA freshrss_test_visible TO freshrss_test_app;
  32. SET LOCAL ROLE freshrss_test_app;
  33. SQL);
  34. $dao = new FreshRSS_DatabaseDAOPGSQL('test', $pdo);
  35. self::assertTrue($dao->tablesAreCorrect(), 'Visible tables may belong to another role.');
  36. $pdo->exec(<<<'SQL'
  37. RESET ROLE;
  38. ALTER TABLE test_feed SET SCHEMA freshrss_test_hidden;
  39. SET LOCAL ROLE freshrss_test_app;
  40. SQL);
  41. self::assertFalse($dao->tablesAreCorrect(), 'A table outside search_path must not hide a missing table.');
  42. $pdo->exec(<<<'SQL'
  43. RESET ROLE;
  44. CREATE VIEW test_feed AS SELECT * FROM freshrss_test_hidden.test_feed;
  45. SET LOCAL ROLE freshrss_test_app;
  46. SQL);
  47. self::assertFalse($dao->tablesAreCorrect(), 'A view must not count as a table.');
  48. } finally {
  49. $pdo->rollBack();
  50. }
  51. }
  52. /** @return list<array{string,string,bool,bool}> */
  53. public static function provideStrilikeCommon(): array {
  54. return [
  55. ['abc', 'abc', false, true],
  56. ['AbC', 'aBc', false, true],
  57. ['zabc', 'abc', false, false],
  58. ['abcd', 'abc', false, false],
  59. ['aéc', 'ac', false, false],
  60. ['abcd', 'bc', true, true],
  61. ['abcd', 'BC', true, true],
  62. ['aßc', 'ß', true, true],
  63. ['aéc', 'é', true, true],
  64. ['Été', 'Ét', true, true],
  65. ['aßc', 'ac', true, false],
  66. ['ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz', false, true],
  67. ['abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', true, true],
  68. ];
  69. }
  70. /** @return list<array{string,string,bool,bool}> */
  71. public static function provideStrilikeAccents(): array {
  72. return [
  73. ['café', 'cafè', false, false],
  74. ['Été', 'Eté', false, false],
  75. ['Été', 'Et', true, false],
  76. ];
  77. }
  78. /** @return list<array{string,string,bool,bool}> */
  79. public static function provideStrilikeNoAccents(): array {
  80. return [
  81. ['café', 'cafè', false, true],
  82. ['Été', 'Eté', false, true],
  83. ['Été', 'Et', true, true],
  84. ];
  85. }
  86. /** @return list<array{string,string,bool,bool}> */
  87. public static function provideStrilikeAccentsCasing(): array {
  88. return [
  89. ['Été', 'été', false, true],
  90. ['AÎNÉE', 'aîné', true, true],
  91. ['AÎNÉ', 'aine', false, false],
  92. ['AÎNÉE', 'aine', true, false],
  93. ];
  94. }
  95. /** @return list<array{string,string,bool,bool}> */
  96. public static function provideStrilikeUnicodeCasing(): array {
  97. return [
  98. ['ČĆĐŠŽ', 'čćđšž', false, true], // Croatian
  99. ['ÁČĎÉĚÍŇÓŘŠŤÚŮÝŽ', 'áčďéěíňóřšťúůýž', false, true], // Czech
  100. ['ÆØÅ', 'æøå', false, true], // Danish
  101. ['ŠŽÕÄÖÜ', 'šžõäöü', false, true], // Estonian
  102. ['ÄÖ', 'äö', false, true], // Finnish
  103. ['ÀÂÆÇÈÉÊËÎÏÔŒÙÛÜŸ', 'àâæçèéêëîïôœùûüÿ', false, true], // French
  104. ['ÄÖÜ', 'äöü', false, true], // German
  105. ['ΑΆΒΓΔΕΈΖΗΉΘΙΊΪΚΛΜΝΞΟΌΠΡΣΤΥΎΫΦΧΨΩΏ', 'αάβγδεέζηήθιίϊκλμνξοόπρστυύϋφχψωώ', false, true], // Greek
  106. ['ÁÉÍÓÖŐÚÜŰ', 'áéíóöőúüű', false, true], // Hungarian
  107. ['ÁÉÍÓÚ', 'áéíóú', false, true], // Irish
  108. ['ÀÈÉÌÒÓÙ', 'àèéìòóù', false, true], // Italian
  109. ['ĀČĒĢĪĶĻŅŠŪŽ', 'āčēģīķļņšūž', false, true], // Latvian
  110. ['ĄČĘĖĮŠŲŪŽ', 'ąčęėįšųūž', false, true], // Lithuanian
  111. ['ĊĠĦŻ', 'ċġħż', false, true], // Maltese
  112. ['ĄĆĘŁŃÓŚŹŻ', 'ąćęłńóśźż', false, true], // Polish
  113. ['ÁÂÃÇÉÍÓÕÚ', 'áâãçéíóõú', false, true], // Portuguese
  114. ['ĂÂÎȘȚ', 'ăâîșț', false, true], // Romanian
  115. ['ÁÄČĎÉÍĹĽŇÓÔŔŠŤÚÝŽ', 'áäčďéíĺľňóôŕšťúýž', false, true], // Slovak
  116. ['ČŠŽ', 'čšž', false, true], // Slovenian
  117. ['ÁÉÍÑÓÚÜ', 'áéíñóúü', false, true], // Spanish
  118. ['ÅÄÖ', 'åäö', false, true], // Swedish
  119. ];
  120. }
  121. /** @return list<array{string,string,bool,bool}> */
  122. public static function provideStrilikeNoUnicodeCasing(): array {
  123. return [
  124. ['café', 'cafè', false, false],
  125. ['café', 'Café', true, true],
  126. ['Été', 'été', true, false],
  127. ];
  128. }
  129. #[DataProvider('provideStrilikeCommon')]
  130. #[DataProvider('provideStrilikeNoAccents')]
  131. #[DataProvider('provideStrilikeUnicodeCasing')]
  132. public static function test_strilike_MySQL(string $haystack, string $needle, bool $contains, bool $expected): void {
  133. if (!function_exists('transliterator_transliterate') && str_contains($haystack, 'α')) {
  134. self::markTestSkipped('transliterator_transliterate function not available to handle e.g. Greek.');
  135. return; // @phpstan-ignore deadCode.unreachable
  136. }
  137. self::assertSame($expected, FreshRSS_DatabaseDAO::strilike($haystack, $needle, $contains));
  138. }
  139. #[DataProvider('provideStrilikeCommon')]
  140. #[DataProvider('provideStrilikeAccents')]
  141. #[DataProvider('provideStrilikeAccentsCasing')]
  142. #[DataProvider('provideStrilikeUnicodeCasing')]
  143. public static function test_strilike_PGSQL(string $haystack, string $needle, bool $contains, bool $expected): void {
  144. self::assertSame($expected, FreshRSS_DatabaseDAOPGSQL::strilike($haystack, $needle, $contains));
  145. }
  146. #[DataProvider('provideStrilikeCommon')]
  147. #[DataProvider('provideStrilikeAccents')]
  148. #[DataProvider('provideStrilikeNoUnicodeCasing')]
  149. public static function test_strilike_SQLite(string $haystack, string $needle, bool $contains, bool $expected): void {
  150. self::assertSame($expected, FreshRSS_DatabaseDAOSQLite::strilike($haystack, $needle, $contains));
  151. }
  152. }