extended patron attributes tables & syspref (DB rev 081)

Added two tables and system preference to support
a new patron alternate ID and attributes feature.

A patron attribute (or extended patron attribute) is an
additional piece of information associated with a patron
record.  Each attribute has a type that specifies
whether the attribute is repeatable, can serve as
a unique identifier, can take a password, and
whether it can be used to search for patron records
in the staff interface.

The list of attribute types is controlled by the
superlibrarian.  Once an attribute type is defined,
values for that attribute can be added to the patron record
via the staff interface or the batch patron import.

Two uses of extended attributes are:

- defining additional unique identifiers, such as
  a campus student ID number, a library staff
  HR number, and so on.  These IDs can be used
  for searching or matching and overlaying records
  during a batch import.
- additional statistical categories.  For example,
  a library could define an attribute type for
  tracking the academic major of a student patron.
  Any number of attributes of this sort could be
  defined.

The extended attributes feature is completely optional.  If
the new syspref, ExtendPatronAttributes, is OFF, the patron
attributes tables will be ignored; it will not be possible
to display, edit, search for, or match on extended
attributes.

The tables are:

[1] borrower_attribute_types - store attribute types
    defined by the administrator.

    - code
    - description
    - repeatable (whether a patron record can have
      more than value of a given attribute type)
    - unique_id (whether values of this type
      must be unique within the database)
    - opac_display (whether values of this type
      can display in the patron details page in the OPAC)
    - staff_searchable (whether values of this type
      can be used to retrieve patron records in circulation)
    - password_allowed (if set, staff patron editor will
      allow a password to be associated with a value; this
      is mostly a hook for functionality to be implemented
      in the future.
    - authorised_value_category (code of an authorised_value
      category.  If one is specified, the staff patron
      editor will use a dropdown for setting values of this type)
[2] borrower_attributes - the actual attributes.
    - code (attribute type code, FK)
    - borrowernumber (link to patron, FK)
    - attribute (the value)
    - password (password associated with value)

Signed-off-by: Joshua Ferraro <jmf@liblime.com>

diff --git a/admin/systempreferences.pl b/admin/systempreferences.pl
index decbd77..b25b55e 100755 (executable)
--- a/admin/systempreferences.pl
+++ b/admin/systempreferences.pl
@@ -175,6 +175,7 @@ my %tabsysprefs;
     $tabsysprefs{NotifyBorrowerDeparture}="Patrons";
     $tabsysprefs{AddPatronLists}="Patrons";
     $tabsysprefs{PatronsPerPage}="Patrons";
+    $tabsysprefs{ExtendedPatronAttributes}="Patrons";
 
 # I18N/L10N
     $tabsysprefs{dateformat}="I18N/L10N";

diff --git a/installer/data/mysql/en/mandatory/sysprefs.sql b/installer/data/mysql/en/mandatory/sysprefs.sql
index 64c7582..ab7f551 100644 (file)
--- a/installer/data/mysql/en/mandatory/sysprefs.sql
+++ b/installer/data/mysql/en/mandatory/sysprefs.sql
@@ -183,3 +183,4 @@ INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
 
 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo');
 INSERT INTO `systempreferences` (variable, value,options,type, explanation) VALUES ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons');
+INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo');

diff --git a/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql b/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql
index bf5eb5b..ee843b6 100644 (file)
--- a/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql
+++ b/installer/data/mysql/fr-FR/1-Obligatoire/unimarc_standard_systemprefs.sql
@@ -185,3 +185,4 @@ INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACItemHolds','1','Si activé, les adhérents peuvent placer des réservations sur un exemplaire spécifique. Sinon, il ne peuvent que réserver le prochain disponible.','','YesNo');
 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo');
 INSERT INTO `systempreferences` (variable, value,options,type, explanation) VALUES ('AddPatronLists','categorycode','categorycode|category_type','Choice','Slectionner categorycode ou category_type permet d\'afficher la liste des catégories ou des types de catégories à l\'ajout d\'un lecteur');
+INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo');

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index f0130c5..2a4ea30 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -494,6 +494,41 @@ CREATE TABLE `borrowers` (
   CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+--
+-- Table structure for table `borrower_attribute_types`
+--
+
+DROP TABLE IF EXISTS `borrower_attribute_types`;
+CREATE TABLE `borrower_attribute_types` (
+  `code` varchar(10) NOT NULL,
+  `description` varchar(255) NOT NULL,
+  `repeatable` tinyint(1) NOT NULL default 0,
+  `unique_id` tinyint(1) NOT NULL default 0,
+  `opac_display` tinyint(1) NOT NULL default 0,
+  `password_allowed` tinyint(1) NOT NULL default 0,
+  `staff_searchable` tinyint(1) NOT NULL default 0,
+  `authorised_value_category` varchar(10) default NULL,
+  PRIMARY KEY  (`code`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `borrower_attributes`
+--
+
+DROP TABLE IF EXISTS `borrower_attributes`;
+CREATE TABLE `borrower_attributes` (
+  `borrowernumber` int(11) NOT NULL,
+  `code` varchar(10) NOT NULL,
+  `attribute` varchar(30) default NULL,
+  `password` varchar(30) default NULL,
+  KEY `borrowernumber` (`borrowernumber`),
+  KEY `code_attribute` (`code`, `attribute`),
+  CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
+    ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
+    ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
 --
 -- Table structure for table `branchcategories`
 --

diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 28be9a2..a54961e 100755 (executable)
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -1512,6 +1512,38 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
        SetVersion ($DBversion);
 }
 
+
+
+$DBversion = "3.00.00.081";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do("CREATE TABLE `borrower_attribute_types` (
+                `code` varchar(10) NOT NULL,
+                `description` varchar(255) NOT NULL,
+                `repeatable` tinyint(1) NOT NULL default 0,
+                `unique_id` tinyint(1) NOT NULL default 0,
+                `opac_display` tinyint(1) NOT NULL default 0,
+                `password_allowed` tinyint(1) NOT NULL default 0,
+                `staff_searchable` tinyint(1) NOT NULL default 0,
+                `authorised_value_category` varchar(10) default NULL,
+                PRIMARY KEY  (`code`)
+              ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+    $dbh->do("CREATE TABLE `borrower_attributes` (
+                `borrowernumber` int(11) NOT NULL,
+                `code` varchar(10) NOT NULL,
+                `attribute` varchar(30) default NULL,
+                `password` varchar(30) default NULL,
+                KEY `borrowernumber` (`borrowernumber`),
+                KEY `code_attribute` (`code`, `attribute`),
+                CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
+                    ON DELETE CASCADE ON UPDATE CASCADE,
+                CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
+                    ON DELETE CASCADE ON UPDATE CASCADE
+            ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+    $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
+    print "Upgrade to $DBversion done (added borrower_attributes and  borrower_attribute_types)\n";
+    SetVersion ($DBversion);
+}
+
 =item DropAllForeignKeys($table)
 
   Drop all foreign keys of the table $table

diff --git a/kohaversion.pl b/kohaversion.pl
index 2d83d26..a0100a1 100644 (file)
--- a/kohaversion.pl
+++ b/kohaversion.pl
@@ -10,7 +10,7 @@
 use strict;
 
 sub kohaversion {
-    our $VERSION = "3.00.00.080";
+    our $VERSION = "3.00.00.081";
     # version needs to be set this way
     # so that it can be picked up by Makefile.PL
     # during install