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 929-934
CREATE TABLE `items` ( -- holdings/item information
Link Here
|
929 |
`copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t) |
799 |
`copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t) |
930 |
`stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i) |
800 |
`stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i) |
931 |
`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. |
801 |
`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 |
`deleted_at` datetime DEFAULT NULL, -- timestamp of deletion |
932 |
PRIMARY KEY (`itemnumber`), |
803 |
PRIMARY KEY (`itemnumber`), |
933 |
UNIQUE KEY `itembarcodeidx` (`barcode`), |
804 |
UNIQUE KEY `itembarcodeidx` (`barcode`), |
934 |
KEY `itemstocknumberidx` (`stocknumber`), |
805 |
KEY `itemstocknumberidx` (`stocknumber`), |
Lines 3938-3943
CREATE TABLE biblio_metadata (
Link Here
|
3938 |
`marcflavour` VARCHAR(16) NOT NULL, |
3809 |
`marcflavour` VARCHAR(16) NOT NULL, |
3939 |
`metadata` LONGTEXT NOT NULL, |
3810 |
`metadata` LONGTEXT NOT NULL, |
3940 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
3811 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
|
|
3812 |
`deleted_at` datetime DEFAULT NULL, -- timestamp of deletion |
3941 |
PRIMARY KEY(id), |
3813 |
PRIMARY KEY(id), |
3942 |
UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), |
3814 |
UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), |
3943 |
CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, |
3815 |
CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, |
Lines 3945-3968
CREATE TABLE biblio_metadata (
Link Here
|
3945 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
3817 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
3946 |
|
3818 |
|
3947 |
-- |
3819 |
-- |
3948 |
-- Table structure for table `deletedbiblio_metadata` |
|
|
3949 |
-- |
3950 |
|
3951 |
DROP TABLE IF EXISTS `deletedbiblio_metadata`; |
3952 |
CREATE TABLE deletedbiblio_metadata ( |
3953 |
`id` INT(11) NOT NULL AUTO_INCREMENT, |
3954 |
`biblionumber` INT(11) NOT NULL, |
3955 |
`format` VARCHAR(16) NOT NULL, |
3956 |
`marcflavour` VARCHAR(16) NOT NULL, |
3957 |
`metadata` LONGTEXT NOT NULL, |
3958 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, |
3959 |
PRIMARY KEY(id), |
3960 |
UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`), |
3961 |
CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, |
3962 |
KEY `timestamp` (`timestamp`) |
3963 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
3964 |
|
3965 |
-- |
3966 |
-- Table structure for table 'club_templates' |
3820 |
-- Table structure for table 'club_templates' |
3967 |
-- |
3821 |
-- |
3968 |
|
3822 |
|
3969 |
- |
|
|