• Advertisement
Sign in to follow this  

Unity Object model using data from SQL database (+ Qt)

This topic is 2647 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Following up from my other question, i have decided to use a database to store data for my application. But i am not quite sure how i will use it with my C++ object model.

I currently keep a list of objects, and these objects contain data. I want to move that data into an SQLite relational database and have my objects access it using SQL, but i am not sure how to map the rows in a table to objects in my application.
It's not like i am reading data from the database and storing it in my objects, my objects will just be like shells and they will access the data directly from the database (which is in memory).

One way would be to keep a list of objects in my application and keep a table in the db. I would use an id as the primary key in the DB and that id is the index of the corresponding object in the list. But i'm not sure if that's the best way to do it. Perhaps Qt provides a better mechanism (QSqlRecord perhaps?).

I also don't know how to store references to other objects. Say my object model looks like this:

class ObjectA {
int id;
int otherData;
ObjectB anObject;
};

class ObjectB {
int id;
int otherData;
};


Then i would define an SQL table like so:

create table ObjectA(
id int primary key,
otherData int;
/*ObjectB ??? */
);

create table ObjectB(
id int primary key,
otherData int;
);


And i have a list of ObjectA objects. I could store ObjectB in the DB as a separate table, but how would i have each ObjectA reference an ObjectB?


Another thing i am concerned about is speed. Although speed is not a critical factor in my application, i do want it to be fairly fast and efficient. So i was wondering if accessing data in a DB via SQL statements would be noticeably slower than accessing data stored in the object itself.
When i want to access some data in bulk (say, get all x variable of each object in my list), I would think that a single SQL statement would be the faster than iterating over each object and have it execute a separate statement.
i.e.

QSqlQuery query(database);
query.exec("select otherData from ObjectA");
while (query.next()) {
doSomething(query.value(0));
}


Would be more efficent than

for each ObjectA in list {
QSqlQuery query(database);
query.exec("select otherData from ObjectA where id = " + each.id);
doSomething(query.value(0));
}



But how would i do the first example in an object-oriented way? That is, i do not want to go accessing the DB directly everywhere, i want to use my object model.

Share this post


Link to post
Share on other sites
Advertisement
I also am wondering what component should be responsible for communicating with the database. Should each object make SQL queries, or should i have a separate class to deal with that?

Share this post


Link to post
Share on other sites
Quote:
So i was wondering if accessing data in a DB via SQL statements would be noticeably slower than accessing data stored in the object itself.

Much slower. At the very best, the machine still has to interpret your SQL statement before it can be executed. Use the database for permanent storage if you would like, but not for in-game.

Quote:
So i was wondering if accessing data in a DB via SQL statements would be noticeably slower than accessing data stored in the object itself.

Either lose the object model or lose speed. Those are you choices. Databases are not object oriented.

Here's the problem. Let's say you have 100 objects. Each object is responsible for loading itself from a database. Now you've got 100 database queries where just one would do! This is why OOP doesn't play well with databases.

It would be better to say:

list_of_objects GetObjects( criteria ) {
data = database->Query( query );
foreach (data) { list_of_objects.Add( new Object(data) ); }
return list_of_objects;
}

Consider a Factory.

Share this post


Link to post
Share on other sites
Quote:
Original post by XTAL256
And i have a list of ObjectA objects. I could store ObjectB in the DB as a separate table, but how would i have each ObjectA reference an ObjectB?


You'd definitely want to store the objects of different type in another table - you'd use the unique primary key of the object you want to refer to as a foreign key.

For example


