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

(-)a/installer/data/mysql/atomicupdate/bug_28267.perl (+66 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    my $fields = {
4
        itemcallnumber           => 255,
5
        coded_location_qualifier => 10,
6
        location                 => 80,
7
        permanent_location       => 80,
8
        cn_source                => 10,
9
        cn_sort                  => 255,
10
        ccode                    => 80,
11
        itype                    => 10,
12
        copynumber               => 32,
13
        stocknumber              => 32,
14
        new_status               => 32,
15
    };
16
    # Index on items
17
    for my $index ( qw( itemstocknumberidx itemcallnumber items_location items_ccode itype_idx ) ) {
18
        next unless index_exists('items', $index);
19
        $dbh->do(qq|
20
            ALTER TABLE items DROP INDEX $index
21
        |);
22
    }
23
24
    # Index on deleteditems
25
    for my $index ( qw( delitemstocknumberidx itype_idx ) ) {
26
        next unless index_exists('deleteditems', $index);
27
        $dbh->do(qq|
28
            ALTER TABLE deleteditems DROP INDEX $index
29
        |);
30
    }
31
32
    while ( my ( $f, $size ) = each %$fields ) {
33
        my $datatype = ( $size >= 24 ) ? 'TEXT' : 'TINYTEXT';
34
        my $size_index = ( $size == 255 ) ? 191 : $size;
35
36
        $dbh->do(qq|
37
            ALTER TABLE items MODIFY $f $datatype DEFAULT NULL
38
        |);
39
40
        # Re-add index on items
41
        unless ( index_exists( 'items', sprintf("%s_idx", $f) ) ) {
42
            $dbh->do(sprintf q|
43
                ALTER TABLE items ADD INDEX %s_idx( %s(%s) )
44
            |, $f, $f, $size_index);
45
            }
46
47
        $dbh->do(qq|
48
            ALTER TABLE deleteditems MODIFY $f $datatype DEFAULT NULL
49
        |);
50
    }
51
52
    # Re-add index on deleteditems
53
    unless ( index_exists( 'deleteditems', 'itype_idx' ) ) {
54
        $dbh->do(q|
55
            ALTER TABLE deleteditems ADD INDEX itype_idx( itype(10))
56
        |);
57
    }
58
    unless ( index_exists( 'deleteditems', 'stocknumber_idx' ) ) {
59
        $dbh->do(q|
60
            ALTER TABLE deleteditems ADD INDEX stocknumber_idx( itype(32))
61
        |);
62
    }
63
64
    NewVersion( $DBversion, 28267, "Switch items VARCHAR fields to TINYTEXT or MEDIUMTEXT");
65
}
66
(-)a/installer/data/mysql/kohastructure.sql (-31 / +36 lines)
Lines 2464-2471 CREATE TABLE `deleteditems` ( Link Here
2464
  `itemlost_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as lost, NULL if not lost',
2464
  `itemlost_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as lost, NULL if not lost',
2465
  `withdrawn` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'authorized value defining this item as withdrawn (MARC21 952$0)',
2465
  `withdrawn` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'authorized value defining this item as withdrawn (MARC21 952$0)',
2466
  `withdrawn_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as withdrawn, NULL if not withdrawn',
2466
  `withdrawn_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as withdrawn, NULL if not withdrawn',
2467
  `itemcallnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)',
2467
  `itemcallnumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)',
2468
  `coded_location_qualifier` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)',
2468
  `coded_location_qualifier` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)',
2469
  `issues` smallint(6) DEFAULT 0 COMMENT 'number of times this item has been checked out',
2469
  `issues` smallint(6) DEFAULT 0 COMMENT 'number of times this item has been checked out',
2470
  `renewals` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been renewed',
2470
  `renewals` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been renewed',
2471
  `reserves` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been placed on hold/reserved',
2471
  `reserves` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been placed on hold/reserved',
Lines 2474-2503 CREATE TABLE `deleteditems` ( Link Here
2474
  `itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)',
2474
  `itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)',
