ddl2cpp with C++26 reflection and `#embed`

[vaijns]

With C23 #embed (which is also in C++26) and C++26 reflection it's probably possible to embed the SQL files, parse them and create the structs at compile-time without invoking any separate tool. Further, annotations could be used to have columns renamed, specify other types and such things.

So similarly to #99 the question here is, whether you'd want this (yet) or if you'd rather keep the extra generation step or wait for reflection support in the other compilers. I think this would be quite a fun project. 😄

-Jonas

[rbock]

Hi Jonas, oh, interesting I missed the #embed directive, TIL.

As shown in #99, I have been thinking about representations for tables in the context of reflection, too. So far, my focus had been to improve the readability the C++ representation of tables, which could include representations as strings.

I also played around with annotations, but was not happy with readability, e.g.

  [[=sqlpp::tag::has_default{}]]
  [[=sqlpp::tag::sql_name<"first_name">{}]]
    std::optional<sqlpp::text> firstName;

Parsing the DDL and creating C++ table classes in place could also be an option, but I am not sure if it is worth the effort. I am wary of two things:

1. The compile time.
2. The error messages (or generally debugging) if the parsing fails.

That said,

• maybe it would be less severe than I expect?
• it would definitely be an interesting experiment to try parsing DDLs in place in C++.

If we can come up with a variant that can act as a drop-in replacement for the current generated table definitions, it might make sense to add this to the library.

Otherwise, as mentioned in #99, it might make more sense to play with these things in an experimental sqlpp26 project.

WDYT?

Cheers,
Roland

[vaijns]

Yeah, I think #embed in combination with reflection can be very powerful, the authors of P2996 have tried it for generating a struct from a JSON file for example.

Your example with the annotations reminds me of what I had in mind for generating the tables last year. I must say I do still like this approach as well.
You could limit the annotations by having just one on the struct itself specifying a naming scheme like [[=sqlpp::naming_scheme::snake_case]] which of course comes with the complexity of transforming between those different casings. If you'd want a completely different name you still have to use the annotation per member of course. Then has_default sounds like something you can figure out with std::meta::has_default_member_initializer:

#include <meta>
#include <print>

struct my_struct_a{
    int a{123};
};

struct my_struct_b{
   int b;
};

int main(){
    std::println("a: {}, b: {}",
        std::meta::has_default_member_initializer(^^my_struct_a::a),
        std::meta::has_default_member_initializer(^^my_struct_b::b)
    );
}

Don't think there's a way to get the default value directly but you could always instantiate the struct and read the value, right?

So you'd basically only have something like this left:

[[=sqlpp::naming_scheme::snake_case]]
struct user{
    std::optional<sqlpp::text> firstName{};
};

I do see the possible problems with compile-time, as even my simple program for generating an alias struct took quite a few seconds to compile. Not sure how it scales with more complex things.
/edit: nevermind, a lot of the compile-time came from std::println, with printf it's quite a bit faster. ~3.5s vs ~1s and removing algorithm shaves off another 0.1s. Guess it's best to just try it out. I might do on the weekend.

Though I'd guess in reflection terms there's not even that much more to do in comparison and I also wouldn't expect the parsing itself to take too much time but you would have some more templates for sure.
Maybe modules can help there or extern template, so it isn't evaluated once per translation unit at least? I've never tried tbh.

In terms of error messages and debugging I wouldn't be very worried actually, as for debugging you can probably just use your functions at runtime if they're constexpr (minus the actual reflection functions).
And writing your parsing state to a string, then using define_static_string and printing it at runtime is also a possible fallback. Also I've heard about CLion having constexpr debugging now, though I'm not using an IDE like CLion, but it seems tooling is getting better.

For parsing errors in general, static_assert, concepts and exceptions could help. You do have to place them in the right spots of course, so I guess this could be a pain at some points but generally I think constexpr has a lot to offer in that regard now, to make it easy to work with for end-users at least.

Whether it makes sense to have an sqlpp26 project depends on your goal of compatibility with sqlpp23 I guess. I've only skimmed over some of the table definition but I'd guess it's possible to make it work with it, then a branch might be enough (I think my definition for the alias should be pretty close already for example).
On the other hand, if you think there's potential for breaking changes that would be worth sacrificing compatibility, or if you see it as more of an experimental playground, then having another repo might be reasonable.

-Jonas

[MeanSquaredError]

I am perfectly fine with someone adding support for generating the table definitions at compile time in sqlpp26, as long as there is also compatibility with the existing system which uses ddl2cpp, which generates the table definitions as header files.

