#!/usr/bin/python
# -*- coding: utf-8 -*-
import argparse

import sqlite3 as sqlite

try:  # optional second engine : DuckDB (pip install duckdb)
    import duckdb

    db_errors = (sqlite.Error, duckdb.Error)
except ImportError:
    duckdb = None
    db_errors = (sqlite.Error,)

import sys
import os
import locale
import csv
import datetime
import io
import json
import re
import shlex  # Simple lexical analysis
from os.path import expanduser
import tempfile as tmpf
import subprocess

from tkinter import *
from tkinter import font, ttk, filedialog, messagebox, simpledialog
from tkinter.ttk import *

tipwindow = None

# starting Python-3.13, PEP 667 forces us to us a specific dictionnary pydef_locals instead of locals()
pydef_locals ={}

# file dialog filter for databases
DB_FILETYPES = [
    ("default", "*.db"),
    ("duckdb", "*.duckdb"),
    ("other", "*.db*"),
    ("all", "*.*"),
]

# the .output/.once settings run_temp() has to save and restore around a run
OUTPUT_STATE = (
    "once_mode", "encode_in", "output_file", "init_output", "output_mode", "x_mode"
)

# the sqlite3-cli style commands a script may use (see App.run_dotcommand)
DOT_COMMANDS = (
    ".backup", ".cd", ".dump", ".excel", ".headers", ".import", ".once",
    ".open", ".output", ".print", ".read", ".restore", ".separator", ".shell",
)


def temp_csv():
    """return the name of a fresh temporary .csv file"""
    return tmpf.NamedTemporaryFile(delete=False, suffix="_sqlite_bro.csv").name


# the create/insert demo shared by the SQLite and DuckDB welcome memos
# (typed columns and single-quoted literals : valid for both engines)
WELCOME_CREATE = """DROP TABLE IF EXISTS item; DROP TABLE IF EXISTS part;
CREATE TABLE item (ItemNo TEXT, Description TEXT, Kg REAL, PRIMARY KEY (ItemNo));
CREATE TABLE part(ParentNo TEXT, ChildNo TEXT, Description TEXT, Qty_per REAL);
\n-- to CREATE an index :
DROP INDEX IF EXISTS parts_id1;
CREATE INDEX parts_id1 ON part(ParentNo, ChildNo);
\n-- to CREATE a view 'v1':
DROP VIEW IF EXISTS v1;
CREATE VIEW v1 as select * from item inner join part as p ON ItemNo=p.ParentNo;
\n-- to INSERT datas : string literals use single quotes
INSERT INTO item values('T','Ford',1000);
INSERT INTO item select 'A','Merced',1250 union all select 'W','Wheel',9 ;
INSERT INTO part select ItemNo,'W','needed',Kg/250 from item where Kg>250;
\n-- to CREATE a Python embedded function,
-- enclose it by "pydef" and ";" :
pydef py_hello():
    "hello world"
    return ("Hello, World !");
pydef py_fib(n):
   "fibonacci : example with function call (may only be internal) "
   fib = lambda n: n if n < 2 else fib(n-1) + fib(n-2)
   return("%s" % fib(n*1));
"""

WELCOME_END = """\n-- to EXPORT :
--    a TABLE, select TABLE, then click on icon 'SQL->CSV'
--    a QUERY RESULT, select the SCRIPT text, then click on icon '???->CSV',
-- example : select the end of this line: SELECT SQLITE_VERSION()
\n\n-- to use COMMIT and ROLLBACK :
BEGIN TRANSACTION;
UPDATE item SET Kg = Kg + 1;
COMMIT;
BEGIN TRANSACTION;
UPDATE item SET Kg = 0;
select Kg, Description from Item;
ROLLBACK;
select Kg, Description from Item;

\n\n-- '.' commands understood:
-- .backup FILE           Backup DB (default "main") to FILE (if Python>=3.7)
-- .cd DIRECTORY          Change the working directory to DIRECTORY
-- .dump ?FILE?           Render database content as SQL (to FILE if specified)
-- .excel                 Display the output of next command in spreadsheet
-- .headers on|off        Turn display of headers on or off
-- .import FILE TABLE     Import data from FILE into TABLE
--                        (create TABLE only if it doesn't exist, keep existing records)
-- .once [--bom] FILE     Output of next SQL command to FILE [with utf-8 bom]
-- .open ?FILE?           Close existing database and reopen FILE
-- .output ?FILE?         Send output to FILE or stdout if FILE is omitted
-- .print STRING...       Print literal STRING
-- .read FILE             Read input from FILE
-- .restore FILE          Restore DB (default "main") from FILE (if Python>=3.7)
-- .separator COL         Set column separator in next .once exports (default ,)
-- .shell CMD ARGS...     Run CMD ARGS... in a system shell
\n-- keyboard : Ctrl+Shift+Enter (or F5) runs the sole statement the cursor is in,
-- Ctrl+Enter (or F9) runs the whole script, or just the selected text

.headers on
.separator ;
.once --bom  '~this_file_of result.txt'
select ItemNo, Description from item order by ItemNo desc;
.import '~this_file_of result.txt' in_this_table
.cd ~
ATTACH 'test.db' as toto;
DROP TABLE IF EXISTS toto.new_item;
CREATE TABLE toto.new_item as select * from "main"."item";
.dump
"""

WELCOME_DUCKDB = """-- DuckDB Memo (Demo = click on green "->" icon)
-- (this database uses the DuckDB engine : SQL is stricter than SQLite)
\n-- to CREATE tables : column types are mandatory
""" + WELCOME_CREATE + """
pydef py_fib_typed(n: int) -> int:
    "type-annotated pydef : registered natively (faster, typed) "
    fib = lambda n: n if n < 2 else fib(n-1) + fib(n-2)
    return fib(n);
\n-- to USE a python embedded function (require numpy installed for DuckDb):
select py_hello(),py_fib_typed(7) as fib_typed, version() as duckdb_version;

\n-- some DuckDB goodies :
DESCRIBE item;
SUMMARIZE item;
select * from duckdb_tables();

""" + WELCOME_END.replace('test.db', 'test.duckdb')

WELCOME_TEXT = """-- SQLite Memo (Demo = click on green "->" and "@" icons)
-- (tip : open or create a '.duckdb' file to use the DuckDB engine instead)
\n-- to CREATE a table 'item' and a table 'part' :
""" + WELCOME_CREATE + """
\n-- to USE a python embedded function :
select py_hello(), py_fib(6) as fibonacci, sqlite_version(),
       time('now', 'localtime') t, datetime('now', 'localtime') dt;
\n-- to use SAVEPOINT :
SAVEPOINT remember_Neo;  -- create a savepoint
UPDATE item SET Description = 'Smith'; -- do things
SELECT ItemNo, Description FROM Item; -- see things done
ROLLBACK TO SAVEPOINT remember_Neo; -- go back to savepoint state
SELECT ItemNo, Description FROM Item;  -- see all is back to normal
RELEASE SAVEPOINT remember_Neo; -- free memory
""" + WELCOME_END


