database: for the record: translations of movies/seasons/episodes

[syndr0m]

@see https://docs.google.com/a/afrostream.tv/spreadsheets/d/1wb5ex92TANKT95rvEQ8tOnzBcrSWDgmECBoA5E3RITQ/edit?usp=sharing

step 1: manuel: on associe aux movies leur id de la base

@see spreadsheet

step 2: on extract de la base ce qui est déjà traduit ou pas traduit

SELECT "Movies"."_id", "Movies"."title", "Movies"."type", 
  "joins"."seasonTitle", "joins"."seasonId", "joins"."seasonNumber",
  "joins"."episodeTitle", "joins"."episodeId", "joins"."episodeNumber",
  CASE WHEN "Movies"."type" = 'serie' THEN "joins"."videoId" ELSE "Movies"."videoId" END,

  "Movies"."title" as "movieTitle",
  "Movies".translations->'title'->>'EN' as "movieTitleEN",
  "Movies"."synopsis" as "movieSynopsis",
  "Movies".translations->'synopsis'->>'EN' as "movieSynopsisEN",

  "joins"."seasonTitle",
  "joins"."seasonTranslations"->'title'->>'EN' as "seasonTitleEN",
  "joins"."seasonSynopsis",
  "joins"."seasonTranslations"->'synopsis'->>'EN' as "seasonSynopsisEN",

  "joins"."episodeTitle",
  "joins"."episodeTranslations"->'title'->>'EN' as "episodeTitleEN",
  "joins"."episodeSynopsis",
  "joins"."episodeTranslations"->'synopsis'->>'EN' as "episodeSynopsisEN"

FROM "Movies"
LEFT JOIN (
   SELECT "Seasons"."movieId",
          "Seasons"."title" as "seasonTitle", "Seasons"."synopsis" as "seasonSynopsis",
          "Seasons"."_id" as "seasonId", "Seasons"."seasonNumber",
          "Seasons"."translations" as "seasonTranslations",
          "Episodes"."title" as "episodeTitle", "Episodes"."synopsis" as "episodeSynopsis",
          "Episodes"."_id" as "episodeId", "Episodes"."episodeNumber",
          "Episodes"."translations" as "episodeTranslations",
          "Episodes"."videoId" 
   FROM "Seasons"
   INNER JOIN "Episodes" ON "Episodes"."seasonId" = "Seasons"."_id"
) as "joins" ON "Movies".type='serie' AND "joins"."movieId" = "Movies"."_id"
WHERE "_id" IN (
'24',
'25',
'26',
'35',
'39',
'47',
'48',
'49',
'50',
'51',
'52',
'53',
'54',
'123',
'131',
'193',
'195',
'196',
'197',
'198',
'199',
'206',
'207',
'208',
'210',
'217',
'219',
'220',
'241',
'242',
'243',
'244',
'257',
'258',
'261',
'262',
'263',
'264',
'265',
'272',
'273',
'274',
'275',
'277',
'278',
'279',
'281',
'291',
'299',
'304',
'327',
'332',
'366',
'367',
'378',
'384')
ORDER BY "Movies".type, "Movies"."_id", "joins"."seasonNumber", "joins"."episodeNumber"

step3: on reintègre les données traduites dans la base

backup des tables impactées

create table "backupMovies30032017" AS TABLE "Movies";
create table "backupSeasons30032017" AS TABLE "Seasons";
create table "backupEpisodes30032017" AS TABLE "Episodes";

on crée dans le spreadsheet des vues spécifiques par objet en vue d'un export CSV @see spreadsheet

on se log sur heroku

heroku run --app afrostream-backend bash

on y intègre les données

cat > m.csv
(ON COLLE ICI LE CONTENU DU CSV)

on y intègre le script js

cat > my-import.js
(ON COLLE ICI LE CONTENU DU SCRIPT CI DESSOUS)

script js :

// npm install csv-parse

process.env.NODE_ENV='production';

global.__dirname = '/app';
global.__basedir = __dirname;
global.rootRequire = name => require(global.__basedir + '/' + (name[0] === '/' ? name.substr(1) : name));

const Q = require('q');
const _ = require('lodash');
const sqldb = rootRequire('sqldb');
const Movie = sqldb.Movie;

// chargement de la data
const parse = require('csv-parse');
const fs = require('fs');

Q.nfcall(parse, fs.readFileSync(__dirname+'/m.csv'))
  .then(data => {

    console.log('processing ' + data.length + ' entries');

    return data.reduce((p, c, i) => {
      return p.then(() => {
        const [ movieId, titleEN, synopsisEN, scheduleEN ] = c;

        const translations = { };
        if (titleEN) {
          translations.title = { EN: titleEN };
        }
        if (synopsisEN) {
          translations.synopsis = { EN: synopsisEN };
        }
        if (scheduleEN) {
          translations.schedule = { EN: scheduleEN };
        }        

        if (!movieId) {
          console.error('no movie id => skip index ' + i);
          return; // skip
        }
        if (isNaN(parseInt(movieId))) {
          console.error('movie id is not a number => skip index ' + i);
          return;
        }

        console.log('processing ' + i + ' / ' + data.length + ' movieId = ' + movieId);
        return Movie.findById(movieId)
          .then(m => {
            if (!m) {
              throw new Error('cannot find movie ' + movieId);
            }
            m.translations = _.merge(translations, m.translations);
            console.log(movieId, m.translations);
            return m.save(); // dans un premier temps, on peut commenter cette ligne pour tester :)
          });
      });
    }, Q());
  })
  .then(
    () => { console.log('success'); },
    err => console.error('Error : ' , err.message, err.stack)
   );

on npm install les outils manquant

npm install parse-csv

on run le script

node my-import.js

[syndr0m]

(done)