Can not find sqlite database

[mitch-n]

This issue respects the following points:

• [X] I have read the FAQ
• [X] I have searched for existing issues
• [X] The issue affects the Notes Android app, neither the Notes server app nor the Nextcloud Android app.

Describe the bug

After fixing some issues with my nextcloud, my notes got out of sync. When I reconnected my nextcloud acct, the local notes disappeared. This is a critical issue, as there was a several month gap of important notes that are gone.

I have read through other issues others have had, I have searched everything I can through an adb shell and I can't find anything remotely related to nextcloud-notes.

Expected behavior

No response

Notes Android version

Latest

Notes server version

Latest

Nextcloud Android version

Latest

Nextcloud version

Latest

Device

Google Pixel 6

Android Version

Latest

App Store

• [ ] Google Play Store
• [X] F-Droid
• [ ] Huawei App Gallery

Stacktrace

N/A

[stefan-niedermann]

The SQLite database file is named OWNCLOUD_NOTES (sic! without extension!) for historic reasons.

[mitch-n]

Thank you for the quick response! I checked for that file name recursively from the root directory and didn't get any hits. Like I said before, I don't have root permissions, and am on grapheneos and can't really get root. Do you know the path to where the sqlite database SHOULD be stored typically?

[webracer999]

I'm sorry, but without rooting you don't have access to the db: https://stackoverflow.com/a/6097293

[mhastu]

So i just moved all my notes to another nextcloud instance, because I lost access to the original instance (and I lost hope that murena will come back up again after months...). I did this by accessing this app's database, and I thought I'd document my journey as it might help others.

First I retrieved the database by USB debugging with adb pull /data/data/it.niedermann.owncloud.notes/databases/OWNCLOUD_NOTES (My /e/OS (Android) phone has a switch in the developer options allowing root access for USB debugging. I'm not sure how it is with other Android variants, but I still think mine isn't "rooted".) However, according to chatGPT there are several options to try, like

• adb backup -noapk it.niedermann.owncloud.notes
• adb shell, run-as it.niedermann.owncloud.notes, cd /data/data/it.niedermann.owncloud.notes/databases/ and cp OWNCLOUD_NOTES /sdcard/ But no guarantees for chatGPT sources ;)

Then I created the note files with the following python script:

import os
import sqlite3
import time
import re
from pathlib import Path
import argparse

def sanitize_filename(filename):
    """Sanitize a string to make it safe for filenames."""
    return re.sub(r'[\\/:*?"<>|]', '_', filename)

def write_notes_to_files(database_path, output_dir):
    """Read notes from SQLite database and write them to files."""
    # Connect to the SQLite database
    conn = sqlite3.connect(database_path)
    cursor = conn.cursor()

    # Query the Note table
    cursor.execute("SELECT title, category, modified, content FROM Note")

    # Iterate through the rows
    for title, category, modified, content in cursor.fetchall():
        # Sanitize the filename
        sanitized_title = sanitize_filename(title) + ".md"

        # Determine the folder and ensure it exists
        category_path = os.path.join(output_dir, sanitize_filename(category)) if category else output_dir
        os.makedirs(category_path, exist_ok=True)

        # Create the full file path
        file_path = os.path.join(category_path, sanitized_title)

        # Write the content to the file
        with open(file_path, 'w', encoding='utf-8') as file:
            file.write(content)

        # Set the modification time
        mod_time = modified / 1000  # Convert milliseconds to seconds
        os.utime(file_path, (mod_time, mod_time))

    # Close the database connection
    conn.close()

def main():
    parser = argparse.ArgumentParser(description="Create note files from nextcloud notes database.")
    parser.add_argument("database_path", help="Path to the SQLite database file.")
    parser.add_argument("output_dir", help="Directory where the files will be created.")

    args = parser.parse_args()

    write_notes_to_files(args.database_path, args.output_dir)

if __name__ == "__main__":
    main()

Just call it with script.py OWNCLOUD_NOTES target_folder.

Then I made sure the files and folders created have the correct rights and ownership for nextcloud (use chmod and chown). I moved the files to the notes folder in my nextcloud, ran occ files:scan <username> and the notes were there with the correct timestamp.

[stefan-niedermann]

@mhastu thank you very much for sharing your knowledge and experiences.

I'll close this issue because I have a feeling that anything related to the DB location has been said.