Initial plugin design

[simonw]

This is an early prototype of a tools plugin that can query SQLite databases.

For the moment It will just provide a method to list available databases (using all *.db in the current working directory), one to see the schema and one to execute a read-only SQL query.

[simonw]

Got it working:

llm \
  -T sqlite_query -T sqlite_list_databases -T sqlite_schema \
  "Count rows in the most interesting looking table" --td

Tool call: sqlite_list_databases({})
  ["content"]
Tool call: sqlite_schema({'database_name': 'content'})
  CREATE TABLE [uses] (
   [_id] TEXT PRIMARY KEY,
   [_path] TEXT,
   [text] TEXT,
   [html] TEXT,
   [title] TEXT,
   [summary] TEXT,
   [order] INTEGER
);
CREATE TABLE [datasette_repos] (
   [id] TEXT PRIMARY KEY,
   [nameWithOwner] TEXT,
   [createdAt] TEXT,
   [openGraphImageUrl] TEXT,
   [usesCustomOpenGraphImage] INTEGER,
   [openIssueCount] INTEGER,
   [closedIssueCount] INTEGER,
   [releaseCount] INTEGER,
   [topics] TEXT,
   [latest_commit] TEXT
);
CREATE TABLE [users] (
   [login] TEXT,
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [avatar_url] TEXT,
   [gravatar_id] TEXT,
   [html_url] TEXT,
   [type] TEXT,
   [user_view_type] TEXT,
   [site_admin] INTEGER,
   [name] TEXT
);
CREATE TABLE [licenses] (
   [key] TEXT PRIMARY KEY,
   [name] TEXT,
   [spdx_id] TEXT,
   [url] TEXT,
   [node_id] TEXT
);
CREATE TABLE [repos] (
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [name] TEXT,
   [full_name] TEXT,
   [private] INTEGER,
   [owner] INTEGER REFERENCES [users]([id]),
   [html_url] TEXT,
   [description] TEXT,
   [fork] INTEGER,
   [created_at] TEXT,
   [updated_at] TEXT,
   [pushed_at] TEXT,
   [homepage] TEXT,
   [size] INTEGER,
   [stargazers_count] INTEGER,
   [watchers_count] INTEGER,
   [language] TEXT,
   [has_issues] INTEGER,
   [has_projects] INTEGER,
   [has_downloads] INTEGER,
   [has_wiki] INTEGER,
   [has_pages] INTEGER,
   [has_discussions] INTEGER,
   [forks_count] INTEGER,
   [archived] INTEGER,
   [disabled] INTEGER,
   [open_issues_count] INTEGER,
   [license] TEXT,
   [allow_forking] INTEGER,
   [is_template] INTEGER,
   [web_commit_signoff_required] INTEGER,
   [topics] TEXT,
   [visibility] TEXT,
   [forks] INTEGER,
   [open_issues] INTEGER,
   [watchers] INTEGER,
   [default_branch] TEXT,
   [permissions] TEXT,
   [template_repository] TEXT,
   [network_count] INTEGER,
   [subscribers_count] INTEGER,
   [organization] INTEGER REFERENCES [users]([id])
, [custom_properties] TEXT, [temp_clone_token] TEXT, [readme] TEXT, [readme_html] TEXT,
   FOREIGN KEY([license]) REFERENCES [licenses]([key])
);
CREATE TABLE [releases] (
   [html_url] TEXT,
   [id] INTEGER PRIMARY KEY,
   [author] INTEGER REFERENCES [users]([id]),
   [node_id] TEXT,
   [tag_name] TEXT,
   [target_commitish] TEXT,
   [name] TEXT,
   [draft] INTEGER,
   [prerelease] INTEGER,
   [created_at] TEXT,
   [published_at] TEXT,
   [body] TEXT,
   [repo] INTEGER REFERENCES [repos]([id])
, [reactions] TEXT, [mentions_count] INTEGER);
CREATE TABLE [assets] (
   [url] TEXT,
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [name] TEXT,
   [label] TEXT,
   [uploader] INTEGER REFERENCES [users]([id]),
   [content_type] TEXT,
   [state] TEXT,
   [size] INTEGER,
   [download_count] INTEGER,
   [created_at] TEXT,
   [updated_at] TEXT,
   [browser_download_url] TEXT,
   [release] INTEGER REFERENCES [releases]([id])
);
CREATE INDEX [idx_repos_license]
    ON [repos] ([license]);
