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