Aegis logs

[Beret]

As we know the emulator still supported the older logs which follows eathena, a lot has changed in those years, my suggestion is to renewal in the logs, to follow aegis and so provide more support for Server Admins.

Logs aegis:

Spoiler

CREATE TABLE [dbo].[itemLog]( [index] [int] IDENTITY(1,1) NOT NULL, [Ver] [int] NULL, [SID] [int] NULL, [Action] [int] NULL, [logtime] [datetime] NULL, [ip] [varchar](20) NULL, [eventx] [int] NULL, [eventy] [int] NULL, [srcAccountID] [int] NULL, [srcAccountName] [varchar](24) NULL, [srcCharID] [int] NULL, [srcCharName] [varchar](24) NULL, [srcJobType] [int] NULL, [srcLevel] [int] NULL, [desAccountID] [int] NULL, [desAccountName] [varchar](24) NULL, [desCharID] [int] NULL, [desCharName] [varchar](24) NULL, [Serialcode] [bigint] NULL, [ItemName] [varchar](24) NULL, [ItemID] [int] NULL, [ItemCount] [int] NULL, [MapName] [varchar](24) NULL, [price] [int] NULL, [slot1] [int] NULL, [slot2] [int] NULL, [slot3] [int] NULL, [slot4] [int] NULL, [refiningLevel] [int] NULL, [AuctionID] [int] NULL, [ItemBuyCash] [int] NULL, [Zeny] [int] NULL, [DestZeny] [int] NULL, [ItemHireExpireDate] [datetime] NULL, [bizType] [int] NULL, [identity] [bigint] NOT NULL,

CREATE TABLE [dbo].[deleteCharacterLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NOT NULL,	[GID] [int] NOT NULL,	[AID] [int] NOT NULL,	[characterName] [varchar](24) NOT NULL,	[IP] [varchar](24) NULL,	[joblevel] [int] NULL,	[level] [int] NOT NULL,	[job] [int] NOT NULL, 

CREATE TABLE [dbo].[deleteblockinfo](	[GID] [int] NOT NULL,	[AID] [int] NOT NULL,	[BlockDate] [datetime] NOT NULL,	[ExpireDate] [datetime] NOT NULL 

CREATE TABLE [dbo].[createCharacterLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NOT NULL,	[GID] [int] NOT NULL,	[AID] [int] NOT NULL,	[characterName] [varchar](24) NOT NULL,	[membership] [tinyint] NOT NULL,	[IP] [varchar](24) NULL, 

CREATE TABLE [dbo].[compositionLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[mapName] [varchar](25) NULL,	[x] [int] NULL,	[y] [int] NULL,	[addedCardID] [int] NULL,	[slot1] [int] NULL,	[slot2] [int] NULL,	[slot3] [int] NULL,	[slot4] [int] NULL,	[equipItem] [int] NULL,	[serial] [int] NULL, 

CREATE TABLE [dbo].[CLevelLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[AID] [int] NULL,	[GID] [int] NULL,	[charName] [varchar](24) NULL,	[clevel] [int] NULL,	[joblevel] [int] NULL, 

CREATE TABLE [dbo].[CharConnectLog](	[Index] [int] IDENTITY(1,1) NOT NULL,	[AID] [int] NOT NULL,	[GID] [int] NOT NULL,	[Membership] [tinyint] NULL,	[CharName] [varchar](24) NULL,	[Login] [datetime] NULL,	[LogOut] [datetime] NULL,	[ip] [varchar](18) NULL,	[Job] [int] NULL,	[cLevel] [smallint] NULL,	[JobLevel] [int] NULL,	[PlayTime]  AS (datediff(second,[Login],[Logout])), 

CREATE TABLE [dbo].[charblockinfo](	[GID] [int] NOT NULL,	[AID] [int] NOT NULL,	[BlockDate] [datetime] NOT NULL,	[ExpireDate] [datetime] NOT NULL 

CREATE TABLE [dbo].[characterLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[logdate] [datetime] NULL,	[action] [int] NULL,	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[ip] [varchar](20) NULL, 

CREATE TABLE [dbo].[changematerial](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NOT NULL,	[action] [int] NOT NULL,	[zsid] [int] NOT NULL,	[gid] [int] NOT NULL,	[mapname] [varchar](24) NOT NULL,	[x] [int] NOT NULL,	[y] [int] NOT NULL,	[itid] [int] NOT NULL,	[itcnt] [int] NOT NULL,	[itslot1] [int] NOT NULL,	[itslot2] [int] NOT NULL,	[itslot3] [int] NOT NULL,	[itslot4] [int] NOT NULL,	[itrefinglevel] [int] NOT NULL,	[itserial] [bigint] NOT NULL, 

CREATE TABLE [dbo].[bossmobhunting](	[idx] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[sprite] [int] NULL,	[name] [varchar](24) NULL,	[mapname] [varchar](16) NULL,	[x] [int] NULL,	[y] [int] NULL,	[type] [int] NULL,	[aid] [int] NULL,	[gid] [int] NULL,	[contribution_factor] [int] NULL,	[finalatk] [int] NULL 

CREATE TABLE [dbo].[UpdateCharLog](	[GID] [int] NOT NULL,	[CharName_Old] [varchar](24) NOT NULL,	[CharName_New] [varchar](24) NOT NULL,	[GDID] [int] NOT NULL,	[Position] [smallint] NOT NULL,	[RegDate] [datetime] NOT NULL 

CREATE TABLE [dbo].[statusLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[mapName] [varchar](25) NULL,	[x] [int] NULL,	[y] [int] NULL,	[str] [int] NULL,	[agi] [int] NULL,	[vit] [int] NULL,	[int] [int] NULL,	[dex] [int] NULL,	[luk] [int] NULL,	[jobPoint] [int] NULL, 

CREATE TABLE [dbo].[skillLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[mapName] [varchar](25) NULL,	[x] [int] NULL,	[y] [int] NULL,	[skillID] [int] NULL,	[skillLevel] [int] NULL,	[extraSkillPoint] [int] NULL, 

CREATE TABLE [dbo].[refiningLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[mapName] [varchar](25) NULL,	[x] [int] NULL,	[y] [int] NULL,	[success] [int] NULL,	[itemID] [int] NULL,	[itemLevel] [int] NULL,	[Serialcode] [bigint] NULL, 

CREATE TABLE [dbo].[ReAssemblyPacketLog](	[InsertDate] [datetime] NULL,	[Type] [int] NOT NULL,	[AID] [int] NOT NULL,	[GID] [int] NOT NULL,	[MapName] [varchar](16) NOT NULL,	[XPos] [int] NOT NULL,	[YPos] [int] NOT NULL 

CREATE TABLE [dbo].[presentLog](	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[mapName] [varchar](25) NULL,	[x] [int] NULL,	[y] [int] NULL,	[itemID] [int] NULL,	[date] [datetime] NULL 

CREATE TABLE [dbo].[PetLog](	[Index] [int] IDENTITY(1,1) NOT NULL,	[Date] [datetime] NOT NULL,	[GID] [int] NOT NULL,	[PetID] [int] NULL,	[PetName] [varchar](24) NULL,	[Modified] [tinyint] NULL,	[SpriteType] [int] NULL,	[Lv] [int] NULL,	[Fullness] [int] NULL,	[Relation] [int] NULL,	[Accessary] [int] NULL,	[mapName] [varchar](25) NULL,	[Action] [int] NULL, 

CREATE TABLE [dbo].[PCInfoUpdateLog](	[AID] [int] NOT NULL,	[SID] [int] NOT NULL,	[date] [datetime] NOT NULL 

CREATE TABLE [dbo].[mercenary_log](	[type] [int] NULL,	[date] [datetime] NOT NULL,	[account_id] [int] NULL,	[account_name] [varchar](25) NULL,	[character_id] [int] NULL,	[character_name] [varchar](25) NULL,	[job] [int] NULL,	[remain_life_second] [int] NULL,	[approval_monster_kill_counter] [int] NULL 

CREATE TABLE [dbo].[mdungeonLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NOT NULL,	[type] [int] NOT NULL,	[mdungeon_name] [varchar](61) NULL,	[party_name] [varchar](24) NULL,	[party_id] [int] NULL,	[create_zsid] [int] NULL, 

CREATE TABLE [dbo].[mapstatisticLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NOT NULL,	[name] [varchar](16) NOT NULL,	[zsvr] [int] NOT NULL,	[player] [int] NOT NULL, 

CREATE TABLE [dbo].[makingLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[accountID] [int] NULL,	[accountName] [varchar](24) NULL,	[characterID] [int] NULL,	[characterName] [varchar](24) NULL,	[mapName] [varchar](25) NULL,	[x] [int] NULL,	[y] [int] NULL,	[success] [int] NULL,	[itemID] [int] NULL,	[meterial1] [int] NULL,	[meterial2] [int] NULL,	[meterial3] [int] NULL, 

CREATE TABLE [dbo].[levelupandjobchange](	[idx] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NULL,	[aid] [int] NULL,	[gid] [int] NULL,	[type] [int] NULL,	[level] [int] NULL,	[joblevel] [int] NULL,	[job] [int] NULL,	[exp] [int] NULL,	[mapname] [varchar](16) NULL,	[x] [int] NULL,	[y] [int] NULL 

CREATE TABLE [dbo].[ItemList](	[id] [int] NOT NULL,	[name] [varchar](40) NOT NULL,	[type] [smallint] NOT NULL, 

CREATE TABLE [dbo].[IrMailLog](	[Idx] [int] IDENTITY(1,1) NOT NULL,	[LogDate] [datetime] NULL,	[TRXID] [bigint] NULL,	[Action] [int] NULL,	[MailOffice] [int] NULL,	[MailID] [int] NULL,	[Title] [varchar](40) NULL,	[ReceiverAID] [int] NULL,	[ReceiverGID] [int] NULL,	[SenderAID] [int] NULL,	[SenderGID] [int] NULL,	[Zeny] [int] NULL,	[IT_ITID] [smallint] NULL,	[IT_Count] [smallint] NULL,	[IT_Kind] [tinyint] NULL,	[IT_Property] [tinyint] NULL,	[IT_CreatorGID] [int] NULL,	[IT_bIdentify] [bit] NULL,	[IT_bBind] [bit] NULL,	[IT_bDamaged] [bit] NULL,	[IT_RefiningLevel] [tinyint] NULL,	[IT_Card1] [smallint] NULL,	[IT_Card2] [smallint] NULL,	[IT_Card3] [smallint] NULL,	[IT_Card4] [smallint] NULL,	[IT_SerialNum] [bigint] NULL,	[IT_HireExpireDate] [smalldatetime] NULL,	[IT_BonusATK] [tinyint] NULL 

CREATE TABLE [dbo].[Homun](	[homunID] [int] NOT NULL,	[Date] [datetime] NOT NULL,	[Describe] [varchar](16) NOT NULL,	[MasterName] [varchar](24) NULL,	[Name] [varchar](24) NOT NULL,	[Fullness] [int] NOT NULL,	[Relation] [int] NOT NULL,	[job] [smallint] NOT NULL,	[clevel] [smallint] NOT NULL,	[hp] [smallint] NOT NULL,	[maxHP] [smallint] NOT NULL,	[sp] [smallint] NOT NULL,	[maxSP] [smallint] NOT NULL,	[mapName] [varchar](16) NOT NULL,	[x] [smallint] NOT NULL,	[y] [smallint] NOT NULL,	[Str] [smallint] NOT NULL,	[Agi] [smallint] NOT NULL,	[Vit] [smallint] NOT NULL,	[Int] [smallint] NOT NULL,	[Dex] [smallint] NOT NULL,	[Luk] [smallint] NOT NULL,	[SKPoint] [smallint] NOT NULL,	[aid] [int] NULL,	[gid] [int] NULL 

CREATE TABLE [dbo].[zsvrstatisticLog](	[index] [int] IDENTITY(1,1) NOT NULL,	[date] [datetime] NOT NULL,	[zsid] [int] NOT NULL,	[player] [int] NOT NULL,	[npc] [int] NOT NULL,	[memory] [int] NOT NULL,	[totalmap] [int] NOT NULL,	[instantmap] [int] NOT NULL, 

CREATE TABLE [dbo].[zenychecksum_log](	[date] [datetime] NOT NULL,	[zsid] [int] NULL,	[aid] [int] NULL,	[gid] [int] NULL,	[valid_zeny] [int] NULL,	[invalid_zeny] [int] NULL 

Some may already exist in hercules.

Soon I will post logs of global_reg_value of aegis.

 

[Beret]

There are also some fields in some tables the more in aegis for example.

CREATE TABLE [dbo].[account](	[AID] [int] NOT NULL,	[Name] [char](20) NOT NULL,	[Address] [char](200) NOT NULL,	[Phone] [char](50) NOT NULL,	[HPhone] [char](15) NULL,	[RegNum] [char](14) NOT NULL,	[zipcode] [char](10) NOT NULL,	[sex] [tinyint] NOT NULL,	[Email] [varchar](50) NOT NULL,	[http] [varchar](50) NULL,	[News] [tinyint] NOT NULL,	[job] [varchar](40) NULL,	[hobby] [varchar](40) NULL,	[game] [varchar](40) NULL,	[comment] [varchar](1000) NULL,	[nation] [varchar](20) NOT NULL,	[ip] [int] NULL,	[lasttime] [datetime] NULL,

CREATE TABLE [dbo].[login](	[AID] [int] IDENTITY(100001,1) NOT NULL,	[ID] [varchar](20) NOT NULL,	[passwd] [varchar](16) NOT NULL,	[LastConnecTime] [varchar](20) NULL,	[grade] [tinyint] NOT NULL,	[isConfirmed] [tinyint] NOT NULL,	[regDate] [datetime] NULL,	[endblockdate] [smalldatetime] NULL,

CREATE TABLE [dbo].[PartyBooking](	[Index] [int] IDENTITY(1,1) NOT NULL,	[GID] [int] NOT NULL,	[MapID] [smallint] NULL,	[Level] [smallint] NULL,	[Job1] [smallint] NULL,	[Job2] [smallint] NULL,	[Job3] [smallint] NULL,	[Job4] [smallint] NULL,	[Job5] [smallint] NULL,	[Job6] [smallint] NULL,	[ExpireTime] [datetime] NOT NULL,

 

[KeiKun]

CREATE TABLE [dbo].[zsvrstatisticLog]( [index] [int] IDENTITY(1,1) NOT NULL, [date] [datetime] NOT NULL, [zsid] [int] NOT NULL, [player] [int] NOT NULL, [npc] [int] NOT NULL, [memory] [int] NOT NULL, [totalmap] [int] NOT NULL, [instantmap] [int] NOT NULL, 

^

interesting

 

[Neo-Mind]

zsvr??

 

[Jedzkie]

zsvr??

zsvr = ZONE SERVER

 

[Ind]

(there are too many tables I'll post individually on each whenever I have time/the patience to research)

ItemLog

I think its overly redundant (as in saves data that is provided elsewhere and thus just duplicates them).

We have index, no idea whats Ver/SID, we have action/logtime, I find ip unnecessary/wasteful (if you want the ip you can check whoever logged in on that account at such time with loginlogs, if you find that action tiresome you can write your control panel to mix the two data when looking at picklogs), no idea whats eventx/y, I don't see why it needs srcAccountID/srcAccountName/srcCharName/srcJobType (same for destination) while the char id from both parties is enough to retrieve all such data from other tables (again if you find the action tiresome you can have your control panel link the data for you), ItemName I find wasteful (you can cross with your item db to figure the name or have your control panel do that), I don't see the point of price while there is zeny log (and the action of such item in picklog would be as purchased, then just need to lookup the timestamp in zenylog -- again if its a tiresome operation your control panel can be set to do it for you), with that in mind, same for auctionID, ItemBuyCash too, same for zeny, no idea whats DestZeny for, don't see the point of storing ItemHireExpireDate (expire_time) on itemlogs -- to begin with expire_time items are not tradeable and if you wanna log when someone got them whats the point? their duration is set wherever they're obtained, no idea whats bizType.