pico

created pr with 55.1 on 2025-03-21T01:06:55Z · by c8ef7d19

cmds

checkout latest patchset:

ssh pr.pico.sh print 55 | git am -3

checkout any patchset in a patch request:

ssh pr.pico.sh print 55.[rev] | git am -3

add changes to patch request:

git format-patch main --stdout | ssh pr.pico.sh pr add 55

Patchset `55.1` on 2025-03-21T01:06:55Z · commit cb95f66

→ refactor: tuns event logs

Eric Bower 2025-03-21T01:06:36Z

refactor: tuns event logs

Eric Bower 2025-03-21T01:06:36Z

Semantic diff summary

0 added, 7 modified, 0 signature changed, 0 removed across 3 analyzed files (2 files skipped: unsupported file type)

pkg/db/db.go

chunk lines 333-340 modified

pkg/db/postgres/storage.go

chunk lines 12-18 modified
method_declaration InsertTunsEventLog modified
function_declaration FindTunsEventLogsByAddr modified
function_declaration FindTunsEventLogs modified

pkg/shared/feed.go

chunk lines 3-8 modified
function_declaration UserFeed modified

+2 -1 Makefile #

......@@ -127,10 +127,11 @@ migrate:
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241125_add_content_type_to_analytics.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241202_add_more_idx_analytics.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
.PHONY: migrate

latest:
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
.PHONY: latest

psql:

+1 -1 pkg/db/db.go #

......@@ -333,8 +333,8 @@ type TunsEventLog struct {
	EventType      string     `json:"event_type"`
	TunnelType     string     `json:"tunnel_type"`
	ConnectionType string     `json:"connection_type"`
	TunnelAddrs    []string   `json:"tunnel_addrs"`
	CreatedAt      *time.Time `json:"created_at"`
	ConnectionID   string     `json:"connection_id"`
}

var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")

+8 -8 pkg/db/postgres/storage.go #

......@@ -12,7 +12,7 @@ import (

	"slices"

	"github.com/lib/pq"
	_ "github.com/lib/pq"
	"github.com/picosh/pico/pkg/db"
	"github.com/picosh/utils"
)

