import sqlite3
import os

SOURCE_DB = "my_app.db"
BACKUP_DB = "my_app_backup.db"

def create_sample_db(db_filepath):
    """創(chuàng)建并填充一個示例SQLite數(shù)據(jù)庫。"""
    print(f"\n--- Creating sample database: {db_filepath} ---")
    conn = sqlite3.connect(db_filepath)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE,
            age INTEGER
        );
    ''')
    # 插入一些數(shù)據(jù)，如果表為空
    cursor.execute("SELECT COUNT(*) FROM users;")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30);")
        cursor.execute("INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 25);")
        cursor.execute("INSERT INTO users (name, email, age) VALUES ('Charlie', 'charlie@example.com', 35);")
        conn.commit()
        print(f"  Inserted 3 sample users into '{db_filepath}'.")
    else:
        print(f"  '{db_filepath}' already contains data. Skipping insertion.")
    conn.close()

def cleanup_files(*filenames):
    """刪除指定的文件。"""
    print("\n--- Cleaning up files ---")
    for filename in filenames:
        if os.path.exists(filename):
            os.remove(filename)
            print(f"  Deleted file: {filename}")

source_conn = sqlite3.connect(SOURCE_DB)

backup_conn = sqlite3.connect(BACKUP_DB)

try:
    source_conn.backup(backup_conn)
    print(f"Backup of '{SOURCE_DB}' to '{BACKUP_DB}' completed successfully.")
except sqlite3.Error as e:
    print(f"Error during backup: {e}")

finally:
    if source_conn:
        source_conn.close()
    if backup_conn:
        backup_conn.close()

import sqlite3
import os

# --- Configuration ---
SOURCE_DB = "my_application.db"
BACKUP_DB = "my_application_backup.db"

# --- Helper Functions (defined above, repeated for clarity) ---
def create_sample_db(db_filepath):
    """創(chuàng)建并填充一個示例SQLite數(shù)據(jù)庫。"""
    print(f"\n--- Creating sample database: {db_filepath} ---")
    conn = sqlite3.connect(db_filepath)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE,
            age INTEGER
        );
    ''')
    cursor.execute("SELECT COUNT(*) FROM users;")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30);")
        cursor.execute("INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 25);")
        cursor.execute("INSERT INTO users (name, email, age) VALUES ('Charlie', 'charlie@example.com', 35);")
        conn.commit()
        print(f"  Inserted 3 sample users into '{db_filepath}'.")
    else:
        print(f"  '{db_filepath}' already contains data. Skipping insertion.")
    conn.close()

def cleanup_files(*filenames):
    """刪除指定的文件。"""
    print("\n--- Cleaning up files ---")
    for filename in filenames:
        if os.path.exists(filename):
            os.remove(filename)
            print(f"  Deleted file: {filename}")

# --- Core Backup Function ---
def perform_backup(source_db_path, backup_db_path):
    """
    使用 sqlite3.Connection.backup() 方法備份 SQLite 數(shù)據(jù)庫。
    """
    print(f"\n--- Starting backup from '{source_db_path}' to '{backup_db_path}' ---")
    source_conn = None
    backup_conn = None
    try:
        source_conn = sqlite3.connect(source_db_path)
        backup_conn = sqlite3.connect(backup_db_path)
        
        # 執(zhí)行備份操作
        source_conn.backup(backup_conn)
        
        print(f"Backup of '{source_db_path}' to '{backup_db_path}' completed successfully.")
        return True
    except sqlite3.Error as e:
        print(f"Error during backup: {e}")
        return False
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
        return False
    finally:
        if source_conn:
            source_conn.close()
        if backup_conn:
            backup_conn.close()

