Method Cleanup

internal/store/postgres/postgres.go:68–119

Cleanup 清理数据库中所有孤立记录，返回各表删除数量。 所有删除在同一事务中执行，保证原子性。

()

// Cleanup 清理数据库中所有孤立记录，返回各表删除数量。
// 所有删除在同一事务中执行，保证原子性。
func (db *DB) Cleanup() (CleanupResult, error) {
	var r CleanupResult
	ctx := context.Background()

	tx, err := db.conn.Begin(ctx)
	if err != nil {
		return r, fmt.Errorf("cleanup begin tx: %w", err)
	}
	defer tx.Rollback(ctx) //nolint:errcheck

	steps := []struct {
		sql  string
		dest *int64
	}{
		{`DELETE FROM user_inbounds WHERE user_id NOT IN (SELECT id FROM users)`, &r.UserInboundsByUser},
		{`DELETE FROM user_inbounds WHERE inbound_id != '' AND inbound_id NOT IN (SELECT id FROM inbounds)`, &r.UserInboundsByInbound},
		{`DELETE FROM hosts WHERE inbound_id NOT IN (SELECT id FROM inbounds)`, &r.HostsByInbound},
		{`DELETE FROM inbounds WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.InboundsByNode},
		{`DELETE FROM node_daily_usage WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.DailyUsageByNode},
		{`DELETE FROM sub_access_logs WHERE user_id NOT IN (SELECT id FROM users)`, &r.SubAccessLogsByUser},
		{`DELETE FROM user_node_daily_usage WHERE user_id NOT IN (SELECT id FROM users)`, &r.UserNodeUsageByUser},
		{`DELETE FROM user_node_daily_usage WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.UserNodeUsageByNode},
		{`DELETE FROM node_uptime_log WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.NodeUptimeByNode},
		{`DELETE FROM node_speedtest WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.NodeSpeedtestByNode},
		{`DELETE FROM node_check_results WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.NodeCheckResultsByNode},
		{`DELETE FROM traceroute_snapshots WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.TracerouteByNode},
		{`DELETE FROM node_domains WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.NodeDomainsByNode},
		{`DELETE FROM ip_sentinel_configs WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.IPSentinelConfigByNode},
		{`DELETE FROM ip_sentinel_runs WHERE node_id NOT IN (SELECT id FROM nodes)`, &r.IPSentinelRunsByNode},
		{`DELETE FROM ticket_messages WHERE ticket_id NOT IN (SELECT id FROM tickets)`, &r.TicketMessagesByTicket},
		{`DELETE FROM ticket_images WHERE ticket_id NOT IN (SELECT id FROM tickets)`, &r.TicketImagesByTicket},
		{`DELETE FROM user_host_exclusions WHERE user_id NOT IN (SELECT id FROM users)`, &r.HostExclusionsByUser},
		{`DELETE FROM user_host_exclusions WHERE host_id NOT IN (SELECT id FROM hosts)`, &r.HostExclusionsByHost},
		{`DELETE FROM user_group_members WHERE user_id NOT IN (SELECT id FROM users)`, &r.UserGroupMembersByUser},
		{`DELETE FROM user_group_members WHERE group_id NOT IN (SELECT id FROM user_groups)`, &r.UserGroupMembersByGroup},
	}

	for _, step := range steps {
		res, err := tx.Exec(ctx, step.sql)
		if err != nil {
			return r, fmt.Errorf("cleanup: %w", err)
		}
		n := res.RowsAffected()
		*step.dest = n
		r.Total += n
	}

	if err := tx.Commit(ctx); err != nil {
		return r, fmt.Errorf("cleanup commit: %w", err)
	}
	return r, nil
}

func (db *DB) NodeStore() *NodeStore {
	return &NodeStore{db: db.conn}