Support for CREATE, DROP tables and views

[margaretselzer]

I outline below the support I have built in my project for creating (and dropping) database tables in a structured manner. If it would be considered to be incorporated in sqlpp11 I would be happy to submit a PR.

This support consists of two parts.

1. Modification in the boilerplate sqlpp class definitions and helper functions
2. Macros that build on the updated sqlpp class definitions

Changes to the sqlpp boilerplate
a) Each db object has a function with the following signature: inline static void createInDatabase(sqlpp::sqlite3::connection& db, bool createAsTemp = false). This function "knows" how to create the object in the database.
b) Each db object derives from either a view_tag or a table_tag to help identifying if the object is a table or view.
c) I also use some helper functions to make life (creating db schemas) easier. These are:

template <typename DbObjT>
void dropIfExists(sqlpp::sqlite3::connection& db);

template <typename... DbObjTs>
size_t createDbObjects(sqlpp::sqlite3::connection& db, bool createAsTemp = false);

Which makes it possible to set up schemas like:

        numTables = createDbObjects<
            TblGlobalParams, TblComponents, TblSmartCommands, TblSubCommands, 
            TblRevisionsModule, TblFrequencies>(db);
        numViews += createDbObjects<ViewAmpCalibration, ViewConverterSetup>(db);

where the returned numbers can be used to verify (via running a query on the master table) that the number of created tables and views match the number of tables, views actually created in the database.

As you can see supporting DROP is not much more than having that free function above and being able to take the db-type (i.e. table or view) and name of the db object from the class type and assemble a DROP [TABLE|VIEW] IF EXISTS {objectName}; raw sql that is then executed on the connection.

Macros

I have a solution that does not use Boost and makes it possible to create tables and views like so:

#define _TblProducts_field_definitions_ \
    (id, sqlpp::unsigned_integral, sqlpp::tag::require_insert) \
    (product, sqlpp::text, sqlpp::tag::require_insert) \
    (price, sqlpp::floating_point, sqlpp::tag::require_insert)

DEFINE_SQLPP_TABLE(Products);

DEFINE_SQLPP_TABLE(
  // Table name without the `Tbl` prefix
  Products,
  // Create query part - fields, indexes and constraints
  R"(
    id		INTEGER NOT NULL,
    product	TEXT NOT NULL,
    price	REAL NOT NULL CHECK (price > 100),
    PRIMARY KEY(id)
  )");

auto p = TblProducts{};

#define _ViewConverterSetup_field_definitions_ \
    (conversionTypeCode, sqlpp::unsigned_integral, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update) \
    (rfPath, sqlpp::text, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update) \
    (rfPathPos, sqlpp::unsigned_integral, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update) \
    ... 
    (command, sqlpp::text, sqlpp::tag::can_be_null, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update) \
    (checksum, sqlpp::unsigned_integral, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update)

DEFINE_SQLPP_VIEW(
    // View name without the 'View' prefix
    ConverterSetup,
    // initializer
    TblConverterSetup tcs{};
    TblConversionTypes tct{};
    TblConverters tc{};
    ViewT v{};  // using alias to self
    auto tcsMax =
        sqlpp::cte(sqlpp::alias::x)
            .as(select(tcs.conversionTypeCode, max(tcs.sequenceNum).as(v.numConverters))
                    .from(tcs)
                    .group_by(tcs.conversionTypeCode));,
    // select expression
    sqlpp::with(tcsMax)(
    select(tcs.conversionTypeCode,
        tcs.rfPath, tcs.rfPathPos,
        ...
        tcs.command,
        tcs.checksum)
    .from(tcs.join(tct).on(tcs.conversionTypeCode == tct.conversionTypeCode)
             .join(tc).on(tcs.converterId == tc.converterId)
             .join(tcsMax).on(tcs.conversionTypeCode == tcsMax.conversionTypeCode)))
);

auto v = ViewConverterSetup{};

Tables
The table definition is pretty straightforward. It takes the defined fields and then creates a boilerplate table definition. The Tbl prefix is automatically prepended to the given name.

Note: An improvement to adding the field creation raw SQL to the macro could be that the raw SQL defining the field properties and checks are added to the field definitions and so in the table definition the only things required to be added would be the table constraints.

Views
The view is treated the same as a table whose fields are read-only. The generated class will essentially be the same as for the table except that the prefix is View instead of Tbl. The main notable difference is that since a view is built on a select query, we can use sqlpp itself to define the create statement for the view. The sqlpp needed to define the create statement is split up in two parts: initializer and select expression.
The initializer part makes available all the sqlpp objects that will be used to build the select query. The select expression is itself the select statement that will be run within an sqlpp::custom_query() where the necessary raw SQL is added to create the view.

