Feature request: Improve Data Storage by Using MySQL Database

[berntpopp]

Current Situation:

• Logging is currently done in local files.
• Pubtator API requests are performed externally.
• Some data (e.g., HELP, FAQ, Startpage content) is stored in JSON objects and not in the database.

Problems:

• Slow performance due to local file handling and external API requests.
• Potential for issues with data retrieval and management.

Proposed Solution:
Create new tables in the MySQL database to handle the following:

1. Logging: Store log entries currently saved in local files.
2. Pubtator API Requests: Cache responses to reduce external API calls.
3. JSON Storage: Save and load JSON objects for dynamic content (HELP, FAQ, Startpage, etc.).

Database Table Design:

1. Logging Table:

   CREATE TABLE `logging` (
     `id` INT AUTO_INCREMENT PRIMARY KEY,
     `timestamp` DATETIME NOT NULL,
     `address` VARCHAR(255) NOT NULL,
     `agent` TEXT,
     `host` VARCHAR(255),
     `request_method` VARCHAR(10),
     `path` TEXT,
     `query` TEXT,
     `post` TEXT,
     `status` INT,
     `duration` FLOAT,
     `file` VARCHAR(255),
     `modified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );

2. Pubtator API Cache Table:

    CREATE TABLE `pubtator_cache` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `query` TEXT NOT NULL,
      `page` INT NOT NULL,
      `response` JSON NOT NULL,
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

3. JSON Storage Table:

      CREATE TABLE `json_storage` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(255) NOT NULL,
        `json_data` JSON NOT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );

Tables:

Pubtator API Cache Table:

• Table Name: pubtator_cache
• Columns:
  • cache_id: INT AUTO_INCREMENT PRIMARY KEY
  • query: VARCHAR(255) NOT NULL
  • pmid: VARCHAR(50) NOT NULL
  • response: TEXT NOT NULL
  • created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Logging Table:

• Table Name: logs
• Columns:
  • log_id: INT AUTO_INCREMENT PRIMARY KEY
  • timestamp: TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  • level: VARCHAR(50)
  • message: TEXT

JSON Data Table:

• Table Name: json_data
• Columns:
  • data_id: INT AUTO_INCREMENT PRIMARY KEY
  • page: VARCHAR(255) NOT NULL
  • json_content: JSON
  • created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Tasks:

Design Database Tables:

• Create table structures for logging, Pubtator API responses, and JSON storage.
• Write Creation Scripts: Follow current script style for table creation.
• Integrate with Existing Functionality: Modify existing functionality to use the new database tables.
• Migrate Existing Data: Develop scripts to migrate current log files and Pubtator responses to the new database tables.
• Implement Data Access Methods: Update the code to read from and write to the new database tables.
• Testing: Thoroughly test the new functionality to ensure data integrity and performance improvements.

Expected Benefits:

• Improved performance and reliability.
• Centralized data storage for easier management and backup.
• Reduced dependency on external API calls.

[berntpopp]

relates to #57

[berntpopp]

relates to #48

[berntpopp]

Completed Tasks:

1. Added Three New Scripts for Database Integration:

   • 15_Rcommands_sysndd_db_logging_table.R: Script to create and manage the logging table in MySQL.
   • 16_Rcommands_sysndd_db_pubtator_cache_table.R: Script to handle the PubTator cache table for efficient data retrieval and storage.
   • 17_Rcommands_sysndd_db_json_storage_table.R: Script for managing JSON storage tables in the MySQL database.

2. Updated API Logging Functions:

   • Enhanced the API to log messages directly to the MySQL database.
   • Modified the start_sysndd_api.R script to include new logging functions and integrate with the MySQL database.