Sebastian Serfling
93 lines
3.0 KiB
Python
93 lines
3.0 KiB
Python
import os
|
||
import adodbapi
|
||
import pyodbc
|
||
from dotenv import load_dotenv
|
||
|
||
load_dotenv()
|
||
|
||
# --- ENV Variablen ---
|
||
SDF_LOCAL_PFAD = os.getenv("SDF_LOCAL_PFAD")
|
||
SDF_NAME = os.getenv("SDF_NAME", "App.sdf")
|
||
sdf_file = os.path.join(SDF_LOCAL_PFAD, SDF_NAME)
|
||
MSSQL_CONNECTION_STR = os.getenv("MSSQL_CONNECTION_STR")
|
||
tables = [t.strip() for t in os.getenv("TABLES", "").split(",") if t.strip()]
|
||
|
||
print(f"📂 Verbinde mit SDF: {sdf_file}")
|
||
sdf_conn = adodbapi.connect(
|
||
f"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source={sdf_file};Persist Security Info=False;"
|
||
)
|
||
sdf_cursor = sdf_conn.cursor()
|
||
|
||
print("🔗 Verbinde mit MSSQL...")
|
||
mssql_conn = pyodbc.connect(MSSQL_CONNECTION_STR)
|
||
mssql_cursor = mssql_conn.cursor()
|
||
|
||
|
||
# --- Hilfsfunktion zur Datentyp-Konvertierung ---
|
||
def sdf_to_mssql_type(sdf_type, length):
|
||
sdf_type = sdf_type.lower()
|
||
if "int" in sdf_type:
|
||
return "INT"
|
||
if "float" in sdf_type or "double" in sdf_type:
|
||
return "FLOAT"
|
||
if "decimal" in sdf_type or "numeric" in sdf_type:
|
||
return "DECIMAL(18,2)"
|
||
if "datetime" in sdf_type or "date" in sdf_type:
|
||
return "DATETIME"
|
||
if "bit" in sdf_type or "bool" in sdf_type:
|
||
return "BIT"
|
||
if "image" in sdf_type or "binary" in sdf_type:
|
||
return "VARBINARY(MAX)"
|
||
if "ntext" in sdf_type or "text" in sdf_type:
|
||
return "NVARCHAR(MAX)"
|
||
if "char" in sdf_type or "string" in sdf_type:
|
||
return f"NVARCHAR({length if length and length > 0 else 255})"
|
||
return "NVARCHAR(MAX)"
|
||
|
||
|
||
# --- Tabellen erzeugen ---
|
||
for table in tables:
|
||
print(f"\n📋 Analysiere Tabelle: {table}")
|
||
try:
|
||
sdf_cursor.execute(f"SELECT * FROM [{table}] WHERE 1=0")
|
||
columns = []
|
||
column_names = []
|
||
|
||
for col in sdf_cursor.description:
|
||
col_name = col[0]
|
||
if col_name.lower() == "id":
|
||
# Id-Spalte wird immer INT PRIMARY KEY
|
||
columns.append("[Id] INT PRIMARY KEY")
|
||
else:
|
||
col_type = str(col[1])
|
||
col_len = col[3]
|
||
sql_type = sdf_to_mssql_type(col_type, col_len)
|
||
columns.append(f"[{col_name}] {sql_type}")
|
||
column_names.append(col_name)
|
||
|
||
# Prüfen, ob überhaupt eine Id-Spalte existiert
|
||
if "Id" not in column_names and "ID" not in column_names:
|
||
print("⚠️ Keine Id-Spalte gefunden – füge Id INT PRIMARY KEY hinzu.")
|
||
columns.insert(0, "[Id] INT PRIMARY KEY")
|
||
|
||
create_sql = f"CREATE TABLE [{table}] (\n " + ",\n ".join(columns) + "\n)"
|
||
print(create_sql)
|
||
|
||
try:
|
||
mssql_cursor.execute(create_sql)
|
||
mssql_conn.commit()
|
||
print(f"✅ Tabelle [{table}] in MSSQL erstellt.")
|
||
except Exception as inner:
|
||
print(f"⚠️ Tabelle [{table}] konnte nicht erstellt werden (vielleicht existiert sie bereits): {inner}")
|
||
|
||
except Exception as e:
|
||
print(f"❌ Fehler beim Lesen von {table}: {e}")
|
||
|
||
# --- Aufräumen ---
|
||
sdf_cursor.close()
|
||
sdf_conn.close()
|
||
mssql_cursor.close()
|
||
mssql_conn.close()
|
||
|
||
print("\n🏁 Fertig!")
|