Jump to content
  • Advertisement
Sign in to follow this  
XTAL256

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

This topic is 2922 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
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!