# --- Verification Function ---
def verify_backup(db_filepath):
    """驗證備份數(shù)據(jù)庫是否存在并包含數(shù)據(jù)。"""
    print(f"\n--- Verifying backup: {db_filepath} ---")
    if not os.path.exists(db_filepath):
        print(f"  Error: Backup file '{db_filepath}' does not exist.")
        return False

    conn = None
    try:
        conn = sqlite3.connect(db_filepath)
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM users;")
        row_count = cursor.fetchone()[0]
        print(f"  Backup file '{db_filepath}' exists and contains {row_count} rows in 'users' table.")
        
        # 打印一些示例數(shù)據(jù)
        cursor.execute("SELECT id, name, email, age FROM users LIMIT 2;")
        sample_data = cursor.fetchall()
        print("  Sample data from backup:")
        for row in sample_data:
            print(f"    ID: {row[0]}, Name: {row[1]}, Email: {row[2]}, Age: {row[3]}")
        return True
    except sqlite3.Error as e:
        print(f"  Error accessing backup database '{db_filepath}': {e}")
        return False
    finally:
        if conn:
            conn.close()

# --- Main execution ---
# cleanup_files(SOURCE_DB, BACKUP_DB) # Clean up previous runs
# create_sample_db(SOURCE_DB)
# if perform_backup(SOURCE_DB, BACKUP_DB):
#     verify_backup(BACKUP_DB)
# cleanup_files(SOURCE_DB, BACKUP_DB) # Clean up after execution

def backup_progress(status, remaining, total):
    """
    備份進(jìn)度回調(diào)函數(shù)。
    :param status: 已復(fù)制的頁數(shù)。
    :param remaining: 剩余未復(fù)制的頁數(shù)。
    :param total: 數(shù)據(jù)庫的總頁數(shù)。
    """
    print(f"\r  Copied: {status} pages, Remaining: {remaining} pages, Total: {total} pages "
          f"({(status/total)*100:.2f}%)", end='')

# 在 perform_backup 函數(shù)中調(diào)用
# source_conn.backup(backup_conn, progress=backup_progress)
# print() # 備份完成后換行

import shutil

def simple_file_copy_backup(source_path, backup_path):
    """
    簡單的文件復(fù)制備份，僅在源數(shù)據(jù)庫未被寫入時安全。
    """
    print(f"\n--- Performing simple file copy backup from '{source_path}' to '{backup_path}' ---")
    if not os.path.exists(source_path):
        print(f"  Error: Source database '{source_path}' does not exist.")
        return False
    try:
        shutil.copyfile(source_path, backup_path)
        print(f"  Successfully copied '{source_path}' to '{backup_path}'.")
        return True
    except Exception as e:
        print(f"  Error during file copy: {e}")
        return False

# --- 嚴(yán)重警告 ---
# 僅當(dāng)確定源數(shù)據(jù)庫在復(fù)制期間不會有任何寫入操作時才使用此方法。
# 否則，你可能會得到一個損壞或不一致的備份文件。
# 對于正在使用的數(shù)據(jù)庫，始終優(yōu)先使用 sqlite3.Connection.backup()。

import zipfile

def compress_backup(db_filepath, zip_filepath):
    """將數(shù)據(jù)庫文件壓縮成zip文件。"""
    print(f"\n--- Compressing '{db_filepath}' to '{zip_filepath}' ---")
    try:
        with zipfile.ZipFile(zip_filepath, 'w', zipfile.ZIP_DEFLATED) as zf:
            zf.write(db_filepath, os.path.basename(db_filepath))
        print(f"  Successfully compressed '{db_filepath}' to '{zip_filepath}'.")
        return True
    except Exception as e:
        print(f"  Error compressing backup: {e}")
        return False

# Example usage:
# if perform_backup(SOURCE_DB, BACKUP_DB):
#     compress_backup(BACKUP_DB, BACKUP_DB + ".zip")

def restore_from_backup(backup_db_path, target_db_path):
    """
    從備份文件恢復(fù)數(shù)據(jù)庫。
    警告：這將覆蓋目標(biāo)路徑的現(xiàn)有數(shù)據(jù)庫！
    """
    print(f"\n--- Restoring from '{backup_db_path}' to '{target_db_path}' ---")
    if not os.path.exists(backup_db_path):
        print(f"  Error: Backup file '{backup_db_path}' does not exist.")
        return False
    try:
        # 確保目標(biāo)數(shù)據(jù)庫連接已關(guān)閉
        if os.path.exists(target_db_path):
            os.remove(target_db_path) # 刪除舊的（或損壞的）數(shù)據(jù)庫
            print(f"  Deleted existing database at '{target_db_path}'.")
        shutil.copyfile(backup_db_path, target_db_path)
        print(f"  Successfully restored '{backup_db_path}' to '{target_db_path}'.")
        return True
    except Exception as e:
        print(f"  Error during restoration: {e}")
        return False

import sqlite3
import os
import shutil # For file copy operations and cleanup
import datetime # For timestamping backup files

# --- Configuration ---
SOURCE_DB = "production_app.db"
BACKUP_DIR = "backups" # Directory to store backups
ZIP_DIR = "compressed_backups" # Directory to store compressed backups

