From cbf1cb064c4e29460a38eb4b0177a2a7948ef2df Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Wed, 31 Aug 2011 15:54:42 -0400 Subject: [PATCH] Progress on the LDAP patron loading script Many functions defined and individually tested, still need to put this together in working order. Signed-off-by: Dan Scott --- tools/patron-load/ldap_sync | 176 ++++++++++++++++++++++++++++++++++++++++---- 1 file changed, 163 insertions(+), 13 deletions(-) diff --git a/tools/patron-load/ldap_sync b/tools/patron-load/ldap_sync index aa23dd3e31..e2b87ef855 100644 --- a/tools/patron-load/ldap_sync +++ b/tools/patron-load/ldap_sync @@ -9,7 +9,7 @@ imported to avoid storing credentials in the VCS. Rough plan: 1. Create new accounts - a. Pull new LDAP records since a 2011-07--1 from the LDAP directory + a. Pull new LDAP records since 2011-07-01 from the LDAP directory using the filter (createTimestamp>=$time) and insert into a staging table with the following columns; included is a sample mapping to the LU LDAP attributes: @@ -17,30 +17,33 @@ Rough plan: * family_name (sn) * ident_value (lulColleagueId) * usrname (cn) + * email (mail) * language (preferredLanguage) * profile (lulPrimaryAffiliation) * datatel_barcode (datatel_to_barcode(lulColleagueId) * student_type (???) - b. For each LDAP record, create a new library system account if it +2. Update existing accounts + a. Update (ident_type, ident_value) to (2, barcode_to_datatel) + b. If we found a match in the user staging column, then update attributes accordingly: + * Set preferred language stat cat + * Set usrname based on lower(cn) + * Set email address to lower(mail) +3. Create new accounts + a. For each LDAP record, create a new library system account if it does not already exist (check for matches based on usrname, email address, datatel_barcode). Map LDAP attributes to account profile, first and last names, email address. * Set passwd to a randomly generated value; first time users can reset via email * Set ident_type = 2, ident_value = ident_value - * Set home_ou appropriately + * Set home_ou based on email (only thing we've got) * Set expire_date to next September for students, 20 years from now (?) for faculty / staff * Set preferred language stat cat - c. Create a new barcode for the user via a PostgreSQL routine; draw + b. Create a new barcode for the user via a PostgreSQL routine; draw the base number from a database series. We no longer want to use barcodes based on the Datatel number. This routine should update the actor.usr.card column with the appropriate card ID. -2. Update existing accounts - a. If we found a match in 1(b), then update attributes accordingly: - * Set preferred language stat cat - * Update ident_type / ident_value to Datatel ID - * Set email address based on cn """ import sys @@ -135,6 +138,7 @@ def database_mod10(): my $total = 0; my $position = 0; foreach my $digit (split('', $barcode)) { + $digit = sprintf('%d', $digit); $position++; if ($position % 2) { # Double it @@ -157,6 +161,45 @@ def database_mod10(): $$ LANGUAGE PLPERLU STRICT IMMUTABLE; """ +def generate_lu_barcode(): + """ + Define a PostgreSQL function for generating a barcode + + Given a user ID from actor.usr: + Define a sequence to pull from. + Grab next entry from the sequence. + Prefix the number with the right prefix. + Add the checksum. + Create the actor.card entry. + Grab the actor.card.id value and push it into the actor.usr.card entry for + our user. + """ + + print """ +CREATE SEQUENCE evergreen.lu_barcode START 200000; + +CREATE OR REPLACE FUNCTION evergreen.generate_lu_barcode(usr_id INT) RETURNS TEXT AS $$ +DECLARE + barcode TEXT; + mod TEXT; + bc_serial RECORD; +BEGIN + SELECT NEXTVAL('evergreen.lu_barcode') AS bc INTO bc_serial; + barcode := '0000700' || bc_serial.bc::text; + barcode := barcode || evergreen.mod10(barcode); + + INSERT INTO actor.card (usr, barcode) VALUES (usr_id, barcode); + + UPDATE actor.usr + SET card = CURRVAL('actor.card_id_seq') + WHERE id = usr_id; + + RETURN barcode; +END; +$$ LANGUAGE PLPGSQL; +""" + + def create_staging_table(): """ Create a staging table for creating or updating user accounts @@ -164,8 +207,112 @@ def create_staging_table(): print """ DROP TABLE IF EXISTS scratchpad.usr_staging; -CREATE TABLE scratchpad.usr_staging (usrname TEXT, family_name TEXT, first_given_name TEXT, ident_value TEXT, lang TEXT); -COPY scratchpad.usr_staging (usrname, family_name, first_given_name, ident_value, lang) FROM STDIN; +CREATE TABLE scratchpad.usr_staging (usrname TEXT, email TEXT, family_name TEXT, first_given_name TEXT, ident_value TEXT, lang TEXT, affiliation TEXT, profile INT, home_ou INT, status TEXT); +COPY scratchpad.usr_staging (usrname, email, family_name, first_given_name, ident_value, lang, affiliation) FROM STDIN; +""" + +def update_existing_users(): + """ + Update existing users in the database + + Update ident_type & ident_value based on existing barcode. + Then check for a match in staging table based on the Datatel ID and + update more attributes. + + Note, be careful with names until we see what LDAP does with middle names. + """ + + print """ + BEGIN; + UPDATE actor.usr SET ident_type = 2, ident_value = ( + SELECT barcode_to_datatel(barcode) + FROM actor.card + WHERE actor.card.id = actor.usr.card + ) WHERE home_ou IN (SELECT id FROM actor.org_unit WHERE parent_ou = 105); + + UPDATE scratchpad.usr_staging + SET home_ou = CASE + WHEN LOWER(email) LIKE '%laurentian.ca' THEN 103 + WHEN LOWER(email) LIKE '%laurentienne.ca' THEN 103 + WHEN LOWER(email) LIKE '%usudbury.ca' THEN 107 + WHEN LOWER(email) LIKE '%huntingtonu.ca' THEN 104 + END, + profile = CASE + WHEN LOWER(affiliation) = 'faculty' THEN 11 + WHEN LOWER(affiliation) = 'student' THEN 13 + WHEN LOWER(affiliation) = 'staff' THEN 15 +-- WHEN LOWER(affiliation) = 'retired' THEN 15 + END + ; + + UPDATE actor.usr SET + home_ou = su.home_ou + usrname = LOWER(su.usrname), + profile = su.profile, + email = LOWER(su.email), + first_given_name = su.first_given_name, + family_name = su.family_name + FROM scratchpad.usr_staging su + WHERE su.ident_value= actor.usr.ident_value + AND actor.usr.home_ou IN ( + SELECT id FROM actor.org_unit WHERE parent_ou = 105 + ) + AND su.profile IS NOT NULL + ; + + UPDATE scratchpad.usr_staging + SET status = 'updated' + FROM actor.usr + WHERE actor.usr.ident_value = scratchpad.usr_staging.ident_value + AND actor.usr.home_ou IN ( + SELECT id FROM actor.org_unit WHERE parent_ou = 105 + ) + AND scratchpad.usr_staging.profile IS NOT NULL + ; + +""" + +def insert_new_users(): + """ + Insert new users into the database + + First, create the new user. + Then, create their card. + """ + + print """ + + BEGIN; + + -- Treat them as undergrads + UPDATE scratchpad.usr_staging + SET profile = 13 + WHERE profile IS NULL; + + INSERT INTO actor.usr (usrname, email, first_given_name, family_name, home_ou, ident_type, ident_value) + SELECT LOWER(su.usrname), LOWER(su.email), su.first_given_name, su.family_name, su.home_ou, 2, su.ident_value + FROM scratchpad.usr_staging su + WHERE su.ident_value NOT IN ( + SELECT ident_value + FROM actor.usr + WHERE home_ou IN ( + SELECT id FROM actor.org_unit WHERE parent_ou = 105 + ) + ) + ; + + UPDATE scratchpad.usr_staging + SET status = 'new' + FROM actor.usr + WHERE actor.usr.ident_value = scratchpad.usr_staging.ident_value + AND status IS NULL; + + SELECT au.id, generate_lu_barcode() + FROM actor.usr au INNER JOIN scratchpad.usr_staging su ON su.ident_value = au.ident_value + WHERE au.home_ou IN ( + SELECT id FROM actor.org_unit WHERE parent_ou = 105 + ); + """ def search_for_students(con, attributes, create_date): @@ -184,6 +331,7 @@ def search_for_students(con, attributes, create_date): else: # dump_data(result_data) insert_into_staging(result_data) + print(r'\.') except ldap.LDAPError, e: print e @@ -193,13 +341,15 @@ def insert_into_staging(result_data): """ usrname = result_data[0][1]['cn'] + email = result_data[0][1]['mail'] family_name = result_data[0][1]['sn'] given_name = result_data[0][1]['givenName'] datatel = result_data[0][1]['lulColleagueId'] lang = result_data[0][1]['preferredLanguage'] or r'\N' + affiliation = result_data[0][1]['lulPrimaryAffiliation'] or r'\N' - print "%s\t%s\t%s\t%s\t%s" % ( - usrname, family_name, given_name, datatel, lang + print "%s\t%s\t%s\t%s\t%s\t%s" % ( + usrname, family_name, given_name, datatel, lang, affiliation ) def dump_data(result_data): -- 2.11.0