Raised This Month: $12 Target: $400
 3% 

How would you approach this?


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
sdz
Senior Member
Join Date: Feb 2012
Old 04-22-2018 , 16:23   How would you approach this?
Reply With Quote #1

This is more or less looking for different approaches and philosophies in programming here
How would you approach a system which stores & loads defined unlockables per player?
Assume we're using MySQL as our storage median and SourcePawn as our programming medium.
Examples?
  • Skins
  • Achievements

How I would personally approach this?
MySQL Table schema as so:
Code:
CREATE TABLE IF NOT EXISTS player_achievements (
	steam_id VARCHAR(32) NOT NULL,
	achievement_id INT NOT NULL,
	unlocked BOOLEAN NOT NULL,
	PRIMARY KEY(steam_id, achievement_id)
);
Some mock code as so, leaving out the verbose mundane stuff:
PHP Code:
#include <sourcemod>

//Arbitrary Achievement Goals:
enum e_Achievements
{
    
Achievement_Point 1//Gain one point
    
Achievement_Die//Die once
    
Achievement_Happy//Become happy
}

bool g_Achievements[MAXPLAYERS 1][e_Achievements];

Database g_Database;

public 
void OnPluginStart()
{
    
//Pretend connect database:
    
ConnectDatabaseStuff(); 
}

public 
void OnClientAuthorized(int client)
{
    
//Default to locked:
    
g_Achievements[client][e_Achievements] = false;

    
//Pretend to load preexisting achievments:
    
LoadAchievements(GetClientSerial(client));
}

/* Pretend to run the query:
 * SELECT * FROM player_achievements WHERE steam_id = '%s';
 */
void LoadAchievements(int serial)
{
    
int client GetClientFromSerial(serial);
    
//check stuff bla bla:
    //skip lots of code...
    
int aColuQol;
    
results.FieldNameToNum("achievement_id"aCol);
    
results.FieldNameToNum("unlocked"uCol);

    while(
results.FetchRow())
    {
        
//Get achievement id, "cast" as achievement:
        
e_Achievements achievement view_as<e_Achievements>(results.FetchInt(aCol));

        
//Set unlocked status
        
g_Achievements[client][achievement] = view_as<bool>(results.FetchInt(uCol));
    }
}

//Pretend our player did something to unlock an achievement...
void UnlockAchievement(int client)
{
    if(!
g_Achievements[client][Achievement_Die])
    {
        
g_Achievements[client][Achievement_Die] = true;
    
        
char query[256]; Format(querysizeof(query), "INSERT IGNORE INTO player_achievements (steam_id, achievement_id, unlocked) VALUES ('%s', %i, 1) ON DUPLICATE KEY UPDATE unlocked = 1;"steamidview_as<int>(Achievement_Die));
        
g_Database.Query(PretendQueryHandlerquery);
        
PrintToChat(client"yay you died!!");
    }

Thus leaving us with a simple solution of:
By default (in memory) you do not have X unlocked.
Once you unlock X, it is set to true and inserted into the database.
Y will always be locked until you unlock it just by the default nature of things.
X will be unlocked because you unlocked it and the database will load as such.

Last edited by sdz; 04-22-2018 at 16:26.
sdz is offline
nosoop
Veteran Member
Join Date: Aug 2014
Old 04-23-2018 , 04:48   Re: How would you approach this?
Reply With Quote #2

Here's my current approach to achievements; I wrote my own personal plugins for it.
SQLite, but should be easy to adapt to SQL:

Code:
/* run with sqlite3 -init "configs/sql-init-scripts/sqlite/custom_achievements.sql" "data/sqlite3/custom-achievements.sq3" */

/* list of achievements */
CREATE TABLE achievements (
    achievement_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    displaytype INTEGER, /* now deprecated */
    visible BOOLEAN DEFAULT TRUE, /* determines whether or not it's rendered on an achievement page */
    CONSTRAINT achievement_name UNIQUE(name)
);

/* per-account achievement status */
CREATE TABLE achievement_status (
    achievement_id INTEGER,
    steamid3 INTEGER NOT NULL,
    achieved INTEGER DEFAULT 0, /* should be time achieved */
    metadata TEXT,
    CONSTRAINT achievement_user UNIQUE(achievement_id, steamid3)
);

/* localized achievement name and descriptions for display */
/* also to be used in CallOnCustomAchievementAwarded() to print to chat */
CREATE TABLE achievement_languages (
    achievement_name TEXT NOT NULL,
    language_shortcode TEXT NOT NULL,
    achievement_local_name TEXT NOT NULL,
    achievement_local_description TEXT,
    CONSTRAINT achievement_in_language UNIQUE(achievement_name, language_shortcode)
);

/* automatically create language placeholder entries */
CREATE TRIGGER create_achievement_localizations AFTER INSERT ON achievements BEGIN
    INSERT INTO achievement_languages (achievement_name, language_shortcode,
            achievement_local_name, achievement_local_description) VALUES
            (NEW.name, 'en', NEW.name, NEW.name || '_description');  
END;
One core plugin handles all the database stuff; other plugins "register" achievements by unique name (getting the achievement_id back as its this methodmap value) and use the core plugin natives to grant / update achievement data. It's kind of like client preferences / cookies, with some additional columns.
  • The methodmap has the following natives: register (return the ID of an achievement based on the input name, inserting a row for it if it doesn't exist), award to account, fetch / set info, and reset.
  • Unlocking an achievement records the time it was unlocked (so you can have unlocked date / time). You can "re-lock" achievements by setting the time to 0 (so it's a "falsy" value), and wiping the metadata.
  • Metadata is additional storage for the account / achievement to track long-term achievements. It's up to any plugins / external applications that care to determine how it's parsed, allowing for more complex displays than what Steam offers. Fetching the metadata is done through a threaded query.
  • Localization is input manually and separately, other than providing placeholders for your language(s) via the AFTER INSERT trigger.
  • Localization data is fetched into a KeyValues handle with language shortcodes as sections; achievement announcements can be done by getting each client's language, going into the section (falling back to the server's by default), and pulling the appropriate name.
For items, I'd imagine I'd do something based off of Valve's inventory approach (note: I don't know the internals of Valve's database): I'd use a table for player items (unique IDs per item / inventory space), a table for custom attribute overrides (names, descriptions, skins, etc. based on item unique ID), maybe a table for the schema too.
__________________
I do TF2, TF2 servers, and TF2 plugins.
I don't do DMs over Discord -- PM me on the forums regarding inquiries.
AlliedModders Releases / Github / TF2 Server / Donate (BTC / BCH / coffee)

Last edited by nosoop; 04-23-2018 at 04:59.
nosoop is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 17:49.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode