eintopf.info@v0.13.16/service/revent/store_sql_migrations.go

// Copyright (C) 2022 The Eintopf authors
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as
// published by the Free Software Foundation, either version 3 of the
// License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <https://www.gnu.org/licenses/>.

package revent

import (
	"context"
	"database/sql"
	"fmt"

	"eintopf.info/internal/crud"
	"eintopf.info/service/dbmigration"
)

func (s *SqlStore) runMigrations(ctx context.Context, migrationService dbmigration.Service) error {
	return migrationService.RunMigrations(ctx, []dbmigration.Migration{
		dbmigration.NewMigration("createRepeatingEventsTable", s.createRepeatingEventsTable, nil),
		dbmigration.NewMigration("createRepeatingEventsOrganizerTable", s.createRepeatingEventsOrganizerTable, nil),
		dbmigration.NewMigration("createRepeatingEventsIntervalsTable", s.createRepeatingEventsIntervalsTable, nil),
		dbmigration.NewMigration("createRepeatingEventsTagsTable", s.createRepeatingEventsTagsTable, nil),
		dbmigration.NewMigration("createRepeatingEventsOwnedByTable", s.createRepeatingEventsOwnedByTable, nil),
		dbmigration.NewMigration("moveOrganizerToNewTable", s.moveOrganizerToNewTable, nil),
		dbmigration.NewMigration("removeDateColumnFromIntervalsTable", s.removeDateColumnFromIntervalsTable, nil),
		dbmigration.NewMigration("removeEmptyTags", s.removeEmptyTags, nil),
	})
}

func (s *SqlStore) createRepeatingEventsTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS repeatingEvents (
            id varchar(32) NOT NULL PRIMARY KEY UNIQUE,
            deactivated boolean DEFAULT FALSE,
            name varchar(64) NOT NULL,
            location varchar(64),
            location2 varchar(64),
            description varchar(512),
            start TIMESTAMP,
            end TIMESTAMP,
            image varchar(128)
        );
    `)
	return err
}

func (s *SqlStore) recreateRepeatingEventsTable(ctx context.Context) error {
	repeatingEvents, total, err := s.Find(ctx, nil)
	if err != nil {
		return err
	}
	if len(repeatingEvents) != total {
		return fmt.Errorf("len(repeatingEvents) != total")
	}
	if _, err := s.db.Exec(`DROP TABLE repeatingEvents`); err != nil {
		return err
	}
	if err := s.createRepeatingEventsTable(ctx); err != nil {
		return err
	}
	for _, repeatingEvent := range repeatingEvents {
		err := s.insertRepeatingEvent(ctx, repeatingEvent)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) createRepeatingEventsOrganizerTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS repeatingEvents_organizer (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            repeatingEvent_id varchar(32),
            organizer varchar(64)
        );
    `)
	return err
}

func (s *SqlStore) createRepeatingEventsIntervalsTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS repeatingEvents_interval (
            repeatingEvent_id varchar(32),
            type varchar(32),
            interval integer,
            weekDay integer
        );
    `)
	return err
}

func (s *SqlStore) createRepeatingEventsTagsTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS repeatingEvents_tags (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            repeatingEvent_id varchar(32),
            tag varchar(64)
        );
    `)
	return err
}

func (s *SqlStore) createRepeatingEventsOwnedByTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS repeatingEvents_owned_by (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            repeatingEvent_id varchar(32),
            user_id varchar(32)
        );
    `)
	return err
}

func (s *SqlStore) removeEmptyTags(ctx context.Context) error {
	revents, _, err := s.Find(context.Background(), &crud.FindParams[FindFilters]{})
	if err != nil {
		return err
	}
	for _, e := range revents {
		needsUpdate := false
		for i, tag := range e.Tags {
			if tag == "" {
				needsUpdate = true
				e.Tags = append(e.Tags[:i], e.Tags[i+1:]...)
			}
		}
		if needsUpdate {
			_, err := s.Update(context.Background(), e)
			if err != nil {
				return err
			}
		}
	}
	return err
}

func (s *SqlStore) moveOrganizerToNewTable(ctx context.Context) error {
	rows, err := s.db.QueryxContext(ctx, `
        SELECT id, organizer, organizer2
        FROM repeatingEvents
    `, nil)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil
		}
		// When the organizer or organizer2 column does not exist, just ignore the migration
		if err.Error() == "no such column: organizer" || err.Error() == "no such column: organizer2" {
			return nil
		}
		return fmt.Errorf("select organizer and organizer2: %s", err)
	}
	defer rows.Close()

	type OldRepeatingEvent struct {
		ID         string `db:"id"`
		Organizer  string `db:"organizer"`
		Organizer2 string `db:"organizer2"`
	}

	oldEvents := []OldRepeatingEvent{}
	for rows.Next() {
		oldEvent := OldRepeatingEvent{}
		err := rows.StructScan(&oldEvent)
		if err != nil {
			return err
		}

		oldEvents = append(oldEvents, oldEvent)
	}
	rows.Close()

	for _, oldEvent := range oldEvents {
		organizers := []string{}
		if oldEvent.Organizer != "" {
			organizers = append(organizers, oldEvent.Organizer)
		}
		if oldEvent.Organizer2 != "" {
			organizers = append(organizers, oldEvent.Organizer2)
		}
		err = s.insertOrganizersForRepeatingEvent(ctx, &RepeatingEvent{
			ID:         oldEvent.ID,
			Organizers: organizers,
		})
		if err != nil {
			return fmt.Errorf("insert organizer and organizer2: %s", err)
		}
	}
	if err := s.recreateRepeatingEventsTable(ctx); err != nil {
		return fmt.Errorf("recreateRepeatingEventsTable: %s", err)
	}
	return nil
}

func (s *SqlStore) removeDateColumnFromIntervalsTable(ctx context.Context) error {
	s.db.Exec(`
        ALTER TABLE repeatingEvents_interval
        DROP COLUMN date;
    `)
	return nil
}