ryzom/server/src/server_share/mysql_wrapper.h

   1 // Ryzom - MMORPG Framework <http://dev.ryzom.com/projects/ryzom/>
   2 // Copyright (C) 2010  Winch Gate Property Limited
   3 //
   4 // This program is free software: you can redistribute it and/or modify
   5 // it under the terms of the GNU Affero General Public License as
   6 // published by the Free Software Foundation, either version 3 of the
   7 // License, or (at your option) any later version.
   8 //
   9 // This program is distributed in the hope that it will be useful,
  10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 // GNU Affero General Public License for more details.
  13 //
  14 // You should have received a copy of the GNU Affero General Public License
  15 // along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 #ifndef MYSQL_WRAPPER_H
  18 #define MYSQL_WRAPPER_H
  19
  20 #include "nel/misc/types_nl.h"
  21 #include <memory>
  22 #include "nel/misc/debug.h"
  23 #include "nel/misc/common.h"
  24 #include "nel/misc/md5.h"
  25 #include "nel/net/service.h"
  26 #include "nel/net/module_common.h"
  27 #include "game_share/utils.h"
  28 #ifdef NL_OS_WINDOWS
  29 # include <WinSock2.h>
  30 # include <Windows.h>
  31 typedef unsigned long ulong;
  32 #endif
  33 #ifdef NL_OS_MAC
  34 typedef unsigned long ulong;
  35 #endif
  36
  37 #include <mysql.h>
  38 #include <time.h>
  39
  40 #include "game_share/r2_basic_types.h" // for TSessionId
  41 namespace MSW
  42 {
  43         class CStoreResult;
  44         class CUseResult;
  45         class CConnection;
  46
  47         /// Utility function to encode a date
  48         std::string encodeDate(NLMISC::TTime date);
  49
  50         /** escape string using 'mysql_real_escape_string' and return a the escaped string.
  51          *      Note that the function is internally optimized to quickly build the return string
  52          *      with very little overhead.
  53          */
  54         const std::string &escapeString(const std::string &str, CConnection &dbCnx);
  55
  56         class CConnection
  57         {
  58                 friend const std::string &escapeString(const std::string &str, CConnection &dbCnx);
  59
  60                 /// Connection info
  61                 std::string             _ConnHostName;
  62                 std::string             _ConnUserName;
  63                 std::string             _ConnPassword;
  64                 std::string             _ConnDefaultDatabase;
  65                 uint                    _ConnPort;
  66
  67
  68                 /// The mysql connection context
  69                 MYSQL   *_MysqlContext;
  70
  71                 /// Flag for connection open
  72                 bool    _Connected;
  73
  74                 typedef std::map<mysql_option, const char*>     TOptions;
  75                 /// A list of pair name/value of connection option
  76                 TOptions        _Options;
  77
  78                 /// Internale connect method (this one do the job)
  79                 bool _connect();
  80
  81         public:
  82
  83                 CConnection()
  84                         :       _MysqlContext(NULL),
  85                                 _Connected(false)
  86                 {
  87                 }
  88
  89                 ~CConnection()
  90                 {
  91                         if (_Connected)
  92                                 mysql_close(_MysqlContext);
  93                 }
  94
  95                 void addOption(mysql_option option, const char *value);
  96                 void clearOption(mysql_option option);
  97
  98                 bool connect(const std::string &hostName, const std::string &userName, const std::string &password, const std::string &defaultDatabase, uint port);
  99                 bool connect(const NLNET::TParsedCommandLine &databaseInfo);
 100
 101                 void closeConn();
 102
 103                 bool query(const std::string &queryString);
 104
 105                 uint32 getLastGeneratedId()
 106                 {
 107                         return uint32(mysql_insert_id(_MysqlContext));
 108                 }
 109
 110                 uint32 getAffectedRows()
 111                 {
 112                         return uint32(mysql_affected_rows(_MysqlContext));
 113                 }
 114
 115                 CUniquePtr<CStoreResult>                storeResult();
 116                 CUniquePtr<CUseResult>          useResult();
 117
 118         };
 119
 120         // base class contains method to extract info from a fetched row
 121         class CResultBase
 122         {
 123         protected:
 124                 MYSQL_ROW               _CurrentRow;
 125                 unsigned long   *_FieldLength;
 126
 127                 MYSQL_RES               *_Result;
 128
 129
 130                 CResultBase(MYSQL_RES   *result)
 131                         : _CurrentRow(NULL),
 132                         _FieldLength(NULL),
 133                         _Result(result)
 134                 {
 135
 136                 }
 137         public:
 138
 139                 ~CResultBase()
 140                 {
 141                         mysql_free_result(_Result);
 142                 }
 143
 144
 145                 /// return the number of fields in the result set
 146                 uint32 getNumFields()
 147                 {
 148                         return mysql_num_fields(_Result);
 149                 }
 150
 151                 const char *getRawField(uint32 fieldIndex)
 152                 {
 153                         nlassert(_CurrentRow != NULL);
 154                         nlassert(fieldIndex < getNumFields());
 155
 156                         static const char *emptyString = "";
 157
 158                         char *ret = _CurrentRow[fieldIndex];
 159
 160                         return ret != NULL ? ret : emptyString;
 161                 }
 162
 163                 void getField(uint32 fieldIndex, std::string &value)
 164                 {
 165                         value = getRawField(fieldIndex);
 166                 }
 167
 168                 void getField(uint32 fieldIndex, bool &value)
 169                 {
 170                         const char *str = getRawField(fieldIndex);
 171                         if (str[0] == '1')
 172                                 value = true;
 173                         else if (str[0] == '0')
 174                                 value = false;
 175                         else
 176                                 value = NLMISC::nlstricmp(str, "true") == 0;
 177                 }
 178                 void getField(uint32 fieldIndex, uint8 &value)
 179                 {
 180                         const char *str = getRawField(fieldIndex);
 181                         value = uint8(strtoul(str, NULL, 10));
 182                 }
 183                 void getField(uint32 fieldIndex, uint32 &value)
 184                 {
 185                         const char *str = getRawField(fieldIndex);
 186                         value = uint32(strtoul(str, NULL, 10));
 187                 }
 188                 void getField(uint32 fieldIndex, sint8 &value)
 189                 {
 190                         NLMISC::fromString(std::string(getRawField(fieldIndex)), value);
 191                 }
 192                 void getField(uint32 fieldIndex, sint32 &value)
 193                 {
 194                         NLMISC::fromString(std::string(getRawField(fieldIndex)), value);
 195                 }
 196
 197                 void getField(uint32 fieldIndex, TSessionId &value)
 198                 {
 199                         sint32 val;
 200                         NLMISC::fromString(std::string(getRawField(fieldIndex)), val);
 201                         value =TSessionId(val);
 202                 }
 203
 204                 void getField(uint32 fieldIndex, std::vector<uint8> &value)
 205                 {
 206                         const char *data = getRawField(fieldIndex);
 207                         long size = _FieldLength[fieldIndex];
 208                         std::vector<uint8> vec(data, data+size);
 209                         value.swap(vec);
 210                 }
 211
 212                 void getMD5Field(uint32 fieldIndex, NLMISC::CHashKeyMD5 &hashKey)
 213                 {
 214                         const char *data = getRawField(fieldIndex);
 215                         long size = _FieldLength[fieldIndex];
 216
 217                         hashKey.fromString(data);
 218                 }
 219
 220                 void getDateField(uint32 fieldIndex, uint32 &time);
 221         };
 222
 223         class CUseResult : public CResultBase
 224         {
 225                 friend class CConnection;
 226
 227                 CUseResult(MYSQL_RES    *result)
 228                         : CResultBase(result)
 229                 {
 230                 }
 231
 232         public:
 233                 /// Advance to next row in the result set, return true is there is a row
 234                 bool fetchRow()
 235                 {
 236                         _CurrentRow = mysql_fetch_row(_Result);
 237                         if (_CurrentRow == NULL)
 238                                 return false;
 239                         _FieldLength = mysql_fetch_lengths(_Result);
 240                         nlassert(_FieldLength != NULL);
 241
 242                         return true;
 243                 }
 244         };
 245
 246         class CStoreResult : public CResultBase
 247         {
 248                 friend class CConnection;
 249
 250
 251                 CStoreResult(MYSQL_RES  *result)
 252                         : CResultBase(result)
 253                 {
 254
 255                 }
 256         public:
 257
 258
 259                 /// Return the number of row in the result set
 260                 uint32 getNumRows()
 261                 {
 262                         return uint32(mysql_num_rows(_Result));
 263                 }
 264
 265                 /// Advance to next row in the result set
 266                 void fetchRow()
 267                 {
 268                         _CurrentRow = mysql_fetch_row(_Result);
 269                         nlassert(_CurrentRow != NULL);
 270                         _FieldLength = mysql_fetch_lengths(_Result);
 271                         nlassert(_FieldLength != NULL);
 272                 }
 273         };
 274
 275
 276
 277 } // namespace MSW
 278
 279 //NeL simple Object Persistence Engine
 280 namespace NOPE
 281 {
 282         enum TObjectState
 283         {
 284                 /// This is a temporary allocated, not persisted object instance
 285                 os_transient,
 286                 /// This is a persistent object, unmodified since it was loaded
 287                 os_clean,
 288                 /// This is a persistent object, modified in transient space that need to be saved to db
 289                 os_dirty,
 290                 /// This is removed object, it should no more be used as it have been removed from db
 291                 os_removed,
 292                 /// This is a persistent object, unmodified but that lie in the object cache.
 293                 os_released,
 294
 295                 // a tag counter MUST BE LAST
 296                 os_nb_state,
 297         };
 298
 299         const uint32 INVALID_OBJECT_ID = 0;
 300
 301         extern bool AllowedTransition [os_nb_state][os_nb_state];
 302
 303
 304         enum TCacheCmd
 305         {
 306                 cc_update,
 307                 cc_clear,
 308                 cc_dump,
 309                 cc_instance_count,
 310         };
 311
 312         typedef uint32 (*TCacheCmdFunc)(TCacheCmd cmd);
 313
 314
 315         class CPersistentCache :
 316                 public NLNET::IServiceUpdatable,
 317                 public NLMISC::ICommandsHandler
 318         {
 319                 NLMISC_SAFE_SINGLETON_DECL(CPersistentCache);
 320
 321                 typedef std::set<TCacheCmdFunc> TUpdateFuncs;
 322                 TUpdateFuncs    _UpdateFuncs;
 323
 324                 CPersistentCache()
 325                 {
 326                         NLMISC::CCommandRegistry::getInstance().registerNamedCommandHandler(this, "CPersistentCache");
 327                 }
 328 //              virtual const std::string &getCommandHandlerClassName() const
 329 //              {
 330 //                      static string name("CPersistentCache");
 331 //                      return name;
 332 //              }
 333
 334                 virtual const std::string &getCommandHandlerName() const
 335                 {
 336                         static std::string name("sqlObjectCache");
 337                         return name;
 338                 }
 339
 340         public:
 341
 342                 void registerCache(TCacheCmdFunc functionPtr)
 343                 {
 344                         _UpdateFuncs.insert(functionPtr);
 345                 }
 346
 347                 void serviceLoopUpdate()
 348                 {
 349                         H_AUTO(CPersistentCache_serviceLoopUpdate);
 350
 351                         TUpdateFuncs::iterator first(_UpdateFuncs.begin()), last(_UpdateFuncs.end());
 352                         for (; first != last; ++first)
 353                         {
 354                                 TCacheCmdFunc f = *first;
 355                                 f(cc_update);
 356                         }
 357                 }
 358
 359                 // delete any unreference object in the cache
 360                 void clearCache()
 361                 {
 362                         TUpdateFuncs::iterator first(_UpdateFuncs.begin()), last(_UpdateFuncs.end());
 363                         for (; first != last; ++first)
 364                         {
 365                                 TCacheCmdFunc f = *first;
 366                                 f(cc_clear);
 367                         }
 368                 }
 369
 370                 /// Return the grand total of instance in memory (including in use and in cache objects)
 371                 uint32 getInstanceCount()
 372                 {
 373                         uint32 total = 0;
 374                         TUpdateFuncs::iterator first(_UpdateFuncs.begin()), last(_UpdateFuncs.end());
 375                         for (; first != last; ++first)
 376                         {
 377                                 TCacheCmdFunc f = *first;
 378                                 total += f(cc_instance_count);
 379                         }
 380
 381                         return total;
 382                 }
 383
 384
 385                 NLMISC_COMMAND_HANDLER_TABLE_BEGIN(CPersistentCache)
 386                         NLMISC_COMMAND_HANDLER_ADD(CPersistentCache, clearCache, "remove any unreferenced cached object from memory", "no params")
 387                         NLMISC_COMMAND_HANDLER_ADD(CPersistentCache, dump, "dump cache status", "no params")
 388                 NLMISC_COMMAND_HANDLER_TABLE_END
 389
 390                 NLMISC_CLASS_COMMAND_DECL(dump)
 391                 {
 392                         log.displayNL("Dumping SQL cache for %u class :", _UpdateFuncs.size());
 393
 394                         // redirect all logs to log
 395                         NLMISC::CNLSmartLogOverride logRedirector(&log);
 396                         TUpdateFuncs::iterator first(_UpdateFuncs.begin()), last(_UpdateFuncs.end());
 397                         for (; first != last; ++first)
 398                         {
 399                                 TCacheCmdFunc f = *first;
 400                                 f(cc_dump);
 401                         }
 402                         return true;
 403                 }
 404
 405                 NLMISC_CLASS_COMMAND_DECL(clearCache)
 406                 {
 407                         // hop
 408                         clearCache();
 409                         return true;
 410                 }
 411
 412
 413         };
 414 } // namespace NOPE
 415
 416 #endif //  MYSQL_WRAPPER_H