""" =============================================================================== SCRIPT DE SINCRONIZAÇÃO COMPLETA: DBMAKER (SYSADM) -> ORACLE (SCHEMA VIMAN) =============================================================================== Contém a lista completa de todas as Views e Tabelas do schema SYSADM do DBMaker. Extrai todos os dados via Bridge HTTP, cria as tabelas no Oracle (schema VIMAN) automaticamente e faz carga em massa com alta performance e sanitização de tipos. Gera arquivos de log detalhados: - logs/sincronizacao_viman.log (Log completo de execução) - logs/erros_sincronizacao.log (Apenas erros para conferência rápida) Modos de uso: 1. Executar a cópia de TODAS as views/tabelas do SYSADM: python3 sincronizar_viman_oracle.py 2. Retomar a partir de uma tabela/índice (ex: índice 514 ou nome VETERI12): python3 sincronizar_viman_oracle.py --comecar-de 514 3. Recriar as tabelas no Oracle caso queira corrigir colunas antigas: python3 sincronizar_viman_oracle.py --recriar 4. Executar continuamente de hora em hora (Modo Loop): python3 sincronizar_viman_oracle.py --loop 5. Testar apenas UMA view ou tabela específica: python3 sincronizar_viman_oracle.py --tabela SYSADM.VETEPD01 """ import os import sys import time import json import re import argparse import traceback import signal from datetime import datetime, date import requests try: import oracledb except ImportError: oracledb = None # ============================================================================= # CONFIGURAÇÕES DE CONEXÃO # ============================================================================= # URL da Bridge Java HTTP conectada ao DBMaker BRIDGE_URL = "http://127.0.0.1:8099/query" # Configurações do Banco Oracle (Servidor de Produção) ORACLE_USER = "SANKHYA" # Usuário que conecta (ex: SANKHYA ou VIMAN) ORACLE_PASS = "vhsnkpr0d" # Senha do usuário de conexão ORACLE_HOST = "10.0.0.202" # IP do Servidor Oracle ORACLE_PORT = 1521 # Porta padrão Oracle ORACLE_SERVICE = "ORCL" # Service Name / SID do Oracle # Schema de destino onde as tabelas serão criadas e populadas (ex: VIMAN) ORACLE_SCHEMA = "VIMAN" # DSN montado para o python-oracledb (Thin Mode - sem precisar de client C) ORACLE_DSN = f"{ORACLE_HOST}:{ORACLE_PORT}/{ORACLE_SERVICE}" # Tamanho do lote de extração e inserção (registros por bloco) TAMANHO_LOTE = 1000 # Intervalo em segundos para o modo loop opcional (3600 = 1 hora) INTERVALO_LOOP_SEGUNDOS = 3600 # ============================================================================= # SISTEMA DE LOGS, LOCKFILE E TELEMETRIA EM ARQUIVO # ============================================================================= # Diretório base dos logs: prioriza a pasta 'logs' na raiz do projeto SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__)) PROJECT_DIR = os.path.dirname(SCRIPT_DIR) LOGS_DIR_CANDIDATE = os.path.join(PROJECT_DIR, "logs") if os.path.exists(LOGS_DIR_CANDIDATE) and os.path.isdir(LOGS_DIR_CANDIDATE): DIRETORIO_LOGS = LOGS_DIR_CANDIDATE else: DIRETORIO_LOGS = os.path.join(SCRIPT_DIR, "logs") try: os.makedirs(DIRETORIO_LOGS, exist_ok=True) except Exception: DIRETORIO_LOGS = SCRIPT_DIR ARQUIVO_LOG_GERAL = os.path.join(DIRETORIO_LOGS, "sincronizacao_viman.log") ARQUIVO_LOG_ERROS = os.path.join(DIRETORIO_LOGS, "erros_sincronizacao.log") ARQUIVO_STATUS_JSON = os.path.join(DIRETORIO_LOGS, "status_etl.json") ARQUIVO_LOCK = os.path.join(DIRETORIO_LOGS, ".sincronizacao.lock") def log_msg(mensagem: str, nivel: str = "INFO", imprimir: bool = False): """Grava mensagem no log geral e opcionalmente imprime no terminal.""" timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S") linha = f"[{timestamp}] [{nivel:<5}] {mensagem}" if imprimir: print(mensagem) try: with open(ARQUIVO_LOG_GERAL, "a", encoding="utf-8") as f: f.write(linha + "\n") except Exception: pass def log_erro(tabela: str, erro_msg: str, detalhes: str = None): """Grava o erro no arquivo de log geral e no arquivo exclusivo de erros.""" timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S") linha_curta = f"[{timestamp}] [ERRO] {tabela}: {erro_msg}" try: with open(ARQUIVO_LOG_GERAL, "a", encoding="utf-8") as f: f.write(linha_curta + "\n") if detalhes: f.write(f" [DETALHES]: {detalhes}\n") except Exception: pass try: with open(ARQUIVO_LOG_ERROS, "a", encoding="utf-8") as f: f.write(linha_curta + "\n") if detalhes: f.write(f" [DETALHES]: {detalhes}\n") except Exception: pass def is_pid_running(pid: int) -> bool: """Verifica de forma portável (Linux/Windows) se um PID de processo está em execução.""" if pid <= 0: return False try: if os.name == 'nt': import ctypes kernel32 = ctypes.windll.kernel32 SYNCHRONIZE = 0x00100000 process = kernel32.OpenProcess(SYNCHRONIZE, False, pid) if process != 0: kernel32.CloseHandle(process) return True return False else: os.kill(pid, 0) return True except (OSError, ProcessLookupError): return False except Exception: return False def salvar_status_etl(dados: dict): """Salva estado e progresso em JSON atômico para leitura pelo painel web.""" try: dados_completos = { "atualizado_em": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), **dados } arquivo_temp = f"{ARQUIVO_STATUS_JSON}.tmp" with open(arquivo_temp, "w", encoding="utf-8") as f: json.dump(dados_completos, f, indent=2, ensure_ascii=False) if os.path.exists(ARQUIVO_STATUS_JSON): os.remove(ARQUIVO_STATUS_JSON) os.rename(arquivo_temp, ARQUIVO_STATUS_JSON) except Exception: pass def adquirir_lock(modo: str = "manual") -> bool: """Garante exclusividade de execução via Lockfile, evitando concorrência.""" pid_atual = os.getpid() if os.path.exists(ARQUIVO_LOCK): try: with open(ARQUIVO_LOCK, "r", encoding="utf-8") as f: conteudo = json.load(f) pid_antigo = conteudo.get("pid") inicio_antigo = conteudo.get("inicio", "data desconhecida") if pid_antigo and is_pid_running(pid_antigo): msg = f"[AVISO] O processo de sincronizacao ja esta em andamento (PID: {pid_antigo}, iniciado em: {inicio_antigo}). Abortando nova execucao." print(msg) log_msg(msg, nivel="AVISO") return False else: # Processo anterior já encerrou ou morreu try: os.remove(ARQUIVO_LOCK) except Exception: pass except Exception: try: os.remove(ARQUIVO_LOCK) except Exception: pass try: info_lock = { "pid": pid_atual, "inicio": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "modo": modo } with open(ARQUIVO_LOCK, "w", encoding="utf-8") as f: json.dump(info_lock, f, indent=2) return True except Exception as e: log_msg(f"Nao foi possivel criar o arquivo de lock: {e}", nivel="ERRO") return True def liberar_lock(): """Remove o lockfile se pertencer ao PID atual.""" try: if os.path.exists(ARQUIVO_LOCK): with open(ARQUIVO_LOCK, "r", encoding="utf-8") as f: conteudo = json.load(f) if conteudo.get("pid") == os.getpid(): os.remove(ARQUIVO_LOCK) except Exception: try: if os.path.exists(ARQUIVO_LOCK): os.remove(ARQUIVO_LOCK) except Exception: pass # ============================================================================= # MAPEAMENTO DE COLUNAS DE DATA # ============================================================================= COLUNAS_DATA_EXPLICITAS = { # Pedidos / Vendas 'PDDTPD', 'PDDTEN', 'PDDTCG', 'PDDTCA', 'PDDTEB', 'PDDTFE', # Vales / Cirurgias 'VADTDG', 'VADTCG', 'VADTFE', 'VADTRC', 'VADTF1', 'VADTNV', # Lotes / Movimentações de Estoque 'L3DTVC', 'L3DTEN', 'L3DTSA', 'L1DTVC', 'L1DTEN', 'L1DTSA', # Notas Fiscais 'NFDTEM', 'NFDTES', 'NFDTCA', 'INDTEM', # Cadastros 'PRDTCB', 'CLDTCD', 'FODTCD', 'VNDTCD', # Financeiro / Contábil 'DTVENC', 'DTPAGTO', 'DTEMISSAO', 'DTMOV', 'DTCADASTRO' } # ============================================================================= # LISTA COMPLETA DE TODAS AS VIEWS / TABELAS DO SYSADM (DBMAKER) # ============================================================================= TABELAS_SYSADM = [ "SYSADM.VEBCCA04", "SYSADM.VEBCCA05", "SYSADM.VEBCCA10", "SYSADM.VEBCCA11", "SYSADM.VEBCCA12", "SYSADM.VEBCCA13", "SYSADM.VEBCCP01", "SYSADM.VEBCCP02", "SYSADM.VEBCCP03", "SYSADM.VEBCCP04", "SYSADM.VEBCCP05", "SYSADM.VEBCCP10", "SYSADM.VEBCCP11", "SYSADM.VEBCCP12", "SYSADM.VEBCCP13", "SYSADM.VECFCC", "SYSADM.VECFCU", "SYSADM.VECFGC01", "SYSADM.VECFGC02", "SYSADM.VECFGC03", "SYSADM.VECFGC04", "SYSADM.VECFGC05", "SYSADM.VECFGC10", "SYSADM.VECFGC11", "SYSADM.VECFGC12", "SYSADM.VECFGC13", "SYSADM.VECFMV", "SYSADM.VECFST01", "SYSADM.VECFST02", "SYSADM.VECFST03", "SYSADM.VECFST04", "SYSADM.VECFST05", "SYSADM.VECFST10", "SYSADM.VECFST11", "SYSADM.VECFST12", "SYSADM.VECFST13", "SYSADM.VEINCN", "SYSADM.VEINEN", "SYSADM.VELFIE", "SYSADM.VELFIS", "SYSADM.VELFNE", "SYSADM.VELFNMLP", "SYSADM.VELFNMLR", "SYSADM.VELFNS", "SYSADM.VELFRV", "SYSADM.VETEAG01", "SYSADM.VETEAG02", "SYSADM.VETEAG03", "SYSADM.VETEAG04", "SYSADM.VETEAG05", "SYSADM.VETEAG06", "SYSADM.VETEAG07", "SYSADM.VETEAG08", "SYSADM.VETEAG09", "SYSADM.VETEAG10", "SYSADM.VETEAG11", "SYSADM.VETEAG12", "SYSADM.VETEAG13", "SYSADM.VETEBP", "SYSADM.VETEC701", "SYSADM.VETEC702", "SYSADM.VETEC703", "SYSADM.VETEC704", "SYSADM.VETEC705", "SYSADM.VETEC706", "SYSADM.VETEC707", "SYSADM.VETEC708", "SYSADM.VETEC709", "SYSADM.VETEC710", "SYSADM.VETEC711", "SYSADM.VETEC712", "SYSADM.VETEC713", "SYSADM.VETECL", "SYSADM.VETECM", "SYSADM.VETECV", "SYSADM.VETECX01", "SYSADM.VETECX02", "SYSADM.VETECX03", "SYSADM.VETECX04", "SYSADM.VETECX05", "SYSADM.VETECX10", "SYSADM.VETECX11", "SYSADM.VETECX12", "SYSADM.VETECX13", "SYSADM.VETED101", "SYSADM.VETED102", "SYSADM.VETED103", "SYSADM.VETED104", "SYSADM.VETED105", "SYSADM.VETED106", "SYSADM.VETED107", "SYSADM.VETED108", "SYSADM.VETED109", "SYSADM.VETED110", "SYSADM.VETED111", "SYSADM.VETED112", "SYSADM.VETED113", "SYSADM.VETEDA", "SYSADM.VETEDC01", "SYSADM.VETEDC02", "SYSADM.VETEDC03", "SYSADM.VETEDC04", "SYSADM.VETEDC05", "SYSADM.VETEDC06", "SYSADM.VETEDC07", "SYSADM.VETEDC08", "SYSADM.VETEDC09", "SYSADM.VETEDC10", "SYSADM.VETEDC11", "SYSADM.VETEDC12", "SYSADM.VETEDC13", "SYSADM.VETEDN01", "SYSADM.VETEDN02", "SYSADM.VETEDN03", "SYSADM.VETEDN04", "SYSADM.VETEDN05", "SYSADM.VETEDN06", "SYSADM.VETEDN07", "SYSADM.VETEDN08", "SYSADM.VETEDN09", "SYSADM.VETEDN10", "SYSADM.VETEDN11", "SYSADM.VETEDN12", "SYSADM.VETEDN13", "SYSADM.VETEDP01", "SYSADM.VETEDP02", "SYSADM.VETEDP03", "SYSADM.VETEDP04", "SYSADM.VETEDP05", "SYSADM.VETEDP10", "SYSADM.VETEDP11", "SYSADM.VETEDP12", "SYSADM.VETEDP13", "SYSADM.VETEEA", "SYSADM.VETEEC01", "SYSADM.VETEEC02", "SYSADM.VETEEC03", "SYSADM.VETEEC04", "SYSADM.VETEEC05", "SYSADM.VETEEC06", "SYSADM.VETEEC07", "SYSADM.VETEEC08", "SYSADM.VETEEC09", "SYSADM.VETEEC10", "SYSADM.VETEEC11", "SYSADM.VETEEC12", "SYSADM.VETEEC13", "SYSADM.VETEES01", "SYSADM.VETEES02", "SYSADM.VETEES03", "SYSADM.VETEES04", "SYSADM.VETEES05", "SYSADM.VETEES06", "SYSADM.VETEES07", "SYSADM.VETEES08", "SYSADM.VETEES09", "SYSADM.VETEES10", "SYSADM.VETEES11", "SYSADM.VETEES12", "SYSADM.VETEES13", "SYSADM.VETEFC", "SYSADM.VETEFU", "SYSADM.VETEG8", "SYSADM.VETEG9", "SYSADM.VETEI101", "SYSADM.VETEI102", "SYSADM.VETEI103", "SYSADM.VETEI104", "SYSADM.VETEI105", "SYSADM.VETEI106", "SYSADM.VETEI107", "SYSADM.VETEI108", "SYSADM.VETEI109", "SYSADM.VETEI110", "SYSADM.VETEI111", "SYSADM.VETEI112", "SYSADM.VETEI113", "SYSADM.VETEII01", "SYSADM.VETEII02", "SYSADM.VETEII03", "SYSADM.VETEII04", "SYSADM.VETEII05", "SYSADM.VETEII06", "SYSADM.VETEII07", "SYSADM.VETEII08", "SYSADM.VETEII09", "SYSADM.VETEII10", "SYSADM.VETEII11", "SYSADM.VETEII12", "SYSADM.VETEII13", "SYSADM.VETEIK01", "SYSADM.VETEIK02", "SYSADM.VETEIK03", "SYSADM.VETEIK04", "SYSADM.VETEIK05", "SYSADM.VETEIK06", "SYSADM.VETEIK07", "SYSADM.VETEIK08", "SYSADM.VETEIK09", "SYSADM.VETEIK10", "SYSADM.VETEIK11", "SYSADM.VETEIK12", "SYSADM.VETEIK13", "SYSADM.VETEIN01", "SYSADM.VETEIN02", "SYSADM.VETEIN03", "SYSADM.VETEIN04", "SYSADM.VETEIN05", "SYSADM.VETEIN06", "SYSADM.VETEIN07", "SYSADM.VETEIN08", "SYSADM.VETEIN09", "SYSADM.VETEIN10", "SYSADM.VETEIN11", "SYSADM.VETEIN12", "SYSADM.VETEIN13", "SYSADM.VETEIP01", "SYSADM.VETEIP02", "SYSADM.VETEIP03", "SYSADM.VETEIP04", "SYSADM.VETEIP05", "SYSADM.VETEIP06", "SYSADM.VETEIP07", "SYSADM.VETEIP08", "SYSADM.VETEIP09", "SYSADM.VETEIP10", "SYSADM.VETEIP11", "SYSADM.VETEIP12", "SYSADM.VETEIP13", "SYSADM.VETEIR01", "SYSADM.VETEIR02", "SYSADM.VETEIR03", "SYSADM.VETEIR04", "SYSADM.VETEIR05", "SYSADM.VETEIR06", "SYSADM.VETEIR07", "SYSADM.VETEIR08", "SYSADM.VETEIR09", "SYSADM.VETEIR10", "SYSADM.VETEIR11", "SYSADM.VETEIR12", "SYSADM.VETEIR13", "SYSADM.VETEIT01", "SYSADM.VETEIT02", "SYSADM.VETEIT03", "SYSADM.VETEIT04", "SYSADM.VETEIT05", "SYSADM.VETEIT06", "SYSADM.VETEIT07", "SYSADM.VETEIT08", "SYSADM.VETEIT09", "SYSADM.VETEIT10", "SYSADM.VETEIT11", "SYSADM.VETEIT12", "SYSADM.VETEIT13", "SYSADM.VETEIV01", "SYSADM.VETEIV02", "SYSADM.VETEIV03", "SYSADM.VETEIV04", "SYSADM.VETEIV05", "SYSADM.VETEIV06", "SYSADM.VETEIV07", "SYSADM.VETEIV08", "SYSADM.VETEIV09", "SYSADM.VETEIV10", "SYSADM.VETEIV11", "SYSADM.VETEIV12", "SYSADM.VETEIV13", "SYSADM.VETEL101", "SYSADM.VETEL102", "SYSADM.VETEL103", "SYSADM.VETEL104", "SYSADM.VETEL105", "SYSADM.VETEL106", "SYSADM.VETEL107", "SYSADM.VETEL108", "SYSADM.VETEL109", "SYSADM.VETEL110", "SYSADM.VETEL111", "SYSADM.VETEL112", "SYSADM.VETEL113", "SYSADM.VETEL301", "SYSADM.VETEL302", "SYSADM.VETEL303", "SYSADM.VETEL304", "SYSADM.VETEL305", "SYSADM.VETEL306", "SYSADM.VETEL307", "SYSADM.VETEL308", "SYSADM.VETEL309", "SYSADM.VETEL310", "SYSADM.VETEL311", "SYSADM.VETEL312", "SYSADM.VETEL313", "SYSADM.VETEL401", "SYSADM.VETEL402", "SYSADM.VETEL403", "SYSADM.VETEL404", "SYSADM.VETEL405", "SYSADM.VETEL406", "SYSADM.VETEL407", "SYSADM.VETEL408", "SYSADM.VETEL409", "SYSADM.VETEL410", "SYSADM.VETEL411", "SYSADM.VETEL412", "SYSADM.VETEL413", "SYSADM.VETEL501", "SYSADM.VETEL502", "SYSADM.VETEL503", "SYSADM.VETEL504", "SYSADM.VETEL505", "SYSADM.VETEL506", "SYSADM.VETEL507", "SYSADM.VETEL508", "SYSADM.VETEL509", "SYSADM.VETEL510", "SYSADM.VETEL511", "SYSADM.VETEL512", "SYSADM.VETEL513", "SYSADM.VETEL701", "SYSADM.VETEL702", "SYSADM.VETEL703", "SYSADM.VETEL704", "SYSADM.VETEL705", "SYSADM.VETEL706", "SYSADM.VETEL707", "SYSADM.VETEL708", "SYSADM.VETEL709", "SYSADM.VETEL801", "SYSADM.VETEL802", "SYSADM.VETEL803", "SYSADM.VETEL804", "SYSADM.VETEL805", "SYSADM.VETEL806", "SYSADM.VETEL807", "SYSADM.VETEL808", "SYSADM.VETEL809", "SYSADM.VETELC01", "SYSADM.VETELC02", "SYSADM.VETELC03", "SYSADM.VETELC04", "SYSADM.VETELC05", "SYSADM.VETELC10", "SYSADM.VETELC11", "SYSADM.VETELC12", "SYSADM.VETELC13", "SYSADM.VETELG", "SYSADM.VETELI", "SYSADM.VETELL", "SYSADM.VETELO01", "SYSADM.VETELO02", "SYSADM.VETELO03", "SYSADM.VETELO04", "SYSADM.VETELO05", "SYSADM.VETELO06", "SYSADM.VETELO07", "SYSADM.VETELO08", "SYSADM.VETELO09", "SYSADM.VETELO10", "SYSADM.VETELO11", "SYSADM.VETELO12", "SYSADM.VETELO13", "SYSADM.VETELV", "SYSADM.VETEMA", "SYSADM.VETEMB01", "SYSADM.VETEMB02", "SYSADM.VETEMB03", "SYSADM.VETEMB04", "SYSADM.VETEMB05", "SYSADM.VETEMB10", "SYSADM.VETEMB11", "SYSADM.VETEMB12", "SYSADM.VETEMB13", "SYSADM.VETEMC01", "SYSADM.VETEMC02", "SYSADM.VETEMC03", "SYSADM.VETEMC04", "SYSADM.VETEMC05", "SYSADM.VETEMC10", "SYSADM.VETEMC11", "SYSADM.VETEMC12", "SYSADM.VETEMC13", "SYSADM.VETEMM01", "SYSADM.VETEMM02", "SYSADM.VETEMM03", "SYSADM.VETEMM04", "SYSADM.VETEMM05", "SYSADM.VETEMM06", "SYSADM.VETEMM07", "SYSADM.VETEMM08", "SYSADM.VETEMM09", "SYSADM.VETEMM10", "SYSADM.VETEMM11", "SYSADM.VETEMM12", "SYSADM.VETEMM13", "SYSADM.VETEMV01", "SYSADM.VETEMV02", "SYSADM.VETEMV03", "SYSADM.VETEMV04", "SYSADM.VETEMV05", "SYSADM.VETEMV06", "SYSADM.VETEMV07", "SYSADM.VETEMV08", "SYSADM.VETEMV09", "SYSADM.VETEMV10", "SYSADM.VETEMV11", "SYSADM.VETEMV12", "SYSADM.VETEMV13", "SYSADM.VETENB01", "SYSADM.VETENB02", "SYSADM.VETENB03", "SYSADM.VETENB04", "SYSADM.VETENB05", "SYSADM.VETENB10", "SYSADM.VETENB11", "SYSADM.VETENB12", "SYSADM.VETENB13", "SYSADM.VETENC", "SYSADM.VETENI01", "SYSADM.VETENI02", "SYSADM.VETENI03", "SYSADM.VETENI04", "SYSADM.VETENI05", "SYSADM.VETENI06", "SYSADM.VETENI07", "SYSADM.VETENI08", "SYSADM.VETENI09", "SYSADM.VETENI10", "SYSADM.VETENI11", "SYSADM.VETENI12", "SYSADM.VETENI13", "SYSADM.VETENT01", "SYSADM.VETENT02", "SYSADM.VETENT03", "SYSADM.VETENT04", "SYSADM.VETENT05", "SYSADM.VETENT06", "SYSADM.VETENT07", "SYSADM.VETENT08", "SYSADM.VETENT09", "SYSADM.VETENT10", "SYSADM.VETENT11", "SYSADM.VETENT12", "SYSADM.VETENT13", "SYSADM.VETEPD01", "SYSADM.VETEPD02", "SYSADM.VETEPD03", "SYSADM.VETEPD04", "SYSADM.VETEPD05", "SYSADM.VETEPD06", "SYSADM.VETEPD07", "SYSADM.VETEPD08", "SYSADM.VETEPD09", "SYSADM.VETEPD10", "SYSADM.VETEPD11", "SYSADM.VETEPD12", "SYSADM.VETEPD13", "SYSADM.VETEPL", "SYSADM.VETEPM", "SYSADM.VETEPN", "SYSADM.VETEPP", "SYSADM.VETEPR", "SYSADM.VETEPU", "SYSADM.VETEPV01", "SYSADM.VETEPV02", "SYSADM.VETEPV03", "SYSADM.VETEPV04", "SYSADM.VETEPV05", "SYSADM.VETEPV06", "SYSADM.VETEPV07", "SYSADM.VETEPV08", "SYSADM.VETEPV09", "SYSADM.VETEPV10", "SYSADM.VETEPV11", "SYSADM.VETEPV12", "SYSADM.VETEPV13", "SYSADM.VETEPW", "SYSADM.VETEQU01", "SYSADM.VETEQU02", "SYSADM.VETEQU03", "SYSADM.VETEQU04", "SYSADM.VETEQU05", "SYSADM.VETEQU06", "SYSADM.VETEQU07", "SYSADM.VETEQU08", "SYSADM.VETEQU09", "SYSADM.VETERA", "SYSADM.VETERE01", "SYSADM.VETERE02", "SYSADM.VETERE03", "SYSADM.VETERE04", "SYSADM.VETERE05", "SYSADM.VETERE06", "SYSADM.VETERE07", "SYSADM.VETERE08", "SYSADM.VETERE09", "SYSADM.VETERE10", "SYSADM.VETERE11", "SYSADM.VETERE12", "SYSADM.VETERE13", "SYSADM.VETERG", "SYSADM.VETERI01", "SYSADM.VETERI02", "SYSADM.VETERI03", "SYSADM.VETERI04", "SYSADM.VETERI05", "SYSADM.VETERI06", "SYSADM.VETERI07", "SYSADM.VETERI08", "SYSADM.VETERI09", "SYSADM.VETERI10", "SYSADM.VETERI11", "SYSADM.VETERI12", "SYSADM.VETERI13", "SYSADM.VETERQ01", "SYSADM.VETERQ02", "SYSADM.VETERQ03", "SYSADM.VETERQ04", "SYSADM.VETERQ05", "SYSADM.VETERQ06", "SYSADM.VETERQ07", "SYSADM.VETERQ08", "SYSADM.VETERQ09", "SYSADM.VETERQ10", "SYSADM.VETERQ11", "SYSADM.VETERQ12", "SYSADM.VETERQ13", "SYSADM.VETERR", "SYSADM.VETERU", "SYSADM.VETESA", "SYSADM.VETESV01", "SYSADM.VETESV02", "SYSADM.VETESV03", "SYSADM.VETESV04", "SYSADM.VETESV05", "SYSADM.VETESV06", "SYSADM.VETESV07", "SYSADM.VETESV08", "SYSADM.VETESV09", "SYSADM.VETESV10", "SYSADM.VETESV11", "SYSADM.VETESV12", "SYSADM.VETESV13", "SYSADM.VETET110", "SYSADM.VETET1ES", "SYSADM.VETET1MG", "SYSADM.VETET1RJ", "SYSADM.VETET1SP", "SYSADM.VETETC", "SYSADM.VETETC01", "SYSADM.VETETG", "SYSADM.VETETI01", "SYSADM.VETETI02", "SYSADM.VETETI03", "SYSADM.VETETI04", "SYSADM.VETETI05", "SYSADM.VETETI06", "SYSADM.VETETI07", "SYSADM.VETETI08", "SYSADM.VETETI09", "SYSADM.VETETI10", "SYSADM.VETETI11", "SYSADM.VETETI12", "SYSADM.VETETI13", "SYSADM.VETETO", "SYSADM.VETETP", "SYSADM.VETETR", "SYSADM.VETETV", "SYSADM.VETEV101", "SYSADM.VETEV102", "SYSADM.VETEV103", "SYSADM.VETEV104", "SYSADM.VETEV105", "SYSADM.VETEV106", "SYSADM.VETEV107", "SYSADM.VETEV108", "SYSADM.VETEV109", "SYSADM.VETEV110", "SYSADM.VETEV111", "SYSADM.VETEV112", "SYSADM.VETEV113", "SYSADM.VETEV8", "SYSADM.VETEV9", "SYSADM.VETEVA01", "SYSADM.VETEVA02", "SYSADM.VETEVA03", "SYSADM.VETEVA04", "SYSADM.VETEVA05", "SYSADM.VETEVA06", "SYSADM.VETEVA07", "SYSADM.VETEVA08", "SYSADM.VETEVA09", "SYSADM.VETEVA10", "SYSADM.VETEVA11", "SYSADM.VETEVA12", "SYSADM.VETEVA13", "SYSADM.VETEVC01", "SYSADM.VETEVC02", "SYSADM.VETEVC03", "SYSADM.VETEVC04", "SYSADM.VETEVC05", "SYSADM.VETEVC06", "SYSADM.VETEVC07", "SYSADM.VETEVC08", "SYSADM.VETEVC09", "SYSADM.VETEVC10", "SYSADM.VETEVC11", "SYSADM.VETEVC12", "SYSADM.VETEVC13", "SYSADM.VETEVH01", "SYSADM.VETEVH02", "SYSADM.VETEVH03", "SYSADM.VETEVH04", "SYSADM.VETEVH05", "SYSADM.VETEVH06", "SYSADM.VETEVH07", "SYSADM.VETEVH08", "SYSADM.VETEVH09", "SYSADM.VETEVH10", "SYSADM.VETEVH11", "SYSADM.VETEVH12", "SYSADM.VETEVH13", "SYSADM.VETEVI01", "SYSADM.VETEVI02", "SYSADM.VETEVI03", "SYSADM.VETEVI04", "SYSADM.VETEVI05", "SYSADM.VETEVI06", "SYSADM.VETEVI07", "SYSADM.VETEVI08", "SYSADM.VETEVI09", "SYSADM.VETEVI10", "SYSADM.VETEVI11", "SYSADM.VETEVI12", "SYSADM.VETEVI13", "SYSADM.VETEVN01", "SYSADM.VETEVN02", "SYSADM.VETEVN03", "SYSADM.VETEVN04", "SYSADM.VETEVN05", "SYSADM.VETEVN06", "SYSADM.VETEVN07", "SYSADM.VETEVN08", "SYSADM.VETEVN09", "SYSADM.VETEVN10", "SYSADM.VETEVN11", "SYSADM.VETEVN12", "SYSADM.VETEVN13", "SYSADM.VETEVP01", "SYSADM.VETEVP02", "SYSADM.VETEVP03", "SYSADM.VETEVP04", "SYSADM.VETEVP05", "SYSADM.VETEVP06", "SYSADM.VETEVP07", "SYSADM.VETEVP08", "SYSADM.VETEVP09", "SYSADM.VETEVP10", "SYSADM.VETEVP11", "SYSADM.VETEVP12", "SYSADM.VETEVP13", "SYSADM.VETEVV01", "SYSADM.VETEVV02", "SYSADM.VETEVV03", "SYSADM.VETEVV04", "SYSADM.VETEVV05", "SYSADM.VETEVV06", "SYSADM.VETEVV07", "SYSADM.VETEVV08", "SYSADM.VETEVV09", "SYSADM.VETEVV10", "SYSADM.VETEVV11", "SYSADM.VETEVV12", "SYSADM.VETEVV13", "SYSADM.VEVMAQ", "SYSADM.VEVMUS" ] # ============================================================================= # COMUNICAÇÃO COM O DBMAKER (COM POOL DE CONEXÕES E RETRY) # ============================================================================= # Sessão HTTP persistente para reutilizar conexões TCP (evita Connection Refused por esgotamento de portas) http_session = requests.Session() adapter = requests.adapters.HTTPAdapter(pool_connections=10, pool_maxsize=20, max_retries=3) http_session.mount('http://', adapter) http_session.mount('https://', adapter) def carregar_json_seguro(texto: str): """ Carrega o JSON com strict=False para permitir quebras de linha e caracteres de controle não escapados que venham de campos de texto/observações do DBMaker (evita JSONDecodeError: Invalid control character). """ try: return json.loads(texto, strict=False) except Exception: # Remove caracteres binários de controle nulos e preserva espaços texto_limpo = re.sub(r'[\x00-\x08\x0b\x0c\x0e-\x1f]', ' ', texto) return json.loads(texto_limpo, strict=False) def extrair_linhas(payload): """Extrai a lista de registros da resposta da Bridge Java HTTP.""" if isinstance(payload, list): return payload candidatos = ['data', 'rows', 'result', 'results'] for chave in candidatos: if chave in payload and isinstance(payload[chave], list): return payload[chave] return [v for v in payload.values() if isinstance(v, (dict, list))] def extrair_dbmaker(sql, max_tentativas=3): """Executa consulta SQL na Bridge HTTP do DBMaker com retry automático, backoff e JSON seguro.""" for tentativa in range(1, max_tentativas + 1): try: response = http_session.post(BRIDGE_URL, data={'sql': sql}, timeout=120) if response.status_code != 200: raise Exception(f"Erro HTTP {response.status_code}: {response.text[:200]}") data = carregar_json_seguro(response.text) if isinstance(data, dict) and 'error' in data: raise Exception(f"Erro DBMaker: {data['error']}") return extrair_linhas(data) except Exception as e: if tentativa == max_tentativas: raise Exception(f"Falha de comunicacao com a Bridge ({BRIDGE_URL}) apos {max_tentativas} tentativas: {e}") # Aguarda antes de tentar novamente (backoff para a bridge respirar) time.sleep(2 * tentativa) # ============================================================================= # TRATAMENTO DE TIPOS E DDL ORACLE # ============================================================================= def obter_tipos_colunas_oracle(cursor, tabela_nome: str) -> dict: """Retorna um dicionário {NOME_COLUNA: DATA_TYPE} da tabela no Oracle.""" cursor.execute(""" SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE OWNER = :owner AND TABLE_NAME = :tabela """, owner=ORACLE_SCHEMA, tabela=tabela_nome) return {row[0].upper(): row[1].upper() for row in cursor.fetchall()} def converter_valor(coluna: str, valor, tipo_oracle: str): """ Converte o valor para o tipo exato esperado pela coluna no Oracle: - Se a coluna no Oracle for DATE/TIMESTAMP: valida data estritamente e converte zeros/dias inválidos para None (evita ORA-01847). - Se a coluna no Oracle for VARCHAR2/CHAR: SEMPRE retorna string ou None (evita DPY-3013). - Se a coluna no Oracle for NUMBER: converte para numérico ou None. """ if valor is None: return None str_val = str(valor).strip() if str_val == '' or str_val.upper() == 'NULL': return None # CASO 1: Coluna de Data no Oracle (DATE / TIMESTAMP) if tipo_oracle in ('DATE', 'TIMESTAMP', 'TIMESTAMP(6)', 'TIMESTAMP(9)'): # 1.1 Formato numérico YYYYMMDD (ex: 20260901) if len(str_val) == 8 and str_val.isdigit(): ano = int(str_val[0:4]) mes = int(str_val[4:6]) dia = int(str_val[6:8]) # Validação rigorosa: se o dia/mês for 00 ou inválido, retorna None if 1900 <= ano <= 2150 and 1 <= mes <= 12 and 1 <= dia <= 31: try: return date(ano, mes, dia) except ValueError: return None # Ex: 30 de Fevereiro, dia 31 em mês de 30 dias -> vira NULL return None # Ex: 00000000 ou 20240000 -> vira NULL # 1.2 Formato ISO YYYY-MM-DD if len(str_val) >= 10 and str_val[4] == '-' and str_val[7] == '-': try: dt_part = str_val[:10] return datetime.strptime(dt_part, '%Y-%m-%d').date() except ValueError: return None # 1.3 Formato Brasileiro DD/MM/YYYY if len(str_val) >= 10 and str_val[2] == '/' and str_val[5] == '/': try: dt_part = str_val[:10] return datetime.strptime(dt_part, '%d/%m/%Y').date() except ValueError: return None # Não casou com data válida -> retorna None para não quebrar o Oracle return None # CASO 2: Coluna de Texto no Oracle (VARCHAR2, CHAR, CLOB) elif tipo_oracle in ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'CLOB', 'NCLOB'): # NUNCA retornar objeto datetime.date para colunas VARCHAR2 (evita DPY-3013) # Se for VARCHAR2/CHAR e exceder 4000 caracteres, trunca em 4000 para evitar ORA-12899 if tipo_oracle in ('VARCHAR2', 'CHAR', 'NVARCHAR2') and len(str_val) > 4000: return str_val[:4000] return str_val # CASO 3: Coluna Numérica no Oracle (NUMBER, FLOAT) elif tipo_oracle in ('NUMBER', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE'): str_num = str_val.replace(',', '.') try: if '.' in str_num: return float(str_num) return int(str_num) except ValueError: return None # Padrão: string limpa (truncada em 4000 se necessário) if len(str_val) > 4000: return str_val[:4000] return str_val def obter_nome_tabela_oracle(tabela_origem: str) -> str: """Extrai apenas o nome do objeto (ex: SYSADM.VETEPD01 -> VETEPD01).""" return tabela_origem.split('.')[-1].strip().upper() def garantir_tabela_oracle(cursor, tabela_nome: str, colunas_amostra: dict, recriar: bool = False): """Cria a tabela automaticamente no schema VIMAN caso ainda não exista.""" tabela_full = f"{ORACLE_SCHEMA}.{tabela_nome}" cursor.execute(""" SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = :owner AND TABLE_NAME = :tabela """, owner=ORACLE_SCHEMA, tabela=tabela_nome) (existe,) = cursor.fetchone() if existe > 0 and recriar: msg = f"Recriando tabela {tabela_full} no Oracle..." print(f"\n [AUTO-DDL] {msg}") log_msg(f"[AUTO-DDL] {msg}") try: cursor.execute(f"DROP TABLE {tabela_full} CASCADE CONSTRAINTS") existe = 0 except Exception as e: log_erro(tabela_full, f"Falha ao executar DROP TABLE: {e}") if existe > 0: return msg = f"Criando tabela {tabela_full} no Oracle..." print(f"\n [AUTO-DDL] {msg}") log_msg(f"[AUTO-DDL] {msg}") colunas_ddl = [] for col, val in colunas_amostra.items(): col_clean = col.strip().upper() str_val = str(val).strip() if val is not None else '' eh_data = ( col_clean in COLUNAS_DATA_EXPLICITAS or col_clean.startswith('DT_') or col_clean.endswith('_DT') or 'DATA' in col_clean or (len(col_clean) >= 4 and 'DT' in col_clean and len(str_val) == 8 and str_val.isdigit()) ) if eh_data: col_tipo = "DATE" elif isinstance(val, (int, float)): col_tipo = "NUMBER" else: col_tipo = "VARCHAR2(4000)" colunas_ddl.append(f" {col_clean} {col_tipo}") ddl = f"CREATE TABLE {tabela_full} (\n" + ",\n".join(colunas_ddl) + "\n)" try: cursor.execute(ddl) log_msg(f"[AUTO-DDL] Tabela {tabela_full} criada com sucesso!") print(f" [AUTO-DDL] Tabela {tabela_full} criada com sucesso!") except Exception as e: log_erro(tabela_full, f"Falha ao criar tabela: {e}") print(f" [AVISO DDL] Falha ao criar {tabela_full}: {e}") # ============================================================================= # ESPELHAMENTO DE UMA VIEW OU TABELA # ============================================================================= def espelhar_tabela(conn_oracle, tabela_origem: str, recriar: bool = False): """Copia todas as linhas de uma view/tabela do SYSADM para o schema VIMAN no Oracle.""" tabela_nome = obter_nome_tabela_oracle(tabela_origem) tabela_destino = f"{ORACLE_SCHEMA}.{tabela_nome}" cursor = conn_oracle.cursor() # 1. Pega 1 registro para validar colunas e estrutura try: primeiro_lote = extrair_dbmaker(f"SELECT * FROM {tabela_origem} LIMIT 1") except Exception as e: cursor.close() raise Exception(f"Erro ao ler {tabela_origem} no DBMaker: {e}") if not primeiro_lote: cursor.close() return 0 colunas_raw = list(primeiro_lote[0].keys()) colunas_oracle = [c.strip().upper() for c in colunas_raw] # Garante estrutura no Oracle garantir_tabela_oracle(cursor, tabela_nome, primeiro_lote[0], recriar=recriar) # Obtém os tipos reais de cada coluna no Oracle para sincronizar conversão tipos_oracle = obter_tipos_colunas_oracle(cursor, tabela_nome) # 2. Limpa dados anteriores na tabela espelho try: cursor.execute(f"TRUNCATE TABLE {tabela_destino}") except Exception: cursor.execute(f"DELETE FROM {tabela_destino}") conn_oracle.commit() # 3. Monta SQL de inserção em massa colunas_sql = ', '.join(colunas_oracle) binds_sql = ', '.join([':' + str(i + 1) for i in range(len(colunas_oracle))]) sql_insert = f"INSERT INTO {tabela_destino} ({colunas_sql}) VALUES ({binds_sql})" # 4. Extração Paginada e Bulk Insert offset = 0 total_tabela = 0 while True: sql_extracao = f"SELECT * FROM {tabela_origem} LIMIT {TAMANHO_LOTE} OFFSET {offset}" try: linhas = extrair_dbmaker(sql_extracao) except Exception as e: log_erro(tabela_origem, f"Falha na consulta DBMaker (Offset {offset}): {e}") print(f"\n [ERRO OFFSET {offset}]: {e}") break if not linhas: break dados_para_inserir = [] for linha in linhas: tupla = tuple( converter_valor(col, linha.get(col), tipos_oracle.get(col.strip().upper(), 'VARCHAR2')) for col in colunas_raw ) dados_para_inserir.append(tupla) try: cursor.executemany(sql_insert, dados_para_inserir) conn_oracle.commit() except Exception as e: detalhe_erro = f"SQL: {sql_insert} | Amostra Linha 1: {dados_para_inserir[:1]}" log_erro(tabela_destino, f"Falha no insert em lote (Offset {offset}): {e}", detalhes=detalhe_erro) print(f"\n [ERRO INSERT]: {e}") conn_oracle.rollback() break total_tabela += len(dados_para_inserir) offset += TAMANHO_LOTE if len(linhas) < TAMANHO_LOTE: break cursor.close() return total_tabela # ============================================================================= # CICLO COMPLETO DE SINCRONIZAÇÃO # ============================================================================= def executar_ciclo(tabelas_alvo, recriar: bool = False, modo: str = "manual") -> bool: """Executa a replicação de toda a lista de views/tabelas do SYSADM.""" if not adquirir_lock(modo=modo): return False inicio = datetime.now() sucesso_geral = False def finalizar_por_sinal(signum, frame): liberar_lock() salvar_status_etl({ "status": "STOPPED", "pid": os.getpid(), "mensagem": "Sincronização interrompida pelo usuário ou sistema", "fim": datetime.now().strftime("%Y-%m-%d %H:%M:%S") }) sys.exit(1) try: signal.signal(signal.SIGINT, finalizar_por_sinal) if hasattr(signal, "SIGTERM"): signal.signal(signal.SIGTERM, finalizar_por_sinal) except Exception: pass salvar_status_etl({ "status": "RUNNING", "pid": os.getpid(), "modo": modo, "inicio": inicio.strftime("%Y-%m-%d %H:%M:%S"), "fim": None, "duracao_segundos": 0, "duracao_formatada": "00:00:00", "tabela_atual": "Conectando ao Oracle...", "indice_atual": 0, "total_tabelas": len(tabelas_alvo), "progresso_pct": 0.0, "linhas_inseridas": 0, "sucessos": 0, "vazias": 0, "erros": 0, "ultimo_erro": None }) cabecalho = ( "=" * 80 + "\n" f"INICIANDO SINCRONIZACAO TOTAL: DBMAKER (SYSADM) -> ORACLE ({ORACLE_SCHEMA})\n" f"Data/Hora Inicio: {inicio.strftime('%Y-%m-%d %H:%M:%S')}\n" f"Oracle Host: {ORACLE_DSN} (Schema: {ORACLE_SCHEMA})\n" f"Bridge DBMaker: {BRIDGE_URL}\n" f"Total de Objetos: {len(tabelas_alvo)} views/tabelas\n" f"Arquivo de Log: {ARQUIVO_LOG_GERAL}\n" f"Log de Erros: {ARQUIVO_LOG_ERROS}\n" + "=" * 80 ) print(cabecalho) if oracledb is None: msg_fatal = "[FATAL] Modulo 'oracledb' nao esta instalado no ambiente Python. Execute: pip install oracledb" print(f"\n{msg_fatal}") log_erro("CONEXAO_ORACLE", msg_fatal) salvar_status_etl({ "status": "ERROR", "pid": os.getpid(), "inicio": inicio.strftime("%Y-%m-%d %H:%M:%S"), "fim": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "ultimo_erro": msg_fatal, "tabela_atual": "Dependencia oracledb ausente" }) liberar_lock() return False try: conn_oracle = oracledb.connect( user=ORACLE_USER, password=ORACLE_PASS, dsn=ORACLE_DSN ) except Exception as e: msg_fatal = f"[FATAL] Nao foi possivel conectar ao Oracle: {e}" print(f"\n{msg_fatal}") log_erro("CONEXAO_ORACLE", msg_fatal) salvar_status_etl({ "status": "ERROR", "pid": os.getpid(), "inicio": inicio.strftime("%Y-%m-%d %H:%M:%S"), "fim": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "ultimo_erro": msg_fatal, "tabela_atual": "Falha na conexão Oracle" }) liberar_lock() return False total_linhas_geral = 0 sucessos = 0 vazias = 0 erros = 0 lista_erros = [] try: for idx, tabela in enumerate(tabelas_alvo, start=1): nome_curto = obter_nome_tabela_oracle(tabela) print(f"[{idx:03d}/{len(tabelas_alvo):03d}] SYSADM.{nome_curto:<12} ...", end=" ", flush=True) duracao_parcial = datetime.now() - inicio salvar_status_etl({ "status": "RUNNING", "pid": os.getpid(), "modo": modo, "inicio": inicio.strftime("%Y-%m-%d %H:%M:%S"), "fim": None, "duracao_segundos": int(duracao_parcial.total_seconds()), "duracao_formatada": str(duracao_parcial).split('.')[0], "tabela_atual": f"SYSADM.{nome_curto}", "indice_atual": idx, "total_tabelas": len(tabelas_alvo), "progresso_pct": round((idx / len(tabelas_alvo)) * 100, 1), "linhas_inseridas": total_linhas_geral, "sucessos": sucessos, "vazias": vazias, "erros": erros, "ultimo_erro": lista_erros[-1][1] if lista_erros else None }) try: qtd = espelhar_tabela(conn_oracle, tabela, recriar=recriar) total_linhas_geral += qtd if qtd > 0: sucessos += 1 print(f"OK ({qtd:,} linhas)") log_msg(f"SYSADM.{nome_curto:<12} -> Sincronizado com Sucesso ({qtd:,} linhas)") else: vazias += 1 print(f"VAZIA (0 linhas)") log_msg(f"SYSADM.{nome_curto:<12} -> Tabela Vazia (0 linhas)") except Exception as e: erros += 1 msg_falha = f"{e}" print(f"FALHA: {msg_falha}") log_erro(tabela, msg_falha, detalhes=traceback.format_exc()) lista_erros.append((tabela, msg_falha)) time.sleep(0.05) conn_oracle.close() fim = datetime.now() duracao = fim - inicio resumo = ( "\n" + "=" * 80 + "\n" "RESUMO DA REPLICACAO SYSADM -> VIMAN\n" f"Total de Objetos Processados: {len(tabelas_alvo)}\n" f"Objetos com Dados Copiados: {sucessos}\n" f"Objetos Vazios no DBMaker: {vazias}\n" f"Objetos com Erro: {erros}\n" f"Total de Linhas Inseridas: {total_linhas_geral:,}\n" f"Tempo Total Gasto: {duracao}\n" f"Logs salvos em: {ARQUIVO_LOG_GERAL}\n" ) if lista_erros: resumo += ( "\n" + "-" * 80 + "\n" f"TABELAS COM ERRO NESTA EXECUCAO ({len(lista_erros)}):\n" ) for num, (tab, err) in enumerate(lista_erros, start=1): resumo += f" {num:02d}. {tab} -> {err}\n" resumo += "-" * 80 + "\n" resumo += "=" * 80 print(resumo) log_msg(resumo) salvar_status_etl({ "status": "COMPLETED" if erros == 0 else "COMPLETED_WITH_ERRORS", "pid": os.getpid(), "modo": modo, "inicio": inicio.strftime("%Y-%m-%d %H:%M:%S"), "fim": fim.strftime("%Y-%m-%d %H:%M:%S"), "duracao_segundos": int(duracao.total_seconds()), "duracao_formatada": str(duracao).split('.')[0], "tabela_atual": "Concluído", "indice_atual": len(tabelas_alvo), "total_tabelas": len(tabelas_alvo), "progresso_pct": 100.0, "linhas_inseridas": total_linhas_geral, "sucessos": sucessos, "vazias": vazias, "erros": erros, "ultimo_erro": lista_erros[-1][1] if lista_erros else None, "tabelas_com_erro": [t[0] for t in lista_erros[:10]] }) sucesso_geral = (erros == 0) finally: liberar_lock() return sucesso_geral # ============================================================================= # PONTO DE ENTRADA # ============================================================================= def main(): parser = argparse.ArgumentParser(description="Sincronizacao Completa SYSADM -> Oracle Schema VIMAN") parser.add_argument("--tabela", help="Nome da view/tabela especifica (ex: SYSADM.VETEPD01 ou VETEPD01)") parser.add_argument("--filtro", help="Filtra objetos por nome (ex: --filtro VETEL3 para lotes)") parser.add_argument("--comecar-de", help="Retoma a partir de um indice (ex: 514) ou nome de tabela (ex: VETERI12)") parser.add_argument("--recriar", action="store_true", help="Recria as tabelas no Oracle (DROP e CREATE) para corrigir tipos") parser.add_argument("--loop", action="store_true", help="Executa continuamente de hora em hora (OBS: se usar crontab, NAO passe --loop)") parser.add_argument("--modo", default="manual", help="Identificador da origem da execução (manual, cron, web)") args = parser.parse_args() # Seleção de tabelas if args.tabela: tabela = args.tabela.strip().upper() if not tabela.startswith("SYSADM."): tabela = f"SYSADM.{tabela}" tabelas_alvo = [tabela] elif args.filtro: filtro = args.filtro.strip().upper() tabelas_alvo = [t for t in TABELAS_SYSADM if filtro in t.upper()] print(f"Filtro '{filtro}' selecionou {len(tabelas_alvo)} objetos.") else: tabelas_alvo = TABELAS_SYSADM # Suporte a retomar de uma tabela/índice específico if args.comecar_de: alvo = args.comecar_de.strip().upper() if alvo.isdigit(): indice = max(0, int(alvo) - 1) tabelas_alvo = tabelas_alvo[indice:] print(f"Retomando a partir do indice {alvo} (restando {len(tabelas_alvo)} objetos)...") else: if not alvo.startswith("SYSADM."): alvo_com_schema = f"SYSADM.{alvo}" else: alvo_com_schema = alvo idx_encontrado = None for i, t in enumerate(tabelas_alvo): if t.upper() == alvo_com_schema or alvo in t.upper(): idx_encontrado = i break if idx_encontrado is not None: tabelas_alvo = tabelas_alvo[idx_encontrado:] print(f"Retomando a partir de '{alvo}' (restando {len(tabelas_alvo)} objetos)...") else: print(f"[AVISO] Objeto '{alvo}' nao encontrado na lista. Executando lista normal.") if not tabelas_alvo: print("[ERRO] Nenhum objeto encontrado para sincronizar.") sys.exit(1) if args.loop: print(f"[MODO LOOP ATIVO] O processo rodara a cada {INTERVALO_LOOP_SEGUNDOS // 60} minutos.") while True: try: executar_ciclo(tabelas_alvo, recriar=args.recriar, modo="loop") except Exception as e: log_erro("LOOP_GERAL", f"Erro no loop de execucao: {e}", detalhes=traceback.format_exc()) print(f"[ERRO NO LOOP]: {e}") print(f"\nAguardando {INTERVALO_LOOP_SEGUNDOS // 60} minutos para o proximo ciclo ({datetime.now().strftime('%H:%M:%S')})...") time.sleep(INTERVALO_LOOP_SEGUNDOS) else: # EXECUÇÃO ÚNICA (PADRÃO PARA CRONTAB E DISPARO VIA WEB) sucesso = executar_ciclo(tabelas_alvo, recriar=args.recriar, modo=args.modo) sys.exit(0 if sucesso else 1) if __name__ == "__main__": main()