How do you delete characters and its items that is older then 6 months?
I have been testing to make some queries but no success.
Printable View
How do you delete characters and its items that is older then 6 months?
I have been testing to make some queries but no success.
#Spanish (cant answer in english here xD)
Debes tener mucho cuidado al borrarlos ya que puedes accidentalmente borrar algún item de otro usuario y no le gustara. xD
Para borrar los items debes buscar por el ID64 en _Items y eliminar los correspondientes con esos personajes creo que ahi aparecen por fecha tambien. Algunos estaran en los PETS por lo que también deberás hacerlo en varias tablas..
O simplemente puedes tratar de eliminar un personaje y este te dará un error con otra tabla.. va sa esa tabla y eliminas lo referente a ese personaje.. y si sale otro error vas a esa y haces lo mismo.. Es algo dificil de hacer para los que no saben mucho sobre estos IDs. xD
Buena suerte de todas maneras. te di varias pistas. :)
Maybe there is a way to use this to delete old characters and items?
PHP Code:USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_DeleteCharPermanently_NoTX] Script Date: 10/19/2012 16:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_DeleteCharPermanently_NoTX]
@UserJID int,
@CharID int
as
------------------------------------
-- Inventory¶û ±×¾È¿¡ °¡Áö°í ÀÖ´ø ¾ÆÀÌÅÛµé ³¯·Á¹ö¸®ÀÚ...
------------------------------------
-------------- Æê ¼Òȯ ¾ÆÀÌÅÛºÎÅÍ ÀÏ´Ü »èÁ¦ ----------------
declare @result int
declare @Slot tinyint
declare @RefItemID int
declare pc_inv_cursor cursor fast_forward for
select inv.slot
from _Inventory as inv join _Items as it on inv.ItemID = it.ID64
where
(inv.CharID = @CharID and inv.Slot >= 13 and inv.ItemID > 0) and -- Àåºñâ Á¦¿ÜÇÏ°í ¾ÆÀÌÅÛ ÀÖ´Â ¼ø¼ö Àκ¥Å丮 ¾È¿¡ µé¾îÀÖÀ¸¸ç
(it.Data <> 0) and -- ¼Òȯ¼ö¸¦ °¡Áö°í ÀÖ´Â
(exists (select top 1 ID from _RefObjCommon where ID = it.RefItemID and TypeID1 = 3 and TypeID2 = 2)) -- Æê¼Òȯ ¾ÆÀÌÅÛµé ã¾ÆÁà~
open pc_inv_cursor
fetch next from pc_inv_cursor into @slot
while @@fetch_status = 0
begin
exec @result = _STRG_DEL_ITEM_NoTX 1, @CharID, @Slot
if (@result < 0)
begin
close pc_inv_cursor
deallocate pc_inv_cursor
return @result
end
fetch next from pc_inv_cursor into @Slot
end
close pc_inv_cursor
deallocate pc_inv_cursor
------------------- ³²Àº ¾ÆÀÌÅÛµé ¸ùâ »èÁ¦ -------------------
-- ¾ÆÀÌÅÛÀÌ Çϳª¶óµµ ÀÖ´Ù¸é...
if (exists (select top 1 itemID from _Inventory where CharID = @CharID AND ItemID <> 0))
begin
update _ItemPool Set InUse = 0
from _ItemPool as pool join _Inventory as inv on pool.ItemID = Inv.ItemID
where Inv.CharID = @CharID and Inv.ItemID <> 0
end
----------- ¾ÆÀÌÅÛµé ³¯·ÈÀ¸´Ï ÃÖÁ¾ÀûÀ¸·ç Àκ¥Å丮 »èÁ¦ ------------
delete from _Inventory where CharID = @CharID
if (@@error <> 0)
begin
return -10001
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- APPLY_AVATAR_SYSTEM (Çã½Â¿í)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
------------------- ³²Àº ¾Æ¹ÙŸ Àκ¥Å丮 ¾ÆÀÌÅÛµé ¸ùâ »èÁ¦ -------------------
-- ¾ÆÀÌÅÛÀÌ Çϳª¶óµµ ÀÖ´Ù¸é...
if (exists (select top 1 itemID from _InventoryForAvatar where CharID = @CharID AND ItemID <> 0))
begin
update _ItemPool Set InUse = 0
from _ItemPool as pool join _InventoryForAvatar as InvAva on pool.ItemID = InvAva.ItemID
where InvAva.CharID = @CharID and InvAva.ItemID <> 0
end
----------- ¾ÆÀÌÅÛµé ³¯·ÈÀ¸´Ï ÃÖÁ¾ÀûÀ¸·ç ¾Æ¹ÙŸ Àκ¥Å丮 »èÁ¦ ------------
delete from _InventoryForAvatar where CharID = @CharID
if (@@error <> 0)
begin
return -10008
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- APPLY_AVATAR_SYSTEM (Çã½Â¿í)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
------------------------------------
-- COS »èÁ¦
------------------------------------
/*
declare @engaged_cos int
set @engaged_cos = 0
select @engaged_cos = EngagedCOS from _char with (nolock) where CharID = @CharID
if (@engaged_cos = 0)
begin
-- ¿Ö ¹ß»ýÇÏ´Â Áö Àß ¸ð¸£°ÚÁö¸¸... ¾ÆÁÖ µå¹°°Ô COS ¸¸ ³²¾ÆÀÖ´Â °æ¿ì°¡ ÀÖ´õ¶ó°í... -_-;
select @engaged_cos = ID from _CharCOS with (nolock) where OwnerCharID = @CharID
if (@engaged_cos is null)
set @engaged_cos = 0
end
if (@engaged_cos is not null and @engaged_cos <> 0)
begin
declare @Return_Del_COS int
exec @Return_Del_COS = _DeleteCharCOS_NoTX @CharID, @engaged_cos
if (@Return_Del_COS < 0)
begin
return -10002
end
end
*/
------------------------------------
-- Trijob µ¥ÀÌÅÍ »èÁ¦
------------------------------------
delete from _CharTrijob where CharID = @CharID
------------------------------------
-- ½ºÅ³ »èÁ¦
------------------------------------
delete _CharSkill where CharId = @CharID
delete _CharSkillMastery where charId = @CharID
------------------------------------
-- Äù½ºÆ® »èÁ¦
------------------------------------
delete _CharQuest where CharID = @CharID
------------------------------------
-- ±æµå ¸â¹ö »èÁ¦
------------------------------------
-- !!! ¸¸¾à ¿©±â¼ Guild ¸â¹ö »èÁ¦°¡ ÀϾÁö ¾ÊÀ¸¸é ShardManager ¿¡¼
-- _Guild_DelMember È£ÃâÇØ ÁÖµµ·Ï ¼öÁ¤ÇØ¾ß ÇÑ´Ù! Áö±ÝÀº ±× °úÁ¤Àº SkipÇÏ°í
-- ¸Þ¸ð¸®¿¡¼ »èÁ¦¸¸ ÀϾ°Ô µÇ¾î Àְŵ¢...
declare @GuildID int
set @GuildID = 0
select @GuildID = GuildID from _char where charid = @CharID
if (@GuildID is not null and @GuildID <> 0)
begin
if (not exists (select ID from _Guild where ID = @GuildID))
begin
update _Char set GuildID = 0 where CharID = @CharID
end
else
begin
declare @Return_Del_GuildMember int
exec @Return_Del_GuildMember = _Guild_DelMember_NoTX @GuildID, @CharID
if (@Return_Del_GuildMember < 0)
return -10003
end
end
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- APPLY_MENTOR_SYSTEM (ÃÖÀÎÈ£)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DECLARE @CampID INT
SET @CampID = 0
SELECT @CampID = CampID FROM _TrainingCampMember WHERE CharID = @CharID
IF (@CampID IS NOT NULL AND @CampID <> 0)
BEGIN
DECLARE @Ret_DelCampMember INT
EXEC @Ret_DelCampMember = _TRAINING_CAMP_DELMEMBER @CampID, @CharID, 0
IF (@Ret_DelCampMember < 0)
RETURN -10007
END
-- ÀÖÀ¸¸é Áö¿ö¹ö¸®ÀÚ~ ¹¹.
DELETE FROM _TrainingCampSubMentorHonorPoint WHERE CharID = @CharID
-- °æÇèÄ¡ ¹öÇÁ ·¹Äڵ嵵~~ ¤»¤»
DELETE FROM _TrainingCampBuffStatus WHERE CampID = @CampID
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- APPLY_MENTOR_SYSTEM (ÃÖÀÎÈ£)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-----------------------------------
-- Ä£±¸ ¸®½ºÆ® »èÁ¦ ( ³ªÀÇ Ä£±¸ ¸®½ºÆ® + Ä£±¸ÀÇ Ä£±¸ ¸®½ºÆ®Áß ³ª )
-----------------------------------
-- ³» Ä£±¸µé..
declare @FriendCharID int
declare @cursor_var cursor
set @cursor_var = cursor fast_forward
for
select FriendCharID
from _Friend
where CharID = @CharID
open @cursor_var
fetch next from @cursor_var into @FriendCharID
while( @@FETCH_STATUS = 0 )
begin
delete _Friend where CharID = @FriendCharID and FriendCharID = @CharID
fetch next from @cursor_var into @FriendCharID
end
close @cursor_var
deallocate @cursor_var
-- ³ª..
delete _Friend where CharID = @CharID
-----------------------------------
-- ÂÊÁö »èÁ¦
-----------------------------------
delete _Memo where CharID = @CharID
------------------------------------
-- TimedJob »èÁ¦Çϱâ
------------------------------------
delete _TimedJob where CharID = @CharID
------------------------------------
-- Static Avatar Á¤º¸ »èÁ¦
------------------------------------
delete from _staticavatar where charid = @charid
------------------------------------
-- ±Ó¸» ºí·° Á¤º¸ »èÁ¦
------------------------------------
-- Hyperdash 2009. 9. 3 ÇöÀç´Â Å×À̺íÀÌ ¾ø¾î ij¸¯ÅÍ »èÁ¦°¡ µÇÁö ¾ÊÀ½, ÃßÈÄ UI °³¼± 1Â÷ ÆÐÄ¡°¡ µÉ °æ¿ì¿¡´Â ÁÖ¼® ÇØÁ¦ ÇÊ
delete from _BlockedWhisperers where OwnerID = @charid
------------------------------------
-- _DeletedChar Entry »èÁ¦
------------------------------------
delete from _DeletedChar where CharID = @CharID
if (@@error <> 0)
begin
return -10004
end
-- start by novice.
DELETE FROM _User WHERE UserJID = @UserJID and CharID = @CharID
-- finish by novice.
------------------------------------
-- commit !!!
------------------------------------
-- Ŭ¶óÀ̾ðÆ®¿ë Äü½½·Ô Á¤º¸ ÀúÀå Å×ÀÌºí ³¯¸®±â!!!
exec _RemoveClientConfig @CharID -- by novice...... for saving client configurations...
return @GuildID
What Exactly do this query? In list of details. Please.Quote:
Originally Posted by djfisring
This is the stored procedure that is beeing used after the 7 days of when you want to delete the char from your account.Quote:
Originally Posted by Madelyn
So.. if a character is deleted.. in 7 day is deleted from DB permanently and not exist more?
Yes, and its items pets etc..
Joymax does this when the account is 6 months old and no a has logged in the to acc to get rid of useless space in the DB
Ok.. but where said the time to delete? Cuz if is for delete "Chars Deleted" its Ok need time: 1 day after deletion. But if is for chars 6 months not log? so where said this time? or where said this rule exactly?