Avlis Persistence System

Table of Contents:

I. Introduction - What does APS do?
II. Installing and updating APS
III. Setting up a database
IV. Customization
V. Troubleshooting and FAQ

I. Introduction - What does APS/NWNX_ODBC do?

APS is a set of scripts written for Neverwinter Nights that work with NWNX2 to produce reliable persistence in a module. At the heart of the APS is an include file that lists a number of custom made functions for governing persistence. These functions can be used as is in your module or changed to suit your needs.

Whenever a script wants to generate a SQL query, it calls our APS functions and the Extender pulls the query out of the memory of the NWN server. It then passes it to the database, and writes the result of the query back into the memory of the server. The database has been tested with Microsoft Access and MySQL so far. Conceivably, any database with a decent ODBC driver will work. Also, because of the nature of ODBC logistics, multiple servers can be linked up to one database file to produce reliable cross-server persistence. A variable can be set on one server and checked on another.

We have included a demo module that illustrates how to use APS/NWNX_ODBC and makes creating the database tables easy, and a second module demonstrating how persistent containers could be implemented.

Licence
APS and NWNX2_ODBC are distributed unter the terms of the GNU GENERAL PUBLIC LICENSE included in licence.txt.

II. Installing and updating APS

A. Importing the erf

  1. Place the file "aps2.erf" into the C:\Neverwinternights\NWN\erf directory.
  2. In the toolset, open up the module into which you wish to install the scripts.
  3. Under the File Menu, click Import. A window will pop up.
  4. Make sure the contents of the C:\Neverwinternights\NWN\erf directory are showing in the window
  5. Select "aps2.erf" from the list
  6. Click Import and ignore the message about the missing resource (click Yes).
The following scripts should now be imported: aps_onload, aps_include.

B. Updating from previous versions

C. Using the persistence functions

  1. After installing according to the instructions above, go to Module Properties under the Edit menu.
  2. Select aps_onload for your module OnModuleLoad event.
      OR
    Open aps_onload in the script editor and paste the contents of it into your pre-existing module's OnModuleLoad script. We only recommend doing this if you are familiar with NWScript.
The functions below are now implemented. Here is a lexicon containing information on their purpose and use:

void SQLInit()

Setup placeholders for ODBC requests and responses. This functions reserves memory APS and NWNX use for communication. Call this function once in the module load event.

SetPersistentString(object oObject, string sVarName, string sValue, int iExpiration=0, string sTable="pwdata")

This sets a persistent string on an object. The object can be any valid object in the game. The command works the same way the usual SetLocalString function works, except you can optionally add a little more information:

SetPersistentInt(object oObject, string sVarName, int iValue, int iExpiration=0, string sTable="pwdata")

This sets a persistent integer value on an object. The object can be any valid object in the game. The command works the same way the usual SetLocalInt function works, except you can optionally add a little more information:

SetPersistentFloat(object oObject, string sVarName, float fValue, int iExpiration=0, string sTable="pwdata")

This sets a persistent float value on an object. The object can be any valid object in the game. The command works the same way the usual SetLocalFloat function works, except you can optionally add a little more information:

SetPersistentLocation(object oObject, string sVarName, location lLocation, int iExpiration=0, string sTable="pwdata")

This sets a persistent location on an object. The object can be any valid object in the game. The command works the same way the usual SetLocalLocation function works, except you can optionally add a little more information:

SetPersistentVector(object oObject, string sVarName, vector vVector, int iExpiration=0, string sTable="pwdata")

This sets a persistent vector on an object. The object can be any valid object in the game. The command works the same way the usual Set local variable functions work, except you can optionally add a little more information:

GetPersistentString(object oObject, string sVarName, string sTable="pwdata")

This function works in the same manner as GetLocalString. It gets the persistent string from object oObject.

GetPersistentInt(object oObject, string sVarName, string sTable="pwdata")

This function works in the same manner as GetLocalInt. It gets the persistent integer value from object oObject.

GetPersistentFloat(object oObject, string sVarName, string sTable="pwdata")

This function works in the same manner as GetLocalFloat. It gets the persistent float value from object oObject.

GetPersistentLocation(object oObject, string sVarName, string sTable="pwdata")

This function works in the same manner as GetLocalLocation. It gets the persistent location value from object oObject.

GetPersistentVector(object oObject, string sVarName, string sTable="pwdata")

