DDNet SQL dumps analysis

More serious discussions, information about official DDNet servers, important announcements.
Chairn
User
Posts: 386
Joined: Sat Apr 11, 2015 5:05 pm
Player profile: Chairn
Clan: QuintessenZ

Re: DDNet SQL dumps analysis

Post by Chairn » Wed Jul 24, 2019 8:48 pm

How did you do the connected graph for rank team?

miamia
User
Posts: 3
Joined: Thu Jul 18, 2019 12:31 am

Re: DDNet SQL dumps analysis

Post by miamia » Wed Jul 24, 2019 9:52 pm

Chairn wrote:
Wed Jul 24, 2019 8:48 pm
How did you do the connected graph for rank team?

First, create a server_mapping table that will be used to "sanitize" the data.
We will map GER2 to GER, and we won't take into account the servers that had a short life (FRA, KSA, vanilla...).

Code: Select all

CREATE TABLE server_mapping (
    Server char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    MappedServer char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    PRIMARY KEY (Server)
) AS
SELECT Server, SUBSTRING(Server, 1, 3) AS MappedServer
FROM record_race
WHERE Server != ''
GROUP BY Server
HAVING Count(*) > 20000;
Then, we compute the prefered server for each tee. It basically counts the number of races done on each server, and selects the most common.

Code: Select all

CREATE TABLE record_nationality (
    Name varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    Server char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    PRIMARY KEY (Name)
) AS
SELECT T2.Name, M.MappedServer AS Server FROM (
    SELECT T.Name, T.Server
    FROM (
        SELECT Name, Server, COUNT(*) AS Count
        FROM record_race
        GROUP BY Name, Server
        ORDER BY Count DESC
    ) T
    GROUP BY T.Name
) T2
JOIN server_mapping M ON T2.Server = M.Server;
Finally, we create the view that will be used for the graph import.
The node view lists each tee ranked in team, with its prefered server.

Code: Select all

CREATE VIEW gephi_nodes AS 
SELECT Name AS id, Server
FROM record_nationality
WHERE Name IN (SELECT Name FROM record_teamrace);
The edge view lists all tees that played together.

Code: Select all

CREATE VIEW gephi_edges AS
SELECT T1.Name AS source, T2.Name AS target
FROM record_teamrace T1
JOIN record_teamrace T2 ON T1.id = T2.id
WHERE T1.Name < T2.Name
GROUP BY T1.Name, T2.Name;

The graph rendering was done in Gephi (free software).

The rough "process" :

Code: Select all

File > Database import > List of edges...
    Create a new configuration, and test the connection to the database.
    Node query: SELECT * FROM gephi_nodes;
    Edges query: SELECT * FROM gephi_edges;
    OK
    graph type: undirected

Spatialisation
    Select "Atlas Force 2"
    Execute

Appearance > Node Color
    Partition
    Attribute: server
    Apply

Data lab
    Remove "nameless tee" and "brainless tee"

Requests
    NOT(Node)
        Giant component
    Select and remove

For the final reqult, you can tweak some values in the "preview" tab, and export as PNG.
And that's it.

This can be extended to color the nodes/edges with some other data (eg: score, "degree", ...).

Chairn
User
Posts: 386
Joined: Sat Apr 11, 2015 5:05 pm
Player profile: Chairn
Clan: QuintessenZ

Re: DDNet SQL dumps analysis

Post by Chairn » Wed Jul 24, 2019 10:41 pm

Very nice, never heard of the gephi software but seems like quite advanced & useful stuff.

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest