14 do '/openils/conf/offline-config.pl';
15 $dbh = DBI->connect( $config{dsn}, $config{usr}, $config{pw} ) or die $DBI::errstr;
26 $cfg->{auto_options}->{csv_options} = \%CSV_options;
29 'config=s' => \$csv_config,
30 'no-legacy-prefix' => \($cfg->{no_legacy_prefix}),
31 'use-no-headers-file' => \($cfg->{use_no_headers_file}),
32 'add-x-migrate' => \($cfg->{add_x_migrate}),
33 'outfile=s' => \($cfg->{outfile}),
34 'schema=s' => \($cfg->{schema}),
35 'parent=s' => \($cfg->{parent}),
38 if ($help || ((@ARGV == 0) && (-t STDIN))) {
39 die qq^\n\t$0 [--config <CONFIG>] [--add-x-migrate] [--no-legacy-prefix] [--schema <schema>] [--parent <base table>] [--outfile <file to create>] <"clean" file from clean_csv script>\n\n^;
41 if ($csv_config && ! -e $csv_config) {
42 die "$csv_config does not exist\n";
44 if ($csv_config && -e $csv_config) {
48 die "$ARGV[0] does not exist\n";
52 sub write_sql_sample {
55 my $fn = $cfg->{outfile} || $cfg->{auto_options}->{file} . '.sql';
57 print "\twriting $fn\n";
60 print SQL "-- $cfg->{auto_options}->{file}\n/*\n";
61 open IN, $cfg->{auto_options}->{file};
75 my $fn = $cfg->{auto_options}->{file};
78 print "\twriting table definition\n";
80 $cfg->{table_name} = $cfg->{parent} . '_legacy';
82 $cfg->{table_name} = lc(basename($fn)); $cfg->{table_name} =~ s/[\-\. ]/_/g;
84 print $sql "DROP TABLE IF EXISTS $cfg->{schema}.$cfg->{table_name};\n";
85 print $sql "CREATE UNLOGGED TABLE $cfg->{schema}.$cfg->{table_name} (\n";
87 if ($cfg->{add_x_migrate}) {
88 print $sql " x_migrate BOOLEAN\n";
90 push @indices, 'x_migrate';
92 foreach my $column (@{ $info }) {
93 my $cn = $column->{'header'};
97 my $col_info = Dumper($column);
98 $col_info =~ s/^\$VAR1 = //;
99 print $sql " " . ($idx++ ? ',' : ' ');
100 print $sql "l_" unless $cfg->{no_legacy_prefix} or $column->{'header'} =~ /^x_/ or $column->{'header'} =~ /^l_/;
101 print $sql "$cn " . ($cn eq 'x_eg_bib_id' ? 'BIGINT' : 'TEXT');
102 print $sql " /*\n $col_info */\n";
104 if ($cfg->{parent}) {
105 print $sql ') INHERITS (' . $cfg->{schema} . '.' . $cfg->{parent} . ");\n";
109 foreach my $cn (@indices) {
110 print $sql "CREATE INDEX ON $cfg->{schema}.$cfg->{table_name} ($cn);\n";
114 sub write_sql_loader {
119 my $fn = $cfg->{auto_options}->{file} . ($cfg->{use_no_headers_file} ? '.no_headers' : '');
121 print "\twriting copy statement\n";
122 print $sql "\n\\COPY $cfg->{schema}.$cfg->{table_name} (";
124 foreach my $column (@{ $info }) {
125 print $sql ($idx++ ? ',' : '');
126 print $sql "l_" unless $cfg->{no_legacy_prefix} or $column->{'header'} =~ /^x_/ or $column->{'header'} =~ /^l_/;
127 print $sql $column->{'header'};
129 print $sql ") FROM '$fn'";
130 if ($auto->csv->sep_char eq chr(9) && ! defined $auto->csv->quote_char && ! defined $auto->csv->escape_char) {
131 # true .tsv, don't treat as csv
132 } elsif ($auto->csv->sep_char eq chr(9)) {
133 # probably good enough .tsv, don't treat as csv
135 print $sql " WITH csv " . ($cfg->{use_no_headers_file} ? "" : "header");
136 print $sql " delimiter " . $dbh->quote( $auto->csv->sep_char ) unless $dbh->quote( $auto->csv->sep_char ) eq 'NULL';
137 print $sql " quote " . $dbh->quote( $auto->csv->quote_char ) unless $dbh->quote( $auto->csv->quote_char ) eq 'NULL';
138 print $sql " escape " . $dbh->quote( $auto->csv->escape_char ) unless $dbh->quote( $auto->csv->escape_char ) eq 'NULL';
145 foreach my $fn (@ARGV) {
146 print "processing $fn\n";
147 $cfg->{auto_options}->{file} = $fn;
148 my $auto = Text::CSV::Auto->new($cfg->{auto_options});
150 my $info = $auto->analyze();
151 my $sql = write_sql_sample($cfg,$info);
152 write_sql_table($sql,$cfg,$info);
153 write_sql_loader($sql,$cfg,$auto,$info);