It’s fun to have a video game collection. It’s sometimes even more fun to try to make the video game collection really really well catalogued.

Having collections properly catalogued is supposedly easier in the era of more social, Internet-based gaming. Systems like Xbox Live and Steam make it really easy to show off your collection and how you’re doing in the games.

But on the other hand, both Xbox Live and Steam, and by extension all of the social third-party websites that depend on them, concern themselves with two questions: What games you have, and what you’ve been doing with them?

Of course, these questions should obviously be irrelevant to collectors of games.

Okay, not really. The sane collectors may admit to having the games just because they actually like to play the games too.

But the point is, we need more than automatic game tracking if we want to get collections organised. And this is why other websites exist, this is why I’ve found these other websites wanting, and this is why I’ve been building my own solution.

In this blog post, I’m explaining a few of my efforts to build hopefully the last personal game database I’ll ever need. Also included are a few curious diversions down the memory lane. Hope you’ll enjoy it as much as I did!

(Side note: I almost posted this to my personal blog, because this is only tangentially related to gaming itself. But then again, it is a gaming article and I’ve certainly not posted a whole lot in this blog lately. Just be warned that the subject matter is a little bit nerdy all around. =)

The Right Questions

Collectors ask a whole lot of more questions: When did you get the game? Where did you get it from? Did you just get that game or a whole bunch of them? Any funny stories to share about how you get the games? Were they new? Mad rush to the store in the launch day? Or was your heart filled with joy as you randomly spotted a game you’ve been hunting for the past four years in the used games shelf? How much did you pay for the game? And what condition is the game in, anyway - did the bastards only get you a CD, or is it brand new in the wrapper? (…it’s never brand new in the wrapper. …well, okay, I have a few games I’ve not yet started, and one duplicate copy of Myth III: The Wolf Age somewhere. But the point is, proper collectors don’t keep stuff in wrappers. =)

So sites like Raptr are great if you want to see what people are playing. Raptr just pokes its nose in XBL or Steam and sees what’s what, and correlates that with what people are doing in the site. But if you want to see a game website that asks the right questions, you need to head to MobyGames. MobyGames lets you organise your games in several lists, and enter the purchase date, purchase place, and condition of each item, along with random other notes.

MobyGames’s limitations and how to cope with them

MobyGames isn’t perfect, though.

In situations where you need to catalogue all sorts of information, it’s pretty important that the system doesn’t have any arbitrary limitations as to what can be entered. The archivist has a certain pieces of data that must be recorded. A system that fails to record that information is rather limited. (For example, Raptr doesn’t give damn that I had 200+ hours of The Elder Scrolls V: Skyrim behind me when I started using Raptr. This would be a fact that almost everyone should agree that I should be able to enter somehow, but the system won’t let me.)

And it’s especially limited if, in case of MobyGames, the answer is “sorry, you can’t enter the information about that game, because I don’t know of that game. You need to tell me about that game first. And wait for a moderator approval. Should only take a few weeks.”

So, one of the most obvious problems is that MobyGames doesn’t let you enter more than one copy of the game for a particular platform in one list. The system is fine if you have one copy of the game, but if you have duplicates of the same edition of the game, things get complicated.

Sure, it’s pretty much necessary to have two or more lists anyway these days, just for the digital copies and physical copies, but this still creates a situation where a librarian asks people put all relevant information in one index card, which is then filled with a ton of scribbling. A smarter librarian just makes a card per copy. (…I guess I’m coming off of a bit of a drunken stupour and I sort of assumed that people still remember the days when library catalogues were in cards. I guess reminiscing about old games has made me also reminisce about the days when our local library got computerised. A real technical marvel, that was!)

Of course, in reality, you don’t usually have copies of the same edition. MobyGames treats various special editions as separate titles. The problem starts to rear its head when I have things like this:

To wit: Betrayal at Krondor. 3.5” floppy release, CD-ROM budget release, and the CD-ROM that came with Raymond E. Feist’s Krondor the Betrayal. It’s hard to argue that these aren’t different editions of the same game, but for some reason, no one has been arsed to submit them as separate editions to MobyGames. I can’t be the only one with this problem.

