- Joined
- Apr 13, 2011
- Messages
- 386
- Reaction score
- 69
[F.A.Q]How to? (in development)
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
2.How to delete an account and all his characters?
Solution thanks to Alphakillo23
3.How to prevent dupe by double login of clients?
Solution thanks to chumpywumpy
4.How to autogive premium to new accounts?
Solution thanks to chumpywumpy
PS.At every question solved i will update this first post so this will be a good tutorial for all.
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
USE [account]
DECLARE @DateTreshold datetime;
/* Calculate the treshold for created accounts.
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
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
DECLARE @DateTreshold datetime;
/* Calculate the treshold for created accounts.
To view the content, you need to sign in or register
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
To view the content, you need to sign in or register
*/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
Open a new query in Gamedb database and run:
Change only the id on the line 4
To find the id of user run query in ACCOUNT database
Code:
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
COMMITTo find the id of user run query in ACCOUNT database
Code:
SELECT UserNum FROM [account].[dbo].[cabal_auth_table] WHERE ID = '[B]Account Name Here[/B]'3.How to prevent dupe by double login of clients?
Solution thanks to chumpywumpy
Execute in the ACCOUNT databse of cabal the folowing query:
And that will remove the triger "fixlogin" that allows the client to connect from more then 1 client on the same account.
Code:
DROP TRIGGER [dbo].[fixlogin]4.How to autogive premium to new accounts?
Solution thanks to chumpywumpy
Go on the ACCOUNT databse in mssql on the stored procedure cabal_tool_registeraccount and edit the following line:
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
Code:
values(@UserNum, [COLOR="Red"]0[/COLOR], DATEADD(day, [COLOR="Blue"]100[/COLOR] , getdate()), 0)PS.At every question solved i will update this first post so this will be a good tutorial for all.
Last edited:
Questions don't belong in the Tutorials section, period.
