SQL Schema Generation

Relevant source files

The following files were used as context for generating this wiki page:

• include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp
• include/HXLibs/db/sql/DataBase.hpp
• include/HXLibs/db/sql/SqlType.hpp
• include/HXLibs/db/sqlite3/SqliteDB.hpp
• include/HXLibs/db/sql/Dml.hpp
• include/HXLibs/db/sql/DbType.hpp
• tests/db/MakeSql.cpp

SQL Schema Generation in HXLibs automates the creation of database tables and indexes by reflecting on C++ structures. The system maps C++ types to SQL data types and interprets member constraints to build valid CREATE TABLE and CREATE INDEX statements. This ensures that the database schema remains synchronized with the application's data models.

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp, include/HXLibs/db/sql/DataBase.hpp

Architecture Overview

The schema generation process uses template metaprogramming and reflection to inspect user-defined structs. The CreateDbSql utility provides static methods to generate SQL strings, while the DataBaseInterface provides a high-level API to execute these operations against a specific database backend like SQLite.

The diagram shows how a C++ struct is transformed into executable SQL through reflection and specialized generation logic.
Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:144-186, include/HXLibs/db/sql/DataBase.hpp:95-103

Type Mapping

HXLibs maps C++ fundamental types and library-specific types to SQL standard types. The mapping is performed by getSqlTypeStrImpl, which evaluates types at compile time.

C++ Type	SQL Type	Description
std::is_integral	INTEGER	Signed integers (Unsigned not supported)
std::is_floating_point	REAL	Floating point numbers
meta::StringType	TEXT	Strings and character sequences
db::Date	DATE	Date representations
db::Time	TIME	Time representations
db::Blob	BLOB	Binary large objects
IsCustomTypeVal	TEXT	Custom types default to TEXT

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:41-65, include/HXLibs/db/sql/SqlType.hpp:33-46

Table Creation Logic

The createDatabase function generates the CREATE TABLE statement. It iterates through the members of a struct, determining the column name and type. If a member is wrapped in a Constraint<T, ...>, the generator extracts SQL attributes such as PRIMARY KEY or NOT NULL.

Constraints Processing

The system handles several column-level attributes:

• NotNull: Appends NOT NULL.
• PrimaryKey: Appends PRIMARY KEY and increments the primary key count.
• Unique: Appends UNIQUE.
• AutoIncrement: Appends AUTOINCREMENT.
• DefaultVal: Appends DEFAULT followed by the serialized value.
• Foreign: Generates FOREIGN KEY references based on member pointers.

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:85-138, tests/db/MakeSql.cpp:44-70

Union Attributes

Complex table constraints, such as composite primary keys or composite foreign keys, are defined in a nested struct named UnionAttr. The generator searches for this struct and processes its members to add table-level constraints.

This diagram illustrates the relationship between a data struct and its extended SQL attributes via the UnionAttr pattern.
Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:169-216, tests/db/MakeSql.cpp:32-38

Index Generation

Indexes are generated separately from the table structure. SQLite requires CREATE INDEX to be a standalone statement. HXLibs supports both single-column indexes defined via Constraint and composite indexes defined in UnionAttr.

Single and Composite Indexes

1. Single Column: Uses attr::Index<>, attr::AseIndex, or attr::DescIndex within a member's Constraint.
2. Composite: Uses attr::Index<attr::IndexOrder<...>> within UnionAttr to specify multiple columns and their respective sort orders (Asc/Desc).

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:248-316, tests/db/MakeSql.cpp:72-80

Execution Flow

The SqliteDB class implements the execution of generated schema SQL. When createDatabase<Table>() is called, it performs the following steps:

1. Generate Table SQL: Calls sqlite::CreateDbSql::createDatabase.
2. Execute Table SQL: Runs the string via sqlite3_exec.
3. Generate Index SQLs: Calls sqlite::CreateDbSql::createIndex which returns a vector of strings.
4. Execute Index SQLs: Iteratively executes each index statement.

Sources: include/HXLibs/db/sqlite3/SqliteDB.hpp:89-103, include/HXLibs/db/sql/DataBase.hpp:95-103

Error Handling

The execution methods return a container::Try<> object. If SQL generation or execution fails (e.g., multiple primary keys declared, which is explicitly checked in MakeCreateDbSql.hpp), the exception is caught and stored within the Try wrapper.

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp:218-221, include/HXLibs/db/sqlite3/SqliteDB.hpp:97-101

Summary

SQL Schema Generation in HXLibs provides a typesafe, reflection-based approach to database initialization. By defining C++ structs with specific constraints and nested UnionAttr metadata, developers can generate complete SQLite schemas including tables, constraints, and complex indexes without writing manual SQL.

Sources: include/HXLibs/db/sqlite3/MakeCreateDbSql.hpp, include/HXLibs/db/sql/DataBase.hpp, tests/db/MakeSql.cpp