)Here is the DB layout
Code: Select all
CREATE DATABASE [mainlogin] ON PRIMARY
(
NAME = N'mainlogin',
FILENAME = N'C:\MSSQL\data\mainlogin.mdf',
size= 10,
FILEGROWTH = 10%
)
GO
use mainlogin
GO
ALTER DATABASE [mainlogin] ADD FILEGROUP [Second]
go
ALTER DATABASE [mainlogin] ADD FILE
(
NAME = N'mainlogin_idx',
FILENAME = N'C:\MSSQL\data\mainlogin_idx.mdf',
size= 10,
FILEGROWTH = 10%
) TO FILEGROUP [Second]
go
CREATE TABLE ACCOUNT_T -- Account info. Table
(
cAccountID NCHAR(10) NOT NULL, -- Account ID 1
cPasswd NCHAR(10), -- Password 2
iAccountID INT NOT NULL, -- Account Unique Number 3
cLoginIpAddress NCHAR(20), -- Login Ip Address 8 -> 4
IsGMAccount BIT, -- GM Account ID 9 -> 5
CreateDate SMALLDATETIME , -- Create Date 4 -> 6
LoginDate SMALLDATETIME , -- LogIn Date 7
LogoutDate SMALLDATETIME , -- LogOut Date 8
BlockDate SMALLDATETIME , -- Account Block Date 9
-- personal information
-- cRealName NCHAR(10), -- Real Name 10
cRealName NCHAR(30), -- Real Name 10
cSSN NCHAR(20), -- Person Number 11
-- cEmail NCHAR(50), -- E-MAIL 12
cEmail NCHAR(60), -- E-MAIL 12 -- for japan
cCharRecord NCHAR(100), -- Character Document 13
cQuiz NCHAR(45), -- QUIZ 14
cAnswer NCHAR(20), -- ANSWER 15
ValidDate SMALLDATETIME , -- Account Vaild Date 16
iValidTime INT, -- Account Vaild Time 17
CONSTRAINT PK_ACCOUNT_T PRIMARY KEY (cAccountID),
CONSTRAINT UniqlACCOUNT_ID UNIQUE (iAccountID)
)
GO
CREATE TABLE WS1_T -- First World Server Characters Info Table
(
iAccountID INT NOT NULL, -- Account ID
cCharName NCHAR(10), -- Character Name
CONSTRAINT FK_WS1_T FOREIGN KEY (
iAccountID
) REFERENCES [ACCOUNT_T] (
iAccountID
) ON DELETE CASCADE ON UPDATE CASCADE
)
GO
-- CREATE TABLE WS2_T -- Second World Server Characters Info Table
-- (
-- iAccountID INT NOT NULL, -- Account Unique Number
-- cCharName NCHAR(10), -- Character Name
--
-- CONSTRAINT FK_WS2_T FOREIGN KEY (
-- iAccountID
-- ) REFERENCES [ACCOUNT_T] (
-- iAccountID
-- ) ON DELETE CASCADE ON UPDATE CASCADE
--
-- )
-- go
CREATE NONCLUSTERED INDEX WS1_IDX
ON WS1_T(iAccountID)
WITH FILLFACTOR = 80,STATISTICS_NORECOMPUTE on SECOND
go
Code: Select all
-----------------------------------------------------------------------------
-- SiemenTech co., LDT. WorldServer.sql
-- 2002.07.4 by SungHun Mun
-----------------------------------------------------------------------------
CREATE DATABASE [ws1] ON PRIMARY
(
NAME = N'ws1',
FILENAME = N'C:\MSSQL\data\ws1.mdf',
size= 100,
FILEGROWTH = 10%
)
GO
ALTER DATABASE [ws1] ADD FILEGROUP [Second]
go
ALTER DATABASE [ws1] ADD FILE
(
NAME = N'ws1_idx',
FILENAME = N'C:\MSSQL\data\ws1_idx.mdf',
size= 10,
FILEGROWTH = 10%
) TO FILEGROUP [Second]
go
use ws1
go
CREATE TABLE CHARACTER_T -- Character Info. Table
(
-- CHARACTER ID
cCharName NCHAR(10) NOT NULL, -- Character Name
cAccountID NCHAR(10) NOT NULL, -- AccountID
CharID INT NOT NULL, -- Character ID
sID1 SMALLINT, -- ID Number 1
sID2 SMALLINT, -- ID Number 2
sID3 SMALLINT, -- ID Number 3
-- CHARACTER Ability
sLevel SMALLINT DEFAULT (1), -- Level
sStr SMALLINT, -- Strength of character
sVit SMALLINT, -- Vitality of character
sDex SMALLINT, -- Dexterity of character
sInt SMALLINT, -- Intelligent of character
sMag SMALLINT, -- Magic of character
sChar SMALLINT, -- Charisma of character
iExp INT DEFAULT (0), -- Experience of character
sUpStr SMALLINT DEFAULT (0), -- STR Up Point for next level up
sUpVit SMALLINT DEFAULT (0), -- Vit Up Point for next level up
sUpDex SMALLINT DEFAULT (0), -- Dex Up Point for next level up
sUpInt SMALLINT DEFAULT (0), -- Int Up Point for next level up
sUpMag SMALLINT DEFAULT (0), -- Mag Up Point for next level up
sUpChar SMALLINT DEFAULT (0), -- Char Up Point for next level up
-- CHARACTER Feature
bGender BIT, -- Gender(Character gender, male or female)
sSkin SMALLINT, -- Skin Color(Skin color--> black, white, yellow)
sHairStyle SMALLINT, -- Hair Style
sHairColor SMALLINT, -- Hair Color
sUnderWear SMALLINT, -- Underwear(underwear color)
iApprColor INT, -- Apperance Color
sAppr1 SMALLINT, -- Appearance Setting 1
sAppr2 SMALLINT, -- Appearance Setting 2
sAppr3 SMALLINT, -- Appearance Setting 3
sAppr4 SMALLINT, -- Appearance Setting 4
-- CHARACTER Location
cNation NCHAR(10) NOT NULL, -- Nation(Character's nation like aresden,elvine or NONE)
cMapLoc NCHAR(10) NOT NULL, -- Map Location(Current Character's position)
sLocX SMALLINT DEFAULT (-1), -- X Coordinates(Current character's x position in the map)
sLocY SMALLINT DEFAULT (-1), -- Y Coordinates(Current character's y position in the map)
-- CHARACTER Info.
cProfile NCHAR(70) NOT NULL, -- Profile(Character profile)
sAdminLevel SMALLINT, -- Administrator Level(GM>1, normal user=0)
iContribution INT, -- Contribution(Contribution point rises when character complete quest)
iLeftSpecTime INT, -- Remain Special Ability Time(If character use special ability, he can't do it within 20minutes)
cLockMapName NCHAR(10) NOT NULL, -- Locked Map Name(If character is locked at some map for some time, this means the locked map name)
iLockMapTime INT, -- Locked Map Time(And this is locked time)
FileSaveDate SMALLDATETIME , -- File Saved Date(last saved data date)
BlockDate SMALLDATETIME , -- Character Block Date(The GM can block character, and the character can't login until this date)
-- CHARACTER Guild Info.
cGuildName NCHAR(20) NOT NULL, -- Guild Name
iGuildID INT, -- Guild ID
sGuildRank SMALLINT, -- Guild Rank
sFightNum SMALLINT, -- Reserved FightZone ID(The fightzone id that the player has reserved)
sFightDate SMALLINT, -- Reserved FightZone Date(The reserved date that the player can use)
sFightTicket SMALLINT, -- Remain Reserved FightZone Ticket(Left counts of fightzone ticket)
-- CHARACTER Quest Info.
sQuestNum SMALLINT, -- Quest Number
sQuestID SMALLINT, -- Quest ID
sQuestCount SMALLINT, -- Quest Count(If the quest is slaying 10 slimes, this count means left count for player to complete the quest.)
sQuestRewType SMALLINT, -- Quest Reward Type(Kind of reward type, it could be experince or gold)
sQuestRewAmount SMALLINT, -- Quest Reward Amount(The amount of reward, ex.gold 1000)
bQuestCompleted BIT, -- Quest Completed(Whether player completed quest or not)
-- CHARCTER Event Info.
iEventID INT, -- Event ID
-- CHRACTER Crusade Info.
iWarCon INT, -- Charcter Crusade Contribution "character-war-contribution" 55
iCruJob INT, -- Crusade Job "crusade-job" 56
iCruID INT, -- Crusade ID "crusade-GUID" 57
iCruConstructPoint INT, -- Crusade Construct Point "construct-point" 58
-- CHARACTER Status
iPopular INT, -- Popularity(A player can evaluate other player. This is used for character rightness)
iHP INT, -- Hit Point(Current Hit point)
iMP INT, -- Magic Point(Current Mana point)
iSP INT, -- Stamina Point(Curren stamina point)
iEK INT, -- Enemy Kill Count(If a player capture enemy, this count rises)
iPK INT, -- Player Kill Count(If a player kill innocent friends, this count rises)
iRewardGold INT, -- Reward Gold(You can take the prize gold at city hall when you got captured enemy or completed quest.)
sDownSkillIDX SMALLINT, -- Down Skill Index(A character skill can't exceed 700. So if user want to raise other skill, he should down the other skill.)
sHunger SMALLINT, -- Hunger Status(A character is going to be hungry like the real human being. This is the parameter of hunger status. From 0 to 100)
sLeftSAC SMALLINT, -- Remain Super Attack Count(remained counts that character can use super attack)
iLeftShutupTime INT, -- Remain Shutup Time(If the GM gave a penalty to bad user, he can't chat for this time)
iLeftPopTime INT, -- Remain Popular Time(If a character evaluate other character, he can't do it again for some time to protect abuser)
iLeftForceRecallTime INT, -- Remain Force Recall Time(Time left when a character visits enemy's nation)
iLeftFirmStaminarTime INT, -- Remain Firm Staminar Time(Time left when a character eats super green potion)
iLeftDeadpenaltyTime INT, -- Remain Dead Penalty Time(To protect abuser, if a character dies, he can't go outside from his town for this time)
-- CHARACTER Magic Mastery Info.
cMagicMastery NCHAR(100) NOT NULL, -- Magic Mastery
-- CHARACTER Party Info.
iPartyID INT , -- Party ID
-- iGizoneItemUpgradeLeft INT -- highest Level Player Item Upgrade Left
CONSTRAINT PK_CHARACTER_T PRIMARY KEY (cCharName),
CONSTRAINT CHARID UNIQUE (CharID)
)
GO
CREATE TABLE SKILL_T -- Skill list Table take lessons
(
CharID INT NOT NULL, -- Character Name
sSkillID SMALLINT, -- SKill ID(Skill Name)
sSkillMastery SMALLINT, -- Skill Mastery(Skill Level)
iSkillSSN INT, -- Skill SSN(Left counts to skill up)
)
GO
CREATE NONCLUSTERED INDEX SKILL_IDX
ON SKILL_T(CharID)
WITH FILLFACTOR = 80,PAD_INDEX,STATISTICS_NORECOMPUTE on SECOND
go
CREATE TABLE ITEM_T -- Items list Table
(
CharID INT NOT NULL, -- Character Name(Character name who has these items.)
sItemID SMALLINT , -- Item Name
iCount INT DEFAULT (1), -- Item Count(number of item)
sItemType SMALLINT DEFAULT (0), -- Item Type
sID1 SMALLINT DEFAULT (0), -- Item Unique ID 1
sID2 SMALLINT DEFAULT (0), -- Item Unique ID 2
sID3 SMALLINT DEFAULT (0), -- Item Unique ID 3
sColor SMALLINT DEFAULT (0), -- Item Color( the color of the item)
sEffect1 SMALLINT DEFAULT (0), -- Item Effect Value 1
sEffect2 SMALLINT DEFAULT (0), -- Item Effect Value 2
sEffect3 SMALLINT DEFAULT (0), -- Item Effect Value 3
iLifeSpan INT DEFAULT (0), -- Item LifeSpan( Left lifespan of the item)
iAttribute INT DEFAULT (0), -- Item Attribute
bItemEquip BIT DEFAULT (0), -- Item Equip Status(this means the item is Equiped or not)
sItemPosX SMALLINT DEFAULT (40), -- Item X Coordinates(X position of item in the inventory window)
sItemPosY SMALLINT DEFAULT (30), -- Item Y Coordinates(Y position of item in the inventory window)
)
go
CREATE NONCLUSTERED INDEX ITEM_IDX
ON ITEM_T(CharID)
WITH FILLFACTOR = 70,PAD_INDEX,STATISTICS_NORECOMPUTE on SECOND
go
CREATE TABLE BANKITEM_T -- Items list table stored in warehouse
(
CharID INT NOT NULL, -- Character Name(Character name who has this item)
sItemID SMALLINT , -- Item Name(Item name)
iCount INT, -- Item Count(Number of item)
sItemType SMALLINT, -- Item Type
sID1 SMALLINT, -- Item Unique ID 1
sID2 SMALLINT, -- Item Unique ID 2
sID3 SMALLINT, -- Item Unique ID 3
sColor SMALLINT, -- Item Color( the color of the item)
sEffect1 SMALLINT, -- Item Effect Value 1
sEffect2 SMALLINT, -- Item Effect Value 2
sEffect3 SMALLINT, -- Item Effect Value 3
iLifeSpan INT, -- Item LifeSpan( left lifespan of the item)
iAttribute INT, -- Item Attribute
)
go
CREATE NONCLUSTERED INDEX BANKITEM_IDX
ON BANKITEM_T(CharID)
WITH FILLFACTOR = 80,PAD_INDEX,STATISTICS_NORECOMPUTE on SECOND
go
CREATE TABLE GUILD_T --
(
cGuildName NCHAR(20) NOT NULL, -- Guild Name
iGuildID INT, -- Guild ID
cMasterName NCHAR(10),
cLocation NCHAR(10),
sMemberNO SMALLINT,
CreateDate SMALLDATETIME
)
go
CREATE NONCLUSTERED INDEX GUILD_IDX
ON GUILD_T(cGuildName)
WITH FILLFACTOR = 80,PAD_INDEX,STATISTICS_NORECOMPUTE on SECOND
go
CREATE TABLE GUILDMEMBER_T --
(
cGuildName NCHAR(20) NOT NULL, -- Guild Name
cMemberName NCHAR(10),
JoinDate SMALLDATETIME
)
CREATE NONCLUSTERED INDEX GUILDMEMBER_IDX
ON GUILDMEMBER_T(cGuildName)
WITH FILLFACTOR = 80,PAD_INDEX,STATISTICS_NORECOMPUTE on SECOND
go