Metadata-Version: 2.4
Name: gsdb
Version: 0.1.2
Summary: A MySQL-like interface over Google Sheets
Author-email: Buğra Kadir Arıkan <kadirbugraa@gmail.com>
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.7
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: gspread
Requires-Dist: oauth2client
Dynamic: license-file

# gsheetdb
This Python module, `gsheetdb.py`, implements a relational database interface using **Google Sheets** as the storage engine. It provides a familiar SQL-like API (CRUD operations, schema management, and joins) while handling the underlying complexities of the Google Sheets API.

### Core Architecture
The system treats a single Google Spreadsheet as a "database" and individual worksheets as "tables."

* **`__schema__` Worksheet**: A hidden table that stores metadata, including data types, primary keys, auto-increment settings, and foreign key relationships.
* **Worksheets**: Each worksheet corresponds to a table where rows are stored. The first row of each worksheet is reserved for column headers.

### Key Features
* **Schema Enforcement**: Validates data types (`INTEGER`, `FLOAT`, `BOOLEAN`, etc.) and constraints like `NOT NULL`.
* **Relational Logic**: Supports Primary Keys (with auto-increment) and Foreign Keys to maintain referential integrity.
* **Joins**: Implements `INNER` and `LEFT` joins in memory after fetching data from the sheets.
* **Resilience**: Includes a `_retry` decorator with exponential backoff to handle Google API rate limits (HTTP 429).
* **Caching**: Uses in-memory caches for schema and table data to reduce the number of API calls, with manual and automatic invalidation.
* **Maintenance Tools**: Includes `verify_integrity()` to find inconsistencies between the schema and worksheets, and a `repair()` method to fix them.

---

### Usage Overview

#### 1. Initialization and Table Creation
You define your schema using a list of dictionaries. The `id` column here is set to auto-increment, meaning the library will automatically calculate the next integer value.

```python
from gsheetdb import GSheetDB

db = GSheetDB("my_database_spreadsheet", "service_account.json")

db.create_table("employees", [
    {"name": "id", "type": "INTEGER", "primary_key": True, "auto_increment": True},
    {"name": "name", "type": "STRING", "nullable": False},
    {"name": "department_id", "type": "INTEGER", "foreign_key": {"table": "depts", "column": "id"}}
])
```

#### 2. CRUD Operations
The API mimics standard SQL operations:

| Operation | Method Call |
| :--- | :--- |
| **Insert** | `db.insert("users", {"email": "bob@example.com", "age": 25})` |
| **Select** | `db.select("users", where={"age": 25}, order_by="email")` |
| **Update** | `db.update("users", set_values={"age": 26}, where={"id": 1})` |
| **Delete** | `db.delete("users", where={"id": 1})` |

#### 3. Joining Tables
You can combine data from two worksheets based on a shared column:

```python
# Returns a list of dicts combining employee and department data
results = db.join(
    left_table="employees",
    right_table="departments",
    on=("department_id", "id"),
    join_type="INNER"
)
```

### Constraints & Requirements
* **Dependencies**: Requires `gspread` and `oauth2client`.
* **Credentials**: A Google Service Account JSON file is required with access to the target spreadsheet.
* **Reserved Names**: The worksheet name `__schema__` is reserved and cannot be used for user tables.