Another Moby’s limitation is that different editions of the game aren’t necessarily there. Hell, games are frequently missing. The reason why I originally started this newfangled offline database project of mine was that I kept buying new games, and the receipts just kept piling on the desk when I checked if the games were in Moby and found out they weren’t. MobyGames wants every submission to be pretty comprehensive and as correct as possible, which is why all submissions are vetted pretty hard. There’s a whole bunch of ping-pong to get the submissions right, and this sometimes takes weeks, and sometimes a month or more. This obviously usually leads to fairly well maintained data, but it means that people who rush for new games get to wait. And wait. And wait.

So when the weird edition isn’t there, all you can do are the margin scrabblings in your proverbial library card. When the game is outright missing, you end up putting all that data in some temporary storage location - not MobyGames, unsurprisingly. And the “weird editions” can be something innocuous like, oh, every single budget release ever, every single Humble Indie Bundle ever, or every single Steam sale pack.

Human nature

But MobyGame’s failures are kind of avoidable. Bigger failures are related to my own person, of course. I’ve not always been a methodical game collector, and it appears that due to the technological rifts, I sometimes even manage to screw things up nowadays.

At some points in my gaming, I’ve probably had Lists Of Stuff I Own. Then I had the presence of mind to add dates when I bought games, and as I started to maintain my stuff on MobyGames, I started cataloguing purchase prices, purchase dates and purchase places. It’s pretty hard to tell exactly when this started happening, but it was definitely somewhere in my GameCube era.

So information I have at hand may not be entirely reliable, and it’s kind of weird and a little bit humbling to just look at the information I’ve gathered so far and realise that, dammit, I’m just a human after all, and not even a particularly stellar one. But I live with it and I’ve apparently gotten better at keeping track of shit over the years. Good good.

Of course, since I copied most of the stuff from MobyGames to this new database by hand, it’s possible I’ve introduced even further mistakes. Further checks will be necessary. Nice, isn’t it?

Software chosen

The beginning of the project is always exciting time, because you never know what software you end up using. Some difficult choices will need to be made. Choices that will shape your destiny.

