Migrate database subsystem to ODB

[SantjagoCorkez]

As of https://github.com/EvEmu-Project/evemu_Crucible/pull/177#issuecomment-1015160394 I've taken a look into market's DB code. Well... Current staging has horrific code in some cases

void MarketDB::GetMaterialPrices(std::map< uint16, Market::matlData >& data)
{
    DBQueryResult res;
    DBResultRow row;

    std::map< uint16, Market::matlData >::iterator itr;
    for (itr = data.begin(); itr != data.end(); ++itr) {
        sDatabase.RunQuery(res, "SELECT basePrice FROM invTypes WHERE typeID = %u", itr->first);
        if (res.GetRow(row))
            itr->second.price = (row.GetFloat(0) * 1.05);
    }
}

In this example for each and every of thousands of ItemTypes provided a separate query is issued; moreover they are run one-by-one.

Well, my first point was to just make a special flag-like field in the DB so that one could declare special flags for ItemTypes and then mark them with those flags on purpose. For example, such a flag could be MARKET_ITEM = 1 and hence an SQL would have something like ... WHERE flags & %lu > 0", EVEDB::ItemTypeFlags::MARKET_ITEM);

But guys... We could make it even better with just migrating into an ORM powerful enough to provide us with functionality for writing as custom queries as we need at any time so for example we don't need to pull all and every field from all and every tables involved in a query. I've made some research and seems ODB provides with enough of power for that.

Moreover, with that ORM the server could easily define support for almost every widespread RDBMS like: SQLite3, PostgreSQL, MSSQL, Oracle.

Moreover, it provides with automatic database migration (so once we could drop EVEDBTool for not needing it any more still having all the migration capabilities and what's more important having all the DB fixtures generation automated... well, maybe except DML queries that need to patch some special fields with some other special values, like in 20211011182454-ihub.sql).

For example my imlementation (which looks correct at least syntactically) is like this:

void MarketDB::GetMaterialPrices(std::map<uint16_t, Market::matlData> &data) {
    using std::vector;

    auto conn = EVEDBCore::get().GetConnection();
    odb::transaction t(conn->begin());
    vector<uint16_t> ids;
    for ( auto it: data ) {
        ids.push_back(it.first);
    }

    odb::result<InvType_BasePrice> r(
        conn->query<InvType_BasePrice>(
            odb::query<InvType_BasePrice>::typeID.in_range(ids.begin(), ids.end())
        )
    );
    for ( auto it: r ) {
        auto data_item = data.at(it.typeID);
        data_item.price = it.basePrice * 1.05;
    }
    ids.clear();
    t.commit();
}

Where InvType is a class mapping of invTypes table into C++ class with ODB's rules and InvType_BasePrice is defined as:

#pragma db view object(InvType)
struct InvType_BasePrice {
#pragma db column(InvType::typeID_)
    uint16_t typeID;
#pragma db column(InvType::basePrice_)
    float basePrice;
};

So:

• just defining an ODB's "view" I declare a lightweight object to be retrieved (or written) from/to DB
• this object still is based on a real full-featured object definition
• the ORM itself takes all the routine to mess with parameters mapping onto the query being generated (and seems they use prepared statements for that, so we don't need to worry about SQL injections any more, and the same time, prepared statements' values are transferred onto DB server in binary instead of a vsnprintf'ed query where each and every parameter is translated into its textual representation and transferred in that form just to be translated back by RDBMS for checks, planning and so on...
• it's RDBMS-type agnostic, the only difference is in here:

  void
  EVEDBCore::Initialize(dbtype type,
                    std::string host,
                    std::string user,
                    std::string password,
                    std::string database,
                    bool compress,
                    bool SSL,
                    int16_t port,
                    bool socket,
                    bool reconnect,
                    bool profile) {
  // a very rough code snippet
  if (type == dbtype::PGSQL) {
      pConnection = new odb::pgsql::database(user, password, database, host, port);
  } else {
      pConnection = new odb::mysql::database(user, password, database, host, port);
  }
  }

• it's not covered with my examples and research though there's some caching middleware within ODB itself so it could boost some long-term persistent objects workload if there's a need in that

So. Would you be interested in further research and implementation?

[VizanArkonin]

Not gonna lie - ORM did cross my mind on quite a few occasions. The problem was - as it's often the case with C++ - there were no clear contender to look for, lol. All in all, if it's optimized well enough and it has connections pooling - it'll be great to at least try it out and see how it fits. Luckily, the way DB is made right now, we can start implementing it from a few selected hotspots - there's no need to rework entire bloody system at once :D

[jdhirst]

@SantjagoCorkez

So. Would you be interested in further research and implementation?

Absolutely! Great work! I'd very much like to see your work on the db engine as it would go a long way to aid performance.

As far as caching middleware goes, we currently using a variety of memory objects for this purpose (see SovereigntyDataMgr as an example), however this is not ideal as if we could have a library-based solution to allow us to focus on querying the virtual cache db would save a lot of time and complexity. If this is an option, it would be a game changer as long as it has similar performance to an in-memory object.