class App:
    """the GUI graphic application"""

    def __init__(self, use_gui=True):
        """create a tkk graphic interface with a main window tk_win"""
        self.__version__ = "1.3.2"
        self._title = "of 2026-08-04 : 'python -m sqlite_bro , at last !'"
        self.conn = None  # Baresql database object
        self.database_file = ""
        self.initialdir = "."

        # Do we use a GUI ?
        self.use_gui = use_gui  # gui ok by default
        if self.use_gui and os.name == "nt":
            # tell Windows we are dpi-aware, so it doesn't bitmap-stretch
            # (blur) the interface on high-dpi screens
            try:
                from ctypes import windll
                try:
                    windll.shcore.SetProcessDpiAwareness(1)  # Windows 8.1+
                except Exception:
                    windll.user32.SetProcessDPIAware()  # older Windows
            except Exception:
                pass
        try:
            self.tk_win = Tk()
        except:
            self.use_gui = False

        if self.use_gui:
            self.tk_win.title(
                "A graphic SQLite and DuckDB Client in 1 Python file ("
                + self.__version__
                + ")"
            )
            self.tk_win.option_add("*tearOff", FALSE)  # hint of tk documentation

            # high-dpi: how much bigger is this screen than a classic 96 dpi one,
            # and the nearest integer ratio to enlarge the (bitmap) icons by.
            # never below 1: macOS tk reports a 72 dpi baseline, don't shrink
            from fractions import Fraction

            self.dpi_scale = max(1.0, self.tk_win.winfo_fpixels("1i") / 96.0)
            zoom = Fraction(round(self.dpi_scale * 96), 96).limit_denominator(4)
            self.icon_zoom = (zoom.numerator, zoom.denominator)
            self.tk_win.minsize(
                int(600 * self.dpi_scale), int(200 * self.dpi_scale)
            )  # minimal size
            # open at 80% of the screen: dpi-aware windows are no longer
            # bitmap-enlarged by Windows, and the natural (content) size
            # would not fit the screen anyway
            self.tk_win.geometry(
                "%sx%s"
                % (
                    self.tk_win.winfo_screenwidth() * 4 // 5,
                    self.tk_win.winfo_screenheight() * 4 // 5,
                )
            )

            # start from the real default font size (9 on Windows), so the
            # first Ctrl+mousewheel zoom moves one point, not two
            self.font_size = abs(font.nametofont("TkDefaultFont").actual("size")) or 10
            self.font_wheight = 0
            # With a Menubar and Toolbar
            self.create_menu()
            self.create_toolbar()

            # Create style "ButtonNotebook"
            self.create_style()
            # adjust Treeview rows to the real font height (high-dpi screens)
            self.set_treeview_rowheight()
            # Initiate Drag State
            self.state_drag = False
            self.state_drag_index = 0

            # With a Panedwindow of two frames: 'Database' and 'Queries'
            p = ttk.Panedwindow(self.tk_win, orient=HORIZONTAL)
            p.pack(fill=BOTH, expand=1)

            f_database = ttk.Labelframe(
                p,
                text="Databases",
                width=int(200 * self.dpi_scale),
                height=int(100 * self.dpi_scale),
            )
            p.add(f_database)
            f_queries = ttk.Labelframe(
                p,
                text="Queries",
                width=int(200 * self.dpi_scale),
                height=int(100 * self.dpi_scale),
            )
            p.add(f_queries)

            # build tree view 't' inside the left 'Database' Frame
            self.db_tree = ttk.Treeview(
                f_database, displaycolumns=[], columns=("detail", "action")
            )
            # the tree column width is in (dpi-blind) pixels: follow the screen
            self.db_tree.column("#0", width=int(200 * self.dpi_scale))
            self.db_tree.tag_configure("run")
            self.db_tree.pack(fill=BOTH, expand=1)

            # create a  notebook 'n' inside the right 'Queries' Frame
            self.n = NotebookForQueries(self.tk_win, f_queries, [], self.use_gui)

            # Bind keyboard shortcuts
            self.tk_win.bind("<F9>", self.run_tab)
            # Ctrl+Shift+Enter runs only the statement the cursor is in (class
            # binding, so it eats the line return). F5 does the same, when the
            # keyboard has a function row that doesn't need an 'Fn' press
            self.tk_win.bind_class(
                "Text", "<Control-Shift-Return>", self.run_statement
            )
            self.tk_win.bind("<F5>", self.run_statement)
            # Ctrl+Enter runs the script (class binding also eats the line return)
            self.tk_win.bind_class("Text", "<Control-Return>", self.run_tab_event)
            # Ctrl+mousewheel zooms the font, wherever the mouse is
            # (class bindings so the wheel doesn't also scroll; -Button-4/5 is linux)
            for seq in ("<Control-MouseWheel>", "<Control-Button-4>", "<Control-Button-5>"):
                for cls in ("Text", "Treeview", "Listbox"):
                    self.tk_win.bind_class(cls, seq, self.zoom_font)
                self.tk_win.bind(seq, self.zoom_font)
        else:
            # create a GUI-Less notebook 'n'
            self.n = NotebookForQueries(None, None, [], self.use_gui)

        # define default home directory
        self.home = expanduser("~")

        # defaults for export
        self.default_header = True
        self.default_separator = ","
        self.current_directory = os.getcwd()

        # show the DuckDB welcome demo only once per session
        self.duckdb_demo_shown = False

        # Initiate Output State
        self.once_mode = False
        self.encode_in = "utf-8"
        self.output_file = None
        self.init_output = True
        self.output_mode = False
        self.x_mode = False

    def create_menu(self):
        """create the menu of the application"""
        menubar = Menu(self.tk_win)
        self.tk_win["menu"] = menubar

        # feeding the top level menu
        self.menu = Menu(menubar)
        menubar.add_cascade(menu=self.menu, label="Database")
        self.menu_help = Menu(menubar)
        menubar.add_cascade(menu=self.menu_help, label="?")

        # feeding database sub-menu
        self.menu.add_command(label="New Database", command=self.new_db)
        self.menu.add_command(
            label="New In-Memory Database", command=lambda: self.new_db(":memory:")
        )
        if duckdb is not None:
            self.menu.add_command(
                label="New In-Memory DuckDB Database",
                command=lambda: self.new_db(":memory:", engine="duckdb"),
            )
        self.menu.add_command(label="Open Database ...", command=self.open_db)
        self.menu_recent = Menu(self.menu, postcommand=self.refresh_recent_menu)
        self.menu.add_cascade(menu=self.menu_recent, label="Open Recent")
        self.menu.add_command(
            label="Open Database ...(legacy auto-commit)",
            command=lambda: self.open_db(""),
        )
        self.menu.add_command(label="Close Database", command=self.close_db)
        self.menu.add_separator()
        self.menu.add_command(label="Attach Database", command=self.attach_db)
        if sys.version_info[:2] >= (3, 7):
            self.menu.add_separator()
            self.menu.add_command(label="Backup main Database", command=self.backup_db)
            self.menu.add_command(
                label="Restore into main Database", command=self.restore_db
            )
        self.menu.add_separator()
        self.menu.add_command(label="Quit", command=self.quit_db)

        self.menu_help.add_command(
            label="about",
            command=lambda: messagebox.showinfo(
                message="\nSQLite_bro : a graphic SQLite and DuckDB Client"
                " in 1 Python file\n\nVersion %s %s"
                "\n(https://github.com/stonebig/sqlite_bro)"
                "\n\nrun by: %s\n\nhome: %s\n\ncurrent directory: %s"
                % (self.__version__, self._title, sys.executable, self.home, os.getcwd())
            ),
        )

    def create_toolbar(self):
        """create the toolbar of the application"""
        self.toolbar = Frame(self.tk_win, relief=RAISED)
        self.toolbar.pack(side=TOP, fill=X)
        self.tk_icon = self.get_tk_icons()

        # list of (image, action, tooltip) :
        to_show = [
            ("refresh_img", self.actualize_db, "Actualize databases"),
            ("run_img", self.run_tab, "Run script selection"),
            (
                "newtab_img",
                lambda x=self: x.n.new_query_tab("___", ""),
                "Create a new script",
            ),
            ("csvin_img", self.import_csvtb, "Import a CSV or JSON file into a table"),
            ("clipin_img", self.paste_csvtb, "Import Clipboard into a table"),
            ("csvex_img", self.export_csvtb, "Export selected table to a CSV or JSON file"),
            ("clipex_img", self.copy_csvtb, "Export selected table to Clipboard"),
            ("dbdef_img", self.savdb_script, "Save main database as a SQL script"),
            ("qryex_img", self.export_csvqr, "Export script selection to a CSV or JSON file"),
            ("qryclip_img", self.copy_csvqr, "Export script selection to Clipboard"),
            (
                "exe_img",
                self.exsav_script,
                "Run script+output to a file (First 200 rec. per Qry)",
            ),
            ("sqlin_img", self.load_script, "Load a SQL script file"),
            ("sqlsav_img", self.sav_script, "Save a SQL script in a file"),
            ("chgsz_img", self.chg_fontsize, "Modify font size"),
            (
                "img_run_temp",
                self.run_temp,
                "Run script selection and Display output in temporary files",
            ),
            ("img_clean_temp", self.clean_temp, "Remove old temporary files"),
        ]

        for img, action, tip in to_show:
            b = Button(self.toolbar, image=self.tk_icon[img], command=action)
            b.pack(side=LEFT, padx=2, pady=2)
            self.createToolTip(b, tip)

    def set_initialdir(self, proposal):
        """change initial dir, if possible"""
        if os.path.isfile(proposal):
            self.initialdir = os.path.dirname(proposal)

    def new_db(self, filename="", engine=None):
        """create a new database (engine guessed from extension if not given)"""
        if filename == "":
            filename = filedialog.asksaveasfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                title="Define a new database name and location",
                filetypes=DB_FILETYPES,
            )
        if filename != "":
            self.database_file = filename
            if os.path.isfile(filename):
                self.set_initialdir(filename)
                if messagebox.askyesno(
                    message="Confirm Destruction of previous Datas ?",
                    icon="question",
                    title="Destroying",
                ):
                    os.remove(filename)
            self.conn = Baresql(self.database_file, engine=engine)
            self.add_recent_db(filename)
            self.show_duckdb_demo()
            self.actualize_db()

    def open_db(self, filename="", isolation_level=None, engine=None):
        """open an existing database (engine guessed from extension if not given)"""
        if filename == "":
            filename = filedialog.askopenfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                filetypes=DB_FILETYPES,
            )
        if filename != "":
            self.set_initialdir(filename)
            self.database_file = filename
            self.conn = Baresql(self.database_file, engine=engine)
            self.add_recent_db(filename)
            self.show_duckdb_demo()
            self.actualize_db()

    def recent_dbs(self):
        """return the list of recently opened databases"""
        try:
            with open(
                os.path.join(self.home, ".sqlite_bro_recent"), encoding="utf-8"
            ) as f:
                return [line.strip() for line in f if line.strip()]
        except (OSError, IOError):
            return []

    def add_recent_db(self, filename):
        """remember the ten most recently opened databases"""
        if filename in ("", ":memory:"):
            return
        filename = os.path.abspath(filename)
        recents = [filename] + [r for r in self.recent_dbs() if r != filename]
        try:
            with open(
                os.path.join(self.home, ".sqlite_bro_recent"), "w", encoding="utf-8"
            ) as f:
                f.write("\n".join(recents[:10]))
        except (OSError, IOError):
            pass

    def refresh_recent_menu(self):
        """(re)feed the 'Open Recent' menu, at click time"""
        self.menu_recent.delete(0, "end")
        for recent in self.recent_dbs():
            if os.path.isfile(recent):
                self.menu_recent.add_command(
                    label=recent, command=lambda r=recent: self.open_db(r)
                )

    def show_duckdb_demo(self):
        """open the DuckDB welcome demo tab, once, when DuckDB is first used"""
        if (
            self.use_gui
            and self.conn.engine == "duckdb"
            and not self.duckdb_demo_shown
        ):
            self.duckdb_demo_shown = True
            self.n.new_query_tab("DuckDB Memo", WELCOME_DUCKDB)

    def backup_db(self, filename="", isolation_level=None):
        """Backup the current database"""
        if filename == "":
            filename = filedialog.asksaveasfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                title="Define a new database name and location",
                filetypes=[("default", "*.db"), ("other", "*.db*"), ("all", "*.*")],
            )
        if filename != "":
            if os.path.isfile(filename):
                self.set_initialdir(filename)
                if messagebox.askyesno(
                    message="Confirm Destruction of previous Datas ?",
                    icon="question",
                    title="Destroying",
                ):
                    os.remove(filename)
                    self.backup_main_to(filename)
                    self.actualize_db()

    def restore_db(self, filename="", isolation_level=None):
        """Restore an existing database into current one"""
        if filename == "":
            filename = filedialog.askopenfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                filetypes=[("default", "*.db"), ("other", "*.db*"), ("all", "*.*")],
            )
        if filename != "":
            self.restore_main_from(filename)
            self.actualize_db()

    def backup_main_to(self, filename):
        """backup the current 'main' database to filename, per engine"""
        if self.conn.engine == "duckdb":
            current = self.conn.execute("select current_database()").fetchall()[0][0]
            self.conn.execute(
                "ATTACH '%s' AS bro_backup" % filename.replace("'", "''")
            )
            self.conn.execute(
                'COPY FROM DATABASE "%s" TO bro_backup' % current.replace('"', '""')
            )
            self.conn.execute("DETACH bro_backup")
        else:
            db_to = sqlite.connect(filename)
            self.conn.conn.backup(db_to)
            db_to.close()

    def restore_main_from(self, filename):
        """restore the database in filename into current 'main', per engine"""
        if self.conn.engine == "duckdb":
            current = self.conn.execute("select current_database()").fetchall()[0][0]
            self.conn.execute(
                "ATTACH '%s' AS bro_restore (READ_ONLY)" % filename.replace("'", "''")
            )
            self.conn.execute(
                'COPY FROM DATABASE bro_restore TO "%s"' % current.replace('"', '""')
            )
            self.conn.execute("DETACH bro_restore")
        else:
            db_from = sqlite.connect(filename)
            db_from.backup(self.conn.conn)
            db_from.close

    def load_script(self):
        """load a script file, ask validation of detected Python code"""
        filename = filedialog.askopenfilename(
            initialdir=self.initialdir,
            defaultextension=".sql",
            filetypes=[("default", "*.sql"), ("other", "*.txt"), ("all", "*.*")],
        )
        if filename != "":
            self.set_initialdir(filename)
            text = os.path.split(filename)[1].split(".")[0]
            with open(filename, encoding=guess_encoding(filename)[0]) as f:
                script = f.read()
                sqls = self.conn.get_sqlsplit(script, remove_comments=True)
                dg = [
                    s
                    for s in sqls
                    if s.strip(" \t\n\r")[:5].lower() in ("pydef", ".read", ".shel")
                    or s.strip(" \t\n\r")[:1].lower() == "."
                ]
                if dg:
                    fields = [
                        "",
                        ["In Script File:", filename, "r", 100],
                        "",
                        ["non pure SQL code", "\n".join(dg), "r", 80, 20],
                    ]

                    create_dialog(
                        ("Ok for this non pure SQL code ?"),
                        fields,
                        ("Confirm", self.load_script_ok),
                        [text, script],
                    )
                else:
                    new_tab_ref = self.n.new_query_tab(text, script)

    def load_script_ok(self, thetop, entries, actions):
        """continue loading of script after confirmation dialog"""
        new_tab_ref = self.n.new_query_tab(*actions)
        thetop.destroy()

    def savdb_script(self):
        """save database as a script file"""
        filename = filedialog.asksaveasfilename(
            initialdir=self.initialdir,
            defaultextension=".db",
            title="save database structure in a text file",
            filetypes=[("default", "*.sql"), ("other", "*.txt"), ("all", "*.*")],
        )
        if filename != "":
            self.set_initialdir(filename)
            with open(filename, "w", encoding="utf-8") as f:
                for line in self.conn.iterdump():
                    f.write("%s\n" % line)

    def sav_script(self):
        """save a script in a file"""
        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":  # the whole script, never just a selection
            fw = self.n.fw_labels[active_tab_id]
            script = fw.get(1.0, END)[:-1]
            filename = filedialog.asksaveasfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                title="save script in a sql file",
                filetypes=[("default", "*.sql"), ("other", "*.txt"), ("all", "*.*")],
            )
        if filename != "":
            self.set_initialdir(filename)
            with open(filename, "w", encoding="utf-8") as f:
                if "你好 мир Artisou à croute" not in script:
                    f.write("/*utf-8 tag : 你好 мир Artisou à croute*/\n")
                f.write(script)

    def attach_db(self, filename="", attach_as=""):
        """attach an existing database"""
        if filename == "":
            filename = filedialog.askopenfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                title="Choose a database to attach ",
                filetypes=[
                    ("default", "*.db"),
                    ("duckdb", "*.duckdb"),
                    ("other", "*.db*"),
                    ("all", "*.*"),
                ],
            )
        if attach_as == "":
            attach = os.path.basename(filename).split(".")[0]
        else:
            attach = attach_as
        avoid = {i[1]: 0 for i in get_leaves(self.conn, "attached_databases")}
        att, indice = attach, 0
        while attach in avoid:
            attach, indice = att + "_" + str(indice), indice + 1
        if filename != "":
            self.set_initialdir(filename)
            # double-quoted alias : accepted by both SQLite and DuckDB
            attach_order = 'ATTACH DATABASE \'%s\' as "%s" ' % (filename, attach)
            self.conn.execute(attach_order)
            self.actualize_db()

    def close_db(self):
        """close the database"""
        self.conn.close()
        self.new_db(":memory:")
        self.actualize_db()

    def actualize_db(self):
        """refresh the database view"""
        if not self.use_gui:
            return

        # bind double-click for easy user interaction
        self.db_tree.tag_bind("run", "<Double-1>", self.t_doubleClicked)
        self.db_tree.tag_bind("run_up", "<Double-1>", self.t_doubleClicked)

        # delete existing tree entries before re-creating them
        for node in self.db_tree.get_children():
            self.db_tree.delete(node)
        # create top node, showing the engine driving the main database
        engine_label = (
            "DuckDB" if getattr(self.conn, "engine", "sqlite") == "duckdb"
            else "SQLite"
        )
        dbtext = os.path.basename(self.database_file)
        self.tk_win.title(
            "A graphic SQLite and DuckDB Client in 1 Python file (%s) - %s %s"
            % (self.__version__, engine_label, self.database_file)
        )
        id0 = self.db_tree.insert(
            "",
            0,
            "Database",
            text="main (%s %s)" % (engine_label, dbtext),
            values=(dbtext, ""),
        )
        # add Database Objects, by Category
        for categ in ["master_table", "table", "view", "trigger", "index", "pydef"]:
            self.feed_dbtree(id0, categ, "main")
        # for attached databases
        for att_db in self.feed_dbtree(id0, "attached_databases"):
            # create another top node
            dbtext2, insert_position = att_db + " (Attached)", "end"
            if att_db == "temp":
                dbtext2, insert_position = "temp (%s)" % dbtext, 0
            id0 = self.db_tree.insert(
                "", insert_position, dbtext2, text=dbtext2, values=(att_db, "")
            )
            # add attached Database Objects, by Category
            for categ in ["master_table", "table", "view", "trigger", "index"]:
                self.feed_dbtree(id0, categ, att_db)
        # update time of last refresh
        self.db_tree.heading(
            "#0", text=(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        )

    def quit_db(self):
        """quit the application"""
        if messagebox.askyesno(
            message="Are you sure you want to quit ?", icon="question", title="Quiting"
        ):
            self.tk_win.destroy()

    def run_tab(self, event=None):
        """clear previous results and run current script of a tab"""
        if not self.use_gui:
            active_tab_id = len(self.n.nongui_tabs) - 1
            script = self.n.nongui_tabs[active_tab_id]
            # print ("silently running,active_tab_id , script)  # testtmp
            self.create_and_add_results(script, active_tab_id)
            return
        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":
            # remove previous results
            self.n.remove_treeviews(active_tab_id)
            fw, script = self.tab_script(active_tab_id)
            self.create_and_add_results(script, active_tab_id)
            fw.focus_set()  # workaround bug http://bugs.python.org/issue17511

    def tab_script(self, active_tab_id):
        """return a tab text widget and its script : the selection, or all"""
        fw = self.n.fw_labels[active_tab_id]
        try:
            return fw, fw.get("sel.first", "sel.last")
        except TclError:  # nothing selected
            return fw, fw.get(1.0, END)[:-1]

    def exsav_script(self):
        """write script commands + top results to a log file"""
        # idea from http://blog.mp933.fr/post/2014/05/15/Script-vs.-copy/paste
        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":  # the whole script, never just a selection
            fw = self.n.fw_labels[active_tab_id]
            script = fw.get(1.0, END)[:-1]
            filename = filedialog.asksaveasfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                title="execute Script + output in a log file",
                filetypes=[("default", "*.txt"), ("other", "*.log"), ("all", "*.*")],
            )
        if filename == "":
            return
        self.set_initialdir(filename)
        with open(filename, "w", encoding="utf-8") as f:
            if "你好 мир Artisou à croute" not in script:
                f.write("/*utf-8 tag : 你好 мир Artisou à croute*/\n")
            self.create_and_add_results(script, active_tab_id, limit=99, log=f)
            fw.focus_set()  # workaround bug http://bugs.python.org/issue17511

    def run_temp(self):
        """run selected script commands and display results via tmp files"""
        keep = {k: getattr(self, k) for k in OUTPUT_STATE}  # backup defaults
        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":
            self.n.remove_treeviews(active_tab_id)
            fw, query = self.tab_script(active_tab_id)
            self.create_and_add_results(
                "\n.output --bom -x \n" + query, active_tab_id
            )
            fw.focus_set()  # workaround bug http://bugs.python.org/issue17511
        for k, v in keep.items():  # restore previous defaults
            setattr(self, k, v)

    def chg_fontsize(self):
        """change the display font size"""
        sizes = [10, 13, 14]
        font_types = [
            "TkDefaultFont",
            "TkTextFont",
            "TkFixedFont",
            "TkMenuFont",
            "TkHeadingFont",
            "TkCaptionFont",
            "TkSmallCaptionFont",
            "TkIconFont",
            "TkTooltipFont",
        ]
        ww = ["normal", "bold"]
        if self.font_size < max(sizes):
            self.font_size = min([i for i in sizes if i > self.font_size])
        else:
            self.font_size = sizes[0]
            self.font_wheight = 0

        ff = "Helvetica" if self.font_size != min(sizes) else "Courier"
        self.font_wheight = 0 if self.font_size == min(sizes) else 1
        for typ in font_types:
            default_font = font.nametofont(typ)
            default_font.configure(
                size=self.font_size, weight=ww[self.font_wheight], family=ff
            )
        self.set_treeview_rowheight()

    def set_treeview_rowheight(self):
        """fit ttk.Treeview row height to the current font (size and dpi)"""
        linespace = font.nametofont("TkDefaultFont").metrics("linespace")
        ttk.Style().configure("Treeview", rowheight=linespace + 4)

    def run_tab_event(self, event=None):
        """Ctrl+Enter : run the script (without inserting a line return)"""
        self.run_tab()
        return "break"

    def statement_at(self, script, offset):
        """return the sql statement of 'script' the character 'offset' is in"""
        # get_sqlsplit can drop a separator (a shell order loses what precedes
        # it), so locate each piece in the script instead of adding up lengths
        pos, found = 0, ""
        for statement in self.conn.get_sqlsplit(script, remove_comments=False):
            start = script.find(statement, pos)
            if start < 0:
                break
            pos = start + len(statement)
            if statement.strip():
                found = statement
            if offset <= pos:  # a cursor right after ';' means that statement
                break
        return found

    def run_statement(self, event=None):
        """Ctrl+Shift+Enter (or F5) : run only the statement the cursor is in"""
        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":
            fw = self.n.fw_labels[active_tab_id]
            statement = self.statement_at(
                fw.get(1.0, END)[:-1], len(fw.get(1.0, INSERT))
            )
            if statement.strip():
                self.n.remove_treeviews(active_tab_id)
                self.create_and_add_results(statement, active_tab_id)
            fw.focus_set()  # workaround bug http://bugs.python.org/issue17511
        return "break"

    def zoom_font(self, event=None):
        """Ctrl+mousewheel : change the font size one point at a time"""
        up = getattr(event, "num", 0) == 4 or getattr(event, "delta", 0) > 0
        new_size = max(6, min(32, self.font_size + (1 if up else -1)))
        if new_size != self.font_size:
            self.font_size = new_size
            for typ in font.names(self.tk_win):
                if str(typ).startswith("Tk"):
                    font.nametofont(typ).configure(size=self.font_size)
            self.set_treeview_rowheight()
        return "break"

    def clean_temp(self):
        """clear temp directory"""
        ff = tmpf.NamedTemporaryFile(delete=True, suffix="_sqlite_bro.csv").name
        temp_directory = os.path.dirname(ff)
        report = [
            ("", ""),
        ]
        for file in os.listdir(temp_directory):
            if file.endswith("_sqlite_bro.csv"):
                print("removing ", os.path.join(temp_directory, file))
                try:
                    os.remove(os.path.join(temp_directory, file))
                    report += [
                        ("removing ", os.path.join(temp_directory, file)),
                    ]
                except PermissionError:
                    report += [
                        ("PermissionError ", os.path.join(temp_directory, file)),
                    ]

        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":
            self.n.remove_treeviews(active_tab_id)
        self.n.add_treeview(
            active_tab_id,
            ("Cleanup", "file"),
            (report),
            "Cleanup",
            ".Cleaning tmp files",
        )

    def t_doubleClicked(self, event):
        """launch action when dbl_click on the Database structure"""
        # determine item to consider
        selitem = self.db_tree.focus()  # the item having the focus
        seltag = self.db_tree.item(selitem, "tag")[0]
        if seltag == "run_up":  # 'run-up' tag ==> dbl-click 1 level up
            selitem = self.db_tree.parent(selitem)
        # get final information : text, selection and action
        definition, action = self.db_tree.item(selitem, "values")
        tab_text = self.db_tree.item(selitem, "text")
        script = action + " limit 999 " if action != "" else definition

        # create a new tab and run it if action suggest it
        new_tab_ref = self.n.new_query_tab(tab_text, script)
        if action != "":
            self.run_tab()  # run the new_tab created

    def get_tk_icons(self):
        """return a dictionary of icon_in_tk_format, from B64 images"""
        # to create this base 64 from a toto.gif image of 24x24 size do :
        #    import base64
        #    b64 = base64.encodestring(open(r"toto.gif","rb").read())
        #    print("'gif_img': '''\\\n" + b64.decode("utf8") + "'''")
        icons = {
            "run_img": """\
R0lGODdhGAAYAJkAADOqM////wCqMwAAACwAAAAAGAAYAAACM4SPqcvt7wJ8oU5W8025b9OFW0hO
5EmdKKauSosKL9zJC21FsK27kG+qfUC5IciITConBQA7
""",
            "exe_img": """\
R0lGODdhGAAYALsAAP///zOqM/8AAGSJtqHA4Jyen3ul0+jo6Y6z2cLCwaSmpACqM4ODgmKGs4yM
jYOPniwAAAAAGAAYAAAEhBDISacqOBdWOy1HKB6F41mKwihH4r4kdypD0wx4rg8nUDSEoHAY5J0K
AyFiyWQaPY+kYUqtGp4dx26b60kE4LC3FwaPyeJOYM1ur8sCzxrgZovN6sDEHdYD4nkVb2BzPYUV
hIdyfouMi14BC5COgoqBHQttk5VumxJ1bJuZoJacpKE9EQA7
""",
            "refresh_img": """\
R0lGODdhGAAYAJkAAP///zOqMwCqMwAAACwAAAAAGAAYAAACSoSPqcvt4aIJEFU5g7AUC9px1/JR
3yYy4LqAils2IZdFMzCP6nhLd2/j6VqHD+1RAQKLHVfC+VwtcT3pNKOTYjTC4SOK+YbH5EYBADs=
""",
            "newtab_img": """\
R0lGODdhGAAYAJkAAP///56fnQAAAAAAACwAAAAAGAAYAAACSoSPqcsm36KDsj1R1d00840E4ige
3xWSo9YppRGwGKPGCUrXtfQCut3ouYC5IHEhTCSHRt4x5fytIlKSs0l9HpZKLcy7BXOhRUYBADs=
""",
            "csvin_img": """\
R0lGODdhGAAYAMwAAPj4+AAAADOqM2FkZtjY2r7Awujo6V9gYeDg4b/Cwzc3N0pKSl9fX5GRkVVV
VXl6fKSmpLCxsouNkFdXV97d4N7e4N7g4IyMjZyen6SopwAAAAAAAAAAAAAAAAAAAAAAACwAAAAA
GAAYAAAFlSAgjkBgmuUZlONKii4br/MLv/Gt47ia/rYcT2bb0VowVFFF8+2K0KjUJqhOo1XBlaQV
Zbdc7Rc8ylrJ5THaa5YqFozBgOFQAMznl6FhsO37UwMEBgiFFRYIhANXBxgJBQUJkpAZi1MEBxAR
kI8REAMUVxIEcgcDpqYEElcODwSvsK8PllMLAxeQkA0DDmhvEwwLdmAhADs=
""",
            "csvex_img": """\
R0lGODdhGAAYAMwAAPj4+AAAADOqM2FkZtjY2r7AwuDg4b/Cw+jo6V9gYTc3N0pKSlVVVV9fX5GR
kaSmpLCxsnl6fIuNkN7g4N7d4KSop4yMjZyen1dXVwAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAA
GAAYAAAFkiAgjkBgmuUZlONKii4br/MLv/Gt47ia/rYcT2bb0VowVFFF8+2K0Kh0JKhap1BrFZu9
cl9awZd03Y4BXvQ5DVUsGoNBg6FAm6MOhA3h4A4ICAaCBhOCCANYAxcHBQUHj44ViFMECQ8QjY0Q
DwMUWBIEcQkDo6MEElgMEQSsrawRk1MLAxaZBQ4DDGduGA0LdV8hADs=
""",
            "qryex_img": """\
R0lGODdhGAAYAJkAAP///56fnQAAAP8AACwAAAAAGAAYAAACXIQPoporeR4yEtZ3J511e845zah1
oKV9WEQxqYOJX0rX9oDndp3jO6/7aXqDVOCIPB50Pk0yaQgCijSlITBt/p4B6ZbL3VkBYKxt7DTX
0BN2uowUw+NndVq+tk8KADs=
""",
            "clipin_img": """\
R0lGODdhGAAYAIQAAP////j4+Ojo6eDg4d7g4N7e4N7d4NjY2r/Cw77AwrCxsjOqM6Sop6SmpJye
n5GRkYuNkIyMjXl6fJZvM2FkZmBgYV9gYV9fX1dXV1VVVUpKSjc3NwAAAAAAAAAAAAAAACwAAAAA
GAAYAEAIyAADCBxIsKDACggNKiQ4YQIABQsiKgDQUMADAQYtYlzIUWFDACBDUpzQMUDEkw1TpoRw
gAIFCy9jHoBQsqbAlAhzprT5UYFPnyNLLgjQ8OTPoB1PRlxowQGCBAkQSIXKgIJNDRQiQIX6gEIG
m2AP5qwQlihOhCp5OhQJsqFatm1J1vx4MqTbpAsm1BV5l6PSkhs0XHB5IcMGk0qHGqRwQMCAxwUI
DHBsteQBCw0UQH2qoAEFAzUzSDhAujRpCZVtBsZwQcNhsAEBADs=
""",
            "clipex_img": """\
R0lGODdhGAAYAIQAAP////j4+Ojo6eDg4d7g4N7d4NjY2r/Cw77AwrCxsjOqM6Sop6SmpJyen5GR
kYuNkIyMjXl6fJZvM2FkZmBgYV9gYV9fX1dXV1VVVUpKSjc3NwAAAAAAAAAAAAAAAAAAACwAAAAA
GAAYAEAIxQADCBxIsKBACggNKiQoQYHDhwkASJAQwIEAgwIcLNy4cSKAjyAlUuQosKFDkxMnPjAw
YUIFlzANPCBJs+REhDhT1vSYoGdPkTslAHioIOJEmg83TmhwAAGCA1CfLphQM8MECE6dOpiAoabX
gzgpfA2QUkJOnTQ9hvx4NK3QtUDdElXAdiTJiXPr1kwqUIFBDRkstLSAQUMAvgsnGBAwoPEAAo0F
UCVpoAKDBFkRJGAwoQBNDBEMiB4tOsLkmoAvWMhg2GtAADs=
""",
            "qryclip_img": """\
R0lGODdhGAAYAIIAAP///7Cxsp6fnZZvM2BgYf8AAAAAAAAAACwAAAAAGAAYAEAIjAABCBxIsKBA
AggNKhRYoKHDABADABgwQKCAixgzYlzIsSBFhRQ1bmTYcEBJiigrAhDJsqPLiRQRykT5EiaAiBBt
vnRYAKfOiwMFAHBYc6XQhUA7JlW4tOhCmQmdppxJ8+VHg1ddZiW4dSHPAh5VdvwaluVRogw5ikRb
c6lZl00LxjVoViPco0zxGgwIADs=
""",
            "sqlin_img": """\
R0lGODdhGAAYALsAAP///46z2Xul02SJtp6fnenp6f8AAMLCwaHA4IODgoCo01RymIOPnmKGswAA
AAAAACwAAAAAGAAYAAAEkRDIOYm9N9G9SfngR2jclnhHqh7FWAKZF84uFxdr3pb3TPOWEy6n2tkG
jcZgyWw6OwOEdEqtIgYbRjTA7Xq/WIoW8S17wxOteR1AS9Ts8sI08Aru+Px9TknU9YB5fBN+AYGH
gxJ+dwoCjY+OCpKNiQAGBk6ZTgsGE5edLy+XlqOhop+gpiWoqqGoqa0Ur7CxABEAOw==
""",
            "sqlsav_img": """\
R0lGODdhGAAYALsAAP///56fnZyen/8AAGSJtqHA4Hul0+jo6Y6z2cLCwaSmpIODgmKGs4yMjYOP
ngAAACwAAAAAGAAYAAAEgxDISacSOItVOxVHKB5C41mKsihH4r4kdyoEwxB4rhMnIDCFoHAY5J0E
BCFiyWQaPY6kYUqtGp6dxm6b60kG4LC3FwaPyeJzpzzwaDQTsbkTqNsx3zmgfapPAnt6Y3Z1Amlq
AoR3cF5+EoqFY4k9jpSAfQKSkJCDm4SZXpN9l5aUoB4RADs=
""",
            "dbdef_img": """\
R0lGODdhGAAYAMwAAPj4+DOqM2SJtmFkZqHA4NjY2sLCwejo6b7Awpyen3ul046z2aSop+Dg4V9g
YZGRkaSmpLCxsouNkDc3N2dxekpKSlVVVYyMjWKGs4ODgnl6fJ+goYOPnl9fX0xMTAAAACwAAAAA
GAAYAAAFuCAgjuTIJGiaZGVLJkcsH9DjmgxzMYfh/4fVDcAQCDDGpNI4hGAI0KgUKhgmBNGFdrut
3jhYhXhMVnhdj6U6OWy7h4G4/O2Sx+n1OZ5kD5QmHgOCAx0TJXN3Iw8HLQc2InoAfiIUBQcNmJkN
BxSSiS0DCT8/CAYMA55DBQMQEQilCBGndBKrAw4Ot7kFEm+rG66vsRCob7WCube3vG8WGgXQ0dAa
nW8VAxfCCA8DFnsAExUWAxWGeCEAOw==
""",
            "chgsz_img": """\
R0lGODdhGAAYAJkAAP///wAAADOqMwCqMywAAAAAGAAYAAACZISPGRvpb1iDRjy5KBBWYc0NXjQ9
A8cdDFkiZyiIwDpnCYqzCF2lr2rTHVKbDgsTJG52yE8R0nRSJA7qNOhpVbFPHhdhPF20w46S+f2h
xlzceksqu6ET7JwtLRrhwNt+1HdDUQAAOw==
""",
            "img_close": """\
R0lGODlhCAAIAMIBAAAAADs7O4+Pj9nZ2Ts7Ozs7Ozs7Ozs7OyH+EUNyZWF0ZWQgd2l0aCBHSU1Q
ACH5BAEKAAQALAAAAAAIAAgAAAMVGDBEA0qNJyGw7AmxmuaZhWEU5kEJADs=
""",
            "img_closeactive": """\
R0lGODlhCAAIAMIEAAAAAP/SAP/bNNnZ2cbGxsbGxsbGxsbGxiH5BAEKAAQALAAAAAAIAAgAAAMV
GDBEA0qNJyGw7AmxmuaZhWEU5kEJADs=
""",
            "img_closepressed": """\
R0lGODdhCAAIAIgAAPAAAP///ywAAAAACAAIAAACDkyAeJYM7FR8Ex7aVpIFADs=
""",
            "img_run_temp": """\
R0lGODlhGAAYAHAAACH5BAEAAPwALAAAAAAYABgAhwAAAAAAMwAAZgAAmQAAzAAA/wArAAArMwAr
ZgArmQArzAAr/wBVAABVMwBVZgBVmQBVzABV/wCAAACAMwCAZgCAmQCAzACA/wCqAACqMwCqZgCq
mQCqzACq/wDVAADVMwDVZgDVmQDVzADV/wD/AAD/MwD/ZgD/mQD/zAD//zMAADMAMzMAZjMAmTMA
zDMA/zMrADMrMzMrZjMrmTMrzDMr/zNVADNVMzNVZjNVmTNVzDNV/zOAADOAMzOAZjOAmTOAzDOA
/zOqADOqMzOqZjOqmTOqzDOq/zPVADPVMzPVZjPVmTPVzDPV/zP/ADP/MzP/ZjP/mTP/zDP//2YA
AGYAM2YAZmYAmWYAzGYA/2YrAGYrM2YrZmYrmWYrzGYr/2ZVAGZVM2ZVZmZVmWZVzGZV/2aAAGaA
M2aAZmaAmWaAzGaA/2aqAGaqM2aqZmaqmWaqzGaq/2bVAGbVM2bVZmbVmWbVzGbV/2b/AGb/M2b/
Zmb/mWb/zGb//5kAAJkAM5kAZpkAmZkAzJkA/5krAJkrM5krZpkrmZkrzJkr/5lVAJlVM5lVZplV
mZlVzJlV/5mAAJmAM5mAZpmAmZmAzJmA/5mqAJmqM5mqZpmqmZmqzJmq/5nVAJnVM5nVZpnVmZnV
zJnV/5n/AJn/M5n/Zpn/mZn/zJn//8wAAMwAM8wAZswAmcwAzMwA/8wrAMwrM8wrZswrmcwrzMwr
/8xVAMxVM8xVZsxVmcxVzMxV/8yAAMyAM8yAZsyAmcyAzMyA/8yqAMyqM8yqZsyqmcyqzMyq/8zV
AMzVM8zVZszVmczVzMzV/8z/AMz/M8z/Zsz/mcz/zMz///8AAP8AM/8AZv8Amf8AzP8A//8rAP8r
M/8rZv8rmf8rzP8r//9VAP9VM/9VZv9Vmf9VzP9V//+AAP+AM/+AZv+Amf+AzP+A//+qAP+qM/+q
Zv+qmf+qzP+q///VAP/VM//VZv/Vmf/VzP/V////AP//M///Zv//mf//zP///wAAAAAAAAAAAAAA
AAiFAPcJHEiwoMGDBgEo3Lew4UKEAwEIlEiRoUWIEzNmVCgR48WOFkF6HEmypMmTAqWpXIky5Upp
LV2qPDikps2bNV/CNFhzH06bLGfyHDIQ50qi+3YW7Ekwp0qmCKESfCp1KESbGKsuzZAVKU2tTb0S
zHCz69KfZgX+xHqVKNm1YIvCxYkxIAA7
""",
            "img_clean_temp": """\
R0lGODlhGAAYAHAAACwAAAAAGAAYAIcAAAAAADMAAGYAAJkAAMwAAP8AKwAAKzMAK2YAK5kAK8wA
K/8AVQAAVTMAVWYAVZkAVcwAVf8AgAAAgDMAgGYAgJkAgMwAgP8AqgAAqjMAqmYAqpkAqswAqv8A
1QAA1TMA1WYA1ZkA1cwA1f8A/wAA/zMA/2YA/5kA/8wA//8zAAAzADMzAGYzAJkzAMwzAP8zKwAz
KzMzK2YzK5kzK8wzK/8zVQAzVTMzVWYzVZkzVcwzVf8zgAAzgDMzgGYzgJkzgMwzgP8zqgAzqjMz
qmYzqpkzqswzqv8z1QAz1TMz1WYz1Zkz1cwz1f8z/wAz/zMz/2Yz/5kz/8wz//9mAABmADNmAGZm
AJlmAMxmAP9mKwBmKzNmK2ZmK5lmK8xmK/9mVQBmVTNmVWZmVZlmVcxmVf9mgABmgDNmgGZmgJlm
gMxmgP9mqgBmqjNmqmZmqplmqsxmqv9m1QBm1TNm1WZm1Zlm1cxm1f9m/wBm/zNm/2Zm/5lm/8xm
//+ZAACZADOZAGaZAJmZAMyZAP+ZKwCZKzOZK2aZK5mZK8yZK/+ZVQCZVTOZVWaZVZmZVcyZVf+Z
gACZgDOZgGaZgJmZgMyZgP+ZqgCZqjOZqmaZqpmZqsyZqv+Z1QCZ1TOZ1WaZ1ZmZ1cyZ1f+Z/wCZ
/zOZ/2aZ/5mZ/8yZ///MAADMADPMAGbMAJnMAMzMAP/MKwDMKzPMK2bMK5nMK8zMK//MVQDMVTPM
VWbMVZnMVczMVf/MgADMgDPMgGbMgJnMgMzMgP/MqgDMqjPMqmbMqpnMqszMqv/M1QDM1TPM1WbM
1ZnM1czM1f/M/wDM/zPM/2bM/5nM/8zM////AAD/ADP/AGb/AJn/AMz/AP//KwD/KzP/K2b/K5n/
K8z/K///VQD/VTP/VWb/VZn/Vcz/Vf//gAD/gDP/gGb/gJn/gMz/gP//qgD/qjP/qmb/qpn/qsz/
qv//1QD/1TP/1Wb/1Zn/1cz/1f///wD//zP//2b//5n//8z///8AAAAAAAAAAAAAAAAIcwD3CRxI
sKDBgwgTKlzIUKEfPw0T/qES8SAViBUL+vmTsSAVjh0H/sEYct/FkgJHojRJMqM9P1VaVrRHE2ZJ
e/BqhsRJM2fHnD1puuQJ9GdQewIBKN23tOnSfTR5FgSQlKlVqlQXZs169anCrQOxrhyLMCAAOw==
""",
        }
        num, den = self.icon_zoom
        if (num, den) == (1, 1):
            return {k: PhotoImage(k, data=v) for k, v in icons.items()}
        # high-dpi: enlarge the bitmap icons by num/den to follow the screen
        # scale, into images keeping the same tk names ("run_img", ...)
        scaled = {}
        for k, v in icons.items():
            raw = PhotoImage(data=v).zoom(num)
            img = PhotoImage(k)
            img.tk.call(img, "copy", raw, "-subsample", den, den)
            scaled[k] = img
        return scaled

    def btn_chg_tab_ok(self, thetop, entries, actions):
        """chg a tab title"""
        widget, index = actions
        # build dico of result
        d = {f[0]: f[1]() for f in entries if not isinstance(f, str)}

        title = d["new label"].strip()
        thetop.destroy()
        widget.tab(index, text=title)

    def btn_presstwice(self, event):
        """double-click on a tab definition to change label"""
        x, y, widget = event.x, event.y, event.widget
        elem = widget.identify(x, y)
        try:
            index = widget.index("@%d,%d" % (x, y))
        except TclError:
            return  # click landed outside any tab label
        titre = widget.tab(index, "text")
        # determine selected table
        actions = [widget, index]
        title = "Changing Tab label"
        fields = [
            "",
            ["current label", (titre), "r", 30],
            "",
            ["new label", titre, "w", 30],
        ]
        create_dialog(title, fields, ("Ok", self.btn_chg_tab_ok), actions)

    def btn_press(self, event):
        """button press over a widget with a 'close' element"""
        x, y, widget = event.x, event.y, event.widget
        elem = widget.identify(x, y)  # widget is the notebook
        try:
            index = widget.index("@%d,%d" % (x, y))
        except TclError:
            return  # click landed outside any tab label
        if "close" in elem:  # close button function
            widget.state(["pressed"])
            widget.pressed_index = index
        else:  # move function
            self.state_drag = True
            self.state_drag_widgetid = widget.tabs()[index]
            self.state_drag_index = index

    def btn_Movex(self, event):
        """make the tab follows if button is pressed and mouse moves"""
        x, y, widget = event.x, event.y, event.widget
        elem = widget.identify(x, y)
        try:
            index = widget.index("@%d,%d" % (x, y))
        except TclError:
            return  # cursor is outside any tab label
        if self.state_drag:
            if self.state_drag_index != index:
                self.btn_Move(widget, self.state_drag_index, index)
                self.state_drag_index = index

    def btn_Move(self, notebook, old_index, new_index):
        """Move old_index tab to new_index position"""
        # stackoverflow.com/questions/11570786/tkinter-treeview-drag-and-drop
        if new_index != "":
            target_index = new_index
            if new_index >= len(notebook.tabs()) - 1:
                target_index = "end"
            titre = notebook.tab(old_index, "text")
            notebook.forget(old_index)
            notebook.insert(target_index, self.state_drag_widgetid, text=titre)
            notebook.select(new_index)

    def btn_release(self, event):
        """button release over a widget with a 'close' element"""
        x, y, widget = event.x, event.y, event.widget
        elem = widget.identify(x, y)
        index = self.state_drag_index
        if "close" in elem or "label" in elem:
            try:
                index = widget.index("@%d,%d" % (x, y))
            except TclError:
                return  # release landed outside any tab label
        if "close" in elem and widget.instate(["pressed"]):
            if widget.pressed_index == index:
                widget.forget(index)
                widget.event_generate("<<NotebookClosedTab>>")
        if self.state_drag and elem.strip() != "":
            if self.state_drag_index != index:
                self.btn_Move(widget, self.state_drag_index, index)
        self.state_drag = False

        if not widget.instate(["pressed"]):
            return
        widget.state(["!pressed"])
        widget.pressed_index = None

    def create_style(self):
        """create a Notebook style with close button"""
        # from https://github.com/python-git/python/blob/master/Demo/tkinter/
        #             ttk/notebook_closebtn.py
        # himself from http://paste.tclers.tk/896
        style = ttk.Style()

        style.element_create(
            "close",
            "image",
            "img_close",
            ("active", "pressed", "!disabled", "img_closepressed"),
            ("active", "!disabled", "img_closeactive"),
            border=6,
            sticky="",
        )

        style.layout("ButtonNotebook", [("ButtonNotebook.client", {"sticky": "nswe"})])
        # the Tab is a chain tab > padding > focus > (label + close),
        # built inner-most first
        layer = [
            ("ButtonNotebook.label", {"side": "left", "sticky": ""}),
            ("ButtonNotebook.close", {"side": "left", "sticky": ""}),
        ]
        for element in ("focus", "padding", "tab"):
            options = {"sticky": "nswe", "children": layer}
            if element != "tab":
                options["side"] = "top"
            layer = [("ButtonNotebook." + element, options)]
        style.layout("ButtonNotebook.Tab", layer)

        # make the selected tab easy to spot: dimmed labels for the others,
        # a strong color for the current one (tab backgrounds are drawn by
        # the native Windows theme and can't be recolored reliably)
        style.configure("ButtonNotebook.Tab", foreground="#707070")
        style.map(
            "ButtonNotebook.Tab",
            foreground=[("selected", "#000000")],
            background=[("selected", "#ffffff")],  # honored on non-native themes
        )

        self.tk_win.bind_class("TNotebook", "<ButtonPress-1>", self.btn_press, True)
        self.tk_win.bind_class("TNotebook", "<ButtonRelease-1>", self.btn_release)
        self.tk_win.bind_class("TNotebook", "<B1-Motion>", self.btn_Movex)
        self.tk_win.bind_class("TNotebook", "<Double-1>", self.btn_presstwice)

    def createToolTip(self, widget, text):
        """create a tooptip box for a widget."""
        # www.daniweb.com/software-development/python/code/234888/tooltip-box
        def enter(event):
            global tipwindow
            x = y = 0
            if tipwindow or not text:
                return
            x, y, cx, cy = widget.bbox("insert")
            x += widget.winfo_rootx() + 27
            y += widget.winfo_rooty() + 27
            # Creates a toplevel window
            tipwindow = tw = Toplevel(widget)
            # Leaves only the label and removes the app window
            tw.wm_overrideredirect(1)
            tw.wm_geometry("+%d+%d" % (x, y))
            label = Label(
                tw,
                text=text,
                justify=LEFT,
                background="#ffffe0",
                relief=SOLID,
                borderwidth=1,
            )
            label.pack(ipadx=1)

        def close(event):
            global tipwindow
            tw = tipwindow
            tipwindow = None
            if tw:
                tw.destroy()

        widget.bind("<Enter>", enter)
        widget.bind("<Leave>", close)

    def feed_dbtree(self, root_id, category, attached_db=""):
        """feed database treeview for category, return list of leaves names"""

        # prepare re-formatting functions for fields and database names
        def f(t):
            return ('"%s"' % t.replace('"', '""')) if t != "" else t

        def db(t):
            return ('"%s".' % t.replace('"', '""')) if t != "" else t

        attached = db(attached_db)

        # get Category list of [unique_name, name, definition, sub_category]
        tables = get_leaves(self.conn, category, attached_db)
        if len(tables) > 0:
            # level 1 : create  the "category" node (as Category is not empty)
            root_txt = "%s(%s)" % (attached, category)
            idt = self.db_tree.insert(
                root_id,
                "end",
                root_txt,
                text="%s (%s)" % (category, len(tables)),
                values=("", ""),
            )
            for t_id, t_name, definition, sub_cat in tables:
                # level 2 : print object creation, and '(Definition)' if fields
                sql3 = ""
                if sub_cat != "":
                    # it's a table : prepare a Query with names of each column
                    sub_c = get_leaves(self.conn, sub_cat, attached_db, t_name)
                    colnames = [col[1] for col in sub_c]
                    columns = [col[1] + " " + col[2] for col in sub_c]
                    sql3 = (
                        'select "'
                        + '" , "'.join(colnames)
                        + '" from '
                        + ("%s%s" % (attached, f(t_name)))
                    )
                idc = self.db_tree.insert(
                    idt,
                    "end",
                    "%s%s" % (root_txt, t_id),
                    text=t_name,
                    tags=("run",),
                    values=(definition, sql3),
                )
                if sql3 != "":
                    self.db_tree.insert(
                        idc,
                        "end",
                        ("%s%s;d" % (root_txt, t_id)),
                        text=["(Definition)"],
                        tags=("run",),
                        values=(definition, ""),
                    )
                    # level 3 : Insert a line per column of the Table/View
                    # (id by column position : DuckDB views may repeat names)
                    for c in range(len(sub_c)):
                        self.db_tree.insert(
                            idc,
                            "end",
                            "%s%s.%s" % (root_txt, t_id, c),
                            text=columns[c],
                            tags=("run_up",),
                            values=("", ""),
                        )
        return [i[1] for i in tables]

    def clean_path(self, filename):
        """unquote a dot-command file name, and expand a leading '~'"""
        filename = filename.strip("'").strip('"')
        if filename.startswith("~"):
            filename = os.path.join(self.home, filename[1:])
        return filename

    def run_dotcommand(self, instru, tab_tk_id, log=None):
        """run one sqlite3-cli style '.command' : return (message, status)"""
        shell_list = shlex.split(instru, posix=False)  # magic standard library
        cmd, args, result = shell_list[0], shell_list[1:], ""
        if cmd == ".cd" and args:
            new_dir = self.clean_path(args[0])
            if (new_dir + "z")[:2] == "..":
                self.current_directory = os.path.join(self.current_directory, new_dir)
            elif (new_dir + "z")[:1] == ".":
                self.current_directory = os.path.join(
                    self.current_directory, new_dir[1:]
                )
            else:
                self.current_directory = new_dir
        # every dot-command runs from (and refreshes) the current directory
        os.chdir(self.current_directory)
        self.current_directory = os.getcwd()
        if cmd not in DOT_COMMANDS:
            return "unknown command : %s" % cmd, "Error"
        if cmd == ".headers" and args and args[0].lower() in ("on", "off"):
            self.default_header = args[0].lower() == "on"
        elif cmd == ".separator" and args:
            self.default_separator = args[0]
        elif cmd in (".once", ".output", ".excel"):
            if cmd == ".excel":
                cmd, args = ".once", ["--bom", "-x"]
            if cmd == ".once":
                self.once_mode, self.init_output = True, True
            else:
                self.output_mode, self.init_output = True, True
            self.x_mode = False
            self.encode_in = "utf-8-sig" if "--bom" in args else "utf-8"
            self.output_file = self.clean_path(
                args[1] if args[0] == "--bom" else args[0]
            )
            if self.output_file == "-x":
                self.x_mode = True
                self.output_file = temp_csv()
            if self.output_file is None or self.output_file == "":
                self.output_mode, self.init_output = False, False
        elif cmd == ".print":
            if self.output_mode or self.once_mode:
                write_mode = "w" if self.init_output else "a"  # Write or Append
                with open(
                    self.output_file, write_mode, encoding=self.encode_in
                ) as fout:
                    fout.writelines(instru[len(".print") + 1 :] + "\n")
                self.init_output, self.once_mode = False, False
        elif cmd == ".import" and args:
            csv_file = self.clean_path(args[0])
            table = args[1] if len(args) >= 2 else ""
            if csv_file.lower().endswith((".json", ".jsonl", ".ndjson")):
                # the file extension decides the format
                table = table if table else json_table_name(csv_file)
                self.conn.insert_reader(
                    (
                        (json.dumps(r, ensure_ascii=False),)
                        for r in read_this_json(csv_file)
                    ),
                    table,
                    'CREATE TABLE "%s" (json TEXT)' % table,
                    create_table=False,
                    replace=False,
                )
            else:
                guess = guess_csv(csv_file)
                table = table if table else guess.table_name
                # Create csv reader and give it to import
                reading = read_this_csv(
                    csv_file,
                    guess.encodings[0],
                    guess.default_sep,
                    guess.default_quote,
                    guess.has_header,
                    guess.default_decims[0],
                )
                guess_sql = guess_sql_creation(
                    table,
                    guess.default_sep,
                    ".",
                    guess.has_header,
                    guess.dlines,
                    guess.default_quote,
                )[0]
                self.conn.insert_reader(
                    reading, table, guess_sql, create_table=False, replace=False
                )
            result = 'file %s imported in "%s"' % (csv_file, table)
            if log is not None:  # write to logFile
                log.write('-- File %s imported in "%s"\n' % (csv_file, table))
        elif cmd == ".dump":
            if args:
                with open(self.clean_path(args[0]), "w", encoding="utf-8") as f:
                    for line in self.conn.iterdump():
                        f.write("%s\n" % line)
            else:
                self.n.add_treeview(
                    tab_tk_id,
                    ("output"),
                    ([("%s" % line) for line in self.conn.iterdump()]),
                    "Dump",
                    ".dump",
                    position=0,  # keep the "Dump" tab on the left
                )
        elif cmd == ".read" and args:
            filename = self.clean_path(args[0])
            with open(filename, encoding=guess_encoding(filename)[0]) as f:
                self.n.new_query_tab(".Read", f.read())
            self.run_tab()
            self.actualize_db()
        elif cmd == ".open":
            self.conn.close()  # was 'self.close_db' : an attribute, never called
            if args:
                self.open_db(self.clean_path(args[0]))
            else:
                self.new_db(":memory:")
            self.actualize_db()
        elif cmd == ".restore" and args:
            self.restore_main_from(self.clean_path(args[0]))
            self.actualize_db()
        elif cmd == ".backup" and args:
            self.backup_main_to(self.clean_path(args[0]))
        elif cmd == ".shell" and args:
            os.system(instru[len(cmd) + 1 :] + "\n")
        return result, "Ok"

    def create_and_add_results(self, instructions, tab_tk_id, limit=-1, log=None):
        """execute instructions and add them to given tab results"""
        a_jouer = self.conn.get_sqlsplit(instructions, remove_comments=False)
        # must read :https://www.youtube.com/watch?v=09tM18_st4I#t=1751
        # stackoverflow.com/questions/15856976/transactions-with-python-sqlite3
        # DuckDB connections have no isolation_level attribute
        isolation = getattr(self.conn.conn, "isolation_level", None)
        counter = 0
        if isolation == "":  # Sqlite3 and dump.py default don't match
            self.conn.conn.isolation_level = None  # right behavior
        cu = self.conn.conn.cursor()
        sql_error = False
        log_rows = []  # one line per instruction : the "Logs" tab of the run
        grid_count = 0  # number of data grids ("Qry N°x" tabs) displayed

        def add_log(result, status, first_line):
            """record one instruction outcome for the "Logs" tab"""
            timing = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            log_rows.append(
                (len(log_rows) + 1, timing, result, status, first_line)
            )

        def beurk(r):
            """format data line log"""
            s = [
                '"' + s.replace('"', '""') + '"'
                if isinstance(s, str)
                else str(s)
                for s in r
            ]
            return "(" + ",".join(s) + ")"

        def bip(c):
            """format instruction log header"""
            timing = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            return "\n---------N°%s----------[" % counter + timing + "]\n\n"

        for instruction in a_jouer:
            if log is not None:  # write to logFile
                counter += 1
                log.write(bip(counter))
                log.write(instruction)
                log.write("\n")
            instru = next(self.conn.get_sqlsplit(instruction, remove_comments=True))
            instru = instru.replace(";", ";").strip(" \t\n\r")
            first_line = (instru + "\n").splitlines()[0]
            if instru[:5] == "pydef":
                pydef = self.conn.createpydef(instru)
                rows = self.conn.conn_def[pydef]["pydef"].splitlines()
                rows.append(self.conn.conn_def[pydef]["inst"])
                add_log("python function %s created" % pydef, "Ok", first_line)
                if log is not None:  # write to logFile
                    log.write("\n".join(['("%s")' % r for r in rows]) + "\n")
            elif instru[:1] == ".":  # a sqlite3-cli style dot-command
                try:
                    result, status = self.run_dotcommand(
                        instru, tab_tk_id, log
                    )
                    add_log(result, status, first_line)
                    # .open/.restore swap self.conn : the cursor must follow
                    cu = self.conn.conn.cursor()
                    if getattr(self.conn.conn, "isolation_level", None) == "":
                        self.conn.conn.isolation_level = None

                except IOError as err:
                    msg = "I/O error: {0}".format(err)
                    self.n.add_treeview(
                        tab_tk_id, ("Error !",), [(msg,)], "Error !", instru
                    )
                    add_log(msg, "Error", first_line)
                    if not self.use_gui:
                        print("Error !", [msg])
                    if log is not None:  # write to logFile
                        log.write("Error ! %s : %s" % (msg, instru))
                    sql_error = True
                    break
            elif len("".join(instruction.split())) > 1:  # PyPy answer 42 to blanks sql
                nb_columns = -1
                try:
                    if self.output_mode or self.once_mode:
                        nb_columns = self.conn.export_writer(
                            instruction,
                            self.output_file,
                            header=self.default_header,
                            delimiter=self.default_separator,
                            encoding=self.encode_in,
                            initialize=self.init_output,
                        )
                        if nb_columns > 0:
                            self.once_mode, self.init_output = False, False
                            add_log(
                                "query exported to %s" % self.output_file,
                                "Ok",
                                first_line,
                            )
                        if self.x_mode and nb_columns > 0:
                            os.system(
                                "start excel.exe " + self.output_file.replace("\\", "/")
                            )
                            self.output_file = temp_csv()
                    else:
                        cur = cu.execute(instruction)
                        rows = cur.fetchall()
                        # a query may have no result( like for an "update")
                        if (
                            cur.description is not None and len(cur.description) > 0
                        ):  # pypy needs this test
                            titles = [row_info[0] for row_info in cur.description]
                            # DuckDB DDL/DML return a synthetic 'Count' or
                            # 'Success' resultset : log it, don't grid it
                            # (unless the instruction is a genuine query)
                            if (
                                titles in (["Count"], ["Success"])
                                and len(rows) <= 1
                                and not instru.lstrip("( \t\n\r")[:9]
                                .lower()
                                .startswith(
                                    (
                                        "select",
                                        "with",
                                        "from",
                                        "values",
                                        "table",
                                        "show",
                                        "describe",
                                        "summarize",
                                        "pragma",
                                        "call",
                                        "explain",
                                    )
                                )
                            ):
                                affected = rows[0][0] if rows else 0
                                add_log(
                                    "%s rows affected" % affected
                                    if titles == ["Count"]
                                    else "",
                                    "Ok",
                                    first_line,
                                )
                            else:
                                add_log("%s rows" % len(rows), "Ok", first_line)
                                # grid tab title carries the "Logs" record N°
                                self.n.add_treeview(
                                    tab_tk_id,
                                    titles,
                                    rows,
                                    "Qry_%s" % len(log_rows),
                                    first_line,
                                )
                                grid_count += 1
                            if log is not None:  # write to logFile
                                log.write(beurk(titles) + "\n")
                                log.write(
                                    "\n".join([beurk(l) for l in rows[:limit]]) + "\n"
                                )
                                if len(rows) > limit:
                                    log.write("%s more..." % len((rows) - limit))
                        else:
                            rowcount = getattr(cur, "rowcount", -1)
                            add_log(
                                "%s rows affected" % rowcount
                                if rowcount >= 0
                                else "",
                                "Ok",
                                first_line,
                            )
                except db_errors as msg:  # OperationalError
                    self.n.add_treeview(
                        tab_tk_id, ("Error !",), [(msg,)], "Error !", first_line
                    )
                    add_log("%s" % msg, "Error", first_line)
                    if log is not None:  # write to logFile
                        log.write("Error ! %s" % msg)
                    sql_error = True
                    break

        # display the "Logs" tab, unless the run was a single query
        # already fully rendered by its data grid (classic behavior)
        if len(log_rows) > 1 or (log_rows and grid_count == 0):
            self.n.add_treeview(
                tab_tk_id,
                ("N°", "Time", "Result", "Status", "Instruction"),
                log_rows,
                "Logs",
                "execution log",
                position=0,  # keep the permanent "Logs" tab leftmost
            )

        if getattr(self.conn.conn, "isolation_level", None) != isolation:
            # 'backward' compatible mode (automatic commit) : end transaction
            # (engines without .in_transaction just attempt the COMMIT)
            try:
                if getattr(self.conn.conn, "in_transaction", True):
                    cu.execute("COMMIT;" if not sql_error else "ROLLBACK;")
                    if not sql_error and log is not None:  # write to logFile
                        log.write("\n-------COMMIT;--------\n")
            except:
                pass
            self.conn.conn.isolation_level = isolation  # restore standard

    def import_csvtb(self, csv_file=None):
        """import csv dialog (with guessing of encoding and separator)"""
        if csv_file is None:
            csv_file = filedialog.askopenfilename(
                initialdir=self.initialdir,
                defaultextension=".db",
                title="Choose a csv or json file to import ",
                filetypes=[
                    ("default", "*.csv"),
                    ("json", "*.json *.jsonl *.ndjson"),
                    ("other", "*.txt"),
                    ("all", "*.*"),
                ],
            )
        if csv_file != "" and csv_file.lower().endswith(
            (".json", ".jsonl", ".ndjson")
        ):  # the file extension decides the format
            self.import_jsontb(csv_file)
        elif csv_file != "":
            self.set_initialdir(csv_file)
            # guess all via an object
            guess = guess_csv(csv_file)
            guess_sql = guess_sql_creation(
                guess.table_name,
                guess.default_sep,
                ".",
                guess.has_header,
                guess.dlines,
                guess.default_quote,
            )[2]
            # check it via dialog box
            fields_in = [
                "",
                ["csv Name", csv_file, "r", 100],
                "",
                ["table Name", guess.table_name],
                ["column separator", guess.default_sep, "w", 20],
                ["string delimiter", guess.default_quote, "w", 20],
                "",
                ["Decimal separator", guess.default_decims],
                ["Encoding", guess.encodings],
                "Fliflaps",
                ["Header line", guess.has_header],
                ["Create table", True],
                ["Replace existing data", True],
                "",
                ["first 3 lines", guess.dlines, "r", 100, 10],
                "",
                ["use manual creation request", False],
                "",
                ["creation request", guess_sql, "w", 100, 10],
            ]
            actions = [self.conn, self.actualize_db]
            create_dialog(
                ("Importing %s" % csv_file),
                fields_in,
                ("Import", import_csvtb_ok),
                actions,
            )

    def import_jsontb(self, json_file):
        """import a .json (array) or .jsonl file into a 1-column raw table,
        and propose a first 'shredding' query in a new tab"""
        self.set_initialdir(json_file)
        table_name = json_table_name(json_file)
        if self.conn.engine == "duckdb":
            # DuckDB reads (and types) json natively : do it in visible sql
            base = table_name[: -len("_raw")]
            query = (
                'CREATE OR REPLACE TABLE "%s" AS\n'
                "SELECT * FROM read_json_auto('%s');\n"
                'SELECT * FROM "%s" LIMIT 100;'
            ) % (base, json_file.replace("'", "''"), base)
            self.n.new_query_tab("import %s" % base, query)
            self.run_tab()
        else:
            records = read_this_json(json_file)
            self.conn.insert_reader(
                ((json.dumps(r, ensure_ascii=False),) for r in records),
                table_name,
                'CREATE TABLE "%s" (json TEXT)' % table_name,
                create_table=True,
                replace=True,
            )
            self.n.new_query_tab(
                "shred %s" % table_name, shred_query(table_name, records)
            )
        self.actualize_db()

    def paste_csvtb(self):
        """paste clipboard into a table, via the csv import dialog"""
        try:
            content = self.tk_win.clipboard_get()
        except TclError:
            content = ""
        if len(content.splitlines()) < 2:  # guess_sql_creation needs 2 lines
            messagebox.showinfo(
                parent=self.tk_win,
                message="Clipboard has no usable text content",
            )
            return
        csv_file = os.path.join(tmpf.gettempdir(), "from_clipboard.csv")
        with open(csv_file, "w", encoding="utf-8", newline="") as f:
            f.write(content)
        self.import_csvtb(csv_file)

    def get_table_query(self):
        """return (name, select query) of the table selected in the db tree"""
        selitem = self.db_tree.focus()  # get tree item having the focus
        if selitem != "":
            seltag = self.db_tree.item(selitem, "tag")[0]
            if seltag == "run_up":  # if 'run-up', do as dbl-click 1 level up
                selitem = self.db_tree.parent(selitem)
            # get final information
            definition, query = self.db_tree.item(selitem, "values")
            if query != "":
                return self.db_tree.item(selitem, "text"), query
        return None

    def get_tab_query(self):
        """return the current script tab selection (or its whole text)"""
        active_tab_id = self.n.notebook.select()
        if active_tab_id != "":
            return self.tab_script(active_tab_id)[1]
        return ""

    def export_csvtb(self):
        """get selected table definition and launch cvs export dialog"""
        table_query = self.get_table_query()
        if table_query is not None:
            title = 'Export Table "%s" to ?' % table_query[0]
            self.export_csv_dialog(table_query[1], title, [self.conn, self.db_tree])

    def export_csvqr(self):
        """get tab selected definition and launch cvs export dialog"""
        query = self.get_tab_query()
        if query != "":
            self.export_csv_dialog(query, "Export Query", [self.conn, self.n])

    def copy_query_to_clipboard(self, query, header=True, delimiter="\t"):
        """run query and place its result in the clipboard as csv text"""
        buffer = io.StringIO()
        if self.conn.export_writer(query, buffer,
                                   header=header, delimiter=delimiter) > 0:
            self.tk_win.clipboard_clear()
            self.tk_win.clipboard_append(buffer.getvalue())
            self.tk_win.update()  # push to the system clipboard now

    def copy_csvtb(self):
        """get selected table definition and launch copy-to-clipboard dialog"""
        table_query = self.get_table_query()
        if table_query is not None:
            title = 'Copy Table "%s" to Clipboard' % table_query[0]
            self.copy_csv_dialog(table_query[1], title)

    def copy_csvqr(self):
        """get tab selected definition and launch copy-to-clipboard dialog"""
        query = self.get_tab_query()
        if query != "":
            self.copy_csv_dialog(query, "Copy Query to Clipboard")

    def copy_csv_dialog(self, query="--", text="Copy to Clipboard"):
        """copy-to-clipboard dialog : like csv export, minus file and encoding"""
        fields = [
            "",
            ["column separator", ["<TAB>", ",", ";", "|"]],
            ["Header line", True],
            "",
            ["Data to export (MUST be 1 Request)", (query), "w", 100, 10],
        ]
        create_dialog(
            text,
            fields,
            (("Copy and close", copy_csv_close_ok), ("Copy", copy_csv_ok)),
            [self],
        )

    def export_csv_dialog(self, query="--", text="undefined.csv", actions=[]):
        """export csv dialog"""
        # proposed encoding (we favorize utf-8 or utf-8-sig)
        encodings = ["utf-8", locale.getdefaultlocale()[1], "utf-16", "utf-8-sig"]
        if os.name == "nt":
            encodings = ["utf-8-sig", locale.getdefaultlocale()[1], "utf-16", "utf-8"]
        # proposed csv separator
        default_sep = [",", "|", ";"]
        csv_file = filedialog.asksaveasfilename(
            initialdir=self.initialdir,
            defaultextension=".db",
            title=text,
            filetypes=[("default", "*.csv"), ("other", "*.txt"), ("all", "*.*")],
        )
        if csv_file != "":
            # Idea from (http://www.python-course.eu/tkinter_entry_widgets.php)
            fields = [
                "",
                ["csv Name", csv_file, "r", 100],
                "",
                ["column separator", default_sep],
                ["Header line", True],
                ["Encoding", encodings],
                "",
                ["Data to export (MUST be 1 Request)", (query), "w", 100, 10],
            ]

            create_dialog(
                ("Export to %s" % csv_file), fields, ("Export", export_csv_ok), actions
            )


