Skip to content

Commit df700d1

Browse files
mgrojomgrojo
committed
Export SQL: option to keep original CREATE statements
Additionally, for consistency, when the option to keep old schema is not activated, the statement does not add "IF NOT EXISTS", so it's the same either when the keep original statement is used or not. See issues #2735 and #2627
1 parent 910a2e1 commit df700d1

5 files changed

Lines changed: 47 additions & 28 deletions

File tree

‎src/ExportSqlDialog.cpp‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ ExportSqlDialog::ExportSqlDialog(DBBrowserDB* db, QWidget* parent, const QString
2626
// Load settings
2727
ui->checkColNames->setChecked(Settings::getValue("exportsql", "insertcolnames").toBool());
2828
ui->checkMultiple->setChecked(Settings::getValue("exportsql", "insertmultiple").toBool());
29+
ui->checkOriginal->setChecked(Settings::getValue("exportsql", "keeporiginal").toBool());
2930
ui->comboOldSchema->setCurrentIndex(Settings::getValue("exportsql", "oldschema").toInt());
3031

3132
// Get list of tables to export
@@ -94,6 +95,7 @@ void ExportSqlDialog::accept()
9495
// Save settings
9596
Settings::setValue("exportsql", "insertcolnames", ui->checkColNames->isChecked());
9697
Settings::setValue("exportsql", "insertmultiple", ui->checkMultiple->isChecked());
98+
Settings::setValue("exportsql", "keeporiginal", ui->checkOriginal->isChecked());
9799
Settings::setValue("exportsql", "oldschema", ui->comboOldSchema->currentIndex());
98100

99101
std::vector<std::string> tables;
@@ -110,6 +112,7 @@ void ExportSqlDialog::accept()
110112
tables,
111113
ui->checkColNames->isChecked(),
112114
ui->checkMultiple->isChecked(),
115+
ui->checkOriginal->isChecked(),
113116
exportSchema,
114117
exportData,
115118
keepSchema);

‎src/ExportSqlDialog.ui‎

Lines changed: 26 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -89,14 +89,7 @@
8989
</property>
9090
</widget>
9191
</item>
92-
<item row="1" column="0">
93-
<widget class="QCheckBox" name="checkMultiple">
94-
<property name="text">
95-
<string>Multiple rows (VALUES) per INSERT statement</string>
96-
</property>
97-
</widget>
98-
</item>
99-
<item row="4" column="0">
92+
<item row="5" column="0">
10093
<spacer name="verticalSpacer">
10194
<property name="orientation">
10295
<enum>Qt::Vertical</enum>
@@ -109,39 +102,53 @@
109102
</property>
110103
</spacer>
111104
</item>
112-
<item row="2" column="0">
113-
<widget class="QComboBox" name="comboWhat">
105+
<item row="4" column="0">
106+
<widget class="QComboBox" name="comboOldSchema">
114107
<item>
115108
<property name="text">
116-
<string>Export everything</string>
109+
<string>Keep old schema (CREATE TABLE IF NOT EXISTS)</string>
117110
</property>
118111
</item>
119112
<item>
120113
<property name="text">
121-
<string>Export schema only</string>
114+
<string>Overwrite old schema (DROP TABLE, then CREATE TABLE)</string>
122115
</property>
123116
</item>
117+
</widget>
118+
</item>
119+
<item row="1" column="0">
120+
<widget class="QCheckBox" name="checkMultiple">
121+
<property name="text">
122+
<string>Multiple rows (VALUES) per INSERT statement</string>
123+
</property>
124+
</widget>
125+
</item>
126+
<item row="3" column="0">
127+
<widget class="QComboBox" name="comboWhat">
124128
<item>
125129
<property name="text">
126-
<string>Export data only</string>
130+
<string>Export everything</string>
127131
</property>
128132
</item>
129-
</widget>
130-
</item>
131-
<item row="3" column="0">
132-
<widget class="QComboBox" name="comboOldSchema">
133133
<item>
134134
<property name="text">
135-
<string>Keep old schema (CREATE TABLE IF NOT EXISTS)</string>
135+
<string>Export schema only</string>
136136
</property>
137137
</item>
138138
<item>
139139
<property name="text">
140-
<string>Overwrite old schema (DROP TABLE, then CREATE TABLE)</string>
140+
<string>Export data only</string>
141141
</property>
142142
</item>
143143
</widget>
144144
</item>
145+
<item row="2" column="0">
146+
<widget class="QCheckBox" name="checkOriginal">
147+
<property name="text">
148+
<string>Keep original CREATE statements</string>
149+
</property>
150+
</widget>
151+
</item>
145152
</layout>
146153
</widget>
147154
</item>

