app.py

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import subprocess
import json
import os
import mysql.connector
from datetime import datetime

class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.geometry('500x700')
        self.title("Database Dump Tool")
        self.config_json = self.load_config()
        self.tables = []
        self.views = []
        self.routines = []
        self.table_dependencies = {}

    def load_config(self):
        with open('config.json', 'r') as f:
            return json.load(f)

    def get_database_connection(self):
        config = None
        selected_name = self.db_combo.get()
        for c in self.config_json['configs']:
            if c['name'] == selected_name:
                config = c['database']
                break
        if config is None:
            raise Exception('Database tidak ditemukan')
        
        return mysql.connector.connect(
            host=config['host'],
            user=config['user'],
            password=config['password'],
            database=config['database']
        )

    def get_table_dependencies(self):
        try:
            conn = self.get_database_connection()
            cursor = conn.cursor()
            
            # Get all tables and their foreign key dependencies
            cursor.execute("""
                SELECT 
                    TABLE_NAME,
                    REFERENCED_TABLE_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE REFERENCED_TABLE_SCHEMA = %s
                AND REFERENCED_TABLE_NAME IS NOT NULL
            """, (self.get_selected_database(),))
            
            dependencies = {}
            for table_name, ref_table in cursor.fetchall():
                if table_name not in dependencies:
                    dependencies[table_name] = set()
                dependencies[table_name].add(ref_table)
            
            cursor.close()
            conn.close()
            return dependencies
        except Exception as e:
            messagebox.showerror('Error', f'Gagal mengambil dependensi tabel: {str(e)}')
            return {}

    def sort_tables_by_dependencies(self, tables, dependencies):
        # Implementasi topological sort untuk mengurutkan tabel
        sorted_tables = []
        visited = set()
        temp_visited = set()

        def visit(table):
            if table in temp_visited:
                return  # Skip jika ada circular dependency
            if table in visited:
                return
            
            temp_visited.add(table)
            
            # Kunjungi semua tabel yang direferensikan
            if table in dependencies:
                for dep in dependencies[table]:
                    if dep in tables:  # Hanya proses jika tabel ada dalam list yang akan diexport
                        visit(dep)
            
            temp_visited.remove(table)
            visited.add(table)
            sorted_tables.append(table)

        # Mulai dengan tabel yang tidak memiliki dependencies
        independent_tables = [t for t in tables if t not in dependencies]
        for table in independent_tables:
            visit(table)

        # Kemudian proses tabel yang memiliki dependencies
        for table in tables:
            if table not in visited:
                visit(table)

        return sorted_tables

    def get_database_objects(self, event=None):
        try:
            conn = self.get_database_connection()
            cursor = conn.cursor()

            # Get tables
            cursor.execute("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")
            self.tables = [row[0] for row in cursor.fetchall()]

            # Get table dependencies
            self.table_dependencies = self.get_table_dependencies()

            # Sort tables based on dependencies
            self.tables = self.sort_tables_by_dependencies(self.tables, self.table_dependencies)

            # Get views
            cursor.execute("SHOW FULL TABLES WHERE Table_type = 'VIEW'")
            self.views = [row[0] for row in cursor.fetchall()]

            # Get routines
            cursor.execute("SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = %s", 
                         (self.get_selected_database(),))
            self.routines = [(row[0], row[1]) for row in cursor.fetchall()]

            cursor.close()
            conn.close()

            self.update_object_lists()
        except Exception as e:
            messagebox.showerror('Error', f'Gagal mengambil objek database: {str(e)}')

    def get_selected_database(self):
        selected_name = self.db_combo.get()
        for c in self.config_json['configs']:
            if c['name'] == selected_name:
                return c['database']['database']
        return None

    def update_object_lists(self):
        # Update tables listbox
        self.tables_listbox.delete(0, tk.END)
        for table in self.tables:
            prefix = "└─ " if table in self.table_dependencies else "├─ "
            self.tables_listbox.insert(tk.END, f"{prefix}{table}")

        # Update views listbox
        self.views_listbox.delete(0, tk.END)
        for view in self.views:
            self.views_listbox.insert(tk.END, view)

        # Update routines listbox
        self.routines_listbox.delete(0, tk.END)
        for routine, type_ in self.routines:
            self.routines_listbox.insert(tk.END, f"{routine} ({type_})")

    def select_all_items(self, listbox):
        listbox.select_set(0, tk.END)

    def create_list_frame(self, parent, title):
        frame = ttk.Frame(parent)
        
        # Buat frame untuk listbox dengan scrollbar
        list_frame = ttk.Frame(frame)
        list_frame.pack(fill='both', expand=True)
        
        # Buat Scrollbar
        scrollbar = ttk.Scrollbar(list_frame)
        scrollbar.pack(side='right', fill='y')
        
        # Buat Listbox
        listbox = tk.Listbox(list_frame, selectmode=tk.MULTIPLE, yscrollcommand=scrollbar.set)
        listbox.pack(side='left', fill='both', expand=True)
        
        # Konfigurasikan scrollbar
        scrollbar.config(command=listbox.yview)
        
        # Buat frame untuk tombol
        button_frame = ttk.Frame(frame)
        button_frame.pack(fill='x', pady=(5,0))
        
        # Tambahkan tombol "Pilih Semua"
        ttk.Button(button_frame, text="Pilih Semua", 
                  command=lambda lb=listbox: self.select_all_items(lb)).pack(side='left')
        
        return frame, listbox

    def create_widgets(self):
        # Database selection frame
        db_frame = ttk.Frame(self)
        db_frame.pack(fill='x', pady=5, padx=5)
        
        ttk.Label(db_frame, text='Database:').pack(side='left', padx=(0, 5))
        
        # Menggunakan Combobox alih-alih OptionMenu
        self.db_combo = ttk.Combobox(db_frame, state='readonly')
        self.db_combo['values'] = [config['name'] for config in self.config_json['configs']]
        self.db_combo.set(self.config_json['configs'][0]['name'])  # Set nilai default
        self.db_combo.pack(side='left', fill='x', expand=True)
        self.db_combo.bind('<<ComboboxSelected>>', self.get_database_objects)

        # Output directory
        dir_frame = ttk.Frame(self)
        dir_frame.pack(fill='x', pady=5, padx=5)
        
        ttk.Label(dir_frame, text='Output Directory:').pack(side='left', padx=(0, 5))
        self.output_dir = tk.StringVar()
        ttk.Entry(dir_frame, textvariable=self.output_dir).pack(side='left', fill='x', expand=True)
        ttk.Button(dir_frame, text='Browse', command=self.browse_directory).pack(side='left', padx=(5, 0))

        # Split files option
        self.split_files = tk.BooleanVar(value=True)  # Default ke True
        ttk.Checkbutton(self, text='Split into separate files', variable=self.split_files).pack(pady=5)

        # Create notebook for different objects
        notebook = ttk.Notebook(self)
        notebook.pack(fill='both', expand=True, pady=5)

        # Tables frame
        tables_frame, self.tables_listbox = self.create_list_frame(notebook, "Tables")
        notebook.add(tables_frame, text='Tables')

        # Views frame
        views_frame, self.views_listbox = self.create_list_frame(notebook, "Views")
        notebook.add(views_frame, text='Views')

        # Routines frame
        routines_frame, self.routines_listbox = self.create_list_frame(notebook, "Routines")
        notebook.add(routines_frame, text='Routines')

        # Export buttons
        button_frame = ttk.Frame(self)
        button_frame.pack(fill='x', pady=10, padx=5)
        ttk.Button(button_frame, text='Export Selected', command=self.export_selected).pack(side='left', padx=(0, 5))
        ttk.Button(button_frame, text='Export All', command=self.export_all).pack(side='left')

        # Trigger initial database load
        self.get_database_objects()

    def browse_directory(self):
        directory = filedialog.askdirectory()
        if directory:  # Only set if user actually selected a directory
            self.output_dir.set(directory)

    def get_create_statement(self, object_name, object_type):
        try:
            conn = self.get_database_connection()
            cursor = conn.cursor()

            if object_type == 'table':
                cursor.execute(f"SHOW CREATE TABLE `{object_name}`")
                result = cursor.fetchone()
                create_stmt = result[1]
            elif object_type == 'view':
                cursor.execute(f"SHOW CREATE VIEW `{object_name}`")
                result = cursor.fetchone()
                create_stmt = result[1]
            elif object_type in ['PROCEDURE', 'FUNCTION']:
                cursor.execute(f"SHOW CREATE {object_type} `{object_name}`")
                result = cursor.fetchone()
                create_stmt = result[2]

            # Remove DEFINER
            create_stmt = create_stmt.replace('\n', ' ')
            create_stmt = ' '.join([line for line in create_stmt.split() if not line.startswith('DEFINER=')])

            cursor.close()
            conn.close()
            return create_stmt
        except Exception as e:
            messagebox.showerror('Error', f'Gagal mengambil create statement: {str(e)}')
            return None

    def get_insert_statements(self, table_name):
        try:
            conn = self.get_database_connection()
            cursor = conn.cursor(dictionary=True)  # Menggunakan dictionary cursor

            # Dapatkan nama kolom dan tipe datanya
            cursor.execute(f"SHOW COLUMNS FROM `{table_name}`")
            columns_info = cursor.fetchall()
            columns = [column['Field'] for column in columns_info]
            columns_str = ', '.join(f'`{col}`' for col in columns)

            # Ambil semua data
            cursor.execute(f"SELECT * FROM `{table_name}`")
            rows = cursor.fetchall()

            insert_statements = []
            for row in rows:
                values = []
                for col in columns:
                    val = row[col]
                    if val is None:
                        values.append('NULL')
                    elif isinstance(val, (int, float)):
                        values.append(str(val))
                    elif isinstance(val, (bytes, bytearray)):
                        # Handle binary data
                        values.append(f"x'{val.hex()}'")
                    elif isinstance(val, str):
                        # Escape string values
                        val_str = val.replace("'", "''")
                        values.append(f"'{val_str}'")
                    else:
                        # Handle datetime dan timestamp
                        values.append(f"'{str(val)}'")

                values_str = ', '.join(values)
                insert_stmt = f"INSERT INTO `{table_name}` ({columns_str}) VALUES ({values_str});"
                insert_statements.append(insert_stmt)

            cursor.close()
            conn.close()
            return insert_statements
        except Exception as e:
            messagebox.showerror('Error', f'Gagal mengambil data: {str(e)}')
            return []

    def export_object(self, object_name, object_type):
        create_stmt = self.get_create_statement(object_name, object_type)
        if not create_stmt:
            return False

        # Buat direktori berdasarkan tipe objek
        type_dir = os.path.join(self.output_dir.get(), object_type.lower() + 's')
        os.makedirs(type_dir, exist_ok=True)

        if self.split_files.get():
            filename = f"{object_name}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.sql"
            filepath = os.path.join(type_dir, filename)
        else:
            filename = f"full_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.sql"
            filepath = os.path.join(self.output_dir.get(), filename)
        
        try:
            mode = 'a' if os.path.exists(filepath) else 'w'
            with open(filepath, mode) as f:
                f.write(f"-- Export of {object_type} {object_name}\n")
                if object_type == 'table':
                    f.write(f"DROP TABLE IF EXISTS `{object_name}`;\n")
                elif object_type == 'view':
                    f.write(f"DROP VIEW IF EXISTS `{object_name}`;\n")
                elif object_type in ['PROCEDURE', 'FUNCTION']:
                    f.write(f"DROP {object_type} IF EXISTS `{object_name}`;\n")
                f.write("DELIMITER ;;\n")
                f.write(f"{create_stmt};;\n")
                f.write("DELIMITER ;\n\n")

                # Export data untuk tabel
                if object_type == 'table':
                    insert_statements = self.get_insert_statements(object_name)
                    if insert_statements:
                        f.write("\n-- Dumping data for table `" + object_name + "`\n")
                        f.write("/*!40000 ALTER TABLE `" + object_name + "` DISABLE KEYS */;\n")
                        for stmt in insert_statements:
                            f.write(stmt + "\n")
                        f.write("/*!40000 ALTER TABLE `" + object_name + "` ENABLE KEYS */;\n")

            return True
        except Exception as e:
            messagebox.showerror('Error', f'Gagal mengekspor {object_type} {object_name}: {str(e)}')
            return False

    def export_selected(self):
        if not self.output_dir.get():
            messagebox.showerror('Error', 'Pilih direktori output terlebih dahulu')
            return

        success_count = 0
        error_count = 0

        # Export selected tables
        for idx in self.tables_listbox.curselection():
            table_name = self.tables[idx]
            if self.export_object(table_name, 'table'):
                success_count += 1
            else:
                error_count += 1

        # Export selected views
        for idx in self.views_listbox.curselection():
            view_name = self.views[idx]
            if self.export_object(view_name, 'view'):
                success_count += 1
            else:
                error_count += 1

        # Export selected routines
        for idx in self.routines_listbox.curselection():
            routine_name, routine_type = self.routines[idx]
            if self.export_object(routine_name, routine_type):
                success_count += 1
            else:
                error_count += 1

        messagebox.showinfo('Export Complete', 
            f'Ekspor selesai!\nBerhasil: {success_count}\nGagal: {error_count}')

    def export_all(self):
        if not self.output_dir.get():
            messagebox.showerror('Error', 'Pilih direktori output terlebih dahulu')
            return

        success_count = 0
        error_count = 0

        # Export all tables
        for table_name in self.tables:
            if self.export_object(table_name, 'table'):
                success_count += 1
            else:
                error_count += 1

        # Export all views
        for view_name in self.views:
            if self.export_object(view_name, 'view'):
                success_count += 1
            else:
                error_count += 1

        # Export all routines
        for routine_name, routine_type in self.routines:
            if self.export_object(routine_name, routine_type):
                success_count += 1
            else:
                error_count += 1

        messagebox.showinfo('Export Complete', 
            f'Ekspor selesai!\nBerhasil: {success_count}\nGagal: {error_count}')

if __name__ == '__main__':
    app = App()
    app.create_widgets()
    app.mainloop()