Add to

ea's spore released on 7th september 2008

Written: 02:13 on September 04, 2008  |  By: jon  |  MORE…
Sandbox game addicts, get your wallets ready. One of the most anticipated games of the year, and perhaps even the past five, is about to hit stores on 7th September 2008. Spore's release date has been widely publicised since it slipped last winter by six months.

So, what exactly is Spore? It comes from the terrifyingly clever mind of Will Wright and his team at Maxis (the same company behind the phenomenally popular 'Sims' franchise), now a subsidiary of Electronic Arts. It is also a game which has proven difficult to explain, although the best explanation I have heard so far is “it is Sim Everything”. You start out as a small organism and, much like the real world, it's survival of the fittest. Progress through the game is rewarded with upgrades, where you can spend DNA points on upgrading your lil' fella. As the game progresses, you move from simple organism to pond life, to a land animal, and from there to founding society, then civilisations, and onto becoming the dominant species of your world. But that is not all – from there, the game folds out allowing intergalactic travel to other worlds, where the local monsters have been populated from a huge database of other people's creations.

I toyed with the 'creature creator' a few weeks ago. It's a small part of the game that EA released early, so we could get a taster of what is to come. The creator is fantastic fun – I made a crocodile with a huge head for instance, and I laughed at it for probably fifteen minutes straight. The bright and colourful models together with the very cutesy animations your creation performs (my crocodile almost shat itself in excitement when I gave it an extra set of eyes, for example) not only entertained me, but roped my non-gamer girlfriend in too. It was interesting to see what monsters she came up with. Inevitably, the first one looked very sappy and cute, some sort of hug-monster. I have a sneaking suspicion come this Friday, I may have competition trying to get on my PC!

So what is this blog about? The pitfalls, really. I don't mean to be cynical but I'm British, and it's in my genes. I really want this game to succeed, I really do. A few things worry me, however. First off, great, it's a game where you can 'level' your monster up (reminds me of E.V.O. on the Super Nintendo, if anybody remembers that game), which is great. I'm familiar with that concept, and together with the creation mode, it sounds like a game I'd have a lot of fun with. What about the other areas of gameplay? If you watch the trailer, it shifts from top-down munch-em-up to a third-person action game, then to an RTS, then to a space-faring sim. The variety looks great and I applaud it, but I hope it doesn't feel like I'm just playing a set of mini-games with a tentative link between them all (the link being your creation, as it will be constant throughout, although you can 'upgrade' it). It's that sort of detached feeling that made Wii Sports a decent game and not a great game, in my eyes.

The second is the fact that content comes from other people. If this thing gets as big as 'The Sims' did, which lets face it is likely, there will be a hell of a lot of content within Spore. Somebody has already mentioned: what happens when the Star Wars, Pokémon and Futurama creations start appearing? Are they protected from take-down notices? Will EA have to screen all the content? If you're new to the whole concept of Spore, or gaming in general, you may not be familiar with the one core concept rooted deep within our kind. If you give a gamer (especially a male one) a sandbox environment where you can build or model things, they will. make. a penis. I haven't got the statistics to hand, but apparently there have been thousands of cock-monsters uploaded already and the game isn't even out yet. Spore has an ESRB rating of E10+. Would you want your ten year old daughter and her interstellar pal 'Mr. Cuddles', the green snuggle-monster to suddenly arrive on a planet filled with huge pulsating penis-monsters, wriggling around and oozing god knows what? I'm not so sure either.

I hope it lives up to the huge weight being placed upon its digital shoulders by the Internet. I'm sure it will pull me away from reading and programming in the evenings for a while, at least. The game has to be admired, just for the sheer scale, the ingenuity (you can give a creature a billion legs and it just works out how to walk), and the love that has gone into designing something this complex. At five or so years in the making… let's see if can match the hype.
Add to

nested sets (again) with group_concat()

Written: 16:34 on September 02, 2008  |  By: jon  |  MORE…
A little development blog I've had on my mind lately. Consider the hierarchical nested set article which I have linked to previously. I began using this paradigm extensively for my hierarchal data and one thing that immediately leapt out at me (about the MySQL article) were the examples. I found that whilst informative, I couldn't really contrast or compare the two methods (adjacency and nested) in several ways, since the outputs were different. The example they gave for retrieving a "single path" from a "root" to a "leaf" is a good one for illustrating my point. Often whilst working with the nested set model, I have wanted to get results related to a node in the system (with joins with other tables). Also, I have wanted to grab all the tree that relates to something (for example, imagine a 'purchased' table that links items within the 'nested_category' table to a user) and also have information about the path to that product. I achieved this with a sub-query.

