Loading...   


use DBI;
use DBD::mysql;

my $database_name = "";

my $total_items = 0;
my $read_items = "items.txt"; #default

my $dbh = LoadMysql();
ReadItems();
printf "$total_items items added to database...                        \r\n";

Update_Items_Table();


sub LoadMysql{	
	# CONFIG VARIABLES
	my $confile = "eqemu_config.xml"; #default
	open(F, "<$confile") or die "Unable to open config: $confile\n";
	my $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; }
	}
	$database_name = $db;
	#::: DATA SOURCE NAME
	$dsn = "dbi:mysql:$db:localhost:3306";
	#::: PERL DBI CONNECT
	$connect = DBI->connect($dsn, $user, $pass);
	return $connect;
}

sub ReadItems {
	#Read file and place into Array
	open(F, "<$read_items") or die "Unable to open itemfile: $read_items\n";
	my @lines = <F>;
	close(F);

	my @newlines;
	chomp($lines[0]); #Chomp this array...
	@fields=split("(?<!\\\\)\\|", $lines[0]);
	
	my $db_check = "SHOW TABLES LIKE 'items_floor'";
	
	my $sth = $dbh->prepare("$db_check");
	$sth->execute();
	my $answer = $sth->fetchrow_array();
	
	if ($answer eq '') {
		my $create_db = "CREATE TABLE `items_floor` (`" . join("` VARCHAR(64) NOT NULL DEFAULT '', `", @fields). "` VARCHAR(64) NOT NULL DEFAULT '', UNIQUE INDEX `ID` (`id`)) COLLATE='latin1_swedish_ci' ENGINE=MyISAM";

		$dbh->do($create_db);
		
		my $alter_db = "ALTER TABLE `items_floor` CHANGE `id` `id` INT(11) NOT NULL DEFAULT '0'";
		$dbh->do($alter_db);
		
		printf "Database items_floor created\n";
	}
	$insert="replace into items_floor (".join(",",@fields).") values (\"";
	
	for (1 .. $#lines) {
		@f=split("(?<!\\\\)\\|", $lines[$_]);
		foreach (@f) {
			$_ =~ s/\"/\\"/g;
		}
		$insert2=join("\",\"",@f);
		$insert2 =~ s/\'/\\'/g;
		$new_query = $insert . $insert2 . "\")";
		$dbh->do($new_query);
		$total_items++;
		printf("Processing: %d %s                        \r",$f[5],$f[1]);
	}
}

sub Update_Items_Table {
	
	my @matching_table;
	
	my @missing_items_table;
	my @missing_items_floor_table;
	
	my $items_query = "SELECT column_name FROM information_schema.COLUMNS WHERE table_name = 'items' and table_schema = '$database_name'";
	my $sth = $dbh->prepare("$items_query");
	$sth->execute();
	my @items_table;
	while (my @row = $sth->fetchrow_array()) {
		push(@items_table, $row[0]);
	}
	
	my $items_floor_query = "SELECT column_name FROM information_schema.COLUMNS WHERE table_name = 'items_floor' and table_schema = '$database_name'";
	my $sth2 = $dbh->prepare("$items_floor_query");
	$sth2->execute();
	my @items_floor_table;
	while (my @row = $sth2->fetchrow_array()) {
		push(@items_floor_table, $row[0]);
	}
	
	foreach $value (@items_table) {
		if ( grep( /^$value$/i, @items_floor_table ) ) {
			push(@matching_table, $value);
			#if ($value eq "id") {
			#	print "Adding ID to matching table...\n";
			#}
		} else {
			#What values are we missing from EMU items table..
			push(@missing_items_table, $value);
		}
	}
	
	#What values are we missing from.. 13thFloor
	foreach $value (@items_floor_table) {
		if ( grep( /^$value$/i, @items_table ) ) {
			#DO NOTHING...
		} else {
			push(@missing_items_floor_table, $value);
		}
	}
	
	
	
	my $update_query2 = "";
	my $items_field_list = "";
	my $items_floor_field_list = "";
	foreach $match (@matching_table) {
		$match = lc($match);
		if ($match eq $matching_table[$#matching_table]) {
			#Last in matches..
			$update_fields .= "`$match` = fi.`$match`";
			$items_field_list .= "`$match`";
			$items_floor_field_list .= "fi.`$match`";
		} else {
			$update_fields .= "`$match` = fi.`$match`, ";
			$items_field_list .= "`$match`, ";
			$items_floor_field_list .= "fi.`$match`, ";
		}
	}
	
	#MIXED UP FIELDS...
	
	$items_floor_field_list =~ s/booktype/booklang/g; #our booktype is mixed with theirs...
	$update_fields =~ s/`booktype` = fi.`booktype`/`booktype` = fi.`booklang`/g;
	
	
	
	## FIELDS THAT DO NOT MATCH GO HERE
	my @items_add = 	  ("casttime_", "endur", 	"range", 	"attuneable", "evolvinglevel", "herosforgemodel", "scrolltype", 	"scriptfileid",
							"powersourcecapacity", "augslot1unk2", "augslot2unk2", "augslot3unk2", "augslot4unk2", "augslot5unk2", "augslot6unk2",
							"recskill", "book"
							);
	my @items_floor_add = ("foodduration", "endurance", "therange", "attunable",	"evolvl", 		"heroforge1",	   "scrolleffecttype", "rightclickscriptid",
							"powersourcecap",	   "augslot1unk", "augslot2unk", "augslot3unk", "augslot4unk", "augslot5unk", "augslot6unk",
							"reqskill", "booktype"
							);
	
	#Match the missmatched fields...
	my $spot = 0;
	foreach $value (@items_add) {
		$items_field_list .= ", `$value`";
		$update_fields .= ", `$value` = fi.`$items_floor_add[$spot]`";
		$spot++;
		@missing_items_table = grep {$_ ne $value} @missing_items_table;
	}
	foreach $value (@items_floor_add) {
		$items_floor_field_list .= ", fi.`$value`";
		@missing_items_floor_table = grep {$_ ne $value} @missing_items_floor_table;
	}
	
	
	my $update_query = "INSERT INTO items ($items_field_list) SELECT $items_floor_field_list FROM items_floor fi ON DUPLICATE KEY UPDATE $update_fields";
	
	
	##WAS TO FIND MISSING FIELDS....
	my $write_file = "missing_item_fields.txt";
	open(F, ">$write_file") or die "Unable to open questfile: $write_file\n";
	print F "$update_query \n\n";
	print F "EQEMU items Table missing fields\n";
	foreach $value (@missing_items_table) {
		print F "$value\n";
	}
	print F "\n\n13thFloor items Table missing fields\n";
	foreach $value (@missing_items_floor_table) {
		print F "$value\n";
	}
	close(F);
	
	$dbh->do($update_query);
	
	$dbh->do("UPDATE items i SET i.stackable = 1 WHERE i.stacksize > 1");
	
	print "Added all new items to Items table!\n";
	
}

Raw Paste Data