Forum BigDB Double sort?

Discussion and help relating to the PlayerIO database solution, BigDB.

Double sort?

Postby INS » June 30th, 2010, 6:54 pm

Hi.

Just a simple common task: create highscore table for today's best, this week's best etc. What's the obvious logic:

Table highscores:

int score;
DateTime date;
String username;

I've created Index:

date descending;
score descending;

And want to loadRange with starter object "NOW" and stopper object "NOW-WEEK". Yeah, it's ok, it's all loaded but... Result sorting must depend on score, not on date! I guess for now there is no method to select property for final sorting? And what I have to do - is to sort results from my own code? What if I used "limit" parameter? Really high scores from just a bit earlier than a week ago might not get into results. Loading without limits... there may be a lot of values for a month, for example.

What's the other option? Having 4 different highscore tables: Highscores_Today, Highscores_Week, .._month, _all_the_time, inserting score to all of them and cleaning up these tables with each query as far as no "automatic culling" exists?

Another option: having field "period" in "Highscores" table, including that in index and setting indexPath to what i need. But the big problem is to update this field on each record in table.

So what's the best way?
INS
 
Posts: 12
Joined: June 19th, 2010, 8:22 am

Re: Double sort?

Postby Oliver » June 30th, 2010, 7:32 pm

Hey there,

This is actually a common type issue with NoSQL databases; There is a scalability and performance tradeoff made where ad-hoc queries aren't possible in order to be able to guarentee great scalability and performance.

With an SQL database you'd just have a row with [Score, TimeStamp] and the do queries on that like ("Select * from scores where month(TimeStamp)='...' order by score desc limit 10"). However, such a query can't be easily answered with an index on [TimeStamp, Score], so the database has to read all the data (in the index) for the month and sort that internally before being able to return the top 10.

With most NoSQL database you have to structure your data in way that's optimized for the queries you wish to run on it, and BigDB is no different.

For instance, in your case can get away with a single table; but you'll need a property for each period:

score[randomid] => {
userid:...
score:....
time:<actual datetime of score>
weekId'2007-7', // 7th week of 2007
monthId:'2008-08'
}

Then you just need to have an index pr period:

- ByDay = time (as a date index property)
- ByWeek: weekId,
- ByMonth: monthId

Then you just use the different indexes when you want to lookup in different ranges.

Hope it makes sense :-)

- Oliver
User avatar
Oliver
.IO
 
Posts: 1159
Joined: January 12th, 2010, 8:29 am

Re: Double sort?

Postby INS » June 30th, 2010, 7:36 pm

Oh, those NoSQL databases. So, "period" property seems like the best option. That's what i will try.

Thanks, Oliver!

UPD:

Ouch, "weekId" is fixed from date-to-date, so no floating ranges "week ago" possible, just "this week" and "previous week". Decision changed: making 4 tables :)
INS
 
Posts: 12
Joined: June 19th, 2010, 8:22 am

Re: Double sort?

Postby Oliver » June 30th, 2010, 7:44 pm

Can you explain the four table solution to me? I'm not sure i quite get it :)
User avatar
Oliver
.IO
 
Posts: 1159
Joined: January 12th, 2010, 8:29 am

Re: Double sort?

Postby INS » June 30th, 2010, 8:03 pm

It's a stupid, but i guess working solution. Instead of having one table i have 4 tables - ScoresToday, ScoresWeek, ScoresMonths and ScoresTotal (the last one is actualy PlayerObjects, property "max_score" for each player).

Each table has two indexes, "ByDate", descending for deletion and "ByScore", descending for output.

When it's time to save a score, i put the value to all tables and deleting values that don't fit table anymore:

Code: Select all
                if (score > 0)
                {
                    DatabaseObject new_score = new DatabaseObject();
                    new_score.Set("user", ConnectUserId);
                    new_score.Set("screen_name", JoinData["screen_name"]);
                    new_score.Set("score", score);
                    new_score.Set("date", DateTime.UtcNow);

                    db.CreateObject("ScoresToday", null, new_score, null);
                    db.CreateObject("ScoresWeek", null, new_score, null);
                    db.CreateObject("ScoresMonth", null, new_score, null);

                    db.DeleteRange("ScoresToday", "ByDate", null, DateTime.UtcNow.AddDays(-1), null);
                    db.DeleteRange("ScoresWeek", "ByDate", null, DateTime.UtcNow.AddDays(-7), null);
                    db.DeleteRange("ScoresMonth", "ByDate", null, DateTime.UtcNow.AddMonths(-1), null);
                }


This way is bad because of duplicating the same data, but as far as these tables are being cleaned up "automaticaly", it's not a big deal (i mean resources). This way is good, because i can now select values from proper period with sorting i need, and having ability to limit output to save some bandwith (and processor resources on sorting) :)

AS3:
Code: Select all
client.bigDB.loadRange("ScoresToday", "ByScore", null, null, null, 30, listScores);


"All the time" scores are just selected with "ByScore" descending index from PlayerObjects's "max_score" property, also having ability to limit it with, let's say, 30 first results.
INS
 
Posts: 12
Joined: June 19th, 2010, 8:22 am

Re: Double sort?

Postby INS » June 30th, 2010, 8:28 pm

By the way, while implementing "all the time" I've just got this error:

Error: The connection 'public' does not have the rights to load database objects via indexes from the table 'PlayerObjects'. (If you're getting this error in the development server, remeber that it inherits its rights from the client who created the room. Only the production multiplayer servers have automatic full rights because they run in a trusted enviroment)


I was loading from AS:
Code: Select all
client.bigDB.loadRange("PlayerObjects", "ByScore", null, null, null, 30, listMaxScores, onError);


So, this means I won't be able to read it and have to implement one more table?

UPD: I've set up permission to load by indexes for "public" connection in Edit Table screen in admin panel and this error gone away. But this is bad for security, am I right? someone potentialy could decode my swf, and then load all my playerobjects and steal their data?
INS
 
Posts: 12
Joined: June 19th, 2010, 8:22 am

Re: Double sort?

Postby Oliver » July 1st, 2010, 9:56 am

For most games, it's perfectly acceptable to have reading (loading by keys, indexes) happen clientside; It all depends on how much data you wish to expose.

UPD: I've set up permission to load by indexes for "public" connection in Edit Table screen in admin panel and this error gone away. But this is bad for security, am I right? someone potentialy could decode my swf, and then load all my playerobjects and steal their data?


Yes, correct -- it's up to you to decide whether that's acceptable or not.

If it's not acceptable, you'll have to do the query in serverside code, and return the result via a multiplayer room.

The permission system is setup to allow you to control how secure you want your game to be, on a very fine pr. table level.

- Oliver
User avatar
Oliver
.IO
 
Posts: 1159
Joined: January 12th, 2010, 8:29 am


Return to BigDB



cron