Attachment #69478 for bug #19724

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




$DBversion = 'XXX';
if( CheckVersion( $DBversion ) ) {
    foreach my $table (qw(biblio_metadata deletedbiblio_metadata)) {
        if (!column_exists($table, 'timestamp')) {
            $dbh->do(qq{
                ALTER TABLE `$table`
                ADD COLUMN `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `metadata`,
                ADD KEY `timestamp` (`timestamp`)
            });
            $dbh->do(qq{
                UPDATE $table metadata
                    LEFT JOIN biblioitems ON (biblioitems.biblionumber = metadata.biblionumber)
                    LEFT JOIN biblio ON (biblio.biblionumber = metadata.biblionumber)
                SET metadata.timestamp = GREATEST(biblioitems.timestamp, biblio.timestamp);
            });
        }
    }

    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 19724 - Add [deleted]biblio_metadata.timestamp)\n";
}




    `format` VARCHAR(16) NOT NULL,
    `marcflavour` VARCHAR(16) NOT NULL,
    `metadata` LONGTEXT NOT NULL,
    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
    CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

    `format` VARCHAR(16) NOT NULL,
    `marcflavour` VARCHAR(16) NOT NULL,
    `metadata` LONGTEXT NOT NULL,
    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
    CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
- 

Return to bug 19724

Lines 3981-3989 CREATE TABLE biblio_metadata ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-3 / +6 lines)
3981	`format` VARCHAR(16) NOT NULL,	3981	`format` VARCHAR(16) NOT NULL,
3982	`marcflavour` VARCHAR(16) NOT NULL,	3982	`marcflavour` VARCHAR(16) NOT NULL,
3983	`metadata` LONGTEXT NOT NULL,	3983	`metadata` LONGTEXT NOT NULL,
		3984	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3984	PRIMARY KEY(id),	3985	PRIMARY KEY(id),
3985	UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),	3986	UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
3986	CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE	3987	CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
		3988	KEY `timestamp` (`timestamp`)
3987	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	3989	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3988		3990
3989	--	3991	--
Lines 3997-4005 CREATE TABLE deletedbiblio_metadata ( Link Here
3997	`format` VARCHAR(16) NOT NULL,	3999	`format` VARCHAR(16) NOT NULL,
3998	`marcflavour` VARCHAR(16) NOT NULL,	4000	`marcflavour` VARCHAR(16) NOT NULL,
3999	`metadata` LONGTEXT NOT NULL,	4001	`metadata` LONGTEXT NOT NULL,
		4002	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
4000	PRIMARY KEY(id),	4003	PRIMARY KEY(id),
4001	UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),	4004	UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
4002	CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE	4005	CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
		4006	KEY `timestamp` (`timestamp`)
4003	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	4007	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
4004		4008
4005	--	4009	--
4006	-

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_19724.perl (+21 lines)
	1	$DBversion = 'XXX';
	2	if( CheckVersion( $DBversion ) ) {
	3	foreach my $table (qw(biblio_metadata deletedbiblio_metadata)) {
	4	if (!column_exists($table, 'timestamp')) {
	5	$dbh->do(qq{
	6	ALTER TABLE `$table`
	7	ADD COLUMN `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `metadata`,
	8	ADD KEY `timestamp` (`timestamp`)
	9	});
	10	$dbh->do(qq{
	11	UPDATE $table metadata
	12	LEFT JOIN biblioitems ON (biblioitems.biblionumber = metadata.biblionumber)
	13	LEFT JOIN biblio ON (biblio.biblionumber = metadata.biblionumber)
	14	SET metadata.timestamp = GREATEST(biblioitems.timestamp, biblio.timestamp);
	15	});
	16	}
	17	}
	18
	19	SetVersion( $DBversion );
	20	print "Upgrade to $DBversion done (Bug 19724 - Add [deleted]biblio_metadata.timestamp)\n";
	21	}