Skip to content

Commit 02db681

Browse files
MKleusbergMKleusberg
committed
Refactor the way we store a database schema
This commit does a lot of refactoring. But most noticeably it changes two things: 1) Instead of saving all objects (tables, views, indices, triggers) of a schema in a common map, we now store tables/views, indices and triggers in three separate maps. This has a number of benefits: - It resembles more closely how SQLite stores its data internally and therefore achieves greater compatability e.g. for databases with a view and a trigger with the same name. - It reduces the need for runtime polymorphism. This makes the code run a bit faster. - By working with explicit types more often more error checking can be done at compile time, making the code less error prone. - The code becomes a lot clearer to read. 2) By making View inherit form Table, views are now a sort of tables. This has the following benefits: - This is a again how SQLite stores views internally which again should increase compatibility a bit. - We mostly treat views and tables the same anyway and with these changes we can unify the code for them even more.
1 parent 13ab455 commit 02db681

17 files changed

+317
-411
lines changed

‎src/AddRecordDialog.cpp‎

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -189,25 +189,22 @@ void AddRecordDialog::populateFields()
189189
bool auto_increment = false;
190190

191191
// Initialize fields, fks and pk differently depending on whether it's a table or a view.
192-
const sqlb::ObjectPtr obj = pdb.getObjectByName(curTable);
193-
if (obj->type() == sqlb::Object::Table)
192+
const sqlb::TablePtr table = pdb.getTableByName(curTable);
193+
fields = table->fields;
194+
if (!table->isView())
194195
{
195-
sqlb::TablePtr m_table = pdb.getObjectByName<sqlb::Table>(curTable);
196-
fields = m_table->fields;
197-
198-
std::transform(fields.begin(), fields.end(), std::back_inserter(fks), [m_table](const auto& f) {
199-
return m_table->constraint({f.name()}, sqlb::Constraint::ForeignKeyConstraintType);
196+
std::transform(fields.begin(), fields.end(), std::back_inserter(fks), [table](const auto& f) {
197+
return table->constraint({f.name()}, sqlb::Constraint::ForeignKeyConstraintType);
200198
});
201199

202-
const auto pk_constraint = m_table->primaryKey();
200+
const auto pk_constraint = table->primaryKey();
203201
if(pk_constraint)
204202
{
205203
pk = pk_constraint->columnList();
206204
auto_increment = pk_constraint->autoIncrement();
207205
}
208206
} else {
209-
sqlb::ViewPtr m_view = pdb.getObjectByName<sqlb::View>(curTable);
210-
fields = m_view->fields;
207+
// It's a view
211208
fks.resize(fields.size(), sqlb::ConstraintPtr(nullptr));
212209
pk = pseudo_pk;
213210
}

‎src/DbStructureModel.cpp‎

Lines changed: 43 additions & 75 deletions
Original file line numberDiff line numberDiff line change
@@ -8,8 +8,6 @@
88
#include <QMimeData>
99
#include <QMessageBox>
1010
#include <QApplication>
11-
#include <unordered_map>
12-
#include <map>
1311

1412
DbStructureModel::DbStructureModel(DBBrowserDB& db, QObject* parent)
1513
: QAbstractItemModel(parent),
@@ -304,108 +302,78 @@ bool DbStructureModel::dropMimeData(const QMimeData* data, Qt::DropAction action
304302
}
305303
}
306304

307-
static long calc_number_of_objects_by_type(const objectMap& objmap, const std::string& type)
308-
{
309-
auto objects = objmap.equal_range(type);
310-
if(objects.first == objmap.end())
311-
return 0;
312-
else
313-
return std::distance(objects.first, objects.second);
314-
}
315-
316305
void DbStructureModel::buildTree(QTreeWidgetItem* parent, const std::string& schema)
317306
{
318-
// Build a map from object type to tree node to simplify finding the correct tree node later
319-
std::unordered_map<std::string, QTreeWidgetItem*> typeToParentItem;
320-
321307
// Get object map for the given schema
322-
objectMap objmap = m_db.schemata[schema];
308+
const objectMap& objmap = m_db.schemata.at(schema);
323309

324310
// Prepare tree
311+
auto num_tables = std::count_if(objmap.tables.begin(), objmap.tables.end(), [](const auto& t) { return !t.second->isView(); });
325312
QTreeWidgetItem* itemTables = new QTreeWidgetItem(parent);
326313
itemTables->setIcon(ColumnName, IconCache::get("table"));
327-
itemTables->setText(ColumnName, tr("Tables (%1)").arg(calc_number_of_objects_by_type(objmap, "table")));
328-
typeToParentItem.insert({"table", itemTables});
314+
itemTables->setText(ColumnName, tr("Tables (%1)").arg(num_tables));
329315

330316
QTreeWidgetItem* itemIndices = new QTreeWidgetItem(parent);
331317
itemIndices->setIcon(ColumnName, IconCache::get("index"));
332-
itemIndices->setText(ColumnName, tr("Indices (%1)").arg(calc_number_of_objects_by_type(objmap, "index")));
333-
typeToParentItem.insert({"index", itemIndices});
318+
itemIndices->setText(ColumnName, tr("Indices (%1)").arg(objmap.indices.size()));
334319

320+
auto num_views = std::count_if(objmap.tables.begin(), objmap.tables.end(), [](const auto& t) { return t.second->isView(); });
335321
QTreeWidgetItem* itemViews = new QTreeWidgetItem(parent);
336322
itemViews->setIcon(ColumnName, IconCache::get("view"));
337-
itemViews->setText(ColumnName, tr("Views (%1)").arg(calc_number_of_objects_by_type(objmap, "view")));
338-
typeToParentItem.insert({"view", itemViews});
323+
itemViews->setText(ColumnName, tr("Views (%1)").arg(num_views));
339324

340325
QTreeWidgetItem* itemTriggers = new QTreeWidgetItem(parent);
341326
itemTriggers->setIcon(ColumnName, IconCache::get("trigger"));
342-
itemTriggers->setText(ColumnName, tr("Triggers (%1)").arg(calc_number_of_objects_by_type(objmap, "trigger")));
343-
typeToParentItem.insert({"trigger", itemTriggers});
344-
345-
// Get all database objects and sort them by their name.
346-
// This needs to be a multimap because SQLite allows views and triggers with the same name which means that names can appear twice.
347-
std::multimap<std::string, sqlb::ObjectPtr> dbobjs;
348-
for(const auto& it : objmap)
349-
dbobjs.insert({it.second->name(), it.second});
327+
itemTriggers->setText(ColumnName, tr("Triggers (%1)").arg(objmap.triggers.size()));
350328

351-
// Add the database objects to the tree nodes
352-
for(const auto& obj : dbobjs)
329+
// Add tables and views
330+
for(const auto& obj : objmap.tables)
353331
{
354-
sqlb::ObjectPtr it = obj.second;
332+
QTreeWidgetItem* item = addNode(schema, obj.second->name(), obj.second->isView() ? "view" : "table", obj.second->originalSql(), obj.second->isView() ? itemViews : itemTables);
355333

356-
// Object node
357-
QTreeWidgetItem* item = addNode(typeToParentItem.at(sqlb::Object::typeToString(it->type())), it, schema);
334+
// Add an extra node for the browsable section
335+
addNode(schema, obj.second->name(), obj.second->isView() ? "view" : "table", obj.second->originalSql(), browsablesRootItem);
358336

359-
// If it is a table or view add the field nodes, add an extra node for the browsable section
360-
if(it->type() == sqlb::Object::Types::Table || it->type() == sqlb::Object::Types::View)
361-
addNode(browsablesRootItem, it, schema);
337+
sqlb::StringVector pk_columns;
338+
if(!obj.second->isView())
339+
{
340+
const auto pk = obj.second->primaryKey();
341+
if(pk)
342+
pk_columns = pk->columnList();
343+
}
362344

363-
// Add field nodes if there are any
364-
sqlb::FieldInfoList fieldList = it->fieldInformation();
365-
if(!fieldList.empty())
345+
for(const auto& field : obj.second->fields)
366346
{
367-
sqlb::StringVector pk_columns;
368-
if(it->type() == sqlb::Object::Types::Table)
369-
{
370-
const auto pk = std::dynamic_pointer_cast<sqlb::Table>(it)->primaryKey();
371-
if(pk)
372-
pk_columns = pk->columnList();
373-
}
347+
bool isPK = contains(pk_columns, field.name());
348+
bool isFK = obj.second->constraint({field.name()}, sqlb::Constraint::ForeignKeyConstraintType) != nullptr;
374349

375-
for(const sqlb::FieldInfo& field : fieldList)
376-
{
377-
QTreeWidgetItem *fldItem = new QTreeWidgetItem(item);
378-
bool isFK = false;
379-
if(it->type() == sqlb::Object::Types::Table)
380-
isFK = std::dynamic_pointer_cast<sqlb::Table>(it)->constraint({field.name}, sqlb::Constraint::ForeignKeyConstraintType) != nullptr;
381-
382-
fldItem->setText(ColumnName, QString::fromStdString(field.name));
383-
fldItem->setText(ColumnObjectType, "field");
384-
fldItem->setText(ColumnDataType, QString::fromStdString(field.type));
385-
fldItem->setText(ColumnSQL, QString::fromStdString(field.sql));
386-
fldItem->setText(ColumnSchema, QString::fromStdString(schema));
387-
if(contains(pk_columns, field.name))
388-
fldItem->setIcon(ColumnName, IconCache::get("field_key"));
389-
else if(isFK)
390-
fldItem->setIcon(ColumnName, IconCache::get("field_fk"));
391-
else
392-
fldItem->setIcon(ColumnName, IconCache::get("field"));
393-
}
350+
addNode(schema, field.name(), "field", field.toString(" ", " "), item, field.type(), isPK ? "_key" : (isFK ? "_fk" : std::string{}));
394351
}
395352
}
353+
354+
// Add indices
355+
for(const auto& obj : objmap.indices)
356+
{
357+
QTreeWidgetItem* item = addNode(schema, obj.second->name(), "index", obj.second->originalSql(), itemIndices);
358+
359+
for(const auto& field : obj.second->fields)
360+
addNode(schema, field.name(), "field", field.toString(" ", " "), item, field.order());
361+
}
362+
363+
// Add triggers
364+
for(const auto& obj : objmap.triggers)
365+
addNode(schema, obj.second->name(), "trigger", obj.second->originalSql(), itemTriggers);
396366
}
397367

398-
QTreeWidgetItem* DbStructureModel::addNode(QTreeWidgetItem* parent, const sqlb::ObjectPtr& object, const std::string& schema)
368+
QTreeWidgetItem* DbStructureModel::addNode(const std::string& schema, const std::string& name, const std::string& object_type, const std::string& sql, QTreeWidgetItem* parent_item, const std::string& data_type, const std::string& icon_suffix)
399369
{
400-
std::string type = sqlb::Object::typeToString(object->type());
401-
402-
QTreeWidgetItem *item = new QTreeWidgetItem(parent);
403-
item->setIcon(ColumnName, IconCache::get(type));
404-
item->setText(ColumnName, QString::fromStdString(object->name()));
405-
item->setText(ColumnObjectType, QString::fromStdString(type));
406-
item->setText(ColumnSQL, QString::fromStdString(object->originalSql()));
370+
QTreeWidgetItem* item = new QTreeWidgetItem(parent_item);
371+
item->setText(ColumnName, QString::fromStdString(name));
372+
item->setText(ColumnObjectType, QString::fromStdString(object_type));
373+
item->setText(ColumnDataType, QString::fromStdString(data_type));
374+
item->setText(ColumnSQL, QString::fromStdString(sql));
407375
item->setText(ColumnSchema, QString::fromStdString(schema));
408-
376+
item->setIcon(ColumnName, IconCache::get(object_type + icon_suffix));
409377
return item;
410378
}
411379

‎src/DbStructureModel.h‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,6 @@
66

77
class DBBrowserDB;
88
class QTreeWidgetItem;
9-
namespace sqlb { class Object; using ObjectPtr = std::shared_ptr<Object>; }
109

1110
class DbStructureModel : public QAbstractItemModel
1211
{
@@ -50,7 +49,7 @@ public slots:
5049
bool m_dropEnquotedNames;
5150

5251
void buildTree(QTreeWidgetItem* parent, const std::string& schema);
53-
QTreeWidgetItem* addNode(QTreeWidgetItem* parent, const sqlb::ObjectPtr& object, const std::string& schema);
52+
QTreeWidgetItem* addNode(const std::string& schema, const std::string& name, const std::string& object_type, const std::string& sql, QTreeWidgetItem* parent_item, const std::string& data_type = {}, const std::string& icon_suffix = {});
5453
QString getNameForDropping(const QString& domain, const QString& object, const QString& field) const;
5554
};
5655

‎src/EditIndexDialog.cpp‎

Lines changed: 11 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -25,20 +25,18 @@ EditIndexDialog::EditIndexDialog(DBBrowserDB& db, const sqlb::ObjectIdentifier&
2525
{
2626
for(const auto& it : pdb.schemata)
2727
{
28-
auto tables = it.second.equal_range("table");
29-
for(auto jt=tables.first;jt!=tables.second;++jt)
28+
for(const auto& jt : it.second.tables)
3029
{
3130
// Only show the schema name for non-main schemata
32-
sqlb::ObjectIdentifier obj(it.first, jt->second->name());
31+
sqlb::ObjectIdentifier obj(it.first, jt.first);
3332
dbobjs.insert({obj.toDisplayString(), obj});
3433
}
3534
}
3635
} else { // If this is an existing index, only offer tables of the current database schema
37-
auto tables = pdb.schemata[curIndex.schema()].equal_range("table");
38-
for(auto it=tables.first;it!=tables.second;++it)
36+
for(const auto& it : pdb.schemata[curIndex.schema()].tables)
3937
{
4038
// Only show the schema name for non-main schemata
41-
sqlb::ObjectIdentifier obj(curIndex.schema(), it->second->name());
39+
sqlb::ObjectIdentifier obj(curIndex.schema(), it.first);
4240
dbobjs.insert({obj.toDisplayString(), obj});
4341
}
4442
}
@@ -54,7 +52,7 @@ EditIndexDialog::EditIndexDialog(DBBrowserDB& db, const sqlb::ObjectIdentifier&
5452
if(!newIndex)
5553
{
5654
// Load the current layout and fill in the dialog fields
57-
index = *(pdb.getObjectByName<sqlb::Index>(curIndex));
55+
index = *pdb.getIndexByName(curIndex);
5856

5957
ui->editIndexName->blockSignals(true);
6058
ui->editIndexName->setText(QString::fromStdString(index.name()));
@@ -114,26 +112,25 @@ void EditIndexDialog::tableChanged(const QString& new_table, bool initialLoad)
114112
void EditIndexDialog::updateColumnLists()
115113
{
116114
// Fill the table column list
117-
sqlb::TablePtr table = pdb.getObjectByName<sqlb::Table>(sqlb::ObjectIdentifier(ui->comboTableName->currentData().toString().toStdString()));
115+
sqlb::TablePtr table = pdb.getTableByName(sqlb::ObjectIdentifier(ui->comboTableName->currentData().toString().toStdString()));
118116
if(!table)
119117
return;
120-
sqlb::FieldInfoList tableFields = table->fieldInformation();
121-
ui->tableTableColumns->setRowCount(static_cast<int>(tableFields.size()));
118+
ui->tableTableColumns->setRowCount(static_cast<int>(table->fields.size()));
122119
int tableRows = 0;
123-
for(size_t i=0;i<tableFields.size();++i)
120+
for(const auto& field: table->fields)
124121
{
125122
// When we're doing the initial loading and this field already is in the index to edit, then don't add it to the
126123
// list of table columns. It will be added to the list of index columns in the next step. When this is not the initial
127124
// loading, the index column list is empty, so this check will always be true.
128-
if(sqlb::findField(index, tableFields.at(i).name) == index.fields.end())
125+
if(sqlb::findField(index, field.name()) == index.fields.end())
129126
{
130127
// Put the name of the field in the first column
131-
QTableWidgetItem* name = new QTableWidgetItem(QString::fromStdString(tableFields.at(i).name));
128+
QTableWidgetItem* name = new QTableWidgetItem(QString::fromStdString(field.name()));
132129
name->setFlags(Qt::ItemIsSelectable | Qt::ItemIsEnabled);
133130
ui->tableTableColumns->setItem(tableRows, 0, name);
134131

135132
// Put the data type in the second column
136-
QTableWidgetItem* type = new QTableWidgetItem(QString::fromStdString(tableFields.at(i).type));
133+
QTableWidgetItem* type = new QTableWidgetItem(QString::fromStdString(field.type()));
137134
type->setFlags(Qt::ItemIsSelectable | Qt::ItemIsEnabled);
138135
ui->tableTableColumns->setItem(tableRows, 1, type);
139136

‎src/EditTableDialog.cpp‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -78,7 +78,7 @@ EditTableDialog::EditTableDialog(DBBrowserDB& db, const sqlb::ObjectIdentifier&
7878
if(m_bNewTable == false)
7979
{
8080
// Existing table, so load and set the current layout
81-
m_table = *(pdb.getObjectByName<sqlb::Table>(curTable));
81+
m_table = *pdb.getTableByName(curTable);
8282
ui->labelEditWarning->setVisible(!m_table.fullyParsed());
8383

8484
// Initialise the list of tracked columns for table layout changes
@@ -492,13 +492,11 @@ void EditTableDialog::fieldItemChanged(QTreeWidgetItem *item, int column)
492492
if(!m_bNewTable)
493493
{
494494
const auto pk = m_table.primaryKey();
495-
const auto tables = pdb.schemata[curTable.schema()].equal_range("table");
496-
for(auto it=tables.first;it!=tables.second;++it)
495+
for(const auto& it : pdb.schemata[curTable.schema()].tables)
497496
{
498-
const sqlb::ObjectPtr& fkobj = it->second;
497+
const sqlb::TablePtr& fkobj = it.second;
499498

500-
501-
auto fks = std::dynamic_pointer_cast<sqlb::Table>(fkobj)->constraints({}, sqlb::Constraint::ForeignKeyConstraintType);
499+
auto fks = fkobj->constraints({}, sqlb::Constraint::ForeignKeyConstraintType);
502500
for(const sqlb::ConstraintPtr& fkptr : fks)
503501
{
504502
auto fk = std::dynamic_pointer_cast<sqlb::ForeignKeyClause>(fkptr);
@@ -591,7 +589,7 @@ void EditTableDialog::fieldItemChanged(QTreeWidgetItem *item, int column)
591589
// to at least replace all troublesome NULL values by the default value
592590
SqliteTableModel m(pdb, this);
593591
m.setQuery(QString("SELECT COUNT(%1) FROM %2 WHERE coalesce(NULL,%3) IS NULL;").arg(
594-
QString::fromStdString(sqlb::joinStringVector(sqlb::escapeIdentifier(pdb.getObjectByName<sqlb::Table>(curTable)->rowidColumns()), ",")),
592+
QString::fromStdString(sqlb::joinStringVector(sqlb::escapeIdentifier(pdb.getTableByName(curTable)->rowidColumns()), ",")),
595593
QString::fromStdString(curTable.toString()),
596594
QString::fromStdString(sqlb::escapeIdentifier(field.name()))));
597595
if(!m.completeCache())

‎src/ExportDataDialog.cpp‎

Lines changed: 3 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -47,18 +47,10 @@ ExportDataDialog::ExportDataDialog(DBBrowserDB& db, ExportFormats format, QWidge
4747
// Get list of tables to export
4848
for(const auto& it : pdb.schemata)
4949
{
50-
const auto tables = it.second.equal_range("table");
51-
const auto views = it.second.equal_range("view");
52-
std::map<std::string, sqlb::ObjectPtr> objects;
53-
for(auto jt=tables.first;jt!=tables.second;++jt)
54-
objects.insert({jt->second->name(), jt->second});
55-
for(auto jt=views.first;jt!=views.second;++jt)
56-
objects.insert({jt->second->name(), jt->second});
57-
58-
for(const auto& jt : objects)
50+
for(const auto& jt : it.second.tables)
5951
{
60-
sqlb::ObjectIdentifier obj(it.first, jt.second->name());
61-
QListWidgetItem* item = new QListWidgetItem(IconCache::get(sqlb::Object::typeToString(jt.second->type())), QString::fromStdString(obj.toDisplayString()));
52+
sqlb::ObjectIdentifier obj(it.first, jt.first);
53+
QListWidgetItem* item = new QListWidgetItem(IconCache::get(jt.second->isView() ? "view" : "table"), QString::fromStdString(obj.toDisplayString()));
6254
item->setData(Qt::UserRole, QString::fromStdString(obj.toSerialised()));
6355
ui->listTables->addItem(item);
6456
}

‎src/ExportSqlDialog.cpp‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -29,9 +29,8 @@ ExportSqlDialog::ExportSqlDialog(DBBrowserDB* db, QWidget* parent, const QString
2929
ui->comboOldSchema->setCurrentIndex(Settings::getValue("exportsql", "oldschema").toInt());
3030

3131
// Get list of tables to export
32-
const auto objects = pdb->schemata["main"].equal_range("table");
33-
for(auto it=objects.first;it!=objects.second;++it)
34-
ui->listTables->addItem(new QListWidgetItem(IconCache::get(sqlb::Object::typeToString(it->second->type())), QString::fromStdString(it->second->name())));
32+
for(const auto& it : pdb->schemata["main"].tables)
33+
ui->listTables->addItem(new QListWidgetItem(IconCache::get(it.second->isView() ? "view" : "table"), QString::fromStdString(it.first)));
3534

3635
// Sort list of tables and select the table specified in the
3736
// selection parameter or all tables if table not specified

0 commit comments

Comments
 (0)