Spaces:
Sleeping
Sleeping
File size: 7,291 Bytes
185c377 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 |
import logging
from src.database_manager import DatabaseManager
def create_all_tables():
"""Create all required database tables"""
db_manager = DatabaseManager()
# Users table
users_table = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
role ENUM('practitioner', 'organization') NOT NULL,
org INT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP NULL,
is_active BOOLEAN DEFAULT TRUE,
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# Organizations table
organizations_table = """
CREATE TABLE IF NOT EXISTS organizations (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
country_code VARCHAR(10),
department VARCHAR(100),
location VARCHAR(200),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE,
INDEX idx_name (name),
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# Questionnaires table
questionnaires_table = """
CREATE TABLE IF NOT EXISTS questionnaires (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
patient_name VARCHAR(100) NOT NULL,
patient_age INT,
patient_gender ENUM('Male', 'Female', 'Other'),
wound_location VARCHAR(200),
wound_duration VARCHAR(100),
pain_level INT DEFAULT 0,
previous_treatment TEXT,
medical_history TEXT,
medications TEXT,
allergies TEXT,
additional_notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_id (user_id),
INDEX idx_patient_name (patient_name),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# Wound images table
wound_images_table = """
CREATE TABLE IF NOT EXISTS wound_images (
id INT AUTO_INCREMENT PRIMARY KEY,
questionnaire_id INT NOT NULL,
image_url VARCHAR(500) NOT NULL,
original_filename VARCHAR(255),
file_size INT,
image_width INT,
image_height INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE,
INDEX idx_questionnaire_id (questionnaire_id),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# AI analyses table
ai_analyses_table = """
CREATE TABLE IF NOT EXISTS ai_analyses (
id INT AUTO_INCREMENT PRIMARY KEY,
questionnaire_id INT NOT NULL,
image_id INT,
analysis_data JSON,
summary TEXT,
recommendations TEXT,
risk_score INT DEFAULT 0,
risk_level ENUM('Low', 'Moderate', 'High', 'Unknown') DEFAULT 'Unknown',
processing_time DECIMAL(5,2),
model_version VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE,
FOREIGN KEY (image_id) REFERENCES wound_images(id) ON DELETE SET NULL,
INDEX idx_questionnaire_id (questionnaire_id),
INDEX idx_risk_level (risk_level),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# Analysis sessions table (to link everything together)
analysis_sessions_table = """
CREATE TABLE IF NOT EXISTS analysis_sessions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
questionnaire_id INT NOT NULL,
image_id INT,
analysis_id INT,
session_duration DECIMAL(5,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE,
FOREIGN KEY (image_id) REFERENCES wound_images(id) ON DELETE SET NULL,
FOREIGN KEY (analysis_id) REFERENCES ai_analyses(id) ON DELETE SET NULL,
INDEX idx_user_id (user_id),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# User sessions table (for session management)
user_sessions_table = """
CREATE TABLE IF NOT EXISTS user_sessions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
session_token VARCHAR(255) UNIQUE NOT NULL,
ip_address VARCHAR(45),
user_agent TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NOT NULL,
is_active BOOLEAN DEFAULT TRUE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_session_token (session_token),
INDEX idx_user_id (user_id),
INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
# Create tables
tables = [
("users", users_table),
("organizations", organizations_table),
("questionnaires", questionnaires_table),
("wound_images", wound_images_table),
("ai_analyses", ai_analyses_table),
("analysis_sessions", analysis_sessions_table),
("user_sessions", user_sessions_table)
]
for table_name, table_sql in tables:
try:
result = db_manager.execute_query(table_sql)
if result is not None:
logging.info(f"Table '{table_name}' created or already exists")
else:
logging.error(f"Failed to create table '{table_name}'")
except Exception as e:
logging.error(f"Error creating table '{table_name}': {e}")
# Add foreign key constraints if they don't exist
add_foreign_keys(db_manager)
def add_foreign_keys(db_manager):
"""Add foreign key constraints"""
foreign_keys = [
"""
ALTER TABLE users
ADD CONSTRAINT fk_users_org
FOREIGN KEY (org) REFERENCES organizations(id)
ON DELETE SET NULL
""",
]
for fk_sql in foreign_keys:
try:
# Check if foreign key already exists before adding
db_manager.execute_query(fk_sql)
except Exception as e:
# Foreign key might already exist, which is fine
logging.debug(f"Foreign key constraint note: {e}")
if __name__ == "__main__":
create_all_tables()
print("Database schema created successfully!")
|