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 }