121 lines
4.5 KiB
Python
121 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Script to add modules column to external database and migrate existing users
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
import mariadb
|
|
|
|
def migrate_external_database():
|
|
"""Add modules column to external database and update existing users"""
|
|
try:
|
|
# Read external database configuration from instance folder
|
|
config_file = os.path.join(os.path.dirname(__file__), 'instance/external_server.conf')
|
|
if not os.path.exists(config_file):
|
|
print("External database configuration file not found at instance/external_server.conf")
|
|
return False
|
|
|
|
with open(config_file, 'r') as f:
|
|
lines = f.read().strip().split('\n')
|
|
|
|
# Parse the config file format "key=value"
|
|
config = {}
|
|
for line in lines:
|
|
if '=' in line and not line.strip().startswith('#'):
|
|
key, value = line.split('=', 1)
|
|
config[key.strip()] = value.strip()
|
|
|
|
host = config.get('server_domain', 'localhost')
|
|
port = int(config.get('port', '3306'))
|
|
database = config.get('database_name', '')
|
|
user = config.get('username', '')
|
|
password = config.get('password', '')
|
|
|
|
if not all([host, database, user, password]):
|
|
print("Missing required database configuration values.")
|
|
return False
|
|
|
|
print(f"Connecting to external database: {host}:{port}/{database}")
|
|
|
|
# Connect to external database
|
|
conn = mariadb.connect(
|
|
user=user,
|
|
password=password,
|
|
host=host,
|
|
port=port,
|
|
database=database
|
|
)
|
|
cursor = conn.cursor()
|
|
|
|
# Check if users table exists
|
|
cursor.execute("SHOW TABLES LIKE 'users'")
|
|
if not cursor.fetchone():
|
|
print("Users table not found in external database.")
|
|
conn.close()
|
|
return False
|
|
|
|
# Check if modules column already exists
|
|
cursor.execute("DESCRIBE users")
|
|
columns = [row[0] for row in cursor.fetchall()]
|
|
|
|
if 'modules' not in columns:
|
|
print("Adding modules column to users table...")
|
|
cursor.execute("ALTER TABLE users ADD COLUMN modules TEXT")
|
|
print("Modules column added successfully.")
|
|
else:
|
|
print("Modules column already exists.")
|
|
|
|
# Get current users and convert their roles
|
|
cursor.execute("SELECT id, username, role FROM users")
|
|
users = cursor.fetchall()
|
|
|
|
role_mapping = {
|
|
'superadmin': ('superadmin', None),
|
|
'administrator': ('admin', None),
|
|
'admin': ('admin', None),
|
|
'quality': ('manager', '["quality"]'),
|
|
'warehouse': ('manager', '["warehouse"]'),
|
|
'warehouse_manager': ('manager', '["warehouse"]'),
|
|
'scan': ('worker', '["quality"]'),
|
|
'etichete': ('manager', '["labels"]'),
|
|
'quality_manager': ('manager', '["quality"]'),
|
|
'quality_worker': ('worker', '["quality"]'),
|
|
}
|
|
|
|
print(f"Migrating {len(users)} users...")
|
|
|
|
for user_id, username, old_role in users:
|
|
if old_role in role_mapping:
|
|
new_role, modules_json = role_mapping[old_role]
|
|
|
|
cursor.execute("UPDATE users SET role = ?, modules = ? WHERE id = ?",
|
|
(new_role, modules_json, user_id))
|
|
|
|
print(f" {username}: {old_role} -> {new_role} with modules {modules_json}")
|
|
else:
|
|
print(f" {username}: Unknown role '{old_role}', keeping as-is")
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
print("External database migration completed successfully!")
|
|
return True
|
|
|
|
except Exception as e:
|
|
print(f"Error migrating external database: {e}")
|
|
return False
|
|
|
|
if __name__ == "__main__":
|
|
print("External Database Migration for Simplified 4-Tier Permission System")
|
|
print("=" * 70)
|
|
|
|
success = migrate_external_database()
|
|
|
|
if success:
|
|
print("\n✅ Migration completed successfully!")
|
|
print("\nUsers can now log in with the new simplified permission system.")
|
|
print("Role structure: superadmin → admin → manager → worker")
|
|
print("Modules: quality, warehouse, labels")
|
|
else:
|
|
print("\n❌ Migration failed. Please check the error messages above.") |