From 596fc7099275b29b3fa2713cbba4c261f81d4082 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Thu, 1 Sep 2011 12:51:04 -0400 Subject: [PATCH] Clean up LDAP synchronization tool further A few syntax errors here, a few logic errors there... that's what we have test servers for. Signed-off-by: Dan Scott --- tools/patron-load/ldap_sync | 73 +++++++++++++++++++++++++-------------------- 1 file changed, 40 insertions(+), 33 deletions(-) diff --git a/tools/patron-load/ldap_sync b/tools/patron-load/ldap_sync index 6a8a9a6553..f66e2fa1bc 100644 --- a/tools/patron-load/ldap_sync +++ b/tools/patron-load/ldap_sync @@ -232,11 +232,11 @@ def update_existing_users(): """ return """ - 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 + AND LENGTH(actor.card.barcode) = 14 ) WHERE home_ou IN (SELECT id FROM actor.org_unit WHERE parent_ou = 105); UPDATE scratchpad.usr_staging @@ -250,12 +250,12 @@ def update_existing_users(): WHEN LOWER(affiliation) = 'faculty' THEN 11 WHEN LOWER(affiliation) = 'student' THEN 13 WHEN LOWER(affiliation) = 'staff' THEN 15 --- WHEN LOWER(affiliation) = 'retired' THEN 15 + WHEN LOWER(affiliation) = 'retired' THEN NULL END ; UPDATE actor.usr SET - home_ou = su.home_ou + home_ou = su.home_ou, usrname = LOWER(su.usrname), profile = su.profile, email = LOWER(su.email), @@ -290,24 +290,23 @@ def insert_new_users(): """ return """ - - 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 ( + INSERT INTO actor.usr (usrname, passwd, email, first_given_name, family_name, home_ou, ident_type, ident_value, profile) + SELECT LOWER(usrname), MD5(RANDOM()::TEXT), LOWER(email), first_given_name, family_name, home_ou, 2, ident_value, profile + FROM scratchpad.usr_staging WHERE ident_value IN ( + SELECT ident_value + FROM scratchpad.usr_staging + EXCEPT SELECT ident_value FROM actor.usr WHERE home_ou IN ( SELECT id FROM actor.org_unit WHERE parent_ou = 105 ) - ) + ) AND home_ou IS NOT NULL ; UPDATE scratchpad.usr_staging @@ -316,11 +315,11 @@ def insert_new_users(): WHERE actor.usr.ident_value = scratchpad.usr_staging.ident_value AND status IS NULL; - SELECT au.id, generate_lu_barcode() + SELECT au.id, lu_generate_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 - ); + ) AND su.status = 'new'; """ @@ -339,7 +338,7 @@ def search_for_students(con, attributes, create_date): break else: # dump_data(result_data) - insert_into_staging(result_data) + insert_into_staging(result_data[0][1]) print(r'\.') except ldap.LDAPError, e: print e @@ -349,23 +348,24 @@ def insert_into_staging(result_data): Generate statements to push data into the staging table """ - if 'mail' not in result_data[0][1]: - print >> sys.stderr, 'mail not found for %s' % result_data[0][1]['cn'] + if 'mail' not in result_data: + print >> sys.stderr, 'mail not found for %s' % result_data['cn'] return - usrname = result_data[0][1]['cn'][0] - email = result_data[0][1]['mail'][0] - family_name = result_data[0][1]['sn'][0] - given_name = result_data[0][1]['givenName'][0] - datatel = result_data[0][1]['lulColleagueId'][0] + # Strip leading/ending whitespace + usrname = result_data['cn'][0].strip() + email = result_data['mail'][0].strip() + family_name = result_data['sn'][0].strip() + given_name = result_data['givenName'][0].strip() + datatel = result_data['lulColleagueId'][0].strip() - if 'preferredLanguage' in result_data[0][1]: - lang = result_data[0][1]['preferredLanguage'][0] + if 'preferredLanguage' in result_data: + lang = result_data['preferredLanguage'][0].strip() else: lang = r'\N' - if 'lulPrimaryAffiliation' in result_data[0][1]: - affiliation = result_data[0][1]['lulPrimaryAffiliation'][0] + if 'lulPrimaryAffiliation' in result_data: + affiliation = result_data['lulPrimaryAffiliation'][0].strip() else: affiliation = r'\N' @@ -383,15 +383,15 @@ def dump_data(result_data): for key in result_data[0][1]: print(key, result_data[0][1][key]) -if __name__ == '__main__': - import doctest - doctest.testmod() - exit() +def generate_ldap_sql(create_date): + """ + Generate the SQL required to create and update Evergreen accounts + """ - create_date = '20110701' con = ldap.initialize(luauth.hostname) con.set_option(ldap.OPT_REFERRALS, 0) + print("BEGIN;") print(database_barcode_to_datatel()) print(database_mod10()) print(generate_lu_barcode()) @@ -402,11 +402,11 @@ if __name__ == '__main__': con.simple_bind_s(luauth.dn, luauth.pw) search_for_students(con, attributes, create_date) except ldap.LDAPError, e: - print "Could not connect: " + e.message['info'] + print >> sys.stderr, "Could not connect: " + e.message['info'] if type(e.message) == dict and e.message.has_key('desc'): - print e.message['desc'] + print >> sys.stderr, e.message['desc'] else: - print e + print >> sys.stderr, e sys.exit() finally: con.unbind() @@ -414,4 +414,11 @@ if __name__ == '__main__': print(update_existing_users()) print(insert_new_users()) +if __name__ == '__main__': + import doctest + doctest.testmod() + exit() + + generate_ldap_sql('20110701') + # vim: et:ts=4:sw=4:tw=78: -- 2.11.0