Implement TAP Schema Storage Using SQLite for Prototype TAP Server

[gitosaurus]

Overview

Implement the TAP schema storage layer for the prototype TAP server using SQLite, leveraging Python's built-in sqlite3 module. This will enable the server to natively answer TAP schema queries, including the required JOIN between tap_schema.schemas and tap_schema.tables, with minimal dependencies and maximum flexibility for future query needs.

Requirements

• Use Python's built-in sqlite3 library. No external server or installation required.
• The SQLite database will be a file on disk, managed by the server process (no daemon or external service).
• Implement schema definitions for tap_schema.schemas, tap_schema.tables, and tap_schema.columns per the TAP 1.1 specification (reference).
• Initialization script should create the TAP tables by connecting directly to the tap_schema.db file and issuing unqualified CREATE TABLE statements. This ensures tables are created in the intended database file.
• At query/runtime, each SQLite connection should ATTACH the tap_schema.db file as the database named tap_schema before executing SQL referencing tap_schema.* tables. This enables TAP queries such as:

  SELECT *
  FROM tap_schema.schemas
  INNER JOIN tap_schema.tables
  ON tap_schema.tables.schema_name = tap_schema.schemas.schema_name

  to work natively, with no SQL rewriting needed.
• Allow future extensibility for additional SQL queries (e.g., filtering, column selection).
• Ensure the database can be inspected with standard SQLite tools (CLI, GUI).

Example Initialization Script (Creation)

import sqlite3
import os
DB_PATH = os.path.abspath("tap_schema.db")
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS schemas (
    schema_name TEXT PRIMARY KEY,
    schema_description TEXT
);
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS tables (
    table_name TEXT PRIMARY KEY,
    schema_name TEXT,
    table_description TEXT,
    FOREIGN KEY(schema_name) REFERENCES schemas(schema_name)
);
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS columns (
    table_name TEXT,
    column_name TEXT,
    ucd TEXT,
    unit TEXT,
    datatype TEXT,
    description TEXT,
    PRIMARY KEY (table_name, column_name),
    FOREIGN KEY (table_name) REFERENCES tables(table_name)
);
""")
conn.commit()
conn.close()

Example Usage: ATTACH for Query/Runtime

import sqlite3
import os
MAIN_DB = os.path.abspath("main.db")
TAP_SCHEMA_DB = os.path.abspath("tap_schema.db")
conn = sqlite3.connect(MAIN_DB)
cur = conn.cursor()
cur.execute("ATTACH DATABASE ? AS tap_schema", (TAP_SCHEMA_DB,))
# Now TAP queries referencing tap_schema.schemas, tap_schema.tables, etc. will work verbatim.
conn.close()

Deliverables

• Python initialization script to create the TAP tables directly in tap_schema.db
• Helper code for runtime to ATTACH tap_schema.db as tap_schema on every connection before executing TAP SQL
• Python module/class for database operations: insert, update, query
• Example code for the required JOIN query
• Documentation for setup, inspection, and troubleshooting
• Integration notes for use with the TAP server prototype

Simplicity Rationale

• Explicit creation of TAP tables in the intended database file avoids confusion and silent failures
• ATTACH at runtime ensures TAP queries are handled natively, with no SQL rewriting required
• No external dependencies; sqlite3 is part of Python standard library
• No separate server process required (just a file)
• Data is inspectable with standard SQLite tools
• Ready for future query expansion as needed by users

References

• TAP 1.1 Specification
• Python sqlite3 documentation

---

Note: This task should be completed before implementing metadata retrieval and cache population, as it provides the necessary backend for schema queries.

[hombit]

Should this sqlite file be distributed as a part of HATS catalog?

[gitosaurus]

Should this sqlite file be distributed as a part of HATS catalog?

I think this is a good idea! I plan to build it up originally for demo purposes by simply using a copy of schema information from another TAP server for the same catalog, but I like the idea of preparing it on import.

[hombit]

After discussion with @delucchi-cmu, we are not doing it as part of the HATS standard, at least for now. In the future, we can still consider generating it from VOParquet metadata with the HATS Python library.