Solved Loading data on startup

[Kyraie]

Hey guys,
I got something complex that i can't wrap my head around.
I'm trying to retrieve multiple rows from a database table, however the query only returns first entry instead of the entry that's expected.
I'm trying to retrieve 5 of each entries depending column value in the database.

Is there a FetchAll function that I'm missing or is single query the only option? I could do a loop but I need top 15 entries, that would be a loop of 15 to get the data.

Edit
If someone has a better approach or how to handle this here's what I want to accomplish.
When the server launches I want the worldserver to store the retrieved database values, this database values can be stored in project if wanted to so they can be accessed as "prj.GetRanking" or something, what I want to do with this information is whenever the player opens a certain window, the data gets loaded into it.

When new entries that's better than current ranking it replaces it server side, no need to update in the database since the entry gets added there by another query, so whenever the server restarts the correct data would load.

Please login and(or) register to see this awesome content today.

Database structure looks like this

Please login and(or) register to see this awesome content today.

And example that would be

Please login and(or) register to see this awesome content today.

 

[Fliperworld]

Your Sql query seems correct to me, could you share the c++ part where you process the results?

my fetch concept:

Please login and(or) register to see this awesome content today.

 

[Kyraie]

Your Sql query seems correct to me, could you share the c++ part where you process the results?

my fetch concept:

Please login and(or) register to see this awesome content today.

So I'm just trying out the result received from the query, it's not quite there yet as I got stuck on only one entry received from the database however testing the query in mssql it works fine, so it's clearly at fault in the c++ part, my c++ looks like this and I know it's only for single entry handling, I'm just debugging the query to see what data is received.

Please login and(or) register to see this awesome content today.

 

[Fliperworld]

Using the CQuery class, you just need to call "pQuery->Fetch()" while Fetch() returns TRUE;

Don't worry about performance calling Fetch in a looping, the query is executed only once, and Fetch() just advances the pointer and returns the next row that was already retrieved by the initial query.

 

[Kyraie]

Using the CQuery class, you just need to call "pQuery->Fetch()" while Fetch() returns TRUE;

Don't worry about performance calling Fetch in a looping, the query is executed only once, and Fetch() just advances the pointer and returns the next row that was already retrieved by the initial query.

With the code you posted above in your first post, how would you do the handling correctly?

Also any tips on how to pass the data through to the worldserver, atm I’m using a LoadRanking function made in the CInstanceDungeon manager with __DBSERVER

 

[Fliperworld]

I think that for your system, CInstanceDungeonParty is a good place to ask the DatabaseServer to include the run in the db and also request an updated list of the best times.

My suggestion is to insert the run data into the ranking while simultaneously requesting an updated list, all within a single stored procedure, something like this:

Please login and(or) register to see this awesome content today.

The above procedure also can be called when the server starts, passing the last optional argument as 0.

 

[Kyraie]

I think that for your system, CInstanceDungeonParty is a good place to ask the DatabaseServer to include the run in the db and also request an updated list of the best times.

My suggestion is to insert the run data into the ranking while simultaneously requesting an updated list, all within a single stored procedure, something like this:

Please login and(or) register to see this awesome content today.

The above procedure also can be called when the server starts, passing the last optional argument as 0.

So when inserting match result you insert the data entry and retrieve latest result?

Any example on how the c++ side of this would look like?

 

[Fliperworld]

The procedure is basically the same as the one you already use.I assume you are already using CQuery* pQueryLog to insert the data and also request the query.

Please login and(or) register to see this awesome content today.

I'm not so sure if I remember exactly how packets works, so take this answer as a concept to adapt and use in your system.

Please login and(or) register to see this awesome content today.

 

[Kyraie]

The procedure is basically the same as the one you already use.I assume you are already using CQuery* pQueryLog to insert the data and also request the query.

Please login and(or) register to see this awesome content today.

I'm not so sure if I remember exactly how packets works, so take this answer as a concept to adapt and use in your system.

