First time install of NWNX4 and MySQL

[Lugoun]

I am almost completely inexperienced with server and database administration.

At this time, on my local machine, I have successfully installed MySQL Server and the MySQL Tools, and have successfully run the demo module and verified that the data was persistent (available after a mod/server restart, and located the data in the pwdata table using the MySQL Tools).

Soon I will be working with the server admin to get everything set up on our server (which I understand is one box with several virtual machines), and I have some questions:

1) General advice - Are there any general recommendations/known pitfalls/tips for setting up/structuring/installing MySQL and NWNX on a server for a multi-module PW?

2) DB Management - Are there any suggestions, tips, or advice on management of the persistent data? For example, should I set up multiple tables now to handle major data categories? Exactly what is the use of the "pwobjdata" (may have the name wrong) table if nwnx_sql defaults to "pwdata"?

3) Module Settings - Other than having the include files in the module, are there any other settings that need to be in place to make this system work correctly (like module onload commands etc)?

4) Source/Example Wanted - I am interested in an example that I could use to retrieve persistent player data from the db and set them up as locals on a character when joining the mod for the first time since the last module start. Can someone point me the right direction?

Thanks!
Lugoun
_________________
Lugoun
www.hotta-rpg.org

[Lugoun]

No advice for the newbie?
_________________
Lugoun
www.hotta-rpg.org

[Gryphyn]

How do I put this... RTFM...

[Lugoun]

[Gryphyn]

[Papillon]

[Lugoun]

[Gryphyn]

[Lugoun]

Ok, I mined some of the older forum scripts for info, and found something that appears to do something like what I need. Here it is modified for my purposes:

[Gryphyn]

SQLExecDirect() = Ask the question,
SQLFetch() = Get the next row (if there is one)

calling SQLExecDirect() a second time would have created a new set of data

... you need a bit more information to get data form the 'pwdata' table.
Players usually have more than one character. Have a look at the demo mod and see how it's done.

... but for what you're trying to do you would be better off just using the
GetPersistent...() and SetPersistent...() functions. All the SQL stuff will be done for you.

[Lugoun]

Great, the first part was really helpful.

There are several reasons I wish to loop and find the variables this way, not the least of which is less maintenance (not having to update a script everytime a new variable for player is to be tracked, and the extra coordination that involves when working with a team). Also, many scripts were written with a different system in mind and those involve GetLocal. Im also not interested in tracking those down and editing them if its not necessary. I can think of other applications that this loop could be used for as well.

Anyway, based on the additional information I've updated the code a bit. So, does adding SQLFetch() at the end do the trick? Or, is it just called again in the "while(SQLFeth() == SQL_SUCCESS)" making the added SQLFetch unnecessary? If it looks good either way (with or without the exta SQLFetch), I'll try to test this weekend.

[Gryphyn]

No, you don't want that last SQLFetch()

what happens is, when the while tests for the condition. it does a SQLFetch() to get the next row.

An extra one would have you reading every 2nd row.


Cheers
Gryphyn

[Papillon]

@Lugoun: I might be able to offer a piece of advice.

Start small and simple.

You probably do not have a the complete picture of your world and the corresponding database structure in mind, yet. What you are trying to do sounds like it would actually degrade performance at some point in time:

a) You will have many, many variables that might only be read once in a couple of hours. A player logging in with many variables might therefore make the server hiccup a little. Also, most variables will not be used in a single session and so their space is wasted.

b) You are not going to use the advanced data structures that are possible with SQL, since you only want to optimize the GET/SET persistent functions. Better structures mean much more performance improvement than caching the basic pwdata structure.

I would start with the basic functions in nwnx_sql, only. The database answers quite fast, so you will not notice performance problems if you do not query 100's of variables every second (which you will not do as a sensible programmer). After that, you will probably create specialized tables that optimally reflect the type of data you want to store.
_________________
Papillon

[Lugoun]

Thanks for the advice.

I actually am trying to get things mapped out for the future and its making my head hurt

After consideration this weekend, I did end up scrapping the idea of doing the whole data dump pwdata to locals and will update existing scripts to nwnx_sql as necessary.

For the time being I've settled on three tables, pwdata and two of my own, one for quests and another for faction data (basically duplicates of pwdata).

I will still be using a modified version of the loop above to add quests to the nwn campaign journal after mod restarts. This will be handy because it wont be necessary to modify the script every time a quest is added, and every row for that player should be needed.

Thanks again. I'm sure Ill be back
_________________
Lugoun
www.hotta-rpg.org