PluginDataSql Model

class smarter.apps.plugin.models.PluginDataSql(*args, **kwargs)[source]

Bases: PluginDataBase

Stores SQL-based data configuration for a Smarter plugin.

This model is used for plugins that return data by executing SQL queries. It defines the SQL connection, query, parameters, test values, and result limits. The model provides methods for validating parameter and test value structures, preparing SQL queries with parameters, and executing queries.

PluginDataSql is a concrete subclass of PluginDataBase and is referenced by PluginMeta to provide the data payload for SQL-type plugins. It is tightly integrated with SqlConnection for managing database connectivity and query execution, and supports advanced features such as parameterized queries, dynamic placeholder validation, and result limiting.

This model is responsible for:

Storing the SQL query template and associated parameter schema.
Validating that all placeholders in the SQL query are defined in the parameters.
Ensuring that test values are provided and conform to the expected structure.
Preparing and executing SQL queries with runtime parameters, including safe substitution of placeholders.
Enforcing result limits to prevent excessive data retrieval.
Providing methods for returning sanitized query results for use in LLM responses.

Typical use cases include plugins that need to retrieve or analyze data from organizational databases, support dynamic user queries, or expose structured data to the Smarter LLM platform.

See also:

Parameters:

id (BigAutoField) – Primary key: ID
created_at (DateTimeField) – Created at
updated_at (DateTimeField) – Updated at
description (TextField) – Description. A brief description of what this plugin returns. Be verbose, but not too verbose.
parameters (JSONField) –
Parameters. A JSON dict containing parameter names and data types. Example: {‘required’: [], ‘properties’: {‘max_cost’: {‘type’: ‘float’, ‘description’: ‘the maximum cost that a student is willing to pay for a course.’}, ‘description’: {‘enum’: [‘AI’, ‘mobile’, ‘web’, ‘database’, ‘network’, ‘neural networks’], ‘type’: ‘string’, ‘description’: ‘areas of specialization for courses in the catalogue.’}}}

A JSON dict containing parameter names and data types. Example: {‘required’: [], ‘properties’: {‘max_cost’: {‘type’: ‘float’, ‘description’: ‘the maximum cost that a student is willing to pay for a course.’}, ‘description’: {‘enum’: [‘AI’, ‘mobile’, ‘web’, ‘database’, ‘network’, ‘neural networks’], ‘type’: ‘string’, ‘description’: ‘areas of specialization for courses in the catalogue.’}}}
test_values (JSONField) –
Test values. A JSON dict containing test values for each parameter. Example: {‘city’: ‘San Francisco’}

A JSON dict containing test values for each parameter. Example: {‘city’: ‘San Francisco’}
sql_query (TextField) – Sql query. The SQL query that this plugin will execute when invoked by the user prompt.
limit (IntegerField) – Limit. The maximum number of rows to return from the query.

Relationship fields:

Parameters:

plugin (OneToOneField to PluginMeta) – Plugin (related name: plugin_data_base_plugin)
plugindatabase_ptr (OneToOneField to PluginDataBase) – Primary key: Plugindatabase ptr (related name: plugindatasql)
connection (ForeignKey to SqlConnection) – Connection (related name: plugin_data_sql_connection)

class DataTypes[source]

Bases: object

ARRAY = 'array'

BOOL = 'bool'

INT = 'integer'

NULL = 'null'

NUMBER = 'number'

OBJECT = 'object'

STR = 'string'

classmethod all()[source]

Return type:: list[str]

exception DoesNotExist: Bases: DoesNotExist

exception MultipleObjectsReturned: Bases: MultipleObjectsReturned

are_test_values_pydantic()[source]

Return type:: bool

connection

ForeignKey to SqlConnection

Connection (related name: plugin_data_sql_connection)

Type:: Type

connection_id: Internal field, use connection instead.

data(params=None)[source]

Returns a dict of custom data return results.

Return type:: dict

execute_query(params)[source]

Execute the SQL query and return the results.

Return type:: Union[str, bool]

classmethod get_cached_data_by_plugin(plugin, invalidate=False)[source]

Return a single instance of PluginDataSql by plugin.

This method caches the results to improve performance.

Parameters:: plugin (PluginMeta) – The plugin whose data should be retrieved.
Returns:: A PluginDataSql instance if found, otherwise None.
Return type:: Union[PluginDataSql, None]

classmethod get_cached_object(invalidate=False, pk=None, plugin=None)[source]

Retrieve a model instance by primary key, using caching to optimize performance. This method is selectively overridden in models that inherit from MetaDataModel to provide class-specific function parameters.

Example usage:

# Retrieve by primary key
instance = MyModel.get_cached_object(pk=1)

Parameters:

invalidate (bool) – If True, invalidate the cache for this query before retrieving the object.
pk (int) – The primary key of the model instance to retrieve.
plugin (PluginMeta) – The PluginMeta instance associated with the data to retrieve.

Returns:

The model instance if found, otherwise None.

Return type:

Optional[“PluginDataBase”]

limit

IntegerField

Limit. The maximum number of rows to return from the query.

Type:: Type

plugindatabase_ptr

OneToOneField to PluginDataBase

Primary key: Plugindatabase ptr (related name: plugindatasql)

Type:: Type

plugindatabase_ptr_id: Internal field, use plugindatabase_ptr instead.

prepare_sql(params)[source]

Prepare the SQL query by replacing placeholders with values.

Return type:: str

sanitized_return_data(params=None)[source]

Return a dict by executing the query with the provided params.

Return type:: Union[str, bool]

save(*args, **kwargs)[source]: Override the save method to validate the field dicts.

sql_query

TextField

Sql query. The SQL query that this plugin will execute when invoked by the user prompt.

Type:: Type

test()[source]

Test the SQL query using the test_values in the record.

Return type:: Union[str, bool]

validate()[source]

Validate the model.

Attention

Intended to be overridden in subclasses to provide custom validation logic.

Return type:: bool

validate_all_placeholders_in_parameters()[source]

Validates that every placeholder found in the SQL query string is defined as a parameter.

This method scans the sql_query attribute for placeholders in the format {parameter_name}. It then checks that each placeholder corresponds to a key in the parameters['properties'] dictionary. If any placeholder is not defined in the parameters, a SmarterValueError is raised.

Example:

plugin = {
    'plugin': <PluginMeta: sql_test>,
    'description': 'test plugin',
    'sql_query': "SELECT * FROM auth_user WHERE username = '{username}';",
    'parameters': {
        'type': 'object',
        'properties': {
            'username': {
                'type': 'string',
                'description': 'The username of the user.'
            }
        },
        'required': ['username'],
        'additionalProperties': False
    },
    'test_values': 'admin',
    'limit': 1,
    'connection': <SqlConnection: test_sql_connection - django.db.backends.mysql://smarter:******@smarter-mysql:3306/smarter>
}

Raises:: SmarterValueError – If a placeholder in the SQL query is not defined in the parameters.
Return type:: None

validate_test_values()[source]

Validates the structure of the test_values attribute to ensure it matches the expected JSON representation.

Each item in test_values must be a dictionary with the keys name and value. This method attempts to instantiate each item as a Pydantic TestValue model to verify the structure.

Example of a valid test_values list:

[
    {"name": "username", "value": "admin"},
    {"name": "unit", "value": "Celsius"}
]

Raises:: SmarterValueError – If any item in test_values does not conform to the required structure.
Return type:: None