CREATE TABLE ObjectsOfTypeA
(
ID SERIAL, /* Primary key */
Data bytea,
OwnedObjectB INT, /* Each Object A owns a single Object B */

CONSTRAINT objecta_pkey PRIMARY KEY (ID),
CONSTRAINT objecta_owned_object FOREIGN KEY (OwnedObjectB)
REFERENCES ObjectsOfTypeB (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE ObjectsOfTypeB
(
ID SERIAL, /* Primary key */
Data bytea,

CONSTRAINT objectb_pkey PRIMARY KEY (ID)
);




Something that that effect anyway... hth

Share this post


Link to post
Share on other sites
Maybe i won't use a database after all. The only reason i was going to do it is so that i could implement an advanced search feature which would use an SQL query to find the data (again, this is detailed in my other post).

Share this post


Link to post
Share on other sites
It might still be a good idea for you to use a database - here's the important part - if it is the best solution for other areas of your app.

I looked into using an SQLite database to store/retrieve my object attributes. I did some timings and it was only taking a few milliseconds to retrieve a whole bunch of matrices via a prepared SELECT query (this was on my netbook).

The slowest part was when I wanted to update those matrices from my app. Using an in-memory database the performance was acceptable (can't quite remember but might have been around 50ms), but when writing to a disk based database it was taking upwards of 700ms to write the matrices back.

Depending on what you want to do the DB might be a good solution, particularly if the database does not need to reside on disk constantly.

Share this post


Link to post
Share on other sites
To answer another question, you typically don't want the objects to know how to persist and load themselves from the database. You'd want to write a database layer that would take your game objects and map them into SQL statements, or know how to load objects from the database. This is known as a Data Access Layer, or Data Access Objects (DAL or DAO). Some people generate this by using an ORM (Object Relational Mapper) library. Hopefully that will give you some idea on how to begin researching this.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
  • Advertisement
  • Popular Now

  • Advertisement
  • Similar Content

    • By Atwo Studios
       
      Hey guys,

      Anthony here from Atwo Studios bringing you some new updates for the new year!
      In this video I go over our game ROY, the new games and some general updates to the company!

      If you have not checked out ROY feel free to give it a try! Many people have said they enjoyed the game thus far!
      ROY: https://goo.gl/o6JJ5P
       
    • By Affgoo
      https://play.google.com/store/apps/details?id=com.NE.Alien
      still a lot of work to do, but its pretty stable  please let me know what you think <3
      Atlas Sentry is a game of destroy everything. Using your turret, simply swivel and shoot your way to victory, upgrading your weapons to unleash destruction on the variety of spaceships. The bigger your combo’s the more score you get! Earn silver as you play and then purchase new weapons and abilities to better deal with your enemy. Different enemies use different tactics and weapons, work out your own priorities in their destruction order. 

      Features: 
      **2 different game modes 
      **A level select mode with 20 difficult levels including a final boss, can you defeat it? **Arcade mode of endless destruction, how long will you last? 
      **High scores to compete against others, see who can take the top spot. 
       
    • By Chamferbox
      Chamferbox, a mini game asset store has just opened with some nice game assets, 
      Here you can find a free greek statue asset 

      Also check their dragon, zombie dragon and scorpion monster out:



      They're running the Grand Opening Sale, it's 30% off for all items, but for gamedev member, you can use this coupon code:
      GRANDOPEN
      to get 50% off prices What are you waiting for, go to
      http://chamferbox.com
      and get those models now!

      View full story
    • By Dafu
      FES Retro Game Framework is now available on the Unity Asset Store for your kind consideration!
      FES was born when I set out to start a retro pixel game project. I was looking around for an engine to try next. I tried a number of things, from GameMaker, to Fantasy Consoles, to MonoGame and Godot and then ended up back at Unity. Unity is just unbeatable in it's cross-platform support, and ease of deployment, but it sure as heck gets in the way of proper retro pixel games!
      So I poured over the Unity pipeline and found the lowest levels I could tie into and bring up a new retro game engine inside of Unity, but with a completely different source-code-only, classic game-loop retro blitting and bleeping API. Months of polishing and tweaking later I ended up with FES.
      Some FES features:
      Pixel perfect rendering RGB and Indexed color mode, with palette swapping support Primitive shape rendering, lines, rectangles, ellipses, pixels Multi-layered tilemaps with TMX file support Offscreen rendering Text rendering, with text alignment, overflow settings, and custom pixel font support Clipping Sound and Music APIs Simplified Input handling Wide pixel support (think Atari 2600) Post processing and transition effects, such as scanlines, screen wipes, screen shake, fade, pixelate and more Deploy to all Unity supported platforms I've put in lots of hours into a very detail documentation, you can flip through it here to get an better glimpse at the features and general overview: http://www.pixeltrollgames.com/fes/docs/index.html
      FES is carefully designed and well optimized (see live stress test demo below). Internally it uses batching, it chunks tilemaps, is careful about memory allocations, and tries to be smart about any heavy operations.
      Please have a quick look at the screenshots and live demos below and let me know what you think! I'd love to hear some opinions, feedback and questions!
      I hope I've tickled your retro feels!



      More images at: https://imgur.com/a/LFMAc
      Live demo feature reel: https://simmer.io/@Dafu/fes
      Live blitting stress test: https://simmer.io/@Dafu/fes-drawstress
      Unity Asset Store: https://www.assetstore.unity3d.com/#!/content/102064

      View full story
    • By DevdogUnity

      Ho ho ho
      Tis the season of Christmas surprises, and we have a awesome one for you! 🎅  
      Sponsored by all your favorite Unity Asset Store developers, Nordic Game Jam, Pocket Gamer Connects, and co-hosted by Game Analytics, we (Joris and I – Devdog) are launching the second edition of our yearly Christmas Giveaway Calendar for all Unity game developers!
      You can already now sign up right here.
       
      So what’s this all about?
      For the past weeks, we’ve been collecting sponsored gifts related to Unity (asset vouchers, product keys, conference tickets etc.), and throughout each day of December leading up to Christmas Day on the 25th, we will be sending out these sponsored gifts as early gamedev Christmas presents via e-mail to hundreds of lucky winners.
      The total prize pool is at $35,000, with over 1200 presents donated by the awesome sponsors!
       
      Merry Christmas from Devdog, Game Analytics, and every single one of the sponsors.

      View full story
  • Advertisement