How to retrieve notes content without knotes?

Question

Now that Knotes from KDE has been killed, buried, wiped from rolling release distros repositories, and also from my computer when trying to reinstall it while ignoring the situation, how can I get my notes data back ?

Answer 1

Knotes uses Akonadi framework to store notes content in a database, and MariaDB is the RDBMS behind the scene by default in most use cases, so this answer deals with it. The method is crude, and the retrieved data has a lot of noise but at least it does not require any additional obscure software and works in the most desperate conditions. Notice that this method cannot extract data from other user’s database because each ones is stored in each user’s home directory.

1. At first, get the MariaDB prompt from the terminal. The password required is most likely the operating system user’s password by default in most distributions. mysql --socket=$XDG_RUNTIME_DIR/akonadi/mysql.socket -u root -p
2. Check if the akonadi database is present with the show databases; command
3. Type use akonadi; to connect to it.
4. You can see the available tables with the show tables; command. The actual content of everything managed by akonadi (notes, calendar, e-mails) is stored in the parttable table. The "headers" of the content entries are stored in the pimitemtable table, and the nature of those entries is listed in the mimetypetable. Based on those informations, we will be able to only select the data of the notes. But before that, we need to check the mimetype of the notes entries with the command select * from mimetypetable; that should display a content similar to this:

MariaDB [akonadi]> select * from mimetypetable;
+----+---------------------------------------------+
| id | name                                        |
+----+---------------------------------------------+
|  2 | application/x-vnd.akonadi.calendar.event    |
| 10 | application/x-vnd.akonadi.calendar.freebusy |
|  9 | application/x-vnd.akonadi.calendar.journal  |
|  3 | application/x-vnd.akonadi.calendar.todo     |
|  7 | application/x-vnd.kde.contactgroup          |
|  6 | inode/directory                             |
|  5 | message/rfc822                              |
|  8 | text/calendar                               |
|  4 | text/directory                              |
|  1 | text/x-vnd.akonadi.note                     | << this is for the notes
+----+---------------------------------------------+
10 rows in set (0,000 sec)

So the correct id number for the note type is 1 and we will use it to filter the notes content.

5. Now the critical part, the following SQL command will directly output the whole content of all notes into a single file, located preferably in /tmp to avoid any denied access errors. Don’t forget to check the mimeTypeId number.

SELECT pimitemtable.id, pimitemtable.mimeTypeId, parttable.data INTO OUTFILE '/tmp/outraw.txt' FROM pimitemtable JOIN parttable ON pimitemtable.id = parttable.pimItemId WHERE pimitemtable.mimeTypeId = 1;

6. Now that we safely extracted notes data (/tmp/outraw.txt), you can exit the MariaDB prompt with ctrl+D, move the file out of the /tmp folder as quickly as possible for confidentiality reasons, and use any text file editor to display the content. It is not pretty looking though. The way to clean it easily is in a different answer for another question.
7. Optional step for non ASCII-only typers: The format of the content seems to be quoted-printable encoded, so the unicode data needs to be restored with the following perl command:

cat outraw.txt | perl -M MIME::QuotedPrint -ne 'print MIME::QuotedPrint::decode($_)'

Just append > outdecoded.txt to this command to store the decoded content in the specified file.

Answer 2

First I want to thank you for sharing your solution. I had no idea about storing notes in the Plasma database. However, those notes that appear there in my case, are not the KNotes notes I had created. So, I leave here as a link for anyone who comes to your post, the way I have found to recover my notes in openSUSE Tumbleweed (although it should be the same in any Linux distribution with Plasma).