4 ms·

I've never had the occasion to use an SQL database. But say I was writing a game using C++ - at what point would I go from managing a bunch of maps or vectors o

I've never had the occasion to use an SQL database. But say I was writing a game using C++ - at what point would I go from managing a bunch of maps or vectors of entities to using a SQL database? If I was writing a ray tracer and needed to store vertices, would it makes sense to use a SQL database? How about for a list of object? Or textures? In general I often need to filter on objects, update object state, generate new objects, remove some others, etc. but I never know when I should stop thinking containers and start thinking "aha! time for SQL"

cdelsolar 10y ago
Basically any time you want to persist data to disk.
gh02t 10y ago
It's not really appropriate for any of those things. You should use a database to store data that you want to keep after the program terminates, not so much transient things like in-memory data structures. It's also best used for relational data- stuff that is logically linked together. For developing a game, maybe storing item tables with items and stats or the player's inventory might be good candidates. Sqlite in particular is good for this because it's easily embedded and a lot of games use it from what I know. This is oversimplifying a good bit, but it's hard to completely describe the scope of relational DBs.
- optforfon 10y ago
  I've definitely come across it being referenced for use in games. Specifically the "Component" design pattern is supposed to use it. It's kinda described here: http://gameprogrammingpatterns.com/component.html http://gameprogrammingpatterns.com/component.html But the author doesn't take it to it's logical conclusion. I found the full explanation by it's inventor here, in this slide deck: http://scottbilas.com/files/2002/gdc_san_jose/game_objects_slides.pdf http://scottbilas.com/files/2002/gdc_san_jose/game_objects_s... But I'm fuzzy on the details
- int_19h 10y ago
  >> For developing a game, maybe storing item tables with items and stats or the player's inventory might be good candidates Doubtful. Player inventory is not going to be large enough to bother, and item tables you'll want to be in-memory anyway, so you might as well just read them from CSV, JSON, XML etc (and that way you can easily edit them, too). I would say that SQLite only makes sense when your dataset is too big to be entirely loaded into memory in a cooperative environment (i.e. assuming that your app is not allowed to hog the entire memory). I'd say that starts at tens of megabytes.
  - gh02t 10y ago
    It could be worth it if you need to do lots of relational queries with complex inventory management. I was imagining e.g. an RPG that would ship the stats for all its items in a sqlite data file and then you can store the player's stats and inventory with foreign keys pointing to the item table. You're gonna have to store that data somehow and if you've got enough items and/or complex enough inventory management it seems like maybe you might want to consider sqlite as it already exists and provides a lot of relevant features. I don't consider size so much as whether or not there is a need to persist data and the complexity of relationships; size is more of a factor in "should I use sqlite or should I use a beefier database like Postgres." I know sqlite is used heavily on iOS and Android and a lot of people use it as a glorified serialization format. Probably not the best in most cases but hey sqlite is so lightweight that it doesn't have much downside. I tend to use it as intended as a lightweight database myself but hey if it works it works.
    - int_19h 10y ago
      It is far easier to store such structures as object graphs in-memory (i.e. your "foreign key" is a pointer/reference to the actual object). The navigation patterns would mostly be looking up properties on the item referenced by inventory, so it's not like you need to do joins etc (but even if you did, a join on in-memory object graph is still pretty easy and blazing fast). For C++ especially, I would recommend looking at Boost multi_index library. This gives you the ability to do fast lookups on a variety of keys across the same data. Pretty much the only benefit I can see from SQLite in those small dataset scenarios is when you need persistence and the ability to change subset of data in an atomic way (if you only need to save the entire in-memory dataset atomically, you can always just do the rename trick to ensure atomicity with far less overhead). Well, and, I guess, optimization of complicated queries - but I'm somewhat skeptical about the ability of their optimizer to use indices in a query that's really complicated; and simple ones are trivial to do explicitly.
jinjin2 10y ago
Instead of thinking "time for SQL" you should consider using a real object database (like Realm). In my experience that will map much better to the data structures in your program.
AtheistOfFail 10y ago
The main idea for a database is for storing data on disk and as a cheap man's way of sharing data across processes that are running at the same time. If you don't find yourself needing to share data between two processes at the same time or storing data on the system, you may never need it. SQLite can actually be used as a decent save system for a video game since you can just store into it and read from it. You could actually do a "Mass-Effect Style" storage system where you can store items from Game 1 and read it on Game 2. You could actually have your studio share a SQLite DB and have your games reference whether a user has played another one of your games.
- optforfon 10y ago
  I've definitely come across it being referenced for use in games. Specifically the "Component" design pattern is supposed to use it. It's kinda described here: http://gameprogrammingpatterns.com/component.html http://gameprogrammingpatterns.com/component.html But the author doesn't take it to it's logical conclusion. I found the full explanation by it's inventor here, in this slide deck: http://scottbilas.com/files/2002/gdc_san_jose/game_objects_slides.pdf http://scottbilas.com/files/2002/gdc_san_jose/game_objects_s... But I'm fuzzy on the details
