HeliosDB Multi-Protocol Research Index

Research Agent: Complete Protocol Compatibility Analysis Date: 2025-10-10 Status: All Research Complete

📋 Start Here

RESEARCH_SUMMARY.md - Executive summary, key findings, implementation roadmap, and coordination notes for architect/coder agents

📚 Detailed Research Documents

protocol_specifications_summary.md (38 KB)
- Wire protocol overviews for all 8 protocols
- PostgreSQL (libpq), MySQL v10, TDS 7.4, DRDA, Oracle Net/TTC
- Snowflake REST, Databricks SQL HTTP/Thrift, Pinecone HTTP/gRPC
- Protocol autodetection strategy with magic bytes reference
- Implementation checklists and requirements
authentication_flows.md (39 KB)
- Step-by-step authentication flows with diagrams
- PostgreSQL SCRAM-SHA-256 (RFC 5802/7677 compliant)
- MySQL caching_sha2_password (fast auth + full auth)
- TDS 7.4 PRELOGIN → LOGIN7 (with TLS wrapping)
- DRDA EXCSAT → ACCSEC → SECCHK
- Oracle O3LOGON (3-phase logon)
- HTTP API authentication (JWT, OAuth, API keys)
packet_formats.md (47 KB)
- Complete wire-level packet specifications
- Byte-by-byte field descriptions for all protocols
- Message type tables and data type encodings
- Protocol detection magic bytes reference
- Example hex dumps for key packets
python_client_compatibility.md (32 KB)
- Analysis of 15 Python client libraries
- psycopg2, asyncpg, PyMySQL, mysql-connector-python
- pyodbc, pymssql, ibm_db, oracledb
- snowflake-connector-python, databricks-sql-connector, pinecone-client
- Parameter binding styles, prepared statements, connection pooling
- Implementation recommendations and testing requirements

Research Coverage

Protocols Analyzed (8 total)

Protocol	Target Level	Feasibility	Document References
PostgreSQL (libpq)	GOLD	HIGH	specs §1, auth §1, packets §1, clients §1
MySQL Protocol v10	GOLD	HIGH	specs §2, auth §2, packets §2, clients §2
TDS 7.4 (SQL Server)	BRONZE	MEDIUM	specs §3, auth §3, packets §3, clients §3
DRDA (DB2)	BRONZE	MEDIUM	specs §4, auth §4, packets §4, clients §4
Oracle Net/TTC	BRONZE	LOW	specs §5, auth §5, packets §5, clients §5
Snowflake REST API	SILVER	HIGH	specs §6, auth §6, packets §6, clients §6
Databricks SQL	SILVER	HIGH	specs §7, auth §7, packets §6, clients §6
Pinecone HTTP/gRPC	SILVER	HIGH	specs §8, auth §8, packets §6, clients §6

Python Clients Evaluated (15 total)

Ecosystem	Clients	Parameter Styles	Prepared Statements
PostgreSQL	psycopg2, asyncpg, SQLAlchemy	`%s`, `$1`, `%(name)s`	Server-side (asyncpg), libpq (psycopg2)
MySQL	PyMySQL, mysql-connector-python, SQLAlchemy	`%s`, `?`	Client-side (PyMySQL), Server-side (mysql-connector)
SQL Server	pyodbc, pymssql	`?`, `%s`	ODBC driver, FreeTDS
DB2	ibm_db, ibm_db_dbi	`?`	IBM Data Server Driver
Oracle	oracledb (Thin/Thick)	`:name`, `:1`	Auto-caching
HTTP APIs	snowflake, databricks, pinecone	Various	N/A (REST/Thrift)

Key Research Findings

1. Protocol Autodetection

TLS ALPN: PostgreSQL (“postgresql”), HTTP (“h2”, “http/1.1”)
Magic Bytes: PostgreSQL (0x00030000), TDS (0x12), DRDA (0xD0), Oracle TNS (0x01)
Server-First: MySQL HandshakeV10 (0x0a after sequence byte)

2. Authentication Complexity

From simplest to most complex:

Pinecone (API key header)
Snowflake (JWT RS256)
Databricks (PAT/OAuth)
MySQL (caching_sha2_password)
PostgreSQL (SCRAM-SHA-256)
TDS 7.4 (LOGIN7 + TLS wrapping)
DRDA (EXCSAT/ACCSEC/SECCHK)
Oracle (O3LOGON 3-phase)

3. Parameter Binding Styles

5 different styles identified:

