ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_Status] DEFAULT ((1)) FOR [Status]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_GMrank] DEFAULT ((0)) FOR [GMrank]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_regtime] DEFAULT (getdate()) FOR [regtime]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_freetime] DEFAULT ((0)) FOR [freetime]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_sec_primary] DEFAULT ((3)) FOR [sec_primary]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_sec_content] DEFAULT ((3)) FOR [sec_content]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_at] DEFAULT ((0)) FOR [at]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_block] DEFAULT ((0)) FOR [block]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_expired] DEFAULT (getdate()) FOR [expired]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_hashigh] DEFAULT ((0)) FOR [hashigh]
GO
ALTER TABLE [dbo].[TB_User] ADD CONSTRAINT [DF_TB_User_lastloginip] DEFAULT ((0)) FOR [lastloginip]
GO
2-create dbo._FreeAccess
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: Table [dbo].[_FreeAccess] Script Date: 05/19/2012 21:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_FreeAccess](
[JID] [int] NOT NULL,
[EndTime] [datetime] NOT NULL,
[ShardID] [int] NOT NULL
) ON [PRIMARY]
GO
3-create dbo.BrutalBlockedIP
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: Table [dbo].[_BrutalBlockedIP] Script Date: 05/19/2012 21:55:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_BrutalBlockedIP](
[nIdx] [int] IDENTITY(1,1) NOT NULL,
[szIPBegin] [varchar](16) NOT NULL,
[szIPEnd] [varchar](16) NOT NULL,
[szGM] [varchar](64) NOT NULL,
[dIssueDate] [datetime] NOT NULL,
[szISP] [varchar](256) NULL,
[szDesc] [varchar](512) NULL,
CONSTRAINT [PK__BrutalBlockedIP] PRIMARY KEY CLUSTERED
(
[nIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
4-drop and create dbo._PrivilegedIP
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: Table [dbo].[_PrivilegedIP] Script Date: 05/19/2012 21:57:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_PrivilegedIP](
[nIdx] [int] IDENTITY(1,1) NOT NULL,
[szIPBegin] [varchar](16) NOT NULL,
[szIPEnd] [varchar](16) NOT NULL,
[szGM] [varchar](64) NOT NULL,
[dIssueDate] [datetime] NOT NULL,
[szISP] [varchar](256) NULL,
[szDesc] [varchar](512) NULL,
CONSTRAINT [PK__PrivilegedIP] PRIMARY KEY CLUSTERED
(
[nIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Now the required stored procedures for AccountDB
5-CertifyTB_User
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [dbo].[_CertifyTB_User] Script Date: 05/19/2012 21:59:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 4. ALTER PROCEDURE _CertifyTB_User
CREATE PROCEDURE [dbo].[_CertifyTB_User]
@szUserID varchar(25),
@szPassword varchar(50)
AS
if exists (
select
''
from _Punishment with ( nolock )
where SerialNo = (
select
max( SerialNo ) from _BlockedUser with ( nolock )
where UserJID = ( SELECT JID FROM TB_User WHERE StrUserID = @szUserID ) and
Type = 99 and getdate() between timeBegin and timeEnd ) )
begin
select convert( tinyint, 3), ( select JID from TB_User where StrUserID = @szUserID ), convert( tinyint, 0), convert( tinyint, 0)
return
end
if exists (select 1 from TB_User where StrUserID=@szUserID and block=1 and expired>=GETDATE())
begin
select convert( tinyint, 3), ( select JID from TB_User where StrUserID = @szUserID ), convert( tinyint, 0), convert( tinyint, 0)
return
end
declare @nUserJID int
declare @sec_primary tinyint
declare @sec_content tinyint
set @nUserJID = 0
set @sec_primary = 0
set @sec_content = 0
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- ?????? ?????????? ???????? ??????
declare @ApplyType tinyint
declare @AccPlayTime int
declare @LatestUpdateTime_ToPlayTime int
set @ApplyType = 0 -- ?????? ?????????? ???????? ?????????? ???????? ???? ?????????? ???? ??????
set @AccPlayTime = 0
set @LatestUpdateTime_ToPlayTime = 0
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SELECT @nUserJID = JID, @sec_primary = sec_primary, @sec_content = sec_content
, @AccPlayTime = AccPlayTime, @LatestUpdateTime_ToPlayTime = LatestUpdateTime_ToPlayTime FROM TB_User
WHERE StrUserID = @szUserID and password = @szPassword
--and AT=1
-- Edit For fatigue system
-- Please, fill value to @ApplyType ( 1 : TEEN USER , 2 : ADULT USER ) from your web database
-- ex ) @ApplyType = exec web.proc.certify
if( @nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
begin
-- ?????? ?????? ???? ????
select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
--select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert(tinyint, 0), convert(int, 0), convert(int, 0)
return
end
if( exists( select Type from _BlockedUser where UserJID = @nUserJID and Type = 1 and getdate() between timeBegin and timeEnd))
begin
-- ?????? ?????? ???? ????
select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0)
--select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert(tinyint, 0), convert(int, 0), convert(int, 0)
return
end
-- ?????? ?????? ???? ????
select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content
--select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @ApplyType, @AccPlayTime, @LatestUpdateTime_ToPlayTime
return
GO
6-create _CheckfreeAccess
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [dbo].[_CheckFreeAccess] Script Date: 05/19/2012 22:04:52 ******/
SET ANSI_NULLS ON
GO
-- ว๖ภ็ ผณฐ่ ป๓ BANLevel ภฬ 4 ( BanLevel รึฐํฤก ) ภฮ ฐๆฟ์ฟกธธ FA ฐก ฐกดษวฯดู.
SELECT TOP 1 @EndTIme = Endtime
FROM _FreeAccess
WHERE JID = @JID
AND ShardID = @ShardID
ORDER BY EndTime DESC
if exists (select 1 from TB_User where JID=@JID and block=1)
begin
Select 1
return
end
IF ( @EndTIme <> 0 ) AND ( DATEDIFF( SECOND, GETDATE(), @EndTIme ) > 0 )
BEGIN
-- ฝรฐฃย๗ฐก พ็ผ๖, ฝรฐฃ ฟฉภฏฐก ภึดยณ๐
SELECT 1
END
ELSE
BEGIN
-- ฝรฐฃย๗ฐก ภฝผ๖, ฝรฐฃ ฟฉภฏฐก พ๘ดยณ๐
SELECT 0
END
GO
7-create _ResetFreeAccess
PHP Code:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: StoredProcedure [dbo].[_ResetFreeAccess] Script Date: 05/19/2012 22:06:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------
-- ณฏยฅฐก ม๖ณญ พึต้ภบ ป่มฆวุ น๖ธฐดู.
----------------------------------------------------------------------
CREATE PROCEDURE [dbo].[_ResetFreeAccess]
AS
DELETE FROM _FreeAccess WHERE GETDATE() > EndTime
GO
now we finished AccountDB time for ShardDB
8-create table _FreeAccessBanlvl
PHP Code:
USE [SRO_VT_SHARD]
GO
/****** Object: Table [dbo].[_FreeAccessBanLvl] Script Date: 05/19/2012 22:07:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_FreeAccessBanLvl](
[JID] [int] NOT NULL,
[BanLvl] [int] NOT NULL,
[EndTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
9-create procedure _GetAllFreeAccessBanlvl
PHP Code:
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_GetAllFreeAccessBanLvl] Script Date: 05/19/2012 22:10:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[_GetAllFreeAccessBanLvl]
AS
-- ธีภ๚ ฑโฐฃภฬ ม๖ณญ ณัภบ ม๖ฟ๖ น๖ธฐดู!
DELETE FROM _FreeAccessBanLvl
WHERE GETDATE() > EndTime
IF EXISTS (SELECT JID FROM _FreeAccessBanLvl WITH (NOLOCK))
BEGIN
-- SELECT ALL!!
SELECT JID, BanLvl, EndTime
FROM _FreeAccessBanLvl WITH (NOLOCK)
END
ELSE
BEGIN
-- ทนฤฺตๅฐก พ๘ดย ฐๆฟ์ ด๕นฬภป บธณปมึภฺ.
SELECT 0, 0, GETDATE()
END
GO
10-create this function because the next stored procedure will ask for it
PHP Code:
USE [SRO_VT_SHARD]
GO
/****** Object: UserDefinedFunction [dbo].[_DiffTime_CRTTIME_SQLTIME] Script Date: 05/19/2012 22:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_DiffTime_CRTTIME_SQLTIME]
(@CRTTime INT, @SQLTime DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @nTimeTo INT
DECLARE @nTimeFrom INT
SET @nTimeTo = 0
SET @nTimeFrom = @CRTTime
SET @nTimeTo = DATEDIFF( ss, '1970-01-01 09:00:00', @SQLTime )
/*
DECLARE @nRemainHH INT
DECLARE @nRemainMM INT
DECLARE @nRemainSS INT
SET @nRemainHH = (@nTimeTo - @nTimeFrom) / 60 / 60
SET @nRemainMM = (@nTimeTo - @nTimeFrom) / 60 - (@nRemainHH * 60)
SET @nRemainSS = (@nTimeTo - @nTimeFrom) - (@nRemainMM * 60 ) - (@nRemainHH * 60 * 60)
*/
/*
RETURN cast( abs(@nRemainHH) as varchar(128)) + 'ฝรฐฃ'
+ cast( abs(@nRemainMM) as varchar(128)) + 'บะ'
+ cast( abs(@nRemainSS) as varchar(128)) + 'รส'
*/
RETURN (@nTimeFrom - @nTimeTo)
END
GO
11- create procedure _InitializeFreeAccessBanLvl
PHP Code:
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_InitializeFreeAccessBanLvl] Script Date: 05/20/2012 13:15:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[_InitializeFreeAccessBanLvl]
as
delete _FreeAccessBanLvl where BanLvl in(3, 4)
declare @CharID int
declare @Category int
declare @JobID int
declare @TimeToKeep int
declare @Data1 int
declare @Data3 int
declare @JID int
declare @Gold1D_ItemID int
declare @Gold4W_ItemID int
declare @Silver1D_ItemID int
declare @Silver4W_ItemID int
declare @EndDateTime datetime
select @Gold1D_ItemID = id from _RefObjCommon with(nolock) where codename128 = 'ITEM_MALL_GOLD_TIME_SERVICE_TICKET_1D'
if( @@error <> 0 or @@rowcount = 0 )
begin
raiserror( '1ภฯ ฐ๑ตๅลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
return -1
end
select @Gold4W_ItemID = id from _RefObjCommon with(nolock) where codename128 = 'ITEM_MALL_GOLD_TIME_SERVICE_TICKET_4W'
if( @@error <> 0 or @@rowcount = 0 )
begin
raiserror( '4มึ ฐ๑ตๅลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
return -1
end
select @Silver1D_ItemID = id from _RefObjCommon with(nolock) where codename128 = 'ITEM_MALL_SILVER_TIME_SERVICE_TICKET_1D'
if( @@error <> 0 or @@rowcount = 0 )
begin
raiserror( '1ภฯ ฝวน๖ลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
return -1
end
select @Silver4W_ItemID = id from _RefObjCommon with(nolock) where codename128 = 'ITEM_MALL_SILVER_TIME_SERVICE_TICKET_4W'
if( @@error <> 0 or @@rowcount = 0 )
begin
raiserror( '4มึ ฝวน๖ลธภำ พฦภฬลภฬ พ๘ฝภดฯดู.', 0, 1 )
return -1
end
set @JID = 0
declare @Date datetime
declare @DiffTime int
set @Date = cast(getdate() as datetime)
declare timedjob_cursor cursor fast_forward for
-- ฐ๑ตๅ, ฝวน๖, วมธฎนฬพ๖ พฦภฬล
select max(Category), max(JobID), max(TimeToKeep), max(Data1), max( isnull(Data3, 0) ), CharID
from _TimedJob with (nolock)
where Category = 5 or ( Category = 3 and JobID in( @Gold1D_ItemID,@Gold4W_ItemID,@Silver1D_ItemID,@Silver4W_ItemID ) )
group by CharID
open timedjob_cursor
fetch next from timedjob_cursor into @Category, @JobID, @TimeToKeep, @Data1, @Data3, @CharID
while @@fetch_status = 0
begin
select @JID = UserJID from _User where CharID = @CharID
if @@rowcount = 0 or @@error <> 0
begin
close timedjob_cursor
deallocate timedjob_cursor
Raiserror('ฤณธฏลอฟก วุด็วฯดย ฐ่มคภฬ พ๘ณื! [%d]', 1, 16, @CharID)
return -1
end
if( @Category = 3 )
begin
declare @ItemTotalSecond int
select @ItemTotalSecond = param1 from _RefObjItem where id in ( select link from _RefObjCommon where id = @JobID )
set @TimeToKeep = @Data1 + ( @ItemTotalSecond + ( @Data3 * 86400 ) + 86400 )
end
set @DiffTime = dbo._DiffTime_CRTTIME_SQLTIME( @TimeToKeep, @Date )
set @EndDateTime = DATEADD(second, @DiffTime, getdate())
insert into _FreeAccessBanLvl values(@JID, 4, @EndDateTime)
fetch next from timedjob_cursor into @Category, @JobID, @TimeToKeep, @Data1, @Data3, @CharID
end
close timedjob_cursor
deallocate timedjob_cursor
GO
12-create procedure _InsertFreeAccessBanLvl_CashItemUsed and watch out from it because it link ShardDB with AccountDB
PHP Code:
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_InsertFreeAccessBanLvl_CashItemUsed] Script Date: 05/19/2012 22:16:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_InsertFreeAccessBanLvl_CashItemUsed]
@JID INT,
@BanLevel INT,
@ValidityTerm INT,
@ShardID INT
AS
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(second, @ValidityTerm, GETDATE())
it's not recommended to set BanUselessUserWhenFreeAccess to 0 cause it may make flood when your server get crowded
okay you must download the GW too from Chrenobyl's thread and if you got problem with download server you can replace with BR's one it works too
well i'm not gonna insult or even say anything after what you said Caipi
and the picture says everything
i'm just laughing when i read that "today i'm gonna share the leaked version of new sql revolution"
#ban my ID after this post it's not honor to stay here anymore
![[SQL Guide] vSRO - Premium Free Access System](https://www.elitepvpers.com/forum/iconimages/sro-pserver-guides-releases/sql-guide-vsro-premium-free-access-system_ltr.gif){kind=small}
[SQL Guide] vSRO - Premium Free Access System
