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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
#
# Patch for 0.54,0.55 - 0.62
#
# cleanup
DELETE FROM s_attribute_type WHERE s_attribute_type IN ('S_TITLE', 'S_STATUS', 'S_STATCMNT');
#
# Moving s_field_type to new table
#
ALTER TABLE s_attribute_type ADD s_field_type varchar(10) AFTER display_type;
# Item Title s_field_type attribute
INSERT INTO s_attribute_type (s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type) VALUES ( 'S_TITLE', 'Item Title', 'Title', 'text(50,255)', 'hidden', 'TITLE', NULL );
INSERT INTO s_attribute_type (s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type) VALUES ( 'S_STATUS', 'System Status Type', 'Status Type', NULL, 'hidden', 'STATUSTYPE', NULL );
INSERT INTO s_attribute_type (s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type) VALUES ( 'S_STATCMNT', 'System Status Comment', 'Status Comment', 'textarea(50,5)', 'hidden', 'STATUSCMNT', NULL );
#
# It is not compulsory to set the duration, especially for Wishlist, where the input field will
# not even be supplied.
#
UPDATE s_item_attribute_type SET compulsory_ind = 'N' WHERE s_attribute_type = 'S_DURATION';
#
# Inclusion of S_STATCMNT attribute, at 255, necessitates this update.
#
UPDATE s_item_attribute_type SET order_no = '254' WHERE s_attribute_type = 'S_STATUS' and order_no = '255';
#
# These attributes are mapped to item and item_instance columns, and should have no instances in item_attribute table.
#
DELETE FROM item_attribute WHERE s_attribute_type IN('S_STATUS','S_DURATION','S_STATCMNT','S_TITLE');
#
# UPDATE s_item_attribute_type AND item_attribute for IMAGEURL, so the input field's appear after
# the TITLE, instead of before, as they currently do!
#
UPDATE s_item_attribute_type SET order_no = '2' WHERE s_attribute_type = 'IMAGEURL' and order_no = '0';
UPDATE item_attribute SET order_no = '2' WHERE s_attribute_type = 'IMAGEURL' and order_no = '0';
#
# Fix the duration lookup
#
UPDATE s_attribute_type_lookup SET value = '' WHERE s_attribute_type = 'S_DURATION' AND value = '0';
UPDATE item_instance SET borrow_duration = NULL, update_on = update_on WHERE borrow_duration = '0';
#
# Due to update of format_mins widget, we want to get rid of all english words from the
# format mask.
#
UPDATE s_attribute_type SET display_type = 'format_mins(%h %H %m %M)' WHERE s_attribute_type = 'RUN_TIME';
#
# AUDIO Lang support patch
#
# dts.gif image is smaller than the jpeg one.
UPDATE s_attribute_type_lookup SET img = 'dts.gif' WHERE s_attribute_type = 'AUDIO_LANG' AND value = 'ENGLISH_DTS';
UPDATE s_attribute_type_lookup SET img = 'dolby.gif' WHERE s_attribute_type = 'AUDIO_LANG' AND value = 'ENGLISH_5.0';
#
# New Audio Language options
#
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_THX', 'English(THX)', 'thx.gif', NULL);
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_6.1_DTS_ES', 'English (6.1 DTS ES)', 'dts.gif', NULL);
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_6.1_EX', 'English(6.1 EX)', 'dolby.gif', NULL);
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_6.1', 'English(6.1)', 'dolby.gif', NULL);
#
# Deprecated check_boxes in favour of checkbox_grid
#
UPDATE s_attribute_type SET input_type = 'checkbox_grid(%value% - %display%, 1)' WHERE s_attribute_type = 'DVD_REGION';
UPDATE s_attribute_type SET input_type = 'checkbox_grid(%value%, *)' WHERE s_attribute_type = 'RATIO';
#
# Deprecated radio_group in favour of radio_grid
#
UPDATE s_attribute_type SET input_type = 'radio_grid(%img% %display%, 1)' WHERE s_attribute_type = 'AGE_RATING';
UPDATE s_attribute_type SET input_type = 'radio_grid(%display%, *)' WHERE s_attribute_type = 'VIDQUALITY';
#
# Status Type support
#
ALTER TABLE item_instance CHANGE active_ind s_status_type varchar(1) NOT NULL default 'Y';
ALTER TABLE item_instance ADD status_comment varchar(255) AFTER s_status_type;
#
# Update s_status_type to new 'A' - Active type.
#
UPDATE item_instance SET s_status_type = 'A', update_on = update_on WHERE s_status_type = 'Y';
#
# Update empty type's to be correct
#
UPDATE user SET type = 'N' WHERE type = '';
#
# Make category allow 100 characters instead of 50
#
ALTER TABLE item CHANGE category category varchar(100);
#
# Fix borrowed item indexes
#
# Drop old index.
ALTER TABLE borrowed_item DROP INDEX borrowed_item_idx;
# drop and replace new indexes
ALTER TABLE borrowed_item DROP INDEX borrower_idx;
ALTER TABLE borrowed_item ADD INDEX borrower_idx ( borrower_id );
ALTER TABLE borrowed_item DROP INDEX item_instance_idx;
ALTER TABLE borrowed_item ADD INDEX item_instance_idx ( item_id, instance_no );
#
# Fix review table indexes
#
# Drop old index.
ALTER TABLE review DROP INDEX author_item;
# drop and replace new indexes
ALTER TABLE review DROP INDEX author_idx;
ALTER TABLE review ADD INDEX author_idx ( author_id );
ALTER TABLE review DROP INDEX item_idx;
ALTER TABLE review ADD INDEX item_idx ( item_id );
#
# Fix item table indexes
#
# Drop old index.
ALTER TABLE item DROP INDEX title;
# drop and replace new indexes
ALTER TABLE item DROP INDEX title_idx;
ALTER TABLE item ADD INDEX title_idx ( title );
ALTER TABLE item DROP INDEX s_item_type_idx;
ALTER TABLE item ADD INDEX s_item_type_idx ( s_item_type );
#
# Fix item_instance table indexes
#
# Drop old index.
ALTER TABLE item_instance DROP INDEX owner_id;
# drop and replace new indexes
ALTER TABLE item_instance DROP INDEX owner_id_idx;
ALTER TABLE item_instance ADD INDEX owner_id_idx ( owner_id );
ALTER TABLE item_instance DROP INDEX s_status_type_idx;
ALTER TABLE item_instance ADD INDEX s_status_type_idx ( s_status_type );
#
# System Item Status table
#
DROP TABLE IF EXISTS s_status_type;
CREATE table s_status_type (
s_status_type varchar(1) NOT NULL default 'Y',
description varchar(30) NOT NULL,
img varchar(255),
insert_ind varchar(1) NOT NULL default 'Y',
update_ind varchar(1) NOT NULL default 'Y',
delete_ind varchar(1) NOT NULL default 'Y',
min_display_user_type varchar(1),
min_create_user_type varchar(1),
new_owner_instance_ind varchar(1) NOT NULL default 'Y',
new_not_owner_instance_ind varchar(1) NOT NULL default 'Y',
borrow_ind varchar(1) NOT NULL default 'Y',
status_comment_ind varchar(1) NOT NULL default 'N',
default_ind varchar(1),
closed_ind varchar(1) NOT NULL default 'N',
PRIMARY KEY ( s_status_type )
) TYPE=MyISAM COMMENT='System Item Status table';
INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('A', 'Available', 'avail.gif', 'Y', 'Y', 'Y', 'G', 'N', 'Y', 'Y', 'Y', 'N', 'Y', 'N');
INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('H', 'Hidden', 'hidden.gif', 'Y', 'Y', 'Y', 'A', 'N', 'Y', 'Y', 'Y', 'N', '', 'N');
INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('N', 'Inactive', 'inactive.gif', 'Y', 'Y', 'Y', 'G', 'N', 'Y', 'Y', 'N', 'Y', '', 'N');
INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('O', 'Ordered', 'ordered.gif', 'Y', 'Y', 'Y', 'G', 'N', 'N', 'Y', 'X', 'Y', '', 'N');
INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('W', 'Wishlist', 'wishlist.gif', 'Y', 'Y', 'Y', 'G', 'N', 'N', 'Y', 'X', 'Y', '', 'N');
INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('X', 'External', 'external.gif', 'N', 'Y', 'N', 'G', 'N', 'Y', 'Y', 'B', 'H', '', 'N');
#
# A bug in System Admin Tool for 0.60 releases
#
UPDATE s_attribute_type SET s_field_type = UPPER(s_field_type) WHERE s_field_type IS NOT NULL;
#
# Need to increase size of language and theme columns in database
# as there are already themes which have exceeded the previous
# 10 character limit.
#
ALTER TABLE user CHANGE language language varchar(20);
ALTER TABLE user CHANGE theme theme varchar(20);
|
