|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace DoctrineMigrations; |
|
6
|
|
|
|
|
7
|
|
|
use Doctrine\DBAL\Schema\Schema; |
|
8
|
|
|
use Doctrine\Migrations\AbstractMigration; |
|
9
|
|
|
|
|
10
|
|
|
/** |
|
11
|
|
|
* Auto-generated Migration: Please modify to your needs! |
|
12
|
|
|
*/ |
|
13
|
|
|
final class Version20210424131056 extends AbstractMigration |
|
14
|
|
|
{ |
|
15
|
|
|
public function getDescription() : string |
|
16
|
|
|
{ |
|
17
|
|
|
return ''; |
|
18
|
|
|
} |
|
19
|
|
|
|
|
20
|
|
|
public function up(Schema $schema) : void |
|
21
|
|
|
{ |
|
22
|
|
|
// this up() migration is auto-generated, please modify it to your needs |
|
23
|
|
|
$this->addSql('CREATE TABLE image_tag (image_id INT NOT NULL, tag_id INT NOT NULL, INDEX IDX_5B6367D03DA5256D (image_id), INDEX IDX_5B6367D0BAD26311 (tag_id), PRIMARY KEY(image_id, tag_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB'); |
|
24
|
|
|
$this->addSql('ALTER TABLE image_tag ADD CONSTRAINT FK_5B6367D03DA5256D FOREIGN KEY (image_id) REFERENCES image (id) ON DELETE CASCADE'); |
|
25
|
|
|
$this->addSql('ALTER TABLE image_tag ADD CONSTRAINT FK_5B6367D0BAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE'); |
|
26
|
|
|
} |
|
27
|
|
|
|
|
28
|
|
|
public function down(Schema $schema) : void |
|
29
|
|
|
{ |
|
30
|
|
|
// this down() migration is auto-generated, please modify it to your needs |
|
31
|
|
|
$this->addSql('DROP TABLE image_tag'); |
|
32
|
|
|
} |
|
33
|
|
|
public function postUp(Schema $schema): void |
|
34
|
|
|
{ |
|
35
|
|
|
// TODO: Should we take this DELETE out? Outside of testing, there should never be anything in tag already |
|
36
|
|
|
$this->connection->executeStatement('DELETE FROM tag'); |
|
37
|
|
|
$images = $this->connection->fetchAllAssociative('SELECT id, keywords FROM image'); |
|
38
|
|
|
// First, gather unique keywords |
|
39
|
|
|
$allKeywords = []; |
|
40
|
|
|
foreach ($images as $image) { |
|
41
|
|
|
$keywords = unserialize($image['keywords']); |
|
42
|
|
|
$allKeywords = array_merge($allKeywords, $keywords); |
|
43
|
|
|
} |
|
44
|
|
|
$unique_keywords = array_unique($allKeywords); |
|
45
|
|
|
$insertTagStmt = $this->connection->prepare('INSERT INTO tag (name) VALUES (:name)'); |
|
46
|
|
|
|
|
47
|
|
|
// Insert our unique tags into the database, recording the ID values |
|
48
|
|
|
// we insert to use when populating image_tag. |
|
49
|
|
|
$tagsToIds = []; |
|
50
|
|
|
foreach ($unique_keywords as $newTag) { |
|
51
|
|
|
$insertTagStmt->bindValue('name', $newTag); |
|
52
|
|
|
$insertTagStmt->execute(); |
|
|
|
|
|
|
53
|
|
|
$tagsToIds[$newTag] = $this->connection->lastInsertId(); |
|
54
|
|
|
}; |
|
55
|
|
|
|
|
56
|
|
|
// Now go through the images again, creating relationships to our newly- |
|
57
|
|
|
// created tags from their existing keywords. |
|
58
|
|
|
$insertImageTagStmt = $this->connection->prepare('INSERT INTO image_tag (image_id, tag_id) VALUES (:image_id, :tag_id)'); |
|
59
|
|
|
foreach ($images as $image) { |
|
60
|
|
|
// Uniquify, just in case we had two of the same tag on one image |
|
61
|
|
|
$keywords = array_unique(unserialize($image['keywords'])); |
|
62
|
|
|
foreach ($keywords as $keyword) { |
|
63
|
|
|
$imageId = $image['id']; |
|
64
|
|
|
$tagId = $tagsToIds[$keyword]; |
|
65
|
|
|
$insertImageTagStmt->bindValue('image_id', $imageId); |
|
66
|
|
|
$insertImageTagStmt->bindValue('tag_id', $tagId); |
|
67
|
|
|
$insertImageTagStmt->execute(); |
|
|
|
|
|
|
68
|
|
|
} |
|
69
|
|
|
} |
|
70
|
|
|
// var_dump(array_unique($unique_keywords)); |
|
71
|
|
|
|
|
72
|
|
|
} |
|
73
|
|
|
} |
|
74
|
|
|
|
gothick /
omm
Loading history...
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.