Commit | Line | Data |
046f18e5 |
1 | package SQL::Translator::Parser::xSV; |
2 | |
825ed07b |
3 | =head1 NAME |
4 | |
5 | SQL::Translator::Parser::xSV - parser for arbitrarily delimited text files |
6 | |
7 | =head1 SYNOPSIS |
8 | |
9 | use SQL::Translator; |
10 | use SQL::Translator::Parser::xSV; |
11 | |
12 | my $translator = SQL::Translator->new( |
13 | parser => 'xSV', |
14 | parser_args => { field_separator => "\t" }, |
15 | ); |
16 | |
17 | =head1 DESCRIPTION |
18 | |
19 | Parses arbitrarily delimited text files. See the |
20 | Text::RecordParser manpage for arguments on how to parse the file |
21 | (e.g., C<field_separator>, C<record_separator>). Other arguments |
22 | include: |
23 | |
5eb4a350 |
24 | =head1 OPTIONS |
25 | |
825ed07b |
26 | =over |
27 | |
28 | =item * scan_fields |
29 | |
30 | Indicates that the columns should be scanned to determine data types |
9d90f9cd |
31 | and field sizes. True by default. |
825ed07b |
32 | |
33 | =item * trim_fields |
34 | |
35 | A shortcut to sending filters to Text::RecordParser, will create |
36 | callbacks that trim leading and trailing spaces from fields and headers. |
9d90f9cd |
37 | True by default. |
825ed07b |
38 | |
39 | =back |
40 | |
41 | Field names will automatically be normalized by |
7b09a302 |
42 | C<SQL::Translator::Utils::normalize_name>. |
825ed07b |
43 | |
44 | =cut |
45 | |
46 | # ------------------------------------------------------------------- |
47 | |
046f18e5 |
48 | use strict; |
da06ac74 |
49 | use vars qw($VERSION @EXPORT); |
11ad2df9 |
50 | $VERSION = '1.59'; |
046f18e5 |
51 | |
52 | use Exporter; |
53 | use Text::ParseWords qw(quotewords); |
825ed07b |
54 | use Text::RecordParser; |
55 | use SQL::Translator::Utils qw(debug normalize_name); |
046f18e5 |
56 | |
57 | use base qw(Exporter); |
58 | @EXPORT = qw(parse); |
59 | |
825ed07b |
60 | # |
046f18e5 |
61 | # Passed a SQL::Translator instance and a string containing the data |
825ed07b |
62 | # |
046f18e5 |
63 | sub parse { |
70944bc5 |
64 | my ( $tr, $data ) = @_; |
825ed07b |
65 | my $args = $tr->parser_args; |
66 | my $parser = Text::RecordParser->new( |
67 | field_separator => $args->{'field_separator'} || ',', |
68 | record_separator => $args->{'record_separator'} || "\n", |
69 | data => $data, |
70 | header_filter => \&normalize_name, |
71 | ); |
72 | |
5eb4a350 |
73 | $parser->field_filter( sub { $_ = shift || ''; s/^\s+|\s+$//g; $_ } ) |
9d90f9cd |
74 | unless defined $args->{'trim_fields'} && $args->{'trim_fields'} == 0; |
825ed07b |
75 | |
70944bc5 |
76 | my $schema = $tr->schema; |
9d90f9cd |
77 | my $table = $schema->add_table( name => 'table1' ); |
78 | |
825ed07b |
79 | # |
80 | # Get the field names from the first row. |
81 | # |
82 | $parser->bind_header; |
83 | my @field_names = $parser->field_list; |
046f18e5 |
84 | |
825ed07b |
85 | for ( my $i = 0; $i < @field_names; $i++ ) { |
9d90f9cd |
86 | my $field = $table->add_field( |
87 | name => $field_names[$i], |
88 | data_type => 'char', |
89 | default_value => '', |
90 | size => 255, |
91 | is_nullable => 1, |
92 | is_auto_increment => undef, |
93 | ) or die $table->error; |
94 | |
95 | if ( $i == 0 ) { |
96 | $table->primary_key( $field->name ); |
97 | $field->is_primary_key(1); |
046f18e5 |
98 | } |
99 | } |
100 | |
825ed07b |
101 | # |
102 | # If directed, look at every field's values to guess size and type. |
103 | # |
9d90f9cd |
104 | unless ( |
105 | defined $args->{'scan_fields'} && |
106 | $args->{'scan_fields'} == 0 |
107 | ) { |
825ed07b |
108 | my %field_info = map { $_, {} } @field_names; |
109 | while ( my $rec = $parser->fetchrow_hashref ) { |
110 | for my $field ( @field_names ) { |
111 | my $data = defined $rec->{ $field } ? $rec->{ $field } : ''; |
2008ecf3 |
112 | my $size = [ length $data ]; |
825ed07b |
113 | my $type; |
114 | |
115 | if ( $data =~ /^-?\d+$/ ) { |
116 | $type = 'integer'; |
117 | } |
2008ecf3 |
118 | elsif ( |
119 | $data =~ /^-?[,\d]+\.[\d+]?$/ |
120 | || |
121 | $data =~ /^-?[,\d]+?\.\d+$/ |
122 | || |
123 | $data =~ /^-?\.\d+$/ |
124 | ) { |
825ed07b |
125 | $type = 'float'; |
35ea7ccb |
126 | my ( $w, $d ) = |
127 | map { s/,//g; length $_ || 1 } split( /\./, $data ); |
128 | $size = [ $w + $d, $d ]; |
825ed07b |
129 | } |
130 | else { |
131 | $type = 'char'; |
132 | } |
133 | |
2008ecf3 |
134 | for my $i ( 0, 1 ) { |
135 | next unless defined $size->[ $i ]; |
136 | my $fsize = $field_info{ $field }{'size'}[ $i ] || 0; |
137 | if ( $size->[ $i ] > $fsize ) { |
138 | $field_info{ $field }{'size'}[ $i ] = $size->[ $i ]; |
139 | } |
825ed07b |
140 | } |
141 | |
142 | $field_info{ $field }{ $type }++; |
143 | } |
144 | } |
145 | |
146 | for my $field ( keys %field_info ) { |
6808d3e9 |
147 | my $size = $field_info{ $field }{'size'} || [ 1 ]; |
9d90f9cd |
148 | my $data_type = |
35ea7ccb |
149 | $field_info{ $field }{'char'} ? 'char' : |
150 | $field_info{ $field }{'float'} ? 'float' : |
151 | $field_info{ $field }{'integer'} ? 'integer' : 'char'; |
9d90f9cd |
152 | |
6808d3e9 |
153 | if ( $data_type eq 'char' && scalar @$size == 2 ) { |
154 | $size = [ $size->[0] + $size->[1] ]; |
155 | } |
156 | |
9d90f9cd |
157 | my $field = $table->get_field( $field ); |
158 | $field->size( $size ); |
159 | $field->data_type( $data_type ); |
825ed07b |
160 | } |
161 | } |
162 | |
f62bd16c |
163 | return 1; |
046f18e5 |
164 | } |
165 | |
046f18e5 |
166 | 1; |
825ed07b |
167 | |
168 | # ------------------------------------------------------------------- |
169 | =pod |
170 | |
90075866 |
171 | =head1 AUTHORS |
825ed07b |
172 | |
173 | Darren Chamberlain E<lt>darren@cpan.orgE<gt>, |
11ad2df9 |
174 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt>. |
825ed07b |
175 | |
176 | =head1 SEE ALSO |
177 | |
5eb4a350 |
178 | Text::RecordParser, SQL::Translator. |
825ed07b |
179 | |
180 | =cut |