Attachment #159390 for bug #35451

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




use Modern::Perl;

return {
    bug_number  => "35451",
    description => "Add record_table field and index to additional_field_values",
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @$args{qw(dbh out)};

        unless ( column_exists('additional_field_values', 'record_table') ) {
            $dbh->do(q{
                ALTER TABLE additional_field_values ADD record_table varchar(255) NOT NULL DEFAULT '' COMMENT 'tablename of the related record' AFTER field_id
            });

            $dbh->do(q{
                UPDATE
                    additional_field_values AS v
                INNER JOIN
                    additional_fields AS f ON f.id = v.field_id
                SET
                    v.record_table = f.tablename
            });
            say $out "Added column 'additional_field_values.record_table'";
        }

        unless ( index_exists( 'additional_field_values', 'record_table' ) ) {
            $dbh->do(q{
                ALTER TABLE `additional_field_values`
                ADD KEY `record_table` (`record_table`)
            });

            say $out "Added index 'record_table' to 'additional_field_values'";
        }
    },
};




CREATE TABLE `additional_field_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key identifier',
  `field_id` int(11) NOT NULL COMMENT 'foreign key references additional_fields(id)',
  `record_table` varchar(255) NOT NULL DEFAULT '' COMMENT 'tablename of the related record',
  `record_id` int(11) NOT NULL COMMENT 'record_id',
  `value` varchar(255) NOT NULL DEFAULT '' COMMENT 'value for this field',
  PRIMARY KEY (`id`),
  UNIQUE KEY `field_record` (`field_id`,`record_id`),
  KEY `record_table` (`record_table`),
  CONSTRAINT `afv_fk` FOREIGN KEY (`field_id`) REFERENCES `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
- 

Return to bug 35451

Lines 241-250 DROP TABLE IF EXISTS `additional_field_values`; Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +2 lines)
241	CREATE TABLE `additional_field_values` (	241	CREATE TABLE `additional_field_values` (
242	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key identifier',	242	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key identifier',
243	`field_id` int(11) NOT NULL COMMENT 'foreign key references additional_fields(id)',	243	`field_id` int(11) NOT NULL COMMENT 'foreign key references additional_fields(id)',
		244	`record_table` varchar(255) NOT NULL DEFAULT '' COMMENT 'tablename of the related record',
244	`record_id` int(11) NOT NULL COMMENT 'record_id',	245	`record_id` int(11) NOT NULL COMMENT 'record_id',
245	`value` varchar(255) NOT NULL DEFAULT '' COMMENT 'value for this field',	246	`value` varchar(255) NOT NULL DEFAULT '' COMMENT 'value for this field',
246	PRIMARY KEY (`id`),	247	PRIMARY KEY (`id`),
247	UNIQUE KEY `field_record` (`field_id`,`record_id`),	248	UNIQUE KEY `field_record` (`field_id`,`record_id`),
		249	KEY `record_table` (`record_table`),
248	CONSTRAINT `afv_fk` FOREIGN KEY (`field_id`) REFERENCES `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE	250	CONSTRAINT `afv_fk` FOREIGN KEY (`field_id`) REFERENCES `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
249	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	251	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
250	/!40101 SET character_set_client = @saved_cs_client /;	252	/!40101 SET character_set_client = @saved_cs_client /;
251	-

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_35451.pl (+35 lines)
	1	use Modern::Perl;
	2
	3	return {
	4	bug_number => "35451",
	5	description => "Add record_table field and index to additional_field_values",
	6	up => sub {
	7	my ($args) = @_;
	8	my ( $dbh, $out ) = @$args{qw(dbh out)};
	9
	10	unless ( column_exists('additional_field_values', 'record_table') ) {
	11	$dbh->do(q{
	12	ALTER TABLE additional_field_values ADD record_table varchar(255) NOT NULL DEFAULT '' COMMENT 'tablename of the related record' AFTER field_id
	13	});
	14
	15	$dbh->do(q{
	16	UPDATE
	17	additional_field_values AS v
	18	INNER JOIN
	19	additional_fields AS f ON f.id = v.field_id
	20	SET
	21	v.record_table = f.tablename
	22	});
	23	say $out "Added column 'additional_field_values.record_table'";
	24	}
	25
	26	unless ( index_exists( 'additional_field_values', 'record_table' ) ) {
	27	$dbh->do(q{
	28	ALTER TABLE `additional_field_values`
	29	ADD KEY `record_table` (`record_table`)
	30	});
	31
	32	say $out "Added index 'record_table' to 'additional_field_values'";
	33	}
	34	},
	35	};