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