Introduction To Databases
By popular (and numerous) request, a short introduction to databases
Note: This tutorial was written from home in the author's spare time. As such the example is using code that is perhaps not as optimal/clean as it should be, but it should help someone who is fairly new to scripting to understand how to use the SoU/1.30 database commands.
Intro
This is a short workshop on how to use the new database related commands to add features to your module that make use of persistant data storage.
These scripts will show you:
- How to keep track of a players health status and how to use it to prevent a player from logging out to avoid death penalties.
- How to find out if a player visits your module for the first time or has been on before.
- How to create a "SaveSpot" placeable that a player can use to set the location where he respawns to.
- How to create a scroll that summons a portal that leads to the players last SaveSpot.
- How to keep simple statistics like a player's death count.
- How to reset your database.
For each of these purposes there are probably way better and more complex scripts on the Vault, so keep in mind this is only to get you started with databases.
Other Uses
The database was used in the official SoU campaign to transfer information between the chapters which are seperate modules.
Such information could include:
- Flags to show if certain quests have been done.
- Like / Dislike information for npcs.
- Which henchmen were chosen.
- Etc....
1. The Database Include File
This include file holds all of the important calls to the database system, so they are all conveniently accessible in one place.
Save this file as 'gz_inc_db.nss' in your module
// The name of your database
const string GZ_CAMPAIGN_DATABASE = "MY_DB";
// possible values for the GTSavePlayerLocation() function
const string GT_DB_L_PLAYER_DEATH = "GZ_PLAYER_L_LAST_DEATH"; // last place of death
const string GT_DB_L_PLAYER_BIND = "GZ_PLAYERL_L_LAST_BIND"; // last savepoint used
const string GT_DB_L_PLAYER_START = "GZ_PLAYER_L_LAST_START"; // start location
// C O N F I G U R A T I O N
// if set to TRUE, the player can only save his last save location at savepoints
const int GT_DB_USESAVEPOINTS = TRUE;
// cost in GP to use a savepoint
const int GT_DB_SAVEPOINT_COST = 10;
// toggle debug messages
const int GT_DB_DEBUGMODE = TRUE;
// Message strings
const string GZ_DB_S_SAVEPOINT_USED = "This place is now your SavePoint where you return after dying";
const string GZ_DB_S_SAVEPOINT_OFF = "SavePoints are not activated in this world";
const string GZ_DB_S_SAVEPOINT_NOGOLD = "You can not afford to use this SavePoint";
const string GZ_DB_S_PORTALSCROLL_FAIL = "An invisible force prevents you from entering the magical portal";
const string GZ_DB_S_FORCEDEATH = "Forced Death - Last time you left this world you were dead.";
// Object Tags
const string GZ_DB_O_PORTAL = "gz_o_portaldoor";
// I N T E R F A C E
// returns TRUE if a location is valid
int GTGetIsLocationValid(location lLoc);
// returns a unique string for each PC
//string GTGetUniqueCharID(object oPC); //Not used anymore
// saves the current status of the player (hp, location)
void GTSavePlayerStatus(object oPC);
// returns the number of time a player has died
int GTGetPlayerDeathCount(object oPC);
// saves the location of the player into the slot defined in sLocationID
// for easy tracking, use the GT_DB_L_* constants defined in this library for the sLocationID
void GTSavePlayerLocation(object oPC, string sLocationID);
// returns a persistant location stored with GTSavePlayerLocation on the player
// use with the GT_DB_L_* constants to prevent typos errors
location GTLoadPlayerLocation(object oPC, string sLocationID);
// increase the death count of a player by one
void GTIncreasePlayerDeathCount(object oPC);
// reset the database
void GTResetDatabase();
// I M P L E M E N T A T I O N
int GTGetIsLocationValid(location lLoc)
{
return (GetAreaFromLocation(lLoc)!= OBJECT_INVALID);
}
string GTGetUniquePlayerID(object oPC)
{
return GetPCPublicCDKey(oPC) + GetName(oPC);
}
void GTIncreasePlayerDeathCount(object oPC)
{
// Increment death count only if death was not forced by OnEnter Event
if (GetLocalInt(oPC, "GZ_DB_DIE_FORCED"))
{
DeleteLocalInt(oPC, "GZ_DB_DIE_FORCED");
return;
}
SetLocalInt(oPC, "GZ_DB_DIE_FORCED",TRUE);
SetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_DEATHCOUNT",GTGetPlayerDeathCount(oPC)+1,oPC);
}
int GTGetPlayerDeathCount(object oPC)
{
return GetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_DEATHCOUNT",oPC);
}
void GTSavePlayerLocation(object oPC, string sLocationID)
{
if (GTGetIsLocationValid(GetLocation(oPC)))
{
SetCampaignLocation(GZ_CAMPAIGN_DATABASE, sLocationID , GetLocation(oPC), oPC);
}
}
location GTLoadPlayerLocation(object oPC, string sLocationID)
{
return GetCampaignLocation(GZ_CAMPAIGN_DATABASE, sLocationID, oPC);
}
void GTDebug(object oPC, string sInfo)
{
if (!GT_DB_DEBUGMODE)
{
return;
}
SendMessageToPC(oPC, "**** GZ-DB Debug: " + sInfo);
WriteTimestampedLogEntry( "**** GZ-DB Debug: " + GTGetUniquePlayerID(oPC) + " - " + sInfo);
}
void GTDie(object oPC = OBJECT_SELF)
{
SetLocalInt(oPC, "GZ_DB_DIE_FORCED",TRUE);
effect eDeath = EffectDeath();
ApplyEffectToObject(DURATION_TYPE_INSTANT,eDeath,oPC);
SendMessageToPC(oPC,GZ_DB_S_FORCEDEATH);
}
void GTSavePlayerStatus(object oPC)
{
// Save current HP
SetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_CUR_HP",GetCurrentHitPoints(oPC), oPC);
// Save current state (dead/alive)
SetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_IS_DEAD",GetIsDead(oPC),oPC);
// SendMessageToPC(oPC
GTDebug(oPC, "Status Saved");
}
void GTRestorePlayerStatus(object oPC)
{
location lLoc;
int bDead = GetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_IS_DEAD",oPC);
if (GT_DB_USESAVEPOINTS)
{
// load save point
lLoc =GTLoadPlayerLocation(oPC, GT_DB_L_PLAYER_BIND);
}
else
{
//load last save point
lLoc =GTLoadPlayerLocation(oPC,GT_DB_L_PLAYER_START );
}
if (GTGetIsLocationValid(lLoc))
{
AssignCommand(oPC, JumpToLocation (lLoc));
}
// if player was dead on last save, revert him to that state
if (bDead)
{
AssignCommand(oPC,GTDie());
}
else
{
// if player was damage last save, lower his hitpoints
int nHP = GetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_CUR_HP", oPC);
int nHPDelta= GetCurrentHitPoints(oPC)- nHP;
if (nHPDelta>0)
{
effect eDamage = EffectDamage(nHPDelta , DAMAGE_TYPE_MAGICAL,DAMAGE_POWER_PLUS_FIVE);
eDamage = SupernaturalEffect(eDamage);
ApplyEffectToObject (DURATION_TYPE_INSTANT, eDamage,oPC);
}
}
}
void GTResetDatabase()
{
DestroyCampaignDatabase(GZ_CAMPAIGN_DATABASE);
}
2. The Module Events
Copy the following files into your module and name them accordingly.
This list shows how to set up the module events:
| Event | Script (see below) |
|---|---|
|
OnActivateItem |
_mod_onactivate.nss |
|
OnClientEnter |
_mod_onactivate.nss |
|
OnPlayerDeath |
_mod_ondeath.nss |
|
OnPlayerRespawn |
_mod_onrespawn.nss |
|
OnPlayerRest |
_mod_onrest.nss |
_mod_onactivate.nss
void main()
{
object oItem = GetItemActivated();
ExecuteScript (GetTag(oItem), GetModule());
}
_mod_onenter.nss
#include "gz_inc_db"
void main()
{
object oPC = GetEnteringObject();
if (GetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_WAS_HERE",oPC) == 0)
{
// this code is run if the pc was never in this module before
SendMessageToPC(oPC,"Welcome newbie!");
// save that the player was already once in this module
SetCampaignInt(GZ_CAMPAIGN_DATABASE,"GZ_PLAYER_WAS_HERE", TRUE,oPC);
// save PCs start location for later use
GTSavePlayerLocation(oPC,GT_DB_L_PLAYER_START);
}
else
{
// this code is run if the player was already in this module once
GTRestorePlayerStatus(oPC);
SendMessageToPC(oPC,"Welcome back!");
}
}
_mod_ondeath.nss
#include "gz_inc_db"
void main()
{
object oPlayer = GetLastPlayerDied();
string sArea = GetTag(GetArea(oPlayer));
// * make friendly to Each of the 3 common factions
AssignCommand(oPlayer, ClearAllActions());
// * Note: waiting for Sophia to make SetStandardFactionReptuation to clear all personal reputation
if (GetStandardFactionReputation(STANDARD_FACTION_COMMONER, oPlayer) <= 10)
{ SetLocalInt(oPlayer, "NW_G_Playerhasbeenbad", 10); // * Player bad
SetStandardFactionReputation(STANDARD_FACTION_COMMONER, 80, oPlayer);
}
if (GetStandardFactionReputation(STANDARD_FACTION_MERCHANT, oPlayer) <= 10)
{ SetLocalInt(oPlayer, "NW_G_Playerhasbeenbad", 10); // * Player bad
SetStandardFactionReputation(STANDARD_FACTION_MERCHANT, 80, oPlayer);
}
if (GetStandardFactionReputation(STANDARD_FACTION_DEFENDER, oPlayer) <= 10)
{ SetLocalInt(oPlayer, "NW_G_Playerhasbeenbad", 10); // * Player bad
SetStandardFactionReputation(STANDARD_FACTION_DEFENDER, 80, oPlayer);
}
GTSavePlayerLocation(oPlayer,GT_DB_L_PLAYER_DEATH);
GTIncreasePlayerDeathCount(oPlayer);
// Save the players status
GTSavePlayerStatus(oPlayer);
DelayCommand(2.5, PopUpGUIPanel(oPlayer,GUI_PANEL_PLAYER_DEATH));
}
_mod_onrespawn.nss
//::///////////////////////////////////////////////
//:: Generic On Pressed Respawn Button
//:: Copyright (c) 2001 Bioware Corp.
//:://////////////////////////////////////////////
/*
// * June 1: moved RestoreEffects into plot include
*/
//:://////////////////////////////////////////////
//:: Created By: Brent
//:: Created On: November
//:://////////////////////////////////////////////
#include "nw_i0_plot"
#include "gz_inc_db"
// * Applies an XP and GP penalty
// * to the player respawning
void ApplyPenalty(object oDead)
{
int nXP = GetXP(oDead);
int nPenalty = 50 * GetHitDice(oDead);
int nHD = GetHitDice(oDead);
// * You can not lose a level with this respawning
int nMin = ((nHD * (nHD - 1)) / 2) * 1000;
int nNewXP = nXP - nPenalty;
if (nNewXP < nMin)
nNewXP = nMin;
SetXP(oDead, nNewXP);
int nGoldToTake = FloatToInt(0.10 * GetGold(oDead));
// * a cap of 10 000gp taken from you
if (nGoldToTake > 10000)
{
nGoldToTake = 10000;
}
AssignCommand(oDead, TakeGoldFromCreature(nGoldToTake, oDead, TRUE));
DelayCommand(4.0, FloatingTextStrRefOnCreature(58299, oDead, FALSE));
DelayCommand(4.8, FloatingTextStrRefOnCreature(58300, oDead, FALSE));
}
void main()
{
object oRespawner = GetLastRespawnButtonPresser();
ApplyEffectToObject(DURATION_TYPE_INSTANT,EffectResurrection(),oRespawner);
ApplyEffectToObject(DURATION_TYPE_INSTANT,EffectHeal(GetMaxHitPoints(oRespawner)), oRespawner);
RemoveEffects(oRespawner);
//* Return PC to temple
// Get last player savepoint location
location lLastSavePoint = GTLoadPlayerLocation(oRespawner,GT_DB_L_PLAYER_BIND);
if (GTGetIsLocationValid(lLastSavePoint) && GT_DB_USESAVEPOINTS)
{
AssignCommand(oRespawner,JumpToLocation(lLastSavePoint));
}
else
{
// no last savepoint location, try player start location
lLastSavePoint = GTLoadPlayerLocation(oRespawner,GT_DB_L_PLAYER_START);
// jump to start location
if (GTGetIsLocationValid(lLastSavePoint))
{
AssignCommand(oRespawner,JumpToLocation(lLastSavePoint));
}
}
ApplyPenalty(oRespawner);
// save player status (alive again)
DelayCommand(3.0f,GTSavePlayerStatus(oRespawner));
}
_mod_onrest.nss
#include "gz_inc_db"
void main()
{
// used to track a players health every time he rests
object oPC = GetLastPCRested();
if (GetLastRestEventType() == REST_EVENTTYPE_REST_FINISHED || GetLastRestEventType() == REST_EVENTTYPE_REST_CANCELLED)
{
// every time a PC rest ends or is cancelled
GTSavePlayerStatus(oPC);
}
}
The system should already be mostly up and running now. It will track a players health each time he rests/aborts resting and it will remember if he died.
3. Adding SaveSpots
Create these files and store them in your module:
_plc_onused.nss
// this little hack allows to have all scripts associated with an item in one file
void main()
{
// Set script mode to OnUsed
SetLocalInt(OBJECT_SELF,"PLC_SCRIPT_MODE",1);
// execute the on used script for the object
ExecuteScript (GetTag(OBJECT_SELF), OBJECT_SELF);
}
gz_o_savepoint.nss
#include "gz_inc_db"
// minimalistic persistent savepoint script
void main()
{
int nMode = GetLocalInt(OBJECT_SELF,"PLC_SCRIPT_MODE");
DeleteLocalInt(OBJECT_SELF,"PLC_SCRIPT_MODE");
if (nMode == 1)
{
object oPC = GetLastUsedBy();
if (GT_DB_USESAVEPOINTS)
{
if (GT_DB_SAVEPOINT_COST>0)
{
if (GetGold(oPC) < GT_DB_SAVEPOINT_COST)
{
FloatingTextStringOnCreature(GZ_DB_S_SAVEPOINT_NOGOLD,oPC);
return;
}
else
{
// Take Cash
TakeGoldFromCreature(GT_DB_SAVEPOINT_COST,oPC,TRUE);
}
}
// save the current savespot
GTSavePlayerLocation(oPC, GT_DB_L_PLAYER_BIND);
FloatingTextStringOnCreature(GZ_DB_S_SAVEPOINT_USED,oPC);
}
else
{
FloatingTextStringOnCreature(GZ_DB_S_SAVEPOINT_OFF,oPC);
}
}
}
Now create a placeable object that will allow the player to save his respawn spot:
- On the Basic page: Check the Set Plot and Useable flags.
- change the TAG of the object to 'gz_o_savepoint'.
- On the Scripts page: add '_plc_onused' into the OnUsed Event handler.
4. Adding Portal Scrolls
To add a portal scroll that allows you to summon a gate to your last SaveSpot, add this code:
gz_i_portalscrl.nss
#include "gz_inc_db"
// item activation script for portal scroll
void main()
{
object oDoor = CreateObject(OBJECT_TYPE_PLACEABLE, GZ_DB_O_PORTAL,GetItemActivatedTargetLocation(),TRUE);
effect eVis2 = EffectVisualEffect(VFX_DUR_GHOSTLY_VISAGE);
ApplyEffectToObject(DURATION_TYPE_PERMANENT,eVis2, oDoor);
SetLocalObject(oDoor, "TP_OWNER", GetItemActivator());
return;
}
Create a new scroll:
- Set TAG to 'gz_i_portalscrl'.
- Add the Property "Cast Spell: Unique Power" to the scroll.
- Remove any usage restrictions from the scroll.
Now we just need to build our portal:
Add the following script to the module
gz_o_portaldoor.nss
#include "gz_inc_db"
// Portal Door Events
// Return the user to his last SavePoint
void main()
{
int nMode = GetLocalInt(OBJECT_SELF,"PLC_SCRIPT_MODE");
DeleteLocalInt(OBJECT_SELF,"PLC_SCRIPT_MODE");
if (nMode == 1)
{
object oidUser;
object oidDest;
object oOwner = GetLocalObject(OBJECT_SELF, "TP_OWNER");
// d
if (GetName(oOwner) == "" || oOwner == OBJECT_INVALID)// owner no longer online
{
DestroyObject(OBJECT_SELF);
return;
}
int bAllow = FALSE;
// only faction members may enter
if (GetLastUsedBy() != oOwner)
{
object oTest = GetFirstFactionMember(oOwner);
while (oTest != OBJECT_INVALID)
{
if (oTest == GetLastUsedBy())
{
bAllow = TRUE;
break;
}
oTest = GetNextFactionMember(oOwner);
}
}
else
{
bAllow = TRUE;
}
oidUser = GetLastUsedBy();
if (!bAllow)
{
AssignCommand(oidUser,ClearAllActions());
SendMessageToPC(oidUser,GZ_DB_S_PORTALSCROLL_FAIL);
return;
}
if (GetIsOpen(OBJECT_SELF))
{
location lLastSavePoint = GTLoadPlayerLocation(oidUser,GT_DB_L_PLAYER_BIND);
AssignCommand(oidUser,ClearAllActions());
if (GTGetIsLocationValid(lLastSavePoint) && GT_DB_USESAVEPOINTS)
{
AssignCommand(oidUser,JumpToLocation(lLastSavePoint));
}
else
{
// no last savepoint location, try player start location
lLastSavePoint = GTLoadPlayerLocation(oidUser,GT_DB_L_PLAYER_START);
// jump to start location
if (GTGetIsLocationValid(lLastSavePoint))
{
AssignCommand(oidUser,JumpToLocation(lLastSavePoint));
}
}
PlayAnimation(ANIMATION_PLACEABLE_CLOSE);
DestroyObject(OBJECT_SELF,10.0f);
}
else
{
PlayAnimation(ANIMATION_PLACEABLE_OPEN);
}
}
}
- Create a new placeable called 'Doorway' and place it somewhere.
- Change its tag to 'gz_o_portaldoor'.
- Change its resref to 'gz_o_portaldoor' on the Advanced properties tab.
- Change its OnUsed Event script to '_plc_used'.
Now your module should be done. It remembers the players SaveSpot, Hit Points and if they are dead, even if you reboot your server or edit your module.
Every once in a while you may want to restart your campaign, removing all stored information.
You can create a new script file to do this:
resetmod.nss
#include "gz_inc_db"
void main
{
GTResetDatabase()
}
As a dm, you can call this file from your console with the runscript command 'runscript resetmod' to delete all information stored in your database.
What is left
The system is not flawless yet.
- Database access is slower than normal variable access and more disk/cpu intensive. A good way to speed up your scripts is to save certain informations as local variables on the player and every once in a while read them and store them into the database. (I.e. the last save position needs to be read only once from the database per session.)
- Since a player's health is only tracked when he rests/dies/resurrects, you probably want to add some code to periodically save a players status. Unfortunatelly the OnClientExit ScriptHook does not work for this, (the player object does not hold a valid location anymore,) so you need to come up with other solutions (i.e. periodic saves, save triggers in your world, etc).
As a workaround you could provide the player with an item that allows them to save the location themselves, this would be pretty easy:
- create an item and attach a unique power self only to it.
- create a script with the same name as the items tag and write this into it
#include "gz_inc_db"
void main()
{
// save the player location
GTSavePlayerLocation(GetItemActivator(), GT_DB_L_PLAYER_SAVEPOINT);
SendMessageToPC(GetItemActivator(),"Your current location has been saved, you can now log off");
}
Have fun!
author: Georg Zoeller, editors: Grimlar, Mistress, contributor: Ken Cotterill