Metadata Retrieval CLI Script for SQLite TAP Schema Integration

Overview

Implement a command-line Python script to retrieve column metadata (UCDs, units, descriptions, etc.) for a specified catalog from a remote TAP server (e.g., https://irsa.ipac.caltech.edu/TAP), and store the results in a local SQLite database. This metadata will be used by the TAP server prototype to answer schema inquiries and serve ADQL queries.

Requirements

• Accept TAP server URL and catalog name as command-line arguments
• Connect to the TAP server and query for all available column metadata for the specified catalog/table
• Retrieve relevant metadata fields (column name, UCD, unit, data type, description, etc.)
• Store results in a local SQLite file compatible with the TAP 1.1 schema (no YAML option)
• Provide error handling for invalid catalogs, network issues, and incomplete metadata
• Provide logging or output summary of metadata retrieved and stored
• Should be compatible with the TAP 1.1 specification
• Document usage and example invocations

Example Code Snippets

Querying the TAP server for column metadata

import requests
from xml.etree import ElementTree as ET

TAP_URL = 'https://irsa.ipac.caltech.edu/TAP'
CATALOG = 'wise_allsky_4band_p3as_psd'

# Get columns metadata for table
query = f"SELECT * FROM tap_schema.columns WHERE table_name = '{CATALOG}'"
response = requests.get(f"{TAP_URL}/sync", params={'query': query, 'format': 'votable'})
tree = ET.fromstring(response.content)
for field in tree.findall('.//FIELD'):
    print(field.attrib)

Inserting metadata into SQLite

import sqlite3
conn = sqlite3.connect('tap_schema.db')
cursor = conn.cursor()
# Example insert
cursor.execute("INSERT INTO tap_schema.columns (table_name, column_name, ucd, unit, description, datatype) VALUES (?, ?, ?, ?, ?, ?)",
               (table_name, column_name, ucd, unit, description, datatype))
conn.commit()

Deliverables

• Python script (e.g., retrieve_tap_metadata.py) with CLI argument parsing
• Functions for querying TAP server and extracting metadata
• Functions for storing metadata in SQLite
• Example usage in README or docstring
• Integration guidance for use by TAP server prototype

References

• TAP 1.1 Specification
• Example TAP endpoint: https://irsa.ipac.caltech.edu/TAP

This task is a prerequisite for full schema and column metadata support in the TAP server prototype. Only SQLite storage is supported; YAML is excluded as per project decision.

Start with the proposed retrieve_tap_metadata.py entry point and the TAP /sync query shown in the issue, then review the TAP 1.1 reference and SQLite insertion example. Done means a CLI accepts the server URL and catalog, retrieves and stores the requested metadata with error handling and a summary, and documents example usage in the README or script docstring.