Please login and(or) register to see this awesome content today.

I'll try this out, but just to wrap my head around all these executables and how they work, I read @Kia thread recently explaining what every executable does and how it works. Basically when the dungeon ends and it was cleared it sends from DPSrvr.cpp to dptrans.cpp that receives the packet, from there it sends a AllocRequest() to DbManager which checks the switch case and triggers "LogDungeon(pQueryLog, lpDbOverlappedPlus)" and in DbManagerLog it inserts the data to the database and retrieves the latest ranking, I'd guess where you commented "//send back to worldserver..." it sends a packet back to the worldserver, which i need to catch there?

 

[Fliperworld]

I'll try this out, but just to wrap my head around all these executables and how they work, I read @Kia thread recently explaining what every executable does and how it works. Basically when the dungeon ends and it was cleared it sends from DPSrvr.cpp to dptrans.cpp that receives the packet, from there it sends a AllocRequest() to DbManager which checks the switch case and triggers "LogDungeon(pQueryLog, lpDbOverlappedPlus)" and in DbManagerLog it inserts the data to the database and retrieves the latest ranking, I'd guess where you commented "//send back to worldserver..." it sends a packet back to the worldserver, which i need to catch there?

Since your system will store a 'cache' containing the best results on WorldServer, you can receive and process them with something like this:

Please login and(or) register to see this awesome content today.

Remember: In the previous answer , I'm not sure if I remembered and used the best macros/ids to choose the destination to send the response, but the basic concept is still the same...
I was used to sending/receiving packets with simpler communication, usually between client and server.

 

[Kyraie]

I adjusted your code to work with how it was intended, seems to work fine however making the server load the data on startup, is it simply just sending a packet from X file and storing the data in prj?

Since your system will store a 'cache' containing the best results on WorldServer, you can receive and process them with something like this:

Please login and(or) register to see this awesome content today.

Remember: In the previous answer , I'm not sure if I remembered and used the best macros/ids to choose the destination to send the response, but the basic concept is still the same...
I was used to sending/receiving packets with simpler communication, usually between client and server.

 

[Fliperworld]

I adjusted your code to work with how it was intended, seems to work fine however making the server load the data on startup, is it simply just sending a packet from X file and storing the data in prj?

For startup, is simply making a call/sending the packet in a similar way to what is done when finishing the dungeon.The difference is that you can send an empty/invalid 'DUNGEON_RANKING_SCORE' and use InsertFlag 0.The stored procedure will check this flag, and since it will be at 0, it will ignore the data insertion part, and will only return the best results.

 

[Kyraie]

For startup, is simply making a call/sending the packet in a similar way to what is done when finishing the dungeon.The difference is that you can send an empty/invalid 'DUNGEON_RANKING_SCORE' and use InsertFlag 0.The stored procedure will check this flag, and since it will be at 0, it will ignore the data insertion part, and will only return the best results.

Yeah I've seen that the stored procedure defaults to 0, and the insert procedure only runs if the flag is 1, I'll try to make the call from the manager constructor and see if that works

 

[Kyraie]

For startup, is simply making a call/sending the packet in a similar way to what is done when finishing the dungeon.The difference is that you can send an empty/invalid 'DUNGEON_RANKING_SCORE' and use InsertFlag 0.The stored procedure will check this flag, and since it will be at 0, it will ignore the data insertion part, and will only return the best results.

Alright I've tried the code a bit, seems like the insert works but after it inserts there is no fetch, the while loop never runs

Edit
I tried checking if the fetch is false or true, seems like the fetch returns as false.

Please login and(or) register to see this awesome content today.

 

[Kia]

When the server launches I want the worldserver to store the retrieved database values, this database values can be stored in project if wanted to so they can be accessed as "prj.GetRanking" or something, what I want to do with this information is whenever the player opens a certain window, the data gets loaded into it.

