SQLiteStatementBuilder.h (23428B)
1 // Copyright (c) Microsoft Corporation. 2 // Licensed under the MIT License. 3 #pragma once 4 #include <winget/SQLiteWrapper.h> 5 #include <AppInstallerLanguageUtilities.h> 6 7 #include <functional> 8 #include <initializer_list> 9 #include <memory> 10 #include <optional> 11 #include <sstream> 12 #include <string_view> 13 #include <vector> 14 15 using namespace std::string_view_literals; 16 17 namespace AppInstaller::SQLite::Builder 18 { 19 namespace details 20 { 21 // Sentinel types to indicate special cases to the builder. 22 struct unbound_t {}; 23 struct rowcount_t {}; 24 25 // Class for intake from external functions. 26 struct SubBuilder 27 { 28 SubBuilder(std::string&& s) : m_string(std::move(s)) {} 29 30 SubBuilder(const SubBuilder&) = default; 31 SubBuilder& operator=(const SubBuilder&) = default; 32 33 SubBuilder(SubBuilder&&) noexcept = default; 34 SubBuilder& operator=(SubBuilder&&) noexcept = default; 35 36 const std::string& GetString() const { return m_string; } 37 38 protected: 39 std::string m_string; 40 }; 41 42 // Base class for all sub-builders. 43 struct SubBuilderBase 44 { 45 SubBuilderBase() = default; 46 47 SubBuilderBase(const SubBuilderBase&) = default; 48 SubBuilderBase& operator=(const SubBuilderBase&) = default; 49 50 SubBuilderBase(SubBuilderBase&&) noexcept = default; 51 SubBuilderBase& operator=(SubBuilderBase&&) noexcept = default; 52 53 virtual operator SubBuilder() { return { m_stream.str() }; } 54 55 protected: 56 std::ostringstream m_stream; 57 }; 58 } 59 60 // Pass this value to indicate that the caller will bind the value later. 61 __declspec_selectany_ details::unbound_t Unbound; 62 63 // Pass this value to indicate that the number of rows is to be selected. 64 __declspec_selectany_ details::rowcount_t RowCount; 65 66 // A qualified table reference. 67 struct QualifiedTable 68 { 69 std::string_view Schema; 70 std::string_view Table; 71 72 explicit constexpr QualifiedTable(std::string_view table) : Table(table) {} 73 explicit constexpr QualifiedTable(std::string_view schema, std::string_view table) : Schema(schema), Table(table) {} 74 }; 75 76 namespace Schema 77 { 78 // The main database's schema table. 79 // More info can be found at: https://www.sqlite.org/schematab.html 80 constexpr QualifiedTable MainTable{ "main"sv, "sqlite_master"sv }; 81 82 // The sqlite_schema column name for the type of the object. 83 constexpr std::string_view TypeColumn = "type"sv; 84 85 // The sqlite_schema type value for a table. 86 constexpr std::string_view Type_Table = "table"sv; 87 88 // The sqlite_schema type value for an index. 89 constexpr std::string_view Type_Index = "index"sv; 90 91 // The sqlite_schema column name for the name of the object. 92 constexpr std::string_view NameColumn = "name"sv; 93 } 94 95 // A qualified column reference. 96 struct QualifiedColumn 97 { 98 std::string_view Table; 99 std::string_view Column; 100 101 explicit QualifiedColumn(std::string_view column) : Column(column) {} 102 explicit QualifiedColumn(std::string_view table, std::string_view column) : Table(table), Column(column) {} 103 }; 104 105 // SQLite types as an enum. 106 enum class Type 107 { 108 Int, 109 Bool = Int, 110 Int64, 111 RowId = Int64, 112 Text, 113 Blob, 114 Integer, // Type for specifying a primary key column as a row id alias. 115 None, // Does not declare a type 116 }; 117 118 template <Type type, bool allowNull> 119 struct TypeInfo 120 { 121 }; 122 123 template <> 124 struct TypeInfo<Type::Text, false> 125 { 126 using value_t = std::string; 127 }; 128 129 template <> 130 struct TypeInfo<Type::Text, true> 131 { 132 using value_t = std::optional<std::string>; 133 }; 134 135 template <> 136 struct TypeInfo<Type::Blob, false> 137 { 138 using value_t = SQLite::blob_t; 139 }; 140 141 template <> 142 struct TypeInfo<Type::Blob, true> 143 { 144 using value_t = std::optional<SQLite::blob_t>; 145 }; 146 147 // Aggregate functions. 148 enum class Aggregate 149 { 150 Min, 151 Max, 152 }; 153 154 // Helper to mark create an integer primary key for rowid, making it stable across vacuum. 155 struct IntegerPrimaryKey : public details::SubBuilderBase 156 { 157 IntegerPrimaryKey(); 158 159 IntegerPrimaryKey(const IntegerPrimaryKey&) = default; 160 IntegerPrimaryKey& operator=(const IntegerPrimaryKey&) = default; 161 162 IntegerPrimaryKey(IntegerPrimaryKey&&) noexcept = default; 163 IntegerPrimaryKey& operator=(IntegerPrimaryKey&&) noexcept = default; 164 165 // Set the column to autoincrement. SQLite recommends against using this value unless 166 // you need to ensure that rowids are not ever reused. 167 IntegerPrimaryKey& AutoIncrement(bool isTrue = true); 168 }; 169 170 // Helper used when creating a table. 171 struct ColumnBuilder : public details::SubBuilderBase 172 { 173 // Specify the column name and type when creating the builder. 174 ColumnBuilder(std::string_view column, Type type); 175 176 ColumnBuilder(const ColumnBuilder&) = default; 177 ColumnBuilder& operator=(const ColumnBuilder&) = default; 178 179 ColumnBuilder(ColumnBuilder&&) noexcept = default; 180 ColumnBuilder& operator=(ColumnBuilder&&) noexcept = default; 181 182 // Indicate that the column is not able to be null. 183 // Allow for data driven construction with input value. 184 ColumnBuilder& NotNull(bool isTrue = true); 185 186 // Indicate that the column is case-insensitive. 187 // Allow for data driven construction with input value. 188 ColumnBuilder& CollateNoCase(bool isTrue = true); 189 190 // Indicate the default value for the column. 191 // Note that a default value is not considered constant if it is bound, 192 // so this function directly places the incoming value into the SQL statement. 193 ColumnBuilder& Default(int64_t value); 194 195 // Indicate that the column is unique. 196 // Allow for data driven construction with input value. 197 ColumnBuilder& Unique(bool isTrue = true); 198 199 // Indicate that the column is the primary key. 200 // Allow for data driven construction with input value. 201 ColumnBuilder& PrimaryKey(bool isTrue = true); 202 }; 203 204 // Helper used to specify a primary key with multiple columns. 205 struct PrimaryKeyBuilder : public details::SubBuilderBase 206 { 207 PrimaryKeyBuilder(); 208 PrimaryKeyBuilder(std::initializer_list<std::string_view> columns); 209 210 PrimaryKeyBuilder(const PrimaryKeyBuilder&) = default; 211 PrimaryKeyBuilder& operator=(const PrimaryKeyBuilder&) = default; 212 213 PrimaryKeyBuilder(PrimaryKeyBuilder&&) noexcept = default; 214 PrimaryKeyBuilder& operator=(PrimaryKeyBuilder&&) noexcept = default; 215 216 virtual operator details::SubBuilder() override; 217 218 // Add a column to the primary key. 219 PrimaryKeyBuilder& Column(std::string_view column); 220 221 private: 222 bool m_isFirst = true; 223 bool m_needsClosing = true; 224 }; 225 226 // A class that aids in building SQL statements in a more expressive manner than simple strings. 227 struct StatementBuilder 228 { 229 StatementBuilder() = default; 230 231 StatementBuilder(const StatementBuilder&) = default; 232 StatementBuilder& operator=(const StatementBuilder&) = default; 233 234 StatementBuilder(StatementBuilder&&) = default; 235 StatementBuilder& operator=(StatementBuilder&&) = default; 236 237 // Begin a select statement for the given columns. 238 StatementBuilder& Select(); 239 StatementBuilder& Select(std::string_view column); 240 StatementBuilder& Select(std::initializer_list<std::string_view> columns); 241 StatementBuilder& Select(const QualifiedColumn& column); 242 StatementBuilder& Select(std::initializer_list<QualifiedColumn> columns); 243 StatementBuilder& Select(details::rowcount_t); 244 245 // Indicate the table that the statement will be operating on. 246 // The initializer_list form enables the table name to be constructed from multiple parts. 247 StatementBuilder& From(); 248 StatementBuilder& From(std::string_view table); 249 StatementBuilder& From(QualifiedTable table); 250 StatementBuilder& From(std::initializer_list<std::string_view> table); 251 252 // Begin a filter clause on the given column. 253 StatementBuilder& Where(std::string_view column); 254 StatementBuilder& Where(const QualifiedColumn& column); 255 256 // A full filter clause looking for an embedded null character. 257 // Is extremely specific to consistency checks, and so a more detailed construct is not required. 258 StatementBuilder& WhereValueContainsEmbeddedNullCharacter(std::string_view column); 259 StatementBuilder& WhereValueContainsEmbeddedNullCharacter(const QualifiedColumn& column); 260 261 // Indicate the operation of the filter clause. 262 template <typename ValueType> 263 StatementBuilder& Equals(const ValueType& value) 264 { 265 AddBindFunctor(AppendOpAndBinder(Op::Equals), value); 266 return *this; 267 } 268 template <typename ValueType> 269 StatementBuilder& Equals(const std::optional<ValueType>& value) 270 { 271 if (value) 272 { 273 AddBindFunctor(AppendOpAndBinder(Op::Equals), value.value()); 274 return *this; 275 } 276 else 277 { 278 return IsNull(); 279 } 280 } 281 // The optional index value can be used to specify the parameter index. 282 StatementBuilder& Equals(details::unbound_t, std::optional<size_t> index = {}); 283 StatementBuilder& Equals(std::nullptr_t); 284 StatementBuilder& Equals(); 285 StatementBuilder& Equals(const QualifiedColumn& column); 286 287 template <typename ValueType> 288 StatementBuilder& IsGreaterThan(const ValueType& value) 289 { 290 AddBindFunctor(AppendOpAndBinder(Op::GreaterThan), value); 291 return *this; 292 } 293 StatementBuilder& IsGreaterThan(details::unbound_t, std::optional<size_t> index = {}); 294 295 template <typename ValueType> 296 StatementBuilder& IsGreaterThanOrEqualTo(const ValueType& value) 297 { 298 AddBindFunctor(AppendOpAndBinder(Op::GreaterThanOrEqualTo), value); 299 return *this; 300 } 301 StatementBuilder& IsGreaterThanOrEqualTo(details::unbound_t, std::optional<size_t> index = {}); 302 303 StatementBuilder& LikeWithEscape(std::string_view value); 304 StatementBuilder& Like(details::unbound_t); 305 306 StatementBuilder& Escape(std::string_view escapeChar); 307 308 StatementBuilder& Not(); 309 StatementBuilder& In(); 310 311 // Appends a set of value binders for the In clause. 312 StatementBuilder& In(size_t count); 313 314 // IsNull(true) means the value is null; IsNull(false) means the value is not null. 315 StatementBuilder& IsNull(bool isNull = true); 316 StatementBuilder& IsNotNull() { return IsNull(false); } 317 318 // Operators for combining filter clauses. 319 StatementBuilder& And(std::string_view column); 320 StatementBuilder& And(const QualifiedColumn& column); 321 StatementBuilder& Or(const QualifiedColumn& column); 322 323 // Begin a join clause. 324 // The initializer_list form enables the table name to be constructed from multiple parts. 325 StatementBuilder& Join(std::string_view table); 326 StatementBuilder& Join(QualifiedTable table); 327 StatementBuilder& Join(std::initializer_list<std::string_view> table); 328 329 // Begin a left outer join clause. 330 // The initializer_list form enables the table name to be constructed from multiple parts. 331 StatementBuilder& LeftOuterJoin(std::string_view table); 332 StatementBuilder& LeftOuterJoin(QualifiedTable table); 333 StatementBuilder& LeftOuterJoin(std::initializer_list<std::string_view> table); 334 335 // Set the join constraint. 336 StatementBuilder& On(const QualifiedColumn& column1, const QualifiedColumn& column2); 337 338 // Specify the grouping to use. 339 StatementBuilder& GroupBy(std::string_view column); 340 StatementBuilder& GroupBy(const QualifiedColumn& column); 341 342 // Specify the ordering to use. 343 StatementBuilder& OrderBy(std::string_view column); 344 StatementBuilder& OrderBy(const QualifiedColumn& column); 345 StatementBuilder& OrderBy(std::initializer_list<std::string_view> columns); 346 347 // Specify the ordering behavior. 348 StatementBuilder& Ascending(); 349 StatementBuilder& Descending(); 350 351 // Limits the result set to the given number of rows. 352 StatementBuilder& Limit(size_t rowCount); 353 354 // Begin an insert statement for the given table. 355 // The initializer_list form enables the table name to be constructed from multiple parts. 356 StatementBuilder& InsertInto(std::string_view table); 357 StatementBuilder& InsertInto(QualifiedTable table); 358 StatementBuilder& InsertInto(std::initializer_list<std::string_view> table); 359 360 // Begin an insert or ignore statement for the given table. 361 // The initializer_list form enables the table name to be constructed from multiple parts. 362 StatementBuilder& InsertOrIgnore(std::string_view table); 363 StatementBuilder& InsertOrIgnore(QualifiedTable table); 364 StatementBuilder& InsertOrIgnore(std::initializer_list<std::string_view> table); 365 366 // Set the columns for a statement (typically insert). 367 StatementBuilder& Columns(std::string_view column); 368 StatementBuilder& Columns(std::initializer_list<std::string_view> columns); 369 StatementBuilder& Columns(const QualifiedColumn& column); 370 StatementBuilder& Columns(std::initializer_list<QualifiedColumn> columns); 371 372 // Set the columns for a select or create table statement. 373 StatementBuilder& Columns(std::initializer_list<details::SubBuilder> columns); 374 StatementBuilder& BeginColumns(); 375 StatementBuilder& Column(std::string_view column); 376 StatementBuilder& Column(const QualifiedColumn& column); 377 StatementBuilder& Column(Aggregate aggOp, std::string_view column); 378 StatementBuilder& Column(Aggregate aggOp, const QualifiedColumn& column); 379 StatementBuilder& Column(const details::SubBuilder& column); 380 StatementBuilder& EndColumns(); 381 382 // Set the columns null constraint. 383 StatementBuilder& NotNull(bool isTrue = true); 384 385 // Set the column's default value. 386 template <typename ValueType> 387 StatementBuilder& Default(const ValueType& value) 388 { 389 m_stream << " DEFAULT (" << value << ")"; 390 return *this; 391 } 392 393 // Add the values clause for an insert statement. 394 template <typename... ValueTypes> 395 StatementBuilder& Values(const ValueTypes&... values) 396 { 397 int bindIndexBegin = AppendValuesAndBinders(sizeof...(ValueTypes)); 398 // Use folding to add a binder for every value, specifically in the order they were given. 399 // Do not change this expression without understanding the implications to the bind order. 400 // See: https://en.cppreference.com/w/cpp/language/fold for more details. 401 (FoldHelper{}, ..., InsertValuesValueBinder(bindIndexBegin++, values)); 402 return *this; 403 } 404 StatementBuilder& BeginValues(); 405 template <typename ValueType> 406 StatementBuilder& Value(const ValueType& value) 407 { 408 InsertValuesValueBinder(AppendValueAndBinder(), value); 409 return *this; 410 } 411 StatementBuilder& EndValues(); 412 413 // Begin a table creation statement. 414 // The initializer_list form enables the table name to be constructed from multiple parts. 415 StatementBuilder& CreateTable(std::string_view table); 416 StatementBuilder& CreateTable(QualifiedTable table); 417 StatementBuilder& CreateTable(std::initializer_list<std::string_view> table); 418 419 // Begin an alter table statement. 420 // The initializer_list form enables the table name to be constructed from multiple parts. 421 StatementBuilder& AlterTable(std::string_view table); 422 StatementBuilder& AlterTable(QualifiedTable table); 423 StatementBuilder& AlterTable(std::initializer_list<std::string_view> table); 424 425 // Complete an alter table statement by adding a column. 426 StatementBuilder& Add(std::string_view column, Type type); 427 428 // Begin a table deletion statement. 429 // The initializer_list form enables the table name to be constructed from multiple parts. 430 StatementBuilder& DropTable(std::string_view table); 431 StatementBuilder& DropTable(QualifiedTable table); 432 StatementBuilder& DropTable(std::initializer_list<std::string_view> table); 433 434 // Begin a table deletion statement. 435 // The initializer_list form enables the table name to be constructed from multiple parts. 436 StatementBuilder& DropTableIfExists(std::string_view table); 437 StatementBuilder& DropTableIfExists(QualifiedTable table); 438 StatementBuilder& DropTableIfExists(std::initializer_list<std::string_view> table); 439 440 // Begin an index creation statement. 441 // The initializer_list form enables the index name to be constructed from multiple parts. 442 StatementBuilder& CreateIndex(std::string_view table); 443 StatementBuilder& CreateIndex(QualifiedTable table); 444 StatementBuilder& CreateIndex(std::initializer_list<std::string_view> table); 445 446 // Begin an unique index creation statement. 447 // The initializer_list form enables the index name to be constructed from multiple parts. 448 StatementBuilder& CreateUniqueIndex(std::string_view table); 449 StatementBuilder& CreateUniqueIndex(QualifiedTable table); 450 StatementBuilder& CreateUniqueIndex(std::initializer_list<std::string_view> table); 451 452 // Begin an index deletion statement. 453 // The initializer_list form enables the table name to be constructed from multiple parts. 454 StatementBuilder& DropIndex(std::string_view index); 455 StatementBuilder& DropIndex(QualifiedTable index); 456 StatementBuilder& DropIndex(std::initializer_list<std::string_view> index); 457 458 // Set index target table. 459 StatementBuilder& On(std::string_view table); 460 StatementBuilder& On(std::initializer_list<std::string_view> table); 461 462 // Begin a delete statement. 463 // The initializer_list form enables the table name to be constructed from multiple parts. 464 StatementBuilder& DeleteFrom(std::string_view table); 465 StatementBuilder& DeleteFrom(QualifiedTable table); 466 StatementBuilder& DeleteFrom(std::initializer_list<std::string_view> table); 467 468 // Begin an update statement. 469 // The initializer_list form enables the table name to be constructed from multiple parts. 470 StatementBuilder& Update(std::string_view table); 471 StatementBuilder& Update(QualifiedTable table); 472 StatementBuilder& Update(std::initializer_list<std::string_view> table); 473 474 // Begin an `update or replace` statement. 475 // The initializer_list form enables the table name to be constructed from multiple parts. 476 StatementBuilder& UpdateOrReplace(std::string_view table); 477 StatementBuilder& UpdateOrReplace(QualifiedTable table); 478 StatementBuilder& UpdateOrReplace(std::initializer_list<std::string_view> table); 479 480 // Output the set portion of an update statement. 481 StatementBuilder& Set(); 482 483 // Output the set portion of an update statement. 484 StatementBuilder& Vacuum(); 485 486 // General purpose functions to begin and end a parenthetical expression. 487 StatementBuilder& BeginParenthetical(); 488 StatementBuilder& EndParenthetical(); 489 490 // Adds the `without rowid` clause. 491 StatementBuilder& WithoutRowID(); 492 493 // Assign an alias to the previous item. 494 StatementBuilder& As(std::string_view alias); 495 496 // Gets the last bound index. 497 // A value of zero indicates that nothing has been bound. 498 int GetLastBindIndex() const { return m_bindIndex - 1; } 499 500 // Prepares and returns the statement, applying any bindings that were requested. 501 Statement Prepare(const Connection& connection); 502 503 // A convenience function that prepares, binds, and then executes a statement that does not return rows. 504 void Execute(const Connection& connection); 505 506 private: 507 enum class Op 508 { 509 Equals, 510 Like, 511 Escape, 512 Literal, 513 GreaterThan, 514 GreaterThanOrEqualTo, 515 }; 516 517 // Appends given the operation. 518 // The optional index value can be used to specify the parameter index. 519 int AppendOpAndBinder(Op op, std::optional<size_t> index = {}); 520 521 // Appends a set of binders for the values clause of an insert. 522 int AppendValuesAndBinders(size_t count); 523 524 // Appends a binder for the values clause of an insert. 525 int AppendValueAndBinder(); 526 527 // Adds a functor to our list that will bind the given value. 528 template <typename ValueType> 529 void AddBindFunctor(int binderIndex, const ValueType& value) 530 { 531 m_binders.emplace_back([binderIndex, value](Statement& s) { s.Bind(binderIndex, value); }); 532 } 533 534 // Helper template for binding incoming values for an insert. 535 template <typename ValueType> 536 StatementBuilder& InsertValuesValueBinder(int bindIndex, const ValueType& value) 537 { 538 AddBindFunctor(bindIndex, value); 539 return *this; 540 } 541 template <typename ValueType> 542 StatementBuilder& InsertValuesValueBinder(int bindIndex, const std::optional<ValueType>& value) 543 { 544 if (value) 545 { 546 AddBindFunctor(bindIndex, value.value()); 547 } 548 else 549 { 550 AddBindFunctor(bindIndex, nullptr); 551 } 552 return *this; 553 } 554 StatementBuilder& InsertValuesValueBinder(int, details::unbound_t) 555 { 556 return *this; 557 } 558 StatementBuilder& InsertValuesValueBinder(int bindIndex, std::nullptr_t) 559 { 560 AddBindFunctor(bindIndex, nullptr); 561 return *this; 562 } 563 564 std::ostringstream m_stream; 565 // Because binding values starts at 1 566 int m_bindIndex = 1; 567 std::vector<std::function<void(Statement&)>> m_binders; 568 bool m_needsComma = false; 569 }; 570 }