I wouldn't mind upgrading to the bleeding-edge sqlpp26, but I would probably keep using ddl2cpp for a while exactly because I expect that parsing SQL and generating table definitions at compile time to be really slow. And besides the way it is now, the build system can compare the timestamps of the SQL script file and the header file with the table definitions, and only re-generate the header file when the SQL file has been modified. I assume that this kind of dependency check would probably be impossible if the sql code is included through embed and then parsed at compile time.

[rbock]

I think there are two options:

1. Similar to the .as function in Aliases with reflection^^ #99, we could generate code at compile time, that is compatible with the code generated by ddl2cpp.
2. We could also come up with something new (as in the table.cpp attachment I showed above). In that case, a new repository would make more sense, IMO. In that case, other things like statements and result rows should be rewritten with reflection in mind, too. That's a major undertaking.

Suggestion: Let's try option 1 first.

[MeanSquaredError]

2. We could also come up with something new (as in the table.cpp attachment I showed above). In that case, a new repository would make more sense, IMO. In that case, other things like statements and result rows should be rewritten with reflection in mind, too. That's a major undertaking.

Even if the table representation in the C++ code is different from the one that ddl2cpp currently generates, I think it wouldn't be a major problem, because ddl2cpp can be updated to generate .h files with the new table representation. What I actually need is a way to generate the database model (as a headers file) in a separate step, just like ddl2cpp does now. As I mentioned above this is mostly for performance reasons.

I am not sure how difficult it would be to write an SQL parser similar to ddl2cpp as a C++ template metaprogam, but I suspect it would be a ton of work. Then again I am definitely not an expert in C++ template metaprogramming.

Maybe it makes sense to search for some kind of parser generator, similar to Yacc/Bison, that can take a grammar and then generate the parser as a C++ template metaprogram. It would probably be much easier than writing the parser by hand.

[vaijns]

I think it would make sense to have a kinda generic AST which is generated after parsing, allowing for easily swappable "backends" that generate the actual table structure from the AST. The frontend (parser) could then also be swappable/replaced if we'd instead want to write out our input table definition as C++ code.

The parsing itself wouldn't require much template programming at all btw. just constexpr functions which now have most capabilities a normal C++ program has. Just no I/O but you can store the result of the constexpr function in a variable and print it at runtime (or run all of it at runtime as constexpr can do both).
I also wouldn't worry too much about parser generators in the beginning as we could start with parsing something simpler than SQL (plus ddl2cpp only parses a subset anyways) to try it out and go further if it has potential.

For a completely separate build step a possibilty would probably be it's own CMake target you depend on in your main program which can be configured to not run by default, only explicitly. From what I've read, if you exclude it from default build, even the dependency won't trigger it's build implicitly.
/edit: oh, I've also read something about only Visual Studio using the property EXCLUDE_FROM_DEFAULT_BUILD, I'd have to try to see if there's a way to do this (+ cross-platform).

In general performance should probably just be measured and then checked for possible improvements if necessary.

-Jonas

[vaijns]

Generally might be a good idea to just play around with it in a separare project without any sqlpp code (or just locally) a bit more first, before trying to directly integrate it into sqlpp in whatever way.

It's just too many questions with too little answers to stamp out a whole path on where we should be heading and how we should do it I think.

-Jonas

[MeanSquaredError]

I think it would make sense to have a kinda generic AST which is generated after parsing, allowing for easily swappable "backends" that generate the actual table structure from the AST. The frontend (parser) could then also be swappable/replaced if we'd instead want to write out our input table definition as C++ code.

The parsing itself wouldn't require much template programming at all btw. just constexpr functions which now have most capabilities a normal C++ program has. Just no I/O but you can store the result of the constexpr function in a variable and print it at runtime (or run all of it at runtime as constexpr can do both). I also wouldn't worry too much about parser generators in the beginning as we could start with parsing something simpler than SQL (plus ddl2cpp only parses a subset anyways) to try it out and go further if it has potential.

I think that parsing SQL scripts with C++ constexpr functions will require some kind of constexpr map in order to detect duplicate table names/rows, also other C++ containers like vectors will be needed. At the moment the non-trivial constructors of most C++ containers are not constexpr because they do dynamic memory allocation. So this means that some kind of constexpr-friendly container library will be needed in order tp do parsing at compile time.

For a completely separate build step a possibilty would probably be it's own CMake target you depend on in your main program which can be configured to not run by default, only explicitly. From what I've read, if you exclude it from default build, even the dependency won't trigger it's build implicitly. /edit: oh, I've also read something about only Visual Studio using the property EXCLUDE_FROM_DEFAULT_BUILD, I'd have to try to see if there's a way to do this (+ cross-platform).

