Attachment #68888 for bug #19555

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




    # first deleted records ($deleted == 1), then normal records ($deleted == 0)
    STAGELOOP:
    for ( ; $deleted >= 0; $deleted-- ) {
        my $table = $deleted ? 'deletedbiblio_metadata' : 'biblio_metadata';
        my $sql = "
            SELECT biblionumber
            FROM $table

            $sql = "
                SELECT MAX(timestamp)
                FROM (
                    SELECT timestamp FROM deletedbiblio_metadata WHERE biblionumber = ?
                    UNION
                    SELECT timestamp FROM deleteditems WHERE biblionumber = ?
                ) bis

            $sql = "
                SELECT MAX(timestamp)
                FROM (
                    SELECT timestamp FROM biblio_metadata WHERE biblionumber = ?
                    UNION
                    SELECT timestamp FROM deleteditems WHERE biblionumber = ?
                    UNION




$DBversion = 'XXX';  # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {

    # or perform some test and warn
    unless( column_exists( 'biblio_metadata', 'timestamp' ) ) {
        $dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" );
    }

    # or perform some test and warn
    unless( column_exists( 'deletedbiblio_metadata', 'timestamp' ) ) {
        $dbh->do( "ALTER TABLE deletedbiblio_metadata ADD COLUMN timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" );
    }

    # Always end with this (adjust the bug info)
    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 19555 - Add timestamps to biblio_metadata and deletedbiblio_metadata)\n";
}

Lines 3980-3985 CREATE TABLE biblio_metadata ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+2 lines)
3980	`format` VARCHAR(16) NOT NULL,	3980	`format` VARCHAR(16) NOT NULL,
3981	`marcflavour` VARCHAR(16) NOT NULL,	3981	`marcflavour` VARCHAR(16) NOT NULL,
3982	`metadata` LONGTEXT NOT NULL,	3982	`metadata` LONGTEXT NOT NULL,
		3983	timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
3983	PRIMARY KEY(id),	3984	PRIMARY KEY(id),
3984	UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),	3985	UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
3985	CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE	3986	CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
Lines 3996-4001 CREATE TABLE deletedbiblio_metadata ( Link Here
3996	`format` VARCHAR(16) NOT NULL,	3997	`format` VARCHAR(16) NOT NULL,
3997	`marcflavour` VARCHAR(16) NOT NULL,	3998	`marcflavour` VARCHAR(16) NOT NULL,
3998	`metadata` LONGTEXT NOT NULL,	3999	`metadata` LONGTEXT NOT NULL,
		4000	timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
3999	PRIMARY KEY(id),	4001	PRIMARY KEY(id),
4000	UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),	4002	UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
4001	CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE	4003	CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE

Lines 69-75 $dbh->do('DELETE FROM oai_sets'); Link Here

(-)a/t/db_dependent/OAI/Server.t (-3 / +2 lines)
69	my $date_added = DateTime->now() . 'Z';	69	my $date_added = DateTime->now() . 'Z';
70	my $date_to = substr($date_added, 0, 10) . 'T23:59:59Z';	70	my $date_to = substr($date_added, 0, 10) . 'T23:59:59Z';
71	my (@header, @marcxml, @oaidc);	71	my (@header, @marcxml, @oaidc);
72	my $sth = $dbh->prepare('SELECT timestamp FROM biblioitems WHERE biblionumber=?');	72	my $sth = $dbh->prepare('SELECT timestamp FROM biblio_metadata WHERE biblionumber=?');
73		73
74	# Add biblio records	74	# Add biblio records
75	foreach my $index ( 0 .. NUMBER_OF_MARC_RECORDS - 1 ) {	75	foreach my $index ( 0 .. NUMBER_OF_MARC_RECORDS - 1 ) {
Lines 344-350 sub manipulate_timestamp { Link Here
344	return $timestamp if $timestamp !~ /\d{2}Z/;	344	return $timestamp if $timestamp !~ /\d{2}Z/;
345	my $secs = sprintf( "%02d", $index );	345	my $secs = sprintf( "%02d", $index );
346	$timestamp =~ s/\d{2}Z/${secs}Z/;	346	$timestamp =~ s/\d{2}Z/${secs}Z/;
347	$dbh->do("UPDATE biblioitems SET timestamp=? WHERE biblionumber=?", undef,	347	$dbh->do("UPDATE biblio_metadata SET timestamp=? WHERE biblionumber=?", undef,
348	( $timestamp, $bibno ));	348	( $timestamp, $bibno ));
349	return $timestamp;	349	return $timestamp;
350	}	350	}
351	-

Return to bug 19555

Lines 58-64 sub GetRecords { Link Here

(-)a/Koha/OAI/Server/ListBase.pm (-3 / +3 lines)
58	# first deleted records ($deleted == 1), then normal records ($deleted == 0)	58	# first deleted records ($deleted == 1), then normal records ($deleted == 0)
59	STAGELOOP:	59	STAGELOOP:
60	for ( ; $deleted >= 0; $deleted-- ) {	60	for ( ; $deleted >= 0; $deleted-- ) {
61	my $table = $deleted ? 'deletedbiblioitems' : 'biblioitems';	61	my $table = $deleted ? 'deletedbiblio_metadata' : 'biblio_metadata';
62	my $sql = "	62	my $sql = "
63	SELECT biblionumber	63	SELECT biblionumber
64	FROM $table	64	FROM $table
Lines 103-109 sub GetRecords { Link Here
103	$sql = "	103	$sql = "
104	SELECT MAX(timestamp)	104	SELECT MAX(timestamp)
105	FROM (	105	FROM (
106	SELECT timestamp FROM deletedbiblioitems WHERE biblionumber = ?	106	SELECT timestamp FROM deletedbiblio_metadata WHERE biblionumber = ?
107	UNION	107	UNION
108	SELECT timestamp FROM deleteditems WHERE biblionumber = ?	108	SELECT timestamp FROM deleteditems WHERE biblionumber = ?
109	) bis	109	) bis
Lines 112-118 sub GetRecords { Link Here
112	$sql = "	112	$sql = "
113	SELECT MAX(timestamp)	113	SELECT MAX(timestamp)
114	FROM (	114	FROM (
115	SELECT timestamp FROM biblioitems WHERE biblionumber = ?	115	SELECT timestamp FROM biblio_metadata WHERE biblionumber = ?
116	UNION	116	UNION
117	SELECT timestamp FROM deleteditems WHERE biblionumber = ?	117	SELECT timestamp FROM deleteditems WHERE biblionumber = ?
118	UNION	118	UNION

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug19555-biblio_metadata-timestamp.perl (+17 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if( CheckVersion( $DBversion ) ) {
	3
	4	# or perform some test and warn
	5	unless( column_exists( 'biblio_metadata', 'timestamp' ) ) {
	6	$dbh->do( "ALTER TABLE biblio_metadata ADD COLUMN timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" );
	7	}
	8
	9	# or perform some test and warn
	10	unless( column_exists( 'deletedbiblio_metadata', 'timestamp' ) ) {
	11	$dbh->do( "ALTER TABLE deletedbiblio_metadata ADD COLUMN timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" );
	12	}
	13
	14	# Always end with this (adjust the bug info)
	15	SetVersion( $DBversion );
	16	print "Upgrade to $DBversion done (Bug 19555 - Add timestamps to biblio_metadata and deletedbiblio_metadata)\n";
	17	}