EVE Technology Lab

Hey Guys,

So recently, one of the geekier threads in #tweetfleet on Twitter has been the discussion that EVE Stores stacks of items as an signed integer:


source

Long story short, a signed integer means a whole number that can represent values from -2m to +2m (roughly) depending on how much memory you give it.

If your familiar with programming, you will possibly be face palming a bit at this fact - after all, how can you have a negative stack amount ? You can't have -200 Erebus's stacked in your hangar.

Naturally, the thread has progressed and I offered my opinion that whilst the signing was possibly a little mad, from a SQL perspective, it makes a lot of sense, as the representation of a signed integer vs. an unsigned integer is 4 bytes rather than 8 bytes.

Sure you can use smaller types again, but the problem is then marshalling that between your Database and the game logic, and then considering you have to ensure that both your C++ and your Python code interpret the same value correctly.

If you're asking yourself why this is important at all, you need to understand that if you told SQL that your number field is an unsigned integer, it'll use 8 bytes of storage, *even if you only store a single digit value*.

If 1000 people in your alliance each have 1000 stacks, then it's not much but it adds up, that's 8mb (approx) of storage used simply to count how many items you have stacked.

CCP recenlly announce that they have reach 500,000 subscribers; assuming that they each subscriber has one account, and that account has 3 charactetrs with 1000 stacks each, you're talking now about 11gigbytes of storage used to represent how many items you have stacked.

Okay, the above is all theoretical, I clearly don't know how CCP stores their data; it's their trade secret and I daresay it impacts very heavily on their methods to allow EVE to be single server.
Suffice to say, whilst it seems trivial in small groups, size *does* matter.

Now, I personally can't see why anyone would have a stack of 2mn items, never mind the maximum allowed by an unsigned 4byte integer (4,294,967,295), and certainly not the amount allowed by a an 8 byte unsigned int (18,446,744,073,709,551,615) but the question was raise; how would you do it ?

Of course, some people suggest database shaping, or changing the column format and running a script to cast all ints to bigints (in SQL parlance) but my suggestion was to use a Modified Exess-K function. The would let you keep the 4 bytes of storage, but lose the sign, so you can still reference 4.2 million items.

I was asked on Twitter to write out how this might work, so here goes:

Excess-K is a method to allow you to represent signed numbers, in a format that doesn't allow signed numbers. Again, this is relevant, because there's no 'natural' way to store negative numbers in Binary code, without losing the range of numbers you can store in that field (because you have to use one bit to represent the sign of the number being stored, maths yeah!).

Excess-K is a simple way of storing signed numbers, as it in effect lets you redefine which binary position represents the number 0.
That is to say, if you read a 0 from your data store, you know that it actually means -2,147,483,648; If you read 1,073,741,824 from your data store, it actually means 0 and of course, 4,294,967,295 really means 2,147,483,647.

You can bias this too, if you know your code will never exceed -100, you can say that 0 means -100 and 4,294,967,295 actually means 4,294,967,195. This is where the -K part of Exess-K comes in, K represents which number is really 0.

So to resolve the 'problem' of storing stack counts in a signed integer, without doubling the database storage used, you can apply Exess-K backwards.

When you write out inventory contents to the database, instead of saying the player has 0 items, you say the player has -2,147... items, or in other words, you subtract your 'K' constant from the count and write that out instead.
when you read the items back out from the database, you simply add your 'K' constant to the value to yield its actual value.

Simple, eh ?

NB: I don't advocate doing this at all! I think it's a total non-issue; but as a programmer, I am fascinated with the technical details of such problems.

Of course, the whole problem would be solved if Microsoft and the SQL standards authority allowed a 4-byte unsigned int data type

Ahh, interesting point - no; I have not dealt with that scale of industry by any means.

Would it be worth having the storage overhead, just in case you use that many minerals, or incurring the cost of converting each integer read ?

Hard to say ...

it's far from uncommon to see a freighter load of trit moving around. 86 million units of trit.

Why? because a /hurricane/ uses 2.6 million units.

A Maelstrom uses 11 million.

Mynnna meant /billion/ of units for a titan.

Eve, it makes you use big numbers.

Aren't a couple of flags (singleton, and IIRC BPO/BPC) encoded using negative stack counts?

The database is Microsoft SQL; it doesn't have a 4 byte unsigned int, without resorting to said trickery.

This is because MS-SQL supports the SQL-92 standard which too doesn't represent this number.

Source for SQL 2000

Source for SQL 2012

Making more ISK than I do, I suspect