OntoL7.0 Architecture: MySQL8 Migration with SQLite Dialect Translation

Overview

OntoL7.0's ontology reasoning platform migrated its business data to MySQL8 while preserving a single business codebase. The architecture now comprises two physically isolated data stores: a business database (MySQL8, 87 tables) and a system database (SQLite, 14 tables), supplemented by specialized graph (Memgraph MAGE) and vector (ChromaDB 1.5) stores. The backend runs on FastAPI 0.115 with Uvicorn, Python 3.14, LangChain 0.3, LangGraph 0.3, PyMySQL, Redis 7, ReactFlow 11, and Alpine.js. The system exposes 26 API routes and uses the environment variable DB_TYPE=mysql to select the business database driver.

Six-Layer Request Flow

Requests traverse six layers top-down: the frontend page sends only HTTP and SSE; the gateway handles authentication and serialization; intelligent agents enter the business domain via tool calls; the business domain touches SQL only at the data access layer. Cross-layer calls are implemented as Python function calls — no internal HTTP is used.

Data Stores: Same SQL, Two Databases

Business code continues to be written in SQLite syntax. A translation layer at the driver level converts SQLite dialect to MySQL at runtime. Switching to MySQL requires only changing DB_TYPE; business repositories remain untouched. The system management database stays on SQLite regardless of the backend switch.

Dialect Translation (Explicit Errors, No Silent Fallback)

The translation layer maps SQLite constructs to MySQL equivalents. Unsupported syntax raises explicit errors rather than silently degrading. Key mappings include: ? →

%s

"col"

→

`col`

datetime('now')

→

NOW()

INSERT OR IGNORE

→

INSERT IGNORE

INSERT OR REPLACE

→

REPLACE

ON CONFLICT

→

ON DUPLICATE KEY UPDATE

rowid

→

`id`

last_insert_rowid()

→

LAST_INSERT_ID()

a || b

→

CONCAT(a, b)

CAST AS TEXT

→

CAST AS CHAR

sqlite_master

→ compatibility simulation CREATE TRIGGER → skipped

Type Mapping

TEXT

→

LONGTEXT

INTEGER

→

BIGINT

REAL

→

DOUBLE

NUMERIC

→

DECIMAL(24,6)

BOOLEAN

→

TINYINT(1)

DATETIME

→

VARCHAR(64)

Migration Process

Migration is performed by scripts/migrate_business_data_to_mysql.py. The script first runs schema migration to create tables, then copies data in batches of 500 rows using REPLACE INTO for idempotent overwrites. After each table, row counts are verified. The script is safe to re-run.

Upgrade Highlights: Changed Infrastructure, Not Business Logic

All upgrades are confined to the data access layer and operational tooling. Business modules, interface fields, and frontend pages remain unchanged. Every change listed corresponds to a directly accessible source file.