Cleaning up SQL Database

[Rami_Ahmed]

Hey!

So we've been using NWNx2 for a while as a lot of you may know. We use NWNx2 to store a dozen things, I prefer NWNx2 over BioWare database. But. When people delete their character, we need 'something' to clean up the database. It's a in-human work to sit and delete everytime someone decides to make a new character, so is there anything, anything at all to check for inactivity for a month or so in the database and then deletes all datas with the character name? Or something like that? Shouldn't be impossible, should it? :/

Thanks for reading this.

[Papillon]

The timestamp column in the pwdata table was made for exactly this reason. There should be a SQL command somewhere on this forum that deletes old data based on that column.
_________________
Papillon

[Rami_Ahmed]

Hi!
I've been searching for the post you mentioned, but can't seem to find it. Could anyone point me in the direction or something?
Furthermore I don't really understand anything of SQLCommands and the like, so an idiot-proof description of how to set something like what I want up, would be ... great.

[Rami_Ahmed]

Argh I've been searching numerous web sites and forums for a solution. Can't find anything. Does anyone have time and patience to help?

[Zunath]

Oh, our project is definitely going to need this as well. Is there anyone out there that can help us two?

[Papillon]

Here is the thread I am talking about:

http://www.nwnx.org/phpBB2/viewtopic.php?t=195

Does that help ?
_________________
Papillon

[Rami_Ahmed]

Hi!

Thank you for the answer and the link. It doesn't really help me though, I can't see what to do with that script? As I said, I don't know SQL commands or scripting or what ever it's called.
An idiot-proof explanation is needed if this it to really help, sorry.

I know how to script in nwn and how to use the aps_include, but that's about that.

[Rami_Ahmed]

~ Bump ~

[Zebranky]

I have a scheduled task (Windows; cronjob for Linux freaks ) that executes a couple SQL statements at 2 AM every day. For example:

delete from pwdata where expire!=0 and datediff(curdate(),last)>expire;

This will work for every table with an expire and last column. The others shouldn't need expiration. Just place a bunch of those lines in a file, changing the table name as you desire, and run it with mysql.

[Rami_Ahmed]

Uhm... maybe I should just give up, I don't even know how to 'run MySQL'. My scripting abilites are only self-taught and only in NWScript..



But thank you very much for answering.

[Zebranky]

Assuming:
1. mysql.exe is in your path (otherwise, find it and put the full path here instead; it's wherever you installed MySQL under the 'bin' directory),
2. You connect to DB nwndbname with user nwnusername and password PASSWORD, and
3. You put the expire commands in a file called expire.sql,

You need to run this command:
mysql -u nwnusername -pPASSWORD nwndbname < expire.sql

[Rami_Ahmed]

We use SQLite so I never installed MySQl.

[Zebranky]

Oh. Then no clue.

Something to that effect, but I have NO idea how to do it.

[Rami_Ahmed]

[odenien]

We do a similar delete task like Zebranky is talking about, but we use mySQL. You probably could use the delete character schemes that are used in-game; although it probably would cause some lag.