inde.js

//node --max-old-space-size=8192 index.js
import {Dex} from '@pkmn/dex';
import {Sets} from '@pkmn/sets';
import * as fs from 'fs';
import * as bigjson from 'big-json';
import  * as mysql from 'mysql';

const origin="smogon";
const readStream = fs.createReadStream('/home/gigad/teams/output.json');
const parseStream = bigjson.createParseStream();

function findGen(tier) {
    const gen = parseInt(tier[3]);
    if (isNaN(gen)) return 6;
    return gen;
}

function pastify(team, gen) {
  var pak="";
  //console.log(team);
  for (let poke of team) {
    var o = {};
    o["species"] = (poke["FormName"] != "null") ? poke["Name"] : poke["formName"];
    o["ability"] = poke["Ability"];
    o["item"] = poke["Item"];
    o["teraType"] = poke["TeraType"];
    o["moves"] = poke["Moves"];
    pak+=Sets.pack(
      o,
      Dex.forGen(gen)
    );
    pak+="]";
  };
  pak = pak.slice(0,-1);
  return pak;
};

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  database: 'RTB',
  password: "0000"
});

con.connect(function(err) {
if (err) throw err;
console.log("Connected!");

// 'ping' SQL server every hour to keep connection alive
setInterval(() => {
    con.query('SELECT 1');
}, 3600000);

con.query(`SET FOREIGN_KEY_CHECKS=0;`);
con.query(`DROP TABLE IF EXISTS RTB.replay;`);
con.query(`DROP TABLE IF EXISTS RTB.team;`);
con.query(`DROP TABLE IF EXISTS RTB.ps_user;`);
con.query(`DROP TABLE IF EXISTS RTB.smogon_user;`);
con.query(`DROP TABLE IF EXISTS RTB.post;`);
con.query(`SET FOREIGN_KEY_CHECKS=1;`);


con.query(`CREATE TABLE RTB.ps_user (username VARCHAR(18) NOT NULL PRIMARY KEY)`, function (err, result) {
  if (err) throw err;
  console.log("Result: " + result);
});

con.query(`CREATE TABLE RTB.replay (
  id BIGINT(10) NOT NULL,
  server_id INT(1) NOT NULL,
  server TINYTEXT,
  UploadTime INT(10),
  isPrivate BOOL,
  password VARCHAR(33),
  tier TINYTEXT,
  p1 VARCHAR(18),
  p2 VARCHAR(18),
  winner VARCHAR(18),
  looser VARCHAR(18),
  rating INT(4),
  log LONGTEXT,
  origin TINYTEXT,
  path INT(7),
  PRIMARY KEY(id, server_id),
  FOREIGN KEY (p1) REFERENCES ps_user(username),
  FOREIGN KEY (p2) REFERENCES ps_user(username)
)`, function (err, result) {
  if (err) throw err;
  console.log("Result: " + result);
});

con.query(`CREATE TABLE RTB.team (n int NOT NULL AUTO_INCREMENT,
  replay_id BIGINT(10) NOT NULL,
  server_id INT(1) NOT NULL,
  player VARCHAR(18) NOT NULL,
  tier TINYTEXT,
  pokelead INT(6),
  poke1 INT(6),
  poke2 INT(6),
  poke3 INT(6),
  poke4 INT(6),
  poke5 INT(6),
  poke6 INT(6),
  scout TEXT,
  PRIMARY KEY (replay_id,server_id,player),
  FOREIGN KEY (replay_id, server_id) REFERENCES replay(id, server_id),
  FOREIGN KEY (player) REFERENCES ps_user(username)
)`, function (err, result) {
  if (err) throw err;
  console.log("Result: " + result);
});

var c=0;

parseStream.on('data', function(pojo) {
    //parcours du JSON parsé
    for (const i in pojo) {
      for (const j in pojo[i]["Teams"]) {
        var team = pojo[i]["Teams"][j];
        for (const replay in team["Replays"]) {
          const k = team["Replays"][replay];
          const id = k["Id"].split("-").reverse();
          const tier = k["FormatId"];
          const isPrivate = k["Private"];
          const password = k["Password"];
          const rating = k["Rating"];
          const isRated = (rating != "null") ? true : false;
          const UploadTime = k["UploadTime"];
          const path = pojo[i]["i"];
          const past = pastify(team["Pokemon"],(findGen(tier)));
          const p1 = k["Players"][0].replace(/\W/g, '');
          const p2 = k["Players"][1].replace(/\W/g, '');
          const winner = k["Winner"]?.replace(/\W/g, '');
          var looser; //= (winner==p1) ? p2 : p1;
          if (winner==p1) looser = p2;
          else if (winner==p2) looser = p1;
          else looser = null;
          const player = k["PlayerInfo"]["PlayerName"].replace(/\W/g, '');
          var server_id;
          var server;
          if (id.length==3) {
            server = id[2].replace(/\W/g, '');
            if (server=="smogtours") {server_id=1} else server_id=2;
          } else {
            server_id = 0;
            server = "main";
          };

          con.query(`INSERT IGNORE INTO ps_user (username) VALUES ('${p1}');`, function (err, result) {
            if (err) throw err;
            //p1_id = result["id"];
          });

          con.query(`INSERT IGNORE INTO ps_user (username) VALUES ('${p2}');`, function (err, result) {
            if (err) throw err;
            //p2_id = result["id"];
          });

          con.query(`INSERT IGNORE INTO replay (id, server_id, server, UploadTime, isPrivate, password, tier, p1, p2, winner, looser, rating, origin, path)
            VALUES (${id[0]},${server_id},'${server}',${UploadTime},${isPrivate},'${password}','${tier}','${p1}','${p2}','${winner}','${looser}',${rating},'${origin}',${path});`, function (err, result) {
            if (err) throw err;
            //console.log("Result: " + i);
          });

          /*
          console.log({"ID":id,"isPrivate":isPrivate,"password":password,"tier":tier,"p1":p1,"p2":p2,"winner":winner,"rating":rating,rating,"origin":origin,"path":path})

          console.log("=========================");

          console.log({"replay_id":id,"UploadTime":UploadTime,"tier":tier,"player":player,
          1: Dex.data.Species[team["Pokemon"][0]["Name(/\W/g, '')"].replace(/\s+/g,'').toLowerCase()]["num"],
          2: Dex.data.Species[team["Pokemon"][1]["Name"].replace(/\s+/g,'').toLowerCase()]["num"],
          3: Dex.data.Species[team["Pokemon"][2]["Name"].replace(/\s+/g,'').toLowerCase()]["num"],
          4: Dex.data.Species[team["Pokemon"][3]["Name"].replace(/\s+/g,'').toLowerCase()]["num"],
          5: Dex.data.Species[team["Pokemon"][4]["Name"].replace(/\s+/g,'').toLowerCase()]["num"],
          6: Dex.data.Species[team["Pokemon"][5]["Name"].replace(/\s+/g,'').toLowerCase()]["num"],
          "scout":past})
          */
          function getDex(n) {
            const poke = team["Pokemon"][n]?.["Name"].replace(/\W/g, '').toLowerCase();
            if (typeof(Dex.data.Species[poke]) === "undefined") return "NULL";
            const form = Dex.data.Species[Dex.data.Species[poke]?.["baseSpecies"]?.replace(/\W/g, '')?.toLowerCase()]?.["formeOrder"]?.indexOf(team["Pokemon"][n]?.["Name"]?.trim());
            if (typeof(form) === "undefined") {
              const res = 100 * Dex.data.Species[poke]?.["num"];
              if (team["Pokemon"][n]["Lead"]) lead=res;
              return (res || "NULL");
            } else {
              const res = 100 * Dex.data.Species[poke]?.["num"] + form;
              if (team["Pokemon"][n]["Lead"]) lead=res;
              return (res || "NULL");
            }
          };

          var lead = getDex(0);


          //if (typeof(Dex.data.Species[team["Pokemon"][1]["Name"].replace(/\W/g, '').toLowerCase()])=="undefined") {console.log(poke)};

          con.query(`INSERT INTO team (replay_id, server_id, tier, player, pokelead, poke1, poke2, poke3, poke4, poke5, poke6, scout)
            VALUES (${id[0]},${server_id},'${tier}','${player}',${lead},
            ${[getDex(0),getDex(1),getDex(2),getDex(3),getDex(4),getDex(5)].sort(function(a, b) {return a - b;}).toString()},
            "${past}");`, function (err, result) {
              if (c%100==0) console.log(c + " : " + result);
              c+=1;
              if (err) {
                throw err
            };
            //console.log("Result: " + i);
          });

          /*
          team["Pokemon"].forEach((poke) => {
            //console.log(poke["Name"]);
            console.log(poke);
          });
          */
        };
      };
    };
});
}); //sqlco

readStream.pipe(parseStream);