I put some thought into it and turns out when I said:
Quote:
|
Unfortunately, to do this you'd need every database to be consistent and they're not.
|
I was wrong. I was looking at it from the wrong direction (going from ingredients in the old recipe to ingredients in the new recipe). This allowed me to catch a lot of tradeskills where people had entered them in wrong, but as a conversion script it requires too much manual work.
Looking at it from the other side, where I know exactly what we want to get as a result makes it much more easily scripted.
I realize that most everyone already knows how to work around this (and that all of the tools take the query differences into account), but just from looking at the PEQ database I was able to find at least 30 counts where whoever was entering the recipe made the mistake of not taking into account that you can't return a component for a recipe without at least a second entry.
Granted, 1% of recipes is a very very small number. I'm not even sure why this bothers me so much. It's been years since I did 1NF conversions.
At any rate, the following steps will convert any database to the proposed format. It does require PHP because that's the language I write fastest in, but that shouldn't be a problem for anyone wanting to do a database conversion.
Always back up your database before running anything against it no matter how safe the person who wrote it says it is.
Step 1 -- Modify your table so we can do comparisons:
Code:
ALTER TABLE `tradeskill_recipe_entries` ADD COLUMN `isnewrecipe` TINYINT(1) NOT NULL DEFAULT 1 AFTER `iscontainer`;
UPDATE `tradeskill_recipe_entries` SET isnewrecipe=0;
Step 2 -- Generate new recipes: (ConvertRecipes.php)
PHP Code:
<?php
//Modify the following variables to match your database
$dbhost = "localhost";
$database = "projecteq";
$username = "databaseuser";
$password = "databasepass";
$timeout = 60000; // in seconds
//stop editing here
set_time_limit($timeout);
$link = mysql_connect($dbhost, $username, $password)
or die('Could not connect: ' . mysql_error());
//First we get all of the old recipe_id information
$query = "SELECT DISTINCT recipe_id FROM `$database`.`tradeskill_recipe_entries` WHERE isnewrecipe=0;";
$result = mysql_query($query)
or die(mysql_error());
print "Converting recipes, this may take a while.";
//Now we need to loop through all of those IDs we just got:
while($row = mysql_fetch_assoc($result)) {
$recipe_ID = $row['recipe_id'];
$newrecipe = getRecipeComponentsArray($recipe_ID, $database);
$newrecipe = getRecipeContainer($recipe_ID, $newrecipe, $database);
$newrecipe = getRecipeFailureArray($recipe_ID, $newrecipe, $database);
$newrecipe = getRecipeSuccessArray($recipe_ID, $newrecipe, $database);
foreach ($newrecipe as $key => $value) {
if($value["componentcount"] == Null) {
$value["componentcount"] = 0;
}
if($value["iscontainer"] == Null) {
$value["iscontainer"] = 0;
}
if($value["successcount"] == Null) {
$value["successcount"] = 0;
}
if($value["failcount"] == Null) {
$value["failcount"] = 0;
}
if($value["componentcount"] == Null) {
$value["componentcount"] = 0;
}
if ($key == Null) {
//Do nothing if we don't have an item ID
}
else {
$insertquery = "INSERT INTO `$database`.`tradeskill_recipe_entries` (recipe_id,item_id,successcount,failcount,componentcount,iscontainer,isnewrecipe) VALUES ($recipe_ID, $key, " . $value["successcount"] . ", " . $value["failcount"] . ", " . $value["componentcount"] . ", " . $value["iscontainer"] . ", 1);";
//echo $insertquery . "\n";
mysql_query($insertquery)
or die (mysql_error());
}
}
}
print "and DONE!";
function getRecipeComponentsArray($func_recipeid, $func_database) {
$func_query = "SELECT item_id, componentcount FROM `$func_database`.`tradeskill_recipe_entries` WHERE recipe_id = $func_recipeid AND componentcount > 0 AND isnewrecipe=0 ORDER BY item_id";
$func_result = mysql_query($func_query)
or die(mysql_error());
while ($func_row = mysql_fetch_assoc($func_result)) {
$func_recipereturn[$func_row['item_id']]["componentcount"] = $func_recipereturn[$func_row['item_id']]["componentcount"] + $func_row['componentcount'];
}
return $func_recipereturn;
}
function getRecipeContainer($func_recipeid, $func_recipereturn, $func_database) {
$func_query = "SELECT item_id FROM `$func_database`.`tradeskill_recipe_entries` WHERE recipe_id = $func_recipeid AND iscontainer = 1 AND isnewrecipe=0 ORDER BY item_id;";
$func_result = mysql_query($func_query)
or die(mysql_error());
$func_row = mysql_fetch_assoc($func_result);
$func_recipereturn[$func_row['item_id']]["iscontainer"] = 1;
return $func_recipereturn;
}
function getRecipeFailureArray($func_recipeid, $func_recipereturn, $func_database) {
$func_query = "SELECT item_id, failcount FROM `$func_database`.`tradeskill_recipe_entries` WHERE failcount>0 AND componentcount=0 AND recipe_id=$func_recipeid AND isnewrecipe=0 ORDER BY item_id;";
$func_result = mysql_query($func_query)
or die(mysql_error());
while ($func_row = mysql_fetch_assoc($func_result)) {
$func_recipereturn[$func_row['item_id']]["failcount"] = $func_recipereturn[$func_row['item_id']]["failcount"] + $func_row['failcount'];
}
return $func_recipereturn;
}
function getRecipeSuccessArray($func_recipeid, $func_recipereturn, $func_database) {
$func_query = "SELECT item_id, successcount FROM `$func_database`.`tradeskill_recipe_entries` WHERE successcount>0 AND componentcount=0 AND recipe_id=$func_recipeid AND isnewrecipe=0 ORDER BY item_id;";
$func_result = mysql_query($func_query)
or die(mysql_error());
while ($func_row = mysql_fetch_assoc($func_result)) {
$func_recipereturn[$func_row['item_id']]["successcount"] = $func_recipereturn[$func_row['item_id']]["successcount"] + $func_row['successcount'];
}
return $func_recipereturn;
}
?>