database-operation

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Query (数据库查询)

数据库查询 (Database Query)

This skill guides you through executing SQL against the MoviePilot database. Both read and write operations are supported.

本Skill将引导你对MoviePilot数据库执行SQL操作，支持读写两种操作。

Prerequisites

前提条件

You need the following tools:

```
execute_command
```
- Execute shell commands to run database queries

你需要以下工具：

```
execute_command
```
- 执行Shell命令以运行数据库查询

Getting Database Connection Info

获取数据库连接信息

The system prompt

<system_info>

section already contains all the database connection details you need:

数据库类型 —
```
sqlite
```
or
```
postgresql
```
数据库 — Full connection info:
- For SQLite: the database file path, e.g.
```
SQLite (/config/db/moviepilot.db)
```
- For PostgreSQL: the connection string, e.g.
```
PostgreSQL (user:password@host:port/database)
```

Do NOT run any detection commands. Extract the database type and connection details directly from

<system_info>

系统提示<system_info>部分已包含你所需的所有数据库连接详情：

数据库类型 —
```
sqlite
```
或
```
postgresql
```
数据库 — 完整连接信息：
- 对于SQLite：数据库文件路径，例如
```
SQLite (/config/db/moviepilot.db)
```
- 对于PostgreSQL：连接字符串，例如
```
PostgreSQL (user:password@host:port/database)
```

请勿运行任何检测命令，直接从<system_info>中提取数据库类型和连接详情。

Executing Queries

执行查询

SQLite Mode

SQLite模式

Extract the database file path from

<system_info>

