lincc-frameworks / lincc-frameworks/notebooks_lf

Implement TAP Schema Storage Using SQLite for Prototype TAP Server

Open
#45 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
Jupyter Notebook
Stars
4
Forks
0
PR merge metrics
No merged PRs in 30d

Description

## 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](https://www.ivoa.net/documents/TAP/20181024/PR-TAP-1.1-20181024.html)).
- **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:
```sql
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)
```python
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
```python
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](https://www.ivoa.net/documents/TAP/20181024/PR-TAP-1.1-20181024.html)
- [Python sqlite3 documentation](https://docs.python.org/3/library/sqlite3.html)

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

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by locating the prototype TAP server's connection and query entry points, then review the requested initialization script and runtime ATTACH behavior in this issue. Compare the TAP 1.1 schema requirements with the existing project structure and determine where database operations, the JOIN example, setup documentation, and integration notes belong; done means all listed deliverables work together and the database remains inspectable with standard SQLite tools.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlite
Domain
backend-api-design, database
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.