How to?

[xXxAxXx]

I will make this post so in the future everibody who want to know the aswers will find them here.

1.How can i make a daily prune on the cabal database of all accounts that doesent have characters created for over 3 day(for ex)?
Solution thanks to Alphakillo23

Spoiler:

USE [account]
DECLARE @DateTreshold datetime;

/* Calculate the treshold for created accounts.
DATEADD (Transact-SQL)
Example: 12 hours:

SELECT @DateTreshold = DATEADD(hh, 12, GETDATE());

One week:
SELECT @DateTreshold = DATEADD(ww, 1, GETDATE()); */
SELECT @DateTreshold = DATEADD(d, 1, GETDATE());

/* Begin marked transaction
Using Marked Transactions (Full Recovery Model) */
BEGIN TRAN AccCleanup
WITH MARK 'Scheduled inactive account cleanup';

DELETE
FROM [dbo].[cabal_auth_table]
WHERE
/* We don't want to harass logged in users, do we? ;) */
[Login] = 0

/* Also we don't want to touch accounts that aren't registered for under a day */
AND [CreateDate] > @DateTreshold

/* And we only want to touch accounts that weren't logged in */
AND [LoginTime] IS NULL

/* To sum it up:
Check if the account isn't logged in currently, then check if it exists
for longer than @DateTreshold and last but not least, check if the account has a "LoginTime"
Timestamp (which doesn't exist when the account wasn't logged in yet) */

/* Go ahead, make my day ^.^ */
COMMIT TRAN AccCleanup
GO

2.How to delete an account and all his characters?
Solution thanks to Alphakillo23

Spoiler:

Open a new query in Gamedb database and run:

    USE gamedb
     
    DECLARE @UserNum INT, @i int, @max int;
    SET @UserNum = 1 /* UserNum / Account ID goes here */
                   
    SET @i  = @UserNum * 8;
    SET @max                = @i + 5
     
     
    BEGIN TRAN
            --Iterate through all possible charactersslots on the specified account
            WHILE @i < @max
            BEGIN
                    --Check if the character exists
                    IF EXISTS (SELECT CharacterIdx FROM cabal_character_table WHERE CharacterIdx = @i)
                    BEGIN
                            DELETE FROM cabal_equipment_table WHERE CharacterIdx = @i
                            DELETE FROM cabal_inventory_table WHERE CharacterIdx = @i
                            DELETE FROM cabal_skilllist_table WHERE CharacterIdx = @i
                            DELETE FROM cabal_quickslot_table WHERE CharacterIdx = @i
                            DELETE FROM cabal_qddata_table WHERE CharacterIdx = @i
                            DELETE FROM cabal_questdata_table WHERE CharacterIdx = @i
                            DELETE FROM cabal_record_combo WHERE charIdx = @i              
                            DELETE FROM cabal_character_table WHERE CharacterIdx = @i              
                            DELETE FROM GuildMember WHERE CharacterIndex = @i
                           
                            DELETE FROM chat_buddy_table WHERE RegisteeCharIdx = @i OR RegisterCharIdx = @i                
                    END            
                    SET @i += 1
            END
     
            DELETE FROM cabal_warehouse_table WHERE UserNum = @UserNum
            DELETE FROM CHATBUDDY WHERE USERNUM = @UserNum 
    COMMIT

Change only the id on the line 4
To find the id of user run query in ACCOUNT database

SELECT UserNum FROM [account].[dbo].[cabal_auth_table] WHERE ID = 'Account Name Here'

3.How to prevent dupe by double login of clients?
Solution thanks to chumpywumpy

Spoiler:

Execute in the ACCOUNT databse of cabal the folowing query:

DROP TRIGGER [dbo].[fixlogin]

And that will remove the triger "fixlogin" that allows the client to connect from more then 1 client on the same account.

4.How to autogive premium to new accounts?
Solution thanks to chumpywumpy

Spoiler:

Go on the ACCOUNT databse in mssql on the stored procedure cabal_tool_registeraccount and edit the following line:

values(@UserNum, 0, DATEADD(day, 100 , getdate()), 0)

The red number is the account type. 0 is a free account and 1 is charged (premium). The blue number is the expiry date (the dateadd function is adding 100 days to today's date). Simply change the 0 to 1 to make all new chars premium and change the 100 if you want them to have more than 100 days of prem

PS.At every question solved i will update this first post so this will be a good tutorial for all.

[MentaL]

[Yamachi]

It doesn't quite work like that, I'm afraid :P Questions don't belong in the Tutorials section, period.

*MOVED*

[xXxAxXx]

no problem you will gladly movet after 10 question at least answered.
Waiting for help.

[Alphakilo23]

This can't be done natively with the Express (free) edition, I'm afraid. But you could add a task to your windows task planner.

Checking whether an account has associated characters is a rather challenging task for the official database. I'd just check if they have logged in yet.

In fact, I do have a script lying around somewhere on a backup media... I'll post it here.

[xXxAxXx]

thanks.
I am ussing mssql 2005

[Alphakilo23]

Got it, I even added commentary and helpful links to not-so-common SQL commands:
http://pastebin.com/L8bhJSdb

[xXxAxXx]

so i just onli have to modifi the time in
SELECT @DateTreshold = DATEADD(hh, 12, GETDATE());
an run the query in account database?

[Alphakilo23]

so i just onli have to modifi the time in
SELECT @DateTreshold = DATEADD(hh, 12, GETDATE());
an run the query in account database?

That would delete all accounts that aren't logged in currently, are older than 12 hours and never were used to authenticate against the gameserver, yes.

[xXxAxXx]

and dont have caracters i presume?

---------- Post added at 12:56 AM ---------- Previous post was at 12:55 AM ----------

Ok it worked.Deleted the accounts.Now how do i schedule that?

[Yamachi]

mssql scheduled taks - Google Search

I appreciate you making this thread, but please put some effort into attempting to find the answer to things yourself. Questions like your last one are best asked on Google.

[xXxAxXx]

allready did it but i am new in mssql.
I know mysql with phpmyadmin so that is why i have asked for help here.

[Alphakilo23]

mssql scheduled taks - Google Search

I appreciate you making this thread, but please put some effort into attempting to find the answer to things yourself. Questions like your last one are best asked on Google.

Unfortunately that relies on the SQL Server-Agent service, which doesn't exist in the Express edition:


So yarr, this thread has it's purpose after all. But there is way to do that with the Express Edition, using the Windows Task-Scheduler and the SQL CLI (sqlcmd.exe)

and dont have caracters i presume?

It doesn't check whether the account has characters or not. It just checks if the account was logged in or not.
Because if an account wasn't logged it, logically it can't have any characters.

You'd have to loop SELECTs on the dbo.cabal_character_table which is an resource consuming task on large databases, because of the complete lack of indexes and relations (PKs / FKs).
I could modify the query to check for characters, but it'll drain performance once have a decent amount of characters on your server.

[xXxAxXx]

ok now i understand.It specificaly deletes the account that where never logen id.

---------- Post added at 01:37 PM ---------- Previous post was at 12:52 PM ----------

Updated the fisrt post with a new how to.

Alpha when i tried to delete user 11 i got

Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '+'.

update of the first post.