This function works in the same manner as the other get local variable functions. It gets the persistent vector value from object oObject.

void DeletePersistentVariable(object oObject, string sVarName, string sTable="pwdata")

This function deletes a variable from the database.

void SQLExecDirect(string sSQL)

Executes a SQL statement. If the statement returns a result set, you can read the data with the next two functions.

int SQLFetch()

Position cursor on next row of the resultset. Call this function before using SQLGetData().
Returns

int SQLFirstRow() (*deprecated*)

Function is deprecated but still there for backward compability. Simply calls SQLFetch().

int SQLNextRow() (*deprecated*)

Function is deprecated but still there for backward compability. Simply calls SQLFetch().

string SQLGetData(int iCol)

Return value of column iCol in the current row of result set sResultSetName.

Comments

III. Setting up a database

These are basic instructions on how to setup a database for APS/NWNX.

A. For Access

  1. Create an ODBC connection
    This NWNX module tries to connect to a so called ODBC DSN with the name "nwn" on startup. Here is how to create this connection: Go to Windows control panel, choose Administrative Tools, choose Data Sources (ODBC). In the window that pops up, go to the second tab (System DSN) and click "Add". Choose "Microsoft Access Driver (*.mdb)" from the list and hit "Finish".

    A new window "ODBC Microsoft Access Setup" will appear where you have to make some settings. The data source name has to be "nwn" (without the quotes). The description can contain any text you like. Next, select "Create..." and browse to the location where you want your new database to be stored. Type in a name (e.g. nwn.mdb) and hit OK. The new database has now been created and all settings have been made. Click OK again to close the "ODBC Microsoft Access Setup" window.

  2. Database format
    You now have an empty database. In order to store data in it, you have to create some tables in the database. The included module "aps_demo.mod" makes this easy.

    Start NWNX/APS according to the instructions in Section V.1 (see below) and load "aps_demo" in the server. Next connect to your server with the Neverwinter Nights client. You will see several different signs in front of you (from left-to-right): Create Tables, Store variable in database, and Load variable from database. Each sign performs an action when you click on it:

    • Create tables: Issues a database command that creates tables in an MS Access database
    • Store variable in database: Tries to save a test variable named "demoName" with the value "testValue" in the database
    • Load variable from database: Tries to retrieve the variable "demoName" from the database and prints the results in the server message window.

    Now click every sign once, starting with the one on the left (Create Tables).

    If the last sign sends you the message "Retrieved variable from database: testValue" your setup is ok and you're ready to start using APS. Note: This is the most basic setup. We encourage you to use more sophisticated databases and data structures if you feel confident to do so (see below).

B. For other databases

