7 #include <QCoreApplication>
8 #include <QElapsedTimer>
9 #include <QRegularExpression>
32 #define DEBUG_RECONNECT 0
40 const QString& dbUserName,
47 if (dbHostName.isEmpty() || dbUserName.isEmpty())
55 dbparms.
m_dbName = std::move(dbName);
67 db->SetDBParams(dbparms);
69 ret = db->OpenDatabase(
true);
80 if (!QSqlDatabase::isDriverAvailable(
m_driver))
82 LOG(VB_FLUSH, LOG_CRIT,
83 QString(
"FATAL: Unable to load the QT %1 driver, is it installed?")
90 LOG(VB_DATABASE, LOG_INFO,
"Database object created: " +
m_name);
92 if (!
m_db.isValid() ||
m_db.isOpenError())
95 LOG(VB_FLUSH, LOG_CRIT, QString(
"FATAL: Unable to create database object (%1), the installed QT driver may be invalid.").arg(
m_name));
107 m_db = QSqlDatabase();
110 QSqlDatabase::removeDatabase(
m_name);
111 LOG(VB_DATABASE, LOG_INFO,
"Database object deleted: " +
m_name);
129 LOG(VB_GENERAL, LOG_ERR,
130 "MSqlDatabase::OpenDatabase(), db object is not valid!");
134 bool connected =
true;
166 m_db.setHostName(
"localhost");
169 m_db.setConnectOptions(QString(
"MYSQL_OPT_READ_TIMEOUT=300"));
171 connected =
m_db.open();
180 LOG(VB_GENERAL, LOG_INFO,
181 QString(
"Using WOL to wakeup database server (Try %1 of "
187 LOG(VB_GENERAL, LOG_ERR,
188 QString(
"Failed to run WOL command '%1'")
193 connected =
m_db.open();
198 LOG(VB_GENERAL, LOG_ERR,
199 "WOL failed, unable to connect to database!");
204 LOG(VB_DATABASE, LOG_INFO,
205 QString(
"[%1] Connected to database '%2' at host: %3")
218 bool have_schema =
false;
219 QString sql =
"SELECT COUNT(TABLE_NAME) "
220 " FROM INFORMATION_SCHEMA.TABLES "
221 " WHERE TABLE_SCHEMA = DATABASE() "
222 " AND TABLE_TYPE = 'BASE TABLE';";
226 QSqlQuery query =
m_db.exec(sql);
228 have_schema = query.value(0).toInt() > 1;
238 LOG(VB_GENERAL, LOG_ERR, QString(
"[%1] Unable to connect to database!").arg(
m_name));
252 return m_db.isOpen();
260 bool open =
m_db.isOpen();
263 LOG(VB_GENERAL, LOG_INFO,
"MySQL reconnected successfully");
273 m_db.exec(
"SET @@session.time_zone='+00:00'");
275 m_db.exec(
"SET @@session.sql_mode=''");
288 LOG(VB_GENERAL, LOG_CRIT,
289 "MDBManager exiting with connections still open");
309 db =
m_inuse[QThread::currentThread()];
326 LOG(VB_DATABASE, LOG_INFO,
327 QString(
"New DB connection, total: %1").arg(
m_connCount));
339 m_inuse[QThread::currentThread()] = db;
355 if (db ==
m_inuse[QThread::currentThread()])
363 m_inuse[QThread::currentThread()] =
nullptr;
370 m_pool[QThread::currentThread()].push_front(db);
380 QMutexLocker locker(&
m_lock);
386 DBList::iterator it = list.begin();
388 uint purgedConnections = 0;
389 uint totalConnections = 0;
391 while (it != list.end())
394 if ((*it)->m_lastDBKick.secsTo(now) <=
kPurgeTimeout.count())
417 if (leaveOne && it == list.end() &&
418 purgedConnections > 0 &&
419 totalConnections == purgedConnections)
424 LOG(VB_GENERAL, LOG_INFO,
425 QString(
"New DB connection, total: %1").arg(
m_connCount));
429 LOG(VB_DATABASE, LOG_INFO,
"Deleting idle DB connection...");
431 LOG(VB_DATABASE, LOG_INFO,
"Done deleting idle DB connection.");
434 list.push_front(newDb);
436 if (purgedConnections)
438 LOG(VB_DATABASE, LOG_INFO,
439 QString(
"Purged %1 idle of %2 total DB connections.")
440 .arg(purgedConnections).arg(totalConnections));
452 LOG(VB_GENERAL, LOG_INFO,
"New static DB connection" + name);
455 (*dbcon)->OpenDatabase();
457 if (!
m_staticPool[QThread::currentThread()].contains(*dbcon))
458 m_staticPool[QThread::currentThread()].push_back(*dbcon);
477 m_pool[QThread::currentThread()].clear();
480 for (
auto *conn : qAsConst(list))
482 LOG(VB_DATABASE, LOG_INFO,
483 "Closing DB connection named '" + conn->m_name +
"'");
491 while (!slist.isEmpty())
494 LOG(VB_DATABASE, LOG_INFO,
495 "Closing DB connection named '" + db->
m_name +
"'");
513 qi.
qsqldb = QSqlDatabase();
519 : QSqlQuery(QString(), qi.qsqldb)
533 if (dbmanager &&
m_db)
551 if (db->
m_db.hostName().isEmpty())
557 GetMythDB()->GetDBManager()->pushConnection(db);
618 LOG(VB_GENERAL, LOG_ERR,
619 "MSqlQuery::exec(void) called without a prepared query.");
626 LOG(VB_GENERAL, LOG_INFO,
627 "MSqlQuery disconnecting DB to test reconnection logic");
636 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
643 bool result = QSqlQuery::exec();
644 qint64 elapsed = timer.elapsed();
647 result = QSqlQuery::exec();
652 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
655 QVariantList
tmp = QSqlQuery::boundValues();
657 bool has_null_strings =
false;
659 for (
auto it =
tmp.begin(); it !=
tmp.end(); ++it)
661 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
662 auto type =
static_cast<QMetaType::Type
>(it->type());
664 auto type = it->typeId();
666 if (
type != QMetaType::QString)
668 if (it->isNull() || it->toString().isNull())
670 has_null_strings =
true;
671 *it = QVariant(QString(
""));
674 if (has_null_strings)
676 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
679 for (
int i = 0; i < static_cast<int>(
tmp.size()); i++)
680 QSqlQuery::bindValue(i,
tmp.at(i));
683 result = QSqlQuery::exec();
684 elapsed = timer.elapsed();
688 LOG(VB_GENERAL, LOG_ERR,
689 QString(
"Original query failed, but resend with empty "
690 "strings in place of NULL strings worked. ") +
697 QString str = lastQuery();
701 if (!str.startsWith(
"INSERT INTO logging "))
707 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
712 str.replace(b.key(),
'\'' + b.value().toString() +
'\'');
716 static const QRegularExpression placeholders {
"(:\\w+)" };
717 auto match = placeholders.match(str);
718 while (match.hasMatch())
720 str.replace(match.capturedStart(), match.capturedLength(),
723 :
'\'' + b.takeFirst().toString() +
'\'');
724 match = placeholders.match(str);
728 LOG(VB_DATABASE, LOG_INFO,
729 QString(
"MSqlQuery::exec(%1) %2%3%4")
730 .arg(
m_db->MSqlDatabase::GetConnectionName(), str,
731 QString(
" <<<< Took %1ms").arg(QString::number(elapsed)),
733 ? QString(
", Returned %1 row(s)").arg(
size())
753 LOG(VB_GENERAL, LOG_INFO,
"MySQL server disconnected");
757 bool result = QSqlQuery::exec(query);
760 result = QSqlQuery::exec(query);
762 LOG(VB_DATABASE, LOG_INFO,
763 QString(
"MSqlQuery::exec(%1) %2%3")
764 .arg(
m_db->MSqlDatabase::GetConnectionName(), query,
766 ? QString(
" <<<< Returns %1 row(s)").arg(
size())
773 int where,
bool relative)
const
778 QSqlRecord rec =
record();
780 for (
int i = 0; i < rec.count(); i++)
785 str.append(rec.fieldName(i) +
" = " +
789 if (QString(
"seek")==
type)
791 LOG(VB_DATABASE, LOG_DEBUG,
792 QString(
"MSqlQuery::seek(%1,%2,%3) Result: \"%4\"")
793 .arg(
m_db->MSqlDatabase::GetConnectionName())
794 .arg(where).arg(relative)
799 LOG(VB_DATABASE, LOG_DEBUG,
800 QString(
"MSqlQuery::%1(%2) Result: \"%3\"")
801 .arg(
type,
m_db->MSqlDatabase::GetConnectionName(), str));
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);
860 if (!ok && !(
GetMythDB()->SuppressDBMessages()))
862 LOG(VB_GENERAL, LOG_ERR,
863 QString(
"Error preparing query: %1").arg(query));
864 LOG(VB_GENERAL, LOG_ERR,
877 bool isOpen = db->
isOpen();
879 GetMythDB()->GetDBManager()->pushConnection(db);
885 QSqlQuery::bindValue(placeholder, val, QSql::In);
890 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
891 auto type =
static_cast<QMetaType::Type
>(val.type());
893 auto type = val.typeId();
895 if (
type == QMetaType::QString && val.toString().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));
946 static QStringList kLostConnectionCodes = {
"2006",
"2013",
"4031" };
948 QString error_code = QSqlQuery::lastError().nativeErrorCode();
951 LOG(VB_GENERAL, LOG_DEBUG, QString(
"SQL Native Error Code: %1")
956 return (kLostConnectionCodes.contains(error_code) &&
Reconnect());
962 MSqlBindings::Iterator it;
963 for (it = addfrom.begin(); it != addfrom.end(); ++it)
965 output.insert(it.key(), it.value());
970 explicit Holder( QString hldr = QString(),
int pos = -1 )
985 static const QRegularExpression rx {
"('[^']+'|:\\w+)",
986 QRegularExpression::UseUnicodePropertiesOption};
988 QVector<Holder> holders;
990 auto matchIter = rx.globalMatch(query);
991 while (matchIter.hasNext())
993 auto match = matchIter.next();
994 if (match.capturedLength(1) > 0)
995 holders.append(
Holder(match.captured(), match.capturedStart()));
1001 for (
int i = holders.count() - 1; i >= 0; --i)
1003 holder = holders[(
uint)i].m_holderName;
1004 val = bindings[holder];
1005 #if QT_VERSION < QT_VERSION_CHECK(6,0,0)
1006 QSqlField f(
"", val.type());
1008 QSqlField f(
"", val.metaType());
1015 query = query.replace((
uint)holders[(
uint)i].m_holderPos, holder.length(),
1016 result.
driver()->formatValue(f));