/** Return the date type from the persistent in time uuid (for the database management) */
VariableDatesItem::DateType VariableDatesItem::dateTypeFromSql(const QString &type)
{
if (type=="med_real")
return Date_MedicalRealisation;
if (type=="inv")
return Date_Invocing;
if (type=="pay")
return Date_Payment;
if (type=="bkg")
return Date_Banking;
if (type=="acc")
return Date_Accountancy;
if (type=="crea")
return Date_Creation;
if (type=="upd")
return Date_Update;
if (type=="val")
return Date_Validation;
if (type=="ann")
return Date_Annulation;
if (type=="validitystart")
return Date_ValidityPeriodStart;
if (type=="validityend")
return Date_ValidityPeriodEnd;
Q_ASSERT(false);
LOG_ERROR_FOR("VariableDatesItem", "Unknown type: " + type);
return Date_Creation;
}
bool executeProcess(const QString &proc)
{
QProcess process;
process.start(proc, QIODevice::ReadOnly);
LOG_FOR("Tools", QString("Executing process: %1").arg(proc));
if (!process.waitForStarted())
LOG_ERROR_FOR("Tools", QString("Process %1 can not start").arg(proc.left(20)));
if (!process.waitForFinished(100000))
LOG_ERROR_FOR("Tools", QString("Process %1 does not end").arg(proc.left(20)));
QString error = process.readAllStandardError();
if (!error.isEmpty()) {
LOG_ERROR_FOR("Tools", QString("ERROR: %1").arg(proc));
LOG_ERROR_FOR("Tools", QString("ERROR: %1").arg(error));
return false;
}
LOG_FOR("Tools", QString("Process done: %1, output: %2").arg(proc.left(20)).arg(QString(process.readAllStandardOutput())));
return true;
}
/** Return the payment type from the persistent in time uuid (for the database management) */
Payment::PaymentType Payment::typeFromSql(const QString &type)
{
if (type=="cash")
return Cash;
if (type=="chq")
return Cheque;
if (type=="creditcard")
return CreditCard;
if (type=="bktfrt")
return BankTransfer;
if (type=="delay")
return InsuranceDelayed;
if (type=="other")
return Other;
if (type=="none")
return None;
Q_ASSERT(false);
LOG_ERROR_FOR("Payment", "Unknown type: " + type);
return None;
}
void checkDatabaseVersion()
{
QString version = q->getVersion(Utils::Field(Constants::Table_Version, Constants::VERSION_ACTUAL));
bool updateVersionNumber = false;
if (version=="0.3.0") {
QSqlDatabase DB = QSqlDatabase::database(Constants::DB_TEMPLATES_NAME);
if (!connectDatabase(DB, __LINE__)) {
return;
}
// Update database schema to 0.4.0
// MySQL server connection starts here, so no update needed for MySQL database
// For SQLite :
// --> USER_UID from int to varchar
// --> Adding GROUP_UID for templates and categories
LOG_FOR(q, "Updating templates database version (0.3.0 to 0.4.0)");
// 1. Rename old tables
QStringList reqs;
reqs
<< "ALTER TABLE `CATEGORIES` RENAME TO `OLD_CATEGORIES`;"
<< "ALTER TABLE `TEMPLATES` RENAME TO `OLD_TEMPLATES`;";
if (!q->executeSQL(reqs, DB))
LOG_ERROR_FOR(q, "Unable to recreate template database during update (0.3.0 to 0.4.0)");
// 2. Recreate the db schema
if (!q->createTables())
LOG_ERROR_FOR(q, "Unable to recreate template database during update (0.3.0 to 0.4.0)");
reqs.clear();
reqs << QString("INSERT INTO `CATEGORIES` (%1) SELECT %1 FROM `OLD_CATEGORIES`;")
.arg("`CATEGORY_ID`,"
"`CATEGORY_UUID`,"
"`USER_UUID`,"
"`PARENT_CATEGORY`,"
"`LABEL`,"
"`SUMMARY`,"
"`MIMETYPES`,"
"`DATE_CREATION`,"
"`DATE_MODIFICATION`,"
"`THEMED_ICON_FILENAME`,"
"`TRANSMISSION_DATE`")
<< "DROP TABLE `OLD_CATEGORIES`;"
<< QString("INSERT INTO `TEMPLATES` (%1) SELECT %1 FROM `OLD_TEMPLATES`;")
.arg("`TEMPLATE_ID`,"
"`TEMPLATE_UUID`,"
"`USER_UUID`,"
"`ID_CATEGORY`,"
"`LABEL`,"
"`SUMMARY`,"
"`CONTENT`,"
"`CONTENT_MIMETYPES`,"
"`DATE_CREATION`,"
"`DATE_MODIFICATION`,"
"`THEMED_ICON_FILENAME`,"
"`TRANSMISSION_DATE`")
<< "DROP TABLE `OLD_TEMPLATES`;";
// Reinsert data to new tables
if (!q->executeSQL(reqs, DB))
LOG_ERROR_FOR(q, "Unable to recreate template database during update (0.3.0 to 0.4.0)");
// Refresh db version
version = "0.4.0";
updateVersionNumber = true;
}
if (updateVersionNumber) {
q->setVersion(Utils::Field(Constants::Table_Version, Constants::VERSION_ACTUAL), Constants::DB_ACTUAL_VERSION);
}
}
bool executeSqlFile(const QString &connectionName, const QString &fileName, QProgressDialog *dlg)
{
if (!QFile::exists(fileName)) {
LOG_ERROR_FOR("Tools", QString("ERROR: missing database schema file: %1.").arg(fileName));
return false;
}
// execute all sql queries
QString req = Utils::readTextFile(fileName);
if (req.isEmpty()) {
LOG_ERROR_FOR("Tools", "File is empty: " + fileName);
return false;
}
req.replace("\n\n", "\n");
req.replace("\n\n", "\n");
req.replace("\n\n", "\n");
req.replace("\n\n", "\n");
req.replace("\n\n", "\n");
req.remove("AUTO_INCREMENT"); // SQLite compatibility
QStringList list = req.split("\n");
QSqlDatabase DB = QSqlDatabase::database(connectionName);
if (!DB.isOpen()) {
if (!DB.open()) {
LOG_ERROR_FOR("Tools", "Database not opened");
return false;
}
}
DB.transaction();
req.clear();
QStringList queries;
// Reconstruct req: removes comments
foreach(const QString &s, list) {
if (s.startsWith("--")) {
LOG_FOR("Tools", s);
continue;
}
req += s + " \n";
if (s.endsWith(";")) {
queries.append(req);
req.clear();
}
}
// Execute queries
if (dlg)
dlg->setRange(0, queries.count());
foreach(const QString &sql, queries) {
QTime time;
time.start();
QString q = sql.simplified();
// Do not processed empty strings
if (q.isEmpty())
continue;
// No SQLite extra commands
if (q.startsWith("."))
continue;
// No BEGIN, No COMMIT
if (q.startsWith("BEGIN", Qt::CaseInsensitive) || q.startsWith("COMMIT", Qt::CaseInsensitive))
continue;
qWarning() << sql;
QSqlQuery query(sql, DB);
if (!query.isActive()) {
LOG_ERROR_FOR("Tools", QString("SQL ERROR: %1 \"%2\"").arg(query.lastError().text(), sql));
// DB.rollback();
return false;
} else {
LOG_FOR("Tools", QString("Query correctly done (%1 ms)").arg(time.elapsed()));
}
if (dlg)
dlg->setValue(dlg->value()+1);
}