Data structure for Bit Icons
Thinking out loud. This may not make any sense at all. Especially if you have no idea what a bit icon is.
The bit icon as well as the player authentication is the last bit of code that I need to transfer out my older system. This older system was built on top of a custom CMS that ran on the tornado web framework. I now use the Flask web framework and having to maintain this older code was not ideal.
Problem
How to automatically recycle a bit icon when a player hasn't been active?
- currently just running a cleanup query on start of app
-- Currently, this is executed each time the app starts
UPDATE User SET icon = '' WHERE icon != '' AND (
(score = 0 AND m_date < date('now', '-1 hour')) OR
(score <= 5 AND m_date < date('now', '-10 hour')) OR
(score <= 15 AND m_date < date('now', '-2 days')) OR
(score <= 25 AND m_date < date('now', '-3 days')) OR
(score <= 35 AND m_date < date('now', '-4 days')) OR
(score <= 55 AND m_date < date('now', '-8 days')) OR
(score <= 65 AND m_date < date('now', '-16 days')) OR
(score <= 165 AND m_date < date('now', '-1 months')) OR
(score <= 265 AND m_date < date('now', '-2 months')) OR
(score <= 365 AND m_date < date('now', '-3 months')) OR
(score <= 453 AND m_date < date('now', '-4 months')) OR
(score <= (SELECT score FROM User WHERE icon != '' LIMIT 1 OFFSET 15)
AND m_date < date('now', '-5 months'))
);
quick, simple, does the job. Doesn't scale well. I could just run this process on a more regular basis.
Proposal of a better solution
four tables involved: User, BitAuthor, BitIcon, BitExpiration Create the new tables and drop the icon column from User. One to one relationship of BitIcon to User:id. One to many on BitAuthor to BitIcon:id Easy to just update the expiration in the BitExpiration table instead of the sub select for top 15 high scores.
BitIcon expiration updated each time a user increases their score. The extend time is based on the player's score.
drawback is this will happen often and the query may be too slow. Solution would be to push it to a job queue to be ran later.
Or can just run it for all players every day.
CREATE TABLE BitExpiration (score INTEGER, id INTEGER PRIMARY KEY, extend TEXT);
CREATE TABLE BitAuthor (id INTEGER PRIMARY KEY, name TEXT, artist_document TEXT);
CREATE TABLE BitIcon (id INTEGER PRIMARY KEY, user INTEGER REFERENCES User (id), author INTEGER REFERENCES BitAuthor (id), name TEXT UNIQUE, last_viewed TEXT, expiration TEXT);
-- Set the BitExpiration table data
INSERT INTO BitExpiration (score, id, extend) VALUES (0, 1, '+1 hour');
INSERT INTO BitExpiration (score, id, extend) VALUES (5, 2, '+10 hours');
INSERT INTO BitExpiration (score, id, extend) VALUES (15, 3, '+2 days');
-- ... more inserts ...
INSERT INTO BitExpiration (score, id, extend) VALUES (265, 4, '+2 months');
INSERT INTO BitExpiration (score, id, extend) VALUES (365, 5, '+3 months');
-- Update this each time a player earns a point
UPDATE BitIcon SET
expiration = (
SELECT datetime('now', (
SELECT be.extend FROM BitExpiration AS be
JOIN BitIcon AS b
JOIN User AS u ON u.id = b.user
WHERE u.score > be.score AND u.id = :user
ORDER BY be.score DESC LIMIT 1
)
)
)
WHERE user = :user;
-- The icon name comes from the BitIcon table now and has an expiration date
SELECT u.id, b.name AS icon, u.score, u.m_date, b.expiration
FROM BitIcon AS b
JOIN User AS u ON u.id = b.user
WHERE u.id = :user;
Create a migrate script to populate the new BitIcon table from what is in the file system. The users that have icons will also have expiration timestamp set based on m_date.
Benefits
Now can show an expired label on the icon. Expired icons are free for any other player to claim, but will still be assigned to the original player until then. This is better then just suddenly removing the assigned bit icon.
Going over the details in the SQL statements and tweaking them with various ideas of expiring bit icons quickly for players with less then 50 points.
More tweaks
bit icon selection base on a time track