Spaces:
Running
Running
File size: 4,497 Bytes
2978bba | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | #!/usr/bin/env python3
"""
migrate_users.py
Migrates users from SQLite (users.db) to PostgreSQL (TimescaleDB).
SAFE operation: Reads from SQLite, Inserts into Postgres. Does not delete SQLite data.
"""
import sqlite3
import psycopg2
import os
import sys
import logging
from datetime import datetime
# Set up logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
# Paths
SQLITE_DB_PATH = 'users.db'
# Configuration (mirrors setup_timescaledb.py logic)
def get_postgres_connection():
try:
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
try:
import config
host = getattr(config, 'DB_HOST', 'localhost')
port = getattr(config, 'DB_PORT', 5432)
dbname = getattr(config, 'DB_NAME', 'morphguard')
user = getattr(config, 'DB_USER', 'morphguard')
password = getattr(config, 'DB_PASSWORD', 'morphguard')
except ImportError:
# Fallback to env vars or strict defaults matching deploy scripts
host = os.environ.get('MORPHGUARD_DB_HOST', 'localhost')
port = int(os.environ.get('MORPHGUARD_DB_PORT', 5432))
dbname = os.environ.get('MORPHGUARD_DB_NAME', 'morphguard')
user = os.environ.get('MORPHGUARD_DB_USER', 'morphguard')
password = os.environ.get('MORPHGUARD_DB_PASS', 'morphguard')
logger.info(f"Connecting to Postgres at {host}:{port} db={dbname} user={user}...")
conn = psycopg2.connect(
host=host, port=port, dbname=dbname, user=user, password=password
)
return conn
except Exception as e:
logger.error(f"Failed to connect to Postgres: {e}")
return None
def migrate():
if not os.path.exists(SQLITE_DB_PATH):
logger.error(f"SQLite database not found at {SQLITE_DB_PATH}")
return
# Connect to SQLite
try:
sqlite_conn = sqlite3.connect(SQLITE_DB_PATH)
sqlite_conn.row_factory = sqlite3.Row
sqlite_cur = sqlite_conn.cursor()
logger.info("Connected to SQLite")
except Exception as e:
logger.error(f"Failed to connect to SQLite: {e}")
return
# Connect to Postgres
pg_conn = get_postgres_connection()
if not pg_conn:
return
pg_cur = pg_conn.cursor()
try:
# Fetch all users from SQLite
sqlite_cur.execute("SELECT * FROM users")
users = sqlite_cur.fetchall()
logger.info(f"Found {len(users)} users in SQLite database.")
count = 0
for user in users:
# Map fields
# SQLite: id, username, password_hash, role, email, created_at (REAL), last_login (REAL), active (INT)
# Postgres: id (SERIAL), username, password_hash, role, email, created_at (TIMESTAMPTZ), last_login (TIMESTAMPTZ), active (BOOL)
username = user['username']
# Check if user exists in Postgres
pg_cur.execute("SELECT id FROM users WHERE username = %s", (username,))
if pg_cur.fetchone():
logger.info(f"Skipping existing user: {username}")
continue
password_hash = user['password_hash']
role = user['role']
email = user['email'] if 'email' in user.keys() else None
# Handle timestamps (SQLite stores as float timestamp)
created_at_ts = user['created_at']
last_login_ts = user['last_login']
created_at = datetime.fromtimestamp(created_at_ts) if created_at_ts else datetime.now()
last_login = datetime.fromtimestamp(last_login_ts) if last_login_ts else None
active = bool(user['active'])
# Insert into Postgres
pg_cur.execute("""
INSERT INTO users (username, password_hash, role, email, created_at, last_login, active)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""", (username, password_hash, role, email, created_at, last_login, active))
count += 1
pg_conn.commit()
logger.info(f"Successfully migrated {count} users.")
except Exception as e:
pg_conn.rollback()
logger.error(f"Migration failed: {e}")
finally:
sqlite_conn.close()
pg_conn.close()
if __name__ == "__main__":
migrate()
|