Embedded relational databases


  • Garbage Person

    So I'm looking at my game project again, and I need an internal database.

    My initial plan was "Just use SQL Server Compact", because I'm familiar with full fat SQL and I need to do some pretty intensive data work for which none of the opensores embeddable RDBMS' I'm familiar with are vastly unsuited (of course, never having used SqlCE in anger, it might also suck)

    Seriously. I need to bung around gigs of data and gazillions of records. Fast retrieval, reasonably fast writes, strong datatypes and a good query planner and ACID transactions are a must. This thing is going to be massively multithreaded, so thread safe locks are a must. A halfass system with shitty or minimal indexing support ain't gonna cut it.

    But I've decided to run with MonoGame, which opens up cross platform development. And you can't go cross-platform with SQL Compact.

    Of course, the alternative to this is to just use a full up object model and wank around trying to serialize/deserialize and deal with my own savegame file format. Which, given the quality that I'm seeing in the free, cross-platform embeddables I've wiki'd, is probably the sane course of action.



  • @Weng said:

    I need to bung around gigs of data

    You need to... what???

    @Weng said:

    Of course, the alternative to this is to just use a full up object model and wank around trying to serialize/deserialize and deal with my own savegame file format.

    Works (for various values of "works") for Skyrim, and Skyrim is the best game. Just serialize to JSON and gzip it.

    BTW, why's there so much data? Are all of those millions of records being generated by the player? Because generally even the most complex games only have saves of a few MB, max.

    Super bonus nerd trivia: CreationEngine/Gamebryo's database format is almost identical to the old Mac Classic resource fork format.



  • Using an RDBMS is going to offer many advantages over rolling your own persistence format. If anything, it gives you scalability, an extra layer of abstraction, and puts you in the mindset of making things 3NF or higher, etc.

    Does the database have to reside on the device, or could it be pushed out to the cloud? Gigs seems pretty intense.

    What I do in my own project is somewhat of a mix of the two extremes you describe. Client machines read an XML file export of relevant data from the central database that is consumed into objects, while the server talks directly to a SQL Server instance via ODBC. Any major updates to the base data (e.g. item stats, monster stats, etc.) could get pushed out with application updates, or the player could download a copy every time s/he connects to the server (right now, this XML export is like 50kb uncompressed).



  • I was looking into these for my own project.

    In the end, the one that seemed the best was firebird. Full ACID compliance. Can work as both embedded and server. Seems to be highly underrated. Possibly due to the maintainers being a bunch of non-hip Russians.

    Just to be clear, still haven't actually used it. Just researched.


  • Garbage Person

    Yeah, it's user data. The elevator pitch is "An epic-scale time-sliced based semi-hard-scifi space 4X game set in a procedurally generated universe that is stylistically Eve Online meets Dwarf Fortress, has a one night stand and births a baby with Aspergers"

    Things to consider: Lightspeed information lag (which is basically partially optional as the player is basically omniscient, but I'll optionally include the information anyway because I personally find the problems keeping to that rule introduces to be fascinating) means that we not only need to know where everything is, but where everything has (basically ever, given arbitrary sensor resolution) been.

    And the way various FTL (yes, there are many) travel and communications technologies will interact with that.


  • Garbage Person

    @Groaner said:

    Does the database have to reside on the device, or could it be pushed out to the cloud? Gigs seems pretty intense.
    Technically could be, but in the case I ever decide I want to dostribute this, that introduces monetization and upkeep challenges.



  • @Weng said:

    "An epic-scale time-sliced based semi-hard-scifi space 4X game set in a procedurally generated universe that is stylistically Eve Online meets Dwarf Fortress, has a one night stand and births a baby with Aspergers"

    "Halo 2 is a lot like Halo 1, except it's Halo 1 on fire going 120 miles per hour through a hospital zone chased by helicopters and ninjas. And the ninjas are all on fire too."

    • Jason Jones

    Seriously though, good luck to you, but it sounds pretty goddamned ambitious unless you have 20 guys working for you.

    @Weng said:

    And the way various FTL (yes, there are many) travel and communications technologies will interact with that.

    But once the player develops FTL, they'd just replace all the old light-speed stuff. I guess you could keep it for things like supernovas (when will Alpha Centauri see that this happened?) But they'd already know it happened years ago, because they'd be idiots to not install a FTL news channel relay or whatever.


  • Garbage Person

    @blakeyrat said:

    Seriously though, good luck to you, but it sounds pretty goddamned ambitious unless you have 20 guys working for you.
    Fortunately I'm not in it to make money. I'm in it because I want to build the thing that I want to play. Sure, it's essentially a universe simulator. Is there anything wrong with wanting to be God?

    Fortunately, the sheer scope of the problem domain means that it can be chunked into big pieces that are satisfying in their own right - the Dwarf Fortress development model.

    @blakeyrat said:

    But once the player develops FTL, they'd just replace all the old light-speed stuff. I guess you could keep it for things like supernovas (when will Alpha Centauri see that this happened?) But they'd already know it happened years ago, because they'd be idiots to not install a FTL news channel relay or whatever.
    The trick is to shove truly cheezy tech far enough up the Kardashev scale that any reasonable player ain't gettin' there (in fact, I'm considering making all the big, nasty tech impossible to come by naturally, and the only way to get it is to literally take it off the corpse of the Endgame alien species).

    Consider an FTL comms system where:

    1. All nodes are instantaneously networked
    2. Sensors have a finite range but are governed by light speed.

    Base A in one region communicates with Ship B in another region instantaneously. Ship B sees Trouble coming. Ship B tells Base A about it. Ship B is destroyed. Base A now knows only the last known position of Trouble from when Ship B was destroyed - future knowledge will have to come in at light speed.


  • Garbage Person

    This actually looks quite good. I'll give it a try.



  • @blakeyrat said:

    Seriously though, good luck to you, but it sounds pretty goddamned ambitious unless you have 20 guys working for you.

    I want a 20-man team, but people like getting paid for some reason. :(

    @Weng said:

    Fortunately I'm not in it to make money. I'm in it because I want to build the thing that I want to play.

    This is probably the best motivation for embarking on the long, masochistic and yet oddly satisfying journey that is game development.



  • You can do that without trucking around gigs of data. My first recommendation is figuring out a deterministic algorithm for state calculation. If this is all local you only need to render the players immediate world and buffer zone. There's no reason to calculate every bit of the world in advance, do a 'universe will look like this in a day if no player shows up ' then extrapolateover time. Bring it back in if the player actually does show up to recalculate the world based on deltas.

    My phone updated software today, now it's implicitly adding a space after every word and back tracking when you use punctuation. If you have a wrong word auto selected for you, there's an extra space you have to delete. This is fucking awful.


  • Java Dev

    I've seen network-based FTL before, where exploration goes outward from the nodes at light speed. It applies to mass effect, but I don't think that's where I first saw the idea.
    As Matches mentions, you don't need gigs of data, certainly not in the client. At most the server needs to be aware of that stuff.

    You may even be able to feasibly narrow down communication even further - omnidirectional light-speed broadcasts are inefficient. Instead you'd use narrow-beam communication, which can only be seen in a very limited region of spacetime.

    If you just foward communication between known nodes (instantanious if they're FTL nodes, light-speed otherwise) there's much less you need to actually keep track of over longer timescales.


  • Garbage Person

    Yes, deterministic interpolation covers a lot - I'm planning on it.

    But it falls apart as soon as you need to track something that acts in response to stimuli or god forbid is touched by an actual player. Then you need to log events, which starts eating disk.

    Also, recalculating the state of everything every time you need it is expensive (especially since I'm planning on nontrivial 3-5 body gravitic interaction - yes, you can totally punt that planet into the star if you can manipulate gravity hard enough). Disk is cheap.

    Maybe my back-of-the-envelope data calculations are too pessimistic, but my gut feeling is that they're probably optimistic.

    Basically, I'm going to be looking at a tiered model:

    1. In-memory/live data structures: "Active" objects, "Active" lightspeed history
    2. On-disk/RDBMS: "Nearline" objects - Basically things in space "near" (from both a physical and lightspeed bubble aspect) players. These have current state and historic states for each intersection with a lightspeed bubble, as well as a set of 'future historic' checkpointed state deltas for each lightspeed bubble.
    3. On-disk/RDBMS: "Inactive" objects and associated event history for interpolation on loading (with periodic checkpoints)
    4. On-disk/RDBMS: "Hypothetical" objects. Statistical data on stars and such to be used to procedureally generate lower tier stuff when someone wanders close enough.

    And regarding tight beam communications:
    I'll basically be tracking "information" as an object.
    Q: What's the difference between a communications laser and a laser weapon?
    A: How much hull ablates away when it hits you.

    Q: What's the difference between an alcubierre drive and a pulsar?
    A: It's a lot harder to accidentally destroy your homeworld with a pulsar.

    Yes, I plan on players unintentionally discovering these facts of life.



  • You might want to give db4o a look. It's an embedded Object DB with support for Java and .Net. This means, no queries, no ORM and no SQL mappings.

    WTF? Just went there to see the version and it seems they're dropping support and leave only the community version.



  • Yes, there's lots of disk usage.

    but you're not moving gigabytes of data, ever.

    If you are, you're doing it actually wrong.


  • Discourse touched me in a no-no place

    @Matches said:

    Yes, there's lots of disk usage.

    but you're not moving gigabytes of data, ever.

    If you are, you're doing it actually wrong.

    Or you are getting serious about buying network bandwidth. You don't do that for anything you might want to let an ordinary user on; home networks aren't designed for that sort of uplink bandwidth (and many users won't thank you for having that much shipped down to them either, what with bandwidth caps in many backwards places…)



  • At some point you need to ask yourself: do these systems you're creating actually contribute to the fun of the game?

    Think about Peter Molyneux and learn from his example. Why do you think they removed the "track each individual tree's growth" feature of Fable?


  • Java Dev

    Also keep thinking about what you can get rid of easily. I believe actual many-body gravity interaction is very computationally intensive (and inaccurate) - you'd rather optimise that away as much as possible. This possibly gets even worse if you can land on that planet - you'll need to procedurally patch an existing geography to adapt to changing solar input.

    On the communications front, remember all players can communicate with each other out-of-game instantaneously. Short lags may be annoying - long lags will be circumvented.

    If you are doing sublight interplanetary travel, you are talking about journeys that take weeks at minimum. Month with current technology. Players won't want to wait that long, so you will likely want to use an accelerated timeframe for players to experience. IIRC light travel from earth to mars is 5-10 minutes; if time compression reduces that to 5-10 seconds experienced by the player, it may not be worth the bother of implementing it at all.

    These matters are worth contemplation: How long does it physically take to travel from earth to mars with various technologies, how long is the player expected to be willing to wait for that journey.


  • Java Dev

    Seems those numbers aren't as bad as I thought - space can be disorienting. While a free transit from earth to mars takes months, playing with wolfram alpha a bit to get a continuous-thrust solution lands me at roughly 3 hours when earth and mars are at maximum distance, using 1g of thrust. Which is probably actually doable in real time.


  • Garbage Person

    Timing is pretty well handled by the nature of the game. We aren't doing realtime, we're ding time slice. Time slice is pretty rare, but the gist of it is this:

    It's effectively turn based, but the interval between turns is player defined (subject to interrupt by qualifying events.

    Essentially, at the end of every turn the player is asked how long the simulation should run before dinging them for input. If no players need to provide input, the simulation runs as fast as hardware allows.


  • Java Dev

    Ah. Yes, I can see that working well on a limited number of players. Somehow I'd gotten MMO into my head.



  • @Weng said:

    So I'm looking at my game project again, and I need an internal database.

    My initial plan was "Just use SQL Server Compact", because I'm familiar with full fat SQL and I need to do some pretty intensive data work for which none of the opensores embeddable RDBMS' I'm familiar with are vastly unsuited (of course, never having used SqlCE in anger, it might also suck)

    Seriously. I need to bung around gigs of data and gazillions of records. Fast retrieval, reasonably fast writes, strong datatypes and a good query planner and ACID transactions are a must. This thing is going to be massively multithreaded, so thread safe locks are a must. A halfass system with shitty or minimal indexing support ain't gonna cut it.

    But I've decided to run with MonoGame, which opens up cross platform development. And you can't go cross-platform with SQL Compact.

    Is SQLite unsuitable for some reason? It's about the most stress-tested and reliable embedded RDBMS you'll ever find.


  • Garbage Person

    Loosely typed sql is an abomination.


  • FoxDev

    @Weng said:

    Loosely typed sql is an abomination.

    ...

    SELECT * FROM some_table WHERE '1' = 1;
    

    in every SQL server i know about that;s exactly the same as saying:

    SELECT * from some_table;
    

    strongly typed SQL? not seeing that here.


  • Discourse touched me in a no-no place

    @Weng said:

    Loosely typed sql is an abomination.

    Not nearly as bad as losing data.


  • Garbage Person

    Type coercion is one thing. Storing 'quesadilla' in a numeric column is quite another.


  • Discourse touched me in a no-no place

    @Weng said:

    Type coercion is one thing. Storing 'quesadilla' in a numeric column is quite another.

    But why are you trying to put that in there in the first place?



  • Because NoSQL is a thing? :trollface:


  • FoxDev

    well... 'quesadilla==' is a valid BASE64 encoded number, and i could even see leaving off the trailing == to save a bit of space (insane but i can see it)

    but i don't think that's what you meant. ;-P


  • ♿ (Parody)

    @dkf said:

    But why are you trying to put that in there in the first place?

    Well, exactly. Wouldn't you like to find and fix that bug early?


  • Garbage Person

    Nah, it needs to become pervasive and corrupt other parts of the app first.


Log in to reply