When new entries that's better than current ranking it replaces it server side, no need to update in the database since the entry gets added there by another query, so whenever the server restarts the correct data would load.

Sounds like the following:
1. You want to store Dungeon Completion time and who've completed a dungeon. (assuming this is for one dungeon since m_nMode is your only variable and i assume thats difficulty)
2. You want the data to be stored on the database.
3. You want to load the data from the database on server load.
4. You want to replace the data when better one's occur - the controlling server being the Worldserver.
5. You want to then send that data to the client.
I suggest going backwards
In that case:

CDPTrans::SysMessageHandler

• As you can see, this is where it sends the first set of data off (dbserver to wserver).
• You can utilize the same IOCompletionPort handle system (essentially being used here as a thread safe -- threaded task queue).
• The reason for the above is to avoid main thread delays.

: You want to load the data / set defaults:

I'd recommend not merging the queries, but that's just me -- because, if the insert operation isn't allowed duplicate data due to key constraint or some variation, a failure might also skip the select statement. Make sure to set nocount on (i believe otherwise, one of the result sets will be the count). I also see SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON commonly in flyff's db, but in this case, that shouldn't matter.

Please login and(or) register to see this awesome content today.

If this is created as the internals of a stored procedure, then that looks fine.

As mentioned, Using the while Fetch() loop is required, and everything mentioned has been good.


You'd recieve that information on the worldserverand then that's all there is. On the dungeon completion, just check on worldserve and update. You have two options, you can post the update to all users and force them to always have it, or you can do the "request data" when opening the app, then updating the CWndListBox data when recievning that data and applying a cooldown to that to avoid spamming. The latter is probably better for this system.

And that'd be upon cdpsrvr request from client, send data into the m_snapshot of the requesting CUser.


Lastly, it's just sending the information back, which, same idea, using the Putthread instead.

Please login and(or) register to see this awesome content today.

And as Fliperworld said, this is where you'd do it.

Then another stored procedure to save it to the DB.

There's some things to keep in mind though. What is the plan? Because as is, imagine 100 people just spam going in and out of dungeons. Is it on dungeon close, or boss completition? Are you logging "all" instances? do you want to log every single dungeon? You only want 5 on the worldserver / client, right? You can cut back on some storage or add some more efficiency based on your needs.

I'm not so sure if I remember exactly how packets works, so take this answer as a concept to adapt and use in your system.

Flyff doesn't have built in vector and string -- well, it's actually a bit more complicated. There are revisions for vector, but not as good and, they don't really work with char*, char[], std::string. On that note, a very nice thing to do using C++20 is utilizing concepts and constraints, letting the compiler to-handle the heavy lifting of types, and then being able to handle stuff.


Hope this helps a bit, if you still need more answers, just mention which part.

 

[Kyraie]

Sounds like the following:
1. You want to store Dungeon Completion time and who've completed a dungeon. (assuming this is for one dungeon since m_nMode is your only variable and i assume thats difficulty)
2. You want the data to be stored on the database.
3. You want to load the data from the database on server load.
4. You want to replace the data when better one's occur - the controlling server being the Worldserver.
5. You want to then send that data to the client.
I suggest going backwards
In that case:

CDPTrans::SysMessageHandler

• As you can see, this is where it sends the first set of data off (dbserver to wserver).
• You can utilize the same IOCompletionPort handle system (essentially being used here as a thread safe -- threaded task queue).
• The reason for the above is to avoid main thread delays.

: You want to load the data / set defaults:

I'd recommend not merging the queries, but that's just me -- because, if the insert operation isn't allowed duplicate data due to key constraint or some variation, a failure might also skip the select statement. Make sure to set nocount on (i believe otherwise, one of the result sets will be the count). I also see SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON commonly in flyff's db, but in this case, that shouldn't matter.

If this is created as the internals of a stored procedure, then that looks fine.

As mentioned, Using the while Fetch() loop is required, and everything mentioned has been good.


