View | Details | Raw Unified | Return to bug 25260
Collapse All | Expand All

(-)a/C4/Installer.pm (-1 / +17 lines)
Lines 30-36 use vars qw(@ISA @EXPORT); Link Here
30
BEGIN {
30
BEGIN {
31
    require Exporter;
31
    require Exporter;
32
    @ISA = qw( Exporter );
32
    @ISA = qw( Exporter );
33
    push @EXPORT, qw( primary_key_exists foreign_key_exists index_exists column_exists TableExists);
33
    push @EXPORT, qw( primary_key_exists foreign_key_exists index_exists column_exists TableExists table_is_view);
34
};
34
};
35
35
36
=head1 NAME
36
=head1 NAME
Lines 683-688 sub TableExists { # Could be renamed table_exists for consistency Link Here
683
    return 0;
683
    return 0;
684
}
684
}
685
685
686
sub table_is_view {
687
    my ($table_name) = @_;
688
689
    return unless TableExists($table_name);
690
    my $dbh = C4::Context->dbh;
691
    my $database = C4::Context->config("database");
692
693
    my ($is_view) = $dbh->selectrow_array(qq{
694
        SELECT COUNT(*)
695
        FROM information_schema.tables
696
        WHERE     TABLE_SCHEMA='$database'
697
              AND TABLE_NAME='$table_name'
698
              AND TABLE_TYPE='VIEW';
699
    });
700
    return ($is_view) ? 1 : 0;
701
}
686
702
687
=head1 AUTHOR
703
=head1 AUTHOR
688
704
(-)a/installer/data/mysql/atomicupdate/bug_25260_holds_table.perl (-1 / +261 lines)
Line 0 Link Here
0
- 
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
4
    if( !TableExists( 'holds' ) ) {
5
        $dbh->do( q|
6
            CREATE TABLE `holds` (
7
                `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'internal hold identifier',
8
                `patron_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for',
9
                `pickup_library_id` VARCHAR(10) DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at',
10
                `desk_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at',
11
                `created_date` DATE DEFAULT NULL COMMENT 'the date the hold was placed',
12
                `biblio_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on',
13
                `item_id` INT(11) DEFAULT NULL COMMENT 'foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with',
14
                `item_type` VARCHAR(10) DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting',
15
                `item_level` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Is the hold was placed at item level',
16
                `completed_date` DATE DEFAULT NULL COMMENT 'the date this hold was completed (fulfilled or cancelled)',
17
                `notes` LONGTEXT COMMENT 'notes related to this hold',
18
                `priority` SMALLINT(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits',
19
                `status` ENUM('placed', 'fulfilled', 'processing', 'waiting', 'in_transit', 'cancelled') DEFAULT 'placed' NOT NULL COMMENT "the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled')",
20
                `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'the date and time this hold was last updated',
21
                `waiting_date` DATE DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library',
22
                `expiration_date` DATE DEFAULT NULL COMMENT "the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)",
23
                `lowest_priority` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned the lowest priority',
24
                `non_priority` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned no priority',
25
                `suspended` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If it is a non-priority hold',
26
                `suspended_until_date` DATETIME NULL DEFAULT NULL COMMENT 'Date until it is suspended',
27
                `completed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT "If it has been completed (i.e. either 'fulfilled' or 'cancelled')",
28
                `cancellation_reason` VARCHAR(80) NULL DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON',
29
                PRIMARY KEY (`id`),
30
                KEY `priority_status_idx` (`priority`, `status`),
31
                KEY `patron_id` (`patron_id`),
32
                KEY `biblio_id` (`biblio_id`),
33
                KEY `item_id` (`item_id`),
34
                KEY `pickup_library_id` (`pickup_library_id`),
35
                KEY `item_type` (`item_type`),
36
                CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
37
                CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`desk_id`)   REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE,
38
                CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`)   REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
39
                CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE,
40
                CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
41
                CONSTRAINT `holds_ibfk_6` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
42
            ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
43
        |);
44
45
        # migrate 'reserves' and 'old_reserves' into 'holds'
46
        $dbh->do(q|
47
            INSERT INTO holds (
48
                id,
49
                patron_id,
50
                created_date,
51
                biblio_id,
52
                pickup_library_id,
53
                desk_id,
54
                completed_date,
55
                cancellation_reason,
56
                notes,
57
                priority,
58
                status,
59
                timestamp,
60
                item_id,
61
                waiting_date,
62
                expiration_date,
63
                lowest_priority,
64
                suspended,
65
                suspended_until_date,
66
                item_type,
67
                item_level,
68
                completed
69
            )
70
            SELECT  *
71
            FROM (
72
                SELECT reserve_id,
73
                    borrowernumber,
74
                    reservedate,
75
                    biblionumber,
76
                    branchcode,
77
                    desk_id,
78
                    NULL,
79
                    cancellation_reason,
80
                    reservenotes,
81
                    priority,
82
                    CASE
83
                        WHEN found = 'W' THEN
84
                            'waiting'
85
                        WHEN found = 'T' THEN
86
                            'in_transit'
87
                        ELSE
88
                            'placed'
89
                    END,
90
                    timestamp,
91
                    itemnumber,
92
                    waitingdate,
93
                    expirationdate,
94
                    lowestPriority,
95
                    suspend,
96
                    suspend_until,
97
                    itemtype,
98
                    item_level_hold,
99
                    0
100
                FROM reserves
101
                UNION
102
                SELECT o.reserve_id,
103
                    o.borrowernumber,
104
                    o.reservedate,
105
                    o.biblionumber,
106
                    o.branchcode,
107
                    o.desk_id,
108
                    o.timestamp,
109
                    o.cancellation_reason,
110
                    o.reservenotes,
111
                    o.priority,
112
                    CASE
113
                        WHEN o.found = 'F' THEN
114
                            'fulfilled'
115
                        ELSE
116
                            'cancelled'
117
                    END,
118
                    o.timestamp,
119
                    o.itemnumber,
120
                    o.waitingdate,
121
                    o.expirationdate,
122
                    o.lowestPriority,
123
                    o.suspend,
124
                    o.suspend_until,
125
                    o.itemtype,
126
                    o.item_level_hold,
127
                    1 AS completed
128
                FROM old_reserves o
129
                LEFT JOIN
130
                    reserves r
131
                ON  o.reserve_id = r.reserve_id
132
                WHERE r.reserve_id is null
133
            ) reserves
134
            ORDER BY reservedate
135
        |);
136
137
        if ( foreign_key_exists('club_holds_to_patron_holds', 'clubs_holds_paton_holds_ibfk_3') ) {
138
            $dbh->do(q|
139
                ALTER TABLE club_holds_to_patron_holds
140
                    DROP FOREIGN KEY clubs_holds_paton_holds_ibfk_3
141
            |);
142
        }
143
144
        $dbh->do(q|
145
            ALTER TABLE club_holds_to_patron_holds
146
            ADD CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES holds (id) ON DELETE CASCADE ON UPDATE CASCADE
147
        |);
148
149
    }
150
151
    if ( TableExists('holds') && TableExists('reserves') && !table_is_view('reserves') ) {
152
        $dbh->do(q|
153
            DELETE FROM res USING `reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id
154
        |);
155
156
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM reserves");
157
        $sth->execute();
158
        my $row = $sth->fetchrow_hashref;
159
        if ($row->{count}) {
160
            print "Warning to database administrator:\n"
161
                . "There were $row->{count} reserves that could not be moved, please check '_reserves'.\n";
162
            $dbh->do("RENAME TABLE reserves TO _reserves");
163
        } else {
164
            $dbh->do("DROP TABLE reserves");
165
        }
166
    }
167
168
    $dbh->do(q|
169
        CREATE VIEW `reserves`
170
        AS
171
            SELECT
172
                `id` AS `reserve_id`,
173
                `patron_id` AS `borrowernumber`,
174
                `created_date` AS `reservedate`,
175
                `biblio_id` AS `biblionumber`,
176
                `pickup_library_id` AS `branchcode`,
177
                `desk_id`,
178
                NULL AS `cancellationdate`,
179
                `cancellation_reason`,
180
                `notes` AS `reservenotes`,
181
                `priority`,
182
                CASE
183
                    when `status` = 'waiting' then
184
                        'W'
185
                    when `status` = 'in_transit' then
186
                        'T'
187
                    else
188
                        NULL
189
                END AS `found`,
190
                `timestamp`,
191
                `item_id` AS `itemnumber`,
192
                `waiting_date` AS `waitingdate`,
193
                `expiration_date` AS `expirationdate`,
194
                `lowest_priority` AS `lowestPriority`,
195
                `suspended` AS `suspend`,
196
                `suspended_until_date` AS `suspend_until`,
197
                `item_type` AS `itemtype`,
198
                `item_level` AS `item_level_hold`,
199
                `non_priority`
200
            FROM `holds`
201
            WHERE completed=0;
202
    |) unless TableExists('reserves') && table_is_view('reserves');
203
204
    if ( TableExists('holds') && TableExists('old_reserves') && !table_is_view('old_reserves') ) {
205
        $dbh->do(q|
206
            DELETE FROM res USING `old_reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id
207
        |);
208
209
        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM old_reserves");
210
        $sth->execute();
211
        my $row = $sth->fetchrow_hashref;
212
        if ($row->{count}) {
213
            print "Warning to database administrator:\n"
214
                . "There were $row->{count} old reserves that could not be moved, please check '_old_reserves'.\n";
215
            $dbh->do("RENAME TABLE old_reserves TO _old_reserves");
216
        } else {
217
            $dbh->do("DROP TABLE old_reserves");
218
        }
219
    }
220
221
    $dbh->do(q|
222
        CREATE VIEW `old_reserves`
223
        AS
224
            SELECT
225
                `id` AS `reserve_id`,
226
                `patron_id` AS `borrowernumber`,
227
                `created_date` AS `reservedate`,
228
                `biblio_id` AS `biblionumber`,
229
                `pickup_library_id` AS `branchcode`,
230
                `desk_id`,
231
                CASE
232
                    WHEN `status` = 'cancelled' THEN
233
                        `completed_date`
234
                    ELSE
235
                        NULL
236
                END AS `cancellationdate`,
237
                `cancellation_reason`,
238
                `notes` AS `reservenotes`,
239
                `priority`,
240
                CASE
241
                    WHEN `status` = 'fulfilled' THEN
242
                        'F'
243
                    ELSE
244
                        NULL
245
                END AS `found`,
246
                `timestamp`,
247
                `item_id` AS `itemnumber`,
248
                `waiting_date` AS `waitingdate`,
249
                `expiration_date` AS `expirationdate`,
250
                `lowest_priority` AS `lowestPriority`,
251
                `suspended` AS `suspend`,
252
                `suspended_until_date` AS `suspend_until`,
253
                `item_type` AS `itemtype`,
254
                `item_level` AS `item_level_hold`,
255
                `non_priority`
256
            FROM `holds`
257
            WHERE completed=1;
258
    |) unless TableExists('old_reserves') && table_is_view('old_reserves');
259
260
    NewVersion( $DBversion, 25260, "Merge 'reserves' and 'old_reserves' into a new 'holds' table");
261
}

Return to bug 25260