Your description pretty much matches the way I am using ddl2cpp now. The database model header is a custom CMake target, generated by ddl2cpp. So CMake checks if the input SQL script is newer than the database model headers and if so re-runs ddl2cpp to regenerate the headers from the SQL script. I don't even use EXCLUDE_FROM_DEFAULT_BUILD, because I am fine with the custom target being a part of the build, because CMake checks the timestamps of the input (sql scriipt) and output (header) files and skips the rebuilding if it is unnecessary.

In general performance should probably just be measured and then checked for possible improvements if necessary.

Actually I can even put up with a small performance degradation of the SQL parsing, as long as it is possible to output the database model into a separate header/module, using a custom CMake rule. In that case CMake would ensure that the database model would be rebuilt only when the input SQL has changed, so the performance loss will be negligible.

[vaijns]

I think that parsing SQL scripts with C++ constexpr functions will require some kind of constexpr map in order to detect duplicate table names/rows, also other C++ containers like vectors will be needed. At the moment the non-trivial constructors of most C++ containers are not constexpr because they do dynamic memory allocation. So this means that some kind of constexpr-friendly container library will be needed in order tp do parsing at compile time.

Dynamic memory allocation is completely fine at compile-time though. Even for list and unordered_map all of the constructors are constexpr. Not sure if they were in C++23 already. If I'm up-to-date, the new std::hive is one of the, or maybe even the only non-constexpr container as this adopted paper suggests. Vectors of std::meta::info are btw. also what nonstatic_data_members_of of std::meta (reflection) is returning for example.
What I can't say is how much of it is already implemented in the compilers as I don't use most of these containers often and I don't do as much compile-time programming as I'd like to.

The only thing you can't do is persist those dynamic allocations to runtime. That's where define_static_array would come into play if needed (takes a range and creates an array from it that can be persisted). And even if we didn't have those things, there will be inplace_vector which requires no dynamic allocations at all, because while the size is dynamic, the capacity is set to a fixed value at compile-time.
And for generating tables in the end I think you don't have to persist much at all, only the strings of your table/columns I'd say.

It's really amazing (and hard to keep up with) what you can do in a constexpr context now. I mean, it's possible I'll be proven wrong, as I said I don't do it often enough, but I wouldn't expect huge problems.

To make sure I had to try it out myself and this compiles for example (GCC 16, C++26, -freflection):

#include <cstddef>
#include <vector>
#include <meta>
#include <utility>
#include <cstdio>

class my_object{
public:
	consteval my_object(const my_object&) = delete;
	consteval my_object& operator=(const my_object&) = delete;

	consteval my_object(my_object&& other) noexcept{
		std::swap(value, other.value);
	}
	consteval my_object& operator=(my_object&& other) noexcept{
		if(value != nullptr){
			delete value;
			value = nullptr;
		}

		std::swap(value, other.value);

		return *this;
	}

	consteval my_object(int value) : value(new int(value)){

	}

	constexpr ~my_object(){
		if(value != nullptr){
			delete value;
			value = nullptr;
		}
	}

	int* value{nullptr};
};

class my_runtime_object{
public:
	int value{};
};

consteval std::vector<my_object> get_objects(){
	std::vector<my_object> objects{};
	objects.reserve(4);
	objects.emplace_back(123);
	objects.emplace_back(456);
	objects.emplace_back(789);
	objects.emplace_back(0);
	return objects;
}

consteval std::vector<my_runtime_object> transform_objects(std::vector<my_object>&& objects){
	std::vector<my_runtime_object> result{};
	result.reserve(objects.size());
	for(std::size_t i{0}; i < objects.size(); ++i){
		result.emplace_back(*objects[i].value);
	}
	return result;
}

inline constexpr auto my_objects{std::define_static_array(transform_objects(get_objects()))};

auto main(int /*argc*/, char* /*argv*/[]) -> int{
	for(const auto& element : my_objects){
		std::printf("%d\n", element.value);
	}

	return 0;
}

[MeanSquaredError]

Dynamic memory allocation is completely fine at compile-time though.

Indeed my statement that dynamic memory allocation does not work at compile time is incorrect. Starting from C++20 compile-time execution is allowed to allocate dynamic memory as long as this allocation is "transient", that is any dynamic memory allocated at compile-time should be deallocated at compile time too. So standard containers like maps and vector still cannot be passed from the compile-time execution to the runtime execution. This in turn means that the final representation of the AST generated from the SQL script at compile-time will have to do one of the two:

• Either use either static arrays without any maps
• Or will have to resort to using some kind of non-standard map containers that don't have dynamic memory allocation.

Overall I am all for this experiment (if it works), just pointing out possible problems.

BTW, I don't know if this limitation on the dynamic memory allocation/freeing has been relaxed or otherwise changed in C++26. I am used to checking things in cppreference and obviously it is not very current with regard to C++26.

[vaijns]

I might finally start working on this. :D

I've already tried the reflection part which allows writing something like:

