![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
The database structure for Crystal Towers 2 is absolutely hideous. As I came up with it not knowing how big it would grow, it's an extraordinary example of how not to lay out data, taking advantage of modern relational database features only so far as that it seems to use a table. There's no sensible storage of many-to-many relationships, separate tables with concatenated keys to store player's progress through individual levels, or anything like that - instead, you get one row to yourself, and the rest of the site relies on a demented sort of pointer arithmetic and comparison to PHP arrays to work out what the data means.
For example, my testing account currently has this under the "leveldata" column:
.8.15.16.9.5.1.8.15.12.8.2.1.8.15.14.9.1.1.8.15.27.10.5.1.8.15.12.8.4..8.15.23.11.1.1.8.15.10.8..1.8.15.10.8.2.1.8.15.10.9.3.1.8.15.23.10.2.1.8.15.17.8.2.1.8.15.17.9.4.1.8.15.47.10..1.8.13.13.8.4.1.8.7.11.9.2.1.8.15.25.12.2.1.8.15.22.11.25.1.8.15.15.10.2.1.8.15.16.10.5.1.8.15.12.9.2.1.8.15.12.9.4.1.8.15.15.8.3.1.8.15.17.12.2..8.13.21.8.7.1.8.15.17.9.8..8.15.11.8.6..8.15.17.8.5.1.8.15.18.11.5.1.8.15.10.9.1.1.8.15.14.8.5.1.8.15.73.23.7.1.8.15.16.8.10.1.8.13.15.9.9...0.4.3....0.2.2....0.4.2.1...0.3.1....0.1.1.2...0.3.1....0.3.2....0.1.1.1...0.7.1....0.13.2.2...0.5.3.1...0.5.2.1...0.22.5.5...0.19.2.1.
Only by looking at the PHP to interpret it, with its reference to positions like $leveldata[($i*6+5)], can I infer that this is a set of sequences of six numbers that represent various things about the level - times played, won and lost, a pseudo-bit-encoded (in a relational database!) value to represent the rewards gathered from a level, whether or not a medal has been given, and so on. Zero is represented by nothing between two dots - but apparently, not always.
It's especially curious to me that the flag for medal is the first one in the sequence, despite that being added to the game very late on... perhaps I left an unused number by mistake, or intended to fill it with something else, and then just used the handy gap.
The way that the player's current list of recipes are stored somehow manages to be even worse - there's nothing to look up for these, as there can only be one recipe for an item and the name is treated as the identifier. This always ends in disaster, even when you really won't have any name collisions:
X__Red_Solution,X__Gold_Ingot,A__Gem_Sonar,X__Topaz,X__Blue_Solution,I__Quartz_Flask,I__Magic_Compass,X__Quartz_Piece,X__Diamond,X__Sapphire,X__Emerald,A__Luck_Vial,
It's a comma-delimited list this time, just to vary things a bit. It took me a while to remember why all the names were preceded by a letter and two underscores - what this is is a perfectly awful tangled interpretation of display, identification and storage.
Recipes are of three types - those for unique items that can only be made once and have not been made, those for unique items that can only be made once and have already been made, and those for other synthesis items (in which case we don't care whether they've been made or not). Apparently to avoid having to look up a separate file for what type a recipe is, the recipe names are actually stored with a character in front of them - respectively, "/", "^" and "*" - to denote the type. (I remember that I had intended the * and / characters to be the other way around at first, but got the order wrong somewhere early on - something that should have clued me in to how bad this system was right then.)
So in the game data, the recipes are stored as one of these characters, followed by two spaces, then the actual name. I don't know why there are two spaces - it might be so that the recipes screen can just interpret the first character and display the rest, but that raises the question of why it couldn't interpret the first character, insert two spaces and display the rest. Was I really this stupid at this point? Of course, by a further stroke of genius, none of these characters are safe ones to just stick into request data, so when they're shovelled into the database I do some further conversion to make them conform to a standard on the site that's analogous but entirely different from that of the game. This time, the characters are "I", "A" and "X". I can't remember why I might have specifically chosen those, other than for convenience in that the recipes could then be sorted alphabetically on the page in order of importance with no further effort. I'll just let you react to this for yourself.
Just about the only thing the database does right is that it has unique IDs for players, instead of relying on their names not to collide (which I remember was an adaptation I made much later on), and that most of the other fields in the player table do actually store one identifiable bit of data. And that game events are stored in a separate table instead of somehow cramming them into this one as well - apparently I had learned how databases work by that point.
Of course, this is something that you face when you let a personal project go on for (help) four years - it's a Forth Rail Bridge problem, where as soon as you've finished everything, the older bits are looking so tatty that you need to redo them, but once you've done that, you've found better ways to do everything and want to start something else all over again. It looks pretty good to the player in the end, though, doesn't it?
I suppose you can try hacking around in the request that the game sends to the website, now. I'm not fixing it if you break your save.
For example, my testing account currently has this under the "leveldata" column:
.8.15.16.9.5.1.8.15.12.8.2.1.8.15.14.9.1.1.8.15.27.10.5.1.8.15.12.8.4..8.15.23.11.1.1.8.15.10.8..1.8.15.10.8.2.1.8.15.10.9.3.1.8.15.23.10.2.1.8.15.17.8.2.1.8.15.17.9.4.1.8.15.47.10..1.8.13.13.8.4.1.8.7.11.9.2.1.8.15.25.12.2.1.8.15.22.11.25.1.8.15.15.10.2.1.8.15.16.10.5.1.8.15.12.9.2.1.8.15.12.9.4.1.8.15.15.8.3.1.8.15.17.12.2..8.13.21.8.7.1.8.15.17.9.8..8.15.11.8.6..8.15.17.8.5.1.8.15.18.11.5.1.8.15.10.9.1.1.8.15.14.8.5.1.8.15.73.23.7.1.8.15.16.8.10.1.8.13.15.9.9...0.4.3....0.2.2....0.4.2.1...0.3.1....0.1.1.2...0.3.1....0.3.2....0.1.1.1...0.7.1....0.13.2.2...0.5.3.1...0.5.2.1...0.22.5.5...0.19.2.1.
Only by looking at the PHP to interpret it, with its reference to positions like $leveldata[($i*6+5)], can I infer that this is a set of sequences of six numbers that represent various things about the level - times played, won and lost, a pseudo-bit-encoded (in a relational database!) value to represent the rewards gathered from a level, whether or not a medal has been given, and so on. Zero is represented by nothing between two dots - but apparently, not always.
It's especially curious to me that the flag for medal is the first one in the sequence, despite that being added to the game very late on... perhaps I left an unused number by mistake, or intended to fill it with something else, and then just used the handy gap.
The way that the player's current list of recipes are stored somehow manages to be even worse - there's nothing to look up for these, as there can only be one recipe for an item and the name is treated as the identifier. This always ends in disaster, even when you really won't have any name collisions:
X__Red_Solution,X__Gold_Ingot,A__Gem_Sonar,X__Topaz,X__Blue_Solution,I__Quartz_Flask,I__Magic_Compass,X__Quartz_Piece,X__Diamond,X__Sapphire,X__Emerald,A__Luck_Vial,
It's a comma-delimited list this time, just to vary things a bit. It took me a while to remember why all the names were preceded by a letter and two underscores - what this is is a perfectly awful tangled interpretation of display, identification and storage.
Recipes are of three types - those for unique items that can only be made once and have not been made, those for unique items that can only be made once and have already been made, and those for other synthesis items (in which case we don't care whether they've been made or not). Apparently to avoid having to look up a separate file for what type a recipe is, the recipe names are actually stored with a character in front of them - respectively, "/", "^" and "*" - to denote the type. (I remember that I had intended the * and / characters to be the other way around at first, but got the order wrong somewhere early on - something that should have clued me in to how bad this system was right then.)
So in the game data, the recipes are stored as one of these characters, followed by two spaces, then the actual name. I don't know why there are two spaces - it might be so that the recipes screen can just interpret the first character and display the rest, but that raises the question of why it couldn't interpret the first character, insert two spaces and display the rest. Was I really this stupid at this point? Of course, by a further stroke of genius, none of these characters are safe ones to just stick into request data, so when they're shovelled into the database I do some further conversion to make them conform to a standard on the site that's analogous but entirely different from that of the game. This time, the characters are "I", "A" and "X". I can't remember why I might have specifically chosen those, other than for convenience in that the recipes could then be sorted alphabetically on the page in order of importance with no further effort. I'll just let you react to this for yourself.
Just about the only thing the database does right is that it has unique IDs for players, instead of relying on their names not to collide (which I remember was an adaptation I made much later on), and that most of the other fields in the player table do actually store one identifiable bit of data. And that game events are stored in a separate table instead of somehow cramming them into this one as well - apparently I had learned how databases work by that point.
Of course, this is something that you face when you let a personal project go on for (help) four years - it's a Forth Rail Bridge problem, where as soon as you've finished everything, the older bits are looking so tatty that you need to redo them, but once you've done that, you've found better ways to do everything and want to start something else all over again. It looks pretty good to the player in the end, though, doesn't it?
I suppose you can try hacking around in the request that the game sends to the website, now. I'm not fixing it if you break your save.
