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