You'd recieve that information on the worldserverand then that's all there is. On the dungeon completion, just check on worldserve and update. You have two options, you can post the update to all users and force them to always have it, or you can do the "request data" when opening the app, then updating the CWndListBox data when recievning that data and applying a cooldown to that to avoid spamming. The latter is probably better for this system.

And that'd be upon cdpsrvr request from client, send data into the m_snapshot of the requesting CUser.


Lastly, it's just sending the information back, which, same idea, using the Putthread instead.

And as Fliperworld said, this is where you'd do it.

Then another stored procedure to save it to the DB.

There's some things to keep in mind though. What is the plan? Because as is, imagine 100 people just spam going in and out of dungeons. Is it on dungeon close, or boss completition? Are you logging "all" instances? do you want to log every single dungeon? You only want 5 on the worldserver / client, right? You can cut back on some storage or add some more efficiency based on your needs.



Flyff doesn't have built in vector and string -- well, it's actually a bit more complicated. There are revisions for vector, but not as good and, they don't really work with char*, char[], std::string. On that note, a very nice thing to do using C++20 is utilizing concepts and constraints, letting the compiler to-handle the heavy lifting of types, and then being able to handle stuff.


Hope this helps a bit, if you still need more answers, just mention which part.

First of thanks for a lot of useful information, I’ll read it once or twice more when I wake up, so the initial plan is that I have a check already in place if the dungeon has cleared successfully or if the player failed the dungeon, in that check all succeeded dungeons gets the call to store their dungeon run time, etc to the database.

As flipper mentioned in his example he merged the insert statement along with the select statement, I tried some catch and rollback testing on the SQL side, running all this in MS SQL works fine, however putting a breakpoint when running the query in CDbmanager it shows that the insert statement completes without any issues, however the pQuery->Fetch fails. Like the code below that I added for testing.

Alright I've tried the code a bit, seems like the insert works but after it inserts there is no fetch, the while loop never runs

Edit
I tried checking if the fetch is false or true, seems like the fetch returns as false.

Please login and(or) register to see this awesome content today.

The initial plan is to store the data on world server, in this case in the project.h so it can be accessed with ”prj.GetRanking” instead of storing it in every mover class, when the player opens the UI the constructor of the UI will read the data from ”prj.GetRanking”.

My issue is that I have no clue why ->Fetch doesn’t trigger, and no clue how to debug it. I’ve put breakpoints and followed call stack but nothing in particular that tells me what’s wrong or why it’s failing to read it.

 

[Fliperworld]

I did a basic implementation, and tests.
Use this as a simple base, and apply @Kia's suggestions.

SQL : Same as before https://lodelight.com/threads/loading-data-on-startup.877/post-2739

Implementations based on L15, adjust as best for your source version.

TEST_DungeonStructs.h

Please login and(or) register to see this awesome content today.

DbManager.h

Please login and(or) register to see this awesome content today.

MsgHdr.h

Please login and(or) register to see this awesome content today.

DbManager.cpp

Please login and(or) register to see this awesome content today.

DbManagerLog.cpp

Please login and(or) register to see this awesome content today.

Dptrans.cpp

Please login and(or) register to see this awesome content today.

dptrans.h

Please login and(or) register to see this awesome content today.

DPDatabaseClient.cpp

Please login and(or) register to see this awesome content today.

DPDatabaseClient.h

Please login and(or) register to see this awesome content today.

All that's left is to add the calls to the SendDungeonRankingRequest function, which is up to you.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Edit:
@Kia's brought up some interesting points for using 2 separate queries in the answer below this one.
The example I posted implements and sends Worldserver <-> DatabaseServer packets.
The fetch loop is working, and Worldserver receives 15 results.

The example can be easily modified by implementing PACKETTYPE_DUNGEON_RANKING_LIST_REQUEST or something similar and creating functions to handle that query.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I'd recommend not merging the queries, but that's just me -- because, if the insert operation isn't allowed duplicate data due to key constraint or some variation, a failure might also skip the select statement

