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 into the documents directory. The Databases list screen then appears.
• Access Settings: Tap the gear icon (⚙️) in the top-right of the Databases screen. Tap "Close" to save and exit. Settings persist across sessions.

Managing Databases

The Database list screen lists all databases. Tap a database to open its Tables screen (prompts for password if encrypted).

Creating a Database

1. Tap the "+" button at the top.
2. Select "Create Database".
3. Enter a name (e.g., "MyNotes") in the dialog and tap "Create".

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.

SSH Terminal Usage

The SSH Terminal allows you to connect to remote servers (e.g., Linux servers) for command-line access. It's useful for managing servers, running scripts, or troubleshooting.

Adding and Managing SSH Connections

1. Open the app and navigate to the "SSH Connections" menu (accessible from the main screen or database list).
2. Tap the "+" icon in the app bar to add a new connection.
3. Enter:
   • Connection Name: A friendly name (e.g., "My Server").
   • Host IP: The server's IP address or domain (e.g., "192.168.1.100" or "example.com").
   • Port: Default is 22 (standard SSH port).
   • Username: Your server username (e.g., "root" or "user").
   • Password: Your server password (stored securely in the app).
4. Tap "Add" to save. If there's an error (e.g., duplicate name or invalid port), it will be shown.
5. Connections are listed and can be reordered by dragging (long-press and move).
6. To edit: Tap the "more" icon (three dots) next to a connection and select "Edit".
7. To delete: Select "Delete" from the same menu (confirmation required).

Connecting to a Server

1. Tap a connection from the list to open the terminal.
2. The app will attempt to connect automatically using the provided credentials.
3. Once connected, you'll see a terminal interface (powered by xterm.dart).
4. Type commands as usual. Special keys:
   • Use the on-screen buttons for CTRL, ALT, TAB, ESC, arrows, ENTER, etc.
   • CTRL/ALT modifiers: Toggle them on/off; they apply to the next key press.
5. The terminal supports resizing and basic input/output.
6. Connection status is shown (e.g., "Connecting..." or "Connected successfully").
7. To disconnect: Close the screen or let the session end naturally.

Important Notes for SSH

• Battery Optimization: The app reminds you to disable battery optimization for the app (via a Snackbar). This keeps the connection alive in the background. Tap the message to open Android settings and disable it. Otherwise, Android may kill the app, disconnecting SSH.
• Security: Passwords are stored locally (via shared_preferences). Use strong passwords and avoid saving sensitive ones.
• Limitations: No key-based authentication (password-only). For production, ensure your server allows password logins securely.
• Troubleshooting: If connection fails, check host/port/username/password. Errors are displayed in the terminal (e.g., "Error: Connection refused").

MySQL Database Usage

MySQL support lets you connect to remote MySQL databases, browse tables, query data, add/edit/delete posts, and handle images (uploaded to the server).

Adding and Managing MySQL Connections

1. From the main "Databases" screen, tap the "+" icon and select "Add MySQL Connection" (or similar UI element).
2. Enter:
   • Connection Name: A display name (e.g., "My Remote DB").
   • Host: Server hostname/IP (e.g., "mysql.example.com").
   • Port: Default 3306.
   • Username: MySQL user (e.g., "dbuser").
   • Password: MySQL password.
   • Database Name: The specific database to connect to (e.g., "mydatabase").
   • Use SSL: Toggle for secure connections (recommended).
   • CA Certificate, Client Certificate, Client Key: Optional PEM-encoded certs for SSL (paste or load from files).
   • Server API Key: Must match the key in my_database_api.php on your server (default '8888'—change it!).
3. Tap "Add". Connections appear as ".mysql" entries in the database list.
4. Edit/Delete: Similar to SSH—use the "more" menu.
5. Connections are reordered by dragging.

Working with MySQL Databases

• Tap a MySQL database from the list to open its tables.
• Features mirror SQLite: Browse tables, add/rename/duplicate/delete tables, run SQL queries, manage posts (add/edit/delete), search/filter/sort.
• Images: Posts can have images uploaded to the server (via the API). View, add, reorder, or delete images per post.
  • When adding images: Select from gallery/camera; thumbnails are auto-generated.
  • Images are stored on the server in /my_database_images/{database}/{table}/{post_id}/ (e.g., "001_image.jpg" and "001_image_thumb.jpg").
• SQL Queries: Use the SQL screen to run queries (SELECT, INSERT, etc.). Paginated results for large datasets.
• Default Database/Table: Set a MySQL DB/table as default for quick access.

Battery Optimization for MySQL

Similar to SSH: Disable battery optimization to prevent disconnections in the background (e.g., during long queries or uploads).

Server-Side Installation for MySQL (Required only for images)

To enable full MySQL functionality (especially images), set up a PHP script on your server. This API handles image uploads/lists/deletes/reorders, as the app can't directly access server files.

Steps to Install

1. Upload the PHP Script

• Download the PHP script as .php or .zip. You can also find it at the My Database GitHub repository.
• Upload it to your web server (e.g., via FTP/SSH to /var/www/html/ or a subdomain).
• Ensure the server supports PHP (e.g., Apache/Nginx with PHP 7+).
• The script's URL becomes your "API Base URL" (e.g., "https://example.com/my_database_api.php").

2. Create Images Folder

• Next to my_database_api.php, create a folder named my_database_images.
• Set permissions: Make it writable by PHP (e.g., chmod 755 my_database_images or via hosting panel).
• This folder stores uploaded images hierarchically: {database}/{table}/{post_id}/{images}.

3. Change the API Key

• In my_database_api.php, find $API_KEY = '8888';.
• Change '8888' to a strong, random string (e.g., generate via password tool: 'xY9zA8bC7dE6fG5hJ4kL3mN2oP1qR0sT').
• This key must match what you enter in the app's MySQL connection settings.
• Security Warning: Use a very strong key! Without it, anyone could access/upload/delete images.

4. SSL Certificate

• Use HTTPS (enable SSL on your server).
• If using a self-signed or unrecognized certificate, images may not load (Android blocks insecure connections). Get a free Let's Encrypt cert or a paid one.

5. Test the API

• Access https://yourserver.com/my_database_api.php?action=list_images&db=test&table=test&post=1&key=YOUR_KEY.
• It should return JSON like {"success":true,"message":"","data":{"images":[]}}.
• In the app, when adding a MySQL connection, enter the API key and test by uploading an image to a post.