1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
<?php
 
declare(strict_types=1);
 
namespace DoctrineMigrations;
 
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Types\Types;
use Doctrine\Migrations\AbstractMigration;
 
/**
 * Baseline schema. It uses the Schema API rather than SQL so that the same migration runs on
 * SQLite, MySQL, MariaDB and PostgreSQL.
 */
final class Version20261106120000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Create the book, loan and label tables';
    }
 
    public function up(Schema $schema): void
    {
        $book = $schema->createTable('book');
        $this->addBookColumns($book);
        $book->setPrimaryKey(['id']);
 
        $oustedBook = $schema->createTable('ousted_book');
        $this->addBookColumns($oustedBook);
        $oustedBook->addColumn('reason', Types::STRING, ['length' => 255]);
        $oustedBook->setPrimaryKey(['id']);
 
        $borrow = $schema->createTable('borrow');
        $borrow->addColumn('book_id', Types::INTEGER);
        $borrow->addColumn('who', Types::STRING, ['length' => 255]);
        $borrow->addColumn('at', Types::DATETIME_IMMUTABLE);
        $borrow->setPrimaryKey(['book_id']);
        $borrow->addForeignKeyConstraint('book', ['book_id'], ['id'], ['deferrable' => false], 'FK_55DBA8B016A2B381');
 
        $history = $schema->createTable('borrow_history');
        $this->addHistoryColumns($history);
        $history->addUniqueIndex(['book_id', 'at'], 'borrow_history_book_at');
        $history->addIndex(['book_id'], 'IDX_794A76CF16A2B381');
        $history->addForeignKeyConstraint('book', ['book_id'], ['id'], ['deferrable' => false], 'FK_794A76CF16A2B381');
 
        $oustedHistory = $schema->createTable('ousted_borrow_history');
        $this->addHistoryColumns($oustedHistory);
        $oustedHistory->addUniqueIndex(['book_id', 'at'], 'ousted_borrow_history_book_at');
        $oustedHistory->addIndex(['book_id'], 'IDX_8BF39F5F16A2B381');
        $oustedHistory->addForeignKeyConstraint('ousted_book', ['book_id'], ['id'], ['deferrable' => false], 'FK_8BF39F5F16A2B381');
 
        $label = $schema->createTable('label');
        $this->addLabelColumns($label);
        $label->addUniqueIndex(['book_id', 'type', 'label'], 'label_book_type_label');
        $label->addIndex(['label'], 'label_label');
        $label->addIndex(['book_id'], 'IDX_EA750E816A2B381');
        $label->addForeignKeyConstraint('book', ['book_id'], ['id'], ['deferrable' => false], 'FK_EA750E816A2B381');
 
        $oustedLabel = $schema->createTable('ousted_label');
        $this->addLabelColumns($oustedLabel);
        $oustedLabel->addUniqueIndex(['book_id', 'type', 'label'], 'ousted_label_book_type_label');
        $oustedLabel->addIndex(['book_id'], 'IDX_CCF8BC1B16A2B381');
        $oustedLabel->addForeignKeyConstraint('ousted_book', ['book_id'], ['id'], ['deferrable' => false], 'FK_CCF8BC1B16A2B381');
    }
 
    public function down(Schema $schema): void
    {
        $schema->dropTable('ousted_label');
        $schema->dropTable('label');
        $schema->dropTable('ousted_borrow_history');
        $schema->dropTable('borrow_history');
        $schema->dropTable('borrow');
        $schema->dropTable('ousted_book');
        $schema->dropTable('book');
    }
 
    private function addBookColumns(Table $table): void
    {
        $table->addColumn('id', Types::INTEGER);
        $table->addColumn('title', Types::STRING, ['length' => 255]);
        $table->addColumn('author', Types::STRING, ['length' => 255]);
        $table->addColumn('language', Types::STRING, ['length' => 255, 'notnull' => false]);
        $table->addColumn('edition', Types::STRING, ['length' => 255, 'notnull' => false]);
        $table->addColumn('publisher', Types::STRING, ['length' => 255, 'notnull' => false]);
        $table->addColumn('publication_loc', Types::STRING, ['length' => 255, 'notnull' => false]);
        $table->addColumn('publication_year', Types::INTEGER, ['notnull' => false]);
        $table->addColumn('placement', Types::STRING, ['length' => 255, 'notnull' => false]);
        $table->addColumn('cover', Types::STRING, ['length' => 64, 'notnull' => false]);
    }
 
    private function addLabelColumns(Table $table): void
    {
        $table->addColumn('id', Types::INTEGER, ['autoincrement' => true]);
        $table->addColumn('book_id', Types::INTEGER);
        $table->addColumn('type', Types::STRING, ['length' => 16]);
        $table->addColumn('label', Types::STRING, ['length' => 255]);
        $table->setPrimaryKey(['id']);
    }
 
    private function addHistoryColumns(Table $table): void
    {
        $table->addColumn('id', Types::INTEGER, ['autoincrement' => true]);
        $table->addColumn('book_id', Types::INTEGER);
        $table->addColumn('who', Types::STRING, ['length' => 255]);
        $table->addColumn('at', Types::DATETIME_IMMUTABLE);
        $table->addColumn('till', Types::DATETIME_IMMUTABLE);
        $table->setPrimaryKey(['id']);
    }
}