From 3850e2f070bb3c1227b78dcd1e8a34a89a9e9906 Mon Sep 17 00:00:00 2001
From: Ali Golzar <57574919+aliglzr@users.noreply.github.com>
Date: Wed, 21 May 2025 13:34:38 +0330
Subject: feat: Add MySQL database support (#3024)

* feat: Add MySQL database support

- Add MySQL database support with environment-based configuration
- Fix MySQL compatibility issue with 'key' column name
- Maintain SQLite as default database
- Add proper validation for MySQL configuration
- Test and verify compatibility with existing database
- Replaced raw SQL queries using JSON_EACH functions with standard GORM queries
- Modified functions to handle JSON parsing in Go code instead of database since JSON_EACH is not available on MySQL or MariaDB:
  - getAllEmails()
  - GetClientTrafficByID()
  - getFallbackMaster()
  - MigrationRemoveOrphanedTraffics()

The system now supports both MySQL and SQLite databases, with SQLite remaining as the default option. MySQL connection is only used when explicitly configured through environment variables.

* refactor: prefix env variables of database with XUI_ to support direct environment usage without .env file

All database configuration environment variables now start with the XUI_ prefix to avoid conflicts and allow configuration via system-level environment variables, not just the .env file.
---
 .env.example | 8 ++++++++
 1 file changed, 8 insertions(+)
 create mode 100644 .env.example

(limited to '.env.example')

diff --git a/.env.example b/.env.example
new file mode 100644
index 00000000..228fde76
--- /dev/null
+++ b/.env.example
@@ -0,0 +1,8 @@
+XUI_DB_CONNECTION=sqlite
+
+# If DB connection is "mysql"
+# XUI_DB_HOST=127.0.0.1
+# XUI_DB_PORT=3306
+# XUI_DB_DATABASE=xui
+# XUI_DB_USERNAME=root
+# XUI_DB_PASSWORD=
\ No newline at end of file
-- 
cgit v1.2.3