git.sesse.net Git - pkanalytics/blob - events.cpp

   1 #include <algorithm>
   2 #include <string>
   3 #include <map>
   4 #include <vector>
   5 #include <optional>
   6 #include <sqlite3.h>
   7 #include "events.h"
   8
   9 using namespace std;
  10
  11 string format_timestamp(uint64_t pos);
  12
  13 EventsModel::EventsModel(sqlite3 *db, int match_id) : db(db), match_id(match_id)
  14 {
  15         load_data();
  16 }
  17
  18 QVariant EventsModel::headerData(int section, Qt::Orientation orientation, int role) const
  19 {
  20         if (role != Qt::DisplayRole) {
  21                 return QVariant();
  22         }
  23         if (orientation == Qt::Horizontal) {
  24                 if (section == 0) {
  25                         return "Time";
  26                 } else if (section == 1) {
  27                         return "Who/what";
  28                 } else {
  29                         return "Type";
  30                 }
  31         } else {
  32                 return "";
  33         }
  34 }
  35
  36 QVariant EventsModel::data(const QModelIndex &index, int role) const
  37 {
  38         if (role != Qt::DisplayRole) {
  39                 return QVariant();
  40         }
  41         const Event &e = events[index.row()];
  42         if (index.column() == 0) {
  43                 return QString::fromUtf8(format_timestamp(e.t));
  44         } else if (index.column() == 1) {
  45                 optional<int> player_id = e.player_id;
  46                 optional<int> formation_id = e.formation_id;
  47                 if (player_id) {
  48                         auto p_it = players.find(*player_id);
  49                         const Player &p = p_it->second;
  50                         return QString::fromUtf8(p.name + " (" + p.number + ")");
  51                 } else if (formation_id) {
  52                         auto f_it = formations.find(*formation_id);
  53                         const Formation &f = f_it->second;
  54                         return QString::fromUtf8(f.name);
  55                 } else if (e.type == "formation_offense" || e.type == "formation_defense") {
  56                         return "(None/unknown)";
  57                 } else {
  58                         return QVariant();
  59                 }
  60         } else if (index.column() == 2) {
  61                 string type = e.type;
  62                 type[0] = toupper(e.type[0]);
  63                 for (char &ch : type) {
  64                         if (ch == '_') {
  65                                 ch = ' ';
  66                         }
  67                 }
  68
  69                 // Various fixups.
  70                 if (type == "Pull oob") {
  71                         type = "Pull OOB";
  72                 } else if (type == "Formation defense") {
  73                         type = "Defensive formation";
  74                 } else if (type == "Formation offense") {
  75                         type = "Offensive formation";
  76                 } else if (type == "Set offense") {
  77                         type = "On offense";
  78                 } else if (type == "Set defense") {
  79                         type = "On defense";
  80                 } else if (type == "Catch") {
  81                         type = "Catch/take";
  82                 } else if (type == "Was d") {
  83                         type = "Was d-ed";
  84                 }
  85
  86                 return QString::fromUtf8(type);
  87         }
  88         return QVariant();
  89 }
  90
  91 void EventsModel::load_data()
  92 {
  93         players.clear();
  94         events.clear();
  95
  96         // Read the players. (The ordering is used to build the order map.)
  97         sqlite3_stmt *stmt;
  98         int ret = sqlite3_prepare_v2(db, "SELECT player, number, name FROM player ORDER BY gender, (number+0), number", -1, &stmt, 0);
  99         if (ret != SQLITE_OK) {
 100                 fprintf(stderr, "SELECT prepare: %s\n", sqlite3_errmsg(db));
 101                 abort();
 102         }
 103         int order = 0;
 104         for ( ;; ) {
 105                 ret = sqlite3_step(stmt);
 106                 if (ret == SQLITE_ROW) {
 107                         Player p;
 108                         p.player_id = sqlite3_column_int(stmt, 0);
 109                         p.number = (const char *)sqlite3_column_text(stmt, 1);
 110                         p.name = (const char *) sqlite3_column_text(stmt, 2);
 111                         players[p.player_id] = std::move(p);
 112                         player_ordering[p.player_id] = order++;
 113                 } else if (ret == SQLITE_DONE) {
 114                         break;
 115                 } else {
 116                         fprintf(stderr, "SELECT step: %s\n", sqlite3_errmsg(db));
 117                         abort();
 118                 }
 119         }
 120         ret = sqlite3_finalize(stmt);
 121         if (ret != SQLITE_OK) {
 122                 fprintf(stderr, "SELECT finalize: %s\n", sqlite3_errmsg(db));
 123                 abort();
 124         }
 125
 126         // Read the formations.
 127         ret = sqlite3_prepare_v2(db, "SELECT formation, name FROM formation", -1, &stmt, 0);
 128         if (ret != SQLITE_OK) {
 129                 fprintf(stderr, "SELECT prepare: %s\n", sqlite3_errmsg(db));
 130                 abort();
 131         }
 132         for ( ;; ) {
 133                 ret = sqlite3_step(stmt);
 134                 if (ret == SQLITE_ROW) {
 135                         Formation f;
 136                         f.formation_id = sqlite3_column_int(stmt, 0);
 137                         f.name = (const char *) sqlite3_column_text(stmt, 1);
 138                         formations[f.formation_id] = std::move(f);
 139                 } else if (ret == SQLITE_DONE) {
 140                         break;
 141                 } else {
 142                         fprintf(stderr, "SELECT step: %s\n", sqlite3_errmsg(db));
 143                         abort();
 144                 }
 145         }
 146         ret = sqlite3_finalize(stmt);
 147         if (ret != SQLITE_OK) {
 148                 fprintf(stderr, "SELECT finalize: %s\n", sqlite3_errmsg(db));
 149                 abort();
 150         }
 151
 152         // Read the events.
 153         ret = sqlite3_prepare_v2(db, "SELECT event, t, player, formation, type FROM event WHERE match=? ORDER BY t", -1, &stmt, 0);
 154         if (ret != SQLITE_OK) {
 155                 fprintf(stderr, "SELECT prepare: %s\n", sqlite3_errmsg(db));
 156                 abort();
 157         }
 158         sqlite3_bind_int64(stmt, 1, match_id);
 159         for ( ;; ) {
 160                 ret = sqlite3_step(stmt);
 161                 if (ret == SQLITE_ROW) {
 162                         Event e;
 163                         e.event_id = sqlite3_column_int(stmt, 0);
 164                         e.t = sqlite3_column_int(stmt, 1);
 165                         if (sqlite3_column_type(stmt, 2) == SQLITE_INTEGER) {  // Non-NULL.
 166                                 e.player_id = sqlite3_column_int(stmt, 2);
 167                         }
 168                         if (sqlite3_column_type(stmt, 3) == SQLITE_INTEGER) {  // Non-NULL.
 169                                 e.formation_id = sqlite3_column_int(stmt, 3);
 170                         }
 171                         e.type = (const char *)sqlite3_column_text(stmt, 4);
 172                         events.push_back(std::move(e));
 173                 } else if (ret == SQLITE_DONE) {
 174                         break;
 175                 } else {
 176                         fprintf(stderr, "SELECT step: %s\n", sqlite3_errmsg(db));
 177                         abort();
 178                 }
 179         }
 180         ret = sqlite3_finalize(stmt);
 181         if (ret != SQLITE_OK) {
 182                 fprintf(stderr, "SELECT finalize: %s\n", sqlite3_errmsg(db));
 183                 abort();
 184         }
 185 }
 186
 187 unsigned EventsModel::insert_event(uint64_t t, optional<int> player_id, optional<int> formation_id, const string &type)
 188 {
 189         auto it = lower_bound(events.begin(), events.end(), t,
 190                 [](const Event &e, uint64_t t) { return e.t < t; });
 191         unsigned pos = distance(events.begin(), it);
 192         beginInsertRows(QModelIndex(), pos, pos);
 193
 194         Event e;
 195         e.t = t;
 196         e.player_id = player_id;
 197         e.formation_id = formation_id;
 198         e.type = type;
 199         events.insert(events.begin() + pos, e);
 200
 201         endInsertRows();
 202
 203         // Insert the new row into the database.
 204         sqlite3_stmt *stmt;
 205         int ret = sqlite3_prepare_v2(db, "INSERT INTO event (match, t, player, formation, type) VALUES (?, ?, ?, ?, ?)", -1, &stmt, 0);
 206         if (ret != SQLITE_OK) {
 207                 fprintf(stderr, "INSERT prepare: %s\n", sqlite3_errmsg(db));
 208                 abort();
 209         }
 210
 211         sqlite3_bind_int64(stmt, 1, match_id);
 212         sqlite3_bind_int64(stmt, 2, t);
 213         if (player_id) {
 214                 sqlite3_bind_int64(stmt, 3, *player_id);
 215         } else {
 216                 sqlite3_bind_null(stmt, 3);
 217         }
 218         if (formation_id) {
 219                 sqlite3_bind_int64(stmt, 4, *formation_id);
 220         } else {
 221                 sqlite3_bind_null(stmt, 4);
 222         }
 223         sqlite3_bind_text(stmt, 5, type.data(), type.size(), SQLITE_STATIC);
 224
 225         ret = sqlite3_step(stmt);
 226         if (ret == SQLITE_ROW) {
 227                 fprintf(stderr, "INSERT step: %s\n", sqlite3_errmsg(db));
 228                 abort();
 229         }
 230
 231         ret = sqlite3_finalize(stmt);
 232         if (ret != SQLITE_OK) {
 233                 fprintf(stderr, "INSERT finalize: %s\n", sqlite3_errmsg(db));
 234                 abort();
 235         }
 236
 237         events[pos].event_id = sqlite3_last_insert_rowid(db);
 238         return pos;
 239 }
 240
 241 void EventsModel::delete_event(unsigned pos)
 242 {
 243         int event_id = events[pos].event_id;
 244
 245         beginRemoveRows(QModelIndex(), pos, pos);
 246         events.erase(events.begin() + pos);
 247         endRemoveRows();
 248
 249         // Delete the row from the database.
 250         sqlite3_stmt *stmt;
 251         int ret = sqlite3_prepare_v2(db, "DELETE FROM event WHERE event=?", -1, &stmt, 0);
 252         if (ret != SQLITE_OK) {
 253                 fprintf(stderr, "DELETE prepare: %s\n", sqlite3_errmsg(db));
 254                 abort();
 255         }
 256
 257         sqlite3_bind_int64(stmt, 1, event_id);
 258
 259         ret = sqlite3_step(stmt);
 260         if (ret == SQLITE_ROW) {
 261                 fprintf(stderr, "DELETE step: %s\n", sqlite3_errmsg(db));
 262                 abort();
 263         }
 264
 265         ret = sqlite3_finalize(stmt);
 266         if (ret != SQLITE_OK) {
 267                 fprintf(stderr, "DELETE finalize: %s\n", sqlite3_errmsg(db));
 268                 abort();
 269         }
 270 }
 271
 272 void EventsModel::set_event_type(unsigned pos, const string &type)
 273 {
 274         events[pos].type = type;
 275         emit dataChanged(createIndex(pos, 0), createIndex(pos, 2));
 276
 277         sqlite3_stmt *stmt;
 278         int ret = sqlite3_prepare_v2(db, "UPDATE event SET type=? WHERE event=?", -1, &stmt, 0);
 279         if (ret != SQLITE_OK) {
 280                 fprintf(stderr, "INSERT prepare: %s\n", sqlite3_errmsg(db));
 281                 abort();
 282         }
 283
 284         sqlite3_bind_text(stmt, 1, type.data(), type.size(), SQLITE_STATIC);
 285         sqlite3_bind_int64(stmt, 2, events[pos].event_id);
 286
 287         ret = sqlite3_step(stmt);
 288         if (ret == SQLITE_ROW) {
 289                 fprintf(stderr, "UPDATE step: %s\n", sqlite3_errmsg(db));
 290                 abort();
 291         }
 292
 293         ret = sqlite3_finalize(stmt);
 294         if (ret != SQLITE_OK) {
 295                 fprintf(stderr, "UPDATE finalize: %s\n", sqlite3_errmsg(db));
 296                 abort();
 297         }
 298 }
 299
 300 void EventsModel::set_event_formation(unsigned pos, int formation_id)
 301 {
 302         events[pos].formation_id = formation_id;
 303         emit dataChanged(createIndex(pos, 0), createIndex(pos, 2));
 304
 305         sqlite3_stmt *stmt;
 306         int ret = sqlite3_prepare_v2(db, "UPDATE event SET formation=? WHERE event=?", -1, &stmt, 0);
 307         if (ret != SQLITE_OK) {
 308                 fprintf(stderr, "INSERT prepare: %s\n", sqlite3_errmsg(db));
 309                 abort();
 310         }
 311
 312         sqlite3_bind_int64(stmt, 1, formation_id);
 313         sqlite3_bind_int64(stmt, 2, events[pos].event_id);
 314
 315         ret = sqlite3_step(stmt);
 316         if (ret == SQLITE_ROW) {
 317                 fprintf(stderr, "UPDATE step: %s\n", sqlite3_errmsg(db));
 318                 abort();
 319         }
 320
 321         ret = sqlite3_finalize(stmt);
 322         if (ret != SQLITE_OK) {
 323                 fprintf(stderr, "UPDATE finalize: %s\n", sqlite3_errmsg(db));
 324                 abort();
 325         }
 326 }
 327
 328 unsigned EventsModel::get_last_event_pos(uint64_t t) const
 329 {
 330         // upper_bound() gives first where e.t > t,
 331         // and the one before that is the one we want.
 332         auto it = upper_bound(events.begin(), events.end(), t,
 333                 [](uint64_t t, const Event &e) { return t < e.t; });
 334         if (it == events.begin()) {
 335                 return 0;
 336         } else {
 337                 return distance(events.begin(), it - 1);
 338         }
 339 }
 340
 341 EventsModel::Status EventsModel::get_status_at(uint64_t t)
 342 {
 343         Status s;
 344         s.our_score = 0;
 345         s.their_score = 0;
 346         s.attack_state = Status::NOT_STARTED;
 347         s.offensive_formation = 0;
 348         s.defensive_formation = 0;
 349         s.stoppage = false;
 350         s.pull_state = Status::SHOULD_PULL;
 351         uint64_t last_gained_possession = 0;
 352         uint64_t last_stoppage = 0;
 353         uint64_t time_spent_in_stoppage = 0;
 354         unsigned num_touches = 0;
 355
 356         auto set_offense = [&s] { s.attack_state = Status::OFFENSE; };
 357         auto set_defense = [&s] { s.attack_state = Status::DEFENSE; };
 358
 359         for (const Event &e : events) {
 360                 if (e.t > t) {
 361                         break;
 362                 }
 363
 364                 if (e.type == "goal" || e.type == "their_goal") {
 365                         s.pull_state = Status::SHOULD_PULL;
 366                 } else if (e.type == "in" || e.type == "out" || e.type == "stoppage" || e.type == "restart" || e.type == "unknown" || e.type == "set_defense" || e.type == "set_offense") {
 367                         // No effect on pull status.
 368                 } else if (e.type == "pull") {
 369                         s.pull_state = Status::PULL_IN_AIR;
 370                 } else {
 371                         s.pull_state = Status::NOT_PULLING;  // Includes pull_landed and pull_oob.
 372                 }
 373
 374                 if (e.type == "set_offense") {
 375                         set_offense();
 376                 } else if (e.type == "set_defense") {
 377                         set_defense();
 378                 }
 379
 380                 if (e.type == "goal") {
 381                         ++s.our_score;
 382                         set_defense();
 383                         num_touches = 0;
 384                 }
 385                 if (e.type == "their_goal") {
 386                         ++s.their_score;
 387                         set_offense();
 388                         num_touches = 0;
 389                 }
 390                 if (e.type == "catch") {
 391                         if (num_touches == 0) {  // Pick up.
 392                                 last_gained_possession = e.t;
 393                                 time_spent_in_stoppage = 0;
 394                         }
 395                         ++num_touches;
 396                 }
 397                 if (e.type == "interception") {
 398                         num_touches = 1;
 399                         set_offense();
 400                         last_gained_possession = e.t;
 401                         time_spent_in_stoppage = 0;
 402                 }
 403                 if (e.type == "defense" || e.type == "their_throwaway") {
 404                         set_offense();
 405                         num_touches = 0;
 406                         time_spent_in_stoppage = 0;
 407                 }
 408                 if (e.type == "drop" || e.type == "was_d" || e.type == "throwaway" || e.type == "stallout") {
 409                         set_defense();
 410                         num_touches = 0;
 411                 }
 412                 if (e.type == "stoppage") {
 413                         s.stoppage = true;
 414                         last_stoppage = e.t;
 415                 }
 416                 if (e.type == "restart") {
 417                         s.stoppage = false;
 418                         if (last_stoppage != 0) {
 419                                 time_spent_in_stoppage += (e.t - last_stoppage);
 420                                 last_stoppage = 0;
 421                         }
 422                 }
 423                 if (e.type == "formation_offense") {
 424                         if (e.formation_id) {
 425                                 s.offensive_formation = *e.formation_id;
 426                         } else {
 427                                 s.offensive_formation = 0;
 428                         }
 429                 }
 430                 if (e.type == "formation_defense") {
 431                         if (e.formation_id) {
 432                                 s.defensive_formation = *e.formation_id;
 433                         } else {
 434                                 s.defensive_formation = 0;
 435                         }
 436                 }
 437         }
 438         if (s.stoppage && last_stoppage != 0) {
 439                 time_spent_in_stoppage += (t - last_stoppage);
 440         }
 441
 442         s.num_passes = (num_touches == 0) ? 0 : num_touches - 1;
 443         s.possession_sec = (s.attack_state == Status::OFFENSE && last_gained_possession != 0 && num_touches != 0) ? (t - last_gained_possession - time_spent_in_stoppage) / 1000 : 0;
 444         s.stoppage_sec = (s.attack_state == Status::OFFENSE && last_gained_possession != 0 && num_touches != 0) ? time_spent_in_stoppage / 1000 : 0;
 445         return s;
 446 }
 447
 448 set<int> EventsModel::get_team_at(uint64_t t)
 449 {
 450         set<int> team;
 451         for (const Event &e : events) {
 452                 if (e.t > t) {
 453                         break;
 454                 }
 455                 if (e.type == "in") {
 456                         team.insert(*e.player_id);
 457                 }
 458                 if (e.type == "out") {
 459                         team.erase(*e.player_id);
 460                 }
 461         }
 462         return team;
 463 }
 464
 465 void EventsModel::set_team_at(uint64_t t, const set<int> &new_team)
 466 {
 467         // Backdate to the last goal or stoppage, _or_ the last time someone
 468         // going out is mentioned. (We don't really track injuries yet;
 469         // do we want an explicit injury type? If we had one, it would probably
 470         // be the simplest.)
 471         uint64_t backdate_point = 0;
 472         for (const Event &e : events) {
 473                 if (e.t > t) {
 474                         break;
 475                 }
 476                 if (e.type == "goal" || e.type == "their_goal" || e.type == "stoppage" || e.type == "reset" || e.type == "set_offense" || e.type == "set_defense") {
 477                         backdate_point = e.t + 1;
 478                 }
 479                 if (e.player_id.has_value() && !new_team.count(*e.player_id)) {
 480                         backdate_point = e.t + 1;
 481                 }
 482         }
 483
 484         // Delete all in/outs already at the backdate point.
 485         for (unsigned i = 0; i < events.size(); ) {
 486                 if (events[i].t > backdate_point) {
 487                         break;
 488                 }
 489                 if (events[i].t == backdate_point && (events[i].type == "in" || events[i].type == "out")) {
 490                         delete_event(i);
 491                 } else {
 492                         ++i;
 493                 }
 494         }
 495
 496         // Finally make the subs we need.
 497         set<int> old_team = get_team_at(backdate_point);
 498         for (int player_id : old_team) {
 499                 if (!new_team.count(player_id)) {
 500                         insert_event(backdate_point, player_id, nullopt, "out");
 501                 }
 502         }
 503         for (int player_id : new_team) {
 504                 if (!old_team.count(player_id)) {
 505                         insert_event(backdate_point, player_id, nullopt, "in");
 506                 }
 507         }
 508 }
 509
 510 void EventsModel::set_formation_at(uint64_t t, bool offense, unsigned formation)
 511 {
 512         // If there's another goal/stoppage/turnover no more than 20 seconds ago,
 513         // we assume that the formation started at that point (it just took
 514         // the operator a bit of time to see it). If not, we assume we
 515         // changed in the middle of a point.
 516         uint64_t backdate_point = 0;
 517         for (const Event &e : events) {
 518                 if (e.t > t) {
 519                         break;
 520                 }
 521                 if (e.type == "goal" || e.type == "their_goal" ||
 522                     e.type == "in" || e.type == "out" ||
 523                     e.type == "stoppage" || e.type == "reset" ||
 524                     e.type == "set_defense" || e.type == "set_offense" ||
 525                     e.type == "throwaway" || e.type == "their_throwaway" ||
 526                     e.type == "drop" || e.type == "was_d" || e.type == "defense" || e.type == "interception" || e.type == "stallout" ||
 527                     e.type == "pull" || e.type == "pull_landed" || e.type == "pull_oob" || e.type == "their_pull" ||
 528                     e.type == "formation_offense" || e.type == "formation_defense") {
 529                         backdate_point = e.t + 1;
 530                 }
 531                 if (e.type == "formation_offense" || e.type == "formation_defense") {
 532                         backdate_point = 0;
 533                 }
 534         }
 535         if (backdate_point != 0 && t - backdate_point < 20000) {
 536                 t = backdate_point;
 537         }
 538         if (offense) {
 539                 insert_event(t, nullopt, formation == 0 ? nullopt : optional{formation}, "formation_offense");
 540         } else {
 541                 insert_event(t, nullopt, formation == 0 ? nullopt : optional{formation}, "formation_defense");
 542         }
 543 }
 544
 545 vector<int> EventsModel::sort_team(const set<int> &team) const
 546 {
 547         vector<int> ret(team.begin(), team.end());
 548         std::sort(ret.begin(), ret.end(), [this](int a, int b) {
 549                 return player_ordering.find(a)->second < player_ordering.find(b)->second;
 550         });
 551         return ret;
 552 }