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 / +46 lines)
Lines 6407-6412 if ( CheckVersion($DBversion) ) { Link Here
6407
   SetVersion ($DBversion);
6407
   SetVersion ($DBversion);
6408
}
6408
}
6409
6409
6410
$DBversion = "3.11.00.XXX";
6411
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
6412
    #issues
6413
    $dbh->do(q{
6414
        ALTER TABLE `issues`
6415
            ADD KEY `itemnumber_idx` (`itemnumber`),
6416
            ADD KEY `branchcode_idx` (`branchcode`),
6417
            ADD KEY `issuingbranch_idx` (`issuingbranch`)
6418
    });
6419
    $dbh->do(q{
6420
        ALTER TABLE `old_issues`
6421
            ADD KEY `branchcode_idx` (`branchcode`),
6422
            ADD KEY `issuingbranch_idx` (`issuingbranch`)
6423
    });
6424
    #items
6425
    $dbh->do(q{
6426
        ALTER TABLE `items` ADD KEY `itype_idx` (`itype`)
6427
    });
6428
    $dbh->do(q{
6429
        ALTER TABLE `deleteditems` ADD KEY `itype_idx` (`itype`)
6430
    });
6431
    # biblioitems
6432
    $dbh->do(q{
6433
        ALTER TABLE `biblioitems` ADD KEY `itemtype_idx` (`itemtype`)
6434
    });
6435
    $dbh->do(q{
6436
        ALTER TABLE `deletedbiblioitems` ADD KEY `itemtype_idx` (`itemtype`)
6437
    });
6438
    # statistics
6439
    $dbh->do(q{
6440
        ALTER TABLE `statistics`
6441
            ADD KEY `branch_idx` (`branch`),
6442
            ADD KEY `proccode_idx` (`proccode`),
6443
            ADD KEY `type_idx` (`type`),
6444
            ADD KEY `usercode_idx` (`usercode`),
6445
            ADD KEY `itemnumber_idx` (`itemnumber`),
6446
            ADD KEY `itemtype_idx` (`itemtype`),
6447
            ADD KEY `borrowernumber_idx` (`borrowernumber`),
6448
            ADD KEY `associatedborrower_idx` (`associatedborrower`),
6449
            ADD KEY `ccode_idx` (`ccode`)
6450
    });
6451
6452
    print "Upgrade to $DBversion done (Bug 9681: Add some database indexes)\n";
6453
    SetVersion($DBversion);
6454
}
6455
6410
=head1 FUNCTIONS
6456
=head1 FUNCTIONS
6411
6457
6412
=head2 TableExists($table)
6458
=head2 TableExists($table)
6413
- 

Return to bug 9681