1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
.. _plugins:
=========
Plugins
=========
``sqlite-utils`` supports plugins, which can be used to add extra features to the software.
Plugins can add new commands, for example ``sqlite-utils some-command ...``
Plugins can be installed using the ``sqlite-utils install`` command:
.. code-block:: bash
sqlite-utils install sqlite-utils-name-of-plugin
You can see a JSON list of plugins that have been installed by running this:
.. code-block:: bash
sqlite-utils plugins
Plugin hooks such as :ref:`plugins_hooks_prepare_connection` affect each instance of the ``Database`` class. You can opt-out of these plugins by creating that class instance like so:
.. code-block:: python
db = Database(memory=True, execute_plugins=False)
.. _plugins_building:
Building a plugin
-----------------
Plugins are created in a directory named after the plugin. To create a "hello world" plugin, first create a ``hello-world`` directory:
.. code-block:: bash
mkdir hello-world
cd hello-world
In that folder create two files. The first is a ``pyproject.toml`` file describing the plugin:
.. code-block:: toml
[project]
name = "sqlite-utils-hello-world"
version = "0.1"
[project.entry-points.sqlite_utils]
hello_world = "sqlite_utils_hello_world"
The ``[project.entry-points.sqlite_utils]`` section tells ``sqlite-utils`` which module to load when executing the plugin.
Then create ``sqlite_utils_hello_world.py`` with the following content:
.. code-block:: python
import click
import sqlite_utils
@sqlite_utils.hookimpl
def register_commands(cli):
@cli.command()
def hello_world():
"Say hello world"
click.echo("Hello world!")
Install the plugin in "editable" mode - so you can make changes to the code and have them picked up instantly by ``sqlite-utils`` - like this:
.. code-block:: bash
sqlite-utils install -e .
Or pass the path to your plugin directory:
.. code-block:: bash
sqlite-utils install -e /dev/sqlite-utils-hello-world
Now, running this should execute your new command:
.. code-block:: bash
sqlite-utils hello-world
Your command will also be listed in the output of ``sqlite-utils --help``.
See the `LLM plugin documentation <https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#distributing-your-plugin>`__ for tips on distributing your plugin.
.. _plugins_hooks:
Plugin hooks
------------
Plugin hooks allow ``sqlite-utils`` to be customized.
.. _plugins_hooks_register_commands:
register_commands(cli)
~~~~~~~~~~~~~~~~~~~~~~
This hook can be used to register additional commands with the ``sqlite-utils`` CLI. It is called with the ``cli`` object, which is a ``click.Group`` instance.
Example implementation:
.. code-block:: python
import click
import sqlite_utils
@sqlite_utils.hookimpl
def register_commands(cli):
@cli.command()
def hello_world():
"Say hello world"
click.echo("Hello world!")
New commands implemented by plugins can invoke existing commands using the `context.invoke <https://click.palletsprojects.com/en/stable/api/#click.Context.invoke>`__ mechanism.
As a special niche feature, if your plugin needs to import some files and then act against an in-memory database containing those files you can forward to the :ref:`sqlite-utils memory command <cli_memory>` and pass it ``return_db=True``:
.. code-block:: python
@cli.command()
@click.pass_context
@click.argument(
"paths",
type=click.Path(file_okay=True, dir_okay=False, allow_dash=True),
required=False,
nargs=-1,
)
def show_schema_for_files(ctx, paths):
from sqlite_utils.cli import memory
db = ctx.invoke(memory, paths=paths, return_db=True)
# Now do something with that database
click.echo(db.schema)
.. _plugins_hooks_prepare_connection:
prepare_connection(conn)
~~~~~~~~~~~~~~~~~~~~~~~~
This hook is called when a new SQLite database connection is created. You can
use it to `register custom SQL functions <https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function>`_,
aggregates and collations. For example:
.. code-block:: python
import sqlite_utils
@sqlite_utils.hookimpl
def prepare_connection(conn):
conn.create_function(
"hello", 1, lambda name: f"Hello, {name}!"
)
This registers a SQL function called ``hello`` which takes a single
argument and can be called like this:
.. code-block:: sql
select hello("world"); -- "Hello, world!"
|
