- Generated by
1.9.8
|
Lightweight 0.20260625.0
|
SQL Query builder class is the starting point of building sql queries to execute.
To create a database you need to use Migration() function provided by the SqlQueryBuilder class, then use API defined in SqlMigrationQueryBuilder to construct sql query to migrate to another schema or create a database with the given schema. Detailed documentation can be found on separate documentation pages for each of the classes in the hierarchy, here we present overall usage of the library. Following options exist.
CreateTable(tableName) Following calls can be chained, for example CreateTable("test").Column(first).Column(second)... Available functions:PrimaryKey(std::string columnName, SqlColumnTypeDefinition columnType)PrimaryKeyWithAutoIncrement( std::string columnName, SqlColumnTypeDefinition columnType )SqlColumnTypeDefinitions::Bigint<type> IDENTITY(1,1) and PostgreSQL emits the matching serial pseudo-type (Bigint → BIGSERIAL, Integer → SERIAL, Smallint/Tinyint → SMALLSERIAL). SQLite is the exception — AUTOINCREMENT is only valid on an INTEGER PRIMARY KEY, which is a 64-bit rowid alias there, so the column is always INTEGER.Smallint/Tinyint cap at 32767 rows on both PostgreSQL (smallserial) and SQL Server (SMALLINT IDENTITY). Declare Bigint (the default) unless you specifically want that limit.Column(std::string columnName, SqlColumnTypeDefinition columnType), Column(SqlColumnDeclaration column)SqlColumnDeclaration can be used as an argument.RequiredColumn(std::string columnName, SqlColumnTypeDefinition columnType)Timestamps()ForeignKey(std::string columnName, SqlColumnTypeDefinition columnType, SqlForeignKeyReferenceDefinition foreignKey)RequiredForeignKey function.CreateTable("test").Column(first).UniqueIndex().Unique() enables the UNIQUE constraint on the last declared column.Index() enables the INDEX constraint on the last declared column.UniqueIndex() enables the UNIQUE and INDEX constraint on the last declared column.AlterTable(tableName) Available functions:RenameTo(std::string_view newTableName)RenameColumn(std::string_view oldColumnName, std::string_view newColumnName)DropColumn(std::string_view columnName)AddIndex(std::string_view columnName)AddUniqueIndex(std::string_view columnName)DropIndex(std::string_view columnName)DropTable(tableName)To insert elements in the database first call FrommTable(table) function to specify which table to use, and then function Insert() to start construction of SqlInsertQueryBuilder
Set(std::string_view columnName, ColumnValue const& value)To select some elements from the Database you first need to specify which existing table you are going to use, for this use FromTable(table) function, it returns you an instance of a SqlQueryBuilder and then use Select() function to continue constructing select query that described by SqlSelectQueryBuilder interface. Here we present a compressed list of functions that can be used to create complete selection query.
Distinct()Field()Field("field")Field(SqlQualifiedTableColumnName { "Table", "field" })SqlQualifiedTableColumnName from a string QualifiedColumnName<"Table.field">Fields()Fields({"a", "b", "c"})Fields({"a", "b", "c"}, "Table_B")Field<TableType>(). Note: can pass more than one typeOrderByGroupBySqlWhereClauseBuilderWhereWhere("a", 42) specify simple condition that is equivalent to the sql query WHERE "a" = 42Where(SqlQualifiedTableColumnName { .tableName = "Table_A", .columnName = "a" }, 42) such call translated into WHERE "Table_A"."a" = 42Or(), And() and Not() logical functions to apply to the next callWhere("a",1).Or().Where("b",1)If(optional).ThenWhere(column[, binaryOp]) — conditional WHERE driven by a std::optionalThenWhere(column) appends WHERE column = *value only when the optional holds a value; when the optional is empty the call is a no-op and the underlying query is left untouched. Returns the underlying builder, so it can be chained between other clauses.ThenWhere(column, binaryOp) mirrors Where(column, binaryOp, value) — emits WHERE column <binaryOp> *value (e.g. ">=", "<", "!=", "LIKE") under the same empty/populated rules. Useful for range-style filters over SqlDateTime, numeric columns, etc.Where — plain strings, SqlQualifiedTableColumnName, and FullyQualifiedNameOf<&Record::field>.SqlWhereClauseBuilder: Select, Update, and Delete.Inner|LeftOuter|RightOuter|FullOuter + JoinSqlJoinConditionBuilder for detailsCount()SELECT COUNT(*) FROM .... Exposed on the starter directly — SELECT COUNT(*) is well-formed without an explicit column list.First()All()Range(offset, limit)Compile-time guard against empty projections.
Select()returns aSqlSelectQueryStarter— a distinct type that intentionally does not exposeAll(),First(), orRange(). Both of these patterns are therefore compile errors:auto bad1 = q.FromTable("T").Select().All(); // chain — compile errorauto q2 = q.FromTable("T").Select();auto bad2 = q2.All(); // named lvalue — compile errorAdding a projection (
Field,Fields,FieldAs,Build) returns aSqlSelectQueryBuilder&aliasing the starter's storage. That reference exposes the finalizers:// Chain — Field returns Builder&, finalizer bound to that reference:auto good = q.FromTable("T").Select().Field("*").All();// Imperative — capture the first projection as auto&, continue from there:auto starter = q.FromTable("T").Select();auto& query = starter.Field(columns[0].name);for (size_t i = 1; i < columns.size(); ++i) query.Field(columns[i].name);auto result = query.All();To select all columns, use
.Field("*")— the single-Fieldoverload special-cases the wildcard.Fields("*")andFields({"*"})quote the literal and produceSELECT "*" FROM ..., which is not what you want.
Distinct()is exposed on the starter as a state-preserving override (it returnsSqlSelectQueryStarter&), so chains likeSelect().Distinct().Fields(...).All()keep working whileSelect().Distinct().All()is still a compile error.Where,OrderBy,GroupBy, and the join family (InnerJoin,LeftOuterJoin, etc.) are re-exposed viausingdeclarations and promote the chain — they returnSqlSelectQueryBuilder&. A chain likeSelect().WhereNotNull("x").All()will therefore compile (a small leak in the gate, in exchange for keeping the commonSelect().WhereNotNull("x").Count()pattern working unchanged).
1.9.8