1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* DBSetup.php |
4
|
|
|
* |
5
|
|
|
* -Description- |
6
|
|
|
* |
7
|
|
|
* This program is free software: you can redistribute it and/or modify |
8
|
|
|
* it under the terms of the GNU General Public License as published by |
9
|
|
|
* the Free Software Foundation, either version 3 of the License, or |
10
|
|
|
* (at your option) any later version. |
11
|
|
|
* |
12
|
|
|
* This program is distributed in the hope that it will be useful, |
13
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
14
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the |
15
|
|
|
* GNU General Public License for more details. |
16
|
|
|
* |
17
|
|
|
* You should have received a copy of the GNU General Public License |
18
|
|
|
* along with this program. If not, see <https://www.gnu.org/licenses/>. |
19
|
|
|
* |
20
|
|
|
* @link https://www.librenms.org |
21
|
|
|
* |
22
|
|
|
* @copyright 2017 Neil Lathwood |
23
|
|
|
* @author Neil Lathwood <[email protected]> |
24
|
|
|
*/ |
25
|
|
|
|
26
|
|
|
namespace LibreNMS\Tests; |
27
|
|
|
|
28
|
|
|
use Artisan; |
29
|
|
|
use DB; |
30
|
|
|
use LibreNMS\DB\Schema; |
31
|
|
|
|
32
|
|
|
class DBSetupTest extends DBTestCase |
33
|
|
|
{ |
34
|
|
|
protected $db_name; |
35
|
|
|
protected $connection = 'testing'; |
36
|
|
|
|
37
|
|
|
protected function setUp(): void |
38
|
|
|
{ |
39
|
|
|
parent::setUp(); |
40
|
|
|
$this->db_name = DB::connection($this->connection)->getDatabaseName(); |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
public function testSetupDB() |
44
|
|
|
{ |
45
|
|
|
$result = Artisan::call('migrate:fresh', [ |
46
|
|
|
'--seed' => true, |
47
|
|
|
'--env' => 'testing', |
48
|
|
|
'--database' => $this->connection, |
49
|
|
|
]); |
50
|
|
|
|
51
|
|
|
$this->assertSame(0, $result, 'Errors loading DB Schema: ' . Artisan::output()); |
52
|
|
|
} |
53
|
|
|
|
54
|
|
|
public function testSchemaFiles() |
55
|
|
|
{ |
56
|
|
|
$files = glob(base_path('/sql-schema/*.sql')); |
57
|
|
|
$this->assertCount(282, $files, 'You should not create new legacy schema files.'); |
58
|
|
|
} |
59
|
|
|
|
60
|
|
|
public function testSchema() |
61
|
|
|
{ |
62
|
|
|
$files = array_map(function ($migration_file) { |
63
|
|
|
return basename($migration_file, '.php'); |
64
|
|
|
}, array_diff(scandir(base_path('/database/migrations')), ['.', '..', '.gitkeep'])); |
65
|
|
|
$migrated = DB::connection($this->connection)->table('migrations')->pluck('migration')->toArray(); |
66
|
|
|
sort($files); |
67
|
|
|
sort($migrated); |
68
|
|
|
$this->assertEquals($files, $migrated, 'List of run migrations did not match existing migration files.'); |
69
|
|
|
|
70
|
|
|
// check legacy schema version is 1000 |
71
|
|
|
$schema = DB::connection($this->connection)->table('dbSchema') |
72
|
|
|
->orderBy('version', 'DESC') |
73
|
|
|
->value('version'); |
74
|
|
|
$this->assertEquals(1000, $schema, 'Seed not run, after seed legacy dbSchema should be 1000'); |
75
|
|
|
} |
76
|
|
|
|
77
|
|
|
public function testCheckDBCollation() |
78
|
|
|
{ |
79
|
|
|
$collation = DB::connection($this->connection)->select(DB::raw("SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S WHERE schema_name = '$this->db_name' AND ( DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')")); |
80
|
|
|
if (isset($collation[0])) { |
81
|
|
|
$error = implode(' ', (array) $collation[0]); |
82
|
|
|
} else { |
83
|
|
|
$error = ''; |
84
|
|
|
} |
85
|
|
|
$this->assertEmpty($collation, 'Wrong Database Collation or Character set: ' . $error); |
86
|
|
|
} |
87
|
|
|
|
88
|
|
|
public function testCheckTableCollation() |
89
|
|
|
{ |
90
|
|
|
$collation = DB::connection($this->connection)->select(DB::raw("SELECT T.TABLE_NAME, C.CHARACTER_SET_NAME, C.COLLATION_NAME FROM information_schema.TABLES AS T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS C WHERE C.collation_name = T.table_collation AND T.table_schema = '$this->db_name' AND ( C.CHARACTER_SET_NAME != 'utf8mb4' OR C.COLLATION_NAME != 'utf8mb4_unicode_ci' );")); |
91
|
|
|
$error = ''; |
92
|
|
|
foreach ($collation as $data) { |
93
|
|
|
$error .= implode(' ', (array) $data) . PHP_EOL; |
94
|
|
|
} |
95
|
|
|
$this->assertEmpty($collation, 'Wrong Table Collation or Character set: ' . $error); |
96
|
|
|
} |
97
|
|
|
|
98
|
|
|
public function testCheckColumnCollation() |
99
|
|
|
{ |
100
|
|
|
$collation = DB::connection($this->connection)->select(DB::raw("SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$this->db_name' AND ( CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_unicode_ci' );")); |
101
|
|
|
$error = ''; |
102
|
|
|
foreach ($collation as $data) { |
103
|
|
|
$error .= implode(' ', (array) $data) . PHP_EOL; |
104
|
|
|
} |
105
|
|
|
$this->assertEmpty($collation, 'Wrong Column Collation or Character set: ' . $error); |
106
|
|
|
} |
107
|
|
|
|
108
|
|
|
public function testSqlMode() |
109
|
|
|
{ |
110
|
|
|
$result = DB::connection($this->connection)->selectOne(DB::raw('SELECT @@version AS version, @@sql_mode AS mode')); |
111
|
|
|
preg_match('/([0-9.]+)(?:-(\w+))?/', $result->version, $matches); |
112
|
|
|
$version = $matches[1] ?? null; |
113
|
|
|
$vendor = $matches[2] ?? null; |
114
|
|
|
$mode = $result->mode; |
115
|
|
|
|
116
|
|
|
// NO_AUTO_CREATE_USER is removed in mysql 8 |
117
|
|
|
$expected = ($vendor !== 'MariaDB' && version_compare($version, '8.0.0') >= 0) |
|
|
|
|
118
|
|
|
? 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' |
119
|
|
|
: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; |
120
|
|
|
|
121
|
|
|
$this->assertEquals($expected, $mode); |
122
|
|
|
} |
123
|
|
|
|
124
|
|
|
public function testValidateSchema() |
125
|
|
|
{ |
126
|
|
|
if (is_file('misc/db_schema.yaml')) { |
127
|
|
|
DB::connection($this->connection)->statement('SET time_zone = "+00:00";'); |
128
|
|
|
|
129
|
|
|
$master_schema = \Symfony\Component\Yaml\Yaml::parse( |
130
|
|
|
file_get_contents('misc/db_schema.yaml') |
131
|
|
|
); |
132
|
|
|
|
133
|
|
|
$current_schema = Schema::dump($this->connection); |
134
|
|
|
|
135
|
|
|
$message = "Schema does not match the expected schema defined by misc/db_schema.yaml\n"; |
136
|
|
|
$message .= "If you have changed the schema, make sure you update it with: lnms schema:dump\n"; |
137
|
|
|
|
138
|
|
|
$this->assertEquals($master_schema, $current_schema, $message); |
139
|
|
|
} |
140
|
|
|
} |
141
|
|
|
} |
142
|
|
|
|