Hi Jelte,
Jelte Liebrand schrieb:
Hi there,
Appologies if this is not the correct forum to send this mail to, but i
thought I'd start here as you guys probably know how to use sqlite the
best. If this is wrong, please let me know where i should be sending my
mail to...
I've recently created a little gaming server, which keeps track of
online highscores. So far i've just used gdbm to keep track of the
users, as all i really need is:
- unique user cookie (to identify him)
- user name
- user score
Howeveer, gdbm does not seem to be able to sort the records, so getting
a 'current rank' withing the score list is tricky. So I'm thinking of
changing this over to use sqlite.
In doing so i've started to wonder how best to do this. The user cookie
should be the primary key, but does sqlite support secondary keys?
If not, should i just put an index on the score column?
sqlite tables always have an integer Primary Key. If you do not define
one, sqlite adds one itself. This is also the rowid.
See http://www.sqlite.org/lang_createtable.html.
Ideally i'd like inserted records to be inserted based on their score.
That way i can just use the ROWID as their ranking. Otherwise i would
need to do something like:
select * from table order by score
And then count somehow how far down the list the user is to work out his
ranking. That sounds like a rather big performance hit, as i'd be
listing and looping through _all_ users just to get the ranking for one.
Surely there's a better way to use SQL to achieve this?
If you have an Index on the score, you could do something like
select count(*) as rank from users where score < my_score
I thought secondary keys could do just that, but then i'm not an SQL
guru. So if anyone has any thoughts on how best to structure such a
table, i'd be most grateful.
(Note: every time the user updates his score i'd like to tell him what
his current rank is. Hence looping through all records would be too
costly i guess)
/Cheers,
Jelte
Martin
