lerxst2112
09-22-2011, 05:52 PM
I put together a quick and dirty update to the load_13thfloor_items.pl script to import the current file format in use at http://eqitems.13th-floor.org/
After researching the differences between the fields in the items database and the items.txt file I made these lists:
Fields present in items table that are not present in the 13th floor items.txt file
clickunk6, comment, focusunk6, heroic_cr, heroic_dr, heroic_fr, heroic_mr, heroic_pr, heroic_svcorrup, minstatus, pendingloreflag, procunk6, scrollunk6, serialization, serialized, stackable, summonedflag, wornunk6, UNK012, UNK033, UNK054, UNK059, UNK123, UNK127, UNK134, UNK137, UNK142, UNK147, UNK152, UNK157, UNK193
Fields present in the 13th floor items.txt file that are not present in the items table.
UNK109, UNK219, UNK220, UNK221, UNK222, UNK223, UNK224, UNK225, UNK226, UNK227, UNK228, UNK229, UNK230, UNK231, UNK232, UNK233, UNK234, UNK235, UNK236, UNK237, UNK238, UNK239, UNK240, UNK241, UNK242
The only field no longer present in items.txt that seemed important to me was the stackable field, so I modified the script to set it to true on all items that have a stacksize > 1. I don't believe any items have heroic saves on them, and summonedflag seems to be used very inconsistently.
I also set the bagslots to 10 on all items with more than that to avoid breaking existing code that assumes 10 is the upper limit. If you do import the bags you'll probably notice a fair number of discrepancies from what you might expect since a lot of sizes changed on live.
Anyway, if you're feeling brave and want to import new items here's the script. No warranties, etc. Always keep a backup. :)
#! /usr/bin/perl
use DBI;
use Getopt::Std;
getopts('d:h');
$conf = "eqemu_config.xml";
if($opt_h) {
die "Usage: load_13thfloor_items.pl [-d path/to/eqemu_config.xml]\n";
}
if($opt_d) {
$conf = $opt_d;
}
$db = "eq";
$user = "eq";
$pass = "eq";
$host = "localhost";
open(F, "<$conf") or die "Unable to open config $conf\n";
$indb = 0;
while(<F>) {
s/\r//g;
if(/<database>/i) {
$indb = 1;
}
next unless($indb == 1);
if(/<\/database>/i) {
$indb = 0;
last;
}
if(/<host>(.*)<\/host>/i) {
$host = $1;
} elsif(/<username>(.*)<\/username>/i) {
$user = $1;
} elsif(/<password>(.*)<\/password>/i) {
$pass = $1;
} elsif(/<db>(.*)<\/db>/i) {
$db = $1;
}
}
if(!$db || !$user || !$pass || !$host) {
die "Invalid db.ini, missing one of: host, user, password, database\n";
}
$source="DBI:mysql:database=$db;host=$host";
my $dbh = DBI->connect($source, $user, $pass) || die "Could not create db handle\n";
$_=<STDIN>;
chomp();
s/'/\\'/g;
@fields=split("(?<!\\\\)\\|", $_);
#strip out unknown fields we don't have in the current item database
splice(@fields,114,1);
splice(@fields,218,24);
%conversions = (
"itemtype" => "itemuse"
);
$insert="replace into items (".join(",",@fields).",source,updated) values ('";
#select(STDOUT); $|=1;
while(<STDIN>) {
chomp();
s/'/\\'/g;
@f=split("(?<!\\\\)\\|", $_);
#strip out unknown fields we don't have in the current item database
splice(@f,114,1);
splice(@f,218,24);
$insert2=join("','",@f);
$#f--;
grep(s/\\\|/\\\\\|/g,@f);
grep(s/"/\\\\"/g,@f);
$statement=sprintf("%s%s','13THFLOOR',now())",$insert,$insert2,join('|',@f));
$dbh->do($statement);
printf("Processing: %d %s \r",$f[5],$f[1]);
++$count;
}
printf("Processed: %d items(s) \n",$count);
#fix stackable flag on items since it isn't in the latest 13th floor data.
#My assumption is that all items with a stack count > 1 are stackable.
#That seems to be how 13th floor displays them as stackable
print("Setting items with a stacksize > 1 to stackable\n");
$statement = "update items set stackable = 1 where stacksize > 1";
$dbh->do($statement);
print("Setting bagslots > 10 to 10\n");
$statement = "update items set bagslots = 10 where bagslots > 10";
$dbh->do($statement);
After researching the differences between the fields in the items database and the items.txt file I made these lists:
Fields present in items table that are not present in the 13th floor items.txt file
clickunk6, comment, focusunk6, heroic_cr, heroic_dr, heroic_fr, heroic_mr, heroic_pr, heroic_svcorrup, minstatus, pendingloreflag, procunk6, scrollunk6, serialization, serialized, stackable, summonedflag, wornunk6, UNK012, UNK033, UNK054, UNK059, UNK123, UNK127, UNK134, UNK137, UNK142, UNK147, UNK152, UNK157, UNK193
Fields present in the 13th floor items.txt file that are not present in the items table.
UNK109, UNK219, UNK220, UNK221, UNK222, UNK223, UNK224, UNK225, UNK226, UNK227, UNK228, UNK229, UNK230, UNK231, UNK232, UNK233, UNK234, UNK235, UNK236, UNK237, UNK238, UNK239, UNK240, UNK241, UNK242
The only field no longer present in items.txt that seemed important to me was the stackable field, so I modified the script to set it to true on all items that have a stacksize > 1. I don't believe any items have heroic saves on them, and summonedflag seems to be used very inconsistently.
I also set the bagslots to 10 on all items with more than that to avoid breaking existing code that assumes 10 is the upper limit. If you do import the bags you'll probably notice a fair number of discrepancies from what you might expect since a lot of sizes changed on live.
Anyway, if you're feeling brave and want to import new items here's the script. No warranties, etc. Always keep a backup. :)
#! /usr/bin/perl
use DBI;
use Getopt::Std;
getopts('d:h');
$conf = "eqemu_config.xml";
if($opt_h) {
die "Usage: load_13thfloor_items.pl [-d path/to/eqemu_config.xml]\n";
}
if($opt_d) {
$conf = $opt_d;
}
$db = "eq";
$user = "eq";
$pass = "eq";
$host = "localhost";
open(F, "<$conf") or die "Unable to open config $conf\n";
$indb = 0;
while(<F>) {
s/\r//g;
if(/<database>/i) {
$indb = 1;
}
next unless($indb == 1);
if(/<\/database>/i) {
$indb = 0;
last;
}
if(/<host>(.*)<\/host>/i) {
$host = $1;
} elsif(/<username>(.*)<\/username>/i) {
$user = $1;
} elsif(/<password>(.*)<\/password>/i) {
$pass = $1;
} elsif(/<db>(.*)<\/db>/i) {
$db = $1;
}
}
if(!$db || !$user || !$pass || !$host) {
die "Invalid db.ini, missing one of: host, user, password, database\n";
}
$source="DBI:mysql:database=$db;host=$host";
my $dbh = DBI->connect($source, $user, $pass) || die "Could not create db handle\n";
$_=<STDIN>;
chomp();
s/'/\\'/g;
@fields=split("(?<!\\\\)\\|", $_);
#strip out unknown fields we don't have in the current item database
splice(@fields,114,1);
splice(@fields,218,24);
%conversions = (
"itemtype" => "itemuse"
);
$insert="replace into items (".join(",",@fields).",source,updated) values ('";
#select(STDOUT); $|=1;
while(<STDIN>) {
chomp();
s/'/\\'/g;
@f=split("(?<!\\\\)\\|", $_);
#strip out unknown fields we don't have in the current item database
splice(@f,114,1);
splice(@f,218,24);
$insert2=join("','",@f);
$#f--;
grep(s/\\\|/\\\\\|/g,@f);
grep(s/"/\\\\"/g,@f);
$statement=sprintf("%s%s','13THFLOOR',now())",$insert,$insert2,join('|',@f));
$dbh->do($statement);
printf("Processing: %d %s \r",$f[5],$f[1]);
++$count;
}
printf("Processed: %d items(s) \n",$count);
#fix stackable flag on items since it isn't in the latest 13th floor data.
#My assumption is that all items with a stack count > 1 are stackable.
#That seems to be how 13th floor displays them as stackable
print("Setting items with a stacksize > 1 to stackable\n");
$statement = "update items set stackable = 1 where stacksize > 1";
$dbh->do($statement);
print("Setting bagslots > 10 to 10\n");
$statement = "update items set bagslots = 10 where bagslots > 10";
$dbh->do($statement);