gmax has asked for the wisdom of the Perl Monks concerning the following question:
To create such statement, I am parsing the raw data from an input file, and I have the problem of inserting commas after each set of values, except the last one (or we could say before each set, except the first one).INSERT INTO tablename VALUES (value1, value2, value3), (value1, value2, value3), (value1,value2, value3);
A little tricky, but it works. The first call will change the TRUE value to a FALSE one, thus preventing the print from happening. Each successive call will return 1 (TRUE) with the help of the ternary operator, unleashing the print.my $should_print = 1; while (...) { print "'\n" if ($should_print? --$should_print : 1); # munging ... }
#!/usr/bin/perl -w use strict; print <<MYSQL; CREATE DATABASE IF NOT EXISTS chess; USE chess; DROP TABLE IF EXISTS eco_class; # create table statement CREATE TABLE eco_class ( eco_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, eco CHAR(3) NOT NULL, opening VARCHAR(50) NOT NULL, variation VARCHAR(50), moves VARCHAR(200) NOT NULL, KEY eco(eco), KEY opening(opening), KEY variation (variation), KEY moves(moves)); # create the header for the insert statement INSERT INTO eco_class VALUES MYSQL my @line; # input fields my $should_print =1; # flag for first line with_while(); # comment to test the alternative #with_map(); # uncomment to test the alternative print ";\n"; sub with_while { while (<DATA>) { chomp; # removes trailing newline next if /^\s*$/; # skips blank lines # skips the first line print ",\n" if ($should_print? --$should_print : 1); @line = split /;/; # gets individual records print "(NULL, ", quotrim($line[0]), ", ", quotrim($line[1]), ", "; # the third field may be missing print scalar @line < 4 ? "NULL, " . quotrim($line[2]) : quotrim($line[2]) . ", " . quotrim($line[3]), ")"; } } sub with_map { print join ",\n" , grep { defined $_ } map { chomp; # removes trailing newline if (/^\s*$/ ) { # skips blank lines $_ = undef; } else { @line = split /;/; # gets individual records $_ = "(NULL, " . quotrim($line[0]) . ", " . quotrim($line[1]) . ", "; $_ .= scalar @line < 4 ? "NULL, " . quotrim($line[2]) : quotrim($line[2]) . ", " . quotrim($line[3]), ")"; $_ .= ")"; } } <DATA>; } sub quotrim { # quote and trim :-> $_[0] =~ s/\"/\\"/g; $_[0] =~ s/^\s+//; $_[0] =~ s/\s+$//; return '"' . $_[0] . '"'; } #sample data follows __DATA__ A00;Polish (Sokolsky) opening;1.b4 A00;Polish;Tuebingen Variation ;1.b4 Nh6 A00;Polish;Outflank Variation ;1.b4 c6 A00;Benko's Opening;1.g3 A00;Lasker simul special;1.g3 h5 A00;Benko's Opening;reversed Alekhine;1.g3 e5 2.Nf3 A00;Grob's attack;1.g4 A00;Grob;Spike attack;1.g4 d5 2.Bg2 c6 3.g5 A00;Grob;Fritz gambit;1.g4 d5 2.Bg2 Bxg4 3.c4 A00;Grob;Romford counter-gambit;1.g4 d5 2.Bg2 Bxg4 3.c4 d4 A00;Clemenz (Mead's, Basman's or de Klerk's) Opening;1.h3 A00;Global Opening;1.h3 e5 2.a3 A00;Amar (Paris) Opening;1.Nh3 A00;Amar gambit;1.Nh3 d5 2.g3 e5 3.f4 Bxh3 4.Bxh3 exf4
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: A TRUE-once variable
by gmax (Abbot) on Dec 06, 2001 at 13:31 UTC | |
by demerphq (Chancellor) on Dec 06, 2001 at 16:13 UTC | |
|
Re: A TRUE-once variable
by Lucky (Scribe) on Dec 05, 2001 at 21:32 UTC | |
|
Re: A TRUE-once variable
by clintp (Curate) on Dec 05, 2001 at 21:39 UTC | |
|
Re: A TRUE-once variable
by Rhandom (Curate) on Dec 05, 2001 at 22:00 UTC | |
|
(jeffa) Re: A TRUE-once variable
by jeffa (Bishop) on Dec 05, 2001 at 21:36 UTC | |
|
Re: A TRUE-once variable
by japhy (Canon) on Dec 05, 2001 at 22:15 UTC | |
|
Re: A TRUE-once variable
by Rhandom (Curate) on Dec 05, 2001 at 22:05 UTC | |
|
Re: A TRUE-once variable
by danger (Priest) on Dec 06, 2001 at 08:42 UTC | |
by Anonymous Monk on Jan 29, 2002 at 05:25 UTC | |
|
Re: A TRUE-once variable
by perrin (Chancellor) on Dec 05, 2001 at 22:54 UTC | |
|
Re: A TRUE-once variable
by baku (Scribe) on Dec 06, 2001 at 03:10 UTC | |
by clintp (Curate) on Dec 06, 2001 at 19:33 UTC | |
|
Re: A TRUE-once variable
by George_Sherston (Vicar) on Dec 05, 2001 at 22:49 UTC | |
|
Re: A TRUE-once variable
by Lucky (Scribe) on Dec 05, 2001 at 22:14 UTC | |
|
Re: A TRUE-once variable
by diotalevi (Canon) on Sep 21, 2005 at 21:50 UTC |