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