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