|
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 3939-3944
CREATE TABLE biblio_metadata (
Link Here
|
| 3939 |
`marcflavour` VARCHAR(16) NOT NULL, |
3810 |
`marcflavour` VARCHAR(16) NOT NULL, |
| 3940 |
`metadata` LONGTEXT NOT NULL, |
3811 |
`metadata` LONGTEXT NOT NULL, |
| 3941 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
3812 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
|
|
3813 |
`deleted_at` datetime DEFAULT NULL, -- timestamp of deletion |
| 3942 |
PRIMARY KEY(id), |
3814 |
PRIMARY KEY(id), |
| 3943 |
UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), |
3815 |
UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), |
| 3944 |
CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, |
3816 |
CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, |
|
Lines 3946-3969
CREATE TABLE biblio_metadata (
Link Here
|
| 3946 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
3818 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
| 3947 |
|
3819 |
|
| 3948 |
-- |
3820 |
-- |
| 3949 |
-- Table structure for table `deletedbiblio_metadata` |
|
|
| 3950 |
-- |
| 3951 |
|
| 3952 |
DROP TABLE IF EXISTS `deletedbiblio_metadata`; |
| 3953 |
CREATE TABLE deletedbiblio_metadata ( |
| 3954 |
`id` INT(11) NOT NULL AUTO_INCREMENT, |
| 3955 |
`biblionumber` INT(11) NOT NULL, |
| 3956 |
`format` VARCHAR(16) NOT NULL, |
| 3957 |
`marcflavour` VARCHAR(16) NOT NULL, |
| 3958 |
`metadata` LONGTEXT NOT NULL, |
| 3959 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
| 3960 |
PRIMARY KEY(id), |
| 3961 |
UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), |
| 3962 |
CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3963 |
KEY `timestamp` (`timestamp`) |
| 3964 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
| 3965 |
|
| 3966 |
-- |
| 3967 |
-- Table structure for table 'club_templates' |
3821 |
-- Table structure for table 'club_templates' |
| 3968 |
-- |
3822 |
-- |
| 3969 |
|
3823 |
|
| 3970 |
- |
|
|