CS541 – Project Log
9-17-2011:
Proposed project idea. Going to create a server management utility in Java for the EQEmu Open Source game server that uses mysql for it’s database.
Links:
EQEmu Server Project (Server Core)
PEQ Database (Server Database)
Maps Checkout
Quests Checkout
Located Database Schema and started to look it over:
Requirements:
The server management utility will need to be able to connect to the MYSQL database with read/write permissions, it will also need to be able to communicate with the server over a telnet connection, and it will need a console area to display output.
9-18-2011:
Setup Eclipse 3.5.1 on my computer and created a workspace for the project.
Located some Java libraries to help with development, and set them up in eclipse to be used in the project
Simple Java Console (Swing)
Apache Commons Net (Includes Telnet Support)
MySQL Connector / JDBC
Began working on coding the user interface with Swing.
9-20-2011:
Resumed work on coding the UI.
Started to practice some queries against the database in the mysql command line client.
Did some research on how to use JDBC to connect to a MYSQL database within a Java application, found this article.
9-27-2011:
Finished up the GUI for my program. I integrated the Java console component into the project, however I cannot seem to get it to display within the main program window. Currently it displays as a separate window as you can see in the screenshot below. It works for now, but for aesthetics it will need to be moved to within the main window in the output area. I also started to implement some of the server telnet commands with the Apache Commons Net library.
9-29-2011:
Compiled new version of the game server, and updated the database to the latest version.
10-04-2011:
Worked on getting a test server installed on my laptop so that I can use it for further development as well as demonstration without messing up my production server. The database was imported successfully however I am having a hard time getting the server code to compile correctly on a 64 bit platform. My production server is x86 which the server code is optimized for.
10-07-2011:
Still no luck at a 64bit server install. Started to do more research on how to do insert and update statements with JDBC. I have no issues querying the database for information but cannot seem to figure out how to insert or update data. Seems that prepared statements may be the key.
After a bit more searching I found this bug report about the 64 bit compiling. However after removing the -Di386 from the makefiles I still had the same issue, the code compiles however I am getting the following error when loading a character into the game:
kernel: [ 1612.707807] zone[29038]: segfault at 0 ip
00007f49b3208c60 sp 00007fffad0c0e00 error 6 in
libstdc++.so.6.0.14[7f49b319e000+eb000]
Found this thread that says to remove the -O flags from the makefiles that have them to fix an issue with 64 bit gcc and stringstreams. This fixed my issue!
Setup development server on laptop and cleaned up my imported production server database so I had a clean database to work with.
Performed the following queries to clean it up:
use eq; delete from account where id>0; delete from account_ip where accid>0; delete from character_ where level>0; delete from adventure_stats where player_id>0; delete from botbuffs where BotId>0; delete from botinventory where BotId>0; delete from botpetbuffs where BotPetsId>0; delete from botpetinventory where BotPetsId>0; delete from botpets where BotId>0; delete from bots where BotId>0; delete from character_activities where charid>0; delete from character_backup where charid>0; delete from character_tasks where charid>0; delete from completed_tasks where charid>0; delete from eventlog where status=250; delete from guilds where id=1; delete from guild_bank where guildid=1; delete from guild_members where guild_id=1; delete from inventory where charid>0; delete from keyring where char_id>0; delete from player_corpses where charid>0; delete from sharedbank where acctid>0; delete from tblLoginServerAccounts where LoginServerID>0;
Created an admin user and a regular user at the mysql command line:
insert into tblLoginServerAccounts (AccountName, AccountPassword) values ("eqadmin", sha1("pass"));
insert into tblLoginServerAccounts (AccountName, AccountPassword) values ("ronny", sha1("pass"));
Created a testing character on the normal account and granted GM privileges to the admin account so it can be used for telnetting to the server within the frontend I am creating.
UPDATE account SET status='255' WHERE id='14';
10-16-2011:
Figured out how to use JDBC prepared statements to do SQL inserts. Also had to do some research on how to insert the current timestamp into a table with a prepared statment.
Also figured out how to hash an account password with SHA1 Hashing in a JDBC prepared statement used for login server account creation.
10-21-2011:
Finished writing JDBC prepared statements for login account managment, guild management, password management, and character management. I also cleaned up the comments in my code to make things more clear.
10-22-2011:
Added a new command that allows the program to list a character’s inventory. I had to use a join in order to complete my task. The Java statement code is based on this sample query I worked up.
SELECT Name FROM items i JOIN inventory v on i.id=v.itemid WHERE v.charid = (SELECT id FROM character_ c WHERE name = 'charName');
Which produces a list like this:
+----------------------------------+ | Name | +----------------------------------+ | Kobold Skull Charm | | Bone Earring of Evasion | | Helm of Enlightenment | | Eyepatch of Plunder | | Garudon Boneshard Earring | | Ritual Talisman of Fate | | Spirit Wracked Cord | | Gravedigger Arm Straps | | Battleleader Ceremonial Cape | | Wrist Guard of Thunder | | Shackle of Tynnonium | | Fist of the Grand Master | | Whistling Fists | | Golden Wraps of the Compact | | Velium Coldain Insignia Ring | | Terror Chain Ring | | Lunar Fungus Covered Tunic | | Flayed Barbarian Skin Leggings | | Slippers of Enlightenment | | Spiked Seahorse Hide Belt | | Reusable Steambomb | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Fetish of the Agile | | Leggings of Humility | | Celestial Sword | | Fistwraps of Humility | | Sandals of Humility | | Earring of Z | | Grand Master's Chest Wraps | | Grand Master's Wrist Wraps | | Intricate Wooden Figurine | | Fist of Iron | | Greatstaff of the Four Winds | | Master Wu's Trance Stick | | Tranquil Staff | | Fisherman's Companion | | Choker of Battles | | Robe of the Whistling Fists | | Trunt's Head | | Iron Ration | | Iron Ration | | Iron Ration | | Water Flask | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Leatherfoot Haversack | | Planar Sewing Kit | | Leatherfoot Haversack | | Leatherfoot Haversack | | Cloak of Flames | | Regal Band of Bathezid | | Mask of Fright | | Grand Master's Leg Wraps | | White Lotus Pants | | Grand Master's Slippers | | Grand Master's Arm Wraps | | Coldain Head | | Kromrif Bones | | Dirk of the Traitor | | Key to Charasis | | Trakanon Idol | | Hand of Glory | | Key of the Misplaced | | Key of Swords | | Druushk's Assistant's Arm | | Druushk's Assistant's Jaw | | Staff Shard of Luclin | | Tome of Silentfist Discipline | | Tome of Hundred Fists Discipline | | Tome of Inner Flame Discipline | | Tome of Phantom Echo | | Tome of Voiddance Discipline | +----------------------------------+
I also completed a list bots command that I added to the character drop down menu. The Java statement was based on the following query:
SELECT c.Name,b.Name,b.Class,b.BotLevel FROM bots b JOIN character_ c ON b.BotOwnerCharacterID=c.id ORDER BY c.Name;
I needed to use a case switch in order to change the class id returned into the actual class name, which is not located anywhere in the database and is usually handled in code on the game clients side.
11-05-2011:
Wrapped up all the loose ends in the application and tested everything over the weekend.
11-12-2011:
Began working on putting together my project presentation.
11-18-2011:
Finished up presentation and packed up code for distribution.
The latest project source code can be obtained by using subversion with the following command:
svn checkout \ http://cubbers-eqemu-utils.googlecode.com/svn/trunk/EQEmuJSM EQEmuJSM
Note that the project was built in eclipse and the file structure is setup as such.
If you wish to only view the latest code then you can do so at the following links:
