AppType=StandardJava Build1=Default,b4j.JRDCMultiDB Build2=New_1,b4j.JRDCMulti File1=config.DB2.properties File2=config.DB3.properties File3=config.DB4.properties File4=config.properties File5=reiniciaProcesoBow.bat File6=reiniciaProcesoPM2.bat File7=start.bat File8=start2.bat File9=stop.bat FileGroup1=Default Group FileGroup2=Default Group FileGroup3=Default Group FileGroup4=Default Group FileGroup5=Default Group FileGroup6=Default Group FileGroup7=Default Group FileGroup8=Default Group FileGroup9=Default Group Group=Default Group Library1=bcrypt Library2=byteconverter Library3=javaobject Library4=jcore Library5=jrandomaccessfile Library6=jserver Library7=jshell Library8=json Library9=jsql Module1=Cambios Module10=LogoutHandler Module11=Manager Module12=ParameterValidationUtils Module13=ping Module14=RDCConnector Module15=SSE Module16=SSEHandler Module17=TestHandler Module2=ChangePassHandler Module3=DBHandlerB4X Module4=DBHandlerJSON Module5=DoLoginHandler Module6=faviconHandler Module7=GlobalParameters Module8=HikariConnectionPool Module9=LoginHandler NumberOfFiles=9 NumberOfLibraries=9 NumberOfModules=17 Version=10.3 @EndOfDesignText@ 'Non-UI application (console / server application) #Region Project Attributes ' Specify command line arguments if any #CommandLineArgs: ' Merge all referenced libraries into the final JAR #MergeLibraries: True ' VERSION 5.10.25 '########################################################################################################### '###################### PULL ############################################################# ' IDE helper link to perform a 'git pull' 'Ctrl + click ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=git&Args=pull '########################################################################################################### '###################### PUSH ############################################################# ' IDE helper link to perform a 'git push' (using a custom script/alias 'github') 'Ctrl + click ide://run?file=%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe&Args=github&Args=..\..\ '########################################################################################################### '###################### PUSH TORTOISE GIT ######################################################### ' IDE helper link to open the TortoiseGit commit dialog 'Ctrl + click ide://run?file=%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe&Args=TortoiseGitProc&Args=/command:commit&Args=/path:"../"&Args=/closeonend:2 '########################################################################################################### #End Region ' --- JDBC Driver Selection --- ' Change based on the jdbc jar file '#AdditionalJar: mysql-connector-java-5.1.27-bin '#AdditionalJar: postgresql-42.7.0 ' Using Oracle JDBC driver #AdditionalJar: ojdbc11 ' Using SQLite JDBC driver (for user/log database) #AdditionalJar: sqlite-jdbc-3.7.2 ' --- Critical Dependencies for HikariCP (Connection Pooling) and SLF4J (Logging) --- #AdditionalJar: HikariCP-4.0.3 #AdditionalJar: slf4j-api-1.7.25 #AdditionalJar: slf4j-simple-1.7.25 ' --- Global variables for the entire application --- Sub Process_Globals ' The main B4J HTTP server object Public srvr As Server ' The current version of this modified jRDC server Public const VERSION As Float = 2.23 ' Custom types for serializing/deserializing data Type DBCommand (Name As String, Parameters() As Object) Type DBResult (Tag As Object, Columns As Map, Rows As List) ' Holds a list of configured database identifiers (e.g., "DB1", "DB2") Public listaDeCP As List ' A temporary list to store found configuration file names during startup Private cpFiles As List ' Global maps to manage database connectors and loaded SQL commands Public Connectors, commandsMap As Map ' SQL object for interacting with the local users database (Authentication) Public SQL_Auth As SQL ' --- NEW INSTANCE FOR AUTHENTICATION ' SQL object for interacting with the local logs database (Performance/Errors) Public SQL_Logs As SQL ' --- NEW INSTANCE FOR LOGS ' Defines the storage mode for the Log database (SQL_Logs). Default is DISK. ' Options: "DISK" (persistent) or "MEMORY" (in-memory, lost on exit). Private const LOG_DB_MODE As String = "DISK" ' Object for securely hashing and verifying passwords Private bc As BCrypt ' A Java ReentrantLock object to protect Main.Connectors during Hot-Swapping (thread-safety) Public MainConnectorsLock As JavaObject ' A Java ReentrantLock object to protect the log caches (QueryLogCache and ErrorLogCache) Public LogCacheLock As JavaObject ' Timer for executing periodic tasks, such as log cleanup Public timerLogs As Timer ' Map to store the SQLite logging status (True/False) for each DBKey (DB1, DB2, etc.) Public SQLiteLoggingStatusByDB As Map ' Global flag indicating if AT LEAST one database has SQLite logging enabled Public IsAnySQLiteLoggingEnabled As Boolean ' Type to encapsulate the result of parameter validation Type ParameterValidationResult ( _ Success As Boolean, _ ErrorMessage As String, _ ParamsToExecute As List _ ' The final list of parameters to use in the SQL execution ) ' In-memory cache for performance logs (query_logs) Public QueryLogCache As List ' In-memory cache for error and warning logs Public ErrorLogCache As List ' Threshold of records to force a batch write to the DB Public LOG_CACHE_THRESHOLD As Int = 400 ' Flag to enable/disable verbose logging, set in AppStart Dim logger As Boolean ' Thread-Safe Map to store the latest metrics for each connection pool Public LatestPoolStats As Map ' Counter for timer ticks to control VACUUM frequency Private TimerTickCount As Int = 0 ' Run VACUUM every 48 cycles (48 * 30 minutes = 24 hours) Private const VACUUM_CYCLES As Int = 48 ' Granular control for TEXT file logging (CSV) Public TextLoggingStatusByDB As Map End Sub ' --- Main application entry point --- Sub AppStart (Args() As String) ' Initialize Server-Sent Events handler SSE.Initialize ' Set logger flag based on build mode (DEBUG or RELEASE) #if DEBUG logger = True ' Use a small threshold in DEBUG mode for easier testing LOG_CACHE_THRESHOLD = 10 #else logger = False #End If Log("Log_Cache_Threshold: " & LOG_CACHE_THRESHOLD) ' Copy web admin panel files if they don't exist CopiarRecursoSiNoExiste("manager.html", "www") CopiarRecursoSiNoExiste("login.html", "www") ' Copy root files (configs, start/stop scripts) if they don't exist CopiarRecursoSiNoExiste("config.properties", "") ' CopiarRecursoSiNoExiste("config.DB2.properties", "") ' CopiarRecursoSiNoExiste("config.DB3.properties", "") CopiarRecursoSiNoExiste("start.bat", "") CopiarRecursoSiNoExiste("start2.bat", "") CopiarRecursoSiNoExiste("stop.bat", "") CopiarRecursoSiNoExiste("reiniciaProcesoBow.bat", "") CopiarRecursoSiNoExiste("reiniciaProcesoPM2.bat", "") ' Initialize the BCrypt password hashing library bc.Initialize("BC") ' Initialize in-memory log caches QueryLogCache.Initialize ErrorLogCache.Initialize ' === 1. Initialize the local user database (SQLite) and log tables === InitializeSQLiteDatabase ' === 2. Initialize global maps defined in GlobalParameters.bas === GlobalParameters.mpLogs.Initialize GlobalParameters.mpTotalRequests.Initialize GlobalParameters.mpTotalConnections.Initialize GlobalParameters.mpBlockConnection.Initialize ' Ensure the active request counter map is thread-safe GlobalParameters.ActiveRequestsCountByDB = srvr.CreateThreadSafeMap ' === 3. Initialize the main HTTP server structures === listaDeCP.Initialize srvr.Initialize("") ' Connectors map must be thread-safe Connectors = srvr.CreateThreadSafeMap commandsMap.Initialize ' Initialize the stats map as Thread-Safe LatestPoolStats = srvr.CreateThreadSafeMap ' Initialize the map for text logging status TextLoggingStatusByDB.Initialize ' Initialize the granular SQLite logging status map SQLiteLoggingStatusByDB.Initialize ' Create a ReentrantLock instance to protect Main.Connectors MainConnectorsLock.InitializeNewInstance("java.util.concurrent.locks.ReentrantLock", Null) ' Initialize the lock for log caches LogCacheLock.InitializeNewInstance("java.util.concurrent.locks.ReentrantLock", Null) ' === 4. INITIALIZATION OF THE MAIN DATABASE CONNECTOR (DB1) === Try ' Initialize the main 'DB1' connector Dim con1 As RDCConnector con1.Initialize("DB1") ' Add it to the thread-safe map Connectors.Put("DB1", con1) ' Set the server port from the DB1 config file srvr.Port = con1.serverPort ' Add 'DB1' to the list of active database keys listaDeCP.Add("DB1") Log($"Main.AppStart: Connector 'DB1' initialized successfully on port: ${srvr.Port}"$) ' Read the 'enableSQLiteLogs' setting from config.properties (default to 0) Dim enableLogsSetting As Int = con1.config.GetDefault("enableSQLiteLogs", 0).As(Int) Dim isEnabled As Boolean = (enableLogsSetting = 1) ' Store the status in the granular map SQLiteLoggingStatusByDB.Put("DB1", isEnabled) ' Read the 'enableTextLogging' setting Dim enableTextLogsSetting As Int = con1.config.GetDefault("enableTextLogging", 0).As(Int) Dim isTextEnabled As Boolean = (enableTextLogsSetting = 1) ' Store the text log status TextLoggingStatusByDB.Put("DB1", isTextEnabled) Catch ' This is a critical failure; the server cannot start without DB1 Dim ErrorMsg As String = $"Main.AppStart: CRITICAL ERROR initializing connector 'DB1': ${LastException.Message}"$ Log(ErrorMsg) ' Log the error to the SQLite DB (if it's already initialized) LogServerError("ERROR", "Main.AppStart", ErrorMsg, "DB1", Null, Null) ' Stop the application ExitApplication End Try ' === 5. DETECTION AND INITIALIZATION OF ADDITIONAL DATABASES (DB2, DB3, DB4) === ' Scan the application's root directory for configuration files cpFiles = File.ListFiles("./") If cpFiles.Size > 0 Then For Each fileName As String In cpFiles Dim keyPrefix As String = "config." Dim keySuffix As String = ".properties" ' 1. Filter and exclude DB1 (which is already loaded) ' Find files matching "config.xxx.properties" but not "config.properties" If fileName.StartsWith(keyPrefix) And fileName.EndsWith(keySuffix) And fileName <> "config.properties" Then Try ' 2. Extract the key ("xxx" from config.xxx.properties) Dim keyLength As Int = fileName.Length - keySuffix.Length Dim dbKey As String = fileName.SubString2(keyPrefix.Length, keyLength) ' ROBUSTNESS: Ensure the key is UPPERCASE for consistency. ' Handlers normalize the key to uppercase, so we must match that. dbKey = dbKey.ToUpperCase.Trim Log($"Main.AppStart: Configuration file detected: '${fileName}'. Initializing connector '${dbKey}'."$) Dim newCon As RDCConnector ' 3. Initialize the RDC Connector (which reads its own config.dbKey.properties file) newCon.Initialize(dbKey) ' 4. Update global structures (Thread-Safe Maps) Connectors.Put(dbKey, newCon) listaDeCP.Add(dbKey) ' 5. Granular Logging Logic ' Capture the logging status for this new DB Dim enableLogsSetting As Int = newCon.config.GetDefault("enableSQLiteLogs", 0).As(Int) Dim isEnabled As Boolean = (enableLogsSetting = 1) SQLiteLoggingStatusByDB.Put(dbKey, isEnabled) ' Capture text logging status for this new DB Dim enableTextLogsSetting As Int = newCon.config.GetDefault("enableTextLogging", 0).As(Int) Dim isTextEnabled As Boolean = (enableTextLogsSetting = 1) TextLoggingStatusByDB.Put(dbKey, isTextEnabled) Log("TEXT LOGGING STATUS BY DB: " & TextLoggingStatusByDB) ' Note: Global re-evaluation of IsAnySQLiteLoggingEnabled is done at the end of AppStart. Catch ' 6. Error Handling: If a file is invalid (e.g., bad credentials, malformed URL), ' the server should log the error but continue trying with the next file. Dim ErrorMsg As String = $"Main.AppStart: CRITICAL ERROR initializing connector '${dbKey}' from '${fileName}': ${LastException.Message}"$ Log(ErrorMsg) LogServerError("ERROR", "Main.AppStart", ErrorMsg, dbKey, Null, Null) End Try End If Next End If ' Final log of all databases the server is managing. Dim sbListaDeCP_Log As StringBuilder sbListaDeCP_Log.Initialize For Each item As String In listaDeCP sbListaDeCP_Log.Append(item).Append(", ") Next If sbListaDeCP_Log.Length > 0 Then sbListaDeCP_Log.Remove(sbListaDeCP_Log.Length - 2, sbListaDeCP_Log.Length) End If Log($"Main.AppStart: Configured and ready databases: [${sbListaDeCP_Log.ToString}]"$) ' <<<< Initialization block for the log cleanup Timer >>>> ' UNCONDITIONAL Initialization of the Timer (Ensures the object exists and prevents IllegalStateException) timerLogs.Initialize("TimerLogs", 1800000) ' 30 minutes = 1800 * 1000 = 1800000 ms ' CONDITIONAL CONTROL BASED ON GRANULAR STATUS IsAnySQLiteLoggingEnabled = False For Each dbStatus As Boolean In SQLiteLoggingStatusByDB.Values If dbStatus Then IsAnySQLiteLoggingEnabled = True Exit ' If one is active, it's enough to turn on the Timer End If Next If IsAnySQLiteLoggingEnabled Then timerLogs.Enabled = True If logger Then Log("Main.AppStart: Log cleanup timer ACTIVATED (at least one DB requires logs).") Else timerLogs.Enabled = False If logger Then Log("Main.AppStart: Log cleanup timer DISABLED (no DB requires logs).") End If ' <<<< End of Timer block >>>> ' === 6. REGISTERING HTTP HANDLERS FOR THE SERVER === srvr.AddHandler("/ping", "ping", False) srvr.AddHandler("/test", "TestHandler", False) srvr.AddHandler("/login", "LoginHandler", False) srvr.AddHandler("/dologin", "DoLoginHandler", False) srvr.AddHandler("/logout", "LogoutHandler", False) srvr.AddHandler("/changepass", "ChangePassHandler", False) srvr.AddHandler("/manager", "Manager", False) srvr.AddHandler("/DBJ", "DBHandlerJSON", False) srvr.AddHandler("/dbrquery", "DBHandlerJSON", False) srvr.AddHandler("/favicon.ico", "faviconHandler", False) srvr.AddHandler("/stats-stream", "SSEHandler", False) srvr.AddHandler("/*", "DBHandlerB4X", False) ' 7. Start the HTTP server. srvr.Start Log("===========================================================") Log($"-=== jRDC is running on port: ${srvr.Port} (version = $1.2{VERSION}) ===-"$) Log("===========================================================") ' 8. Start the B4J message loop. StartMessageLoop End Sub ' --- Subroutine to initialize the local user database (SQLite) --- Sub InitializeSQLiteDatabase Private dbFileName As String = "users.db" Private dbDirName As String = GlobalParameters.WorkingDirectory ' --- Configuration for SQL_Logs based on LOG_DB_MODE --- Private logDirName As String = dbDirName Private logFileName As String = dbFileName Private isInMemoryMode As Boolean = (LOG_DB_MODE = "MEMORY") If isInMemoryMode Then ' For in-memory databases, use the special filename ":memory:" and empty directory. logDirName = "" logFileName = ":memory:" If logger Then Log("NOTICE: SQL_Logs initialized as IN-MEMORY database (data is non-persistent).") Else If logger Then Log($"NOTICE: SQL_Logs initialized as DISK database: ${dbFileName}"$) End If ' Initialize SQL_Auth (always points to the disk file for user persistence). SQL_Auth.InitializeSQLite(dbDirName, dbFileName, True) ' Initialize SQL_Logs (points to disk file or :memory:) SQL_Logs.InitializeSQLite(logDirName, logFileName, True) ' Check if schema creation/migration is necessary. ' This is true if the disk file is brand new OR if we are running in memory mode. Private isNewDbFile As Boolean = File.Exists(dbDirName, dbFileName) = False If isNewDbFile Or isInMemoryMode Then If logger Then Log("Schema creation required (New DB file or In-Memory mode).") ' 1. TABLE CREATION (Done via SQL_Logs instance, as it handles the schema) SQL_Logs.ExecNonQuery("CREATE TABLE users (username TEXT PRIMARY KEY, password_hash TEXT, last_login_timestamp INTEGER, is_admin INTEGER DEFAULT 0)") SQL_Logs.ExecNonQuery("CREATE TABLE query_logs (query_name TEXT, duration_ms INTEGER, timestamp INTEGER, db_key TEXT, client_ip TEXT, busy_connections INTEGER, handler_active_requests INTEGER, timestamp_text_local TEXT)") SQL_Logs.ExecNonQuery("CREATE TABLE errores (timestamp INTEGER, type TEXT, source TEXT, message TEXT, db_key TEXT, command_name TEXT, client_ip TEXT)") ' 2. INDEX CREATION (Done via SQL_Logs instance) SQL_Logs.ExecNonQuery("CREATE INDEX idx_query_timestamp ON query_logs(timestamp)") SQL_Logs.ExecNonQuery("CREATE INDEX idx_query_duration ON query_logs(duration_ms)") SQL_Logs.ExecNonQuery("CREATE INDEX idx_query_dbkey ON query_logs(db_key)") ' --- NEW INDEX: CRITICAL FOR MULTI-DB REPORTS SQL_Logs.ExecNonQuery("CREATE INDEX idx_error_timestamp ON errores(timestamp)") ' 3. PRAGMAS (Applied to both to ensure consistency in WAL mode) SQL_Logs.ExecNonQuery("PRAGMA journal_mode=WAL") SQL_Logs.ExecNonQuery("PRAGMA synchronous=NORMAL") SQL_Auth.ExecNonQuery("PRAGMA journal_mode=WAL") SQL_Auth.ExecNonQuery("PRAGMA synchronous=NORMAL") Else ' Load existing database (DISK Mode) If logger Then Log("Existing users.db found. Applying PRAGMAS and checking migrations.") ' Ensure PRAGMAS are set on both connections SQL_Logs.ExecNonQuery("PRAGMA journal_mode=WAL") SQL_Logs.ExecNonQuery("PRAGMA synchronous=NORMAL") SQL_Auth.ExecNonQuery("PRAGMA journal_mode=WAL") SQL_Auth.ExecNonQuery("PRAGMA synchronous=NORMAL") ' >>> Migration Logic (INDEX VERIFICATION) <<< ' Migration check must run on SQL_Logs ' --- VERIFY AND ADD idx_query_dbkey INDEX --- If SQL_Logs.ExecQuerySingleResult($"SELECT name FROM sqlite_master WHERE type='index' AND name='idx_query_dbkey'"$) = Null Then If logger Then Log("Adding index 'idx_query_dbkey' to query_logs.") SQL_Logs.ExecNonQuery("CREATE INDEX idx_query_dbkey ON query_logs(db_key)") End If ' (Migration logic for other assumed columns/tables should use SQL_Logs) End If End Sub ' Public subroutine to log query performance. Public Sub LogQueryPerformance(QueryName As String, DurationMs As Long, DbKey As String, ClientIp As String, HandlerActiveRequests As Int, PoolBusyConnections As Int) ' Check if logging is enabled for this specific DBKey Dim isEnabled As Boolean = SQLiteLoggingStatusByDB.GetDefault(DbKey, False) If isEnabled Then ' Set date format for the new text timestamp column DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss.SSS" Dim formattedTimestamp As String = DateTime.Date(DateTime.Now) ' 1. Create the data map (log entry) Dim logEntry As Map = CreateMap("query_name": QueryName, "duration_ms": DurationMs, "timestamp": DateTime.Now, _ "db_key": DbKey, "client_ip": ClientIp, "busy_connections": PoolBusyConnections, _ "handler_active_requests": HandlerActiveRequests, "timestamp_text_local": formattedTimestamp) ' 2. Critical Zone: Add to cache and check threshold Dim shouldWriteBatch As Boolean = False ' Use the *cache lock* to ensure adding and size-checking are atomic LogCacheLock.RunMethod("lock", Null) QueryLogCache.Add(logEntry) If QueryLogCache.Size >= LOG_CACHE_THRESHOLD Then shouldWriteBatch = True End If LogCacheLock.RunMethod("unlock", Null) ' 3. If the threshold was reached, trigger the write. ' This MUST be done OUTSIDE the lock. If shouldWriteBatch Then CallSub(Me, "WriteQueryLogsBatch") End If End If End Sub ' --- Subroutine to log errors and warnings in the 'errores' table. --- Public Sub LogServerError(Type0 As String, Source As String, Message As String, DBKey As String, CommandName As String, ClientIp As String) If logger Then Log($">>>> LogServerError <<<<<${CRLF}tipo:${Type0}, source:${Source}, message:${Message}, dbkey:${DBKey}, commandanme:${CommandName}"$) ' Check if logging is enabled for this specific DBKey (or fallback if DBKey is null) Dim isEnabled As Boolean = SQLiteLoggingStatusByDB.GetDefault(DBKey, False) If isEnabled Then ' 1. Create the log entry map Dim logEntry As Map = CreateMap("timestamp": DateTime.Now, "type": Type0, "source": Source, "message": Message, _ "db_key": DBKey, "command_name": CommandName, "client_ip": ClientIp) Dim shouldWriteBatch As Boolean = False ' 2. Critical Zone: Add to cache and check threshold ' Use the *cache lock* for Thread Safety LogCacheLock.RunMethod("lock", Null) ' Log(">>>>> Agregamos a errorLog") ErrorLogCache.Add(logEntry) If ErrorLogCache.Size >= LOG_CACHE_THRESHOLD Then shouldWriteBatch = True End If LogCacheLock.RunMethod("unlock", Null) ' 3. If threshold was reached, trigger the write If shouldWriteBatch Then CallSub(Me, "WriteErrorLogsBatch") End If Else ' Logging is disabled for this DBKey, so the log is skipped. End If End Sub ' Writes the cached performance logs to the SQLite DB in a single transaction Public Sub WriteQueryLogsBatch Dim logsToWrite As List logsToWrite.Initialize ' 1. Initialize the local list (CRITICAL) ' === STEP 1: Atomic Cache Swap (Protected by ReentrantLock) === LogCacheLock.RunMethod("lock", Null) If QueryLogCache.Size = 0 Then ' Cache is empty, release lock and return LogCacheLock.RunMethod("unlock", Null) Return End If ' *** CRITICAL FIX: Copy content (AddAll) instead of reference. *** logsToWrite.AddAll(QueryLogCache) Dim batchSize As Int = logsToWrite.Size ' Clear the global cache. logsToWrite now holds the copy of the items. QueryLogCache.Initialize LogCacheLock.RunMethod("unlock", Null) ' Check if text logging is enabled for any of these logs If logsToWrite.Size > 0 Then ' Call the text archiving sub on a separate worker thread. ' This is NON-BLOCKING for the current thread, which will proceed to the SQLite transaction. CallSubDelayed2(Me, "ArchiveQueryLogsToDailyFile", logsToWrite) End If ' === STEP 2: Transactional Write to SQLite === Try ' 1. Begin the transaction on the dedicated LOGS instance. SQL_Logs.BeginTransaction For Each logEntry As Map In logsToWrite ' Insert the log entry SQL_Logs.ExecNonQuery2("INSERT INTO query_logs (query_name, duration_ms, timestamp, db_key, client_ip, busy_connections, handler_active_requests, timestamp_text_local) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", _ Array As Object(logEntry.Get("query_name"), logEntry.Get("duration_ms"), logEntry.Get("timestamp"), logEntry.Get("db_key"), _ logEntry.Get("client_ip"), logEntry.Get("busy_connections"), logEntry.Get("handler_active_requests"), _ logEntry.Get("timestamp_text_local"))) Next ' 2. Finalize the transaction: Efficient write to disk. SQL_Logs.TransactionSuccessful If logger Then Log($"[LOG BATCH] Batch of ${batchSize} performance logs written successfully."$) Catch ' If it fails, undo all logs in this batch and log the failure. SQL_Logs.Rollback Dim ErrorMsg As String = "CRITICAL ERROR: Failed to write performance log batch to SQLite: " & LastException.Message Log(ErrorMsg) ' Use LogServerError so the failure is recorded in the 'errores' table (if logging is enabled) LogServerError("ERROR", "Main.WriteQueryLogsBatch", ErrorMsg, Null, "log_batch_write_performance", Null) End Try End Sub ' --- Event subroutine for the 'timerLogs' Timer. --- ' The 'Enabled' state of the Timer is already controlled by IsAnySQLiteLoggingEnabled in AppStart and Manager. Sub TimerLogs_Tick Try ' 1. Flush performance logs WriteQueryLogsBatch ' 2. Flush error logs WriteErrorLogsBatch ' 3. Clean up and VACUUM (this sub also checks IsAnySQLiteLoggingEnabled) borraArribaDe10000Logs Catch Dim ErrorMsg As String = "ERROR in TimerLogs_Tick while trying to clear logs: " & LastException.Message Log(ErrorMsg) LogServerError("ERROR", "Main.TimerLogs_Tick", ErrorMsg, Null, "log_cleanup", Null) End Try End Sub ' Writes the cached error logs to the SQLite DB in a single transaction Public Sub WriteErrorLogsBatch ' List to store error logs copied from the cache Dim logsToWrite As List logsToWrite.Initialize ' Lock LogCacheLock to guarantee atomicity of copy and cleanup. LogCacheLock.RunMethod("lock", Null) If ErrorLogCache.Size = 0 Then ' Cache is empty, release the lock immediately and exit. LogCacheLock.RunMethod("unlock", Null) Return End If ' *** Atomically copy global cache content. *** logsToWrite.AddAll(ErrorLogCache) Dim batchSize As Int = logsToWrite.Size ' Clean the global cache. logsToWrite is now an independent and populated list. ErrorLogCache.Initialize LogCacheLock.RunMethod("unlock", Null) ' Release the lock. If logger Then Log($"[LOG BATCH] Starting transactional write of ${batchSize} ERROR logs to SQLite. Logs copied: ${batchSize}"$) ' === STEP 1: Archive to daily CSV file (if enabled) === If batchSize > 0 Then ' Delegate to a new subroutine to handle CSV disk I/O (CallSubDelayed2) CallSubDelayed2(Me, "ArchiveErrorLogsToDailyFile", logsToWrite) End If ' === STEP 2: Transactional Write to SQLite (Uses logsToWrite) === If batchSize = 0 Then Log("WARNING: Failed to copy list. logsToWrite is empty. Aborting write.") Return End If Try ' 1. Start transaction on the dedicated LOGS instance. SQL_Logs.BeginTransaction For Each logEntry As Map In logsToWrite ' Insert the log entry SQL_Logs.ExecNonQuery2("INSERT INTO errores (timestamp, type, source, message, db_key, command_name, client_ip) VALUES (?, ?, ?, ?, ?, ?, ?)", _ Array As Object(logEntry.Get("timestamp"), logEntry.Get("type"), logEntry.Get("source"), logEntry.Get("message"), _ logEntry.Get("db_key"), logEntry.Get("command_name"), logEntry.Get("client_ip"))) Next ' 2. Commit the transaction. SQL_Logs.TransactionSuccessful If logger Then Log($"[LOG BATCH] Batch of ${logsToWrite.Size} ERROR logs written successfully."$) Catch ' 3. Rollback if failed. SQL_Logs.Rollback Dim ErrorMsg As String = "CRITICAL ERROR: Failed to write ERROR log batch to SQLite: " & LastException.Message Log(ErrorMsg) End Try End Sub ' Deletes the oldest records from 'query_logs' table and runs VACUUM. Sub borraArribaDe10000Logs 'ignore Private Const LOG_LIMIT_PERFORMANCE As Int = 10000 ' New limit for performance logs Private Const LOG_LIMIT_ERRORS As Int = 10000 ' Limit for error logs (retained 15,000) If IsAnySQLiteLoggingEnabled Then ' Only run if at least one DB requires logs. ' 1. Cleanup of Performance Logs (query_logs) If logger Then Log($"Trimming 'query_logs' table, limit of ${LOG_LIMIT_PERFORMANCE} records."$) Dim fechaCorte As Long ' (cutoff date/timestamp) ' Find the timestamp of the (LOG_LIMIT_PERFORMANCE + 1)st record using SQL_Logs Try ' OFFSET skips the most recent records. fechaCorte = SQL_Logs.ExecQuerySingleResult($"SELECT timestamp FROM query_logs ORDER BY timestamp DESC LIMIT 1 OFFSET ${LOG_LIMIT_PERFORMANCE}"$) Catch ' If the table has fewer records than the limit. fechaCorte = 0 End Try If fechaCorte > 0 Then ' Execute DELETE on SQL_Logs SQL_Logs.ExecNonQuery2("DELETE FROM query_logs WHERE timestamp < ?", Array As Object(fechaCorte)) End If ' 2. Cleanup of Error Logs (errores) Dim fechaCorteError As Long Try ' OFFSET LOG_LIMIT_ERRORS skips the most recent. fechaCorteError = SQL_Logs.ExecQuerySingleResult($"SELECT timestamp FROM errores ORDER BY timestamp DESC LIMIT 1 OFFSET ${LOG_LIMIT_ERRORS}"$) Catch ' If the table has fewer than 15,000 records. fechaCorteError = 0 End Try ' If a cutoff time was found... If fechaCorteError > 0 Then SQL_Logs.ExecNonQuery2("DELETE FROM errores WHERE timestamp < ?", Array As Object(fechaCorteError)) End If ' 3. Control and Conditional Execution of VACUUM TimerTickCount = TimerTickCount + 1 If TimerTickCount >= VACUUM_CYCLES Then If logger Then Log("EXECUTING VACUUM (24-hour cycle completed).") SQL_Logs.ExecNonQuery("vacuum;") ' Execute VACUUM on SQL_Logs. TimerTickCount = 0 ' Reset the counter. Else If logger Then Log($"VACUUM skipped. ${VACUUM_CYCLES - TimerTickCount} cycles remaining until daily execution."$) End If Else If logger Then Log("NOTICE: Log cleanup task skipped. Global SQLite logging is disabled.") End If End Sub 'Copies resources from the jar to the app directory Sub CopiarRecursoSiNoExiste(NombreArchivo As String, SubCarpeta As String) Dim DirDestino As String = File.Combine(File.DirApp, SubCarpeta) ' If the subfolder is not empty and doesn't exist, create it If SubCarpeta <> "" And File.Exists(DirDestino, "") = False Then File.MakeDir(DirDestino, "") End If Dim ArchivoDestino As String = File.Combine(DirDestino, NombreArchivo) ' If the target file doesn't exist, copy it from resources If File.Exists(DirDestino, NombreArchivo) = False Then Dim RutaRecurso As String If SubCarpeta <> "" Then ' Path inside the JAR (e.g., "Files/www/manager.html") RutaRecurso = "Files/" & SubCarpeta & "/" & NombreArchivo Else ' Path inside the JAR (e.g., "Files/config.properties") RutaRecurso = "Files/" & NombreArchivo End If ' Get the correct class loader to access JAR resources Dim classLoader As JavaObject = GetThreadContextClassLoader Dim InStream As InputStream = classLoader.RunMethod("getResourceAsStream", Array(RutaRecurso)) If InStream.IsInitialized Then Log($"Copiando recurso: '${RutaRecurso}'..."$) ' Call our own manual copy function Dim OutStream As OutputStream = File.OpenOutput(DirDestino, NombreArchivo, False) CopiarStreamManualmente(InStream, OutStream) Log($"'${ArchivoDestino}' copiado correctamente."$) Else Log($"ERROR: Could not find the resource with the internal path: '${RutaRecurso}'"$) End If End If End Sub ' Does not depend on any external libraries (like File.Copy) Sub CopiarStreamManualmente (InStream As InputStream, OutStream As OutputStream) Try Dim buffer(1024) As Byte Dim len As Int ' Read bytes from input stream len = InStream.ReadBytes(buffer, 0, buffer.Length) Do While len > 0 ' Write bytes to output stream OutStream.WriteBytes(buffer, 0, len) ' Read next chunk len = InStream.ReadBytes(buffer, 0, buffer.Length) Loop Catch LogError(LastException) End Try ' Always close streams InStream.Close OutStream.Close End Sub ' Helper function to get the correct Class Loader Sub GetThreadContextClassLoader As JavaObject Dim thread As JavaObject thread = thread.InitializeStatic("java.lang.Thread").RunMethod("currentThread", Null) Return thread.RunMethod("getContextClassLoader", Null) End Sub ' This runs on a separate worker thread (via CallSubDelayed2) Private Sub ArchiveQueryLogsToDailyFile(logs As List) ' Set date format for the filename DateTime.DateFormat = "yyyy-MM-dd" Dim dateStr As String = DateTime.Date(DateTime.Now) Dim fileBaseName As String = $"query_logs_${dateStr}.csv"$ ' Fields based on the log structure: Dim HEADER_LINE As String = $""timestamp_text_local","query_name","duration_ms","db_key","client_ip","busy_connections","handler_active_requests","timestamp_millis""$ Dim sbContent As StringBuilder sbContent.Initialize ' === 1. CHECK AND WRITE HEADER (CRITICAL) === ' Only write the header if the file does NOT exist OR if it exists but is empty. Dim writeHeader As Boolean = False If Not(File.Exists(File.DirApp, fileBaseName)) Or File.Size(File.DirApp, fileBaseName) = 0 Then writeHeader = True End If If writeHeader Then sbContent.Append(HEADER_LINE).Append(CRLF) End If ' === 2. GENERATE CONTENT IN MEMORY === For Each logEntry As Map In logs ' Check if text logging is enabled for this specific DBKey Dim dbKey As String = logEntry.Get("db_key").As(String) If TextLoggingStatusByDB.GetDefault(dbKey, False) Then ' Format the log line (using double quotes for CSV) Dim line As String = $""${logEntry.Get("timestamp_text_local")}","${logEntry.Get("query_name")}",${logEntry.Get("duration_ms")},"${dbKey}","${logEntry.Get("client_ip")}",${logEntry.Get("busy_connections")},${logEntry.Get("handler_active_requests")},${logEntry.Get("timestamp")}"$ sbContent.Append(line).Append(CRLF) End If Next ' === 3. CONSOLIDATED WRITE TO DISK === If sbContent.Length > 0 Then Dim outStream As OutputStream Try ' APPEND mode (True) ensures that both the header (if written) and the logs are added. outStream = File.OpenOutput(File.DirApp, fileBaseName, True) Dim bytes() As Byte = sbContent.ToString.GetBytes("UTF8") outStream.WriteBytes(bytes, 0, bytes.Length) Catch ' Log the error to the SQLite database LogServerError("ADVERTENCIA", "ArchiveQueryLogsToDailyFile", $"Fallo al escribir lote + HEADER en ${fileBaseName}: ${LastException.Message}"$, "SYSTEM", "Log Batch Write", "N/A") End Try If outStream.IsInitialized Then outStream.Close End If End If End Sub ' This runs on a separate worker thread (via CallSubDelayed2) Private Sub ArchiveErrorLogsToDailyFile(logs As List) Log(">>>>>>> ArchiveErrorLogsToDailyFile <<<<<<<<<<<<< ") ' Log(CRLF & logs) ' Set date format for the filename DateTime.DateFormat = "yyyy-MM-dd" Dim dateStr As String = DateTime.Date(DateTime.Now) Dim fileBaseName As String = $"error_logs_${dateStr}.csv"$ ' Different filename ' AVAILABLE ERROR FIELDS (See LogServerError): ' timestamp, type, source, message, db_key, command_name, client_ip Dim HEADER_LINE As String = $""timestamp","type","source","message","db_key","command_name","client_ip""$ Dim sbContent As StringBuilder sbContent.Initialize ' === 1. CONDITIONAL WRITE OF HEADER === ' Check if file exists or is empty. Only write header if it's the first time today. Dim writeHeader As Boolean = False Try If Not(File.Exists(File.DirApp, fileBaseName)) Or File.Size(File.DirApp, fileBaseName) = 0 Then writeHeader = True End If Catch ' In case of an I/O error checking the file, assume we should try to write the header. writeHeader = True End Try If writeHeader Then sbContent.Append(HEADER_LINE).Append(CRLF) End If ' === 2. GENERATE CONTENT IN MEMORY === For Each logEntry As Map In logs ' Log($"--- agregamos ${logEntry.Get("db_key").As(String).ToUpperCase}"$) Dim dbKey As String = logEntry.Get("db_key").As(String).ToUpperCase ' Log($"==== ${dbKey} -> ${TextLoggingStatusByDB.GetDefault(dbKey, False)}"$) ' Log(TextLoggingStatusByDB) If TextLoggingStatusByDB.GetDefault(dbKey, False) Then ' Format the log line for CSV. ' The 'message' field is CRITICAL, as it can contain multiline exceptions or special characters. Dim line As String = $""${logEntry.Get("timestamp")}","${logEntry.Get("type")}","${logEntry.Get("source")}","${logEntry.Get("message")}","${dbKey}","${logEntry.Get("command_name")}","${logEntry.Get("client_ip")}""$ sbContent.Append(line).Append(CRLF) End If Next ' === 3. CONSOLIDATED WRITE TO DISK === If sbContent.Length > 0 Then Dim outStream As OutputStream Try ' Open the stream in APPEND mode (True) to add the batch outStream = File.OpenOutput(File.DirApp, fileBaseName, True) Dim bytes() As Byte = sbContent.ToString.GetBytes("UTF8") outStream.WriteBytes(bytes, 0, bytes.Length) Catch ' I/O failure in secondary thread. Use LogServerError so it's logged to SQLite (if active) LogServerError("ERROR", "ArchiveErrorLogsToDailyFile", $"Fallo E/S al escribir logs de ERRORES en ${fileBaseName}: ${LastException.Message}"$, "SYSTEM", "Error Log Batch Write", "N/A") End Try If outStream.IsInitialized Then outStream.Close End If End If End Sub