7 #include <QCoreApplication>
8 #include <QElapsedTimer>
9 #include <QRegularExpression>
31 #define DEBUG_RECONNECT 0
39 const QString& dbUserName,
46 if (dbHostName.isEmpty() || dbUserName.isEmpty())
54 dbparms.
m_dbName = std::move(dbName);
66 db->SetDBParams(dbparms);
68 ret = db->OpenDatabase(
true);
77 : m_name(
std::move(name))
79 if (!QSqlDatabase::isDriverAvailable(
"QMYSQL"))
81 LOG(VB_FLUSH, LOG_CRIT,
"FATAL: Unable to load the QT mysql driver, is it installed?");
86 m_db = QSqlDatabase::addDatabase(
"QMYSQL",
m_name);
87 LOG(VB_DATABASE, LOG_INFO,
"Database object created: " +
m_name);
89 if (!
m_db.isValid() ||
m_db.isOpenError())
92 LOG(VB_FLUSH, LOG_CRIT, QString(
"FATAL: Unable to create database object (%1), the installed QT driver may be invalid.").arg(
m_name));
104 m_db = QSqlDatabase();
107 QSqlDatabase::removeDatabase(
m_name);
108 LOG(VB_DATABASE, LOG_INFO,
"Database object deleted: " +
m_name);
126 LOG(VB_GENERAL, LOG_ERR,
127 "MSqlDatabase::OpenDatabase(), db object is not valid!");
131 bool connected =
true;
163 m_db.setHostName(
"localhost");
166 m_db.setConnectOptions(QString(
"MYSQL_OPT_READ_TIMEOUT=300"));
168 connected =
m_db.open();
177 LOG(VB_GENERAL, LOG_INFO,
178 QString(
"Using WOL to wakeup database server (Try %1 of "
184 LOG(VB_GENERAL, LOG_ERR,
185 QString(
"Failed to run WOL command '%1'")
190 connected =
m_db.open();
195 LOG(VB_GENERAL, LOG_ERR,
196 "WOL failed, unable to connect to database!");
201 LOG(VB_DATABASE, LOG_INFO,
202 QString(
"[%1] Connected to database '%2' at host: %3")
204 .arg(
m_db.databaseName()).arg(
m_db.hostName()));
216 bool have_schema =
false;
217 QString sql =
"SELECT COUNT(TABLE_NAME) "
218 " FROM INFORMATION_SCHEMA.TABLES "
219 " WHERE TABLE_SCHEMA = DATABASE() "
220 " AND TABLE_TYPE = 'BASE TABLE';";
224 QSqlQuery query =
m_db.exec(sql);
226 have_schema = query.value(0).toInt() > 1;
236 LOG(VB_GENERAL, LOG_ERR, QString(
"[%1] Unable to connect to database!").arg(
m_name));
250 return m_db.isOpen();
258 bool open =
m_db.isOpen();
261 LOG(VB_GENERAL, LOG_INFO,
"MySQL reconnected successfully");
271 m_db.exec(
"SET @@session.time_zone='+00:00'");
273 m_db.exec(
"SET @@session.sql_mode=''");
286 LOG(VB_GENERAL, LOG_CRIT,
287 "MDBManager exiting with connections still open");
307 db =
m_inuse[QThread::currentThread()];
322 LOG(VB_DATABASE, LOG_INFO,
323 QString(
"New DB connection, total: %1").arg(
m_connCount));
335 m_inuse[QThread::currentThread()] = db;
351 if (db ==
m_inuse[QThread::currentThread()])
359 m_inuse[QThread::currentThread()] =
nullptr;
366 m_pool[QThread::currentThread()].push_front(db);
376 QMutexLocker locker(&
m_lock);
382 DBList::iterator it = list.begin();
384 uint purgedConnections = 0;
385 uint totalConnections = 0;
387 while (it != list.end())
390 if ((*it)->m_lastDBKick.secsTo(now) <=
kPurgeTimeout.count())
413 if (leaveOne && it == list.end() &&
414 purgedConnections > 0 &&
415 totalConnections == purgedConnections)
420 LOG(VB_GENERAL, LOG_INFO,
421 QString(
"New DB connection, total: %1").arg(
m_connCount));
425 LOG(VB_DATABASE, LOG_INFO,
"Deleting idle DB connection...");
427 LOG(VB_DATABASE, LOG_INFO,
"Done deleting idle DB connection.");
430 list.push_front(newDb);
432 if (purgedConnections)
434 LOG(VB_DATABASE, LOG_INFO,
435 QString(
"Purged %1 idle of %2 total DB connections.")
436 .arg(purgedConnections).arg(totalConnections));
448 LOG(VB_GENERAL, LOG_INFO,
"New static DB connection" + name);
451 (*dbcon)->OpenDatabase();
453 if (!
m_staticPool[QThread::currentThread()].contains(*dbcon))
454 m_staticPool[QThread::currentThread()].push_back(*dbcon);
473 m_pool[QThread::currentThread()].clear();
476 for (
auto *conn : qAsConst(list))
478 LOG(VB_DATABASE, LOG_INFO,
479 "Closing DB connection named '" + conn->m_name +
"'");
487 while (!slist.isEmpty())
490 LOG(VB_DATABASE, LOG_INFO,
491 "Closing DB connection named '" + db->
m_name +
"'");
509 qi.
qsqldb = QSqlDatabase();
515 : QSqlQuery(QString(), qi.qsqldb)
529 if (dbmanager &&
m_db)
547 if (db->
m_db.hostName().isEmpty())
553 GetMythDB()->GetDBManager()->pushConnection(db);
614 LOG(VB_GENERAL, LOG_ERR,
615 "MSqlQuery::exec(void) called without a prepared query.");
620 if (
random() < RAND_MAX / 50)
622 LOG(VB_GENERAL, LOG_INFO,
623 "MSqlQuery disconnecting DB to test reconnection logic");
632 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
639 bool result = QSqlQuery::exec();
640 qint64 elapsed = timer.elapsed();
646 && QSqlQuery::lastError().nativeErrorCode() ==
"2006"
648 result = QSqlQuery::exec();
653 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
656 QVariantList
tmp = QSqlQuery::boundValues();
658 bool has_null_strings =
false;
660 for (
auto it =
tmp.begin(); it !=
tmp.end(); ++it)
662 if (it->type() != QVariant::String)
664 if (it->isNull() || it->toString().isNull())
666 has_null_strings =
true;
667 *it = QVariant(QString(
""));
670 if (has_null_strings)
672 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
675 for (
int i = 0; i < static_cast<int>(
tmp.size()); i++)
676 QSqlQuery::bindValue(i,
tmp.at(i));
679 result = QSqlQuery::exec();
680 elapsed = timer.elapsed();
684 LOG(VB_GENERAL, LOG_ERR,
685 QString(
"Original query failed, but resend with empty "
686 "strings in place of NULL strings worked. ") +
693 QString str = lastQuery();
697 if (!str.startsWith(
"INSERT INTO logging "))
703 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
708 str.replace(b.key(),
'\'' + b.value().toString() +
'\'');
712 static const QRegularExpression placeholders {
"(:\\w+)" };
713 auto match = placeholders.match(str);
714 while (match.hasMatch())
716 str.replace(match.capturedStart(), match.capturedLength(),
719 :
'\'' + b.takeFirst().toString() +
'\'');
720 match = placeholders.match(str);
724 LOG(VB_DATABASE, LOG_INFO,
725 QString(
"MSqlQuery::exec(%1) %2%3%4")
726 .arg(
m_db->MSqlDatabase::GetConnectionName()).arg(str)
727 .arg(QString(
" <<<< Took %1ms").arg(QString::number(elapsed)))
728 .arg(isSelect() ? QString(
", Returned %1 row(s)")
729 .arg(
size()) : QString()));
748 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
752 bool result = QSqlQuery::exec(query);
758 && QSqlQuery::lastError().nativeErrorCode() ==
"2006"
760 result = QSqlQuery::exec(query);
762 LOG(VB_DATABASE, LOG_INFO,
763 QString(
"MSqlQuery::exec(%1) %2%3")
764 .arg(
m_db->MSqlDatabase::GetConnectionName()).arg(query)
765 .arg(isSelect() ? QString(
" <<<< Returns %1 row(s)")
766 .arg(
size()) : QString()));
772 int where,
bool relative)
const
777 QSqlRecord rec =
record();
779 for (
int i = 0; i < rec.count(); i++)
784 str.append(rec.fieldName(i) +
" = " +
788 if (QString(
"seek")==
type)
790 LOG(VB_DATABASE, LOG_DEBUG,
791 QString(
"MSqlQuery::seek(%1,%2,%3) Result: \"%4\"")
792 .arg(
m_db->MSqlDatabase::GetConnectionName())
793 .arg(where).arg(relative)
798 LOG(VB_DATABASE, LOG_DEBUG,
799 QString(
"MSqlQuery::%1(%2) Result: \"%3\"")
800 .arg(
type).arg(
m_db->MSqlDatabase::GetConnectionName())
809 return seekDebug(
"next", QSqlQuery::next(), 0,
false);
814 return seekDebug(
"previous", QSqlQuery::previous(), 0,
false);
819 return seekDebug(
"first", QSqlQuery::first(), 0,
false);
824 return seekDebug(
"last", QSqlQuery::last(), 0,
false);
829 return seekDebug(
"seek", QSqlQuery::seek(where, relative), where, relative);
844 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
855 bool ok = QSqlQuery::prepare(query);
861 && QSqlQuery::lastError().nativeErrorCode() ==
"2006"
865 if (!ok && !(
GetMythDB()->SuppressDBMessages()))
867 LOG(VB_GENERAL, LOG_ERR,
868 QString(
"Error preparing query: %1").arg(query));
869 LOG(VB_GENERAL, LOG_ERR,
882 bool isOpen = db->
isOpen();
884 GetMythDB()->GetDBManager()->pushConnection(db);
890 QSqlQuery::bindValue(placeholder, val, QSql::In);
895 if ((val.type() == QVariant::String) && val.isNull())
897 QSqlQuery::bindValue(placeholder, QString(
""), QSql::In);
900 QSqlQuery::bindValue(placeholder, val, QSql::In);
905 MSqlBindings::const_iterator it;
906 for (it = bindings.begin(); it != bindings.end(); ++it)
914 return QSqlQuery::lastInsertId();
923 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
929 QVariantList
tmp = QSqlQuery::boundValues();
932 for (
int i = 0; i < static_cast<int>(
tmp.size()); i++)
933 QSqlQuery::bindValue(i,
tmp.at(i));
941 MSqlBindings::Iterator it;
942 for (it = addfrom.begin(); it != addfrom.end(); ++it)
944 output.insert(it.key(), it.value());
949 explicit Holder( QString hldr = QString(),
int pos = -1 )
964 QRegularExpression rx {
"('[^']+'|:\\w+)",
965 QRegularExpression::UseUnicodePropertiesOption};
967 QVector<Holder> holders;
969 auto matchIter = rx.globalMatch(query);
970 while (matchIter.hasNext())
972 auto match = matchIter.next();
973 if (match.capturedLength(1) > 0)
974 holders.append(
Holder(match.captured(), match.capturedStart()));
980 for (
int i = holders.count() - 1; i >= 0; --i)
982 holder = holders[(
uint)i].m_holderName;
983 val = bindings[holder];
984 QSqlField f(
"", val.type());
990 query = query.replace((
uint)holders[(
uint)i].m_holderPos, holder.length(),
991 result.
driver()->formatValue(f));