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

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

Return to bug 25260