119 lines
6.8 KiB
Python
119 lines
6.8 KiB
Python
import pyodbc
|
|
import config
|
|
from logger import get_logger
|
|
|
|
logger = get_logger(__name__)
|
|
|
|
def run_migration() -> bool:
|
|
"""
|
|
Ejecuta la migración de base de datos para agregar las columnas necesarias a SQL Server.
|
|
"""
|
|
conn_str = (
|
|
f"DRIVER={config.DB_DRIVER};"
|
|
f"SERVER={config.DB_SERVER};"
|
|
f"DATABASE={config.DB_NAME};"
|
|
f"Trusted_Connection=yes;"
|
|
)
|
|
conn = None
|
|
try:
|
|
conn = pyodbc.connect(conn_str)
|
|
cursor = conn.cursor()
|
|
|
|
# 1. Add 'tipo' column to Recursos if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Recursos' AND column_name = 'tipo'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Recursos ADD tipo NVARCHAR(50) NOT NULL DEFAULT 'MAQUINA'")
|
|
logger.info("Columna 'tipo' agregada a la tabla 'Recursos'.")
|
|
print("Agregada columna 'tipo' a 'Recursos'.")
|
|
|
|
# 2. Add 'id_hm' column to Relevamientos if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Relevamientos' AND column_name = 'id_hm'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Relevamientos ADD id_hm INT NULL CONSTRAINT FK_Rel_HM FOREIGN KEY (id_hm) REFERENCES Recursos(id_recurso)")
|
|
logger.info("Columna 'id_hm' agregada a la tabla 'Relevamientos'.")
|
|
print("Agregada columna 'id_hm' a 'Relevamientos'.")
|
|
|
|
# 3. Add 'piezas_por_golpe' column to Relevamientos if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Relevamientos' AND column_name = 'piezas_por_golpe'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Relevamientos ADD piezas_por_golpe INT NOT NULL DEFAULT 1")
|
|
logger.info("Columna 'piezas_por_golpe' agregada a la tabla 'Relevamientos'.")
|
|
print("Agregada columna 'piezas_por_golpe' a 'Relevamientos'.")
|
|
|
|
# 4. Add 'articulos_por_golpe' column to Relevamientos if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Relevamientos' AND column_name = 'articulos_por_golpe'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Relevamientos ADD articulos_por_golpe INT NOT NULL DEFAULT 1")
|
|
logger.info("Columna 'articulos_por_golpe' agregada a la tabla 'Relevamientos'.")
|
|
print("Agregada columna 'articulos_por_golpe' a 'Relevamientos'.")
|
|
|
|
# 5. Add 'operarios_maquina' column to Relevamientos if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Relevamientos' AND column_name = 'operarios_maquina'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Relevamientos ADD operarios_maquina INT NOT NULL DEFAULT 1")
|
|
logger.info("Columna 'operarios_maquina' agregada a la tabla 'Relevamientos'.")
|
|
print("Agregada columna 'operarios_maquina' a 'Relevamientos'.")
|
|
|
|
# 6. Add 'id_tarea' column to Relevamientos if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Relevamientos' AND column_name = 'id_tarea'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Relevamientos ADD id_tarea INT NULL CONSTRAINT FK_Rel_Tarea FOREIGN KEY (id_tarea) REFERENCES Tareas(id_tarea)")
|
|
logger.info("Columna 'id_tarea' agregada a la tabla 'Relevamientos'.")
|
|
print("Agregada columna 'id_tarea' a 'Relevamientos'.")
|
|
|
|
# 7. Add 'elementos_medidos' column to TiemposElemento if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'TiemposElemento' AND column_name = 'elementos_medidos'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE TiemposElemento ADD elementos_medidos INT NOT NULL DEFAULT 1")
|
|
logger.info("Columna 'elementos_medidos' agregada a la tabla 'TiemposElemento'.")
|
|
print("Agregada columna 'elementos_medidos' a 'TiemposElemento'.")
|
|
|
|
# 8. Add 'lote_frecuencial' column to TiemposElemento if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'TiemposElemento' AND column_name = 'lote_frecuencial'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE TiemposElemento ADD lote_frecuencial FLOAT NOT NULL DEFAULT 1.0")
|
|
logger.info("Columna 'lote_frecuencial' agregada a la tabla 'TiemposElemento'.")
|
|
print("Agregada columna 'lote_frecuencial' a 'TiemposElemento'.")
|
|
|
|
# 9. Add 'suplemento' column to TiemposElemento if it doesn't exist
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'TiemposElemento' AND column_name = 'suplemento'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE TiemposElemento ADD suplemento FLOAT NOT NULL DEFAULT 0.0")
|
|
logger.info("Columna 'suplemento' agregada a la tabla 'TiemposElemento'.")
|
|
print("Agregada columna 'suplemento' a 'TiemposElemento'.")
|
|
|
|
# 10. ALTER COLUMN minutos de INT a FLOAT en TiemposElemento (BUG 1: Truncamiento SQL)
|
|
cursor.execute("""
|
|
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = 'TiemposElemento' AND COLUMN_NAME = 'minutos'
|
|
""")
|
|
col_type_row = cursor.fetchone()
|
|
if col_type_row and col_type_row[0].lower() != 'float':
|
|
cursor.execute("ALTER TABLE TiemposElemento ALTER COLUMN minutos FLOAT")
|
|
logger.info("Columna 'minutos' de TiemposElemento migrada de INT a FLOAT.")
|
|
print("Migrada columna 'minutos' a FLOAT en 'TiemposElemento'.")
|
|
|
|
# 11. ADD columna 'codigo' a Articulos (BUG 2: Desfase de Artículo)
|
|
cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'Articulos' AND column_name = 'codigo'")
|
|
if not cursor.fetchone():
|
|
cursor.execute("ALTER TABLE Articulos ADD codigo NVARCHAR(50) NULL")
|
|
logger.info("Columna 'codigo' agregada a la tabla 'Articulos'.")
|
|
print("Agregada columna 'codigo' a 'Articulos'.")
|
|
|
|
conn.commit()
|
|
logger.info("Migración de base de datos completada exitosamente.")
|
|
print("Migración completada exitosamente.")
|
|
return True
|
|
except Exception as e:
|
|
if conn:
|
|
conn.rollback()
|
|
logger.exception("Error crítico durante la migración de base de datos.")
|
|
print(f"Error de migración: {e}")
|
|
return False
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
run_migration()
|