portal shit!

docker create \
  -e DATABASE_URL=db_url \
  -e RACK_ENV=production \
  -v /home/morygonzalez/sites/portalshit/public:/app/public \
  -p 3001:3001 --name portalshit -it morygonzalez/portalshit bundle exec puma -p 3001

docker start portalshit

jobs:
  deploy:
    docker:
      - image: docker:17.05.0-ce-git
    steps:
      - checkout
      - setup_remote_docker:
          docker_layer_caching: true
      - run:
          name: Install dependencies
          command: |
            apk add --no-cache py-pip=9.0.0-r1 jq curl curl-dev bash
            pip install docker-compose==1.18.0 awscli==1.14.38
            curl -s https://raw.githubusercontent.com/silinternational/ecs-deploy/ac2b53cb358814ff2cdf753365cc0ea383d7b77c/ecs-deploy | tee -a /usr/bin/ecs-deploy && chmod +x /usr/bin/ecs-deploy
      - run:
          name: Execute deployment (Docker image build, push to ECR, create new Task and replace container)
          command: |
            case ${CIRCLE_BRANCH} in
              "deployment/dev" | "master" )
                DEPLOY_ENV="dev" ;;
              "deployment/production" )
                DEPLOY_ENV="production" ;;
            esac
            bin/deploy ${DEPLOY_ENV}

workflows:
  version: 2
  production-deploy:
    jobs:
      - deploy:
          filters:
            branches:
              only:
                - deployment/production

desc "Detect and update similar entries"
task similar_entries: %i[similar_entries:extract_term similar_entries:vector_normalize similar_entries:export]