2475
  `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)',
2475
  `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)',
2476
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered',
2476
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered',
2477
  `location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
2477
  `location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
2478
  `permanent_location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location',
2478
  `permanent_location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location',
2479
  `onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)',
2479
  `onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)',
2480
  `cn_source` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)',
2480
  `cn_source` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)',
2481
  `cn_sort` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting',
2481
  `cn_sort` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting',
2482
  `ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)',
2482
  `ccode` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)',
2483
  `materials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'materials specified (MARC21 952$3)',
2483
  `materials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'materials specified (MARC21 952$3)',
2484
  `uri` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL for the item (MARC21 952$u)',
2484
  `uri` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL for the item (MARC21 952$u)',
2485
  `itype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)',
2485
  `itype` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)',
2486
  `more_subfields_xml` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'additional 952 subfields in XML format',
2486
  `more_subfields_xml` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'additional 952 subfields in XML format',
2487
  `enumchron` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'serial enumeration/chronology for the item (MARC21 952$h)',
2487
  `enumchron` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'serial enumeration/chronology for the item (MARC21 952$h)',
2488
  `copynumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)',
2488
  `copynumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)',
2489
  `stocknumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)',
2489
  `stocknumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)',
2490
  `new_status` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.',
2490
  `new_status` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.',
2491
  `exclude_from_local_holds_priority` tinyint(1) DEFAULT NULL COMMENT 'Exclude this item from local holds priority',
2491
  `exclude_from_local_holds_priority` tinyint(1) DEFAULT NULL COMMENT 'Exclude this item from local holds priority',
2492
  PRIMARY KEY (`itemnumber`),
2492
  PRIMARY KEY (`itemnumber`),
2493
  KEY `delitembarcodeidx` (`barcode`),
2493
  KEY `delitembarcodeidx` (`barcode`),
2494
  KEY `delitemstocknumberidx` (`stocknumber`),
2495
  KEY `delitembinoidx` (`biblioitemnumber`),
2494
  KEY `delitembinoidx` (`biblioitemnumber`),
2496
  KEY `delitembibnoidx` (`biblionumber`),
2495
  KEY `delitembibnoidx` (`biblionumber`),
2497
  KEY `delhomebranch` (`homebranch`),
2496
  KEY `delhomebranch` (`homebranch`),
2498
  KEY `delholdingbranch` (`holdingbranch`),
2497
  KEY `delholdingbranch` (`holdingbranch`),
2499
  KEY `itype_idx` (`itype`),
2498
  KEY `timestamp` (`timestamp`),
2500
  KEY `timestamp` (`timestamp`)
2499
  KEY `itype_idx` (`itype`(10)),
2500
  KEY `stocknumber_idx` (`itype`(32))
2501
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2501
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2502
/*!40101 SET character_set_client = @saved_cs_client */;
2502
/*!40101 SET character_set_client = @saved_cs_client */;
2503
2503
Lines 3022-3029 CREATE TABLE `items` ( Link Here
3022
  `itemlost_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as lost, NULL if not lost',
3022
  `itemlost_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as lost, NULL if not lost',
3023
  `withdrawn` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'authorized value defining this item as withdrawn (MARC21 952$0)',
3023
  `withdrawn` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'authorized value defining this item as withdrawn (MARC21 952$0)',
3024
  `withdrawn_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as withdrawn, NULL if not withdrawn',
3024
  `withdrawn_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as withdrawn, NULL if not withdrawn',
3025
  `itemcallnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)',
3025
  `itemcallnumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)',
3026
  `coded_location_qualifier` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)',
3026
  `coded_location_qualifier` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)',
3027
  `issues` smallint(6) DEFAULT 0 COMMENT 'number of times this item has been checked out/issued',
3027
  `issues` smallint(6) DEFAULT 0 COMMENT 'number of times this item has been checked out/issued',
3028
  `renewals` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been renewed',
