> For the complete documentation index, see [llms.txt](https://bots-mother.gitbook.io/bots-mother-help/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://bots-mother.gitbook.io/bots-mother-help/pre-built-libraries/bot-library-functions-2.md).

# BotDB Library Functions

#### BotDB::get($path)

**Description**: Retrieves data from a specified path, which maps to a SQLite table (first part of the path) and optionally a nested JSON field (sub-path). Returns an array of JSON-decoded `data` column values or filtered sub-path values.

**Parameters**:

* `path` (string): The data path (e.g., `messages`, `users.settings.theme`). The first part is the table name; the rest is a dot-separated JSON path.

**Returns**: `array`

* `ok` (bool): `true` if successful, `false` if failed.
* `data` (array|null): Array of JSON-decoded data or sub-path values; `null` on error.
* `error` (string|null): Error message if failed; `null` on success.
* `exists` (bool): `true` if data exists, `false` otherwise.

**Example**:

```php
$result = BotDB::get('messages');
// Returns: ['ok' => true, 'data' => [['user_id' => 123, 'text' => 'Hello'], ...], 'error' => null, 'exists' => true]

$result = BotDB::get('users.settings.theme');
// Returns: ['ok' => true, 'data' => ['dark', ...], 'error' => null, 'exists' => true]
```

#### BotDB::append($path, $value)

**Description**: Appends a new row to the SQLite table corresponding to the path’s first part, storing the provided value as JSON in the `data` column.

**Parameters**:

* `path` (string): The data path (e.g., `messages`, `users`). Only the first part is used as the table name.
* `value` (mixed): The data to store (must be JSON-serializable, e.g., array, scalar).

**Returns**: `array`

* `ok` (bool): `true` if successful, `false` if failed.
* `data` (array): On success, `['path' => $path, 'value' => $value]`; empty array on failure.
* `error` (string|null): Error message if failed; `null` on success.

**Example**:

```php
BotDB::append('messages', ['user_id' => 123, 'text' => 'Hello']);
// Returns: ['ok' => true, 'data' => ['path' => 'messages', 'value' => ['user_id' => 123, 'text' => 'Hello']], 'error' => null]
```

#### BotDB::clear($path = null)

**Description**: Deletes data from a specific table (if `path` is provided) or all tables (if `path` is `null`) in the bot’s SQLite database.

**Parameters**:

* `path` (string|null): The data path (e.g., `messages`). Only the first part is used as the table name. If `null`, all tables are dropped.

**Returns**: `array`

* `ok` (bool): `true` if successful, `false` if failed.
* `data` (array): Always empty.
* `error` (string|null): Error message if failed; `null` on success.

**Example**:

```php
BotDB::clear('messages');
// Returns: ['ok' => true, 'data' => [], 'error' => null]

BotDB::clear();
// Returns: ['ok' => true, 'data' => [], 'error' => null]
```

#### BotDB::query($sql, $params = \[])

**Description**: Executes a custom SQL query on the bot’s SQLite database, supporting `SELECT`, `INSERT`, `UPDATE`, `DELETE`, etc. Queries are restricted to tables created by the bot, and parameters are bound to prevent SQL injection.

**Parameters**:

* `sql` (string): The SQL query (e.g., `SELECT * FROM messages WHERE json_extract(data, '$.user_id') = :user_id`).
* `params` (array): Optional key-value pairs for prepared statement parameters (e.g., `[':user_id' => 123]`).

**Returns**: `array`

* `ok` (bool): `true` if successful, `false` if failed.
* `data` (array): For `SELECT`, an array of rows; for other queries, `['affected_rows' => int]`; empty on error.
* `error` (string|null): Error message if failed; `null` on success.

**Example**:

```php
$result = BotDB::query('SELECT * FROM messages WHERE json_extract(data, "$.user_id") = :user_id', [':user_id' => 123]);
// Returns: ['ok' => true, 'data' => [['id' => 1, 'data' => '{"user_id": 123, "text": "Hello"}', 'created_at' => '...'], ...], 'error' => null]

$result = BotDB::query('DELETE FROM messages WHERE json_extract(data, "$.user_id") = :user_id', [':user_id' => 123]);
// Returns: ['ok' => true, 'data' => ['affected_rows' => 2], 'error' => null]
```

### Notes

* **Database Structure**: Each table has columns: `id` (auto-incrementing integer), `data` (JSON text), `created_at` (timestamp). Tables are created automatically for each unique path’s first part (e.g., `messages` for `messages.user_id`).
* **Path Handling**: Paths like `users.settings.theme` use the first part (`users`) as the table name and the rest (`settings.theme`) as a JSON sub-path in the `data` column.
* **JSON Queries**: Use `json_extract(data, '$.field')` in SQL queries to access JSON fields (e.g., `$.user_id`, `$.settings.theme`).
* **Security**: Table names are sanitized, and `query` restricts access to bot-created tables. Use parameterized queries for safety.
* **Initialization**: Call `BotDB::initialize($botId)` before using other methods.