Lines 1737-1823
CREATE TABLE `patronimage` ( -- information related to patron images
Link Here
|
1737 |
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE |
1737 |
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE |
1738 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
1738 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
1739 |
|
1739 |
|
|
|
1740 |
-- |
1741 |
-- Table structure for table `holds` |
1742 |
-- |
1743 |
|
1744 |
DROP TABLE IF EXISTS `holds`; |
1745 |
CREATE TABLE `holds` ( |
1746 |
-- information related to holds in Koha |
1747 |
`id` INT(11) NOT NULL AUTO_INCREMENT, |
1748 |
-- primary key |
1749 |
`patron_id` INT(11) DEFAULT NULL, |
1750 |
-- foreign key from the borrowers table defining which patron this hold is for |
1751 |
`pickup_library_id` VARCHAR(10) DEFAULT NULL, |
1752 |
-- foreign key from the branches table defining which branch the patron wishes to pick this hold up at |
1753 |
`placing_date` DATE DEFAULT NULL, |
1754 |
-- the date the hold was placed |
1755 |
`biblio_id` INT(11) DEFAULT NULL, |
1756 |
-- foreign key from the biblio table defining which bib record this hold is on |
1757 |
`item_id` INT(11) DEFAULT NULL, |
1758 |
-- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with |
1759 |
`item_type` VARCHAR(10) DEFAULT NULL, |
1760 |
-- If record level hold, the optional itemtype of the item the patron is requesting |
1761 |
`item_level` TINYINT(1) NOT NULL DEFAULT 0, |
1762 |
-- Is the hold was placed at item level |
1763 |
`completion_date` DATE DEFAULT NULL, |
1764 |
-- the date this hold was completed (fulfilled or cancelled) |
1765 |
`notes` LONGTEXT, |
1766 |
-- notes related to this hold |
1767 |
`priority` SMALLINT(6) NOT NULL DEFAULT 1, |
1768 |
-- where in the queue the patron sits |
1769 |
`status` ENUM('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') DEFAULT 'placed' NOT NULL, |
1770 |
-- the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') |
1771 |
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
1772 |
-- the date and time this hold was last updated |
1773 |
`waiting_date` DATE DEFAULT NULL, |
1774 |
-- the date the item was marked as waiting for the patron at the library |
1775 |
`expiration_date` DATE DEFAULT NULL, |
1776 |
-- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) |
1777 |
`lowest_priority` TINYINT(1) NOT NULL DEFAULT 0, |
1778 |
-- If it was assigned the lowest priority |
1779 |
`suspended` TINYINT(1) NOT NULL DEFAULT 0, |
1780 |
-- If it is suspended |
1781 |
`suspended_until_date` DATETIME NULL DEFAULT NULL, |
1782 |
-- Date until it is suspended |
1783 |
`completed` TINYINT(1) NOT NULL DEFAULT 0, |
1784 |
-- If it has been completed (i.e. either 'fulfilled' or 'cancelled') |
1785 |
PRIMARY KEY (`id`), |
1786 |
KEY `priority_status_idx` (`priority`, `status`), |
1787 |
KEY `patron_id` (`patron_id`), |
1788 |
KEY `biblio_id` (`biblio_id`), |
1789 |
KEY `item_id` (`item_id`), |
1790 |
KEY `pickup_library_id` (`pickup_library_id`), |
1791 |
KEY `item_type` (`item_type`), |
1792 |
CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
1793 |
CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
1794 |
CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
1795 |
CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, |
1796 |
CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE |
1797 |
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; |
1798 |
|
1740 |
-- |
1799 |
-- |
1741 |
-- Table structure for table `reserves` |
1800 |
-- Table structure for table `reserves` |
1742 |
-- |
1801 |
-- |
1743 |
|
1802 |
|
1744 |
DROP TABLE IF EXISTS `reserves`; |
1803 |
CREATE VIEW `reserves` |
1745 |
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha |
1804 |
AS |
1746 |
`reserve_id` int(11) NOT NULL auto_increment, -- primary key |
1805 |
SELECT |
1747 |
`borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for |
1806 |
`id` AS `reserve_id`, |
1748 |
`reservedate` date default NULL, -- the date the hold was placed |
1807 |
`patron_id` AS `borrowernumber`, |
1749 |
`biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on |
1808 |
`placing_date` AS `reservedate`, |
1750 |
`branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at |
1809 |
`biblio_id` AS `biblionumber`, |
1751 |
`notificationdate` date default NULL, -- currently unused |
1810 |
`pickup_library_id` AS `branchcode`, |
1752 |
`reminderdate` date default NULL, -- currently unused |
1811 |
NULL AS `cancellationdate`, |
1753 |
`cancellationdate` date default NULL, -- the date this hold was cancelled |
1812 |
`notes` AS `reservenotes`, |
1754 |
`reservenotes` LONGTEXT, -- notes related to this hold |
1813 |
`priority`, |
1755 |
`priority` smallint(6) NOT NULL DEFAULT 1, -- where in the queue the patron sits |
1814 |
CASE |
1756 |
`found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed |
1815 |
when `status` = 'waiting' then |
1757 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated |
1816 |
'W' |
1758 |
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with |
1817 |
when `status` = 'in_transit' then |
1759 |
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library |
1818 |
'T' |
1760 |
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) |
1819 |
else |
1761 |
`lowestPriority` tinyint(1) NOT NULL DEFAULT 0, |
1820 |
NULL |
1762 |
`suspend` tinyint(1) NOT NULL DEFAULT 0, |
1821 |
END AS `found`, |
1763 |
`suspend_until` DATETIME NULL DEFAULT NULL, |
1822 |
`timestamp`, |
1764 |
`itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting |
1823 |
`item_id` AS `itemnumber`, |
1765 |
`item_level_hold` tinyint(1) NOT NULL DEFAULT 0, -- Is the hpld placed at item level |
1824 |
`waiting_date` AS `waitingdate`, |
1766 |
PRIMARY KEY (`reserve_id`), |
1825 |
`expiration_date` AS `expirationdate`, |
1767 |
KEY priorityfoundidx (priority,found), |
1826 |
`lowest_priority` AS `lowestPriority`, |
1768 |
KEY `borrowernumber` (`borrowernumber`), |
1827 |
`suspended` AS `suspend`, |
1769 |
KEY `biblionumber` (`biblionumber`), |
1828 |
`suspended_until_date` AS `suspend_until`, |
1770 |
KEY `itemnumber` (`itemnumber`), |
1829 |
`item_type` AS `itemtype`, |
1771 |
KEY `branchcode` (`branchcode`), |
1830 |
`item_level` AS `item_level_hold` |
1772 |
KEY `itemtype` (`itemtype`), |
1831 |
FROM `holds` |
1773 |
CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
1832 |
WHERE completed=0; |
1774 |
CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
1833 |
|
1775 |
CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
1834 |
-- |
1776 |
CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
1835 |
-- Table structure for view `old_reserves` |
1777 |
CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE |
1836 |
-- |
1778 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
1837 |
|
1779 |
|
1838 |
CREATE VIEW `old_reserves` |
1780 |
-- |
1839 |
AS |
1781 |
-- Table structure for table `old_reserves` |
1840 |
SELECT |
1782 |
-- |
1841 |
`id` AS `reserve_id`, |
1783 |
|
1842 |
`patron_id` AS `borrowernumber`, |
1784 |
DROP TABLE IF EXISTS `old_reserves`; |
1843 |
`placing_date` AS `reservedate`, |
1785 |
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) |
1844 |
`biblio_id` AS `biblionumber`, |
1786 |
`reserve_id` int(11) NOT NULL, -- primary key |
1845 |
`pickup_library_id` AS `branchcode`, |
1787 |
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for |
1846 |
CASE |
1788 |
`reservedate` date default NULL, -- the date the hold was places |
1847 |
WHEN `status` = 'cancelled' THEN |
1789 |
`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on |
1848 |
`completion_date` |
1790 |
`branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at |
1849 |
ELSE |
1791 |
`notificationdate` date default NULL, -- currently unused |
1850 |
NULL |
1792 |
`reminderdate` date default NULL, -- currently unused |
1851 |
END AS `cancellationdate`, |
1793 |
`cancellationdate` date default NULL, -- the date this hold was cancelled |
1852 |
`notes` AS `reservenotes`, |
1794 |
`reservenotes` LONGTEXT, -- notes related to this hold |
1853 |
`priority`, |
1795 |
`priority` smallint(6) NOT NULL DEFAULT 1, -- where in the queue the patron sits |
1854 |
CASE |
1796 |
`found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed |
1855 |
WHEN `status` = 'fulfilled' THEN |
1797 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated |
1856 |
'F' |
1798 |
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with |
1857 |
ELSE |
1799 |
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library |
1858 |
NULL |
1800 |
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) |
1859 |
END AS `found`, |
1801 |
`lowestPriority` tinyint(1) NOT NULL DEFAULT 0, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no) |
1860 |
`timestamp`, |
1802 |
`suspend` tinyint(1) NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no) |
1861 |
`item_id` AS `itemnumber`, |
1803 |
`suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely) |
1862 |
`waiting_date` AS `waitingdate`, |
1804 |
`itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting |
1863 |
`expiration_date` AS `expirationdate`, |
1805 |
`item_level_hold` tinyint(1) NOT NULL DEFAULT 0, -- Is the hpld placed at item level |
1864 |
`lowest_priority` AS `lowestPriority`, |
1806 |
PRIMARY KEY (`reserve_id`), |
1865 |
`suspended` AS `suspend`, |
1807 |
KEY `old_reserves_borrowernumber` (`borrowernumber`), |
1866 |
`suspended_until_date` AS `suspend_until`, |
1808 |
KEY `old_reserves_biblionumber` (`biblionumber`), |
1867 |
`item_type` AS `itemtype`, |
1809 |
KEY `old_reserves_itemnumber` (`itemnumber`), |
1868 |
`item_level` AS `item_level_hold` |
1810 |
KEY `old_reserves_branchcode` (`branchcode`), |
1869 |
FROM `holds` |
1811 |
KEY `old_reserves_itemtype` (`itemtype`), |
1870 |
WHERE completed=1; |
1812 |
CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
|
|
1813 |
ON DELETE SET NULL ON UPDATE SET NULL, |
1814 |
CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) |
1815 |
ON DELETE SET NULL ON UPDATE SET NULL, |
1816 |
CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) |
1817 |
ON DELETE SET NULL ON UPDATE SET NULL, |
1818 |
CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
1819 |
ON DELETE SET NULL ON UPDATE SET NULL |
1820 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
1821 |
|
1871 |
|
1822 |
-- |
1872 |
-- |
1823 |
-- Table structure for table `reviews` |
1873 |
-- Table structure for table `reviews` |
Lines 4108-4114
CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
Link Here
|
4108 |
-- KEY club_hold_id (club_hold_id), |
4158 |
-- KEY club_hold_id (club_hold_id), |
4109 |
CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE, |
4159 |
CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE, |
4110 |
CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
4160 |
CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
4111 |
CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE |
4161 |
CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES holds (id) ON DELETE CASCADE ON UPDATE CASCADE |
4112 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
4162 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
4113 |
|
4163 |
|
4114 |
-- |
4164 |
-- |
4115 |
- |
|
|