Metadata-Version: 2.4
Name: detectk-collectors-sql
Version: 0.1.0
Summary: Generic SQL collector for DetectK (PostgreSQL, MySQL, SQLite)
Author: DetectK Contributors
License: MIT
Project-URL: Homepage, https://github.com/alexeiveselov92/detectk
Project-URL: Repository, https://github.com/alexeiveselov92/detectk
Project-URL: Documentation, https://github.com/alexeiveselov92/detectk/blob/master/README.md
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: detectk>=0.1.0
Requires-Dist: sqlalchemy>=2.0.0
Requires-Dist: pandas>=2.0.0
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9.0; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: mysqlclient>=2.2.0; extra == "mysql"
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9.0; extra == "all"
Requires-Dist: mysqlclient>=2.2.0; extra == "all"
Provides-Extra: dev
Requires-Dist: pytest>=7.0.0; extra == "dev"
Requires-Dist: pytest-cov>=4.0.0; extra == "dev"
Requires-Dist: black>=23.0.0; extra == "dev"
Requires-Dist: ruff>=0.1.0; extra == "dev"
Requires-Dist: mypy>=1.0.0; extra == "dev"

# DetectK SQL Collectors

Generic SQL collector for DetectK with support for PostgreSQL, MySQL, and SQLite.

## Installation

```bash
# Core package (supports SQLite out of the box)
pip install detectk-collectors-sql

# With PostgreSQL support
pip install detectk-collectors-sql[postgres]

# With MySQL support
pip install detectk-collectors-sql[mysql]

# With all database drivers
pip install detectk-collectors-sql[all]
```

## Supported Databases

- **PostgreSQL** (9.6+)
- **MySQL** (5.7+, 8.0+)
- **SQLite** (3.x)

## Usage

### PostgreSQL

```yaml
name: "user_sessions_postgres"
description: "Monitor active sessions in PostgreSQL"

collector:
  type: "sql"
  params:
    connection_string: "postgresql://user:password@localhost:5432/analytics"
    query: |
      SELECT
        COUNT(DISTINCT user_id) as value,
        NOW() as timestamp
      FROM sessions
      WHERE created_at >= NOW() - INTERVAL '10 minutes'

detector:
  type: "threshold"
  params:
    threshold: 100
    operator: "less_than"
```

### MySQL

```yaml
name: "orders_mysql"
description: "Monitor order volume in MySQL"

collector:
  type: "sql"
  params:
    connection_string: "mysql://user:password@localhost:3306/ecommerce"
    query: |
      SELECT
        COUNT(*) as value,
        NOW() as timestamp
      FROM orders
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
```

### SQLite

```yaml
name: "local_metrics_sqlite"
description: "Monitor local database metrics"

collector:
  type: "sql"
  params:
    connection_string: "sqlite:///./metrics.db"
    query: |
      SELECT
        COUNT(*) as value,
        datetime('now') as timestamp
      FROM events
      WHERE timestamp >= datetime('now', '-10 minutes')
```

## Configuration

### Connection String

The collector uses SQLAlchemy connection strings:

- **PostgreSQL**: `postgresql://[user[:password]@][host][:port][/database]`
- **MySQL**: `mysql://[user[:password]@][host][:port][/database]`
- **SQLite**: `sqlite:///path/to/database.db`

### Environment Variables

```bash
export POSTGRES_URL="postgresql://user:password@localhost:5432/analytics"
export MYSQL_URL="mysql://user:password@localhost:3306/ecommerce"
```

Then in config:

```yaml
collector:
  type: "sql"
  params:
    connection_string: "${POSTGRES_URL}"
    query: "SELECT ..."
```

### Query Requirements

Query must return:
- `value` column (float or int) - the metric value
- `timestamp` column (optional) - timestamp of measurement

If timestamp is not provided, current time is used.

## Storage

SQL collector can also be used as storage backend:

```yaml
storage:
  enabled: true
  type: "sql"
  params:
    connection_string: "${POSTGRES_URL}"
    datapoints_retention_days: 90
    save_detections: false  # Optional
```

This creates tables:
- `dtk_datapoints` - collected metric values
- `dtk_detections` - detection results (if save_detections=true)

## Examples

See `examples/sql/` directory for complete configurations.

## License

MIT
