157 lines
8.7 KiB
SQL
157 lines
8.7 KiB
SQL
CREATE TABLE if not exists "agent_admin_group_all"
|
||
(
|
||
id text primary key unique,
|
||
name text, -- '分组名称'
|
||
data blob, -- '配置文件数据'
|
||
created_at datetime, -- '创建时间'
|
||
updated_at datetime, -- '更新时间'
|
||
deleted_at datetime, -- '删除标志'
|
||
check (length('name') > 0)
|
||
);
|
||
|
||
CREATE TABLE if not exists "agent_admin_instance_all"
|
||
(
|
||
id text primary key unique,
|
||
gid integer references agent_admin_group_all (id),
|
||
ip text, -- '实例发送消息的 ip' // 需要索引
|
||
topic text, -- '实例通信 topic'
|
||
version text, -- '实例版本'
|
||
hostname text, -- '实例通信主机名' // 需要索引
|
||
state text, -- '实例状态' // 需要索引
|
||
switch text, -- '实例开启状态' // 需要索引
|
||
data blob, -- '配置文件数据'
|
||
cpu_agent int, -- 'Agent 占用的 CPU 资源'
|
||
cpu_total int, -- 'Machine 占用的 CPU 资源'
|
||
cpu_maximum int, -- 'Machine 拥有的总 CPU 资源'
|
||
mem_agent int, -- 'Agent 占用的 内存 量（bytes）'
|
||
mem_total int, -- 'Machine 占用的 内存 量（bytes）'
|
||
mem_maximum int, -- 'Machine 拥有的总 内存 量（bytes）'
|
||
bandwidth_agent_in int, -- 'Agent 占用的入向带宽'
|
||
bandwidth_total_in int, -- 'Machine 占用的入向带宽'
|
||
bandwidth_agent_out int, -- 'Agent 占用的出向带宽'
|
||
bandwidth_total_out int, -- 'Machine 占用的出向带宽'
|
||
latest_reported_at datetime, -- '实例最后上报时间'
|
||
created_at datetime, -- '创建时间'
|
||
updated_at datetime, -- '更新时间'
|
||
deleted_at datetime, -- '删除标志'
|
||
CHECK (state in ('running', 'stopped', 'upgrading', 'offline', 'upgrade_failed')),
|
||
CHECK (switch in ('on', 'off'))
|
||
);
|
||
CREATE INDEX if not exists index_ins_gid_refer on agent_admin_instance_all (gid);
|
||
CREATE INDEX if not exists index_ins_state_idx on agent_admin_instance_all (state);
|
||
CREATE INDEX if not exists index_ins_switch_idx on agent_admin_instance_all (switch);
|
||
CREATE INDEX if not exists index_ins_hostname_idx on agent_admin_instance_all (hostname);
|
||
CREATE INDEX if not exists index_ins_cpu_agent_idx on agent_admin_instance_all (cpu_agent);
|
||
CREATE INDEX if not exists index_ins_cpu_total_idx on agent_admin_instance_all (cpu_total);
|
||
CREATE INDEX if not exists index_ins_cpu_maximum_idx on agent_admin_instance_all (cpu_maximum);
|
||
CREATE INDEX if not exists index_ins_mem_agent_idx on agent_admin_instance_all (mem_agent);
|
||
CREATE INDEX if not exists index_ins_mem_total_idx on agent_admin_instance_all (mem_total);
|
||
CREATE INDEX if not exists index_ins_mem_maximum_idx on agent_admin_instance_all (mem_maximum);
|
||
CREATE INDEX if not exists index_ins_bandwidth_agent_in_idx on agent_admin_instance_all (bandwidth_agent_in);
|
||
CREATE INDEX if not exists index_ins_bandwidth_total_in_idx on agent_admin_instance_all (bandwidth_total_in);
|
||
CREATE INDEX if not exists index_ins_bandwidth_agent_out_idx on agent_admin_instance_all (bandwidth_agent_out);
|
||
CREATE INDEX if not exists index_ins_bandwidth_total_out_idx on agent_admin_instance_all (bandwidth_total_out);
|
||
|
||
CREATE TABLE if not exists "agent_admin_log_all"
|
||
(
|
||
id text, -- references agent_admin_instance_all (id)
|
||
created_at datetime, -- '日志记录时间 (时间戳)'
|
||
deleted_at datetime, -- '删除标志'
|
||
type text, -- '日志记录时刻，实例状态'
|
||
operator text, -- '变更操作者'
|
||
message text, -- '日志附带信息'
|
||
CHECK (type in
|
||
('action_up', 'action_down', 'action_config', 'action_upgrade', 'action_join', 'stopped', 'upgrading',
|
||
'upgrade_success', 'upgrade_failed', 'offline', 'running', 'register'))
|
||
);
|
||
|
||
-- insert default record
|
||
INSERT OR IGNORE INTO agent_admin_group_all (id, name, data, created_at, updated_at, deleted_at)
|
||
VALUES ('default', '默认分组', null, datetime('now'), datetime('now'), null);
|
||
CREATE TABLE if not exists "agent_admin_group_all"
|
||
(
|
||
id text primary key unique,
|
||
name text, -- '分组名称'
|
||
data blob, -- '配置文件数据'
|
||
created_at datetime, -- '创建时间'
|
||
updated_at datetime, -- '更新时间'
|
||
deleted_at datetime, -- '删除标志'
|
||
check (length('name') > 0)
|
||
);
|
||
|
||
CREATE TABLE if not exists "agent_admin_instance_all"
|
||
(
|
||
id text primary key unique,
|
||
gid integer references agent_admin_group_all (id),
|
||
ip text, -- '实例发送消息的 ip' // 需要索引
|
||
topic text, -- '实例通信 topic'
|
||
hostname text, -- '实例通信主机名' // 需要索引
|
||
state text, -- '实例状态' // 需要索引
|
||
switch text, -- '实例开启状态' // 需要索引
|
||
data blob, -- '配置文件数据'
|
||
cpu_agent int, -- 'Agent 占用的 CPU 资源'
|
||
cpu_total int, -- 'Machine 占用的 CPU 资源'
|
||
cpu_maximum int, -- 'Machine 拥有的总 CPU 资源'
|
||
mem_agent int, -- 'Agent 占用的 内存 量（bytes）'
|
||
mem_total int, -- 'Machine 占用的 内存 量（bytes）'
|
||
mem_maximum int, -- 'Machine 拥有的总 内存 量（bytes）'
|
||
bandwidth_agent_in int, -- 'Agent 占用的入向带宽'
|
||
bandwidth_total_in int, -- 'Machine 占用的入向带宽'
|
||
bandwidth_agent_out int, -- 'Agent 占用的出向带宽'
|
||
bandwidth_total_out int, -- 'Machine 占用的出向带宽'
|
||
created_at datetime, -- '创建时间'
|
||
latest_reported_at datetime, -- '最新指标更新时间'
|
||
updated_at datetime, -- '更新时间'
|
||
deleted_at datetime, -- '删除标志'
|
||
CHECK (state in ('running', 'stopped', 'upgrading', 'offline', 'upgrade_failed')),
|
||
CHECK (switch in ('on', 'off'))
|
||
);
|
||
CREATE INDEX if not exists index_ins_gid_refer on agent_admin_instance_all (gid);
|
||
CREATE INDEX if not exists index_ins_state_idx on agent_admin_instance_all (state);
|
||
CREATE INDEX if not exists index_ins_switch_idx on agent_admin_instance_all (switch);
|
||
CREATE INDEX if not exists index_ins_hostname_idx on agent_admin_instance_all (hostname);
|
||
CREATE INDEX if not exists index_ins_cpu_agent_idx on agent_admin_instance_all (cpu_agent);
|
||
CREATE INDEX if not exists index_ins_cpu_total_idx on agent_admin_instance_all (cpu_total);
|
||
CREATE INDEX if not exists index_ins_cpu_maximum_idx on agent_admin_instance_all (cpu_maximum);
|
||
CREATE INDEX if not exists index_ins_mem_agent_idx on agent_admin_instance_all (mem_agent);
|
||
CREATE INDEX if not exists index_ins_mem_total_idx on agent_admin_instance_all (mem_total);
|
||
CREATE INDEX if not exists index_ins_mem_maximum_idx on agent_admin_instance_all (mem_maximum);
|
||
CREATE INDEX if not exists index_ins_bandwidth_agent_in_idx on agent_admin_instance_all (bandwidth_agent_in);
|
||
CREATE INDEX if not exists index_ins_bandwidth_total_in_idx on agent_admin_instance_all (bandwidth_total_in);
|
||
CREATE INDEX if not exists index_ins_bandwidth_agent_out_idx on agent_admin_instance_all (bandwidth_agent_out);
|
||
CREATE INDEX if not exists index_ins_bandwidth_total_out_idx on agent_admin_instance_all (bandwidth_total_out);
|
||
|
||
CREATE TABLE if not exists "agent_admin_log_all"
|
||
(
|
||
id text, -- references agent_admin_instance_all (id)
|
||
created_at datetime, -- '日志记录时间 (时间戳)'
|
||
deleted_at datetime, -- '删除标志'
|
||
type text, -- '日志记录时刻，实例状态'
|
||
operator text, -- '变更操作者'
|
||
message text, -- '日志附带信息'
|
||
CHECK (type in
|
||
('action_up', 'action_down', 'action_config', 'action_upgrade', 'action_join', 'stopped', 'upgrading',
|
||
'upgrade_success', 'upgrade_failed', 'offline', 'running', 'register'))
|
||
);
|
||
|
||
-- insert default record
|
||
INSERT OR IGNORE INTO agent_admin_group_all (id, name, data, created_at, updated_at, deleted_at)
|
||
VALUES ('default', '默认分组', null, datetime('now'), datetime('now'), null);
|
||
|
||
-- count
|
||
SELECT count(*) as 'cnt0'
|
||
FROM `agent_admin_instance_all`
|
||
WHERE gid = 'default'
|
||
AND `agent_admin_instance_all`.`deleted_at` IS NULL
|
||
LIMIT 10 OFFSET 0;
|
||
SELECT count(*) as 'cnt1'
|
||
FROM `agent_admin_instance_all`
|
||
WHERE gid = 'default'
|
||
AND `agent_admin_instance_all`.`deleted_at` IS NULL
|
||
LIMIT 10 OFFSET 1;
|
||
SELECT count(*) as 'cnt10'
|
||
FROM `agent_admin_instance_all`
|
||
WHERE gid = 'default'
|
||
AND `agent_admin_instance_all`.`deleted_at` IS NULL
|
||
LIMIT 10 OFFSET 10;
|