sqlChannel.cc

   1 /**
   2  * sqlChannel.cc
   3  *
   4  * Author: Matthias Crauwels <ultimate_@wol.be>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  * You should have received a copy of the GNU General Public License
  17  * along with this program; if not, write to the Free Software
  18  * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
  19  * USA.
  20  *
  21  * $Id$
  22  */
  23
  24 #include        <sstream>
  25 #include        <string>
  26
  27 #include        "dbHandle.h"
  28
  29 #include        "ELog.h"
  30 #include        "misc.h"
  31
  32 #include        "sqlChannel.h"
  33 #include        "chanfix.h"
  34
  35 namespace gnuworld
  36 {
  37
  38 namespace cf
  39 {
  40
  41 const sqlChannel::flagType sqlChannel::F_BLOCKED        = 0x00000001 ;
  42 const sqlChannel::flagType sqlChannel::F_ALERT          = 0x00000002 ;
  43
  44 const int sqlChannel::EV_MISC           = 1  ; /* Uncategorized event */
  45 const int sqlChannel::EV_NOTE           = 2  ; /* Miscellaneous notes */
  46 const int sqlChannel::EV_CHANFIX        = 3  ; /* Manual chanfixes */
  47 const int sqlChannel::EV_BLOCK          = 4  ; /* Channel block */
  48 const int sqlChannel::EV_UNBLOCK        = 5  ; /* Channel unblock */
  49 const int sqlChannel::EV_ALERT          = 6  ; /* Channel alert */
  50 const int sqlChannel::EV_UNALERT        = 7  ; /* Channel unalert */
  51 const int sqlChannel::EV_REQUESTOP      = 8  ; /* Requestops */
  52 const int sqlChannel::EV_TEMPBLOCK      = 9  ; /* Temp channel block */
  53 const int sqlChannel::EV_UNTEMPBLOCK    = 10 ; /* Temp channel unblock */
  54 const int sqlChannel::EV_SIMULATE       = 11 ; /* Fix simulation */
  55
  56 unsigned long int sqlChannel::maxUserId = 0;
  57
  58 sqlChannel::sqlChannel(sqlManager* _myManager) :
  59   id(0),
  60   channel(),
  61   user_name(),
  62   last(0),
  63   start(0),
  64   maxScore(0),
  65   modesRemoved(false),
  66   flags(0),
  67   inSQL(false)
  68 {
  69   myManager = _myManager;
  70 }
  71
  72 void sqlChannel::setAllMembers(dbHandle* theDB, int row)
  73 {
  74   id = atoi(theDB->GetValue(row, 0));
  75   channel = theDB->GetValue(row, 1);
  76   flags = atoi(theDB->GetValue(row, 2));
  77   inSQL = true;
  78
  79   if (id > maxUserId) maxUserId = id;
  80 }
  81
  82 /**
  83  * This function inserts a brand new channel into the DB.
  84  */
  85 bool sqlChannel::Insert(dbHandle* cacheCon)
  86 {
  87 /* Get a connection instance to our backend */
  88 //dbHandle* cacheCon = myManager->getConnection();
  89
  90 /* Grab the next available user id */
  91 id = ++maxUserId;
  92
  93 /* Create the INSERT statement */
  94 std::stringstream insertString;
  95 insertString    << "INSERT INTO channels "
  96                 << "(id, channel, flags) "
  97                 << "VALUES "
  98                 << "("
  99                 << id << ", "
 100                 << "'" << escapeSQLChars(channel) << "', "
 101                 << flags
 102                 << ")"
 103                 ;
 104
 105 if (!cacheCon->Exec(insertString.str())) {
 106   elog  << "sqlChannel::Insert> Something went wrong: "
 107         << cacheCon->ErrorMessage()
 108         << std::endl;
 109   inSQL = false;
 110   maxUserId--;
 111 } else {
 112   inSQL = true;
 113 }
 114
 115 /* Dispose of our connection instance */
 116 //myManager->removeConnection(cacheCon);
 117
 118 return inSQL;
 119 } // sqlChannel::Insert()
 120
 121 bool sqlChannel::Delete(dbHandle* cacheCon)
 122 {
 123 bool retval = false;
 124
 125 /* Get a connection instance to our backend */
 126 //dbHandle* cacheCon = myManager->getConnection();
 127
 128 /* Create the DELETE statement */
 129 std::stringstream deleteString;
 130 deleteString    << "DELETE FROM channels "
 131                 << "WHERE id = '" << id << "'"
 132                 ;
 133
 134 if (!cacheCon->Exec(deleteString.str())) {
 135   elog  << "sqlChannel::Delete> Something went wrong: "
 136         << cacheCon->ErrorMessage()
 137         << std::endl;
 138   retval = false;
 139 } else
 140   retval = true;
 141
 142 /* Dispose of our connection instance */
 143 //myManager->removeConnection(cacheCon);
 144
 145 return retval;
 146 }
 147
 148 bool sqlChannel::commit(dbHandle* cacheCon)
 149 {
 150 bool retval = false;
 151
 152 /* Get a connection instance to our backend */
 153 //dbHandle* cacheCon = myManager->getConnection();
 154
 155 /* Create the UPDATE statement */
 156 std::stringstream chanCommit;
 157 chanCommit      << "UPDATE channels SET "
 158                 << "flags = " << flags
 159                 << " WHERE "
 160                 << "id = " << id
 161                 ;
 162
 163 if (!cacheCon->Exec(chanCommit.str())) {
 164   elog  << "sqlChannel::commit> Something went wrong: "
 165         << cacheCon->ErrorMessage()
 166         << std::endl;
 167   retval = false;
 168 } else
 169   retval = true;
 170
 171 /* Dispose of our connection instance */
 172 //myManager->removeConnection(cacheCon);
 173
 174 return retval;
 175 }
 176
 177 /**
 178  * This method writes a 'notes' record, recording an event that has
 179  * occured in this channel.
 180  */
 181
 182 void sqlChannel::addNote(dbHandle* cacheCon, unsigned short eventType, iClient* theUser,
 183         const std::string& theMessage)
 184 {
 185 unsigned int num_notes = countNotes(cacheCon, 0);
 186 while (num_notes >= MAXNOTECOUNT) {
 187   if (!deleteOldestNote(cacheCon))
 188     return;
 189   num_notes--;
 190 }
 191
 192 /* Get a connection instance to our backend */
 193 //dbHandle* cacheCon = myManager->getConnection();
 194
 195 /* Create the INSERT statement */
 196 std::stringstream theLog;
 197 theLog  << "INSERT INTO notes (ts, channelID, user_name, event, message) "
 198         << "VALUES ("
 199         << "now()::abstime::int4"
 200         << ", "
 201         << id
 202         << ", '"
 203         << theUser->getAccount()
 204         << "', "
 205         << eventType
 206         << ", "
 207         << "'"
 208         << escapeSQLChars(theMessage)
 209         << "')"
 210         ;
 211
 212 if (!cacheCon->Exec(theLog.str())) {
 213   elog  << "sqlChannel::addNote> Something went wrong: "
 214         << cacheCon->ErrorMessage()
 215         << std::endl;
 216 }
 217
 218 /* Dispose of our connection instance */
 219 //myManager->removeConnection(cacheCon);
 220
 221 return;
 222 }
 223
 224 const std::string sqlChannel::getLastNote(dbHandle* cacheCon, unsigned short eventType, time_t& eventTime)
 225 {
 226 std::string retval;
 227
 228 /* Get a connection instance to our backend */
 229 //dbHandle* cacheCon = myManager->getConnection();
 230
 231 /* Retrieve the last note */
 232 std::stringstream queryString;
 233 queryString     << "SELECT message,ts"
 234                 << " FROM notes WHERE channelID = "
 235                 << id
 236                 << " AND event = "
 237                 << eventType
 238                 << " ORDER BY ts DESC LIMIT 1"
 239                 ;
 240
 241 if (cacheCon->Exec(queryString.str(),true)) {
 242   if (cacheCon->Tuples() > 0) {
 243     std::string note = cacheCon->GetValue(0, 0);
 244     eventTime = atoi(cacheCon->GetValue(0, 1));
 245     retval = note;
 246   }
 247 }
 248
 249 /* Dispose of our connection instance */
 250 //myManager->removeConnection(cacheCon);
 251
 252 return retval;
 253 }
 254
 255 bool sqlChannel::deleteNote(dbHandle* cacheCon, unsigned int messageId)
 256 {
 257 bool retval = false;
 258
 259 /* Get a connection instance to our backend */
 260 //dbHandle* cacheCon = myManager->getConnection();
 261
 262 /* Create the DELETE statement */
 263 std::stringstream deleteString;
 264 deleteString    << "DELETE FROM notes WHERE channelID = "
 265                 << id
 266                 << " AND id = "
 267                 << messageId
 268                 ;
 269
 270 if (!cacheCon->Exec(deleteString.str())) {
 271   elog  << "sqlChannel::deleteNote> Something went wrong: "
 272         << cacheCon->ErrorMessage()
 273         << std::endl;
 274   retval = false;
 275 } else
 276   retval = true;
 277
 278 /* Dispose of our connection instance */
 279 //myManager->removeConnection(cacheCon);
 280
 281 return retval;
 282 }
 283
 284 bool sqlChannel::deleteOldestNote(dbHandle* cacheCon)
 285 {
 286 bool retval = false;
 287
 288 /* Get a connection instance to our backend */
 289 //dbHandle* cacheCon = myManager->getConnection();
 290
 291 /* Retrieve the id of the oldest note */
 292 std::stringstream selectString;
 293 selectString    << "SELECT id FROM notes WHERE channelID = "
 294                 << id
 295                 << " ORDER BY ts ASC LIMIT 1"
 296                 ;
 297
 298 if (cacheCon->Exec(selectString.str(),true)) {
 299   if (cacheCon->Tuples() > 0) {
 300     unsigned int note_id = atoi(cacheCon->GetValue(0, 0));
 301
 302     std::stringstream deleteString;
 303     deleteString        << "DELETE FROM notes WHERE id = "
 304                         << note_id
 305                         ;
 306
 307     if (cacheCon->Exec(deleteString.str(),true))
 308       retval = true;
 309   }
 310 }
 311
 312 /* Dispose of our connection instance */
 313 //myManager->removeConnection(cacheCon);
 314
 315 return retval;
 316 }
 317
 318 bool sqlChannel::deleteAllNotes(dbHandle* cacheCon)
 319 {
 320 bool retval = false;
 321
 322 /* Get a connection instance to our backend */
 323 //dbHandle* cacheCon = myManager->getConnection();
 324
 325 /* Create the DELETE statement */
 326 std::stringstream deleteString;
 327 deleteString    << "DELETE FROM notes WHERE channelID = "
 328                 << id
 329                 ;
 330
 331 if (!cacheCon->Exec(deleteString.str())) {
 332   elog  << "sqlChannel::deleteAllNotes> Something went wrong: "
 333         << cacheCon->ErrorMessage()
 334         << std::endl;
 335   retval = false;
 336 } else
 337   retval = true;
 338
 339 /* Dispose of our connection instance */
 340 //myManager->removeConnection(cacheCon);
 341
 342 return retval;
 343 }
 344
 345 size_t sqlChannel::countNotes(dbHandle* cacheCon, unsigned short eventType)
 346 {
 347 /* Get a connection instance to our backend */
 348 //dbHandle* cacheCon = myManager->getConnection();
 349
 350 /* Count the notes */
 351 std::stringstream queryString;
 352 queryString     << "SELECT count(id) FROM notes WHERE channelID = "
 353                 << id
 354                 ;
 355 if (eventType) {
 356 queryString     << " AND event = "
 357                 << eventType
 358                 ;
 359 }
 360
 361 size_t num_notes = 0;
 362
 363 if (cacheCon->Exec(queryString.str(),true))
 364   num_notes = atoi(cacheCon->GetValue(0, 0));
 365
 366 /* Dispose of our connection instance */
 367 //myManager->removeConnection(cacheCon);
 368
 369 return num_notes;
 370 }
 371
 372 sqlChannel::~sqlChannel()
 373 {
 374 // No heap space allocated
 375 }
 376
 377 } // namespace cf
 378
 379 } // namespace gnuworld