class NotebookForQueries:
    """Create a Notebook with a list in the First frame
    and query results in following treeview frames"""

    def __init__(self, tk_win, root, queries, use_gui):
        self.use_gui = use_gui
        self.nongui_tabs = [
            "",
        ]
        if self.use_gui:
            self.tk_win = tk_win
            self.root = root
            # high-dpi: same screen scale as the main window (never below 1)
            self.dpi_scale = max(1.0, tk_win.winfo_fpixels("1i") / 96.0)
            self.notebook = Notebook(root, style="ButtonNotebook")  # ttk.

            self.fw_labels = {}  # tab_tk_id -> Scripting frame python object
            self.fw_result_nbs = {}  # tab_tk_id -> Notebook of Results

            # resize rules
            root.columnconfigure(0, weight=1)
            root.rowconfigure(0, weight=1)
            # grid widgets
            self.notebook.grid(row=0, column=0, sticky=(N, W, S, E))

    def new_query_tab(self, title, query):
        """add a Tab 'title' to the notebook, containing the Script 'query'"""

        if not self.use_gui:
            self.nongui_tabs += [query]
            return len(self.nongui_tabs) - 1

        fw_welcome = ttk.Panedwindow(self.tk_win, orient=VERTICAL)  # tk_win
        fw_welcome.pack(fill="both", expand=True)
        self.notebook.add(fw_welcome, text=(title))

        # new "editable" script
        f1 = ttk.Labelframe(
            fw_welcome,
            text="Script",
            width=int(200 * self.dpi_scale),
            height=int(100 * self.dpi_scale),
        )
        fw_welcome.add(f1)
        fw_label = Text(f1, bd=1, undo=True)

        scroll = ttk.Scrollbar(f1, command=fw_label.yview)
        fw_label.configure(yscrollcommand=scroll.set)
        fw_label.insert(END, (query))
        fw_label.pack(side=LEFT, expand=YES, fill=BOTH, padx=2, pady=2)
        scroll.pack(side=RIGHT, expand=NO, fill=BOTH, padx=2, pady=2)

        # keep tab reference  by tk id
        working_tab_id = "." + fw_welcome._name

        # keep tab reference to script (by tk id)
        self.fw_labels[working_tab_id] = fw_label

        # new "Results" Container
        fr = ttk.Labelframe(
            fw_welcome,
            text="Results",
            width=int(200 * self.dpi_scale),
            height=int(100 * self.dpi_scale),
        )
        fw_welcome.add(fr)

        # containing a notebook
        fw_result_nb = Notebook(fr, style="ButtonNotebook")
        fw_result_nb.pack(fill="both", expand=True)
        # resize rules
        fw_welcome.columnconfigure(0, weight=1)
        # keep reference to result_nb objects (by tk id)
        self.fw_result_nbs[working_tab_id] = fw_result_nb

        # activate this tab print(self.notebook.tabs())
        self.notebook.select(working_tab_id)
        # workaround to have a visible result pane on initial launch
        self.add_treeview(working_tab_id, "_", "", "click on ('->') to run Script")
        return working_tab_id  # gives back tk_id reference of the new tab

    def del_tab(self):
        """delete active notebook tab's results"""
        given_tk_id = self.notebook.select()
        if given_tk_id != "":
            self.notebook.forget(given_tk_id)

    def remove_treeviews(self, given_tk_id):
        """remove results from given tab tk_id"""
        if given_tk_id != "":
            myz = self.fw_result_nbs[given_tk_id]
            for xx in list(myz.children.values()):
                xx.grid_forget()
                xx.destroy()

    def add_treeview(
        self, given_tk_id, columns, data, title="__", subt="", position="end"
    ):
        """add a dataset result to the given tab tk_id"""
        if not self.use_gui:
            return
        # ensure we work on lists
        if isinstance(columns, str):
            tree_columns = [columns]
        else:
            tree_columns = columns
        lines = [data] if isinstance(data, str) else data

        # get back reference to Notebooks of Results
        # (see http://www.astro.washington.edu/users/rowen/TkinterSummary.html)
        fw_result_nb = self.fw_result_nbs[given_tk_id]

        # create a Labelframe to contain new resultset and scrollbars
        f2 = ttk.Labelframe(
            fw_result_nb,
            text=("(%s lines) %s   [Ctrl+c copy, Ctrl+f filter]" % (len(lines), subt)),
            width=int(200 * self.dpi_scale),
            height=int(100 * self.dpi_scale),
        )
        f2.pack(fill="both", expand=True)
        if position != "end" and not fw_result_nb.tabs():
            position = "end"  # Tk refuses insert(0) in an empty notebook
        fw_result_nb.insert(position, f2, text=title)

        # ttk.Style().configure('TLabelframe.label', font=("Arial",14, "bold"))
        # lines=queries
        fw_Box = Treeview(
            f2, columns=tree_columns, show="headings", padding=(2, 2, 2, 2)
        )
        fw_Box.bind("<Control-c>", lambda e: self.copy_rows(e.widget))
        fw_Box.bind("<Control-f>", lambda e: self.filter_rows(e.widget))
        fw_vsb = Scrollbar(f2, orient="vertical", command=fw_Box.yview)
        fw_hsb = Scrollbar(f2, orient="horizontal", command=fw_Box.xview)
        fw_Box.configure(yscrollcommand=fw_vsb.set, xscrollcommand=fw_hsb.set)
        fw_Box.grid(column=0, row=0, sticky="nsew", in_=f2)
        fw_vsb.grid(column=1, row=0, sticky="ns", in_=f2)
        fw_hsb.grid(column=0, row=2, sticky="ew", in_=f2)

        # this new Treeview  may occupy all variable space
        f2.grid_columnconfigure(0, weight=1)
        f2.grid_rowconfigure(0, weight=1)

        # feed Treeview Header
        for col in tuple(tree_columns):
            fw_Box.heading(
                col, text=col.title(), command=lambda c=col: self.sortby(fw_Box, c, 0)
            )
            # keep a little breathing room around the header text
            fw_Box.column(col, width=font.Font().measure(col.title()) + 12)

        def flat(x):
            """replace line_return by space, if given a string"""
            if isinstance(x, str):
                return x.replace("\n", " ")
            return x

        # feed Treeview Lines
        for items in lines:
            # if line is a string, redo a tuple
            item = (items,) if isinstance(items, str) else items

            # replace line_return by space (grid don't like line_returns)
            line_cells = tuple(flat(item[c]) for c in range(len(tree_columns)))
            # insert the line of data
            fw_Box.insert("", "end", values=line_cells)
            # adjust columns length if necessary and possible
            for indx, val in enumerate(line_cells):
                try:
                    ilen = font.Font().measure(val) + 12
                    if (
                        fw_Box.column(tree_columns[indx], width=None) < ilen
                        and ilen < 400
                    ):
                        fw_Box.column(tree_columns[indx], width=ilen)
                except:
                    pass

    def copy_rows(self, tree):
        """Ctrl+c : copy header + selected rows (or all) as tab-separated text"""
        rows = tree.selection() or tree.get_children()
        text = "\t".join(tree.heading(c, "text") for c in tree["columns"])
        for iid in rows:
            text += "\n" + "\t".join(str(v) for v in tree.item(iid)["values"])
        tree.clipboard_clear()
        tree.clipboard_append(text)

    def filter_rows(self, tree):
        """Ctrl+f : show only rows with a cell matching a regex (empty = all)"""
        pattern = simpledialog.askstring(
            "Filter rows", "regular expression :", parent=tree
        )
        if pattern is None:
            return
        if not hasattr(tree, "initial_rows"):
            tree.initial_rows = tree.get_children()  # remember full set once
            tree.initial_title = tree.master.cget("text")
        try:
            regex = re.compile(pattern, re.IGNORECASE)
        except re.error:
            return
        for iid in tree.initial_rows:
            row = "\t".join(str(v) for v in tree.item(iid)["values"])
            if regex.search(row):
                tree.reattach(iid, "", "end")
            else:
                tree.detach(iid)
        # reflect the filter in the frame title : (x out of N lines, filter= p)
        title = tree.initial_title
        if pattern != "":
            title = "(%s out of %s lines, filter= %s)%s" % (
                len(tree.get_children()),
                len(tree.initial_rows),
                pattern,
                tree.initial_title.split(")", 1)[1],
            )
        tree.master.configure(text=title)

    def sortby(self, tree, col, descending):
        """Sort a ttk treeview contents when a column is clicked on."""
        # grab values to sort
        data = [(tree.set(child, col), child) for child in tree.get_children()]

        # reorder data
        data.sort(reverse=descending)
        for indx, item in enumerate(data):
            tree.move(item[1], "", indx)

        # switch the heading so that it will sort in the opposite direction
        tree.heading(
            col, command=lambda col=col: self.sortby(tree, col, int(not descending))
        )


