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