1
|
|
|
<?php |
2
|
|
|
declare(strict_types=1); |
3
|
|
|
|
4
|
|
|
/* |
5
|
|
|
Copyright (C) 2006-2014 David Négrier - THE CODING MACHINE |
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 2 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, write to the Free Software |
19
|
|
|
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA |
20
|
|
|
*/ |
21
|
|
|
|
22
|
|
|
namespace TheCodingMachine\TDBM; |
23
|
|
|
|
24
|
|
|
use DateTime; |
25
|
|
|
use DateTimeImmutable; |
26
|
|
|
use Doctrine\Common\Cache\ArrayCache; |
27
|
|
|
use Doctrine\Common\EventManager; |
28
|
|
|
use Doctrine\DBAL\Connection; |
29
|
|
|
use Doctrine\DBAL\DriverManager; |
30
|
|
|
use Doctrine\DBAL\Event\Listeners\OracleSessionInit; |
31
|
|
|
use Doctrine\DBAL\Platforms\MySqlPlatform; |
32
|
|
|
use Doctrine\DBAL\Platforms\OraclePlatform; |
33
|
|
|
use Doctrine\DBAL\Types\Type; |
34
|
|
|
use PHPUnit\Framework\TestCase; |
35
|
|
|
use TheCodingMachine\FluidSchema\FluidSchema; |
36
|
|
|
use TheCodingMachine\FluidSchema\TdbmFluidSchema; |
37
|
|
|
use TheCodingMachine\TDBM\Fixtures\Interfaces\TestUserDaoInterface; |
38
|
|
|
use TheCodingMachine\TDBM\Fixtures\Interfaces\TestUserInterface; |
39
|
|
|
use TheCodingMachine\TDBM\Fixtures\Traits\TestOtherUserTrait; |
40
|
|
|
use TheCodingMachine\TDBM\Fixtures\Traits\TestUserDaoTrait; |
41
|
|
|
use TheCodingMachine\TDBM\Fixtures\Traits\TestUserTrait; |
42
|
|
|
use TheCodingMachine\TDBM\Utils\Annotation\AnnotationParser; |
43
|
|
|
use TheCodingMachine\TDBM\Utils\Annotation\AddInterface; |
44
|
|
|
use TheCodingMachine\TDBM\Utils\DefaultNamingStrategy; |
45
|
|
|
use TheCodingMachine\TDBM\Utils\PathFinder\PathFinder; |
46
|
|
|
use function stripos; |
47
|
|
|
use const PHP_EOL; |
48
|
|
|
|
49
|
|
|
abstract class TDBMAbstractServiceTest extends TestCase |
50
|
|
|
{ |
51
|
|
|
/** |
52
|
|
|
* @var Connection |
53
|
|
|
*/ |
54
|
|
|
protected static $dbConnection; |
55
|
|
|
|
56
|
|
|
/** |
57
|
|
|
* @var TDBMService |
58
|
|
|
*/ |
59
|
|
|
protected $tdbmService; |
60
|
|
|
|
61
|
|
|
/** |
62
|
|
|
* @var DummyGeneratorListener |
63
|
|
|
*/ |
64
|
|
|
private $dummyGeneratorListener; |
65
|
|
|
|
66
|
|
|
/** |
67
|
|
|
* @var ConfigurationInterface |
68
|
|
|
*/ |
69
|
|
|
private $configuration; |
70
|
|
|
|
71
|
|
|
/** |
72
|
|
|
* @var ArrayCache |
73
|
|
|
*/ |
74
|
|
|
private static $cache; |
75
|
|
|
|
76
|
|
|
public static function setUpBeforeClass(): void |
77
|
|
|
{ |
78
|
|
|
self::resetConnection(); |
79
|
|
|
|
80
|
|
|
self::$cache = new ArrayCache(); |
81
|
|
|
|
82
|
|
|
$dbConnection = ConnectionFactory::resetDatabase( |
83
|
|
|
$GLOBALS['db_driver'], |
84
|
|
|
$GLOBALS['db_host'] ?? null, |
85
|
|
|
$GLOBALS['db_port'] ?? null, |
86
|
|
|
$GLOBALS['db_username'] ?? null, |
87
|
|
|
$GLOBALS['db_admin_username'] ?? null, |
88
|
|
|
$GLOBALS['db_password'] ?? null, |
89
|
|
|
$GLOBALS['db_name'] ?? null |
90
|
|
|
); |
91
|
|
|
|
92
|
|
|
self::$dbConnection = $dbConnection; |
93
|
|
|
|
94
|
|
|
self::initSchema($dbConnection); |
95
|
|
|
} |
96
|
|
|
|
97
|
|
|
private static function resetConnection(): void |
98
|
|
|
{ |
99
|
|
|
if (self::$dbConnection !== null) { |
100
|
|
|
self::$dbConnection->close(); |
101
|
|
|
} |
102
|
|
|
self::$dbConnection = null; |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
protected static function getConnection(): Connection |
106
|
|
|
{ |
107
|
|
|
if (self::$dbConnection === null) { |
108
|
|
|
self::$dbConnection = ConnectionFactory::createConnection( |
109
|
|
|
$GLOBALS['db_driver'], |
110
|
|
|
$GLOBALS['db_host'] ?? null, |
111
|
|
|
$GLOBALS['db_port'] ?? null, |
112
|
|
|
$GLOBALS['db_username'] ?? null, |
113
|
|
|
$GLOBALS['db_password'] ?? null, |
114
|
|
|
$GLOBALS['db_name'] ?? null |
115
|
|
|
); |
116
|
|
|
} |
117
|
|
|
return self::$dbConnection; |
118
|
|
|
} |
119
|
|
|
|
120
|
|
|
protected function onlyMySql() |
121
|
|
|
{ |
122
|
|
|
if (!self::getConnection()->getDatabasePlatform() instanceof MySqlPlatform) { |
123
|
|
|
$this->markTestSkipped('MySQL specific test'); |
124
|
|
|
} |
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
protected function setUp(): void |
128
|
|
|
{ |
129
|
|
|
$this->tdbmService = new TDBMService($this->getConfiguration()); |
130
|
|
|
} |
131
|
|
|
|
132
|
|
|
protected function getDummyGeneratorListener() : DummyGeneratorListener |
133
|
|
|
{ |
134
|
|
|
if ($this->dummyGeneratorListener === null) { |
135
|
|
|
$this->dummyGeneratorListener = new DummyGeneratorListener(); |
136
|
|
|
} |
137
|
|
|
return $this->dummyGeneratorListener; |
138
|
|
|
} |
139
|
|
|
|
140
|
|
|
protected function getCache(): ArrayCache |
141
|
|
|
{ |
142
|
|
|
return self::$cache; |
143
|
|
|
} |
144
|
|
|
|
145
|
|
|
protected function getConfiguration() : ConfigurationInterface |
146
|
|
|
{ |
147
|
|
|
if ($this->configuration === null) { |
148
|
|
|
$this->configuration = new Configuration('TheCodingMachine\\TDBM\\Test\\Dao\\Bean', 'TheCodingMachine\\TDBM\\Test\\Dao', self::getConnection(), $this->getNamingStrategy(), $this->getCache(), null, null, [$this->getDummyGeneratorListener()]); |
149
|
|
|
$this->configuration->setPathFinder(new PathFinder(null, dirname(__DIR__, 4))); |
150
|
|
|
} |
151
|
|
|
return $this->configuration; |
152
|
|
|
} |
153
|
|
|
|
154
|
|
|
protected function getNamingStrategy() |
155
|
|
|
{ |
156
|
|
|
$strategy = new DefaultNamingStrategy(AnnotationParser::buildWithDefaultAnnotations([]), self::getConnection()->getSchemaManager()); |
157
|
|
|
$strategy->setBeanPrefix(''); |
158
|
|
|
$strategy->setBeanSuffix('Bean'); |
159
|
|
|
$strategy->setBaseBeanPrefix(''); |
160
|
|
|
$strategy->setBaseBeanSuffix('BaseBean'); |
161
|
|
|
$strategy->setDaoPrefix(''); |
162
|
|
|
$strategy->setDaoSuffix('Dao'); |
163
|
|
|
$strategy->setBaseDaoPrefix(''); |
164
|
|
|
$strategy->setBaseDaoSuffix('BaseDao'); |
165
|
|
|
|
166
|
|
|
return $strategy; |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
private static function initSchema(Connection $connection): void |
170
|
|
|
{ |
171
|
|
|
$fromSchema = $connection->getSchemaManager()->createSchema(); |
172
|
|
|
$toSchema = clone $fromSchema; |
173
|
|
|
|
174
|
|
|
$db = new TdbmFluidSchema($toSchema, new \TheCodingMachine\FluidSchema\DefaultNamingStrategy($connection->getDatabasePlatform())); |
175
|
|
|
|
176
|
|
|
$db->table('country') |
177
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
178
|
|
|
->column('label')->string(255)->unique(); |
179
|
|
|
|
180
|
|
|
$db->table('person') |
181
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
182
|
|
|
->column('name')->string(255); |
183
|
|
|
|
184
|
|
|
if ($connection->getDatabasePlatform() instanceof OraclePlatform) { |
185
|
|
|
$toSchema->getTable($connection->quoteIdentifier('person')) |
186
|
|
|
->addColumn( |
187
|
|
|
$connection->quoteIdentifier('created_at'), |
188
|
|
|
'datetime', |
189
|
|
|
['columnDefinition' => 'TIMESTAMP(0) DEFAULT SYSDATE NOT NULL'] |
190
|
|
|
); |
191
|
|
|
} else { |
192
|
|
|
$toSchema->getTable('person') |
193
|
|
|
->addColumn( |
194
|
|
|
$connection->quoteIdentifier('created_at'), |
195
|
|
|
'datetime', |
196
|
|
|
['columnDefinition' => 'timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP'] |
197
|
|
|
); |
198
|
|
|
} |
199
|
|
|
|
200
|
|
|
$db->table('person') |
201
|
|
|
->column('modified_at')->datetime()->null()->index() |
202
|
|
|
->column('order')->integer()->null(); |
203
|
|
|
|
204
|
|
|
|
205
|
|
|
$db->table('contact') |
206
|
|
|
->extends('person') |
207
|
|
|
->column('email')->string(255) |
208
|
|
|
->column('manager_id')->references('contact')->null(); |
209
|
|
|
|
210
|
|
|
$db->table('users') |
211
|
|
|
->addAnnotation('AddTrait', ['name'=>TestUserTrait::class], false) |
212
|
|
|
->addAnnotation('AddTrait', ['name'=>TestOtherUserTrait::class, 'modifiers'=>['\\'.TestOtherUserTrait::class.'::method1 insteadof \\'.TestUserTrait::class, '\\'.TestUserTrait::class.'::method1 as method1renamed']], false) |
213
|
|
|
->addAnnotation('AddTraitOnDao', ['name'=>TestUserDaoTrait::class], false) |
214
|
|
|
->implementsInterface(TestUserInterface::class) |
215
|
|
|
->implementsInterfaceOnDao(TestUserDaoInterface::class) |
216
|
|
|
->extends('contact') |
217
|
|
|
->column('login')->string(255) |
218
|
|
|
->column('password')->string(255)->null() |
219
|
|
|
->column('status')->string(10)->null()->default(null) |
220
|
|
|
->column('country_id')->references('country') |
221
|
|
|
// Used to test generation for a column that starts with a digit |
222
|
|
|
->then()->column('3d_view')->boolean()->default(true); |
223
|
|
|
|
224
|
|
|
$db->table('rights') |
225
|
|
|
->column('label')->string(255)->primaryKey()->comment('Non autoincrementable primary key'); |
226
|
|
|
|
227
|
|
|
$db->table('roles') |
228
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
229
|
|
|
->column('name')->string(255) |
230
|
|
|
->column('created_at')->date()->null() |
231
|
|
|
->column('status')->boolean()->null()->default(1); |
232
|
|
|
|
233
|
|
|
$db->table('roles_rights') |
234
|
|
|
->column('role_id')->references('roles') |
235
|
|
|
->column('right_label')->references('rights')->then() |
236
|
|
|
->primaryKey(['role_id', 'right_label']); |
237
|
|
|
|
238
|
|
|
$db->table('users_roles') |
239
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
240
|
|
|
->column('user_id')->references('users') |
241
|
|
|
->column('role_id')->references('roles'); |
242
|
|
|
|
243
|
|
|
$db->table('all_nullable') |
244
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
245
|
|
|
->column('label')->string(255)->null() |
246
|
|
|
->column('country_id')->references('country')->null(); |
247
|
|
|
|
248
|
|
|
$db->table('animal') |
249
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
250
|
|
|
->column('name')->string(45)->index() |
251
|
|
|
->column('UPPERCASE_COLUMN')->string(45)->null() |
252
|
|
|
->column('order')->integer()->null(); |
253
|
|
|
|
254
|
|
|
$db->table('dog') |
255
|
|
|
->extends('animal') |
256
|
|
|
->column('race')->string(45)->null(); |
257
|
|
|
|
258
|
|
|
$db->table('cat') |
259
|
|
|
->extends('animal') |
260
|
|
|
->column('cuteness_level')->integer()->null(); |
261
|
|
|
|
262
|
|
|
$db->table('panda') |
263
|
|
|
->extends('animal') |
264
|
|
|
->column('weight')->float()->null(); |
265
|
|
|
|
266
|
|
|
$db->table('boats') |
267
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
268
|
|
|
->column('name')->string(255) |
269
|
|
|
->column('anchorage_country')->references('country')->notNull()->then() |
270
|
|
|
->column('current_country')->references('country')->null()->then() |
271
|
|
|
->column('length')->decimal(10, 2)->null()->then() |
272
|
|
|
->unique(['anchorage_country', 'name']); |
273
|
|
|
|
274
|
|
|
$db->table('sailed_countries') |
275
|
|
|
->column('boat_id')->references('boats') |
276
|
|
|
->column('country_id')->references('country') |
277
|
|
|
->then()->primaryKey(['boat_id', 'country_id']); |
278
|
|
|
|
279
|
|
|
$db->table('category') |
280
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
281
|
|
|
->column('label')->string(255) |
282
|
|
|
->column('parent_id')->references('category')->null(); |
283
|
|
|
|
284
|
|
|
$db->table('article') |
285
|
|
|
->column('id')->string(36)->primaryKey()->comment('@UUID') |
286
|
|
|
->column('content')->string(255) |
287
|
|
|
->column('author_id')->references('users')->null() |
288
|
|
|
->column('attachment')->blob()->null(); |
289
|
|
|
|
290
|
|
|
$db->table('article2') |
291
|
|
|
->column('id')->string(36)->primaryKey()->comment('@UUID v4') |
292
|
|
|
->column('content')->string(255); |
293
|
|
|
|
294
|
|
|
$db->table('files') |
295
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
296
|
|
|
->column('file')->blob() |
297
|
|
|
->column('md5')->string()->null()->comment("@ProtectedGetter\n@ProtectedSetter") |
298
|
|
|
->column('article_id')->references('article')->null()->comment("@ProtectedGetter\n@ProtectedSetter\n@ProtectedOneToMany"); |
299
|
|
|
|
300
|
|
|
$toSchema->getTable('users') |
301
|
|
|
->addUniqueIndex([$connection->quoteIdentifier('login')], 'users_login_idx') |
302
|
|
|
->addIndex([$connection->quoteIdentifier('status'), $connection->quoteIdentifier('country_id')], 'users_status_country_idx'); |
303
|
|
|
|
304
|
|
|
// We create the same index twice |
305
|
|
|
// except for Oracle that won't let us create twice the same index. |
306
|
|
|
if (!$connection->getDatabasePlatform() instanceof OraclePlatform) { |
307
|
|
|
$toSchema->getTable('users') |
308
|
|
|
->addUniqueIndex([$connection->quoteIdentifier('login')], 'users_login_idx_2'); |
309
|
|
|
} |
310
|
|
|
|
311
|
|
|
// A table with a foreign key that references a non primary key. |
312
|
|
|
$db->table('ref_no_prim_key') |
313
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment('@Autoincrement') |
314
|
|
|
->column('from')->string(50) |
315
|
|
|
->column('to')->string(50)->unique(); |
316
|
|
|
|
317
|
|
|
$toSchema->getTable($connection->quoteIdentifier('ref_no_prim_key'))->addForeignKeyConstraint($connection->quoteIdentifier('ref_no_prim_key'), [$connection->quoteIdentifier('from')], [$connection->quoteIdentifier('to')]); |
318
|
|
|
|
319
|
|
|
// A table with multiple primary keys. |
320
|
|
|
$db->table('states') |
321
|
|
|
->column('country_id')->references('country') |
322
|
|
|
->column('code')->string(3) |
323
|
|
|
->column('name')->string(50)->then() |
324
|
|
|
->primaryKey(['country_id', 'code']); |
325
|
|
|
|
326
|
|
|
// Tables using @Json annotations |
327
|
|
|
$db->table('accounts') |
328
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
329
|
|
|
->column('name')->string(); |
330
|
|
|
|
331
|
|
|
$db->table('nodes') |
332
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement()->comment("@JsonIgnore\n@Autoincrement") |
333
|
|
|
->column('alias_id')->references('nodes')->null()->comment('@JsonRecursive') |
334
|
|
|
->column('parent_id')->references('nodes')->null()->comment('@JsonInclude') |
335
|
|
|
->column('root_id')->references('nodes')->null()->comment('@JsonIgnore') |
336
|
|
|
->column('owner_id')->references('accounts')->null()->comment('@JsonFormat(property="name") @JsonInclude') |
337
|
|
|
->column('name')->string()->comment('@JsonKey("basename")') |
338
|
|
|
->column('size')->integer()->notNull()->default(0)->comment('@JsonFormat(unit=" o")') |
339
|
|
|
->column('weight')->float()->null()->comment('@JsonFormat(decimals=2,unit="g")') |
340
|
|
|
->column('created_at')->date()->null()->comment('@JsonFormat("Y-m-d")'); |
341
|
|
|
|
342
|
|
|
$db->table('nodes_guests') |
343
|
|
|
->column('node_id')->references('nodes')->comment('@JsonIgnore') |
344
|
|
|
->column('guest_id')->references('accounts')->comment('@JsonKey("guests") @JsonFormat(method="getName")'); |
345
|
|
|
|
346
|
|
|
$db->table('node_entries') |
347
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
348
|
|
|
->column('node_id')->references('nodes')->comment('@JsonCollection("entries") @JsonFormat(property="entry")') |
349
|
|
|
->column('entry')->string()->null(); |
350
|
|
|
|
351
|
|
|
$db->table('artists') |
352
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
353
|
|
|
->column('children')->array()->null() //used to test conflicts with autopivot |
354
|
|
|
->column('name')->string(); |
355
|
|
|
|
356
|
|
|
$db->table('albums') |
357
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
358
|
|
|
->column('artist_id')->references('artists')->comment('@JsonCollection(key="discography")') |
359
|
|
|
->column('account_id')->references('accounts') |
360
|
|
|
->column('node_id')->references('nodes')->null() |
361
|
|
|
->column('title')->string() |
362
|
|
|
->then()->unique(['artist_id', 'account_id'])->unique(['artist_id', 'node_id']); |
363
|
|
|
|
364
|
|
|
$db->table('tracks') |
365
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
366
|
|
|
->column('album_id')->references('albums')->comment('@JsonCollection @JsonRecursive') |
367
|
|
|
->column('title')->string() |
368
|
|
|
->column('duration')->time()->comment('@JsonFormat("H:i:s")'); |
369
|
|
|
|
370
|
|
|
$db->table('featuring') |
371
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
372
|
|
|
->column('track_id')->references('tracks') |
373
|
|
|
->column('artist_id')->references('artists')->comment('@JsonKey("feat") @JsonInclude'); |
374
|
|
|
|
375
|
|
|
$db->table('artists_relations') //used to test the auto pivot case |
376
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
377
|
|
|
->column('parent_id')->references('artists') |
378
|
|
|
->column('child_id')->references('artists'); |
379
|
|
|
|
380
|
|
|
$db->table('children') //used to test conflicts with autopivot |
381
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
382
|
|
|
->column('artist_id')->references('artists'); |
383
|
|
|
|
384
|
|
|
$db->junctionTable('person', 'boats'); |
385
|
|
|
|
386
|
|
|
if (!$connection->getDatabasePlatform() instanceof OraclePlatform) { |
387
|
|
|
$db->table('base_objects') |
388
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
389
|
|
|
->column('label')->string(); |
390
|
|
|
$db->table('inherited_objects') |
391
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
392
|
|
|
->column('base_object_id')->references('base_objects')->unique()->comment('@JsonCollection'); |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
$db->table('composite_fk_target_reference') |
396
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
397
|
|
|
->column('label')->string(); |
398
|
|
|
|
399
|
|
|
$targetTable = $db->table('composite_fk_target') |
400
|
|
|
->column('id_1')->references('composite_fk_target_reference') |
401
|
|
|
->column('id_2')->integer() |
402
|
|
|
->then()->primaryKey(['id_1', 'id_2']); |
403
|
|
|
$db->table('composite_fk_source') |
404
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
405
|
|
|
->column('fk_1')->integer() |
406
|
|
|
->column('fk_2')->integer() |
407
|
|
|
->then()->getDbalTable()->addForeignKeyConstraint($targetTable->getDbalTable(), [$connection->quoteIdentifier('fk_1'), $connection->quoteIdentifier('fk_2')], [$connection->quoteIdentifier('id_1'), $connection->quoteIdentifier('id_2')]); |
408
|
|
|
|
409
|
|
|
// Test case, the problem here is: |
410
|
|
|
// - `inheritance_agency` have an FK to `inheritance_society.**id_entity**` |
411
|
|
|
// - `inheritance_society` have an FK to `inheritance_entity.**id**` |
412
|
|
|
$db->table('inheritance_entity') |
413
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement(); |
414
|
|
|
$db->table('inheritance_society') |
415
|
|
|
->column('id_entity')->references('inheritance_entity')->primaryKey() |
416
|
|
|
->then(); |
417
|
|
|
$db->table('inheritance_agency') |
418
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
419
|
|
|
->column('id_parent_society')->references('inheritance_society'); |
420
|
|
|
|
421
|
|
|
// Uppercase table and column names: |
422
|
|
|
if (!$connection->getDatabasePlatform() instanceof OraclePlatform) { |
423
|
|
|
$db->table('UPPERCASE_A') |
424
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement(); |
425
|
|
|
|
426
|
|
|
$db->table('UPPERCASE_B') |
427
|
|
|
->column('id')->integer()->primaryKey()->autoIncrement() |
428
|
|
|
->column('UNIQUE_UPPERCASE_A_ID')->references('UPPERCASE_A')->unique(); |
429
|
|
|
|
430
|
|
|
$db->table('UPPERCASE_A') |
431
|
|
|
->column('UPPERCASE_B_ID')->references('UPPERCASE_B'); |
432
|
|
|
} |
433
|
|
|
|
434
|
|
|
$sqlStmts = $toSchema->getMigrateFromSql($fromSchema, $connection->getDatabasePlatform()); |
435
|
|
|
|
436
|
|
|
foreach ($sqlStmts as $sqlStmt) { |
437
|
|
|
//echo $sqlStmt."\n"; |
438
|
|
|
$connection->exec($sqlStmt); |
439
|
|
|
} |
440
|
|
|
|
441
|
|
|
// Let's generate computed columns |
442
|
|
|
if ($connection->getDatabasePlatform() instanceof MySqlPlatform && !self::isMariaDb($connection)) { |
443
|
|
|
$connection->exec('CREATE TABLE `players` ( |
444
|
|
|
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL, |
445
|
|
|
`player_and_games` JSON NOT NULL, |
446
|
|
|
`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> \'$.name\') NOT NULL COMMENT \'@ReadOnly\', |
447
|
|
|
`animal_id` INT COMMENT \'@ReadOnly\', |
448
|
|
|
PRIMARY KEY (`id`), |
449
|
|
|
FOREIGN KEY (animal_id) REFERENCES animal(id) |
450
|
|
|
); |
451
|
|
|
'); |
452
|
|
|
} |
453
|
|
|
|
454
|
|
|
self::insert($connection, 'country', [ |
455
|
|
|
'label' => 'France', |
456
|
|
|
]); |
457
|
|
|
self::insert($connection, 'country', [ |
458
|
|
|
'label' => 'uk', |
459
|
|
|
]); |
460
|
|
|
self::insert($connection, 'country', [ |
461
|
|
|
'label' => 'Jamaica', |
462
|
|
|
]); |
463
|
|
|
|
464
|
|
|
self::insert($connection, 'person', [ |
465
|
|
|
'name' => 'John Smith', |
466
|
|
|
'created_at' => '2015-10-24 11:57:13', |
467
|
|
|
]); |
468
|
|
|
self::insert($connection, 'person', [ |
469
|
|
|
'name' => 'Jean Dupont', |
470
|
|
|
'created_at' => '2015-10-24 11:57:13', |
471
|
|
|
]); |
472
|
|
|
self::insert($connection, 'person', [ |
473
|
|
|
'name' => 'Robert Marley', |
474
|
|
|
'created_at' => '2015-10-24 11:57:13', |
475
|
|
|
]); |
476
|
|
|
self::insert($connection, 'person', [ |
477
|
|
|
'name' => 'Bill Shakespeare', |
478
|
|
|
'created_at' => '2015-10-24 11:57:13', |
479
|
|
|
]); |
480
|
|
|
|
481
|
|
|
self::insert($connection, 'contact', [ |
482
|
|
|
'id' => 1, |
483
|
|
|
'email' => '[email protected]', |
484
|
|
|
'manager_id' => null, |
485
|
|
|
]); |
486
|
|
|
self::insert($connection, 'contact', [ |
487
|
|
|
'id' => 2, |
488
|
|
|
'email' => '[email protected]', |
489
|
|
|
'manager_id' => null, |
490
|
|
|
]); |
491
|
|
|
self::insert($connection, 'contact', [ |
492
|
|
|
'id' => 3, |
493
|
|
|
'email' => '[email protected]', |
494
|
|
|
'manager_id' => null, |
495
|
|
|
]); |
496
|
|
|
self::insert($connection, 'contact', [ |
497
|
|
|
'id' => 4, |
498
|
|
|
'email' => '[email protected]', |
499
|
|
|
'manager_id' => 1, |
500
|
|
|
]); |
501
|
|
|
|
502
|
|
|
self::insert($connection, 'rights', [ |
503
|
|
|
'label' => 'CAN_SING', |
504
|
|
|
]); |
505
|
|
|
self::insert($connection, 'rights', [ |
506
|
|
|
'label' => 'CAN_WRITE', |
507
|
|
|
]); |
508
|
|
|
|
509
|
|
|
self::insert($connection, 'roles', [ |
510
|
|
|
'name' => 'Admins', |
511
|
|
|
'created_at' => '2015-10-24' |
512
|
|
|
]); |
513
|
|
|
self::insert($connection, 'roles', [ |
514
|
|
|
'name' => 'Writers', |
515
|
|
|
'created_at' => '2015-10-24' |
516
|
|
|
]); |
517
|
|
|
self::insert($connection, 'roles', [ |
518
|
|
|
'name' => 'Singers', |
519
|
|
|
'created_at' => '2015-10-24' |
520
|
|
|
]); |
521
|
|
|
|
522
|
|
|
self::insert($connection, 'roles_rights', [ |
523
|
|
|
'role_id' => 1, |
524
|
|
|
'right_label' => 'CAN_SING' |
525
|
|
|
]); |
526
|
|
|
self::insert($connection, 'roles_rights', [ |
527
|
|
|
'role_id' => 3, |
528
|
|
|
'right_label' => 'CAN_SING' |
529
|
|
|
]); |
530
|
|
|
self::insert($connection, 'roles_rights', [ |
531
|
|
|
'role_id' => 1, |
532
|
|
|
'right_label' => 'CAN_WRITE' |
533
|
|
|
]); |
534
|
|
|
self::insert($connection, 'roles_rights', [ |
535
|
|
|
'role_id' => 2, |
536
|
|
|
'right_label' => 'CAN_WRITE' |
537
|
|
|
]); |
538
|
|
|
|
539
|
|
|
self::insert($connection, 'users', [ |
540
|
|
|
'id' => 1, |
541
|
|
|
'login' => 'john.smith', |
542
|
|
|
'password' => null, |
543
|
|
|
'status' => 'on', |
544
|
|
|
'country_id' => 2 |
545
|
|
|
]); |
546
|
|
|
self::insert($connection, 'users', [ |
547
|
|
|
'id' => 2, |
548
|
|
|
'login' => 'jean.dupont', |
549
|
|
|
'password' => null, |
550
|
|
|
'status' => 'on', |
551
|
|
|
'country_id' => 1 |
552
|
|
|
]); |
553
|
|
|
self::insert($connection, 'users', [ |
554
|
|
|
'id' => 3, |
555
|
|
|
'login' => 'robert.marley', |
556
|
|
|
'password' => null, |
557
|
|
|
'status' => 'off', |
558
|
|
|
'country_id' => 3 |
559
|
|
|
]); |
560
|
|
|
self::insert($connection, 'users', [ |
561
|
|
|
'id' => 4, |
562
|
|
|
'login' => 'bill.shakespeare', |
563
|
|
|
'password' => null, |
564
|
|
|
'status' => 'off', |
565
|
|
|
'country_id' => 2 |
566
|
|
|
]); |
567
|
|
|
|
568
|
|
|
self::insert($connection, 'users_roles', [ |
569
|
|
|
'user_id' => 1, |
570
|
|
|
'role_id' => 1, |
571
|
|
|
]); |
572
|
|
|
self::insert($connection, 'users_roles', [ |
573
|
|
|
'user_id' => 2, |
574
|
|
|
'role_id' => 1, |
575
|
|
|
]); |
576
|
|
|
self::insert($connection, 'users_roles', [ |
577
|
|
|
'user_id' => 3, |
578
|
|
|
'role_id' => 3, |
579
|
|
|
]); |
580
|
|
|
self::insert($connection, 'users_roles', [ |
581
|
|
|
'user_id' => 4, |
582
|
|
|
'role_id' => 2, |
583
|
|
|
]); |
584
|
|
|
self::insert($connection, 'users_roles', [ |
585
|
|
|
'user_id' => 3, |
586
|
|
|
'role_id' => 2, |
587
|
|
|
]); |
588
|
|
|
|
589
|
|
|
self::insert($connection, 'ref_no_prim_key', [ |
590
|
|
|
'from' => 'foo', |
591
|
|
|
'to' => 'foo', |
592
|
|
|
]); |
593
|
|
|
|
594
|
|
|
self::insert($connection, 'accounts', [ |
595
|
|
|
'id' => 1, |
596
|
|
|
'name' => 'root' |
597
|
|
|
]); |
598
|
|
|
self::insert($connection, 'accounts', [ |
599
|
|
|
'id' => 2, |
600
|
|
|
'name' => 'user' |
601
|
|
|
]); |
602
|
|
|
self::insert($connection, 'accounts', [ |
603
|
|
|
'id' => 3, |
604
|
|
|
'name' => 'www' |
605
|
|
|
]); |
606
|
|
|
self::insert($connection, 'nodes', [ |
607
|
|
|
'id' => 1, |
608
|
|
|
'owner_id' => 1, |
609
|
|
|
'name' => '/', |
610
|
|
|
'created_at' => (new DateTime('last year'))->format('Y-m-d 00:00:00'), |
611
|
|
|
]); |
612
|
|
|
self::insert($connection, 'nodes', [ |
613
|
|
|
'id' => 2, |
614
|
|
|
'name' => 'private', |
615
|
|
|
'created_at' => (new DateTime('last year'))->format('Y-m-d 00:00:00'), |
616
|
|
|
'parent_id' => 1, |
617
|
|
|
]); |
618
|
|
|
self::insert($connection, 'nodes', [ |
619
|
|
|
'id' => 3, |
620
|
|
|
'name' => 'var', |
621
|
|
|
'created_at' => (new DateTime('last year'))->format('Y-m-d 00:00:00'), |
622
|
|
|
'parent_id' => 2, |
623
|
|
|
]); |
624
|
|
|
self::insert($connection, 'nodes', [ |
625
|
|
|
'id' => 4, |
626
|
|
|
'name' => 'var', |
627
|
|
|
'created_at' => (new DateTime('last year'))->format('Y-m-d 00:00:00'), |
628
|
|
|
'parent_id' => 1, |
629
|
|
|
'alias_id' => 3 |
630
|
|
|
]); |
631
|
|
|
self::insert($connection, 'nodes', [ |
632
|
|
|
'id' => 5, |
633
|
|
|
'name' => 'www', |
634
|
|
|
'created_at' => (new DateTime('last week'))->format('Y-m-d 00:00:00'), |
635
|
|
|
'parent_id' => 4 |
636
|
|
|
]); |
637
|
|
|
self::insert($connection, 'nodes', [ |
638
|
|
|
'id' => 6, |
639
|
|
|
'owner_id' => 2, |
640
|
|
|
'name' => 'index.html', |
641
|
|
|
'created_at' => (new DateTime('now'))->format('Y-m-d 00:00:00'), |
642
|
|
|
'size' => 512, |
643
|
|
|
'weight' => 42.5, |
644
|
|
|
'parent_id' => 5 |
645
|
|
|
]); |
646
|
|
|
self::insert($connection, 'nodes', [ |
647
|
|
|
'id' => 7, |
648
|
|
|
'name' => 'index.html', |
649
|
|
|
'created_at' => (new DateTime('now'))->format('Y-m-d 00:00:00'), |
650
|
|
|
'alias_id' => 6, |
651
|
|
|
'parent_id' => 1 |
652
|
|
|
]); |
653
|
|
|
self::insert($connection, 'nodes', [ |
654
|
|
|
'id' => 8, |
655
|
|
|
'name' => 'index.htm', |
656
|
|
|
'created_at' => (new DateTime('now'))->format('Y-m-d 00:00:00'), |
657
|
|
|
'alias_id' => 7, |
658
|
|
|
'parent_id' => 1 |
659
|
|
|
]); |
660
|
|
|
self::insert($connection, 'nodes_guests', [ |
661
|
|
|
'node_id' => 6, |
662
|
|
|
'guest_id' => 1 |
663
|
|
|
]); |
664
|
|
|
self::insert($connection, 'nodes_guests', [ |
665
|
|
|
'node_id' => 6, |
666
|
|
|
'guest_id' => 3 |
667
|
|
|
]); |
668
|
|
|
self::insert($connection, 'node_entries', [ |
669
|
|
|
'node_id' => 6, |
670
|
|
|
'entry' => '<h1>' |
671
|
|
|
]); |
672
|
|
|
self::insert($connection, 'node_entries', [ |
673
|
|
|
'node_id' => 6, |
674
|
|
|
'entry' => 'Hello, World' |
675
|
|
|
]); |
676
|
|
|
self::insert($connection, 'node_entries', [ |
677
|
|
|
'node_id' => 6, |
678
|
|
|
'entry' => '</h1>' |
679
|
|
|
]); |
680
|
|
|
|
681
|
|
|
|
682
|
|
|
self::insert($connection, 'artists', [ |
683
|
|
|
'id' => 1, |
684
|
|
|
'name' => 'Pink Floyd' |
685
|
|
|
]); |
686
|
|
|
self::insert($connection, 'artists', [ |
687
|
|
|
'id' => 2, |
688
|
|
|
'name' => 'Roger Waters' |
689
|
|
|
]); |
690
|
|
|
self::insert($connection, 'artists', [ |
691
|
|
|
'id' => 3, |
692
|
|
|
'name' => 'David Gilmour' |
693
|
|
|
]); |
694
|
|
|
self::insert($connection, 'albums', [ |
695
|
|
|
'id' => 1, |
696
|
|
|
'artist_id' => 1, |
697
|
|
|
'account_id' => 1, |
698
|
|
|
'title' => 'Animals' |
699
|
|
|
]); |
700
|
|
|
self::insert($connection, 'artists_relations', [ |
701
|
|
|
'parent_id' => 1, |
702
|
|
|
'child_id' => 2 |
703
|
|
|
]); |
704
|
|
|
|
705
|
|
|
$timeType = Type::getType(Type::TIME_IMMUTABLE); |
706
|
|
|
|
707
|
|
|
self::insert($connection, 'tracks', [ |
708
|
|
|
'album_id' => 1, |
709
|
|
|
'title' =>'Pigs on the Wing 1', |
710
|
|
|
// Note: Oracle does not have a TIME column type |
711
|
|
|
'duration' => $timeType->convertToDatabaseValue(new DateTimeImmutable('1970-01-01 00:01:25'), $connection->getDatabasePlatform()), |
712
|
|
|
]); |
713
|
|
|
self::insert($connection, 'tracks', [ |
714
|
|
|
'album_id' => 1, |
715
|
|
|
'title' => 'Dogs', |
716
|
|
|
'duration' => $timeType->convertToDatabaseValue(new DateTimeImmutable('1970-01-01 00:17:04'), $connection->getDatabasePlatform()), |
717
|
|
|
]); |
718
|
|
|
self::insert($connection, 'tracks', [ |
719
|
|
|
'album_id' => 1, |
720
|
|
|
'title' => 'Pigs (Three Different Ones)', |
721
|
|
|
'duration' => $timeType->convertToDatabaseValue(new DateTimeImmutable('1970-01-01 00:11:22'), $connection->getDatabasePlatform()), |
722
|
|
|
]); |
723
|
|
|
self::insert($connection, 'tracks', [ |
724
|
|
|
'album_id' => 1, |
725
|
|
|
'title' => 'Sheep', |
726
|
|
|
'duration' => $timeType->convertToDatabaseValue(new DateTimeImmutable('1970-01-01 00:10:24'), $connection->getDatabasePlatform()), |
727
|
|
|
]); |
728
|
|
|
self::insert($connection, 'tracks', [ |
729
|
|
|
'album_id' => 1, |
730
|
|
|
'title' => 'Pigs on the Wing 2', |
731
|
|
|
'duration' => $timeType->convertToDatabaseValue(new DateTimeImmutable('1970-01-01 00:01:26'), $connection->getDatabasePlatform()), |
732
|
|
|
]); |
733
|
|
|
self::insert($connection, 'featuring', [ |
734
|
|
|
'track_id' => 1, |
735
|
|
|
'artist_id' => 2 |
736
|
|
|
]); |
737
|
|
|
self::insert($connection, 'featuring', [ |
738
|
|
|
'track_id' => 2, |
739
|
|
|
'artist_id' => 3 |
740
|
|
|
]); |
741
|
|
|
self::insert($connection, 'featuring', [ |
742
|
|
|
'track_id' => 2, |
743
|
|
|
'artist_id' => 2 |
744
|
|
|
]); |
745
|
|
|
self::insert($connection, 'featuring', [ |
746
|
|
|
'track_id' => 3, |
747
|
|
|
'artist_id' => 2 |
748
|
|
|
]); |
749
|
|
|
self::insert($connection, 'featuring', [ |
750
|
|
|
'track_id' => 4, |
751
|
|
|
'artist_id' => 2 |
752
|
|
|
]); |
753
|
|
|
self::insert($connection, 'featuring', [ |
754
|
|
|
'track_id' => 5, |
755
|
|
|
'artist_id' => 2 |
756
|
|
|
]); |
757
|
|
|
|
758
|
|
|
self::insert($connection, 'boats', [ |
759
|
|
|
'name' => 'RoseBud', |
760
|
|
|
'anchorage_country' => 1, |
761
|
|
|
'current_country' => 1, |
762
|
|
|
'length' => '13.5', |
763
|
|
|
]); |
764
|
|
|
|
765
|
|
|
self::insert($connection, 'person_boats', [ |
766
|
|
|
'person_id' => 1, |
767
|
|
|
'boat_id' => 1, |
768
|
|
|
]); |
769
|
|
|
self::insert($connection, 'composite_fk_target_reference', [ |
770
|
|
|
'id' => 1, |
771
|
|
|
'label' => 'test' |
772
|
|
|
]); |
773
|
|
|
self::insert($connection, 'composite_fk_target', [ |
774
|
|
|
'id_1' => 1, |
775
|
|
|
'id_2' => 1 |
776
|
|
|
]); |
777
|
|
|
self::insert($connection, 'composite_fk_source', [ |
778
|
|
|
'id' => 1, |
779
|
|
|
'fk_1' => 1, |
780
|
|
|
'fk_2' => 1 |
781
|
|
|
]); |
782
|
|
|
} |
783
|
|
|
|
784
|
|
|
public static function insert(Connection $connection, string $tableName, array $data): void |
785
|
|
|
{ |
786
|
|
|
$quotedData = []; |
787
|
|
|
foreach ($data as $id => $value) { |
788
|
|
|
$quotedData[$connection->quoteIdentifier($id)] = $value; |
789
|
|
|
} |
790
|
|
|
$connection->insert($connection->quoteIdentifier($tableName), $quotedData); |
791
|
|
|
} |
792
|
|
|
|
793
|
|
|
protected static function delete(Connection $connection, string $tableName, array $data): void |
794
|
|
|
{ |
795
|
|
|
$quotedData = []; |
796
|
|
|
foreach ($data as $id => $value) { |
797
|
|
|
$quotedData[$connection->quoteIdentifier($id)] = $value; |
798
|
|
|
} |
799
|
|
|
$connection->delete($connection->quoteIdentifier($tableName), $quotedData); |
800
|
|
|
} |
801
|
|
|
|
802
|
|
|
protected static function isMariaDb(Connection $connection): bool |
803
|
|
|
{ |
804
|
|
|
if (!$connection->getDatabasePlatform() instanceof MySqlPlatform) { |
805
|
|
|
return false; |
806
|
|
|
} |
807
|
|
|
$version = $connection->fetchColumn('SELECT VERSION()'); |
|
|
|
|
808
|
|
|
return stripos($version, 'maria') !== false; |
|
|
|
|
809
|
|
|
} |
810
|
|
|
} |
811
|
|
|
|
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.