CREATE INDEX [idx_repos_organization]
    ON [repos] ([organization]);
CREATE INDEX [idx_repos_owner]
    ON [repos] ([owner]);
CREATE INDEX [idx_releases_repo]
    ON [releases] ([repo]);
CREATE INDEX [idx_releases_author]
    ON [releases] ([author]);
CREATE INDEX [idx_assets_release]
    ON [assets] ([release]);
CREATE INDEX [idx_assets_uploader]
    ON [assets] ([uploader]);
CREATE VIRTUAL TABLE [licenses_fts] USING FTS5 (
    [name],
    content=[licenses]
);
CREATE TABLE 'licenses_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'licenses_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'licenses_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'licenses_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TRIGGER [licenses_ai] AFTER INSERT ON [licenses] BEGIN
  INSERT INTO [licenses_fts] (rowid, [name]) VALUES (new.rowid, new.[name]);
END;
CREATE TRIGGER [licenses_ad] AFTER DELETE ON [licenses] BEGIN
  INSERT INTO [licenses_fts] ([licenses_fts], rowid, [name]) VALUES('delete', old.rowid, old.[name]);
END;
CREATE TRIGGER [licenses_au] AFTER UPDATE ON [licenses] BEGIN
  INSERT INTO [licenses_fts] ([licenses_fts], rowid, [name]) VALUES('delete', old.rowid, old.[name]);
  INSERT INTO [licenses_fts] (rowid, [name]) VALUES (new.rowid, new.[name]);
END;
CREATE VIRTUAL TABLE [releases_fts] USING FTS5 (
    [name], [body],
    content=[releases]
);
CREATE TABLE 'releases_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'releases_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'releases_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'releases_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TRIGGER [releases_ai] AFTER INSERT ON [releases] BEGIN
  INSERT INTO [releases_fts] (rowid, [name], [body]) VALUES (new.rowid, new.[name], new.[body]);
END;
CREATE TRIGGER [releases_ad] AFTER DELETE ON [releases] BEGIN
  INSERT INTO [releases_fts] ([releases_fts], rowid, [name], [body]) VALUES('delete', old.rowid, old.[name], old.[body]);
END;
CREATE TRIGGER [releases_au] AFTER UPDATE ON [releases] BEGIN
  INSERT INTO [releases_fts] ([releases_fts], rowid, [name], [body]) VALUES('delete', old.rowid, old.[name], old.[body]);
  INSERT INTO [releases_fts] (rowid, [name], [body]) VALUES (new.rowid, new.[name], new.[body]);
END;
CREATE VIRTUAL TABLE [repos_fts] USING FTS5 (
    [name], [description],
    content=[repos]
);
CREATE TABLE 'repos_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'repos_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'repos_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'repos_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TRIGGER [repos_ai] AFTER INSERT ON [repos] BEGIN
  INSERT INTO [repos_fts] (rowid, [name], [description]) VALUES (new.rowid, new.[name], new.[description]);
END;
CREATE TRIGGER [repos_ad] AFTER DELETE ON [repos] BEGIN
  INSERT INTO [repos_fts] ([repos_fts], rowid, [name], [description]) VALUES('delete', old.rowid, old.[name], old.[description]);
END;
CREATE TRIGGER [repos_au] AFTER UPDATE ON [repos] BEGIN
  INSERT INTO [repos_fts] ([repos_fts], rowid, [name], [description]) VALUES('delete', old.rowid, old.[name], old.[description]);
  INSERT INTO [repos_fts] (rowid, [name], [description]) VALUES (new.rowid, new.[name], new.[description]);
END;
CREATE VIRTUAL TABLE [users_fts] USING FTS5 (
    [login], [name],
    content=[users]
);
CREATE TABLE 'users_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'users_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'users_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'users_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TRIGGER [users_ai] AFTER INSERT ON [users] BEGIN
  INSERT INTO [users_fts] (rowid, [login], [name]) VALUES (new.rowid, new.[login], new.[name]);
