|
|
Post by hipsterdufus on Mar 31, 2024 17:52:49 GMT
Hi, couple questions:
1. Let's say I have a collection of Items (plain C# class). If I want to store these into a table called PlayerInventory, I have to create the SQL insert statement with the table and loop through each item - I cannot use InsertAll because that will by default try to insert into a Items table?
2. If my Item class has a reference to another object of ItemDefinition how should I handle that when storing in SQL? I guess it's recommended to only store the ItemDefinitionID? And I'll have to get the ItemDefinition in code at runtime if I ever need it?
Thanks.
|
|
|
|
Post by echo17 on Apr 1, 2024 8:42:17 GMT
1. Let's say I have a collection of Items (plain C# class). If I want to store these into a table called PlayerInventory, I have to create the SQL insert statement with the table and loop through each item - I cannot use InsertAll because that will by default try to insert into a Items table? Correct. I rarely use the helper methods Insert or InsertAll. They are useful in some cases, but I prefer to just use native SQL syntax myself. 2. If my Item class has a reference to another object of ItemDefinition how should I handle that when storing in SQL? I guess it's recommended to only store the ItemDefinitionID? And I'll have to get the ItemDefinition in code at runtime if I ever need it? Yes, I'd just store the ID of the Item in your ItemDefinition class. For retrieving data, you can set up special classes that contain all the fields of your joined tables query. They don't have to mimic the structure of the table. ORM is helpful in that you can set up multiple definitions of queries independent of the actual table structures. |
|
|
|
Post by hipsterdufus on Apr 1, 2024 13:31:34 GMT
Ok I think I understand a little better now. Currently I have it working by populating the child reference in code but maybe the proper way is to use a join statement? I'm not 100% clear how the child object would get populated but I have another slight issue - I put the ItemDefinition table in another database (since it's static data and doesn't need to be saved). So I guess I cannot use a join statement in this case?
|
|
|
|
Post by echo17 on Apr 2, 2024 9:57:55 GMT
I don't think you can do joins on an update. You'd have to do separate update commands for the parents and children. I'd recommend doing a web search for sqlite tutorials. There are lots out there that can help you with relational databases.
Tables need to be in the same database in order to use joins.
|
|
|
|
Post by hipsterdufus on Apr 2, 2024 17:05:29 GMT
Ok, one more question if you don't mind. I'm a little confused because I thought SQLite was an in-memory db but this implementation is a file based db correct? So it's best to read all the data at once into memory and then interface with that at runtime as opposed to interfacing directly with the db many times during runtime since the file access is quite a bit slower. Am I understanding it correctly?
|
|
|
|
Post by echo17 on Apr 3, 2024 10:35:42 GMT
Ok, one more question if you don't mind. I'm a little confused because I thought SQLite was an in-memory db but this implementation is a file based db correct? SQLite, and databases in general, are used to persist data (store permanently). Memory is flushed when an application quits, so that wouldn't work for persisting data. Files are needed for this. So it's best to read all the data at once into memory and then interface with that at runtime as opposed to interfacing directly with the db many times during runtime since the file access is quite a bit slower. Am I understanding it correctly? It depends on your use case whether you want to read in all data or not. If you are pulling in all the items for a select box, for instance, then yes. If you are reading in all records from a financial app or something, that would probably overwhelm the memory if you had millions of records. I've never experienced slowdown using SQLite before. Even queries against huge databases should run in milliseconds. I'd highly recommend going through some SQLite tutorials. It's a bit overwhelming at first, but easy to pick up. |
|
|
|
Post by hipsterdufus on Apr 3, 2024 16:33:26 GMT
Gotcha, thanks. I did some reading and I guess there's a way to use sqlite as an in-memory database for temporary data. I just noticed when I was doing any sqlite access my game would stutter for a millisecond so there's some latency with accessing the db file. Yeah I guess if you have a huge dataset it might not be great to read it into memory. It's all making sense now.
|
|
|
|
Post by echo17 on Apr 4, 2024 9:54:44 GMT
Are you saying you are trying to access the database in the middle of gameplay? That is usually not how games are structured. You'll notice in major games there are save and load points, usually denoted by an icon in the corner of the screen. These save and load points are not during heavy action as they have to interact with data on the disk, which as you noted, is slower. Try to do your read and writes outside of high performance times, like at the end of a major action or between levels.
|
|
