#!/usr/bin/perl
#
# List of options:
# txt2sql_upd.pl --help
use strict;
use warnings;
use Getopt::Long;
my $sDirPath = "";
my $sFileout = "";
my $sType = "";
my $sHelp = 0;
my $sTable = "";
my $sSkCom = 1;
my $sOffset = 1;
my $sTarget = "";
my $db;
my $nb_columns;
my @str_col = (); #Use basic escape.
my @str_col2 = (); #Use second escape (currently for scripts).
my $line_format;
my $create_table;
my @defaults = ();
Main();
sub GetArgs {
GetOptions(
'i=s' => \$sDirPath, #input directory path
'o=s' => \$sFileout, #input out filename
'table=s' => \$sTable, #Table name.
'm=s' => \$sType, #Database: map,castle
'c=i' => \$sSkCom, #skip comment ?
's=i' => \$sOffset, #offset for id
't=s' => \$sTarget, #Renewal setting: pre-re, re.
'help!' => \$sHelp,
) or $sHelp=1; #Display help if invalid options are supplied.
my $sValidType = "map|castle|abra|impro|mpouch|mboss|mbranch|mporing";
my $sValidTarget = "re|pre";
if( $sHelp ) {
print "Incorrect option specified. Available options:\n"
."\t --o=filename => Output file name. \n"
."\t --i=filename => input directory path \n"
."\t --c=[0/1] => skip comment ?\n"
."\t --s=[int] => offset for id \n"
."\t --table=tablename => Table name to create. \n";
exit;
}
unless($sDirPath or $sFileout){
print "ERROR: DirPath and Filename_out are required to continue.\n";
exit;
}
unless($sType =~ /$sValidType/i){
print "ERROR: Incorrect type specified. Available types:\n"
."\t --m => Type (specify which data entry to use [$sValidType]).\n";
exit;
}
if($sTarget && $sTarget !~ /$sValidTarget/i){
print "ERROR: Incorrect target specified. Available targets:\n"
."\t --t => Target (specify which kind of table_struct to build [$sValidTarget]).\n";
exit;
}
}
sub Main {
GetArgs();
BuildDataForType($sType);
ConvertFile($sDirPath,$sFileout,$sType);
print "Conversion ended.\n";
}
sub ConvertFile { my($sDirPath,$sFileout,$sType)=@_;
my $sFHout;
my $sFileIn;
my $sOldChamp=0;
if($sType =~ /map/i){ $sFileIn = "map_index.txt" }
elsif($sType =~ /castle/i) { $sFileIn = "castle_db.txt"; }
elsif($sType =~ /abra/i) { $sFileIn = "abra_db.txt"; }
elsif($sType =~ /impro/i) { $sFileIn = "skill_improvise_db.txt"; }
elsif($sType =~ /mpouch/i) { $sFileIn = "mob_pouch.txt"; }
elsif($sType =~ /mboss/i) { $sFileIn = "mob_boss.txt"; }
elsif($sType =~ /mbranch/i) { $sFileIn = "mob_branch.txt"; }
elsif($sType =~ /mporing/i) { $sFileIn = "mob_poring.txt"; }
print "Starting ConvertFile with: \n\t DirPath=$sDirPath targetFiles=[ $sFileIn ] \n\t fileout=$sFileout \n";
if($sType =~ /mboss|mbranch|mporing/i){
if($sTarget =~ /pre/i){ $sDirPath .= "/pre-re/"; }
elsif($sTarget =~ /Re/i){ $sDirPath .= "/re/"; }
}
chdir $sDirPath;
open FHIN,"$sFileIn" or die "ERROR: Can't read or locate $sDirPath$sFileIn.\n";
open $sFHout,">$sFileout" or die "ERROR: Can't write $sDirPath$sFileout.\n";
printf $sFHout ("%s\n",$create_table);
while(my $ligne=<FHIN>) {
my $sIsCom=0;
if ($ligne =~ /^\s*$/ ) {
print $sFHout "\n";
next;
}
if ($ligne =~ /[^\r\n]+/) {
$ligne = $&;
if ($ligne =~ /^\/\//) {
next if($sSkCom);
printf $sFHout ("#");
$ligne = substr($ligne, 2);
$sIsCom=1;
}
my @champ = ();
if ($sType =~ /map/i ) {
@champ = split('\s',$ligne);
if($#champ < 2 && $champ[0] =~ /\w/ && $champ[0] !~ /\:/ ) {
if($#champ == 1) { $sOldChamp=$champ[1] }
else { $sOldChamp+=1; }
$champ[1] = $sOldChamp;
}
}
elsif ($sType =~ /castle|abra/i) {
my @desc = ();
@champ = split(",",$ligne);
if ($#champ >= 4) {
@desc = split('\t',$champ[4]);
$champ[4] = $desc[0];
}
}
elsif ($sType =~ /impro/i) {
my @desc = ();
@champ = split(",",$ligne);
if ($#champ >= 1) {
@desc = split('\t',$champ[1]);
$champ[1] = $desc[0];
}
}
elsif($sType =~ /mpouch|mboss|mbranch|mporing/i) {
my @desc = ();
@champ = split(",",$ligne);
if ($#champ >= $nb_columns - 1) {
@desc = split('\t',$champ[$nb_columns - 1]);
$champ[$nb_columns - 1] = $desc[0];
}
}
if ($#champ != $nb_columns - 1) { #Can't parse, it's a real comment.
printf $sFHout ("%s\n", $ligne);
} else {
if ($sType =~ /castle/i ) {
foreach my $col (@str_col) {
my $string = $champ[$col];
$string = escape($string,"'","\\'");
$string =~ s/\s+$//; #Remove trailing spaces.
$string =~ s/^\s+//; #Remove leading spaces.
$champ[$col] = $string;
}
print $sFHout "REPLACE INTO `$db`( `id`, `map_id`, `name`, `guildbreakevent` ) VALUES ( '".($champ[0]+$sOffset)."\', ( SELECT DISTINCT(`id`) FROM `maps` WHERE `name` = \'".$champ[1]."\' ), \'".$champ[2]."\', \'".$champ[3]."\');\n";
}
elsif($sType =~ /map/i) { printf $sFHout ("REPLACE INTO `%s`( `name`, `id` ) VALUES (", $db);
for (my $i=0; $i<$#champ; $i++) {
printField($sFHout,$champ[$i],",",$i);
}
printField($sFHout,$champ[$#champ],");\n",$#champ);
}
elsif($sType =~ /abra|impro/i) {
my $sSkID = 294; #if impro
$sSkID = 290 if($sType =~ /abra/i);
print $sFHout "REPLACE INTO `$db`( `skill_id`, `target_skill_id`, `rate`) VALUES ( ".$sSkID.",".$champ[0].",".$champ[2].");\n";
}
elsif($sType =~ /mpouch|mboss|mbranch|mporing/i) {
my $sItID = 0; #if impro
if($sType =~ /mpouch/i) { $sItID = 12024; }
elsif($sType =~ /mboss/i) { $sItID = 12103; }
elsif($sType =~ /mbranch/i) { $sItID = 604; }
elsif($sType =~ /mporing/i) { $sItID = 12109; }
print $sFHout "REPLACE INTO `$db`( `item_id`, `mob_id`, `rate`) VALUES ( ".$sItID.",".$champ[0].",".$champ[2].");\n";
}
}
}
}
print $sFHout "\n";
}
sub printField { my ($sFHout,$str, $suffix, $idCol) = @_;
# Remove first { and last } .
if ($str =~ /{.*}/) {
$str = substr($&,1,-1);
}
# If nothing, put NULL.
if ($str eq "") {
my $sDef;
if(scalar(@defaults)) { $sDef = $defaults[$idCol]; } #Use default in array.
else { $sDef = "NULL" unless($sDef); } #Let SQL handle the default.
print $sFHout "$sDef$suffix";
} else {
my $flag = 0;
# Search if it's a string column?
foreach my $col (@str_col) {
if ($col == $idCol) {
$flag |= 1;
last;
}
}
foreach my $col (@str_col2) {
if ($col == $idCol) {
$flag |= 2;
last;
}
}
if ($flag & 3) {
# String column, so escape , remove trailing and add '' .
my $string;
$string = escape($str,"'","\\'") if($flag & 1) ;
$string =~ s/\s+$//; #Remove trailing spaces.
$string =~ s/^\s+//; #Remove leading spaces.
$string = escape($string,'\\\"','\\\\\"') if($flag & 2) ;
printf $sFHout ("'%s'%s", $string, $suffix);
} else {
# Not a string column.
printf $sFHout ("%s%s", $str,$suffix);
}
}
}
sub escape { my ($str,$sregex,$sreplace) = @_;
my @str_splitted = split($sregex, $str);
my $result = "";
for (my $i=0; $i<=$#str_splitted; $i++) {
if ($i == 0) {
$result = $str_splitted[0];
} else {
$result = $result.$sreplace.$str_splitted[$i];
}
}
return $result
}
sub BuildDataForType{ my($sType) = @_;
print "Starting BuildDataForType with: \n\t Type=$sType \n";
if($sType =~ /castle/i) {
$db = $sTable;
$db = "castles" unless($db);
$nb_columns = 5;
@str_col = (3,4);
@str_col2 = ();
$create_table =
"#
# Table structure for table `$db`
#
DROP TABLE IF EXISTS `$db`;
CREATE TABLE `castles` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`map_id` int(11) unsigned NOT NULL,
`name` varchar(255) DEFAULT '',
`guildbreakevent` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
";
}
elsif($sType =~ /map/i){
$db = $sTable;
$db = "maps" unless($db);
$nb_columns = 2;
@str_col = (0);
@str_col2 = ();
$line_format = "";
$create_table =
"#
# Table structure for table `$db`
#
DROP TABLE IF EXISTS `$db`;
CREATE TABLE `$db` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
";
}
elsif($sType =~ /abra|impro/i){
$db = $sTable;
$db = "skills_random" unless($db);
$nb_columns = 3;
$nb_columns = 2 if($sType =~ /impro/i);
@str_col = ();
@str_col2 = ();
$line_format = "";
$create_table =
"#
# Table structure for table `$db`
#
DROP TABLE IF EXISTS `$db`;
CREATE TABLE `$db` (
`skill_id` int(11) unsigned NOT NULL,
`target_skill_id` int(11) unsigned NOT NULL,
`rate` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`skill_id`,`target_skill_id`)
) ENGINE=MyISAM;
";
}
elsif($sType =~ /mpouch|mboss|mbranch|mporing/i){
$db = $sTable;
$db = "item_mobs_random" unless($db);
$nb_columns = 3;
@str_col = ();
@str_col2 = ();
$line_format = "";
$create_table =
"#
# Table structure for table `$db`
#
DROP TABLE IF EXISTS `$db`;
CREATE TABLE `$db` (
`item_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`mob_id` mediumint(9) unsigned NOT NULL DEFAULT '0',
`rate` int(11) unsigned NOT NULL,
PRIMARY KEY (`item_id`,`mob_id`)
) ENGINE=MyISAM;
";
}
}