namespace :similar_entries do
  require 'sqlite3'
  desc "Extract term"
  task :extract_term do
    require 'natto'
    nm = Natto::MeCab.new
    db = SQLite3::Database.new('db/tfidf.sqlite3')
    create_table_sql =<<~SQL
      DROP TABLE IF EXISTS tfidf;
      CREATE TABLE tfidf (
        `id` INTEGER PRIMARY KEY,
        `term` TEXT NOT NULL,
        `entry_id` INTEGER NOT NULL,
        `term_count` INTEGER NOT NULL DEFAULT 0, -- エントリ内でのターム出現回数
        `tfidf` FLOAT NOT NULL DEFAULT 0, -- 正規化前の TF-IDF
        `tfidf_n` FLOAT NOT NULL DEFAULT 0 -- ベクトル正規化した TF-IDF
      );
      CREATE UNIQUE INDEX index_tf_term ON tfidf (`term`, `entry_id`);
      CREATE INDEX index_tf_entry_id ON tfidf (`entry_id`);
    SQL
    db.execute_batch(create_table_sql)

    entries = Entry.published.all(fields: [:id, :body])
    entry_frequencies = {}
    entries.each do |entry|
      words = []
      body_cleansed = entry.body.
        gsub(/<.+?>/, '').
        gsub(/!?\[.+?\)/, '').
        gsub(/(```|<code>).+?(```|<\/code>)/m, '')
      begin
        nm.parse(body_cleansed) do |n|
          next if !n.feature.match(/名詞/)
          next if n.feature.match(/(サ変接続|数)/)
          next if n.surface.match(/\A([a-z][0-9]|\p{hiragana}|\p{katakana})\Z/i)
          next if %w[これ こと とき よう そう やつ とこ ところ 用 もの はず みたい たち いま 後 確か 中 気 方 頃 上 先 点 前 一 内 lt gt ここ なか どこ まま わけ ため 的 それ あと].include?(n.surface)
          words << n.surface
        end
      rescue ArgumentError
        next
      end
      frequency = words.inject(Hash.new(0)) {|sum, word| sum[word] += 1; sum }
      entry_frequencies[entry.id] = frequency
    end
    entry_frequencies.each do |entry_id, frequency|
      frequency.each do |word, count|
        db.execute("INSERT INTO tfidf (`term`, `entry_id`, `term_count`) VALUES (?, ?, ?)", [word, entry_id, count])
      end
    end
  end

  desc "Vector Normalize"
  task :vector_normalize do
    db = SQLite3::Database.new('db/tfidf.sqlite3')

    load_extension_sql =<<~SQL
      -- SQRT や LOG を使いたいので
      SELECT load_extension('/usr/local/Cellar/sqlite/3.21.0/lib/libsqlitefunctions.dylib');
    SQL
    db.enable_load_extension(true)
    db.execute(load_extension_sql)

    update_tfidf_column_sql = <<~SQL
      -- エントリ数をカウントしておきます
      -- SQLite には変数がないので一時テーブルにいれます
      CREATE TEMPORARY TABLE entry_total AS
          SELECT CAST(COUNT(DISTINCT entry_id) AS REAL) AS value FROM tfidf;

      -- ワード(ターム)が出てくるエントリ数を数えておきます
      -- term と entry_id でユニークなテーブルなのでこれでエントリ数になります
      CREATE TEMPORARY TABLE term_counts AS
          SELECT term, CAST(COUNT(*) AS REAL) AS cnt FROM tfidf GROUP BY term;
      CREATE INDEX temp.term_counts_term ON term_counts (term);

      -- エントリごとの合計ワード数を数えておきます
      CREATE TEMPORARY TABLE entry_term_counts AS
          SELECT entry_id, LOG(CAST(SUM(term_count) AS REAL)) AS cnt FROM tfidf GROUP BY entry_id;
      CREATE INDEX temp.entry_term_counts_entry_id ON entry_term_counts (entry_id);

      -- TF-IDF を計算して埋めます
      -- ここまでで作った一時テーブルからひいて計算しています。
      UPDATE tfidf SET tfidf = IFNULL(
          -- tf (normalized with Harman method)
          (
              LOG(CAST(term_count AS REAL) + 1) -- term_count in an entry
              /
              (SELECT cnt FROM entry_term_counts WHERE entry_term_counts.entry_id = tfidf.entry_id) -- total term count in an entry
          )
          *
          -- idf (normalized with Sparck Jones method)
          (1 + LOG(
              (SELECT value FROM entry_total) -- total
              /
              (SELECT cnt FROM term_counts WHERE term_counts.term = tfidf.term) -- term entry count
          ))
      , 0.0);
    SQL
    db.execute_batch(update_tfidf_column_sql)

    vector_normalize_sql = <<~SQL
      -- エントリごとのTF-IDFのベクトルの大きさを求めておきます
      CREATE TEMPORARY TABLE tfidf_size AS
          SELECT entry_id, SQRT(SUM(tfidf * tfidf)) AS size FROM tfidf
          GROUP BY entry_id;
      CREATE INDEX temp.tfidf_size_entry_id ON tfidf_size (entry_id);

      -- 計算済みの TF-IDF をベクトルの大きさで割って正規化します
      UPDATE tfidf SET tfidf_n = IFNULL(tfidf / (SELECT size FROM tfidf_size WHERE entry_id = tfidf.entry_id), 0.0);
    SQL
    db.execute_batch(vector_normalize_sql)
  end

  desc "Export calculation result to MySQL"
  task :export do
    db = SQLite3::Database.new('db/tfidf.sqlite3')
    create_similar_candidate_sql = <<~SQL
      DROP TABLE IF EXISTS similar_candidate;
      DROP INDEX IF EXISTS index_sc_parent_id;
      DROP INDEX IF EXISTS index_sc_entry_id;
      DROP INDEX IF EXISTS index_sc_cnt;
      CREATE TABLE similar_candidate (
        `id` INTEGER PRIMARY KEY,
        `parent_id` INTEGER NOT NULL,
        `entry_id` INTEGER NOT NULL,
        `cnt` INTEGER NOT NULL DEFAULT 0
      );
      CREATE INDEX index_sc_parent_id ON similar_candidate (parent_id);
      CREATE INDEX index_sc_entry_id ON similar_candidate (entry_id);
      CREATE INDEX index_sc_cnt ON similar_candidate (cnt);
    SQL
    db.execute_batch(create_similar_candidate_sql)

    extract_similar_entries_sql = <<~SQL
      -- 類似していそうなエントリを共通語ベースでまず100エントリほど出します
      INSERT INTO similar_candidate (`parent_id`, `entry_id`, `cnt`)
          SELECT ? as parent_id, entry_id, COUNT(*) as cnt FROM tfidf
          WHERE
              entry_id <> ? AND
              term IN (
                  SELECT term FROM tfidf WHERE entry_id = ?
                  ORDER BY tfidf DESC
                  LIMIT 50
              )
          GROUP BY entry_id
          HAVING cnt > 3
          ORDER BY cnt DESC
          LIMIT 100;
    SQL

    search_similar_entries_sql = <<~SQL
      -- 該当する100件に対してスコアを計算してソートします
      SELECT
          ? AS entry_id,
          entry_id AS similar_entry_id,
          SUM(a.tfidf_n * b.tfidf_n) AS score
      FROM (
          (SELECT term, tfidf_n FROM tfidf WHERE entry_id = ? ORDER BY tfidf DESC LIMIT 50) as a
          INNER JOIN
          (SELECT entry_id, term, tfidf_n FROM tfidf WHERE entry_id IN (SELECT entry_id FROM similar_candidate WHERE parent_id = ?)) as b
          ON
          a.term = b.term
      )
      WHERE similar_entry_id <> ?
      GROUP BY entry_id
      ORDER BY score DESC
      LIMIT 10;
    SQL

    results = {}
    Entry.published.all(fields: [:id]).each do |entry|
      db.execute(extract_similar_entries_sql, [entry.id, entry.id, entry.id])
      db.results_as_hash = true
      similarities = db.execute(search_similar_entries_sql, [entry.id, entry.id, entry.id, entry.id])
      results[entry.id] = similarities
    end

    Similarity.destroy

    results.each do |entry_id, similarities|
      if similarities.present?
        similarities.each do |s|
          conditions = { entry_id: s["entry_id"], similar_entry_id: s["similar_entry_id"] }
          similarity = Similarity.new(conditions)
          similarity.score = s["score"]
          similarity.save
        end
      end
    end
  end
