\u0418\u043c\u0435\u0435\u0442\u0441\u044f \u0442\u0438\u043f\u0438\u0447\u043d\u0430 \u0441\u0438\u0442\u0443\u0430\u0446\u0438\u044f:<\/p>\n
\u0412 \u0442\u0430\u043a\u043e\u0439 \u0441\u0438\u0442\u0443\u0430\u0446\u0438\u0438 \u0445\u043e\u0447\u0435\u0442\u0441\u044f \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c \u0447\u0442\u043e \u0437\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u044b, \u0438\u0445 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e, \u0432\u0440\u0435\u043c\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f. \u0415\u0441\u043b\u0438 \u0432 PostgreSQL \u0435\u0441\u0442\u044c pg_stat_statements<\/a> \u0438 pgBadger<\/a>, \u0442\u043e \u0434\u043b\u044f SQLite \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u043d\u0430\u043f\u0438\u0441\u0430\u0442\u044c \u0441\u0432\u043e\u0439 \u0441\u0430\u043c\u043e\u043a\u0430\u0442. \u0421\u0430\u043c\u043e\u043a\u0430\u0442 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0438\u0437 \u0441\u0435\u0431\u044f \u0432\u044b\u0437\u043e\u0432 sqlite3_profile<\/a> \u0434\u043b\u044f \u043a\u0430\u0436\u0434\u043e\u0433\u043e \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u044f, \u0437\u0430\u043f\u0438\u0441\u044c \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u043d\u043e\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438 \u0432 \u0436\u0443\u0440\u043d\u0430\u043b. <\/p>\n <\/a> <\/p>\n QSqlDriver<\/a> \u043f\u0440\u0435\u0434\u043e\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u043d\u0430\u043c handle<\/a>, \u043e\u0442\u043a\u0443\u0434\u0430 \u043d\u0430\u043c \u043c\u043e\u0436\u043d\u043e \u0432\u044b\u0442\u0430\u0449\u0438\u0442\u044c \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a \u041c\u0430\u043a\u0440\u043e\u0441\u044b \u0421\u0438\u0433\u043d\u0430\u0442\u0443\u0440\u0430 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0430 \u0438\u043c\u0435\u0435\u0442 \u0432\u0438\u0434<\/p>\n \u041f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u044b:<\/p>\n \u0418\u0441\u0445\u043e\u0434\u043d\u0438\u043a\u0438 \u043a\u043b\u0430\u0441\u0441\u0430 \u043b\u043e\u0433\u0430 \u0437\u0430\u043d\u0438\u043c\u0430\u0435\u0442 < 200 \u0441\u0442\u0440\u043e\u043a<\/p>\n <\/p>\n <\/p>\n \u0411\u0435\u0441\u0441\u043c\u044b\u0441\u043b\u0435\u043d\u043d\u044b\u0439 \u0438 \u0442\u0443\u043f\u043e\u0439 \u043f\u0440\u0438\u043c\u0435\u0440<\/p>\n \u041f\u043e\u0441\u043b\u0435 \u043e\u0442\u043a\u0440\u044b\u0442\u0438\u044f \u0431\u0430\u0437\u044b, \u0443\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0435\u043c \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a \u0417\u0430\u043f\u0440\u043e\u0441<\/p>\n \u0412\u044b\u0434\u0430\u0441\u0442 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442<\/p>\n\u0423\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0430 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0430 sqlite3_profile<\/h2>\n
sqlite3*<\/code>. \u0412 Qt5 \u043d\u0430\u043c \u043f\u0440\u0438\u0434\u0435\u0442\u0441\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0435<\/p>\nQ_DECLARE_OPAQUE_POINTER(sqlite3*); Q_DECLARE_METATYPE(sqlite3*); \/\/.... QVariant v = driver->handle(); const QString tpName = v.typeName(); if (v.isValid() && (tpName == "sqlite3*")) { sqlite3* handle = v.value<sqlite3*>(); if (handle != nullptr) { sqlite3_profile(handle, profile, this); } }<\/code><\/pre>\nQ_DECLARE_*<\/code> \u0434\u043b\u044f \u0440\u0435\u0433\u0438\u0441\u0442\u0440\u0430\u0446\u0438\u0438 \u0442\u0438\u043f\u0430 sqlite3*<\/code>, \u0447\u0442\u043e \u0431\u044b \u043f\u043e\u0442\u043e\u043c \u0432\u044b\u0442\u0430\u0449\u0438\u0442\u044c \u0438\u0437 QVariant<\/a>.<\/p>\n\u041e\u0431\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a<\/h2>\n
void profile(void* userData, const char* sql, sqlite3_uint64 time)<\/code><\/pre>\n\n
sqlite3_profile<\/code>, \u0432 \u043c\u043e\u0435\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u044d\u0442\u043e this<\/code>;<\/li>\n#pragma once #include <memory> #include <QtCore\/QString> class QSqlDriver; namespace QSqliteProfiler { struct LogRecord { quint64 timestamp; quint64 duration; QString query; }; class Log { public: static Log& instance(); void setProfile(QSqlDriver* driver); void setLogFile(const QString& file); void write(const LogRecord& record); private: Log(); ~Log(); private: struct LogImpl; std::unique_ptr<LogImpl> m_impl; }; } inline QDataStream & operator<< (QDataStream& stream, const QSqliteProfiler::LogRecord record) { stream<<record.timestamp<<record.duration<<record.query; } inline QDataStream & operator>> (QDataStream& stream, QSqliteProfiler::LogRecord& record) { stream>>record.timestamp>>record.duration>>record.query; }<\/code><\/pre>\n#include <mutex> #include <sqlite3.h> #include <QtCore\/QFile> #include <QtCore\/QDebug> #include <QtCore\/QVariant> #include <QtCore\/QDateTime> #include <QtCore\/QMetaType> #include <QtCore\/QDataStream> #include <QtSql\/QSqlDriver> Q_DECLARE_OPAQUE_POINTER(sqlite3*); Q_DECLARE_METATYPE(sqlite3*); #include "Log.h" typedef std::lock_guard<std::mutex> LockGuard; namespace { void profile(void* userData, const char* sql, sqlite3_uint64 time) { using namespace QSqliteProfiler; const quint64 timestamp = QDateTime::currentMSecsSinceEpoch(); LogRecord record{timestamp, time, sql}; auto log = static_cast<Log*>(userData); log->write(record); } } namespace QSqliteProfiler { Log& Log::instance() { static Log log; return log; } struct Log::LogImpl { ~LogImpl() { LockGuard lock(fileMutex); if(file.isOpen()) { file.flush(); file.close(); } } QFile file; QDataStream stream; std::mutex fileMutex; }; Log::Log() : m_impl(new LogImpl) { } Log::~Log() = default; void Log::setProfile(QSqlDriver* driver) { QVariant v = driver->handle(); const QString tpName = v.typeName(); if (v.isValid() && (tpName == "sqlite3*")) { sqlite3* handle = v.value<sqlite3*>(); if (handle != nullptr) { sqlite3_profile(handle, profile, this); } } } void Log::setLogFile(const QString& file) { LockGuard lock(m_impl->fileMutex); if(m_impl->file.isOpen()) { m_impl->file.close(); } m_impl->file.setFileName(file); auto isOpen = m_impl->file.open(QIODevice::WriteOnly); if(isOpen) { m_impl->stream.setDevice(&m_impl->file); } else { qCritical()<<"Can not open file for writing, file"<<file; qDebug()<<m_impl->file.errorString(); exit(1); } } void Log::write(const LogRecord& record) { LockGuard lock(m_impl->fileMutex); if(m_impl->file.isOpen()) { m_impl->stream<<record; } } }<\/code><\/pre>\n\u041f\u0440\u0438\u043c\u0435\u0440 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f<\/h2>\n
#include <QtCore\/QDebug> #include <QtCore\/QCoreApplication> #include <QtSql\/QSqlQuery> #include <QtSql\/QSqlDriver> #include <QtSql\/QSqlDatabase> #include "Log.h" int main(int argc, char *argv[]) { using namespace QSqliteProfiler; QCoreApplication app(argc, argv); Log::instance().setLogFile("sqlite.profile"); QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName("db.sqlite"); if (!db.open()) { qDebug()<<"Test data base not open"; return 1; } Log::instance().setProfile(db.driver()); QSqlQuery query; query.exec( "CREATE TABLE my_table (" "number integer PRIMARY KEY NOT NULL, " "address VARCHAR(255), " "age integer" ");"); QString str; \/\/db.transaction(); for(int i = 0; i < 100; ++i) { QSqlQuery query1(db); query1.prepare("INSERT INTO my_table(number, address, age) VALUES (?, ?, ?)"); query1.bindValue(0, i); query1.bindValue(1, "hello world str."); query1.bindValue(2, 37); query1.exec(); } \/\/db.commit(); return 0; }<\/code><\/pre>\nLog::instance().setProfile(db.driver());<\/code>
\u041f\u0440\u0438 \u043f\u043e\u043c\u043e\u0449\u0438 \u0432\u0441\u043f\u043e\u043c\u043e\u0433\u0430\u0442\u0435\u043b\u044c\u043d\u043e\u0439 \u0443\u0442\u0438\u043b\u0438\u0442\u044b<\/a> \u043f\u0440\u0435\u043e\u0431\u0440\u0430\u0437\u0443\u0435\u043c \u043b\u043e\u0433 \u0432 \u0431\u0430\u0437\u0443 \u0434\u0430\u043d\u043d\u044b\u0445 SQLite, \u0434\u043b\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u043c\u043e\u0449\u0438 SQL.<\/p>\nSELECT count(time) AS CNT, avg(time)\/1000000 AS AVG , sum(time)\/1000000 AS TIME, query FROM query GROUP BY query<\/code><\/pre>\n