SQL Query Organization

[dk]

Hi there,
I'm about to start a project with an mysql database. now comes the question how to organize all the queries. Do you have any tips?
My plan would be to put all the queries in one object and return them to the caller. Then the queries would look more or less like this:

QSqlQuery myClass::GetUser(int userID)
{
   QString s("SELECT * FROM User WHERE User.ID = userID;");
 
   QSqlQuery q;
   q.prepare(s);
 
   return q;
}

QSqlQuery myClass::GetUser(int userID)
{
   QString s("SELECT * FROM User WHERE User.ID = userID;");

   QSqlQuery q;
   q.prepare(s);
      
   return q;
}

To copy to clipboard, switch view to plain text mode 

My App will have maybe 100 - 200 queries, so, some organization might be a good idea.

any advice in very appreciated.
Thanks a lot in advance

[stampede]

I don't think returning query objects is a good idea. Imagine that you have 10 client classes using this code and someday you need to change the database structure - now you have to go through all the client classes code and update the query access.
Better option is to directly return the data from the database, without exposing internal database structure:

class User{
public:
    User(QString name, QString address, ...) :_name(name), _address(address) ... {
    }
    QString name() const{ return _name; }
    QString address() const{ return _address; }
    // .. other access methods
private:
    QString _name;
    QString _address;
    QString _whatever;
    ...
};
 
User myClass::GetUser(int userId)
{
   QSqlQuery q;
   q.prepare("SELECT name,address,whatever FROM User WHERE User.ID = :userId;");
   q.bindValue(":id",userId);
   if (q.next()) {
      return User(q.value("name").toString(), q.value("address").toString(), ...);
   }
   throw NoSuchUserException; //.. or another way of reporting an error
}

class User{
public:
    User(QString name, QString address, ...) :_name(name), _address(address) ... {
    }
    QString name() const{ return _name; }
    QString address() const{ return _address; }
    // .. other access methods
private:
    QString _name;
    QString _address;
    QString _whatever;
    ...
};

User myClass::GetUser(int userId)
{
   QSqlQuery q;
   q.prepare("SELECT name,address,whatever FROM User WHERE User.ID = :userId;");
   q.bindValue(":id",userId);
   if (q.next()) {
      return User(q.value("name").toString(), q.value("address").toString(), ...);
   }
   throw NoSuchUserException; //.. or another way of reporting an error
}

To copy to clipboard, switch view to plain text mode 

This way database access code and db structure are encapsulated and separated from the rest of the program.

[dk]

Thanks a million my friend.
The main idea behind returning the query is: i could use the model / view architecture, and simply pass the executed query to a qTableView.
So, it is better to write a custom model for each view or just use regular widgets e.g. qTableWidget to present the data?

Thanks again for the help

[stampede]

So, it is better to write a custom model for each view or just use regular widgets e.g. qTableWidget to present the data?

I don't know, it depends on the application. Maybe for your needs it will be just fine to use a simple table widget. I'm not a specialist on table view/model things, so maybe someone else will have some hints for you.
My point is, if you hardcode db access with queries you may find it inconvenient in some cases. For example, your version of "GetUser" method could be uncomfortable if we have to present user details in a labels or set of line edits for editing:

// here you can see 'GetUser' method name is not very self-explanatory
QSqlQuery query = db.GetUser(id);
ui.label_name->setText(query.value("name").toString());
ui.label_address->setText(query.value("address").toString());
 
vs.
 
User user = db.GetUser(id);
ui.label_name->setText(user.name());
ui.label_address->setText(user.address());
// etc

// here you can see 'GetUser' method name is not very self-explanatory
QSqlQuery query = db.GetUser(id);
ui.label_name->setText(query.value("name").toString());
ui.label_address->setText(query.value("address").toString());

vs.

User user = db.GetUser(id);
ui.label_name->setText(user.name());
ui.label_address->setText(user.address());
// etc

To copy to clipboard, switch view to plain text mode 

[dk]

True, you are right! def much more readable. But i do have an other question.
I can see the massive benefit of arranging the queries the way you explained. But how about inner joins? or a query from multible tables?

For example i designed the database in the way that a user has a user_status. so i created a table called user_status_tbl. with an ID and a Description
The user_tbl contains one column called user_status which referes to the user_status_tbl.

How can i implement that without having to programm the SQLQuery in C++ and also take advantave of the simplicity and speed of an SQLQuery?

Thanks again my friend, ou are so helpful!

[stampede]

Great, i'm glad I can help

But how about inner joins? or a query from multible tables?

For example i designed the database in the way that a user has a user_status. so i created a table called user_status_tbl. with an ID and a Description
The user_tbl contains one column called user_status which referes to the user_status_tbl.

How can i implement that without having to programm the SQLQuery in C++ and also take advantave of the simplicity and speed of an SQLQuery?

You can still use SQL code to do cross-table selections and return this data in a data structure, for example:

// extended version of User class
class UserExt{
public:
    UserExt(QString name, QString address, QString description...) 
        :_name(name)
        ,_address(address)
        ,_description(description) ... {
    }
    QString name() const{ return _name; }
    QString address() const{ return _address; }
    QString description() const{ return _description; }
    // .. other access methods
private:
    QString _name;
    QString _address;
    QString _description;
    ...
};
 
// it is just a pseudocode but i think you get the idea
User myClass::GetUser(int userId)
{
   QSqlQuery q;
   q.prepare("SELECT u.name,u.address,st.description FROM User as u, user_status_tbl as st, othertable as ot, ... WHERE u.ID = :userId AND u.id=st.id AND ot.id=st.id, AND ...;");
   q.bindValue(":id",userId);
   if (q.next()) {
      return UserExt(q.value("name").toString(), q.value("address").toString(), q.value("description").toString()...);
   }
   throw new NoSuchUserException; //.. or another way of reporting an error
}

// extended version of User class
class UserExt{
public:
    UserExt(QString name, QString address, QString description...) 
        :_name(name)
        ,_address(address)
        ,_description(description) ... {
    }
    QString name() const{ return _name; }
    QString address() const{ return _address; }
    QString description() const{ return _description; }
    // .. other access methods
private:
    QString _name;
    QString _address;
    QString _description;
    ...
};
 
// it is just a pseudocode but i think you get the idea
User myClass::GetUser(int userId)
{
   QSqlQuery q;
   q.prepare("SELECT u.name,u.address,st.description FROM User as u, user_status_tbl as st, othertable as ot, ... WHERE u.ID = :userId AND u.id=st.id AND ot.id=st.id, AND ...;");
   q.bindValue(":id",userId);
   if (q.next()) {
      return UserExt(q.value("name").toString(), q.value("address").toString(), q.value("description").toString()...);
   }
   throw new NoSuchUserException; //.. or another way of reporting an error
}

To copy to clipboard, switch view to plain text mode 

[dk]

Hello stampede,

very good idea with putting each query in an object. Much easier for further processing! Especialy the more complex queryies become more unified towards the actual application, fantastic!
Thanks you very much for all your help and effort, i wish i could pay a beer for you my friend