I had a few simple criteria:

  • Store everything in regular files that can be stored in version-controllable filesystem (so I can just store snapshots with Git.
  • Has to work in both Windows and Linux.
  • Must not randomly lose data (which is mitigated by the version control mentioned above, but still, it’d be pretty dumb to enter a bunch of shit one day and have it all just vanish.)
  • Highly preferrably not crash randomly for dumb reasons and generally be a pain in the ass to work with.

At the risk of sounding like a complete lunatic, I picked LibreOffice Base, despite not really knowing the next thing about how to actually use it and how reliable it is. I mostly picked it up because I wanted some real experience in developing some kind of a system outside of my usual comfort zone. I could have simply chosen to pick elements that I’m familiar with: Use PostgreSQL for storage and develop various kinds of front-ends to it - tossing together a nifty web-based interface in Rails or whatever. Still, I wanted LibreOffice because I want to just have a frontend rather than develop one. This path required some SQL knowledge but no effort on programming side. LibreOffice uses Java-based HSQLDB as its default backend, and if the stuff gets really hairy in long run, I can always try to migrate the stuff in the database to PostgreSQL and build the damn sophisticated frontend of my own.

However, as we see later, picking up “user-friendly” software is usually a huge mistake, because I’m the sort of a geek that can build complex systems somewhat easily.

LibreOffice is a typical office productivity package. Office productivity packages takes difficult tasks and keep them somewhat difficult, but they also makes it possible for everyone to do those tasks. I’m a geek, so I prefer to just write the software required from ground up - it’ll take more time, but it’s a less painful experience over all.

Still, I think the fact that building the whole system in a short time was the right thing to do at this time. I have a database that’s actually functional right now, and that’s all that really matters in the end.

So what are we interested in? Ruminations on data model

As said earlier, what I’m primarily concerned about are these things:

  • Ability to have multiple records with the same title. Not a biggie, I just assign a sequential ID to records.
  • Title and “Edition”.
  • Platform.
  • Medium (various forms of physical media as well as various download services).
  • Purchase place.
  • Purchase date.
  • Purchase price and currency. Need to use arbitrary currency identifiers to identify things like MS Points and Nintendo Points.
  • Arbitrary notes.
  • Is the title added to my MobyGames lists?

In a philosophical sense: What game I bought? When I bought it? Where I bought it? How much it cost? As we can see, sometimes finding out answers to these simple questions isn’t all that simple and sometimes the systems make finding this stuff out quite difficult.

Currently, purchase place is just stored as a text field. In future versions it’ll likely exist as a record of its own. Though there are some complications with this.

Some shops have changed names. For example, I have strange memories of where the hell I bought Abuse. I’m pretty certain I bought it off a certain bookstore in Rotuaari in Oulu. The price tag says “Pohjalainen”. The fun thing is, I bought it from there long before I lived in Oulu, and for as long as I’ve actually lived in Oulu, it has always been a Suomalainen Kirjakauppa store. They certainly don’t sell computer games any more. (Actually, I’m not even sure they sell much books these days. Fucking hell, I need to pop by and see what the hell is going on.)

And, as above example shows and as I’ve very well managed to scrawl to the database, certain shops have simply ceased to exist altogether, sometimes for good reasons and sometimes for not so good reasons.

As I’ve often lamented, computers are particularly terrible at interpreting and organising hazy memories. For example, in an earlier article about photography, I wailed how storing vague dates for photographs is pretty difficult. Same goes for vague geographical and organisational information.

Relational databases, such as the one I’m using here, are built around the concept of normalisation in which the guiding principle is that entities are only described once, to make updates and maintenance of information pertaining to these entities easier. In philosophical sense, a specific copy of a game is an entity. A specific game store is an entity of another kind. Hence, a game store should be clearly described in a database record of its own, the copy of the game should be described in another record of its own, and there should be a relation between the two: that this specific game was purchased at this specific game store.

In other words, the computer expects to see “this game was purchased from Pelimies, Oulu”. Computers don’t want to see “Fairly certain that I bought this particular title off of Pelimies, but there’s a good chance it was one of those clearing-out-the-goddamn-shelves deals off of Anttila. (Why am I thinking that anyway?) I’m 100% certain I bought it in Oulu though. Or was I thinking of a completely different game?”

Digging up the past: Digressions in present-day archæology

And this takes us deftly to the fields I needed to add to the original plan.

The first field I added was “purchase provenance”. Specifically, another note field that explained where I got this price information from, and how. And this is also where I can easily store hazy memories of the purchase conditions, such as the one delightful example above, describing the events that led to me owning a copy of International Superstar Soccer 2 for GameCube. So, I may yet turn the purchase places to a database table of their own, though I’d still prefer some additional ways of qualifying this information.

Another field I had to add was “Flagged?” - I just need some way to note that the quality of the information in the record in question might be suspect and I need to revisit these records.

Lists collected by hand

The initial records to this database came from a note file that I had been maintaining by hand. I had grown fed up with MobyGames and my growing stack of receipts, so I just started adding games to my org-mode “todo” file. It was, quite literally, a to-do item. List of games I need to add to MobyGames.

As org-mode files are plain text, it wasn’t that difficult to convert the file to CSV format, open it up in LibreOffice Calc, then paste the content to Base, specify which columns correspond to which table columns, and it’d append all that stuff to the “game” table. Figuring out the exact steps was a little bit of a puzzle, but I managed that.

There’s this one old database I know…

I also had a really ancient database in PostgreSQL - basically, part of my very first attempts at trying to learn this relational database stuff. This data source was remarkable because it had pretty complete records of my Commodore 64, VIC-20 and Spectravideo games, as well as some purchase dates for my PC games. But even in that database, purchase dates were quite spotty. I had been focusing on somewhat irrelevant details here, like developers and publishers and game genres, all of which is very interesting if I want to actually play the games but which aren’t very interesting with the collector hat on. Developer information and other publication data may be relevant if I want to make a distinction between titles, but for my purposes, just having the title is sufficient for now. I can always add additional fields later (like “link to Wikipedia article” or “link to MobyGames” or whatever) that will make telling apart particular titles easier.

I was a little bit puzzled on how to copy this data off of this database and put it in this new database. It’s not like LibreOffice had any obvious tools for this, along the lines of “Hey, just let me establish a JDBC connection, and you’ll see the tables right here. Just a matter of SELECT INTO after that.” Obviously, LibreOffice is an easy-to-use database application, so goddess forbid it should let me do anything easily if I know what I’m doing.

So since we’re talking about a small quantity of data, I basically had to do a SELECT * with output to a file, do some hand-massaging in Emacs, import it as a CSV file to LibreOffice Calc, and then do the copypaste ritual again. Apparently this is how things work in the enterprise.

A trip to the shelf

Most of my game purchase data comes from receipts, which I’ve been collecting in a single envelope. Unfortunately, some of the receipts have faded. And, of course, before I had the presence of mind to actually collect the receipts, they tended to get lost. I probably put them somewhere, but where they specifically went is another question.

But one day a while ago, there was a Fact About Game Purchases that was staring right at me on the game shelf.

Yeah, yeah, I’ve decided to put the most iconic game covers on the shelf, just to show that there was an age when games actually had covers that people gave damn about. =) But this just reminded me that I’ve never really had any effort to scrub the boxes for presentation. I could have removed this price tag, but I might have harmed the box, so on it stays.