class guess_csv:
    """guess everything about a csv file of data to import in SQL"""

    def __init__(self, csv_file):
        self.has_header = True
        self.default_sep = ","
        self.default_quote = '"'
        self.encodings = guess_encoding(csv_file)
        self.table_name = os.path.basename(csv_file).split(".")[0]
        with open(csv_file, encoding=self.encodings[0]) as f:
            self.preview = f.read(9999)
            try:
                dialect = csv.Sniffer().sniff(self.preview)
                self.has_header = csv.Sniffer().has_header(self.preview)
                self.default_sep = dialect.delimiter
                self.default_quote = dialect.quotechar
            except:
                pass  # sniffer can fail
        self.default_decims = [".", ","]
        if self.default_sep == ";":
            self.default_decims = [",", "."]
        self.dlines = "\n\n".join(self.preview.splitlines()[:3])


def guess_sql_creation(table_name, separ, decim, header, data, quoter='"'):
    """guess the sql creation request for the table who will receive data"""
    dlines = list(
        csv.reader(
            data.replace("\n\n", "\n").splitlines(),
            delimiter=separ,
            quotechar=quoter,
        )
    )
    r, val = list(dlines[0]), list(dlines[1])
    typ = ["TEXT"] * len(r)  # default value is TEXT
    for i in range(len(r)):
        try:
            float(val[i].replace(decim, "."))  # unless it can be a real
            typ[i] = "REAL"
        except:
            checker = sqlite.connect(":memory:")
            # avoid the false positive 'now'
            val_not_now = val[i].replace("w", "www").replace("W", "WWW")
            test = "select datetime('{0}')".format(val_not_now)
            try:
                if checker.execute(test).fetchall()[0][0]:
                    typ[i] = "DATETIME"  # and unless SQLite can see a DATETIME
            except:
                pass
            checker.close
    if header:
        # de-duplicate column names, if needed by pastixing with '_'+number
        for i in range(len(r)):

           if r[i] == "":  # 2022-02-04a replace empty column title per usual default
                r[i] = "c_" + ("000" + str(i))[-3:]

           if r[i] in r[:i]:
                j = 1
                while r[i] + "_" + str(j) in r[:i] + r[i + 1 :]:
                    j += 1
                r[i] += "_" + str(j)
        head = ",\n".join([('"%s" %s' % (r[i], typ[i])) for i in range(len(r))])
        sql_crea = 'CREATE TABLE "%s" (%s);' % (table_name, head)
    else:
        # typed columns : mandatory for DuckDB, harmless for SQLite
        head = ",".join(
            ["c_" + ("000" + str(i))[-3:] + " " + typ[i] for i in range(len(r))]
        )
        sql_crea = 'CREATE TABLE "%s" (%s);' % (table_name, head)
    return sql_crea, typ, head


