From 7167ce41b61d2ba2cdb526777a4233eb84a3b66a Mon Sep 17 00:00:00 2001 From: Unit 193 Date: Sat, 6 Dec 2014 17:33:25 -0500 Subject: Imported Upstream version 2.99.6 --- Plugins/SqlExport/SqlExport.pro | 29 +++ Plugins/SqlExport/SqlExportCommon.ui | 51 ++++++ Plugins/SqlExport/SqlExportQuery.ui | 85 +++++++++ Plugins/SqlExport/sqlexport.cpp | 329 +++++++++++++++++++++++++++++++++++ Plugins/SqlExport/sqlexport.h | 65 +++++++ Plugins/SqlExport/sqlexport.json | 7 + Plugins/SqlExport/sqlexport.qrc | 6 + Plugins/SqlExport/sqlexport_global.h | 12 ++ 8 files changed, 584 insertions(+) create mode 100644 Plugins/SqlExport/SqlExport.pro create mode 100644 Plugins/SqlExport/SqlExportCommon.ui create mode 100644 Plugins/SqlExport/SqlExportQuery.ui create mode 100644 Plugins/SqlExport/sqlexport.cpp create mode 100644 Plugins/SqlExport/sqlexport.h create mode 100644 Plugins/SqlExport/sqlexport.json create mode 100644 Plugins/SqlExport/sqlexport.qrc create mode 100644 Plugins/SqlExport/sqlexport_global.h (limited to 'Plugins/SqlExport') diff --git a/Plugins/SqlExport/SqlExport.pro b/Plugins/SqlExport/SqlExport.pro new file mode 100644 index 0000000..29a952b --- /dev/null +++ b/Plugins/SqlExport/SqlExport.pro @@ -0,0 +1,29 @@ +#------------------------------------------------- +# +# Project created by QtCreator 2014-04-03T18:21:00 +# +#------------------------------------------------- + +include($$PWD/../../SQLiteStudio3/plugins.pri) + +QT -= gui + +TARGET = SqlExport +TEMPLATE = lib + +DEFINES += SQLEXPORT_LIBRARY + +SOURCES += sqlexport.cpp + +HEADERS += sqlexport.h\ + sqlexport_global.h + +FORMS += \ + SqlExportQuery.ui \ + SqlExportCommon.ui + +OTHER_FILES += \ + sqlexport.json + +RESOURCES += \ + sqlexport.qrc diff --git a/Plugins/SqlExport/SqlExportCommon.ui b/Plugins/SqlExport/SqlExportCommon.ui new file mode 100644 index 0000000..9b5de21 --- /dev/null +++ b/Plugins/SqlExport/SqlExportCommon.ui @@ -0,0 +1,51 @@ + + + sqlExportCommonConfig + + + + 0 + 0 + 467 + 86 + + + + Form + + + + + + Generate "DROP IF EXISTS" statement before "CREATE" statement + + + SqlExport.GenerateDrop + + + + + + + Format DDL statements only (excludes "INSERT" statements) + + + SqlExport.FormatDdlsOnly + + + + + + + Use SQL formatter to format exported SQL statements + + + SqlExport.UseFormatter + + + + + + + + diff --git a/Plugins/SqlExport/SqlExportQuery.ui b/Plugins/SqlExport/SqlExportQuery.ui new file mode 100644 index 0000000..4d2a6ae --- /dev/null +++ b/Plugins/SqlExport/SqlExportQuery.ui @@ -0,0 +1,85 @@ + + + sqlExportQueryConfig + + + + 0 + 0 + 467 + 163 + + + + Form + + + + + + Use SQL formatter to format exported SQL statements + + + SqlExport.UseFormatter + + + + + + + Table name to use for insert statements: + + + + + + + Generate "CREATE TABLE" statement at the begining + + + SqlExport.GenerateCreateTable + + + + + + + Include the query in comments + + + SqlExport.IncludeQueryInComments + + + + + + + Generate "DROP IF EXISTS" statement before "CREATE" statement + + + SqlExport.GenerateDrop + + + + + + + SqlExport.QueryTable + + + + + + + Format DDL statements only (excludes "INSERT" statements) + + + SqlExport.FormatDdlsOnly + + + + + + + + diff --git a/Plugins/SqlExport/sqlexport.cpp b/Plugins/SqlExport/sqlexport.cpp new file mode 100644 index 0000000..0a379b1 --- /dev/null +++ b/Plugins/SqlExport/sqlexport.cpp @@ -0,0 +1,329 @@ +#include "sqlexport.h" +#include "common/utils_sql.h" +#include "sqlitestudio.h" +#include "config_builder.h" +#include "services/exportmanager.h" +#include "common/unused.h" +#include "services/codeformatter.h" +#include + +SqlExport::SqlExport() +{ +} + +QString SqlExport::getFormatName() const +{ + return "SQL"; +} + +ExportManager::StandardConfigFlags SqlExport::standardOptionsToEnable() const +{ + return ExportManager::CODEC; +} + +CfgMain* SqlExport::getConfig() +{ + return &cfg; +} + +QString SqlExport::defaultFileExtension() const +{ + return "sql"; +} + +QString SqlExport::getExportConfigFormName() const +{ + if (exportMode == ExportManager::QUERY_RESULTS) + return "sqlExportQueryConfig"; + + return "sqlExportCommonConfig"; +} + +bool SqlExport::beforeExportQueryResults(const QString& query, QList& columns, const QHash providedData) +{ + UNUSED(providedData); + static_qstring(dropDdl, "DROP TABLE IF EXISTS %1;"); + + Dialect dialect = db->getDialect(); + QStringList colDefs; + for (QueryExecutor::ResultColumnPtr resCol : columns) + colDefs << wrapObjIfNeeded(resCol->displayName, dialect); + + this->columns = colDefs.join(", "); + + writeHeader(); + if (cfg.SqlExport.IncludeQueryInComments.get()) + { + writeln(tr("-- Results of query:")); + writeln(commentAllSqlLines(query)); + writeln("--"); + } + + writeBegin(); + + if (!cfg.SqlExport.GenerateCreateTable.get()) + return true; + + theTable = wrapObjIfNeeded(cfg.SqlExport.QueryTable.get(), dialect); + QString ddl = "CREATE TABLE " + theTable + " (" + this->columns + ");"; + writeln(""); + + if (cfg.SqlExport.GenerateDrop.get()) + writeln(formatQuery(dropDdl.arg(theTable))); + + writeln(formatQuery(ddl)); + return true; +} + +bool SqlExport::exportQueryResultsRow(SqlResultsRowPtr row) +{ + QStringList argList = rowToArgList(row); + QString argStr = argList.join(", "); + QString sql = "INSERT INTO " + theTable + " (" + this->columns + ") VALUES (" + argStr + ");"; + writeln(sql); + return true; +} + +bool SqlExport::exportTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl, SqliteCreateTablePtr createTable, const QHash providedData) +{ + UNUSED(createTable); + UNUSED(providedData); + return exportTable(database, table, columnNames, ddl); +} + +bool SqlExport::exportVirtualTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl, SqliteCreateVirtualTablePtr createTable, const QHash providedData) +{ + UNUSED(createTable); + UNUSED(providedData); + return exportTable(database, table, columnNames, ddl); +} + +bool SqlExport::exportTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl) +{ + static_qstring(dropDdl, "DROP TABLE IF EXISTS %1;"); + + Dialect dialect = db->getDialect(); + + QStringList colList; + for (const QString& colName : columnNames) + colList << wrapObjIfNeeded(colName, dialect); + + columns = colList.join(", "); + + if (isTableExport()) + { + writeHeader(); + writeFkDisable(); + writeBegin(); + } + + QString fullName = getNameForObject(database, table, false); + writeln(""); + writeln(tr("-- Table: %1").arg(fullName)); + + theTable = getNameForObject(database, table, true, dialect); + + if (cfg.SqlExport.GenerateDrop.get()) + writeln(formatQuery(dropDdl.arg(theTable))); + + writeln(formatQuery(ddl)); + return true; +} + +bool SqlExport::exportTableRow(SqlResultsRowPtr data) +{ + QStringList argList = rowToArgList(data); + QString argStr = argList.join(", "); + QString sql = "INSERT INTO " + theTable + " (" + columns + ") VALUES (" + argStr + ");"; + if (!cfg.SqlExport.FormatDdlsOnly.get()) + sql = formatQuery(sql); + + writeln(sql); + return true; +} + +bool SqlExport::afterExport() +{ + writeCommit(); + return true; +} + +bool SqlExport::beforeExportDatabase(const QString& database) +{ + UNUSED(database); + writeHeader(); + writeFkDisable(); + writeBegin(); + return true; +} + +bool SqlExport::exportIndex(const QString& database, const QString& name, const QString& ddl, SqliteCreateIndexPtr createIndex) +{ + UNUSED(createIndex); + static_qstring(dropDdl, "DROP INDEX IF EXISTS %1;"); + + QString index = getNameForObject(database, name, false); + writeln(""); + writeln(tr("-- Index: %1").arg(index)); + + QString fullName = getNameForObject(database, name, true, db->getDialect()); + if (cfg.SqlExport.GenerateDrop.get()) + writeln(formatQuery(dropDdl.arg(fullName))); + + writeln(formatQuery(ddl)); + return true; +} + +bool SqlExport::exportTrigger(const QString& database, const QString& name, const QString& ddl, SqliteCreateTriggerPtr createTrigger) +{ + UNUSED(createTrigger); + static_qstring(dropDdl, "DROP TRIGGER IF EXISTS %1;"); + + QString trig = getNameForObject(database, name, false); + writeln(""); + writeln(tr("-- Trigger: %1").arg(trig)); + + QString fullName = getNameForObject(database, name, true, db->getDialect()); + if (cfg.SqlExport.GenerateDrop.get()) + writeln(dropDdl.arg(fullName)); + + writeln(formatQuery(formatQuery(ddl))); + return true; +} + +bool SqlExport::exportView(const QString& database, const QString& name, const QString& ddl, SqliteCreateViewPtr createView) +{ + UNUSED(createView); + static_qstring(dropDdl, "DROP VIEW IF EXISTS %1;"); + + QString view = getNameForObject(database, name, false); + writeln(""); + writeln(tr("-- View: %1").arg(view)); + + QString fullName = getNameForObject(database, name, true, db->getDialect()); + if (cfg.SqlExport.GenerateDrop.get()) + writeln(dropDdl.arg(fullName)); + + writeln(formatQuery(formatQuery(ddl))); + return true; +} + +void SqlExport::writeHeader() +{ + QDateTime ctime = QDateTime::currentDateTime(); + writeln("--"); + writeln(tr("-- File generated with SQLiteStudio v%1 on %2").arg(SQLITESTUDIO->getVersionString()).arg(ctime.toString())); + writeln("--"); + if (standardOptionsToEnable().testFlag(ExportManager::CODEC)) + { + writeln(tr("-- Text encoding used: %1").arg(QString::fromLatin1(codec->name()))); + writeln("--"); + } +} + +void SqlExport::writeBegin() +{ + writeln("BEGIN TRANSACTION;"); +} + +void SqlExport::writeCommit() +{ + writeln(""); + writeln("COMMIT TRANSACTION;"); +} + +void SqlExport::writeFkDisable() +{ + writeln("PRAGMA foreign_keys = off;"); +} + +QString SqlExport::formatQuery(const QString& sql) +{ + if (cfg.SqlExport.UseFormatter.get()) + return FORMATTER->format("sql", sql, db); + + return sql; +} + +QString SqlExport::getNameForObject(const QString& database, const QString& name, bool wrapped, Dialect dialect) +{ + QString obj = wrapped ? wrapObjIfNeeded(name, dialect) : name; + if (!database.isNull() && database.toLower() != "main") + obj = (wrapped ? wrapObjIfNeeded(database, dialect) : database) + "." + obj; + + return obj; +} + +QStringList SqlExport::rowToArgList(SqlResultsRowPtr row) +{ + QStringList argList; + for (const QVariant& value : row->valueList()) + { + if (!value.isValid() || value.isNull()) + { + argList << "NULL"; + continue; + } + + switch (value.userType()) + { + case QVariant::Int: + case QVariant::UInt: + case QVariant::LongLong: + case QVariant::ULongLong: + argList << value.toString(); + break; + case QVariant::Double: + argList << QString::number(value.toDouble()); + break; + case QVariant::Bool: + argList << QString::number(value.toInt()); + break; + case QVariant::ByteArray: + { + if (db->getVersion() >= 3) // version 2 will go to the regular string processing + { + argList << "X'" + value.toByteArray().toHex().toUpper() + "'"; + break; + } + } + default: + argList << wrapString(escapeString(value.toString())); + break; + } + } + return argList; +} + +void SqlExport::validateOptions() +{ + if (exportMode == ExportManager::QUERY_RESULTS) + { + bool valid = !cfg.SqlExport.QueryTable.get().isEmpty(); + EXPORT_MANAGER->handleValidationFromPlugin(valid, cfg.SqlExport.QueryTable, tr("Table name for INSERT statements is mandatory.")); + } + + bool useFormatter = cfg.SqlExport.UseFormatter.get(); + EXPORT_MANAGER->updateVisibilityAndEnabled(cfg.SqlExport.FormatDdlsOnly, true, useFormatter); + if (!useFormatter) + cfg.SqlExport.FormatDdlsOnly.set(false); + + if (exportMode == ExportManager::QUERY_RESULTS) + { + bool generateCreate = cfg.SqlExport.GenerateCreateTable.get(); + EXPORT_MANAGER->updateVisibilityAndEnabled(cfg.SqlExport.GenerateDrop, true, generateCreate); + if (!generateCreate) + cfg.SqlExport.GenerateDrop.set(false); + } +} + +bool SqlExport::init() +{ + Q_INIT_RESOURCE(sqlexport); + return GenericExportPlugin::init(); +} + +void SqlExport::deinit() +{ + Q_CLEANUP_RESOURCE(sqlexport); +} diff --git a/Plugins/SqlExport/sqlexport.h b/Plugins/SqlExport/sqlexport.h new file mode 100644 index 0000000..9e23418 --- /dev/null +++ b/Plugins/SqlExport/sqlexport.h @@ -0,0 +1,65 @@ +#ifndef SQLEXPORT_H +#define SQLEXPORT_H + +#include "plugins/genericexportplugin.h" +#include "sqlexport_global.h" +#include "config_builder.h" + +CFG_CATEGORIES(SqlExportConfig, + CFG_CATEGORY(SqlExport, + CFG_ENTRY(QString, QueryTable, QString::null) + CFG_ENTRY(bool, GenerateCreateTable, false) + CFG_ENTRY(bool, IncludeQueryInComments, true) + CFG_ENTRY(bool, UseFormatter, false) + CFG_ENTRY(bool, FormatDdlsOnly, false) + CFG_ENTRY(bool, GenerateDrop, false) + ) +) + +class SQLEXPORTSHARED_EXPORT SqlExport : public GenericExportPlugin +{ + Q_OBJECT + + SQLITESTUDIO_PLUGIN("sqlexport.json") + + public: + SqlExport(); + + QString getFormatName() const; + ExportManager::StandardConfigFlags standardOptionsToEnable() const; + CfgMain* getConfig(); + QString defaultFileExtension() const; + QString getExportConfigFormName() const; + bool beforeExportQueryResults(const QString& query, QList& columns, + const QHash providedData); + bool exportQueryResultsRow(SqlResultsRowPtr row); + bool exportTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl, SqliteCreateTablePtr createTable, + const QHash providedData); + bool exportVirtualTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl, SqliteCreateVirtualTablePtr createTable, + const QHash providedData); + bool exportTableRow(SqlResultsRowPtr data); + bool afterExport(); + bool beforeExportDatabase(const QString& database); + bool exportIndex(const QString& database, const QString& name, const QString& ddl, SqliteCreateIndexPtr createIndex); + bool exportTrigger(const QString& database, const QString& name, const QString& ddl, SqliteCreateTriggerPtr createTrigger); + bool exportView(const QString& database, const QString& name, const QString& ddl, SqliteCreateViewPtr createView); + void validateOptions(); + bool init(); + void deinit(); + + private: + bool exportTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl); + void writeHeader(); + void writeBegin(); + void writeCommit(); + void writeFkDisable(); + QString formatQuery(const QString& sql); + QString getNameForObject(const QString& database, const QString& name, bool wrapped, Dialect dialect = Dialect::Sqlite3); + QStringList rowToArgList(SqlResultsRowPtr row); + + QString theTable; + QString columns; + CFG_LOCAL(SqlExportConfig, cfg) +}; + +#endif // SQLEXPORT_H diff --git a/Plugins/SqlExport/sqlexport.json b/Plugins/SqlExport/sqlexport.json new file mode 100644 index 0000000..bd06adf --- /dev/null +++ b/Plugins/SqlExport/sqlexport.json @@ -0,0 +1,7 @@ +{ + "type": "ExportPlugin", + "title": "SQL export", + "description": "Provides SQL format for exporting", + "version": 10100, + "author": "SalSoft" +} diff --git a/Plugins/SqlExport/sqlexport.qrc b/Plugins/SqlExport/sqlexport.qrc new file mode 100644 index 0000000..447617f --- /dev/null +++ b/Plugins/SqlExport/sqlexport.qrc @@ -0,0 +1,6 @@ + + + SqlExportQuery.ui + SqlExportCommon.ui + + diff --git a/Plugins/SqlExport/sqlexport_global.h b/Plugins/SqlExport/sqlexport_global.h new file mode 100644 index 0000000..f7c05a8 --- /dev/null +++ b/Plugins/SqlExport/sqlexport_global.h @@ -0,0 +1,12 @@ +#ifndef SQLEXPORT_GLOBAL_H +#define SQLEXPORT_GLOBAL_H + +#include + +#if defined(SQLEXPORT_LIBRARY) +# define SQLEXPORTSHARED_EXPORT Q_DECL_EXPORT +#else +# define SQLEXPORTSHARED_EXPORT Q_DECL_IMPORT +#endif + +#endif // SQLEXPORT_GLOBAL_H -- cgit v1.2.3