And it tells that I paid €27.80 on the Linux version of Myth II: Soulblighter… which, as I recall, I purchased of the Y-Daatta store at University of Oulu (because the university obviously has a ton of Linuxheads. =)

But it got me thinking: What else is there in the game shelf?

I noted that a whole lot of the PC game boxes actually have price tags on them. Of course, sometimes information found in them is a little bit academic if I don’t know when I bought the games. I found some of the games I had gotten from a second-hand store in Kajaani or possibly in Sotkamo. (I can remember the day pretty vaguely.) Price tag says “1.00”. Question of the day - is this in Euros or Finnish markkas? Knowing when the game was purchased would settle this question, of course…

I was pretty happy when I found several boxes with tags, and one of the boxes had a piece of wrapper in it that had the price tag.

And a couple of the boxes actually had receipts in them. I hadn’t found them earlier. Very cool.

I’ll Show You Online

On Xbox 360, I’ve tried to play every game I get on the day I get it just so I could get a few achievements that remind me what day I got the game on. Okay, there are a few games that completely eluded me (like Dungeon Defenders), and games that I couldn’t get into until very close to midnight so I only got the first achievements narrowly afterward (like Bayonetta and Wolfenstein 3D XBLA), and, as said, a few games that I basically had no time for yet and started late (like Kinect Sports) or I didn’t beat the previous parts of the series yet (right now on shelf: Assassin’s Creed III, Final Fantasy XIII-2). But the point being, achievements can serve as some kind of data that can narrow down when I got the game.

Xbox Live fortunately does have a better way of tracking this stuff, at least what comes to the Xbox Live Arcade games: Microsoft Account system apparently nowadays has full web-based transaction history, so I can see what day I bought which game and exactly how much did it cost - unconverted costs in MS Points or euros, which is as it should be - so it’s easy to see if something was on sale or something. The Xbox Live store only show the current prices, after all.

(I have no idea why my own list of Xbox Live Arcade games was missing this particular entry, and not any others. Of course I should remember the day when absolutely cute cubical wolves got added to my 360! They’ve got boxy heads and the boxy heads tilt cutely and the reason it is so cute is that you can easily tell the angle of tilting between two cubical objects very easily. Purely mathematical. So you can easily see how headtilty these particular wolfies are! And you can live in a mud hut and construct lavish mansions for your wolf packs. And you can go to the wolf mansions and just stand there and watch the wolves sit in there and spin in their spots and bark and that’s absolutely soothing. I don’t have any pets so this is fucking therapeutical.)

GOG.com’s system is somewhat similar: You can actually arrange your shelf by purchase date, and on each game entry, you see when you bought it.

For example, by digging through my GOG account page, I can easily find out the exact day when Electronic Arts got soundly defeated by game distributors who actually do what the public cries for, instead of sitting on a frigging gold mine for years:

Still, getting a list of actual transactions off GOG is a bit of a problem - I sometimes have to dig my emails to figure out how much I paid for each item, as the actual order history doesn’t seem to be on the site itself. The best way to buy games off GOG is to fill your cart, then go to checkout, enter every game in the list to the database with the current date and all prices (especially if there’s a sale going on), and then complete the purchase. Saves you from email digging. But at least GOG does send you email that has full order summaries with each game’s purchase price properly listed.

Steam isn’t much better - there actually is a purchase history feature, but it only shows the total price of each purchase, so if you bought multiple games at once, you’re out of luck and probably need to do some digging.

