eintopf.info@v0.13.16/service/revent/store_sql.go (about)

     1  // Copyright (C) 2022 The Eintopf authors
     2  //
     3  // This program is free software: you can redistribute it and/or modify
     4  // it under the terms of the GNU Affero General Public License as
     5  // published by the Free Software Foundation, either version 3 of the
     6  // License, or (at your option) any later version.
     7  //
     8  // This program is distributed in the hope that it will be useful,
     9  // but WITHOUT ANY WARRANTY; without even the implied warranty of
    10  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    11  // GNU Affero General Public License for more details.
    12  //
    13  // You should have received a copy of the GNU Affero General Public License
    14  // along with this program.  If not, see <https://www.gnu.org/licenses/>.
    15  
    16  package revent
    17  
    18  import (
    19  	"context"
    20  	"database/sql"
    21  	"fmt"
    22  	"strings"
    23  
    24  	"github.com/google/uuid"
    25  	"github.com/jmoiron/sqlx"
    26  
    27  	"eintopf.info/internal/crud"
    28  	"eintopf.info/service/dbmigration"
    29  )
    30  
    31  // NewSqlStore returns a new sql db RepeatingEvent store.
    32  func NewSqlStore(db *sqlx.DB, migrationService dbmigration.Service) (*SqlStore, error) {
    33  	store := &SqlStore{db: db}
    34  	if err := store.runMigrations(context.Background(), migrationService); err != nil {
    35  		return nil, err
    36  	}
    37  	return store, nil
    38  }
    39  
    40  type SqlStore struct {
    41  	db *sqlx.DB
    42  }
    43  
    44  func (s *SqlStore) Create(ctx context.Context, newRepeatingEvent *NewRepeatingEvent) (*RepeatingEvent, error) {
    45  	repeatingEvent := RepeatingEventFromNewRepeatingEvent(newRepeatingEvent, uuid.New().String())
    46  	err := s.insertRepeatingEvent(ctx, repeatingEvent)
    47  	if err != nil {
    48  		return nil, err
    49  	}
    50  
    51  	err = s.insertOrganizersForRepeatingEvent(ctx, repeatingEvent)
    52  	if err != nil {
    53  		return nil, err
    54  	}
    55  	err = s.insertIntervalsForRepeatingEvent(ctx, repeatingEvent)
    56  	if err != nil {
    57  		return nil, err
    58  	}
    59  	err = s.insertTagsForRepeatingEvent(ctx, repeatingEvent)
    60  	if err != nil {
    61  		return nil, err
    62  	}
    63  	err = s.insertOwnedByForRepeatingEvent(ctx, repeatingEvent)
    64  	if err != nil {
    65  		return nil, err
    66  	}
    67  
    68  	return repeatingEvent, nil
    69  }
    70  
    71  func (s *SqlStore) insertRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
    72  	_, err := s.db.NamedExecContext(ctx, `
    73          INSERT INTO repeatingEvents (
    74              id,
    75              deactivated,
    76              name,
    77              location,
    78              location2,
    79              description,
    80              start,
    81              end,
    82              image
    83          ) VALUES (
    84              :id,
    85              :deactivated,
    86              :name,
    87              :location,
    88              :location2,
    89              :description,
    90              :start,
    91              :end,
    92              :image
    93          )
    94      `, repeatingEvent)
    95  	return err
    96  }
    97  
    98  func (s *SqlStore) insertOrganizersForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
    99  	for _, organizer := range repeatingEvent.Organizers {
   100  		_, err := s.db.ExecContext(ctx, `
   101              INSERT INTO repeatingEvents_organizer (
   102                  repeatingEvent_id,
   103                  organizer
   104              ) VALUES (
   105                  $1,
   106                  $2
   107              )
   108          `, repeatingEvent.ID, organizer)
   109  		if err != nil {
   110  			return err
   111  		}
   112  	}
   113  	return nil
   114  }
   115  
   116  func (s *SqlStore) insertTagsForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
   117  	for _, tag := range repeatingEvent.Tags {
   118  		_, err := s.db.ExecContext(ctx, `
   119              INSERT INTO repeatingEvents_tags (
   120                  repeatingEvent_id,
   121                  tag
   122              ) VALUES (
   123                  $1,
   124                  $2
   125              )
   126          `, repeatingEvent.ID, tag)
   127  		if err != nil {
   128  			return err
   129  		}
   130  	}
   131  	return nil
   132  }
   133  
   134  func (s *SqlStore) insertOwnedByForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
   135  	for _, ownedBy := range repeatingEvent.OwnedBy {
   136  		_, err := s.db.ExecContext(ctx, `
   137              INSERT INTO repeatingEvents_owned_by (
   138                  repeatingEvent_id,
   139                  user_id
   140              ) VALUES (
   141                  $1,
   142                  $2
   143              )
   144          `, repeatingEvent.ID, ownedBy)
   145  		if err != nil {
   146  			return err
   147  		}
   148  	}
   149  	return nil
   150  }
   151  
   152  func (s *SqlStore) insertIntervalsForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
   153  	for _, interval := range repeatingEvent.Intervals {
   154  		_, err := s.db.ExecContext(ctx, `
   155              INSERT INTO repeatingEvents_interval (
   156                  repeatingEvent_id,
   157                  type,
   158                  interval,
   159                  weekDay
   160              ) VALUES (
   161                  $1,
   162                  $2,
   163                  $3,
   164                  $4
   165              )
   166              `, repeatingEvent.ID, interval.Type, interval.Interval, interval.WeekDay)
   167  		if err != nil {
   168  			return fmt.Errorf("insert intervals: %s", err)
   169  		}
   170  	}
   171  	return nil
   172  }
   173  
   174  func (s *SqlStore) Update(ctx context.Context, repeatingEvent *RepeatingEvent) (*RepeatingEvent, error) {
   175  	_, err := s.db.NamedExecContext(ctx, `
   176          UPDATE
   177              repeatingEvents
   178          SET
   179              deactivated=:deactivated,
   180              name=:name,
   181              location=:location,
   182              location2=:location2,
   183              description=:description,
   184              start=:start,
   185              end=:end,
   186              image=:image
   187          WHERE
   188              id=:id
   189      `, repeatingEvent)
   190  	if err != nil {
   191  		return nil, err
   192  	}
   193  
   194  	err = s.deleteOrganizersForRepeatingEvent(ctx, repeatingEvent.ID)
   195  	if err != nil {
   196  		return nil, err
   197  	}
   198  	err = s.insertOrganizersForRepeatingEvent(ctx, repeatingEvent)
   199  	if err != nil {
   200  		return nil, err
   201  	}
   202  
   203  	err = s.deleteIntervalsForRepeatingEvent(ctx, repeatingEvent.ID)
   204  	if err != nil {
   205  		return nil, err
   206  	}
   207  	err = s.insertIntervalsForRepeatingEvent(ctx, repeatingEvent)
   208  	if err != nil {
   209  		return nil, err
   210  	}
   211  
   212  	err = s.deleteTagsForRepeatingEvent(ctx, repeatingEvent.ID)
   213  	if err != nil {
   214  		return nil, err
   215  	}
   216  	err = s.insertTagsForRepeatingEvent(ctx, repeatingEvent)
   217  	if err != nil {
   218  		return nil, err
   219  	}
   220  
   221  	err = s.deleteOwnedByForRepeatingEvent(ctx, repeatingEvent.ID)
   222  	if err != nil {
   223  		return nil, err
   224  	}
   225  	err = s.insertOwnedByForRepeatingEvent(ctx, repeatingEvent)
   226  	if err != nil {
   227  		return nil, err
   228  	}
   229  
   230  	return repeatingEvent, err
   231  }
   232  
   233  func (s *SqlStore) Delete(ctx context.Context, id string) error {
   234  	_, err := s.db.ExecContext(ctx, "DELETE FROM repeatingEvents WHERE id = $1", id)
   235  	if err != nil {
   236  		return err
   237  	}
   238  
   239  	err = s.deleteOrganizersForRepeatingEvent(ctx, id)
   240  	if err != nil {
   241  		return err
   242  	}
   243  	err = s.deleteIntervalsForRepeatingEvent(ctx, id)
   244  	if err != nil {
   245  		return err
   246  	}
   247  	err = s.deleteTagsForRepeatingEvent(ctx, id)
   248  	if err != nil {
   249  		return err
   250  	}
   251  	err = s.deleteOwnedByForRepeatingEvent(ctx, id)
   252  	if err != nil {
   253  		return err
   254  	}
   255  
   256  	return nil
   257  }
   258  
   259  func (s *SqlStore) deleteOrganizersForRepeatingEvent(ctx context.Context, id string) error {
   260  	_, err := s.db.ExecContext(ctx, "DELETE FROM repeatingEvents_organizer WHERE repeatingEvent_id = $1", id)
   261  	if err != nil {
   262  		return err
   263  	}
   264  	return nil
   265  }
   266  
   267  func (s *SqlStore) deleteIntervalsForRepeatingEvent(ctx context.Context, id string) error {
   268  	_, err := s.db.ExecContext(ctx, "DELETE FROM repeatingEvents_interval WHERE repeatingEvent_id = $1", id)
   269  	if err != nil {
   270  		return err
   271  	}
   272  	return nil
   273  }
   274  
   275  func (s *SqlStore) deleteTagsForRepeatingEvent(ctx context.Context, id string) error {
   276  	_, err := s.db.ExecContext(ctx, "DELETE FROM repeatingEvents_tags WHERE repeatingEvent_id = $1", id)
   277  	if err != nil {
   278  		return err
   279  	}
   280  	return nil
   281  }
   282  
   283  func (s *SqlStore) deleteOwnedByForRepeatingEvent(ctx context.Context, id string) error {
   284  	_, err := s.db.ExecContext(ctx, "DELETE FROM repeatingEvents_owned_by WHERE repeatingEvent_id = $1", id)
   285  	if err != nil {
   286  		return err
   287  	}
   288  	return nil
   289  }
   290  
   291  func (s *SqlStore) FindByID(ctx context.Context, id string) (*RepeatingEvent, error) {
   292  	repeatingEvent := &RepeatingEvent{}
   293  	err := s.db.GetContext(ctx, repeatingEvent, `
   294          SELECT
   295              id,
   296              deactivated,
   297              name,
   298              location,
   299              location2,
   300              description,
   301              start,
   302              end,
   303              image
   304          FROM
   305              repeatingEvents
   306          WHERE
   307              repeatingEvents.id = $1
   308      `, id)
   309  	if err == sql.ErrNoRows {
   310  		return nil, nil
   311  	}
   312  	if err != nil {
   313  		return nil, err
   314  	}
   315  
   316  	s.findOrganizersForRepeatingEvent(ctx, repeatingEvent)
   317  	if err != nil {
   318  		return nil, err
   319  	}
   320  	s.findIntervalsForRepeatingEvent(ctx, repeatingEvent)
   321  	if err != nil {
   322  		return nil, err
   323  	}
   324  	s.findTagsForRepeatingEvent(ctx, repeatingEvent)
   325  	if err != nil {
   326  		return nil, err
   327  	}
   328  	s.findOwnedByForRepeatingEvent(ctx, repeatingEvent)
   329  	if err != nil {
   330  		return nil, err
   331  	}
   332  
   333  	return repeatingEvent, nil
   334  }
   335  
   336  func (s *SqlStore) findOrganizersForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
   337  	organizers := []string{}
   338  	err := s.db.SelectContext(ctx, &organizers, `
   339          SELECT organizer
   340          FROM repeatingEvents_organizer
   341          WHERE repeatingEvent_id = $1
   342      `, repeatingEvent.ID)
   343  	if err == sql.ErrNoRows {
   344  		return nil
   345  	}
   346  	if err != nil {
   347  		return err
   348  	}
   349  	repeatingEvent.Organizers = organizers
   350  	return nil
   351  }
   352  
   353  func (s *SqlStore) findIntervalsForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
   354  	intervals := []Interval{}
   355  	err := s.db.SelectContext(ctx, &intervals, `
   356          SELECT type, interval, weekDay
   357          FROM repeatingEvents_interval
   358          WHERE repeatingEvent_id = $1
   359      `, repeatingEvent.ID)
   360  	if err == sql.ErrNoRows {
   361  		return nil
   362  	}
   363  	if err != nil {
   364  		return err
   365  	}
   366  	repeatingEvent.Intervals = intervals
   367  	return nil
   368  }
   369  
   370  func (s *SqlStore) findTagsForRepeatingEvent(ctx context.Context, repeatingEvent *RepeatingEvent) error {
   371  	tags := []string{}
   372  	err := s.db.SelectContext(ctx, &tags, `
   373          SELECT tag
   374          FROM repeatingEvents_tags
   375          WHERE repeatingEvent_id = $1
   376      `, repeatingEvent.ID)
   377  	if err == sql.ErrNoRows {
   378  		return nil
   379  	}
   380  	if err != nil {
   381  		return err
   382  	}
   383  	repeatingEvent.Tags = tags
   384  	return nil
   385  }
   386  
   387  func (s *SqlStore) findOwnedByForRepeatingEvent(ctx context.Context, RepeatingEvent *RepeatingEvent) error {
   388  	ownedBy := []string{}
   389  	err := s.db.SelectContext(ctx, &ownedBy, `
   390          SELECT user_id
   391          FROM repeatingEvents_owned_by
   392          WHERE repeatingEvent_id = $1
   393      `, RepeatingEvent.ID)
   394  	if err == sql.ErrNoRows {
   395  		return nil
   396  	}
   397  	if err != nil {
   398  		return err
   399  	}
   400  	RepeatingEvent.OwnedBy = ownedBy
   401  	return nil
   402  }
   403  
   404  var sortableFields = map[string]string{
   405  	"id":          "id",
   406  	"deactivated": "deactivated",
   407  	"name":        "name",
   408  	"location":    "location",
   409  	"start":       "start",
   410  	"end":         "end",
   411  }
   412  
   413  func (s *SqlStore) Find(ctx context.Context, params *crud.FindParams[FindFilters]) ([]*RepeatingEvent, int, error) {
   414  	query := `
   415          SELECT
   416              repeatingEvents.id,
   417              repeatingEvents.deactivated,
   418              repeatingEvents.name,
   419              repeatingEvents.location,
   420              repeatingEvents.location2,
   421              repeatingEvents.description,
   422              repeatingEvents.start,
   423              repeatingEvents.end,
   424              repeatingEvents.image
   425          FROM
   426              repeatingEvents
   427      `
   428  	joinQuery := ""
   429  	whereStatements := []string{}
   430  	sqlParams := make(map[string]interface{})
   431  	if params != nil {
   432  		if params.Filters != nil {
   433  			if params.Filters.ID != nil {
   434  				whereStatements = append(whereStatements, "repeatingEvents.id=:id")
   435  				sqlParams["id"] = params.Filters.ID
   436  			}
   437  			if params.Filters.Deactivated != nil {
   438  				whereStatements = append(whereStatements, "repeatingEvents.deactivated=:deactivated")
   439  				sqlParams["deactivated"] = params.Filters.Deactivated
   440  			}
   441  			if params.Filters.Name != nil {
   442  				whereStatements = append(whereStatements, "repeatingEvents.name=:name")
   443  				sqlParams["name"] = params.Filters.Name
   444  			}
   445  			if params.Filters.Organizers != nil {
   446  				joinQuery += " JOIN repeatingEvents_organizer ON repeatingEvents.id = repeatingEvents_organizer.repeatingEvent_id"
   447  				organizerStatements := make([]string, len(params.Filters.Organizers))
   448  				for i, organizer := range params.Filters.Organizers {
   449  					organizerRef := fmt.Sprintf("tag%d", i)
   450  					organizerStatements[i] = fmt.Sprintf("repeatingEvents_organizer.tag=:%s", organizerRef)
   451  					sqlParams[organizerRef] = organizer
   452  				}
   453  				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(organizerStatements, " OR ")))
   454  			}
   455  			if params.Filters.Location != nil {
   456  				whereStatements = append(whereStatements, "repeatingEvents.location=:location")
   457  				sqlParams["Location"] = params.Filters.Location
   458  			}
   459  			if params.Filters.Location2 != nil {
   460  				whereStatements = append(whereStatements, "repeatingEvents.location2=:location2")
   461  				sqlParams["Location2"] = params.Filters.Location2
   462  			}
   463  			if params.Filters.Description != nil {
   464  				whereStatements = append(whereStatements, "repeatingEvents.description=:description")
   465  				sqlParams["Description"] = params.Filters.Description
   466  			}
   467  			if params.Filters.Start != nil {
   468  				whereStatements = append(whereStatements, "repeatingEvents.start=:start")
   469  				sqlParams["start"] = params.Filters.Start
   470  			}
   471  			if params.Filters.End != nil {
   472  				whereStatements = append(whereStatements, "repeatingEvents.end=:end")
   473  				sqlParams["end"] = params.Filters.End
   474  			}
   475  			if params.Filters.Tags != nil {
   476  				joinQuery += " JOIN repeatingEvents_tags ON repeatingEvents.id = repeatingEvents_tags.repeatingEvent_id"
   477  				tagStatemtes := make([]string, len(params.Filters.Tags))
   478  				for i, tag := range params.Filters.Tags {
   479  					tagRef := fmt.Sprintf("tag%d", i)
   480  					tagStatemtes[i] = fmt.Sprintf("repeatingEvents_tags.tag=:%s", tagRef)
   481  					sqlParams[tagRef] = tag
   482  				}
   483  				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(tagStatemtes, " OR ")))
   484  			}
   485  			if params.Filters.OwnedBy != nil {
   486  				joinQuery += " JOIN repeatingEvents_owned_by ON repeatingEvents.id = repeatingEvents_owned_by.repeatingEvent_id"
   487  				ownedByStatements := make([]string, len(params.Filters.OwnedBy))
   488  				for i, ownedBy := range params.Filters.OwnedBy {
   489  					ownedByRef := fmt.Sprintf("ownedBy%d", i)
   490  					ownedByStatements[i] = fmt.Sprintf("repeatingEvents_owned_by.user_id=:%s", ownedByRef)
   491  					sqlParams[ownedByRef] = ownedBy
   492  				}
   493  				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(ownedByStatements, " OR ")))
   494  			}
   495  
   496  			if joinQuery != "" {
   497  				query += " " + joinQuery
   498  			}
   499  			if len(whereStatements) > 0 {
   500  				query += " WHERE " + strings.Join(whereStatements, " AND ")
   501  			}
   502  			if joinQuery != "" {
   503  				query += " GROUP BY repeatingEvents.id"
   504  			}
   505  		}
   506  
   507  		if params.Sort != "" {
   508  			sort, ok := sortableFields[params.Sort]
   509  			if !ok {
   510  				return nil, 0, fmt.Errorf("find repeatingEvents: invalid sort field: %s", params.Sort)
   511  			}
   512  			order := "ASC"
   513  			if params.Order == "DESC" {
   514  				order = "DESC"
   515  			}
   516  			query += fmt.Sprintf(" ORDER BY %s %s", sort, order)
   517  		}
   518  		if params.Limit > 0 {
   519  			query += fmt.Sprintf(" LIMIT %d", params.Limit)
   520  		}
   521  		if params.Offset > 0 {
   522  			query += fmt.Sprintf(" OFFSET %d", params.Offset)
   523  		}
   524  	}
   525  	rows, err := s.db.NamedQueryContext(ctx, query, sqlParams)
   526  	if err != nil && err != sql.ErrNoRows {
   527  		return nil, 0, fmt.Errorf("find repeatingEvents: %s", err)
   528  	}
   529  	defer rows.Close()
   530  	repeatingEvents := make([]*RepeatingEvent, 0)
   531  	for rows.Next() {
   532  		repeatingEvent := RepeatingEvent{}
   533  		rows.StructScan(&repeatingEvent)
   534  
   535  		repeatingEvents = append(repeatingEvents, &repeatingEvent)
   536  	}
   537  
   538  	for i, repeatingEvent := range repeatingEvents {
   539  		err = s.findOrganizersForRepeatingEvent(ctx, repeatingEvent)
   540  		if err != nil {
   541  			return nil, 0, err
   542  		}
   543  		err = s.findIntervalsForRepeatingEvent(ctx, repeatingEvent)
   544  		if err != nil {
   545  			return nil, 0, err
   546  		}
   547  		err := s.findTagsForRepeatingEvent(ctx, repeatingEvent)
   548  		if err != nil {
   549  			return nil, 0, err
   550  		}
   551  		err = s.findOwnedByForRepeatingEvent(ctx, repeatingEvent)
   552  		if err != nil {
   553  			return nil, 0, err
   554  		}
   555  		repeatingEvents[i] = repeatingEvent
   556  	}
   557  
   558  	totalQuery := `
   559          SELECT COUNT(*) as total
   560          FROM (
   561              SELECT repeatingEvents.id
   562              FROM repeatingEvents
   563      `
   564  	if len(whereStatements) > 0 {
   565  		if joinQuery != "" {
   566  			totalQuery += " " + joinQuery
   567  		}
   568  		totalQuery += " WHERE " + strings.Join(whereStatements, " AND ")
   569  		if joinQuery != "" {
   570  			totalQuery += " GROUP BY repeatingEvents.id"
   571  		}
   572  	}
   573  	totalQuery += ")"
   574  
   575  	totalRepeatingEvents := struct {
   576  		Total int `db:"total"`
   577  	}{}
   578  	rows2, err := s.db.NamedQueryContext(ctx, totalQuery, sqlParams)
   579  	if err != nil && err != sql.ErrNoRows {
   580  		return nil, 0, fmt.Errorf("find repeatingEvents: total: %s", err)
   581  	}
   582  	defer rows2.Close()
   583  	if rows2.Next() {
   584  		rows2.StructScan(&totalRepeatingEvents)
   585  	}
   586  
   587  	return repeatingEvents, totalRepeatingEvents.Total, nil
   588  }