Achievement System - Reset?

[AlpacaOverlord]

Is it possible to have a server-wide reset of certain achievements on a pre-determined time?

I have the knowledge to create the base of the script, however I am unsure on how to implement a server-wide reset of let's say Achievement ID 100. For instance At OnHour0000, Achievement ID is reset for everyone both online and offline players, I know it is possible, however I am unsure how to proceed forward. I just need the snippet for the exact reset procedure, not the whole script. I would be able to finish the script with this alone, thank you and I greatly appreciate any help or tip.

[Tokei]

Heya,

There are multiple approaches for this issue. The first one is "lazy" but it is usually good enough for most people and it is much simpler. You run a script on all players to remove their achievement and then you run a SQL command to remove all achievements. So something along these lines:

OnClock0000:	// At midnight, everyday
	donpcevent strnpcinfo(0) + "::OnResetAchievement";
	end;
OnResetAchievement:
	donpcevent strnpcinfo(0) + "::OnResetAchievementSub";
	query_sql("DELETE FROM `achievement` WHERE `id` = 100");
	end;
OnResetAchievementSub:
	addrid 0;
	achievementremove 100;
	end;

The issue with the above is that the query is ran on the map-server and therefore will lag you depending on the size of your achievement table (the same goes for any query ran on the map-server). This solution is also not "atomic" and can fail in some situations where a player is logging on while the script is being ran, and the char-server has already sent the achievement data and hasn't been received by the player yet. The chances of the last scenario happening are very low though.

An alternative would be to keep the achievements in the database, but only delete them when the player logs on. You would still have to delete them on the online players though. So something like this:

OnClock0000:
	donpcevent strnpcinfo(0) + "::OnResetAchievement";
	$ach_100_start = gettimetick(2);
	end;
OnPCLoginEvent:
	.@aid = 100;
	.@res = achievementinfo(.@aid, ACHIEVEINFO_COMPLETEDATE);
	
	if (.@res > 0 && .@res < $ach_100_start) {
		achievementremove 100;
	}
	
	end;
OnResetAchievement:
	addrid 0;
	achievementremove 100;
	end;

The above works relatively well. It doesn't have concurrency issues, it won't lag your server either. The downside there would be that the achievements would still exist in your sql table. Also, both of the above need to attach a script to the player, which will cause issues if a player is already talking to a NPC (it will terminate the previously ran script).

The proper solution would be to run the SQL query on the char-server instead using an inter-server packet (or using a SQL thread, if that PR is merged). Then you would run a custom script command that iterates through online players and removes their achievement with achievement_remove without ever attaching a script to them. The last solution is the best and will work in all scenarios with no in-game lag. Though it is somewhat annoying to code I suppose.

(Those scripts were not tested, so you may have to fix the errors yourself.)

Edited March 2, 2021 by Tokei

[AlpacaOverlord]

4 hours ago, Tokei said:

Heya,

There are multiple approaches for this issue. The first one is "lazy" but it is usually good enough for most people and it is much simpler. You run a script on all players to remove their achievement and then you run a SQL command to remove all achievements. So something along these lines:

OnClock0000:	// At midnight, everyday
	donpcevent strnpcinfo(0) + "::OnResetAchievement";
	end;
OnResetAchievement:
	donpcevent strnpcinfo(0) + "::OnResetAchievementSub";
	query_sql("DELETE FROM `achievement` WHERE `id` = 100");
	end;
OnResetAchievementSub:
	addrid 0;
	achievementremove 100;
	end;

The issue with the above is that the query is ran on the map-server and therefore will lag you depending on the size of your achievement table (the same goes for any query ran on the map-server). This solution is also not "atomic" and can fail in some situations where a player is logging on while the script is being ran, and the char-server has already sent the achievement data and hasn't been received by the player yet. The chances of the last scenario happening are very low though.

An alternative would be to keep the achievements in the database, but only delete them when the player logs on. You would still have to delete them on the online players though. So something like this:

OnClock0000:
	donpcevent strnpcinfo(0) + "::OnResetAchievement";
	$ach_100_start = gettimetick(2);
	end;
OnPCLoginEvent:
	.@aid = 100;
	.@res = achievementinfo(.@aid, ACHIEVEINFO_COMPLETEDATE);
	
	if (.@res > 0 && .@res < $ach_100_start) {
		achievementremove 100;
	}
	
	end;
OnResetAchievement:
	addrid 0;
	achievementremove 100;
	end;

The above works relatively well. It doesn't have concurrency issues, it won't lag your server either. The downside there would be that the achievements would still exist in your sql table. Also, both of the above need to attach a script to the player, which will cause issues if a player is already talking to a NPC (it will terminate the previously ran script).

The proper solution would be to run the SQL query on the char-server instead using an inter-server packet (or using a SQL thread, if that PR is merged). Then you would run a custom script command that iterates through online players and removes their achievement with achievement_remove without ever attaching a script to them. The last solution is the best and will work in all scenarios with no in-game lag. Though it is somewhat annoying to code I suppose.

(Those scripts were not tested, so you may have to fix the errors yourself.)

Thanks for these tips! I will probably try out the second iteration and see what happens. As for the third one you presented I am quite unfamiliar with it, so I will try to look around and see what I can get, this has been very helpful.