......@@ -1799,11 +1799,11 @@ func (me *PsqlDB) findPagesStats(userID string) (*db.UserServiceStats, error) {
func (me *PsqlDB) InsertTunsEventLog(log *db.TunsEventLog) error {
	_, err := me.Db.Exec(
		`INSERT INTO tuns_event_logs
			(user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs)
			(user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id)
		VALUES
			($1, $2, $3, $4, $5, $6, $7)`,
		log.UserId, log.ServerID, log.RemoteAddr, log.EventType, log.TunnelType,
		log.ConnectionType, pq.Array(log.TunnelAddrs),
		log.ConnectionType, log.ConnectionID,
	)
	return err
}

......@@ -1812,8 +1812,8 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
	logs := []*db.TunsEventLog{}
	fmt.Println(addr)
	rs, err := me.Db.Query(
		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs, created_at
		FROM tuns_event_logs WHERE user_id=$1 AND tunnel_addrs @> ARRAY[$2] ORDER BY created_at DESC`, userID, addr)
		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id, created_at
		FROM tuns_event_logs WHERE user_id=$1 AND connection_id=$2 ORDER BY created_at DESC`, userID, addr)
	if err != nil {
		return nil, err
	}

......@@ -1823,7 +1823,7 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
		err := rs.Scan(
			&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
			&log.EventType, &log.TunnelType, &log.ConnectionType,
			(*pq.StringArray)(&log.TunnelAddrs), &log.CreatedAt,
			&log.ConnectionID, &log.CreatedAt,
		)
		if err != nil {
			return nil, err

......@@ -1841,7 +1841,7 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
	logs := []*db.TunsEventLog{}
	rs, err := me.Db.Query(
		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs, created_at
		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id, created_at
		FROM tuns_event_logs WHERE user_id=$1 ORDER BY created_at DESC`, userID)
	if err != nil {
		return nil, err

......@@ -1852,7 +1852,7 @@ func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
		err := rs.Scan(
			&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
			&log.EventType, &log.TunnelType, &log.ConnectionType,
			(*pq.StringArray)(&log.TunnelAddrs), &log.CreatedAt,
			&log.ConnectionID, &log.CreatedAt,
		)
		if err != nil {
			return nil, err

+3 -4 pkg/shared/feed.go #

......@@ -3,7 +3,6 @@ package shared
import (
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/gorilla/feeds"

......@@ -134,19 +133,19 @@ func UserFeed(me db.DB, user *db.User, token string) (*feeds.Feed, error) {
	for _, eventLog := range tunsLogs {
		content := fmt.Sprintf(`Created At: %s<br />
Event type: %s<br />
Connection ID: %s<br />
Connection type: %s<br />
Remote addr: %s<br />
Tunnel type: %s<br />
Tunnel addrs: %s<br />
Server: %s`,
			eventLog.CreatedAt.Format(time.RFC3339), eventLog.EventType, eventLog.ConnectionType,
			eventLog.RemoteAddr, eventLog.TunnelType, eventLog.TunnelAddrs, eventLog.ServerID,
			eventLog.RemoteAddr, eventLog.TunnelType, eventLog.ConnectionID, eventLog.ServerID,
		)
		logItem := &feeds.Item{
			Id: fmt.Sprintf("%d", eventLog.CreatedAt.Unix()),
			Title: fmt.Sprintf(
				"%s tuns event for %s",
				eventLog.EventType, strings.Join(eventLog.TunnelAddrs, ", "),
				eventLog.EventType, eventLog.ConnectionID,
			),
			Link:        &feeds.Link{Href: "https://pico.sh"},
			Content:     content,

+3 -0 sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql #

......@@ -0,0 +1,3 @@
DELETE FROM tuns_event_logs;
ALTER TABLE tuns_event_logs ADD COLUMN connection_id text NOT NULL;
ALTER TABLE tuns_event_logs DROP COLUMN tunnel_addrs;

...	...		@@ -1799,11 +1799,11 @@ func (me PsqlDB) findPagesStats(userID string) (db.UserServiceStats, error) {
1799	1799		func (me PsqlDB) InsertTunsEventLog(log db.TunsEventLog) error {
1800	1800		_, err := me.Db.Exec(
1801	1801		`INSERT INTO tuns_event_logs
1802		-	(user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs)
	1802	+	(user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id)
1803	1803		VALUES
1804	1804		($1, $2, $3, $4, $5, $6, $7)`,
1805	1805		log.UserId, log.ServerID, log.RemoteAddr, log.EventType, log.TunnelType,
1806		-	log.ConnectionType, pq.Array(log.TunnelAddrs),
	1806	+	log.ConnectionType, log.ConnectionID,
1807	1807		)
1808	1808		return err
1809	1809		}

...	...		@@ -134,19 +133,19 @@ func UserFeed(me db.DB, user db.User, token string) (feeds.Feed, error) {
134	133		for _, eventLog := range tunsLogs {
135	134		content := fmt.Sprintf(`Created At: %s<br />
136	135		Event type: %s<br />
	136	+	Connection ID: %s<br />
137	137		Connection type: %s<br />
138	138		Remote addr: %s<br />
139	139		Tunnel type: %s<br />
140		-	Tunnel addrs: %s<br />
141	140		Server: %s`,
142	141		eventLog.CreatedAt.Format(time.RFC3339), eventLog.EventType, eventLog.ConnectionType,
143		-	eventLog.RemoteAddr, eventLog.TunnelType, eventLog.TunnelAddrs, eventLog.ServerID,
	142	+	eventLog.RemoteAddr, eventLog.TunnelType, eventLog.ConnectionID, eventLog.ServerID,
144	143		)
145	144		logItem := &feeds.Item{
146	145		Id: fmt.Sprintf("%d", eventLog.CreatedAt.Unix()),
147	146		Title: fmt.Sprintf(
148	147		"%s tuns event for %s",
149		-	eventLog.EventType, strings.Join(eventLog.TunnelAddrs, ", "),
	148	+	eventLog.EventType, eventLog.ConnectionID,
150	149		),
151	150		Link: &feeds.Link{Href: "https://pico.sh"},
152	151		Content: content,

...	...		@@ -127,10 +127,11 @@ migrate:
127	127		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241125_add_content_type_to_analytics.sql
128	128		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241202_add_more_idx_analytics.sql
129	129		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
	130	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
130	131		.PHONY: migrate
131	132
132	133		latest:
133		-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
	134	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_connection_id_to_tuns_event_logs_table.sql
134	135		.PHONY: latest
135	136
136	137		psql:

...	...		@@ -333,8 +333,8 @@ type TunsEventLog struct {
333	333		EventType string `json:"event_type"`
334	334		TunnelType string `json:"tunnel_type"`
335	335		ConnectionType string `json:"connection_type"`
336		-	TunnelAddrs []string `json:"tunnel_addrs"`
337	336		CreatedAt *time.Time `json:"created_at"`
	337	+	ConnectionID string `json:"connection_id"`
338	338		}
339	339
340	340		var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")

...	...		@@ -12,7 +12,7 @@ import (
12	12
13	13		"slices"
14	14
15		-	"github.com/lib/pq"
	15	+	_ "github.com/lib/pq"
16	16		"github.com/picosh/pico/pkg/db"
17	17		"github.com/picosh/utils"
18	18		)

...	...		@@ -1812,8 +1812,8 @@ func (me PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]db.TunsEventL
1812	1812		logs := []*db.TunsEventLog{}
1813	1813		fmt.Println(addr)
1814	1814		rs, err := me.Db.Query(
1815		-	`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs, created_at
1816		-	FROM tuns_event_logs WHERE user_id=$1 AND tunnel_addrs @> ARRAY[$2] ORDER BY created_at DESC`, userID, addr)
	1815	+	`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id, created_at
	1816	+	FROM tuns_event_logs WHERE user_id=$1 AND connection_id=$2 ORDER BY created_at DESC`, userID, addr)
1817	1817		if err != nil {
1818	1818		return nil, err
1819	1819		}

...	...		@@ -1823,7 +1823,7 @@ func (me PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]db.TunsEventL
1823	1823		err := rs.Scan(
1824	1824		&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
1825	1825		&log.EventType, &log.TunnelType, &log.ConnectionType,
1826		-	(*pq.StringArray)(&log.TunnelAddrs), &log.CreatedAt,
	1826	+	&log.ConnectionID, &log.CreatedAt,
1827	1827		)
1828	1828		if err != nil {
1829	1829		return nil, err

...	...		@@ -1841,7 +1841,7 @@ func (me PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]db.TunsEventL
1841	1841		func (me PsqlDB) FindTunsEventLogs(userID string) ([]db.TunsEventLog, error) {
1842	1842		logs := []*db.TunsEventLog{}
1843	1843		rs, err := me.Db.Query(
1844		-	`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_addrs, created_at
	1844	+	`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, connection_id, created_at
1845	1845		FROM tuns_event_logs WHERE user_id=$1 ORDER BY created_at DESC`, userID)
1846	1846		if err != nil {
1847	1847		return nil, err

...	...		@@ -1852,7 +1852,7 @@ func (me PsqlDB) FindTunsEventLogs(userID string) ([]db.TunsEventLog, error) {
1852	1852		err := rs.Scan(
1853	1853		&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
1854	1854		&log.EventType, &log.TunnelType, &log.ConnectionType,
1855		-	(*pq.StringArray)(&log.TunnelAddrs), &log.CreatedAt,
	1855	+	&log.ConnectionID, &log.CreatedAt,
1856	1856		)
1857	1857		if err != nil {
1858	1858		return nil, err

...	...		@@ -3,7 +3,6 @@ package shared
3	3		import (
4	4		"fmt"
5	5		"sort"
6		-	"strings"
7	6		"time"
8	7
9	8		"github.com/gorilla/feeds"

...	...		@@ -0,0 +1,3 @@
	1	+	DELETE FROM tuns_event_logs;
	2	+	ALTER TABLE tuns_event_logs ADD COLUMN connection_id text NOT NULL;
	3	+	ALTER TABLE tuns_event_logs DROP COLUMN tunnel_addrs;

pico

Patchset 55.1 on 2025-03-21T01:06:55Z · commit cb95f66

Patchset `55.1` on 2025-03-21T01:06:55Z · commit cb95f66