portal shit!

cat log/access.log | grep 'useragent:Hatena::Russia::Crawler/0.01' | cut -f2 | sort | uniq -c | sort -nr
    434 remote_addr:52.68.0.227
    419 remote_addr:54.249.85.140
    417 remote_addr:54.92.97.59
    379 remote_addr:54.250.227.185

zcat -f log/access.log* | grep -E 'remote_addr:(52\.68\.0\.227|54\.249\.85\.140|54\.92\.97\.59|54\.250\.227\.185)' | cut -f13,2 | sort | uniq -c | sort -nr
  16687 remote_addr:54.250.227.185      useragent:Hatena::Russia::Crawler/0.01
  16448 remote_addr:54.92.97.59 useragent:Hatena::Russia::Crawler/0.01
  16370 remote_addr:54.249.85.140       useragent:Hatena::Russia::Crawler/0.01
  16272 remote_addr:52.68.0.227 useragent:Hatena::Russia::Crawler/0.01
     73 remote_addr:54.249.85.140       useragent:HatenaBookmark/4.0 (Hatena::Bookmark; Scissors)
     60 remote_addr:54.250.227.185      useragent:HatenaBookmark/4.0 (Hatena::Bookmark; Scissors)
     56 remote_addr:52.68.0.227 useragent:HatenaBookmark/4.0 (Hatena::Bookmark; Scissors)
     50 remote_addr:54.92.97.59 useragent:HatenaBookmark/4.0 (Hatena::Bookmark; Scissors)
     31 remote_addr:54.92.97.59 useragent:Hatena::Fetcher/0.01 (master) Furl/3.13
     31 remote_addr:54.250.227.185      useragent:Hatena::Fetcher/0.01 (master) Furl/3.13
     31 remote_addr:54.249.85.140       useragent:Hatena::Fetcher/0.01 (master) Furl/3.13
     26 remote_addr:52.68.0.227 useragent:Hatena::Fetcher/0.01 (master) Furl/3.13
     19 remote_addr:54.92.97.59 useragent:Hatena::Scissors/0.01
     19 remote_addr:54.250.227.185      useragent:Hatena::Scissors/0.01
     16 remote_addr:52.68.0.227 useragent:Hatena::Scissors/0.01
      9 remote_addr:54.249.85.140       useragent:Hatena::Scissors/0.01

zcat -f log/access.log* | grep 'useragent:Hatena::Russia::Crawler/0.01' | cut -f5 | sort | uniq -c | sort -nr
  15697 request_uri:/index.atom
   9913 request_uri:/2022/04/20/integrate-charts-category-with-select-boxs
   9373 request_uri:/2022/05/04/reputation-and-interpretation
   8422 request_uri:/2022/05/11/fly-to-kamikochi-from-fukuoka
   7716 request_uri:/2022/05/16/using-tantivy-over-tantiny
   5906 request_uri:/2022/04/17/quit-using-hey
   5139 request_uri:/2021/12/29/thoughts-on-manga-subscription
   1308 request_uri:/2021/12/13/keep-a-stack-books-whether-reading-them-or-not
    787 request_uri:/2022/06/23/each-entry-title-should-be-marked-up-with-h1
    741 request_uri:/2021/12/13/keep-stack-books-whether-reading-them-or-not
    456 request_uri:/2022/06/24/if-you-feel-apple-musics-recommendation-is-awful
    100 request_uri:/2022/06/14/thoughts-on-hatena-bookmark
     46 request_uri:/2015/12/07/thoughts-on-rural-life
     46 request_uri:/2015/12/02/thoughts-on-t-on-t
     46 request_uri:/2015/12/02/thoughts-on-christmas-song
     45 request_uri:/2019/12/02/stop-drinking-outside-frequently
     16 request_uri:/2020/11/08/where-i-went-in-2019
     11 request_uri:/2015/12/05/omm-writer-music-is-nice-to-listen-to-while-writing
      5 request_uri:/2022/05/04/the-golden-maintenance-week
      2 request_uri:/2022/06/24/
      2 request_uri:/2022/06/24

