1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
|
#include "sqlfileexecutor.h"
#include "db/db.h"
#include "db/sqlquery.h"
#include "services/notifymanager.h"
#include <QtConcurrent/QtConcurrentRun>
#include <QElapsedTimer>
#include <QFile>
SqlFileExecutor::SqlFileExecutor(QObject *parent)
: QObject{parent}
{
}
void SqlFileExecutor::execSqlFromFile(Db* db, const QString& filePath, bool ignoreErrors, QString codec, bool async)
{
if (!db && !db->isOpen())
{
emit execEnded();
return;
}
if (executionInProgress)
{
emit execEnded();
return;
}
// #4871 is caused by this. On one hand it doesn't make sense to disable FK for script execution
// (after all we're trying to execute script just like from SQL Editor, but we do it directly from file),
// but on the other hand, it was introduced probably for some reason. It's kept commented for now to see
// if good reason for it reappears. It's a subject for removal in future (until end of 2025).
//
// fkWasEnabled = db->exec("PRAGMA foreign_keys")->getSingleCell().toBool();
// if (fkWasEnabled)
// {
// SqlQueryPtr res = db->exec("PRAGMA foreign_keys = 0");
// if (res->isError())
// {
// qDebug() << "Failed to temporarily disable foreign keys enforcement:" << db->getErrorText();
// emit execEnded();
// return;
// }
// }
// Exec file
executionInProgress = 1;
this->ignoreErrors = ignoreErrors;
this->codec = codec;
this->filePath = filePath;
this->db = db;
emit updateProgress(0);
if (!db->begin())
{
notifyError(tr("Could not execute SQL, because application has failed to start transaction: %1").arg(db->getErrorText()));
emit execEnded();
return;
}
if (async)
QtConcurrent::run(this, &SqlFileExecutor::execInThread);
else
execInThread();
}
bool SqlFileExecutor::isExecuting() const
{
return executionInProgress;
}
void SqlFileExecutor::stopExecution()
{
if (!executionInProgress)
{
emit execEnded();
return;
}
executionInProgress = 0;
if (db) // should always be there, but just in case
{
db->interrupt();
db->rollback();
db = nullptr;
notifyWarn(tr("Execution from file cancelled. Any queries executed so far have been rolled back."));
}
emit execEnded();
}
bool SqlFileExecutor::execQueryFromFile(Db* db, const QString& sql)
{
return !db->exec(sql)->isError();
}
void SqlFileExecutor::execInThread()
{
// Open file
QFile file(filePath);
if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
{
notifyError(tr("Could not open file '%1' for reading: %2").arg(filePath, file.errorString()));
executionInProgress = 0;
emit execEnded();
return;
}
QTextStream stream(&file);
stream.setCodec(codec.toLatin1().constData());
qint64 fileSize = file.size();
int attemptedExecutions = 0;
int executed = 0;
bool ok = true;
QElapsedTimer timer;
timer.start();
QList<QPair<QString, QString>> errors = executeFromStream(stream, executed, attemptedExecutions, ok, fileSize);
int millis = timer.elapsed();
// See comment about fkWasEnabled above.
//
// if (fkWasEnabled)
// {
// SqlQueryPtr res = db->exec("PRAGMA foreign_keys = 1");
// if (res->isError())
// qDebug() << "Failed to restore foreign keys enforcement after execution SQL from file:" << res->getErrorText();
// }
if (executionInProgress.loadAcquire())
{
handleExecutionResults(db, executed, attemptedExecutions, ok, ignoreErrors, millis);
if (!errors.isEmpty())
emit execErrors(errors, !ok && !ignoreErrors);
}
file.close();
emit execEnded();
executionInProgress = 0;
}
void SqlFileExecutor::handleExecutionResults(Db* db, int executed, int attemptedExecutions, bool ok, bool ignoreErrors, int millis)
{
bool doCommit = ok ? true : ignoreErrors;
if (doCommit)
{
if (!db->commit())
{
notifyError(tr("Could not execute SQL, because application has failed to commit the transaction: %1").arg(db->getErrorText()));
db->rollback();
}
else if (!ok) // committed with errors
{
notifyInfo(tr("Finished executing %1 queries in %2 seconds. %3 were not executed due to errors.")
.arg(QString::number(executed), QString::number(millis / 1000.0), QString::number(attemptedExecutions - executed)));
emit schemaNeedsRefreshing(db);
}
else
{
notifyInfo(tr("Finished executing %1 queries in %2 seconds.").arg(QString::number(executed), QString::number(millis / 1000.0)));
emit schemaNeedsRefreshing(db);
}
}
else
{
db->rollback();
notifyError(tr("Could not execute SQL due to error."));
}
}
QList<QPair<QString, QString>> SqlFileExecutor::executeFromStream(QTextStream& stream, int& executed, int& attemptedExecutions, bool& ok, qint64 fileSize)
{
QList<QPair<QString, QString>> errors;
qint64 pos = 0;
QChar c;
QString sql;
sql.reserve(10000);
SqlQueryPtr results;
while (!stream.atEnd() && executionInProgress.loadAcquire())
{
while (!db->isComplete(sql) && !stream.atEnd())
{
stream >> c;
sql.append(c);
while (c != ';' && !stream.atEnd())
{
stream >> c;
sql.append(c);
}
}
if (shouldSkipQuery(sql))
{
sql.clear();
continue;
}
results = db->exec(sql);
attemptedExecutions++;
if (results->isError())
{
ok = false;
errors << QPair<QString, QString>(sql, results->getErrorText());
if (!ignoreErrors)
break;
}
else
executed++;
sql.clear();
if (attemptedExecutions % 100 == 0)
{
pos = stream.device()->pos();
emit updateProgress(static_cast<int>(100 * pos / fileSize));
}
}
return errors;
}
bool SqlFileExecutor::shouldSkipQuery(const QString& sql)
{
if (sql.trimmed().isEmpty() || !db->isComplete(sql))
return true;
QString upper = sql.toUpper().trimmed();
return (upper.startsWith("BEGIN") ||
upper.startsWith("COMMIT") ||
upper.startsWith("ROLLBACK") ||
upper.startsWith("END"));
}
|
