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