· 7 years ago · Oct 08, 2018, 11:22 PM
1USE [master]
2GO
3/****** Object: Database [SilkroadPF] Script Date: 10/9/2018 1:11:50 AM ******/
4CREATE DATABASE [SilkroadPF]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'SilkroadPF', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\SilkroadPF.mdf' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
8 LOG ON
9( NAME = N'SilkroadPF_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\SilkroadPF_log.ldf' , SIZE = 11200KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
10GO
11ALTER DATABASE [SilkroadPF] SET COMPATIBILITY_LEVEL = 110
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [SilkroadPF].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [SilkroadPF] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [SilkroadPF] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [SilkroadPF] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [SilkroadPF] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [SilkroadPF] SET ARITHABORT OFF
27GO
28ALTER DATABASE [SilkroadPF] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [SilkroadPF] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [SilkroadPF] SET AUTO_UPDATE_STATISTICS ON
33GO
34ALTER DATABASE [SilkroadPF] SET CURSOR_CLOSE_ON_COMMIT OFF
35GO
36ALTER DATABASE [SilkroadPF] SET CURSOR_DEFAULT GLOBAL
37GO
38ALTER DATABASE [SilkroadPF] SET CONCAT_NULL_YIELDS_NULL OFF
39GO
40ALTER DATABASE [SilkroadPF] SET NUMERIC_ROUNDABORT OFF
41GO
42ALTER DATABASE [SilkroadPF] SET QUOTED_IDENTIFIER OFF
43GO
44ALTER DATABASE [SilkroadPF] SET RECURSIVE_TRIGGERS OFF
45GO
46ALTER DATABASE [SilkroadPF] SET DISABLE_BROKER
47GO
48ALTER DATABASE [SilkroadPF] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
49GO
50ALTER DATABASE [SilkroadPF] SET DATE_CORRELATION_OPTIMIZATION OFF
51GO
52ALTER DATABASE [SilkroadPF] SET TRUSTWORTHY OFF
53GO
54ALTER DATABASE [SilkroadPF] SET ALLOW_SNAPSHOT_ISOLATION OFF
55GO
56ALTER DATABASE [SilkroadPF] SET PARAMETERIZATION SIMPLE
57GO
58ALTER DATABASE [SilkroadPF] SET READ_COMMITTED_SNAPSHOT OFF
59GO
60ALTER DATABASE [SilkroadPF] SET HONOR_BROKER_PRIORITY OFF
61GO
62ALTER DATABASE [SilkroadPF] SET RECOVERY SIMPLE
63GO
64ALTER DATABASE [SilkroadPF] SET MULTI_USER
65GO
66ALTER DATABASE [SilkroadPF] SET PAGE_VERIFY CHECKSUM
67GO
68ALTER DATABASE [SilkroadPF] SET DB_CHAINING OFF
69GO
70ALTER DATABASE [SilkroadPF] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
71GO
72ALTER DATABASE [SilkroadPF] SET TARGET_RECOVERY_TIME = 0 SECONDS
73GO
74ALTER DATABASE [SilkroadPF] SET DELAYED_DURABILITY = DISABLED
75GO
76EXEC sys.sp_db_vardecimal_storage_format N'SilkroadPF', N'ON'
77GO
78ALTER DATABASE [SilkroadPF] SET QUERY_STORE = OFF
79GO
80USE [SilkroadPF]
81GO
82ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON;
83GO
84ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
85GO
86ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
87GO
88ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
89GO
90ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
91GO
92ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
93GO
94ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
95GO
96ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
97GO
98ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
99GO
100USE [SilkroadPF]
101GO
102/****** Object: Table [dbo].[_HardwareIdBanList] Script Date: 10/9/2018 1:11:51 AM ******/
103SET ANSI_NULLS ON
104GO
105SET QUOTED_IDENTIFIER ON
106GO
107CREATE TABLE [dbo].[_HardwareIdBanList](
108 [HardwareID] [varchar](20) NULL,
109 [EventTime] [datetime] NULL
110) ON [PRIMARY]
111GO
112/****** Object: Table [dbo].[_User] Script Date: 10/9/2018 1:11:51 AM ******/
113SET ANSI_NULLS ON
114GO
115SET QUOTED_IDENTIFIER ON
116GO
117CREATE TABLE [dbo].[_User](
118 [StrUserID] [varchar](64) NOT NULL,
119 [IP] [varchar](20) NOT NULL,
120 [HardwareId] [varchar](20) NOT NULL,
121 [CharID] [int] NULL,
122 [FreePVP] [tinyint] NOT NULL,
123 [JobEquipped] [bit] NOT NULL
124) ON [PRIMARY]
125GO
126/****** Object: Table [dbo].[_UserAttempts] Script Date: 10/9/2018 1:11:51 AM ******/
127SET ANSI_NULLS ON
128GO
129SET QUOTED_IDENTIFIER ON
130GO
131CREATE TABLE [dbo].[_UserAttempts](
132 [CharID] [int] NOT NULL,
133 [IP] [varchar](20) NULL,
134 [HardwareId] [varchar](20) NULL,
135 [Reason] [varchar](max) NOT NULL,
136 [EventTime] [datetime] NOT NULL
137) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
138GO
139/****** Object: Table [dbo].[_UserInformation] Script Date: 10/9/2018 1:11:51 AM ******/
140SET ANSI_NULLS ON
141GO
142SET QUOTED_IDENTIFIER ON
143GO
144CREATE TABLE [dbo].[_UserInformation](
145 [CharID] [int] NOT NULL,
146 [HardwareId] [varchar](20) NOT NULL,
147 [IP] [varchar](20) NOT NULL,
148 [EventTime] [datetime] NOT NULL
149) ON [PRIMARY]
150GO
151/****** Object: StoredProcedure [dbo].[_BanHardwareId] Script Date: 10/9/2018 1:11:51 AM ******/
152SET ANSI_NULLS ON
153GO
154SET QUOTED_IDENTIFIER ON
155GO
156-- =============================================
157-- Author: Ace
158-- =============================================
159CREATE PROCEDURE [dbo].[_BanHardwareId]
160@CharID INT
161AS
162SET NOCOUNT ON
163
164DECLARE @CharName VARCHAR(16) = (SELECT CharName16 FROM SRO_VT_SHARD.._Char WHERE CharID = @CharID)
165
166IF @CharName IS NULL
167BEGIN
168 PRINT @CharName + ' doesn''t exist.'
169 RETURN
170END
171
172DECLARE @HardwareId VARCHAR(20) = (SELECT HardwareId FROM _User WHERE CharID = @CharID)
173
174IF @HardwareId IS NOT NULL
175BEGIN
176 INSERT INTO _HardwareIdBanList VALUES (@HardwareId, GETDATE())
177 PRINT @CharName + ' | HardwareId: ' + @HardwareId + ' has been successfully banned.'
178 RETURN
179END
180
181INSERT INTO _HardwareIdBanList
182SELECT HardwareId, GETDATE()
183FROM _UserInformation
184WHERE CharId = @CharId
185
186PRINT @CharName + ' | All HardwareIds that the user logged in from were banned.'
187GO
188/****** Object: StoredProcedure [dbo].[_UserAuthentication] Script Date: 10/9/2018 1:11:51 AM ******/
189SET ANSI_NULLS ON
190GO
191SET QUOTED_IDENTIFIER ON
192GO
193CREATE PROCEDURE [dbo].[_UserAuthentication]
194@CharID INT,
195@IP VARCHAR(20)
196AS
197SET NOCOUNT ON
198
199DECLARE @StrUserID VARCHAR(64),
200 @HardwareID VARCHAR(64)
201
202
203SELECT TOP 1 @StrUserID = A.AccountID
204FROM SRO_VT_SHARD.._Char C
205JOIN SRO_VT_SHARD.._User U
206ON C.CharID = U.CharID
207JOIN SRO_VT_SHARd.._AccountJID A
208ON U.UserJID = A.JID
209WHERE C.CharID = @CharID
210
211SELECT TOP 1 @HardwareId = HardwareId
212FROM _User
213WHERE IP = @IP
214AND StrUserID = @StrUserID
215
216IF @HardwareId IS NULL
217BEGIN
218 INSERT INTO _UserAttempts VALUES (@CharID, @IP, @HardwareId, 'HardwareID is NULL', GETDATE())
219 SELECT 0
220 RETURN
221END
222
223IF EXISTS (SELECT HardwareId FROM _HardwareIdBanList WHERE HardwareID = @HardwareID)
224BEGIN
225 INSERT INTO _UserAttempts VALUES (@CharID, @IP, @HardwareID, 'User is HWID banned.', GETDATE())
226 SELECT 1
227 RETURN
228END
229
230IF EXISTS (SELECT CharID FROM _User WHERE CharID = @CharID)
231BEGIN
232 INSERT _UserAttempts SELECT @CharID, @IP, @HardwareId, 'User is already online and logged in _User table.', GETDATE()
233 DELETE _User WHERE CharID = @CharID
234 SELECT 2
235 RETURN
236END
237
238IF (SELECT COUNT(HardwareID) FROM _User WHERE HardwareId = @HardwareId AND CharID IS NOT NULL) >= 2
239BEGIN
240 INSERT INTO _UserAttempts VALUES (@CharID, @IP, @HardwareId, 'The user has already reached his HWID limit.', GETDATE())
241 SELECT 3
242 RETURN
243END
244
245
246IF NOT EXISTS (SELECT CharID FROM _UserInformation WHERE CharId = @CharId AND HardwareID = @HardwareID)
247 INSERT _UserInformation SELECT @CharId, @HardwareId, @IP, GETDATE()
248
249IF ((SELECT I.ItemID FROM SRO_VT_SHARD.._Inventory I WITH(NOLOCK)
250 JOIN SRO_VT_SHARD.._Items IT WITH(NOLOCK) ON I.ItemID = IT.ID64
251 JOIN SRO_VT_SHARD.._CharNameList C WITH(NOLOCK) ON I.CharID = C.CharID
252 WHERE C.CharName16 = @CharID AND I.Slot = 8
253 AND IT.RefItemID IN (SELECT ID FROM SRO_VT_SHARD.._RefObjCommon WHERE TypeID1 = 3 AND TypeID2 = 1 AND TypeID3 = 7 AND TypeID4 IN (1,2,3) AND Service = 1)) != 0)
254BEGIN
255 IF (SELECT COUNT(HardwareID) FROM _User WHERE HardwareId = @HardwareId AND JobEquipped = 1 AND CharID IS NOT NULL) >= 1
256 BEGIN
257 INSERT _UserAttempts SELECT @CharID, @IP, @HardwareId, 'Job HWID limit has been reached.', GETDATE()
258 SELECT 4
259 RETURN
260 END
261
262 UPDATE _User
263 SET CharID = @CharID,
264 JobEquipped = 1
265 WHERE HardwareId = @HardwareId
266 AND StrUserID = @StrUserID
267 AND IP = @IP
268 AND CharID IS NULL
269
270 SELECT 5
271 RETURN
272END
273
274UPDATE _User
275SET CharID = @CharID
276WHERE HardwareId = @HardwareId
277AND StrUserID = @StrUserID
278AND IP = @IP
279AND CharID IS NULL
280
281SELECT 6
282RETURN
283GO
284/****** Object: StoredProcedure [dbo].[_UserCount] Script Date: 10/9/2018 1:11:51 AM ******/
285SET ANSI_NULLS ON
286GO
287SET QUOTED_IDENTIFIER ON
288GO
289-- =============================================
290-- Author: Ace
291-- =============================================
292CREATE PROCEDURE [dbo].[_UserCount]
293AS
294SELECT COUNT(CharID) FROM _User WHERE CharID IS NOT NULL
295GO
296/****** Object: StoredProcedure [dbo].[_UserCurrentLocation] Script Date: 10/9/2018 1:11:51 AM ******/
297SET ANSI_NULLS ON
298GO
299SET QUOTED_IDENTIFIER ON
300GO
301-- =============================================
302-- Author: Ace
303-- =============================================
304CREATE PROCEDURE [dbo].[_UserCurrentLocation]
305@CharName VARCHAR(64)
306AS
307SELECT LatestRegion FROM SRO_VT_SHARD.._Char WHERE CharName16 = @CharName
308GO
309/****** Object: StoredProcedure [dbo].[_UserJob_Check] Script Date: 10/9/2018 1:11:51 AM ******/
310SET ANSI_NULLS ON
311GO
312SET QUOTED_IDENTIFIER ON
313GO
314-- =============================================
315-- Author: Ace
316-- =============================================
317CREATE PROCEDURE [dbo].[_UserJob_Check]
318@CharName VARCHAR(64)
319AS
320SET NOCOUNT ON
321
322DECLARE @HardwareId VARCHAR(20) = (SELECT HardwareId FROM _User WHERE CharID = @CharName)
323
324IF @HardwareId IS NULL
325BEGIN
326 SELECT 0
327 RETURN
328END
329
330IF (SELECT COUNT(HardwareId) FROM _User WHERE HardwareId = @HardwareId AND JobEquipped = 1 AND CharID IS NOT NULL) >= 1
331BEGIN
332 SELECT 1
333 RETURN
334END
335
336SELECT 2
337RETURN
338GO
339/****** Object: StoredProcedure [dbo].[_UserLoginEvent] Script Date: 10/9/2018 1:11:51 AM ******/
340SET ANSI_NULLS ON
341GO
342SET QUOTED_IDENTIFIER ON
343GO
344-- =============================================
345-- Author: Ace
346-- =============================================
347CREATE PROCEDURE [dbo].[_UserLoginEvent]
348@StrUserID VARCHAR(64),
349@IP VARCHAR(20),
350@HardwareId VARCHAR(20)
351AS
352SET NOCOUNT ON
353
354IF EXISTS (SELECT StrUserID FROM _User WHERE StrUserID = @StrUserID AND CharID IS NOT NULL)
355 RETURN
356
357DELETE FROM _User WHERE StrUserID = @StrUserID AND CharID IS NULL
358
359INSERT INTO _User VALUES (@StrUserID, @IP, @HardwareId, NULL, 0, 0)
360GO
361/****** Object: StoredProcedure [dbo].[_UserReturnType] Script Date: 10/9/2018 1:11:51 AM ******/
362SET ANSI_NULLS ON
363GO
364SET QUOTED_IDENTIFIER ON
365GO
366-- =============================================
367-- Author: Ace
368-- =============================================
369CREATE PROCEDURE [dbo].[_UserReturnType]
370@CharName VARCHAR(64),
371@Slot TINYINT
372AS
373SET NOCOUNT ON
374
375DECLARE @RefItemID INT = (SELECT IT.RefItemID FROM SRO_VT_SHARD.._Items IT WITH (NOLOCK)
376 JOIN SRO_VT_SHARD.._Inventory I WITH (NOLOCK)
377 ON IT.ID64 = I.ItemID
378 JOIN SRO_VT_SHARD.._Char C WITH (NOLOCK)
379 ON I.CharID = C.CharID
380 WHERE C.CharName16 = @CharName
381 AND I.Slot = @Slot)
382
383IF @RefItemID = 2128
384BEGIN
385 SELECT 1
386 RETURN
387END
388
389IF @RefItemID IN (3769, 24448)
390BEGIN
391 SELECT 2
392 RETURN
393END
394
395SELECT 0
396GO
397USE [master]
398GO
399ALTER DATABASE [SilkroadPF] SET READ_WRITE
400GO