Connecting, querying and disconnecting to a SQLite DB in a single method is viable?

Hello to all,

It´s my first post, I am in a very early stage of designing some kind of my first video game. Indeed, I´m still not sure if the idea is viable.

The title of this topic is self explanatory.

I would like to develop an AI abble to undertake a wide range of actions acording to the context. In example: if some conditions are verified, then some village will send a merchant to other village with some determined buying-sellying order.

Well, I consider to create a database manager class that abstracts the access and query to DB, to finally have a method called like “check_markets”, for example, where some village object, acording to her properties, checks the surplus or deficit of all the products available in the other villages and then determine who products send to sell or buy and where, etc.

Can I make some like this?

method check_data (attributes_to_get, from_what_entity)
- do the connection stuff
- do the requested query
- do the disconnection.
- Return the data to be accesed by some methods like the above mentioned “check_markets”.

end of the method.

Some orientation about how to do ¿abstraction? were wellcome. I don´t know if this term, abstraction, is precise enough (I´m not english native speaker). What I mean is making a DB manager class accesible from other class in a friendly way for me. Able to do stuff like “check_nearest_enemies” or "track_down_for_recent_presences (in the coordinates where is performed the action)… including the operation to return the exact data from DB and can aply on it the correspondant logic inside the method.

Many thanks.

Yes, that is very doable. You should look up the Data Abstraction Layer pattern.

Basically, you create an interface with all the methods you will need. I like to create the first implementation that just stores all the data in lists in memory. Then use dependency injection to gain access to that data management object.

Once you have the game up and running, you can start creating your implementation that uses a database backend. Swap out the in-memory version in your DI container, and if the game runs the same as before, you are golden.

I use this pattern all the time in my games. For example, I have an app that keeps track of high scores. While I was developing it, I just stored the high scores in memory and they didn’t persist between sessions. The version currently on the app stores stores high scores in a SQLite db on the device, so the player can at least track their own high scores across sessions. I’m working on a version that stores the high scores on PlayFab, once that is ready it’ll be one line of code to swap SqlHighScores -> PlayFabHighScores

Hope that helps, I can point you at some of the SQLite wrappers I use if you need help in that area.


Related to that… I´m in a point where I don´t know if certain ideas are feasible or simply silly things, a nonsense. But I need to know if that designing ideas are “thinkable” or not before entering into code.

About the map, the characters ubication, information to compound the representation in 2D, etc… this map is conformed by few png files (1500 pixel x 800 px aprox.), being every pixel a coordinate, a place. One png file is simply a layer of regions, filled each region with different colors, other represents the bioma, other the roads and infrastructures.

First time the game starts, or when you begin a new game, all the pixels will be “checked” and every single pixel coordinate will be stored in a row in the correspondant place in the DB, every different png file will be "do foreach… ", then get the colour value, then compare with the code associated to that colour and then, store the adecuate property assinged to that colour in the adecuate coordinate field in the DB. If players or pnc do some action that changes the world, the correspondent field in DB changes but also changes the color of the pixel in the adecuate png file via setting colour pixel.

If every coordinate is stored, I will only need to change the status in the DB nor rewrite a pixel in a png file, a forest biome place has been burned, or corrupted by the evil (where the tile_status were 1, now is 5). But I want to draw a 2D representation of the place where the player is, if 300 meters (3 pixels) beyond him there are hills instead the plains where actually is, the background should show a hilly landscape acordingly to the actual coordinate minus or plus X or Y coordinates. The 2D graphic system compounds the scenario reading the actual position and a line of pixels up or down o wherever. So, I think I need to rewrite the pixel of that “maps” files and the graphics stuff will be done faster by accesing pixels.

Is this stuff redundant? has sense? should I use color codes in graphics files to store some properties?

I don´t need to know code examples at this stage, just being redirected to the right sources.


Thank you!

It helps, I lack of concepts and I don´t know if I think correctly in terms of programming, and you brought some new ones to refine my search.

If you’re planning on doing a lot of DB IO, You should probably write a DB abstraction layer (like a DBManager or something) that opens and maintains a small number of DB connections in a pool. Opening and closing database connections tends to be fairly expensive operations, so you ideally want to keep the connections open for as long as you plan on using it. This also depends on how large the DB is / how much data is in it. The larger the DB, the longer it will take to open a connection.

Some local DBs tend to be a lot faster at opening and closing connections (since they’re just local files) than remote / server based DBs like MySQL etc, but it still comes down to your use case, and how much data is involved. You might find it better to just open a connection to the DB and keeping it open than constantly opening and closing the connection.

If your application is primarily single threaded or all your DB reads are serialized, then opening and maintaining a single connection should suffice.

After some research, I think I will bet for using LINQ + SQLITE. Thanks, I couldn´t figure how to store data persistently when is contained in an array of two “keys”, storing x and y coordinates. And how to access to an exact coordinate and assing to this some properties like owned_by_whatever. And in a way I can understand.

An horizon of new complications is open now, one step more. Thank you both.