My Database

Tutorial

This How-To provides a step-by-step guide to using all features of My Database on your smartphone. The app opens on the Databases screen by default (or loads the default database/table if configured). Navigation is intuitive, using buttons, long-press gestures for options, and dialogs for inputs.

Note: The app requests permissions for storage, photos, and camera as needed—grant them for full functionality. If denied, enable them in your phone's settings. For Android 11+, enable "All files access" in the app's settings for imports/exports. Heavy tasks like exports/imports run in background isolates to display progress and prevent UI freezes.

Getting Started and Settings

• Launch the App: On first run, the app imports sample data from embedded ZIP assets (Animals.zip and people_100000.zip) into the documents directory. It creates databases with tables and adds an 'image_paths' column if missing. The Databases list screen appears.
• Access Settings: Tap the gear icon (⚙️) in the top-right of the Databases screen.
  • App Version: Displays the current version and build number (via package_info_plus).
  • Theme: Select "Dark Mode" via toggle. Changes apply instantly and are saved.
  • Image Quality: Toggle "High Image Quality" on/off. High quality uses no compression (larger files); low (85%) compresses images for efficiency during uploads.
  • Thumbnail Scale: Adjust the slider (50%–300%) to change thumbnail sizes in lists and viewers. Higher scales enlarge previews but may slow loading for large datasets.
  • Show All Posts on Open: Toggle to load all posts immediately when opening a table (vs. lazy loading with pagination).
  Tap "Close" to save and exit. Settings persist across sessions.
• Home Navigation: From any screen, tap the home icon (🏠) in the app bar to return to the Databases list.

Managing Databases

The main screen lists all databases (.db files) in alphabetical order (saved via shared_preferences), with icons. Tap a database to open its Tables screen (prompts for password if encrypted).

Creating a Database

1. Tap the floating "+" button at the bottom-right.
2. Select "Create Database".
3. Enter a name (e.g., "MyNotes") in the dialog and tap "Create". This creates an empty .db file.

Importing a Database

1. Tap "+" and choose "Import ZIP (Database + Images)", "Import SQLite DB", or "Import from CSV".
2. Grant storage permissions if prompted.
   • ZIP Import: Select a .zip file; it extracts the .db and images to the app's documents directory, with a progress dialog.
   • SQLite Import: Select a .db/.sqlite/.sqlite3 file; copies it and adds 'image_paths' if missing.
   • CSV Import: Select a .csv file; creates a new database/table with columns from headers (sanitized names), imports rows in batches with a progress dialog.
3. Imports process in a background isolate (showing % progress) and refresh the list.

Database Options (Long-Press a Database Name)

• Set as Default: Configures it (and its default table) to open automatically on app launch.
• Export: Choose "Save to Device" (select folder) or "Share" (via share_plus). Exports as ZIP (database + all referenced images, collected via batch queries), with a progress dialog in an isolate. For encrypted DBs, prompts for password.
• Rename: Edit the name in the text field and tap "Rename" (updates file and preferences).
• Add Password: Enter a new password to encrypt (uses sqlcipher_export in a temp DB).
• Remove Password: Enter the current password to decrypt (similar process).
• Delete: Confirm to permanently delete the .db file (and unreferenced images).
• Reorder: Use up/down arrows to move in the list (saves order).
• Tap "Cancel" to exit options.

Managing Tables (Inside a Database)

From the Databases screen, tap a database to view its tables list (queried via sqlite_master, excluding system tables).

Creating a Table

1. Tap the floating "+" button.
2. Enter a table name (e.g., "Entries", sanitized to letters/numbers/underscores) and optional columns (comma-separated, e.g., "title,description"). The 'image_paths' (TEXT) column is auto-added for image support.
3. Tap "Create".

Table Options (Long-Press a Table Name)

• Set as Default: Pairs with the default database for auto-open on launch.
• Rename: Edit the name (sanitized) and tap "Rename".
• Edit Columns: Opens a dialog to manage non-'image_paths' columns (via PRAGMA table_info).
  • Add: Tap "Add New Column", enter name (sanitized), tap "Add".
  • Move: Use up/down arrows to reorder (recreates table in temp, copies data).
  • Rename: Tap edit icon, enter new name, tap "Rename" (uses ALTER TABLE).
  • Delete: Tap delete icon (red), confirm (recreates table without column, copies data; can't delete PK or 'image_paths').
• Delete: Confirm to drop the table and all data.
• Tap a table name to open its Posts screen (loads all if toggled, else paginates).

Managing Posts (Records in a Table)

The Posts screen displays paginated entries (100 per load, infinite scroll via ScrollController). Each post is a card with fields (via PRAGMA) and thumbnails. Long-press a card to toggle edit/delete buttons. Tap a card to open the detailed view.

Adding a Post

1. Tap the floating "+" button.
2. Fill text fields for each column.
3. If images are supported: Tap "+Gallery" or "+Camera" (grants permissions, rotates via EXIF, compresses via flutter_image_compress based on quality setting, creates thumbnail). Reorder/delete thumbnails in the dialog.
4. Tap "Add" to insert (JSON-encodes image_paths).

Editing a Post

1. Long-press card > "Edit" (or in detailed view).
2. Update fields/images.
3. Tap "Update" (uses rowid or PK for WHERE).

Deleting a Post

1. Long-press card > "Delete" (red) > Confirm.

Adding Images to a Post

• In card or detailed view: Tap "+Gallery" or "+Camera" (appends to existing image_paths JSON).

Viewing Images

• Tap a thumbnail for full-screen viewer (PageView with InteractiveViewer for zoom up to 32x, swipe navigation; disables scroll when zoomed). Tap to exit.

Detailed Post View (Single Post Screen)

• Shows all fields and larger thumbnails. Long-press for edit/delete. Add images or edit/delete as above. Updates refresh the list.

Searching and Filtering Posts

Simple Search

• Enter keywords in the search bar (e.g., "apple pie").
• Tap search icon or enter: Filters across text columns (LIKE %term% or FTS if table has fts_table). Results paginate.
• Clear bar (X) to reset.

Showing All Posts

• Tap "All" button to display everything (ignores search).

Advanced Search (Tune Icon)

• Sort: Choose column and ASC/DESC (applies ORDER BY).
• Filters: Per column, select operator (None, Contains, Equals, >, <, Between) and values (casts to REAL for numbers). Combines with AND (and with simple search if active).
• Tap "Apply" to filter (resets pagination). "Reset" clears dialog.

Exporting Search Results

• Tap download icon (↓) in app bar.
• Select folder to save as CSV (all columns except image_paths, full results not just page).

Count Display

• Shows "Found X entries" (or "Total" if no search) below bar (queried separately, with loading indicator).

Additional Tips

• Pagination: Auto-loads on scroll end; efficient for large sets (e.g., 100k rows).
• Error Handling: Prompts for passwords on open/export; snackbars for errors (e.g., wrong password, invalid CSV). Ignores JSON parse errors in image_paths.
• Performance: Uses batches (1000 rows) for exports; isolates for ZIP/CSV processing. High quality increases sizes; thumbnails always JPEG.
• Back Navigation: Use phone's back button or home icon.
• Troubleshooting: If imports/exports fail, check permissions/file paths. Restart app if stuck. For encrypted DBs, always enter password when prompted.