gctools/database/db_create.py

import json

import pandas as pd

from database.model import DbCreateConfig, DestTable, SourceTable2  # noqa:E402


def get_import_config(filename: str, db_name: str) -> pd.DataFrame:
    df = pd.read_csv(filename, sep=";", encoding="latin-1")
    if "dest" not in df.columns:
        df["dest"] = df["target"]
        df["dest_db"] = db_name
        df["cols"] = ""
        df[["source", "dest", "dest_db", "filter", "query", "iterative", "cols"]].to_csv(
            filename, sep=";", encoding="latin-1", index=False
        )
    return df[df["dest"].notnull()]


def get_table_config(cfg: DbCreateConfig) -> list[DestTable]:
    config = get_import_config(cfg.csv_file, cfg.dest_dsn.database)
    SourceTable2.source_inspect = cfg.source_inspect
    SourceTable2.cfg = cfg
    table_config = [DestTable(*row.values()) for row in config.to_dict(orient="records")]
    for dest_table in table_config:
        dest_table.cfg = cfg
        dest_table.dest_inspect = cfg.dest_inspect
        dest_table.source_tables = []
        for client_db, prefix in cfg.clients.items():
            st = SourceTable2(dest_table.source, client_db, prefix)
            st.dest_table = dest_table
            dest_table.source_tables.append(st)
    return table_config