Steam also has one annoying feature: As I only got a printer of my own this month, I had previously just printed Steam receipts to files, as Steam client at least prompts you to do so. Turns out the printed Steam receipts are bullshit - they have account name (which I obviously know), order number (which is useless to me), and total sum. The printed receipt is missing a few details, like what actual games I bought, when did I purchase this shit and most importatly how much did each of those games cost. In short, these aren’t much of recipts, now are they? By comparison, I tried looking at Desura’s printed recipts, and they at least have everything itemised - the only pretty crucial fact that is missing from the Desura printed receipts is the date, but at least in this case it’s stored in files.

So, while digital PC game stores may have a few advantages over Xbox Live Marketplace (such as not relying as much on DRM, having more sales, somewhat smaller prices, and an actual ability to send gifts), they manage to fuck up a fairly easy-sounding thing that makes life hell for collectors. The world is a very weird place.

Dear diary…

One source of game purchase data was my blog stuff and my diaries.

Of course, I’ve not been a particularly meticulous blogger lately. I’ve needed to straighten up. I’ve seen the error of my ways. I’m just blogging stuff now, and I shall blog stuff a lot in the future. However, this means that the game blogs have not lately been a particularly good source of data, because in all likelihood I’ve kept my personal purchase records in far better shape.

But once upon a time, I was blogging stuff about I had actually purchased. I kept running into funky LiveJournal entries that are a treasure trove of information I was unable to record to MobyGames at the time. Oooh, I bought GBA Breath of Fire “last Tuesday” for a middle-of-the-woods robbery price of €65. Fascinating. (…especially fascinating because, as I noticed, this has pretty much become the standard price of all games anyway these days. Looking at the old games made me notice that there used to be an era where you could buy a game for €50. Damn inflation! Damn the rising AAA title production costs! =)

But this list does illustrate one problem: The list of games I bought off the recycling centre is a huge mess of titles… and the actual titles I bought were collections of games, save for a few individual titles (L.E.D. Storm, Mini-Putt, Sun Star… I think???). I knew there was Karate Ace, Flight Ace, Space Ace …or was it that? Dammit, I really need to check. And what was the name of the collection that had Barbarian, IK+, Rampage and other those games? Can’t remember. Aaargh! Wish I had my Commodore 64 games at hand so I could get all this stuff straight!

But I have a backup of my LiveJournal at hand, and I might be able to grep it for some purchase data.

Another source of data I have at hand is my old diaries from late 1990s. Scrounging through the diary is, unfortunately, not for the faint of the heart. I was far more shy geek those days and I kept angsting over stupid things, instead of writing about things that would have had some lasting cultural impact. Like my thoughts about videogames that changed the world.

(Most of this garbage was just entries along the lines of “I played WarCraft II today, it was good.” No, it was not good. I was terrible at WarCraft II, and I’m absolute crap at real time strategy games to this day. For the sake of my psychological wellbeing, I have to face those lies and denials from my past. I have to be honest.)

A digression in Tomb Raider, or, on actualisation of old projects

As I looked at MobyGames data that was missing, I was fairly sure that at one point, I consulted my diaries for mentions of the day I got my copy of MS-DOS Tomb Raider, and while I’m pretty sure I did that, I had somehow been unable to record it to MobyGames when I found it.

So, I spent the New Year 2007 celebrations by basically playing Tomb Raider on PSone and wrapping the last levels up before 2007 rolled in, because damn me if completing a game that good should take me more than 10 years. I didn’t know exactly when I got the original Tomb Raider, only that it was early 1997, so I wanted to be done before 2007. (Or something along those lines anyway.) So in the end, I had an epic unspeakable gunfight with pixely, low-polygon monstrosity possibly answering to the name “Natla”, and done with that, I started watching The X-Files, and someone was blowing shit outside and I guess the new year’s celebrations had gotten started out there. Good new years. Very good new years.

Now, I didn’t have an actual receipt of the game. I only had a vague recollections of when I bought the game. And turns out my diary actually had the exact date I purchased Tomb Raider on. March 6, 1997. The same diary pages also had descriptions on how my sister was freaking out over not finding a copy of Donkey Kong Country 3 in stores and how the excitement was practically killing her. Can’t remember the exact details, but she did found a copy eventually and clearly beat the game far, far faster than I beat Tomb Raider. (…And I’m pretty sure I haven’t beaten DKC3 yet. Shit.)

