Completed
Push — develop ( 8bd504...58ca2f )
by Freddie
09:29
created

BuilderTest::testItCreateMySQLDatabaseWithUse()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 12
Code Lines 7

Duplication

Lines 0
Ratio 0 %

Importance

Changes 1
Bugs 0 Features 0
Metric Value
eloc 7
c 1
b 0
f 0
dl 0
loc 12
rs 10
cc 1
nc 1
nop 0
1
<?php declare(strict_types=1);
2
/*
3
 * This file is part of FlexPHP.
4
 *
5
 * (c) Freddie Gar <[email protected]>
6
 *
7
 * For the full copyright and license information, please view the LICENSE
8
 * file that was distributed with this source code.
9
 */
10
namespace FlexPHP\Database\Tests\Unit;
11
12
use FlexPHP\Database\Builder;
13
use FlexPHP\Database\Exception\DatabaseValidationException;
14
use FlexPHP\Database\Tests\TestCase;
15
use FlexPHP\Schema\Schema;
16
use FlexPHP\Schema\SchemaAttribute;
17
use FlexPHP\Schema\SchemaInterface;
18
19
class BuilderTest extends TestCase
20
{
21
    public function testItPlatformErrorThrowException(): void
22
    {
23
        $this->expectException(DatabaseValidationException::class);
24
        $this->expectExceptionMessage('try: MySQL, SQLSrv');
25
26
        new Builder('Unknow');
27
    }
28
29
    public function testItDatabaseNameErrorThrowException(): void
30
    {
31
        $this->expectException(DatabaseValidationException::class);
32
        $this->expectExceptionMessage('Database name [_db] invalid');
33
34
        $builder = new Builder('MySQL');
35
        $builder->createDatabase('_db');
36
    }
37
38
    public function testItCreateMySQLDatabase(): void
39
    {
40
        $name = 'db';
41
42
        $builder = new Builder('MySQL');
43
        $builder->createDatabase($name);
44
        $this->assertEquals(<<<T
45
CREATE DATABASE $name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
46
T
47
, $builder->toSql());
48
    }
49
50
    public function testItCreateSQLSrvDatabase(): void
51
    {
52
        $name = 'db';
53
54
        $builder = new Builder('SQLSrv');
55
        $builder->createDatabase($name);
56
        $this->assertEquals(<<<T
57
CREATE DATABASE $name COLLATE latin1_general_100_ci_ai_sc;
58
T
59
, $builder->toSql());
60
    }
61
62
    public function testItCreateMySQLDatabaseWithUse(): void
63
    {
64
        $name = 'db';
65
66
        $builder = new Builder('MySQL');
67
        $builder->createDatabaseWithUse($name);
68
        $this->assertEquals(<<<T
69
CREATE DATABASE $name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
70
71
USE $name;
72
T
73
, $builder->toSql());
74
    }
75
76
    public function testItCreateSQLSrvDatabaseWithUse(): void
77
    {
78
        $name = 'db';
79
80
        $builder = new Builder('SQLSrv');
81
        $builder->createDatabaseWithUse($name);
82
        $this->assertEquals(<<<T
83
CREATE DATABASE $name COLLATE latin1_general_100_ci_ai_sc;
84
T
85
, $builder->toSql());
86
    }
87
88
    public function testItCreateMySQLUser(): void
89
    {
90
        $name = 'mysql';
91
        $password = 'p4sw00rd';
92
93
        $builder = new Builder('MySQL');
94
        $builder->createUser($name, $password);
95
        $this->assertEquals(<<<T
96
CREATE USER '$name'@'%' IDENTIFIED BY '$password';
97
T
98
, $builder->toSql());
99
    }
100
101
    public function testItCreateSQLSrvUser(): void
102
    {
103
        $name = 'sqlsrv';
104
        $password = 'p4sw00rd';
105
106
        $builder = new Builder('SQLSrv');
107
        $builder->createUser($name, $password);
108
        $this->assertEquals(<<<T
109
CREATE LOGIN $name WITH PASSWORD = '$password';
110
GO
111
CREATE USER $name FOR LOGIN $name;
112
GO
113
T
114
, $builder->toSql());
115
    }
116
117
    public function testItCreateMySQLUserWithGrants(): void
118
    {
119
        $name = 'mysql';
120
        $password = 'p4sw00rd';
121
122
        $builder = new Builder('MySQL');
123
        $builder->createUser($name, $password, 'host', ['ALL PRIVILEGES']);
124
        $this->assertEquals(<<<T
125
CREATE USER '$name'@'host' IDENTIFIED BY '$password';
126
127
GRANT ALL PRIVILEGES ON *.* TO '$name'@'host';
128
T
129
, $builder->toSql());
130
    }
131
132
    public function testItCreateSQLSrvUserWithGrants(): void
133
    {
134
        $name = 'sqlsrv';
135
        $password = 'p4sw00rd';
136
137
        $builder = new Builder('SQLSrv');
138
        $builder->createUser($name, $password, 'host', ['ALL PRIVILEGES']);
139
        $this->assertEquals(<<<T
140
CREATE LOGIN $name WITH PASSWORD = '$password';
141
GO
142
CREATE USER $name FOR LOGIN $name;
143
GO
144
145
GRANT ALL TO sqlsrv;
146
GO
147
T
148
, $builder->toSql());
149
    }
150
151
    public function testItCreateMySQLTable(): void
152
    {
153
        $builder = new Builder('MySQL');
154
        $builder->createTable($this->getSchema());
155
        $this->assertEquals(<<<T
156
CREATE TABLE bar (
157
    foo INT DEFAULT NULL COMMENT 'foo'
158
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
159
T
160
, $builder->toSql());
161
    }
162
163
    public function testItCreateSQLSrvTable(): void
164
    {
165
        $builder = new Builder('SQLSrv');
166
        $builder->createTable($this->getSchema());
167
        $this->assertEquals(<<<T
168
CREATE TABLE bar (
169
    foo INT
170
);
171
172
EXEC sp_addextendedproperty N'MS_Description', N'foo', N'SCHEMA', 'dbo', N'TABLE', 'bar', N'COLUMN', foo;
173
T
174
, $builder->toSql());
175
    }
176
177
    public function testItCreateMySQLComplete(): void
178
    {
179
        $dbname = 'complete';
180
        $username = 'username';
181
        $password = 'password';
182
        $host = 'host';
183
        $schema = new Schema('bar', 'title', [
184
            new SchemaAttribute('Pk', 'string', 'pk|required'),
185
            new SchemaAttribute('foo', 'string', 'minlength:10|maxlength:100'),
186
            new SchemaAttribute('bar', 'integer', 'min:10|max'),
187
        ]);
188
189
        $schemaFk = new Schema('fuz', 'title', [
190
            new SchemaAttribute('Pk', 'integer', 'pk|ai|required'),
191
            new SchemaAttribute('barId', 'integer', 'fk:bar'),
192
        ]);
193
194
        $builder = new Builder('MySQL');
195
        $builder->createDatabaseWithUse($dbname);
196
        $builder->createUser($username, $password, $host, ['ALL PRIVILEGES'], $dbname);
197
        $builder->createTable($schema);
198
        $builder->createTable($schemaFk);
199
200
        $this->assertEquals(<<<T
201
CREATE DATABASE $dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
202
203
USE $dbname;
204
205
CREATE USER '$username'@'$host' IDENTIFIED BY '$password';
206
207
GRANT ALL PRIVILEGES ON $dbname.* TO '$username'@'$host';
208
209
CREATE TABLE bar (
210
    Pk VARCHAR(255) NOT NULL COMMENT 'Pk',
211
    foo VARCHAR(100) DEFAULT NULL COMMENT 'foo',
212
    bar INT DEFAULT NULL COMMENT 'bar',
213
    PRIMARY KEY(Pk)
214
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
215
216
CREATE TABLE fuz (
217
    Pk INT AUTO_INCREMENT NOT NULL COMMENT 'Pk',
218
    barId INT DEFAULT NULL COMMENT 'barId',
219
    INDEX IDX_51837B119A5BAE65 (barId),
220
    PRIMARY KEY(Pk)
221
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
222
223
ALTER TABLE fuz ADD CONSTRAINT FK_51837B119A5BAE65 FOREIGN KEY (barId) REFERENCES bar (id);
224
T
225
, $builder->toSql());
226
    }
227
228
    public function testItConstraints(): void
229
    {
230
        $dbname = 'complete';
231
        $username = 'username';
232
        $password = 'password';
233
        $host = 'host';
234
        $schema = new Schema('bar', 'title', [
235
            new SchemaAttribute('Pk', 'string', 'pk|required'),
236
            new SchemaAttribute('foo', 'string', 'minlength:10|maxlength:100'),
237
            new SchemaAttribute('bar', 'integer', 'min:10|max'),
238
        ]);
239
240
        $schemaFk = new Schema('fuz', 'title', [
241
            new SchemaAttribute('Pk', 'integer', 'pk|ai|required'),
242
            new SchemaAttribute('barId', 'integer', 'fk:bar'),
243
        ]);
244
245
        $schemaFk2 = new Schema('baz', 'title', [
246
            new SchemaAttribute('Pk', 'integer', 'pk|ai|required'),
247
            new SchemaAttribute('fuzId', 'integer', 'fk:fuz'),
248
            new SchemaAttribute('barId', 'integer', 'fk:bar'),
249
        ]);
250
251
        $builder = new Builder('MySQL');
252
        $builder->createDatabase($dbname);
253
        $builder->createUser($username, $password, $host);
254
        $builder->createTable($schema);
255
        $builder->createTable($schemaFk);
256
        $builder->createTable($schemaFk2);
257
258
        $this->assertEquals(<<<T
259
CREATE DATABASE $dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
260
261
CREATE USER '$username'@'$host' IDENTIFIED BY '$password';
262
263
CREATE TABLE bar (
264
    Pk VARCHAR(255) NOT NULL COMMENT 'Pk',
265
    foo VARCHAR(100) DEFAULT NULL COMMENT 'foo',
266
    bar INT DEFAULT NULL COMMENT 'bar',
267
    PRIMARY KEY(Pk)
268
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
269
270
CREATE TABLE fuz (
271
    Pk INT AUTO_INCREMENT NOT NULL COMMENT 'Pk',
272
    barId INT DEFAULT NULL COMMENT 'barId',
273
    INDEX IDX_51837B119A5BAE65 (barId),
274
    PRIMARY KEY(Pk)
275
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
276
277
CREATE TABLE baz (
278
    Pk INT AUTO_INCREMENT NOT NULL COMMENT 'Pk',
279
    fuzId INT DEFAULT NULL COMMENT 'fuzId',
280
    barId INT DEFAULT NULL COMMENT 'barId',
281
    INDEX IDX_78240498BEB399D5 (fuzId),
282
    INDEX IDX_782404989A5BAE65 (barId),
283
    PRIMARY KEY(Pk)
284
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
285
286
ALTER TABLE fuz ADD CONSTRAINT FK_51837B119A5BAE65 FOREIGN KEY (barId) REFERENCES bar (id);
287
288
ALTER TABLE baz ADD CONSTRAINT FK_78240498BEB399D5 FOREIGN KEY (fuzId) REFERENCES fuz (id);
289
290
ALTER TABLE baz ADD CONSTRAINT FK_782404989A5BAE65 FOREIGN KEY (barId) REFERENCES bar (id);
291
T
292
, $builder->toSql());
293
    }
294
295
    public function testItConstraintsFkErrorThrowException(): void
296
    {
297
        $this->expectException(DatabaseValidationException::class);
298
        $this->expectExceptionMessage('Tables in foreign');
299
300
        $schema = new Schema('bar', 'title', [
301
            new SchemaAttribute('Pk', 'string', 'pk|required'),
302
            new SchemaAttribute('foo', 'string', 'minlength:10|maxlength:100'),
303
            new SchemaAttribute('bar', 'integer', 'min:10|max'),
304
        ]);
305
306
        $schemaFk = new Schema('fuz', 'title', [
307
            new SchemaAttribute('Pk', 'integer', 'pk|ai|required'),
308
            new SchemaAttribute('barId', 'integer', 'fk:notexist'),
309
        ]);
310
311
        $builder = new Builder('MySQL');
312
        $builder->createTable($schema);
313
        $builder->createTable($schemaFk);
314
315
        $builder->toSql();
316
    }
317
318
    public function getSchema(): SchemaInterface
319
    {
320
        return new Schema('bar', 'title', [
321
            new SchemaAttribute('foo', 'integer', [
322
                'min' => 10,
323
                'max' => 100,
324
            ]),
325
        ]);
326
    }
327
}
328