def create(config_file: str = "database/CARLO.json"):
    cfg = DbCreateConfig.load_config(config_file)
    cfg.create_db_ini()
    print("Clients:")
    print(json.dumps(cfg.clients, indent=2))
    print("Vorschlaege fuer Clients:")
    print(json.dumps(cfg.source_inspect.get_prefix(), indent=2))

    table_config = get_table_config(cfg)

    for dest_table in table_config:
        with open(f"{cfg.sql_import_full_dir}\\{dest_table.dest}.sql", "w", encoding="latin-1") as f:
            if dest_table.dest not in cfg.dest_inspect.tables_list:
                f.write(f"-- Ziel-Tabelle '{dest_table.dest}' existiert nicht!\n")
                continue

            f.write(f"TRUNCATE TABLE {dest_table.full_table_name}\nGO\n\n")

            for source_table in dest_table.source_tables:
                if source_table.table_name not in cfg.source_inspect.tables_list:
                    source_table_str = cfg.source_inspect.convert_table(source_table.table_name)
                    if source_table_str not in cfg.source_inspect.tables_list:
                        f.write(f"-- Quell-Tabelle '{source_table.table_name}' existiert nicht!\n")
                        continue

                select_query = source_table.select_query_with_columns.replace(
                    "[dbo]", f"[{cfg.source_inspect.dsn.server}].[{cfg.source_inspect.dsn.database}].[dbo]"
                ).replace(",", "\n" + " " * 5 + ",")
                for tag in ["SELECT", "FROM", "INNER", "LEFT", "WHERE", "ORDER BY"]:
                    select_query = select_query.replace(tag, "\n" + tag)
                for tag in ["AND", "ON"]:
                    select_query = select_query.replace(tag, "\n      " + tag)
                f.write(source_table.info.replace("rem ", "-- "))
                if select_query == "":
                    f.write(f"-- Ziel-Tabelle '{dest_table.dest}' Spalte 'Client_DB' fehlt!\n")
                    continue

                f.write(
                    f"INSERT INTO [{cfg.dest_dsn.schema}].[{dest_table.dest}]\nWITH (TABLOCK)\n{select_query}\nGO\n"
                )

    for dest_table in table_config:
        with open(dest_table.table_batch_file, "w", encoding="cp850") as f:
            f.write("@echo off\n")
            f.write(f'call "{cfg.scripts_dir}\\config2.bat"\n')
            f.write("rem ==" + dest_table.dest + "==\n")

            if dest_table.dest not in cfg.dest_inspect.tables_list:
                f.write(f"echo Ziel-Tabelle '{dest_table.dest}' existiert nicht!\n")
                print(f"Ziel-Tabelle '{dest_table.dest}' existiert nicht!")
                continue

            f.write(f"del {cfg.logs_dir}\\{dest_table.dest}*.* /Q /F >nul 2>nul\n\n")
            f.write('if not "%1"=="" goto :increment\n')
            f.write("\n:full\n")
            f.write(f'  call sql_query.bat "TRUNCATE TABLE {dest_table.full_table_name}"\n')

            for source_table in dest_table.source_tables:
                if source_table.table_name not in cfg.source_inspect.tables_list:
                    source_table_str = cfg.source_inspect.convert_table(source_table.table_name)
                    if source_table_str not in cfg.source_inspect.tables_list:
                        f.write(f"echo Quell-Tabelle '{source_table.table_name}' existiert nicht!\n")
                        print(f"Quell-Tabelle '{source_table.table_name}' existiert nicht!")
                        continue

                select_query = source_table.select_query_with_columns.replace("%", "%%%%")  # batch-Problem
                f.write(source_table.info)
                if select_query == "":
                    print(f"Ziel-Tabelle '{dest_table.dest}' Spalte 'Client_DB' fehlt!")
                    continue

                f.write(f'  call bcp_queryout.bat "{source_table.table_client}" "{select_query}"\n')
                f.write(
                    f'  call bcp_in.bat "{source_table.table_client}" '
                    f'"[{cfg.dest_dsn.schema}].[{dest_table.dest}]" "{dest_table.dest_db}"\n'
                )

            f.write("  goto :cleanup\n\n")
            f.write(":increment\n")

            f.write(f'  call sql_query.bat "TRUNCATE TABLE {dest_table.temp_table_name}"\n\n')
            for source_table in dest_table.source_tables:
                select_query = source_table.select_query_with_columns.replace("%", "%%%%")  # batch-Problem
                convert_timestamp = "T1.[timestamp] > convert(binary(8), '%TS%', 1)"
                if "WHERE" in select_query:
                    select_query = select_query.replace("WHERE", f"WHERE {convert_timestamp} AND")
                else:
                    print("Dont know where to put WHERE")
                f.write(
                    f'  call sql_timestamp.bat "{source_table.table_client}" "{dest_table.full_table_name}" "{source_table.client_db}"\n'
                )
                f.write(f'  call bcp_queryout.bat "{source_table.table_client}" "{select_query}"\n')
                f.write(
                    f'  call bcp_in.bat "{source_table.table_client}" "[temp].[{dest_table.dest}]" "{cfg.temp_db}"\n\n'
                )

            if dest_table.delete_query == "":
                print(dest_table.dest + " hat keinen Primaerschluessel")
                f.write(f"  rem {dest_table.dest} hat keinen Primaerschluessel")
            else:
                f.write(f'  call sql_query.bat "{dest_table.delete_query}"\n')

            f.write(f'  call sql_query.bat "{dest_table.insert_query}"\n')

            f.write("\n:cleanup\n")
            for source_table in dest_table.source_tables:
                f.write(f'  call delete.bat "{source_table.stage_csv}"\n')

    with open(f"{cfg.batch_dir}/_{cfg.name}.bat", "w", encoding="cp850") as f:
        f.write("@echo off & cd /d %~dp0\n")
        f.write(f"del {cfg.stage_dir}\\*.* /Q /F >nul 2>nul\n\n")
        for dest_table in table_config:
            f.write(f"echo =={dest_table.dest}==\n")
            f.write(f"echo {dest_table.dest} >CON\n")
            f.write(f"call {cfg.batch_dir}\\{dest_table.dest}.bat 1\n\n")

    with open(f"{cfg.batch_dir}/_{cfg.name}_full_load.bat", "w", encoding="cp850") as f:
        f.write("@echo off & cd /d %~dp0\n")
        f.write(f"del {cfg.stage_dir}\\*.* /Q /F >nul 2>nul\n\n")
        for dest_table in table_config:
            f.write(f"echo =={dest_table.dest}==\n")
            f.write(f"echo {dest_table.dest} >CON\n")
            f.write(f"call {cfg.batch_dir}\\{dest_table.dest}.bat\n\n")