How to merge/combine habits from backups

[tapir-whorf]

I've been using Loop since 2014 or so, regularly rearranging my habits and experimenting with different setups (or varied names for the same old habit). As a result, I have years of backups with identical or similar habits that I'd like to merge. What's the best way to do this?

In some cases, the habits I'd like to merge have identical names but different date ranges. In other cases, the relationship between two tracked habits isn't obvious based on names or date ranges. I'd love to be able to merge these overlapping habits within Loop (since I've never worked with SQLite), but it doesn't seem to be possible. Is it?

As I type this up, I realize that I'm probably asking an SQLite question in disguise as a feature request, not even a Loop question, but I'll try my luck anyhow! (That said, if it's an SQLite question, I'd appreciate a nudge in the right direction, or knowing how someone else might solve this [relatively simple for someone-who-is-not-me!] problem.)

[KristianTashkov]

Generally speaking the app saves the data in two big database tables:

1. Habits: Which stores all the settings for each habit.
2. Repetitions: which stores the value of one habit on a given day.

You need to open the database file (using a programming language or via queries interface) and redirect some of the repetitions to the habit you want to keep using.

An example in python:

import sqlite3
connection = sqlite3.connect('/Users/kris/Desktop/Loop Habits Backup 2021-08-06 145151.db')
cursor = connection.cursor()

for habit in cursor.execute('SELECT `id`, `name` from Habits'):
    print(habit)

This will print all your habits and their IDs. Then you can tell the repetitions table to move the IDs of one habit to another. For example 17 -> 18:

cursor.execute('UPDATE `repetitions` SET habit=18 WHERE habit = 17')

Note that this will only work if their date ranges are different, because you can't have two entries with the same timestamp to the same habit. Otherwise you need to write more complex code to decide which one you want to be taken.

When you are down with your changes to save:

connection.commit()
connection.close()

Hopefully this gives you an idea what you need to do, reading some SQLite syntax you can probably do the merging of different date ranges of the same habit pretty easily. For more complex use cases you will need to write some code to make the decision which one to take :)