‎src/Settings.cpp‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -193,7 +193,7 @@ QVariant Settings::getDefaultValue(const std::string& group, const std::string&
193193
// exportsql group?
194194
if(group == "exportsql")
195195
{
196-
if(name == "insertcolnames" || name == "insertmultiple")
196+
if(name == "insertcolnames" || name == "insertmultiple" || name == "keeporiginal")
197197
return false;
198198
if(name == "oldschema")
199199
return 0;

‎src/sqlitedb.cpp‎

Lines changed: 15 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -870,6 +870,7 @@ bool DBBrowserDB::dump(const QString& filePath,
870870
const std::vector<std::string>& tablesToDump,
871871
bool insertColNames,
872872
bool insertNewSyntx,
873+
bool keepOriginal,
873874
bool exportSchema,
874875
bool exportData,
875876
bool keepOldSchema) const
@@ -919,11 +920,18 @@ bool DBBrowserDB::dump(const QString& filePath,
919920
if(!keepOldSchema)
920921
stream << QString("DROP TABLE IF EXISTS %1;\n").arg(QString::fromStdString(sqlb::escapeIdentifier(it->name())));
921922

922-
if(it->fullyParsed())
923-
stream << QString::fromStdString(it->sql("main", true)) << "\n";
924-
else
925-
stream << QString::fromStdString(it->originalSql()) << ";\n";
923+
if(it->fullyParsed() && !keepOriginal)
924+
stream << QString::fromStdString(it->sql("main", keepOldSchema)) << "\n";
925+
else {
926+
QString statement = QString::fromStdString(it->originalSql());
927+
if(keepOldSchema) {
928+
// The statement is guaranteed by SQLite to start with "CREATE TABLE"
929+
const int createTableLength = 12;
930+
statement.replace(0, createTableLength, "CREATE TABLE IF NOT EXISTS");
931+
}
932+
stream << statement << ";\n";
926933
}
934+
}
927935
}
928936

929937
// Now export the data as well
@@ -1016,7 +1024,7 @@ bool DBBrowserDB::dump(const QString& filePath,
10161024
// Finally export all objects other than tables
10171025
if(exportSchema)
10181026
{
1019-
auto writeSchema = [&stream, &tablesToDump, keepOldSchema](const QString& type, auto objects) {
1027+
auto writeSchema = [&stream, &tablesToDump, keepOldSchema, keepOriginal](const QString& type, auto objects) {
10201028
for(const auto& obj : objects)
10211029
{
10221030
const auto& it = obj.second;
@@ -1034,8 +1042,8 @@ bool DBBrowserDB::dump(const QString& filePath,
10341042
type.toUpper(),
10351043
QString::fromStdString(sqlb::escapeIdentifier(it->name())));
10361044

1037-
if(it->fullyParsed())
1038-
stream << QString::fromStdString(it->sql("main", true)) << "\n";
1045+
if(it->fullyParsed() && !keepOriginal)
1046+
stream << QString::fromStdString(it->sql("main", keepOldSchema)) << "\n";
10391047
else
10401048
stream << QString::fromStdString(it->originalSql()) << ";\n";
10411049
}

‎src/sqlitedb.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -129,7 +129,8 @@ class DBBrowserDB : public QObject
129129
bool releaseAllSavepoints();
130130
bool revertAll();
131131

132-
bool dump(const QString& filename, const std::vector<std::string>& tablesToDump, bool insertColNames, bool insertNew, bool exportSchema, bool exportData, bool keepOldSchema) const;
132+
bool dump(const QString& filename, const std::vector<std::string>& tablesToDump,
133+
bool insertColNames, bool insertNew, bool keepOriginal, bool exportSchema, bool exportData, bool keepOldSchema) const;
133134

134135
enum ChoiceOnUse
135136
{

0 commit comments

Comments
 (0)