[Tool] Perl Converter TXT -> SQL

[GreatWizard]

Hello,

Here is the perl script that I have create with regular expressions to convert every database files from TXT to SQL.

So in your configuration conf/inter_athena.conf you can activate:

use_sql_db: yes

Perl script txt2sql (Don't forget to set this file executable) :

#!/usr/bin/perl

use strict;
use warnings;

my ($db) = $ARGV[0] =~ '^([^.]+)';

open(my $out, ">$db.sql") || die "Impossible d'écrire le fichier.";

while(<>) {
	next if m!^$|^//!;

	print $out "REPLACE INTO $db VALUES (";
	s/'/''/g;

	my ($acc) = $_ =~ /[^{]+(.*)/;
	$acc =~ s/(^|,){s*/$1'/g;
	$acc =~ s/s*}(,|$)/'$1/g;

	s/([^,]*),/'$1',/g;
	s/'(d+|0x[A-F0-9]+)'/$1/ig;
	s/([^{]+)'{.*/$1$acc/;

	s/''/NULL/g;
	s///g;
	s/"/"/g;
	s/,$/,NULL/;

	print $out "$_);n";
}

close $out;

exit 0;

Uses:

### My architecture:
#Dir rathena = SVN rAthena
cd rathena/db/

### Convert
~/txt2sql item_db.txt
~/txt2sql item_db_re.txt
~/txt2sql item_db2.txt
~/txt2sql mob_db.txt
~/txt2sql mob_db2.txt
~/txt2sql mob_skill_db.txt
~/txt2sql mob_skill_db2.txt

### Update SQL
# user = ragnarok
# password = pwet
# db = ragnarokdb
mysql --user=ragnarok --password=pwet ragnarokdb < item_db.sql
mysql --user=ragnarok --password=pwet ragnarokdb < item_db_re.sql
mysql --user=ragnarok --password=pwet ragnarokdb < item_db2.sql
mysql --user=ragnarok --password=pwet ragnarokdb < mob_db.sql
mysql --user=ragnarok --password=pwet ragnarokdb < mob_db2.sql
mysql --user=ragnarok --password=pwet ragnarokdb < mob_skill_db.sql # DON'T FORGET TO FLUSH mob_skill_db FIRSTLY
mysql --user=ragnarok --password=pwet ragnarokdb < mob_skill_db2.sql # DON'T FORGET TO FLUSH mob_skill_db2 FIRSTLY

Merry Christmas !

Edited December 26, 2011 by GreatWizard

[CalciumKid]

Does this escape single quotes?

[GreatWizard]

Of course

I use it on my server and load database from SQL without problems.

Edit: A small example

Input Data:

21001,Bellum_Claymore,Bellum Claymore,4,20,,3500,260,,1,0,0x00004082,7,2,34,4,95,1,3,{ bonus2 bAddRace,RC_DemiHuman,80; bonus2 bIgnoreDefRate,RC_DemiHuman,30; if(getrefine()>=6) { bonus2 bAddRace,RC_DemiHuman,40; } if(getrefine()>=9) {  autobonus2 "{ bonus bShortWeaponDamageReturn,20; bonus bMagicDamageReturn,20; }",200,1000,BF_WEAPON,"{ specialeffect2 EF_REFLECTSHIELD; }"; } },{},{}

Output Data:

REPLACE INTO item_db VALUES (21001,'Bellum_Claymore','Bellum Claymore',4,20,NULL,3500,260,NULL,1,0,0x00004082,7,2,34,4,95,1,3,'bonus2 bAddRace,RC_DemiHuman,80; bonus2 bIgnoreDefRate,RC_DemiHuman,30; if(getrefine()>=6) { bonus2 bAddRace,RC_DemiHuman,40; } if(getrefine()>=9) {  autobonus2 "{ bonus bShortWeaponDamageReturn,20; bonus bMagicDamageReturn,20; }",200,1000,BF_WEAPON,"{ specialeffect2 EF_REFLECTSHIELD; }"; }',NULL,NULL
);

Edited December 26, 2011 by GreatWizard