struct [[=sqlpp::meta::naming_scheme::snake_case]] [[=sqlpp::meta::table("mytable")]] user{
	[[=sqlpp::meta::auto_increment]]
	// we'd probably have some kind of protocol (concept) that this type needs to implement
	// for making it work with the sql connectors
	[[=sqlpp::meta::sql_type<int>]]
	std::uint64_t id;

	[[=sqlpp::meta::column("username")]]
	std::string name;

	std::chrono::system_clock::time_point creation_time;
};

and turning it into structs:

template<typename T, typename SqlTypeOverride = ::std::nullopt_t>
struct reflected_column{
	using type = T;
	using sql_type_override = SqlTypeOverride;
	std::optional<std::string_view> sql_name_override;
	std::optional<sqlpp::meta::naming_scheme> naming_scheme_override;
	std::string_view name;
	bool is_primary_key;
	bool is_auto_incremented;
	bool has_default_initializer;
};

template<typename T>
struct reflected_table{
	using type = T;
	std::optional<std::string_view> sql_name_override;
	std::optional<sqlpp::meta::naming_scheme> naming_scheme_override;
	std::string_view name;
};

You can try it out on godbolt. I might still change this too though, I mostly focused on getting it to work for now.

My plan now would be to take those structs and turn them into what sqlpp needs. So it's kinda a split of frontend and backend with the first frontend being reflection. Then we could also add a parser from a #embed string as a frontend and turn it into the same structs from which we can generate the final types again.

[rbock]

Interesting timing. I am currently working on a C++26 version in the sqlpp26 branch (not ready for anything).

In the current approach, a table would be defined like this:

  namespace test {
  struct _TabBar {
    using generator = sqlpp::make_table<_TabBar, "tab_bar",
        sqlpp::column_spec<"id", std::int64_t>::with_default,
        sqlpp::column_spec<"firstName", std::string_view>::with_default::with_sql_name<"first_name">,
        sqlpp::column_spec<"language", std::optional<std::string_view>>,
        sqlpp::column_spec<"department", std::string_view>>;
  };
  using TabBar = sqlpp::table<_TabBar>;

[MeanSquaredError]

@rbock
The sqlpp26 branch is interesting news! Somehow I missed its creation.

I browsed quickly through the newly added commits and did not notice any C++26-specific features being used so far. I could easily be missing something, since I did not look in great detail, but so far it looks to me like a refactoring (which is not a bad thing per se). Is there a reason why the refactoring is done on the C++26 branch instead of the C++23 branch? Maybe to avoid breaking the C++23 branch that works well the way it is now?

BTW the newly added ranges support is a great feature!

[rbock]

did not notice any C++26-specific features

Then you need to look more closely :-)

BTW the newly added ranges support is a great feature!

Thanks and that is not possible in C++23, for instance.

[vaijns]

Actually got selects working now at least. Inserts fail because it seems I've messed up something with the required insert columns. Though the code is very messy right now, so still got things to do.

But what I can now do is having this code (sqlpp::meta::make_table<T> is what the whole generation is behind):

#include <cstdlib>
#include <sqlite3.h>
#include <sqlpp23/sqlpp23.h>
#include <sqlpp23/sqlite3/sqlite3.h>
#include <memory>
#include <iostream>

struct [[=sqlpp::meta::naming_scheme::snake_case]] [[=sqlpp::meta::table("my_table")]] user{
	[[=sqlpp::meta::auto_increment]]
	[[=sqlpp::meta::sql_type<sqlpp::integral>]]
	std::uint64_t id;

	[[=sqlpp::meta::column("username")]]
	std::string name;

	[[=sqlpp::meta::naming_scheme::pascal_case]]
	std::chrono::system_clock::time_point creation_time;
};

using my_table = sqlpp::meta::make_table<user>;

auto log_cerr(sqlpp::log_category, std::string const& message) -> void{
	std::cerr << message << '\n';
}

auto main(int /*argc*/, char* /*argv*/[]) -> int{
	auto config = std::make_shared<sqlpp::sqlite3::connection_config>();
	config->path_to_database = "./testdb.sqlite3";
	config->flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
	config->debug = sqlpp::debug_logger({sqlpp::log_category::all}, log_cerr);
	sqlpp::sqlite3::connection db;
	db.connect_using(config);
	my_table table{};
	for(auto const& row : db(sqlpp::select(table.id, table.name, table.creation_time).from(table).where(table.id != 0u))){
		std::cout << "id: " << row.id << ", username: \"" << row.name << "\"\n";
	}
	return EXIT_SUCCESS;
}

Generate this statement:

Preparing: 'SELECT my_table.id, my_table.username, my_table.CreationTime FROM my_table WHERE my_table.id <> 0'