path: root/krecipes/src/backends/qsqlrecipedb.cpp

/***************************************************************************
*   Copyright (C) 2003 by                                                 *
*   Unai Garro (ugarro@users.sourceforge.net)                             *
*   Cyril Bosselut (bosselut@b1project.com)                               *
*   Jason Kivlighn (jkivlighn@gmail.com)                                  *
*                                                                         *
*   Copyright (C) 2004-2006 Jason Kivlighn (jkivlighn@gmail.com)          *
*                                                                         *
*   This program is free software; you can redistribute it and/or modify  *
*   it under the terms of the GNU General Public License as published by  *
*   the Free Software Foundation; either version 2 of the License, or     *
*   (at your option) any later version.                                   *
***************************************************************************/

#include <stdlib.h>

#include "qsqlrecipedb.h"
#include "datablocks/categorytree.h"
#include "datablocks/rating.h"
#include "datablocks/weight.h"

#include "propertycalculator.h"

#include <ntqbuffer.h>
#include <ntqtextcodec.h>
#include <ntqvariant.h>

#include <tdeapplication.h>
#include <kdebug.h>
#include <kstandarddirs.h>
#include <tdetempfile.h>
#include <tdelocale.h>
#include <tdemessagebox.h>
#include <kmdcodec.h>

int TQSqlRecipeDB::m_refCount = 0;

TQSqlRecipeDB::TQSqlRecipeDB( const TQString &host, const TQString &user, const TQString &pass, const TQString &name, int port ) : RecipeDB(),
	connectionName("connection" + TQString::number( m_refCount+1 ))
{
	DBuser = user;
	DBpass = pass;
	DBhost = host;
	DBname = name;
	DBport = port;

	dbOK = false; //it isn't ok until we've connect()'ed
	++m_refCount;

	TQTextCodec::setCodecForCStrings(TQTextCodec::codecForName("Latin1"));  //this is the default, but let's explicitly set this to be sure
}

TQSqlRecipeDB::~TQSqlRecipeDB()
{
	if ( dbOK ) {
		database->close();
	}

	TQSqlDatabase::removeDatabase( connectionName );
	--m_refCount;
}

void TQSqlRecipeDB::connect( bool create_db, bool create_tables )
{
	kdDebug() << i18n( "TQSqlRecipeDB: Opening Database..." ) << endl;
	kdDebug() << "Parameters: \n\thost: " << DBhost << "\n\tuser: " << DBuser << "\n\ttable: " << DBname << endl;

	bool driver_found = false;

	if ( qsqlDriver() ) //we're using a built-in driver
		driver_found = true;
	else {
		TQStringList drivers = TQSqlDatabase::drivers();
		for ( TQStringList::const_iterator it = drivers.begin(); it != drivers.end(); ++it ) {
			if ( ( *it ) == qsqlDriverPlugin() ) {
				driver_found = true;
				break;
			}
		}
	}

	if ( !driver_found ) {
		dbErr = TQString( i18n( "The TQt database plug-in (%1) is not installed.  This plug-in is required for using this database backend." ) ).arg( qsqlDriverPlugin() );
		return ;
	}

	//we need to have a unique connection name for each TQSqlRecipeDB class as multiple db's may be open at once (db to db transfer)
	if ( qsqlDriver() )
		database = TQSqlDatabase::addDatabase( qsqlDriver(), connectionName );
	else if ( !qsqlDriverPlugin().isEmpty() )
		database = TQSqlDatabase::addDatabase( qsqlDriverPlugin(), connectionName );
	else
		kdDebug()<<"Fatal internal error!  Backend incorrectly written!"<<endl;

	database->setDatabaseName( DBname );
	if ( !( DBuser.isNull() ) )
		database->setUserName( DBuser );
	if ( !( DBpass.isNull() ) )
		database->setPassword( DBpass );
	database->setHostName( DBhost );
	if ( DBport > 0 )
        	database->setPort(DBport);

	kdDebug() << i18n( "Parameters set. Calling db->open()" ) << endl;

	if ( !database->open() ) {
		//Try to create the database
		if ( create_db ) {
			kdDebug() << i18n( "Failing to open database. Trying to create it" ) << endl;
			createDB();
		}
		else {
			// Handle the error (passively)
			dbErr = TQString( i18n( "Krecipes could not open the database using the driver '%2' (with username: \"%1\"). You may not have the necessary permissions, or the server may be down." ) ).arg( DBuser ).arg( qsqlDriverPlugin() );
		}

		//Now Reopen the Database and signal & exit if it fails
		if ( !database->open() ) {
			TQString error = i18n( "Database message: %1" ).arg( database->lastError().databaseText() );
			kdDebug() << i18n( "Failing to open database. Exiting\n" ).latin1();

			// Handle the error (passively)
			dbErr = TQString( i18n( "Krecipes could not open the database using the driver '%2' (with username: \"%1\"). You may not have the necessary permissions, or the server may be down." ) ).arg( DBuser ).arg( qsqlDriverPlugin() );
			return ;
		}
	}

	if ( int( tqRound( databaseVersion() * 1e5 ) ) > int( tqRound( latestDBVersion() * 1e5 ) ) ) { //correct for float's imprecision
		dbErr = i18n( "This database was created with a newer version of Krecipes and cannot be opened." );
		return ;
	}

	// Check integrity of the database (tables). If not possible, exit
	// Because checkIntegrity() will create tables if they don't exist,
	// we don't want to run this when creating the database.  We would be
	// logged in as another user (usually the superuser and not have ownership of the tables
	if ( create_tables && !checkIntegrity() ) {
		dbErr = i18n( "Failed to fix database structure.\nIf you are using SQLite, this is often caused by using an SQLite 2 database with SQLite 3 installed.  If this is the case, make sure both SQLite 2 and 3 are installed, and then run 'krecipes --convert-sqlite3' to update your database to the new structure." );
		return;
	}

	// Database was opened correctly
	m_query = TQSqlQuery( TQString::null, database );
	m_query.setForwardOnly(true);
	dbOK = true;
}

void TQSqlRecipeDB::execSQL( const TQString &command )
{
	database->exec( command );
}

void TQSqlRecipeDB::loadRecipes( RecipeList *rlist, int items, TQValueList<int> ids )
{
	// Empty the recipe first
	rlist->empty();

	TQMap <int, RecipeList::Iterator> recipeIterators; // Stores the iterator of each recipe in the list;

	TQString command;

	TQString current_timestamp = TQDateTime::currentDateTime().toString(TQt::ISODate);

	TQStringList ids_str;
	for ( TQValueList<int>::const_iterator it = ids.begin(); it != ids.end(); ++it ) {
		TQString number_str = TQString::number(*it);
		ids_str << number_str;

		if ( !(items & RecipeDB::Noatime) )
 			database->exec( "UPDATE recipes SET ctime=ctime,mtime=mtime,atime='"+current_timestamp+"' WHERE id="+number_str );
	}

	// Read title, author, yield, and instructions as specified
	command = "SELECT id";
	if ( items & RecipeDB::Title ) command += ",title";
	if ( items & RecipeDB::Instructions ) command += ",instructions";
	if ( items & RecipeDB::PrepTime ) command += ",prep_time";
	if ( items & RecipeDB::Yield ) command += ",yield_amount,yield_amount_offset,yield_type_id";
	command += " FROM recipes"+(ids_str.count()!=0?" WHERE id IN ("+ids_str.join(",")+")":"");

	TQSqlQuery recipeQuery(command,database);
	if ( recipeQuery.isActive() ) {
		while ( recipeQuery.next() ) {
			int row_at = 0;

			Recipe recipe;
			recipe.recipeID = recipeQuery.value( row_at ).toInt(); ++row_at;

			if ( items & RecipeDB::Title ) {
				 recipe.title = unescapeAndDecode( recipeQuery.value( row_at ).toCString() ); ++row_at;
			}

			if ( items & RecipeDB::Instructions ) {
				recipe.instructions = unescapeAndDecode( recipeQuery.value( row_at ).toCString() ); ++row_at;
			}

			if ( items & RecipeDB::PrepTime ) {
				 recipe.prepTime = recipeQuery.value( row_at ).toTime(); ++row_at;
			}

			if ( items & RecipeDB::Yield ) {
				recipe.yield.amount = recipeQuery.value( row_at ).toDouble(); ++row_at;
				recipe.yield.amount_offset = recipeQuery.value( row_at ).toDouble(); ++row_at;
				recipe.yield.type_id = recipeQuery.value( row_at ).toInt(); ++row_at;
				if ( recipe.yield.type_id != -1 ) {
					TQString y_command = TQString("SELECT name FROM yield_types WHERE id=%1;").arg(recipe.yield.type_id);
					TQSqlQuery yield_query(y_command,database);
					if ( yield_query.isActive() && yield_query.first() )
						recipe.yield.type = unescapeAndDecode(yield_query.value( 0 ).toCString());
					else
						kdDebug()<<yield_query.lastError().databaseText()<<endl;
				}
			}

			if ( items & RecipeDB::Meta )
				loadRecipeMetadata(&recipe);

			recipeIterators[ recipe.recipeID ] = rlist->append( recipe );
		}
	}

	// Read the ingredients
	if ( items & RecipeDB::Ingredients ) {
		for ( RecipeList::iterator recipe_it = rlist->begin(); recipe_it != rlist->end(); ++recipe_it ) {
			if ( items & RecipeDB::NamesOnly ) {
				if ( items & IngredientAmounts )
					command = TQString( "SELECT il.ingredient_id,i.name,il.substitute_for,il.amount,il.amount_offset,u.id,u.type FROM ingredients i, ingredient_list il, units u WHERE il.recipe_id=%1 AND i.id=il.ingredient_id AND u.id=il.unit_id ORDER BY il.order_index" ).arg( (*recipe_it).recipeID );
				else
					command = TQString( "SELECT il.ingredient_id,i.name,il.substitute_for FROM ingredients i, ingredient_list il WHERE il.recipe_id=%1 AND i.id=il.ingredient_id" ).arg( (*recipe_it).recipeID );
			}
			else
				command = TQString( "SELECT il.ingredient_id,i.name,il.substitute_for,il.amount,il.amount_offset,u.id,u.name,u.plural,u.name_abbrev,u.plural_abbrev,u.type,il.group_id,il.id FROM ingredients i, ingredient_list il, units u WHERE il.recipe_id=%1 AND i.id=il.ingredient_id AND u.id=il.unit_id ORDER BY il.order_index" ).arg( (*recipe_it).recipeID );

			TQSqlQuery ingredientQuery( command, database );
			if ( ingredientQuery.isActive() ) {
				RecipeList::Iterator it = recipeIterators[ (*recipe_it).recipeID ];
				while ( ingredientQuery.next() ) {
					Ingredient ing;
					ing.ingredientID = ingredientQuery.value( 0 ).toInt();
					ing.name = unescapeAndDecode( ingredientQuery.value( 1 ).toCString() );

					if ( items & RecipeDB::NamesOnly ) {
						if ( items & IngredientAmounts ) {
							ing.amount = ingredientQuery.value( 3 ).toDouble();
							ing.amount_offset = ingredientQuery.value( 4 ).toDouble();
							ing.units.id = ingredientQuery.value( 5 ).toInt();
							ing.units.type = (Unit::Type)ingredientQuery.value( 6 ).toInt();
						}
					}
					else  {
						ing.amount = ingredientQuery.value( 3 ).toDouble();
						ing.amount_offset = ingredientQuery.value( 4 ).toDouble();
						ing.units.id = ingredientQuery.value( 5 ).toInt();
						ing.units.name = unescapeAndDecode( ingredientQuery.value( 6 ).toCString() );
						ing.units.plural = unescapeAndDecode( ingredientQuery.value( 7 ).toCString() );
						ing.units.name_abbrev = unescapeAndDecode( ingredientQuery.value( 8 ).toCString() );
						ing.units.plural_abbrev = unescapeAndDecode( ingredientQuery.value( 9 ).toCString() );
						ing.units.type = (Unit::Type)ingredientQuery.value( 10 ).toInt();
	
						//if we don't have both name and plural, use what we have as both
						if ( ing.units.name.isEmpty() )
							ing.units.name = ing.units.plural;
						else if ( ing.units.plural.isEmpty() )
							ing.units.plural = ing.units.name;
			
						ing.groupID = ingredientQuery.value( 11 ).toInt();
						if ( ing.groupID != -1 ) {
							TQSqlQuery toLoad( TQString( "SELECT name FROM ingredient_groups WHERE id=%1" ).arg( ing.groupID ), database );
							if ( toLoad.isActive() && toLoad.first() )
								ing.group = unescapeAndDecode( toLoad.value( 0 ).toCString() );
						}

						command = TQString("SELECT pl.prep_method_id,p.name FROM prep_methods p, prep_method_list pl WHERE pl.ingredient_list_id=%1 AND p.id=pl.prep_method_id ORDER BY pl.order_index;").arg(ingredientQuery.value( 12 ).toInt());
						TQSqlQuery ingPrepMethodsQuery( command, database );
						if ( ingPrepMethodsQuery.isActive() ) {
							while ( ingPrepMethodsQuery.next() ) {
								ing.prepMethodList.append( Element( unescapeAndDecode(ingPrepMethodsQuery.value(1).toCString()),ingPrepMethodsQuery.value(0).toInt()) );
							}
						}
					}

					if ( ingredientQuery.value( 2 ).toInt() > 0 ) {
						//given the ordering, we can assume substitute_for is the id of the last
						//ingredient in the list
						//int substitute_for = ingredientQuery.value( 2 ).toInt();
						(*it).ingList.last().substitutes.append( ing );
					}
					else
						(*it).ingList.append( ing );
				}
			}
		}
	}

	//Load the Image
	if ( items & RecipeDB::Photo ) {
		for ( RecipeList::iterator recipe_it = rlist->begin(); recipe_it != rlist->end(); ++recipe_it ) {
			RecipeList::iterator it = recipeIterators[ (*recipe_it).recipeID ];
			loadPhoto( (*it).recipeID, (*it).photo );
		}
	}

	//Load the category list
	if ( items & RecipeDB::Categories ) {
		for ( RecipeList::iterator recipe_it = rlist->begin(); recipe_it != rlist->end(); ++recipe_it ) {
			RecipeList::iterator it = recipeIterators[ (*recipe_it).recipeID ];
			
			command = TQString( "SELECT cl.category_id,c.name FROM category_list cl, categories c WHERE recipe_id=%1 AND cl.category_id=c.id;" ).arg( (*it).recipeID );
		
			m_query.exec( command );
			if ( m_query.isActive() ) {
				while ( m_query.next() ) {
					Element el;
					el.id = m_query.value( 0 ).toInt();
					el.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
					(*it).categoryList.append( el );
				}
			}
		}
	}

	//Load the author list
	if ( items & RecipeDB::Authors ) {
		for ( RecipeList::iterator recipe_it = rlist->begin(); recipe_it != rlist->end(); ++recipe_it ) {
			RecipeList::iterator it = recipeIterators[ (*recipe_it).recipeID ];

			command = TQString( "SELECT al.author_id,a.name FROM author_list al, authors a WHERE recipe_id=%1 AND al.author_id=a.id;" ).arg( (*it).recipeID );
		
			m_query.exec( command );
			if ( m_query.isActive() ) {
				while ( m_query.next() ) {
					Element el;
					el.id = m_query.value( 0 ).toInt();
					el.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
					(*it).authorList.append( el );
				}
			}
		}
	}

	//Load the ratings
	if ( items & RecipeDB::Ratings ) {
		for ( RecipeList::iterator recipe_it = rlist->begin(); recipe_it != rlist->end(); ++recipe_it ) {
			RecipeList::iterator it = recipeIterators[ (*recipe_it).recipeID ];
			
			command = TQString( "SELECT id,comment,rater FROM ratings WHERE recipe_id=%1 ORDER BY created DESC" ).arg( (*it).recipeID );
			TQSqlQuery query( command, database );
			if ( query.isActive() ) {
				while ( query.next() ) {
					Rating r;
					r.id = query.value( 0 ).toInt();
					r.comment = unescapeAndDecode( query.value( 1 ).toCString() );
					r.rater = unescapeAndDecode( query.value( 2 ).toCString() );

					command = TQString( "SELECT rc.id,rc.name,rl.stars FROM rating_criteria rc, rating_criterion_list rl WHERE rating_id=%1 AND rl.rating_criterion_id=rc.id" ).arg(r.id);
					TQSqlQuery criterionQuery( command, database );
					if ( criterionQuery.isActive() ) {
						while ( criterionQuery.next() ) {
							RatingCriteria rc;
							rc.id = criterionQuery.value( 0 ).toInt();
							rc.name = unescapeAndDecode( criterionQuery.value( 1 ).toCString() );
							rc.stars = criterionQuery.value( 2 ).toDouble();
							r.append( rc );
						}
					}

					(*it).ratingList.append( r );
				}
			}
		}
	}

	if ( items & RecipeDB::Properties ) {
		for ( RecipeList::iterator recipe_it = rlist->begin(); recipe_it != rlist->end(); ++recipe_it ) {
			RecipeList::iterator it = recipeIterators[ (*recipe_it).recipeID ];
			calculateProperties( *it, this );
		}
	}
}