end

version: 2

shared: &shared
  working_directory: ~/app
  docker:
    - image: xxxxxxxxxxxx.dkr.ecr.ap-northeast-1.amazonaws.com/organization/app
      environment:
        PGHOST: 127.0.0.1
        PGUSER: user
        RAILS_ENV: test
        REDIS_HOST: localhost
    - image: circleci/postgres:9.6-alpine
      environment:
        POSTGRES_USER: user
        POSTGRES_PASSWORD: password
    - image: redis:3.2-alpine

jobs:
  build:
    <<: *shared
    steps:
      - checkout
      # Restore bundle cache
      - &restore_cache
        type: cache-restore
        key: app-{{ arch }}-{{ checksum "Gemfile.lock" }}
      # Bundle install dependencies
      - &bundle_install
        run: bundle install -j4 --path vendor/bundle
      # Store bundle cache
      - &save_cache
        type: cache-save
        key: app-{{ arch }}-{{ checksum "Gemfile.lock" }}
        paths:
            - vendor/bundle
      # Database setup
      - &db_setup
        run:
          name: Database Setup
          command: |
            bundle exec rake db:create
            bundle exec rake db:structure:load
      - type: shell
        command: bundle exec rubocop
      # Run rspec in parallel
      - type: shell
        command: |
          mkdir coverage
          COVERAGE=1 bundle exec rspec --profile 10 \
                            --format RspecJunitFormatter \
                            --out /tmp/test-results/rspec.xml \
                            --format progress \
                            $(circleci tests glob "spec/**/*_spec.rb" | circleci tests split --split-by=timings)
      # Save artifacts
      - type: store_test_results
        path: /tmp/test-results
      - type: store_artifacts
        path: coverage

  generate-doc:
    <<: *shared
    steps:
      - run:
          name: Install dependencies
          command: |
            apk add --no-cache git openssh ca-certificates
      - checkout
      - *restore_cache
      - *bundle_install
      - *save_cache
      - *db_setup
      # Generate document
      - run:
          name: Generate API doc
          command: |
            AUTODOC=1 bundle exec rake spec:requests
      - run:
          name: Generate Schema doc
          command: |
            diff=$(git diff HEAD^ db)
            if [ -n diff ]; then
              bundle exec rake schema_doc:out > doc/schema.md
            fi
      - run:
          name: Setup GitHub
          command: |
            export USERNAME=$(git log --pretty=tformat:%an | head -1)
            export EMAIL=$(git log --pretty=tformat:%ae | head -1)
            git config --global user.email "${EMAIL}"
            git config --global user.name "${USERNAME}"
      - run:
          name: Push updated doc to GitHub
          command: |
            git add doc
            git commit --quiet -m "[ci skip] API document Update

            ${CIRCLE_BUILD_URL}"
            git push origin ${CIRCLE_BRANCH}

  deploy:
    docker:
      - image: docker:17.05.0-ce-git
    steps:
      - checkout
      - setup_remote_docker
      - run:
          name: Install dependencies
          command: |
            apk add --no-cache \
              py-pip=9.0.0-r1 jq curl curl-dev bash
            pip install \
              docker-compose==1.12.0 \
              awscli==1.11.76
            curl https://raw.githubusercontent.com/silinternational/ecs-deploy/ac2b53cb358814ff2cdf753365cc0ea383d7b77c/ecs-deploy | tee -a /usr/bin/ecs-deploy \
              && chmod +x /usr/bin/ecs-deploy
      - restore_cache:
          keys:
            - v1-{{ .Branch }}
          paths:
            - /caches/app.tar
      - run:
          name: Load Docker image layer cache
          command: |
            set +o pipefail
            docker load -i /caches/app.tar | true
      - run:
          name: Build application Docker image
          command: |
            docker build --file=docker/app/Dockerfile --cache-from=app -t organization/app .
      - run:
          name: Save Docker image layer cache
          command: |
            mkdir -p /caches
            docker save -o /caches/app.tar organization/app
      - save_cache:
          key: v1-{{ .Branch }}-{{ epoch }}
          paths:
            - /caches/app.tar
      - run:
          name: Push application Docker image to ECR
          command: |
            login="$(aws ecr get-login --region ap-northeast-1)"
            ${login}
            docker tag organiation/app:latest xxxxxxxxxxxx.dkr.ecr.ap-northeast-1.amazonaws.com/organization/app:latest
            docker push xxxxxxxxxxxx.dkr.ecr.ap-northeast-1.amazonaws.com/organization/app:latest
      - run:
          name: Deploy container
          command: |
            ecs-deploy \
              --region ap-northeast-1 \
              --cluster app-dev \
              --service-name puma \
              --image xxxxxxxxxxxx.dkr.ecr.ap-northeast-1.amazonaws.com/organization/app:latest \
              --timeout 300

