SlidingSQLite

A thread-safe SQLite implementation with automatic time-based database rotation, designed for high-throughput, multi-threaded applications. This library provides a robust solution for managing time-windowed data, with features like database rotation, retention policies, and asynchronous query execution.

Features

• Thread-Safe Operations: Safely execute read and write operations in a multi-threaded environment using a queue-based worker system.
• Time-Based Database Rotation: Automatically rotates database files based on a configurable time interval (e.g., hourly, daily).
• Retention Policy: Automatically deletes old database files after a configurable retention period to prevent disk space exhaustion.
• Asynchronous Query Execution: Supports asynchronous read and write operations with UUID-based result retrieval.
• Transparent Read Across Databases: Read queries are automatically executed across all relevant database files, providing a seamless view of time-windowed data.
• Error Handling: Robust error handling with custom exceptions and query result objects.
• Configurable Cleanup: Periodic cleanup of stale queries and old databases to prevent memory leaks and manage disk usage.
• Customizable Schema: Initialize databases with a user-defined schema.

Installation

To use SlidingSQLite, you need Python 3.7 or higher. The library depends on the standard library and does not require external packages beyond SQLite, which is included with Python.

1. Clone or download the repository:

   git clone <repository-url>
   cd SlidingSQLite
   

2. Place the SlidingSqlite.py file in your project directory or install it as a module.

Quick Start

Here is a basic example to get you started:

import logging
from SlidingSqlite import SlidingSQLite

# Set up logging
logging.basicConfig(level=logging.INFO)

# Define a simple schema
schema = """
CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp REAL,
    message TEXT
);
"""

# Initialize SlidingSQLite
db = SlidingSQLite(
    db_dir="./databases",
    schema=schema,
    rotation_interval=3600,  # Rotate every hour
    retention_period=604800,  # Keep databases for 7 days
    cleanup_interval=3600,  # Run cleanup every hour
    auto_delete_old_dbs=True
)

# Insert some data
query_id = db.execute_write(
    "INSERT INTO logs (timestamp, message) VALUES (?, ?)",
    (time.time(), "Hello, SlidingSQLite!")
)
result = db.get_result(query_id)
if result.success:
    logging.info("Write operation successful")

# Read data across all databases
query_id = db.execute_read("SELECT * FROM logs WHERE timestamp > ? ORDER BY timestamp DESC", (time.time() - 86400,))
result = db.get_read_result(query_id)
if result.success:
    logging.info(f"Found {len(result.data)} log entries: {result.data}")

# Shut down the database
db.shutdown()

For a more comprehensive example, see the example.py file in the repository, which demonstrates multi-threaded usage.

Documentation

For detailed usage instructions, API reference, and examples, please refer to the Usage Documentation.

Requirements

• Python 3.7+
• SQLite (included with Python)