Skip to content

Commit da28fdc

Browse files
MKleusbergMKleusberg
committed
Add support for strict tables in the UI
Add support for strict tables in the Edit Table dialog. This allows creating new strict tables and converting existing tables between ordinary and strict modes while making sure only valid data types are used in strict mode. This also adds the STRICT and ANY keywords to the auto completion and the syntax highlighter. See issue #2926.
1 parent 42b7a37 commit da28fdc

6 files changed

Lines changed: 101 additions & 27 deletions

File tree

‎src/EditTableDialog.cpp‎

Lines changed: 41 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,9 @@ EditTableDialog::EditTableDialog(DBBrowserDB& db, const sqlb::ObjectIdentifier&
9090
ui->checkWithoutRowid->blockSignals(true);
9191
ui->checkWithoutRowid->setChecked(m_table.withoutRowidTable());
9292
ui->checkWithoutRowid->blockSignals(false);
93+
ui->checkStrict->blockSignals(true);
94+
ui->checkStrict->setChecked(m_table.isStrict());
95+
ui->checkStrict->blockSignals(false);
9396
ui->comboSchema->blockSignals(true);
9497
for(const auto& n : pdb.schemata) // Load list of database schemata
9598
ui->comboSchema->addItem(QString::fromStdString(n.first));
@@ -218,9 +221,9 @@ void EditTableDialog::populateFields()
218221
tbitem->setText(kName, QString::fromStdString(f.name()));
219222
QComboBox* typeBox = new QComboBox(ui->treeWidget);
220223
typeBox->setProperty("column", QString::fromStdString(f.name()));
221-
typeBox->setEditable(true);
222-
typeBox->addItems(DBBrowserDB::Datatypes);
223-
int index = typeBox->findText(QString::fromStdString(f.type()), Qt::MatchExactly);
224+
typeBox->setEditable(!m_table.isStrict()); // Strict tables do not allow arbitrary types
225+
typeBox->addItems(m_table.isStrict() ? DBBrowserDB::DatatypesStrict : DBBrowserDB::Datatypes);
226+
int index = typeBox->findText(QString::fromStdString(f.type()), Qt::MatchFixedString);
224227
if(index == -1)
225228
{
226229
// non standard named type
@@ -786,8 +789,8 @@ void EditTableDialog::addField()
786789

787790
QComboBox* typeBox = new QComboBox(ui->treeWidget);
788791
typeBox->setProperty("column", tbitem->text(kName));
789-
typeBox->setEditable(true);
790-
typeBox->addItems(DBBrowserDB::Datatypes);
792+
typeBox->setEditable(!m_table.isStrict()); // Strict tables do not allow arbitrary types
793+
typeBox->addItems(m_table.isStrict() ? DBBrowserDB::DatatypesStrict : DBBrowserDB::Datatypes);
791794

792795
int defaultFieldTypeIndex = Settings::getValue("db", "defaultfieldtype").toInt();
793796

@@ -982,6 +985,39 @@ void EditTableDialog::setWithoutRowid(bool without_rowid)
982985
updateSqlText();
983986
}
984987

988+
void EditTableDialog::setStrict(bool strict)
989+
{
990+
// Set the strict option
991+
m_table.setStrict(strict);
992+
993+
// Replace list of possible data types in each type combo box
994+
for(int i=0;i<ui->treeWidget->topLevelItemCount();i++)
995+
{
996+
QComboBox* w = qobject_cast<QComboBox*>(ui->treeWidget->itemWidget(ui->treeWidget->topLevelItem(i), kType));
997+
QString value = w->currentText();
998+
w->blockSignals(true);
999+
w->clear();
1000+
w->addItems(strict ? DBBrowserDB::DatatypesStrict : DBBrowserDB::Datatypes);
1001+
w->setEditable(!strict); // Strict tables do not allow arbitrary types
1002+
int pos = w->findText(value, Qt::MatchFixedString);
1003+
w->blockSignals(false);
1004+
1005+
if(pos >= 0)
1006+
{
1007+
w->setCurrentIndex(pos);
1008+
} else {
1009+
// When the old value cannot be found we default to ANY for strict tables. For ordinary tables we just add the former value.
1010+
if(strict)
1011+
w->setCurrentText("ANY");
1012+
else
1013+
w->setCurrentText(value);
1014+
}
1015+
}
1016+
1017+
// Update the SQL preview
1018+
updateSqlText();
1019+
}
1020+
9851021
void EditTableDialog::changeSchema(const QString& /*schema*/)
9861022
{
9871023
// Update the SQL preview

‎src/EditTableDialog.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,7 @@ private slots:
8282
void moveTop();
8383
void moveBottom();
8484
void setWithoutRowid(bool without_rowid);
85+
void setStrict(bool strict);
8586
void changeSchema(const QString& schema);
8687
void removeConstraint();
8788
void addConstraint(sqlb::Constraint::ConstraintTypes type);

‎src/EditTableDialog.ui‎

Lines changed: 55 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -86,14 +86,14 @@
8686
</property>
8787
</widget>
8888
</item>
89-
<item row="2" column="0">
89+
<item row="3" column="0">
9090
<widget class="QLabel" name="label_2">
9191
<property name="text">
9292
<string>On Conflict</string>
9393
</property>
9494
</widget>
9595
</item>
96-
<item row="2" column="1">
96+
<item row="3" column="1">
9797
<widget class="QComboBox" name="comboOnConflict">
9898
<item>
9999
<property name="text">
@@ -127,6 +127,23 @@
127127
</item>
128128
</widget>
129129
</item>
130+
<item row="2" column="0">
131+
<widget class="QLabel" name="label_3">
132+
<property name="text">
133+
<string>Strict</string>
134+
</property>
135+
<property name="buddy">
136+
<cstring>checkStrict</cstring>
137+
</property>
138+
</widget>
139+
</item>
140+
<item row="2" column="1">
141+
<widget class="QCheckBox" name="checkStrict">
142+
<property name="toolTip">
143+
<string>When the strict option is enabled SQLite enforces the data types of each column when updating or inserting data.</string>
144+
</property>
145+
</widget>
146+
</item>
130147
</layout>
131148
</widget>
132149
</item>
@@ -562,6 +579,8 @@
562579
<tabstop>buttonMore</tabstop>
563580
<tabstop>comboSchema</tabstop>
564581
<tabstop>checkWithoutRowid</tabstop>
582+
<tabstop>checkStrict</tabstop>
583+
<tabstop>comboOnConflict</tabstop>
565584
<tabstop>groupDefinition</tabstop>
566585
<tabstop>addFieldButton</tabstop>
567586
<tabstop>removeFieldButton</tabstop>
@@ -570,7 +589,6 @@
570589
<tabstop>buttonMoveDown</tabstop>
571590
<tabstop>buttonMoveBottom</tabstop>
572591
<tabstop>treeWidget</tabstop>
573-
<tabstop>sqlTextEdit</tabstop>
574592
<tabstop>buttonAddConstraint</tabstop>
575593
<tabstop>buttonRemoveConstraint</tabstop>
576594
<tabstop>tableConstraints</tabstop>
@@ -634,8 +652,8 @@
634652
<slot>addField()</slot>
635653
<hints>
636654
<hint type="sourcelabel">
637-
<x>82</x>
638-
<y>248</y>
655+
<x>79</x>
656+
<y>311</y>
639657
</hint>
640658
<hint type="destinationlabel">
641659
<x>79</x>
@@ -650,8 +668,8 @@
650668
<slot>removeField()</slot>
651669
<hints>
652670
<hint type="sourcelabel">
653-
<x>178</x>
654-
<y>248</y>
671+
<x>169</x>
672+
<y>311</y>
655673
</hint>
656674
<hint type="destinationlabel">
657675
<x>249</x>
@@ -682,8 +700,8 @@
682700
<slot>moveUp()</slot>
683701
<hints>
684702
<hint type="sourcelabel">
685-
<x>400</x>
686-
<y>248</y>
703+
<x>376</x>
704+
<y>311</y>
687705
</hint>
688706
<hint type="destinationlabel">
689707
<x>308</x>
@@ -698,8 +716,8 @@
698716
<slot>moveDown()</slot>
699717
<hints>
700718
<hint type="sourcelabel">
701-
<x>519</x>
702-
<y>248</y>
719+
<x>487</x>
720+
<y>311</y>
703721
</hint>
704722
<hint type="destinationlabel">
705723
<x>308</x>
@@ -746,8 +764,8 @@
746764
<slot>fieldSelectionChanged()</slot>
747765
<hints>
748766
<hint type="sourcelabel">
749-
<x>324</x>
750-
<y>294</y>
767+
<x>335</x>
768+
<y>475</y>
751769
</hint>
752770
<hint type="destinationlabel">
753771
<x>324</x>
@@ -779,7 +797,7 @@
779797
<hints>
780798
<hint type="sourcelabel">
781799
<x>119</x>
782-
<y>253</y>
800+
<y>315</y>
783801
</hint>
784802
<hint type="destinationlabel">
785803
<x>647</x>
@@ -794,8 +812,8 @@
794812
<slot>moveTop()</slot>
795813
<hints>
796814
<hint type="sourcelabel">
797-
<x>207</x>
798-
<y>240</y>
815+
<x>283</x>
816+
<y>311</y>
799817
</hint>
800818
<hint type="destinationlabel">
801819
<x>202</x>
@@ -810,8 +828,8 @@
810828
<slot>moveBottom()</slot>
811829
<hints>
812830
<hint type="sourcelabel">
813-
<x>530</x>
814-
<y>246</y>
831+
<x>625</x>
832+
<y>311</y>
815833
</hint>
816834
<hint type="destinationlabel">
817835
<x>400</x>
@@ -826,15 +844,31 @@
826844
<slot>setOnConflict(QString)</slot>
827845
<hints>
828846
<hint type="sourcelabel">
829-
<x>371</x>
830-
<y>168</y>
847+
<x>516</x>
848+
<y>219</y>
831849
</hint>
832850
<hint type="destinationlabel">
833851
<x>490</x>
834852
<y>211</y>
835853
</hint>
836854
</hints>
837855
</connection>
856+
<connection>
857+
<sender>checkStrict</sender>
858+
<signal>toggled(bool)</signal>
859+
<receiver>EditTableDialog</receiver>
860+
<slot>setStrict(bool)</slot>
861+
<hints>
862+
<hint type="sourcelabel">
863+
<x>457</x>
864+
<y>177</y>
865+
</hint>
866+
<hint type="destinationlabel">
867+
<x>399</x>
868+
<y>299</y>
869+
</hint>
870+
</hints>
871+
</connection>
838872
</connections>
839873
<slots>
840874
<slot>fieldSelectionChanged()</slot>
@@ -851,5 +885,6 @@
851885
<slot>moveTop()</slot>
852886
<slot>moveBottom()</slot>
853887
<slot>setOnConflict(QString)</slot>
888+
<slot>setStrict(bool)</slot>
854889
</slots>
855890
</ui>

‎src/SqlUiLexer.cpp‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ void SqlUiLexer::setupAutoCompletion()
3838
keywordPatterns
3939
// Keywords
4040
<< "ABORT" << "ACTION" << "ADD" << "AFTER" << "ALL"
41-
<< "ALTER" << "ALWAYS" << "ANALYZE" << "AND" << "AS" << "ASC"
41+
<< "ALTER" << "ALWAYS" << "ANALYZE" << "AND" << "ANY" << "AS" << "ASC"
4242
<< "ATTACH" << "AUTOINCREMENT" << "BEFORE" << "BEGIN" << "BETWEEN"
4343
<< "BY" << "CASCADE" << "CASE" << "CAST" << "CHECK"
4444
<< "COLLATE" << "COLUMN" << "COMMIT" << "CONFLICT" << "CONSTRAINT"
@@ -57,7 +57,7 @@ void SqlUiLexer::setupAutoCompletion()
5757
<< "OUTER" << "OVER" << "PARTITION" << "PLAN" << "PRAGMA" << "PRECEDING" << "PRIMARY" << "QUERY"
5858
<< "RAISE" << "RANGE" << "RECURSIVE" << "REFERENCES" << "REGEXP" << "REINDEX" << "RELEASE"
5959
<< "RENAME" << "REPLACE" << "RESTRICT" << "RETURNING" << "RIGHT" << "ROLLBACK"
60-
<< "ROWID" << "ROW" << "ROWS" << "SAVEPOINT" << "SELECT" << "SET" << "STORED" << "TABLE"
60+
<< "ROWID" << "ROW" << "ROWS" << "SAVEPOINT" << "SELECT" << "SET" << "STORED" << "STRICT" << "TABLE"
6161
<< "TEMP" << "TEMPORARY" << "THEN" << "TO" << "TRANSACTION"
6262
<< "TRIGGER" << "UNBOUNDED" << "UNION" << "UNIQUE" << "UPDATE" << "USING"
6363
<< "VACUUM" << "VALUES" << "VIEW" << "VIRTUAL" << "WHEN"

‎src/sqlitedb.cpp‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
#include <functional>
2828

2929
QStringList DBBrowserDB::Datatypes = {"INTEGER", "TEXT", "BLOB", "REAL", "NUMERIC"};
30+
QStringList DBBrowserDB::DatatypesStrict = {"INT", "INTEGER", "TEXT", "BLOB", "REAL", "ANY"};
3031

3132
// Helper template to allow turning member functions into a C-style function pointer
3233
// See https://stackoverflow.com/questions/19808054/convert-c-function-pointer-to-c-function-pointer/19809787

‎src/sqlitedb.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -265,6 +265,7 @@ class DBBrowserDB : public QObject
265265
void loadExtensionsFromSettings();
266266

267267
static QStringList Datatypes;
268+
static QStringList DatatypesStrict;
268269

269270
private:
270271
std::vector<std::pair<std::string, std::string> > queryColumnInformation(const std::string& schema_name, const std::string& object_name) const;

0 commit comments

Comments
 (0)