void TQSqlRecipeDB::loadIngredientGroups( ElementList *list )
{
	list->clear();

	TQString command = "SELECT id,name FROM ingredient_groups ORDER BY name;";
	m_query.exec( command );

	if ( m_query.isActive() ) {
		while ( m_query.next() ) {
			Element group;
			group.id = m_query.value( 0 ).toInt();
			group.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
			list->append( group );
		}
	}
}

void TQSqlRecipeDB::loadIngredients( ElementList *list, int limit, int offset )
{
	list->clear();

	TQString command = "SELECT id,name FROM ingredients ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));
	m_query.exec( command );

	if ( m_query.isActive() ) {
		while ( m_query.next() ) {
			Element ing;
			ing.id = m_query.value( 0 ).toInt();
			ing.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
			list->append( ing );
		}
	}
}

void TQSqlRecipeDB::loadPrepMethods( ElementList *list, int limit, int offset )
{
	list->clear();

	TQString command = "SELECT id,name FROM prep_methods ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));
	m_query.exec( command );

	if ( m_query.isActive() ) {
		while ( m_query.next() ) {
			Element prep_method;
			prep_method.id = m_query.value( 0 ).toInt();
			prep_method.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
			list->append( prep_method );
		}
	}
}

void TQSqlRecipeDB::loadYieldTypes( ElementList *list, int limit, int offset )
{
	list->clear();

	TQString command = "SELECT id,name FROM yield_types ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));
	m_query.exec( command );

	if ( m_query.isActive() ) {
		while ( m_query.next() ) {
			Element el;
			el.id = m_query.value( 0 ).toInt();
			el.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
			list->append( el );
		}
	}
}

void TQSqlRecipeDB::createNewPrepMethod( const TQString &prepMethodName )
{
	TQString command;
	TQString real_name = prepMethodName.left( maxPrepMethodNameLength() );

	command = TQString( "INSERT INTO prep_methods VALUES(%2,'%1');" ).arg( escapeAndEncode( real_name ) ).arg( getNextInsertIDStr( "prep_methods", "id" ) );
	TQSqlQuery prepMethodToCreate( command, database );

	emit prepMethodCreated( Element( real_name, lastInsertID() ) );
}

void TQSqlRecipeDB::modPrepMethod( int prepMethodID, const TQString &newLabel )
{
	TQString command;

	command = TQString( "UPDATE prep_methods SET name='%1' WHERE id=%2;" ).arg( escapeAndEncode( newLabel ) ).arg( prepMethodID );
	TQSqlQuery prepMethodToCreate( command, database );

	emit prepMethodRemoved( prepMethodID );
	emit prepMethodCreated( Element( newLabel, prepMethodID ) );
}

void TQSqlRecipeDB::modProperty( int propertyID, const TQString &newLabel )
{
	TQString command;

	command = TQString( "UPDATE ingredient_properties SET name='%1' WHERE id=%2;" ).arg( escapeAndEncode( newLabel ) ).arg( propertyID );
	TQSqlQuery createQuery( command, database );

	emit propertyRemoved( propertyID );
	emit propertyCreated( propertyName( propertyID ) );
}

void TQSqlRecipeDB::loadPossibleUnits( int ingredientID, UnitList *list )
{
	list->clear();

	TQString command;

	command = TQString( "SELECT u.id,u.name,u.plural,u.name_abbrev,u.plural_abbrev,u.type FROM unit_list ul, units u WHERE ul.ingredient_id=%1 AND ul.unit_id=u.id;" ).arg( ingredientID );

	TQSqlQuery unitToLoad( command, database );

	if ( unitToLoad.isActive() ) {
		while ( unitToLoad.next() ) {
			Unit unit;
			unit.id = unitToLoad.value( 0 ).toInt();
			unit.name = unescapeAndDecode( unitToLoad.value( 1 ).toCString() );
			unit.plural = unescapeAndDecode( unitToLoad.value( 2 ).toCString() );
			unit.name_abbrev = unescapeAndDecode( unitToLoad.value( 3 ).toCString() );
			unit.plural_abbrev = unescapeAndDecode( unitToLoad.value( 4 ).toCString() );
			unit.type = (Unit::Type) unitToLoad.value( 5 ).toInt();

			list->append( unit );
		}
	}


}

void TQSqlRecipeDB::storePhoto( int recipeID, const TQByteArray &data )
{
	TQSqlQuery query( TQString::null, database );

	query.prepare( "UPDATE recipes SET photo=?,ctime=ctime,atime=atime,mtime=mtime WHERE id=" + TQString::number( recipeID ) );
	query.addBindValue( KCodecs::base64Encode( data ) );
	query.exec();
}

void TQSqlRecipeDB::loadPhoto( int recipeID, TQPixmap &photo )
{
	TQString command = TQString( "SELECT photo FROM recipes WHERE id=%1;" ).arg( recipeID );
	TQSqlQuery query( command, database );

	if ( query.isActive() && query.first() ) {
		TQCString decodedPic;
		TQPixmap pix;
		KCodecs::base64Decode( query.value( 0 ).toCString(), decodedPic );
		int len = decodedPic.size();

		if ( len > 0 ) {
			TQByteArray picData( len );
			memcpy( picData.data(), decodedPic.data(), len );
	
			bool ok = pix.loadFromData( picData, "JPEG" );
			if ( ok )
				photo = pix;
		}
	}
}

void TQSqlRecipeDB::loadRecipeMetadata( Recipe *recipe )
{
	TQString command = "SELECT ctime,mtime,atime FROM recipes WHERE id="+TQString::number(recipe->recipeID);

	TQSqlQuery query( command, database );
	if ( query.isActive() && query.first() ) {
		recipe->ctime = query.value(0).toDateTime();
		recipe->mtime = query.value(1).toDateTime();
		recipe->atime = query.value(2).toDateTime();
	}
}