3028
  `renewals` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been renewed',
3029
  `reserves` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been placed on hold/reserved',
3029
  `reserves` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been placed on hold/reserved',
Lines 3032-3064 CREATE TABLE `items` ( Link Here
3032
  `itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)',
3032
  `itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)',
3033
  `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)',
3033
  `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)',
3034
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered',
3034
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered',
3035
  `location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
3035
  `location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
3036
  `permanent_location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location',
3036
  `permanent_location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location',
3037
  `onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)',
3037
  `onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)',
3038
  `cn_source` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)',
3038
  `cn_source` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)',
3039
  `cn_sort` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting',
3039
  `cn_sort` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting',
3040
  `ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)',
3040
  `ccode` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)',
3041
  `materials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'materials specified (MARC21 952$3)',
3041
  `materials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'materials specified (MARC21 952$3)',
3042
  `uri` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL for the item (MARC21 952$u)',
3042
  `uri` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL for the item (MARC21 952$u)',
3043
  `itype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)',
3043
  `itype` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)',
3044
  `more_subfields_xml` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'additional 952 subfields in XML format',
3044
  `more_subfields_xml` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'additional 952 subfields in XML format',
3045
  `enumchron` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'serial enumeration/chronology for the item (MARC21 952$h)',
3045
  `enumchron` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'serial enumeration/chronology for the item (MARC21 952$h)',
3046
  `copynumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)',
3046
  `copynumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)',
3047
  `stocknumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)',
3047
  `stocknumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)',
3048
  `new_status` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.',
3048
  `new_status` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.',
3049
  `exclude_from_local_holds_priority` tinyint(1) DEFAULT NULL COMMENT 'Exclude this item from local holds priority',
3049
  `exclude_from_local_holds_priority` tinyint(1) DEFAULT NULL COMMENT 'Exclude this item from local holds priority',
3050
  PRIMARY KEY (`itemnumber`),
3050
  PRIMARY KEY (`itemnumber`),
3051
  UNIQUE KEY `itembarcodeidx` (`barcode`),
3051
  UNIQUE KEY `itembarcodeidx` (`barcode`),
3052
  KEY `itemstocknumberidx` (`stocknumber`),
3053
  KEY `itembinoidx` (`biblioitemnumber`),
3052
  KEY `itembinoidx` (`biblioitemnumber`),
3054
  KEY `itembibnoidx` (`biblionumber`),
3053
  KEY `itembibnoidx` (`biblionumber`),
3055
  KEY `homebranch` (`homebranch`),
3054
  KEY `homebranch` (`homebranch`),
3056
  KEY `holdingbranch` (`holdingbranch`),
3055
  KEY `holdingbranch` (`holdingbranch`),
3057
  KEY `itemcallnumber` (`itemcallnumber`(191)),
3058
  KEY `items_location` (`location`),
3059
  KEY `items_ccode` (`ccode`),
3060
  KEY `itype_idx` (`itype`),
3061
  KEY `timestamp` (`timestamp`),
3056
  KEY `timestamp` (`timestamp`),
3057
  KEY `coded_location_qualifier_idx` (`coded_location_qualifier`(10)),
3058
  KEY `itemcallnumber_idx` (`itemcallnumber`(191)),
3059
  KEY `permanent_location_idx` (`permanent_location`(80)),
3060
  KEY `location_idx` (`location`(80)),
3061
  KEY `stocknumber_idx` (`stocknumber`(32)),
3062
  KEY `cn_sort_idx` (`cn_sort`(191)),
3063
  KEY `cn_source_idx` (`cn_source`(10)),
3064
  KEY `new_status_idx` (`new_status`(32)),
3065
  KEY `itype_idx` (`itype`(10)),
3066
  KEY `copynumber_idx` (`copynumber`(32)),
3067
  KEY `ccode_idx` (`ccode`(80)),
3062
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
3068
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
3063
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
3069
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
3064
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
3070
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
3065
- 

Return to bug 28267