Open berntpopp opened 1 month ago
relates to #57
relates to #48
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.Updated API Logging Functions:
start_sysndd_api.R
script to include new logging functions and integrate with the MySQL database.
Current Situation:
Problems:
Proposed Solution: Create new tables in the MySQL database to handle the following:
Database Table Design:
Logging Table:
Pubtator API Cache Table:
JSON Storage Table:
Tables:
Pubtator API Cache Table:
pubtator_cache
cache_id
: INT AUTO_INCREMENT PRIMARY KEYquery
: VARCHAR(255) NOT NULLpmid
: VARCHAR(50) NOT NULLresponse
: TEXT NOT NULLcreated_at
: TIMESTAMP DEFAULT CURRENT_TIMESTAMPLogging Table:
logs
log_id
: INT AUTO_INCREMENT PRIMARY KEYtimestamp
: TIMESTAMP DEFAULT CURRENT_TIMESTAMPlevel
: VARCHAR(50)message
: TEXTJSON Data Table:
json_data
data_id
: INT AUTO_INCREMENT PRIMARY KEYpage
: VARCHAR(255) NOT NULLjson_content
: JSONcreated_at
: TIMESTAMP DEFAULT CURRENT_TIMESTAMPTasks:
Design Database Tables:
Expected Benefits: