xdfcgi/queries/schema.sql

DROP TABLE IF EXISTS Cts_times;
CREATE TABLE Cts_times(
    mapid       TEXT, 
    gametype    TEXT, 
    trank       INT, 
    tvalue      INT,
    PRIMARY KEY (mapid, gametype, trank),
    FOREIGN KEY (mapid, gametype, trank) REFERENCES Cts_ranks(mapid, gametype, idrank)
);
DROP TABLE IF EXISTS Cts_ranks;
CREATE TABLE Cts_ranks(
    mapid       TEXT, 
    gametype    TEXT, 
    idrank      INT, 
    idvalue     TEXT,
    PRIMARY KEY (mapid, gametype, idrank)
);
DROP TABLE IF EXISTS Id2alias;
CREATE TABLE Id2alias(
    rtype       TEXT, 
    cryptokey   TEXT, 
    alias       TEXT,
    PRIMARY KEY (cryptokey)
);
drop table if exists Speed;
create table Speed(
    mapid       text,
    speed       float,
    primary key (mapid)
);
drop table if exists Fastest_players;
create table Fastest_players (
    mapid       text,
    idvalue     text,
    primary key (mapid)
);

-- These table fields are unaltered.
-- Exerpts from source/qcsrc/race.qc
--      // player improved his existing record, only have to iterate on ranks between new and old recs
--      for (i = prevpos; i > newpos; --i)
--      {
--          db_put(ServerProgsDB, strcat(map, rr, "time", ftos(i)), ftos(race_readTime(map, i - 1)));
--          db_put(ServerProgsDB, strcat(map, rr, "crypto_idfp", ftos(i)), race_readUID(map, i - 1));
--      }
--      ....
--	    // store new time itself
--      db_put(ServerProgsDB, strcat(map, rr, "time", ftos(i)), ftos(race_readTime(map, i - 1)));
--      db_put(ServerProgsDB, strcat(map, rr, "crypto_idfp", ftos(i)), race_readUID(map, i - 1));

-- re: foreign key from & to Cts_ranks & Id2alias.
-- An ranked unregistered player will have a row in Cts_ranks, but will not have a row in Id2alias.
-- A registered player may have a row in Id2alias, but may not necessary have a rank.