%s (format): psycopg2, PyMySQL, pymssql
%(name)s (pyformat): psycopg2, pymssql, snowflake
$1, $2 (postgres): asyncpg
? (qmark): pyodbc, ibm_db, mysql-connector (prepared)
:name (named): oracledb, databricks-sql

Solution: Unified parameter adapter required

4. Prepared Statement Paradigms

Client-Side Escaping: PyMySQL (no binary protocol)
Server-Side Binary: mysql-connector-python, asyncpg, oracledb
Both supported: HeliosDB must handle both paradigms

Implementation Roadmap Summary

Phase 1: PostgreSQL + MySQL (GOLD) - 4-6 weeks

PostgreSQL handler (SCRAM-SHA-256, extended query protocol)
MySQL handler (HandshakeV10, caching_sha2_password, binary protocol)
Protocol router (TLS ALPN + magic bytes detection)
Unified parameter adapter
Integration tests: psycopg2, asyncpg, PyMySQL, mysql-connector-python

Phase 1.5: HTTP APIs (SILVER) - 3-4 weeks

HTTP gateway (path routing: /snowflake/, /dbsql/, /pinecone/*)
JWT/OAuth authentication
Async statement execution
Vector operation translator (SQL → Pinecone API)
Integration tests: snowflake-connector-python, databricks-sql-connector, pinecone-client

Phase 2: SQL Server + DB2 (BRONZE) - 4-5 weeks

TDS handler (PRELOGIN, LOGIN7, basic SQL_BATCH)
DRDA handler (EXCSAT/ACCSEC/SECCHK/ACCRDB)
EBCDIC ↔ UTF-8 conversion (or ASCII negotiation)
Integration tests: pyodbc, ibm_db

Phase 3: Oracle/Tibero (BRONZE → SILVER) - 5-6 weeks

TNS packet handling (CONNECT, ACCEPT, DATA)
Basic TTC protocol negotiation
Simple TTI function calls (OSQL for “SELECT 1 FROM DUAL”)
Integration test: oracledb Thin mode

For Architect Agent

Key Architecture Decisions Needed:

Protocol router design (TLS ALPN + magic bytes)
Unified abstractions (AST/plan API, parameter model, type system, error model)
Authentication framework (pluggable adapters)
Connection management (server-side pooling, asyncio)
Dialect adapter layer (SQL rewriters, identifier quoting)

Questions to Address:

How to handle protocol-specific features? (e.g., PostgreSQL LISTEN/NOTIFY)
Unified SQL dialect or protocol-specific dialects?
Distributed transaction handling across protocols?

See: RESEARCH_SUMMARY.md § “Coordination with Architect & Coder Agents”

For Coder Agent

Implementation Modules Needed:

heliosdb/
├── protocols/
│   ├── router.py              # Protocol detection & routing
│   ├── postgresql/            # PostgreSQL handler
│   ├── mysql/                 # MySQL handler
│   ├── tds/                   # SQL Server handler
│   ├── drda/                  # DB2 handler
│   ├── oracle/                # Oracle handler
│   └── http/                  # Snowflake, Databricks, Pinecone
├── dialects/
│   ├── postgresql.py          # PostgreSQL SQL rewriter
│   ├── mysql.py               # MySQL SQL rewriter
│   └── ...
├── auth/
│   ├── scram.py               # SCRAM-SHA-256
│   ├── caching_sha2.py        # MySQL auth
│   ├── jwt.py                 # JWT auth
│   └── ...
├── parameters/
│   └── adapter.py             # Unified parameter adapter
└── types/
    ├── base.py                # Internal type system
    └── mappers.py             # Protocol type mappers

Dependencies:

cryptography (SCRAM, JWT, TLS)
asyncio (async protocol handlers)
struct (binary packet encoding)

See: RESEARCH_SUMMARY.md § “Coordination with Architect & Coder Agents”

Testing Strategy

Integration Test Matrix

Run against each Python client library:

Connection → Auth → Query → Prepared Statement → Transaction → Cursor
Validate parameter binding, type conversion, error mapping

CI/CD Pipeline

test-postgresql: [psycopg2, asyncpg]
test-mysql: [pymysql, mysql-connector-python]
test-http-apis: [snowflake-connector-python, databricks-sql-connector, pinecone-client]
test-sqlserver: [pyodbc]
test-db2: [ibm_db]
test-oracle: [oracledb]

See: python_client_compatibility.md § “Testing Strategy”

Risk Assessment

High-Risk

Oracle protocol complexity (reverse-engineered specs)
asyncpg prepared statement cache compatibility
TDS TLS wrapping (PRELOGIN packets)

Medium-Risk

EBCDIC encoding (DRDA)
Multi-protocol type mapping
Async/sync driver coexistence

Low-Risk

HTTP API integration
Connection pooling

See: RESEARCH_SUMMARY.md § “Risk Assessment & Mitigation”

Success Metrics

Phase 1 (PostgreSQL + MySQL)

psycopg2, asyncpg, PyMySQL, mysql-connector-python pass integration tests
TLS ALPN detection works
Protocol autodetection works
Parameter translation works (%s ↔ $1 ↔ ? ↔ :name)
Performance: <10ms latency overhead

Phase 1.5 (HTTP APIs)

All HTTP API clients can execute queries
JWT/OAuth/API key authentication works
Vector SQL → Pinecone API translation works

Phase 2 (SQL Server + DB2)

pyodbc and ibm_db can connect and run basic queries

Phase 3 (Oracle)

oracledb Thin mode can connect and run “SELECT 1 FROM DUAL”

Document Statistics

Document	Size	Sections	Focus
protocol_specifications_summary.md	38 KB	9	Protocol specs, autodetection, implementation checklists
authentication_flows.md	39 KB	5	Auth flows, step-by-step diagrams, security details
packet_formats.md	47 KB	7	Wire-level packet formats, byte layouts, magic bytes
python_client_compatibility.md	32 KB	7	Client analysis, parameter styles, testing strategy
RESEARCH_SUMMARY.md	26 KB	10	Executive summary, roadmap, coordination notes
Total	182 KB	38	Comprehensive protocol research

Quick Reference Tables

Protocol Detection Magic Bytes

PostgreSQL: 00 00 00 XX [Length] + 00 03 00 00 [Protocol 3.0]
MySQL:      Server sends 0x0a (HandshakeV10) after sequence byte
TDS:        12 [Type: PRELOGIN]
DRDA:       XX XX [Length] + D0 [Magic] + 10 41 [EXCSAT]
Oracle TNS: XX XX [Length] + 00 00 [Checksum] + 01 [Type: CONNECT]
HTTP:       47 45 54 20 [GET ] or 50 4f 53 54 [POST]
TLS:        16 [Handshake] → check ALPN extension

Parameter Style Quick Reference

psycopg2:               %s, %(name)s
asyncpg:                $1, $2, ...
PyMySQL:                %s
mysql-connector (prep): %s, ?
pyodbc:                 ?
ibm_db:                 ?
oracledb:               :name, :1
snowflake:              ?, %(name)s
databricks:             :name

Default Ports

PostgreSQL: 5432
MySQL:      3306
SQL Server: 1433
DB2:        50000
Oracle:     1521
HTTPS:      443 (Snowflake, Databricks, Pinecone)

Next Actions

Architect Agent: Review research, design protocol router and unified abstractions
Coder Agent: Implement PostgreSQL handler (proof-of-concept for Phase 1)
Testing: Set up CI/CD pipeline with integration test matrix
Documentation: Create user-facing DSN examples and migration guides

Research Complete All Documents Available: See file list above Ready for Implementation: Phase 1 (PostgreSQL + MySQL)

Research conducted by HeliosDB Researcher Agent Date: 2025-10-10 Status: Complete

HeliosDB Multi-Protocol Research Index

HeliosDB Multi-Protocol Research Index

Quick Navigation

📋 Start Here

📚 Detailed Research Documents

Research Coverage

Protocols Analyzed (8 total)

Python Clients Evaluated (15 total)

Key Research Findings

1. Protocol Autodetection

2. Authentication Complexity

3. Parameter Binding Styles

4. Prepared Statement Paradigms

Implementation Roadmap Summary

Phase 1: PostgreSQL + MySQL (GOLD) - 4-6 weeks

Phase 1.5: HTTP APIs (SILVER) - 3-4 weeks

Phase 2: SQL Server + DB2 (BRONZE) - 4-5 weeks

Phase 3: Oracle/Tibero (BRONZE → SILVER) - 5-6 weeks

For Architect Agent

For Coder Agent

Testing Strategy

Integration Test Matrix

CI/CD Pipeline

Risk Assessment

High-Risk

Medium-Risk

Low-Risk

Success Metrics

Phase 1 (PostgreSQL + MySQL)

Phase 1.5 (HTTP APIs)

Phase 2 (SQL Server + DB2)

Phase 3 (Oracle)

Document Statistics

Quick Reference Tables

Protocol Detection Magic Bytes

Parameter Style Quick Reference

Default Ports

Next Actions