# --- 1. Helper Functions ---
def create_sample_db(db_filepath):
    """Creates and populates a sample SQLite database."""
    print(f"\n--- Creating sample database: {db_filepath} ---")
    conn = None
    try:
        conn = sqlite3.connect(db_filepath)
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS inventory (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                item_name TEXT NOT NULL,
                quantity INTEGER DEFAULT 0,
                last_updated TEXT
            );
        ''')
        cursor.execute("SELECT COUNT(*) FROM inventory;")
        if cursor.fetchone()[0] == 0:
            cursor.execute("INSERT INTO inventory (item_name, quantity, last_updated) VALUES ('Laptop', 150, '2023-10-26 10:00:00');")
            cursor.execute("INSERT INTO inventory (item_name, quantity, last_updated) VALUES ('Monitor', 200, '2023-10-26 11:30:00');")
            cursor.execute("INSERT INTO inventory (item_name, quantity, last_updated) VALUES ('Keyboard', 300, '2023-10-26 12:00:00');")
            conn.commit()
            print(f"  Inserted 3 sample items into '{db_filepath}'.")
        else:
            print(f"  '{db_filepath}' already contains data. Skipping insertion.")
    except sqlite3.Error as e:
        print(f"  Database error during sample DB creation: {e}")
    finally:
        if conn:
            conn.close()

def cleanup_dirs(*dirs):
    """Deletes directories and their contents."""
    print("\n--- Cleaning up directories ---")
    for dir_path in dirs:
        if os.path.exists(dir_path):
            shutil.rmtree(dir_path)
            print(f"  Deleted directory: {dir_path}")

def cleanup_files(*filenames):
    """Deletes specified files."""
    for filename in filenames:
        if os.path.exists(filename):
            os.remove(filename)
            print(f"  Deleted file: {filename}")

def get_backup_filename(base_name, timestamp=True, extension=".db"):
    """生成帶時間戳的備份文件名。"""
    if timestamp:
        current_time = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
        return f"{base_name}_{current_time}{extension}"
    return f"{base_name}{extension}"

# --- 2. Backup Progress Callback ---
def backup_progress_callback(status, remaining, total):
    """Prints backup progress to the console."""
    percentage = (status / total) * 100 if total > 0 else 0
    print(f"\r  Progress: {status}/{total} pages ({percentage:.2f}%) "
          f"Remaining: {remaining} pages", end='', flush=True)

# --- 3. Core Backup Function ---
def perform_db_backup(source_db_path, backup_dir_path):
    """
    使用 sqlite3.Connection.backup() 方法備份 SQLite 數(shù)據(jù)庫，并包含進(jìn)度監(jiān)控。
    備份文件將存儲在 backup_dir_path 目錄下，并帶有時間戳。
    """
    if not os.path.exists(backup_dir_path):
        os.makedirs(backup_dir_path)
        print(f"  Created backup directory: {backup_dir_path}")

    base_name = os.path.basename(source_db_path).replace(".db", "")
    backup_filename = get_backup_filename(base_name)
    target_backup_path = os.path.join(backup_dir_path, backup_filename)
    
    print(f"\n--- Starting online backup from '{source_db_path}' to '{target_backup_path}' ---")
    source_conn = None
    backup_conn = None
    try:
        source_conn = sqlite3.connect(source_db_path)
        backup_conn = sqlite3.connect(target_backup_path)
        
        # 執(zhí)行備份操作，包含進(jìn)度回調(diào)
        source_conn.backup(backup_conn, pages=1, progress=backup_progress_callback, sleep=0.05) # Small pages for visible progress
        
        print("\nBackup completed successfully.")
        return target_backup_path
    except sqlite3.Error as e:
        print(f"\nError during backup: {e}")
        if os.path.exists(target_backup_path):
            os.remove(target_backup_path) # Clean up partial backup on error
            print(f"  Removed incomplete backup file: {target_backup_path}")
        return None
    except Exception as e:
        print(f"\nAn unexpected error occurred during backup: {e}")
        return None
    finally:
        if source_conn:
            source_conn.close()
        if backup_conn:
            backup_conn.close()

# --- 4. Verification Function ---
def verify_db_backup(db_filepath):
    """Verifies the backup database exists and contains data."""
    print(f"\n--- Verifying backup: {db_filepath} ---")
    if not os.path.exists(db_filepath):
        print(f"  Error: Backup file '{db_filepath}' does not exist.")
        return False

    conn = None
    try:
        conn = sqlite3.connect(db_filepath)
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM inventory;")
        row_count = cursor.fetchone()[0]
        print(f"  Backup file '{db_filepath}' exists and contains {row_count} rows in 'inventory' table.")
        
        cursor.execute("SELECT id, item_name, quantity FROM inventory LIMIT 2;")
        sample_data = cursor.fetchall()
        print("  Sample data from backup:")
        for row in sample_data:
            print(f"    ID: {row[0]}, Item: {row[1]}, Quantity: {row[2]}")
        return True
    except sqlite3.Error as e:
        print(f"  Error accessing backup database '{db_filepath}': {e}")
        return False
    except Exception as e:
        print(f"  An unexpected error occurred during verification: {e}")
        return False
    finally:
        if conn:
            conn.close()

# --- 5. Compression Function ---
def compress_db_backup(db_filepath, compressed_dir_path):
    """Compresses a database file into a .zip archive."""
    if not os.path.exists(compressed_dir_path):
        os.makedirs(compressed_dir_path)
        print(f"  Created compressed backup directory: {compressed_dir_path}")

    zip_filename = os.path.basename(db_filepath) + ".zip"
    target_zip_path = os.path.join(compressed_dir_path, zip_filename)
    
    print(f"\n--- Compressing '{db_filepath}' to '{target_zip_path}' ---")
    try:
        with zipfile.ZipFile(target_zip_path, 'w', zipfile.ZIP_DEFLATED) as zf:
            zf.write(db_filepath, os.path.basename(db_filepath)) # Write with just filename inside zip
        print(f"  Successfully compressed '{db_filepath}' to '{target_zip_path}'.")
        return target_zip_path
    except Exception as e:
        print(f"  Error compressing backup: {e}")
        return None

# --- Main Program Execution ---
def main():
    cleanup_dirs(BACKUP_DIR, ZIP_DIR)
    cleanup_files(SOURCE_DB) # Ensure source DB is clean if it was left from previous run

    create_sample_db(SOURCE_DB) # Create the source database with some data

    # Perform backup using sqlite3.Connection.backup()
    backup_file_path = perform_db_backup(SOURCE_DB, BACKUP_DIR)
    
    if backup_file_path:
        verify_db_backup(backup_file_path) # Verify the created backup
        
        # Optionally, compress the backup file
        compressed_file_path = compress_db_backup(backup_file_path, ZIP_DIR)
        if compressed_file_path:
            print(f"\nCompressed backup available at: {compressed_file_path}")
            
    print("\n--- Program finished ---")
    # cleanup_dirs(BACKUP_DIR, ZIP_DIR) # Uncomment to delete backup files after run
    # cleanup_files(SOURCE_DB) # Uncomment to delete source DB after run

if __name__ == "__main__":
    main()