(the path inside the parentheses after

SQLite

Use

execute_command

to run queries:

bash

sqlite3 -header -column <DB_PATH> "YOUR SQL QUERY HERE;"

For JSON-formatted output (easier to parse):

bash

sqlite3 -json <DB_PATH> "YOUR SQL QUERY HERE;"

List all tables:

bash

sqlite3 -header -column <DB_PATH> "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

View table schema:

bash

sqlite3 <DB_PATH> ".schema tablename"

从<system_info>中提取数据库文件路径（

SQLite

后括号内的路径）。

使用

execute_command

运行查询：

bash

sqlite3 -header -column <DB_PATH> "YOUR SQL QUERY HERE;"

如需JSON格式输出（更易解析）：

bash

sqlite3 -json <DB_PATH> "YOUR SQL QUERY HERE;"

列出所有表：

bash

sqlite3 -header -column <DB_PATH> "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

查看表结构：

bash

sqlite3 <DB_PATH> ".schema tablename"

PostgreSQL Mode

PostgreSQL模式

Extract the connection parameters from

<system_info>

(parse

user:password@host:port/database

from the parentheses after

PostgreSQL

Use

execute_command

to run queries via

psql

bash

PGPASSWORD=<password> psql -h <host> -p <port> -U <user> -d <database> -c "YOUR SQL QUERY HERE;"

List all tables:

bash

PGPASSWORD=<password> psql -h <host> -p <port> -U <user> -d <database> -c "SELECT tablename FROM pg_tables WHERE schemaname='public' ORDER BY tablename;"

View table schema:

bash

PGPASSWORD=<password> psql -h <host> -p <port> -U <user> -d <database> -c "\d tablename"

从<system_info>中提取连接参数（解析

PostgreSQL

后括号内的

user:password@host:port/database

）。

使用

execute_command

通过

psql

运行查询：

bash

PGPASSWORD=<password> psql -h <host> -p <port> -U <user> -d <database> -c "YOUR SQL QUERY HERE;"

列出所有表：

bash

PGPASSWORD=<password> psql -h <host> -p <port> -U <user> -d <database> -c "SELECT tablename FROM pg_tables WHERE schemaname='public' ORDER BY tablename;"

查看表结构：

bash

PGPASSWORD=<password> psql -h <host> -p <port> -U <user> -d <database> -c "\d tablename"

Interpret Results

结果解读

After executing the query, analyze the results and present them in a clear, user-friendly format. Use aggregation, sorting, and filtering as needed.

执行查询后，分析结果并以清晰、友好的格式呈现给用户。按需使用聚合、排序和过滤操作。

Database Schema Reference

数据库结构参考

MoviePilot uses the following core tables:

MoviePilot使用以下核心表：

downloadhistory (下载历史)

downloadhistory（下载历史）

Key columns:

id

path

type

title

year

tmdbid

imdbid

doubanid

seasons

episodes

downloader

download_hash

torrent_name

torrent_site

userid

username

date

media_category

关键列：

id

path

type

title

year

tmdbid

imdbid

doubanid

seasons

episodes

downloader

download_hash

torrent_name

torrent_site

userid

username

date

media_category

downloadfiles (下载文件)

downloadfiles（下载文件）

Key columns:

id

downloader

download_hash

fullpath

savepath

filepath

torrentname

state

关键列：

id

downloader

download_hash

fullpath

savepath

filepath

torrentname

state

transferhistory (整理历史)

transferhistory（整理历史）

Key columns:

id

src

dest

mode

type

category

title

year

tmdbid

seasons

episodes

download_hash

status

(boolean: true=success, false=failed),

errmsg

date

关键列：

id

src

dest

mode

type

category

title

year

tmdbid

seasons

episodes

download_hash

status

（布尔值：true=成功，false=失败）,

errmsg

date

subscribe (订阅)

subscribe（订阅）

Key columns:

id

name

year

type

tmdbid

doubanid

season

total_episode

start_episode

lack_episode

state

('N'=new, 'R'=running, 'S'=paused),

filter

include

exclude

quality

resolution

sites

best_version

date

username

关键列：

id

name

year

type

tmdbid

doubanid

season

total_episode

start_episode

lack_episode

state

（'N'=新建，'R'=运行中，'S'=暂停）,

filter

include

exclude

quality

resolution

sites

best_version

date

username

subscribehistory (订阅历史)

subscribehistory（订阅历史）

Key columns:

id

name

year

type

tmdbid

doubanid

season

total_episode

start_episode

date

username

关键列：

id

name

year

type

tmdbid

doubanid

season

total_episode

start_episode

date

username

user (用户)

user（用户）

Key columns:

id

name

email

is_active

is_superuser

permissions

settings

关键列：

id

name

email

is_active

is_superuser

permissions

settings

site (站点)

site（站点）

Key columns:

id

name

domain

url

pri

(priority),

cookie

proxy

is_active

downloader

limit_interval

limit_count

关键列：

id

name

domain

url

pri

（优先级）,

cookie

proxy

is_active

downloader

limit_interval

limit_count

siteuserdata (站点用户数据)

siteuserdata（站点用户数据）

Key columns:

id

domain

name

username

user_level

bonus

upload

download

ratio

seeding

leeching

seeding_size

updated_day

关键列：

id

domain

name

username

user_level

bonus

upload

download

ratio

seeding

leeching

seeding_size

updated_day

sitestatistic (站点统计)

sitestatistic（站点统计）

Key columns:

id

domain

success

fail

seconds

lst_state

lst_mod_date

关键列：

id

domain

success

fail

seconds

lst_state

lst_mod_date

mediaserveritem (媒体库条目)

mediaserveritem（媒体库条目）

Key columns:

id

server

library

item_id

item_type

title

original_title

year

tmdbid

imdbid

tvdbid

path

关键列：

id

server

library

item_id

item_type

title

original_title

year

tmdbid

imdbid

tvdbid

path

systemconfig (系统配置)

systemconfig（系统配置）

Key columns:

id

key

value

(JSON)

关键列：

id

key

value

（JSON格式）

userconfig (用户配置)

userconfig（用户配置）

Key columns:

id

username

key

value

(JSON)

关键列：

id

username

key

value

（JSON格式）

plugindata (插件数据)

plugindata（插件数据）

Key columns:

id

plugin_id

key

value

(JSON)

关键列：

id

plugin_id

key

value

（JSON格式）

message (消息)

message（消息）

Key columns:

id

channel

source

mtype

title

text

image

link

userid

reg_time

关键列：

id

channel

source

mtype

title

text

image

link

userid

reg_time

workflow (工作流)

workflow（工作流）

Key columns:

id

name

description

timer

trigger_type

event_type

state

('W'=waiting, 'R'=running),

run_count

actions

flows

last_time

关键列：

id

name

description

timer

trigger_type

event_type

state

（'W'=等待，'R'=运行中）,

run_count

actions

flows

last_time

passkey (通行密钥)

passkey（通行密钥）

Key columns:

id

user_id

credential_id

public_key

name

created_at

last_used_at

is_active

关键列：

id

user_id

credential_id

public_key

name

created_at

last_used_at

is_active

siteicon (站点图标)

siteicon（站点图标）

Key columns:

id

name

domain

url

base64

关键列：

id

name

domain

url

base64

Common Query Examples

常见查询示例

Count total downloads

统计总下载量

sql

SELECT COUNT(*) AS total FROM downloadhistory;

sql

SELECT COUNT(*) AS total FROM downloadhistory;

Recent download history

Failed transfers

失败的整理记录

sql

SELECT id, title, src, errmsg, date FROM transferhistory WHERE status = 0 ORDER BY id DESC LIMIT 10;

sql

SELECT id, title, src, errmsg, date FROM transferhistory WHERE status = 0 ORDER BY id DESC LIMIT 10;

Active subscriptions

活跃订阅

sql

SELECT name, year, type, season, state, lack_episode FROM subscribe WHERE state = 'R';

sql

SELECT name, year, type, season, state, lack_episode FROM subscribe WHERE state = 'R';

Site upload/download statistics

站点上传/下载统计

sql

SELECT name, domain, upload, download, ratio, bonus, seeding, user_level FROM siteuserdata ORDER BY upload DESC;

sql

SELECT name, domain, upload, download, ratio, bonus, seeding, user_level FROM siteuserdata ORDER BY upload DESC;

Media library statistics

媒体库统计

sql

SELECT server, library, COUNT(*) AS count FROM mediaserveritem GROUP BY server, library;

sql

SELECT server, library, COUNT(*) AS count FROM mediaserveritem GROUP BY server, library;

Site access success rate

站点访问成功率

sql

SELECT domain, success, fail, ROUND(success * 100.0 / (success + fail), 1) AS success_rate FROM sitestatistic WHERE success + fail > 0 ORDER BY success_rate DESC;

sql

SELECT domain, success, fail, ROUND(success * 100.0 / (success + fail), 1) AS success_rate FROM sitestatistic WHERE success + fail > 0 ORDER BY success_rate DESC;

Plugin data inspection

插件数据查看

sql

SELECT plugin_id, key FROM plugindata ORDER BY plugin_id, key;

sql

SELECT plugin_id, key FROM plugindata ORDER BY plugin_id, key;

Delete old download history (write operation)

删除旧下载历史（写入操作）

sql

DELETE FROM downloadhistory WHERE date < '2024-01-01';

sql

DELETE FROM downloadhistory WHERE date < '2024-01-01';

Update subscription state (write operation)

更新订阅状态（写入操作）

sql

UPDATE subscribe SET state = 'S' WHERE id = 123;

sql

UPDATE subscribe SET state = 'S' WHERE id = 123;

Clean up failed transfer records (write operation)

清理失败的整理记录（写入操作）

sql

DELETE FROM transferhistory WHERE status = 0 AND date < '2024-06-01';

sql

DELETE FROM transferhistory WHERE status = 0 AND date < '2024-06-01';

Safety Rules

安全规则

Confirm before writing — For any
```
INSERT
```
,
```
UPDATE
```
,
```
DELETE
```
,
```
DROP
```
,
```
ALTER
```
, or
```
TRUNCATE
```
operation, always describe what the statement will do and ask the user to confirm before executing. For
```
SELECT
```
queries, execute directly without confirmation
Back up before destructive operations — Before executing
```
DELETE
```
,
```
DROP
```
, or
```
TRUNCATE
```
on important tables, suggest the user back up the data first (e.g., export with
```
.dump
```
for SQLite or
```
pg_dump
```
for PostgreSQL)
Use WHERE clauses — Never run
```
UPDATE
```
or
```
DELETE
```
without a
```
WHERE
```
clause unless the user explicitly intends to affect all rows
Use LIMIT for queries — When querying large tables with
```
SELECT
```
, add
```
LIMIT
```
to prevent excessive output
Sensitive data — The
```
site
```
table contains
```
cookie
```
,
```
apikey
```
, and
```
token
```
fields. NEVER display these values to the user. Exclude them from SELECT or replace with
```
'***'
```
Password data — The
```
user
```
table contains
```
hashed_password
```
and
```
otp_secret
```
fields. NEVER display these values
Output limits — If the query results are very long, summarize or truncate them

写入前确认 — 对于任何
```
INSERT
```
、
```
UPDATE
```
、
```
DELETE
```
、
```
DROP
```
、
```
ALTER
```
或
```
TRUNCATE
```
操作，务必先说明语句的作用，并在执行前请求用户确认。对于
```
SELECT
```
查询，可直接执行无需确认
破坏性操作前备份 — 在对重要表执行
```
DELETE
```
、
```
DROP
```
或
```
TRUNCATE
```
操作前，建议用户先备份数据（例如，SQLite使用
```
.dump
```
导出，PostgreSQL使用
```
pg_dump
```
导出）
使用WHERE子句 — 除非用户明确要影响所有行，否则切勿在没有
```
WHERE
```
子句的情况下运行
```
UPDATE
```
或
```
DELETE
```
查询使用LIMIT — 对大表执行
```
SELECT
```
查询时，添加
```
LIMIT
```
以避免输出过多内容
敏感数据 —
```
site
```
表包含
```
cookie
```
、
```
apikey
```
和
```
token
```
字段。绝对不要向用户展示这些值，在SELECT中排除它们或用
```
'***'
```
替换
密码数据 —
```
user
```
表包含
```
hashed_password
```
和
```
otp_secret
```
字段。绝对不要展示这些值
输出限制 — 如果查询结果过长，进行总结或截断

SQL Dialect Differences

SQL方言差异

When writing queries, be aware of differences between SQLite and PostgreSQL:

Feature	SQLite	PostgreSQL
Boolean values	`0` / `1`	`false` / `true`
String concat	`\|\|`	`\|\|` or `CONCAT()`
Current time	`datetime('now')`	`NOW()`
LIMIT syntax	`LIMIT n`	`LIMIT n`
JSON access	`json_extract(col, '$.key')`	`col->>'key'`
Case sensitivity	Case-insensitive by default	Case-sensitive
LIKE	Case-insensitive	Use `ILIKE` for case-insensitive

编写查询时，请注意SQLite和PostgreSQL之间的差异：

特性	SQLite	PostgreSQL
布尔值	`0` / `1`	`false` / `true`
字符串拼接	`
当前时间	`datetime('now')`	`NOW()`
LIMIT语法	`LIMIT n`	`LIMIT n`
JSON访问	`json_extract(col, '$.key')`	`col->>'key'`
大小写敏感性	默认不区分大小写	区分大小写
LIKE	不区分大小写	使用 `ILIKE` 实现不区分大小写

Troubleshooting

故障排除

sqlite3 not found: The
```
sqlite3
```
CLI should be pre-installed in the MoviePilot Docker container. If missing, you can try using Python:
```
python3 -c "import sqlite3; ..."
```
psql not found: For PostgreSQL, if
```
psql
```
is not available, use Python:
```
python3 -c "import psycopg2; ..."
```
Permission denied: Database queries require admin privileges
Table not found: Use the "list all tables" query first to verify table names

sqlite3未找到：
```
sqlite3
```
命令行工具应已预安装在MoviePilot Docker容器中。若缺失，可尝试使用Python：
```
python3 -c "import sqlite3; ..."
```
psql未找到：对于PostgreSQL，若
```
psql
```
不可用，使用Python：
```
python3 -c "import psycopg2; ..."
```
权限被拒绝：数据库查询需要管理员权限
表未找到：先使用“列出所有表”查询确认表名

database-operation

Original

Translation

Database Query (数据库查询)

数据库查询 (Database Query)

Prerequisites

前提条件

Getting Database Connection Info

获取数据库连接信息

Executing Queries

执行查询

SQLite Mode

SQLite模式

PostgreSQL Mode

PostgreSQL模式

Interpret Results

结果解读

Database Schema Reference

数据库结构参考

downloadhistory (下载历史)

downloadhistory（下载历史）

downloadfiles (下载文件)

downloadfiles（下载文件）

transferhistory (整理历史)

transferhistory（整理历史）

subscribe (订阅)

subscribe（订阅）

subscribehistory (订阅历史)

subscribehistory（订阅历史）

user (用户)

user（用户）

site (站点)

site（站点）

siteuserdata (站点用户数据)

siteuserdata（站点用户数据）

sitestatistic (站点统计)

sitestatistic（站点统计）

mediaserveritem (媒体库条目)

mediaserveritem（媒体库条目）

systemconfig (系统配置)

systemconfig（系统配置）

userconfig (用户配置)

userconfig（用户配置）

plugindata (插件数据)

plugindata（插件数据）

message (消息)

message（消息）

workflow (工作流)

workflow（工作流）

passkey (通行密钥)

passkey（通行密钥）

siteicon (站点图标)

siteicon（站点图标）

Common Query Examples

常见查询示例

Count total downloads

统计总下载量

Recent download history

最近下载历史

Failed transfers

失败的整理记录

Active subscriptions

活跃订阅

Site upload/download statistics

站点上传/下载统计

Media library statistics

媒体库统计

Site access success rate

站点访问成功率

Plugin data inspection

插件数据查看

Delete old download history (write operation)

删除旧下载历史（写入操作）

Update subscription state (write operation)

更新订阅状态（写入操作）

Clean up failed transfer records (write operation)

清理失败的整理记录（写入操作）

Safety Rules

安全规则

SQL Dialect Differences