Fix your Anki streak - the script edition

anki

Like many Anki users, I keep track of my streaks because it motivates me to do my reviews each day. But since life gets in the way sometimes, I may miss my reviews in one or more decks. It has been years since I’ve neglected to do all of my reviews; but sometimes I will forget to come back later in the day to finish up some of my decks. Since I like to have a clean review heatmap, I will “fix” my streak in a skipped deck.

Yes, this is “cheating”; but applied rarely, I gives me no moral qualms. YMMV.

I’ve described a manual process previously in which we execute queries directly against the Anki sqlite3 database. It works, but you have to deal with “bare metal” interaction with the database. There’s some risk involved. To make the process a little easier I’ve developed the following script. It just automates the review date correction so that you don’t have to interact directly with the database. I’ll walk you through the process; which does require a little technical facility, but only a little.

WARNING
Backing up your collection before running this script is strongly recommended.

Prerequisites and installation

N.B.: I work most of the time on macOS and have almost no experience on the Windows ecosystem. I’m sure this could be adapted to work on Windows; but that’s for someone else to do.

  1. Ruby is installed by default on macOS; so you should be good there. If you want to be sure, you can check by going to the Terminal and typing which ruby. You should get something like:
➜  ~ which ruby
/Users/alan/.rbenv/shims/ruby
➜  ~
  1. You will need to install a couple Ruby gems.
gem install sqlite3
gem install tzinfo
  1. Copy the Ruby script (see below for the entire listing)
  2. Install the script
cd ~/Documents  # or wherever you want to put the script
pbpaste > anki_streak_fix.rb
  1. Your collection name is not going to be “Alan - Russian” so you can use any text editor (e.g. TextEdit) to change that in the code.

At this point should have everything you need installed on the system.

Usage

  1. Open Anki and do a couple review in a deck where you missed your streak yesterday.
  2. Now quit Anki.
  3. Run the script from the Terminal:

cd ~/Documents # or wherever to saved the script ruby anki_streak_fix.rb “your_deck_name” –simulate

This should show you which cards will be moved to yesterday. If you’re satisfied with how that looks, then run the script without the --simulate flag.

Source code for the script

#!/usr/bin/env ruby

require 'sqlite3'
require 'optparse'
require 'time'
require 'tzinfo'

def get_system_timezone
  begin
    TZInfo::Timezone.get(Time.now.zone)
  rescue TZInfo::InvalidTimezoneIdentifier
    puts "Unknown system time, default to America/Toronto"
    TZInfo::Timezone.get('America/Toronto')
  end
end

class AnkiCollection
  def initialize(collection_name)
    base_path = "~/Library/Application Support/Anki2/"
    @path = base_path + collection_name + "/collection.anki2"
  end
  
  def collection_path
    File.expand_path(@path)
  end
end

class AnkiProcessor
  def initialize(deck_name, simulate: false)
    @deck_name = deck_name
    @simulate = simulate
    @db_path = AnkiCollection.new("Alan - Russian").collection_path
  end
  
  def process
    rid_string = generate_rid_string
    note_ids = fetch_reviewed_notes
    
    if note_ids.empty?
      puts "No notes found for today in deck '#{@deck_name}'"
      return
    end
    
    process_notes(note_ids, rid_string)
  end
  
  private
  
  def generate_rid_string
    system_timezone = get_system_timezone
    puts "Using timezone: #{system_timezone.identifier}"
    
    today = Time.now
    local_midnight = system_timezone.local_to_utc(Time.new(today.year, today.month, today.day))
    start_time = local_midnight.to_i * 1000
    end_time = (local_midnight + 86400).to_i * 1000
    
    "rid:#{start_time}:#{end_time}"
  end
  
  def fetch_reviewed_notes
    query = <<-SQL
      SELECT DISTINCT notes.id
      FROM cards
      JOIN notes ON cards.nid = notes.id
      JOIN decks ON cards.did = decks.id
      JOIN revlog ON cards.id = revlog.cid
      WHERE decks.name COLLATE NOCASE = ?
      AND date(revlog.id/1000, 'unixepoch', 'localtime') = date('now', 'localtime')
      ORDER BY notes.id;
    SQL
    
    begin
      db = SQLite3::Database.new(@db_path)
      db.results_as_hash = true
      db.execute(query, @deck_name)
    rescue SQLite3::Exception => e
      puts "Database error: #{e.message}"
      []
    ensure
      db&.close
    end
  end
  
  def process_notes(notes, rid_string)
    # Extract start and end times from rid_string
    start_time = rid_string.split(':')[1]
    end_time = rid_string.split(':')[2]
    
    begin
      db = SQLite3::Database.new(@db_path)
      
      notes.each do |row|
        note_id = row['id']
        
        if @simulate
          puts "Would execute: UPDATE revlog for note #{note_id} (#{start_time} to #{end_time})"
        else
          update_query = <<-SQL
            UPDATE revlog
            SET id = id - 86400000
            WHERE id IN (
              SELECT r.id
              FROM revlog r 
              INNER JOIN cards c ON r.cid = c.id
              INNER JOIN notes n ON n.id = c.nid
              WHERE n.id = ?
                AND r.id >= ?
                AND r.id < ?
            );
          SQL
          
          db.execute(update_query, [note_id, start_time, end_time])
          puts "Note date updated successfully for #{note_id}"
        end
      end
    rescue SQLite3::Exception => e
      puts "Database error: #{e.message}"
    ensure
      db&.close
    end
  end
end

# Parse command line arguments
options = {simulate: false}
parser = OptionParser.new do |opts|
  opts.banner = "Usage: #{$0} [options] DECK_NAME"
  opts.on('-s', '--simulate', 'Simulate ankifix calls (print only)') do |s|
    options[:simulate] = s
  end
end

parser.parse!

if ARGV.empty?
  puts parser.help
  exit 1
end

# Run the processor
processor = AnkiProcessor.new(ARGV[0], simulate: options[:simulate])
processor.process

If you have any difficulties or you have ideas for improvements, I can try to help. See my contact page.