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.
 
 
 
 
 

561 lines
23 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 "psqlrecipedb.h"
  15. #include <kdebug.h>
  16. #include <kstandarddirs.h>
  17. #include <tdetempfile.h>
  18. #include <tdelocale.h>
  19. #include <tdeconfig.h>
  20. #include <ntqvariant.h>
  21. //Note: PostgreSQL's database names are always lowercase
  22. PSqlRecipeDB::PSqlRecipeDB( const TQString& host, const TQString& user, const TQString& pass, const TQString& DBname, int port ) : TQSqlRecipeDB( host, user, pass, DBname.lower(), port )
  23. {}
  24. PSqlRecipeDB::~PSqlRecipeDB()
  25. {}
  26. void PSqlRecipeDB::createDB()
  27. {
  28. TQString real_db_name = database->databaseName();
  29. //we have to be connected to some database in order to create the Krecipes database
  30. //so long as the permissions given are allowed access to "template1', this works
  31. database->setDatabaseName( "template1" );
  32. if ( database->open() ) {
  33. TQSqlQuery query( TQString( "CREATE DATABASE %1" ).arg( real_db_name ), database );
  34. if ( !query.isActive() )
  35. kdDebug() << "create query failed: " << database->lastError().databaseText() << endl;
  36. database->close();
  37. }
  38. else
  39. kdDebug() << "create open failed: " << database->lastError().databaseText() << endl;
  40. database->setDatabaseName( real_db_name );
  41. }
  42. TQStringList PSqlRecipeDB::backupCommand() const
  43. {
  44. TDEConfig *config = TDEGlobal::config();
  45. config->setGroup("Server");
  46. TQStringList command;
  47. command<<config->readEntry( "PgDumpPath", "pg_dump" )<<"-d"<<database->databaseName()
  48. <<"-U"<<config->readEntry( "Username" );
  49. int port = config->readNumEntry( "Port", 0 );
  50. if ( port > 0 )
  51. command<<"-p"<<TQString::number(port);
  52. return command;
  53. }
  54. TQStringList PSqlRecipeDB::restoreCommand() const
  55. {
  56. TDEConfig *config = TDEGlobal::config();
  57. config->setGroup("Server");
  58. TQStringList command;
  59. command<<config->readEntry( "PsqlPath", "psql" )<<database->databaseName()
  60. <<"-U"<<config->readEntry( "Username" );
  61. int port = config->readNumEntry( "Port", 0 );
  62. if ( port > 0 )
  63. command<<"-p"<<TQString::number(port);
  64. return command;
  65. }
  66. void PSqlRecipeDB::createTable( const TQString &tableName )
  67. {
  68. TQStringList commands;
  69. if ( tableName == "recipes" )
  70. commands << "CREATE TABLE recipes (id SERIAL NOT NULL PRIMARY KEY,title CHARACTER VARYING, yield_amount FLOAT, yield_amount_offset FLOAT, yield_type_id INTEGER DEFAULT '-1', instructions TEXT, photo TEXT, prep_time TIME, ctime TIMESTAMP, mtime TIMESTAMP, atime TIMESTAMP );";
  71. else if ( tableName == "ingredients" )
  72. commands << "CREATE TABLE ingredients (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING);";
  73. else if ( tableName == "ingredient_list" ) {
  74. commands << "CREATE TABLE ingredient_list (id SERIAL NOT NULL PRIMARY KEY, recipe_id INTEGER, ingredient_id INTEGER, amount FLOAT, amount_offset FLOAT, unit_id INTEGER, order_index INTEGER, group_id INTEGER, substitute_for INTEGER);";
  75. commands << "CREATE INDEX ridil_index ON ingredient_list USING BTREE (recipe_id);";
  76. commands << "CREATE INDEX iidil_index ON ingredient_list USING BTREE (ingredient_id);";
  77. commands << "CREATE INDEX gidil_index ON ingredient_list USING BTREE (group_id);";
  78. }
  79. else if ( tableName == "unit_list" )
  80. commands << "CREATE TABLE unit_list (ingredient_id INTEGER, unit_id INTEGER);";
  81. else if ( tableName == "units" )
  82. commands << "CREATE TABLE units (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING, name_abbrev CHARACTER VARYING, plural CHARACTER VARYING, plural_abbrev CHARACTER VARYING, type INTEGER NOT NULL DEFAULT '0' );";
  83. else if ( tableName == "prep_methods" )
  84. commands << "CREATE TABLE prep_methods (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING);";
  85. else if ( tableName == "prep_method_list" ) {
  86. commands << "CREATE TABLE prep_method_list (ingredient_list_id INTEGER NOT NULL,prep_method_id INTEGER NOT NULL, order_index INTEGER);";
  87. commands << "CREATE INDEX iid_index ON prep_method_list USING BTREE (ingredient_list_id);";
  88. commands << "CREATE INDEX pid_index ON prep_method_list USING BTREE (prep_method_id);";
  89. }
  90. else if ( tableName == "ingredient_info" )
  91. commands << "CREATE TABLE ingredient_info (ingredient_id INTEGER, property_id INTEGER, amount FLOAT, per_units INTEGER);";
  92. else if ( tableName == "ingredient_properties" )
  93. commands << "CREATE TABLE ingredient_properties (id SERIAL NOT NULL,name CHARACTER VARYING, units CHARACTER VARYING);";
  94. else if ( tableName == "ingredient_weights" ) {
  95. commands << "CREATE TABLE ingredient_weights (id SERIAL NOT NULL PRIMARY KEY, ingredient_id INTEGER NOT NULL, amount FLOAT, unit_id INTEGER, weight FLOAT, weight_unit_id INTEGER, prep_method_id INTEGER );"
  96. << "CREATE INDEX weight_wid_index ON ingredient_weights USING BTREE (weight_unit_id)"
  97. << "CREATE INDEX weight_pid_index ON ingredient_weights USING BTREE (prep_method_id)"
  98. << "CREATE INDEX weight_uid_index ON ingredient_weights USING BTREE (unit_id)"
  99. << "CREATE INDEX weight_iid_index ON ingredient_weights USING BTREE (ingredient_id)";
  100. }
  101. else if ( tableName == "units_conversion" )
  102. commands << "CREATE TABLE units_conversion (unit1_id INTEGER, unit2_id INTEGER, ratio FLOAT);";
  103. else if ( tableName == "categories" ) {
  104. commands << "CREATE TABLE categories (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING default NULL, parent_id INTEGER NOT NULL default -1);";
  105. commands << "CREATE index parent_id_index ON categories USING BTREE(parent_id);";
  106. }
  107. else if ( tableName == "category_list" ) {
  108. commands << "CREATE TABLE category_list (recipe_id INTEGER NOT NULL,category_id INTEGER NOT NULL);";
  109. commands << "CREATE INDEX rid_index ON category_list USING BTREE (recipe_id);";
  110. commands << "CREATE INDEX cid_index ON category_list USING BTREE (category_id);";
  111. }
  112. else if ( tableName == "authors" )
  113. commands << "CREATE TABLE authors (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING default NULL);";
  114. else if ( tableName == "author_list" )
  115. commands << "CREATE TABLE author_list (recipe_id INTEGER NOT NULL,author_id INTEGER NOT NULL);";
  116. else if ( tableName == "db_info" ) {
  117. commands << "CREATE TABLE db_info (ver FLOAT NOT NULL,generated_by CHARACTER VARYING default NULL);";
  118. commands << TQString( "INSERT INTO db_info VALUES(%1,'Krecipes %2');" ).arg( latestDBVersion() ).arg( krecipes_version() );
  119. }
  120. else if ( tableName == "ingredient_groups" ) {
  121. commands << "CREATE TABLE ingredient_groups (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING);";
  122. }
  123. else if ( tableName == "yield_types" ) {
  124. commands << "CREATE TABLE yield_types (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING);";
  125. }
  126. else if ( tableName == "ratings" )
  127. commands << "CREATE TABLE ratings (id SERIAL NOT NULL PRIMARY KEY, recipe_id INTEGER NOT NULL, comment CHARACTER VARYING, rater CHARACTER VARYING, created TIMESTAMP);";
  128. else if ( tableName == "rating_criteria" )
  129. commands << "CREATE TABLE rating_criteria (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING);";
  130. else if ( tableName == "rating_criterion_list" )
  131. commands << "CREATE TABLE rating_criterion_list (rating_id INTEGER NOT NULL, rating_criterion_id INTEGER, stars FLOAT);";
  132. else
  133. return ;
  134. TQSqlQuery databaseToCreate( TQString::null, database );
  135. // execute the queries
  136. for ( TQStringList::const_iterator it = commands.begin(); it != commands.end(); ++it )
  137. databaseToCreate.exec( *it );
  138. }
  139. void PSqlRecipeDB::initializeData()
  140. {
  141. TQSqlRecipeDB::initializeData();
  142. TQSqlQuery updateSeq( "SELECT setval('units_id_seq',(SELECT COUNT(1) FROM units))", database );
  143. updateSeq.exec( "SELECT setval('categories_id_seq',(SELECT COUNT(1) FROM categories))" );
  144. }
  145. void PSqlRecipeDB::portOldDatabases( float version )
  146. {
  147. kdDebug() << "Current database version is..." << version << "\n";
  148. TQString command;
  149. if ( tqRound(version*10) < 7 ) {
  150. //version added
  151. }
  152. if ( tqRound(version*100) < 81 ) {
  153. database->transaction();
  154. addColumn("CREATE TABLE %1 (recipe_id INTEGER, ingredient_id INTEGER, amount FLOAT, %2 unit_id INTEGER, prep_method_id INTEGER, order_index INTEGER, group_id INTEGER);","amount_offset FLOAT","'0'","ingredient_list",3);
  155. TQSqlQuery query(TQString::null,database);
  156. query.exec( "CREATE INDEX ridil_index ON ingredient_list USING BTREE (recipe_id);" );
  157. query.exec( "CREATE INDEX iidil_index ON ingredient_list USING BTREE (ingredient_id);");
  158. query.exec( "UPDATE db_info SET ver='0.81',generated_by='Krecipes SVN (20050816)';" );
  159. if ( !database->commit() )
  160. kdDebug()<<"Update to 0.81 failed. Maybe you should try again."<<endl;
  161. }
  162. if ( tqRound(version*100) < 82 ) {
  163. database->transaction();
  164. //==================add a columns to 'recipes' to allow yield range + yield type
  165. database->exec( "CREATE TABLE recipes_copy (id SERIAL NOT NULL PRIMARY KEY,title CHARACTER VARYING, persons INTEGER, instructions TEXT, photo TEXT, prep_time TIME);" );
  166. TQSqlQuery copyQuery = database->exec( "SELECT id,title,persons,instructions,photo,prep_time FROM recipes;" );
  167. if ( copyQuery.isActive() ) {
  168. while ( copyQuery.next() ) {
  169. TQSqlQuery query(TQString::null,database);
  170. query.prepare( "INSERT INTO recipes_copy VALUES (?, ?, ?, ?, ?, ?)" );
  171. query.addBindValue( copyQuery.value( 0 ) );
  172. query.addBindValue( copyQuery.value( 1 ) );
  173. query.addBindValue( copyQuery.value( 2 ) );
  174. query.addBindValue( copyQuery.value( 3 ) );
  175. query.addBindValue( copyQuery.value( 4 ) );
  176. query.addBindValue( copyQuery.value( 5 ) );
  177. query.exec();
  178. emit progress();
  179. }
  180. }
  181. database->exec( "DROP TABLE recipes" );
  182. database->exec( "CREATE TABLE recipes (id SERIAL NOT NULL PRIMARY KEY,title CHARACTER VARYING, yield_amount FLOAT, yield_amount_offset FLOAT, yield_type_id INTEGER DEFAULT '-1', instructions TEXT, photo TEXT, prep_time TIME);" );
  183. copyQuery = database->exec( "SELECT id,title,persons,instructions,photo,prep_time FROM recipes_copy" );
  184. if ( copyQuery.isActive() ) {
  185. while ( copyQuery.next() ) {
  186. TQSqlQuery query(TQString::null,database);
  187. query.prepare( "INSERT INTO recipes VALUES (?, ?, ?, ?, ?, ?, ?, ?)" );
  188. query.addBindValue( copyQuery.value( 0 ) ); //id
  189. query.addBindValue( copyQuery.value( 1 ) ); //title
  190. query.addBindValue( copyQuery.value( 2 ) ); //persons, now yield_amount
  191. query.addBindValue( 0 ); //yield_amount_offset
  192. query.addBindValue( -1 ); //yield_type_id
  193. query.addBindValue( copyQuery.value( 3 ) ); //instructions
  194. query.addBindValue( copyQuery.value( 4 ) ); //photo
  195. query.addBindValue( copyQuery.value( 5 ) ); //prep_time
  196. query.exec();
  197. emit progress();
  198. }
  199. }
  200. database->exec( "DROP TABLE recipes_copy" );
  201. database->exec( "UPDATE db_info SET ver='0.82',generated_by='Krecipes SVN (20050902)';" );
  202. if ( !database->commit() )
  203. kdDebug()<<"Update to 0.82 failed. Maybe you should try again."<<endl;
  204. }
  205. if ( tqRound(version*100) < 83 ) {
  206. database->transaction();
  207. //====add a id columns to 'ingredient_list' to identify it for the prep method list
  208. database->exec( "ALTER TABLE ingredient_list RENAME TO ingredient_list_copy;" );
  209. database->exec( "CREATE TABLE ingredient_list (id SERIAL NOT NULL PRIMARY KEY, recipe_id INTEGER, ingredient_id INTEGER, amount FLOAT, amount_offset FLOAT, unit_id INTEGER, order_index INTEGER, group_id INTEGER);" );
  210. 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" );
  211. if ( copyQuery.isActive() ) {
  212. while ( copyQuery.next() ) {
  213. int ing_list_id = getNextInsertID("ingredient_list","id");
  214. TQSqlQuery query(TQString::null,database);
  215. query.prepare( "INSERT INTO ingredient_list VALUES (?, ?, ?, ?, ?, ?, ?, ?)" );
  216. query.addBindValue( ing_list_id );
  217. query.addBindValue( copyQuery.value( 0 ) );
  218. query.addBindValue( copyQuery.value( 1 ) );
  219. query.addBindValue( copyQuery.value( 2 ) );
  220. query.addBindValue( copyQuery.value( 3 ) );
  221. query.addBindValue( copyQuery.value( 4 ) );
  222. query.addBindValue( copyQuery.value( 6 ) );
  223. query.addBindValue( copyQuery.value( 7 ) );
  224. query.exec();
  225. int prep_method_id = copyQuery.value( 5 ).toInt();
  226. if ( prep_method_id != -1 ) {
  227. query.prepare( "INSERT INTO prep_method_list VALUES (?, ?, ?);" );
  228. query.addBindValue( ing_list_id );
  229. query.addBindValue( prep_method_id );
  230. query.addBindValue( 1 );
  231. query.exec();
  232. }
  233. emit progress();
  234. }
  235. }
  236. database->exec( "DROP TABLE ingredient_list_copy" );
  237. database->exec( "CREATE INDEX ridil_index ON ingredient_list USING BTREE (recipe_id);" );
  238. database->exec( "CREATE INDEX iidil_index ON ingredient_list USING BTREE (ingredient_id);" );
  239. database->exec( "UPDATE db_info SET ver='0.83',generated_by='Krecipes SVN (20050909)';" );
  240. if ( !database->commit() ) {
  241. kdDebug()<<"Update to 0.83 failed. Maybe you should try again."<<endl;
  242. return;
  243. }
  244. }
  245. if ( tqRound(version*100) < 84 ) {
  246. database->transaction();
  247. database->exec( "ALTER TABLE recipes ADD COLUMN ctime TIMESTAMP" );
  248. database->exec( "ALTER TABLE recipes ADD COLUMN mtime TIMESTAMP" );
  249. database->exec( "ALTER TABLE recipes ADD COLUMN atime TIMESTAMP" );
  250. database->exec( "UPDATE recipes SET ctime=CURRENT_TIMESTAMP, mtime=CURRENT_TIMESTAMP, atime=CURRENT_TIMESTAMP;" );
  251. database->exec( "UPDATE db_info SET ver='0.84',generated_by='Krecipes SVN (20050913)';" );
  252. if ( !database->commit() ) {
  253. kdDebug()<<"Update to 0.84 failed. Maybe you should try again."<<endl;
  254. return;
  255. }
  256. }
  257. if ( tqRound(version*100) < 85 ) { //this change altered the photo format, but this backend already used the newer format
  258. database->transaction();
  259. database->exec( "UPDATE db_info SET ver='0.85',generated_by='Krecipes SVN (20050926)';" );
  260. if ( !database->commit() ) {
  261. kdDebug()<<"Update to 0.85 failed. Maybe you should try again."<<endl;
  262. return;
  263. }
  264. }
  265. if ( tqRound(version*100) < 86 ) {
  266. database->transaction();
  267. database->exec( "CREATE INDEX gidil_index ON ingredient_list USING BTREE (group_id);" );
  268. TQSqlQuery query( "SELECT id,name FROM ingredient_groups ORDER BY name", database );
  269. TQString last;
  270. int lastID;
  271. if ( query.isActive() ) {
  272. while ( query.next() ) {
  273. TQString name = query.value(1).toString();
  274. int id = query.value(0).toInt();
  275. if ( last == name ) {
  276. TQString command = TQString("UPDATE ingredient_list SET group_id=%1 WHERE group_id=%2").arg(lastID).arg(id);
  277. database->exec(command);
  278. command = TQString("DELETE FROM ingredient_groups WHERE id=%1").arg(id);
  279. database->exec(command);
  280. }
  281. last = name;
  282. lastID = id;
  283. emit progress();
  284. }
  285. }
  286. database->exec( "UPDATE db_info SET ver='0.86',generated_by='Krecipes SVN (20050928)';" );
  287. if ( !database->commit() )
  288. kdDebug()<<"Update to 0.86 failed. Maybe you should try again."<<endl;
  289. }
  290. if ( tqRound(version*100) < 87 ) {
  291. //Load this default data so the user knows what rating criteria is
  292. database->exec( TQString("INSERT INTO rating_criteria VALUES (1,'%1')").arg(i18n("Overall")) );
  293. database->exec( TQString("INSERT INTO rating_criteria VALUES (2,'%1')").arg(i18n("Taste") ) );
  294. database->exec( TQString("INSERT INTO rating_criteria VALUES (3,'%1')").arg(i18n("Appearance") ) );
  295. database->exec( TQString("INSERT INTO rating_criteria VALUES (4,'%1')").arg(i18n("Originality") ) );
  296. database->exec( TQString("INSERT INTO rating_criteria VALUES (5,'%1')").arg(i18n("Ease of Preparation") ) );
  297. database->exec( "UPDATE db_info SET ver='0.87',generated_by='Krecipes SVN (20051014)'" );
  298. }
  299. if ( tqRound(version*100) < 90 ) {
  300. database->exec("UPDATE db_info SET ver='0.9',generated_by='Krecipes 0.9'");
  301. }
  302. if ( tqRound(version*100) < 91 ) {
  303. database->exec("CREATE index parent_id_index ON categories USING BTREE(parent_id)");
  304. database->exec("UPDATE db_info SET ver='0.91',generated_by='Krecipes SVN (20060526)'");
  305. }
  306. if ( tqRound(version*100) < 92 ) {
  307. database->transaction();
  308. //==================add a columns to 'units' to allow unit abbreviations
  309. database->exec( "ALTER TABLE units RENAME TO units_copy" );
  310. int nextval = -1;
  311. TQSqlQuery getID( "SELECT nextval('units_id_seq')", database );
  312. if ( getID.isActive() && getID.first() )
  313. nextval = getID.value( 0 ).toInt();
  314. if ( nextval == -1 )
  315. kdDebug() << "Database update failed! Unable to update units sequence." << endl;
  316. database->exec( "CREATE TABLE units (id SERIAL NOT NULL PRIMARY KEY, name CHARACTER VARYING, name_abbrev CHARACTER VARYING, plural CHARACTER VARYING, plural_abbrev CHARACTER VARYING )" );
  317. TQSqlQuery copyQuery = database->exec( "SELECT id,name,plural FROM units_copy" );
  318. if ( copyQuery.isActive() ) {
  319. while ( copyQuery.next() ) {
  320. TQSqlQuery query(TQString::null,database);
  321. query.prepare( "INSERT INTO units VALUES(?, ?, ?, ?, ?)" );
  322. query.addBindValue( copyQuery.value( 0 ) );
  323. query.addBindValue( copyQuery.value( 1 ) );
  324. query.addBindValue( TQVariant() );
  325. query.addBindValue( copyQuery.value( 2 ) );
  326. query.addBindValue( TQVariant() );
  327. query.exec();
  328. emit progress();
  329. }
  330. }
  331. database->exec( "DROP TABLE units_copy" );
  332. database->exec( "ALTER TABLE units_id_seq1 RENAME TO units_id_seq" );
  333. database->exec( "ALTER SEQUENCE units_id_seq RESTART WITH "+TQString::number(nextval) );
  334. database->exec("UPDATE db_info SET ver='0.92',generated_by='Krecipes SVN (20060609)'");
  335. if ( !database->commit() )
  336. kdDebug()<<"Update to 0.92 failed. Maybe you should try again."<<endl;
  337. }
  338. if ( tqRound(version*100) < 93 ) {
  339. database->transaction();
  340. database->exec( "ALTER TABLE ingredient_list ADD COLUMN substitute_for INTEGER" );
  341. database->exec("UPDATE db_info SET ver='0.93',generated_by='Krecipes SVN (20060616)'");
  342. if ( !database->commit() )
  343. kdDebug()<<"Update to 0.93 failed. Maybe you should try again."<<endl;
  344. }
  345. if ( tqRound(version*100) < 94 ) {
  346. database->transaction();
  347. database->exec( "ALTER TABLE units ADD COLUMN type INTEGER NOT NULL DEFAULT '0'" );
  348. database->exec("UPDATE db_info SET ver='0.94',generated_by='Krecipes SVN (20060712)'");
  349. if ( !database->commit() )
  350. kdDebug()<<"Update to 0.94 failed. Maybe you should try again."<<endl;
  351. }
  352. if ( tqRound(version*100) < 95 ) {
  353. database->exec( "DROP TABLE ingredient_weights" );
  354. createTable( "ingredient_weights" );
  355. database->exec( "UPDATE db_info SET ver='0.95',generated_by='Krecipes SVN (20060726)'" );
  356. }
  357. }
  358. void PSqlRecipeDB::addColumn( const TQString &new_table_sql, const TQString &new_col_info, const TQString &default_value, const TQString &table_name, int col_index )
  359. {
  360. TQString command;
  361. command = TQString(new_table_sql).arg(table_name+"_copy").arg(TQString::null);
  362. kdDebug()<<"calling: "<<command<<endl;
  363. TQSqlQuery query( command, database );
  364. command = "SELECT * FROM "+table_name+";";
  365. query.exec( command );
  366. if ( query.isActive() ) {
  367. while ( query.next() ) {
  368. TQStringList dataList;
  369. for ( int i = 0 ;; ++i ) {
  370. TQVariant variant = query.value(i);
  371. if ( variant.type() == TQVariant::Invalid ) break;
  372. dataList << "'"+variant.toString()+"'";
  373. }
  374. command = "INSERT INTO "+table_name+"_copy VALUES("+dataList.join(",")+");";
  375. kdDebug()<<"calling: "<<command<<endl;
  376. TQSqlQuery insert_query( command, database );
  377. emit progress();
  378. }
  379. }
  380. query.exec( "DROP TABLE "+table_name+";" );
  381. query.exec( TQString(new_table_sql).arg(table_name).arg(new_col_info+",") );
  382. query.exec( "SELECT * FROM "+table_name+"_copy;" );
  383. if ( query.isActive() ) {
  384. while ( query.next() ) {
  385. TQStringList dataList;
  386. for ( int i = 0 ;; ++i ) {
  387. if ( i == col_index )
  388. dataList << default_value;
  389. TQVariant variant = query.value(i);
  390. if ( variant.type() == TQVariant::Invalid ) break;
  391. dataList << "'"+variant.toString()+"'";
  392. }
  393. command = "INSERT INTO "+table_name+" VALUES(" +dataList.join(",")+");";
  394. TQSqlQuery insert_query( command, database );
  395. kdDebug()<<"calling: "<<command<<endl;
  396. emit progress();
  397. }
  398. }
  399. query.exec( "DROP TABLE "+table_name+"_copy;" );
  400. }
  401. int PSqlRecipeDB::lastInsertID()
  402. {
  403. return last_insert_id;
  404. }
  405. int PSqlRecipeDB::getNextInsertID( const TQString &table, const TQString &column )
  406. {
  407. TQString command = TQString( "SELECT nextval('%1_%2_seq');" ).arg( table ).arg( column );
  408. TQSqlQuery getID( command, database );
  409. if ( getID.isActive() && getID.first() ) {
  410. last_insert_id = getID.value( 0 ).toInt();
  411. }
  412. else
  413. last_insert_id = -1;
  414. return last_insert_id;
  415. }
  416. void PSqlRecipeDB::givePermissions( const TQString & /*dbName*/, const TQString &username, const TQString &password, const TQString & /*clientHost*/ )
  417. {
  418. TQStringList tables;
  419. tables << "ingredient_info" << "ingredient_list" << "ingredient_properties" << "ingredients" << "recipes" << "unit_list" << "units" << "units_conversion" << "categories" << "category_list" << "authors" << "author_list" << "prep_methods" << "db_info" << "ingredient_groups" << "ingredient_weights" << "prep_method_list" << "yield_types" << "ratings" << "rating_criteria" << "rating_criterion_list";
  420. //we also have to grant permissions on the sequences created
  421. tables << "authors_id_seq" << "categories_id_seq" << "ingredient_properties_id_seq" << "ingredient_weights_id_seq" << "ingredients_id_seq" << "prep_methods_id_seq" << "recipes_id_seq" << "units_id_seq" << "ingredient_groups_id_seq" << "yield_types_id_seq" << "ingredient_list_id_seq" << "ratings_id_seq" << "rating_criteria_id_seq";
  422. TQString command;
  423. kdDebug() << "I'm doing the query to create the new user" << endl;
  424. command = "CREATE USER " + username;
  425. if ( !password.isEmpty() )
  426. command.append( "WITH PASSWORD '" + password + "'" );
  427. command.append( ";" );
  428. TQSqlQuery permissionsToSet( command, database );
  429. kdDebug() << "I'm doing the query to setup permissions\n";
  430. command = TQString( "GRANT ALL ON %1 TO %2;" ).arg( tables.join( "," ) ).arg( username );
  431. permissionsToSet.exec( command );
  432. }
  433. void PSqlRecipeDB::empty( void )
  434. {
  435. TQSqlRecipeDB::empty();
  436. TQStringList tables;
  437. tables << "authors_id_seq" << "categories_id_seq" << "ingredient_properties_id_seq" << "ingredient_weights_id_seq" << "ingredients_id_seq" << "prep_methods_id_seq" << "recipes_id_seq" << "units_id_seq" << "ingredient_groups_id_seq" << "yield_types_id_seq" << "ingredient_list_id_seq" << "prep_method_list_id_seq" << "ratings_id_seq" << "rating_criteria_id_seq";
  438. TQSqlQuery tablesToEmpty( TQString::null, database );
  439. for ( TQStringList::Iterator it = tables.begin(); it != tables.end(); ++it ) {
  440. TQString command = TQString( "DELETE FROM %1;" ).arg( *it );
  441. tablesToEmpty.exec( command );
  442. }
  443. }
  444. #include "psqlrecipedb.moc"