PW Admins - request for comment

[Grumalg]

While the following is kind of a wish list item, since I'm asking PW admins for opinions I didn't want to clutter the wish list with responses. I tend to be interested in things that reduce a administrator's workload and provide better overall integration for running a PW. Since I don't actually operate a PW, I'm looking for feedback to see if I'm way off base here, or these ideas have the value to others I think they would.

One of my wish list post requests was the ability to fire a server reset and during the reset run a batch file where you can do DB backup/maintenance/repair and any other file backups such as bic's in an automated fashion. While I haven't tested this (yet), I thank Papillon very much for giving us this capability in nwnx4.

Another of my wish list requests is the ability to shut down a module from NWScript and not have nwnx restart it. My thinking here is that the more dependant a PW is on the DB, the more likely you wouldn't want it to continue running and letting player state get out of sync with DB state if the DB engine fails for some reason. Take for example persistant banking, if a player makes a deposit while the DB engine is down you'd get howls of anguish later. Almost any persistance subsystem has the potiential for headaches trying to fix things after the fact and digging though DB tables trying to correct things surely wouldn't be my idea of a fun time.

What I didn't talk about in my earlier wish list is the other side of this, detecting the problem to begin with. While it is possible to issue DB calls and infer some problems from the result, the overhead of the extra DB calls is undesireable.

My suggested solution is to give SQLExecDirect() a return value. Note that this approach doesn't break any existing code, it only adds new capabilities. Admittedly, doing this will add some overhead to SQLExecDirect(). However since the call already has the large DB connection delay in it, the addition overhead should be so small compared to the DB delays it shouldn't be signifigant.

As to what I think the return value should look like, if everything worked ok, I'd return '0'. If the DB engine reports an error, the error number would be returned as a positive integer. If the DB plugin detected an internal error such as loss of connection to DB engine, it would return a negative integer. The point of useing negative number for plugin internal errors is to prevent collision with DB error numbers. While MySQL errors start at 1000 which would seem to leave room, other DB engines are likely not to use the same error number ranges.

The most important single error return to me is detecting loss of DB engine in the plugin. But, a lot of interesting doors open up if you have DB engine error numbers. True, one would have to be crazy to try and handle every possible DB error in NWScript. However some of these errors would actually allow you do automated fixing of a database problem if the engine is still running instead of just shutting down. Any decent DB engine possesses SQL commands to handle stuff like this. Consider the possibilities of responding to certain error numbers with the following (in MySQL terms).

CHECK TABLE <table name or table list>
REPAIR TABLE <table name or table list>
BACKUP TABLE <table name or table list> TO <path to file(s)>
RESTORE TABLE <table name or table list> FROM <path to file(s)>

Now it's true you can only use some commands with a MySQL localhost account and the right privleges, which means if the DB engine is on a seperate machine your out of luck, but if at least one instance of NWServer shares the same machine as the DB engine it can work. Once stored procedures work, it would be possible to write a proc that handles all of this repairing or restoring things in a single call.

So, I'm curious, would you experienced PW Admins out there value this kind of stuff? Or is it more trouble than it's worth to do such things?

--- Grumalg ---

[Urlord]

I think the idea has merrit. I would like to see something that would do the following...

We only allow one charactater at a time per CDKey. So if you get tired of your character or your existing one dies, you may want to retire it to start a new one. When a character is retired, I have some clean-up scripts that get rid of all data pertaining to the retired character except a single row in the pc table.

When stored procs are working correctly, I want to move all my clean up scripts to a stored proc called RetirePC(oPC);

What I would like to see as far as additional capability is the ability for NWNx4 to fire off a batch or cmd file that moves the bic file from the server vault to a retired_pc folder with the new file name PlayerName_CharacterName.bic. Since this is the only file in the player's directory in the server vault, it should delete the directory incase the player does not create a new character.

All this could be great if fired off by the following NWScriprt command:

ExecuteScript("retire_pc", oPC);
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri

[FunkySwerve]

Urlord - you can do that with the letoscript plugin's system command.
Funky

[Papillon]

Making SQLExeDirect return a proper result code is surely a good idea.

If you want to check the DB connection, a SELECT 1 would do the trick for now.
_________________
Papillon

[Grumalg]

Nod that works....

What I used was SELECT GETVERSION().

Glad you like the idea though

--- Grumalg ---

[Grinning Fool]

If possible, I would like to see SQLGetLastError or SQLGetLastMessage. This time around, I'm using InnoDB tables and taking advantage of foreign key support -- so in some places, it will be important to know the reason for failure.
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[virusman]

This will be added in the next version of XP-MySQL

[Gryphyn]

This is already available in the SQLServer plugin(s).

OS commands can be run via a special stored_proc (xp_cmdshell)
The log contains full ODBC diagnostics for every SQL command execution.

The NWNX2 (NWN1) version has extended functionality to allow parameter binding, multiple result sets, and SCO/RCO is now integral to the NWNX/NWScript interface (no need to treat game objects as a 'special' case)

Cheers
Gryphyn

[virusman]

New versions of XP MySQL and SQLite are available:
http://data.virusman.ru/nwn/nwnx4/xp_mysql-1.10-dev49.rar
http://data.virusman.ru/nwn/nwnx4/xp_sqlite-1.10-dev49.rar
Updated NWScript library (nwnx_sql.nss):
http://data.virusman.ru/nwn/nwnx4/nwnx_sql.erf-1.10-dev49.rar