How do I merge those excessive API-created note types into one?

AnkiDroid (Android) Help
1

notetypes-or8
notetypes-or8486×962 44.5 KB

All those types are actually the same (single card, front → front+back), but my csv-to-AnkiDroid improter (that I kept using even though AnkiDroid can probably import csv directly now) has been creating a new note type each time it imported something.

How do I collapse them all into a single note type? I see some mentions about “merging note types”, but that typically only about importing / syncing, not about already existing decks. Which of the ways would be the most straightforward:

  • Is it possible within AnkiDroid UI? Maybe I am missing something like select range of note types and press merge menu item somewhere?
  • Is there maybe a plugin for this?
  • Shall I just manually edit the sqlite database (to force all those notes to refer to some one note type), then let AnkiDroid clear out unused note types (if that is supported, or just use database editing again)?
  • Shall I use AnkiDroid API again to make a “fixer” app that will remap the notes to one canonical note type and clear out the fluff?
  • Shall I involve AnkiDesktop somehow?

Though those extraneous note types do not seem to impair user experience much, it feels like a tech debt that may become problematic someday.

2

One by one – and on the desktop app, because you’ll need “Change Note Type.”

3

One by one is not practical for the hundred of note types.

I looked into the database and the structure seems to be relatively straightforward.

Should reassigning mid field in notes table to point to some one canonical entry of notetypes, then removing rows for abandoned note types do the trick? Or should something else (i.e. caches or whatever) also be updated after this?

Should passing “Check database” and seemingly normal operation of AnkiDroid after database editing indicate that everything is OK; or there may be problems down the line (i.e. when new notes get added)?

4

I’d never endorse anyone hand-editing their database unless they already know what they are doing (and then they wouldn’t be asking me, for sure! :sweat_smile: ). We’ll have to see if anyone else is braver on that front…

5

Can you export everything into a text file and then move them back into a single note type?

6

Maybe; exported file contains notetype names that seems to be easy to find-and-replace to one where needed. I haven’t checked yet what would happen if I reimport it.

Trying the database way.

$ cd /sdcard/AnkiDroid
$ mkdir dbbackup
$ cp collection.anki2* dbbackup/
$ sqlite3 collection.anki2
sqlite> select id,name,mtime_secs from notetypes where name like '%plaintextimporter%' and name not like '%plaintextimporter_%' ;

1697472596652|com.ichi2.plaintextimporter|1697472596

A good candidate for the canonical notetype of this likeness.

sqlite> select count(*) from notetypes where name like '%plaintextimporter%' and id <> 1697472596652 ;
119

Number of notetypes to be remapped and deleted.

sqlite> select nt.id,count(*) n_notes from notetypes nt left join notes n on n.mid = nt.id where nt.name like '%plaintextimporter%' and nt.id <> 1697472596652 group by nt.id limit 5;

1451180286384|1932
1532351817741|35
1532352268554|1
1536153869241|53
1537957798184|30

Number of notes on some of the notetypes we marked for deletion.

sqlite> with target_note_ids as (select distinct n.id from  notetypes nt left join notes n on n.mid = nt.id where nt.name like '%plaintextimporter%' and nt.id <> 1697472596652) select count(*) from target_note_ids;

7302

Number of notes we should remap.

sqlite> with target_note_ids as (select distinct n.id from  notetypes nt left join notes n on n.mid = nt.id where nt.name like '%plaintextimporter%' and nt.id <> 1697472596652) update notes set mid=1697472596652 where id in (select id from target_note_ids);

sqlite> select changes();
7301

One row not updated for some reason, not sure why. Let’s ignore it.

sqlite> with target_note_ids as (select distinct n.id from  notetypes nt left join notes n on n.mid = nt.id where nt.name like '%plaintextimporter%' and nt.id <> 1697472596652) select count(*) from target_note_ids;

1

Indeed only one note unremapped. After some minor debugging, looks like it’s just NULL crept in due to slightly inaccurate query (left join instead of join for target_note_ids).

sqlite> with target_note_ids as (select distinct n.id from  notetypes nt join notes n on n.mid = nt.id where nt.name like '%plaintextimporter%' and nt.id <> 1697472596652) select count(*) from target_note_ids;

0

Now better.

Let’s remove unreferenced notetypes. First let’s count them.

sqlite> with used_notetype_ids as (select distinct mid from notes) select count(*) from used_notetype_ids;
21
sqlite> select count(*) from notetypes;
151
sqlite> select count(*) from notetypes where id not in (select distinct mid from notes);
130

It’s more than before because of there are other, unrelated abandoned notetypes.

sqlite> delete from  notetypes where id not in (select distinct mid from notes);
Parse error: no such collation sequence: unicase
sqlite> .quit

Looks like Anki uses special things in SQLite. Published an sqlite3-loadable extension to handle this.

sqlite> .open /sdcard/AnkiDroid/collection.anki2
sqlite> .load ./libsqlite_unicase
sqlite> delete from  notetypes where id not in (select distinct mid from notes);
sqlite> select changes();
130
sqlite> .quit

After loading AnkiDroid, everything seems to be OK. Database check did not found any error, now it shows “21 note types available”, with 7325 notes in com.ichi2.plaintextimporter. Studing progress, starred notes, etc. are all preserved.

1 Like
7

You can select the note type when importing text files so no need for find and replace.