#!/usr/bin/perl
#################################################################################
# This is the perl script used to export the oralgen.sql to Excel csv file format.
# The oralgen.sql was imported to a local virtual database to reconstruct
# the original Oralgen mySQL database. This script was then executed to parse 
# the databases and tables to the file system.
# Author: Tsute Chen, The Forsyth Institute.
# Date: 2014-01-17
##################################################################################
use strict;
open(SQL,"oralgen.sql")||die;
while(my $line=<SQL>){
	if($line=~/^USE `([^`]+)/){
		my $fld=$1;
		if($fld eq "frag_recr"){
			next;
		}
		print $fld;
		if(!-e $fld){
			mkdir($fld);
			chmod(0777,$fld);
		}
		system("mysqldump -u root --password=***** --fields-terminated-by=',' --fields-enclosed-by='\"'  --tab=$fld $fld");
		while(my $sql=<$fld/*.sql>){
			$sql=~/$fld\/(.+)\.sql/;
			my $table=$1;
			open(IN,$sql)||die;
			my $start;
			my @cols;
			while(my $line=<IN>){
				if($line=~/  `([^`]+)/){
					push (@cols,$1);
				}
			}
			close IN;
			my $header= "\"".join("\",\"",@cols)."\"\n";
			open(IN,"$fld/$table.txt")||die;
			my $text;
			while(my $line=<IN>){
				$line=~s/\\N/\"\"/mg;
				$line=~s/\\\n//mg;
				$line=~s/[\r]//mg;
				if($line!~/\"$/){
					chomp $line;
				}
				$text.=$line;
			}
			close IN;
			open (OUT,">$fld/$table.csv")||die;
			print OUT $header.$text;
			close OUT;
			unlink($sql);
			unlink("$fld/$table.txt");
		}
		system("mysqldump -u root --password=taipei $fld > $fld/$fld.sql");
		if(-e "$fld/genome_table.csv"){
			open(IN,"$fld/genome_table.csv")||die;
			my $first=<IN>;
			my $str=<IN>; my @tmp=split(",",$str); 
			my $genome;
			if($first=~/^\"genome_seq/){
				$genome=$tmp[1];
			}elsif($first=~/^\"mol_id/){
				$genome=$tmp[1]."_".$tmp[2]; 
			}else{
				$genome=$tmp[0]."_".$tmp[1];
			}
			$genome=~s/\"//g; $genome=~s/\s/_/g;$genome=~s/\//-/g;$genome=~s/__/_/g;$genome=~s/_$//;
			print " => ".$genome;
			close IN;
			system("mv $fld $genome");
			system("chmod 755 $genome");
			system("chmod -R 644 $genome/*");
		}else{
			system("chmod 755 $fld");
			system("chmod -R 644 $fld/*");			
		}
		print "\n";
	}
}
close SQL;