From dc55f9c0097e1c36b85d7666071b840b902920e9 Mon Sep 17 00:00:00 2001 From: Leonardo Bishop Date: Mon, 20 Jan 2025 02:56:25 +0000 Subject: Add calendar support --- pkg/database/migrations/0001_initial.sql | 17 ++++-- pkg/database/query/calendars.sql | 23 ++++++++ pkg/database/query/favourites.sql | 2 +- pkg/database/sqlc/calendars.sql.go | 97 ++++++++++++++++++++++++++++++++ pkg/database/sqlc/favourites.sql.go | 36 ------------ pkg/database/sqlc/models.go | 7 +++ 6 files changed, 141 insertions(+), 41 deletions(-) create mode 100644 pkg/database/query/calendars.sql create mode 100644 pkg/database/sqlc/calendars.sql.go (limited to 'pkg/database') diff --git a/pkg/database/migrations/0001_initial.sql b/pkg/database/migrations/0001_initial.sql index eea0a73..a1f5fda 100644 --- a/pkg/database/migrations/0001_initial.sql +++ b/pkg/database/migrations/0001_initial.sql @@ -1,8 +1,8 @@ -- +goose Up CREATE TABLE users ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, - username varchar(20) UNIQUE NOT NULL, - password char(60) NOT NULL + username varchar(20) UNIQUE NOT NULL CONSTRAINT non_blank_username CHECK(length(username) > 0), + password text NOT NULL CONSTRAINT valid_hash CHECK (length(password) = 60) ); CREATE TABLE favourites ( @@ -11,7 +11,16 @@ CREATE TABLE favourites ( event_guid uuid, event_id int, UNIQUE(user_id, event_guid, event_id), - CONSTRAINT chk_favourites CHECK (event_guid IS NOT NULL OR event_id IS NOT NULL), - FOREIGN KEY (user_id) REFERENCES users(id) + CONSTRAINT require_event_detail CHECK (event_guid IS NOT NULL OR event_id IS NOT NULL), + FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); +CREATE TABLE calendars ( + id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + user_id int NOT NULL, + name text NOT NULL CONSTRAINT non_blank_name CHECK(length(name) > 0), + key text NOT NULL, + UNIQUE(user_id), + UNIQUE(name), + FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE +); diff --git a/pkg/database/query/calendars.sql b/pkg/database/query/calendars.sql new file mode 100644 index 0000000..490475d --- /dev/null +++ b/pkg/database/query/calendars.sql @@ -0,0 +1,23 @@ +-- name: CreateCalendar :one +INSERT INTO calendars ( + user_id, name, key +) VALUES ( + $1, $2, $3 +) +RETURNING *; + +-- name: GetCalendarForUser :one +SELECT * FROM calendars +WHERE user_id = $1 LIMIT 1; + +-- name: GetCalendarByName :one +SELECT * FROM calendars +WHERE name = $1 LIMIT 1; + +-- name: DeleteCalendar :execrows +DELETE FROM calendars +WHERE user_id = $1; + +-- name: DeleteCalendarByName :execrows +DELETE FROM calendars +WHERE name = $1; diff --git a/pkg/database/query/favourites.sql b/pkg/database/query/favourites.sql index 0661daa..94e914c 100644 --- a/pkg/database/query/favourites.sql +++ b/pkg/database/query/favourites.sql @@ -16,4 +16,4 @@ WHERE id = $1; -- name: DeleteFavouriteByEventDetails :execrows DELETE FROM favourites -WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3; \ No newline at end of file +WHERE (event_guid = $1 OR event_id = $2) AND user_id = $3; diff --git a/pkg/database/sqlc/calendars.sql.go b/pkg/database/sqlc/calendars.sql.go new file mode 100644 index 0000000..47ae37f --- /dev/null +++ b/pkg/database/sqlc/calendars.sql.go @@ -0,0 +1,97 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.27.0 +// source: calendars.sql + +package sqlc + +import ( + "context" +) + +const createCalendar = `-- name: CreateCalendar :one +INSERT INTO calendars ( + user_id, name, key +) VALUES ( + $1, $2, $3 +) +RETURNING id, user_id, name, key +` + +type CreateCalendarParams struct { + UserID int32 `json:"user_id"` + Name string `json:"name"` + Key string `json:"key"` +} + +func (q *Queries) CreateCalendar(ctx context.Context, arg CreateCalendarParams) (Calendar, error) { + row := q.db.QueryRow(ctx, createCalendar, arg.UserID, arg.Name, arg.Key) + var i Calendar + err := row.Scan( + &i.ID, + &i.UserID, + &i.Name, + &i.Key, + ) + return i, err +} + +const deleteCalendar = `-- name: DeleteCalendar :execrows +DELETE FROM calendars +WHERE user_id = $1 +` + +func (q *Queries) DeleteCalendar(ctx context.Context, userID int32) (int64, error) { + result, err := q.db.Exec(ctx, deleteCalendar, userID) + if err != nil { + return 0, err + } + return result.RowsAffected(), nil +} + +const deleteCalendarByName = `-- name: DeleteCalendarByName :execrows +DELETE FROM calendars +WHERE name = $1 +` + +func (q *Queries) DeleteCalendarByName(ctx context.Context, name string) (int64, error) { + result, err := q.db.Exec(ctx, deleteCalendarByName, name) + if err != nil { + return 0, err + } + return result.RowsAffected(), nil +} + +const getCalendarByName = `-- name: GetCalendarByName :one +SELECT id, user_id, name, key FROM calendars +WHERE name = $1 LIMIT 1 +` + +func (q *Queries) GetCalendarByName(ctx context.Context, name string) (Calendar, error) { + row := q.db.QueryRow(ctx, getCalendarByName, name) + var i Calendar + err := row.Scan( + &i.ID, + &i.UserID, + &i.Name, + &i.Key, + ) + return i, err +} + +const getCalendarForUser = `-- name: GetCalendarForUser :one +SELECT id, user_id, name, key FROM calendars +WHERE user_id = $1 LIMIT 1 +` + +func (q *Queries) GetCalendarForUser(ctx context.Context, userID int32) (Calendar, error) { + row := q.db.QueryRow(ctx, getCalendarForUser, userID) + var i Calendar + err := row.Scan( + &i.ID, + &i.UserID, + &i.Name, + &i.Key, + ) + return i, err +} diff --git a/pkg/database/sqlc/favourites.sql.go b/pkg/database/sqlc/favourites.sql.go index 3bf7c06..359ae9d 100644 --- a/pkg/database/sqlc/favourites.sql.go +++ b/pkg/database/sqlc/favourites.sql.go @@ -67,42 +67,6 @@ func (q *Queries) DeleteFavouriteByEventDetails(ctx context.Context, arg DeleteF return result.RowsAffected(), nil } -const deleteFavouriteByEventGuid = `-- name: DeleteFavouriteByEventGuid :execrows -DELETE FROM favourites -WHERE event_guid = $1 AND user_id = $2 -` - -type DeleteFavouriteByEventGuidParams struct { - EventGuid pgtype.UUID `json:"event_guid"` - UserID int32 `json:"user_id"` -} - -func (q *Queries) DeleteFavouriteByEventGuid(ctx context.Context, arg DeleteFavouriteByEventGuidParams) (int64, error) { - result, err := q.db.Exec(ctx, deleteFavouriteByEventGuid, arg.EventGuid, arg.UserID) - if err != nil { - return 0, err - } - return result.RowsAffected(), nil -} - -const deleteFavouriteByEventId = `-- name: DeleteFavouriteByEventId :execrows -DELETE FROM favourites -WHERE event_id = $1 AND user_id = $2 -` - -type DeleteFavouriteByEventIdParams struct { - EventID pgtype.Int4 `json:"event_id"` - UserID int32 `json:"user_id"` -} - -func (q *Queries) DeleteFavouriteByEventId(ctx context.Context, arg DeleteFavouriteByEventIdParams) (int64, error) { - result, err := q.db.Exec(ctx, deleteFavouriteByEventId, arg.EventID, arg.UserID) - if err != nil { - return 0, err - } - return result.RowsAffected(), nil -} - const getFavouritesForUser = `-- name: GetFavouritesForUser :many SELECT id, user_id, event_guid, event_id FROM favourites WHERE user_id = $1 diff --git a/pkg/database/sqlc/models.go b/pkg/database/sqlc/models.go index 09208aa..e38851a 100644 --- a/pkg/database/sqlc/models.go +++ b/pkg/database/sqlc/models.go @@ -8,6 +8,13 @@ import ( "github.com/jackc/pgx/v5/pgtype" ) +type Calendar struct { + ID int32 `json:"id"` + UserID int32 `json:"user_id"` + Name string `json:"name"` + Key string `json:"key"` +} + type Favourite struct { ID int32 `json:"id"` UserID int32 `json:"user_id"` -- cgit v1.2.3-70-g09d2