Go to the documentation of this file.
7 #include <QCoreApplication>
8 #include <QElapsedTimer>
30 #define DEBUG_RECONNECT 0
38 const QString& dbUserName,
45 if (dbHostName.isEmpty() || dbUserName.isEmpty())
53 dbparms.
m_dbName = std::move(dbName);
65 db->SetDBParams(dbparms);
67 ret = db->OpenDatabase(
true);
76 : m_name(
std::move(name))
78 if (!QSqlDatabase::isDriverAvailable(
"QMYSQL"))
80 LOG(VB_FLUSH, LOG_CRIT,
"FATAL: Unable to load the QT mysql driver, is it installed?");
85 m_db = QSqlDatabase::addDatabase(
"QMYSQL",
m_name);
86 LOG(VB_DATABASE, LOG_INFO,
"Database object created: " +
m_name);
88 if (!
m_db.isValid() ||
m_db.isOpenError())
91 LOG(VB_FLUSH, LOG_CRIT, QString(
"FATAL: Unable to create database object (%1), the installed QT driver may be invalid.").
arg(
m_name));
103 m_db = QSqlDatabase();
106 QSqlDatabase::removeDatabase(
m_name);
107 LOG(VB_DATABASE, LOG_INFO,
"Database object deleted: " +
m_name);
125 LOG(VB_GENERAL, LOG_ERR,
126 "MSqlDatabase::OpenDatabase(), db object is not valid!");
130 bool connected =
true;
162 m_db.setHostName(
"localhost");
165 m_db.setConnectOptions(QString(
"MYSQL_OPT_READ_TIMEOUT=300"));
167 connected =
m_db.open();
176 LOG(VB_GENERAL, LOG_INFO,
177 QString(
"Using WOL to wakeup database server (Try %1 of "
183 LOG(VB_GENERAL, LOG_ERR,
184 QString(
"Failed to run WOL command '%1'")
189 connected =
m_db.open();
194 LOG(VB_GENERAL, LOG_ERR,
195 "WOL failed, unable to connect to database!");
200 LOG(VB_DATABASE, LOG_INFO,
201 QString(
"[%1] Connected to database '%2' at host: %3")
215 bool have_schema =
false;
216 QString sql =
"SELECT COUNT(TABLE_NAME) "
217 " FROM INFORMATION_SCHEMA.TABLES "
218 " WHERE TABLE_SCHEMA = DATABASE() "
219 " AND TABLE_TYPE = 'BASE TABLE';";
235 LOG(VB_GENERAL, LOG_ERR, QString(
"[%1] Unable to connect to database!").
arg(
m_name));
249 return m_db.isOpen();
257 bool open =
m_db.isOpen();
260 LOG(VB_GENERAL, LOG_INFO,
"MySQL reconnected successfully");
270 m_db.exec(
"SET @@session.time_zone='+00:00'");
272 m_db.exec(
"SET @@session.sql_mode=''");
285 LOG(VB_GENERAL, LOG_CRIT,
286 "MDBManager exiting with connections still open");
306 db =
m_inuse[QThread::currentThread()];
321 LOG(VB_DATABASE, LOG_INFO,
334 m_inuse[QThread::currentThread()] = db;
350 if (db ==
m_inuse[QThread::currentThread()])
358 m_inuse[QThread::currentThread()] =
nullptr;
365 m_pool[QThread::currentThread()].push_front(db);
375 QMutexLocker locker(&
m_lock);
381 DBList::iterator it = list.begin();
383 uint purgedConnections = 0;
384 uint totalConnections = 0;
386 while (it != list.end())
389 if ((*it)->m_lastDBKick.secsTo(now) <=
kPurgeTimeout.count())
412 if (leaveOne && it == list.end() &&
413 purgedConnections > 0 &&
414 totalConnections == purgedConnections)
419 LOG(VB_GENERAL, LOG_INFO,
424 LOG(VB_DATABASE, LOG_INFO,
"Deleting idle DB connection...");
426 LOG(VB_DATABASE, LOG_INFO,
"Done deleting idle DB connection.");
429 list.push_front(newDb);
431 if (purgedConnections)
433 LOG(VB_DATABASE, LOG_INFO,
434 QString(
"Purged %1 idle of %2 total DB connections.")
435 .
arg(purgedConnections).
arg(totalConnections));
447 LOG(VB_GENERAL, LOG_INFO,
"New static DB connection" + name);
450 (*dbcon)->OpenDatabase();
452 if (!
m_staticPool[QThread::currentThread()].contains(*dbcon))
453 m_staticPool[QThread::currentThread()].push_back(*dbcon);
472 m_pool[QThread::currentThread()].clear();
475 for (
auto *conn : qAsConst(list))
477 LOG(VB_DATABASE, LOG_INFO,
478 "Closing DB connection named '" + conn->m_name +
"'");
486 while (!slist.isEmpty())
489 LOG(VB_DATABASE, LOG_INFO,
490 "Closing DB connection named '" + db->
m_name +
"'");
508 qi.
qsqldb = QSqlDatabase();
514 : QSqlQuery(QString(), qi.qsqldb)
528 if (dbmanager &&
m_db)
546 if (db->
m_db.hostName().isEmpty())
552 GetMythDB()->GetDBManager()->pushConnection(db);
613 LOG(VB_GENERAL, LOG_ERR,
614 "MSqlQuery::exec(void) called without a prepared query.");
619 if (
random() < RAND_MAX / 50)
621 LOG(VB_GENERAL, LOG_INFO,
622 "MSqlQuery disconnecting DB to test reconnection logic");
631 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
638 bool result = QSqlQuery::exec();
639 qint64 elapsed = timer.elapsed();
645 && QSqlQuery::lastError().nativeErrorCode() ==
"2006"
647 result = QSqlQuery::exec();
653 bool has_null_strings =
false;
655 for (MSqlBindings::iterator it =
tmp.begin(); it !=
tmp.end(); ++it)
657 if (it->type() != QVariant::String)
659 if (it->isNull() || it->toString().isNull())
661 has_null_strings =
true;
662 *it = QVariant(QString(
""));
665 if (has_null_strings)
669 result = QSqlQuery::exec();
670 elapsed = timer.elapsed();
674 LOG(VB_GENERAL, LOG_ERR,
675 QString(
"Original query failed, but resend with empty "
676 "strings in place of NULL strings worked. ") +
683 QString str = lastQuery();
687 if (!str.startsWith(
"INSERT INTO logging "))
697 str.replace(b.key(),
'\'' + b.value().toString() +
'\'');
700 LOG(VB_DATABASE, LOG_INFO,
701 QString(
"MSqlQuery::exec(%1) %2%3%4")
702 .
arg(
m_db->MSqlDatabase::GetConnectionName()).arg(str)
703 .arg(QString(
" <<<< Took %1ms").
arg(QString::number(elapsed)))
704 .
arg(isSelect() ? QString(
", Returned %1 row(s)")
724 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
728 bool result = QSqlQuery::exec(
query);
734 && QSqlQuery::lastError().nativeErrorCode() ==
"2006"
736 result = QSqlQuery::exec(
query);
738 LOG(VB_DATABASE, LOG_INFO,
739 QString(
"MSqlQuery::exec(%1) %2%3")
740 .
arg(
m_db->MSqlDatabase::GetConnectionName()).arg(
query)
741 .arg(isSelect() ? QString(
" <<<< Returns %1 row(s)")
748 int where,
bool relative)
const
753 QSqlRecord rec =
record();
755 for (
int i = 0; i < rec.count(); i++)
760 str.append(rec.fieldName(i) +
" = " +
764 if (QString(
"seek")==
type)
766 LOG(VB_DATABASE, LOG_DEBUG,
767 QString(
"MSqlQuery::seek(%1,%2,%3) Result: \"%4\"")
768 .
arg(
m_db->MSqlDatabase::GetConnectionName())
769 .arg(where).arg(relative)
774 LOG(VB_DATABASE, LOG_DEBUG,
775 QString(
"MSqlQuery::%1(%2) Result: \"%3\"")
785 return seekDebug(
"next", QSqlQuery::next(), 0,
false);
790 return seekDebug(
"previous", QSqlQuery::previous(), 0,
false);
795 return seekDebug(
"first", QSqlQuery::first(), 0,
false);
800 return seekDebug(
"last", QSqlQuery::last(), 0,
false);
805 return seekDebug(
"seek", QSqlQuery::seek(where, relative), where, relative);
820 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
837 && QSqlQuery::lastError().nativeErrorCode() ==
"2006"
841 if (!ok && !(
GetMythDB()->SuppressDBMessages()))
843 LOG(VB_GENERAL, LOG_ERR,
844 QString(
"Error preparing query: %1").
arg(
query));
845 LOG(VB_GENERAL, LOG_ERR,
858 bool isOpen = db->
isOpen();
860 GetMythDB()->GetDBManager()->pushConnection(db);
871 if ((val.type() == QVariant::String) && val.isNull())
881 MSqlBindings::const_iterator it;
882 for (it = bindings.begin(); it != bindings.end(); ++it)
890 return QSqlQuery::lastInsertId();
909 MSqlBindings::Iterator it;
910 for (it = addfrom.begin(); it != addfrom.end(); ++it)
912 output.insert(it.key(), it.value());
917 explicit Holder( QString hldr = QString(),
int pos = -1 )
933 QRegExp rx(QString::fromLatin1(
"'[^']*'|:([a-zA-Z0-9_]+)"));
935 QVector<Holder> holders;
938 while ((i = rx.indexIn(q, i)) != -1)
940 if (!rx.cap(1).isEmpty())
941 holders.append(
Holder(rx.cap(0), i));
942 i += rx.matchedLength();
948 for (i = holders.count() - 1; i >= 0; --i)
950 holder = holders[(
uint)i].m_holderName;
951 val = bindings[holder];
952 QSqlField
f(
"", val.type());
959 result.
driver()->formatValue(
f));
QMap< QString, QVariant > MSqlBindings
typedef for a map of string -> string bindings for generic queries.
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
QSqlQuery wrapper that fetches a DB connection from the connection pool.
void bindValueNoNull(const QString &placeholder, const QVariant &val)
Add a single binding, taking care not to set a NULL value.
QString m_dbHostName
database server
MSqlQuery(const MSqlQueryInfo &qi)
Get DB connection from pool.
void InitSessionVars(void)
static constexpr std::chrono::seconds kPurgeTimeout
static QString DBErrorMessage(const QSqlError &err)
MSqlDatabase * getSchedCon(void)
Structure containing the basic Database parameters.
bool operator==(const Holder &h) const
QHash< QThread *, int > m_inuseCount
QSqlRecord record(void) const
void bindValues(const MSqlBindings &bindings)
Add all the bindings in the passed in bindings.
void MSqlAddMoreBindings(MSqlBindings &output, MSqlBindings &addfrom)
Add the entries in addfrom to the map in output.
DB connection pool, used by MSqlQuery. Do not use directly.
QVariant lastInsertId()
Return the id of the last inserted row.
QVariant value(int i) const
arg(title).arg(filename).arg(doDelete))
QSqlDatabase db(void) const
bool TestDatabase(const QString &dbHostName, const QString &dbUserName, QString dbPassword, QString dbName, int dbPort)
void PurgeIdleConnections(bool leaveOne=false)
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
QMap< QString, QVariant > boundValues(void) const
#define LOG(_MASK_, _LEVEL_, _QSTRING_)
Holder(QString hldr=QString(), int pos=-1)
MSqlDatabase * getChannelCon(void)
MSqlDatabase(QString name)
QDateTime current(bool stripped)
Returns current Date and Time in UTC.
std::chrono::seconds m_wolReconnect
seconds to wait for reconnect
unsigned sleep(unsigned int x)
static void InitMSqlQueryInfo(MSqlQueryInfo &qi)
QString toString(MarkTypes type)
int m_dbPort
database port
bool previous(void)
Wrap QSqlQuery::previous() so we can display the query results.
QList< MSqlDatabase * > DBList
bool Reconnect(void)
Reconnects server and re-prepares and re-binds the last prepared query.
void CloseDatabases(void)
MSqlDatabase Info, used by MSqlQuery. Do not use directly.
bool first(void)
Wrap QSqlQuery::first() so we can display the query results.
static QString GetError(const QString &where, const MSqlQuery &query)
QHash< QThread *, DBList > m_pool
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
QHash< QThread *, MSqlDatabase * > m_inuse
static bool testDBConnection()
Checks DB connection + login (login info via Mythcontext)
QHash< QThread *, DBList > m_staticPool
bool MythWakeup(const QString &wakeUpCommand, uint flags, std::chrono::seconds timeout)
query bindValue(":CHANID", chanID)
static MSqlQueryInfo SchedCon()
Returns dedicated connection. (Required for using temporary SQL tables.)
query prepare("SELECT chanid, channum, callsign, name " "FROM channel WHERE chanid = :CHANID;")
void setForwardOnly(bool f)
bool seek(int where, bool relative=false)
Wrap QSqlQuery::seek(int,bool)
QString m_dbPassword
DB password.
int m_wolRetry
times to retry to reconnect
static bool resolveLinkLocal(QString &host, int port, std::chrono::milliseconds timeLimit=30s)
Convenience method to resolve link-local address.
QString m_dbName
database name
void MSqlEscapeAsAQuery(QString &query, const MSqlBindings &bindings)
Given a partial query string and a bindings object, escape the string.
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
MSqlDatabase * getStaticCon(MSqlDatabase **dbcon, const QString &name)
MSqlDatabase * popConnection(bool reuse)
QString m_lastPreparedQuery
QSqlDatabase wrapper, used by MSqlQuery. Do not use directly.
static MSqlQueryInfo ChannelCon()
Returns dedicated connection. (Required for using temporary SQL tables.)
~MSqlQuery()
Returns connection to pool.
#define GENERIC_EXIT_DB_ERROR
Database error.
#define VERBOSE_LEVEL_CHECK(_MASK_, _LEVEL_)
QString m_wolCommand
command to use for wake-on-lan
static long int random(void)
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
QString m_dbUserName
DB user name.
void pushConnection(MSqlDatabase *db)
bool operator!=(const Holder &h) const
bool seekDebug(const char *type, bool result, int where, bool relative) const
bool IsWOLAllowed() const
MSqlDatabase * m_channelCon
bool m_wolEnabled
true if wake-on-lan params are used
MSqlDatabase * m_schedCon
MSqlQuery query(MSqlQuery::InitCon())
bool last(void)
Wrap QSqlQuery::last() so we can display the query results.
bool OpenDatabase(bool skipdb=false)
const QSqlDriver * driver(void) const
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.