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

(-)a/installer/data/mysql/kohastructure.sql (-2 / +20 lines)
Lines 184-189 CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Link Here
184
  PRIMARY KEY  (`biblioitemnumber`),
184
  PRIMARY KEY  (`biblioitemnumber`),
185
  KEY `bibinoidx` (`biblioitemnumber`),
185
  KEY `bibinoidx` (`biblioitemnumber`),
186
  KEY `bibnoidx` (`biblionumber`),
186
  KEY `bibnoidx` (`biblionumber`),
187
  KEY `itemtype_idx` (`itemtype`),
187
  KEY `isbn` (`isbn`),
188
  KEY `isbn` (`isbn`),
188
  KEY `issn` (`issn`),
189
  KEY `issn` (`issn`),
189
  KEY `publishercode` (`publishercode`),
190
  KEY `publishercode` (`publishercode`),
Lines 657-662 CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t Link Here
657
  PRIMARY KEY  (`biblioitemnumber`),
658
  PRIMARY KEY  (`biblioitemnumber`),
658
  KEY `bibinoidx` (`biblioitemnumber`),
659
  KEY `bibinoidx` (`biblioitemnumber`),
659
  KEY `bibnoidx` (`biblionumber`),
660
  KEY `bibnoidx` (`biblionumber`),
661
  KEY `itemtype_idx` (`itemtype`),
660
  KEY `isbn` (`isbn`),
662
  KEY `isbn` (`isbn`),
661
  KEY `publishercode` (`publishercode`)
663
  KEY `publishercode` (`publishercode`)
