SQL Refactor

[zach2good]

Live vs Static data
We have been talking about splitting how we organise our SQL files.

We have a large amount of data, which is entirely static and read in on startup.
We have a small amount of tables which house live data and things used at runtime.

We could be storing them away from each other:

root/
    sql/
        backups/
            ...
        dynamic/
            chars.sql -- etc.
            ...
        static/
            mob_spawn_points.sql --etc.
            ...

In addition to this, we could revert the static tables to be MyISAM - for the superior mass-read speed. We don't care at all about transactions or write speed.

SQL Helpers
We use a lot of raw queries, where people add lines to the SELECT for the things they need, and results are pulled out via raw indexes. People come in and add things to the middle and the indexes get jumbled up: Teo has just had some bad times dealing with this. At the very least, we shouldn't be handling this index counting by hand! We could have a helper in the form of:

QueryBuilder qb(QUERY_SELECT);
qb.addSELECTField("mobpools.poolid");
...
qb.addFROM("spell_list, mob_pools, mob_family_system");
qb.addWHERE("spell_list.spellid = %u AND (spell_list.spellid+5000) = mob_pools.poolid AND mob_pools.familyid = mob_family_system.familyid", TrustID);
qb.addORDERBY("spell_list.spellid")

if (qb.execute())
{
    // Extracts into a map behind the scenes so you can extract using your original SELECT key
    auto poolid = qb.getResult<uint32>("mobpools.poolid");
}

Realistically, there are probably very lightweight helper libs out there we could be using.

I don't want to spend too much time digging into this (the helper would be fine), because of my grand char server and caching plans.

[TeoTwawki]

Quick recap from private discussion:

MyISAM = table level locking
InnoDB = row level locking

Non-transactional = write options need to be rolled back manually.
Transactional = write options roll back automatically if they don’t complete.

Feature	MyISAM	InnoDB
Type	Non-Transactional	Transactional
Locking	Table locking	Row-level locking
Foreign keys	No	Yes
Design Intent	SPEED!	data manipulation performance

[RAIST5150]

Nice. I like this. No code guru by any stretch of the imagination (was mostly on the implementations end of things back in the day).

But this reminds me of how our System2000 project manager had them set things up in LANSA and such on the IBM boxes back in the day. The counties had soooooo much data we never used on the webapps, so she created custom "views" they could fetch by simple names like realpropdata or assessordata for extracting to the file/field format they needed to parse through the java/mysql scripts. Had a profound impact for keeping things in synch (we never touched their data in real time, it got replicated and updated in scheduled batches).

Can assert it made things things go WAY smoother for the routine fetches.