END;
CREATE TRIGGER [users_ad] AFTER DELETE ON [users] BEGIN
  INSERT INTO [users_fts] ([users_fts], rowid, [login], [name]) VALUES('delete', old.rowid, old.[login], old.[name]);
END;
CREATE TRIGGER [users_au] AFTER UPDATE ON [users] BEGIN
  INSERT INTO [users_fts] ([users_fts], rowid, [login], [name]) VALUES('delete', old.rowid, old.[login], old.[name]);
  INSERT INTO [users_fts] (rowid, [login], [name]) VALUES (new.rowid, new.[login], new.[name]);
END;
CREATE VIEW recent_releases AS select
  repos.rowid as rowid,
  repos.html_url as repo,
  releases.html_url as release,
  substr(releases.published_at, 0, 11) as date,
  releases.body as body_markdown,
  releases.published_at,
  coalesce(repos.topics, '[]') as topics
from
  releases
  join repos on repos.id = releases.repo
order by
  releases.published_at desc;
CREATE TABLE [pypi_packages] (
   [name] TEXT PRIMARY KEY,
   [summary] TEXT,
   [classifiers] TEXT,
   [description] TEXT,
   [author] TEXT,
   [author_email] TEXT,
   [description_content_type] TEXT,
   [dynamic] TEXT,
   [home_page] TEXT,
   [keywords] TEXT,
   [license] TEXT,
   [license_expression] TEXT,
   [license_files] TEXT,
   [maintainer] TEXT,
   [maintainer_email] TEXT,
   [package_url] TEXT,
   [platform] TEXT,
   [project_url] TEXT,
   [project_urls] TEXT,
   [provides_extra] TEXT,
   [release_url] TEXT,
   [requires_dist] TEXT,
   [requires_python] TEXT,
   [version] TEXT,
   [yanked] INTEGER,
   [yanked_reason] TEXT
);
CREATE TABLE [pypi_versions] (
   [id] TEXT PRIMARY KEY,
   [package] TEXT REFERENCES [pypi_packages]([name]),
   [name] TEXT
);
CREATE TABLE [pypi_releases] (
   [md5_digest] TEXT PRIMARY KEY,
   [package] TEXT REFERENCES [pypi_packages]([name]),
   [version] TEXT REFERENCES [pypi_versions]([id]),
   [packagetype] TEXT,
   [filename] TEXT,
   [comment_text] TEXT,
   [digests] TEXT,
   [has_sig] INTEGER,
   [python_version] TEXT,
   [requires_python] TEXT,
   [size] INTEGER,
   [upload_time] TEXT,
   [upload_time_iso_8601] TEXT,
   [url] TEXT,
   [yanked] INTEGER,
   [yanked_reason] TEXT
);
CREATE TABLE [stats] (
   [package] TEXT,
   [date] TEXT,
   [downloads] INTEGER,
   PRIMARY KEY ([package], [date])
);
CREATE TABLE [tutorials] (
   [path] TEXT PRIMARY KEY,
   [title] TEXT,
   [body] TEXT
);
CREATE TABLE [news] (
   [date] TEXT,
   [body] TEXT
);
CREATE TABLE [example_csvs] (
   [url] TEXT,
   [name] TEXT,
   [table_name] TEXT,
   [source] TEXT,
   [source_url] TEXT,
   [about] TEXT,
   [about_url] TEXT,
   [description] TEXT
);
CREATE TABLE [plugin_repos] (
   [repo] TEXT,
   [tags] TEXT,
   [extra_search] TEXT
);
CREATE TABLE [tool_repos] (
   [repo] TEXT,
   [tags] TEXT,
   [extra_search] TEXT
);
CREATE INDEX [idx_pypi_versions_package]
    ON [pypi_versions] ([package]);
CREATE INDEX [idx_pypi_releases_version]
    ON [pypi_releases] ([version]);
CREATE INDEX [idx_pypi_releases_package]
    ON [pypi_releases] ([package]);
