scratchdata · breadchris · Apr 17, 2024 · Apr 18, 2024 · Apr 19, 2024 · Apr 19, 2024
diff --git a/go.mod b/go.mod
@@ -99,6 +99,8 @@ require (
 	github.com/gorilla/schema v1.3.0 // indirect
 	github.com/gorilla/securecookie v1.1.2 // indirect
 	github.com/gorilla/sessions v1.2.2 // indirect
+	github.com/gosimple/slug v1.14.0 // indirect
+	github.com/gosimple/unidecode v1.0.1 // indirect
 	github.com/jackc/pgpassfile v1.0.0 // indirect
 	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
 	github.com/jackc/pgx/v5 v5.4.3 // indirect

diff --git a/go.sum b/go.sum
@@ -206,6 +206,10 @@ github.com/gorilla/securecookie v1.1.2 h1:YCIWL56dvtr73r6715mJs5ZvhtnY73hBvEF8kX
 github.com/gorilla/securecookie v1.1.2/go.mod h1:NfCASbcHqRSY+3a8tlWJwsQap2VX5pwzwo4h3eOamfo=
 github.com/gorilla/sessions v1.2.2 h1:lqzMYz6bOfvn2WriPUjNByzeXIlVzURcPmgMczkmTjY=
 github.com/gorilla/sessions v1.2.2/go.mod h1:ePLdVu+jbEgHH+KWw8I1z2wqd0BAdAQh/8LRvBeoNcQ=
+github.com/gosimple/slug v1.14.0 h1:RtTL/71mJNDfpUbCOmnf/XFkzKRtD6wL6Uy+3akm4Es=
+github.com/gosimple/slug v1.14.0/go.mod h1:UiRaFH+GEilHstLUmcBgWcI42viBN7mAb818JrYOeFQ=
+github.com/gosimple/unidecode v1.0.1 h1:hZzFTMMqSswvf0LBJZCZgThIZrpDHFXux9KeGmn6T/o=
+github.com/gosimple/unidecode v1.0.1/go.mod h1:CP0Cr1Y1kogOtx0bJblKzsVWrqYaqfNOnHzpgWw4Awc=
 github.com/ilyakaznacheev/cleanenv v1.5.0 h1:0VNZXggJE2OYdXE87bfSSwGxeiGt9moSR2lOrsHHvr4=
 github.com/ilyakaznacheev/cleanenv v1.5.0/go.mod h1:a5aDzaJrLCQZsazHol1w8InnDcOX0OColm64SlIi6gk=
 github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=

diff --git a/pkg/api/data.go b/pkg/api/data.go
@@ -84,12 +84,76 @@ func (a *ScratchDataAPIStruct) Select(w http.ResponseWriter, r *http.Request) {
 		return
 	}
 
-	if err := a.executeQueryAndStreamData(r.Context(), w, query, databaseID, format); err != nil {
+	if err := a.executeQueryAndStreamData(r.Context(), w, query, databaseID, format, nil); err != nil {
 		http.Error(w, err.Error(), http.StatusInternalServerError)
 	}
 }
 
-func (a *ScratchDataAPIStruct) executeQueryAndStreamData(ctx context.Context, w http.ResponseWriter, query string, databaseID int64, format string) error {
+func (a *ScratchDataAPIStruct) SelectSavedQuery(w http.ResponseWriter, r *http.Request) {
+	apiKey, ok := r.Context().Value("apiKeyDetails").(models.APIKey)
+	if !ok {
+		http.Error(w, "not authorized", http.StatusUnauthorized)
+		return
+	}
+
+	teamId := a.AuthGetTeamID(r.Context())
+
+	slug := chi.URLParam(r, "slug")
+	query, ok := a.storageServices.Database.GetSavedQuery(r.Context(), teamId, slug)
+	if !ok {
+		http.Error(w, "not authorized", http.StatusUnauthorized)
+		return
+	}
+
+	// TODO breadchris this should be in the database query
+	var savedKey models.SavedQueryAPIKey
+	for _, key := range query.SavedQueryAPIKeys {
+		if key.APIKeyID == apiKey.ID {
+			savedKey = key
+			break
+		}
+	}
+	if savedKey.ID == 0 {
+		http.Error(w, "not authorized", http.StatusUnauthorized)
+		return
+	}
+
+	params := map[string]any{}
+	for k, v := range savedKey.QueryParams {
+		s, ok := v.(string)
+		if !ok {
+			http.Error(w, "invalid query params", http.StatusBadRequest)
+			return
+		}
+		params[strings.ToLower(k)] = s
+	}
+
+	for k, v := range r.URL.Query() {
+		if k == "api_key" {
+			continue
+		}
+		if _, ok := params[strings.ToLower(k)]; ok {
+			http.Error(w, "invalid query params", http.StatusBadRequest)
+			return
+		}
+		params[strings.ToLower(k)] = v[0]
+	}
+
+	format := r.URL.Query().Get("format")
+
+	if err := a.executeQueryAndStreamData(r.Context(), w, query.Query, query.DestinationID, format, params); err != nil {
+		http.Error(w, err.Error(), http.StatusInternalServerError)
+	}
+}
+
+func (a *ScratchDataAPIStruct) executeQueryAndStreamData(
+	ctx context.Context,
+	w http.ResponseWriter,
+	query string,
+	databaseID int64,
+	format string,
+	params map[string]any,
+) error {
 	dest, err := a.destinationManager.Destination(ctx, databaseID)
 	if err != nil {
 		return err
@@ -98,10 +162,10 @@ func (a *ScratchDataAPIStruct) executeQueryAndStreamData(ctx context.Context, w
 	switch strings.ToLower(format) {
 	case "csv":
 		w.Header().Set("Content-Type", "text/csv")
-		return dest.QueryCSV(query, w)
+		return dest.QueryCSV(query, w, params)
 	default:
 		w.Header().Set("Content-Type", "application/json")
-		return dest.QueryJSON(query, w)
+		return dest.QueryJSON(query, w, params)
 	}
 }
 

diff --git a/pkg/api/destinations.go b/pkg/api/destinations.go
@@ -15,8 +15,7 @@ import (
 func (a *ScratchDataAPIStruct) AddAPIKey(w http.ResponseWriter, r *http.Request) {
 	key := uuid.New().String()
 	destId := a.AuthGetDatabaseID(r.Context())
-	hashedKey := a.storageServices.Database.Hash(key)
-	a.storageServices.Database.AddAPIKey(r.Context(), int64(destId), hashedKey)
+	a.storageServices.Database.AddAPIKey(r.Context(), int64(destId), key)
 
 	render.JSON(w, r, render.M{"key": key, "destination_id": destId})
 }

diff --git a/pkg/api/router.go b/pkg/api/router.go
@@ -44,6 +44,7 @@ func CreateMux(
 
 	api := chi.NewRouter()
 	api.Use(apiFunctions.AuthMiddleware)
+	api.Get("/query/{slug}", apiFunctions.SelectSavedQuery)
 	api.Post("/data/insert/{table}", apiFunctions.Insert)
 	api.Get("/data/query", apiFunctions.Select)
 	api.Post("/data/query", apiFunctions.Select)

diff --git a/pkg/api/share.go b/pkg/api/share.go
@@ -8,6 +8,7 @@ import (
 	"github.com/go-chi/chi/v5"
 	"github.com/go-chi/render"
 	"github.com/google/uuid"
+	"github.com/scratchdata/scratchdata/pkg/storage/database/models"
 )
 
 type CachedQueryData struct {
@@ -41,16 +42,26 @@ func (a *ScratchDataAPIStruct) CreateQuery(w http.ResponseWriter, r *http.Reques
 		return
 	}
 
+	teamId := a.AuthGetTeamID(r.Context())
 	destId := a.AuthGetDatabaseID(r.Context())
 	expires := time.Duration(requestBody.Duration) * time.Second
-	sharedQueryId, err := a.storageServices.Database.CreateShareQuery(r.Context(), destId, requestBody.Name, requestBody.Query, expires)
+	sq := models.NewSavedQuery(
+		teamId,
+		uint(destId),
+		requestBody.Name,
+		requestBody.Query,
+		expires,
+		true,
+		"",
+	)
+	q, err := a.storageServices.Database.UpsertSavedQuery(r.Context(), sq)
 	if err != nil {
 		w.WriteHeader(http.StatusBadRequest)
 		w.Write([]byte(err.Error()))
 		return
 	}
 
-	render.JSON(w, r, render.M{"id": sharedQueryId.String()})
+	render.JSON(w, r, render.M{"id": q.UUID})
 }
 
 func (a *ScratchDataAPIStruct) ShareData(w http.ResponseWriter, r *http.Request) {
@@ -63,13 +74,13 @@ func (a *ScratchDataAPIStruct) ShareData(w http.ResponseWriter, r *http.Request)
 		return
 	}
 
-	cachedQuery, found := a.storageServices.Database.GetShareQuery(r.Context(), id)
+	cachedQuery, found := a.storageServices.Database.GetPublicQuery(r.Context(), id)
 	if !found {
 		http.Error(w, "Query not found", http.StatusNotFound)
 		return
 	}
 
-	if err := a.executeQueryAndStreamData(r.Context(), w, cachedQuery.Query, cachedQuery.DestinationID, format); err != nil {
+	if err := a.executeQueryAndStreamData(r.Context(), w, cachedQuery.Query, cachedQuery.DestinationID, format, nil); err != nil {
 		http.Error(w, err.Error(), http.StatusInternalServerError)
 	}
 }
diff --git a/pkg/connections/service.go b/pkg/connections/service.go
@@ -9,6 +9,7 @@ import (
 
 	"github.com/google/uuid"
 	"github.com/gorilla/schema"
+	"github.com/gosimple/slug"
 	"github.com/rs/zerolog/log"
 	"github.com/scratchdata/scratchdata/pkg/config"
 	"github.com/scratchdata/scratchdata/pkg/destinations"
@@ -137,8 +138,7 @@ func (s *Service) NewKey(ctx context.Context, r *NewKeyRequest) (*NewKeyResponse
 	}
 
 	key := uuid.New().String()
-	hashedKey := s.storageServices.Database.Hash(key)
-	err = s.storageServices.Database.AddAPIKey(ctx, int64(dest.ID), hashedKey)
+	err = s.storageServices.Database.AddAPIKey(ctx, int64(dest.ID), key)
 	if err != nil {
 		return nil, err
 	}
@@ -286,3 +286,188 @@ func (s *Service) UpdateConnection(ctx context.Context, r *UpdateConnectionReque
 	}
 	return res, nil
 }
+
+type GetQueriesRequest struct {
+}
+
+type Query struct {
+	ID       uint
+	Name     string
+	Method   string
+	Endpoint string
+	Database string
+}
+
+type GetQueriesResponse struct {
+	Queries []Query
+}
+
+func (s *Service) GetQueries(ctx context.Context, r *GetQueriesRequest) (*GetQueriesResponse, error) {
+	teamID, err := s.getTeamId(ctx)
+	if err != nil {
+		return nil, err
+	}
+
+	queries := s.storageServices.Database.GetSavedQueries(ctx, teamID)
+
+	res := &GetQueriesResponse{}
+	for _, q := range queries {
+		res.Queries = append(res.Queries, Query{
+			ID:   q.ID,
+			Name: q.Name,
+			// TODO breadchris method
+			Method:   "GET",
+			Endpoint: fmt.Sprintf("/api/query/%s", q.Slug),
+			Database: q.Destination.Name,
+		})
+	}
+	return res, nil
+}
+
+type QueryParam struct {
+	Name         string
+	Type         string
+	ExampleValue string
+	Description  string
+}
+
+type FieldType struct {
+	Value string
+	Name  string
+}
+
+type NewQueryRequest struct {
+	ID uint
+}
+
+type NewQueryResponse struct {
+	SavedQuery   models.SavedQuery
+	Destinations []models.Destination
+}
+
+func (s *Service) NewQuery(ctx context.Context, r *NewQueryRequest) (*NewQueryResponse, error) {
+	teamId, err := s.getTeamId(ctx)
+	if err != nil {
+		return nil, err
+	}
+
+	var q models.SavedQuery
+	if r.ID != 0 {
+		q, err = s.storageServices.Database.GetSavedQueryByID(ctx, teamId, r.ID)
+		if err != nil {
+			return nil, err
+		}
+	}
+
+	dests, err := s.storageServices.Database.GetDestinations(ctx, teamId)
+	if err != nil {
+		return nil, err
+	}
+
+	res := &NewQueryResponse{
+		SavedQuery:   q,
+		Destinations: dests,
+	}
+	// TODO breadchris move this to view
+	if q.ID == 0 {
+		res.SavedQuery.Query = "SELECT * FROM events WHERE user = $user"
+	}
+
+	return res, nil
+}
+
+type UpsertQueryRequest struct {
+	ID     uint
+	DestID uint
+	Name   string
+	Query  string
+	Public bool
+}
+
+type UpsertQueryResponse struct {
+	URL string
+}
+
+func (s *Service) UpsertQuery(ctx context.Context, r *UpsertQueryRequest) (*UpsertQueryResponse, error) {
+	teamId, err := s.getTeamId(ctx)
+	if err != nil {
+		return nil, err
+	}
+
+	// check if the authenticated team has access to the destination
+	_, err = s.storageServices.Database.GetDestination(ctx, teamId, r.DestID)
+	if err != nil {
+		return nil, err
+	}
+
+	var (
+		querySlug string
+		sq        models.SavedQuery
+	)
+	if r.ID != 0 {
+		sq, err = s.storageServices.Database.GetSavedQueryByID(ctx, teamId, r.ID)
+		if err != nil {
+			return nil, err
+		}
+		sq.Name = r.Name
+		sq.Query = r.Query
+		sq.IsPublic = r.Public
+	} else {
+		querySlug = slug.Make(r.Name)
+		_, ok := s.storageServices.Database.GetSavedQuery(ctx, teamId, querySlug)
+		if ok {
+			return nil, errors.New("query name already exists")
+		}
+		sq = models.NewSavedQuery(
+			teamId,
+			r.DestID,
+			r.Name,
+			r.Query,
+			0,
+			r.Public,
+			querySlug,
+		)
+	}
+
+	q, err := s.storageServices.Database.UpsertSavedQuery(ctx, sq)
+	if err != nil {
+		return nil, err
+	}
+
+	if r.ID == 0 {
+		key := uuid.New().String()
+		err = s.storageServices.Database.CreateSavedQueryAPIKey(ctx, q.ID, r.DestID, key, datatypes.JSONMap{})
+		if err != nil {
+			return nil, err
+		}
+
+		return &UpsertQueryResponse{
+			URL: fmt.Sprintf(
+				"%s/api/query/%s?api_key=%s",
+				s.c.ExternalURL,
+				sq.Slug,
+				key,
+			),
+		}, nil
+	}
+	return &UpsertQueryResponse{}, nil
+}
+
+type DeleteQueryRequest struct {
+	ID uint
+}
+
+type DeleteQueryResponse struct{}
+
+func (s *Service) DeleteQuery(ctx context.Context, r *DeleteQueryRequest) (*DeleteQueryResponse, error) {
+	teamId, err := s.getTeamId(ctx)
+	if err != nil {
+		return nil, err
+	}
+
+	err = s.storageServices.Database.DeleteSavedQuery(ctx, teamId, r.ID)
+	if err != nil {
+		return nil, err
+	}
+	return &DeleteQueryResponse{}, nil
+}
diff --git a/pkg/connections/upsert.go b/pkg/connections/upsert.go
@@ -117,8 +117,7 @@ func (s *Service) ConnUpsert(ctx context.Context, req *ConnUpsertRequest) (*Conn
 	}
 
 	res.APIKey = uuid.New().String()
-	hashedKey := s.storageServices.Database.Hash(res.APIKey)
-	err = s.storageServices.Database.AddAPIKey(ctx, int64(dest.ID), hashedKey)
+	err = s.storageServices.Database.AddAPIKey(ctx, int64(dest.ID), res.APIKey)
 	if err != nil {
 		return nil, NewFormError("Failed to create destination", err.Error(), res)
 	}