Warning: as described here, this is in many, many ways a hack and has lots to improve when it comes to optimization.
What are we up to?
Basically, we want a table that will store key-value pairs. We’re assuming your app will have more than one user, so the actual pairs are:
(item_key, item_value, owner_id, timestamp)
Create the table
CREATE TABLE `kv` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_id` INT(11) DEFAULT NULL, `item_key` varchar(128) DEFAULT NULL, `item_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `timestamp` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
You’ll of course want to adjust this to match your data, if, say, you’re using
VARBINARY ids or some other cool thing. Note that the
item_value column is ready for
U+1F4A9 PILE OF POO. Now, now… no need to thank me.
Inserting pairs into the table
Inserting is fast, since no checks (e.g. there are no
UPSERTs here) have to be made:
INSERT INTO kv VALUES (1340, "theme.backgroundColor", "#451120", 1474569150)
Querying the table
Where we pull the current values for all
This is the ~~wonderfully hacky~~ fun part. We’re going to
LEFT JOIN the table on itself, to pull only the newest
item_value for every requested
So let’s fetch the current pairs for all keys matching
SELECT kv.* FROM kv LEFT JOIN _kv ON ( kv.item_key = _kv.item_key AND kv.timestamp < _kv.timestamp AND kv.owner_id = _kv.owner_id ) WHERE _kv.id IS NULL AND kv.owner_id = 1340 AND kv.item_key LIKE 'theme.%';
The purpose of the
JOIN ON ( ... ) is to join every
(item_key, item_value) pair with the pair it was overridden by (see
kv.timestamp < _kv.timestamp). We later filter out (the
WHERE _kv.id IS NULL part) all but the newest pair, since - being the newest pair - it will not have an overriding pair:
_kv.timestamp will be
NULL (it’s a
LEFT JOIN after all).
Still with us? Great. The
kv.owner_id = _kv.owner_id is to ensure we only join pairs from the same user.
Fetching multiple, explicit keys
Where we fetch
The only part that changes is the
SELECT ... LEFT JOIN ... ON ( ... ) WHERE _kv.timestamp IS NULL AND kv.item_key IN ('theme.fontColor', 'theme.fontSize');
Cleaning up the trail
As time goes on, you’ll probably want to clean up older settings. Throw this on a cron and you’re good.
We’ll look at optimizing this table (indexes, partitioning) in Part Two.
Subscribe via RSS