Shorel 10y ago
The developers of Age of Empires used MSAccess to store the numbers that define hit points, size, speed, spawn rate, etc. of each of the units. This enabled the game designers to test changes in game balance without having to wait for modifications in the executable file. Depending on the design of your game, you can use SQLite in a similar way.

cdelsolar 10y ago

Basically any time you want to persist data to disk.

gh02t 10y ago

It's not really appropriate for any of those things. You should use a database to store data that you want to keep after the program terminates, not so much transient things like in-memory data structures. It's also best used for relational data- stuff that is logically linked together. For developing a game, maybe storing item tables with items and stats or the player's inventory might be good candidates. Sqlite in particular is good for this because it's easily embedded and a lot of games use it from what I know. This is oversimplifying a good bit, but it's hard to completely describe the scope of relational DBs.

optforfon 10y ago
I've definitely come across it being referenced for use in games. Specifically the "Component" design pattern is supposed to use it. It's kinda described here: http://gameprogrammingpatterns.com/component.html http://gameprogrammingpatterns.com/component.html But the author doesn't take it to it's logical conclusion. I found the full explanation by it's inventor here, in this slide deck: http://scottbilas.com/files/2002/gdc_san_jose/game_objects_slides.pdf http://scottbilas.com/files/2002/gdc_san_jose/game_objects_s... But I'm fuzzy on the details
int_19h 10y ago
>> For developing a game, maybe storing item tables with items and stats or the player's inventory might be good candidates Doubtful. Player inventory is not going to be large enough to bother, and item tables you'll want to be in-memory anyway, so you might as well just read them from CSV, JSON, XML etc (and that way you can easily edit them, too). I would say that SQLite only makes sense when your dataset is too big to be entirely loaded into memory in a cooperative environment (i.e. assuming that your app is not allowed to hog the entire memory). I'd say that starts at tens of megabytes.
- gh02t 10y ago
  It could be worth it if you need to do lots of relational queries with complex inventory management. I was imagining e.g. an RPG that would ship the stats for all its items in a sqlite data file and then you can store the player's stats and inventory with foreign keys pointing to the item table. You're gonna have to store that data somehow and if you've got enough items and/or complex enough inventory management it seems like maybe you might want to consider sqlite as it already exists and provides a lot of relevant features. I don't consider size so much as whether or not there is a need to persist data and the complexity of relationships; size is more of a factor in "should I use sqlite or should I use a beefier database like Postgres." I know sqlite is used heavily on iOS and Android and a lot of people use it as a glorified serialization format. Probably not the best in most cases but hey sqlite is so lightweight that it doesn't have much downside. I tend to use it as intended as a lightweight database myself but hey if it works it works.
  - int_19h 10y ago
    It is far easier to store such structures as object graphs in-memory (i.e. your "foreign key" is a pointer/reference to the actual object). The navigation patterns would mostly be looking up properties on the item referenced by inventory, so it's not like you need to do joins etc (but even if you did, a join on in-memory object graph is still pretty easy and blazing fast). For C++ especially, I would recommend looking at Boost multi_index library. This gives you the ability to do fast lookups on a variety of keys across the same data. Pretty much the only benefit I can see from SQLite in those small dataset scenarios is when you need persistence and the ability to change subset of data in an atomic way (if you only need to save the entire in-memory dataset atomically, you can always just do the rename trick to ensure atomicity with far less overhead). Well, and, I guess, optimization of complicated queries - but I'm somewhat skeptical about the ability of their optimizer to use indices in a query that's really complicated; and simple ones are trivial to do explicitly.

jinjin2 10y ago

Instead of thinking "time for SQL" you should consider using a real object database (like Realm). In my experience that will map much better to the data structures in your program.

AtheistOfFail 10y ago

The main idea for a database is for storing data on disk and as a cheap man's way of sharing data across processes that are running at the same time. If you don't find yourself needing to share data between two processes at the same time or storing data on the system, you may never need it. SQLite can actually be used as a decent save system for a video game since you can just store into it and read from it. You could actually do a "Mass-Effect Style" storage system where you can store items from Game 1 and read it on Game 2. You could actually have your studio share a SQLite DB and have your games reference whether a user has played another one of your games.

optforfon 10y ago
I've definitely come across it being referenced for use in games. Specifically the "Component" design pattern is supposed to use it. It's kinda described here: http://gameprogrammingpatterns.com/component.html http://gameprogrammingpatterns.com/component.html But the author doesn't take it to it's logical conclusion. I found the full explanation by it's inventor here, in this slide deck: http://scottbilas.com/files/2002/gdc_san_jose/game_objects_slides.pdf http://scottbilas.com/files/2002/gdc_san_jose/game_objects_s... But I'm fuzzy on the details

Shorel 10y ago

The developers of Age of Empires used MSAccess to store the numbers that define hit points, size, speed, spawn rate, etc. of each of the units. This enabled the game designers to test changes in game balance without having to wait for modifications in the executable file. Depending on the design of your game, you can use SQLite in a similar way.