5 HOME MYSQL_HOST MYSQL_TCP_PORT MYSQL_USER MYSQL_DATABASE MYSQL_PW
6 MIGSCHEMA MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
11 my $mig_bin = "$FindBin::Bin/";
12 use lib "$FindBin::Bin/";
26 $dbh = KMig::db_connect();
38 $cfg->{auto_options}->{csv_options} = \%CSV_options;
41 'config=s' => \$csv_config,
42 'no-legacy-prefix' => \($cfg->{no_legacy_prefix}),
43 'use-no-headers-file' => \($cfg->{use_no_headers_file}),
44 'add-x-migrate' => \($cfg->{add_x_migrate}),
45 'outfile=s' => \($cfg->{outfile}),
46 'schema=s' => \($cfg->{schema}),
47 'parent=s' => \($cfg->{parent}),
50 if ($help || ((@ARGV == 0) && (-t STDIN))) {
51 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^;
53 if ($csv_config && ! -e $csv_config) {
54 die "$csv_config does not exist\n";
56 if ($csv_config && -e $csv_config) {
57 eval `cat $csv_config`;
60 die "$ARGV[0] does not exist\n";
64 sub write_sql_sample {
67 my $fn = $cfg->{outfile} || $cfg->{auto_options}->{file} . '.sql';
69 print "\twriting $fn\n";
72 print SQL "-- $cfg->{auto_options}->{file}\n/*\n";
73 open IN, $cfg->{auto_options}->{file};
87 my $fn = $cfg->{auto_options}->{file};
90 print "\twriting table definition\n";
92 if ($cfg->{parent} !~ '^m_') {
93 die "parent table is not a m_ prefixed table; afraid to drop and recreate";
95 $cfg->{table_name} = $cfg->{parent};
96 print $sql "DROP TABLE IF EXISTS $cfg->{table_name};\n";
97 my $prod_table = $cfg->{parent}; $prod_table =~ s/^m_//;
98 print $sql "CREATE TABLE $cfg->{table_name} LIKE $prod_table;\n";
99 print $sql "ALTER TABLE $cfg->{table_name} ADD COLUMN x_migrate BOOLEAN;\n";
101 $cfg->{table_name} = 'm_' . lc(basename($fn)); $cfg->{table_name} =~ s/[\-\. ]/_/g;
102 print $sql "DROP TABLE IF EXISTS $cfg->{table_name};\n";
103 print $sql "CREATE TABLE $cfg->{table_name} (x_migrate BOOLEAN);\n";
105 push @indices, 'x_migrate';
106 foreach my $column (@{ $info }) {
107 my $cn = $column->{'header'};
111 my $col_info = Dumper($column);
112 $col_info =~ s/^\$VAR1 = //;
113 print $sql "ALTER TABLE $cfg->{table_name} ADD COLUMN ";
114 print $sql "l_" unless $cfg->{no_legacy_prefix} or $column->{'header'} =~ /^x_/ or $column->{'header'} =~ /^l_/;
115 if ($column->{'header'} eq 'x_egid' || $column->{'header'} eq 'x_hseq') {
116 print $sql "$cn BIGINT; ";
118 print $sql "$cn TEXT; ";
120 print $sql " /*\n $col_info */\n";
122 foreach my $cn (@indices) {
123 print $sql "CREATE INDEX " . $cn . "_idx ON $cfg->{table_name} ($cn);\n";
127 sub write_sql_loader {
132 my $fn = $cfg->{auto_options}->{file} . ($cfg->{use_no_headers_file} ? '.no_headers' : '');
134 print "\twriting copy statement\n";
135 print $sql "\nLOAD DATA LOCAL INFILE '$fn' INTO TABLE $cfg->{table_name} ";
136 if ($auto->csv->sep_char eq chr(9) && ! defined $auto->csv->quote_char && ! defined $auto->csv->escape_char) {
137 # true .tsv, don't treat as csv
138 } elsif ($auto->csv->sep_char eq chr(9)) {
139 # probably good enough .tsv, don't treat as csv
142 print $sql " TERMINATED BY " . $dbh->quote( $auto->csv->sep_char ) unless $dbh->quote( $auto->csv->sep_char ) eq 'NULL';
143 print $sql " OPTIONALLY ENCLOSED BY " . $dbh->quote( $auto->csv->quote_char ) unless $dbh->quote( $auto->csv->quote_char ) eq 'NULL';
144 print $sql " ESCAPED BY " . $dbh->quote( $auto->csv->escape_char ) unless $dbh->quote( $auto->csv->escape_char ) eq 'NULL';
145 if (!$cfg->{use_no_headers_file}) {
146 print $sql " IGNORE 1 LINES "
151 foreach my $column (@{ $info }) {
152 print $sql ($idx++ ? ',' : '');
153 print $sql "l_" unless $cfg->{no_legacy_prefix} or $column->{'header'} =~ /^x_/ or $column->{'header'} =~ /^l_/;
154 print $sql $column->{'header'};
161 foreach my $fn (@ARGV) {
162 print "processing $fn\n";
163 $cfg->{auto_options}->{file} = $fn;
164 my $auto = Text::CSV::Auto->new($cfg->{auto_options});
166 my $info = $auto->analyze();
167 my $sql = write_sql_sample($cfg,$info);
168 write_sql_table($sql,$cfg,$info);
169 write_sql_loader($sql,$cfg,$auto,$info);