workflows:
  version: 2
  build-and-generate-doc:
    jobs:
      - build
      - generate-doc:
          requires:
            - build
          filters:
            branches:
              only:
                - master
      - deploy:
          requires:
            - build
          filters:
            branches:
              only:
                - master

version: 2

shared: &shared
  working_directory: ~/app
  docker:
    - image: circleci/ruby:2.4.1-node
      environment:
        PGHOST: 127.0.0.1
        PGUSER: username
        RAILS_ENV: test
        REDIS_HOST: localhost
    - image: circleci/postgres:9.6-alpine
      environment:
        POSTGRES_USER: username
        POSTGRES_PASSWORD: pasword
    - image: redis:3.2-alpine

jobs:
  build:
    <<: *shared
    steps:
      - checkout
      # Restore bundle cache
      - &restore_cache
        type: cache-restore
        key: app-{{ checksum "Gemfile.lock" }}
      # Bundle install dependencies
      - &bundle_install
        run: bundle install -j4 --path vendor/bundle
      # Store bundle cache
      - &save_cache
        type: cache-save
        key: app-{{ checksum "Gemfile.lock" }}
        paths:
            - vendor/bundle
      # Database setup
      - &db_setup
        run:
          name: Database Setup
          command: |
            sudo apt install postgresql-client
            bundle exec rake db:create
            bundle exec rake db:structure:load
      - type: shell
        command: bundle exec rubocop
      # Run rspec in parallel
      - type: shell
        command: |
          mkdir coverage
          COVERAGE=1 bundle exec rspec --profile 10 \
            --format RspecJunitFormatter \
            --out /tmp/test-results/rspec.xml \
            --format progress \
            $(circleci tests glob "spec/**/*_spec.rb" | circleci tests split --split-by=timings)
      # Save artifacts
      - type: store_test_results
        path: /tmp/test-results
      - type: store_artifacts
        path: coverage

  generate-doc:
    <<: *shared
    steps:
      - checkout
      - *restore_cache
      - *bundle_install
      - *save_cache
      - *db_setup
      # Generate document
      - type: shell
        command: |
          AUTODOC=1 bundle exec rake spec:requests
      - run:
          name: Setup GitHub
          command: |
            export USERNAME=$(git log --pretty=tformat:%an | head -1)
            export EMAIL=$(git log --pretty=tformat:%ae | head -1)
            git config --global user.email "${EMAIL}"
            git config --global user.name "${USERNAME}"
      - run:
          command: |
            git add doc
            git commit --quiet -m "[ci skip] API document Update

            ${CIRCLE_BUILD_URL}"
            git push origin ${CIRCLE_BRANCH}

workflows:
  version: 2
  build-and-generate-doc:
    jobs:
      - build
      - generate-doc:
          requires:
            - build
          filters:
            branches:
              only:
                - master