postgresql_database.cpp
Go to the documentation of this file.
00001 /*********************************************************************
00002  * Software License Agreement (BSD License)
00003  *
00004  *  Copyright (c) 2009, Willow Garage, Inc.
00005  *  All rights reserved.
00006  *
00007  *  Redistribution and use in source and binary forms, with or without
00008  *  modification, are permitted provided that the following conditions
00009  *  are met:
00010  *
00011  *   * Redistributions of source code must retain the above copyright
00012  *     notice, this list of conditions and the following disclaimer.
00013  *   * Redistributions in binary form must reproduce the above
00014  *     copyright notice, this list of conditions and the following
00015  *     disclaimer in the documentation and/or other materials provided
00016  *     with the distribution.
00017  *   * Neither the name of the Willow Garage nor the names of its
00018  *     contributors may be used to endorse or promote products derived
00019  *     from this software without specific prior written permission.
00020  *
00021  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
00022  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
00023  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
00024  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
00025  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
00026  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
00027  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
00028  *  LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
00029  *  CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
00030  *  LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
00031  *  ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
00032  *  POSSIBILITY OF SUCH DAMAGE.
00033  *********************************************************************/
00034 
00035 // Author(s): Matei Ciocarlie
00036 
00037 #include "database_interface/postgresql_database.h"
00038 
00039 // the header of the libpq library
00040 #include <libpq-fe.h>
00041 
00042 #include <sstream>
00043 #include <iostream>
00044 
00045 namespace database_interface {
00046 
00051 class PostgresqlDatabase::PGresultAutoPtr
00052 {
00053 private:
00054   PGresult* result_;
00055 public:
00056   PGresultAutoPtr(PGresult *ptr) : result_(ptr){}
00057   ~PGresultAutoPtr(){PQclear(result_);}
00058   void reset(PGresult *ptr){PQclear(result_); result_=ptr;}
00059   PGresult* operator * (){return result_;}
00060 };
00061 
00062 
00063 void PostgresqlDatabase::pgMDBconstruct(std::string host, std::string port, std::string user,
00064                                                  std::string password, std::string dbname )
00065 {
00066   std::string conn_info = "host=" + host + " port=" + port + 
00067     " user=" + user + " password=" + password + " dbname=" + dbname;
00068   connection_= PQconnectdb(conn_info.c_str());
00069   if (PQstatus(connection_)!=CONNECTION_OK) 
00070   {
00071     ROS_ERROR("Database connection failed with error message: %s", PQerrorMessage(connection_));
00072   }
00073 }
00074 
00075 PostgresqlDatabase::PostgresqlDatabase(const PostgresqlDatabaseConfig &config)
00076   : in_transaction_(false)
00077 {
00078   pgMDBconstruct(config.getHost(), config.getPort(), config.getUser(), 
00079                  config.getPassword(), config.getDBname());
00080 }
00081 
00082 PostgresqlDatabase::PostgresqlDatabase(std::string host, std::string port, std::string user,
00083                                                  std::string password, std::string dbname )
00084   : in_transaction_(false)
00085 {
00086   pgMDBconstruct(host, port, user, password, dbname);
00087 }
00088 
00089 PostgresqlDatabase::~PostgresqlDatabase()
00090 {
00091   PQfinish(connection_);
00092 }
00093 
00094 bool PostgresqlDatabase::isConnected() const
00095 {
00096   if (PQstatus(connection_)==CONNECTION_OK) return true;
00097   else return false;
00098 }
00099 
00101 bool PostgresqlDatabase::rollback()
00102 {
00103   PGresultAutoPtr result((PQexec(connection_,"ROLLBACK;")));
00104   if (PQresultStatus(*result) != PGRES_COMMAND_OK)
00105   {
00106     ROS_ERROR("Rollback failed");
00107     return false;
00108   }
00109   in_transaction_ = false;
00110   return true;
00111 }
00112 
00114 bool PostgresqlDatabase::begin()
00115 {
00116   if( in_transaction_ ) return true;
00117   //place a begin
00118   PGresultAutoPtr result(PQexec(connection_, "BEGIN;"));
00119   if (PQresultStatus(*result) != PGRES_COMMAND_OK)
00120   {
00121     ROS_ERROR("Database begin query failed. Error: %s", PQresultErrorMessage(*result));
00122     return false;
00123   }
00124   in_transaction_ = true;
00125   return true;
00126 }
00127 
00129 bool PostgresqlDatabase::commit()
00130 {
00131   PGresultAutoPtr result(PQexec(connection_, "COMMIT;"));
00132   if (PQresultStatus(*result) != PGRES_COMMAND_OK)
00133   {
00134     ROS_ERROR("Database commit query failed. Error: %s", PQresultErrorMessage(*result));
00135     return false;
00136   }
00137   in_transaction_ = false;
00138   return true;
00139 }
00140 
00141 bool PostgresqlDatabase::getVariable(std::string name, std::string &value) const
00142 {
00143   std::string query("SELECT variable_value FROM variable WHERE variable_name=" + name);
00144   PGresultAutoPtr result(PQexec(connection_, query.c_str()));  
00145   if (PQresultStatus(*result) != PGRES_TUPLES_OK)
00146   {
00147     ROS_ERROR("Database get variable query failed. Error: %s", PQresultErrorMessage(*result));
00148     return false;
00149   }
00150   if (PQntuples(*result)==0)
00151   {
00152     ROS_ERROR("Database get variable query failed. Variable %s not in database", name.c_str());
00153     return false;
00154   }
00155   value = PQgetvalue(*result, 0, 0);
00156   return true;
00157 }
00158 
00162 bool PostgresqlDatabase::getSequence(std::string name, std::string &value)
00163 {
00164   std::string query("SELECT * FROM currval('" + name + "');");
00165   PGresultAutoPtr result( PQexec(connection_, query.c_str()) );
00166   if (PQresultStatus(*result) != PGRES_TUPLES_OK)
00167   {
00168     ROS_ERROR("Get sequence: query failed. Error: %s", PQresultErrorMessage(*result));
00169     return false;
00170   }
00171   if (!PQntuples(*result))
00172   {
00173     ROS_ERROR("Get sequence: sequence %s not found", name.c_str());
00174     return false;
00175   }
00176   const char *id_char = PQgetvalue(*result, 0, 0);
00177   value.assign(id_char);
00178   return true;
00179 }
00180 
00190 bool PostgresqlDatabase::getListRawResult(const DBClass *example, 
00191                                                    std::vector<const DBFieldBase*> &fields, 
00192                                                    std::vector<int> &column_ids,
00193                                                    std::string where_clause,
00194                                                    boost::shared_ptr<PGresultAutoPtr> &result, int &num_tuples) const
00195 {
00196   //we cannot handle binary results in here; libpq does not support binary results
00197   //for just part of the query, so they all have to be text
00198   if(example->getPrimaryKeyField()->getType() == DBFieldBase::BINARY)
00199   {
00200     ROS_ERROR("Database get list: can not use binary primary key (%s)", 
00201               example->getPrimaryKeyField()->getName().c_str());
00202     return false;
00203   }
00204 
00205   std::string select_query;
00206   select_query += "SELECT " + example->getPrimaryKeyField()->getName() + " ";  
00207   fields.push_back(example->getPrimaryKeyField());
00208 
00209   //we will store here the list of tables we will join on
00210   std::vector<std::string> join_tables;
00211   std::string join_clauses;
00212   for (size_t i=0; i<example->getNumFields(); i++)
00213   {
00214     if (!example->getField(i)->getReadFromDatabase()) continue;
00215 
00216     if (example->getField(i)->getType()==DBFieldBase::BINARY)
00217     {
00218       ROS_WARN("Database get list: binary field (%s) can not be loaded by default", 
00219                example->getField(i)->getName().c_str());
00220       continue;
00221     }
00222 
00223     select_query += ", " + example->getField(i)->getName();
00224     fields.push_back(example->getField(i));
00225     if ( example->getField(i)->getTableName() != example->getPrimaryKeyField()->getTableName() )
00226     {
00227       //check if we are already joining on this table
00228       bool already_join = false;
00229       for (size_t j=0; j<join_tables.size() && !already_join; j++)
00230       {
00231         if (join_tables[j] == example->getField(i)->getTableName()) already_join = true;
00232       }
00233       
00234       if (!already_join)
00235       {
00236         const DBFieldBase *foreign_key = NULL;
00237         if (!example->getForeignKey(example->getField(i)->getTableName(), foreign_key))
00238         {
00239           ROS_ERROR("Database get list: could not find foreign key for table %s", 
00240                     example->getField(i)->getTableName().c_str());
00241           return false;
00242         }
00243         join_clauses += " JOIN " + example->getField(i)->getTableName() + " USING (" 
00244           + foreign_key->getName() + ") ";
00245         join_tables.push_back( example->getField(i)->getTableName() );
00246       }
00247     }
00248   }
00249 
00250   select_query += " FROM " + example->getPrimaryKeyField()->getTableName() + " ";
00251 
00252   if (!join_clauses.empty())
00253   {
00254     select_query += join_clauses;
00255   }
00256 
00257   if (!where_clause.empty())
00258   {
00259     select_query += " WHERE " + where_clause;
00260   }
00261 
00262   select_query += ";";
00263 
00264   //ROS_INFO("Query: %s", select_query.c_str());
00265 
00266   PGresult* raw_result = PQexec(connection_, select_query.c_str());
00267   result.reset( new PGresultAutoPtr(raw_result) );
00268   if (PQresultStatus(raw_result) != PGRES_TUPLES_OK)
00269   {
00270     ROS_ERROR("Database get list: query failed. Error: %s", PQresultErrorMessage(raw_result));
00271     return false;
00272   }
00273   
00274   num_tuples = PQntuples(raw_result);
00275   if (!num_tuples) 
00276   {
00277     return true;
00278   }
00279   
00280   //store the column id's for each field we retrieved
00281   for (size_t t=0; t<fields.size(); t++)
00282   {
00283     int id =  PQfnumber(raw_result, fields[t]->getName().c_str());
00284     if (id < 0)
00285     {
00286       ROS_ERROR("Database get list: column %s missing in result", fields[t]->getName().c_str());
00287       return false;
00288     }
00289     column_ids.push_back(id);
00290   }   
00291   return true;
00292 }
00293 
00298 bool PostgresqlDatabase::populateListEntry(DBClass *entry, boost::shared_ptr<PGresultAutoPtr> result, 
00299                                                     int row_num,
00300                                                     const std::vector<const DBFieldBase*> &fields,
00301                                                     const std::vector<int> &column_ids) const
00302 {
00303   for (size_t t=0; t<fields.size(); t++)
00304   {
00305     const char* char_value =  PQgetvalue(**result, row_num, column_ids[t]);
00306     DBFieldBase *entry_field = entry->getField(fields[t]->getName());
00307     if (!entry_field)
00308     {
00309       ROS_ERROR("Database get list: new entry missing field %s", fields[t]->getName().c_str());
00310       return false;
00311     }
00312     if ( !entry_field->fromString(char_value) )
00313     {
00314       ROS_ERROR("Database get list: failed to parse response \"%s\" for field \"%s\"",   
00315                 char_value, fields[t]->getName().c_str()); 
00316       return false;
00317     }
00318   }
00319   return true;
00320 }
00321 
00328 bool PostgresqlDatabase::countList(const DBClass *example, int &count, std::string where_clause) const
00329 {
00330   const DBFieldBase* pk_field = example->getPrimaryKeyField();
00331   
00332   std::string query( "SELECT COUNT(" + pk_field->getName() + ") FROM " + pk_field->getTableName() );
00333   if (!where_clause.empty())
00334   {
00335     query += " WHERE " + where_clause;
00336   }
00337   query += ";";
00338 
00339   ROS_INFO("Query (count): %s", query.c_str());
00340   PGresultAutoPtr result( PQexec(connection_, query.c_str()) );
00341                          
00342   if (PQresultStatus(*result) != PGRES_TUPLES_OK)
00343   {
00344     ROS_ERROR("Database count list query failed. Error: %s", PQresultErrorMessage(*result));
00345     return false;
00346   }
00347   const char *reply =  PQgetvalue(*result, 0, 0);
00348   if (!DBStreamable<int>::streamableFromString(count, reply)) 
00349   {
00350     ROS_ERROR("Database count list failed. Could not understand reply: %s", reply);
00351     return false;
00352   }
00353   return true;  
00354 }
00355 
00362 bool PostgresqlDatabase::saveToDatabase(const DBFieldBase* field)
00363 {
00364   if (!field->getWritePermission())
00365   {
00366     ROS_ERROR("Database save field: field %s does not have write permission", field->getName().c_str());
00367     return false;
00368   }
00369 
00370   const DBFieldBase* key_field;
00371   if (field->getTableName() == field->getOwner()->getPrimaryKeyField()->getTableName()) 
00372   {
00373     key_field = field->getOwner()->getPrimaryKeyField();
00374   }
00375   else 
00376   {
00377     if (!field->getOwner()->getForeignKey(field->getTableName(), key_field))
00378     {
00379       ROS_ERROR("Database save field: could not find foreign key for table %s", 
00380                 field->getTableName().c_str());
00381       return false;
00382     }
00383     //here we could also check if the join is done on our primary key, and 
00384     //reject if not insted of using the write_permisison flag
00385   }
00386  
00387   //prepare query with parameters so we can use binary data if needed
00388 
00389   std::string query("UPDATE " + field->getTableName() + 
00390                     " SET " + field->getName() + "=$2"
00391                     " WHERE " + key_field->getName() + "=$1;");
00392 
00393   std::vector<const char*> param_values(2);
00394   std::vector<int> param_lengths(2);
00395   std::vector<int> param_formats(2);
00396   //first parameter is always text
00397   std::string id_str;
00398   if (!key_field->toString(id_str))
00399   {
00400     ROS_ERROR("Database save field: failed to convert key id value to string");
00401     return false;
00402   }
00403   param_formats[0] = 0;
00404   param_values[0] = id_str.c_str();
00405 
00406   //second parameter could be binary
00407   std::string value_str;
00408   if (field->getType() == DBFieldBase::TEXT)
00409   {
00410     if (!field->toString(value_str)) 
00411     {
00412       ROS_ERROR("Database save field: failed to convert field value to string");
00413       return false;
00414     }
00415     param_formats[1] = 0;
00416     param_values[1] = value_str.c_str();
00417   }
00418   else if (field->getType() == DBFieldBase::BINARY)
00419   {
00420     size_t length;
00421     if (!field->toBinary(param_values[1], length))
00422     {
00423       ROS_ERROR("Database save field: failed to convert field value to binary");
00424       return false;
00425     }
00426     param_lengths[1] = length;
00427     param_formats[1] = 1;
00428   }
00429   else
00430   {
00431     ROS_ERROR("Database save field: unkown field type");
00432     return false;
00433   }
00434 
00435   //ROS_INFO("Save field query: %s $1=%s, $2=%s", query.c_str(), param_values[0], param_values[1]);
00436 
00437   PGresultAutoPtr result( PQexecParams(connection_, query.c_str(), 2, 
00438                                        NULL, &param_values[0], &param_lengths[0], &param_formats[0], 0) );
00439   if (PQresultStatus(*result) != PGRES_COMMAND_OK)
00440   {
00441     ROS_ERROR("Database save field: query failed. Error: %s", PQresultErrorMessage(*result));
00442     return false;
00443   }
00444   return true;
00445 }
00446 
00452 bool PostgresqlDatabase::loadFromDatabase(DBFieldBase* field) const
00453 {
00454   const DBFieldBase* key_field = NULL;
00455   if (field->getTableName() == field->getOwner()->getPrimaryKeyField()->getTableName())
00456   {
00457     key_field = field->getOwner()->getPrimaryKeyField();
00458   }
00459   else 
00460   {
00461     if (!field->getOwner()->getForeignKey(field->getTableName(), key_field))
00462     {
00463       ROS_ERROR("Database load field: could not find foreign key for table %s", 
00464                 field->getTableName().c_str());
00465       return false;
00466     }
00467   }
00468 
00469   std::string id_str;
00470   if (!key_field->toString(id_str))
00471   {
00472     ROS_ERROR("Database load field: failed to convert key id value to string");
00473     return false;
00474   }
00475 
00476   std::string query("SELECT " + field->getName() + " FROM " + field->getTableName() + 
00477                     " WHERE " + key_field->getName() + " ='" + id_str + "';");
00478 
00479   //ROS_INFO_STREAM("Load field query: " << query);
00480 
00481   int data_type;
00482   if (field->getType() == DBFieldBase::TEXT) data_type = 0;
00483   else if (field->getType() == DBFieldBase::BINARY) data_type = 1;
00484   else
00485   {
00486     ROS_ERROR("Database load field: unkown field type");
00487     return false;
00488   }
00489 
00490   PGresultAutoPtr result( PQexecParams(connection_, query.c_str(), 0, NULL, NULL, NULL, NULL, data_type) );
00491   if (PQresultStatus(*result) != PGRES_TUPLES_OK)
00492   {
00493     ROS_ERROR("Database load field: query failed. Error: %s", PQresultErrorMessage(*result));
00494     return false;
00495   }
00496 
00497   if (PQntuples(*result)==0)
00498   {
00499     ROS_ERROR("Database load field: no entry found for key value %s on column %s", 
00500               id_str.c_str(), key_field->getName().c_str());
00501     return false;
00502   }
00503 
00504   const char* result_char =  PQgetvalue(*result, 0, 0);
00505   if (field->getType() == DBFieldBase::TEXT)
00506   {
00507     if ( !field->fromString(result_char) )
00508     {
00509       ROS_ERROR("Database load field: failed to parse text result \"%s\" for field \"%s\"", 
00510                 result_char, field->getName().c_str()); 
00511       return false;
00512     }
00513   } 
00514   else if (field->getType() == DBFieldBase::BINARY)
00515   {
00516     size_t length = PQgetlength(*result, 0, 0);
00517     if (!field->fromBinary(result_char, length))
00518     {
00519       ROS_ERROR("Database load field: failed to parse binary result length %d for field \"%s\"",
00520                 (int) length, field->getName().c_str()); 
00521       return false;
00522     }
00523   }
00524   else
00525   {
00526     ROS_ERROR("Database load field: failed to parse unkown field type");
00527     return false;
00528   }
00529 
00530   return true;
00531 }
00532 
00541 bool PostgresqlDatabase::insertIntoTable(std::string table_name,
00542                                                   const std::vector<const DBFieldBase*> &fields)
00543 {
00544   if (fields.empty())
00545   {
00546     ROS_ERROR("Insert into table: no columns to insert");
00547     return false;
00548   }
00549 
00550   std::string query("INSERT INTO " + table_name + "(");
00551 
00552   //the first field might be the foreign key
00553   if (table_name == fields[0]->getTableName())
00554   {
00555     //it's not, just any other field
00556     query += fields[0]->getName();
00557   }
00558   else
00559   {
00560     //we are inserting the foreign key, which for now is always assumed to be referencing our primary key
00561     const DBFieldBase *foreign_key = NULL;
00562     if (!fields[0]->getOwner()->getForeignKey(table_name, foreign_key))
00563     {
00564       ROS_ERROR("Database insert into table: could not find foreign key for table %s", table_name.c_str());
00565       return false;
00566     }
00567     query += foreign_key->getName();
00568   }
00569 
00570   for(size_t i=1; i<fields.size(); i++)
00571   {
00572     query += "," + fields[i]->getName();
00573   }
00574   query += ")";
00575 
00576   query += "VALUES(";
00577   for (size_t i=0; i<fields.size(); i++)
00578   {
00579     if ( i!=0 && table_name!=fields[i]->getTableName())
00580     {
00581       ROS_ERROR("Database insert into table: field table does not match table name");
00582       return false;
00583     }
00584     if (i!=0) query += ",";
00585     std::ostringstream ss;
00586     ss << i+1;
00587     query += "$" + ss.str();
00588   }
00589   query += ");";
00590   
00591   //ROS_INFO("Query: %s", query.c_str());
00592 
00593   //now prepare the arguments
00594   std::vector<std::string> param_strings(fields.size());
00595   std::vector<const char*> param_values(fields.size());
00596   std::vector<int> param_lengths(fields.size());
00597   std::vector<int> param_formats(fields.size());
00598   for (size_t i=0; i<fields.size(); i++)
00599   {
00600     if (fields[i]->getType() == DBFieldBase::TEXT)
00601     {
00602       if (!fields[i]->toString(param_strings[i]))
00603       {
00604         ROS_ERROR("Database insert into table: could not parse field %s", fields[i]->getName().c_str());
00605         return false;
00606       }
00607       param_values[i] = param_strings[i].c_str();
00608       param_formats[i] = 0;
00609     }
00610     else if (fields[i]->getType() == DBFieldBase::BINARY)
00611     {
00612       size_t length;
00613       if (!fields[i]->toBinary(param_values[i], length))
00614       {
00615         ROS_ERROR("Database insert into table: could not binarize field %s", fields[i]->getName().c_str());
00616         return false;
00617       }
00618       param_lengths[i] = length;
00619       param_formats[i] = 1;
00620     }
00621     else
00622     {
00623       ROS_ERROR("Database insert into table: unknown field type");
00624       return false;
00625     }
00626   }
00627 
00628   //and send the query
00629   PGresultAutoPtr result( PQexecParams(connection_, query.c_str(), fields.size(), NULL, 
00630                                        &(param_values[0]), &(param_lengths[0]), &(param_formats[0]), 0) );
00631   if (PQresultStatus(*result) != PGRES_COMMAND_OK)
00632   {
00633     ROS_ERROR("Database insert into table: query failed.\nError: %s.\nQuery: %s",
00634               PQresultErrorMessage(*result), query.c_str());
00635     return false;
00636   }
00637 
00638   return true;
00639 }
00640 
00647 bool PostgresqlDatabase::insertIntoDatabase(DBClass* instance)
00648 {
00649   //primary key must be text; its table is first
00650   DBFieldBase* pk_field = instance->getPrimaryKeyField();
00651   if (pk_field->getType() != DBFieldBase::TEXT)
00652   {
00653     ROS_ERROR("Database insert: cannot insert binary primary key %s", pk_field->getName().c_str());
00654     return false;
00655   }
00656 
00657   //make lists of which fields go in which table
00658   std::vector<std::string> table_names;
00659   std::vector< std::vector<const DBFieldBase*> > table_fields;
00660 
00661   //the first table we must insert into always belongs to the primary key
00662   table_names.push_back(pk_field->getTableName());
00663   table_fields.push_back( std::vector<const DBFieldBase*>() );
00664   
00665   bool insert_pk;
00666   if (pk_field->getWriteToDatabase())
00667   {
00668     //we will explicitly insert the primary key in its own table
00669     table_fields.back().push_back(pk_field);
00670     insert_pk = true;
00671   }
00672   else
00673   {
00674     //we do not insert the primary key in its own table; presumably it has a 
00675     //default value from a sequence which we will retrieve afterwards
00676     if (pk_field->getSequenceName().empty())
00677     {
00678       ROS_ERROR("Database insert: attempt to insert instance without primary key and no sequence for it");
00679       return false;
00680     }
00681     insert_pk = false;
00682   }
00683   
00684   //prepare insertions into other tables
00685   //note that even if we are inserting no data, we still need to make an entry
00686   //containing the foreign key so we have it for future insertions
00687   for (size_t i=0; i<instance->getNumFields(); i++)
00688   {
00689     //see if we are already inserting in this table
00690     bool found = false;
00691     size_t t;
00692     for (t=0; t<table_names.size(); t++)
00693     {
00694       if ( table_names[t] == instance->getField(i)->getTableName() )
00695       {
00696         found = true;
00697         break;
00698       }
00699     }
00700     if (!found)
00701     {
00702       //if we are not joining on our primary key, we should not be inserting in this table
00703       const DBFieldBase* foreign_key = NULL;
00704       if (!instance->getForeignKey(instance->getField(i)->getTableName(), foreign_key))
00705       {
00706         ROS_ERROR("Database insert into table: could not find foreign key for table %s", 
00707           instance->getField(i)->getTableName().c_str());
00708         return false;
00709       }
00710       if (foreign_key != pk_field) 
00711       {
00712         continue;
00713       }
00714       //we are joining on primary key, so we will need to insert it in other table as well
00715       table_names.push_back( instance->getField(i)->getTableName() );
00716       table_fields.push_back( std::vector<const DBFieldBase*>() );
00717       //in all the other tables we must explicitly insert the value of our 
00718       //primary key as it is the foreign key in all other tables
00719       table_fields.back().push_back(pk_field);
00720       t = table_names.size() - 1;
00721     }
00722     if ( !instance->getField(i)->getWriteToDatabase() ) continue;
00723     if ( instance->getField(i)->getType() != DBFieldBase::TEXT )
00724     {
00725       ROS_WARN("Database insert: cannot insert binary field %s in database", 
00726         instance->getField(i)->getName().c_str());
00727       continue;
00728     }
00729     //insert the field itself
00730     table_fields[t].push_back(instance->getField(i));
00731   }
00732   
00733   //BEGIN transaction
00734   if (!begin()) return false;
00735 
00736   //first we insert into the primary key's table
00737   if (!insertIntoTable(table_names[0], table_fields[0])) 
00738   {
00739     rollback();
00740     return false;
00741   }
00742 
00743   //if we have to, retrieve the primary key
00744   if (!insert_pk)
00745   {
00746     std::string sequence_value;
00747     if (!getSequence(pk_field->getSequenceName(), sequence_value) || !pk_field->fromString(sequence_value))
00748     {
00749       ROS_ERROR("Database insert: failed to retrieve primary key after insertion");
00750       rollback();
00751       return false;
00752     }
00753   }
00754 
00755   //insert into the rest of the tables
00756   for (size_t i=1; i<table_names.size(); i++)
00757   {
00758     if (!insertIntoTable(table_names[i], table_fields[i]))
00759     {
00760       rollback();
00761       return false;
00762     }
00763   }
00764 
00765   //COMMIT transaction
00766   if (!commit()) return false;
00767 
00768   return true;
00769 }
00770 
00772 bool PostgresqlDatabase::deleteFromTable(std::string table_name, const DBFieldBase *key_field)
00773 {
00774   std::string id_str;
00775   if (!key_field->toString(id_str))
00776   {
00777     ROS_ERROR("Database delete from table: failed to convert key id value to string");
00778     return false;
00779   }
00780 
00781   std::string query("DELETE FROM " + table_name + " WHERE " + key_field->getName() + "=" + id_str);
00782   PGresultAutoPtr result( PQexec(connection_, query.c_str()) );
00783   if (PQresultStatus(*result) != PGRES_COMMAND_OK)
00784   {
00785     ROS_ERROR("Database delete from table: query failed. Error: %s", PQresultErrorMessage(*result));
00786     return false;
00787   }
00788   return true;
00789 }
00790 
00794 bool PostgresqlDatabase::deleteFromDatabase(DBClass* instance)
00795 {
00796   std::vector<std::string> table_names;
00797   std::vector<const DBFieldBase*> table_fields;
00798   DBFieldBase* pk_field = instance->getPrimaryKeyField();
00799   //the table of the primary key
00800   table_names.push_back( pk_field->getTableName() );
00801   table_fields.push_back( pk_field );
00802   //prepare deletions from the other tables
00803   for (size_t i=0; i<instance->getNumFields(); i++)
00804   {
00805     //see if we are already deleting in this table
00806     size_t t;
00807     for (t=0; t<table_names.size(); t++)
00808     {
00809       if ( table_names[t] == instance->getField(i)->getTableName() ) break;
00810     }
00811     if (t<table_names.size()) continue;
00812 
00813     //if we are not joining on our primary key, we should NOT be deleting from this table
00814     const DBFieldBase* foreign_key = NULL;
00815     if (!instance->getForeignKey(instance->getField(i)->getTableName(), foreign_key))
00816     {
00817       ROS_ERROR("Database insert into table: could not find foreign key for table %s", 
00818                 instance->getField(i)->getTableName().c_str());
00819       return false;
00820     }
00821     if (foreign_key != pk_field) continue;
00822 
00823     //we are joining on primary key, so we will need to delete the row in the other table as well
00824     table_names.push_back( instance->getField(i)->getTableName() );
00825     table_fields.push_back( pk_field );
00826   }
00827 
00828   //BEGIN transaction
00829   if (!begin()) return false;
00830   
00831   //delete from all tables, but primary key table goes last
00832   for (int i=(int)table_names.size()-1; i>=0; i--)
00833   {
00834     if (!deleteFromTable(table_names[i], table_fields[i]))
00835     {
00836       rollback();
00837       return false;
00838     }
00839   }
00840 
00841   //COMMIT transaction
00842   if (!commit()) return false;
00843 
00844   return true;
00845 
00846 }
00847 
00848 }//namespace
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends


database_interface
Author(s): Matei Ciocarlie and Lorenz Mosenlechner
autogenerated on Wed Jan 16 2013 13:32:14