1 = Migrating Patron Data =
6 This section will explain the task of migrating your patron data from comma
7 delimited files into Evergreen. It does not deal with the process of exporting
8 from the non-Evergreen system since this process may vary depending on where you
9 are extracting your patron records. Patron could come from an ILS or it could
10 come from a student database in the case of academic records.
12 When importing records into Evergreen you will need to populate 3 tables in your
15 * actor.usr - The main table for user data
16 * actor.card - Stores the barcode for users; Users can have more than 1 card but
17 only 1 can be active at a given time;
18 * actor.usr_address - Used for storing address information; A user can
19 have more than one address.
21 Before following the procedures below to import patron data into Evergreen, it
22 is a good idea to examine the fields in these tables in order to decide on a
23 strategy for data to include in your import. It is important to understand the
24 data types and constraints on each field.
26 . Export the patron data from your existing ILS or from another source into a
27 comma delimited file. The comma delimited file used for importing the records
28 should use Unicode (UTF8) character encoding.
30 . Create a staging table. A staging table will allow you to tweak the data before
31 importing. Here is an example sql statement:
34 ----------------------------------
35 CREATE TABLE students (
36 student_id int, barcode text, last_name text, first_name text, email text,
37 address_type text, street1 text, street2 text,
38 city text, province text, country text, postal_code text, phone text, profile
39 int DEFAULT 2, ident_type int, home_ou int, claims_returned_count int DEFAULT
40 0, usrname text, net_access_level int DEFAULT 2, password text
42 ----------------------------------
44 NOTE: The _default_ variables allow you to set default for your library or to populate
45 required fields in Evergreen if your data includes NULL values.
47 The data field profile in the above SQL script refers to the user group and should be an
48 integer referencing the id field in permission.grp_tree. Setting this value will affect
49 the permissions for the user. See the values in permission.grp_tree for possibilities.
51 ident_type is the identification type used for identifying users. This is a integer value
52 referencing config.identification_type and should match the id values of that table. The
53 default values are 1 for Drivers License, 2 for SSN or 3 for other.
55 home_ou is the home organizational unit for the user. This value needs to match the
56 corresponding id in the actor.org_unit table.
58 . Copy records into staging table from a comma delimited file.
61 ----------------------------------
62 COPY students (student_id, last_name, first_name, email, address_type, street1, street2,
63 city, province, country, postal_code, phone)
64 FROM '/home/opensrf/patrons.csv'
66 ----------------------------------
68 The script will vary depending on the format of your patron load file (patrons.csv).
70 . Formatting of some fields to fit Evergreen filed formatting may be required. Here is an example
71 of sql to adjust phone numbers in the staging table to fit the evergreen field:
74 ----------------------------------
75 UPDATE students phone = replace(replace(replace(rpad(substring(phone from 1 for 9), 10, '-') ||
76 substring(phone from 10), '(', ''), ')', ''), ' ', '-');
77 ----------------------------------
79 Data ``massaging'' will be required to fit formats used in Evergreen.
81 . Insert records from the staging table into the actor.usr Evergreen table:
84 ----------------------------------
85 INSERT INTO actor.usr (
86 profile, usrname, email, passwd, ident_type, ident_value, first_given_name,
87 family_name, day_phone, home_ou, claims_returned_count, net_access_level)
88 SELECT profile, students.usrname, email, password, ident_type, student_id,
89 first_name, last_name, phone, home_ou, claims_returned_count, net_access_level
91 ----------------------------------
93 . Insert records into actor.card from actor.usr .
96 ----------------------------------
97 INSERT INTO actor.card (usr, barcode)
98 SELECT actor.usr.id, students.barcode
101 ON students.usrname = actor.usr.usrname;
102 ----------------------------------
104 This assumes a one to one card patron relationship. If your patron data import has multiple cards
105 assigned to one patron more complex import scripts may be required which look
106 for inactive or active flags.
108 . Update actor.usr.card field with actor.card.id to associate active card with the user:
111 ----------------------------------
113 SET card = actor.card.id
115 WHERE actor.card.usr = actor.usr.id;
116 ----------------------------------
118 . Insert records into actor.usr_address to add address information for users:
121 ----------------------------------
122 INSERT INTO actor.usr_address (usr, street1, street2, city, state, country, post_code)
123 SELECT actor.usr.id, students.street1, students.street2, students.city, students.province,
124 students.country, students.postal_code
126 INNER JOIN actor.usr ON students.usrname = actor.usr.usrname;
127 ----------------------------------
129 . Update actor.usr.address with address id from address table.
132 ----------------------------------
134 SET mailing_address = actor.usr_address.id, billing_address = actor.usr_address.id
135 FROM actor.usr_address
136 WHERE actor.usr.id = actor.usr_address.usr;
137 ----------------------------------
139 This assumes 1 address per patron. More complex scenarios may require more sophisticated SQL.
141 == Creating an sql Script for Importing Patrons ==
143 The procedure for importing patron can be automated with the help of an sql script. Follow these
144 steps to create an import script:
146 . Create an new file and name it import.sql
147 . Edit the file to look similar to this:
150 ----------------------------------
153 -- Remove any old staging table.
154 DROP TABLE IF EXISTS students;
156 -- Create staging table.
157 CREATE TABLE students (
158 student_id text, barcode text, last_name text, first_name text, email text, address_type text,
159 street1 text, street2 text, city text, province text, country text, postal_code text, phone
160 text, profile int, ident_type int, home_ou int, claims_returned_count int DEFAULT 0, usrname text,
161 net_access_level int DEFAULT 2, password text, already_exists boolean DEFAULT FALSE
164 --Copy records from your import text file
165 COPY students (student_id, last_name, first_name, email, address_type, street1, street2, city, province,
166 country, postal_code, phone, password)
167 FROM '/home/opensrf/patrons.csv' WITH CSV HEADER;
169 --Determine which records are new, and which are merely updates of existing patrons
170 --You may with to also add a check on the home_ou column here, so that you don't
171 --accidentally overwrite the data of another library in your consortium.
172 --You may also use a different matchpoint than actor.usr.ident_value.
174 SET already_exists = TRUE
176 WHERE students.student_id = actor.usr.ident_value;
178 --Update the names of existing patrons, in case they have changed their name
180 SET first_given_name = students.first_name, family_name=students.last_name
182 WHERE actor.usr.ident_value=students.student_id
183 AND (first_given_name != students.first_name OR family_name != students.last_name)
184 AND students.already_exists;
186 --Update email addresses of existing patrons
187 --You may wish to update other fields as well, while preserving others
188 --actor.usr.passwd is an example of a field you may not wish to update,
189 --since patrons may have set the password to something other than the
192 SET email=students.email
194 WHERE actor.usr.ident_value=students.student_id
195 AND students.email != ''
196 AND actor.usr.email != students.email
197 AND students.already_exists;
199 --Insert records from the staging table into the actor.usr table.
200 INSERT INTO actor.usr (
201 profile, usrname, email, passwd, ident_type, ident_value, first_given_name, family_name,
202 day_phone, home_ou, claims_returned_count, net_access_level)
203 SELECT profile, students.usrname, email, password, ident_type, student_id, first_name,
204 last_name, phone, home_ou, claims_returned_count, net_access_level
205 FROM students WHERE NOT already_exists;
207 --Insert records from the staging table into the actor.card table.
208 INSERT INTO actor.card (usr, barcode)
209 SELECT actor.usr.id, students.barcode
212 ON students.usrname = actor.usr.usrname
213 WHERE NOT students.already_exists;
215 --Update actor.usr.card field with actor.card.id to associate active card with the user:
217 SET card = actor.card.id
219 WHERE actor.card.usr = actor.usr.id;
221 --INSERT records INTO actor.usr_address from staging table.
222 INSERT INTO actor.usr_address (usr, street1, street2, city, state, country, post_code)
223 SELECT actor.usr.id, students.street1, students.street2, students.city, students.province,
224 students.country, students.postal_code
226 INNER JOIN actor.usr ON students.usrname = actor.usr.usrname
227 WHERE NOT students.already_exists;
230 --Update actor.usr mailing address with id from actor.usr_address table.:
232 SET mailing_address = actor.usr_address.id, billing_address = actor.usr_address.id
233 FROM actor.usr_address
234 WHERE actor.usr.id = actor.usr_address.usr;
237 ----------------------------------
239 Placing the sql statements between BEGIN; and COMMIT; creates a transaction
240 block so that if any sql statements fail, the entire process is canceled and the
241 database is rolled back to its original state. Lines beginning with -- are
242 comments to let you you what each sql statement is doing and are not processed.
244 == Batch Updating Patron Data ==
246 For academic libraries, doing batch updates to add new patrons to the Evergreen
247 database is a critical task. The above procedures and import script can be
248 easily adapted to create an update script for importing new patrons from
249 external databases. If the data import file contains only new patrons, then, the
250 above procedures will work well to insert those patrons. However, if the data
251 load contains all patrons, a second staging table and a procedure to remove
252 existing patrons from that second staging table may be required before importing
253 the new patrons. Moreover, additional steps to update address information and
254 perhaps delete inactive patrons may also be desired depending on the
255 requirements of the institution.
257 After developing the scripts to import and update patrons have been created,
258 another important task for library staff is to develop an import strategy and
259 schedule which suits the needs of the library. This could be determined by
260 registration dates of your institution in the case of academic libraries. It is
261 important to balance the convenience of patron loads and the cost of processing
262 these loads vs staff adding patrons manually.