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

(-)a/installer/data/mysql/kohastructure.sql (-151 / +4 lines)
Lines 140-145 CREATE TABLE `biblio` ( -- table that stores bibliographic information Link Here
140
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
140
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
141
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
141
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
142
  `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
142
  `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
143
  `deleted_at` datetime DEFAULT NULL, -- timestamp of deletion
143
  PRIMARY KEY  (`biblionumber`),
144
  PRIMARY KEY  (`biblionumber`),
144
  KEY `blbnoidx` (`biblionumber`)
145
  KEY `blbnoidx` (`biblionumber`)
145
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
146
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 193-198 CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Link Here
193
  `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
194
  `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
194
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
195
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
195
  `totalissues` int(10),
196
  `totalissues` int(10),
197
  `deleted_at` datetime DEFAULT NULL, -- timestamp of deletion
196
  PRIMARY KEY  (`biblioitemnumber`),
198
  PRIMARY KEY  (`biblioitemnumber`),
197
  KEY `bibinoidx` (`biblioitemnumber`),
199
  KEY `bibinoidx` (`biblioitemnumber`),
198
  KEY `bibnoidx` (`biblionumber`),
200
  KEY `bibnoidx` (`biblionumber`),
Lines 463-538 CREATE TABLE `currency` ( Link Here
463
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
465
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
464
466
465
--
467
--
466
-- Table structure for table `deletedbiblio`
467
--
468
469
DROP TABLE IF EXISTS `deletedbiblio`;
470
CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted
471
  `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
472
  `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
473
  `author` LONGTEXT, -- statement of responsibility from MARC record (100$a in MARC21)
474
  `title` LONGTEXT, -- title (without the subtitle) from the MARC record (245$a in MARC21)
475
  `unititle` LONGTEXT, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
476
  `notes` LONGTEXT, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
477
  `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
478
  `seriestitle` LONGTEXT,
479
  `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
480
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
481
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
482
  `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
483
  PRIMARY KEY  (`biblionumber`),
484
  KEY `blbnoidx` (`biblionumber`)
485
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
486
487
--
488
-- Table structure for table `deletedbiblioitems`
489
--
490
491
DROP TABLE IF EXISTS `deletedbiblioitems`;
492
CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records that have been deleted
493
  `biblioitemnumber` int(11) NOT NULL default 0, -- primary key, unique identifier assigned by Koha
494
  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
495
  `volume` LONGTEXT,
496
  `number` LONGTEXT,
497
  `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
498
  `isbn` LONGTEXT default NULL, -- ISBN (MARC21 020$a)
499
  `issn` LONGTEXT default NULL, -- ISSN (MARC21 022$a)
500
  `ean` LONGTEXT default NULL,
501
  `publicationyear` MEDIUMTEXT,
502
  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
503
  `volumedate` date default NULL,
504
  `volumedesc` MEDIUMTEXT, -- volume information (MARC21 362$a)
505
  `collectiontitle` LONGTEXT default NULL,
506
  `collectionissn` MEDIUMTEXT default NULL,
507
  `collectionvolume` LONGTEXT default NULL,
508
  `editionstatement` MEDIUMTEXT default NULL,
509
  `editionresponsibility` MEDIUMTEXT default NULL,
510
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
511
  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
512
  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
513
  `notes` LONGTEXT,
514
  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
515
  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
516
  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
517
  `url` MEDIUMTEXT default NULL, -- url (MARC21 856$u)
518
  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
519
  `cn_class` varchar(30) default NULL,
520
  `cn_item` varchar(10) default NULL,
521
  `cn_suffix` varchar(10) default NULL,
522
  `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
523
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
524
  `totalissues` int(10),
525
  PRIMARY KEY  (`biblioitemnumber`),
526
  KEY `bibinoidx` (`biblioitemnumber`),
527
  KEY `bibnoidx` (`biblionumber`),
528
  KEY `itemtype_idx` (`itemtype`),
529
  KEY `isbn` (`isbn`(255)),
530
  KEY `ean` (`ean`(255)),
531
  KEY `publishercode` (`publishercode` (191)),
532
  KEY `timestamp` (`timestamp`)
533
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
534
535
--
536
-- Table structure for table `deletedborrowers`
468
-- Table structure for table `deletedborrowers`
537
--
469
--
538
470
Lines 618-685 CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower Link Here
618
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
550
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
619
551
620
--
552
--
621
-- Table structure for table `deleteditems`
622
--
623
624
DROP TABLE IF EXISTS `deleteditems`;
625
CREATE TABLE `deleteditems` (
626
  `itemnumber` int(11) NOT NULL default 0, -- primary key and unique identifier added by Koha
627
  `biblionumber` int(11) NOT NULL default 0, -- foreign key from biblio table used to link this item to the right bib record
628
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
629
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
630
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
631
  `booksellerid` LONGTEXT default NULL, -- where the item was purchased (MARC21 952$e)
632
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
633
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
634
  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
635
  `replacementpricedate` date default NULL, -- the date the price is effective from (MARC21 952$w)
636
  `datelastborrowed` date default NULL, -- the date the item was last checked out
637
  `datelastseen` date default NULL, -- the date the item was last see (usually the last time the barcode was scanned or inventory was done)
638
  `stack` tinyint(1) default NULL,
639
  `notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
640
  `damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
641
  `damaged_on` datetime DEFAULT NULL, -- the date and time an item was last marked as damaged, NULL if not damaged
642
  `itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
643
  `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
644
  `withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
645
  `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn
646
  `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
647
  `coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
648
  `issues` smallint(6) default NULL, -- number of times this item has been checked out
649
  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
650
  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
651
  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
652
  `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$x)
653
  `itemnotes_nonpublic` LONGTEXT default NULL,
654
  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
655
  `paidfor` LONGTEXT,
656
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
657
  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
658
  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
659
  `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out)
660
  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
661
  `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
662
  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
663
  `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3)
664
  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
665
  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
666
  `more_subfields_xml` LONGTEXT default NULL, -- additional 952 subfields in XML format
667
  `enumchron` MEDIUMTEXT default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
668
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
669
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
670
  `new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
671
  PRIMARY KEY  (`itemnumber`),
672
  KEY `delitembarcodeidx` (`barcode`),
673
  KEY `delitemstocknumberidx` (`stocknumber`),
674
  KEY `delitembinoidx` (`biblioitemnumber`),
675
  KEY `delitembibnoidx` (`biblionumber`),
676
  KEY `delhomebranch` (`homebranch`),
677
  KEY `delholdingbranch` (`holdingbranch`),
678
  KEY `itype_idx` (`itype`),
679
  KEY `timestamp` (`timestamp`)
680
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
681
682
--
683
-- Table structure for table `export_format`
553
-- Table structure for table `export_format`
684
--
554
--
685
555
Lines 930-935 CREATE TABLE `items` ( -- holdings/item information Link Here
930
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
800
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
931
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
801
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
932
  `new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
802
  `new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
803
  `deleted_at` datetime DEFAULT NULL, -- timestamp of deletion
933
  PRIMARY KEY  (`itemnumber`),
804
  PRIMARY KEY  (`itemnumber`),
934
  UNIQUE KEY `itembarcodeidx` (`barcode`),
805
  UNIQUE KEY `itembarcodeidx` (`barcode`),
935
  KEY `itemstocknumberidx` (`stocknumber`),
806
  KEY `itemstocknumberidx` (`stocknumber`),
Lines 3962-3967 CREATE TABLE biblio_metadata ( Link Here
3962
    `marcflavour` VARCHAR(16) NOT NULL,
3833
    `marcflavour` VARCHAR(16) NOT NULL,
3963
    `metadata` LONGTEXT NOT NULL,
3834
    `metadata` LONGTEXT NOT NULL,
3964
    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3835
    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3836
    `deleted_at` datetime DEFAULT NULL, -- timestamp of deletion
3965
    PRIMARY KEY(id),
3837
    PRIMARY KEY(id),
3966
    UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
3838
    UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
3967
    CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
3839
    CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
Lines 3969-3992 CREATE TABLE biblio_metadata ( Link Here
3969
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3841
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3970
3842
3971
--
3843
--
3972
-- Table structure for table `deletedbiblio_metadata`
3973
--
3974
3975
DROP TABLE IF EXISTS `deletedbiblio_metadata`;
3976
CREATE TABLE deletedbiblio_metadata (
3977
    `id` INT(11) NOT NULL AUTO_INCREMENT,
3978
    `biblionumber` INT(11) NOT NULL,
3979
    `format` VARCHAR(16) NOT NULL,
3980
    `marcflavour` VARCHAR(16) NOT NULL,
3981
    `metadata` LONGTEXT NOT NULL,
3982
    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3983
    PRIMARY KEY(id),
3984
    UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
3985
    CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
3986
    KEY `timestamp` (`timestamp`)
3987
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3988
3989
--
3990
-- Table structure for table 'club_templates'
3844
-- Table structure for table 'club_templates'
3991
--
3845
--
3992
3846
3993
- 

Return to bug 20271