BotDB Library Functions

Discover the BotDB library for Bots Mother This comprehensive guide details all methods, including CRUD operations, querying, and batch processing, with examples for seamless Telegram bot in

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:

$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:

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:

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:

$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.