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>
![[BACK]](/icons/back.gif){kind=icon}
Return to MTNcataloging.pm CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / nsdl / harvestsmete