Threaded Database structures

Discussion in 'Development General' started by coyotte508, Jul 3, 2010.

  1. coyotte508

    coyotte508 Well-Known Member Administrator Server Owner Administrator Server Owner

    Joined:
    Apr 21, 2010
    Messages:
    6,363
    Likes Received:
    168
    The database system changed a lot. Now it uses SQL (currently supported: MySQL, SQLite, PostGreSQL).

    And so, multiple structures have been created to handle this in an asynchronous way, those structures are used both for the ladder management system and the player management system.

    SQL Tables

    This is the SQLite syntax:
    Code (SQL):
    1. CREATE TABLE trainers (id INTEGER PRIMARY KEY autoincrement, name VARCHAR(20) UNIQUE, laston CHAR(10), auth INT, banned BOOLEAN, salt VARCHAR(7), hash VARCHAR(32), ip VARCHAR(39))
    Code (SQL):
    1. CREATE TABLE tier_xxxx (id INTEGER PRIMARY KEY autoincrement, name VARCHAR(20), rating INT, matches INT)
    Note that the id is created as a primary key, but never really used by the program.

    Players table:


    laston is the last online date (YYYY-MM-DD), salt and hash are for the password, and the IP is on 39 characters because of IPv6.

    Ladder tables:

    matches is the number of rated battles. The rest is pretty self-explanatory.
     
    Last edited: Jul 3, 2010
  2. coyotte508

    coyotte508 Well-Known Member Administrator Server Owner Administrator Server Owner

    Joined:
    Apr 21, 2010
    Messages:
    6,363
    Likes Received:
    168
    Waiting Objects

    Before entering into the tier and player management classes, there's a few things to introduce.

    Code (c++):
    1. class WaitingObject : public QObject
    2. {
    3.     Q_OBJECT
    4. public:
    5.     void emitSignal() {
    6.         emit waitFinished();
    7.     }
    8.  
    9.     QVariantHash data;
    10. signals:
    11.     void waitFinished();
    12. };
    As told before, a lot of accessing the database is asynchronous, so a WaitingObject is used to notify the entities that requested some info, that the info is now available. For that it emits its signal waitFinished(). It's generally called directly if the data is already in memory, or by the thread when the data isn't in memory and is requested in an asynchronous way. The signal waitFinished() will be connected to a slot. Once the slot is called, it'll be disconnected.

    Example:

    A player with the name 'Excalibur' connects. The Player structure makes a call to SecurityManager::loadMemberInMemory() with the slot to call when the member was loaded. The security manager stacks the request in a thread, with a waiting object connected to the slot of the calling Player, and when the request is processed the signal is emitted, and then the Player structure continues the login phase with several calls to the member management structure.

    So what does the QVariantHash data do?

    The previous example showed a case in which the thread was used to load a member in the player management structure (SecurityManager). There are other cases when you want a particular query processed and get the result, but without saving it for further uses. In that case the info are stored in the data member of WaitingObject.

    Example:

    A player wants to see the 22th page of OverUsed rankings. As a consequence it makes this call:

    Code (c++):
    1. TierMachine::obj()->fetchRankings(tier, page, this,  SLOT(displayRankings()));
    Where page=22 and tier="OverUsed".

    But the ladder management structure won't keep those rankings in memory, as they are susceptible to change a lot, say if there's a new player in page 16. So the relevant data are stored in WaitingObject::data:

    Code (c++):
    1.         w->data["rankingpage"] = p;
    2.         w->data["tier"] = this->name();
    3.         w->data["rankingdata"] =  QVariant::fromValue(results);
    And so when the signal is emitted, the Player structure gets the WaitingObject by calling sender() and converting it.

    You may ask why i didn't use QObject::property to store the data, actually changing properties trigger events and it can be incompatible with calls in different threads.
     
    Last edited: Jul 3, 2010
  3. coyotte508

    coyotte508 Well-Known Member Administrator Server Owner Administrator Server Owner

    Joined:
    Apr 21, 2010
    Messages:
    6,363
    Likes Received:
    168
    WaitingObject instances manager: WaitingObjects

    Code (c++):
    1. class WaitingObjects : public QObject
    2. {
    3.     Q_OBJECT
    4. public:
    5.     static WaitingObject* getObject();
    6.     static void freeObject(WaitingObject *c);
    7.     static WaitingObjects * getInstance();
    8.  
    9. public slots:
    10.     /* Must be called with a waiting object to free as sender(), or   segfault */
    11.     void freeObject();
    12.  
    13. //...
    14. }
    This manages waiting objects application wide. Use WaitingObjects::getObject() to get a free or newly allocated object, and then use WaitingObject::freeObject(WaitingObject *) on it to put it back in the pool. You can also connect it's signal waitFinished() to the slot freeObject() of WaitingObject::getInstance().
     
    Last edited: Jul 3, 2010
  4. coyotte508

    coyotte508 Well-Known Member Administrator Server Owner Administrator Server Owner

    Joined:
    Apr 21, 2010
    Messages:
    6,363
    Likes Received:
    168
    Loading and Inserting threads

    The ladder and members structures both use threads.

    There are two kind of threads. The threads used to retrieve information, and the threads used to change the database.

    Typically, the members structure and ladder structure each have 4 threads for loading data, and 1 thread for inserting data.

    Loading Threads

    Code (c++):
    1. class LoadThread : public QThread
    2. {
    3.     Q_OBJECT
    4. public:
    5.     void pushQuery(const QVariant &name, WaitingObject *w, int query_type);
    6.  
    7.     void run();
    8. signals:
    9.     void processQuery (QSqlQuery *q, const QVariant &data, int query_type, WaitingObject *w);
    10. private:
    11.     struct Query {
    12.         QVariant data;
    13.         WaitingObject *w;
    14.         int query_type;
    15.  
    16.         Query(const QVariant &m, WaitingObject *w, int query_type)
    17.             : data(m), w(w), query_type(query_type)
    18.         {
    19.  
    20.         }
    21.     };
    22.  
    23.     QLinkedList<Query> queries;
    24.     QMutex queryMutex;
    25.     QSemaphore sem;
    26. };
    The structures stack queries using the method pushQuery(). The query is pushed in the queries linked list, (queryMutex is used to avoid conflicts). QVariant name is the "data" parameter of the query, and then there is the WaitingObject associated (which you know of now :)) and the query_type, which tells what kind of query you want to perform.

    Each time the thread processes a query, it calls sem.acquire(1) at the end, so that the thread only become active again when a query is stacked, because then pushQuery calls sem.release(1).

    How does a thread process a query? Actually, the management structure will have connected beforehand the signal processQuery() with one of its slots using a direct connection. So, the thread emits the signal which then calls directly the slot of the management structure, which performs the query with all the needed parameters. Notice that a QSqlQuery * is passed in parameter in the signal, it's actually created in the body of the thread.

    Inserting thread

    Code (c++):
    1. class AbstractInsertThread : public QThread
    2. {
    3.     Q_OBJECT
    4. public:
    5.     virtual void run() = 0;
    6.  
    7. signals:
    8.     void processMember (QSqlQuery *q, void * m, int type=1);
    9. };
    10.  
    11. template <class T>
    12. class InsertThread : public AbstractInsertThread
    13. {
    14. public:
    15.     /* update/insert ? */
    16.     void pushMember(const T &m, int desc);
    17.  
    18.     void run();
    19. private:
    20.     QLinkedList<QPair<T, int> > members;
    21.     QMutex memberMutex;
    22.     QSemaphore sem;
    23. };
    This time it needs to be templated, because a whole member is stored until it's processed. The general principle is the same. The reason the abstract base class exists is because you can't declare template QObjects. And it's passed as void * in the signal, convert it back to process it in the management structure.
     
    Last edited: Jul 3, 2010
  5. coyotte508

    coyotte508 Well-Known Member Administrator Server Owner Administrator Server Owner

    Joined:
    Apr 21, 2010
    Messages:
    6,363
    Likes Received:
    168
    Memory Holders

    They are used by the structures to cache in memory some info, to avoid invoking too many queries on the database.

    Code (c++):
    1. template <class Member>
    2. class MemoryHolder
    3. {
    4. public:
    5.     MemoryHolder(int cacheSize=10000);
    6.  
    7.     void changeCacheSize(int cacheSize);
    8.  
    9.     bool isInMemory(const QString &name) const;
    10.  
    11.     void addMemberInMemory(const Member &m);
    12.  
    13.     /* Should only be called from the main thread */
    14.     void cleanCache();
    15.  
    16.     void removeMemberInMemory(const QString &name);
    17.  
    18.     void addNonExistant(const QString &name);
    19.  
    20.     /* Precondition: the member must be in memory, otherwise returns false in all cases. */
    21.     bool exists(const QString &name) const;
    22.  
    23.     Member member (const QString &name2) const;
    24. };
    This stores members. The methods addNonExistant() is there to tell a member does not exist. There are other functions, like addMemberInMemory, removeMemberInMemory... You should remember about isInMemory though, it tells if the member is in the memory (even if it doesn't exist, it is store as not existing) or not. If it is not, then you'll probably want to load it in memory.

    The method exists returns true if the member is in memory and the member exists. If it's not in the cached memory, it'll return false anyway. The method member returns the member if found, or a default value constructed from the name.

    The method cleanCache() is used to clean the cache (depending of its size) to remove old members from the memory. It should only be called from the main thread. The reason is, that in the main thread, if the member is not found in the memory and an urgent operation comes up (or it was loaded from a thread then cleaned out of cache before being processed), it loads back directly from database and perform the operation. You realize it would be silly to clean the cache from another thread beween those two things.

    Note, you should not have to call removeMemberInMemory() directly, cleanCache() will do it for you, except if you want to really remove a member from the memory and the database.

    Note:

    There is a modification to do. cleanCache() for now only cleans existing members, not members stored as not existing. They use a lot less memory and are a lot less frequent, but this still should be taken care of.
     
    Last edited: Jul 3, 2010
  6. coyotte508

    coyotte508 Well-Known Member Administrator Server Owner Administrator Server Owner

    Joined:
    Apr 21, 2010
    Messages:
    6,363
    Likes Received:
    168
    There is still the two global structures to come, but i'll edit this post when putting them in, so you can answer already to the topic if you want.