KRecipes – cooking recipes manager
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 

555 lines
22 KiB

  1. /***************************************************************************
  2. * Copyright (C) 2003 by *
  3. * Unai Garro (ugarro@users.sourceforge.net) *
  4. * Cyril Bosselut (bosselut@b1project.com) *
  5. * Jason Kivlighn (jkivlighn@gmail.com) *
  6. * *
  7. * Copyright (C) 2006 Jason Kivlighn (jkivlighn@gmail.com) *
  8. * *
  9. * This program is free software; you can redistribute it and/or modify *
  10. * it under the terms of the GNU General Public License as published by *
  11. * the Free Software Foundation; either version 2 of the License, or *
  12. * (at your option) any later version. *
  13. ***************************************************************************/
  14. #include "mysqlrecipedb.h"
  15. #include <kdebug.h>
  16. #include <kstandarddirs.h>
  17. #include <tdetempfile.h>
  18. #include <tdelocale.h>
  19. #include <tdeconfig.h>
  20. #include <tdeglobal.h>
  21. MySQLRecipeDB::MySQLRecipeDB( const TQString &host, const TQString &user, const TQString &pass, const TQString &DBname, int port ) : TQSqlRecipeDB( host, user, pass, DBname, port )
  22. {}
  23. MySQLRecipeDB::~MySQLRecipeDB()
  24. {}
  25. void MySQLRecipeDB::createDB()
  26. {
  27. TQString real_db_name = database->databaseName();
  28. //we have to be connected to some database in order to create the Krecipes database
  29. //so long as the permissions given are allowed access to "mysql', this works
  30. database->setDatabaseName( "mysql" );
  31. if ( database->open() ) {
  32. // Create the Database (Note: needs permissions)
  33. //FIXME: I've noticed certain characters cause this to fail (such as '-'). Somehow let the user know.
  34. TQSqlQuery query( TQString( "CREATE DATABASE %1" ).arg( real_db_name ), database );
  35. if ( !query.isActive() )
  36. kdDebug() << "create query failed: " << database->lastError().databaseText() << endl;
  37. database->close();
  38. }
  39. else
  40. kdDebug() << "create open failed: " << database->lastError().databaseText() << endl;
  41. database->setDatabaseName( real_db_name );
  42. }
  43. TQStringList MySQLRecipeDB::backupCommand() const
  44. {
  45. TDEConfig *config = TDEGlobal::config();
  46. config->setGroup("Server");
  47. TQStringList command;
  48. command<<config->readEntry( "MySQLDumpPath", "mysqldump" )<<"-q";
  49. TQString pass = config->readEntry("Password", TQString::null);
  50. if ( !pass.isEmpty() )
  51. command<<"-p"+pass;
  52. TQString user = config->readEntry("Username", TQString::null);
  53. command<<"-u"+user;
  54. command<<"-h"+config->readEntry("Host", "localhost");
  55. int port = config->readNumEntry("Port", 0);
  56. if ( port > 0 )
  57. command<<"-P"+TQString::number(port);
  58. command<<database->databaseName();
  59. return command;
  60. }
  61. TQStringList MySQLRecipeDB::restoreCommand() const
  62. {
  63. TDEConfig *config = TDEGlobal::config();
  64. config->setGroup("Server");
  65. TQStringList command;
  66. command<<config->readEntry( "MySQLPath", "mysql" );
  67. TQString pass = config->readEntry("Password", TQString::null);
  68. if ( !pass.isEmpty() )
  69. command<<"-p"+pass;
  70. TQString user = config->readEntry("Username", TQString::null);
  71. command<<"-u"+user;
  72. int port = config->readNumEntry("Port", 0);
  73. if ( port > 0 )
  74. command<<"-P"+TQString::number(port);
  75. command<<"-h"+config->readEntry("Host", "localhost");
  76. command<<database->databaseName();
  77. return command;
  78. }
  79. void MySQLRecipeDB::createTable( const TQString &tableName )
  80. {
  81. TQStringList commands;
  82. if ( tableName == "recipes" )
  83. commands << TQString( "CREATE TABLE recipes (id INTEGER NOT NULL AUTO_INCREMENT,title VARCHAR(%1), yield_amount FLOAT, yield_amount_offset FLOAT, yield_type_id int(11) DEFAULT '-1', instructions TEXT, photo BLOB, prep_time TIME, ctime TIMESTAMP, mtime TIMESTAMP, atime TIMESTAMP, PRIMARY KEY (id));" ).arg( maxRecipeTitleLength() );
  84. else if ( tableName == "ingredients" )
  85. commands << TQString( "CREATE TABLE ingredients (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(%1), PRIMARY KEY (id));" ).arg( maxIngredientNameLength() );
  86. else if ( tableName == "ingredient_list" )
  87. commands << "CREATE TABLE ingredient_list (id INTEGER NOT NULL AUTO_INCREMENT, recipe_id INTEGER, ingredient_id INTEGER, amount FLOAT, amount_offset FLOAT, unit_id INTEGER, order_index INTEGER, group_id INTEGER, substitute_for INTEGER, PRIMARY KEY(id), INDEX ridil_index(recipe_id), INDEX iidil_index(ingredient_id), INDEX gidil_index(group_id))";
  88. else if ( tableName == "unit_list" )
  89. commands << "CREATE TABLE unit_list (ingredient_id INTEGER, unit_id INTEGER);";
  90. else if ( tableName == "units" )
  91. commands << TQString( "CREATE TABLE units (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(%1), name_abbrev VARCHAR(%2), plural VARCHAR(%3), plural_abbrev VARCHAR(%4), type INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (id));" )
  92. .arg( maxUnitNameLength() ).arg( maxUnitNameLength() ).arg( maxUnitNameLength() ).arg( maxUnitNameLength() );
  93. else if ( tableName == "prep_methods" )
  94. commands << TQString( "CREATE TABLE prep_methods (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(%1), PRIMARY KEY (id));" ).arg( maxPrepMethodNameLength() );
  95. else if ( tableName == "prep_method_list" )
  96. commands << "CREATE TABLE prep_method_list (ingredient_list_id int(11) NOT NULL,prep_method_id int(11) NOT NULL, order_index int(11), INDEX iid_index (ingredient_list_id), INDEX pid_index (prep_method_id));";
  97. else if ( tableName == "ingredient_info" )
  98. commands << "CREATE TABLE ingredient_info (ingredient_id INTEGER, property_id INTEGER, amount FLOAT, per_units INTEGER);";
  99. else if ( tableName == "ingredient_properties" )
  100. commands << "CREATE TABLE ingredient_properties (id INTEGER NOT NULL AUTO_INCREMENT,name VARCHAR(20), units VARCHAR(20), PRIMARY KEY (id));";
  101. else if ( tableName == "ingredient_weights" )
  102. commands << "CREATE TABLE ingredient_weights (id INTEGER NOT NULL AUTO_INCREMENT, ingredient_id INTEGER NOT NULL, amount FLOAT, unit_id INTEGER, weight FLOAT, weight_unit_id INTEGER, prep_method_id INTEGER, PRIMARY KEY (id), INDEX(ingredient_id), INDEX(unit_id), INDEX(weight_unit_id), INDEX(prep_method_id) );";
  103. else if ( tableName == "units_conversion" )
  104. commands << "CREATE TABLE units_conversion (unit1_id INTEGER, unit2_id INTEGER, ratio FLOAT);";
  105. else if ( tableName == "categories" )
  106. commands << TQString( "CREATE TABLE categories (id int(11) NOT NULL auto_increment, name varchar(%1) default NULL, parent_id int(11) NOT NULL default -1, PRIMARY KEY (id), INDEX parent_id_index(parent_id));" ).arg( maxCategoryNameLength() );
  107. else if ( tableName == "category_list" )
  108. commands << "CREATE TABLE category_list (recipe_id int(11) NOT NULL,category_id int(11) NOT NULL, INDEX rid_index (recipe_id), INDEX cid_index (category_id));";
  109. else if ( tableName == "authors" )
  110. commands << TQString( "CREATE TABLE authors (id int(11) NOT NULL auto_increment, name varchar(%1) default NULL,PRIMARY KEY (id));" ).arg( maxAuthorNameLength() );
  111. else if ( tableName == "author_list" )
  112. commands << "CREATE TABLE author_list (recipe_id int(11) NOT NULL,author_id int(11) NOT NULL);";
  113. else if ( tableName == "db_info" ) {
  114. commands << "CREATE TABLE db_info (ver FLOAT NOT NULL,generated_by varchar(200) default NULL);";
  115. commands << TQString( "INSERT INTO db_info VALUES(%1,'Krecipes %2');" ).arg( latestDBVersion() ).arg( krecipes_version() );
  116. }
  117. else if ( tableName == "ingredient_groups" ) {
  118. commands << TQString( "CREATE TABLE `ingredient_groups` (`id` int(11) NOT NULL auto_increment, `name` varchar(%1), PRIMARY KEY (`id`));" ).arg( maxIngGroupNameLength() );
  119. }
  120. else if ( tableName == "yield_types" ) {
  121. commands << TQString( "CREATE TABLE `yield_types` (`id` int(11) NOT NULL auto_increment, `name` varchar(%1), PRIMARY KEY (`id`));" ).arg( 20 );
  122. }
  123. else if ( tableName == "ratings" )
  124. commands << "CREATE TABLE ratings (id INTEGER NOT NULL AUTO_INCREMENT, recipe_id int(11) NOT NULL, comment TEXT, rater TEXT, created TIMESTAMP, PRIMARY KEY (id));";
  125. else if ( tableName == "rating_criteria" )
  126. commands << "CREATE TABLE rating_criteria (id INTEGER NOT NULL AUTO_INCREMENT, name TEXT, PRIMARY KEY (id));";
  127. else if ( tableName == "rating_criterion_list" )
  128. commands << "CREATE TABLE rating_criterion_list (rating_id INTEGER NOT NULL, rating_criterion_id INTEGER, stars FLOAT);";
  129. else
  130. return ;
  131. TQSqlQuery databaseToCreate( TQString::null, database );
  132. // execute the queries
  133. for ( TQStringList::const_iterator it = commands.begin(); it != commands.end(); ++it )
  134. databaseToCreate.exec( ( *it ) );
  135. }
  136. void MySQLRecipeDB::portOldDatabases( float version )
  137. {
  138. kdDebug() << "Current database version is..." << version << "\n";
  139. TQString command;
  140. // Note that version no. means the version in which this DB structure
  141. // was introduced. To work with SVN users, the database will be incrementally
  142. // upgraded for each change made between releases (e.g. 0.81, 0.82,... are
  143. // what will become 0.9)
  144. if ( tqRound(version*10) < 3 ) // The database was generated with a version older than v 0.3. First update to 0.3 version
  145. {
  146. // Add new columns to existing tables (creating new tables is not necessary. Integrity check does that before)
  147. command = "ALTER TABLE recipes ADD COLUMN persons int(11) AFTER title;";
  148. TQSqlQuery tableToAlter( command, database );
  149. // Set the version to the new one (0.3)
  150. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  151. tableToAlter.exec( command );
  152. command = "INSERT INTO db_info VALUES(0.3,'Krecipes 0.4');"; // Set the new version
  153. tableToAlter.exec( command );
  154. }
  155. if ( tqRound(version*10) < 4 ) // Upgrade to the current DB version 0.4
  156. {
  157. // Add new columns to existing tables (creating any new tables is not necessary. Integrity check does that before)
  158. command = "ALTER TABLE ingredient_list ADD COLUMN order_index int(11) AFTER unit_id;";
  159. TQSqlQuery tableToAlter( command, database );
  160. // Missing indexes in the previous versions
  161. command = "CREATE index rid_index ON category_list(recipe_id)";
  162. tableToAlter.exec( command );
  163. command = "CREATE index cid_index ON category_list(category_id)";
  164. tableToAlter.exec( command );
  165. command = "CREATE index ridil_index ON ingredient_list(recipe_id)";
  166. tableToAlter.exec( command );
  167. command = "CREATE index iidil_index ON ingredient_list(ingredient_id)";
  168. tableToAlter.exec( command );
  169. // Port data
  170. //*1:: Recipes have always category -1 to speed up searches (no JOINs needed)
  171. command = "SELECT r.id FROM recipes r;"; // Find all recipes
  172. TQSqlQuery categoryToAdd( TQString::null, database );
  173. tableToAlter.exec( command );
  174. if ( tableToAlter.isActive() )
  175. {
  176. while ( tableToAlter.next() ) {
  177. int recipeId = tableToAlter.value( 0 ).toInt();
  178. TQString cCommand = TQString( "INSERT INTO category_list VALUES (%1,-1);" ).arg( recipeId );
  179. categoryToAdd.exec( cCommand );
  180. emit progress();
  181. }
  182. }
  183. // Set the version to the new one (0.4)
  184. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  185. tableToAlter.exec( command );
  186. command = "INSERT INTO db_info VALUES(0.4,'Krecipes 0.4');"; // Set the new version
  187. tableToAlter.exec( command );
  188. }
  189. if ( tqRound(version*10) < 5 ) {
  190. command = TQString( "CREATE TABLE prep_methods (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(%1), PRIMARY KEY (id));" ).arg( maxPrepMethodNameLength() );
  191. TQSqlQuery tableToAlter( command, database );
  192. command = "ALTER TABLE ingredient_list ADD COLUMN prep_method_id int(11) AFTER unit_id;";
  193. tableToAlter.exec( command );
  194. command = "UPDATE ingredient_list SET prep_method_id=-1 WHERE prep_method_id IS NULL;";
  195. tableToAlter.exec( command );
  196. command = "ALTER TABLE authors MODIFY name VARCHAR(50);";
  197. tableToAlter.exec( command );
  198. command = "ALTER TABLE categories MODIFY name VARCHAR(40);";
  199. tableToAlter.exec( command );
  200. // Set the version to the new one (0.5)
  201. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  202. tableToAlter.exec( command );
  203. command = "INSERT INTO db_info VALUES(0.5,'Krecipes 0.5');";
  204. tableToAlter.exec( command );
  205. }
  206. if ( tqRound(version*10) < 6 ) {
  207. command = "ALTER TABLE categories ADD COLUMN parent_id int(11) NOT NULL default '-1' AFTER name;";
  208. TQSqlQuery tableToAlter( command, database );
  209. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  210. tableToAlter.exec( command );
  211. command = "INSERT INTO db_info VALUES(0.6,'Krecipes 0.6');";
  212. tableToAlter.exec( command );
  213. }
  214. if ( tqRound(version*100) < 61 ) {
  215. TQString command = "ALTER TABLE `recipes` ADD COLUMN `prep_time` TIME DEFAULT NULL";
  216. TQSqlQuery tableToAlter( command, database );
  217. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  218. tableToAlter.exec( command );
  219. command = "INSERT INTO db_info VALUES(0.61,'Krecipes 0.6');";
  220. tableToAlter.exec( command );
  221. }
  222. if ( tqRound(version*100) < 62 ) {
  223. TQString command = "ALTER TABLE `ingredient_list` ADD COLUMN `group_id` int(11) default '-1' AFTER order_index;";
  224. TQSqlQuery tableToAlter( command, database );
  225. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  226. tableToAlter.exec( command );
  227. command = "INSERT INTO db_info VALUES(0.62,'Krecipes 0.7');";
  228. tableToAlter.exec( command );
  229. }
  230. if ( tqRound(version*100) < 63 ) {
  231. TQString command = "ALTER TABLE `units` ADD COLUMN `plural` varchar(20) DEFAULT NULL AFTER name;";
  232. TQSqlQuery tableToAlter( command, database );
  233. TQSqlQuery result( "SELECT id,name FROM units WHERE plural IS NULL", database );
  234. if ( result.isActive() ) {
  235. while ( result.next() ) {
  236. command = "UPDATE units SET plural='" + result.value( 1 ).toString() + "' WHERE id=" + TQString::number( result.value( 0 ).toInt() );
  237. TQSqlQuery query( command, database );
  238. emit progress();
  239. }
  240. }
  241. command = "DELETE FROM db_info;"; // Remove previous version records if they exist
  242. tableToAlter.exec( command );
  243. command = "INSERT INTO db_info VALUES(0.63,'Krecipes 0.7');";
  244. tableToAlter.exec( command );
  245. }
  246. if ( tqRound(version*10) < 7 ) { //simply call 0.63 -> 0.7
  247. TQString command = "UPDATE db_info SET ver='0.7';";
  248. TQSqlQuery query( command, database );
  249. }
  250. if ( tqRound(version*100) < 81 ) {
  251. TQString command = "ALTER TABLE `ingredient_list` ADD COLUMN `amount_offset` FLOAT DEFAULT '0' AFTER amount;";
  252. TQSqlQuery tableToAlter( command, database );
  253. command = "UPDATE db_info SET ver='0.81',generated_by='Krecipes SVN (20050816)';";
  254. tableToAlter.exec( command );
  255. }
  256. if ( tqRound(version*100) < 82 ) {
  257. TQString command = "ALTER TABLE `recipes` ADD COLUMN `yield_amount` FLOAT DEFAULT '0' AFTER persons;";
  258. TQSqlQuery tableToAlter( command, database );
  259. command = "ALTER TABLE `recipes` ADD COLUMN `yield_amount_offset` FLOAT DEFAULT '0' AFTER yield_amount;";
  260. tableToAlter.exec(command);
  261. command = "ALTER TABLE `recipes` ADD COLUMN `yield_type_id` INTEGER DEFAULT '-1' AFTER yield_amount_offset;";
  262. tableToAlter.exec(command);
  263. TQSqlQuery result( "SELECT id,persons FROM recipes", database );
  264. if ( result.isActive() ) {
  265. while ( result.next() ) {
  266. command = "UPDATE recipes SET yield_amount='" + TQString::number( result.value( 1 ).toInt() ) + "' WHERE id=" + TQString::number( result.value( 0 ).toInt() );
  267. TQSqlQuery query( command, database );
  268. emit progress();
  269. }
  270. }
  271. command = "ALTER TABLE `recipes` DROP COLUMN `persons`;";
  272. tableToAlter.exec( command );
  273. command = "UPDATE db_info SET ver='0.82',generated_by='Krecipes SVN (20050902)';";
  274. tableToAlter.exec( command );
  275. }
  276. if ( tqRound(version*100) < 83 ) {
  277. database->transaction();
  278. //====add a id columns to 'ingredient_list' to identify it for the prep method list
  279. database->exec( "RENAME TABLE ingredient_list TO ingredient_list_copy;" );
  280. database->exec( "CREATE TABLE ingredient_list (id INTEGER NOT NULL AUTO_INCREMENT, recipe_id INTEGER, ingredient_id INTEGER, amount FLOAT, amount_offset FLOAT, unit_id INTEGER, order_index INTEGER, group_id INTEGER, PRIMARY KEY(id), INDEX ridil_index(recipe_id), INDEX iidil_index(ingredient_id));" );
  281. TQSqlQuery copyQuery = database->exec( "SELECT recipe_id,ingredient_id,amount,amount_offset,unit_id,prep_method_id,order_index,group_id FROM ingredient_list_copy" );
  282. if ( copyQuery.isActive() ) {
  283. while ( copyQuery.next() ) {
  284. TQSqlQuery query(TQString::null,database);
  285. query.prepare( "INSERT INTO ingredient_list VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)" );
  286. query.addBindValue( copyQuery.value( 0 ) );
  287. query.addBindValue( copyQuery.value( 1 ) );
  288. query.addBindValue( copyQuery.value( 2 ) );
  289. query.addBindValue( copyQuery.value( 3 ) );
  290. query.addBindValue( copyQuery.value( 4 ) );
  291. query.addBindValue( copyQuery.value( 6 ) );
  292. query.addBindValue( copyQuery.value( 7 ) );
  293. query.exec();
  294. int prep_method_id = copyQuery.value( 5 ).toInt();
  295. if ( prep_method_id != -1 ) {
  296. query.prepare( "INSERT INTO prep_method_list VALUES (?, ?, ?);" );
  297. query.addBindValue( lastInsertID() );
  298. query.addBindValue( prep_method_id );
  299. query.addBindValue( 1 );
  300. query.exec();
  301. }
  302. emit progress();
  303. }
  304. }
  305. database->exec( "DROP TABLE ingredient_list_copy" );
  306. database->exec( "UPDATE db_info SET ver='0.83',generated_by='Krecipes SVN (20050909)';" );
  307. if ( !database->commit() )
  308. kdDebug()<<"Update to 0.83 failed. Maybe you should try again."<<endl;
  309. }
  310. if ( tqRound(version*100) < 84 ) {
  311. database->transaction();
  312. database->exec( "ALTER TABLE recipes ADD COLUMN ctime TIMESTAMP;" );
  313. database->exec( "ALTER TABLE recipes ADD COLUMN mtime TIMESTAMP;" );
  314. database->exec( "ALTER TABLE recipes ADD COLUMN atime TIMESTAMP;" );
  315. database->exec( "UPDATE recipes SET ctime=CURRENT_TIMESTAMP, mtime=CURRENT_TIMESTAMP, atime=CURRENT_TIMESTAMP;" );
  316. database->exec( "UPDATE db_info SET ver='0.84',generated_by='Krecipes SVN (20050913)';" );
  317. if ( !database->commit() )
  318. kdDebug()<<"Update to 0.84 failed. Maybe you should try again."<<endl;
  319. }
  320. if ( tqRound(version*100) < 85 ) {
  321. database->transaction();
  322. TQSqlQuery query( "SELECT id,photo FROM recipes", database );
  323. if ( query.isActive() ) {
  324. while ( query.next() ) {
  325. storePhoto( query.value(0).toInt(), query.value(1).toByteArray() );
  326. emit progress();
  327. }
  328. }
  329. database->exec( "UPDATE db_info SET ver='0.85',generated_by='Krecipes SVN (20050926)';" );
  330. if ( !database->commit() )
  331. kdDebug()<<"Update to 0.85 failed. Maybe you should try again."<<endl;
  332. }
  333. if ( tqRound(version*100) < 86 ) {
  334. database->transaction();
  335. database->exec( "ALTER TABLE ingredient_list ADD INDEX (group_id)" );
  336. TQSqlQuery query( "SELECT id,name FROM ingredient_groups ORDER BY name", database );
  337. TQString last;
  338. int lastID;
  339. if ( query.isActive() ) {
  340. while ( query.next() ) {
  341. TQString name = query.value(1).toString();
  342. int id = query.value(0).toInt();
  343. if ( last == name ) {
  344. TQString command = TQString("UPDATE ingredient_list SET group_id=%1 WHERE group_id=%2").arg(lastID).arg(id);
  345. database->exec(command);
  346. command = TQString("DELETE FROM ingredient_groups WHERE id=%1").arg(id);
  347. database->exec(command);
  348. }
  349. last = name;
  350. lastID = id;
  351. emit progress();
  352. }
  353. }
  354. database->exec( "UPDATE db_info SET ver='0.86',generated_by='Krecipes SVN (20050928)';" );
  355. if ( !database->commit() )
  356. kdDebug()<<"Update to 0.86 failed. Maybe you should try again."<<endl;
  357. }
  358. if ( tqRound(version*100) < 87 ) {
  359. //Load this default data so the user knows what rating criteria is
  360. database->exec( TQString("INSERT INTO rating_criteria VALUES (1,'%1')").arg(i18n("Overall")) );
  361. database->exec( TQString("INSERT INTO rating_criteria VALUES (2,'%1')").arg(i18n("Taste") ) );
  362. database->exec( TQString("INSERT INTO rating_criteria VALUES (3,'%1')").arg(i18n("Appearance") ) );
  363. database->exec( TQString("INSERT INTO rating_criteria VALUES (4,'%1')").arg(i18n("Originality") ) );
  364. database->exec( TQString("INSERT INTO rating_criteria VALUES (5,'%1')").arg(i18n("Ease of Preparation") ) );
  365. database->exec( "UPDATE db_info SET ver='0.87',generated_by='Krecipes SVN (20051014)'" );
  366. }
  367. if ( tqRound(version*100) < 90 ) {
  368. database->exec("UPDATE db_info SET ver='0.9',generated_by='Krecipes 0.9'");
  369. }
  370. if ( tqRound(version*100) < 91 ) {
  371. database->exec("CREATE index parent_id_index ON categories(parent_id)");
  372. database->exec("UPDATE db_info SET ver='0.91',generated_by='Krecipes SVN (20060526)'");
  373. }
  374. if ( tqRound(version*100) < 92 ) {
  375. database->transaction();
  376. database->exec( "ALTER TABLE units ADD COLUMN name_abbrev VARCHAR(20) AFTER name");
  377. database->exec( "ALTER TABLE units ADD COLUMN plural_abbrev VARCHAR(20) AFTER plural");
  378. database->exec("UPDATE db_info SET ver='0.92',generated_by='Krecipes SVN (20060609)'");
  379. if ( !database->commit() )
  380. kdDebug()<<"Update to 0.92 failed. Maybe you should try again."<<endl;
  381. }
  382. if ( tqRound(version*100) < 93 ) {
  383. database->transaction();
  384. database->exec( "ALTER TABLE ingredient_list ADD COLUMN substitute_for INTEGER AFTER group_id");
  385. database->exec("UPDATE db_info SET ver='0.93',generated_by='Krecipes SVN (20060615)'");
  386. if ( !database->commit() )
  387. kdDebug()<<"Update to 0.93 failed. Maybe you should try again."<<endl;
  388. }
  389. if ( tqRound(version*100) < 94 ) {
  390. database->transaction();
  391. database->exec( "ALTER TABLE units ADD COLUMN type INTEGER NOT NULL DEFAULT 0 AFTER plural_abbrev");
  392. database->exec("UPDATE db_info SET ver='0.94',generated_by='Krecipes SVN (20060712)'");
  393. if ( !database->commit() )
  394. kdDebug()<<"Update to 0.94 failed. Maybe you should try again."<<endl;
  395. }
  396. if ( tqRound(version*100) < 95 ) {
  397. database->exec( "DROP TABLE ingredient_weights" );
  398. createTable( "ingredient_weights" );
  399. database->exec( "UPDATE db_info SET ver='0.95',generated_by='Krecipes SVN (20060726)'" );
  400. }
  401. }
  402. int MySQLRecipeDB::lastInsertID()
  403. {
  404. TQSqlQuery lastInsertID( "SELECT LAST_INSERT_ID();", database );
  405. int id = -1;
  406. if ( lastInsertID.isActive() && lastInsertID.next() )
  407. id = lastInsertID.value( 0 ).toInt();
  408. return id;
  409. }
  410. void MySQLRecipeDB::givePermissions( const TQString &dbName, const TQString &username, const TQString &password, const TQString &clientHost )
  411. {
  412. TQString command;
  413. if ( !password.isEmpty() )
  414. command = TQString( "GRANT ALL ON %1.* TO '%2'@'%3' IDENTIFIED BY '%4';" ).arg( dbName ).arg( username ).arg( clientHost ).arg( password );
  415. else
  416. command = TQString( "GRANT ALL ON %1.* TO '%2'@'%3';" ).arg( dbName ).arg( username ).arg( clientHost );
  417. kdDebug() << "I'm doing the query to setup permissions\n";
  418. TQSqlQuery permissionsToSet( command, database );
  419. }
  420. #include "mysqlrecipedb.moc"