| View previous topic :: View next topic |
| Author |
Message |
white_pawn
Joined: 05 Apr 2010
Posts: 1
|
 Posted: Mon Apr 05, 2010 6:19 Post subject: How to Learn Database Usage for NWN |
 |
|
Hi there everyone. Firstly, it's a fantastic thing to see that so many people are still using NWNx2 and by virtue of that the original NWN game. I think it's a testament to how great the game is that it still has such strong and active communities like this one.
A friend and I are looking to develop a persistent world server for NWN and in the past few weeks having had no scripting or building background I've decided to take the bull by the horns and just get in there and learn. And wow, it might sound matrix like, but what a world there is behind what you see as an end user!
What brought me here is a desire to make my persistent world in the making a little more persistent. I know that I can save respawn points etc using the NWN Database but from everything I have read if you get a truly persistent server with good numbers of players on it there can be issues with lag and so on, and I'd much rather go all out and get the most potential for use and expansion in the first instance, than make do and have to expand later.
From everything I have read it seems as if a MySQL database linked to a persistent Server via NWNx2 with the newest ODCB or however you say it plugin gives you the quickest and widest range of scope that is out there, and allows you to do many things (with smart scripting) that were never intended to be done. What I really want to do is learn how to use this great tool from scratch, as I will have to with all the tools available to me, and probably via trial and error.
The main thing I want to be able to learn how to do at this stage is to make persistent merchant inventories that survive server resets or crashes. As in, if merchant x has potion y, and then player z buys the last one, 5 more don't mysteriously reappear after a server reset, and players can potentially sell merchants their own self made stock for profit, making a much more realistic and viable item crafter driven economy, etc. Is it possible to make persistent merchant inventories in this way using a database? I know I might not be able to do it straight away, and would be really grateful if anyone could link me to resources as a starting point to learning how to utilise databases in my PW, but it would be great if people could let me know if this is possible. |
|
| Back to top |
|
 |
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Mon Apr 05, 2010 22:17 Post subject: |
|
|
I don't see anything in the Lexicon regarding persistent stores, but perhaps there's something in NWNX that lets you do that. I'm not sure.
The first thing you'd want to do with NWNX is almost certainly going to be making use of a database and the nwnx_odbc plugin. A healthy knowledge of SQL is advised - don't worry, it's far more straightforward than most languages so if you can tackle nwscript you can tackle SQL. The other alternative is to use the SetPersistent functions provided by aps_include, but that's not ideal because each variable takes up one row in one big table, whereas a better schema would - for example - have a PC table, where one row would represent each PC, linked to another table with information.
http://pastebin.myrror.net/2631
The above is a far more feature-filled NWNX database library than the default aps_include containing only the useful functions for those who want to make full use of SQL. I'll be talking about this one, not the aps_include library that comes with nwnx_odbc.
As an example, let's create ourselves a table by executing this query just once.
| Code: | | CREATE TABLE pc_data(id INTEGER PRIMARY KEY, name VARCHAR(255), player VARCHAR(255), location VARCHAR(255), bank INTEGER DEFAULT 0) |
This creates a pc_data table with the columns "id", "name", "location" and "bank". Okay, great, so each PC has their location and bank balance saved. Fantastic! How do we achieve such a remarkable feat? Here are some snippets.
| Code: | // To start up a new player oPC:
SQLExecStatement("INSERT INTO pc_data(name, player, location, bank) VALUES(?, ?, ?)", GetName(oPC), GetPCPlayerName(oPC), SQLLocationToString(GetLocation(oPC)));
// To retrieve oPC's bank balance and jump them to their stored location:
SQLExecStatement("SELECT location, bank FROM pc_data WHERE name = ? AND player = ?", GetName(oPC), GetPCPlayerName(oPC));
if (SQLFetch())
{
SetLocalInt(oPC, "BANK", StringToInt(SQLGetData(1)));
AssignCommand(oPC, JumpToLocation(SQLStringToLocation(SQLGetData(2))));
}
// To update oPC's bank balance to nGold:
SQLExecStatement("UPDATE pc_data SET bank = ? WHERE id = ?", IntToString(nGold), GetLocalString(oPC, "ID"));
// To get a list of all player accounts:
SQLExecStatement("SELECT DISTINCT player FROM pc_data");
while (SQLFetch())
{
sPlayer = SQLGetData(1);
// do something
} |
You use the ? placeholders for when you want to add dynamic information, providing that information as extra arguments of SQLExecStatement. This is safe, if there are any special characters they will be removed and replaced automatically. Note that everything needs to be converted into a string. Note also that the third example, we've stored the contents of the 'id' field on the PC in some way: this is very good for performance because the 'id' field is a primary key and is indexed for fast searching.
Now you know the basics, for dynamic item storage I'd use SQLStoreCampaignObject and SQLRetrieveCampaignObject. Here's an example.
| Code: | SQLStoreCampaignObject(SQLPrepareStatement("UPDATE my_object_table SET object = %s WHERE name = ?", GetName(oPC)), oObject);
SQLRetrieveCampaignObject(SQLPrepareStatement("SELECT object FROM my_object_table WHERE name = ?", GetName(oPC)), GetLocation(oPC), oPC); |
SQLPrepareStatement allows you to add dynamic information to your queries. In fact, SQLExecStatement(...) is really identical to SQLExecDirect(SQLPrepareStatement(...)). I'd avoid using SQLExecDirect on its own if possible: it's all too easy to miss quotes, especially in things like player names. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