I thought about this when I suggested it, but in this case, as new/different results are only possible when there is a new row inserted, when there are failures, there will not be a set of results to update.

 

[Kia]

I did a basic implementation, and tests.
Use this as a simple base, and apply @Kia's suggestions.

SQL : Same as before https://lodelight.com/threads/loading-data-on-startup.877/post-2739

I still wouldn't want to do two queries in the same -- mainly because the functionality is actually different. Ill bring up some issues.

In OP's post, the requirements suggest that OP should use both Put and Get thread separately, as it's like the GuildRanking system (when you open the applet, and it shows the top guilds or players from the siege).

When you're inserting,

Please login and(or) register to see this awesome content today.

Two Queries in One is Unnecessary
So, combining two operations is generally efficient, but the key is the distinction. Both hold different purposes, especially in this case. Something that this induces is

Increased Network I/O
Whenever the procedure is called, it invokes both the "insert" and the "select" statement (most of the time because of it being a logger). Meaning, every time a dungeon score is inserted, the system unnecessarily fetches the top 5 entries and sends it back, regardless if the caller requires that information or not.

• If 10 different groups complete dungeons at the same time, the system will preform 10 separate inserts, followed by 10 select queries, resulting in 50 leaderboard entries being sent back that MAY have not changed between the calls leading to the data redundancy.

Increased Latency
Because of the additional select, every call starts to introduce extra latency. This is because the database will be required to process the insert statement, process the select statement (which eventually will be a big list that will need built in sort optimizations), and returning the result set all the time. The unnecessary calls can cause bottlenecks because it's a high-performant game server application.

Recommendation
Simple, just split the queries.

• Insert Operation: Should be queued by the worldserver.
• Select Operation: Should be ran to retrieve the static data at some point, but not all the time. (Hence my recommendation of when the Worldserver first connects to the database server and it sends lord data back).

Unnecessary Result Set From Insert
Another problem that is presented, is the result set from INSERT.

• INSERT is executed that doesn't return rows.
• SELECT is executed, the data you want.

The problem is, apparently because the INSERT operation is ran first, the return of SqlFetch will be SQL_NO_DATA, so, the function Fetch() part of CQuery will be returning FALSE.

Recommendation
This problem can be solved by either the following:

• Running the SqlMoreResults() function. (Only if its 1000% required to have both logic in one query)
• Separating the queries.

Redundant Leaderboard Data
In the above sql query, it will always fetch the top 5 results after every single insert. Since the leaderboard will be displayed on the client and during runtime, stored on the worldserver, catching 5 new results after every insert, even if the leaderboard hasn't changed. The problem with this is, imagine if 10 groups complete the dungeon at the same time. If the rankings haven't changed, you're still updating the entire ranking 10x. And since, eventually, it will most likely be less frequent of change, repeatingly fetching the data every completion is a bit overkill.

Recommendation
The INSERT call should only be used to log the dungeon completion. The leaderboard can be updated on the server, and it'd only need to pull data once a cycle.


Splitting up the query and the process to be Get and Put on required, will just lead to better expansion practices, better performance, and lower latency, and arguably easier maintenance.

 

[Kyraie]

I'm sorry I've been busy at work, haven't been able to try @Kia or @Fliperworld suggestions, I'll update on how it goes

 

[Kyraie]

I might have taken a bigger bite than what i can chew on, this attempt was quite a big step from what I'm used to doing.
I've split the queries into two parts, the insert is only run on successful run from @Kia suggestion. I've put the ranking load on ThreadMng -> CRunObject::Init() the call is run on worldserver start, not sure if that works or is acceptable but it works as intended.

ThreadMng.cpp

Please login and(or) register to see this awesome content today.

The data is read correctly and it working as intended, I'll need to figure out how to make the call every hour as @Kia suggested instead of flooding the database with calls after every completion.