From 69bc97bdbe330c084d9937360dd11c04b617033d Mon Sep 17 00:00:00 2001 From: Jordan Date: Sun, 26 Dec 2021 20:55:20 -0700 Subject: web, db: add wildcard URL search --- db.go | 16 +++++++++++++++- db_test.go | 22 +++++++++++++++------- web.go | 20 ++++++++++++-------- 3 files changed, 42 insertions(+), 16 deletions(-) diff --git a/db.go b/db.go index 6aa53a5..7521666 100644 --- a/db.go +++ b/db.go @@ -156,7 +156,7 @@ func (db *SqliteDB) Stats() (*Stats, error) { } func (db *SqliteDB) ListEntries(limit int, offset int, user string, - guild string, channel string) (*[]Entry, error) { + guild string, channel string, search string) (*[]Entry, error) { query := ` SELECT urls.id, urls.url, users.user_id, guilds.guild_id, channels.channel_id, status_code @@ -202,6 +202,20 @@ func (db *SqliteDB) ListEntries(limit int, offset int, user string, args = append(args, channel) } } + if search != "" { + if !hasWhere { + query = fmt.Sprintf(`%s + WHERE urls.url LIKE '%%' || ? || '%%' + `, query) + args = append(args, search) + hasWhere = true + } else { + query = fmt.Sprintf(`%s + AND urls.url LIKE '%%' || ? || '%%' + `, query) + args = append(args, search) + } + } query = fmt.Sprintf(`%s ORDER BY urls.id DESC LIMIT ? OFFSET ?;`, query) diff --git a/db_test.go b/db_test.go index d2a1ee1..4ec5a01 100644 --- a/db_test.go +++ b/db_test.go @@ -51,37 +51,45 @@ func TestDB(t *testing.T) { } // ListEntries() - e, err := db.ListEntries(10, 0, "", "", "") + e, err := db.ListEntries(10, 0, "", "", "", "") if err != nil { t.Error(err) } if len(*e) != 2 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } - e, err = db.ListEntries(10, 0, "000000000000000000", "", "") + e, err = db.ListEntries(10, 0, "000000000000000000", "", "", "") if len(*e) != 1 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } - e, err = db.ListEntries(10, 0, "", "222222222222222222", "") + e, err = db.ListEntries(10, 0, "", "222222222222222222", "", "") if len(*e) != 2 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } - e, err = db.ListEntries(10, 0, "", "", "333333333333333333") + e, err = db.ListEntries(10, 0, "", "", "333333333333333333", "") if len(*e) != 1 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } - e, err = db.ListEntries(10, 0, "111111111111111111", "222222222222222222", "") + e, err = db.ListEntries(10, 0, "111111111111111111", "222222222222222222", "", "") if len(*e) != 1 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } - e, err = db.ListEntries(10, 0, "111111111111111111", "", "333333333333333333") + e, err = db.ListEntries(10, 0, "111111111111111111", "", "333333333333333333", "") if len(*e) != 1 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } if len(*e) != 1 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } - e, err = db.ListEntries(10, 0, "111111111111111111", "222222222222222222", "333333333333333333") + e, err = db.ListEntries(10, 0, "111111111111111111", "222222222222222222", "333333333333333333", "") + if len(*e) != 1 { + t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) + } + e, err = db.ListEntries(10, 0, "", "", "", "example") + if len(*e) != 2 { + t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) + } + e, err = db.ListEntries(10, 0, "", "", "333333333333333333", "example") if len(*e) != 1 { t.Errorf("ListEntries(): Recieved length %d; wanted %d", len(*e), 2) } diff --git a/web.go b/web.go index 3f5776b..40a8a87 100644 --- a/web.go +++ b/web.go @@ -18,6 +18,7 @@ type Resp struct { User string Guild string Channel string + Search string } var funcMap = template.FuncMap{ @@ -70,6 +71,9 @@ const index = ` display: flex; justify-content: space-between; } + input { + max-width: 12em; + } @@ -84,22 +88,21 @@ const index = `

- {{- if or (ne .User "") (ne .Guild "") (ne .Channel "") -}} + {{- if or (ne .User "") (ne .Guild "") (ne .Channel "") (ne .Search "" ) -}} Entries filtered by: {{- end -}} {{- if ne .User "" }} User ({{ .User -}}){{- end -}} {{- if ne .Guild "" }} Guild ({{ .Guild -}}){{- end -}} {{- if ne .Channel "" }} Channel ({{ .Channel -}}){{- end -}} + {{- if ne .Search "" }} URL ({{ .Search -}}){{- end -}}

@@ -202,9 +205,10 @@ func (db *SqliteDB) IndexHandler(w http.ResponseWriter, r *http.Request) { resp.User = query.Get("user") resp.Guild = query.Get("guild") resp.Channel = query.Get("channel") + resp.Search = query.Get("search") resp.Entries, resp.Err = db.ListEntries(100, resp.Offset, resp.User, - resp.Guild, resp.Channel) + resp.Guild, resp.Channel, resp.Search) if resp.Err != nil { log.Println(resp.Err) } -- cgit v1.2.3-54-g00ecf