Too much coding, not enough beer...

Finished the Dijkstra script about an hour ago. The database was trickier than expected (of course) so it is good I didn't stay up and try to hack it. And lucky for me that I have a well trained Phil that still answers when he sees my number. I suggest everyone out there in internets world gets their own Phil.

So tables differ from spreadsheets (duh, but stay with me). Building a table of distances in a spreadsheet would be a matter of X rows and X columns, one each for a location. You could find the distance A->B by looking in Column B of Row A. When it comes to SQL, computers like rows much better than columns. Something to do with the linear nature of seraching and itemization or something. The end result is that MySQL does not support 5,000 columns and would rather like three columns and 25+ million rows. 

The columns are Source, Destination, Number of Jumps. And there will be a row for each Source/Dest combination. (Here we can get cute and since the distance is the same in eaither direction we can keep the table half as long, as 12.5 million rows.)

I also optimized the script a bit by only using HiSec systems as starting points. There will still be routes to LoSec where these neighbor HiSec, but I gave these routes an extra 1,000 jump weight per LoSec. This should keep them out of the way. Pretty much if you see a route that is over 1,000 jumps, ignore it.

I started the script about 20 minutes ago and it has found 40,000 routes and closed 8 solar systems. The further it gets, the quicker the searches (as the return route has been calculated) so I expect it to be done in 3 weeks. Yeah, that sounds horrible but we'll see if Phil can improve it?

Hot Damn! Bring on the space babes!

After hours of work and many many mistakes, I've finally conquered the Dijkstra algorithm.

About six hours ago I decided to tackle the route distances. I began by porting some of the Eve map databases over to my production database, and then worked on creating a Graph. That probably took up to two hours. The real bitch was working with the Dijkstra code lying on the internets.

