7 #include <QCoreApplication>
8 #include <QElapsedTimer>
9 #include <QRegularExpression>
32 #define DEBUG_RECONNECT 0
42 const QString& dbUserName,
51 QMutexLocker locker(&
sMutex);
54 if (dbHostName.isEmpty() || dbUserName.isEmpty())
62 dbparms.
m_dbName = std::move(dbName);
74 db->SetDBParams(dbparms);
76 ret = db->OpenDatabase(
true);
87 if (!QSqlDatabase::isDriverAvailable(
m_driver))
89 LOG(VB_FLUSH, LOG_CRIT,
90 QString(
"FATAL: Unable to load the QT %1 driver, is it installed?")
97 LOG(VB_DATABASE, LOG_INFO,
"Database object created: " +
m_name);
99 if (!
m_db.isValid() ||
m_db.isOpenError())
102 LOG(VB_FLUSH, LOG_CRIT, QString(
"FATAL: Unable to create database object (%1), the installed QT driver may be invalid.").arg(
m_name));
114 m_db = QSqlDatabase();
117 QSqlDatabase::removeDatabase(
m_name);
118 LOG(VB_DATABASE, LOG_INFO,
"Database object deleted: " +
m_name);
138 LOG(VB_GENERAL, LOG_ERR,
139 "MSqlDatabase::OpenDatabase(), db object is not valid!");
143 bool connected =
true;
175 m_db.setHostName(
"localhost");
178 m_db.setConnectOptions(QString(
"MYSQL_OPT_READ_TIMEOUT=300"));
180 connected =
m_db.open();
189 LOG(VB_GENERAL, LOG_INFO,
190 QString(
"Using WOL to wakeup database server (Try %1 of "
196 LOG(VB_GENERAL, LOG_ERR,
197 QString(
"Failed to run WOL command '%1'")
202 connected =
m_db.open();
207 LOG(VB_GENERAL, LOG_ERR,
208 "WOL failed, unable to connect to database!");
213 LOG(VB_DATABASE, LOG_INFO,
214 QString(
"[%1] Connected to database '%2' at host: %3")
227 bool have_schema =
false;
228 QString sql =
"SELECT COUNT(TABLE_NAME) "
229 " FROM INFORMATION_SCHEMA.TABLES "
230 " WHERE TABLE_SCHEMA = DATABASE() "
231 " AND TABLE_TYPE = 'BASE TABLE';";
235 QSqlQuery query =
m_db.exec(sql);
237 have_schema = query.value(0).toInt() > 1;
247 LOG(VB_GENERAL, LOG_ERR, QString(
"[%1] Unable to connect to database!").arg(
m_name));
261 return m_db.isOpen();
269 bool open =
m_db.isOpen();
272 LOG(VB_GENERAL, LOG_INFO,
"MySQL reconnected successfully");
282 m_db.exec(
"SET @@session.time_zone='+00:00'");
284 m_db.exec(
"SET @@session.sql_mode=''");
297 LOG(VB_GENERAL, LOG_CRIT,
298 "MDBManager exiting with connections still open");
318 db =
m_inuse[QThread::currentThread()];
335 LOG(VB_DATABASE, LOG_INFO,
336 QString(
"New DB connection, total: %1").arg(
m_connCount));
348 m_inuse[QThread::currentThread()] = db;
364 if (db ==
m_inuse[QThread::currentThread()])
372 m_inuse[QThread::currentThread()] =
nullptr;
379 m_pool[QThread::currentThread()].push_front(db);
389 QMutexLocker locker(&
m_lock);
395 DBList::iterator it = list.begin();
397 uint purgedConnections = 0;
398 uint totalConnections = 0;
400 while (it != list.end())
403 if ((*it)->m_lastDBKick.secsTo(now) <=
kPurgeTimeout.count())
426 if (leaveOne && it == list.end() &&
427 purgedConnections > 0 &&
428 totalConnections == purgedConnections)
433 LOG(VB_GENERAL, LOG_INFO,
434 QString(
"New DB connection, total: %1").arg(
m_connCount));
438 LOG(VB_DATABASE, LOG_INFO,
"Deleting idle DB connection...");
440 LOG(VB_DATABASE, LOG_INFO,
"Done deleting idle DB connection.");
443 list.push_front(newDb);
445 if (purgedConnections)
447 LOG(VB_DATABASE, LOG_INFO,
448 QString(
"Purged %1 idle of %2 total DB connections.")
449 .arg(purgedConnections).arg(totalConnections));
461 LOG(VB_GENERAL, LOG_INFO,
"New static DB connection" + name);
464 (*dbcon)->OpenDatabase();
466 if (!
m_staticPool[QThread::currentThread()].contains(*dbcon))
467 m_staticPool[QThread::currentThread()].push_back(*dbcon);
486 m_pool[QThread::currentThread()].clear();
489 for (
auto *conn : qAsConst(list))
491 LOG(VB_DATABASE, LOG_INFO,
492 "Closing DB connection named '" + conn->m_name +
"'");
500 while (!slist.isEmpty())
503 LOG(VB_DATABASE, LOG_INFO,
504 "Closing DB connection named '" + db->
m_name +
"'");
522 qi.
qsqldb = QSqlDatabase();
528 : QSqlQuery(QString(), qi.qsqldb)
542 if (dbmanager &&
m_db)
560 if (db->
m_db.hostName().isEmpty())
566 GetMythDB()->GetDBManager()->pushConnection(db);
627 LOG(VB_GENERAL, LOG_ERR,
628 "MSqlQuery::exec(void) called without a prepared query.");
635 LOG(VB_GENERAL, LOG_INFO,
636 "MSqlQuery disconnecting DB to test reconnection logic");
645 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
652 bool result = QSqlQuery::exec();
653 qint64 elapsed = timer.elapsed();
656 result = QSqlQuery::exec();
661 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
664 QVariantList
tmp = QSqlQuery::boundValues();
666 bool has_null_strings =
false;
668 for (
auto it =
tmp.begin(); it !=
tmp.end(); ++it)
670 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
671 auto type =
static_cast<QMetaType::Type
>(it->type());
673 auto type = it->typeId();
675 if (
type != QMetaType::QString)
677 if (it->isNull() || it->toString().isNull())
679 has_null_strings =
true;
680 *it = QVariant(QString(
""));
683 if (has_null_strings)
685 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
688 for (
int i = 0; i < static_cast<int>(
tmp.size()); i++)
689 QSqlQuery::bindValue(i,
tmp.at(i));
692 result = QSqlQuery::exec();
693 elapsed = timer.elapsed();
697 LOG(VB_GENERAL, LOG_ERR,
698 QString(
"Original query failed, but resend with empty "
699 "strings in place of NULL strings worked. ") +
706 QString str = lastQuery();
712 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
717 str.replace(b.key(),
'\'' + b.value().toString() +
'\'');
721 static const QRegularExpression placeholders {
"(:\\w+)" };
722 auto match = placeholders.match(str);
723 while (match.hasMatch())
725 str.replace(match.capturedStart(), match.capturedLength(),
728 :
'\'' + b.takeFirst().toString() +
'\'');
729 match = placeholders.match(str);
733 LOG(VB_DATABASE, LOG_INFO,
734 QString(
"MSqlQuery::exec(%1) %2%3%4")
735 .arg(
m_db->MSqlDatabase::GetConnectionName(), str,
736 QString(
" <<<< Took %1ms").arg(QString::number(elapsed)),
738 ? QString(
", Returned %1 row(s)").arg(
size())
757 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
761 bool result = QSqlQuery::exec(query);
764 result = QSqlQuery::exec(query);
766 LOG(VB_DATABASE, LOG_INFO,
767 QString(
"MSqlQuery::exec(%1) %2%3")
768 .arg(
m_db->MSqlDatabase::GetConnectionName(), query,
770 ? QString(
" <<<< Returns %1 row(s)").arg(
size())
777 int where,
bool relative)
const
782 QSqlRecord rec =
record();
784 for (
int i = 0; i < rec.count(); i++)
789 str.append(rec.fieldName(i) +
" = " +
793 if (QString(
"seek")==
type)
795 LOG(VB_DATABASE, LOG_DEBUG,
796 QString(
"MSqlQuery::seek(%1,%2,%3) Result: \"%4\"")
797 .arg(
m_db->MSqlDatabase::GetConnectionName())
798 .arg(where).arg(relative)
803 LOG(VB_DATABASE, LOG_DEBUG,
804 QString(
"MSqlQuery::%1(%2) Result: \"%3\"")
805 .arg(
type,
m_db->MSqlDatabase::GetConnectionName(), str));
813 return seekDebug(
"next", QSqlQuery::next(), 0,
false);
818 return seekDebug(
"previous", QSqlQuery::previous(), 0,
false);
823 return seekDebug(
"first", QSqlQuery::first(), 0,
false);
828 return seekDebug(
"last", QSqlQuery::last(), 0,
false);
833 return seekDebug(
"seek", QSqlQuery::seek(where, relative), where, relative);
848 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
859 bool ok = QSqlQuery::prepare(query);
864 if (!ok && !(
GetMythDB()->SuppressDBMessages()))
866 LOG(VB_GENERAL, LOG_ERR,
867 QString(
"Error preparing query: %1").arg(query));
868 LOG(VB_GENERAL, LOG_ERR,
881 bool isOpen = db->
isOpen();
883 GetMythDB()->GetDBManager()->pushConnection(db);
889 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
890 if (
static_cast<QMetaType::Type
>(val.type()) == QMetaType::QDateTime)
892 QSqlQuery::bindValue(placeholder,
898 QSqlQuery::bindValue(placeholder, val, QSql::In);
903 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
904 auto type =
static_cast<QMetaType::Type
>(val.type());
906 auto type = val.typeId();
908 if (
type == QMetaType::QString && val.toString().isNull())
910 QSqlQuery::bindValue(placeholder, QString(
""), QSql::In);
913 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
914 if (
type == QMetaType::QDateTime)
916 QSqlQuery::bindValue(placeholder,
922 QSqlQuery::bindValue(placeholder, val, QSql::In);
927 MSqlBindings::const_iterator it;
928 for (it = bindings.begin(); it != bindings.end(); ++it)
936 return QSqlQuery::lastInsertId();
945 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
951 QVariantList
tmp = QSqlQuery::boundValues();
954 for (
int i = 0; i < static_cast<int>(
tmp.size()); i++)
955 QSqlQuery::bindValue(i,
tmp.at(i));
968 static QStringList kLostConnectionCodes = {
"2006",
"2013",
"4031" };
970 QString error_code = QSqlQuery::lastError().nativeErrorCode();
973 LOG(VB_GENERAL, LOG_DEBUG, QString(
"SQL Native Error Code: %1")
978 return (kLostConnectionCodes.contains(error_code) &&
Reconnect());
984 MSqlBindings::Iterator it;
985 for (it = addfrom.begin(); it != addfrom.end(); ++it)
987 output.insert(it.key(), it.value());
992 explicit Holder( QString hldr = QString(),
int pos = -1 )
1007 static const QRegularExpression rx {
"('[^']+'|:\\w+)",
1008 QRegularExpression::UseUnicodePropertiesOption};
1010 QVector<Holder> holders;
1012 auto matchIter = rx.globalMatch(query);
1013 while (matchIter.hasNext())
1015 auto match = matchIter.next();
1016 if (match.capturedLength(1) > 0)
1017 holders.append(
Holder(match.captured(), match.capturedStart()));
1023 for (
int i = holders.count() - 1; i >= 0; --i)
1025 holder = holders[(
uint)i].m_holderName;
1026 val = bindings[holder];
1027 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
1028 QSqlField f(
"", val.type());
1030 QSqlField f(
"", val.metaType());
1037 query = query.replace((
uint)holders[(
uint)i].m_holderPos, holder.length(),
1038 result.
driver()->formatValue(f));