From ad505aad5936fdf2eaf29dd91731e0900d1ab6d9 Mon Sep 17 00:00:00 2001 From: Marco Bubke Date: Tue, 14 Dec 2021 13:36:23 +0100 Subject: Sqlite: Strict table support With Sqlite 3.37 strict tables are introduced: https://www.sqlite.org/stricttables.html The introduce strict column types. So you can not add a text to an integer column anymore. Additionally they introduce the "any" column which is a dynamic type. Change-Id: I43c0410821aa154e7de83e24bd221a232f98e910 Reviewed-by: Tim Jenssen Reviewed-by: Qt CI Bot --- src/libs/sqlite/CMakeLists.txt | 2 +- src/libs/sqlite/createtablesqlstatementbuilder.cpp | 308 ---------- src/libs/sqlite/createtablesqlstatementbuilder.h | 323 ++++++++++- src/libs/sqlite/sqlitecolumn.h | 85 ++- src/libs/sqlite/sqliteglobal.h | 1 + src/libs/sqlite/sqlitetable.h | 40 +- src/libs/sqlite/sqlstatementbuilder.cpp | 20 - src/libs/sqlite/sqlstatementbuilder.h | 2 - .../createtablesqlstatementbuilder-test.cpp | 635 +++++++++++++++++++-- tests/unit/unittest/sqlitecolumn-test.cpp | 128 ++++- tests/unit/unittest/sqlitetable-test.cpp | 302 +++++++++- tests/unit/unittest/sqlstatementbuilder-test.cpp | 10 - 12 files changed, 1388 insertions(+), 468 deletions(-) delete mode 100644 src/libs/sqlite/createtablesqlstatementbuilder.cpp diff --git a/src/libs/sqlite/CMakeLists.txt b/src/libs/sqlite/CMakeLists.txt index c200933925..3ad2d371e8 100644 --- a/src/libs/sqlite/CMakeLists.txt +++ b/src/libs/sqlite/CMakeLists.txt @@ -14,7 +14,7 @@ add_qtc_library(Sqlite ../3rdparty/sqlite/config.h ../3rdparty/sqlite/sqlite.h constraints.h - createtablesqlstatementbuilder.cpp createtablesqlstatementbuilder.h + createtablesqlstatementbuilder.h lastchangedrowid.h sqlitealgorithms.h sqlitebasestatement.cpp sqlitebasestatement.h diff --git a/src/libs/sqlite/createtablesqlstatementbuilder.cpp b/src/libs/sqlite/createtablesqlstatementbuilder.cpp deleted file mode 100644 index d237d8e8fc..0000000000 --- a/src/libs/sqlite/createtablesqlstatementbuilder.cpp +++ /dev/null @@ -1,308 +0,0 @@ -/**************************************************************************** -** -** Copyright (C) 2016 The Qt Company Ltd. -** Contact: https://www.qt.io/licensing/ -** -** This file is part of Qt Creator. -** -** Commercial License Usage -** Licensees holding valid commercial Qt licenses may use this file in -** accordance with the commercial license agreement provided with the -** Software or, alternatively, in accordance with the terms contained in -** a written agreement between you and The Qt Company. For licensing terms -** and conditions see https://www.qt.io/terms-conditions. For further -** information use the contact form at https://www.qt.io/contact-us. -** -** GNU General Public License Usage -** Alternatively, this file may be used under the terms of the GNU -** General Public License version 3 as published by the Free Software -** Foundation with exceptions as appearing in the file LICENSE.GPL3-EXCEPT -** included in the packaging of this file. Please review the following -** information to ensure the GNU General Public License requirements will -** be met: https://www.gnu.org/licenses/gpl-3.0.html. -** -****************************************************************************/ - -#include "createtablesqlstatementbuilder.h" - -namespace Sqlite { - -CreateTableSqlStatementBuilder::CreateTableSqlStatementBuilder() - : m_sqlStatementBuilder("CREATE $temporaryTABLE $ifNotExits$table($columnDefinitions)$withoutRowId") -{ -} - -void CreateTableSqlStatementBuilder::setTableName(Utils::SmallString &&tableName) -{ - m_sqlStatementBuilder.clear(); - - this->m_tableName = std::move(tableName); -} - -void CreateTableSqlStatementBuilder::addColumn(Utils::SmallStringView columnName, - ColumnType columnType, - Constraints &&constraints) -{ - m_sqlStatementBuilder.clear(); - - m_columns.emplace_back(Utils::SmallStringView{}, columnName, columnType, std::move(constraints)); -} - -void CreateTableSqlStatementBuilder::addConstraint(TableConstraint &&constraint) -{ - m_tableConstraints.push_back(std::move(constraint)); -} - -void CreateTableSqlStatementBuilder::setConstraints(TableConstraints constraints) -{ - m_tableConstraints = std::move(constraints); -} - -void CreateTableSqlStatementBuilder::setColumns(SqliteColumns columns) -{ - m_sqlStatementBuilder.clear(); - - m_columns = std::move(columns); -} - -void CreateTableSqlStatementBuilder::setUseWithoutRowId(bool useWithoutRowId) -{ - m_useWithoutRowId = useWithoutRowId; -} - -void CreateTableSqlStatementBuilder::setUseIfNotExists(bool useIfNotExists) -{ - m_useIfNotExits = useIfNotExists; -} - -void CreateTableSqlStatementBuilder::setUseTemporaryTable(bool useTemporaryTable) -{ - m_useTemporaryTable = useTemporaryTable; -} - -void CreateTableSqlStatementBuilder::clear() -{ - m_sqlStatementBuilder.clear(); - m_columns.clear(); - m_tableName.clear(); - m_useWithoutRowId = false; -} - -void CreateTableSqlStatementBuilder::clearColumns() -{ - m_sqlStatementBuilder.clear(); - m_columns.clear(); -} - -Utils::SmallStringView CreateTableSqlStatementBuilder::sqlStatement() const -{ - if (!m_sqlStatementBuilder.isBuild()) - bindAll(); - - return m_sqlStatementBuilder.sqlStatement(); -} - -bool CreateTableSqlStatementBuilder::isValid() const -{ - return m_tableName.hasContent() && !m_columns.empty(); -} - -namespace { -Utils::SmallStringView actionToText(ForeignKeyAction action) -{ - switch (action) { - case ForeignKeyAction::NoAction: - return "NO ACTION"; - case ForeignKeyAction::Restrict: - return "RESTRICT"; - case ForeignKeyAction::SetNull: - return "SET NULL"; - case ForeignKeyAction::SetDefault: - return "SET DEFAULT"; - case ForeignKeyAction::Cascade: - return "CASCADE"; - } - - return ""; -} - -class ContraintsVisiter -{ -public: - ContraintsVisiter(Utils::SmallString &columnDefinitionString) - : columnDefinitionString(columnDefinitionString) - {} - - void operator()(const Unique &) { columnDefinitionString.append(" UNIQUE"); } - - void operator()(const PrimaryKey &primaryKey) - { - columnDefinitionString.append(" PRIMARY KEY"); - if (primaryKey.autoincrement == AutoIncrement::Yes) - columnDefinitionString.append(" AUTOINCREMENT"); - } - - void operator()(const ForeignKey &foreignKey) - { - columnDefinitionString.append(" REFERENCES "); - columnDefinitionString.append(foreignKey.table); - - if (foreignKey.column.hasContent()) { - columnDefinitionString.append("("); - columnDefinitionString.append(foreignKey.column); - columnDefinitionString.append(")"); - } - - if (foreignKey.updateAction != ForeignKeyAction::NoAction) { - columnDefinitionString.append(" ON UPDATE "); - columnDefinitionString.append(actionToText(foreignKey.updateAction)); - } - - if (foreignKey.deleteAction != ForeignKeyAction::NoAction) { - columnDefinitionString.append(" ON DELETE "); - columnDefinitionString.append(actionToText(foreignKey.deleteAction)); - } - - if (foreignKey.enforcement == Enforment::Deferred) - columnDefinitionString.append(" DEFERRABLE INITIALLY DEFERRED"); - } - - void operator()(const NotNull &) { columnDefinitionString.append(" NOT NULL"); } - - void operator()(const Check &check) - { - columnDefinitionString.append(" CHECK ("); - columnDefinitionString.append(check.expression); - columnDefinitionString.append(")"); - } - - void operator()(const DefaultValue &defaultValue) - { - columnDefinitionString.append(" DEFAULT "); - switch (defaultValue.value.type()) { - case Sqlite::ValueType::Integer: - columnDefinitionString.append( - Utils::SmallString::number(defaultValue.value.toInteger())); - break; - case Sqlite::ValueType::Float: - columnDefinitionString.append(Utils::SmallString::number(defaultValue.value.toFloat())); - break; - case Sqlite::ValueType::String: - columnDefinitionString.append("'"); - columnDefinitionString.append(defaultValue.value.toStringView()); - columnDefinitionString.append("'"); - break; - default: - break; - } - } - - void operator()(const DefaultExpression &defaultexpression) - { - columnDefinitionString.append(" DEFAULT ("); - columnDefinitionString.append(defaultexpression.expression); - columnDefinitionString.append(")"); - } - - void operator()(const Collate &collate) - { - columnDefinitionString.append(" COLLATE "); - columnDefinitionString.append(collate.collation); - } - - void operator()(const GeneratedAlways &generatedAlways) - { - columnDefinitionString.append(" GENERATED ALWAYS AS ("); - columnDefinitionString.append(generatedAlways.expression); - columnDefinitionString.append(")"); - - if (generatedAlways.storage == Sqlite::GeneratedAlwaysStorage::Virtual) - columnDefinitionString.append(" VIRTUAL"); - else - columnDefinitionString.append(" STORED"); - } - - Utils::SmallString &columnDefinitionString; -}; - -class TableContraintsVisiter -{ -public: - TableContraintsVisiter(Utils::SmallString &columnDefinitionString) - : columnDefinitionString(columnDefinitionString) - {} - - void operator()(const TablePrimaryKey &primaryKey) - { - columnDefinitionString.append("PRIMARY KEY("); - columnDefinitionString.append(primaryKey.columns.join(", ")); - columnDefinitionString.append(")"); - } - - Utils::SmallString &columnDefinitionString; -}; -} // namespace -void CreateTableSqlStatementBuilder::bindColumnDefinitionsAndTableConstraints() const -{ - Utils::SmallStringVector columnDefinitionStrings; - columnDefinitionStrings.reserve(m_columns.size()); - - for (const Column &column : m_columns) { - auto columnDefinitionString = Utils::SmallString::join( - {column.name, SqlStatementBuilder::columnTypeToString(column.type)}); - - ContraintsVisiter visiter{columnDefinitionString}; - - for (const Constraint &constraint : column.constraints) - Utils::visit(visiter, constraint); - - columnDefinitionStrings.push_back(std::move(columnDefinitionString)); - } - - for (const TableConstraint &constraint : m_tableConstraints) { - Utils::SmallString columnDefinitionString; - - TableContraintsVisiter visiter{columnDefinitionString}; - Utils::visit(visiter, constraint); - - columnDefinitionStrings.push_back(std::move(columnDefinitionString)); - } - - m_sqlStatementBuilder.bind("$columnDefinitions", columnDefinitionStrings); -} - -void CreateTableSqlStatementBuilder::bindAll() const -{ - m_sqlStatementBuilder.bind("$table", m_tableName.clone()); - - bindTemporary(); - bindIfNotExists(); - bindColumnDefinitionsAndTableConstraints(); - bindWithoutRowId(); -} - -void CreateTableSqlStatementBuilder::bindWithoutRowId() const -{ - if (m_useWithoutRowId) - m_sqlStatementBuilder.bind("$withoutRowId", " WITHOUT ROWID"); - else - m_sqlStatementBuilder.bindEmptyText("$withoutRowId"); -} - -void CreateTableSqlStatementBuilder::bindIfNotExists() const -{ - if (m_useIfNotExits) - m_sqlStatementBuilder.bind("$ifNotExits", "IF NOT EXISTS "); - else - m_sqlStatementBuilder.bindEmptyText("$ifNotExits"); -} - -void CreateTableSqlStatementBuilder::bindTemporary() const -{ - if (m_useTemporaryTable) - m_sqlStatementBuilder.bind("$temporary", "TEMPORARY "); - else - m_sqlStatementBuilder.bindEmptyText("$temporary"); -} - -} // namespace Sqlite diff --git a/src/libs/sqlite/createtablesqlstatementbuilder.h b/src/libs/sqlite/createtablesqlstatementbuilder.h index 3e3743e9da..fb53e68a42 100644 --- a/src/libs/sqlite/createtablesqlstatementbuilder.h +++ b/src/libs/sqlite/createtablesqlstatementbuilder.h @@ -29,43 +29,326 @@ #include "sqlstatementbuilder.h" #include "tableconstraints.h" +#include + namespace Sqlite { +template class SQLITE_EXPORT CreateTableSqlStatementBuilder { public: - CreateTableSqlStatementBuilder(); + CreateTableSqlStatementBuilder() + : m_sqlStatementBuilder(templateText()) + {} + + void setTableName(Utils::SmallString &&tableName) + { + m_sqlStatementBuilder.clear(); - void setTableName(Utils::SmallString &&tableName); + this->m_tableName = std::move(tableName); + } void addColumn(Utils::SmallStringView columnName, ColumnType columnType, - Constraints &&constraints = {}); - void addConstraint(TableConstraint &&constraint); - void setConstraints(TableConstraints constraints); - void setColumns(SqliteColumns columns); - void setUseWithoutRowId(bool useWithoutRowId); - void setUseIfNotExists(bool useIfNotExists); - void setUseTemporaryTable(bool useTemporaryTable); + Constraints &&constraints = {}) + { + m_sqlStatementBuilder.clear(); + + m_columns.emplace_back(Utils::SmallStringView{}, columnName, columnType, std::move(constraints)); + } + void addConstraint(TableConstraint &&constraint) + { + m_tableConstraints.push_back(std::move(constraint)); + } + void setConstraints(TableConstraints constraints) + { + m_tableConstraints = std::move(constraints); + } + void setColumns(BasicColumns columns) + { + m_sqlStatementBuilder.clear(); + + m_columns = std::move(columns); + } + + void setUseWithoutRowId(bool useWithoutRowId) { m_useWithoutRowId = useWithoutRowId; } + + void setUseIfNotExists(bool useIfNotExists) { m_useIfNotExits = useIfNotExists; } + + void setUseTemporaryTable(bool useTemporaryTable) { m_useTemporaryTable = useTemporaryTable; } + + void clear() + { + m_sqlStatementBuilder.clear(); + m_columns.clear(); + m_tableName.clear(); + m_useWithoutRowId = false; + } + + void clearColumns() + { + m_sqlStatementBuilder.clear(); + m_columns.clear(); + } + + Utils::SmallStringView sqlStatement() const + { + if (!m_sqlStatementBuilder.isBuild()) + bindAll(); + + return m_sqlStatementBuilder.sqlStatement(); + } + + bool isValid() const { return m_tableName.hasContent() && !m_columns.empty(); } + +private: + static Utils::SmallStringView templateText() + { + if constexpr (std::is_same_v) { + return "CREATE $temporaryTABLE $ifNotExits$table($columnDefinitions)$withoutRowId"; + } + + return "CREATE $temporaryTABLE $ifNotExits$table($columnDefinitions)$withoutRowId STRICT"; + } + + static Utils::SmallString columnTypeToString(ColumnType columnType) + { + if constexpr (std::is_same_v) { + switch (columnType) { + case ColumnType::Numeric: + return " NUMERIC"; + case ColumnType::Integer: + return " INTEGER"; + case ColumnType::Real: + return " REAL"; + case ColumnType::Text: + return " TEXT"; + case ColumnType::Blob: + return " BLOB"; + case ColumnType::None: + return {}; + } + } else { + switch (columnType) { + case ColumnType::Any: + return " ANY"; + case ColumnType::Int: + return " INT"; + case ColumnType::Integer: + return " INTEGER"; + case ColumnType::Real: + return " REAL"; + case ColumnType::Text: + return " TEXT"; + case ColumnType::Blob: + return " BLOB"; + } + } + + return ""; + } + + static Utils::SmallStringView actionToText(ForeignKeyAction action) + { + switch (action) { + case ForeignKeyAction::NoAction: + return "NO ACTION"; + case ForeignKeyAction::Restrict: + return "RESTRICT"; + case ForeignKeyAction::SetNull: + return "SET NULL"; + case ForeignKeyAction::SetDefault: + return "SET DEFAULT"; + case ForeignKeyAction::Cascade: + return "CASCADE"; + } + + return ""; + } + + class ContraintsVisiter + { + public: + ContraintsVisiter(Utils::SmallString &columnDefinitionString) + : columnDefinitionString(columnDefinitionString) + {} + + void operator()(const Unique &) { columnDefinitionString.append(" UNIQUE"); } + + void operator()(const PrimaryKey &primaryKey) + { + columnDefinitionString.append(" PRIMARY KEY"); + if (primaryKey.autoincrement == AutoIncrement::Yes) + columnDefinitionString.append(" AUTOINCREMENT"); + } + + void operator()(const ForeignKey &foreignKey) + { + columnDefinitionString.append(" REFERENCES "); + columnDefinitionString.append(foreignKey.table); + + if (foreignKey.column.hasContent()) { + columnDefinitionString.append("("); + columnDefinitionString.append(foreignKey.column); + columnDefinitionString.append(")"); + } + + if (foreignKey.updateAction != ForeignKeyAction::NoAction) { + columnDefinitionString.append(" ON UPDATE "); + columnDefinitionString.append(actionToText(foreignKey.updateAction)); + } + + if (foreignKey.deleteAction != ForeignKeyAction::NoAction) { + columnDefinitionString.append(" ON DELETE "); + columnDefinitionString.append(actionToText(foreignKey.deleteAction)); + } + + if (foreignKey.enforcement == Enforment::Deferred) + columnDefinitionString.append(" DEFERRABLE INITIALLY DEFERRED"); + } + + void operator()(const NotNull &) { columnDefinitionString.append(" NOT NULL"); } + + void operator()(const Check &check) + { + columnDefinitionString.append(" CHECK ("); + columnDefinitionString.append(check.expression); + columnDefinitionString.append(")"); + } + + void operator()(const DefaultValue &defaultValue) + { + columnDefinitionString.append(" DEFAULT "); + switch (defaultValue.value.type()) { + case Sqlite::ValueType::Integer: + columnDefinitionString.append( + Utils::SmallString::number(defaultValue.value.toInteger())); + break; + case Sqlite::ValueType::Float: + columnDefinitionString.append(Utils::SmallString::number(defaultValue.value.toFloat())); + break; + case Sqlite::ValueType::String: + columnDefinitionString.append("'"); + columnDefinitionString.append(defaultValue.value.toStringView()); + columnDefinitionString.append("'"); + break; + default: + break; + } + } + + void operator()(const DefaultExpression &defaultexpression) + { + columnDefinitionString.append(" DEFAULT ("); + columnDefinitionString.append(defaultexpression.expression); + columnDefinitionString.append(")"); + } + + void operator()(const Collate &collate) + { + columnDefinitionString.append(" COLLATE "); + columnDefinitionString.append(collate.collation); + } + + void operator()(const GeneratedAlways &generatedAlways) + { + columnDefinitionString.append(" GENERATED ALWAYS AS ("); + columnDefinitionString.append(generatedAlways.expression); + columnDefinitionString.append(")"); + + if (generatedAlways.storage == Sqlite::GeneratedAlwaysStorage::Virtual) + columnDefinitionString.append(" VIRTUAL"); + else + columnDefinitionString.append(" STORED"); + } + + Utils::SmallString &columnDefinitionString; + }; + + class TableContraintsVisiter + { + public: + TableContraintsVisiter(Utils::SmallString &columnDefinitionString) + : columnDefinitionString(columnDefinitionString) + {} + + void operator()(const TablePrimaryKey &primaryKey) + { + columnDefinitionString.append("PRIMARY KEY("); + columnDefinitionString.append(primaryKey.columns.join(", ")); + columnDefinitionString.append(")"); + } + + Utils::SmallString &columnDefinitionString; + }; + + void bindColumnDefinitionsAndTableConstraints() const + { + Utils::SmallStringVector columnDefinitionStrings; + columnDefinitionStrings.reserve(m_columns.size()); + + for (const BasicColumn &column : m_columns) { + auto columnDefinitionString = Utils::SmallString::join( + {column.name, columnTypeToString(column.type)}); + + ContraintsVisiter visiter{columnDefinitionString}; + + for (const Constraint &constraint : column.constraints) + Utils::visit(visiter, constraint); + + columnDefinitionStrings.push_back(std::move(columnDefinitionString)); + } + + for (const TableConstraint &constraint : m_tableConstraints) { + Utils::SmallString columnDefinitionString; + + TableContraintsVisiter visiter{columnDefinitionString}; + Utils::visit(visiter, constraint); + + columnDefinitionStrings.push_back(std::move(columnDefinitionString)); + } + + m_sqlStatementBuilder.bind("$columnDefinitions", columnDefinitionStrings); + } + + void bindAll() const + { + m_sqlStatementBuilder.bind("$table", m_tableName.clone()); - void clear(); - void clearColumns(); + bindTemporary(); + bindIfNotExists(); + bindColumnDefinitionsAndTableConstraints(); + bindWithoutRowId(); + } - Utils::SmallStringView sqlStatement() const; + void bindWithoutRowId() const + { + if (m_useWithoutRowId) + m_sqlStatementBuilder.bind("$withoutRowId", " WITHOUT ROWID"); + else + m_sqlStatementBuilder.bindEmptyText("$withoutRowId"); + } - bool isValid() const; + void bindIfNotExists() const + { + if (m_useIfNotExits) + m_sqlStatementBuilder.bind("$ifNotExits", "IF NOT EXISTS "); + else + m_sqlStatementBuilder.bindEmptyText("$ifNotExits"); + } -protected: - void bindColumnDefinitionsAndTableConstraints() const; - void bindAll() const; - void bindWithoutRowId() const; - void bindIfNotExists() const; - void bindTemporary() const; + void bindTemporary() const + { + if (m_useTemporaryTable) + m_sqlStatementBuilder.bind("$temporary", "TEMPORARY "); + else + m_sqlStatementBuilder.bindEmptyText("$temporary"); + } private: mutable SqlStatementBuilder m_sqlStatementBuilder; Utils::SmallString m_tableName; - SqliteColumns m_columns; + BasicColumns m_columns; TableConstraints m_tableConstraints; bool m_useWithoutRowId = false; bool m_useIfNotExits = false; diff --git a/src/libs/sqlite/sqlitecolumn.h b/src/libs/sqlite/sqlitecolumn.h index 35a5e0f25c..ecca180559 100644 --- a/src/libs/sqlite/sqlitecolumn.h +++ b/src/libs/sqlite/sqlitecolumn.h @@ -28,19 +28,20 @@ #include "constraints.h" #include +#include namespace Sqlite { - -class Column +template +class BasicColumn { public: - Column() = default; + BasicColumn() = default; - Column(Utils::SmallStringView tableName, - Utils::SmallStringView name, - ColumnType type = ColumnType::None, - Constraints &&constraints = {}) + BasicColumn(Utils::SmallStringView tableName, + Utils::SmallStringView name, + ColumnType type = {}, + Constraints &&constraints = {}) : constraints(std::move(constraints)) , name(name) , tableName(tableName) @@ -50,31 +51,46 @@ public: void clear() { name.clear(); - type = ColumnType::Numeric; + type = {}; constraints = {}; } Utils::SmallString typeString() const { - switch (type) { - case ColumnType::None: - return {}; - case ColumnType::Numeric: - return "NUMERIC"; - case ColumnType::Integer: - return "INTEGER"; - case ColumnType::Real: - return "REAL"; - case ColumnType::Text: - return "TEXT"; - case ColumnType::Blob: - return "BLOB"; + if constexpr (std::is_same_v) { + switch (type) { + case ColumnType::None: + return {}; + case ColumnType::Numeric: + return "NUMERIC"; + case ColumnType::Integer: + return "INTEGER"; + case ColumnType::Real: + return "REAL"; + case ColumnType::Text: + return "TEXT"; + case ColumnType::Blob: + return "BLOB"; + } + } else { + switch (type) { + case ColumnType::Any: + return "ANY"; + case ColumnType::Int: + return "INT"; + case ColumnType::Integer: + return "INTEGER"; + case ColumnType::Real: + return "REAL"; + case ColumnType::Text: + return "TEXT"; + case ColumnType::Blob: + return "BLOB"; + } } - - Q_UNREACHABLE(); } - friend bool operator==(const Column &first, const Column &second) + friend bool operator==(const BasicColumn &first, const BasicColumn &second) { return first.name == second.name && first.type == second.type && first.constraints == second.constraints && first.tableName == second.tableName; @@ -84,11 +100,24 @@ public: Constraints constraints; Utils::SmallString name; Utils::SmallString tableName; - ColumnType type = ColumnType::Numeric; + ColumnType type = {}; }; // namespace Sqlite -using SqliteColumns = std::vector; -using SqliteColumnConstReference = std::reference_wrapper; -using SqliteColumnConstReferences = std::vector; +using Column = BasicColumn; +using StrictColumn = BasicColumn; + +using Columns = std::vector; +using StrictColumns = std::vector; +using ColumnConstReference = std::reference_wrapper; +using StrictColumnConstReference = std::reference_wrapper; +using ColumnConstReferences = std::vector; +using StrictColumnConstReferences = std::vector; + +template +using BasicColumns = std::vector>; +template +using BasicColumnConstReference = std::reference_wrapper>; +template +using BasicColumnConstReferences = std::vector>; } // namespace Sqlite diff --git a/src/libs/sqlite/sqliteglobal.h b/src/libs/sqlite/sqliteglobal.h index b910a05a81..f8a13e0a97 100644 --- a/src/libs/sqlite/sqliteglobal.h +++ b/src/libs/sqlite/sqliteglobal.h @@ -40,6 +40,7 @@ namespace Sqlite { enum class ColumnType : char { None, Numeric, Integer, Real, Text, Blob }; +enum class StrictColumnType : char { Any, Integer, Int, Real, Text, Blob }; enum class ConstraintType : char { NoConstraint, PrimaryKey, Unique, ForeignKey }; diff --git a/src/libs/sqlite/sqlitetable.h b/src/libs/sqlite/sqlitetable.h index 1c1f686975..d0bb850656 100644 --- a/src/libs/sqlite/sqlitetable.h +++ b/src/libs/sqlite/sqlitetable.h @@ -35,10 +35,15 @@ namespace Sqlite { class Database; -class Table +template +class BasicTable { public: - Table(std::size_t reserve = 10) + using Column = ::Sqlite::BasicColumn; + using ColumnConstReferences = ::Sqlite::BasicColumnConstReferences; + using Columns = ::Sqlite::BasicColumns; + + BasicTable(std::size_t reserve = 10) { m_sqliteColumns.reserve(reserve); m_sqliteIndices.reserve(reserve); @@ -71,9 +76,7 @@ public: m_useTemporaryTable = useTemporaryTable; } - Column &addColumn(Utils::SmallStringView name, - ColumnType type = ColumnType::None, - Constraints &&constraints = {}) + Column &addColumn(Utils::SmallStringView name, ColumnType type = {}, Constraints &&constraints = {}) { m_sqliteColumns.emplace_back(m_tableName, name, type, std::move(constraints)); @@ -81,7 +84,7 @@ public: } Column &addForeignKeyColumn(Utils::SmallStringView name, - const Table &referencedTable, + const BasicTable &referencedTable, ForeignKeyAction foreignKeyupdateAction = {}, ForeignKeyAction foreignKeyDeleteAction = {}, Enforment foreignKeyEnforcement = {}, @@ -123,18 +126,19 @@ public: return m_sqliteColumns.back(); } - void addPrimaryKeyContraint(const SqliteColumnConstReferences &columns) + void addPrimaryKeyContraint(const BasicColumnConstReferences &columns) { Utils::SmallStringVector columnNames; columnNames.reserve(columns.size()); for (const auto &column : columns) - columnNames.emplace_back(column.get().name); + columnNames.emplace_back(column.name); m_tableConstraints.emplace_back(TablePrimaryKey{std::move(columnNames)}); } - Index &addIndex(const SqliteColumnConstReferences &columns, Utils::SmallStringView condition = {}) + Index &addIndex(const BasicColumnConstReferences &columns, + Utils::SmallStringView condition = {}) { return m_sqliteIndices.emplace_back(m_tableName, sqliteColumnNames(columns), @@ -142,7 +146,7 @@ public: condition); } - Index &addUniqueIndex(const SqliteColumnConstReferences &columns, + Index &addUniqueIndex(const BasicColumnConstReferences &columns, Utils::SmallStringView condition = {}) { return m_sqliteIndices.emplace_back(m_tableName, @@ -151,10 +155,7 @@ public: condition); } - const SqliteColumns &columns() const - { - return m_sqliteColumns; - } + const Columns &columns() const { return m_sqliteColumns; } bool isReady() const { @@ -164,7 +165,7 @@ public: template void initialize(Database &database) { - CreateTableSqlStatementBuilder builder; + CreateTableSqlStatementBuilder builder; builder.setTableName(m_tableName.clone()); builder.setUseWithoutRowId(m_withoutRowId); @@ -186,7 +187,7 @@ public: database.execute(index.sqlStatement()); } - friend bool operator==(const Table &first, const Table &second) + friend bool operator==(const BasicTable &first, const BasicTable &second) { return first.m_tableName == second.m_tableName && first.m_withoutRowId == second.m_withoutRowId @@ -207,7 +208,7 @@ public: } private: - Utils::SmallStringVector sqliteColumnNames(const SqliteColumnConstReferences &columns) + Utils::SmallStringVector sqliteColumnNames(const ColumnConstReferences &columns) { Utils::SmallStringVector columnNames; @@ -219,7 +220,7 @@ private: private: Utils::SmallString m_tableName; - SqliteColumns m_sqliteColumns; + Columns m_sqliteColumns; SqliteIndices m_sqliteIndices; TableConstraints m_tableConstraints; bool m_withoutRowId = false; @@ -228,4 +229,7 @@ private: bool m_isReady = false; }; +using Table = BasicTable; +using StrictTable = BasicTable; + } // namespace Sqlite diff --git a/src/libs/sqlite/sqlstatementbuilder.cpp b/src/libs/sqlite/sqlstatementbuilder.cpp index 7a6cc1a81a..b72d1d0328 100644 --- a/src/libs/sqlite/sqlstatementbuilder.cpp +++ b/src/libs/sqlite/sqlstatementbuilder.cpp @@ -176,26 +176,6 @@ bool SqlStatementBuilder::isBuild() const return m_sqlStatement.hasContent(); } -Utils::SmallString SqlStatementBuilder::columnTypeToString(ColumnType columnType) -{ - switch (columnType) { - case ColumnType::Numeric: - return " NUMERIC"; - case ColumnType::Integer: - return " INTEGER"; - case ColumnType::Real: - return " REAL"; - case ColumnType::Text: - return " TEXT"; - case ColumnType::Blob: - return " BLOB"; - case ColumnType::None: - return {}; - } - - Q_UNREACHABLE(); -} - void SqlStatementBuilder::generateSqlStatement() const { m_sqlStatement = m_sqlTemplate; diff --git a/src/libs/sqlite/sqlstatementbuilder.h b/src/libs/sqlite/sqlstatementbuilder.h index 670455dc41..d93f8893e3 100644 --- a/src/libs/sqlite/sqlstatementbuilder.h +++ b/src/libs/sqlite/sqlstatementbuilder.h @@ -57,8 +57,6 @@ public: bool isBuild() const; - static Utils::SmallString columnTypeToString(ColumnType columnType); - protected: static Utils::SmallString insertTemplateParameters(const Utils::SmallStringVector &columns); static Utils::SmallString updateTemplateParameters(const Utils::SmallStringVector &columns); diff --git a/tests/unit/unittest/createtablesqlstatementbuilder-test.cpp b/tests/unit/unittest/createtablesqlstatementbuilder-test.cpp index 0e92b06897..130a2e3042 100644 --- a/tests/unit/unittest/createtablesqlstatementbuilder-test.cpp +++ b/tests/unit/unittest/createtablesqlstatementbuilder-test.cpp @@ -39,13 +39,15 @@ using Sqlite::ForeignKeyAction; using Sqlite::JournalMode; using Sqlite::OpenMode; using Sqlite::PrimaryKey; -using Sqlite::SqliteColumns; using Sqlite::SqlStatementBuilderException; +using Sqlite::StrictColumnType; using Sqlite::Unique; class CreateTableSqlStatementBuilder : public ::testing::Test { protected: + using Columns = ::Sqlite::Columns; + using Builder = Sqlite::CreateTableSqlStatementBuilder; void bindValues() { builder.clear(); @@ -55,9 +57,9 @@ protected: builder.addColumn("number", ColumnType::Numeric); } - static SqliteColumns createColumns() + static Columns createColumns() { - SqliteColumns columns; + Columns columns; columns.emplace_back("", "id", ColumnType::Integer, Sqlite::Constraints{PrimaryKey{}}); columns.emplace_back("", "name", ColumnType::Text); columns.emplace_back("", "number", ColumnType::Numeric); @@ -66,7 +68,7 @@ protected: } protected: - Sqlite::CreateTableSqlStatementBuilder builder; + Builder builder; }; TEST_F(CreateTableSqlStatementBuilder, IsNotValidAfterCreation) @@ -125,8 +127,7 @@ TEST_F(CreateTableSqlStatementBuilder, ChangeTable) builder.setTableName("test2"); ASSERT_THAT(builder.sqlStatement(), - "CREATE TABLE test2(id INTEGER PRIMARY KEY, name TEXT, number NUMERIC)" - ); + "CREATE TABLE test2(id INTEGER PRIMARY KEY, name TEXT, number NUMERIC)"); } TEST_F(CreateTableSqlStatementBuilder, IsInvalidAfterClearColumsOnly) @@ -163,7 +164,6 @@ TEST_F(CreateTableSqlStatementBuilder, SetWitoutRowId) TEST_F(CreateTableSqlStatementBuilder, SetColumnDefinitions) { - builder.clear(); builder.setTableName("test"); builder.setColumns(createColumns()); @@ -174,7 +174,6 @@ TEST_F(CreateTableSqlStatementBuilder, SetColumnDefinitions) TEST_F(CreateTableSqlStatementBuilder, UniqueContraint) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {Unique{}}); @@ -185,7 +184,6 @@ TEST_F(CreateTableSqlStatementBuilder, UniqueContraint) TEST_F(CreateTableSqlStatementBuilder, IfNotExitsModifier) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {}); @@ -197,7 +195,6 @@ TEST_F(CreateTableSqlStatementBuilder, IfNotExitsModifier) TEST_F(CreateTableSqlStatementBuilder, TemporaryTable) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {}); @@ -209,7 +206,6 @@ TEST_F(CreateTableSqlStatementBuilder, TemporaryTable) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyWithoutColumn) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {ForeignKey{"otherTable", ""}}); @@ -219,7 +215,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyWithoutColumn) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyWithColumn) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {ForeignKey{"otherTable", "otherColumn"}}); @@ -230,7 +225,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyWithColumn) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateNoAction) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {ForeignKey{"otherTable", "otherColumn"}}); @@ -241,7 +235,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateNoAction) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateRestrict) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -255,7 +248,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateRestrict) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateSetNull) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -269,7 +261,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateSetNull) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateSetDefault) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -283,7 +274,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateSetDefault) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateCascade) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -297,7 +287,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyUpdateCascade) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteNoAction) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {ForeignKey{"otherTable", "otherColumn"}}); @@ -308,7 +297,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteNoAction) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteRestrict) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -322,7 +310,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteRestrict) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteSetNull) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -336,7 +323,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteSetNull) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteSetDefault) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -350,7 +336,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteSetDefault) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteCascade) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -364,7 +349,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteCascade) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteAndUpdateAction) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -381,7 +365,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeleteAndUpdateAction) TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeferred) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -399,7 +382,6 @@ TEST_F(CreateTableSqlStatementBuilder, ForeignKeyDeferred) TEST_F(CreateTableSqlStatementBuilder, NotNullConstraint) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {Sqlite::NotNull{}}); @@ -409,7 +391,6 @@ TEST_F(CreateTableSqlStatementBuilder, NotNullConstraint) TEST_F(CreateTableSqlStatementBuilder, NotNullAndUniqueConstraint) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {Sqlite::Unique{}, Sqlite::NotNull{}}); @@ -419,7 +400,6 @@ TEST_F(CreateTableSqlStatementBuilder, NotNullAndUniqueConstraint) TEST_F(CreateTableSqlStatementBuilder, Check) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Text, {Sqlite::Check{"id != ''"}}); @@ -429,7 +409,6 @@ TEST_F(CreateTableSqlStatementBuilder, Check) TEST_F(CreateTableSqlStatementBuilder, DefaultValueInt) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {Sqlite::DefaultValue{1LL}}); @@ -439,7 +418,6 @@ TEST_F(CreateTableSqlStatementBuilder, DefaultValueInt) TEST_F(CreateTableSqlStatementBuilder, DefaultValueFloat) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Real, {Sqlite::DefaultValue{1.1}}); @@ -449,7 +427,6 @@ TEST_F(CreateTableSqlStatementBuilder, DefaultValueFloat) TEST_F(CreateTableSqlStatementBuilder, DefaultValueString) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Text, {Sqlite::DefaultValue{"foo"}}); @@ -459,7 +436,6 @@ TEST_F(CreateTableSqlStatementBuilder, DefaultValueString) TEST_F(CreateTableSqlStatementBuilder, DefaultExpression) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -472,7 +448,6 @@ TEST_F(CreateTableSqlStatementBuilder, DefaultExpression) TEST_F(CreateTableSqlStatementBuilder, Collation) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Text, {Sqlite::Collate{"unicode"}}); @@ -482,7 +457,6 @@ TEST_F(CreateTableSqlStatementBuilder, Collation) TEST_F(CreateTableSqlStatementBuilder, GeneratedAlwaysStored) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -497,7 +471,6 @@ TEST_F(CreateTableSqlStatementBuilder, GeneratedAlwaysStored) TEST_F(CreateTableSqlStatementBuilder, GeneratedAlwaysVirtual) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", @@ -512,7 +485,6 @@ TEST_F(CreateTableSqlStatementBuilder, GeneratedAlwaysVirtual) TEST_F(CreateTableSqlStatementBuilder, PrimaryKeyAutoincrement) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer, {Sqlite::PrimaryKey{Sqlite::AutoIncrement::Yes}}); @@ -522,7 +494,6 @@ TEST_F(CreateTableSqlStatementBuilder, PrimaryKeyAutoincrement) TEST_F(CreateTableSqlStatementBuilder, BlobType) { - builder.clear(); builder.setTableName("test"); builder.addColumn("data", ColumnType::Blob); @@ -532,7 +503,6 @@ TEST_F(CreateTableSqlStatementBuilder, BlobType) TEST_F(CreateTableSqlStatementBuilder, TablePrimaryKeyConstaint) { - builder.clear(); builder.setTableName("test"); builder.addColumn("id", ColumnType::Integer); builder.addColumn("text", ColumnType::Text); @@ -543,4 +513,595 @@ TEST_F(CreateTableSqlStatementBuilder, TablePrimaryKeyConstaint) ASSERT_THAT(statement, "CREATE TABLE test(id INTEGER, text TEXT, PRIMARY KEY(id, text))"); } +TEST_F(CreateTableSqlStatementBuilder, NoneColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", ColumnType::None); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id)"); +} + +TEST_F(CreateTableSqlStatementBuilder, NumericColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", ColumnType::Numeric); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id NUMERIC)"); +} + +TEST_F(CreateTableSqlStatementBuilder, IntegerColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", ColumnType::Integer); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id INTEGER)"); +} + +TEST_F(CreateTableSqlStatementBuilder, RealColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", ColumnType::Real); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id REAL)"); +} + +TEST_F(CreateTableSqlStatementBuilder, TextColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", ColumnType::Text); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id TEXT)"); +} + +TEST_F(CreateTableSqlStatementBuilder, BlobColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", ColumnType::Blob); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id BLOB)"); +} + +class CreateStrictTableSqlStatementBuilder : public ::testing::Test +{ +protected: + using Columns = ::Sqlite::StrictColumns; + + void bindValues() + { + builder.clear(); + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Integer, {PrimaryKey{}}); + builder.addColumn("name", StrictColumnType::Text); + builder.addColumn("number", StrictColumnType::Any); + } + + static Columns createColumns() + { + Columns columns; + columns.emplace_back("", "id", StrictColumnType::Integer, Sqlite::Constraints{PrimaryKey{}}); + columns.emplace_back("", "name", StrictColumnType::Text); + columns.emplace_back("", "number", StrictColumnType::Any); + + return columns; + } + +protected: + Sqlite::CreateTableSqlStatementBuilder builder; +}; + +TEST_F(CreateStrictTableSqlStatementBuilder, IsNotValidAfterCreation) +{ + ASSERT_FALSE(builder.isValid()); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, IsValidAfterBinding) +{ + bindValues(); + + ASSERT_TRUE(builder.isValid()); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, InvalidAfterClear) +{ + bindValues(); + + builder.clear(); + + ASSERT_TRUE(!builder.isValid()); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, NoSqlStatementAfterClear) +{ + bindValues(); + builder.sqlStatement(); + + builder.clear(); + + ASSERT_THROW(builder.sqlStatement(), SqlStatementBuilderException); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, SqlStatement) +{ + bindValues(); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT, number ANY) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, AddColumnToExistingColumns) +{ + bindValues(); + + builder.addColumn("number2", StrictColumnType::Real); + + ASSERT_THAT( + builder.sqlStatement(), + "CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT, number ANY, number2 REAL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ChangeTable) +{ + bindValues(); + + builder.setTableName("test2"); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test2(id INTEGER PRIMARY KEY, name TEXT, number ANY) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, IsInvalidAfterClearColumsOnly) +{ + bindValues(); + builder.sqlStatement(); + + builder.clearColumns(); + + ASSERT_THROW(builder.sqlStatement(), SqlStatementBuilderException); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ClearColumnsAndAddColumnNewColumns) +{ + bindValues(); + builder.clearColumns(); + + builder.addColumn("name3", StrictColumnType::Text); + builder.addColumn("number3", StrictColumnType::Real); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(name3 TEXT, number3 REAL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, SetWitoutRowId) +{ + bindValues(); + + builder.setUseWithoutRowId(true); + + ASSERT_THAT( + builder.sqlStatement(), + "CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT, number ANY) WITHOUT ROWID STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, SetColumnDefinitions) +{ + builder.setTableName("test"); + + builder.setColumns(createColumns()); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT, number ANY) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, UniqueContraint) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {Unique{}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id INTEGER UNIQUE) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, IfNotExitsModifier) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Integer, {}); + + builder.setUseIfNotExists(true); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE IF NOT EXISTS test(id INTEGER) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, TemporaryTable) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Integer, {}); + + builder.setUseTemporaryTable(true); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TEMPORARY TABLE test(id INTEGER) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyWithoutColumn) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {ForeignKey{"otherTable", ""}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id INTEGER REFERENCES otherTable) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyWithColumn) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {ForeignKey{"otherTable", "otherColumn"}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn)) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyUpdateNoAction) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {ForeignKey{"otherTable", "otherColumn"}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn)) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyUpdateRestrict) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", ForeignKeyAction::Restrict}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON UPDATE " + "RESTRICT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyUpdateSetNull) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", ForeignKeyAction::SetNull}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON UPDATE SET " + "NULL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyUpdateSetDefault) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", ForeignKeyAction::SetDefault}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON UPDATE SET " + "DEFAULT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyUpdateCascade) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", ForeignKeyAction::Cascade}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON UPDATE " + "CASCADE) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeleteNoAction) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {ForeignKey{"otherTable", "otherColumn"}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn)) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeleteRestrict) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", {}, ForeignKeyAction::Restrict}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON DELETE " + "RESTRICT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeleteSetNull) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", {}, ForeignKeyAction::SetNull}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON DELETE SET " + "NULL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeleteSetDefault) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", {}, ForeignKeyAction::SetDefault}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON DELETE SET " + "DEFAULT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeleteCascade) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", "otherColumn", {}, ForeignKeyAction::Cascade}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON DELETE " + "CASCADE) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeleteAndUpdateAction) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", + "otherColumn", + ForeignKeyAction::SetDefault, + ForeignKeyAction::Cascade}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON UPDATE SET " + "DEFAULT ON DELETE CASCADE) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, ForeignKeyDeferred) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {ForeignKey{"otherTable", + "otherColumn", + ForeignKeyAction::SetDefault, + ForeignKeyAction::Cascade, + Enforment::Deferred}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER REFERENCES otherTable(otherColumn) ON UPDATE SET " + "DEFAULT ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, NotNullConstraint) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {Sqlite::NotNull{}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id INTEGER NOT NULL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, NotNullAndUniqueConstraint) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {Sqlite::Unique{}, Sqlite::NotNull{}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id INTEGER UNIQUE NOT NULL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, Check) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Text, {Sqlite::Check{"id != ''"}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id TEXT CHECK (id != '')) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, DefaultValueInt) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Integer, {Sqlite::DefaultValue{1LL}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id INTEGER DEFAULT 1) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, DefaultValueFloat) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Real, {Sqlite::DefaultValue{1.1}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id REAL DEFAULT 1.100000) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, DefaultValueString) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Text, {Sqlite::DefaultValue{"foo"}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id TEXT DEFAULT 'foo') STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, DefaultExpression) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {Sqlite::DefaultExpression{"SELECT name FROM foo WHERE id=?"}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER DEFAULT (SELECT name FROM foo WHERE id=?)) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, Collation) +{ + builder.setTableName("test"); + + builder.addColumn("id", StrictColumnType::Text, {Sqlite::Collate{"unicode"}}); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(id TEXT COLLATE unicode) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, GeneratedAlwaysStored) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Text, + {Sqlite::GeneratedAlways{"SELECT name FROM foo WHERE id=?", + Sqlite::GeneratedAlwaysStorage::Stored}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id TEXT GENERATED ALWAYS AS (SELECT name FROM foo WHERE id=?) " + "STORED) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, GeneratedAlwaysVirtual) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Text, + {Sqlite::GeneratedAlways{"SELECT name FROM foo WHERE id=?", + Sqlite::GeneratedAlwaysStorage::Virtual}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id TEXT GENERATED ALWAYS AS (SELECT name FROM foo WHERE id=?) " + "VIRTUAL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, PrimaryKeyAutoincrement) +{ + builder.setTableName("test"); + + builder.addColumn("id", + StrictColumnType::Integer, + {Sqlite::PrimaryKey{Sqlite::AutoIncrement::Yes}}); + + ASSERT_THAT(builder.sqlStatement(), + "CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, BlobType) +{ + builder.setTableName("test"); + + builder.addColumn("data", StrictColumnType::Blob); + + ASSERT_THAT(builder.sqlStatement(), "CREATE TABLE test(data BLOB) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, TablePrimaryKeyConstaint) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Integer); + builder.addColumn("text", StrictColumnType::Text); + + builder.addConstraint(Sqlite::TablePrimaryKey{{"id, text"}}); + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id INTEGER, text TEXT, PRIMARY KEY(id, text)) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, AnyColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Any); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id ANY) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, IntColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Int); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id INT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, IntegerColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Integer); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id INTEGER) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, RealColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Real); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id REAL) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, TextColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Text); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id TEXT) STRICT"); +} + +TEST_F(CreateStrictTableSqlStatementBuilder, BlobColumnTypeStringConversion) +{ + builder.setTableName("test"); + builder.addColumn("id", StrictColumnType::Blob); + + auto statement = builder.sqlStatement(); + + ASSERT_THAT(statement, "CREATE TABLE test(id BLOB) STRICT"); +} + } // namespace diff --git a/tests/unit/unittest/sqlitecolumn-test.cpp b/tests/unit/unittest/sqlitecolumn-test.cpp index 9753457c94..865726d54f 100644 --- a/tests/unit/unittest/sqlitecolumn-test.cpp +++ b/tests/unit/unittest/sqlitecolumn-test.cpp @@ -31,18 +31,19 @@ namespace { using Sqlite::ColumnType; using Sqlite::ConstraintType; -using Sqlite::JournalMode; -using Sqlite::OpenMode; -using Column = Sqlite::Column; using Sqlite::Enforment; using Sqlite::ForeignKey; using Sqlite::ForeignKeyAction; -using Sqlite::SqliteColumns; +using Sqlite::JournalMode; +using Sqlite::OpenMode; +using Sqlite::StrictColumnType; class SqliteColumn : public ::testing::Test { protected: - Sqlite::Column column; + using Column = Sqlite::Column; + + Column column; }; TEST_F(SqliteColumn, DefaultConstruct) @@ -50,7 +51,7 @@ TEST_F(SqliteColumn, DefaultConstruct) ASSERT_THAT(column, AllOf(Field(&Column::name, IsEmpty()), Field(&Column::tableName, IsEmpty()), - Field(&Column::type, ColumnType::Numeric), + Field(&Column::type, ColumnType::None), Field(&Column::constraints, IsEmpty()))); } @@ -66,20 +67,20 @@ TEST_F(SqliteColumn, Clear) ASSERT_THAT(column, AllOf(Field(&Column::name, IsEmpty()), Field(&Column::tableName, IsEmpty()), - Field(&Column::type, ColumnType::Numeric), + Field(&Column::type, ColumnType::None), Field(&Column::constraints, IsEmpty()))); } TEST_F(SqliteColumn, Constructor) { - column = Sqlite::Column{"table", - "column", - ColumnType::Text, - {ForeignKey{"referencedTable", - "referencedColumn", - ForeignKeyAction::SetNull, - ForeignKeyAction::Cascade, - Enforment::Deferred}}}; + column = Column{"table", + "column", + ColumnType::Text, + {ForeignKey{"referencedTable", + "referencedColumn", + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred}}}; ASSERT_THAT(column, AllOf(Field(&Column::name, Eq("column")), @@ -96,14 +97,14 @@ TEST_F(SqliteColumn, Constructor) TEST_F(SqliteColumn, FlatConstructor) { - column = Sqlite::Column{"table", - "column", - ColumnType::Text, - {ForeignKey{"referencedTable", - "referencedColumn", - ForeignKeyAction::SetNull, - ForeignKeyAction::Cascade, - Enforment::Deferred}}}; + column = Column{"table", + "column", + ColumnType::Text, + {ForeignKey{"referencedTable", + "referencedColumn", + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred}}}; ASSERT_THAT(column, AllOf(Field(&Column::name, Eq("column")), @@ -118,4 +119,85 @@ TEST_F(SqliteColumn, FlatConstructor) Field(&ForeignKey::enforcement, Enforment::Deferred))))))); } +class SqliteStrictColumn : public ::testing::Test +{ +protected: + using Column = Sqlite::StrictColumn; + + Column column; +}; + +TEST_F(SqliteStrictColumn, DefaultConstruct) +{ + ASSERT_THAT(column, + AllOf(Field(&Column::name, IsEmpty()), + Field(&Column::tableName, IsEmpty()), + Field(&Column::type, StrictColumnType::Any), + Field(&Column::constraints, IsEmpty()))); +} + +TEST_F(SqliteStrictColumn, Clear) +{ + column.name = "foo"; + column.name = "foo"; + column.type = StrictColumnType::Text; + column.constraints = {Sqlite::PrimaryKey{}}; + + column.clear(); + + ASSERT_THAT(column, + AllOf(Field(&Column::name, IsEmpty()), + Field(&Column::tableName, IsEmpty()), + Field(&Column::type, StrictColumnType::Any), + Field(&Column::constraints, IsEmpty()))); +} + +TEST_F(SqliteStrictColumn, Constructor) +{ + column = Column{"table", + "column", + StrictColumnType::Text, + {ForeignKey{"referencedTable", + "referencedColumn", + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred}}}; + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("column")), + Field(&Column::tableName, Eq("table")), + Field(&Column::type, StrictColumnType::Text), + Field(&Column::constraints, + ElementsAre(VariantWith( + AllOf(Field(&ForeignKey::table, Eq("referencedTable")), + Field(&ForeignKey::column, Eq("referencedColumn")), + Field(&ForeignKey::updateAction, ForeignKeyAction::SetNull), + Field(&ForeignKey::deleteAction, ForeignKeyAction::Cascade), + Field(&ForeignKey::enforcement, Enforment::Deferred))))))); +} + +TEST_F(SqliteStrictColumn, FlatConstructor) +{ + column = Column{"table", + "column", + StrictColumnType::Text, + {ForeignKey{"referencedTable", + "referencedColumn", + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred}}}; + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("column")), + Field(&Column::tableName, Eq("table")), + Field(&Column::type, StrictColumnType::Text), + Field(&Column::constraints, + ElementsAre(VariantWith( + AllOf(Field(&ForeignKey::table, Eq("referencedTable")), + Field(&ForeignKey::column, Eq("referencedColumn")), + Field(&ForeignKey::updateAction, ForeignKeyAction::SetNull), + Field(&ForeignKey::deleteAction, ForeignKeyAction::Cascade), + Field(&ForeignKey::enforcement, Enforment::Deferred))))))); +} + } // namespace diff --git a/tests/unit/unittest/sqlitetable-test.cpp b/tests/unit/unittest/sqlitetable-test.cpp index 20aa0680be..24be87cca4 100644 --- a/tests/unit/unittest/sqlitetable-test.cpp +++ b/tests/unit/unittest/sqlitetable-test.cpp @@ -32,7 +32,6 @@ namespace { -using Sqlite::Column; using Sqlite::ColumnType; using Sqlite::ConstraintType; using Sqlite::Database; @@ -41,10 +40,13 @@ using Sqlite::ForeignKey; using Sqlite::ForeignKeyAction; using Sqlite::JournalMode; using Sqlite::OpenMode; +using Sqlite::StrictColumnType; class SqliteTable : public ::testing::Test { protected: + using Column = Sqlite::Column; + NiceMock databaseMock; Sqlite::Table table; Utils::SmallString tableName = "testTable"; @@ -328,4 +330,302 @@ TEST_F(SqliteTable, AddPrimaryTableContraint) table.initialize(databaseMock); } + +class StrictSqliteTable : public ::testing::Test +{ +protected: + using Column = Sqlite::StrictColumn; + + NiceMock databaseMock; + Sqlite::StrictTable table; + Utils::SmallString tableName = "testTable"; +}; + +TEST_F(StrictSqliteTable, ColumnIsAddedToTable) +{ + table.setUseWithoutRowId(true); + + ASSERT_TRUE(table.useWithoutRowId()); +} + +TEST_F(StrictSqliteTable, SetTableName) +{ + table.setName(tableName.clone()); + + ASSERT_THAT(table.name(), tableName); +} + +TEST_F(StrictSqliteTable, SetUseWithoutRowid) +{ + table.setUseWithoutRowId(true); + + ASSERT_TRUE(table.useWithoutRowId()); +} + +TEST_F(StrictSqliteTable, AddIndex) +{ + table.setName(tableName.clone()); + auto &column = table.addColumn("name"); + auto &column2 = table.addColumn("value"); + + auto index = table.addIndex({column, column2}); + + ASSERT_THAT(Utils::SmallStringView(index.sqlStatement()), + Eq("CREATE INDEX IF NOT EXISTS index_testTable_name_value ON testTable(name, " + "value)")); +} + +TEST_F(StrictSqliteTable, InitializeTable) +{ + table.setName(tableName.clone()); + table.setUseIfNotExists(true); + table.setUseTemporaryTable(true); + table.setUseWithoutRowId(true); + table.addColumn("name"); + table.addColumn("value"); + + EXPECT_CALL(databaseMock, + execute(Eq("CREATE TEMPORARY TABLE IF NOT EXISTS testTable(name ANY, value ANY) " + "WITHOUT ROWID STRICT"))); + + table.initialize(databaseMock); +} + +TEST_F(StrictSqliteTable, InitializeTableWithIndex) +{ + InSequence sequence; + table.setName(tableName.clone()); + auto &column = table.addColumn("name"); + auto &column2 = table.addColumn("value"); + table.addIndex({column}); + table.addIndex({column2}, "value IS NOT NULL"); + + EXPECT_CALL(databaseMock, execute(Eq("CREATE TABLE testTable(name ANY, value ANY) STRICT"))); + EXPECT_CALL(databaseMock, + execute(Eq("CREATE INDEX IF NOT EXISTS index_testTable_name ON testTable(name)"))); + EXPECT_CALL(databaseMock, + execute(Eq("CREATE INDEX IF NOT EXISTS index_testTable_value ON testTable(value) " + "WHERE value IS NOT NULL"))); + + table.initialize(databaseMock); +} + +TEST_F(StrictSqliteTable, InitializeTableWithUniqueIndex) +{ + InSequence sequence; + table.setName(tableName.clone()); + auto &column = table.addColumn("name"); + auto &column2 = table.addColumn("value"); + table.addUniqueIndex({column}); + table.addUniqueIndex({column2}, "value IS NOT NULL"); + + EXPECT_CALL(databaseMock, execute(Eq("CREATE TABLE testTable(name ANY, value ANY) STRICT"))); + EXPECT_CALL(databaseMock, + execute(Eq( + "CREATE UNIQUE INDEX IF NOT EXISTS index_testTable_name ON testTable(name)"))); + EXPECT_CALL(databaseMock, + execute(Eq( + "CREATE UNIQUE INDEX IF NOT EXISTS index_testTable_value ON testTable(value) " + "WHERE value IS NOT NULL"))); + + table.initialize(databaseMock); +} + +TEST_F(StrictSqliteTable, AddForeignKeyColumnWithTableCalls) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + table.setName(tableName); + table.addForeignKeyColumn("name", + foreignTable, + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred); + + EXPECT_CALL(databaseMock, + execute(Eq("CREATE TABLE testTable(name INTEGER REFERENCES foreignTable ON UPDATE " + "SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) STRICT"))); + + table.initialize(databaseMock); +} + +TEST_F(StrictSqliteTable, AddForeignKeyColumnWithColumnCalls) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + auto &foreignColumn = foreignTable.addColumn("foreignColumn", + StrictColumnType::Text, + {Sqlite::Unique{}}); + table.setName(tableName); + table.addForeignKeyColumn("name", + foreignColumn, + ForeignKeyAction::SetDefault, + ForeignKeyAction::Restrict, + Enforment::Deferred); + + EXPECT_CALL( + databaseMock, + execute( + Eq("CREATE TABLE testTable(name TEXT REFERENCES foreignTable(foreignColumn) ON UPDATE " + "SET DEFAULT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) STRICT"))); + + table.initialize(databaseMock); +} + +TEST_F(StrictSqliteTable, AddColumn) +{ + table.setName(tableName); + + auto &column = table.addColumn("name", StrictColumnType::Text, {Sqlite::Unique{}}); + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("name")), + Field(&Column::tableName, Eq(tableName)), + Field(&Column::type, StrictColumnType::Text), + Field(&Column::constraints, + ElementsAre(VariantWith(Eq(Sqlite::Unique{})))))); +} + +TEST_F(StrictSqliteTable, AddForeignKeyColumnWithTable) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + + table.setName(tableName); + + auto &column = table.addForeignKeyColumn("name", + foreignTable, + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred); + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("name")), + Field(&Column::tableName, Eq(tableName)), + Field(&Column::type, StrictColumnType::Integer), + Field(&Column::constraints, + ElementsAre(VariantWith( + AllOf(Field(&ForeignKey::table, Eq("foreignTable")), + Field(&ForeignKey::column, IsEmpty()), + Field(&ForeignKey::updateAction, ForeignKeyAction::SetNull), + Field(&ForeignKey::deleteAction, ForeignKeyAction::Cascade), + Field(&ForeignKey::enforcement, Enforment::Deferred))))))); +} + +TEST_F(StrictSqliteTable, AddForeignKeyColumnWithColumn) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + auto &foreignColumn = foreignTable.addColumn("foreignColumn", + StrictColumnType::Text, + {Sqlite::Unique{}}); + table.setName(tableName); + + auto &column = table.addForeignKeyColumn("name", + foreignColumn, + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred); + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("name")), + Field(&Column::tableName, Eq(tableName)), + Field(&Column::type, StrictColumnType::Text), + Field(&Column::constraints, + ElementsAre(VariantWith( + AllOf(Field(&ForeignKey::table, Eq("foreignTable")), + Field(&ForeignKey::column, Eq("foreignColumn")), + Field(&ForeignKey::updateAction, ForeignKeyAction::SetNull), + Field(&ForeignKey::deleteAction, ForeignKeyAction::Cascade), + Field(&ForeignKey::enforcement, Enforment::Deferred))))))); +} + +TEST_F(StrictSqliteTable, AddForeignKeyWhichIsNotUniqueThrowsAnExceptions) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + auto &foreignColumn = foreignTable.addColumn("foreignColumn", StrictColumnType::Text); + table.setName(tableName); + + ASSERT_THROW(table.addForeignKeyColumn("name", + foreignColumn, + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred), + Sqlite::ForeignKeyColumnIsNotUnique); +} + +TEST_F(StrictSqliteTable, AddForeignKeyColumnWithTableAndNotNull) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + + table.setName(tableName); + + auto &column = table.addForeignKeyColumn("name", + foreignTable, + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred, + {Sqlite::NotNull{}}); + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("name")), + Field(&Column::tableName, Eq(tableName)), + Field(&Column::type, StrictColumnType::Integer), + Field(&Column::constraints, + UnorderedElementsAre( + VariantWith( + AllOf(Field(&ForeignKey::table, Eq("foreignTable")), + Field(&ForeignKey::column, IsEmpty()), + Field(&ForeignKey::updateAction, ForeignKeyAction::SetNull), + Field(&ForeignKey::deleteAction, ForeignKeyAction::Cascade), + Field(&ForeignKey::enforcement, Enforment::Deferred))), + VariantWith(Eq(Sqlite::NotNull{})))))); +} + +TEST_F(StrictSqliteTable, AddForeignKeyColumnWithColumnAndNotNull) +{ + Sqlite::StrictTable foreignTable; + foreignTable.setName("foreignTable"); + auto &foreignColumn = foreignTable.addColumn("foreignColumn", + StrictColumnType::Text, + {Sqlite::Unique{}}); + table.setName(tableName); + + auto &column = table.addForeignKeyColumn("name", + foreignColumn, + ForeignKeyAction::SetNull, + ForeignKeyAction::Cascade, + Enforment::Deferred, + {Sqlite::NotNull{}}); + + ASSERT_THAT(column, + AllOf(Field(&Column::name, Eq("name")), + Field(&Column::tableName, Eq(tableName)), + Field(&Column::type, StrictColumnType::Text), + Field(&Column::constraints, + UnorderedElementsAre( + VariantWith( + AllOf(Field(&ForeignKey::table, Eq("foreignTable")), + Field(&ForeignKey::column, Eq("foreignColumn")), + Field(&ForeignKey::updateAction, ForeignKeyAction::SetNull), + Field(&ForeignKey::deleteAction, ForeignKeyAction::Cascade), + Field(&ForeignKey::enforcement, Enforment::Deferred))), + VariantWith(Eq(Sqlite::NotNull{})))))); +} + +TEST_F(StrictSqliteTable, AddPrimaryTableContraint) +{ + table.setName(tableName.clone()); + const auto &idColumn = table.addColumn("id"); + const auto &nameColumn = table.addColumn("name"); + table.addPrimaryKeyContraint({idColumn, nameColumn}); + + EXPECT_CALL(databaseMock, + execute( + Eq("CREATE TABLE testTable(id ANY, name ANY, PRIMARY KEY(id, name)) STRICT"))); + + table.initialize(databaseMock); +} } // namespace diff --git a/tests/unit/unittest/sqlstatementbuilder-test.cpp b/tests/unit/unittest/sqlstatementbuilder-test.cpp index 1e24faeded..806939fcdb 100644 --- a/tests/unit/unittest/sqlstatementbuilder-test.cpp +++ b/tests/unit/unittest/sqlstatementbuilder-test.cpp @@ -135,16 +135,6 @@ TEST(SqlStatementBuilder, ClearBinding) ASSERT_THROW(sqlStatementBuilder.sqlStatement(), SqlStatementBuilderException); } -TEST(SqlStatementBuilder, ColumnType) -{ - ASSERT_THAT(SqlStatementBuilder::columnTypeToString(ColumnType::Numeric), " NUMERIC"); - ASSERT_THAT(SqlStatementBuilder::columnTypeToString(ColumnType::Integer), " INTEGER"); - ASSERT_THAT(SqlStatementBuilder::columnTypeToString(ColumnType::Real), " REAL"); - ASSERT_THAT(SqlStatementBuilder::columnTypeToString(ColumnType::Text), " TEXT"); - ASSERT_THAT(SqlStatementBuilder::columnTypeToString(ColumnType::Blob), " BLOB"); - ASSERT_TRUE(SqlStatementBuilder::columnTypeToString(ColumnType::None).isEmpty()); -} - TEST(SqlStatementBuilder, SqlStatementFailure) { SqlStatementBuilder sqlStatementBuilder("SELECT $columns FROM $table"); -- cgit v1.2.3