Annotation of nsdl/harvestsmete/OAIcataloging.pm, revision 1.1
1.1 ! www 1: #!/usr/local/bin/perl -w
! 2:
! 3: use strict;
! 4:
! 5: use Getopt::Std;
! 6:
! 7: use DBI;
! 8: use DBD::ODBC;
! 9:
! 10: my $DBI_DSN='dbi:ODBC:needs2.odbc';
! 11: my $DBI_USER='smete_user';
! 12: my $DBI_PWD='needsmete';
! 13: my $dbh;
! 14:
! 15: sub SP_ct_key {
! 16: my ($dbh,$name) = @_;
! 17: # Fetch the ct_key number from the contrib_type table
! 18: my @row_ary = $dbh->selectrow_array(q{SELECT c.ct_key FROM contrib_type c WHERE c.name = ?}, undef, $name);
! 19: my $ct_key = $row_ary[0];
! 20: # print $ct_key . "\n";
! 21: return $ct_key;
! 22: }
! 23:
! 24: # Find general_key given a title
! 25: # return undef if nothing found
! 26: # Usage: OAIc_loexists($dbh,$title)
! 27: sub OAIc_loexists {
! 28: my ($dbh, $title) = @_;
! 29: my @row_ary = $dbh->selectrow_array(q{SELECT logeneral.general_key FROM logeneral logeneral WHERE logeneral.title1 = ?}, undef, $title);
! 30: if ($row_ary[0]) {
! 31: return $row_ary[0];
! 32: } else {
! 33: return undef;
! 34: }
! 35: }
! 36:
! 37: # Generate a key given a field name
! 38: # e.g., my $key = &OAIc_key ($dbh,$field_name);
! 39: sub OAIc_key {
! 40: my ($dbh,$field_name) = @_;
! 41: # Fetch the current key number from the KEYS table
! 42: my @row_ary = $dbh->selectrow_array(q{SELECT k.key_value FROM keys k WHERE k.field_name = ?}, undef, $field_name);
! 43: # Increment the value by 1
! 44: my $key = $row_ary[0] + 1;
! 45: # print $key . "\n";
! 46: # Now update the table with the new value
! 47: 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 key value in table keys: $dbh->errstr\n";
! 48: $dbh->commit;
! 49: return $key;
! 50: }
! 51:
! 52: # Generate a needs number "nn"
! 53: # e.g., my $nn = &OAIc_nn ( '2000', '01', 1000 );
! 54: sub OAIc_nn {
! 55: my ( $pubYear, $pubMonth, $lo_key ) = @_;
! 56: # Format lo_key to be 10 characters long
! 57: $lo_key = sprintf("%10d",$lo_key);
! 58: # Replace leading spaces with a 0
! 59: $lo_key =~ tr/ /0/;
! 60: my $nn = sprintf("%s_%s_%s", $pubYear, $pubMonth, $lo_key );
! 61: # print $nn . "\n";
! 62: return $nn;
! 63: }
! 64:
! 65: # Update lo into tables
! 66: sub OAIc_update_lo {
! 67: my ($dbh,$lo_key, $needs_number, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $affiliate, $contributor, $format, $platform, $os, $version, $date, $url, $ped_lcontext, $ped_enduserrole, $author_reg_key, $publisher_reg_key) = @_;
! 68: my $rc;
! 69: # UPDATE [needs_v2_1]..logeneral
! 70: $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, substr($description,0,1024), $image, $keywords, $pub_month, $pub_year, $general_key);
! 71: if (!$rc) {
! 72: $dbh->rollback;
! 73: $dbh->disconnect;
! 74: die "Unable to update record into logeneral: $dbh->errstr\n";
! 75: }
! 76: # UPDATE [needs_v2_1]..lo
! 77: $rc = $dbh->do(q{UPDATE lo SET needs_number = ?, modification_date = GetDate() WHERE lo_key = ?}, undef, $needs_number, $lo_key);
! 78: if (!$rc) {
! 79: $dbh->rollback;
! 80: $dbh->disconnect;
! 81: die "Unable to update record into lo: $dbh->errstr\n";
! 82: }
! 83: # UPDATE [needs_v2_1]..contrib_role2
! 84: $rc = $dbh->do(q{UPDATE contrib_role2 SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
! 85: if (!$rc) {
! 86: $dbh->rollback;
! 87: $dbh->disconnect;
! 88: die "Unable to update contrib_role2: $dbh->errstr\n";
! 89: }
! 90: # UPDATE [needs_v2_1]..cw_search
! 91: $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, substr($description,0,1024), $author, $keywords, $publisher, $affiliate, $platform, $contributor, $lo_key);
! 92: if (!$rc) {
! 93: $dbh->rollback;
! 94: $dbh->disconnect;
! 95: die "Unable to update record into cw_search: $dbh->errstr\n";
! 96: }
! 97: # UPDATE [needs_v2_1]..lo_pedagogy
! 98: $rc = $dbh->do(q{UPDATE lo_pedagogy SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
! 99: if (!$rc) {
! 100: $dbh->rollback;
! 101: $dbh->disconnect;
! 102: die "Unable to update record into lo_pedagogy: $dbh->errstr\n";
! 103: }
! 104: # UPDATE [needs_v2_1]..pedagogy
! 105: $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, '', '', '', '', '', $ped_enduserrole, '', '', '', '', $ped_lcontext, '', '', '1-en-US', $submitter_key, $lo_key);
! 106: if (!$rc) {
! 107: $dbh->rollback;
! 108: $dbh->disconnect;
! 109: die "Unable to update record into pedagogy: $dbh->errstr\n";
! 110: }
! 111: # UPDATE [needs_v2_1]..lo_platform
! 112: $rc = $dbh->do(q{UPDATE lo_platform SET modification_date = GetDate() WHERE lo_key = ?}, undef, $lo_key);
! 113: if (!$rc) {
! 114: $dbh->rollback;
! 115: $dbh->disconnect;
! 116: die "Unable to update record into lo_platform: $dbh->errstr\n";
! 117: }
! 118: # UPDATE [needs_v2_1]..platform
! 119: $rc = $dbh->do(q{UPDATE platform SET format = ?, type = ?, version = ?, location_url = ?, cost = ?, os = ?, status = ?, modification_date = GetDate(), created_by = ? WHERE platform_key = (SELECT platform_key FROM lo_platform WHERE lo_key = ?)}, undef, $format, $platform, $version, $url, '1-Freeware', $os, 'complete', $submitter_key, $lo_key);
! 120: if (!$rc) {
! 121: $dbh->rollback;
! 122: $dbh->disconnect;
! 123: die "Unable to update record into lo_platform: $dbh->errstr\n";
! 124: }
! 125: # Upon success commit
! 126: $dbh->commit;
! 127: return $rc;
! 128: }
! 129:
! 130: # Insert lo into tables
! 131: sub OAIc_insert_lo {
! 132: my ($dbh,$lo_key, $needs_number, $general_key, $title, $language, $description, $image, $pub_month, $pub_year, $keywords, $submitter_key, $author, $publisher, $affiliate, $contributor, $format, $platform, $os, $version, $date, $url, $ped_lcontext, $ped_enduserrole, $author_reg_key, $publisher_reg_key, $affiliate_key) = @_;
! 133: my $rc;
! 134: # INSERT INTO [needs_v2_1]..logeneral
! 135: $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, substr($description,0,1024), $image, $keywords, $pub_month, $pub_year, $submitter_key);
! 136: if (!$rc) {
! 137: $dbh->rollback;
! 138: $dbh->disconnect;
! 139: die "Unable to insert new record into logeneral: $dbh->errstr\n";
! 140: }
! 141: # INSERT INTO [needs_v2_1]..lo
! 142: $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');
! 143: if (!$rc) {
! 144: $dbh->rollback;
! 145: $dbh->disconnect;
! 146: die "Unable to insert new record into lo: $dbh->errstr\n";
! 147: }
! 148: # Get a cr_key
! 149: my $cr_key = OAIc_key($dbh,'cr_key');
! 150: # INSERT INTO [needs_v2_1]..contrib_role2
! 151: # Add author contribution (ct_key=1)
! 152: $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, $author_reg_key, 1, 1, 1, $submitter_key, 'active');
! 153: # Add publisher contribution (ct_key=2)
! 154: $cr_key = OAIc_key($dbh,'cr_key');
! 155: $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, $publisher_reg_key, 2, 1, 1, $submitter_key, 'active');
! 156: # Add submitter contribution (ct_key=3)
! 157: $cr_key = OAIc_key($dbh,'cr_key');
! 158: $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');
! 159: # Add contact contribution (ct_key=4)
! 160: $cr_key = OAIc_key($dbh,'cr_key');
! 161: $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, $author_reg_key, 4, 1, 1, $submitter_key, 'active');
! 162: if (!$rc) {
! 163: $dbh->rollback;
! 164: $dbh->disconnect;
! 165: die "Unable to insert new record into contrib_role2: $dbh->errstr\n";
! 166: }
! 167: # INSERT INTO [needs_v2_1]..cw_search
! 168: $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, substr($description,0,1024), $author, $keywords, $publisher, $affiliate, $platform, $contributor);
! 169: if (!$rc) {
! 170: $dbh->rollback;
! 171: $dbh->disconnect;
! 172: die "Unable to insert new record into cw_search: $dbh->errstr\n";
! 173: }
! 174: # INSERT INTO [needs_v2_1]..lo_pedagogy
! 175: my $loped_key = OAIc_key($dbh,'loped_key');
! 176: my $pedagogy_key = OAIc_key($dbh,'pedagogy_key');
! 177: $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');
! 178: if (!$rc) {
! 179: $dbh->rollback;
! 180: $dbh->disconnect;
! 181: die "Unable to insert new record into lo_pedagogy: $dbh->errstr\n";
! 182: }
! 183: # INSERT INTO [needs_v2_1]..pedagogy
! 184: $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, '', $ped_lcontext, '', '', '', '', '', '', '', '', '', $ped_enduserrole, '', '', '', '1-en-US', $submitter_key);
! 185: if (!$rc) {
! 186: $dbh->rollback;
! 187: $dbh->disconnect;
! 188: die "Unable to insert new record into pedagogy: $dbh->errstr\n";
! 189: }
! 190: # INSERT INTO [needs_v2_1]..lo_platform
! 191: my $loplat_key = OAIc_key($dbh,'loplat_key');
! 192: my $platform_key = OAIc_key($dbh,'platform_key');
! 193: $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);
! 194: if (!$rc) {
! 195: $dbh->rollback;
! 196: $dbh->disconnect;
! 197: die "Unable to insert new record into lo_platform: $dbh->errstr\n";
! 198: }
! 199: # INSERT INTO [needs_v2_1]..platform
! 200: $rc = $dbh->do(q{INSERT INTO platform (platform_key, format, type, os, version, location_url, cost, status, creation_date, modification_date, created_by) VALUES (?,?,?,?,?,?,?,?,GetDate(),GetDate(),?)}, undef, $platform_key, $format, $platform, $os, $version, $url, '1-Freeware', 'complete', $submitter_key);
! 201: if (!$rc) {
! 202: $dbh->rollback;
! 203: $dbh->disconnect;
! 204: die "Unable to insert new record into lo_platform: $dbh->errstr\n";
! 205: }
! 206: # INSERT INTO [needs_v2_1]..cw_affiliate
! 207: my $cw_affiliate_key = OAIc_key($dbh,'cw_affiliate_key');
! 208: $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, $affiliate_key);
! 209: if (!$rc) {
! 210: $dbh->rollback;
! 211: $dbh->disconnect;
! 212: die "Unable to insert new record into cw_affiliate: $dbh->errstr \n";
! 213: }
! 214: # Upon success commit
! 215: $dbh->commit;
! 216: return $rc;
! 217: }
! 218:
! 219: sub OAIc_personexists {
! 220: my ($dbh,$email) = @_;
! 221: my @person_row_ary = $dbh->selectrow_array(q{SELECT person.person_key FROM person person WHERE person.email = ?}, undef, $email);
! 222: if ($person_row_ary[0]) {
! 223: my @reg_row_ary = $dbh->selectrow_array(q{SELECT u.reg_key FROM [user] u WHERE u.person_key = ?}, undef, $person_row_ary[0]);
! 224: if ($reg_row_ary[0]) {
! 225: return $reg_row_ary[0];
! 226: } else {
! 227: return undef;
! 228: }
! 229: } else {
! 230: return undef;
! 231: }
! 232: }
! 233:
! 234: sub OAIc_insert_person {
! 235: my ($dbh,$reg_key,$person_key,$org_key,$submitter_key,$lastname,$firstname,$email,$company) = @_;
! 236: my $rc = $dbh->do(q{INSERT INTO [user] (reg_key,person_key,org_key,type,created_by,creation_date,modification_date,status) VALUES (?,?,?,?,?,GetDate(),GetDate(),'active')}, undef, $reg_key, $person_key, $org_key, 'person', $submitter_key);
! 237: if (!$rc) {
! 238: $dbh->rollback;
! 239: $dbh->disconnect;
! 240: die "Unable to insert new person into user: $dbh->errstr \n";
! 241: }
! 242:
! 243: $rc = $dbh->do(q{INSERT INTO person (person_key,email,lastname,firstname,company,creation_date,created_by,olderThan13) VALUES (?,?,?,?,?,GetDate(),?,1)}, undef, $person_key, $email, $lastname, $firstname, $company, $submitter_key);
! 244: if (!$rc) {
! 245: $dbh->rollback;
! 246: $dbh->disconnect;
! 247: die "Unable to insert new person into person: $dbh->errstr \n";
! 248: }
! 249:
! 250: return $rc;
! 251: }
! 252: return 1;
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to OAIcataloging.pm CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / nsdl / harvestsmete