As there is an almost unlimited amount of different databases out there, we can't give detailed instructions for all of them. If you want to use a database server like MySQL or MSSQL, try to follow the steps described above accordingly:

  • Create a database for NWN.
  • Create a table "pwdata" with the following fields player, tag, name, val, expire, last.

    Here is an example create statement for MySQL (taken from aps_demo.mod, script demo_createtable): 

        SQLExecDirect("CREATE TABLE pwdata (" +
        "player varchar(64) default NULL," +
        "tag varchar(64) default NULL," +
        "name varchar(64) default NULL," +
        "val text," +
        "expire int(11) default NULL," +
        "last timestamp(14) NOT NULL," +
        "KEY idx (player,tag,name)" +
        ")" );
  • Create an ODBC connection to your database and name it "nwn".
  • Let your imagination run wild ;-).

    IV. Customization

    The APS is merely a set of custom functions that were originally used for the Avlis persistent world. The names of the functions and their parameters can be set to whatever is convenient for your module. Below is a hypothetical example of how customization can be done.

    The "PowerG I33t" persistent world maintains their persistence with the Joe Bloe Persistence system (JBPS). In that system, a persistent string is set with the following JBPS function:

    SetStickySring(string sVariableName, string sVariableValue, object oTarget)

    All 5000 scripts in PowerG's elite world are written with the SetStickyString function, and they wish to retrofit their world to use NWNX. They would follow these steps:

    1. Open up the file aps_include in the script editor.
    2. Change the name of the APS function called SetPersistentString to SetStickyString.
    3. Rearrange the parameters of:

      SetPersistentString(object oObject, string sVarName, string sVarValue, int iExpiration, string sTable = "pwdata")
        to:
      SetStickyString(string VarName, string VarValue, object oObject, int iExpiration = 0, string sTable = "pwdata")

    4. Build the module, i.e. recompile all scripts.

    Once the module is restarted, all of PowerG I33t's old persistent string scripts should be running the new persistence system. All it took was changing one script.

    The above example is a simplified one, and the rest of the functions in the JBPS would need to be changed in the same manner. In cases where the function parameters were completely not equivalent to those used by the APS, they may have to be changed throughout every script in the module. Many but not all of the persistence systems out there should be convertible by the above method. We encourage further modification of aps_include to tailor the variable handling to your needs.

    For persistence systems that use tokens, conversion will not be as easy. In these systems, token items are used to represent information on a player character. These tokens are not lost because they are actually in the inventory of the character. Because these systems work with tokens and not actual variables it will be hard to convert them into a database format. The module will most likely have to be completely re-fitted.

    On possible idea to do this scriptomatically would be to write a module OnEnter script that strips the character of their tokens and issues SetPersistent variable commands into the database before destroying them. That would preserve the information that is there, but handling the actual scripts throughout the module will have to be done separately.

    Alternatively, you can have a look at the GetPersistentString() function in "aps_include". There are some comments in this functions that should give you ideas on how to convert persistent data from your current system to APS.

    V. FAQ & Troubleshooting

    Q. What is NWNX?
    A. NWNX2 is a program that loads the NWN server and injects the database functionality into the server. Setting special string variables using NWScript triggers the database requests. The resulting data can be read with the usual string functions. It uses no hakpaks, and the only in-game NWN modification necessary is the addition of either the script for APS, or your own custom modified Structured Query Language (SQL) statements. Whenever a script wants to generate a SQL query, it calls our APS functions and the Extender pulls the query out of the memory of the NWN server. It then passes it to the database, and writes the result of the query back into the memory of the server. The database has been tested with Microsoft Access and MySQL so far. Conceivably, any database with a decent ODBC driver will work. Also, because of the nature of ODBC logistics, multiple servers can be linked up to one database file to produce reliable cross-server persistence. A variable can be set on one server and checked on another.

    Q.What are the system Requirements for NWNX?
    A. NWNX currently works with all Windows operating systems. If your system processor can handle running Neverwinter Nights, it can more than handle NWNX.

    Q. Will there be a LINUX port?
    A. We have a version of NWNX2 for Linux available. Refer to     our sourceforge page.

    Q. Since NWNX is running the server as a child process, does the parent also have crash monitoring so it can auto-reboot the server if it crashes?
    A. If the child process crashes or becomes unresponsive to network traffic, NWNX will reboot it.

    Q. Will I still be able to start my server up with firedaemon(or any other server crash utility)?
    A. NWNX will restart the server if needed. Firedaemon may possibly be able to restart NWNX instead of the server, but we have not looked into this at all.

    Q. How does the data get read back into the module? When does this occur?
    A. As soon as NWNX detects that APS has issued a request, it sends that request to the database and encodes the result set into a string. This string is copied into the space that has been reserved for responses and can be parsed by the result set functions in APS.

    Q.You said you suspend the process during reading and writing to the memory of the child process. Any benchmarks availible to how that works in a large persistant worlds?
    A. Achievable performance varies greatly with the used database, the ODBC driver, the network connection to the database server, the CPU NWNX is running on, etc. That said, we ran a small test where APS generated many queries per second and it handled 500 queries/second just fine on a 1GHZ class PC (which was bored doing that, btw.) using a remote MySQL database server.

    Q. How will nwserver react when a read or write commit fails, such as when connection to the database can not be established.
    A. It will continue running but persistent data won't be available. We ran a test where we shut down our database server (MySQL) and restarted it 5 minutes later, all while APS/NWNX was running and issuing queries. APS returned empty sets (i.e. not data) during the down time and came back up seemlessly. If NWNX can't connect to the ODBC datasource on startup, it will notify the user of that fact and terminate itself.

    Q. How are errors and null values handled?
    A. SQL commands resulting in an empty set, an error, or null value all result in an empty string sent to APS.

    Q. Ok, enough techie questions, will I have to know MySQL and all of this database nonsense?
    A. Following the instructions in this document should be enough to set up a server yourself if you are using MS Access or MySQL. However, any modifications beyond the scope of this document may require you to know these programs. Ideally, NWNX is most powerful in the hands of experienced database users.

    Q. How much time does it take to upgrade an existing module to use the system? How about a newly created module, how much 'planning' time would it take?
    A. That depends on how your module looks like. If you already have a fairly large module with a lot of scripts that make use of persistence, we encourage you do write some lines of code that convert the old function calls to the new ones (basically a wrapper around APS). That's what we did for Avlis.

    Q. I do not own Oracle or SqlServer and will not use MySQL so what do you suggest as the DB backend?
    A. MS Access can be used, but we do not recommend it for large persistent worlds. The number of queries in a short amount of time could conceivably corrupt the database after a while. If you are serious about starting a large PW, some kind of decent database backend will be needed, like MySQL, which is free software.

    Q. I am using the HC Rules with their PWDB. If I was to start the world up using this DB, would my players info be lost when I switch to yours?
    A. Not necessarily. Data of most other persistence systems can be preserved and converted to APS, but you'll have to change some lines of code. We don't have a conversion guide right now, so we can only point you to the function GetPersistentString() in aps_include. There's a comment in there that tells you how to read in old data.

    We converted our existing data by using the previous persistent data functions as soon as a database query yielded an empty result, i.e. the value of a variable couldn't be found in the database. We fetched the value using the previous functions and copied this data with APS into the database, so we basically did a "conversion-on-the-fly".

    Q. I've successfully converted my existing data to APS. Do you want to include this info on how I did that in your documentation ?
    A. Absolutely. We really like to know what people are doing with APS/NWNX and if you think other people may benefit from what you found out then don't hesitate to tell us.

    Q. Will you have pre-made stuff put in like banking, saving locations, health status, and housing?
    A. Much of the stuff mentioned in this question is easily scripted with NWScript. Most likely, people in the community will get this stuff to work with APS/NWNX much more quickly than we could release it ourselves. We encourage this, because it is what the community is about afterall. Still, the Avlis Team may release things like this in the future on our own.

    Q. Does the system handle journal entries and explored maps? This has been one of many systems' drawbacks in that journal entries must be coded into the system at design time. At this time it seems no one has figured out how to get this to work.
    A. Journal entries are not inherently saved by this system, no. However, it could easily be scripted to reinstate journal entries upon player login if the NPC conversations are designed this way initially. Map exploration is not covered by this system because there is never a time when the state of the map is converted to a string and sent to the database. Only pieces of information that can be converted to a string with NWScript and sent to the database can be saved with APS/NWNX.

    Q. Could you give an example of how a chest/trap/locked door might be designed to take advantage of your system?
    A. Persistent chests are a good example for this. Create a chest with a unique tag, and make some scripts that take the objects inside the chest and store them as persistent objects on the chest itself. This could be done OnOpen or OnClose depending on how you would want your chest designed. This example would make use of result sets, which is the concept of one database query being answered with multiple pieces of information. Opening the chest would send one "chest opened" query to the database, which would return every object in the chest as an answer. Have a look at the included demo module "pcontainers" where we used a different approach that works fine, too.

    Q. Any chance the source code will be open sourced?
    A. Shortly after the initial release, the complete source code was released as part of the distribution of APS/NWNX2.

    Troubleshooting

    Before doing any trouble shooting, make sure you look at the system requirements and are running a compatible operating system.

    Problem: I start up NWNX and the server doesn't come up.
    Solution:
    Make sure that you have created an ODBC datasource called "nwn".

    Problem: Persistence is not working for me. I installed everything correctly, and I built the module, but there's no persistence.
    Solution:
    Make sure that every script in your module that uses the persistence functions has this line at the very top of each script top:

        #include "aps_include"
    If you found scripts where you forgot to add this line, you must recompile that script after you add it.

    Problem: Persistence is working fine, but only NEW information is being saved. My old information from my old persistence system is not there.
    Solution:
    There is no guarantee that your old information will be salvagable. Token systems are especially subject to complete losses of old information upon conversion. Converting persistent token information to usable database information is beyond the scope of this document. Persistence systems that use script writers and log readers may be salvagable, but we cannot guarantee them either. The best thing to try, if you are using a log reader system, is to make sure you have compiled your latest persistent data script from your old system into your module. You may have to do some fancy custom scripting to develop a program that reads your old values into the new system.

    See the question "I am using the HC Rules with their PWDB..." for a hint on how you can convert your data.