If you haven't used them before, sub-queries are a very handy addition to your developer's tool kit. Traditionally, they are used to grab parts of data where the grouping in the main SQL query makes it difficult, or for getting similar result sets to outer joins. Going back to our nested set model, if you look at the examples for the adjacency set you can see that when they are getting the path to a node, the result set is one row containing several fields.
+-------------+----------------------+-------------+-------+ | lev1 | lev2 | lev3 | lev4 | +-------------+----------------------+-------------+-------+ | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | +-------------+----------------------+-------------+-------+ 1 row in set (0.01 sec)
As the article rightly states, this method is extremely limited as the SQL query has to be written knowing how 'deep' the tree will go. If the depth is always the same, then it isn't so bad. The nested set model offers a lot more flexibility. However, when we are figuring out the 'path' from a specified root to a leaf, the results come out in the same column as different rows (shallowest node first). You can try this for yourself (I'm just reiterating what can be found in the article currently) with the following queries:
INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4), (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19), (7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13), (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
Now, the query…
SELECT FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND = 'FLASH' ORDER BY parent.lft;
…will result in the following result:
+----------------------+ | name | +----------------------+ | ELECTRONICS | | PORTABLE ELECTRONICS | | MP3 PLAYERS | | FLASH | +----------------------+ 4 rows in set (0.00 sec)
Now, I get to my point. What if I wanted to get the full tree, with a column inside that result set that showed me the path to each node in the set. My initial reaction was to do it as a sub query. As my original result set is being built, I know everything about that node (with the select *). I can just use the path query on every row. However, as the above 'path' query pulls back several rows, they will overwrite each other. So, instead of obtaining a path to say, the 'FLASH' leaf, i'll just have the name of the product 'FLASH' again. This is because FLASH is the last row in the pathing query: it will overwrite 'MP3 PLAYERS', which in turn will overwrite 'PORTABLE ELECTRONICS', and so on and so forth.

So, what do we want to do? We could do it programatically with a loop, and use two queries. I am always incredibly weary about placing queries in loops and I decided against this. All we really need to do is concatenate all the node names together into a string. Makes sense? So as we traverse the path, PORTABLE ELECTRONICS is appended to ELECTRONICS, and then MP3 PLAYERS is appended to the previous string, and finally FLASH is appended to this string.

Unfortunately, this method doesn't work either. Your 'node' and 'parent' elements will concatenate together, but again they will overwrite the previous concatenation. The only way around this is to use an aggregate function that is present in MySQL 4.1 called GROUP_CONCAT(). This will concatenate a field together based upon a GROUP BY clause. If we run the query:
SELECT GROUP_CONCAT( SEPARATOR ' -> ') as path FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND = 'FLASH' ORDER BY parent.lft;
It produces:
+-------------------------------------------------------------+ | path | +-------------------------------------------------------------+ | ELECTRONICS -> PORTABLE ELECTRONICS -> MP3 PLAYERS -> FLASH | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
Notice the 'SEPARATOR' modifier I added in. All this does is decide the 'glue' of the concatenation. If you leave this out, it will default to a comma. As you can see, this would be useful for pulling out CSV (comma separated values) of certain datasets. Now, if we introduce this as a sub query to the original query, we shall get the entire result set required.
SELECT, (SELECT GROUP_CONCAT( FROM nested_category AS node2, nested_category AS parent2 WHERE node2.lft BETWEEN parent2.lft AND parent2.rgt AND = ORDER BY parent2.lft) as path FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND = 'FLASH' ORDER BY parent.lft;
+----------------------+----------------------------------------------------+ | name | path | +----------------------+----------------------------------------------------+ | ELECTRONICS | ELECTRONICS | | PORTABLE ELECTRONICS | ELECTRONICS,PORTABLE ELECTRONICS | | MP3 PLAYERS | ELECTRONICS,PORTABLE ELECTRONICS,MP3 PLAYERS | | FLASH | ELECTRONICS,PORTABLE ELECTRONICS,MP3 PLAYERS,FLASH | +----------------------+----------------------------------------------------+ 4 rows in set (0.00 sec)
Please forgive my unimaginative naming in the sub-query… but you get the idea! if you use a 'EXPLAIN' query and check out what's happening in that query, I think you'll agree it isn't too bad. The only irksome part I'd say is the temporary table when checking for equality between and (i.e. where this path's node name is the same as the original's parent). Note that I also put an INDEX on the 'name' column in the table, and this drastically increases the efficiency of the query (in this instance cutting down row scan from n to just 1).

So there you have it. GROUP_CONCAT()… I feel stupid that I never knew about it before. I use it every now and then in queries like this to produce a CSV from a dataset and use WHERE IN() or just return the string and 'explode' the list with PHP. Hopefully my little adventure with the nested set model and this MySQL string function proved useful to you. If you have any further ideas for making the query more efficient, or you can rewrite this without a sub-query, please: leave a comment!
What is dovka?

this is a group blog run by a group of irc zealots, the prefabricators; each is a member of an exclusive irc channel on phrenzy.


here's a list of destinations that are worth visiting…


we talk about a range of stuff here at dovka:


always dedicated to the cause, the prefabricators microblog whilst out exploring the real world…

  • johnny: So happy it's my last day. All I wanna do is sleep — 7 pm
  • johnny: Just got asked to play beach volleyball this summer, I haven't played volleyball in years and I'm not that good #decisions — 28 HRS AGO
  • johnny: People always ask me why I sound so bored at work. You ask the same questions 50000 times and try and sound excited about it — 35 HRS AGO
  • johnny: I don't want to work tomorrow — 45 HRS AGO
  • johnny: I would much rather be anywhere having beers than at work. Gorgeous day #borderproblems #drinkingproblems — 60 HRS AGO
  • johnny: Ivan Nova just made Albert Pujols look stupid #MLB — 74 HRS AGO

dig through our monthly archives and find old relics:

RSS Feed for this page
Add to Technorati Favorites