And then I read some other stuff on what the hell I was angsting over those days, and then I couldn’t just read my frigging diaries at the time, had to get drunk and talk this shit through with my friends, in my continuing efforts to become a passable geek instead of a shut-in one. =)

Old, really hazy shit

There are a bunch of other data sources that might be of use.

I used to live in Kuhmo, which is a small city in middle of the woods. Best known for its serene natural beauty and a chamber music festival in the summer. Not so much for its gaming opportunities.

In a small town like Kuhmo, appearance of actual computer stores was somewhat sporadic. I’m not sure there even are any computer stores now. A few shopping mall chains have showed up (I was pretty amused to get a cheap copy of WarioWare: Smooth Moves a few years ago), there used to be a second-hand store, and there’s a recycling centre (off of which I grabbed a few puzzling PSone titles lately, and such).

My primary source of games for a long time were some game stores in Oulu and Tampere - I used to order some games from them through mail, and I visited them when the opportunity permitted itself. After digging through the dustiest parts of my brain banks, I found out the store in Oulu was called Komentokeskus, and the shop in Tampere was called Triosoft. Komentokeskus certainly isn’t there any more, and I’m not sure if it was here when I moved to Oulu either. I wasn’t entirely sure what happened to Triosoft - it might have been that they moved from one place to another - but apparently they’re still in business.

And I have pretty vivid memories of a game store called Web Direct that managed to sell me a bunch of games through mail. Too bad it took time for them to actually mention somewhere that game boxes cost extra. I received a few games from them without packaging, some of them were damaged in transit, had to be returned and they didn’t have an agreement with the post office so I had to pay extra. (Until I found out that they do return the postage somehow). Fun times. Confusing times.

So how the hell do I approach this shit? Some of the purchase dates were found in the database, of course. But are there other ways of finding out when I ordered stuff, and how much it cost?

I should probably check my email.

Of course, the problem is, much of my old email from late 1990s is stuck in some crazy-obscure CD-Rs somewhere, in .tar.gz format so grepping that shit is going to involve some exercises. Or it might have even gotten lost. But it’s probably the CD-Rs and obscure archive files.

Still, this idea of tracking down mail orders got me sidetracked into thinking of what the hell is this this concept we call “purchase date”. The day I say “please send me this game”? Or the day I receive it and pay for it? Hmm…

Come to think of it, was I actually crazy enough to retain the letters I sent to Triosoft and Komentokeskus? I have an obscure CD-R of my work files from 1990s - I basically copied a whole bunch of old floppies to CD-Rs and those included a great bunch of correspondence.

…Oh yes! I did some cursory checking of the files, and did find some orders that I had sent back in the day and some other files that are probably quite interesting.

First, I found a fascinating record of my monthly allowance I was getting from my parents:

It’s kind of awesome that LibreOffice will happily open “old Lotus files” (in this case, Microsoft Works files from ages gone by). Even if the date column isn’t technically properly implemented. Even when the sums were in markkas and definitely not in Euros. Even if the character set is pretty funky.

But at least this file gives me some information to work with. I was getting 100 FIM (about €16.82, not sure about inflation) a month. I saved up three months for Betrayal at Krondor. I think this means that the game was bough in the March of 1994 in the school winter holiday on trip to Tampere, probably from City-Sokos (or whatever that place was at the time) or Stockmann, and the price was around 300 FIM - possibly more, possibly less.

(And why yes, Turbo Pascal was quite awesome too. =)

LibreOffice also opens up WordPerfect 5.1 MS-DOS files just fine. Look:

In case you can’t read our moon language, the basic gist of this rambling is that in April 23, 1993, I politely wondered if Komentokeskus in Oulu had received my faxed order that I sent in March 25, for Super Mario Land 2. And if not, I’d frigging order one, please, goddamn it.

(Remember fax machines? Of course you don’t. It’s one of those dark secrets of the Nineties we don’t want the future generations to find out. But the truth must be told. They existed. We’re very sorry that they existed. So very sorry. Also, I’m not sure why I’m censoring the phone number, because I’m pretty sure the thing was disconnected long ago…)