def guess_encoding(csv_file):
    """guess the encoding of the given file"""
    with open(csv_file, "rb") as f:
        data = f.read(5)
    if data.startswith(b"\xEF\xBB\xBF"):  # UTF-8 with a "BOM"
        return ["utf-8-sig"]
    elif data.startswith(b"\xFF\xFE") or data.startswith(b"\xFE\xFF"):
        return ["utf-16"]
    else:  # in Windows, guessing utf-8 doesn't work, so we have to try
        try:
            with open(csv_file, encoding="utf-8") as f:
                preview = f.read(222222)
                return ["utf-8"]
        except:
            return [locale.getdefaultlocale()[1], "utf-8"]


def create_dialog(title, fields_in, buttons, actions):
    """create a formular with title, fields, button, data"""
    # drawing the request form
    top = Toplevel()
    top.title(title)
    # pop up near the main window (matters on multi-screen setups)
    top.geometry(
        "+%d+%d" % (top.master.winfo_rootx() + 60, top.master.winfo_rooty() + 60)
    )
    top.columnconfigure(0, weight=1)
    top.rowconfigure(0, weight=1)
    # drawing global frame
    content = ttk.Frame(top)
    content.grid(column=0, row=0, sticky=(N, S, E, W))
    content.columnconfigure(0, weight=1)
    # fields = Horizontal FrameLabel, or
    #         label, default_value, 'r' or 'w' default_width,default_height
    fields = fields_in
    mf_col = -1
    for f in range(len(fields)):  # same structure out
        field = fields[f]
        if isinstance(field, str) or mf_col == -1:
            # a new horizontal frame
            mf_col += 1
            ta_col = -1
            if isinstance(field, str) and field == "":
                mf_frame = ttk.Frame(content, borderwidth=1)
            else:
                mf_frame = ttk.LabelFrame(content, borderwidth=1, text=field)
            mf_frame.grid(column=0, row=mf_col, sticky="nsew")
            Grid.rowconfigure(mf_frame, 0, weight=1)
            content.rowconfigure(mf_col, weight=1)
        if not isinstance(field, str):
            # a new vertical frame
            ta_col += 1
            Grid.columnconfigure(mf_frame, ta_col, weight=1)
            packing_frame = ttk.Frame(mf_frame, borderwidth=1)
            packing_frame.grid(column=ta_col, row=0, sticky="nsew")
            Grid.columnconfigure(packing_frame, 0, weight=1)
            # prepare width and height and writable status
            width = field[3] if len(field) > 3 else 30
            height = field[4] if len(field) > 4 else 30
            status = "normal"
            if len(field) >= 3 and field[2] == "r":
                status = "disabled"
            # switch between object types
            if len(field) > 4:
                # datas
                d_frame = ttk.LabelFrame(
                    packing_frame,
                    borderwidth=5,
                    width=width,
                    height=height,
                    text=field[0],
                )
                d_frame.grid(column=0, row=0, sticky="nsew", pady=1, padx=1)
                Grid.rowconfigure(packing_frame, 0, weight=1)
                fw_label = Text(d_frame, bd=1, width=width, height=height, undo=True)
                fw_label.pack(side=LEFT, expand=YES, fill=BOTH)
                scroll = ttk.Scrollbar(d_frame, command=fw_label.yview)
                scroll.pack(side=RIGHT, expand=NO, fill=Y)
                fw_label.configure(yscrollcommand=scroll.set)
                fw_label.insert(END, ("%s" % field[1]))
                fw_label.configure(state=status)
                Grid.rowconfigure(d_frame, 0, weight=1)
                Grid.columnconfigure(d_frame, 0, weight=1)
                # Data Text Extractor in the fields list ()
                # see stackoverflow.com/questions/17677649 (loop and lambda)
                fields[f][1] = lambda x=fw_label: x.get("1.0", "end")
            elif isinstance(field[1], type(True)):
                # boolean Field
                name_var = BooleanVar()
                name = ttk.Checkbutton(
                    packing_frame,
                    text=field[0],
                    variable=name_var,
                    onvalue=True,
                    state=status,
                )
                name_var.set(field[1])
                name.grid(column=0, row=0, sticky="nsew", pady=5, padx=5)
                fields[f][1] = name_var.get
            else:  # Text or Combo
                namelbl = ttk.Label(packing_frame, text=field[0])
                namelbl.grid(column=0, row=0, sticky="nsw", pady=5, padx=5)
                name_var = StringVar()
                if not isinstance(field[1], (list, tuple)):
                    name = ttk.Entry(
                        packing_frame, textvariable=name_var, width=width, state=status
                    )
                    name_var.set(field[1])
                else:
                    name = ttk.Combobox(
                        packing_frame, textvariable=name_var, state=status
                    )
                    name["values"] = list(field[1])
                    name.current(0)
                name.grid(column=1, row=0, sticky="nsw", pady=0, padx=10)
                fields[f][1] = name_var.get
    # adding buttons below the same way : one ("label", action) pair,
    # or a tuple of such pairs ; a "Cancel" button is always appended
    if isinstance(buttons[0], str):
        buttons = (buttons,)
    mf_col += 1
    packing_frame = ttk.LabelFrame(content, borderwidth=5)
    packing_frame.grid(column=0, row=mf_col, sticky="nsew")
    col = 0
    for label, action in buttons:
        ttk.Button(
            packing_frame,
            text=label,
            command=lambda a=top, b=fields, c=actions, f=action: f(a, b, c),
        ).grid(column=col, row=mf_col)
        col += 1
    cancelbutton = ttk.Button(packing_frame, text="Cancel", command=top.destroy)
    cancelbutton.grid(column=col, row=mf_col)
    for x in range(col + 2):
        Grid.columnconfigure(packing_frame, x, weight=1)
    top.grab_set()


