diff options
Diffstat (limited to 'Plugins/SqlExport')
| -rw-r--r-- | Plugins/SqlExport/SqlExport.pro | 29 | ||||
| -rw-r--r-- | Plugins/SqlExport/SqlExportCommon.ui | 51 | ||||
| -rw-r--r-- | Plugins/SqlExport/SqlExportQuery.ui | 85 | ||||
| -rw-r--r-- | Plugins/SqlExport/sqlexport.cpp | 329 | ||||
| -rw-r--r-- | Plugins/SqlExport/sqlexport.h | 65 | ||||
| -rw-r--r-- | Plugins/SqlExport/sqlexport.json | 7 | ||||
| -rw-r--r-- | Plugins/SqlExport/sqlexport.qrc | 6 | ||||
| -rw-r--r-- | Plugins/SqlExport/sqlexport_global.h | 12 |
8 files changed, 584 insertions, 0 deletions
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 @@ +<?xml version="1.0" encoding="UTF-8"?> +<ui version="4.0"> + <class>sqlExportCommonConfig</class> + <widget class="QWidget" name="sqlExportCommonConfig"> + <property name="geometry"> + <rect> + <x>0</x> + <y>0</y> + <width>467</width> + <height>86</height> + </rect> + </property> + <property name="windowTitle"> + <string>Form</string> + </property> + <layout class="QGridLayout" name="gridLayout"> + <item row="2" column="0" colspan="2"> + <widget class="QCheckBox" name="dropCheck"> + <property name="text"> + <string>Generate "DROP IF EXISTS" statement before "CREATE" statement</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.GenerateDrop</string> + </property> + </widget> + </item> + <item row="1" column="0" colspan="2"> + <widget class="QCheckBox" name="formatDdlOnlyCheck"> + <property name="text"> + <string>Format DDL statements only (excludes "INSERT" statements)</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.FormatDdlsOnly</string> + </property> + </widget> + </item> + <item row="0" column="0" colspan="2"> + <widget class="QCheckBox" name="formatCheck"> + <property name="text"> + <string>Use SQL formatter to format exported SQL statements</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.UseFormatter</string> + </property> + </widget> + </item> + </layout> + </widget> + <resources/> + <connections/> +</ui> 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 @@ +<?xml version="1.0" encoding="UTF-8"?> +<ui version="4.0"> + <class>sqlExportQueryConfig</class> + <widget class="QWidget" name="sqlExportQueryConfig"> + <property name="geometry"> + <rect> + <x>0</x> + <y>0</y> + <width>467</width> + <height>163</height> + </rect> + </property> + <property name="windowTitle"> + <string>Form</string> + </property> + <layout class="QGridLayout" name="gridLayout"> + <item row="3" column="0" colspan="2"> + <widget class="QCheckBox" name="formatCheck"> + <property name="text"> + <string>Use SQL formatter to format exported SQL statements</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.UseFormatter</string> + </property> + </widget> + </item> + <item row="0" column="0"> + <widget class="QLabel" name="tableLabel"> + <property name="text"> + <string>Table name to use for insert statements:</string> + </property> + </widget> + </item> + <item row="1" column="0" colspan="2"> + <widget class="QCheckBox" name="createTableCheck"> + <property name="text"> + <string>Generate "CREATE TABLE" statement at the begining</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.GenerateCreateTable</string> + </property> + </widget> + </item> + <item row="2" column="0" colspan="2"> + <widget class="QCheckBox" name="queryInCommentsCheck"> + <property name="text"> + <string>Include the query in comments</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.IncludeQueryInComments</string> + </property> + </widget> + </item> + <item row="5" column="0" colspan="2"> + <widget class="QCheckBox" name="dropCheck"> + <property name="text"> + <string>Generate "DROP IF EXISTS" statement before "CREATE" statement</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.GenerateDrop</string> + </property> + </widget> + </item> + <item row="0" column="1"> + <widget class="QLineEdit" name="tableEdit"> + <property name="cfg" stdset="0"> + <string>SqlExport.QueryTable</string> + </property> + </widget> + </item> + <item row="4" column="0" colspan="2"> + <widget class="QCheckBox" name="formatDdlOnlyCheck"> + <property name="text"> + <string>Format DDL statements only (excludes "INSERT" statements)</string> + </property> + <property name="cfg" stdset="0"> + <string>SqlExport.FormatDdlsOnly</string> + </property> + </widget> + </item> + </layout> + </widget> + <resources/> + <connections/> +</ui> 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 <QTextCodec> + +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<QueryExecutor::ResultColumnPtr>& columns, const QHash<ExportManager::ExportProviderFlag, QVariant> 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<ExportManager::ExportProviderFlag, QVariant> 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<ExportManager::ExportProviderFlag, QVariant> 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<QueryExecutor::ResultColumnPtr>& columns, + const QHash<ExportManager::ExportProviderFlag,QVariant> providedData); + bool exportQueryResultsRow(SqlResultsRowPtr row); + bool exportTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl, SqliteCreateTablePtr createTable, + const QHash<ExportManager::ExportProviderFlag,QVariant> providedData); + bool exportVirtualTable(const QString& database, const QString& table, const QStringList& columnNames, const QString& ddl, SqliteCreateVirtualTablePtr createTable, + const QHash<ExportManager::ExportProviderFlag,QVariant> 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 @@ +<RCC> + <qresource prefix="/forms"> + <file>SqlExportQuery.ui</file> + <file>SqlExportCommon.ui</file> + </qresource> +</RCC> 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 <QtCore/qglobal.h> + +#if defined(SQLEXPORT_LIBRARY) +# define SQLEXPORTSHARED_EXPORT Q_DECL_EXPORT +#else +# define SQLEXPORTSHARED_EXPORT Q_DECL_IMPORT +#endif + +#endif // SQLEXPORT_GLOBAL_H |