69 lines
3.0 KiB
Transact-SQL
69 lines
3.0 KiB
Transact-SQL
-- LosaDev 2019: procedures the 2019 server calls (ls_query.ini ids 1, 136, 137, 139)
|
|
-- that no released DB script contains. Written for the testbed from the server code (DBClient.cpp).
|
|
-- Still missing on purpose: 197 game_item_extra_get_list_dev, 1500 SC_ERROR_LOG_ADD,
|
|
-- 2108 log_item_bonus_gold_set_add, 2207 game_block_hdd_serial_check (dev/log/anti-cheat only).
|
|
USE [LosaGame]
|
|
GO
|
|
IF OBJECT_ID(N'dbo.game_pingpong', N'P') IS NOT NULL DROP PROCEDURE dbo.game_pingpong
|
|
GO
|
|
-- id 1: DB agent keep-alive; the code says "a query that does not really exist"
|
|
CREATE PROCEDURE dbo.game_pingpong
|
|
AS
|
|
SET NOCOUNT ON
|
|
GO
|
|
IF OBJECT_ID(N'dbo.game_member_count', N'P') IS NOT NULL DROP PROCEDURE dbo.game_member_count
|
|
GO
|
|
-- id 136: OnSelectMemberConut - one LONG back, 0 = new account (the server then calls game_member_add)
|
|
CREATE PROCEDURE dbo.game_member_count
|
|
@userID varchar(12)
|
|
AS
|
|
SET NOCOUNT ON
|
|
SELECT COUNT(*) FROM dbo.userMemberDB WITH(NOLOCK) WHERE userID = @userID
|
|
GO
|
|
IF OBJECT_ID(N'dbo.game_member_add', N'P') IS NOT NULL DROP PROCEDURE dbo.game_member_add
|
|
GO
|
|
-- id 137: OnInsertMember(privateID, publicID) - first login of a publisher account creates the game account
|
|
CREATE PROCEDURE dbo.game_member_add
|
|
@userID varchar(12),
|
|
@nickName varchar(20)
|
|
AS
|
|
SET NOCOUNT ON
|
|
IF EXISTS (SELECT 1 FROM dbo.userMemberDB WITH(NOLOCK) WHERE userID = @userID) RETURN
|
|
DECLARE @ret int
|
|
EXEC dbo.web_member_add @userID, @nickName, '', '', 0, 0, 0, 0, '127.0.0.1', @ret OUTPUT
|
|
GO
|
|
IF OBJECT_ID(N'dbo.game_create_nickname', N'P') IS NOT NULL DROP PROCEDURE dbo.game_create_nickname
|
|
GO
|
|
-- id 139: OnUpdateFirstPublicID(accountIDX, newNick)
|
|
CREATE PROCEDURE dbo.game_create_nickname
|
|
@accountIDX int,
|
|
@nickName varchar(20)
|
|
AS
|
|
SET NOCOUNT ON
|
|
UPDATE dbo.userMemberDB SET nickName = @nickName WHERE accountIDX = @accountIDX
|
|
GO
|
|
-- id 11 game_game_get_data: the released script returns cpID/cpUNIQ (channeling columns), but the game server only
|
|
-- reads them when the locale uses channeling IDs (Korea). The Indonesia locale expects the same row without them.
|
|
ALTER PROCEDURE [dbo].[game_game_get_data]
|
|
(
|
|
@userID varchar(12)
|
|
)
|
|
As
|
|
SET NOCOUNT ON
|
|
DECLARE @CURRENTDATE DATETIME
|
|
DECLARE @CONNECTDATE DATETIME
|
|
SET @CURRENTDATE = GETDATE();
|
|
SET @CONNECTDATE = ( SELECT conndate FROM dbo.userGameDB WHERE accountIDX IN ( SELECT accountIDX FROM dbo.userMemberDB WHERE userID = @userID ))
|
|
IF( @CURRENTDATE < @CONNECTDATE )
|
|
SET @CONNECTDATE = @CURRENTDATE
|
|
SELECT a.accountIDX, b.userID, b.nickName, a.userState, a.gameMoney
|
|
, a.conn_count, @CONNECTDATE, a.userLevel, a.userEXP
|
|
, b.eventType, isNull(c.class,0) as 'class', b.joinType, b.regDate, a.regionType
|
|
, a.rencpoint, a.renspoint, isNull(c.region,0) as 'region', b.cpType
|
|
, b.limitType, b.limitDate, a.userFishingLevel, a.userFishingEXP
|
|
, a.refillData, a.userExcavationLevel, a.userExcavationEXP, a.userHerocEXP, a.userHerosEXP, a.practice
|
|
FROM userGameDB a WITH( NOLOCK )
|
|
JOIN userMemberDB b WITH( NOLOCK ) ON a.accountIDX = b.accountIDX
|
|
LEFT JOIN userRankingDB c WITH( NOLOCK ) ON a.accountIDX = c.accountIDX
|
|
WHERE b.userID = @userID
|
|
GO |