Most references want me to use PriorityDictionaries so I spent an hour or so trying to figure them out. I had a ton of python editors full of gibberish. In the end I resorted to the wikipedia Dijkstra site (http://en.wikipedia.org/wiki/Dijkstra%27s_algorithm). They have a pseudo-code logical outline for a solution.

My version would run through once, find the starting location (as it should) and then die (as it shouldn't). I thought maybe I can't alter a variable that is running a while statement I'm in, so far too much time was wasted tyring to solve that. The real problem was a variable that wasn't being redefined. Pretty much after it found the starting point (distance = 0) it would then look for something closer, and since there isn't it would die.

A quick "cut and paste" later and the shell started spewing out distances. I only checked one (Jita to Sizamod) against the Battle Clinic site (http://www.battleclinic.com/eve_online/route_finder.php) but it came up accurate (15 jumps in Hi-Sec). So I count that as a win!!

With Dijkstra written I need to define a distance table. This will have a column for each solar system and a row for each solar system. Should be about 12.5M entries. I'm jazzed and want to do it now, but I should get some sleep. And who knows how long the DB part will take to code??

Night all..

Just got back from watching The Pharaoh Serket and the Stone of Fire. A bunch of us went to see Whitney's play at the Seattle Children's Theatre. It was very very good, with the sets and changes being some of the best parts. 

Market Analysis Tool

The database has 77k orders in it. I just ran the Trade Finder looking for deals worth more than 10M ISK assuming I'm flying a Badger and have 50M ISK to trade with. This would give me a 20% return on investment!

The script pared 298k deals in 2 1/2 minutes and found 626 transactions that made more than 10M ISK. Many of these were Isogen or Noxium but a few were for Capacitor Consoles, and there were worth in the 15M-17M range. Though I haven't checked to see if they are in low sec or very far apart.

End result? The finder works, it may take some time but it does complete in the less than 5 minutes I was aiming for. Will have to see how it holds up as the database gets larger. Adding a function to exclude orders older then X hours would help with day to day stuff. Plus I could have it auto find every so many minutes or just for those orders that get imported each time? That last bit would be very slick. Maybe maintain a priority database of orders that is updated every every few minutes? Then when hit by a user it would just parse this list and use the users data? 

Not sure how I would implement that. Would I have to hard code the capacity and a minimum ISK value? That would assume the user would not have more ISK or cargo capacity. Just some thoughts. In the end this could make it faster but might miss some trades?

This weekend I hope to work on Dijkstra. But tomorrow might end up being Rock Band...

(BTW I have enabled comments, didn't know I needed to)

Plenty of deals... Plenty of work.

The database has grown to 40k orders. Still small, but the Trade Finder is able to pull 738 deals worth over 5M ISK already, 289 over 10M ISK, and 40 over 15M ISK. Of course there is no way of knowing if they are in low sec or how far you'd have to travel, but as a proof of concept I'm happy. Trade Finder takes about 30 seconds to run through all 40k orders (reminds me to add a line to count deals analyzed, and get a stop watch).

The next task is adding jump distance and security levels. 

BTW it snowed again last night. ug. 

A small setback and then Victory!

Hey, why are all my orders set as bids? I had a mistake in how the script assigned whether an order was a bid (buy order) or not (a sell order). The problem was case sensitivity. I was looking for ALL CAPS so assigned everything as a sell order. This was fixed and I'm rebuilding the database one more time (no way to tell what the existing orders were)! So far it has 10,000 orders.

Cracked the 'getting stuff out of python' problem and was able to write the Trade Finder script in under an hour. Having the Google Docs file was great. This let me get my ideas down and in a decent form so once I figured it out just needed to add a few lines and correct for typos.

Still plenty to be done though. I currently have to hard code my cargo capacity, ISK reserves, and Profit Expectations. Plus the output is just a list of those trades that match or exceed my expectations. So it needs love and attention on the input and output side, but the engine looks like it is humming along.

Might test it in the morning and see how long it takes to crack a larger database.

Tomorrow I'm gonna go watch Whitney's play with some of the crew, so we'll see if I get to the code.

State Of The Code:

Started using google-docs to store and edit the python scripts. This allows me to look at them during lunch. Still pondering how to get data out of MySQL and into python. I think it will require me to use the python library mysqldb and create a new class of objects to represent tables. Just more things to cram into the brain. I was really hoping it would've been possible using what I've already learned.

Additionally the script will need sys.argv to import user preferences (maybe even store them in a profile?) and priority dictionaries to easily sort results. The list keeps growing.

Things to Learn

• mysqldb
• creating a new class (table)
• priority dictionaries
• importing user arguments (sys.argv)

Resources: http://www.palewire.com/2008/04/26/python-recipe-connect-to-mysql-database-execute-a-query-print-the-results/

Early in the morning....

Had to restart the python script this morning. Looks like it stopped just before 2am. I might need to create some sort of windows job that checks if the script is running and kicks it as needed?

Yesterday I wrote a 1/2 python 1/2 logical program for finding trades. Over the next few days I need to understand how to get lists from MySQL into python and how to use priority dictionaries. Then I can begin implementing and testing the trade finder.

Off to work, part two of the Faxitron install.

Moved the python scripts to a production environment last night and forgot to restart them. Had 1,259 emails in the mailbox this morning (over 9 hours). Just ran the script and it inserted 60,000 orders in about two minutes. Looks like the program is pretty robust!

I'm heading into work early for some new equipment in the OR, if the day gets slow I may create a logic diagram of the MySQL for finding best orders.

Realized I copied the wrong database into eveproduction.orders. The one I was using did not include fields for a UID or TimeStamp. I deleted the database and recreated with the proper fields.

The original database was running for just over 24 hours, here are some numbers from just before it was dropped:

• Total Orders in Database: 114,836
• Total Regions Represented: 30
• Total Items Represented: 1,577
• Vast majority of items have less than 400 orders in system
• 8 items have over 700 orders in system
• 1 item had over 1,000 orders (1,114)

Can't tell the oldest order or how many total orders were imported without the timestamp and UID.

While the new database fills up I will work on the MySQL queries.

Chicago or Bust!

After 12 hours the database contains 63,575 orders. Now what the hell do I do with them?

Now gonna get breakfast with Jonathan and drive him to the airport for his Mid-West artage thing. Tonight will be the gym and cleaning so not much Market Analysis.

The weekend closes on goodness....

After fighting a hangover and watching some basketball I cleaned up the Market Analysis Tool. Carl spent a couple of hours getting me a mail server that won't choke on the Eve Central dumps. And I set to making a production import tool.

As of now the Import Tool is working. The basic functions are as follows:

• Create an IMAP connection to the email server
• Select those emails from Eve-Central
• Parse these emails for orders and delete the mail
• Connect to the MySQL database
• Purge old orders that are being updated (those with the same Items and Region as the new ones)
• Insert the new orders
• Sleep for 3 minutes and repeat

The script has been running about two hours and has 28,576 orders in it. Not sure how large it will get or if the script will kill my PC. Stay tuned.

This week I'll work on creating the MySQL queries that should provide actual trade data.

A beautiful day....

It is amazing out so I'm not gonna waste too much time indoors.  Maybe that is just the excuse I'm using to avoid cleaning up this place?

Market Analysis Tool progress:

Added a clean-up routine to purge out-dated orders.

Imported the CCP database dump! (That was a pain)

Carl promised to set me up with an email account to get the Eve-Central files.

Next Steps:

Once the email is ready, test it and start importing.

Test algorithms on the database.

Parse the CCP dump in anticipation of the Dijkstra routine.

Figure out how in hell to run Dijkstra.

I've learned a lot so far, and my head feels swollen. Lot's left to do and understand.

Hot Tip #2:  The world is full of hard working fools who are standing on the shoulders of intellectual giants. Be the lazy grasshopper who steals results from those fools.

The Brave New World starts today...

I'm currently working on a market analyser tool for Eve Online. This latest project has seen me dabble in programming (python and MySQL). Ack, the brain it does hurt so. While drafting another team status report it hit me that the whole thing would work better as a blog. And nows I gots one.

OK, Eve Market Analyser:

This is actually a collection of tools focused on collecting, mining, and displaying economic data. Some of the expected products are Trade Finders, Market Tickers, Buy and Sell Alerts, and Trending Snapshots. Many of these have been created by other entities in Eve but none tailored to our specific needs. And the nature of Eve keeps people from sharing much of their information. (Actually we are dabbling in this cut-throat nature by using Eve-Central's public market feed while directing our market order dumps only towards our own private database.)

Hot Tip #1: Information doesn't yearn to be free, it yearns to manipulated, twisted, and fucked with in the back of an expensive car like a hot bi-polar girlfriend with father issues.

I'm focusing on the Trade Finder first. Partly because it already exists in a form so it is easier to create, and also because I expect it has a solid chance of producing results. The Trade Finder will import all market orders from Eve-Central along with from our private contributors and mine this data for the best trades universe wide. As opposed to Eve-Central where you must select two regions to analyze this will parse everything. Though taking longer I don't expect it to server nearly as many users. Eventually I'd like to to accept a pilot's location, cargo capacity, and ISK for trading, and calculate the best ISK/Jump trade from there. Maybe even move towards an ISK/sec calculation someday.

OK, this got much longer than expected. I supposed the first post will have a lot of background. Next time I expect to stay closer to a State of the Code address.

Ciao'
The Dystopian