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