Annotation of nsdl/harvestsmete/MTNcataloging.pm, revision 1.1

1.1     ! www         1: #!/usr/local/bin/perl
        !             2: 
        !             3: #
        !             4: # MTNcataloging.pl
        !             5: # SMETE Cataloging Functions in PERL
        !             6: #
        !             7: # Andy Dong <adong@smete.org> 05/10/2001
        !             8: #
        !             9: 
        !            10: use strict;
        !            11: use DBI;
        !            12: use DBD::ODBC;
        !            13: 
        !            14: # Configuration
        !            15: 
        !            16: my $DBI_DSN='dbi:ODBC:needs2.odbc';
        !            17: my $DBI_USER='smete_user';
        !            18: my $DBI_PWD='needsmete';
        !            19: 
        !            20: # Generate a key given a field name
        !            21: # e.g., my $key = &SP_key ($dbh,$field_name);
        !            22: sub SP_key {
        !            23: 	my ($dbh,$field_name) = @_;
        !            24: 	# Fetch the current key number from the KEYS table
        !            25: 	my @row_ary = $dbh->selectrow_array(q{SELECT k.key_value FROM keys k WHERE k.field_name = ?}, undef, $field_name);
        !            26: 	# Increment the value by 1
        !            27: 	my $key = $row_ary[0] + 1;
        !            28: #	print $key . "\n";
        !            29: 	# Now update the table with the new value
        !            30: 	my $rc = $dbh->do(q{UPDATE keys SET key_value = ?, mod_date=GetDate() FROM keys k WHERE k.field_name = ?}, undef, $key, $field_name) || warn "Unable to update lo_key in table keys: $dbh->errstr\n";
        !            31: 	$dbh->commit;
        !            32: 	return $key;
        !            33: }
        !            34: 
        !            35: # Generate a needs number "nn"
        !            36: # e.g., my $nn = &SP_nn ( '2000', '01', 1000 );
        !            37: sub SP_nn {
        !            38: 	my ( $pubYear, $pubMonth, $lo_key ) = @_;
        !            39: 	# Format lo_key to be 10 characters long
        !            40: 	$lo_key = sprintf("%10d",$lo_key);
        !            41: 	# Replace leading spaces with a 0
        !            42: 	$lo_key =~ tr/ /0/;
        !            43: 	my $nn = sprintf("%s_%s_%s", $pubYear, $pubMonth, $lo_key );
        !            44: #	print $nn . "\n";
        !            45: 	return $nn;
        !            46: }
        !            47: 
        !            48: sub SP_ct_key {
        !            49: 	my ($dbh,$name) = @_;
        !            50: 	# Fetch the ct_key number from the contrib_type table
        !            51: 	my @row_ary = $dbh->selectrow_array(q{SELECT c.ct_key FROM contrib_type c WHERE c.name = ?}, undef, $name);
        !            52: 	my $ct_key = $row_ary[0];
        !            53: #	print $ct_key . "\n";
        !            54: 	return $ct_key;
        !            55: }
        !            56: 
        !            57: # Update lo into tables
        !            58: sub SP_update_lo {
        !            59: 	my ($dbh,$lo_key, $needs_number, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $affiliate, $contributor, $platform, $date, $url, $ped_lcontext) = @_;
        !            60: 	my $rc;
        !            61: 	# UPDATE [needs_v2_1]..logeneral
        !            62: 	$rc = $dbh->do(q{UPDATE logeneral SET title1 = ?, language1 = ?, description = ?, image = ?, keywords = ?, modification_date = GetDate(), pub_month = ?, pub_year = ? WHERE general_key = ?}, undef, $title, $language, $description, $image, $keywords, $pub_month, $pub_year, $general_key);
        !            63: 	if (!$rc) {
        !            64: 		$dbh->rollback;
        !            65: 		$dbh->disconnect;
        !            66: 		die "Unable to update record into logeneral: $dbh->errstr\n";
        !            67: 	}
        !            68: 	# UPDATE [needs_v2_1]..lo
        !            69: 	$rc = $dbh->do(q{UPDATE lo SET needs_number = ?, modification_date = GetDate() WHERE lo_key = ?}, undef, $needs_number, $lo_key);
        !            70: 	if (!$rc) {
        !            71: 	$dbh->rollback;
        !            72: 	$dbh->disconnect;
        !            73: 	die "Unable to update record into lo: $dbh->errstr\n";
        !            74: 	}
        !            75: 	# UPDATE [needs_v2_1]..contrib_role2
        !            76: 	$rc = $dbh->do(q{UPDATE contrib_role2 SET modification_date = GetDate(), reg_key=4275 WHERE lo_key = ? AND reg_key=3328}, undef, $lo_key);
        !            77: 	if (!$rc) {
        !            78: 	$dbh->rollback;
        !            79: 	$dbh->disconnect;
        !            80: 	die "Unable to update contrib_role2: $dbh->errstr\n";
        !            81: 	}
        !            82: 	# UPDATE [needs_v2_1]..cw_search
        !            83: 	$rc = $dbh->do(q{UPDATE cw_search SET title = ?, summary = ?, author_text = ?, subject_heading_text = ?, publisher_text = ?, affiliate_text = ?, platform_text = ?, contributor_text = ?, modification_date = GetDate() WHERE lo_key = ?}, undef, $title, $description, $author, $keywords, $publisher, $affiliate, $platform, $contributor, $lo_key);
        !            84: 	if (!$rc) {
        !            85: 	$dbh->rollback;
        !            86: 	$dbh->disconnect;
        !            87: 	die "Unable to update record into cw_search: $dbh->errstr\n";
        !            88: 	}
        !            89: 	# UPDATE [needs_v2_1]..lo_pedagogy
        !            90: 	$rc = $dbh->do(q{UPDATE lo_pedagogy SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
        !            91: 	if (!$rc) {
        !            92: 	$dbh->rollback;
        !            93: 	$dbh->disconnect;
        !            94: 	die "Unable to update record into lo_pedagogy: $dbh->errstr\n";
        !            95: 	}
        !            96: 	# UPDATE [needs_v2_1]..pedagogy
        !            97: 	$rc = $dbh->do(q{UPDATE pedagogy SET ped_title = ?, ped_desc = ?, ped_lcontext = ?, ped_lcontext_other = ?, ped_difficulty = ?, ped_restype = ?, ped_restype_other = ?, ped_use = ?, ped_use_other = ?, ped_intlevel = ?, ped_inttype = ?, ped_semdensity = ?, ped_enduserrole = ?, ped_agerange = ?, ped_ltime = ?, ped_coverage = ?, ped_intlanguage = ?, created_by = ?, modification_date = GetDate() WHERE pedagogy_key = (SELECT pedagogy_key FROM lo_pedagogy WHERE lo_key = ?)}, undef, $title, $author, $ped_lcontext, '', '', '27-Collection', '', '3-Instructor-guided classroom instruction', '', '', '', '', '', $ped_lcontext, '', '', '1-en-US', $submitter_key, $lo_key);
        !            98: 	if (!$rc) {
        !            99: 	$dbh->rollback;
        !           100: 	$dbh->disconnect;
        !           101: 	die "Unable to update record into pedagogy: $dbh->errstr\n";
        !           102: 	}
        !           103: 	# UPDATE [needs_v2_1]..lo_platform
        !           104: 	$rc = $dbh->do(q{UPDATE lo_platform SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
        !           105: 	if (!$rc) {
        !           106: 	$dbh->rollback;
        !           107: 	$dbh->disconnect;
        !           108: 	die "Unable to update record into lo_platform: $dbh->errstr\n";
        !           109: 	}
        !           110: 	# UPDATE [needs_v2_1]..platform
        !           111: 	$rc = $dbh->do(q{UPDATE platform SET format = ?, type = ?, version = ?, location_url = ?, cost = ?, modification_date = GetDate(), created_by = ? WHERE platform_key = (SELECT platform_key FROM lo_platform WHERE lo_key = ?)}, undef, '1-Generate Automatically from MIME Type', '5-WWW', $date, $url, '1-Freeware', $submitter_key, $lo_key);
        !           112: 	if (!$rc) {
        !           113: 	$dbh->rollback;
        !           114: 	$dbh->disconnect;
        !           115: 	die "Unable to update record into lo_platform: $dbh->errstr\n";
        !           116: 	}
        !           117: 	# Upon success commit
        !           118: 	$dbh->commit;
        !           119: 	return $rc;
        !           120: }
        !           121: 
        !           122: # Find general_key given a title
        !           123: # return undef if nothing found
        !           124: sub SP_loexists {
        !           125: 	my ($dbh, $title) = @_;
        !           126: 	my @row_ary = $dbh->selectrow_array(q{SELECT logeneral.general_key FROM logeneral logeneral WHERE logeneral.title1 = ?}, undef, $title);
        !           127: 	if ($row_ary[0]) {
        !           128: 		return $row_ary[0];
        !           129: 	} else {
        !           130: 		return undef;
        !           131: 	}
        !           132: }
        !           133: 
        !           134: # Insert lo into tables
        !           135: sub SP_insert_lo {
        !           136: 	my ($dbh,$lo_key, $needs_number, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $affiliate, $contributor, $platform, $date, $url, $ped_lcontext) = @_;
        !           137: 	my $rc;
        !           138: 	# INSERT INTO [needs_v2_1]..logeneral
        !           139: 	$rc = $dbh->do(q{INSERT INTO logeneral (general_key, title1, language1, description, image, keywords, creation_date, modification_date, pub_month, pub_year, submitter_key) VALUES (?,?,?,?,?,?,GetDate(),GetDate(),?,?,?)}, undef, $general_key, $title, $language, $description, $image, $keywords, $pub_month, $pub_year, $submitter_key);
        !           140: 	if (!$rc) {
        !           141: 		$dbh->rollback;
        !           142: 		$dbh->disconnect;
        !           143: 		die "Unable to insert new record into logeneral: $dbh->errstr\n";
        !           144: 	}
        !           145: 	# INSERT INTO [needs_v2_1]..lo
        !           146: 	$rc = $dbh->do(q{INSERT INTO lo (lo_key, needs_number, status, general_key, lo_commit, community, creation_date, modification_date) VALUES (?,?,?,?,?,?,GetDate(),GetDate())}, undef, $lo_key, $needs_number, 'active', $general_key, 1, 'eng');
        !           147: 	if (!$rc) {
        !           148: 	$dbh->rollback;
        !           149: 	$dbh->disconnect;
        !           150: 	die "Unable to insert new record into lo: $dbh->errstr\n";
        !           151: 	}
        !           152: 	# Get a cr_key
        !           153: 	my $cr_key = &SP_key($dbh,'cr_key');
        !           154: 	# INSERT INTO [needs_v2_1]..contrib_role2
        !           155: 	# Add author contribution (ct_key=1)
        !           156: 	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, 3329, 1, 1, 1, $submitter_key, 'active');
        !           157: 	# Add publisher contribution (ct_key=2)
        !           158: 	$cr_key = &SP_key($dbh,'cr_key');
        !           159: 	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, 3681, 2, 1, 1, $submitter_key, 'active');
        !           160: 	# Add submitter contribution (ct_key=3)
        !           161: 	$cr_key = &SP_key($dbh,'cr_key');
        !           162: 	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, $submitter_key, 3, 1, 1, $submitter_key, 'active');
        !           163: 	# Add contact contribution (ct_key=4)
        !           164: 	$cr_key = &SP_key($dbh,'cr_key');
        !           165: 	# Marcia Mardis is [user].reg_key=4275 and person.person_key=3420
        !           166: 	$rc = $dbh->do(q{INSERT INTO contrib_role2 (cr_key, lo_key, reg_key, ct_key, primary_member, order_by, created_by, creation_date, modification_date, status) VALUES (?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $cr_key, $lo_key, 4275, 4, 1, 1, $submitter_key, 'active');
        !           167: 	if (!$rc) {
        !           168: 	$dbh->rollback;
        !           169: 	$dbh->disconnect;
        !           170: 	die "Unable to insert new record into contrib_role2: $dbh->errstr\n";
        !           171: 	}
        !           172: 	# INSERT INTO [needs_v2_1]..cw_search
        !           173: 	$rc = $dbh->do(q{INSERT INTO cw_search (lo_key, title, summary, author_text, subject_heading_text, publisher_text, affiliate_text, platform_text, contributor_text, creation_date, modification_date) VALUES (?,?,?,?,?,?,?,?,?,GetDate(),GetDate())}, undef, $lo_key, $title, $description, $author, $keywords, $publisher, $affiliate, $platform, $contributor);
        !           174: 	if (!$rc) {
        !           175: 	$dbh->rollback;
        !           176: 	$dbh->disconnect;
        !           177: 	die "Unable to insert new record into cw_search: $dbh->errstr\n";
        !           178: 	}
        !           179: 	# INSERT INTO [needs_v2_1]..lo_pedagogy
        !           180: 	my $loped_key = &SP_key($dbh,'loped_key');
        !           181: 	my $pedagogy_key = &SP_key($dbh,'pedagogy_key');
        !           182: 	$rc = $dbh->do(q{INSERT INTO lo_pedagogy (loped_key, lo_key, pedagogy_key, order_by, creation_date, modification_date, status) VALUES (?,?,?,?,GetDate(),GetDate(),?)}, undef, $loped_key, $lo_key, $pedagogy_key, 1, 'complete');
        !           183: 	if (!$rc) {
        !           184: 	$dbh->rollback;
        !           185: 	$dbh->disconnect;
        !           186: 	$dbh->disconnect;
        !           187: 	die "Unable to insert new record into lo_pedagogy: $dbh->errstr\n";
        !           188: 	}
        !           189: 	# INSERT INTO [needs_v2_1]..pedagogy
        !           190: 	$rc = $dbh->do(q{INSERT INTO pedagogy (pedagogy_key, ped_title, ped_desc, ped_lcontext, ped_lcontext_other, ped_difficulty, ped_restype, ped_restype_other, ped_use, ped_use_other, ped_intlevel, ped_inttype, ped_semdensity, ped_enduserrole, ped_agerange, ped_ltime, ped_coverage, ped_intlanguage, created_by, creation_date, modification_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,GetDate(),GetDate())}, undef, $pedagogy_key, $title, $author, $ped_lcontext, 'Professional Development', '', '27-Collection', '', '3-Instructor-guided classroom instruction', '', '', '', '', '', '', '', '', '1-en-US', $submitter_key);
        !           191: 	if (!$rc) {
        !           192: 	$dbh->rollback;
        !           193: 	$dbh->disconnect;
        !           194: 	die "Unable to insert new record into pedagogy: $dbh->errstr\n";
        !           195: 	}
        !           196: 	# INSERT INTO [needs_v2_1]..lo_platform
        !           197: 	my $loplat_key = &SP_key($dbh,'loplat_key');
        !           198: 	my $platform_key = &SP_key($dbh,'platform_key');
        !           199: 	$rc = $dbh->do(q{INSERT INTO lo_platform (loplat_key, lo_key, platform_key, order_by, creation_date, modification_date, status) VALUES (?,?,?,?,GetDate(),GetDate(),'complete')}, undef, $loplat_key, $lo_key, $platform_key, 1);
        !           200: 	if (!$rc) {
        !           201: 	$dbh->rollback;
        !           202: 	$dbh->disconnect;
        !           203: 	die "Unable to insert new record into lo_platform: $dbh->errstr\n";
        !           204: 	}
        !           205: 	# INSERT INTO [needs_v2_1]..platform
        !           206: 	$rc = $dbh->do(q{INSERT INTO platform (platform_key, format, type, version, location_url, cost, creation_date, modification_date, created_by) VALUES (?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $platform_key, '1-Generate Automatically from MIME Type', '5-WWW', $date, $url, '1-Freeware', $submitter_key);
        !           207: 	if (!$rc) {
        !           208: 	$dbh->rollback;
        !           209: 	$dbh->disconnect;
        !           210: 	die "Unable to insert new record into lo_platform: $dbh->errstr\n";
        !           211: 	}
        !           212: 	# INSERT INTO [needs_v2_1]..cw_affiliate
        !           213: 	my $cw_affiliate_key = &SP_key($dbh,'cw_affiliate_key');
        !           214: 	$rc = $dbh->do(q{INSERT INTO cw_affiliate (cw_affiliate_key, cw_key, affiliate_key, creation_date, modification_date, order_by) VALUES (?,?,?,GetDate(),GetDate(),1)}, undef, $cw_affiliate_key, $lo_key, 10);
        !           215: 	if (!$rc) {         
        !           216: 		$dbh->rollback;   
        !           217: 		$dbh->disconnect;  
        !           218: 		die "Unable to insert new record into cw_affiliate: $dbh->errstr\n";
        !           219: 	}
        !           220: 	$cw_affiliate_key = &SP_key($dbh,'cw_affiliate_key');
        !           221: 	$rc = $dbh->do(q{INSERT INTO cw_affiliate (cw_affiliate_key, cw_key, affiliate_key, creation_date, modification_date, order_by) VALUES (?,?,?,GetDate(),GetDate(),1)}, undef, $cw_affiliate_key, $lo_key, 12);
        !           222: 	if (!$rc) {          
        !           223: 		$dbh->rollback;
        !           224: 		$dbh->disconnect;
        !           225: 		die "Unable to insert new record into cw_affiliate: $dbh->errstr \n";          
        !           226: 	}
        !           227: 	# Upon success commit
        !           228: 	$dbh->commit;
        !           229: 	return $rc;
        !           230: }

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>