662
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
664
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Lines 791-797 CREATE TABLE `deleteditems` ( Link Here
791
  KEY `delitembinoidx` (`biblioitemnumber`),
793
  KEY `delitembinoidx` (`biblioitemnumber`),
792
  KEY `delitembibnoidx` (`biblionumber`),
794
  KEY `delitembibnoidx` (`biblionumber`),
793
  KEY `delhomebranch` (`homebranch`),
795
  KEY `delhomebranch` (`homebranch`),
794
  KEY `delholdingbranch` (`holdingbranch`)
796
  KEY `delholdingbranch` (`holdingbranch`),
797
  KEY `itype_idx` (`itype`)
795
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
798
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
796
799
797
--
800
--
Lines 988-993 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
988
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
991
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
989
  `issuedate` datetime default NULL, -- date the item was checked out or issued
992
  `issuedate` datetime default NULL, -- date the item was checked out or issued
990
  KEY `issuesborridx` (`borrowernumber`),
993
  KEY `issuesborridx` (`borrowernumber`),
994
  KEY `itemnumber_idx` (`itemnumber`),
995
  KEY `branchcode_idx` (`branchcode`),
996
  KEY `issuingbranch_idx` (`issuingbranch`),
991
  KEY `bordate` (`borrowernumber`,`timestamp`),
997
  KEY `bordate` (`borrowernumber`,`timestamp`),
992
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
998
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
993
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
999
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
Lines 1080-1085 CREATE TABLE `items` ( -- holdings/item information Link Here
1080
  KEY `itemcallnumber` (`itemcallnumber`),
1086
  KEY `itemcallnumber` (`itemcallnumber`),
1081
  KEY `items_location` (`location`),
1087
  KEY `items_location` (`location`),
1082
  KEY `items_ccode` (`ccode`),
1088
  KEY `items_ccode` (`ccode`),
1089
  KEY `itype_idx` (`itype`),
1083
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1090
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1084
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1091
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1085
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1092
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
Lines 1458-1463 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1458
  `issuedate` datetime default NULL, -- date the item was checked out or issued
1465
  `issuedate` datetime default NULL, -- date the item was checked out or issued
1459
  KEY `old_issuesborridx` (`borrowernumber`),
1466
  KEY `old_issuesborridx` (`borrowernumber`),
1460
  KEY `old_issuesitemidx` (`itemnumber`),
1467
  KEY `old_issuesitemidx` (`itemnumber`),
1468
  KEY `branchcode_idx` (`branchcode`),
1469
  KEY `issuingbranch_idx` (`issuingbranch`),
1461
  KEY `old_bordate` (`borrowernumber`,`timestamp`),
1470
  KEY `old_bordate` (`borrowernumber`,`timestamp`),
1462
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1471
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1463
    ON DELETE SET NULL ON UPDATE SET NULL,
1472
    ON DELETE SET NULL ON UPDATE SET NULL,
Lines 1851-1857 CREATE TABLE `statistics` ( -- information related to transactions (circulation Link Here
1851
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
1860
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
1852
  `associatedborrower` int(11) default NULL,
1861
  `associatedborrower` int(11) default NULL,
1853
  `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
1862
  `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
1854
  KEY `timeidx` (`datetime`)
1863
  KEY `timeidx` (`datetime`),
1864
  KEY `branch_idx` (`branch`),
1865
  KEY `proccode_idx` (`proccode`),
1866
  KEY `type_idx` (`type`),
1867
  KEY `usercode_idx` (`usercode`),
1868
  KEY `itemnumber_idx` (`itemnumber`),
1869
  KEY `itemtype_idx` (`itemtype`),
1870
  KEY `borrowernumber_idx` (`borrowernumber`),
1871
  KEY `associatedborrower_idx` (`associatedborrower`),
1872
  KEY `ccode_idx` (`ccode`)
1855
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1873
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1856
1874
1857
--
1875
--
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +45 lines)
Lines 6428-6433 if ( CheckVersion($DBversion) ) { Link Here
6428
    SetVersion($DBversion);
6428
    SetVersion($DBversion);
6429
}
6429
}
6430
6430
6431
$DBversion = "3.11.00.XXX";
6432
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
6433
    #issues
6434
    $dbh->do(q{
6435
        ALTER TABLE `issues`
6436
            ADD KEY `itemnumber_idx` (`itemnumber`),
6437
            ADD KEY `branchcode_idx` (`branchcode`),
6438
            ADD KEY `issuingbranch_idx` (`issuingbranch`)
6439
    });
6440
    $dbh->do(q{
6441
        ALTER TABLE `old_issues`
6442
            ADD KEY `branchcode_idx` (`branchcode`),
6443
            ADD KEY `issuingbranch_idx` (`issuingbranch`)
6444
    });
6445
    #items
6446
    $dbh->do(q{
6447
        ALTER TABLE `items` ADD KEY `itype_idx` (`itype`)
6448
    });
6449
    $dbh->do(q{
6450
        ALTER TABLE `deleteditems` ADD KEY `itype_idx` (`itype`)
6451
    });
6452
    # biblioitems
6453
    $dbh->do(q{
6454
        ALTER TABLE `biblioitems` ADD KEY `itemtype_idx` (`itemtype`)
6455
    });
6456
    $dbh->do(q{
6457
        ALTER TABLE `deletedbiblioitems` ADD KEY `itemtype_idx` (`itemtype`)
6458
    });
6459
    # statistics
6460
    $dbh->do(q{
6461
        ALTER TABLE `statistics`
6462
            ADD KEY `branch_idx` (`branch`),
6463
            ADD KEY `proccode_idx` (`proccode`),
6464
            ADD KEY `type_idx` (`type`),
6465
            ADD KEY `usercode_idx` (`usercode`),
6466
            ADD KEY `itemnumber_idx` (`itemnumber`),
6467
            ADD KEY `itemtype_idx` (`itemtype`),
6468
            ADD KEY `borrowernumber_idx` (`borrowernumber`),
6469
            ADD KEY `associatedborrower_idx` (`associatedborrower`),
6470
            ADD KEY `ccode_idx` (`ccode`)
6471
    });
6472
6473
    print "Upgrade to $DBversion done (Bug 9681: Add some database indexes)\n";
6474
    SetVersion($DBversion);
6475
}
6431
6476
6432
=head1 FUNCTIONS
6477
=head1 FUNCTIONS
6433
6478
6434
- 

Return to bug 9681