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: The key you set in $API_KEYS in my_database_api.php on your server, at least 16 characters. Needed only for pictures; see Server Setup for Pictures below.
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 Setup for Pictures (my_database_api.php)

What it is

Pictures on a MySQL database cannot live in MySQL, so they are kept as files on your web server, in a folder called my_database_images. The app reaches them through one PHP file, my_database_api.php. Rows and tables work without it; only pictures need it.

My Database and My Database Pro use the same file, so a server set up for one works with the other.

1. Download and upload the file

• Download my_database_api.php (or as a .zip). It is also in the GitHub repository.
• Upload it to your web server's public folder (for example public_html/, htdocs/ or /var/www/html/).
• The server needs PHP 7.1 or newer. Apache, LiteSpeed and nginx all work.

2. The pictures folder

• The file keeps pictures in my_database_images/ next to itself, as {database}/{table}/{record id}/.
• It creates the folder on the first upload if PHP may write to the web folder. If it may not, create my_database_images yourself and make it writable by PHP (on most shared hosting it already is; otherwise chmod 775).

3. Set your API key

The file ships with no key and refuses every request until you add one. Open it and find:

$API_KEYS = [
    // 'put-a-long-random-string-here' => 'admin',
];

Add a line with your own key and a role:

$API_KEYS = [
    'c3f9a1e07b5d42e8a6f0b91d7c2e4a58f1b6d9e3' => 'admin',
];

• A key must be at least 16 characters; a shorter one is refused. Use a long random string, not a word. php -r "echo bin2hex(random_bytes(24));" makes one, or use a password manager.
• Type the same key into the app, in the MySQL connection's Server API Key field.
• You can add more than one key, one per person or phone, each with its own role:
  • admin: everything.
  • uploader: see, add and reorder pictures, and delete only the pictures it added itself.
  • viewer: see pictures only.

4. Use HTTPS

• Give the server a certificate (Let's Encrypt is free). The key travels with every request, and over plain HTTP anyone on the way can read it.
• A certificate is issued for a name, not an IP address, so reach the server by its name.

5. Test it

The app sends the key in an X-Api-Key header, never in the address, so a browser cannot test the file. From a computer:

curl -H "X-Api-Key: YOUR_KEY" "https://yourserver.com/my_database_api.php?action=list_images&db=test&table=test&post=1"

• The answer should be {"success":true,"message":"","data":{"images":[]}}.
• Invalid or missing API key means the key is missing from the file, shorter than 16 characters, or not the same as the one you sent.
• Then, in the app, add a picture to a record on that connection.

What the file protects on its own

• It accepts only picture files (JPEG, PNG, WebP, GIF, BMP, HEIC), checked by name and by content.
• It writes an .htaccess into each table's folder so nothing there can run as a script, and keeps its own bookkeeping files from being downloaded. (nginx ignores .htaccess; the checks on every upload still apply.)
• Anything that changes the server has to be a POST, so a link opened by a crawler or a link preview cannot delete pictures.

Upgrading from the older file

If your server runs the older my_database_api.php (the one with $API_KEY = '8888';):

• Replace it with the new file and set a key in $API_KEYS as in step 3, at least 16 characters. 8888 no longer works.
• Put the same key into each MySQL connection in the app.
• Pictures already on the server stay where they are. Nothing needs moving.
• The new file needs My Database 1.2.8 or newer, or any version of My Database Pro. Version 1.2.7 and older send the key in the address, which the new file does not accept, so they show no pictures until the app is updated.