Hi space-friends!

I won’t lie to you, I haven’t been playing much EVE lately. All the spare time that I was able to find was used to improve and fix LMeve. And Aideron Technologies found a big-badda-bug this time.

On the last saturday of January, a corpmate asked me why he can’t see the effect of his fire sales on the corp wallet graph in LMeve. It was the last day of January, and all graphs showed an empty bar for January 31st.

First I thought that poller might have stopped and we don’t get up to date API data. But I was quickly proven wrong, because I could clearly see all the recent market transactions in the database. “Must be a mistake in code that feeds database values to the graph” I thought. But when I checked that code, it only confirmed what I saw on the graph: that the value for the last day is indeed equal zero.

If the data is in the database, then it must be some kind of a presentation error; there is simply no way last day should show zero!

Let’s have a look at the database query used to retrieve the wallet data for a specific month. The clue must be there!

SELECT SUM(awj.amount)/1000000 AS income,date_format(awj.date, '%e') AS day FROM
apiwalletjournal awj
JOIN apireftypes art
ON awj.refTypeID=art.refTypeID
WHERE awj.date BETWEEN '${year}-${month}-01' AND LAST_DAY('${year}-${month}-01')
AND awj.corporationID=${corp['corporationID']}
AND awj.refTypeID <> 37
AND awj.amount > 0
GROUP BY date_format(awj.date, '%e')
ORDER BY date_format(awj.date, '%e');

Everything looks good from here, we SUM all transactions with positive “amount” field, and group them by the “day”. Of course I want output in millions of ISK, so I divide it by 1 000 000. Then we only select records between ‘$year-$month-01’ and the last day of the same month. Everything else is just filtering the correct corporation (because one instance of LMeve can monitor many corps at a time), filtering out player donations (refTypeID 37) and ordering the output by day, so we get a list of values for an entire month, from 1st day till the last.

How come this query will always return zero for the last day? Maybe that LAST_DAY() function actually returns last-but-one day?

SELECT LAST_DAY('2015-01-01');

2015-01-31

one row selected

Hmmm, it works exactly as advertized…

Or does it? Records in the wallet journal table don’t just have a DATE of the transaction, they have a full DATETIME! And what LAST_DAY() returns, is midnight on the last day of month! The entire timespan from midnight of that day until 23:59 that day is… well… filtered out. That explains why the last day was always missing.

How to fix it then?

Well, since we’re missing exactly 24 hours, it’s as simple as adding 1 day interval to the output of LAST_DAY():

WHERE awj.date BETWEEN '${year}-${month}-01' AND DATE_ADD(LAST_DAY('${year}-${month}-01'), INTERVAL 1 day)

LMeve CDN proxy

Now something else. As you know, I am now hosting T’Ambers ship painting tool – caldariprimeponyclub.com. In short, the problem was with the CCP CDN not setting Access-Control-Allow-Origin * in the header for users in AU and NZ regions (or some transparent proxy on the way stripped that header). Regardless, the end result was that T’Amber’s tool was unable to fetch 3D models and textures and ended up with a blank screen. I had a similar issue in LMeve a while back, and I made a very simple proxy in PHP, which would fetch the file from CCP CDN using curl, add the necessary CORS headers, and then send it back to the user.

The original proxy was really crude and could cause unnecessary network traffic, because every time user asked for file ‘A.png’ I would download file ‘A.png’ from CCP CDN, even if I have downloaded this file five seconds ago! Couldn’t I just save the file on disk, so I only retrieve each file once? Well, I’ve added the necessary logic, and the file is now saved in a cache, so I only retrieve each and every file once.

Now the second thing we needed was Analytics. It is a simple thing really: it’s statistics and visibility. I keep a log that contains information about the file (filename, path and its size in bytes) if the file was served from my local cache or retrieved from CCP servers, when it was retrieved, and the IP address of the client (which makes pretty much a standard proxy log). Once I have all this information, I can plot pretty graphs showing how many unique users were there on any given day, how many files they retrieved, and how much traffic they created:

Happy ship painting!

One more thing

As you have noticed on that last screenshot, LMeve has a brand new skin. That skin is not just a random beige palette. I recently learned of a color scheme which improves readability in text and programming editors, dubbed “Solarized“. That colour set contains 8 monotones and 8 accent colours which have several unique properties. First, they cause much less stress on the eyes, and second, they are displayed correctly on old and new displays, even on intentionally miscalibrated ones.

These two new skins have been added under Settings -> Preferences: solarized-light.css (above) and solarized-dark.css (best for night owls). They are way more minimalistic than original LMeve skins, and they don’t contain too many images, which makes them “work-safe”.

Hint: you can go further and remove all the images from LMeve. To do that, simply make a copy of your favourite skin .css file (they are in /wwwroot/css/ directory) and add this code:

img {

visibility: hidden;

}

This will hide all the images (character portraits, item icons and so on), but will retain the space that these images would normally occupy. This way LMeve remains completely usable, but becomes a “text only” application, that looks like everything but game 😉