def import_csvtb_ok(thetop, entries, actions):
    """read input values from tk formular"""
    conn, actualize_db = actions
    # build dico of result
    d = {f[0]: f[1]() for f in entries if not isinstance(f, str)}
    # affect to variables
    csv_file = d["csv Name"].strip()
    table_name = d["table Name"].strip()
    separ = d["column separator"]
    decim = d["Decimal separator"]
    quotechar = d["string delimiter"]
    # action
    if csv_file != "(none)" and len(csv_file) * len(table_name) * len(separ) > 1:
        thetop.destroy()
        # do initialization job
        sql, typ, head = guess_sql_creation(
            table_name, separ, decim, d["Header line"], d["first 3 lines"], quotechar
        )
        if d["use manual creation request"]:
            sql = 'CREATE TABLE "%s" (%s);' % (table_name, d["creation request"])

        # Create csv reader function and give it to insert
        reading = read_this_csv(
            csv_file, d["Encoding"], separ, quotechar, d["Header line"], decim
        )

        conn.insert_reader(
            reading,
            table_name,
            sql,
            create_table=d["Create table"],
            replace=d["Replace existing data"],
        )
        # refresh
        actualize_db()


def read_this_csv(csv_file, encoding, delimiter, quotechar, header, decim):
    """yield csv data records from a file"""
    with open(csv_file, "r", encoding=encoding, newline="") as f:
        reader = csv.reader(f, delimiter=delimiter, quotechar=quotechar)
        # handle header
        if header:
            next(reader)
        # otherwise handle special decimal treatment
        for row in reader:
            if decim != "." and not isinstance(row, str):
                for i in range(len(row)):
                    row[i] = row[i].replace(decim, ".")
            yield (row)


