MythTV  master
mythdbcon.h
Go to the documentation of this file.
1 #ifndef MYTHDBCON_H_
2 #define MYTHDBCON_H_
3 
4 #include <QSqlDatabase>
5 #include <QSqlRecord>
6 #include <QSqlError>
7 #include <QVariant>
8 #include <QSqlQuery>
9 #include <QDateTime>
10 #include <QMutex>
11 #include <QList>
12 
13 #include "mythbaseexp.h"
14 #include "mythdbparams.h"
15 
16 #define REUSE_CONNECTION 1
17 
18 MBASE_PUBLIC bool TestDatabase(const QString& dbHostName,
19  const QString& dbUserName,
20  QString dbPassword,
21  QString dbName = "mythconverg",
22  int dbPort = 3306);
23 
26 {
27  friend class MDBManager;
28  friend class MSqlQuery;
29  public:
30  explicit MSqlDatabase(QString name);
31  ~MSqlDatabase(void);
32 
33  bool OpenDatabase(bool skipdb = false);
34  void SetDBParams(const DatabaseParams &params) { m_dbparms = params; };
35 
36  private:
37  bool isOpen(void);
38  bool KickDatabase(void);
39  QString GetConnectionName(void) const { return m_name; }
40  QSqlDatabase db(void) const { return m_db; }
41  bool Reconnect(void);
42  void InitSessionVars(void);
43 
44  private:
45  QString m_name;
46  QSqlDatabase m_db;
47  QDateTime m_lastDBKick;
49 };
50 
53 {
54  friend class MSqlQuery;
55  public:
56  MDBManager(void) = default;
57  ~MDBManager(void);
58 
59  void CloseDatabases(void);
60  void PurgeIdleConnections(bool leaveOne = false);
61 
62  protected:
63  MSqlDatabase *popConnection(bool reuse);
64  void pushConnection(MSqlDatabase *db);
65 
66  MSqlDatabase *getSchedCon(void);
67  MSqlDatabase *getChannelCon(void);
68 
69  private:
70  MSqlDatabase *getStaticCon(MSqlDatabase **dbcon, const QString& name);
71 
72  QMutex m_lock;
73  using DBList = QList<MSqlDatabase*>;
74  QHash<QThread*, DBList> m_pool; // protected by m_lock
75 #if REUSE_CONNECTION
76  QHash<QThread*, MSqlDatabase*> m_inuse; // protected by m_lock
77  QHash<QThread*, int> m_inuseCount; // protected by m_lock
78 #endif
79 
80  int m_nextConnID {0};
81  int m_connCount {0};
82 
83  MSqlDatabase *m_schedCon {nullptr};
84  MSqlDatabase *m_channelCon {nullptr};
85  QHash<QThread*, DBList> m_staticPool;
86 };
87 
90 {
91  MSqlDatabase *db {nullptr};
92  QSqlDatabase qsqldb;
93  bool returnConnection {false};
94 };
95 
97 using MSqlBindings = QMap<QString, QVariant>;
98 
101 
103  MBASE_PUBLIC void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings);
104 
124 class MBASE_PUBLIC MSqlQuery : private QSqlQuery
125 {
126  MBASE_PUBLIC friend void MSqlEscapeAsAQuery(QString& query, MSqlBindings& bindings);
127  public:
129  explicit MSqlQuery(const MSqlQueryInfo &qi);
131  ~MSqlQuery();
132 
134  bool isConnected(void) const { return m_isConnected; }
135 
137  bool exec(void);
138 
140  bool next(void);
141 
143  bool previous(void);
144 
146  bool first(void);
147 
149  bool last(void);
150 
152  // so we can display the query results
153  bool seek(int where, bool relative = false);
154 
156  bool exec(const QString &query);
157 
159  bool prepare(const QString &query);
160 
162  void bindValue(const QString &placeholder, const QVariant &val);
163 
177  void bindValueNoNull(const QString &placeholder, const QVariant &val);
178 
180  void bindValues(const MSqlBindings &bindings);
181 
190  QVariant lastInsertId();
191 
194  bool Reconnect(void);
195 
196  // Thunks that allow us to make QSqlQuery private
197  QVariant value(int i) const { return QSqlQuery::value(i); }
198  QString executedQuery(void) const { return QSqlQuery::executedQuery(); }
199  QMap<QString, QVariant> boundValues(void) const
200  { return QSqlQuery::boundValues(); }
201  QSqlError lastError(void) const { return QSqlQuery::lastError(); }
202  int size(void) const { return QSqlQuery::size();}
203  bool isActive(void) const { return QSqlQuery::isActive(); }
204  QSqlRecord record(void) const { return QSqlQuery::record(); }
205  int numRowsAffected() const { return QSqlQuery::numRowsAffected(); }
206  void setForwardOnly(bool f) { QSqlQuery::setForwardOnly(f); }
207  bool isNull(int field) const { return QSqlQuery::isNull(field); }
208  const QSqlDriver *driver(void) const { return QSqlQuery::driver(); }
209  int at(void) const { return QSqlQuery::at(); }
210 
212  static bool testDBConnection();
213 
215  {
218  };
220  static MSqlQueryInfo InitCon(ConnectionReuse _reuse = kNormalConnection);
221 
223  static MSqlQueryInfo SchedCon();
224 
226  static MSqlQueryInfo ChannelCon();
227 
228  private:
229  // Only QSql::In is supported as a param type and only named params...
230  void bindValue(const QString&, const QVariant&, QSql::ParamType);
231  void bindValue(int, const QVariant&, QSql::ParamType);
232  void addBindValue(const QVariant&, QSql::ParamType = QSql::In);
233 
234  bool seekDebug(const char *type, bool result,
235  int where, bool relative) const;
236 
237  MSqlDatabase *m_db {nullptr};
238  bool m_isConnected {false};
239  bool m_returnConnection {false};
240  QString m_lastPreparedQuery; // holds a copy of the last prepared query
241 };
242 
243 #endif
MSqlBindings
QMap< QString, QVariant > MSqlBindings
typedef for a map of string -> string bindings for generic queries.
Definition: mythdbcon.h:97
MSqlQuery::isActive
bool isActive(void) const
Definition: mythdbcon.h:203
MSqlQuery
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:124
MSqlQuery::size
int size(void) const
Definition: mythdbcon.h:202
MDBManager::m_lock
QMutex m_lock
Definition: mythdbcon.h:72
MSqlQuery::isNull
bool isNull(int field) const
Definition: mythdbcon.h:207
MSqlDatabase::InitSessionVars
void InitSessionVars(void)
Definition: mythdbcon.cpp:267
DatabaseParams
Structure containing the basic Database parameters.
Definition: mythdbparams.h:10
mythbaseexp.h
MDBManager::m_inuseCount
QHash< QThread *, int > m_inuseCount
Definition: mythdbcon.h:77
MSqlQuery::record
QSqlRecord record(void) const
Definition: mythdbcon.h:204
MDBManager
DB connection pool, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:52
MSqlDatabase::KickDatabase
bool KickDatabase(void)
Definition: mythdbcon.cpp:242
MSqlQuery::value
QVariant value(int i) const
Definition: mythdbcon.h:197
MSqlDatabase::db
QSqlDatabase db(void) const
Definition: mythdbcon.h:40
MSqlQuery::boundValues
QMap< QString, QVariant > boundValues(void) const
Definition: mythdbcon.h:199
TestDatabase
MBASE_PUBLIC bool TestDatabase(const QString &dbHostName, const QString &dbUserName, QString dbPassword, QString dbName="mythconverg", int dbPort=3306)
Definition: mythdbcon.cpp:37
MSqlDatabase::MSqlDatabase
MSqlDatabase(QString name)
Definition: mythdbcon.cpp:75
MSqlEscapeAsAQuery
MBASE_PUBLIC void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings)
Given a partial query string and a bindings object, escape the string.
Definition: mythdbcon.cpp:928
MSqlDatabase::isOpen
bool isOpen(void)
Definition: mythdbcon.cpp:111
MDBManager::DBList
QList< MSqlDatabase * > DBList
Definition: mythdbcon.h:73
MBASE_PUBLIC
#define MBASE_PUBLIC
Definition: mythbaseexp.h:15
MSqlQueryInfo
MSqlDatabase Info, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:89
MSqlQuery::kDedicatedConnection
@ kDedicatedConnection
Definition: mythdbcon.h:216
MDBManager::m_pool
QHash< QThread *, DBList > m_pool
Definition: mythdbcon.h:74
MSqlDatabase::Reconnect
bool Reconnect(void)
Definition: mythdbcon.cpp:252
MDBManager::m_inuse
QHash< QThread *, MSqlDatabase * > m_inuse
Definition: mythdbcon.h:76
MDBManager::m_staticPool
QHash< QThread *, DBList > m_staticPool
Definition: mythdbcon.h:85
MSqlQueryInfo::db
MSqlDatabase * db
Definition: mythdbcon.h:91
MSqlDatabase::m_db
QSqlDatabase m_db
Definition: mythdbcon.h:46
bindValue
query bindValue(":CHANID", chanID)
prepare
query prepare("SELECT chanid, channum, callsign, name " "FROM channel WHERE chanid = :CHANID;")
MSqlQuery::setForwardOnly
void setForwardOnly(bool f)
Definition: mythdbcon.h:206
MSqlQuery::kNormalConnection
@ kNormalConnection
Definition: mythdbcon.h:217
MSqlDatabase::~MSqlDatabase
~MSqlDatabase(void)
Definition: mythdbcon.cpp:98
f
QTextStream t & f
Definition: mythplugins/mytharchive/mytharchivehelper/main.cpp:603
MSqlQuery::isConnected
bool isConnected(void) const
Only updated once during object creation.
Definition: mythdbcon.h:134
MSqlAddMoreBindings
MBASE_PUBLIC void MSqlAddMoreBindings(MSqlBindings &output, MSqlBindings &addfrom)
Add the entries in addfrom to the map in output.
Definition: mythdbcon.cpp:907
MSqlQuery::m_lastPreparedQuery
QString m_lastPreparedQuery
Definition: mythdbcon.h:240
MSqlDatabase
QSqlDatabase wrapper, used by MSqlQuery. Do not use directly.
Definition: mythdbcon.h:25
MSqlQuery::lastError
QSqlError lastError(void) const
Definition: mythdbcon.h:201
MSqlQueryInfo::returnConnection
bool returnConnection
Definition: mythdbcon.h:93
MSqlDatabase::m_lastDBKick
QDateTime m_lastDBKick
Definition: mythdbcon.h:47
MSqlQuery::executedQuery
QString executedQuery(void) const
Definition: mythdbcon.h:198
MSqlDatabase::GetConnectionName
QString GetConnectionName(void) const
Definition: mythdbcon.h:39
Reconnect
Definition: backendconnectionmanager.cpp:24
MSqlQuery::numRowsAffected
int numRowsAffected() const
Definition: mythdbcon.h:205
MSqlDatabase::m_name
QString m_name
Definition: mythdbcon.h:45
MSqlQueryInfo::qsqldb
QSqlDatabase qsqldb
Definition: mythdbcon.h:92
MSqlQuery::ConnectionReuse
ConnectionReuse
Definition: mythdbcon.h:214
query
MSqlQuery query(MSqlQuery::InitCon())
output
#define output
Definition: synaesthesia.cpp:220
MSqlDatabase::SetDBParams
void SetDBParams(const DatabaseParams &params)
Definition: mythdbcon.h:34
MSqlDatabase::OpenDatabase
bool OpenDatabase(bool skipdb=false)
Definition: mythdbcon.cpp:121
MSqlQuery::at
int at(void) const
Definition: mythdbcon.h:209
mythdbparams.h
MSqlQuery::driver
const QSqlDriver * driver(void) const
Definition: mythdbcon.h:208
MSqlDatabase::m_dbparms
DatabaseParams m_dbparms
Definition: mythdbcon.h:48