CREATE VIEW plugins AS select
  repos.name as name,
  repos.full_name as full_name,
  users.login as owner,
  repos.description as description,
  plugin_repos.extra_search as extra_search,
  plugin_repos.tags as tags,
  repos.stargazers_count,
  pypi_versions.name as tag_name,
  max(pypi_releases.upload_time) as latest_release_at,
  repos.created_at as created_at,
  datasette_repos.openGraphImageUrl,
  datasette_repos.usesCustomOpenGraphImage,
  (
    select
      sum(downloads)
    from
      stats
    where
      stats.package = repos.name
      and stats.date > date('now', '-7 days')
  ) as downloads_this_week,
  (
    select
      count(*)
    from
      plugin_repos
    where
      repo = repos.full_name
  ) as is_plugin,
  (
    select
      count(*)
    from
      tool_repos
    where
      repo = repos.full_name
  ) as is_tool
from
  datasette_repos
  join repos on datasette_repos.id = repos.node_id
  left join pypi_releases on (
    pypi_releases.package = repos.name or pypi_releases.package = 'datasette-' || repos.name
  )
  left join pypi_versions on pypi_releases.version = pypi_versions.id
  join users on users.id = repos.owner
  join plugin_repos on plugin_repos.repo = datasette_repos.nameWithOwner
group by
  repos.id
order by
  latest_release_at desc;
CREATE VIEW tools AS select
  repos.name as name,
  repos.full_name as full_name,
  users.login as owner,
  repos.description as description,
  tool_repos.extra_search as extra_search,
  tool_repos.tags as tags,
  repos.stargazers_count,
  pypi_versions.name as tag_name,
  max(pypi_releases.upload_time) as latest_release_at,
  repos.created_at as created_at,
  datasette_repos.openGraphImageUrl,
  datasette_repos.usesCustomOpenGraphImage,
  (
    select
      sum(downloads)
    from
      stats
    where
      stats.package = repos.name
      and stats.date > date('now', '-7 days')
  ) as downloads_this_week,
  (
    select
      count(*)
    from
      plugin_repos
    where
      repo = repos.full_name
  ) as is_plugin,
  (
    select
      count(*)
    from
      tool_repos
    where
      repo = repos.full_name
  ) as is_tool
from
  datasette_repos
  join repos on datasette_repos.id = repos.node_id
  left join pypi_releases on (
    pypi_releases.package = repos.name or pypi_releases.package = 'datasette-' || repos.name
  )
  left join pypi_versions on pypi_releases.version = pypi_versions.id
  join users on users.id = repos.owner
  join tool_repos on tool_repos.repo = datasette_repos.nameWithOwner
group by
  repos.id
order by
  latest_release_at desc;
Tool call: sqlite_query({'database_name': 'content', 'sql': 'SELECT COUNT(*) AS row_count FROM repos;'})
  [{"row_count": 199}]
Tool call: sqlite_query({'database_name': 'content', 'sql': 'SELECT COUNT(*) AS row_count FROM users;'})
  [{"row_count": 13}]
Tool call: sqlite_query({'database_name': 'content', 'sql': 'SELECT COUNT(*) AS row_count FROM releases;'})
  [{"row_count": 1734}]
Tool call: sqlite_query({'database_name': 'content', 'sql': 'SELECT COUNT(*) AS row_count FROM pypi_packages;'})
  [{"row_count": 208}]
Tool call: sqlite_query({'database_name': 'content', 'sql': 'SELECT COUNT(*) AS row_count FROM assets;'})
  [{"row_count": 1604}]
The most interesting tables based on row count are:
- releases with 1,734 rows,
- assets with 1,604 rows,
- repos with 199 rows,
- pypi_packages with 208 rows,
- users with 13 rows.

The releases table has the highest number of rows (1,734). Would you like me to count 
rows in another table or provide more details on a specific table?

[simonw]

This plugin demonstrates the need for several features:

• The ability to configure tools. Having to change directory to wherever the *.db files is first is weird.
• Bundling tools. It's annoying to have to specify all three individually.
• Being able to llm -c without passing the three tools again would be nice.
• llm chat -T ... would be great too.

Also maybe this should be able to relax the default chain limit a bit? Ideally I'd be able to have a much longer chain limit BUT spot if the LLM keeps trying to call the same tool more than once and cut that off or treat that as a higher rate limit violation.

[simonw]

The lack of a configuration mechanism also makes it hard to figure out how to use this from Python, where a pattern of changing the working directory to where the database files live feels VERY wrong.