def read_this_json(json_file):
    """return the records of a .json (array or object) or .jsonl file"""
    with open(json_file, encoding="utf-8-sig") as f:
        if json_file.lower().endswith(".json"):
            data = json.load(f)
            return data if isinstance(data, list) else [data]
        return [json.loads(line) for line in f if line.strip()]


def json_table_name(json_file):
    """a table name from a file name : 'd:/some stuff.json' -> some_stuff_raw"""
    base = os.path.splitext(os.path.basename(json_file))[0]
    return re.sub(r"\W", "_", base) + "_raw"


def shred_query(table_name, records):
    """sql proposal shredding a raw json table, from the first record keys"""
    first = records[0] if records else None
    if not isinstance(first, dict) or not first:
        return 'SELECT json FROM "%s";' % table_name
    cols = ",\n".join(
        "    json_extract(json, '$.%s') AS \"%s\""
        % (k if re.match(r"^\w+$", k) else '"' + k + '"', k.replace('"', '""'))
        for k in first
    )
    return (
        '-- shredding "%s" : keys seen in the first record, edit at will\n'
        'SELECT\n%s\nFROM "%s";\n'
        "-- nested data ? see json_each(json, '$.path') and json_tree\n"
    ) % (table_name, cols, table_name)


def copy_csv_close_ok(thetop, entries, actions):
    "copy a query result to the clipboard, then close the dialog (action)"
    copy_csv_ok(thetop, entries, actions)
    thetop.destroy()


def copy_csv_ok(thetop, entries, actions):
    "copy a query result to the clipboard (action)"
    d = {f[0]: f[1]() for f in entries if not isinstance(f, str)}
    separ = "\t" if d["column separator"] == "<TAB>" else d["column separator"]
    actions[0].copy_query_to_clipboard(
        d["Data to export (MUST be 1 Request)"],
        header=d["Header line"],
        delimiter=separ,
    )


def export_csv_ok(thetop, entries, actions):
    "export a csv table (action)"
    conn = actions[0]
    # build dico of result
    d = {f[0]: f[1]() for f in entries if not isinstance(f, str)}

    csv_file = d["csv Name"].strip()
    conn.export_writer(
        d["Data to export (MUST be 1 Request)"],
        csv_file,
        header=d["Header line"],
        delimiter=d["column separator"],
        encoding=d["Encoding"],
        quotechar='"',
    )


def get_leaves(conn, category, attached_db="", tbl=""):
    """returns a list of 'category' objects in attached_db
    [objectCode, objectLabel, Definition, 'sub-level']
    """
    # create formatting shortcuts
    def f(t):
        return ('"%s"' % t.replace('"', '""')) if t != "" else t

    def d(t):
        return ("%s." % t) if t != "" else t

    # Initialize datas
    Tables, db, tb = [], d(attached_db), f(tbl)
    master = "sqlite_master" if db != "temp." else "sqlite_temp_master"

    if category == "pydef":  # pydef request is not sql, answer is direct
        Tables = [[k, k, v["pydef"], ""] for k, v in conn.conn_def.items()]
    elif getattr(conn, "engine", "sqlite") == "duckdb":
        # DuckDB : sqlite_master only exists for the current database,
        # so rely on the duckdb_xxx() catalog functions instead
        dbf = (
            "current_database()"
            if attached_db in ("", "main", "temp")
            else "'%s'" % attached_db.replace("'", "''")
        )
        if category == "attached_databases":
            sql = """select database_oid, database_name, coalesce(path, '')
                  from duckdb_databases()
                  where not internal and database_name != current_database()"""
            for c in conn.execute(sql).fetchall():
                instruct = "ATTACH DATABASE %s as %s" % (f(c[2]), f(c[1]))
                Tables.append([c[0], c[1], instruct, ""])
        elif category == "fields":
            qualified = (
                tbl
                if attached_db in ("", "main")
                else "%s.%s" % (attached_db, tbl)
            )
            resu = conn.execute(
                "PRAGMA table_info('%s')" % qualified.replace("'", "''")
            ).fetchall()
            Tables = [[c[1], c[1], c[2], ""] for c in resu]
        elif category in ("table", "view", "index"):
            if category == "table":
                sql = """select 'table:' || table_name, table_name,
                      coalesce(sql, '--auto'), 'fields'
                      from duckdb_tables() where not internal
                      and database_name = {0} order by table_name"""
            elif category == "view":
                sql = """select 'view:' || view_name, view_name,
                      coalesce(sql, '--auto'), 'fields'
                      from duckdb_views() where not internal
                      and database_name = {0} order by view_name"""
            else:
                sql = """select 'index:' || index_name, index_name,
                      coalesce(sql, '--auto'), ''
                      from duckdb_indexes()
                      where database_name = {0} order by index_name"""
            Tables = list(conn.execute(sql.format(dbf)).fetchall())
        # 'trigger' and 'master_table' : nothing to show for DuckDB
    elif category == "attached_databases":
        # get all attached database, but not the first one ('main')
        resu = list((conn.execute("PRAGMA database_list").fetchall()))[1:]
        for c in resu:
            instruct = "ATTACH DATABASE %s as %s" % (f(c[2]), f(c[1]))
            Tables.append([c[0], c[1], instruct, ""])
    elif category == "fields":
        resu = conn.execute("PRAGMA %sTABLE_INFO(%s)" % (db, tb)).fetchall()
        Tables = [[c[1], c[1], c[2], ""] for c in resu]
    elif category in ("index", "trigger", "master_table", "table", "view"):
        # others are 1 sql request that generates directly Tables
        if category in ("index", "trigger"):
            sql = """SELECT '{0}' || name, name, coalesce(sql,'--auto') , ''
                  FROM {0}{3} WHERE type='{1}' ORDER BY name"""
        elif category == "master_table":
            sql = """SELECT '{0}{3}', '{3}', '--auto', 'fields'
                   UNION SELECT '{0}'||name, name, sql, 'fields'
                  FROM {0}{3}
                  WHERE type='table' AND name LIKE 'sqlite_%' ORDER BY name"""
        elif category in ("table", "view"):
            sql = """SELECT '{0}' || name, name, sql , 'fields'
                FROM {0}{3} WHERE type = '{1}' AND NOT
                (type='table' AND name LIKE 'sqlite_%') ORDER BY name"""
        Tables = list(conn.execute(sql.format(db, category, tbl, master)).fetchall())
    return Tables


def duck_type_adapt(fn, nargs):
    """wrap an annotation-less pydef for DuckDB : mimic SQLite dynamic typing
    (arguments arrive as VARCHAR, numeric-looking ones are converted back)"""

    def convert_call(args):
        converted = []
        for arg in args:
            if isinstance(arg, str):
                try:
                    arg = int(arg)
                except ValueError:
                    try:
                        arg = float(arg)
                    except ValueError:
                        pass
            converted.append(arg)
        result = fn(*converted)
        return result if result is None else str(result)

    if nargs == 0:
        return lambda: convert_call(())
    # build a wrapper of the exact arity DuckDB expects (*args confuses it)
    argnames = ",".join("a%s" % i for i in range(nargs))
    return eval("lambda %s: _c((%s,))" % (argnames, argnames), {"_c": convert_call})