zcat -f log/access.log* | cut -f13 | sort | uniq -c | sort -nr | head -10
 124894 useragent:Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)
  65794 useragent:Hatena::Russia::Crawler/0.01
  58274 useragent:Ruby
  31493 useragent:Mozilla/5.0 (iPhone; CPU iPhone OS 15_4_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.4 Mobile/15E148 Safari/604.1
  29454 useragent:Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/)
  21492 useragent:Tiny Tiny RSS/21.11-7cfc30a (https://tt-rss.org/)
  20351 useragent:Slackbot 1.0 (+https://api.slack.com/robots)
  18765 useragent:Mozilla/5.0 (compatible; SemrushBot/7~bl; +http://www.semrush.com/bot.html)
  18395 useragent:Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/)
  17942 useragent:Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)

zcat -f log/access.log* | grep 'useragent:Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)' | cut -f5 | sort | uniq -c | sort -nr | head -25
    571 request_uri:/robots.txt
    352 request_uri:/
    205 request_uri:/category/misc
    160 request_uri:/2007/01/13/732
    156 request_uri:/2005/10/28/129
    150 request_uri:/2009/03/23/1010
    148 request_uri:/category/music
    146 request_uri:/archives
    144 request_uri:/category/www
    143 request_uri:/2016/07/
    143 request_uri:/2009/08/31/1074
    139 request_uri:/2010/07/05/1140
    138 request_uri:/category/photo
    138 request_uri:/2009/02/
    137 request_uri:/2006/09/09/658
    136 request_uri:/tags/netatmo
    136 request_uri:/2010/07/17/1145
    136 request_uri:/2006/07/23/611
    135 request_uri:/2014/03/
    134 request_uri:/?page=32
    134 request_uri:/2007/02/09/747
    133 request_uri:/category/shopping
    133 request_uri:/2021/07/26/how-to-get-to-kamikochi-from-fukuoka
    133 request_uri:/2011/11/03/finally-got-hhkpro2
    132 request_uri:/2006/01/

+----------+        +------------+        +-----------+
|          |        |            |        |           |
|   Blog   +---+--->+  Category  +------->+   Entry   |
|          |   |    |            |        |           |
+----------+   |    +------------+        +-----------+
               |
               |
               |    +------------+        +-----------+
               |    |            |        |           |
               +--->+  Category  +---+--->+   Entry   |
                    |            |   |    |           |
                    +------------+   |    +-----------+
                                     |
                                     |
                                     |    +-----------+
                                     |    |           |
                                     +--->+   Entry   |
                                     |    |           |
                                     |    +-----------+
                                     |
                                     |
                                     |    +-----------+
                                     |    |           |
                                     +--->+   Entry   |
                                          |           |
                                          +-----------+

select entries.id
from entries
inner join (
  select
    category_id,
    GROUP_CONCAT(id order by created_at desc) as entry_ids,
    max(created_at) as last_created_at
  from entries
  where entries.draft = false
  group by category_id
) as grouped_entries
on grouped_entries.category_id = entries.category_id
and FIND_IN_SET(id, entry_ids) between 1 and 4
order by last_created_at desc, entries.id desc;

query = <<~SQL
  select entries.id
  from entries
  inner join (
    select
      category_id,
      group_concat(id order by created_at desc) as entry_ids,
      max(created_at) as last_created_at
    from entries
    where entries.draft = false
    group by category_id
  ) as grouped_entries
    on grouped_entries.category_id = entries.category_id
    and find_in_set(id, entry_ids) between 1 and 4
  inner join categories on categories.id = entries.category_id
  order by last_created_at desc, entries.id desc;
SQL
entry_ids = ActiveRecord::Base.connection.select_all(query).rows.flatten
entries = Entry.includes(:category, :user, :tags, :comments).where(id: entry_ids)

- "雑談":303
- "技術/プログラミング":272
- "映画/ドラマ/テレビ":150
- "Mac/iPhone":134
- "WWW":113
- "散財":95
- "旅行/ハイキング":70
- "ブログ":69
- "音楽":63
- "読書":34
- "写真":32
- "料理/食事":31
- "労働":27

- 雑記
- パソコン・インターネット
- 見た・読んだ・聞いた
- 出かけた・撮った・食べた

require 'yaml'
require 'pathname'

files = Dir.glob(File.join(__dir__, '_posts', '*.markdown'))
files.each do |file|
  content = File.read(file)
  _, header, body = content.split('---')
  header_yml = YAML.load(header)
  title = header_yml['title']
  tags = header_yml['category']
  tags = tags.is_a?(Array) ? tags.map(&:downcase) : [tags&.downcase]
  slug = Pathname.new(file).basename.to_s.sub(/\d{4}\-\d{2}\-\d{2}\-(.+?)\.markdown/, '\1').gsub('_', '-')
  body = body.strip.gsub(/\n/, "\\n").gsub('\'', '\'\'')
  created_at = File.birthtime(file).to_s.sub(' +0900', '')
  updated_at = File.mtime(file).to_s.sub(' +0900', '')
  puts <<~EOS
    INSERT INTO entries(title, user_id, category_id, slug, markup, type, draft, body, frozen_tag_list, created_at, updated_at) VALUES('#{title}', 1, 6, '#{slug}', 'redcarpet', 'Post', 0, '#{body}', '#{tags.join(',')}', '#{created_at}', '#{updated_at}');
  EOS
end

  // クリックされたアイテムが `this.state.disabled` 配列の中にすでに存在していれば除外し、
  // 存在してなければ追加する
  selectBar(event) {
    let dataKey = event.dataKey.trim()
    if (this.state.disabled.includes(dataKey)) {
      this.setState({ disabled: this.state.disabled.filter(item => item !== dataKey) })
    } else {
      this.setState({ disabled: this.state.disabled.concat([dataKey]) })
    }
  }

  render() {
    return (
      <ResponsiveContainer height={500}>
        <BarChart
          data={this.state.data}
          margin={{
            top: 20, right: 20, left: 0, bottom: 20,
          }}
          style={{ fontSize: '14px' }}
        >
          <CartesianGrid strokeDasharray="3 3" />
          <XAxis dataKey="year" />
          <YAxis />
          <Tooltip labelStyle={{ color: '#000', fontWeight: 'bold' }} itemStyle={{ margin: '0 2px 0 4px', padding: '0' }} />
          {// Legend クリック時のコールバックに `this.selectBar` を指定する }
          <Legend onClick={this.selectBar} />
          {/*
            `this.state.categories` 配列と `this.state.disabled` 配列の内容を比較し、
            `this.state.disabled` に追加済のカテゴリーは dataKey に空白を追加することで非表示に
          */}
          {this.state.categories.map((category, index) => {
            let dataKey = this.state.disabled.includes(category) ? category + " " : category
            let color = this.colors[index % this.colors.length]
            return(<Bar key={index} dataKey={dataKey} stackId="a" fill={color} />)
          })}
        </BarChart>
      </ResponsiveContainer>
    );
  }