Transition from JSONL File to SQLite Database for Page Generation

[wesslen]

Issue Description

Currently, our static website built with Quarto is generating pages using a .jsonl file, where each record corresponds to an Arxiv ID. We aim to replace this with a SQLite database to enhance scalability and maintainability.

Steps to Implement

1. Create a SQLite Database

• Design Database Schema: Reflect the structure of the .jsonl file in the database schema.
• Initialize Database: Create a SQLite database with tables based on the schema.

2. Data Migration

• Migration Script: Develop a Python script to migrate data from the .jsonl file to the SQLite database.
• Data Integrity: Ensure all data is accurately transferred.

3. Update Python Scripts for Data Retrieval

• Modify Existing Scripts: Adapt scripts that currently read the .jsonl file to instead interact with the SQLite database.
• Implement Database Queries: Use Python libraries like sqlite3 to handle database operations.
• Fetch Records by ID: Ensure the updated scripts can retrieve specific records using Arxiv IDs.

4. Testing

• Test Data Retrieval: Thoroughly test the updated scripts for accurate database interactions.
• Validate Page Generation: Compare newly generated pages with existing ones for consistency.

5. Documentation

• Update Documentation: Reflect the transition from .jsonl to SQLite in the project documentation.
• Setup Instructions: Include guidelines for database setup and script execution.

6. Deployment

• Merge and Deploy: After testing, merge the changes into the main branch and update the live website.

Additional Considerations

• Performance: Optimize query performance for large datasets.
• Backup and Recovery: Implement a strategy for database backup.
• CI/CD Pipeline: Update CI/CD processes to include the new database setup.

Request for Contributions

Contributions are welcome! If you're interested in helping with this transition, please comment on this issue or submit a pull request.

[wesslen]

Alternatively, use modal: https://modal.com/docs/examples/covid_datasette