![[H]ard|Forum](/styles/hardforum/xenforo/logo_dark.png){kind=logo}
Looks like you're writing quotes, and also you're writing \r\r\n at the end of each line instead of \r\n.fluxion said:well it wouldnt be hard to tighten up the matches, but yah i just kinda assumed that wouldnt happen. i live my life on the edge
generelz said:
mikeblas said:
generelz said:
nord# time ./bench.pl enwiki-20060702-page.sql
137.011u 7.501s 3:20.09 72.2% 10+4715k 4816+3112io 38pf+0w
nord# time ./bench enwiki-20060702-page.sql tmpfile
412064906 -> 378689587 characters converted.
7.012u 4.041s 0:29.24 37.7% 5+179k 13+23io 6296pf+0wdoh said:
doh said:
doh said:
Outside of synthetic benchmarks, I've never felt the need to convert the same file twice in a rowmikeblas said:
.I'm not sure the term "synthetic benchmark" can be applied to this exercise; particularly carrying the negative connotation it has in these forums.MonkeyShave said:Outside of synthetic benchmarks, I've never felt the need to convert the same file twice in a row
mikeblas said:
Thanks; I don't see anything about installation here, tho.doh said:
I think your diagnosis is probably right. But that doesn't take care of the double quotes around each of the strings in the file. I'm getting this output:fluxion said:
1 0 "AaA" "" 8 1 0 0.116338664774167 "20060401120725"...1 0 AaA 8 1 0 0.116338664774167 20060401120725...billylee@canti ~ $ time ./bakeoff.perl
real 0m9.942s
user 0m7.939s
sys 0m1.999s5803517 0 'Phalacrocorax nigrogularis' '' 0 1 1 0.955231507353 '20060702231129' 61758005 31
5803518 6 'Uniontown PA.jpg' '' 0 0 1 0.111196242907 '20060702231130' 61758009 891 0 'AaA' '' 8 1 0 0.116338664774167 '20060401120725' 46448774 70
5 0 'AlgeriA' '' 0 1 0 0.553221851171201 '20060301005610' 18063769 41
6 0 'AmericanSamoa' '' 0 1 0 0.387834867823715 '20060301005610' 18063795 48#!/usr/bin/perl
# Author: William Lee
# Perl submission for the bakeoff. Mmmmm, baking.
#
# Not fully complete.
use strict;
open my $SQLFILE, '<', 'enwiki-20060702-page.sql' or die 'Can\'t open input file';
open my $OUTFILE, '>', 'out.txt' or die 'Can\'t open file for writing';
# must be set differently for windows! - to ;\r\n
#$/ = ';\n'; # set new chomp target
#my @fields;
#$#fields = 17000;
while(my $line = <$SQLFILE>)
{
next unless ($line =~ /INSERT INTO /o);
chop;chop;chop;
#truncate and remove INSERT INTO 'page' VALUES (
$line = substr($line, 27);
# substitute \t for a ',', a ' ' for an '_'
# attempt 1 - this will also make ),( become )\t(
$line =~ tr/,_/\t /;
# the escape sequence \'
$line =~ s/\\'/'/g;
# borrowed from fluxion
$line =~ s/(?<!\\)(\\+)\\(?!\\)/$1/g;
# replace all )\t( with newline
$line =~ s/\)\t\(/\n/g;
print $OUTFILE $line;
} # end while
close $SQLFILE;
close $OUTFILE;fluxion said:wow, 9 seconds???
*deletes script and never returns*
very nice. the foreach split() was definetely killing me, current version is a lot faster but your approach still seems more efficient. i'm still reading in each record seperately
getting rid of the quotes from the output should be easy. note that every quote inside a title is escaped in the original file, so you can just remove all unescaped quotes before you get rid of the escapes.
there's also cases where commas appear in the title field so you cant just replace all the commas with tabs, i havent figured that one out yet ...if only perl did variable-length look behinds you could do something simple like s/(?<!'.+),(?=.*')/\t/g;
nice job though
my @fields_array;
$#fields_array = 16000; # or something
@fields_array = ... #split/readme, whateverYeah, it might. Right now, you're around 15 seconds on my machine. But your results are not correct beause your replacements are sloppy. Essentially, you've ignored the quotes and replace things within the quotes that shouldn't be replaced -- comams and parens, most notably. An article named "Albert III, Elector of Saxony" becomes "Albert III\t Elector of Saxony", for example.BillLeeLee said:But it's a little too early to celebrate - for all I know the extra stuff to make it correct could balloon the runtime to minutes.
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
/**
* SQLConvert.java: Submission for the C++/Perl bakeoff.
*
* The objective is to read in a MySQL dump file containing table information
* and convert the INSERT statements to a tab delimited format for import by
* MS SQL Server.
*
* This implementation relies on Java's regular expression utilities.
*
* @author Zach Bailey
* @created 21 July 2006
*/
public class SQLConvert
{
public static void main(String[] args) throws Exception
{
long startTime = System.currentTimeMillis();
//set up input/output files
File file = new File("enwiki-20060702-page.sql");
BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(file)), 6*1024*1024);
File outputFile = new File("output.txt");
BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile)));
while(in.ready())
{
String line = in.readLine();
if(line.length() < 10 || !line.startsWith("IN")) continue;
line = line.replaceFirst("INSERT INTO `page` VALUES ", "");
line = line.replaceFirst("\\);$", "),");
line = line.replaceAll("\\((\\d+),(\\d+),'(.*?)','(.*?)',(\\d+),(\\d+),(\\d+),(0.\\d+),'(\\d+)',(\\d+),(\\d+)\\),","$1\t$2\t$3\t$4\t$5\t$6\t$7\t$8\t$9\t$10\t$11\r\n");
//replace all "\'" with just "'" and all "\\" with just "\"
line = line.replaceAll("\\\\('|\\\\)", "'$1");
out.write(line, 0, line.length());
}
out.close();
long delta = System.currentTimeMillis() - startTime;
System.out.println("Completed in: " + delta/1000 + "." + delta%1000 + "s");
System.exit(0);
}
}mikeblas said:
BillLeeLee said:Ah, that is quite the bummer - I didn't know Wikipedia allowed commas within the actual page names. Now I can't do the magic global transliteration for commas, at least, since you can't use patterns with tr (which would completely negate the performance increase tr/// affords over s/// for trivial replacements).
It'd be much easier to know some of the more subtle formatting things if I could actually read the file without it blowing up my programs.
Back to the drawing board.
If I eventually succeed in making one in Perl, I might attempt it in python if no one else takes up that path. I'd also be interested in digging out my clisp and sml knowledge and building programs in either of those languages...but that'd be off in the future.
BillLeeLee said:Ah, that is quite the bummer - I didn't know Wikipedia allowed commas within the actual page names. Now I can't do the magic global transliteration for commas, at least, since you can't use patterns with tr (which would completely negate the performance increase tr/// affords over s/// for trivial replacements).
It'd be much easier to know some of the more subtle formatting things if I could actually read the file without it blowing up my programs.
Back to the drawing board.
If I eventually succeed in making one in Perl, I might attempt it in python if no one else takes up that path. I'd also be interested in digging out my clisp and sml knowledge and building programs in either of those languages...but that'd be off in the future.
#replace commas in titles with <z>
$records =~ s/(\(\d+,\d+,'[^']+?),(?!')/$1<z>/g;
#replace commas with tabs
$records =~ s/,/\t/g;
#replace <z> with commas again
$records =~ s/<z>/,/g;fluxion@purity:~/scripts/perl_bakeoff$ head -3 output
1 0 AaA 8 1 0 0.116338664774167 20060401120725 46448774 70
5 0 AlgeriA 0 1 0 0.553221851171201 20060301005610 18063769 41
6 0 AmericanSamoa 0 1 0 0.387834867823715 20060301005610 18063795 48
fluxion@purity:~/scripts/perl_bakeoff$ tail -3 output
5803516 6 CVCOG logo.png 0 0 1 0.723697823784 20060702231120 61757981 68
5803517 0 Phalacrocorax nigrogularis 0 1 1 0.955231507353 20060702231129 61758005 31
5803518 6 Uniontown PA.jpg 0 0 1 0.111196242907 20060702231130 61758009 89#!/usr/bin/perl
use strict;
open(FILE, '>output');
while (my $records = <>) {
#skip all uninteresting lines
next unless ($records =~ /INSERT INTO `page` VALUES/o);
#remove sql info from beginning, remove ); from the end
$records = substr($records, 27, -3);
#replace commas in titles with <z>
$records =~ s/(\(\d+,\d+,'[^']+?),(?!')/$1<z>/g;
#replace ),( with newlines
$records =~ s/\),\(/\n/g;
#remove non-escaped quotes
$records =~ s/(?<!\\)'//g;
#commas with tabs
$records =~ s/,/\t/g;
#put embedded commas back
$records =~ s/<z>/,/g;
#underscores with spaces
$records =~ s/_/ /g;
#remove an escape off any sequence of escapes
$records =~ s/(?<!\\)(\\+)\\(?!\\)/$1/g;
print FILE "$records\r\n";
}
close FILE;MonkeyShave said:
Compiled like this:
xeon# c++ -O2 -march=pentium4 -ansi -Wall StripWiki.cpp -o mike
xeon# gcc -O2 -march=pentium4 -ansi -Wall bench.c -o hhuntxeon# gcc -O2 -march=pentium4 -ansi -Wall bench.c -o hhunt
xeon# time ./hhunt enwiki-20060702-page.sql /newdisk/tmpfil
2.675u 1.878s 0:12.96 35.0% 5+8206k 14+16io 4849pf+0w
xeon# time ./hhunt enwiki-20060702-page.sql /newdisk/tmpfil
2.428u 0.723s 0:03.66 85.7% 5+8379k 0+18io 0pf+0w
xeon# time ./hhunt enwiki-20060702-page.sql /newdisk/tmpfil
2.437u 0.727s 0:03.67 85.8% 5+8258k 0+13io 0pf+0w
xeon# time ./hhunt enwiki-20060702-page.sql /newdisk/tmpfil
2.486u 0.657s 0:03.66 85.5% 5+8310k 0+13io 0pf+0wxeon# c++ -O2 -march=pentium4 -ansi -Wall StripWiki.cpp -o mike
xeon# time ./mike enwiki-20060702-page.sql /newdisk/tmpfil
11.827u 2.203s 0:16.69 84.0% 5+8171k 2+2889io 2pf+0w
xeon# time ./mike enwiki-20060702-page.sql /newdisk/tmpfil
11.682u 2.395s 0:16.94 83.0% 5+8286k 4+2889io 0pf+0w
xeon# time ./mike enwiki-20060702-page.sql /newdisk/tmpfil
11.574u 2.465s 0:16.86 83.2% 5+8372k 7+2889io 0pf+0w
xeon# time ./mike enwiki-20060702-page.sql /newdisk/tmpfil
11.718u 2.330s 0:16.76 83.7% 5+8284k 0+2889io 0pf+0wxeon# time ./mike enwiki-20060702-page.sql /newdisk/tmpfil
11.528u 2.809s 0:26.73 53.5% 5+8247k 6425+2889io 6pf+0w
xeon# time ./mike enwiki-20060702-page.sql /newdisk/tmpfil
11.717u 2.320s 0:16.92 82.9% 5+8232k 6+2889io 9pf+0w