Hmm, I remember the availability problems with this game. I was hoping I’d be able to buy it from Triosoft in Tampere during the winter holiday, but Triosoft didn’t have a copy, because it was loaned or something, and we were supposed to come looking for it the next day, and when we did it wasn’t there, and we had to leave that day, so I didn’t get that bastard in that trip. And if this file is to be believed, apparently after the holidays I sent a grudging fax to Komentokeskus and they apparently had a few hitches fulfilling this order too.

Such is the way with stunningly popular Nintendo titles.

(…and fuck it, I got to the last boss in SML2 but never beat Wario. My sister did. Not in easy mode either. Goddamn it.)

And I guess that this further cements the idea that in a lot of cases, “purchase date” is a very very vague idea indeed.

Digging through the CDs yielded even more geekery.

Yes.

I wrote a fucking TeX template for ordering games off of Triosoft. And not even a LaTeX2e template, it’s a motherfucking Plain TeX file.

Those were crazier times. We needed desperate solutions to get desperate results.

This was just a cursory look at the files on CD, and I haven’t even gotten around to look at the shit in emails. I have very very hazy memories of using a typewriter to write these order letters too. Did I photocopy them for personal reference? …I may have been crazy, but probably not that crazy, and if I were, the letters in question were probably undated.

One hopeful source of purchase data is probably the huge stack of old computer magazines. I have some memories of me circling some entries in old MikroBitti, C=lehti and Pelit magazines - of course, it’s not exactly a particularly good way of figuring out when some games were ordered, but looking at the stuff will probably get me an idea of how much those games probably cost at the time.

Too bad I don’t have the magazines at hand. When the opportunities to dig some archives comes my way, I’m sure to take it.

And the least hopeful data source I have is a dump of my Windows partitions back when my desktop still had Windows 98SE. I could check the dates certain folders or savegames or such were created to narrow stuff down, but since modern operating systems aren’t generally concerned of file creation dates, this is going to be funky…

Some kind if an user interface

Enough of the data sources - time to build the bastard!

LibreOffice does let you build nice forms for entering data, as can be seen from the Betrayal at Antara example above. The forms let me enter new records. They also let me update old ones. They also let me perform rudimentary searches, so I can be pretty sure that each record is entered correctly. It’s not a particularly good search engine, though - literal substrings already bit me in the butt when it was making a distinction between Zork Nemesis and Zork: Nemesis. It’s also doing record-by-record scanning, when it could be just pulling SQL queries.

But all that really matters is that I could open up this form, have MobyGames open in the web browser, and enter new records and update old ones. It was actually a pretty fast, straightforward process.

It allowed me to take it easy and go down the memory lane. I needed to put all sorts of details in about things I remember about the purchase conditions. Like here:

All kind of reminiscing ensued. So what the fuck was that bookstore called? Let’s see, I have one book that I very clearly bought from that shop. (Java in a Nutshell, second edition, covering Java 1.1 =) Right, Dataclub. It says so in the exlibris. Oh man, this book was awesome. I bought this from that bookstore in a school trip to Helsinki. Got strange memories of a classmate of mine receiving a call in our hotel room - remember, this was before every kid had cellphones - and he slowly said that his BBS had crashed - remember, this was before the rise of the Interwebs and before we had passable operating systems. That store was frigging crazy, they used to send me like ten copies of their catalog because their web order system was confusing and they automatically registered me as a new customer every time I ordered stuff. Really freaking weird.

Actually, in this particular case, I simply should have looked at the cover of the game. It had a sticker that says it was imported by Dataclub.

Oh well, memory-laning is sometimes fun, isn’t it?

The Travails of Those Who Brave the Long Cold And Hard Bristles of User-Friendly Software

I’m happy that I can enter all of this data to the database. All I needed then was a query that produced a “flat” game list. In the list, it’s already pretty certain that there are certain clusters. =)

This flat game list can then be fed to the reporting engine in LibreOffice Base. It will produce reports. Real enterprise-grade reporting here. The final step of verifying that stuff on MobyGames is also in my database would be to take a look at the printed versions of the lists. You can’t easily print out all stuff in MobyGames, but printing the list of games is easy. LibreOffice gives you various tools to make printed out reports.

I printed out the list of my games on MobyGames. 13 pages.

I then printed out the report that the LibreOffice report builder produced. 79 pages.

I got the distinct impression that this shit is what the folks in the industry call “Enterprise Software”. Or, as the developers tend to call it, “enterprisey”.

