only get active copies
[Evergreen.git] / Evergreen / src / extras / import / drain-batgirl-item.pl
1 #!/usr/bin/perl
2
3 use strict;
4 use DBI;
5
6 my $dbh = DBI->connect('DBI:mysql:database=reports;host=batgirl.gsu.edu','miker','poopie');
7
8 print <<SQL;
9
10 DROP TABLE legacy_item;
11
12 CREATE TABLE legacy_item (CAT_1 text,CREATION_DATE date,CAT_2 text,CURRENT_LOCATION text,ITEM_ID text,CAT_KEY int,CALL_KEY int,ITEM_KEY int,PRICE numeric(8,2),ITEM_TYPE text,OWNING_LIBRARY text,SHADOW bool,ITEM_COMMENT text,LAST_IMPORT_DATE date,HOME_LOCATION text);
13
14 COPY legacy_item (CAT_1,CREATION_DATE,CAT_2,CURRENT_LOCATION,ITEM_ID,CAT_KEY,CALL_KEY,ITEM_KEY,PRICE,ITEM_TYPE,OWNING_LIBRARY,SHADOW,ITEM_COMMENT,LAST_IMPORT_DATE,HOME_LOCATION) FROM STDIN;
15 SQL
16
17 warn "going for the data...";
18
19 my $sth = $dbh->prepare("SELECT * FROM ITEM WHERE LAST_IMPORT_DATE IN ('20060901','20060902');");
20 $sth->execute;
21
22 warn "got it, writing file...";
23
24 while (my $cn = $sth->fetchrow_hashref) {
25         my @data = map { $$cn{$_} } qw/CAT_1 CREATION_DATE CAT_2 CURRENT_LOCATION ITEM_ID CAT_KEY CALL_KEY ITEM_KEY PRICE ITEM_TYPE OWNING_LIBRARY SHADOW ITEM_COMMENT LAST_IMPORT_DATE HOME_LOCATION/;
26         for (@data) {
27                 if (defined($_)) {
28                         s/\\/\\\\/go;
29                         s/\t/ /go;
30                 } else {
31                         $_ = '\N';
32                 }
33         }
34         print join("\t", @data) . "\n";
35 }
36
37 print "\\.\n";
38 print "CREATE INDEX cat_call_idx ON legacy_item (cat_key,call_key);\n";
39
40