. mysql « Torchwood Archives
Torchwood Archives
16Feb/15Off

LMeve dev blog: important fix in dates, proxy and one more thing

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:

lmeve-cdn-stats-solarized-light

Happy ship painting!

quafe-domi

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".

lmeve-cdn-stats-solarized-dark

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 😉

23Oct/14Off

LMeve dev blog: tuning SQL performance and EVE SSO

Hello industry moguls and third party devs of EVE o7

First off, I'd like to say I respect Rixx Javix greatly for his last post about EVE industrialists and his respect towards them. Much appreciated, dear sir! But today's dev blog post will be about three other things. First is a solution to the performance problems that Aideron Technologies instance of LMeve was having recently. Second is an announcement of a new feature, which thanks to @CCP_FoxFour will soon become widely available: ability to log in to LMeve using your EVE Online credentials (EVE SSO). Last but not least, I've added the new Blueprints.xml API endpoint.

Ohnoes, LMeve slows down!

I've got numerous complaints from my corp members recently about LMeve being slow and unresponsive. It was apparent on many subpages, so at first it was hard to pinpoint. Our database has grown over the past two years, and I thought this performance hit was caused by the amount of records we have accumulated:

lmeve-db

When you look at it, there is a few hundred thousands records there. But usually we only need to display the current month, and the rest has to be filtered out:

lmeve-graphs-1

This prompted me to have a look at how I filter out data by dates to show the current month only:

SELECT (...) FROM (...)
WHERE date_format(beginProductionTime, '%Y%m') = '${year}${month}'
AND (...);

At first glance it looks completely logical (and it actually works). I know the current month and year, so I only select records that have their year and month equal to the current one. But look closely what I've done there. I convert a DATETIME field "beginProductionTime" to a string which shows year and month in YYYYMM format, and then I compare this string with another string. As you probably know, comparing strings is much slower than comparing numbers (it's why in databases you use indexes on text fields that need comparison). But I made it even worse; besides comparing strings, I converted DATETIME, and I did that for each and every record encountered by the query!

Here's a piece of advice when you write an SQL query that sifts through a lot of data:

  • don't run unnecessary functions or conversions on database fields that you use in WHERE clause
  • create indexes on TEXT and VARCHAR fields if you have to compare them

Each function which you put in there will have to execute for every record in the table, which will adversely affect performance.

When you take the two rules above into account, fixing my queries was pretty simple and straightforward. Instead of converting DATETIME values to string, I converted the compared string into a DATETIME:

SELECT (...) FROM (...)
WHERE beginProductionTime BETWEEN '${year}-${month}-01' AND LAST_DAY('${year}-${month}-01')
AND (...);

See? The values I compare beginProductionTime against are only evaluated once, instead of for every record, which means the whole SQL executes way faster than before.

Results:

Old query execution time: 37.4 seconds

New query execution time: 0.87 seconds

Op success! o7

Damn, I forgot my LMeve password again

lmeve-ssoWell, this kind of excuse will not be possible anymore, because thanks to @CCP_FoxFour and the limited EVE SSO trial, LMeve can now benefit from the SSO mechanism. CCP FoxFour explained SSO in detail in his dev blog here. How does this work?

  • LMeve redirects user to SSO at https://login.eveonline.com/
  • User logs in with his EVE Online login details and chooses one of the characters
  • EVE SSO redirects user back to LMeve
  • LMeve confirms with the SSO server that the login is valid
  • LMeve accesses the character name and ID and checks if that character is allowed to use LMeve (character must belong to the corporation configured in LMeve, and that character must also be linked to an LMeve account on "Characters" page. Users can do it on their own using their API keys.)

This is of course simplified exchange, because special token and secret values are also exchanged to make sure that nothing got spoofed on the way.

SSO feature for LMeve has already been developed and tested, but to set it up in your own instance of LMeve, you will need a special "client_id" and "secret" values. These values are similar to API keys, and you will be able to generate them  on the new 3rd party developer page (beta of this page working with Singularity server is already available).

New API endpoint

The last feature is the addition of Blueprints.xml API endpoint, which returns data about all the blueprints owned by a corporation, both originals and copies. Information retrieved from it contains ME and TE levels. Previously LMeve users had to input ME and TE manually for each blueprint owned by the corporation. With this new endpoint LMeve does all this automatically. This way all cost predictions made by LMeve always use up to date ME levels.

That's all for today!

You can download the latest LMeve version from GitHub.

25Jan/14Off

Rubicon 1.1 Static Data Dump conversion

DevFleetHi #DevFleet! CCP has again published the static data dump before deploying expansion to Tranqulity. I would like to thank @Darkblad_eve here, who sent me and @FuzzySteve a heads up tweet on Twitter last night about new SDE being available. It seems CCP has a new procedure: SDE is deployed on friday immediately preceding the usual tuesday expansion deployment on Tranquility (only database is available for now, icons have not been published yet). Anyway, today I had a few spare mintues to run the conversion. Please find the results here:

You might want to get the new "Ship traits" -> Head to Fuzzy Steve's page (look for invTraits).

The original MSSQL data dump is available on the official Toolkit page.

LMeve Database updated

The item database has been updated to use the new Static Data dump as well. For example: Nestor stats are up to date now, and Deployable Structures have been move to Structures group, along with other POS and Outpost modules. When you head there, you will find all the new deployables (they will be missing icons until CCP publishes them, though).

17Nov/13Off

Rubicon 1.0 Static Data Dump conversions available

DevFleet

This post is entirely for the #DevFleet out there. CCP have outdone themselves by publishing static data dump for a new expansion 4 days before actually deploying it on Tranqulity! It was already available on the Toolkit page last friday.

It means that we - the third party developers - had even more time to convert and adapt the data to our projects. Unfortunately it was not so easy to convert the data this time. As most of you know, CCP has recently switched from SQL Server 2008 to SQL Server 2012, which means the official Static Data Dump has now SQL Server's 2012 media format. To make the ride even more rough, SQL Server 2012 only runs on Windows Vista and above, and Windows Server 2008 R2 and above. In order to make my development VM compatible with the new Static Data Dump, I had to set up a clean new VM with Windows Server 2008 R2 and the new database version. Fortunately it is possible to do all that legally, because Windows Server 2008 R2 is available as a 180 day trial (which can then be rearmed twice to extend the trial even further), and you can use the Express version of SQL Server without paying a dime.

Now the results!

SQL Server -> MySQL & SQLite:

YAML -> MySQL:

YAML Certificates schema

YAML Certificates schema

I have also updated the LMeve Database to use this new dump, feel free to have a look.

Type Icons

CCP has only published a data dump, with icons and renders (probably) coming soon. Don't worry, I have prepared a "delta" Rubicon icon pack. Simply copy it on top of your existing Odyssey icons:

Icons and renders have now been updated by CCP. Go to the Toolkit page to fetch it.

Feedback

If you find any issues with these dumps, please let me know. This is the first dump from SQL Server 2012, so it is possible some things can be different either in schema or in the data itself. LMeve works just fine, but that's just me.