- Joined
- Oct 22, 2008
- Messages
- 75
- Reaction score
- 143
Here's some stuff I talked about in previous posts:
Cleanup.sql - This is my current WIP script for cleaning up the existing data. There's nothing special about it, but it does go over some of the tables that you do and don't have to set. The point of this is to serve as a base script to setup stock servers from the uploaded files rather than doing a repack of the DB just yet.
KeyTransform.cs - This is a tool I wrote to work with the passwords of the zip files. The current password of the zip files in 99lunia is "JuhaniHonkala". However, the client stores the password as "JvjdrnNvvtkwm". This simple code does the encoding/decoding back and forth using their algorithm (simple, but a real PITA to find & reverse). An usage/example output would be:
Usage:
KeyTransform.exe -d JvjdrnNvvtkwm -e JuhaniHonkala
Output:
Decrypting: JvjdrnNvvtkwm => JuhaniHonkala
Encrypting: JuhaniHonkala => JvjdrnNvvtkwm
connectinfo.xml - This is the connectinfo.b that is used by the 99Lunia client dumped to XML. Someone mentioned another connectinfo.xml exists, but I don't have it to compare to see how different it is. I've only modified a few urls and settings, but they should be obvious. Some of the extra data is still unknown and I'm working through what it is used for. NOTE: To use it with your 99Lunia client, rename it to connectinfo.b, the client will handle it still. I'm looking for the settings to make the client read the XML rather than the .b still, but for now renaming works.
NewLuniaArchive.cs - This is my current WIP Lunia cfp/cpv extractor. There's still some work left to do with figuring stuff out, but for now it seems to be able to extract the files. It requires the "LZMA.dll" from the Lunia directory to run (put it in same folder as exe). After you compile it, you will want to run: "NewLuniaArchive <base name> <output dir>" from a folder with the cfp/cpv files where "base name" is the base name of the cfp/cpv (such as Animation, Database, Locales, etc..) and "output" is the directory to extract to.
ChineseT.zip - This is just the current Lunia global's Locales files zipped up using the right password. Some stuff is not translated because this is not a merge, it's simply a replacement. You will need to go through and add any missing or untranslated defines to continue working, but it cuts down on a lot. Make sure you don't change the password! (Unless you know how to change it in the client or remove it, I know neither yet).
Link:
Zip Password: JuhaniHonkala
Notes on server configs:
* You can replace all JP related console stuff with (if you want):
But I'm not sure if it really makes a difference or is used for anything.
* Setup wise, you just have to fix addresses and make sure the ports are linked correctly. The files as-is are configured pretty well, they just need some minor editing. A full guide for everything will come later though.
* If you have not already, read this thread! It contains a lot of useful information. When it is mentioned the Stage/Square servers take a long time to start, they really do mean a long time. If you don't have a very high end PC/Server to work with, be prepared to wait (on my PC takes about 5 mins or so, on a VM, 10+).
* Web shop stuff is still being looked at, but it seems partially working.
* In terms of character classes and playing the game, the files seem complete and very stable. I've had no issues so far and I've been testing via VirtualBox. How everything will fare on a live server with more users is for future exploration.
Cleanup.sql - This is my current WIP script for cleaning up the existing data. There's nothing special about it, but it does go over some of the tables that you do and don't have to set. The point of this is to serve as a base script to setup stock servers from the uploaded files rather than doing a repack of the DB just yet.
Code:
/*
BEFORE YOU RUN:
I'm no MSSQL master, but this is what I have come up with from
researching the issue on how to cleanup tables with FK constraints.
There might be easier ways to do this, but this is what I have for now.
Stage 0:
- Change @SERVERNAME to the name of the server you define in your config
files. The variable comes after this large comment block.
Stage 1:
- Expand "Databases\v3_guild\Tables"
- Right click on "dbo.guild" and choose Design
- Right click on "guildId" and choose "Relationships"
- There should be one relationship, "FK_guildGrade_guild"
- For each relationship:
-- Expand "INSERT And UPDATE Specification" at the bottom
-- Change "Delete Rule" to "Cascade"
-- Change "Update Rule" to "Cascade"
- Hit "Close" and save the table (ctr + s)
- You will get a warning box about changes, hit "Yes"
Stage 2:
- Expand "Databases\v3_character\Tables"
- Right click on "dbo.Accounts" and choose Design
- Right click on "accountName" and choose "Relationships"
- There should be two relationships, "FK_Characters_Accounts"
and "FK_LobbyConnections_Accounts"
- For each relationship:
-- Expand "INSERT And UPDATE Specification" at the bottom
-- Change "Delete Rule" to "Cascade" if it is not already
-- Change "Update Rule" to "Cascade" if it is not already
- Hit "Close" and save the table (ctr + s)
- You will get a warning box about changes, hit "Yes"
Stage 3:
- Expand "Databases\v3_character\Tables"
- Right click on "dbo.Characters" and choose Design
- Right click on "characterName" and choose "Relationships"
- There should be six relationships, "FK_Characters_Accounts", "FK_Items_Characters",
"FK_Licenses_Characters", "FK_QuickSlots_Characters", "FK_SelectedCharacters_Characters",
and "FK_Skills_Characters"
- For each relationship:
-- Expand "INSERT And UPDATE Specification" at the bottom
-- Change "Delete Rule" to "Cascade" if it is not already
-- Change "Update Rule" to "Cascade" if it is not already
- Hit "Close" and save the table (ctr + s)
- You will get a warning box about changes, hit "Yes"
Stage 4:
- Expand "Databases\v3_character\Tables"
- Right click on "LobbyServers" and choose "Relationships"
- There should be one relationship, "FK_LobbyConnections_LobbyServers"
- For each relationship:
-- Expand "INSERT And UPDATE Specification" at the bottom
-- Change "Delete Rule" to "Cascade" if it is not already
-- Change "Update Rule" to "Cascade" if it is not already
- Hit "Close" and save the table (ctr + s)
- You will get a warning box about changes, hit "Yes"
*/
/*
AFTER YOU RUN (optional?):
Go through the above steps again, but change the "Cascade"
back to "None".
I'm not sure if this has to be done or not or what implications
arise if you do or don't.
*/
DECLARE @SERVERNAME varchar(50)
SET @SERVERNAME = 'MyServerName'
/********************/
use [Billing]
go
/* Remove all old data */
truncate table CardList
truncate table CardUseLog
truncate table CashPoints
truncate table CashUseLog
go
/********************/
use [v3_gate]
go
/* Clear existing data */
truncate table Connections
/* Add our new server's data */
insert into Connections(serverCode, serverName, connectionCount, lastUpdated)
values (126, @SERVERNAME, 0, GETDATE())
go
/********************/
use [v3_guild]
go
/* Remove all old data */
delete from guild where 1=1
delete from guildGrade where 1=1
truncate table family
truncate table familyMember
truncate table guildNexon
truncate table guildLevel
truncate table guildMaintain
truncate table guildMember
truncate table guildRank
truncate table guildShopProduct
go
/********************/
use [v4_stage]
go
/* Remove all old data */
truncate table ActiveStages
truncate table Connections
truncate table InActiveStages
truncate table LastPlayedStage
truncate table PvpChannels
truncate table PvpConnections
truncate table SquareStages
truncate table StageGroups
truncate table StageServers
/* NOTE: PvpChannels / StageServers are setup for us automatically! */
go
/********************/
use [v3_character]
go
/* Seems to be some temp table, I *think*... */
drop table aa
/* Remove all old data */
delete from Accounts where 1=1 /* This might take some time to run */
delete from Characters where 1=1 /* This might take some time to run */
truncate table SkillLicenses
truncate table BankItems
truncate table BankBags
truncate table Bags
truncate table WorkingQuests
truncate table CharacterRebirth
truncate table CharacterLicenses
truncate table Blocking
truncate table CompletedQuests
truncate table InvalidString
truncate table PlayTime
truncate table web_MailItems_History
truncate table web_Mails_History
truncate table LobbyConnections
truncate table PetItems
truncate table PetTraining
truncate table Pets
truncate table Event_PlayTime
truncate table Items
truncate table FriendList
truncate table Event_0901_3Year
truncate table CashItemStorage
truncate table Event_090827_ExpBox
truncate table AddedSkillPoint
truncate table jiangli
truncate table CharacterRename
truncate table Skills
truncate table Market
truncate table QuickSlots
truncate table Licenses
truncate table SelectedCharacters
truncate table Event_PcRoom
truncate table Event_ExpBox
truncate table KeySetting
truncate table Market_History
truncate table MailItems
truncate table Mails
/* TODO: Work this table's data out */
update Constants set CharValue=@SERVERNAME where KeyName='ServerName'
/* Note: LobbyServers is setup for us at runtime */
delete from LobbyServers where 1=1
go
/********************/
use [d-shop]
go
/* TODO: Work out and clean up this database
There's too much interconnected data to try and hard wipe it. The
SP's need to be gone through and used to cleanup. The shop's web
pages' data also needs to be examined during this process as well
for compatibility issues.
*/
/********************/
/*
I think this isn't needed since we truncated all the old data from the
database. I'll leave it in here for reference for future maintence though.
It will generate quite a few (harmless) errors.
use Billing
EXECUTE sp_MSforeachtable 'DBCC CHECKIDENT ([?], RESEED, 1)';
use v3_character
EXECUTE sp_MSforeachtable 'DBCC CHECKIDENT ([?], RESEED, 1)';
use v3_gate
EXECUTE sp_MSforeachtable 'DBCC CHECKIDENT ([?], RESEED, 1)';
use v3_guild
EXECUTE sp_MSforeachtable 'DBCC CHECKIDENT ([?], RESEED, 1)';
use v4_stage
EXECUTE sp_MSforeachtable 'DBCC CHECKIDENT ([?], RESEED, 1)';
*/
/********************/
KeyTransform.cs - This is a tool I wrote to work with the passwords of the zip files. The current password of the zip files in 99lunia is "JuhaniHonkala". However, the client stores the password as "JvjdrnNvvtkwm". This simple code does the encoding/decoding back and forth using their algorithm (simple, but a real PITA to find & reverse). An usage/example output would be:
Usage:
KeyTransform.exe -d JvjdrnNvvtkwm -e JuhaniHonkala
Output:
Decrypting: JvjdrnNvvtkwm => JuhaniHonkala
Encrypting: JuhaniHonkala => JvjdrnNvvtkwm
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace KeyTransform
{
class Program
{
static void Main(string[] args)
{
for (int x = 0; x < args.Length; x += 2)
{
String key = args[x + 1];
if (args[x] == "-d")
{
Console.Write("Decrypting: ");
String src = "";
for (int y = 0; y < key.Length; ++y)
{
char c = (char)(key[y] - y);
src += c;
}
Console.WriteLine("{0} => {1}", key, src);
}
else if (args[x] == "-e")
{
Console.Write("Encrypting: ");
String dst = "";
for (int y = 0; y < key.Length; ++y)
{
char c = (char)(key[y] + y);
dst += c;
}
Console.WriteLine("{0} => {1}", key, dst);
}
Console.WriteLine();
}
}
}
}
connectinfo.xml - This is the connectinfo.b that is used by the 99Lunia client dumped to XML. Someone mentioned another connectinfo.xml exists, but I don't have it to compare to see how different it is. I've only modified a few urls and settings, but they should be obvious. Some of the extra data is still unknown and I'm working through what it is used for. NOTE: To use it with your 99Lunia client, rename it to connectinfo.b, the client will handle it still. I'm looking for the settings to make the client read the XML rather than the .b still, but for now renaming works.
Code:
<allm version="(0,0)">
<ClientInfo type="ClientBasicInfo">
<GameCode value="94213" />
<ErrorReportAddress type="wstring" value="http://192.168.1.148/manage/error_report.asp" />
<ServerPopulationAddress type="wstring" value="http://192.168.1.148/Client/ListConnections.asp" />
<ResearchPingAndPCInfosAddress type="wstring" value="http://192.168.1.148/etc/client.asp" />
<GetPollAddressPage type="wstring" value="" />
<PollAddress type="wstring" value="" />
</ClientInfo>
<ServerTable type="associative_array">
<item type="pair">
<key type="int32" value="0" />
<value type="ServerInfo">
<Code type="uint8" value="0" />
<PossibleAge type="uint32" value="0" />
<Id type="wstring" value="pushedx1" />
<Name type="wstring" value="pushedx1" />
<DisplayName type="wstring" value="pushedx1" />
<DisplayTooltip type="wstring" value="Server pushedx1" />
<Addr type="wstring" value="192.168.1.148" />
<Port type="uint16" value="15550" />
<CashWebpageAddr type="wstring" value="http://192.168.1.148/g/encrypt.asp" />
<GuildPvPInfoAddr type="wstring" value="" />
<ShopInfoAddr type="wstring" value="" />
<PartyServerAddr type="wstring" value="192.168.1.148" />
<PartyServerPort type="int32" value="15557" />
<FamilyServerAddr type="wstring" value="192.168.1.148" />
<FamilyServerPort type="int32" value="15555" />
<GuildServerAddr type="wstring" value="192.168.1.148" />
<GuildServerPort type="int32" value="15556" />
<GuildIconUpload type="wstring" value="http://192.168.1.148/guild.aspx" />
<GuildIconSource type="wstring" value="http://192.168.1.148/showicon.aspx" />
<ItemToLinkTable type="associative_array">
<item type="pair">
<key type="uint32" value="11838961" />
<value type="wstring" value="oidProduct=1526" />
</item>
<item type="pair">
<key type="uint32" value="15310266" />
<value type="wstring" value="oidProduct=1525" />
</item>
<item type="pair">
<key type="uint32" value="16856317" />
<value type="wstring" value="oidProduct=1548" />
</item>
<item type="pair">
<key type="uint32" value="19897741" />
<value type="wstring" value="oidProduct=1549" />
</item>
<item type="pair">
<key type="uint32" value="36359512" />
<value type="wstring" value="oidProduct=1949" />
</item>
<item type="pair">
<key type="uint32" value="50646546" />
<value type="wstring" value="oidProduct=1550" />
</item>
<item type="pair">
<key type="uint32" value="53145434" />
<value type="wstring" value="oidProduct=1527" />
</item>
<item type="pair">
<key type="uint32" value="59815993" />
<value type="wstring" value="oidProduct=1528" />
</item>
</ItemToLinkTable>
<ChattingBlockUpdateUrl type="wstring" value="http://192.168.1.148/invalid-str/list.asp" />
<InGameNoticeUpdateUrl type="wstring" value="http://192.168.1.148/m/notice/list.asp" />
<ServerEventType type="uint8" value="0" />
<CashItemPreviewServerInfo type="AllM::XRated::LuniaBase::Info::SeverConnectInfo::CashItemPreviewServerInfo">
<AddressPreviewList type="wstring" value="http://192.168.1.148/g/preview_v1/preview_list.asp" />
<AddressHotList type="wstring" value="http://192.168.1.148/g/preview_v1/hot_list.asp" />
<AddressCategoryList type="wstring" value="http://192.168.1.148/g/preview_v1/category_list.asp" />
<AddressPurchase type="wstring" value="oidProduct=" />
<AddressDirectPurchase type="wstring" value="http://192.168.1.148/g/preview_v1/preview_info.asp" />
</CashItemPreviewServerInfo>
<PingProfileAddr type="wstring" value="http://192.168.1.148/crm/ping.asp" />
<TingProfileAddr type="wstring" value="http://192.168.1.148/crm/ting.asp" />
<LowerFrameProfileAddr type="wstring" value="http://192.168.1.148/crm/low_frame.asp" />
<DpsAddr type="wstring" value="" />
</value>
</item>
</ServerTable>
</allm>
NewLuniaArchive.cs - This is my current WIP Lunia cfp/cpv extractor. There's still some work left to do with figuring stuff out, but for now it seems to be able to extract the files. It requires the "LZMA.dll" from the Lunia directory to run (put it in same folder as exe). After you compile it, you will want to run: "NewLuniaArchive <base name> <output dir>" from a folder with the cfp/cpv files where "base name" is the base name of the cfp/cpv (such as Animation, Database, Locales, etc..) and "output" is the directory to extract to.
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
namespace NewLuniaArchive
{
class LuniaArchive
{
[DllImport("LZMA.dll", CallingConvention = CallingConvention.StdCall)]
public static extern int LzmaUncompress(byte[] dest, ref int destLen, byte[] src, ref int srcLen, byte[] propData, int propSize);
public class EntryChecksum
{
public String name;
public String md5;
}
public class EntryHeader
{
public const UInt16 DirPath = 0x5044;
public const UInt16 FilePath = 0x5046;
public Int32 DirCount { get { if (type != DirPath) throw new Exception("A DirCount property is not valid for this object."); return val1; } }
public Int32 FileCount { get { if (type != DirPath) throw new Exception("A DirCount property is not valid for this object."); return val2; } }
public Int32 FileOffset { get { if (type != FilePath) throw new Exception("A FileOffset property is not valid for this object."); return val1; } }
//public Int32 FileSize { get { if (type != FilePath) throw new Exception("A FileSize property is not valid for this object."); return val2; } } // not sure yet
internal Int16 type;
Int32 val1;
Int32 val2;
public String Name { get; set; }
public bool IsFile { get { return type == FilePath; } }
public bool IsDir { get { return type == DirPath; } }
public EntryHeader parent;
public EntryChecksum checksum;
public EntryHeader()
{
}
public EntryHeader(BinaryReader br)
{
type = br.ReadInt16();
val1 = br.ReadInt32();
val2 = br.ReadInt32();
Int16 len = br.ReadInt16();
if (len > 0)
{
Name = Encoding.Unicode.GetString(br.ReadBytes(len * 2));
}
}
}
void Recurse(EntryHeader parent, BinaryReader br)
{
for (int x = 0; x < parent.DirCount; ++x)
{
EntryHeader e2 = new EntryHeader(br);
if (e2.type != EntryHeader.DirPath)
{
throw new Exception("Invalid entry type - DirPath expected.");
}
e2.parent = parent;
entries.Add(e2);
for (int y = 0; y < e2.DirCount; ++y)
{
EntryHeader e3 = new EntryHeader(br);
if (e3.type != EntryHeader.DirPath)
{
throw new Exception("Invalid entry type - DirPath expected.");
}
e3.parent = e2;
entries.Add(e3);
Recurse(e3, br);
}
for (int y = 0; y < e2.FileCount; ++y)
{
EntryHeader e5 = new EntryHeader(br);
if (e5.type != EntryHeader.FilePath)
{
throw new Exception("Invalid entry type - FilePath expected.");
}
e5.parent = e2;
entries.Add(e5);
}
}
for (int x = 0; x < parent.FileCount; ++x)
{
EntryHeader e4 = new EntryHeader(br);
if (e4.type != EntryHeader.FilePath)
{
throw new Exception("Invalid entry type - FilePath expected.");
}
e4.parent = parent;
entries.Add(e4);
}
}
EntryHeader root_entry;
List<EntryHeader> entries = new List<EntryHeader>();
Dictionary<String, EntryChecksum> checksums = new Dictionary<String, EntryChecksum>();
String m_basename;
public byte[] Extract(EntryHeader entry)
{
using (FileStream fs = new FileStream(m_basename + ".cfp", FileMode.Open, FileAccess.Read))
{
using (BinaryReader br = new BinaryReader(fs))
{
br.BaseStream.Position = entry.FileOffset;
Int32 decompressed_size = br.ReadInt32();
Int32 compressed_size = br.ReadInt32();
byte[] propData = br.ReadBytes(5);
byte[] compressed = br.ReadBytes(compressed_size);
byte[] decompressed = new byte[decompressed_size];
int result = LzmaUncompress(decompressed, ref decompressed_size, compressed, ref compressed_size, propData, 5);
if (result != 0)
{
throw new Exception(String.Format("LzmaUncompress returned error code {0}", result));
}
return decompressed;
}
}
}
public EntryHeader GetEntry(String name)
{
String[] parts = name.ToLower().Split(new char[] { '\\', '/' });
EntryHeader p = null;
EntryHeader e = null;
int x = 0;
for (int y = 0; y < parts.Length; ++y )
{
String part = parts[y];
for (; x < entries.Count; ++x)
{
e = entries[x];
if (p != null)
{
if (e.parent != p)
continue;
}
if (e.Name.ToLower() == part)
{
p = e;
break;
}
e = null;
}
}
if (e != null)
{
e.checksum = checksums[name];
}
return e;
}
public List<String> GetFileNames()
{
List<String> names = new List<String>();
foreach(var kvp in checksums)
{
if (kvp.Key.Length > 0 && kvp.Key[0] != '/')
{
names.Add(kvp.Key);
}
}
return names;
}
public void Load(String basename)
{
m_basename = basename;
using (FileStream fs = new FileStream(m_basename + ".cfp", FileMode.Open, FileAccess.Read))
{
using (BinaryReader br = new BinaryReader(fs))
{
Int16 type = br.ReadInt16();
Int64 magic = br.ReadInt64();
Int64 header_offset = br.ReadInt64();
if (type != 0x4150)
{
throw new Exception("Invalid archive type - 0x4150 expected.");
}
br.BaseStream.Position = header_offset;
root_entry = new EntryHeader(br);
Recurse(root_entry, br);
}
}
using (FileStream fs = new FileStream(m_basename + ".cpv", FileMode.Open, FileAccess.Read))
{
using (BinaryReader br = new BinaryReader(fs))
{
Int16 type = br.ReadInt16();
Int64 magic = br.ReadInt64();
Int32 count = br.ReadInt32();
if (type != 0x4649)
{
throw new Exception("Invalid archive type - 0x4649 expected.");
}
for (int x = 0; x < count; ++x)
{
EntryChecksum e = new EntryChecksum();
UInt16 len = br.ReadUInt16();
e.name = Encoding.Unicode.GetString(br.ReadBytes(len * 2), 0, len * 2);
len = br.ReadUInt16();
e.md5 = Encoding.ASCII.GetString(br.ReadBytes(len), 0, len);
checksums.Add(e.name.ToLower(), e);
}
long p = br.BaseStream.Length - br.BaseStream.Position;
if (p != 0)
{
throw new Exception(String.Format("There are {0} bytes left to parse.", p));
}
}
}
}
}
class Program
{
static void Main(string[] args)
{
LuniaArchive archive = new LuniaArchive();
archive.Load(Directory.GetCurrentDirectory() + "\\" + args[0]);
String output = Directory.GetCurrentDirectory() + "\\" + args[1] + "\\" + args[0] + "\\";
List<String> filenames = archive.GetFileNames();
foreach (String filename in filenames)
{
try
{
LuniaArchive.EntryHeader e = archive.GetEntry(filename);
if (e == null)
{
Console.WriteLine("Error: The file {0} could not be found in {1}!", filename, args[0]);
continue;
}
byte[] bytes = archive.Extract(e);
if (bytes == null)
{
Console.WriteLine("Error: The file {0} could not be extracted!", filename);
continue;
}
Directory.CreateDirectory(Path.GetDirectoryName(output + filename));
using (FileStream fs = new FileStream(output + filename, FileMode.Create, FileAccess.Write))
{
using (BinaryWriter bw = new BinaryWriter(fs))
{
bw.Write(bytes);
bw.Flush();
}
}
}
catch (Exception ex)
{
Console.WriteLine("There was an error processing {0}{1}{2}", filename, Environment.NewLine, ex);
}
}
}
}
}
ChineseT.zip - This is just the current Lunia global's Locales files zipped up using the right password. Some stuff is not translated because this is not a merge, it's simply a replacement. You will need to go through and add any missing or untranslated defines to continue working, but it cuts down on a lot. Make sure you don't change the password! (Unless you know how to change it in the client or remove it, I know neither yet).
Link:
You must be registered to see links
Zip Password: JuhaniHonkala
Notes on server configs:
* You can replace all JP related console stuff with (if you want):
Code:
<ConsoleInput value="en" />
<LocalCharacterLowerBound_0 value="65" />
<LocalCharacterUpperBound_0 value="97" />
* Setup wise, you just have to fix addresses and make sure the ports are linked correctly. The files as-is are configured pretty well, they just need some minor editing. A full guide for everything will come later though.
* If you have not already, read this thread! It contains a lot of useful information. When it is mentioned the Stage/Square servers take a long time to start, they really do mean a long time. If you don't have a very high end PC/Server to work with, be prepared to wait (on my PC takes about 5 mins or so, on a VM, 10+).
* Web shop stuff is still being looked at, but it seems partially working.
* In terms of character classes and playing the game, the files seem complete and very stable. I've had no issues so far and I've been testing via VirtualBox. How everything will fare on a live server with more users is for future exploration.