In the example above I use a rather complex sqlpp query with a CTE so it even includes an alias to the just defined view class itself (we reference the class within its createInDatabase() method.)

Note: The view fields in the example are treated as read-only, but if someone wanted to create views where appropriate triggers take care of inserts/updates in the related tables, these fields could even be marked differently to allow inserts and updates in which case the view would really only be different from a table (from an sqlpp/C++ standpoint) in that is created with an sqlpp select statement.

This hardcoded Tbl and View name prefixes of course might not be to everyone's liking - they are easy to remove.

Note: Capturing the types returned by an sqlpp select query is not exactly straightforward. You may also want to use enums or other types not supported by your database in your objects so you may have the idea to use your own record structures to store data retrieved from the database. Once you have the field definitions defined via a macro, it is pretty easy to define macros that extract the data from sqlpp objects into your own data structure or insert data from your own data structure to an sqlpp insert statement largely automating all your to-from data conversions. If it is to anyone's interest I may be able to provide some examples.

[rbock]

Thanks for sharing your approach! Looks nifty! This is certainly an alternative to the code generated by ddl2cpp.

I wonder if there is a way to get the SQL column definition into the field definition? e.g.

#define _TblProducts_field_definitions_ \
    (id, sqlpp::unsigned_integral, sqlpp::tag::require_insert, "INTEGER NOT NULL") \
    (product, sqlpp::text, sqlpp::tag::require_insert, "TEXT NOT NULL") \
    (price, sqlpp::floating_point, sqlpp::tag::require_insert, "REAL NOT NULL CHECK (price > 100)")

DEFINE_SQLPP_TABLE(
  // Table name without the `Tbl` prefix
  Products,
  // Create query part - indexes and constraints
  R"(
    PRIMARY KEY(id)
  )");

That would make it easier to keep SQL table creation and sqlpp table types in sync.

[margaretselzer]

I wonder if there is a way to get the SQL column definition into the field definition?

Yes, as I suggested in my original post, it is a potential improvement. I looked briefly at the code now and I think it should be doable. The raw SQL though will probably not be the last macro arg, but the second as the number of tags is not fixed. Maybe using parenthesis could solve even that problem. Needs a bit of tinkering. Should I give it a go? (I only want to spend time on it if it leads to an actual contribution to the library.)

[rbock]

Yes, as I suggested in my original post, it is a potential improvement.

Ah, I somehow missed that.

I think it should be doable.

Cool.

Should I give it a go? (I only want to spend time on it if it leads to an actual contribution to the library.)

I freely admit, I have no idea how much this is of interest? I would want to see at least two more folks on this thread saying they are interested.

Edit: \

1. Rationale: Both, adding your code to the library and maintaining it over the years will take commitment.
2. I am interested to learn how your macros look like, though :-)

[margaretselzer]

• Rationale: Both, adding your code to the library and maintaining it over the years will take commitment.

Yes, indeed, I understand. As long as your table structure remains the same, this would not give you lot of headache though. :)

• I am interested to learn how your macros look like, though :-)

I would gladly show it to you, but to share any code here would require that I remove some proprietary stuff first and clean it up to make an "sqlpp-only" version. I wrote a bit more about the ecosystem we have built around sqlpp11 that relies on these macros in #10, maybe that helps a bit. At this point though it feels like that a chat would be more effective to decide where you want to go with this.

[margaretselzer]

I wonder if there is a way to get the SQL column definition into the field definition? e.g.

Now I can confirm with absolute certainty: yes, it can be done. E.g:

#define _TblProducts_field_definitions_ \
    (id, "INTEGER NOT NULL", sqlpp::unsigned_integral, sqlpp::tag::require_insert) \
    (product, "TEXT NOT NULL", sqlpp::text, sqlpp::tag::require_insert) \
    (price, "REAL NOT NULL CHECK (price > 100)", sqlpp::floating_point, sqlpp::tag::require_insert)

DEFINE_SQLPP_TABLE(
  // Table name without the `Tbl` prefix
  Products,
  // Create query part - indexes and constraints
  R"(
    PRIMARY KEY(id)
  )");

Which then gives me a createDbObject() method in the table class that I can call like: TblProducts::createDbObject(db); and my table is now created in the database. For views the field DDL can be left empty as it is not used, but the view itself is created calling the same createDbObject() static method.

I am almost certain that the same approach works for sqlpp23 as well. I'll get back on that when I managed to make it work.

[rbock]

Thanks for the update! This looks about as concise as possible :-)