File size: 9,911 Bytes
2ed8996 | 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 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 | """
Simple SQLite Fallback Database Setup
This script initializes the SQLite fallback database with all required tables.
"""
import asyncio
import logging
import sys
from pathlib import Path
# Add the backend directory to Python path
backend_dir = Path(__file__).parent
sys.path.insert(0, str(backend_dir))
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from sqlalchemy import text
# Setup logging
logging.basicConfig(
level=logging.INFO,
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)
async def setup_sqlite_fallback():
"""Setup SQLite fallback database."""
print("🚀 Setting up SQLite Fallback Database")
print("=" * 50)
try:
# Import configuration
from core.config import settings
print(f"SQLite Fallback Enabled: {settings.ENABLE_SQLITE_FALLBACK}")
print(f"SQLite Database Path: {settings.SQLITE_DATABASE_PATH}")
if not settings.ENABLE_SQLITE_FALLBACK:
print("❌ SQLite fallback is disabled in configuration")
return False
# Create SQLite engine
db_path = Path(settings.SQLITE_DATABASE_PATH)
db_path.parent.mkdir(parents=True, exist_ok=True)
sqlite_url = f"sqlite+aiosqlite:///{settings.SQLITE_DATABASE_PATH}"
engine = create_async_engine(
sqlite_url,
echo=True, # Show SQL statements for debugging
connect_args={
"check_same_thread": False,
}
)
print(f"✅ SQLite engine created: {sqlite_url}")
# Create session factory
SessionLocal = async_sessionmaker(
engine,
class_=AsyncSession,
expire_on_commit=False
)
# Test connection
async with SessionLocal() as session:
await session.execute(text("SELECT 1"))
print("✅ SQLite connection test passed")
# Create tables manually
print("\n📝 Creating tables...")
async with SessionLocal() as session:
# Users table
await session.execute(text("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(255),
company VARCHAR(255),
is_active BOOLEAN DEFAULT TRUE NOT NULL,
is_verified BOOLEAN DEFAULT FALSE NOT NULL,
is_superuser BOOLEAN DEFAULT FALSE NOT NULL,
verified_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
last_login_at TIMESTAMP
)
"""))
print(" ✅ Users table created")
# Roles table
await session.execute(text("""
CREATE TABLE IF NOT EXISTS roles (
id INTEGER PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
description TEXT,
level INTEGER DEFAULT 0 NOT NULL,
is_active BOOLEAN DEFAULT TRUE NOT NULL,
is_system BOOLEAN DEFAULT FALSE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)
"""))
print(" ✅ Roles table created")
# Permissions table
await session.execute(text("""
CREATE TABLE IF NOT EXISTS permissions (
id INTEGER PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
description TEXT,
resource VARCHAR(255) NOT NULL,
action VARCHAR(255) NOT NULL,
is_active BOOLEAN DEFAULT TRUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)
"""))
print(" ✅ Permissions table created")
# User roles junction table
await session.execute(text("""
CREATE TABLE IF NOT EXISTS user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
)
"""))
print(" ✅ User roles junction table created")
# API Keys table
await session.execute(text("""
CREATE TABLE IF NOT EXISTS api_keys (
id INTEGER PRIMARY KEY,
key_name VARCHAR(255) NOT NULL,
key_prefix VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL,
is_active BOOLEAN DEFAULT TRUE NOT NULL,
expires_at TIMESTAMP,
last_used_at TIMESTAMP,
usage_count INTEGER DEFAULT 0 NOT NULL,
rate_limit_per_minute INTEGER DEFAULT 60 NOT NULL,
permissions TEXT,
scope VARCHAR(255) DEFAULT 'read' NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
"""))
print(" ✅ API Keys table created")
# Evaluations table
await session.execute(text("""
CREATE TABLE IF NOT EXISTS evaluations (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
job_id VARCHAR(255) UNIQUE NOT NULL,
status VARCHAR(50) DEFAULT 'pending' NOT NULL,
model_config TEXT NOT NULL,
pipeline_config TEXT NOT NULL,
result_json TEXT,
error_message TEXT,
experiment_run_id VARCHAR(255),
total_attacks INTEGER DEFAULT 0 NOT NULL,
successful_attacks INTEGER DEFAULT 0 NOT NULL,
success_rate VARCHAR(50) DEFAULT '0%' NOT NULL,
execution_time_ms INTEGER,
progress TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
started_at TIMESTAMP,
completed_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
"""))
print(" ✅ Evaluations table created")
# Create indexes for better performance
await session.execute(text("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"))
await session.execute(text("CREATE INDEX IF NOT EXISTS idx_evaluations_user_id ON evaluations(user_id)"))
await session.execute(text("CREATE INDEX IF NOT EXISTS idx_evaluations_job_id ON evaluations(job_id)"))
await session.execute(text("CREATE INDEX IF NOT EXISTS idx_evaluations_status ON evaluations(status)"))
await session.execute(text("CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id)"))
await session.execute(text("CREATE INDEX IF NOT EXISTS idx_api_keys_key_prefix ON api_keys(key_prefix)"))
print(" ✅ Indexes created")
# Enable foreign keys and optimizations
await session.execute(text("PRAGMA foreign_keys = ON"))
await session.execute(text("PRAGMA journal_mode = WAL"))
await session.execute(text("PRAGMA synchronous = NORMAL"))
await session.execute(text("PRAGMA cache_size = 10000"))
await session.execute(text("PRAGMA temp_store = memory"))
await session.commit()
print(" ✅ SQLite optimizations enabled")
# Verify tables were created
async with SessionLocal() as session:
result = await session.execute(text("""
SELECT name FROM sqlite_master
WHERE type='table'
AND name NOT LIKE 'sqlite_%'
ORDER BY name
"""))
tables = result.fetchall()
table_names = [table[0] for table in tables]
print(f"\n📋 Created {len(table_names)} tables:")
for table_name in table_names:
print(f" - {table_name}")
# Close engine
await engine.dispose()
print(f"\n🎉 SQLite fallback database setup completed successfully!")
print(f" Database file: {db_path.absolute()}")
print(f" File size: {db_path.stat().st_size} bytes" if db_path.exists() else "")
return True
except Exception as e:
print(f"❌ Setup failed: {e}")
logger.error(f"Setup failed: {e}")
return False
if __name__ == "__main__":
success = asyncio.run(setup_sqlite_fallback())
exit(0 if success else 1)
|