Skip to content

Commit 28317d8

Browse files
chadweimerchadweimer
authored
Added created and modified columns to app_user (#335)
* Added created and modified columns to app_user * Compare token and user modified dates * SQLite is funny like that * Only check token scopes if issued before the user was last modified * Return new fields on list * sqlite supports RETURNING now. Massive cleanup * More significant cleanup * Cleanup driver construction * Make foreign keys deferrable during migration * Only update modified date when things changed * Enable foreign keys in sqlite * Turns out settings weren't created for new users
1 parent c0cb993 commit 28317d8

23 files changed

+446
-725
lines changed

‎Makefile

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ MODELS_CODEGEN_FILE=models/models.gen.go
1616
API_CODEGEN_FILE=api/routes.gen.go
1717
CODEGEN_FILES=$(API_CODEGEN_FILE) $(MODELS_CODEGEN_FILE)
1818

19+
GO_FLAGS=--tags "sqlite_foreign_keys"
1920
GO_VERSION_FLAGS=-X 'github.com/chadweimer/gomp/metadata.BuildVersion=$(BUILD_VERSION)'
2021
GO_LD_FLAGS=-ldflags "$(GO_VERSION_FLAGS) -extldflags '-static -static-libgcc'"
2122
GO_WIN_LD_FLAGS=-ldflags "$(GO_VERSION_FLAGS)"
@@ -97,9 +98,9 @@ $(BUILD_DIR)/%/static: $(CLIENT_BUILD_DIR)
9798
rm -rf $@ && mkdir -p $@ && cp -R $</* $@
9899

99100
$(BUILD_DIR)/linux/%/gomp: go.mod $(CODEGEN_FILES) $(GO_FILES)
100-
$(GO_ENV) go build -o $@ $(GO_LD_FLAGS)
101+
$(GO_ENV) go build $(GO_FLAGS) -o $@ $(GO_LD_FLAGS)
101102
$(BUILD_DIR)/windows/%/gomp.exe: go.mod $(CODEGEN_FILES) $(GO_FILES)
102-
$(GO_ENV) go build -o $@ $(GO_WIN_LD_FLAGS)
103+
$(GO_ENV) go build $(GO_FLAGS) -o $@ $(GO_WIN_LD_FLAGS)
103104

104105
.PHONY: clean-$(BUILD_DIR)/%
105106
clean-$(BUILD_DIR)/%:

‎api/authentication.go

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,7 @@ func (h apiHandler) createToken(user *models.User) (string, error) {
8686

8787
func (h apiHandler) checkScopes(next http.HandlerFunc) http.HandlerFunc {
8888
return func(w http.ResponseWriter, r *http.Request) {
89-
scopes, ok := r.Context().Value(BearerScopes).([]string)
89+
routeScopes, ok := r.Context().Value(BearerScopes).([]string)
9090
if ok {
9191
user, claims, err := h.isAuthenticated(r)
9292
if err != nil {
@@ -95,16 +95,20 @@ func (h apiHandler) checkScopes(next http.HandlerFunc) http.HandlerFunc {
9595
}
9696

9797
// If the route requires scopes, check them
98-
if len(scopes) > 0 && (len(scopes) != 1 || scopes[0] != "") {
99-
// If the scopes of the token don't match the latest scopes of the user,
100-
// don't proceed. The client should refresh the token and try again.
101-
userScopes := getScopes(user)
102-
if !reflect.DeepEqual(userScopes, []string(claims.Scopes)) {
103-
h.Error(w, r, http.StatusForbidden, errors.New("user scopes have changed"))
104-
return
98+
if len(routeScopes) > 0 && (len(routeScopes) != 1 || routeScopes[0] != "") {
99+
// If the user has been modified since issuing the token,
100+
// we need to check if the scopes are still the same
101+
if claims.IssuedAt.Time.Before(*user.ModifiedAt) {
102+
// If the scopes of the token don't match the latest scopes of the user,
103+
// don't proceed. The client should refresh the token and try again.
104+
userScopes := getScopes(user)
105+
if !reflect.DeepEqual(userScopes, []string(claims.Scopes)) {
106+
h.Error(w, r, http.StatusForbidden, errors.New("user scopes have changed"))
107+
return
108+
}
105109
}
106110

107-
for _, scope := range scopes {
111+
for _, scope := range routeScopes {
108112
if err := hasScope(scope, claims); err != nil {
109113
err := fmt.Errorf("endpoint '%s' requires '%s' scope: %w", r.URL.Path, scope, err)
110114
h.Error(w, r, http.StatusForbidden, err)

‎db/app-sql.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@ import (
66
)
77

88
type sqlAppConfigurationDriver struct {
9-
*sqlDriver
9+
Db *sqlx.DB
1010
}
1111

1212
func (d *sqlAppConfigurationDriver) Read() (*models.AppConfiguration, error) {

‎db/driver-postgres.go

Lines changed: 19 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ import (
88
"path/filepath"
99
"time"
1010

11+
"github.com/chadweimer/gomp/models"
1112
"github.com/golang-migrate/migrate/v4"
1213
"github.com/golang-migrate/migrate/v4/database/postgres"
1314
"github.com/jmoiron/sqlx"
@@ -23,16 +24,22 @@ import (
2324
// PostgresDriverName is the name to use for this driver
2425
const PostgresDriverName string = "postgres"
2526

26-
type postgresDriver struct {
27-
*sqlDriver
27+
type postgresRecipeDriverAdapter struct{}
28+
29+
func (postgresRecipeDriverAdapter) GetSearchFields(filterFields []models.SearchField, query string) (string, []any) {
30+
fieldStr := ""
31+
fieldArgs := make([]any, 0)
32+
for _, field := range supportedSearchFields {
33+
if containsField(filterFields, field) {
34+
if fieldStr != "" {
35+
fieldStr += " OR "
36+
}
37+
fieldStr += "to_tsvector('english', r." + string(field) + ") @@ plainto_tsquery(?)"
38+
fieldArgs = append(fieldArgs, query)
39+
}
40+
}
2841

29-
app *sqlAppConfigurationDriver
30-
recipes *postgresRecipeDriver
31-
images *postgresRecipeImageDriver
32-
tags *sqlTagDriver
33-
notes *postgresNoteDriver
34-
links *sqlLinkDriver
35-
users *postgresUserDriver
42+
return fieldStr, fieldArgs
3643
}
3744

3845
func openPostgres(connectionString string, migrationsTableName string, migrationsForceVersion int) (Driver, error) {
@@ -57,58 +64,15 @@ func openPostgres(connectionString string, migrationsTableName string, migration
5764
// This is meant to mitigate connection drops
5865
db.SetConnMaxLifetime(time.Minute * 15)
5966

60-
sqlDriver := &sqlDriver{Db: db}
61-
drv := &postgresDriver{
62-
sqlDriver: sqlDriver,
63-
64-
app: &sqlAppConfigurationDriver{sqlDriver},
65-
images: &postgresRecipeImageDriver{&sqlRecipeImageDriver{sqlDriver}},
66-
tags: &sqlTagDriver{sqlDriver},
67-
notes: &postgresNoteDriver{&sqlNoteDriver{sqlDriver}},
68-
links: &sqlLinkDriver{sqlDriver},
69-
users: &postgresUserDriver{&sqlUserDriver{sqlDriver}},
70-
}
71-
drv.recipes = &postgresRecipeDriver{
72-
postgresDriver: drv,
73-
sqlRecipeDriver: &sqlRecipeDriver{sqlDriver},
74-
}
75-
76-
if err := drv.migrateDatabase(db, migrationsTableName, migrationsForceVersion); err != nil {
67+
if err := migratePostgresDatabase(db, migrationsTableName, migrationsForceVersion); err != nil {
7768
return nil, fmt.Errorf("failed to migrate database: '%w'", err)
7869
}
7970

71+
drv := newSqlDriver(db, postgresRecipeDriverAdapter{})
8072
return drv, nil
8173
}
8274

83-
func (d *postgresDriver) AppConfiguration() AppConfigurationDriver {
84-
return d.app
85-
}
86-
87-
func (d *postgresDriver) Recipes() RecipeDriver {
88-
return d.recipes
89-
}
90-
91-
func (d *postgresDriver) Images() RecipeImageDriver {
92-
return d.images
93-
}
94-
95-
func (d *postgresDriver) Tags() TagDriver {
96-
return d.tags
97-
}
98-
99-
func (d *postgresDriver) Notes() NoteDriver {
100-
return d.notes
101-
}
102-
103-
func (d *postgresDriver) Links() LinkDriver {
104-
return d.links
105-
}
106-
107-
func (d *postgresDriver) Users() UserDriver {
108-
return d.users
109-
}
110-
111-
func (*postgresDriver) migrateDatabase(db *sqlx.DB, migrationsTableName string, migrationsForceVersion int) error {
75+
func migratePostgresDatabase(db *sqlx.DB, migrationsTableName string, migrationsForceVersion int) error {
11276
// Lock the database while we're migrating so that multiple instances
11377
// don't attempt to migrate simultaneously. This requires the same connection
11478
// to be used for both locking and unlocking.

‎db/driver-sql.go

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,50 @@ type UserWithPasswordHash struct {
1919

2020
type sqlDriver struct {
2121
Db *sqlx.DB
22+
23+
app *sqlAppConfigurationDriver
24+
recipes *sqlRecipeDriver
25+
images *sqlRecipeImageDriver
26+
notes *sqlNoteDriver
27+
links *sqlLinkDriver
28+
users *sqlUserDriver
29+
}
30+
31+
func newSqlDriver(db *sqlx.DB, adapter sqlRecipeDriverAdapter) *sqlDriver {
32+
return &sqlDriver{
33+
Db: db,
34+
35+
app: &sqlAppConfigurationDriver{db},
36+
recipes: &sqlRecipeDriver{db, adapter},
37+
images: &sqlRecipeImageDriver{db},
38+
notes: &sqlNoteDriver{db},
39+
links: &sqlLinkDriver{db},
40+
users: &sqlUserDriver{db},
41+
}
42+
}
43+
44+
func (d *sqlDriver) AppConfiguration() AppConfigurationDriver {
45+
return d.app
46+
}
47+
48+
func (d *sqlDriver) Recipes() RecipeDriver {
49+
return d.recipes
50+
}
51+
52+
func (d *sqlDriver) Images() RecipeImageDriver {
53+
return d.images
54+
}
55+
56+
func (d *sqlDriver) Notes() NoteDriver {
57+
return d.notes
58+
}
59+
60+
func (d *sqlDriver) Links() LinkDriver {
61+
return d.links
62+
}
63+
64+
func (d *sqlDriver) Users() UserDriver {
65+
return d.users
2266
}
2367

2468
func (d *sqlDriver) Close() error {

‎db/driver-sqlite.go

Lines changed: 19 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ import (
88
"path/filepath"
99
"time"
1010

11+
"github.com/chadweimer/gomp/models"
1112
"github.com/golang-migrate/migrate/v4"
1213
"github.com/golang-migrate/migrate/v4/database/sqlite3"
1314
"github.com/jmoiron/sqlx"
@@ -22,16 +23,22 @@ import (
2223
// SQLiteDriverName is the name to use for this driver
2324
const SQLiteDriverName string = "sqlite3"
2425

25-
type sqliteDriver struct {
26-
*sqlDriver
26+
type sqliteRecipeDriverAdapter struct{}
27+
28+
func (sqliteRecipeDriverAdapter) GetSearchFields(filterFields []models.SearchField, query string) (string, []any) {
29+
fieldStr := ""
30+
fieldArgs := make([]interface{}, 0)
31+
for _, field := range supportedSearchFields {
32+
if containsField(filterFields, field) {
33+
if fieldStr != "" {
34+
fieldStr += " OR "
35+
}
36+
fieldStr += "r." + string(field) + " LIKE ?"
37+
fieldArgs = append(fieldArgs, "%"+query+"%")
38+
}
39+
}
2740

28-
app *sqlAppConfigurationDriver
29-
recipes *sqliteRecipeDriver
30-
images *sqlRecipeImageDriver
31-
tags *sqlTagDriver
32-
notes *sqlNoteDriver
33-
links *sqlLinkDriver
34-
users *sqlUserDriver
41+
return fieldStr, fieldArgs
3542
}
3643

3744
func openSQLite(connectionString string, migrationsTableName string, migrationsForceVersion int) (Driver, error) {
@@ -59,58 +66,15 @@ func openSQLite(connectionString string, migrationsTableName string, migrationsF
5966
// This is meant to mitigate connection drops
6067
db.SetConnMaxLifetime(time.Minute * 15)
6168

62-
sqlDriver := &sqlDriver{Db: db}
63-
drv := &sqliteDriver{
64-
sqlDriver: sqlDriver,
65-
66-
app: &sqlAppConfigurationDriver{sqlDriver},
67-
images: &sqlRecipeImageDriver{sqlDriver},
68-
tags: &sqlTagDriver{sqlDriver},
69-
notes: &sqlNoteDriver{sqlDriver},
70-
links: &sqlLinkDriver{sqlDriver},
71-
users: &sqlUserDriver{sqlDriver},
72-
}
73-
drv.recipes = &sqliteRecipeDriver{
74-
sqliteDriver: drv,
75-
sqlRecipeDriver: &sqlRecipeDriver{sqlDriver},
76-
}
77-
78-
if err := drv.migrateDatabase(db, migrationsTableName, migrationsForceVersion); err != nil {
69+
if err := migrateSqliteDatabase(db, migrationsTableName, migrationsForceVersion); err != nil {
7970
return nil, fmt.Errorf("failed to migrate database: '%w'", err)
8071
}
8172

73+
drv := newSqlDriver(db, sqliteRecipeDriverAdapter{})
8274
return drv, nil
8375
}
8476

85-
func (d *sqliteDriver) AppConfiguration() AppConfigurationDriver {
86-
return d.app
87-
}
88-
89-
func (d *sqliteDriver) Recipes() RecipeDriver {
90-
return d.recipes
91-
}
92-
93-
func (d *sqliteDriver) Images() RecipeImageDriver {
94-
return d.images
95-
}
96-
97-
func (d *sqliteDriver) Tags() TagDriver {
98-
return d.tags
99-
}
100-
101-
func (d *sqliteDriver) Notes() NoteDriver {
102-
return d.notes
103-
}
104-
105-
func (d *sqliteDriver) Links() LinkDriver {
106-
return d.links
107-
}
108-
109-
func (d *sqliteDriver) Users() UserDriver {
110-
return d.users
111-
}
112-
113-
func (*sqliteDriver) migrateDatabase(db *sqlx.DB, migrationsTableName string, migrationsForceVersion int) error {
77+
func migrateSqliteDatabase(db *sqlx.DB, migrationsTableName string, migrationsForceVersion int) error {
11478
conn, err := db.Conn(context.Background())
11579
if err != nil {
11680
return err

‎db/driver.go

Lines changed: 3 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,6 @@ type Driver interface {
2121

2222
AppConfiguration() AppConfigurationDriver
2323
Recipes() RecipeDriver
24-
Tags() TagDriver
2524
Notes() NoteDriver
2625
Images() RecipeImageDriver
2726
Links() LinkDriver
@@ -131,20 +130,17 @@ type RecipeDriver interface {
131130

132131
// Find retrieves all recipes matching the specified search filter and within the range specified.
133132
Find(filter *models.SearchFilter, page int64, count int64) (*[]models.RecipeCompact, int64, error)
134-
}
135133

136-
// TagDriver provides functionality to edit and retrieve tags attached to recipes.
137-
type TagDriver interface {
138134
// Create stores the tag in the database as a new record using
139135
// a dedicated transaction that is committed if there are not errors.
140-
Create(recipeId int64, tag string) error
136+
CreateTag(recipeId int64, tag string) error
141137

142138
// DeleteAll removes all tags for the specified recipe from the database using a dedicated
143139
// transaction that is committed if there are not errors.
144-
DeleteAll(recipeId int64) error
140+
DeleteAllTags(recipeId int64) error
145141

146142
// List retrieves all tags associated with the recipe with the specified id.
147-
List(recipeId int64) (*[]string, error)
143+
ListTags(recipeId int64) (*[]string, error)
148144
}
149145

150146
// UserDriver provides functionality to edit and authenticate users.

‎db/image-postgres.go

Lines changed: 0 additions & 30 deletions
This file was deleted.

0 commit comments

Comments
 (0)