sqlpp11 code generator can be more powerful

[zhengminlai]

Hi rbock, I have used sqlpp11 for about one year. Thanks to the powerful and amazing library, it does improve my coding efficiency and code safety a lot, running stable in production.

Nevertheless, I want to give some advice on the code generator to make it more powerful.

Based on my project, I have optimized the sqlpp code generator in the following ways:

1. Generate entity struct for each sql file (or each table), with ToDebugString() method and GetEntities(sqlpp res) method provided:

struct TTestTableEntity {
 public:
  TTestTableEntity () { Reset(); }

  TTestTableEntity (const TTestTableEntity &entity) {
    id = entity.id;
    name = entity.name;
    db_time = entity.db_time;
  }

  ~TTestTableEntity () {}

  void Reset() {
    id = 0;
    name = "";
    db_time = "";
  }

  template <typename Result>
  static std::vector<TTestTableEntity> GetEntities(Result &&res) {
    std::vector<TTestTableEntity> ret;
    for (const auto &row : res) {
      TTestTableEntity entity;
      entity.id = static_cast<int>(row.id);
      entity.name = row.name;
      entity.db_time = sqlpp::DateTimeToString(row.db_time);

      ret.push_back(entity);
    }
    return ret;
  }

  std::string ToDebugString() const {
    std::stringstream ss;
    ss << "\n{\n"
       << "\t id: " << id << ",\n"
       << "\t name: " << group << ",\n"
       << "\t db_time: " << db_time << "\n}\n";
    return ss.str();
  }

 public:
  int id;                            // id
  std::string name;         // name
  std::string db_time;      // selected db time
};

2. Support parsing sharding db/tables, such as test_db_xx.t_test_table_y (where xx in [00, 99], y in [0, 9]), or test_db_yyyymm.t_test_table_dd (where yyyymm means year-month, dd means day)

Sharding db/tables has nothing different in fields but the suffix of the db/table names between two tables. Therefore, we can generate code overwriting the serializer of the table name:

namespace orm {
namespace test_db_suffix {
extern thread_local std::string kSuffix;
}  // namespace test_db_suffix

namespace t_test_table_suffix {
extern thread_local std::string kSuffix;
}  // namespace t_test_table_suffix
}  // namespace orm

namespace sqlpp {
template <typename Context>
struct serializer_t<Context, orm::TTestTableX> {
  using _serialize_check = consistent_t;
  using T = orm::TTestTableX;

  static Context& _(const T& /*unused*/, Context& context) {
    context << "test_db_" << context.escape(orm::test_db_suffix::kSuffix) << ".t_test_table_"
            << context.escape(orm::t_test_table_suffix::kSuffix);
    return context;
  }
};
}  // namespace sqlpp

users can set orm::test_db_suffix::kSuffix and orm::t_test_table_suffix::kSuffix to specify the db suffix and table suffix.

3. Generate db utils class including method:
   InsertRcd, QueryRcdByWhereCond, QueryRcdListByWhereCond, QueryRcdListByWhereCondWithPaging, QueryRcdTotalCntByWhereCond,

such as InsertRcd:

  static int InsertRcd(sqlpp::mysql::connection *db_conn, const TTestTableEntity &t_test_table_entity,
                       const std::string &db_suffix, const std::string &tb_suffix) {
    // specify db suffix
    orm::test_db_suffix::kSuffix = db_suffix;
    // specify table suffix
    orm::t_test_table_suffix::kSuffix = tb_suffix;
 
    auto sql = sqlpp::insert_into(orm::kTTestTable)
                   .set(orm::kTTestTable.id = t_test_table_entity.id,
                        orm::kTCouponFlow.name= t_test_table_entity.name);
    // execute sql
    ...
    return 0;
  }

After those code generated, the coding efficiency can be greately improved, I hope the features can be supported in the code generator to help more developers, especially the feature of supporting parsing sharding dbs/tables.

Best Regards.

[rbock]

Hi zhengminlai, Thanks for sharing. I am happy to learn that you enjoy the library, and the extensions to the code generator sound very interesting.

2. Would the sharding be generated for every table? Or is this "on demand"?
3. I am not sure what the *Rcd functions would be used for?

Thanks,
Roland

[zhengminlai]

2. The sharding db/tb generator is invoked by the create table sql such as ''' create table test_db_$.t_test_table_$ (…)''', we can regard the $ as a suffix remark, then the code generator can know that this is a sharding db/tb so that it can generate corresponding code for this table.

3. The *Rcd function for each table can greatly improve the coding efficiency, such as InsertRcd method: users just need to set filed values for the entity structure and invoke the InsertRcd method to insert a record into db, they don't need to write sqlpp insert code by hand anymore. As for QueryRcdByWhereCond method, users just need to write where condition and pass it into this method to get the queried result entity returned by the method. It's much more straightforward to access field of an entity structure than that of a sqlpp result set.

[ljluestc]

DB utils class: Including utility methods like InsertRcd(), QueryRcdByWhereCond(), etc., can certainly simplify database operations by providing a higher-level interface for common tasks. These utility methods can encapsulate complex SQL queries and make the code more readable and maintainable. However, it's crucial to handle security and parameterization properly to prevent SQL injection vulnerabilities.