void TQSqlRecipeDB::saveRecipe( Recipe *recipe )
{
	// Check if it's a new recipe or it exists (supossedly) already.

	bool newRecipe;
	newRecipe = ( recipe->recipeID == -1 );
	// First check if the recipe ID is set, if so, update (not create)
	// Be carefull, first check if the recipe hasn't been deleted while changing.

	TQSqlQuery recipeToSave( TQString::null, database );

	TQString command;

	TQDateTime current_datetime = TQDateTime::currentDateTime();
	TQString current_timestamp = current_datetime.toString(TQt::ISODate);
	if ( newRecipe ) {
		command = TQString( "INSERT INTO recipes VALUES ("+getNextInsertIDStr("recipes","id")+",'%1',%2,'%3','%4','%5',NULL,'%6','%7','%8','%9');" )  // Id is autoincremented
		          .arg( escapeAndEncode( recipe->title ) )
		          .arg( recipe->yield.amount )
		          .arg( recipe->yield.amount_offset )
		          .arg( recipe->yield.type_id )
		          .arg( escapeAndEncode( recipe->instructions ) )
		          .arg( recipe->prepTime.toString( "hh:mm:ss" ) )
		          .arg( current_timestamp )
		          .arg( current_timestamp )
		          .arg( current_timestamp );
		recipe->mtime = recipe->ctime = recipe->atime = current_datetime;
	}
	else {
		command = TQString( "UPDATE recipes SET title='%1',yield_amount='%2',yield_amount_offset='%3',yield_type_id='%4',instructions='%5',prep_time='%6',mtime='%8',ctime=ctime,atime=atime WHERE id=%7;" )
		          .arg( escapeAndEncode( recipe->title ) )
		          .arg( recipe->yield.amount )
		          .arg( recipe->yield.amount_offset )
		          .arg( recipe->yield.type_id )
		          .arg( escapeAndEncode( recipe->instructions ) )
		          .arg( recipe->prepTime.toString( "hh:mm:ss" ) )
		          .arg( recipe->recipeID )
		          .arg( current_timestamp );
		recipe->mtime = current_datetime;
	}
	recipeToSave.exec( command );

	if ( !newRecipe ) {
		// Clean up yield_types which have no recipe that they belong to
		TQStringList ids;
		command = TQString( "SELECT DISTINCT(yield_type_id) FROM recipes" );
		recipeToSave.exec( command );
		if ( recipeToSave.isActive() ) {
			while ( recipeToSave.next() ) {
				if ( recipeToSave.value( 0 ).toInt() != -1 )
					ids << TQString::number( recipeToSave.value( 0 ).toInt() );
			}
		}
		command = TQString( "DELETE FROM yield_types WHERE id NOT IN ( %1 )" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
		recipeToSave.exec( command );
	}

	// If it's a new recipe, identify the ID that was given to the recipe and store in the Recipe itself
	int recipeID;
	if ( newRecipe ) {
		recipeID = lastInsertID();
		recipe->recipeID = recipeID;
	}
	recipeID = recipe->recipeID;
	loadRecipeMetadata(recipe);

	// Let's begin storing the Image!
	if ( !recipe->photo.isNull() ) {
		TQByteArray ba;
		TQBuffer buffer( ba );
		buffer.open( IO_WriteOnly );
		TQImageIO iio( &buffer, "JPEG" );
		iio.setImage( recipe->photo.convertToImage() );
		iio.write();
		//recipe->photo.save( &buffer, "JPEG" ); don't need TQImageIO in QT 3.2

		storePhoto( recipeID, ba );
	}
	else {
		recipeToSave.exec( "UPDATE recipes SET photo=NULL, mtime=mtime, ctime=ctime, atime=atime WHERE id=" + TQString::number( recipeID ) );
	}

	// Save the ingredient list (first delete if we are updating)
	command = TQString( "SELECT id FROM ingredient_list WHERE recipe_id=%1" ).arg(recipeID);
	recipeToSave.exec( command );
	if ( recipeToSave.isActive() ) {
		while ( recipeToSave.next() ) {
			command = TQString("DELETE FROM prep_method_list WHERE ingredient_list_id=%1")
			  .arg(recipeToSave.value(0).toInt());
			database->exec(command);
		}
	}
	command = TQString( "DELETE FROM ingredient_list WHERE recipe_id=%1;" )
	          .arg( recipeID );
	recipeToSave.exec( command );

	int order_index = 0;
	for ( IngredientList::const_iterator ing_it = recipe->ingList.begin(); ing_it != recipe->ingList.end(); ++ing_it ) {
		order_index++;
		TQString ing_list_id_str = getNextInsertIDStr("ingredient_list","id");
		command = TQString( "INSERT INTO ingredient_list VALUES (%1,%2,%3,%4,%5,%6,%7,%8,NULL);" )
		          .arg( ing_list_id_str )
		          .arg( recipeID )
		          .arg( ( *ing_it ).ingredientID )
		          .arg( ( *ing_it ).amount )
		          .arg( ( *ing_it ).amount_offset )
		          .arg( ( *ing_it ).units.id )
		          .arg( order_index )
		          .arg( ( *ing_it ).groupID );
		recipeToSave.exec( command );

		int ing_list_id = lastInsertID();
		int prep_order_index = 0;
		for ( ElementList::const_iterator prep_it = (*ing_it).prepMethodList.begin(); prep_it != (*ing_it).prepMethodList.end(); ++prep_it ) {
			prep_order_index++;
			command = TQString( "INSERT INTO prep_method_list VALUES (%1,%2,%3);" )
				.arg( ing_list_id )
				.arg( ( *prep_it ).id )
				.arg( prep_order_index );
			recipeToSave.exec( command );
		}
		
		for ( TQValueList<IngredientData>::const_iterator sub_it = (*ing_it).substitutes.begin(); sub_it != (*ing_it).substitutes.end(); ++sub_it ) {
			order_index++;
			TQString ing_list_id_str = getNextInsertIDStr("ingredient_list","id");
			command = TQString( "INSERT INTO ingredient_list VALUES (%1,%2,%3,%4,%5,%6,%7,%8,%9);" )
				.arg( ing_list_id_str )
				.arg( recipeID )
				.arg( ( *sub_it ).ingredientID )
				.arg( ( *sub_it ).amount )
				.arg( ( *sub_it ).amount_offset )
				.arg( ( *sub_it ).units.id )
				.arg( order_index )
				.arg( ( *sub_it ).groupID )
				.arg( (*ing_it).ingredientID );
			recipeToSave.exec( command );
	
			int ing_list_id = lastInsertID();
			int prep_order_index = 0;
			for ( ElementList::const_iterator prep_it = (*sub_it).prepMethodList.begin(); prep_it != (*sub_it).prepMethodList.end(); ++prep_it ) {
				prep_order_index++;
				command = TQString( "INSERT INTO prep_method_list VALUES (%1,%2,%3);" )
					.arg( ing_list_id )
					.arg( ( *prep_it ).id )
					.arg( prep_order_index );
				recipeToSave.exec( command );
			}
		}
	}

	// Save the category list for the recipe (first delete, in case we are updating)
	command = TQString( "DELETE FROM category_list WHERE recipe_id=%1;" )
	          .arg( recipeID );
	recipeToSave.exec( command );

	ElementList::const_iterator cat_it = recipe->categoryList.end(); // Start from last, mysql seems to work in lifo format... so it's read first the latest inserted one (newest)
	--cat_it;
	for ( unsigned int i = 0; i < recipe->categoryList.count(); i++ ) {
		command = TQString( "INSERT INTO category_list VALUES (%1,%2);" )
		          .arg( recipeID )
		          .arg( ( *cat_it ).id );
		recipeToSave.exec( command );

		--cat_it;
	}

	//Add the default category -1 to ease and speed up searches

	command = TQString( "INSERT INTO category_list VALUES (%1,-1);" )
	          .arg( recipeID );
	recipeToSave.exec( command );


	// Save the author list for the recipe (first delete, in case we are updating)
	command = TQString( "DELETE FROM author_list WHERE recipe_id=%1;" )
	          .arg( recipeID );
	recipeToSave.exec( command );

	ElementList::const_iterator author_it = recipe->authorList.end(); // Start from last, mysql seems to work in lifo format... so it's read first the latest inserted one (newest)
	--author_it;
	for ( unsigned int i = 0; i < recipe->authorList.count(); i++ ) {
		command = TQString( "INSERT INTO author_list VALUES (%1,%2);" )
		          .arg( recipeID )
		          .arg( ( *author_it ).id );
		recipeToSave.exec( command );

		--author_it;
	}

	// Save the ratings (first delete criterion list if we are updating)
	command = TQString( "SELECT id FROM ratings WHERE recipe_id=%1" ).arg(recipeID);
	recipeToSave.exec( command );
	if ( recipeToSave.isActive() ) {
		while ( recipeToSave.next() ) {
			
			command = TQString("DELETE FROM rating_criterion_list WHERE rating_id=%1")
			  .arg(recipeToSave.value(0).toInt());
			database->exec(command);
		}
	}

	TQStringList ids;

	for ( RatingList::iterator rating_it = recipe->ratingList.begin(); rating_it != recipe->ratingList.end(); ++rating_it ) {
		//double average = (*rating_it).average();
		if ( (*rating_it).id == -1 ) //new rating
			command ="INSERT INTO ratings VALUES("+TQString(getNextInsertIDStr("ratings","id"))+","+TQString::number(recipeID)+",'"+TQString(escapeAndEncode((*rating_it).comment))+"','"+TQString(escapeAndEncode((*rating_it).rater))+/*"','"+TQString::number(average)+*/"','"+current_timestamp+"')";
		else //existing rating
			command = "UPDATE ratings SET "
			  "comment='"+TQString(escapeAndEncode((*rating_it).comment))+"',"
			  "rater='"+TQString(escapeAndEncode((*rating_it).rater))+"',"
			  "created=created "
			  "WHERE id="+TQString::number((*rating_it).id);

		recipeToSave.exec( command );
		
		if ( (*rating_it).id == -1 )
			(*rating_it).id = lastInsertID();
		
		for ( TQValueList<RatingCriteria>::const_iterator rc_it = (*rating_it).ratingCriteriaList.begin(); rc_it != (*rating_it).ratingCriteriaList.end(); ++rc_it ) {
			command = TQString( "INSERT INTO rating_criterion_list VALUES("+TQString::number((*rating_it).id)+","+TQString::number((*rc_it).id)+","+TQString::number((*rc_it).stars)+")" );
			recipeToSave.exec( command );
		}

		ids << TQString::number((*rating_it).id);
	}

	// only delete those ratings that don't exist anymore
	command = TQString( "DELETE FROM ratings WHERE recipe_id=%1 AND id NOT IN( %2 )" )
	          .arg( recipeID ).arg( ids.join(",") );
	recipeToSave.exec( command );

	if ( newRecipe )
		emit recipeCreated( Element( recipe->title.left( maxRecipeTitleLength() ), recipeID ), recipe->categoryList );
	else
		emit recipeModified( Element( recipe->title.left( maxRecipeTitleLength() ), recipeID ), recipe->categoryList );
}

void TQSqlRecipeDB::loadRecipeList( ElementList *list, int categoryID, bool recursive )
{
	TQString command;

	if ( categoryID == -1 )  // load just the list
		command = "SELECT id,title FROM recipes;";
	else  // load the list of those in the specified category
		command = TQString( "SELECT r.id,r.title FROM recipes r,category_list cl WHERE r.id=cl.recipe_id AND cl.category_id=%1 ORDER BY r.title" ).arg( categoryID );

	if ( recursive ) {
		TQSqlQuery subcategories( TQString("SELECT id FROM categories WHERE parent_id='%1'").arg(categoryID), database );
		if ( subcategories.isActive() ) {
			while ( subcategories.next() ) {
				loadRecipeList(list,subcategories.value( 0 ).toInt(),true);
			}
		}
	}

	TQSqlQuery recipeToLoad( command, database );

	if ( recipeToLoad.isActive() ) {
		while ( recipeToLoad.next() ) {
			Element recipe;
			recipe.id = recipeToLoad.value( 0 ).toInt();
			recipe.name = unescapeAndDecode( recipeToLoad.value( 1 ).toCString() );
			list->append( recipe );
		}
	}
}


void TQSqlRecipeDB::loadUncategorizedRecipes( ElementList *list )
{
	list->clear();

	TQString command = "SELECT r.id,r.title FROM recipes r,category_list cl WHERE r.id=cl.recipe_id GROUP BY id HAVING COUNT(*)=1 ORDER BY r.title DESC";
	m_query.exec( command );
	if ( m_query.isActive() ) {
		while ( m_query.next() ) {
			Element recipe;
			recipe.id = m_query.value( 0 ).toInt();
			recipe.name = unescapeAndDecode( m_query.value( 1 ).toCString() );
			list->append( recipe );
		}
	}
}



void TQSqlRecipeDB::removeRecipe( int id )
{
	emit recipeRemoved( id );

	TQString command;

	command = TQString( "DELETE FROM recipes WHERE id=%1;" ).arg( id );
	TQSqlQuery recipeToRemove( command, database );
	command = TQString( "DELETE FROM ingredient_list WHERE recipe_id=%1;" ).arg( id );
	recipeToRemove.exec( command );
	command = TQString( "DELETE FROM category_list WHERE recipe_id=%1;" ).arg( id );
	recipeToRemove.exec( command );

	// Clean up ingredient_groups which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_groups WHERE id NOT IN ( SELECT DISTINCT(group_id) FROM ingredient_list );)
	TQStringList ids;
	command = TQString( "SELECT DISTINCT(group_id) FROM ingredient_list;" );
	recipeToRemove.exec( command );
	if ( recipeToRemove.isActive() ) {
		while ( recipeToRemove.next() ) {
			if ( recipeToRemove.value( 0 ).toInt() != -1 )
				ids << TQString::number( recipeToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_groups WHERE id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	recipeToRemove.exec( command );

	// Clean up yield_types which have no recipe that they belong to
	ids.clear();
	command = TQString( "SELECT DISTINCT(yield_type_id) FROM recipes" );
	recipeToRemove.exec( command );
	if ( recipeToRemove.isActive() ) {
		while ( recipeToRemove.next() ) {
			if ( recipeToRemove.value( 0 ).toInt() != -1 )
				ids << TQString::number( recipeToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM yield_types WHERE id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	recipeToRemove.exec( command );
}

void TQSqlRecipeDB::removeRecipeFromCategory( int recipeID, int categoryID )
{
	TQString command;
	command = TQString( "DELETE FROM category_list WHERE recipe_id=%1 AND category_id=%2;" ).arg( recipeID ).arg( categoryID );
	TQSqlQuery recipeToRemove( command, database );

	emit recipeRemoved( recipeID, categoryID );
}

void TQSqlRecipeDB::categorizeRecipe( int recipeID, const ElementList &categoryList )
{
	TQString command;

	//emit recipeRemoved( recipeID, -1 );

	for ( ElementList::const_iterator it = categoryList.begin(); it != categoryList.end(); ++it ) {
		command = TQString( "INSERT INTO category_list VALUES(%1,%2)" ).arg( recipeID ).arg( (*it).id );
		database->exec( command );
	}

	emit recipeModified( Element(recipeTitle(recipeID),recipeID), categoryList );
}

void TQSqlRecipeDB::createNewIngGroup( const TQString &name )
{
	TQString command;
	TQString real_name = name.left( maxIngGroupNameLength() );

	command = TQString( "INSERT INTO ingredient_groups VALUES(%2,'%1');" ).arg( escapeAndEncode( real_name ) ).arg( getNextInsertIDStr( "ingredient_groups", "id" ) );
	TQSqlQuery query( command, database );

	emit ingGroupCreated( Element( real_name, lastInsertID() ) );
}

void TQSqlRecipeDB::createNewIngredient( const TQString &ingredientName )
{
	TQString command;
	TQString real_name = ingredientName.left( maxIngredientNameLength() );

	command = TQString( "INSERT INTO ingredients VALUES(%2,'%1');" ).arg( escapeAndEncode( real_name ) ).arg( getNextInsertIDStr( "ingredients", "id" ) );
	TQSqlQuery ingredientToCreate( command, database );

	emit ingredientCreated( Element( real_name, lastInsertID() ) );
}

void TQSqlRecipeDB::createNewRating( const TQString &rating )
{
	TQString command;
	TQString real_name = rating/*.left( maxIngredientNameLength() )*/;

	command = TQString( "INSERT INTO rating_criteria VALUES(%2,'%1');" ).arg( escapeAndEncode( real_name ) ).arg( getNextInsertIDStr( "rating_criteria", "id" ) );
	TQSqlQuery toCreate( command, database );

	emit ratingCriteriaCreated( Element( real_name, lastInsertID() ) );
}

void TQSqlRecipeDB::createNewYieldType( const TQString &name )
{
	TQString command;
	TQString real_name = name.left( maxYieldTypeLength() );

	command = TQString( "INSERT INTO yield_types VALUES(%2,'%1');" ).arg( escapeAndEncode( real_name ) ).arg( getNextInsertIDStr( "yield_types", "id" ) );
	database->exec(command);

	//emit yieldTypeCreated( Element( real_name, lastInsertID() ) );
}

void TQSqlRecipeDB::modIngredientGroup( int groupID, const TQString &newLabel )
{
	TQString command;

	command = TQString( "UPDATE ingredient_groups SET name='%1' WHERE id=%2;" ).arg( escapeAndEncode( newLabel ) ).arg( groupID );
	TQSqlQuery ingredientToCreate( command, database );

	emit ingGroupRemoved( groupID );
	emit ingGroupCreated( Element( newLabel, groupID ) );
}

void TQSqlRecipeDB::modIngredient( int ingredientID, const TQString &newLabel )
{
	TQString command;

	command = TQString( "UPDATE ingredients SET name='%1' WHERE id=%2;" ).arg( escapeAndEncode( newLabel ) ).arg( ingredientID );
	TQSqlQuery ingredientToCreate( command, database );

	emit ingredientRemoved( ingredientID );
	emit ingredientCreated( Element( newLabel, ingredientID ) );
}

void TQSqlRecipeDB::addUnitToIngredient( int ingredientID, int unitID )
{
	TQString command;

	command = TQString( "INSERT INTO unit_list VALUES(%1,%2);" ).arg( ingredientID ).arg( unitID );
	TQSqlQuery ingredientToCreate( command, database );
}

void TQSqlRecipeDB::loadUnits( UnitList *list, Unit::Type type, int limit, int offset )
{
	list->clear();

	TQString command;

	command = "SELECT id,name,name_abbrev,plural,plural_abbrev,type FROM units "
	  +((type==Unit::All)?"":"WHERE type="+TQString::number(type))
	  +" ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));

	TQSqlQuery unitToLoad( command, database );

	if ( unitToLoad.isActive() ) {
		while ( unitToLoad.next() ) {
			Unit unit;
			unit.id = unitToLoad.value( 0 ).toInt();
			unit.name = unescapeAndDecode( unitToLoad.value( 1 ).toCString() );
			unit.name_abbrev = unescapeAndDecode( unitToLoad.value( 2 ).toCString() );
			unit.plural = unescapeAndDecode( unitToLoad.value( 3 ).toCString() );
			unit.plural_abbrev = unescapeAndDecode( unitToLoad.value( 4 ).toCString() );
			unit.type = (Unit::Type)unitToLoad.value( 5 ).toInt();
			list->append( unit );
		}
	}
}

void TQSqlRecipeDB::removeUnitFromIngredient( int ingredientID, int unitID )
{
	TQString command;

	command = TQString( "DELETE FROM unit_list WHERE ingredient_id=%1 AND unit_id=%2;" ).arg( ingredientID ).arg( unitID );
	TQSqlQuery unitToRemove( command, database );

	// Remove any recipe using that combination of ingredients also (user must have been warned before calling this function!)

	command = TQString( "SELECT r.id FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.ingredient_id=%1 AND il.unit_id=%2;" ).arg( ingredientID ).arg( unitID );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			emit recipeRemoved( unitToRemove.value( 0 ).toInt() );
			database->exec( TQString( "DELETE FROM recipes WHERE id=%1;" ).arg( unitToRemove.value( 0 ).toInt() ) );
		}
	}

	// Remove any ingredient in ingredient_list which has references to this unit and ingredient
	command = TQString( "DELETE FROM ingredient_list WHERE ingredient_id=%1 AND unit_id=%2;" ).arg( ingredientID ).arg( unitID );
	unitToRemove.exec( command );

	// Remove any ingredient properties from ingredient_info where the this ingredient+unit is being used (user must have been warned before calling this function!)
	command = TQString( "DELETE FROM ingredient_info ii WHERE ii.ingredient_id=%1 AND ii.per_units=%2;" ).arg( ingredientID ).arg( unitID );
	unitToRemove.exec( command );

	// Clean up ingredient_list which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_list WHERE recipe_id NOT IN ( SELECT id FROM recipes );)
	TQStringList ids;
	command = TQString( "SELECT id FROM recipes;" );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			ids << TQString::number( unitToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	unitToRemove.exec( command );

	// Clean up category_list which have no recipe that they belong to
	command = TQString( "DELETE FROM category_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	unitToRemove.exec( command );

	// Clean up ingredient_groups which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_groups WHERE id NOT IN ( SELECT DISTINCT(group_id) FROM ingredient_list );)
	ids.clear();
	command = TQString( "SELECT DISTINCT(group_id) FROM ingredient_list;" );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			if ( unitToRemove.value( 0 ).toInt() != -1 )
				ids << TQString::number( unitToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_groups WHERE id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	unitToRemove.exec( command );
}

void TQSqlRecipeDB::removeIngredientGroup( int groupID )
{
	TQString command;

	// First remove the ingredient

	command = TQString( "DELETE FROM ingredient_groups WHERE id=%1" ).arg( groupID );
	TQSqlQuery toDelete( command, database );

	// Remove all the unit entries for this ingredient

	command = TQString( "UPDATE ingredient_list SET group_id='-1' WHERE group_id=%1" ).arg( groupID );
	toDelete.exec( command );

	emit ingGroupRemoved( groupID );
}

void TQSqlRecipeDB::removeIngredient( int ingredientID )
{
	TQString command;

	// First remove the ingredient

	command = TQString( "DELETE FROM ingredients WHERE id=%1;" ).arg( ingredientID );
	TQSqlQuery ingredientToDelete( command, database );

	// Remove all the unit entries for this ingredient

	command = TQString( "DELETE FROM unit_list WHERE ingredient_id=%1;" ).arg( ingredientID );
	ingredientToDelete.exec( command );

	// Remove any recipe using that ingredient

	command = TQString( "SELECT r.id FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.ingredient_id=%1;" ).arg( ingredientID );
	ingredientToDelete.exec( command );
	if ( ingredientToDelete.isActive() ) {
		while ( ingredientToDelete.next() ) {
			emit recipeRemoved( ingredientToDelete.value( 0 ).toInt() );
			database->exec( TQString( "DELETE FROM recipes WHERE id=%1;" ).arg( ingredientToDelete.value( 0 ).toInt() ) );
		}
	}

	// Remove any ingredient in ingredient_list which has references to this ingredient
	command = TQString( "DELETE FROM ingredient_list WHERE ingredient_id=%1;" ).arg( ingredientID );
	ingredientToDelete.exec( command );

	// Clean up ingredient_list which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_list WHERE recipe_id NOT IN ( SELECT id FROM recipes );)
	TQStringList ids;
	command = TQString( "SELECT id FROM recipes;" );
	ingredientToDelete.exec( command );
	if ( ingredientToDelete.isActive() ) {
		while ( ingredientToDelete.next() ) {
			ids << TQString::number( ingredientToDelete.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	ingredientToDelete.exec( command );

	// Clean up category_list which have no recipe that they belong to. Same method as above
	command = TQString( "DELETE FROM category_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	ingredientToDelete.exec( command );

	// Clean up ingredient_groups which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_groups WHERE id NOT IN ( SELECT DISTINCT(group_id) FROM ingredient_list );)
	ids.clear();
	command = TQString( "SELECT DISTINCT(group_id) FROM ingredient_list;" );
	ingredientToDelete.exec( command );
	if ( ingredientToDelete.isActive() ) {
		while ( ingredientToDelete.next() ) {
			if ( ingredientToDelete.value( 0 ).toInt() != -1 )
				ids << TQString::number( ingredientToDelete.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_groups WHERE id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	ingredientToDelete.exec( command );

	// Remove property list of this ingredient
	command = TQString( "DELETE FROM ingredient_info WHERE ingredient_id=%1;" ).arg( ingredientID );
	ingredientToDelete.exec( command );

	emit ingredientRemoved( ingredientID );
}

void TQSqlRecipeDB::removeIngredientWeight( int id )
{
	TQString command;

	// First remove the ingredient

	command = TQString( "DELETE FROM ingredient_weights WHERE id=%1" ).arg( id );
	TQSqlQuery toDelete( command, database );
}

void TQSqlRecipeDB::addIngredientWeight( const Weight &w )
{
	TQString command;
	if ( w.id != -1 ) {
		command = TQString( "UPDATE ingredient_weights SET ingredient_id=%1,amount=%2,unit_id=%3,weight=%4,weight_unit_id=%5,prep_method_id=%7 WHERE id=%6" )
		  .arg(w.ingredientID)
		  .arg(w.perAmount)
		  .arg(w.perAmountUnitID)
		  .arg(w.weight)
		  .arg(w.weightUnitID)
		  .arg(w.id)
		  .arg(w.prepMethodID);
	}
	else {
		command = TQString( "INSERT INTO ingredient_weights VALUES(%6,%1,%2,%3,%4,%5,%7)" )
		  .arg(w.ingredientID)
		  .arg(w.perAmount)
		  .arg(w.perAmountUnitID)
		  .arg(w.weight)
		  .arg(w.weightUnitID)
		  .arg(getNextInsertIDStr( "ingredient_weights", "id" ))
		  .arg(w.prepMethodID);
	}
	TQSqlQuery query( command, database );
}

void TQSqlRecipeDB::addProperty( const TQString &name, const TQString &units )
{
	TQString command;
	TQString real_name = name.left( maxPropertyNameLength() );

	command = TQString( "INSERT INTO ingredient_properties VALUES(%3,'%1','%2');" )
	          .arg( escapeAndEncode( real_name ) )
	          .arg( escapeAndEncode( units ) )
	          .arg( getNextInsertIDStr( "ingredient_properties", "id" ) );
	TQSqlQuery propertyToAdd( command, database );

	emit propertyCreated( IngredientProperty( real_name, units, lastInsertID() ) );
}

void TQSqlRecipeDB::loadProperties( IngredientPropertyList *list, int ingredientID )
{
	list->clear();
	TQString command;
	bool usePerUnit;
	if ( ingredientID >= 0 )  // Load properties of this ingredient
	{
		usePerUnit = true;
		command = TQString( "SELECT ip.id,ip.name,ip.units,ii.per_units,u.name,u.type,ii.amount,ii.ingredient_id  FROM ingredient_properties ip, ingredient_info ii, units u WHERE ii.ingredient_id=%1 AND ii.property_id=ip.id AND ii.per_units=u.id;" ).arg( ingredientID );
	}
	else if ( ingredientID == -1 )  // Load the properties of all the ingredients
	{
		usePerUnit = true;
		command = TQString( "SELECT ip.id,ip.name,ip.units,ii.per_units,u.name,u.type,ii.amount,ii.ingredient_id FROM ingredient_properties ip, ingredient_info ii, units u WHERE ii.property_id=ip.id AND ii.per_units=u.id;" );
	}
	else // Load the whole property list (just the list of possible properties, not the ingredient properties)
	{
		usePerUnit = false;
		command = TQString( "SELECT  id,name,units FROM ingredient_properties;" );
	}

	TQSqlQuery propertiesToLoad ( command, database );
	// Load the results into the list
	if ( propertiesToLoad.isActive() ) {
		while ( propertiesToLoad.next() ) {
			IngredientProperty prop;
			prop.id = propertiesToLoad.value( 0 ).toInt();
			prop.name = unescapeAndDecode( propertiesToLoad.value( 1 ).toCString() );
			prop.units = unescapeAndDecode( propertiesToLoad.value( 2 ).toCString() );
			if ( usePerUnit ) {
				prop.perUnit.id = propertiesToLoad.value( 3 ).toInt();
				prop.perUnit.name = unescapeAndDecode( propertiesToLoad.value( 4 ).toCString() );
				prop.perUnit.type = (Unit::Type)propertiesToLoad.value( 5 ).toInt();
			}

			if ( ingredientID >= -1 )
				prop.amount = propertiesToLoad.value( 6 ).toDouble();
			else
				prop.amount = -1; // Property is generic, not attached to an ingredient

			if ( ingredientID >= -1 )
				prop.ingredientID = propertiesToLoad.value( 7 ).toInt();

			list->append( prop );
		}
	}
}

void TQSqlRecipeDB::changePropertyAmountToIngredient( int ingredientID, int propertyID, double amount, int per_units )
{
	TQString command;
	command = TQString( "UPDATE ingredient_info SET amount=%1 WHERE ingredient_id=%2 AND property_id=%3 AND per_units=%4;" ).arg( amount ).arg( ingredientID ).arg( propertyID ).arg( per_units );
	TQSqlQuery infoToChange( command, database );
}

void TQSqlRecipeDB::addPropertyToIngredient( int ingredientID, int propertyID, double amount, int perUnitsID )
{
	TQString command;

	command = TQString( "INSERT INTO ingredient_info VALUES(%1,%2,%3,%4);" ).arg( ingredientID ).arg( propertyID ).arg( amount ).arg( perUnitsID );
	TQSqlQuery propertyToAdd( command, database );
}


void TQSqlRecipeDB::removePropertyFromIngredient( int ingredientID, int propertyID, int perUnitID )
{
	TQString command;
	// remove property from ingredient info. Note that there could be duplicates with different units (per_units). Remove just the one especified.
	command = TQString( "DELETE FROM ingredient_info WHERE ingredient_id=%1 AND property_id=%2 AND per_units=%3;" ).arg( ingredientID ).arg( propertyID ).arg( perUnitID );
	TQSqlQuery propertyToRemove( command, database );
}

void TQSqlRecipeDB::removeProperty( int propertyID )
{
	TQString command;

	// Remove property from the ingredient_properties
	command = TQString( "DELETE FROM ingredient_properties WHERE id=%1;" ).arg( propertyID );
	TQSqlQuery propertyToRemove( command, database );

	// Remove any ingredient info that uses this property
	command = TQString( "DELETE FROM ingredient_info WHERE property_id=%1;" ).arg( propertyID );
	propertyToRemove.exec( command );

	emit propertyRemoved( propertyID );
}

void TQSqlRecipeDB::removeUnit( int unitID )
{
	TQString command;
	// Remove the unit first
	command = TQString( "DELETE FROM units WHERE id=%1;" ).arg( unitID );
	TQSqlQuery unitToRemove( command, database );

	//Remove the unit from ingredients using it

	command = TQString( "DELETE FROM unit_list WHERE unit_id=%1;" ).arg( unitID );
	unitToRemove.exec( command );


	// Remove any recipe using that unit in the ingredient list (user must have been warned before calling this function!)

	command = TQString( "SELECT r.id FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.unit_id=%1;" ).arg( unitID );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			emit recipeRemoved( unitToRemove.value( 0 ).toInt() );
			database->exec( TQString( "DELETE FROM recipes WHERE id=%1;" ).arg( unitToRemove.value( 0 ).toInt() ) );
		}
	}

	// Remove any ingredient in ingredient_list which has references to this unit
	command = TQString( "DELETE FROM ingredient_list WHERE unit_id=%1;" ).arg( unitID );
	unitToRemove.exec( command );

	// Clean up ingredient_list which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_list WHERE recipe_id NOT IN ( SELECT id FROM recipes );)
	TQStringList ids;
	command = TQString( "SELECT id FROM recipes;" );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			ids << TQString::number( unitToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	unitToRemove.exec( command );

	// Clean up category_list which have no recipe that they belong to. Same method as above
	command = TQString( "DELETE FROM category_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	unitToRemove.exec( command );

	// Clean up ingredient_groups which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_groups WHERE id NOT IN ( SELECT DISTINCT(group_id) FROM ingredient_list );)
	ids.clear();
	command = TQString( "SELECT DISTINCT(group_id) FROM ingredient_list;" );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			if ( unitToRemove.value( 0 ).toInt() != -1 )
				ids << TQString::number( unitToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_groups WHERE id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	unitToRemove.exec( command );

	// Remove the ingredient properties using this unit (user must be warned before calling this function)
	command = TQString( "DELETE FROM ingredient_info WHERE per_units=%1;" ).arg( unitID );
	unitToRemove.exec( command );

	// Remove the unit conversion ratios with this unit
	command = TQString( "DELETE FROM units_conversion WHERE unit1_id=%1 OR unit2_id=%2;" ).arg( unitID ).arg( unitID );
	unitToRemove.exec( command );

	// Remove associated ingredient weights
	command = TQString( "DELETE FROM ingredient_weights WHERE unit_id=%1" ).arg( unitID );
	unitToRemove.exec( command );

	emit unitRemoved( unitID );
}

void TQSqlRecipeDB::removePrepMethod( int prepMethodID )
{
	TQString command;
	// Remove the prep method first
	command = TQString( "DELETE FROM prep_methods WHERE id=%1;" ).arg( prepMethodID );
	TQSqlQuery prepMethodToRemove( command, database );

	// Remove any recipe using that prep method in the ingredient list (user must have been warned before calling this function!)

	command = TQString( "SELECT DISTINCT r.id FROM recipes r,ingredient_list il, prep_method_list pl WHERE r.id=il.recipe_id AND pl.ingredient_list_id=il.id AND pl.prep_method_id=%1;" ).arg( prepMethodID );
	prepMethodToRemove.exec( command );
	if ( prepMethodToRemove.isActive() ) {
		while ( prepMethodToRemove.next() ) {
			emit recipeRemoved( prepMethodToRemove.value( 0 ).toInt() );
			database->exec( TQString( "DELETE FROM recipes WHERE id=%1;" ).arg( prepMethodToRemove.value( 0 ).toInt() ) );
		}
	}

	// Clean up ingredient_list which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_list WHERE recipe_id NOT IN ( SELECT id FROM recipes );)
	TQStringList ids;
	command = TQString( "SELECT id FROM recipes;" );
	prepMethodToRemove.exec( command );
	if ( prepMethodToRemove.isActive() ) {
		while ( prepMethodToRemove.next() ) {
			ids << TQString::number( prepMethodToRemove.value( 0 ).toInt() );
		}
	}

	command = TQString( "DELETE FROM ingredient_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	prepMethodToRemove.exec( command );

	// Clean up category_list which have no recipe that they belong to. Same method as above
	command = TQString( "DELETE FROM category_list WHERE recipe_id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	prepMethodToRemove.exec( command );

	// Clean up ingredient_groups which have no recipe that they belong to
	// MySQL doesn't support subqueries until 4.1, so we'll do this the long way
	// (Easy way: DELETE FROM ingredient_groups WHERE id NOT IN ( SELECT DISTINCT(group_id) FROM ingredient_list );)
	ids.clear();
	command = TQString( "SELECT DISTINCT(group_id) FROM ingredient_list;" );
	prepMethodToRemove.exec( command );
	if ( prepMethodToRemove.isActive() ) {
		while ( prepMethodToRemove.next() ) {
			if ( prepMethodToRemove.value( 0 ).toInt() != -1 )
				ids << TQString::number( prepMethodToRemove.value( 0 ).toInt() );
		}
	}
	command = TQString( "DELETE FROM ingredient_groups WHERE id NOT IN ( %1 );" ).arg( ( ids.count() == 0 ) ? "-1" : ids.join( "," ) );
	prepMethodToRemove.exec( command );

	emit prepMethodRemoved( prepMethodID );
}


void TQSqlRecipeDB::createNewUnit( const Unit &unit )
{
	TQString real_name = unit.name.left( maxUnitNameLength() ).stripWhiteSpace();
	TQString real_plural = unit.plural.left( maxUnitNameLength() ).stripWhiteSpace();
	TQString real_name_abbrev = unit.name_abbrev.left( maxUnitNameLength() ).stripWhiteSpace();
	TQString real_plural_abbrev = unit.plural_abbrev.left( maxUnitNameLength() ).stripWhiteSpace();

	Unit new_unit( real_name, real_plural );
	new_unit.name_abbrev = real_name_abbrev;
	new_unit.plural_abbrev = real_plural_abbrev;
	new_unit.type = unit.type;

	if ( real_name.isEmpty() )
		real_name = real_plural;
	else if ( real_plural.isEmpty() )
		real_plural = real_name;

	if ( real_name_abbrev.isEmpty() )
		real_name_abbrev = "NULL";
	else
		real_name_abbrev = "'"+escapeAndEncode(real_name_abbrev)+"'";
	if ( real_plural_abbrev.isEmpty() )
		real_plural_abbrev = "NULL";
	else
		real_plural_abbrev = "'"+escapeAndEncode(real_plural_abbrev)+"'";
	

	TQString command = "INSERT INTO units VALUES(" + getNextInsertIDStr( "units", "id" ) 
	   + ",'" + escapeAndEncode( real_name )
	   + "'," + real_name_abbrev
	   + ",'" + escapeAndEncode( real_plural )
	   + "'," + real_plural_abbrev
	   + "," + TQString::number(unit.type)
	   + ");";

	TQSqlQuery unitToCreate( command, database );

	new_unit.id = lastInsertID();
	emit unitCreated( new_unit );
}


void TQSqlRecipeDB::modUnit( const Unit &unit )
{
	TQSqlQuery unitQuery( TQString::null, database );

	TQString real_name = unit.name.left( maxUnitNameLength() ).stripWhiteSpace();
	TQString real_plural = unit.plural.left( maxUnitNameLength() ).stripWhiteSpace();
	TQString real_name_abbrev = unit.name_abbrev.left( maxUnitNameLength() ).stripWhiteSpace();
	TQString real_plural_abbrev = unit.plural_abbrev.left( maxUnitNameLength() ).stripWhiteSpace();

	Unit newUnit( real_name, real_plural, unit.id );
	newUnit.type = unit.type;
	newUnit.name_abbrev = real_name_abbrev;
	newUnit.plural_abbrev = real_plural_abbrev;

	if ( real_name_abbrev.isEmpty() )
		real_name_abbrev = "NULL";
	else
		real_name_abbrev = "'"+escapeAndEncode(real_name_abbrev)+"'";
	if ( real_plural_abbrev.isEmpty() )
		real_plural_abbrev = "NULL";
	else
		real_plural_abbrev = "'"+escapeAndEncode(real_plural_abbrev)+"'";

	TQString command = TQString("UPDATE units SET name='%1',name_abbrev=%2,plural='%3',plural_abbrev=%4,type=%6 WHERE id='%5'")
	  .arg(escapeAndEncode(real_name))
	  .arg(real_name_abbrev)
	  .arg(escapeAndEncode(real_plural))
	  .arg(real_plural_abbrev)
	  .arg(unit.id)
	  .arg(unit.type);
	unitQuery.exec( command );

	emit unitRemoved( unit.id );
	emit unitCreated( newUnit );
}

void TQSqlRecipeDB::findUseOfIngGroupInRecipes( ElementList *results, int groupID )
{
	TQString command = TQString( "SELECT DISTINCT r.id,r.title FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.group_id=%1" ).arg( groupID );
	TQSqlQuery query( command, database );

	// Populate data
	if ( query.isActive() ) {
		while ( query.next() ) {
			Element recipe;
			recipe.id = query.value( 0 ).toInt();
			recipe.name = unescapeAndDecode( query.value( 1 ).toCString() );
			results->append( recipe );
		}
	}
}

void TQSqlRecipeDB::findUseOfCategoryInRecipes( ElementList *results, int catID )
{
	TQString command = TQString( "SELECT r.id,r.title FROM recipes r,category_list cl WHERE r.id=cl.recipe_id AND cl.category_id=%1" ).arg( catID );
	TQSqlQuery query( command, database );

	// Populate data
	if ( query.isActive() ) {
		while ( query.next() ) {
			Element recipe;
			recipe.id = query.value( 0 ).toInt();
			recipe.name = unescapeAndDecode( query.value( 1 ).toCString() );
			results->append( recipe );
		}
	}

	//recursively find dependenacies in subcategories
	command = TQString( "SELECT id FROM categories WHERE parent_id=%1" ).arg( catID );
	TQSqlQuery findDeps = database->exec( command );
	if ( findDeps.isActive() ) {
		while ( findDeps.next() ) {
			findUseOfCategoryInRecipes(results,findDeps.value( 0 ).toInt() );
		}
	}
}

void TQSqlRecipeDB::findUseOfAuthorInRecipes( ElementList *results, int authorID )
{
	TQString command = TQString( "SELECT r.id,r.title FROM recipes r,author_list al WHERE r.id=al.recipe_id AND al.author_id=%1" ).arg( authorID );
	TQSqlQuery query( command, database );

	// Populate data
	if ( query.isActive() ) {
		while ( query.next() ) {
			Element recipe;
			recipe.id = query.value( 0 ).toInt();
			recipe.name = unescapeAndDecode( query.value( 1 ).toCString() );
			results->append( recipe );
		}
	}
}

void TQSqlRecipeDB::loadUnitRatios( UnitRatioList *ratioList, Unit::Type type )
{
	ratioList->clear();

	TQString command;
	if ( type == Unit::All )
		command = "SELECT unit1_id,unit2_id,ratio FROM units_conversion";
	else
		command = "SELECT unit1_id,unit2_id,ratio FROM units_conversion,units unit1,units unit2 WHERE unit1_id=unit1.id AND unit1.type="+TQString::number(type)+" AND unit2_id=unit2.id AND unit2.type="+TQString::number(type);
	TQSqlQuery ratiosToLoad( command, database );

	if ( ratiosToLoad.isActive() ) {
		while ( ratiosToLoad.next() ) {
			UnitRatio ratio;
			ratio.uID1 = ratiosToLoad.value( 0 ).toInt();
			ratio.uID2 = ratiosToLoad.value( 1 ).toInt();
			ratio.ratio = ratiosToLoad.value( 2 ).toDouble();
			ratioList->add( ratio );
		}
	}
}

void TQSqlRecipeDB::saveUnitRatio( const UnitRatio *ratio )
{
	TQString command;

	// Check if it's a new ratio or it exists already.
	command = TQString( "SELECT * FROM units_conversion WHERE unit1_id=%1 AND unit2_id=%2" ).arg( ratio->uID1 ).arg( ratio->uID2 ); // Find ratio between units

	TQSqlQuery ratioFound( command, database ); // Find the entries
	bool newRatio = ( ratioFound.size() == 0 );

	if ( newRatio )
		command = TQString( "INSERT INTO units_conversion VALUES(%1,%2,%3);" ).arg( ratio->uID1 ).arg( ratio->uID2 ).arg( ratio->ratio );
	else
		command = TQString( "UPDATE units_conversion SET ratio=%3 WHERE unit1_id=%1 AND unit2_id=%2" ).arg( ratio->uID1 ).arg( ratio->uID2 ).arg( ratio->ratio );

	ratioFound.exec( command ); // Enter the new ratio
}

void TQSqlRecipeDB::removeUnitRatio( int unitID1, int unitID2 )
{
	database->exec(TQString( "DELETE FROM units_conversion WHERE unit1_id=%1 AND unit2_id=%2" ).arg( unitID1 ).arg( unitID2 ));
}

double TQSqlRecipeDB::unitRatio( int unitID1, int unitID2 )
{

	if ( unitID1 == unitID2 )
		return ( 1.0 );
	TQString command;

	command = TQString( "SELECT ratio FROM units_conversion WHERE unit1_id=%1 AND unit2_id=%2;" ).arg( unitID1 ).arg( unitID2 );
	TQSqlQuery ratioToLoad( command, database );

	if ( ratioToLoad.isActive() && ratioToLoad.next() )
		return ( ratioToLoad.value( 0 ).toDouble() );
	else
		return ( -1 );
}

double TQSqlRecipeDB::ingredientWeight( const Ingredient &ing, bool *wasApproximated )
{
	TQString command = TQString( "SELECT amount,weight,prep_method_id,unit_id FROM ingredient_weights WHERE ingredient_id=%1 AND (unit_id=%2 OR weight_unit_id=%3)" )
	   .arg( ing.ingredientID )
	   .arg( ing.units.id ).arg( ing.units.id );

	TQSqlQuery query( command, database );

	if ( query.isActive() ) {
		//store the amount for the entry with no prep method.  If no other suitable entry is found, we'll guesstimate
		//the weight using this entry
		double convertedAmount = -1;
		while ( query.next() ) {
			int prepMethodID = query.value( 2 ).toInt();

			if ( ing.prepMethodList.containsId( prepMethodID ) ) {
				if ( wasApproximated ) *wasApproximated = false;
				double amount = query.value( 0 ).toDouble();

				//'per_amount' -> 'weight' conversion
				if ( query.value( 3 ).toInt() == ing.units.id )
					convertedAmount = query.value( 1 ).toDouble() * ing.amount / amount;
				//'weight' -> 'per_amount' conversion
				else
					convertedAmount = amount * ing.amount / query.value( 1 ).toDouble();

				return convertedAmount;
			}
			if ( prepMethodID == -1 ) {
				//'per_amount' -> 'weight' conversion
				if ( query.value( 3 ).toInt() == ing.units.id )
					convertedAmount = query.value( 1 ).toDouble() * ing.amount / query.value( 0 ).toDouble();
				//'weight' -> 'per_amount' conversion
				else
					convertedAmount = query.value( 0 ).toDouble() * ing.amount / query.value( 1 ).toDouble();
			}
		}
		//no matching prep method found, use entry without a prep method if there was one
		if ( convertedAmount > 0 ) {
			if ( wasApproximated ) *wasApproximated = true;
			kdDebug()<<"Prep method given, but no weight entry found that uses that prep method.  I'm fudging the weight with an entry without a prep method."<<endl;

			return convertedAmount;
		}
	}
	return -1;
}

WeightList TQSqlRecipeDB::ingredientWeightUnits( int ingID )
{
	WeightList list;

	TQString command = TQString( "SELECT id,amount,unit_id,weight,weight_unit_id,prep_method_id FROM ingredient_weights WHERE ingredient_id=%1" ).arg( ingID );
	TQSqlQuery query( command, database );
	if ( query.isActive() ) {
		while ( query.next() ) {
			Weight w;
			w.id = query.value(0).toInt();
			w.perAmount = query.value(1).toDouble();
			w.perAmountUnitID = query.value(2).toInt();
			w.weight = query.value(3).toDouble();
			w.weightUnitID = query.value(4).toInt();
			w.prepMethodID = query.value(5).toInt();
			w.ingredientID = ingID;
			list.append(w);
		}
	}

	return list;
}

//Finds data dependant on this Ingredient/Unit combination
void TQSqlRecipeDB::findIngredientUnitDependancies( int ingredientID, int unitID, ElementList *recipes, ElementList *ingredientInfo )
{

	// Recipes using that combination

	TQString command = TQString( "SELECT DISTINCT r.id,r.title  FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.ingredient_id=%1 AND il.unit_id=%2;" ).arg( ingredientID ).arg( unitID );
	TQSqlQuery unitToRemove( command, database );
	loadElementList( recipes, &unitToRemove );
	// Ingredient info using that combination
	command = TQString( "SELECT i.name,ip.name,ip.units,u.name FROM ingredients i, ingredient_info ii, ingredient_properties ip, units u WHERE i.id=ii.ingredient_id AND ii.ingredient_id=%1 AND ii.per_units=%2 AND ii.property_id=ip.id AND ii.per_units=u.id;" ).arg( ingredientID ).arg( unitID );

	unitToRemove.exec( command );
	loadPropertyElementList( ingredientInfo, &unitToRemove );
}

void TQSqlRecipeDB::findIngredientDependancies( int ingredientID, ElementList *recipes )
{
	TQString command = TQString( "SELECT DISTINCT r.id,r.title FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.ingredient_id=%1" ).arg( ingredientID );

	TQSqlQuery ingredientToRemove( command, database );
	loadElementList( recipes, &ingredientToRemove );
}



//Finds data dependant on the removal of this Unit
void TQSqlRecipeDB::findUnitDependancies( int unitID, ElementList *properties, ElementList *recipes, ElementList *weights )
{

	// Ingredient-Info (ingredient->property) using this Unit

	TQString command = TQString( "SELECT i.name,ip.name,ip.units,u.name  FROM ingredients i, ingredient_info ii, ingredient_properties ip, units u WHERE i.id=ii.ingredient_id AND ii.per_units=%1 AND ii.property_id=ip.id  AND ii.per_units=u.id;" ).arg( unitID );
	TQSqlQuery unitToRemove( command, database );
	loadPropertyElementList( properties, &unitToRemove );

	// Recipes using this Unit
	command = TQString( "SELECT DISTINCT r.id,r.title  FROM recipes r,ingredient_list il WHERE r.id=il.recipe_id AND il.unit_id=%1;" ).arg( unitID ); // Without "DISTINCT" we get duplicates since ingredient_list has no unique recipe_id's
	unitToRemove.exec( command );
	loadElementList( recipes, &unitToRemove );

	// Weights using this unit
	command = TQString( "SELECT i.name,weight_u.name,per_u.name,w.prep_method_id FROM ingredients i,ingredient_weights w,units weight_u,units per_u WHERE i.id=w.ingredient_id AND w.unit_id=per_u.id AND w.weight_unit_id=weight_u.id AND (weight_u.id=%1 OR per_u.id=%2)" )
	  .arg( unitID )
	  .arg( unitID );
	unitToRemove.exec( command );
	if ( unitToRemove.isActive() ) {
		while ( unitToRemove.next() ) {
			Element el;

			TQString ingName = unescapeAndDecode( unitToRemove.value( 0 ).toCString() );
			TQString weightUnit = unescapeAndDecode( unitToRemove.value( 1 ).toCString() );
			TQString perUnit = unescapeAndDecode( unitToRemove.value( 2 ).toCString() );

			int prepID = unitToRemove.value( 3 ).toInt();
			TQString prep;
			if ( prepID != -1 ) {
				command = TQString( "SELECT p.name FROM prep_methods p, ingredient_weights w WHERE p.id = w.prep_method_id AND w.prep_method_id=%1" )
					.arg( prepID );
				TQSqlQuery query( command, database );
				if ( query.isActive() && query.first() )
					prep = unescapeAndDecode( query.value( 0 ).toCString() );
			}

			el.name = TQString( i18n("In ingredient '%1': weight [%2/%3%4]") ).arg( ingName ).arg( weightUnit ).arg( perUnit ).arg( (prepID == -1)?TQString::null:"; "+prep );
			weights->append( el );
		}
	}

}

void TQSqlRecipeDB::findPrepMethodDependancies( int prepMethodID, ElementList *recipes )
{
	//get just the ids first so that we can use DISTINCT
	TQString command = TQString( "SELECT DISTINCT r.id FROM recipes r,ingredient_list il, prep_method_list pl WHERE r.id=il.recipe_id AND pl.ingredient_list_id=il.id AND pl.prep_method_id=%1;" ).arg( prepMethodID );

	TQStringList ids;
	TQSqlQuery query( command, database );
	if ( query.isActive() ) {
		while ( query.next() ) {
			ids << TQString::number(query.value( 0 ).toInt());
		}
	}

	//now get the titles of the ids
	command = TQString( "SELECT r.id, r.title FROM recipes r WHERE r.id IN ("+ids.join(",")+")" );
	TQSqlQuery prepMethodToRemove( command, database );
	loadElementList( recipes, &prepMethodToRemove );
}


void TQSqlRecipeDB::loadElementList( ElementList *elList, TQSqlQuery *query )
{
	if ( query->isActive() ) {
		while ( query->next() ) {
			Element el;
			el.id = query->value( 0 ).toInt();
			el.name = unescapeAndDecode( query->value( 1 ).toCString() );
			elList->append( el );
		}
	}
}
// See function "findUnitDependancies" for use
void TQSqlRecipeDB::loadPropertyElementList( ElementList *elList, TQSqlQuery *query )
{
	if ( query->isActive() ) {
		while ( query->next() ) {
			Element el;
			el.id = -1; // There's no ID for the ingredient-property combination
			TQString ingName = unescapeAndDecode( query->value( 0 ).toCString() );
			TQString propName = unescapeAndDecode( query->value( 1 ).toCString() );
			TQString propUnits = unescapeAndDecode( query->value( 2 ).toCString() );
			TQString propPerUnits = unescapeAndDecode( query->value( 3 ).toCString() );

			el.name = TQString( i18n("In ingredient '%1': property \"%2\" [%3/%4]") ).arg( ingName ).arg( propName ).arg( propUnits ).arg( propPerUnits );
			elList->append( el );
		}
	}
}


//The string going into the database is utf8 text interpreted as latin1
TQString TQSqlRecipeDB::escapeAndEncode( const TQString &s ) const
{
	TQString s_escaped = s;

	s_escaped.replace ( "'", "\\'" );
	s_escaped.replace ( ";", "\";@" ); // Small trick for only for parsing later on

	return TQString::fromLatin1( s_escaped.utf8() );
}

//The string coming out of the database is utf8 text, interpreted as though latin1.  Calling fromUtf8() on this gives us back the original utf8.
TQString TQSqlRecipeDB::unescapeAndDecode( const TQCString &s ) const
{
	return TQString::fromUtf8( s ).replace( "\";@", ";" ); // Use unicode encoding
}

bool TQSqlRecipeDB::ingredientContainsUnit( int ingredientID, int unitID )
{
	TQString command = TQString( "SELECT *  FROM unit_list WHERE ingredient_id= %1 AND unit_id=%2;" ).arg( ingredientID ).arg( unitID );
	TQSqlQuery recipeToLoad( command, database );
	if ( recipeToLoad.isActive() ) {
		return ( recipeToLoad.size() > 0 );
	}
	return false;
}

bool TQSqlRecipeDB::ingredientContainsProperty( int ingredientID, int propertyID, int perUnitsID )
{
	TQString command = TQString( "SELECT *  FROM ingredient_info WHERE ingredient_id=%1 AND property_id=%2 AND per_units=%3;" ).arg( ingredientID ).arg( propertyID ).arg( perUnitsID );
	TQSqlQuery recipeToLoad( command, database );
	if ( recipeToLoad.isActive() ) {
		return ( recipeToLoad.size() > 0 );
	}
	return false;
}

TQString TQSqlRecipeDB::categoryName( int ID )
{
	TQString command = TQString( "SELECT name FROM categories WHERE id=%1;" ).arg( ID );
	TQSqlQuery toLoad( command, database );
	if ( toLoad.isActive() && toLoad.next() )  // Go to the first record (there should be only one anyway.
		return ( unescapeAndDecode( toLoad.value( 0 ).toCString() ) );

	return ( TQString::null );
}

TQString TQSqlRecipeDB::ingredientName( int ID )
{
	TQString command = TQString( "SELECT name FROM ingredients WHERE id=%1" ).arg( ID );
	TQSqlQuery toLoad( command, database );
	if ( toLoad.isActive() && toLoad.next() )  // Go to the first record (there should be only one anyway.
		return ( unescapeAndDecode( toLoad.value( 0 ).toCString() ) );

	return ( TQString::null );
}

TQString TQSqlRecipeDB::prepMethodName( int ID )
{
	TQString command = TQString( "SELECT name FROM prep_methods WHERE id=%1" ).arg( ID );
	TQSqlQuery toLoad( command, database );
	if ( toLoad.isActive() && toLoad.next() )  // Go to the first record (there should be only one anyway.
		return ( unescapeAndDecode( toLoad.value( 0 ).toCString() ) );

	return ( TQString::null );
}

IngredientProperty TQSqlRecipeDB::propertyName( int ID )
{
	TQString command = TQString( "SELECT name,units FROM ingredient_properties WHERE id=%1;" ).arg( ID );
	TQSqlQuery toLoad( command, database );
	if ( toLoad.isActive() && toLoad.next() ) { // Go to the first record (there should be only one anyway.
		return ( IngredientProperty( unescapeAndDecode( toLoad.value( 0 ).toCString() ), unescapeAndDecode( toLoad.value( 1 ).toCString() ), ID ) );
	}

	return ( IngredientProperty( TQString::null, TQString::null ) );
}

Unit TQSqlRecipeDB::unitName( int ID )
{
	TQString command = TQString( "SELECT name,plural,name_abbrev,plural_abbrev,type FROM units WHERE id=%1" ).arg( ID );
	TQSqlQuery toLoad( command, database );
	if ( toLoad.isActive() && toLoad.next() ) { // Go to the first record (there should be only one anyway.
		Unit unit( unescapeAndDecode( toLoad.value( 0 ).toCString() ), unescapeAndDecode( toLoad.value( 1 ).toCString() ) );

		//if we don't have both name and plural, use what we have as both
		if ( unit.name.isEmpty() )
			unit.name = unit.plural;
		else if ( unit.plural.isEmpty() )
			unit.plural = unit.name;

		unit.name_abbrev = unescapeAndDecode( toLoad.value( 2 ).toCString() );
		unit.plural_abbrev = unescapeAndDecode( toLoad.value( 3 ).toCString() );
		unit.type = (Unit::Type) toLoad.value( 4 ).toInt();
		unit.id = ID;

		return unit;
	}

	return Unit();
}

int TQSqlRecipeDB::getCount( const TQString &table_name )
{
	m_command = "SELECT COUNT(1) FROM "+table_name;
	TQSqlQuery count( m_command, database );
	if ( count.isActive() && count.next() ) { // Go to the first record (there should be only one anyway.
		return count.value( 0 ).toInt();
	}

	return -1;
}

int TQSqlRecipeDB::categoryTopLevelCount()
{
	m_command = "SELECT COUNT(1) FROM categories WHERE parent_id='-1'";
	TQSqlQuery count( m_command, database );
	if ( count.isActive() && count.next() ) { // Go to the first record (there should be only one anyway.
		return count.value( 0 ).toInt();
	}

	return -1;
}

bool TQSqlRecipeDB::checkIntegrity( void )
{


	// Check existence of the necessary tables (the database may be created, but empty)
	TQStringList tables;
	tables << "ingredient_info" << "ingredient_list" << "ingredient_properties" << "ingredient_weights" << "ingredients" << "recipes" << "unit_list" << "units" << "units_conversion" << "categories" << "category_list" << "authors" << "author_list" << "db_info" << "prep_methods" << "ingredient_groups" << "yield_types" << "prep_method_list" << "ratings" << "rating_criteria" << "rating_criterion_list";

	TQStringList existingTableList = database->tables();
	for ( TQStringList::Iterator it = tables.begin(); it != tables.end(); ++it ) {
		bool found = false;

		for ( TQStringList::Iterator ex_it = existingTableList.begin(); ( ( ex_it != existingTableList.end() ) && ( !found ) ); ++ex_it ) {
			found = ( *ex_it == *it );
		}

		if ( !found ) {
			kdDebug() << "Recreating missing table: " << *it << "\n";
			createTable( *it );
		}
	}

	TQStringList newTableList = database->tables();
	if ( newTableList.isEmpty() )
		return false;


	// Check for older versions, and port

	kdDebug() << "Checking database version...\n";
	float version = databaseVersion();
	kdDebug() << "version found... " << version << " \n";
	kdDebug() << "latest version... " << latestDBVersion() << endl;
	if ( int( tqRound( databaseVersion() * 1e5 ) ) < int( tqRound( latestDBVersion() * 1e5 ) ) ) { //correct for float's imprecision
		switch ( KMessageBox::questionYesNo( 0, i18n( "<!doc>The database was created with a previous version of Krecipes.  Would you like Krecipes to update this database to work with this version of Krecipes?  Depending on the number of recipes and amount of data, this could take some time.<br><br><b>Warning: After updating, this database will no longer be compatible with previous versions of Krecipes.<br><br>Cancelling this operation may result in corrupting the database.</b>" ) ) ) {
		case KMessageBox::Yes:
			emit progressBegin(0,TQString::null,i18n("Porting database structure..."),50);
			portOldDatabases( version );
			emit progressDone();
			break;
		case KMessageBox::No:
			return false;
		}
	}

	return true;
}

void TQSqlRecipeDB::splitCommands( TQString& s, TQStringList& sl )
{
	sl = TQStringList::split( TQRegExp( ";{1}(?!@)" ), s );
}

void TQSqlRecipeDB::portOldDatabases( float /* version */ )
{}

float TQSqlRecipeDB::databaseVersion( void )
{

	TQString command = "SELECT ver FROM db_info";
	TQSqlQuery dbVersion( command, database );

	if ( dbVersion.isActive() && dbVersion.next() )
		return ( dbVersion.value( 0 ).toDouble() ); // There should be only one (or none for old DB) element, so go to first
	else
		return ( 0.2 ); // if table is empty, assume oldest (0.2), and port
}

void TQSqlRecipeDB::loadRatingCriterion( ElementList *list, int limit, int offset )
{
	list->clear();

	TQString command = "SELECT id,name FROM rating_criteria ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));
	TQSqlQuery toLoad( command, database );
	if ( toLoad.isActive() ) {
		while ( toLoad.next() ) {
			Element el;
			el.id = toLoad.value( 0 ).toInt();
			el.name = unescapeAndDecode( toLoad.value( 1 ).toCString() );
			list->append( el );
		}
	}
}

void TQSqlRecipeDB::loadCategories( ElementList *list, int limit, int offset )
{
	list->clear();

	m_command = "SELECT id,name FROM categories ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));
	TQSqlQuery categoryToLoad( m_command, database );
	if ( categoryToLoad.isActive() ) {
		while ( categoryToLoad.next() ) {
			Element el;
			el.id = categoryToLoad.value( 0 ).toInt();
			el.name = unescapeAndDecode( categoryToLoad.value( 1 ).toCString() );
			list->append( el );
		}
	}
}

void TQSqlRecipeDB::loadCategories( CategoryTree *list, int limit, int offset, int parent_id, bool recurse )
{
	TQString limit_str;
	if ( parent_id == -1 ) {
		emit progressBegin(0,TQString::null,i18n("Loading category list"));
		list->clear();

		//only limit the number of top-level categories
		limit_str = (limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset);
	}

	m_command = "SELECT id,name,parent_id FROM categories WHERE parent_id='"+TQString::number(parent_id)+"' ORDER BY name "+limit_str;

	TQSqlQuery categoryToLoad( TQString::null, database );
	//categoryToLoad.setForwardOnly(true); //FIXME? Subcategories aren't loaded if this is enabled, even though we only go forward

	categoryToLoad.exec(m_command);

	if ( categoryToLoad.isActive() ) {
		while ( categoryToLoad.next() ) {
			emit progress();

			int id = categoryToLoad.value( 0 ).toInt();
			Element el;
			el.id = id;
			el.name = unescapeAndDecode( categoryToLoad.value( 1 ).toCString() );
			CategoryTree *list_child = list->add( el );

			if ( recurse ) {
				//TQTime dbg_timer; dbg_timer.start(); kdDebug()<<"   calling TQSqlRecipeDB::loadCategories"<<endl;
				loadCategories( list_child, -1, -1, id ); //limit and offset won't be used
				// kdDebug()<<"   done in "<<dbg_timer.elapsed()<<" ms"<<endl;
			}
		}
	}

	if ( parent_id == -1 )
		emit progressDone();
}

void TQSqlRecipeDB::createNewCategory( const TQString &categoryName, int parent_id )
{
	TQString command;
	TQString real_name = categoryName.left( maxCategoryNameLength() );

	command = TQString( "INSERT INTO categories VALUES(%3,'%1',%2);" )
	          .arg( escapeAndEncode( real_name ) )
	          .arg( parent_id )
	          .arg( getNextInsertIDStr( "categories", "id" ) );
	TQSqlQuery categoryToCreate( command, database );

	emit categoryCreated( Element( real_name, lastInsertID() ), parent_id );
}

void TQSqlRecipeDB::modCategory( int categoryID, const TQString &newLabel )
{
	TQString command = TQString( "UPDATE categories SET name='%1' WHERE id=%2;" ).arg( escapeAndEncode( newLabel ) ).arg( categoryID );
	TQSqlQuery categoryToUpdate( command, database );

	emit categoryModified( Element( newLabel, categoryID ) );
}

void TQSqlRecipeDB::modCategory( int categoryID, int new_parent_id )
{
	TQString command = TQString( "UPDATE categories SET parent_id=%1 WHERE id=%2;" ).arg( new_parent_id ).arg( categoryID );
	TQSqlQuery categoryToUpdate( command, database );

	emit categoryModified( categoryID, new_parent_id );
}

void TQSqlRecipeDB::removeCategory( int categoryID )
{
	TQString command;

	command = TQString( "DELETE FROM categories WHERE id=%1;" ).arg( categoryID );
	TQSqlQuery categoryToRemove( command, database );

	command = TQString( "DELETE FROM category_list WHERE category_id=%1;" ).arg( categoryID );
	categoryToRemove.exec( command );

	//recursively delete subcategories
	command = TQString( "SELECT id FROM categories WHERE parent_id=%1;" ).arg( categoryID );
	categoryToRemove.exec( command );
	if ( categoryToRemove.isActive() ) {
		while ( categoryToRemove.next() ) {
			removeCategory( categoryToRemove.value( 0 ).toInt() );
		}
	}

	emit categoryRemoved( categoryID );
}


void TQSqlRecipeDB::loadAuthors( ElementList *list, int limit, int offset )
{
	list->clear();
	TQString command = "SELECT id,name FROM authors ORDER BY name"
	  +((limit==-1)?"":" LIMIT "+TQString::number(limit)+" OFFSET "+TQString::number(offset));
	TQSqlQuery authorToLoad( command, database );
	if ( authorToLoad.isActive() ) {
		while ( authorToLoad.next() ) {
			Element el;
			el.id = authorToLoad.value( 0 ).toInt();
			el.name = unescapeAndDecode( authorToLoad.value( 1 ).toCString() );
			list->append( el );
		}
	}
}

void TQSqlRecipeDB::createNewAuthor( const TQString &authorName )
{
	TQString command;
	TQString real_name = authorName.left( maxAuthorNameLength() );

	command = TQString( "INSERT INTO authors VALUES(%2,'%1');" ).arg( escapeAndEncode( real_name ) ).arg( getNextInsertIDStr( "authors", "id" ) );
	TQSqlQuery authorToCreate( command, database );

	emit authorCreated( Element( real_name, lastInsertID() ) );
}

void TQSqlRecipeDB::modAuthor( int authorID, const TQString &newLabel )
{
	TQString command;

	command = TQString( "UPDATE authors SET name='%1' WHERE id=%2;" ).arg( escapeAndEncode( newLabel ) ).arg( authorID );
	TQSqlQuery authorToCreate( command, database );

	emit authorRemoved( authorID );
	emit authorCreated( Element( newLabel, authorID ) );
}

void TQSqlRecipeDB::removeAuthor( int authorID )
{
	TQString command;

	command = TQString( "DELETE FROM authors WHERE id=%1;" ).arg( authorID );
	TQSqlQuery authorToRemove( command, database );

	emit authorRemoved( authorID );
}

int TQSqlRecipeDB::findExistingAuthorByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxAuthorNameLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM authors WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingCategoryByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxCategoryNameLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM categories WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingIngredientGroupByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxIngGroupNameLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM ingredient_groups WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingIngredientByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxIngredientNameLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM ingredients WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingPrepByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxPrepMethodNameLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM prep_methods WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingPropertyByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxPropertyNameLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM ingredient_properties WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingUnitByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxUnitNameLength() ) ); //truncate to the maximum size db holds

	TQString command = "SELECT id FROM units WHERE name LIKE '" + search_str 
		  + "' OR plural LIKE '" + search_str 
		  + "' OR name_abbrev LIKE '" + search_str 
		  + "' OR plural_abbrev LIKE '" + search_str 
		  + "'";

	TQSqlQuery elementToLoad( command, database ); // Run the query
	int id = -1;

	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingRatingByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM rating_criteria WHERE name LIKE '%1'" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query

	int id = -1;
	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingRecipeByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxRecipeTitleLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM recipes WHERE title LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query

	int id = -1;
	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

int TQSqlRecipeDB::findExistingYieldTypeByName( const TQString& name )
{
	TQString search_str = escapeAndEncode( name.left( maxYieldTypeLength() ) ); //truncate to the maximum size db holds

	TQString command = TQString( "SELECT id FROM yield_types WHERE name LIKE '%1';" ).arg( search_str );
	TQSqlQuery elementToLoad( command, database ); // Run the query

	int id = -1;
	if ( elementToLoad.isActive() && elementToLoad.first() )
		id = elementToLoad.value( 0 ).toInt();

	return id;
}

void TQSqlRecipeDB::mergeAuthors( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1'
	TQString command = TQString( "UPDATE author_list SET author_id=%1 WHERE author_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//and ensure no duplicates were created in this process
	command = TQString( "SELECT recipe_id FROM author_list WHERE author_id=%1 ORDER BY recipe_id" )
	          .arg( id1 );
	update.exec( command );
	int last_id = -1;
	if ( update.isActive() ) {
		while ( update.next() ) {
			int current_id = update.value( 0 ).toInt();
			if ( last_id == current_id ) {
				int count = -1;
				command = TQString( "SELECT COUNT(1) FROM author_list WHERE author_id=%1 AND recipe_id=%2" )
				          .arg( id1 )
				          .arg( last_id );
				TQSqlQuery remove( command, database);
				if ( remove.isActive() && remove.first() )
					count = remove.value(0).toInt();
				if ( count > 1 ) {
					command = TQString( "DELETE FROM author_list WHERE author_id=%1 AND recipe_id=%2" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
	
					command = TQString( "INSERT INTO author_list VALUES(%1,%2)" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
				}
			}
			last_id = current_id;
		}
	}

	//remove author with id 'id2'
	command = TQString( "DELETE FROM authors WHERE id=%1" ).arg( id2 );
	update.exec( command );
	emit authorRemoved( id2 );
}

void TQSqlRecipeDB::mergeCategories( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1'
	TQString command = TQString( "UPDATE category_list SET category_id=%1 WHERE category_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//and ensure no duplicates were created in this process
	command = TQString( "SELECT recipe_id FROM category_list WHERE category_id=%1 ORDER BY recipe_id" )
	          .arg( id1 );
	update.exec( command );
	int last_id = -1;
	if ( update.isActive() ) {
		while ( update.next() ) {
			int current_id = update.value( 0 ).toInt();
			if ( last_id == current_id ) {
				int count = -1;
				command = TQString( "SELECT COUNT(1) FROM category_list WHERE category_id=%1 AND recipe_id=%2" )
				          .arg( id1 )
				          .arg( last_id );
				TQSqlQuery remove( command, database);
				if ( remove.isActive() && remove.first() )
					count = remove.value(0).toInt();
				if ( count > 1 ) {
					command = TQString( "DELETE FROM category_list WHERE category_id=%1 AND recipe_id=%2" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
	
					command = TQString( "INSERT INTO category_list VALUES(%1,%2)" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
				}
			}
			last_id = current_id;
		}
	}

	command = TQString( "UPDATE categories SET parent_id=%1 WHERE parent_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//we don't want to have a category be its own parent...
	command = TQString( "UPDATE categories SET parent_id=-1 WHERE parent_id=id" );
	update.exec( command );

	//remove category with id 'id2'
	command = TQString( "DELETE FROM categories WHERE id=%1" ).arg( id2 );
	update.exec( command );

	emit categoriesMerged( id1, id2 );
}

void TQSqlRecipeDB::mergeIngredientGroups( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1'
	TQString command = TQString( "UPDATE ingredient_list SET group_id=%1 WHERE group_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//remove ingredient with id 'id2'
	command = TQString( "DELETE FROM ingredient_groups WHERE id=%1" ).arg( id2 );
	update.exec( command );
	emit ingGroupRemoved( id2 );
}

void TQSqlRecipeDB::mergeIngredients( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1'
	TQString command = TQString( "UPDATE ingredient_list SET ingredient_id=%1 WHERE ingredient_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//delete nutrient info associated with ingredient with id 'id2'
	command = TQString( "DELETE FROM ingredient_info WHERE ingredient_id=%1" )
	          .arg( id2 );
	update.exec( command );

	//update the unit_list
	command = TQString( "UPDATE unit_list SET ingredient_id=%1 WHERE ingredient_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//and ensure no duplicates were created in this process
	command = TQString( "SELECT unit_id FROM unit_list WHERE ingredient_id=%1 ORDER BY unit_id" )
	          .arg( id1 );
	update.exec( command );
	int last_id = -1;
	if ( update.isActive() ) {
		while ( update.next() ) {
			int current_id = update.value( 0 ).toInt();
			if ( last_id == current_id ) {
				int count = -1;
				command = TQString( "SELECT COUNT(1) FROM unit_list WHERE ingredient_id=%1 AND unit_id=%2" )
				          .arg( id1 )
				          .arg( last_id );
				TQSqlQuery remove( command, database);
				if ( remove.isActive() && remove.first() )
					count = remove.value(0).toInt();
				if ( count > 1 ) {
					command = TQString( "DELETE FROM unit_list WHERE ingredient_id=%1 AND unit_id=%2" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
	
					command = TQString( "INSERT INTO unit_list VALUES(%1,%2)" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
				}
			}
			last_id = current_id;
		}
	}

	//update ingredient info
	command = TQString( "UPDATE ingredient_info SET ingredient_id=%1 WHERE ingredient_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//and ensure no duplicates were created in this process
	//info associated with one ingredient will be lost... they should be the same ingredient and thus info anyways
	command = TQString( "SELECT property_id FROM ingredient_info WHERE ingredient_id=%1 ORDER BY property_id" )
	          .arg( id1 );
	update.exec( command );
	last_id = -1;
	if ( update.isActive() ) {
		while ( update.next() ) {
			int current_id = update.value( 0 ).toInt();
			if ( last_id == current_id ) {
				int count = -1;
				command = TQString( "SELECT COUNT(1) FROM ingredient_info WHERE ingredient_id=%1 AND property_id=%2" )
				          .arg( id1 )
				          .arg( last_id );
				TQSqlQuery remove( command, database);
				if ( remove.isActive() && remove.first() )
					count = remove.value(0).toInt();
				if ( count > 1 ) {
					command = TQString( "DELETE FROM ingredient_info WHERE ingredient_id=%1 AND property_id=%2" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
	
					command = TQString( "INSERT INTO ingredient_info VALUES(%1,%2)" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
				}

			}
			last_id = current_id;
		}
	}

	//remove ingredient with id 'id2'
	command = TQString( "DELETE FROM ingredients WHERE id=%1" ).arg( id2 );
	update.exec( command );
	emit ingredientRemoved( id2 );
}

void TQSqlRecipeDB::mergePrepMethods( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1' in ingredient list
	TQString command = TQString( "UPDATE prep_method_list SET prep_method_id=%1 WHERE prep_method_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//change all instances of 'id2' to 'id1' in ingredient weights
	command = TQString( "UPDATE ingredient_weights SET prep_method_id=%1 WHERE prep_method_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//remove prep method with id 'id2'
	command = TQString( "DELETE FROM prep_methods WHERE id=%1" ).arg( id2 );
	update.exec( command );
	emit prepMethodRemoved( id2 );
}

void TQSqlRecipeDB::mergeProperties( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1'
	TQString command = TQString( "UPDATE ingredient_properties SET id=%1 WHERE id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	command = TQString( "UPDATE ingredient_info SET property_id=%1 WHERE property_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//remove prep method with id 'id2'
	command = TQString( "DELETE FROM ingredient_properties WHERE id=%1" ).arg( id2 );
	update.exec( command );
	emit propertyRemoved( id2 );
}

void TQSqlRecipeDB::mergeUnits( int id1, int id2 )
{
	TQSqlQuery update( TQString::null, database );

	//change all instances of 'id2' to 'id1' in unit list
	TQString command = TQString( "UPDATE unit_list SET unit_id=%1 WHERE unit_id=%2" )
	                  .arg( id1 )
	                  .arg( id2 );
	update.exec( command );

	//change all instances of 'id2' to 'id1' in ingredient list
	command = TQString( "UPDATE ingredient_list SET unit_id=%1 WHERE unit_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//and ensure no duplicates were created in this process
	command = TQString( "SELECT ingredient_id FROM unit_list WHERE unit_id=%1 ORDER BY ingredient_id" )
	          .arg( id1 );
	update.exec( command );
	int last_id = -1;
	if ( update.isActive() ) {
		while ( update.next() ) {
			int current_id = update.value( 0 ).toInt();
			if ( last_id == current_id ) {
				int count = -1;
				command = TQString( "SELECT COUNT(1) FROM unit_list WHERE ingredient_id=%1 AND unit_id=%2" )
				          .arg( id1 )
				          .arg( last_id );
				TQSqlQuery remove( command, database);
				if ( remove.isActive() && remove.first() )
					count = remove.value(0).toInt();
				if ( count > 1 ) {
					command = TQString( "DELETE FROM unit_list WHERE ingredient_id=%1 AND unit_id=%2" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
	
					command = TQString( "INSERT INTO unit_list VALUES(%1,%2)" )
						.arg( id1 )
						.arg( last_id );
					database->exec( command );
				}
			}
			last_id = current_id;
		}
	}

	//update ingredient info
	command = TQString( "UPDATE ingredient_info SET per_units=%1 WHERE per_units=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//change all instances of 'id2' to 'id1' in unit_conversion
	command = TQString( "UPDATE units_conversion SET unit1_id=%1 WHERE unit1_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );
	command = TQString( "UPDATE units_conversion SET unit2_id=%1 WHERE unit2_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//and ensure that the one to one ratio wasn't created
	command = TQString( "DELETE FROM units_conversion WHERE unit1_id=unit2_id" );
	update.exec( command );

	//update ingredient weights
	command = TQString( "UPDATE ingredient_weights SET unit_id=%1 WHERE unit_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );
	command = TQString( "UPDATE ingredient_weights SET weight_unit_id=%1 WHERE weight_unit_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//change all instances of 'id2' to 'id1' in ingredient weights
	command = TQString( "UPDATE ingredient_weights SET unit_id=%1 WHERE unit_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	command = TQString( "UPDATE ingredient_weights SET weight_unit_id=%1 WHERE weight_unit_id=%2" )
	          .arg( id1 )
	          .arg( id2 );
	update.exec( command );

	//remove units with id 'id2'
	command = TQString( "DELETE FROM units WHERE id=%1" ).arg( id2 );
	update.exec( command );
	emit unitRemoved( id2 );
}

TQString TQSqlRecipeDB::getUniqueRecipeTitle( const TQString &recipe_title )
{
	//already is unique
	if ( findExistingRecipeByName( recipe_title ) == -1 )
		return recipe_title;

	TQString return_title = recipe_title; //If any error is produced, just go for default value (always return something)

	TQString command = TQString( "SELECT COUNT(1) FROM recipes WHERE title LIKE '%1 (%)';" ).arg( escapeAndEncode( recipe_title ) );

	TQSqlQuery alikeRecipes( command, database );
	if ( alikeRecipes.isActive() && alikeRecipes.first() )
	{
		int count = alikeRecipes.value( 0 ).toInt();
		return_title = TQString( "%1 (%2)" ).arg( recipe_title ).arg( count + 2 );

		//make sure this newly created title is unique (just in case)
		while ( findExistingRecipeByName( return_title ) != -1 ) {
			count--; //go down to find the skipped recipe(s)
			return_title = TQString( "%1 (%2)" ).arg( recipe_title ).arg( count + 2 );
		}
	}

	return return_title;
}

TQString TQSqlRecipeDB::recipeTitle( int recipeID )
{
	TQString command = TQString( "SELECT title FROM recipes WHERE id=%1;" ).arg( recipeID );
	TQSqlQuery recipeToLoad( command, database );
	if ( recipeToLoad.isActive() && recipeToLoad.next() )  // Go to the first record (there should be only one anyway.
		return ( unescapeAndDecode(recipeToLoad.value( 0 ).toCString()) );

	return ( TQString::null );
}

void TQSqlRecipeDB::emptyData( void )
{
	TQStringList tables;
	tables << "ingredient_info" << "ingredient_list" << "ingredient_properties" << "ingredients" << "recipes" << "unit_list" << "units" << "units_conversion" << "categories" << "category_list" << "authors" << "author_list" << "prep_methods" << "ingredient_groups" << "yield_types" << "ratings" << "rating_criteria" << "rating_criterion_list";
	TQSqlQuery tablesToEmpty( TQString::null, database );
	for ( TQStringList::Iterator it = tables.begin(); it != tables.end(); ++it ) {
		TQString command = TQString( "DELETE FROM %1;" ).arg( *it );
		tablesToEmpty.exec( command );
	}
}

void TQSqlRecipeDB::empty( void )
{
	TQSqlQuery tablesToEmpty( TQString::null, database );

	TQStringList list = database->tables();
	TQStringList::const_iterator it = list.begin();
	while( it != list.end() ) {
		TQString command = TQString( "DROP TABLE %1;" ).arg( *it );
		tablesToEmpty.exec( command );

		if ( !tablesToEmpty.isActive() )
			kdDebug()<<tablesToEmpty.lastError().databaseText()<<endl;

		++it;
	}
}

TQString TQSqlRecipeDB::getNextInsertIDStr( const TQString &table, const TQString &column )
{
	int next_id = getNextInsertID( table, column );

	TQString id_str;
	if ( next_id == -1 )
		id_str = "NULL";
	else
		id_str = TQString::number( next_id );

	return id_str;
}

void TQSqlRecipeDB::search( RecipeList *list, int items, const RecipeSearchParameters &parameters )
{
	TQString query = buildSearchQuery(parameters);

	TQValueList<int> ids;
	TQSqlQuery recipeToLoad( query, database );
	if ( recipeToLoad.isActive() ) {
		while ( recipeToLoad.next() ) {
			ids << recipeToLoad.value( 0 ).toInt();
		}
	}

	if ( ids.count() > 0 )
		loadRecipes( list, items, ids );
}

#include "qsqlrecipedb.moc"