logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
PW Admins - request for comment

 
Post new topic   Reply to topic    nwnx.org Forum Index -> Development
View previous topic :: View next topic  
Author Message
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Fri Dec 08, 2006 3:49    Post subject: PW Admins - request for comment Reply with quote

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 ---
Back to top
View user's profile Send private message
Urlord



Joined: 17 Nov 2006
Posts: 122

PostPosted: Fri Dec 08, 2006 5:05    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
FunkySwerve



Joined: 02 Jun 2005
Posts: 377

PostPosted: Sat Dec 09, 2006 3:20    Post subject: Reply with quote

Urlord - you can do that with the letoscript plugin's system command.
Funky
Back to top
View user's profile Send private message
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1058
Location: Germany

PostPosted: Sat Dec 09, 2006 12:21    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Grumalg



Joined: 04 Nov 2005
Posts: 70

PostPosted: Sat Dec 09, 2006 13:59    Post subject: Reply with quote

Nod that works....

What I used was SELECT GETVERSION().

Glad you like the idea though Smile

--- Grumalg ---
Back to top
View user's profile Send private message
Grinning Fool



Joined: 12 Feb 2005
Posts: 264

PostPosted: Sat Dec 09, 2006 19:19    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
virusman



Joined: 30 Jan 2005
Posts: 994
Location: Russia

PostPosted: Sat Nov 15, 2008 14:23    Post subject: Reply with quote

This will be added in the next version of XP-MySQL
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Sun Nov 16, 2008 0:33    Post subject: Reply with quote

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

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
Back to top
View user's profile Send private message
virusman



Joined: 30 Jan 2005
Posts: 994
Location: Russia

PostPosted: Sun Nov 16, 2008 16:10    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Development All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
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