(I guess this is going to devolve into another off-topic rant. Hngh…)

People who have programmed something for themselves or in university courses probably regard enterprise software as well-engineered, demanding stuff that takes the best possible software engineers in the field to develop. Of course, when they actually get hired to do actual coding, they realise that while there are some shining examples of rock-solid, colossal software engineering, the vast majority of “enterprise software” actually does very little. And does it very slowly and eats gobs of resources. And is absolutely shittily written by some consultant that gave the company an astronomically huge bill. Or, as this case shows, it was developed in-house using off-the-shelf ahem “solution” that we idealistic geeks might describe as not optimally suited for the task. Why develop custom software when we have Microsoft Office? …or, in this case, LibreOffice?

I guess I knew I was in deep shit when the report builder proudly advertised itself as an Oracle product. It’s one of those reverse-Midas things. They take wonderful software and make it go to hell - just look at what happened to Java lately. Java is a brilliantly engineered programing language, and has a reference implementation, which was taken under Oracle’s wings. And now Oracle is apparently finding out that there’s this “security update” thing that they’re supposed to do on schedule. Instead they’re just busy foisting toolbars and shit for desktop users. Nice.

LibreOffice’s report builder was one of those Oracle contributions. It’s not particularly intuitive. You can waste tons of time in it getting somewhat mediocre results.

Had this been up to me, and had all that information been stored in an external database (like PostgreSQL), I’d just have whipped out a bit of scripting language stuff to pull a few queries and format text. But nooooo. Embedded database, using only LibreOffice tools. So I get huge badly formatted report with truncated titles, gigantic fonts, and wacky pagination. That is, provided the report tool doesn’t make LibreOffice crash entirely. And on top of that, each generated report is stuck in the recently used files. Good going.

But I suppose this stuff is good enough for some manual verification that the data entered is actually in the database.

What the future holds

The database is pretty crude at the moment, but it does its job. I’ll proably expand the system in the future to new directions.

Since the Microsoft Account system does show you every little purchase, it also made me realise that before this, I haven’t put DLCs in my game purchase records, while I have done so for actual physical expansion disks. As I appear to have the exact records after all, I now have a chance of actually remedying this. I’ve also not categorised the games into categories like “Retail”, “Compilation”, “GOTY”, “Expansion disk”, “DLC” and so forth - once I do this, I’ll probably tackle the DLC issue.

Also, I keep wondering if I should store the CD keys in the database so that there would be less hunting. Then again, CD keys are fortunately a thing that’s gliding slowly to the past, but just keeping them here would be great.

In interests of normalising the data, perhaps it’d be better if the purchases were organised as events, and these events were regarded as entities of their own. There wouldn’t be “purchase date” entries any more, just purchase events that may be related to one or more games, and may have an associated receipt stored in the database as well.

I’m also debating whether I really should store the information in this embedded SQL database or not. I do value the fact that the file is entirely self-contained and the file is versioned in Git. But Git isn’t exactly suited for binary files, so perhaps I should instead store the information in an external SQL database and only save versioned, pretty-printed XML dumps of the data, or something like that - Git is better at handling those, after all. But this is a relatively minor quibble, and fixing this won’t take stupenduously huge resources either. Dump the HSQLDB, do some hand-massaging, import stuff in PostgreSQL, start building an interface that doesn’t suck quite as much. Tadah!

A few last words

So, I have a brand new game database that seems to be working pretty fine. Technically the first entry in the new game database was Baldur’s Gate Complete, off of GOG.com, and I absolutely need to tackle that thing because I’ve had copies of BG for ages and never actually completed it. The first actual game purchases entered to the new database after it was built was Neverwinter Nights 2 and Ultima VIII off GOG.com again - NWN2 is one of those games I apparently have CDs for but never actually played. NWN had Linux client, NWN2 doesn’t. Now, I have Windows here, but can’t play NWN2 anyway because my hard drive is packed. Hmm, history seems to repeat itself. Ultima VIII is one of those games I never completed and need to restart again, mostly because it has some absolutely fantastic stuff in it. And I’ll just ramble about it in other blog posts. Soon.

But this article is one of those huge blog posts that got me all reminiscey and weary. Hopefully those articles take less research to pull off thanks to this new database thingy that is fortunately taking shape now.

I recommend database building as a hobby for everyone, though. =)