aboutsummaryrefslogtreecommitdiffstats
path: root/pkg/database
diff options
context:
space:
mode:
Diffstat (limited to 'pkg/database')
-rw-r--r--pkg/database/migrations/0003_multi_conference.sql13
-rw-r--r--pkg/database/migrations/0004_user_admins.sql2
-rw-r--r--pkg/database/query/conferences.sql21
-rw-r--r--pkg/database/query/favourites.sql10
-rw-r--r--pkg/database/sqlc/conferences.sql.go119
-rw-r--r--pkg/database/sqlc/favourites.sql.go76
-rw-r--r--pkg/database/sqlc/models.go18
-rw-r--r--pkg/database/sqlc/users.sql.go36
8 files changed, 267 insertions, 28 deletions
diff --git a/pkg/database/migrations/0003_multi_conference.sql b/pkg/database/migrations/0003_multi_conference.sql
new file mode 100644
index 0000000..31a1f58
--- /dev/null
+++ b/pkg/database/migrations/0003_multi_conference.sql
@@ -0,0 +1,13 @@
+-- +goose Up
+CREATE TABLE conferences (
+ id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
+ url text NOT NULL,
+ title text,
+ venue text,
+ city text
+);
+
+TRUNCATE TABLE favourites CONTINUE IDENTITY;
+ALTER TABLE favourites ADD conference_id int NOT NULL REFERENCES conferences(id) ON DELETE CASCADE;
+ALTER TABLE favourites DROP CONSTRAINT favourites_user_id_event_guid_event_id_key;
+ALTER TABLE favourites ADD UNIQUE(user_id, event_guid, event_id, conference_id);
diff --git a/pkg/database/migrations/0004_user_admins.sql b/pkg/database/migrations/0004_user_admins.sql
new file mode 100644
index 0000000..5c4efb3
--- /dev/null
+++ b/pkg/database/migrations/0004_user_admins.sql
@@ -0,0 +1,2 @@
+-- +goose Up
+ALTER TABLE users ADD admin boolean NOT NULL DEFAULT false;
diff --git a/pkg/database/query/conferences.sql b/pkg/database/query/conferences.sql
new file mode 100644
index 0000000..7acac3f
--- /dev/null
+++ b/pkg/database/query/conferences.sql
@@ -0,0 +1,21 @@
+-- name: CreateConference :one
+INSERT INTO conferences (
+ url, title, venue, city
+) VALUES (
+ $1, $2, $3, $4
+)
+RETURNING *;
+
+-- name: UpdateConferenceDetails :one
+UPDATE conferences SET (
+ title, venue, city
+) = ($2, $3, $4)
+WHERE id = $1
+RETURNING *;
+
+-- name: GetConferences :many
+SELECT * FROM conferences;
+
+-- name: DeleteConference :exec
+DELETE FROM conferences
+WHERE id = $1;
diff --git a/pkg/database/query/favourites.sql b/pkg/database/query/favourites.sql
index 94e914c..a903ac7 100644
--- a/pkg/database/query/favourites.sql
+++ b/pkg/database/query/favourites.sql
@@ -1,12 +1,16 @@
+-- name: GetFavouritesForUserConference :many
+SELECT * FROM favourites
+WHERE user_id = $1 AND conference_id = $2;
+
-- name: GetFavouritesForUser :many
SELECT * FROM favourites
WHERE user_id = $1;
-- name: CreateFavourite :one
INSERT INTO favourites (
- user_id, event_guid, event_id
+ user_id, event_guid, event_id, conference_id
) VALUES (
- $1, $2, $3
+ $1, $2, $3, $4
)
RETURNING *;
@@ -16,4 +20,4 @@ WHERE id = $1;
-- name: DeleteFavouriteByEventDetails :execrows
DELETE FROM favourites
-WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3;
+WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3 AND conference_id = $4;
diff --git a/pkg/database/sqlc/conferences.sql.go b/pkg/database/sqlc/conferences.sql.go
new file mode 100644
index 0000000..1345185
--- /dev/null
+++ b/pkg/database/sqlc/conferences.sql.go
@@ -0,0 +1,119 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+// sqlc v1.29.0
+// source: conferences.sql
+
+package sqlc
+
+import (
+ "context"
+
+ "github.com/jackc/pgx/v5/pgtype"
+)
+
+const createConference = `-- name: CreateConference :one
+INSERT INTO conferences (
+ url, title, venue, city
+) VALUES (
+ $1, $2, $3, $4
+)
+RETURNING id, url, title, venue, city
+`
+
+type CreateConferenceParams struct {
+ Url string `json:"url"`
+ Title pgtype.Text `json:"title"`
+ Venue pgtype.Text `json:"venue"`
+ City pgtype.Text `json:"city"`
+}
+
+func (q *Queries) CreateConference(ctx context.Context, arg CreateConferenceParams) (Conference, error) {
+ row := q.db.QueryRow(ctx, createConference,
+ arg.Url,
+ arg.Title,
+ arg.Venue,
+ arg.City,
+ )
+ var i Conference
+ err := row.Scan(
+ &i.ID,
+ &i.Url,
+ &i.Title,
+ &i.Venue,
+ &i.City,
+ )
+ return i, err
+}
+
+const deleteConference = `-- name: DeleteConference :exec
+DELETE FROM conferences
+WHERE id = $1
+`
+
+func (q *Queries) DeleteConference(ctx context.Context, id int32) error {
+ _, err := q.db.Exec(ctx, deleteConference, id)
+ return err
+}
+
+const getConferences = `-- name: GetConferences :many
+SELECT id, url, title, venue, city FROM conferences
+`
+
+func (q *Queries) GetConferences(ctx context.Context) ([]Conference, error) {
+ rows, err := q.db.Query(ctx, getConferences)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ var items []Conference
+ for rows.Next() {
+ var i Conference
+ if err := rows.Scan(
+ &i.ID,
+ &i.Url,
+ &i.Title,
+ &i.Venue,
+ &i.City,
+ ); err != nil {
+ return nil, err
+ }
+ items = append(items, i)
+ }
+ if err := rows.Err(); err != nil {
+ return nil, err
+ }
+ return items, nil
+}
+
+const updateConferenceDetails = `-- name: UpdateConferenceDetails :one
+UPDATE conferences SET (
+ title, venue, city
+) = ($2, $3, $4)
+WHERE id = $1
+RETURNING id, url, title, venue, city
+`
+
+type UpdateConferenceDetailsParams struct {
+ ID int32 `json:"id"`
+ Title pgtype.Text `json:"title"`
+ Venue pgtype.Text `json:"venue"`
+ City pgtype.Text `json:"city"`
+}
+
+func (q *Queries) UpdateConferenceDetails(ctx context.Context, arg UpdateConferenceDetailsParams) (Conference, error) {
+ row := q.db.QueryRow(ctx, updateConferenceDetails,
+ arg.ID,
+ arg.Title,
+ arg.Venue,
+ arg.City,
+ )
+ var i Conference
+ err := row.Scan(
+ &i.ID,
+ &i.Url,
+ &i.Title,
+ &i.Venue,
+ &i.City,
+ )
+ return i, err
+}
diff --git a/pkg/database/sqlc/favourites.sql.go b/pkg/database/sqlc/favourites.sql.go
index b13261f..d28470d 100644
--- a/pkg/database/sqlc/favourites.sql.go
+++ b/pkg/database/sqlc/favourites.sql.go
@@ -13,27 +13,34 @@ import (
const createFavourite = `-- name: CreateFavourite :one
INSERT INTO favourites (
- user_id, event_guid, event_id
+ user_id, event_guid, event_id, conference_id
) VALUES (
- $1, $2, $3
+ $1, $2, $3, $4
)
-RETURNING id, user_id, event_guid, event_id
+RETURNING id, user_id, event_guid, event_id, conference_id
`
type CreateFavouriteParams struct {
- UserID int32 `json:"user_id"`
- EventGuid pgtype.UUID `json:"event_guid"`
- EventID pgtype.Int4 `json:"event_id"`
+ UserID int32 `json:"user_id"`
+ EventGuid pgtype.UUID `json:"event_guid"`
+ EventID pgtype.Int4 `json:"event_id"`
+ ConferenceID int32 `json:"conference_id"`
}
func (q *Queries) CreateFavourite(ctx context.Context, arg CreateFavouriteParams) (Favourite, error) {
- row := q.db.QueryRow(ctx, createFavourite, arg.UserID, arg.EventGuid, arg.EventID)
+ row := q.db.QueryRow(ctx, createFavourite,
+ arg.UserID,
+ arg.EventGuid,
+ arg.EventID,
+ arg.ConferenceID,
+ )
var i Favourite
err := row.Scan(
&i.ID,
&i.UserID,
&i.EventGuid,
&i.EventID,
+ &i.ConferenceID,
)
return i, err
}
@@ -50,17 +57,23 @@ func (q *Queries) DeleteFavourite(ctx context.Context, id int32) error {
const deleteFavouriteByEventDetails = `-- name: DeleteFavouriteByEventDetails :execrows
DELETE FROM favourites
-WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3
+WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3 AND conference_id = $4
`
type DeleteFavouriteByEventDetailsParams struct {
- EventGuid pgtype.UUID `json:"event_guid"`
- EventID pgtype.Int4 `json:"event_id"`
- UserID int32 `json:"user_id"`
+ EventGuid pgtype.UUID `json:"event_guid"`
+ EventID pgtype.Int4 `json:"event_id"`
+ UserID int32 `json:"user_id"`
+ ConferenceID int32 `json:"conference_id"`
}
func (q *Queries) DeleteFavouriteByEventDetails(ctx context.Context, arg DeleteFavouriteByEventDetailsParams) (int64, error) {
- result, err := q.db.Exec(ctx, deleteFavouriteByEventDetails, arg.EventGuid, arg.EventID, arg.UserID)
+ result, err := q.db.Exec(ctx, deleteFavouriteByEventDetails,
+ arg.EventGuid,
+ arg.EventID,
+ arg.UserID,
+ arg.ConferenceID,
+ )
if err != nil {
return 0, err
}
@@ -68,7 +81,7 @@ func (q *Queries) DeleteFavouriteByEventDetails(ctx context.Context, arg DeleteF
}
const getFavouritesForUser = `-- name: GetFavouritesForUser :many
-SELECT id, user_id, event_guid, event_id FROM favourites
+SELECT id, user_id, event_guid, event_id, conference_id FROM favourites
WHERE user_id = $1
`
@@ -86,6 +99,43 @@ func (q *Queries) GetFavouritesForUser(ctx context.Context, userID int32) ([]Fav
&i.UserID,
&i.EventGuid,
&i.EventID,
+ &i.ConferenceID,
+ ); err != nil {
+ return nil, err
+ }
+ items = append(items, i)
+ }
+ if err := rows.Err(); err != nil {
+ return nil, err
+ }
+ return items, nil
+}
+
+const getFavouritesForUserConference = `-- name: GetFavouritesForUserConference :many
+SELECT id, user_id, event_guid, event_id, conference_id FROM favourites
+WHERE user_id = $1 AND conference_id = $2
+`
+
+type GetFavouritesForUserConferenceParams struct {
+ UserID int32 `json:"user_id"`
+ ConferenceID int32 `json:"conference_id"`
+}
+
+func (q *Queries) GetFavouritesForUserConference(ctx context.Context, arg GetFavouritesForUserConferenceParams) ([]Favourite, error) {
+ rows, err := q.db.Query(ctx, getFavouritesForUserConference, arg.UserID, arg.ConferenceID)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ var items []Favourite
+ for rows.Next() {
+ var i Favourite
+ if err := rows.Scan(
+ &i.ID,
+ &i.UserID,
+ &i.EventGuid,
+ &i.EventID,
+ &i.ConferenceID,
); err != nil {
return nil, err
}
diff --git a/pkg/database/sqlc/models.go b/pkg/database/sqlc/models.go
index 57fd082..b7dfc19 100644
--- a/pkg/database/sqlc/models.go
+++ b/pkg/database/sqlc/models.go
@@ -15,15 +15,25 @@ type Calendar struct {
Key string `json:"key"`
}
+type Conference struct {
+ ID int32 `json:"id"`
+ Url string `json:"url"`
+ Title pgtype.Text `json:"title"`
+ Venue pgtype.Text `json:"venue"`
+ City pgtype.Text `json:"city"`
+}
+
type Favourite struct {
- ID int32 `json:"id"`
- UserID int32 `json:"user_id"`
- EventGuid pgtype.UUID `json:"event_guid"`
- EventID pgtype.Int4 `json:"event_id"`
+ ID int32 `json:"id"`
+ UserID int32 `json:"user_id"`
+ EventGuid pgtype.UUID `json:"event_guid"`
+ EventID pgtype.Int4 `json:"event_id"`
+ ConferenceID int32 `json:"conference_id"`
}
type User struct {
ID int32 `json:"id"`
Username string `json:"username"`
Password pgtype.Text `json:"password"`
+ Admin bool `json:"admin"`
}
diff --git a/pkg/database/sqlc/users.sql.go b/pkg/database/sqlc/users.sql.go
index cf0aeb9..45ae019 100644
--- a/pkg/database/sqlc/users.sql.go
+++ b/pkg/database/sqlc/users.sql.go
@@ -17,7 +17,7 @@ INSERT INTO users (
) VALUES (
$1, $2
)
-RETURNING id, username, password
+RETURNING id, username, password, admin
`
type CreateUserParams struct {
@@ -28,7 +28,12 @@ type CreateUserParams struct {
func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
row := q.db.QueryRow(ctx, createUser, arg.Username, arg.Password)
var i User
- err := row.Scan(&i.ID, &i.Username, &i.Password)
+ err := row.Scan(
+ &i.ID,
+ &i.Username,
+ &i.Password,
+ &i.Admin,
+ )
return i, err
}
@@ -43,31 +48,41 @@ func (q *Queries) DeleteUser(ctx context.Context, id int32) error {
}
const getUserByID = `-- name: GetUserByID :one
-SELECT id, username, password FROM users
+SELECT id, username, password, admin FROM users
WHERE id = $1 LIMIT 1
`
func (q *Queries) GetUserByID(ctx context.Context, id int32) (User, error) {
row := q.db.QueryRow(ctx, getUserByID, id)
var i User
- err := row.Scan(&i.ID, &i.Username, &i.Password)
+ err := row.Scan(
+ &i.ID,
+ &i.Username,
+ &i.Password,
+ &i.Admin,
+ )
return i, err
}
const getUserByName = `-- name: GetUserByName :one
-SELECT id, username, password FROM users
+SELECT id, username, password, admin FROM users
WHERE username = $1 LIMIT 1
`
func (q *Queries) GetUserByName(ctx context.Context, username string) (User, error) {
row := q.db.QueryRow(ctx, getUserByName, username)
var i User
- err := row.Scan(&i.ID, &i.Username, &i.Password)
+ err := row.Scan(
+ &i.ID,
+ &i.Username,
+ &i.Password,
+ &i.Admin,
+ )
return i, err
}
const listUsers = `-- name: ListUsers :many
-SELECT id, username, password FROM users
+SELECT id, username, password, admin FROM users
ORDER BY username
`
@@ -80,7 +95,12 @@ func (q *Queries) ListUsers(ctx context.Context) ([]User, error) {
var items []User
for rows.Next() {
var i User
- if err := rows.Scan(&i.ID, &i.Username, &i.Password); err != nil {
+ if err := rows.Scan(
+ &i.ID,
+ &i.Username,
+ &i.Password,
+ &i.Admin,
+ ); err != nil {
return nil, err
}
items = append(items, i)
generated by cgit v1.2.3-70-g09d2 (git 2.51.2) at 2025-11-25 07:02:48 +0000