Attachment #126148 for bug #28854

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




use Modern::Perl;

return {
    bug_number => "28854",
    description => "Item bundles support",
    up => sub {
        my ($args) = @_;
        my ($dbh, $out) = @$args{qw(dbh out)};

        if( !TableExists( 'item_bundles' ) ) {
            $dbh->do(q{
                CREATE TABLE `item_bundles` (
                  `item` int(11) NOT NULL,
                  `host` int(11) NOT NULL,
                  PRIMARY KEY (`host`, `item`),
                  UNIQUE KEY `item_bundles_uniq_1` (`item`),
                  CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
                  CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            });
        }
        say $out "item_bundles table added";

        if( !TableExists( 'items_lost_issue' ) ) {
            $dbh->do(q{
                CREATE TABLE `items_lost_issue` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `itemnumber` int(11) NOT NULL,
                  `issue_id` int(11) DEFAULT NULL,
                  `created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                  PRIMARY KEY (`id`),
                  UNIQUE KEY `itemnumber` (`itemnumber`),
                  KEY `issue_id` (`issue_id`),
                  CONSTRAINT `items_lost_issue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
                  CONSTRAINT `items_lost_issue_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            });
        }
        say $out "items_list_issue table added";

        my ($lost_val) = $dbh->selectrow_array( "SELECT MAX(authorised_value) FROM authorised_values WHERE category = 'LOST'", {} );
        $lost_val++;

        $dbh->do(qq{
           INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOST',$lost_val,'Missing from bundle')
        });
        say $out "Missing from bundle LOST AV added";

        my ($nfl_val) = $dbh->selectrow_array( "SELECT MAX(authorised_value) FROM authorised_values WHERE category = 'NOT_LOAN'", {} );
        $nfl_val++;

        $dbh->do(qq{
           INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('NOT_LOAN',$nfl_val,'Added to bundle')
        });
        say $out "Added to bundle NOT_LOAN AV added";

        $dbh->do(qq{
            INSERT IGNORE INTO systempreferences( `variable`, `value`, `options`, `explanation`, `type` )
            VALUES
              ( 'BundleLostValue', $lost_val, '', 'Sets the LOST AV value that represents "Missing from bundle" as a lost value', 'Free' ),
              ( 'BundleNotLoanValue', $nfl_val, '', 'Sets the NOT_LOAN AV value that represents "Added to bundle" as a not for loan value', 'Free')
        });
        say $out "System preferences added and set";
    }
}




) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table item_bundles
--

DROP TABLE IF EXISTS `item_bundles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item_bundles` (
  `item` int(11) NOT NULL,
  `host` int(11) NOT NULL,
  PRIMARY KEY (`host`, `item`),
  UNIQUE KEY `item_bundles_uniq_1` (`item`),
  CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `items_last_borrower`
--

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `items_lost_issue`
--

DROP TABLE IF EXISTS `items_lost_issue`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `items_lost_issue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `itemnumber` int(11) NOT NULL,
  `issue_id` int(11) DEFAULT NULL,
  `created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `itemnumber` (`itemnumber`),
  KEY `issue_id` (`issue_id`),
  CONSTRAINT `items_lost_issue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `items_lost_issue_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `items_search_fields`
--
- 

Return to bug 28854

Lines 3064-3069 CREATE TABLE `items` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +37 lines)
3064	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	3064	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3065	/!40101 SET character_set_client = @saved_cs_client /;	3065	/!40101 SET character_set_client = @saved_cs_client /;
3066		3066
		3067	--
		3068	-- Table structure for table item_bundles
		3069	--
		3070
		3071	DROP TABLE IF EXISTS `item_bundles`;
		3072	/!40101 SET @saved_cs_client = @@character_set_client /;
		3073	/!40101 SET character_set_client = utf8 /;
		3074	CREATE TABLE `item_bundles` (
		3075	`item` int(11) NOT NULL,
		3076	`host` int(11) NOT NULL,
		3077	PRIMARY KEY (`host`, `item`),
		3078	UNIQUE KEY `item_bundles_uniq_1` (`item`),
		3079	CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
		3080	CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
		3081	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		3082	/!40101 SET character_set_client = @saved_cs_client /;
		3083
3067	--	3084	--
3068	-- Table structure for table `items_last_borrower`	3085	-- Table structure for table `items_last_borrower`
3069	--	3086	--
Lines 3084-3089 CREATE TABLE `items_last_borrower` ( Link Here
3084	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	3101	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3085	/!40101 SET character_set_client = @saved_cs_client /;	3102	/!40101 SET character_set_client = @saved_cs_client /;
3086		3103
		3104	--
		3105	-- Table structure for table `items_lost_issue`
		3106	--
		3107
		3108	DROP TABLE IF EXISTS `items_lost_issue`;
		3109	/!40101 SET @saved_cs_client = @@character_set_client /;
		3110	/!40101 SET character_set_client = utf8 /;
		3111	CREATE TABLE `items_lost_issue` (
		3112	`id` int(11) NOT NULL AUTO_INCREMENT,
		3113	`itemnumber` int(11) NOT NULL,
		3114	`issue_id` int(11) DEFAULT NULL,
		3115	`created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
		3116	PRIMARY KEY (`id`),
		3117	UNIQUE KEY `itemnumber` (`itemnumber`),
		3118	KEY `issue_id` (`issue_id`),
		3119	CONSTRAINT `items_lost_issue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
		3120	CONSTRAINT `items_lost_issue_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE
		3121	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		3122	/!40101 SET character_set_client = @saved_cs_client /;
		3123
3087	--	3124	--
3088	-- Table structure for table `items_search_fields`	3125	-- Table structure for table `items_search_fields`
3089	--	3126	--
3090	-

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_28854.perl (+65 lines)
		1	use Modern::Perl;
		2
		3	return {
		4	bug_number => "28854",
		5	description => "Item bundles support",
		6	up => sub {
		7	my ($args) = @_;
		8	my ($dbh, $out) = @$args{qw(dbh out)};
		9
		10	if( !TableExists( 'item_bundles' ) ) {
		11	$dbh->do(q{
		12	CREATE TABLE `item_bundles` (
		13	`item` int(11) NOT NULL,
		14	`host` int(11) NOT NULL,
		15	PRIMARY KEY (`host`, `item`),
		16	UNIQUE KEY `item_bundles_uniq_1` (`item`),
		17	CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
		18	CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
		19	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		20	});
		21	}
		22	say $out "item_bundles table added";
		23
		24	if( !TableExists( 'items_lost_issue' ) ) {
		25	$dbh->do(q{
		26	CREATE TABLE `items_lost_issue` (
		27	`id` int(11) NOT NULL AUTO_INCREMENT,
		28	`itemnumber` int(11) NOT NULL,
		29	`issue_id` int(11) DEFAULT NULL,
		30	`created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
		31	PRIMARY KEY (`id`),
		32	UNIQUE KEY `itemnumber` (`itemnumber`),
		33	KEY `issue_id` (`issue_id`),
		34	CONSTRAINT `items_lost_issue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
		35	CONSTRAINT `items_lost_issue_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE
		36	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		37	});
		38	}
		39	say $out "items_list_issue table added";
		40
		41	my ($lost_val) = $dbh->selectrow_array( "SELECT MAX(authorised_value) FROM authorised_values WHERE category = 'LOST'", {} );
		42	$lost_val++;
		43
		44	$dbh->do(qq{
		45	INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOST',$lost_val,'Missing from bundle')
		46	});
		47	say $out "Missing from bundle LOST AV added";
		48
		49	my ($nfl_val) = $dbh->selectrow_array( "SELECT MAX(authorised_value) FROM authorised_values WHERE category = 'NOT_LOAN'", {} );
		50	$nfl_val++;
		51
		52	$dbh->do(qq{
		53	INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('NOT_LOAN',$nfl_val,'Added to bundle')
		54	});
		55	say $out "Added to bundle NOT_LOAN AV added";
		56
		57	$dbh->do(qq{
		58	INSERT IGNORE INTO systempreferences( `variable`, `value`, `options`, `explanation`, `type` )
		59	VALUES
		60	( 'BundleLostValue', $lost_val, '', 'Sets the LOST AV value that represents "Missing from bundle" as a lost value', 'Free' ),
		61	( 'BundleNotLoanValue', $nfl_val, '', 'Sets the NOT_LOAN AV value that represents "Added to bundle" as a not for loan value', 'Free')
		62	});
		63	say $out "System preferences added and set";
		64	}
65	}