class Baresql:
    """a small wrapper around sqlite3 module"""

    def __init__(
        self,
        connection="",
        keep_log=False,
        cte_inline=True,
        isolation_level=None,
        engine=None,
    ):
        self.dbname = connection.replace(":///", "://").replace("sqlite://", "")
        if engine is None:  # guess the engine from the file extension
            engine = (
                "duckdb"
                if self.dbname.lower().endswith((".duckdb", ".ddb"))
                else "sqlite"
            )
        self.engine = engine
        if self.engine == "duckdb":
            if duckdb is None:
                raise ImportError(
                    "DuckDB engine requested, but 'duckdb' module is not installed"
                )
            self.conn = duckdb.connect(self.dbname)
        else:
            self.conn = sqlite.connect(
                self.dbname, detect_types=sqlite.PARSE_DECLTYPES
            )
            self.conn.isolation_level = isolation_level  # commit experience
        # pydef and logging infrastructure
        self.conn_def = {}
        self.do_log = keep_log
        self.log = []

    def close(self):
        """close database and clear dictionnary of registered 'pydef'"""
        self.conn.close()
        self.conn_def = {}

    def iterdump(self):
        """dump the database (add tweaks over the default dump)"""
        # force detection of utf-8 by placing an only utf-8 comment at top
        yield ("/*utf-8 tag : 你好 мир Artisou à croute*/\n")
        # add the Python functions pydef
        for k in self.conn_def.values():
            yield (k["pydef"] + ";\n")
        if self.engine == "duckdb":
            # DuckDB has no iterdump : rebuild one from catalog functions
            yield ("BEGIN TRANSACTION;")
            tables = self.conn.execute(
                """select table_name, sql from duckdb_tables()
                where not internal and database_name = current_database()
                order by table_name"""
            ).fetchall()
            for table_name, sql in tables:
                yield (sql if sql.rstrip().endswith(";") else sql + ";")
                quoted = table_name.replace('"', '""')
                for row in self.conn.execute(
                    'SELECT * FROM "%s"' % quoted
                ).fetchall():
                    vals = ",".join(
                        "NULL"
                        if v is None
                        else str(v)
                        if isinstance(v, (int, float))
                        else "'%s'" % str(v).replace("'", "''")
                        for v in row
                    )
                    yield ('INSERT INTO "%s" VALUES(%s);' % (quoted, vals))
            for (sql,) in self.conn.execute(
                """select sql from duckdb_views()
                where not internal and database_name = current_database()"""
            ).fetchall():
                yield (sql if sql.rstrip().endswith(";") else sql + ";")
            for (sql,) in self.conn.execute(
                """select sql from duckdb_indexes()
                where database_name = current_database()"""
            ).fetchall():
                if sql:
                    yield (sql if sql.rstrip().endswith(";") else sql + ";")
            yield ("COMMIT;")
            return
        # disable Foreign Constraints at Load
        yield ("PRAGMA foreign_keys = OFF; /*if SQlite */;")
        yield ("\n/* SET foreign_key_checks = 0;/*if Mysql*/;")
        # how to parametrize Mysql to SQL92 standard
        yield ("/* SET sql_mode = 'PIPES_AS_CONCAT';/*if Mysql*/;")
        yield ("/* SET SQL_MODE = ANSI_QUOTES; /*if Mysql*/;\n")
        # now the standard dump (notice it uses BEGIN TRANSACTION)
        for line in self.conn.iterdump():
            yield (line)
        # re-instantiate Foreign_keys = True
        for row in self.conn.execute("PRAGMA foreign_keys"):
            flag = "ON" if row[0] == 1 else "OFF"
            yield ("PRAGMA foreign_keys = %s;/*if SQlite*/;" % flag)
            yield ("PRAGMA foreign_keys = %s;/*if SQlite bug*/;" % flag)
            yield ("PRAGMA foreign_key_check;/*if SQLite, check*/;")
            yield ("\n/*SET foreign_key_checks = %s;/*if Mysql*/;\n" % row[0])

    def execute(self, sql, env=None):
        """execute sql but intercept log"""
        if self.do_log:
            self.log.append(sql)
        return self.conn.execute(sql)

    def createpydef(self, sql):
        """generates and register a pydef instruction"""
        import re

        instruction = sql.strip("; \t\n\r")
        # create Python function in Python
        exec(instruction[2:], globals(), pydef_locals)
        # add Python function in SQLite
        instr_header = re.findall(r"\w+", instruction[: (instruction + ")").find(")")])
        instr_name = instr_header[1]
        instr_parms = len(instr_header) - 2
        instr_pointer=eval(instr_name, globals(), pydef_locals)
        if self.engine == "duckdb":
            # DuckDB needs typed functions (and numpy installed) :
            # 1- clean up previous definitions of the same name
            for cleanup in (
                lambda: self.conn.remove_function(instr_name),
                lambda: self.conn.remove_function("_bro_" + instr_name),
                lambda: self.conn.execute('DROP MACRO IF EXISTS "%s"' % instr_name),
            ):
                try:
                    cleanup()
                except Exception:
                    pass
            # 2- a fully type-annotated pydef registers natively ...
            try:
                self.conn.create_function(instr_name, instr_pointer)
            except Exception:
                # ... otherwise register a VARCHAR implementation behind a
                # macro that casts any argument, to mimic SQLite duck-typing
                self.conn.create_function(
                    "_bro_" + instr_name,
                    duck_type_adapt(instr_pointer, instr_parms),
                    ["VARCHAR"] * instr_parms,
                    "VARCHAR",
                )
                parms = ["p%s" % i for i in range(instr_parms)]
                casts = ["CAST(p%s AS VARCHAR)" % i for i in range(instr_parms)]
                self.conn.execute(
                    'CREATE OR REPLACE MACRO "%s"(%s) AS "_bro_%s"(%s)'
                    % (instr_name, ",".join(parms), instr_name, ",".join(casts))
                )
        else:
            self.conn.create_function(instr_name, instr_parms, instr_pointer)
        instr_add = "self.conn.create_function('%s', %s, %s)" % (
            instr_name,
            instr_parms,
            instr_name,
        )
        # housekeeping definition of pydef in a dictionnary
        the_help = pydef_locals[instr_name].__doc__
        self.conn_def[instr_name] = {
            "parameters": instr_parms,
            "inst": instr_add,
            "help": the_help,
            "pydef": instruction,
        }
        return instr_name

    def get_tokens(self, sql, start=0, shell_tokens=False):
        """
        from given sql start position, yield tokens (value + token type)
        if shell_tokens is True, identify line shell_tokens as sqlite.exe does
        """
        length = len(sql)
        i = start
        can_be_shell_command = True
        dico = {
            " ": "TK_SP",
            "\t": "TK_SP",
            "\n": "TK_SP",
            "\f": "TK_SP",
            "\r": "TK_SP",
            "(": "TK_LP",
            ")": "TK_RP",
            ";": "TK_SEMI",
            ",": "TK_COMMA",
            "/": "TK_OTHER",
            "'": "TK_STRING",
            "-": "TK_OTHER",
            '"': "TK_STRING",
            "`": "TK_STRING",
        }
        while length > start:
            token = "TK_OTHER"
            if (
                shell_tokens
                and can_be_shell_command
                and i < length
                and (
                    (sql[i] == "." and i == start)
                    or (i > start and sql[i - 1 : i] == "\n.")
                )
            ):
                # a command line shell ! (supposed on one starting line)
                token = "TK_SHELL"
                i = sql.find("\n", start)
                if i <= 0:
                    i = length
            elif sql[i] == "-" and i < length and sql[i : i + 2] == "--":
                # this Token is an end-of-line comment : --blabla
                token = "TK_COM"
                i = sql.find("\n", start)
                if i <= 0:
                    i = length
            elif sql[i] == "/" and i < length and sql[i : i + 2] == "/*":
                # this Token is a comment block : /* and bla bla \n bla */
                token = "TK_COM"
                i = sql.find("*/", start) + 2
                if i <= 1:
                    i = length
            elif sql[i] not in dico:
                # this token is a distinct word (tagged as 'TK_OTHER')
                while i < length and sql[i] not in dico:
                    i += 1
            else:
                # default token analyze case
                token = dico[sql[i]]
                if token == "TK_SP":
                    # find the end of the 'Spaces' Token just detected
                    while i < length and sql[i] in dico and dico[sql[i]] == "TK_SP":
                        i += 1
                elif token == "TK_STRING":
                    # find the end of the 'String' Token just detected
                    delimiter = sql[i]
                    if delimiter != "'":
                        token = "TK_ID"  # usefull nuance ?
                    while i < length:
                        i = sql.find(delimiter, i + 1)
                        if i <= 0:  # String is never closed
                            i = length
                            token = "TK_ERROR"
                        elif i < length - 1 and sql[i + 1] == delimiter:
                            i += 1  # double '' case, so ignore and continue
                        else:
                            i += 1
                            break  # normal End of a  String
                else:
                    if i < length:
                        i += 1
            yield sql[start:i], token
            if token == "TK_SEMI":  # a new sql order can be a new shell token
                can_be_shell_command = True
            elif token not in ("TK_COM", "TK_SP", "TK_SHELL"):  # can't be a shell token
                can_be_shell_command = False
            start = i

    def get_sqlsplit(self, sql, remove_comments=False):
        """yield a list of separated sql orders from a sql file"""
        trigger_mode = False
        mysql = [""]
        for tokv, token in self.get_tokens(sql, shell_tokens=True):
            # clear comments option
            if token != "TK_COM" or not remove_comments:
                mysql.append(tokv)
            # special case for Trigger : semicolumn don't count
            if token == "TK_OTHER":
                tok = tokv.upper()
                if tok == "TRIGGER":
                    trigger_mode = True
                    translvl = 0
                elif trigger_mode and tok in ("BEGIN", "CASE"):
                    translvl += 1
                elif trigger_mode and tok == "END":
                    translvl -= 1
                    if translvl <= 0:
                        trigger_mode = False
            elif token == "TK_SEMI" and not trigger_mode:
                # end of a single sql
                yield "".join(mysql)
                mysql = []
            elif token == "TK_SHELL":
                # end of a shell order
                yield ("" + tokv)
                mysql = []
        if mysql != []:
            yield ("".join(mysql))

    def insert_reader(
        self,
        reader,
        table_name,
        create_sql=None,
        create_table=True,
        replace=True,
        header=False,
    ):
        """import a given csv reader into a given table"""
        curs = self.conn.cursor()
        # 1-do initialization job
        # speed-up dead otherwise dead slow speed if not memory database
        try:
            curs.execute("begin transaction")
        except:
            pass
        # check if table exists (DuckDB raises where SQLite returns empty)
        try:
            here = curs.execute('PRAGMA table_info("%s")' % table_name).fetchall()
        except db_errors:
            here = []
        if create_sql and (create_table or len(here) == 0):
            curs.execute('drop TABLE if exists "%s";' % table_name)
            curs.execute(create_sql)
        if replace:
            curs.execute('delete from "%s";' % table_name)
        # count rows of target table
        nbcol = len(curs.execute('pragma table_info("%s")' % table_name).fetchall())
        sql = 'INSERT INTO "%s" VALUES(%s);' % (table_name, ", ".join(["?"] * nbcol))
        # read first_line if hasked to skip headers
        if header:
            next(reader)
        # 2-push records
        curs.executemany(sql, reader)
        if self.engine == "duckdb":
            curs.commit()  # a DuckDB cursor is a duplicated connection
        else:
            self.conn.commit()

    def export_writer(
        self,
        sql,
        csv_file,
        header=True,
        delimiter=",",
        encoding="utf-8",
        quotechar='"',
        initialize=True,
    ):
        """export a csv table and return number of columns"""
        cursor = self.conn.cursor()
        cursor.execute(sql)
        # do nothing if nothing
        if cursor.description is None or len(cursor.description) == 0:
            return -1
        if self.engine == "duckdb":
            # DuckDB gives a description ('Count') even to non-SELECT
            # statements : export only genuine queries, like SQLite does
            first_word = ""
            for line in sql.splitlines():
                line = line.strip()
                if line and not line.startswith("--"):
                    first_word = line.split()[0].lower()
                    break
            if first_word not in (
                "select", "with", "from", "pragma", "show",
                "describe", "values", "call", "summarize", "table",
            ):
                return -1
        nb_columns = len(cursor.description)

        def write_rows(fout):
            writer = csv.writer(
                fout, delimiter=delimiter, quotechar=quotechar, quoting=csv.QUOTE_MINIMAL
            )
            if header:
                writer.writerow(
                    [i if isinstance(i, str) else i[0] for i in cursor.description]
                )  # PyPy as a strange list of list
            writer.writerows(cursor.fetchall())

        def write_json_rows(fout, lines_mode):
            """one object per row ; a .json array, or .jsonl lines"""
            cols = [i if isinstance(i, str) else i[0] for i in cursor.description]

            def clean(v):
                if isinstance(v, (bytes, bytearray)):
                    return v.decode("utf-8", "replace")
                if v is None or isinstance(v, (int, float, str)):
                    return v
                return str(v)  # datetime, Decimal, uuid, ...

            first = True
            for row in cursor:
                record = json.dumps(
                    dict(zip(cols, [clean(v) for v in row])), ensure_ascii=False
                )
                if lines_mode:
                    fout.write(record + "\n")
                else:
                    fout.write(("[\n" if first else ",\n") + record)
                    first = False
            if not lines_mode:
                fout.write("[]" if first else "\n]\n")

        if hasattr(csv_file, "write"):  # file-like target (e.g. clipboard buffer)
            write_rows(csv_file)
        else:
            write_mode = "w" if initialize else "a"  # Write or Append
            target = csv_file.lower()
            with open(csv_file, write_mode, newline="", encoding=encoding) as fout:
                if target.endswith((".json", ".jsonl", ".ndjson")):
                    # the file extension decides the format : json, not csv
                    write_json_rows(fout, not target.endswith(".json"))
                else:
                    write_rows(fout)
        return nb_columns


def _main():
    welcome_text = WELCOME_TEXT

    parser = argparse.ArgumentParser(
        description="sqlite_bro : a graphic SQLite and DuckDB browser"
        " in 1 Python file"
    )
    parser.add_argument(
        "-q", "--quiet", action="store_true", help="do not launch the gui"
    )
    parser.add_argument(
        "-w",
        "--wait",
        action="store_true",
        help="wait the user to launch the scripts",
    )
    parser.add_argument(
        "-db",
        "--database",
        default=":memory:",
        type=str,
        help="specify initial Database if not ':memory:'"
        " (a .duckdb/.ddb extension selects the DuckDB engine)",
    )
    parser.add_argument(
        "-sc", "--scripts", type=str, help="qive a list of initial scripts"
    )
    args = parser.parse_args()

    if args.quiet:
        app = App(use_gui=False)
    else:
        app = App(use_gui=True)
    # start with a memory Database and a welcome
    app.new_db(":memory:")
    if args.database:
        app.open_db(args.database)
    if args.scripts:
        if isinstance(args.scripts, str):
            scripts = [args.scripts, "", ""]
        else:
            scripts = args.scripts
        for script in scripts:
            if os.path.isfile(script):
                with open(script, encoding=guess_encoding(script)[0]) as f:
                    welcome_text = f.read()
                    app.n.new_query_tab("Welcome", welcome_text)
                    if not args.wait:
                        app.run_tab()
    elif app.conn.engine != "duckdb":  # duckdb welcome tab auto-shows
        app.n.new_query_tab("Welcome", welcome_text)
    if args.quiet:
        app.close_db
    if app.use_gui:
        app.tk_win.mainloop()


if __name__ == "__main__":
    _main()  # create a tkk graphic interface with a main window tk_win
