Add default board to new projects, remove uncategorized pseudo-board (#29874)

On creation of an empty project (no template) a default board will be
created instead of falling back to the uneditable pseudo-board.

Every project now has to have exactly one default boards. As a
consequence, you cannot unset a board as default, instead you have to
set another board as default. Existing projects will be modified using a
cron job, additionally this check will run every midnight by default.

Deleting the default board is not allowed, you have to set another board
as default to do it.

Fixes #29873
Fixes #14679 along the way
Fixes #29853

Co-authored-by: delvh <dev.lh@web.de>

5 files changed, 180 insertions, 0 deletions

diff --git a/models/migrations/fixtures/Test_CheckProjectColumnsConsistency/project.yml b/models/migrations/fixtures/Test_CheckProjectColumnsConsistency/project.yml
new file mode 100644
index 0000000000..2450d20beb
--- /dev/null
+++ b/models/migrations/fixtures/Test_CheckProjectColumnsConsistency/project.yml
@@ -0,0 +1,23 @@
+-
+  id: 1
+  title: project without default column
+  owner_id: 2
+  repo_id: 0
+  is_closed: false
+  creator_id: 2
+  board_type: 1
+  type: 2
+  created_unix: 1688973000
+  updated_unix: 1688973000
+
+-
+  id: 2
+  title: project with multiple default columns
+  owner_id: 2
+  repo_id: 0
+  is_closed: false
+  creator_id: 2
+  board_type: 1
+  type: 2
+  created_unix: 1688973000
+  updated_unix: 1688973000
diff --git a/models/migrations/fixtures/Test_CheckProjectColumnsConsistency/project_board.yml b/models/migrations/fixtures/Test_CheckProjectColumnsConsistency/project_board.yml
new file mode 100644
index 0000000000..2e1b1c7eee
--- /dev/null
+++ b/models/migrations/fixtures/Test_CheckProjectColumnsConsistency/project_board.yml
@@ -0,0 +1,26 @@
+-
+  id: 1
+  project_id: 1
+  title: Done
+  creator_id: 2
+  default: false
+  created_unix: 1588117528
+  updated_unix: 1588117528
+
+-
+  id: 2
+  project_id: 2
+  title: Backlog
+  creator_id: 2
+  default: true
+  created_unix: 1588117528
+  updated_unix: 1588117528
+
+-
+  id: 3
+  project_id: 2
+  title: Uncategorized
+  creator_id: 2
+  default: true
+  created_unix: 1588117528
+  updated_unix: 1588117528
diff --git a/models/migrations/migrations.go b/models/migrations/migrations.go
index 87fddefb88..77895fba61 100644
--- a/models/migrations/migrations.go
+++ b/models/migrations/migrations.go
@@ -568,6 +568,8 @@ var migrations = []Migration{
 	NewMigration("Add PayloadVersion to HookTask", v1_22.AddPayloadVersionToHookTaskTable),
 	// v291 -> v292
 	NewMigration("Add Index to attachment.comment_id", v1_22.AddCommentIDIndexofAttachment),
+	// v292 -> v293
+	NewMigration("Ensure every project has exactly one default column", v1_22.CheckProjectColumnsConsistency),
 }
 
 // GetCurrentDBVersion returns the current db version
diff --git a/models/migrations/v1_22/v292.go b/models/migrations/v1_22/v292.go
new file mode 100644
index 0000000000..7c051a2b75
--- /dev/null
+++ b/models/migrations/v1_22/v292.go
@@ -0,0 +1,85 @@
+// Copyright 2024 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package v1_22 //nolint
+
+import (
+	"code.gitea.io/gitea/models/project"
+	"code.gitea.io/gitea/modules/setting"
+
+	"xorm.io/builder"
+	"xorm.io/xorm"
+)
+
+// CheckProjectColumnsConsistency ensures there is exactly one default board per project present
+func CheckProjectColumnsConsistency(x *xorm.Engine) error {
+	sess := x.NewSession()
+	defer sess.Close()
+
+	if err := sess.Begin(); err != nil {
+		return err
+	}
+
+	limit := setting.Database.IterateBufferSize
+	if limit <= 0 {
+		limit = 50
+	}
+
+	start := 0
+
+	for {
+		var projects []project.Project
+		if err := sess.SQL("SELECT DISTINCT `p`.`id`, `p`.`creator_id` FROM `project` `p` WHERE (SELECT COUNT(*) FROM `project_board` `pb` WHERE `pb`.`project_id` = `p`.`id` AND `pb`.`default` = ?) != 1", true).
+			Limit(limit, start).
+			Find(&projects); err != nil {
+			return err
+		}
+
+		if len(projects) == 0 {
+			break
+		}
+		start += len(projects)
+
+		for _, p := range projects {
+			var boards []project.Board
+			if err := sess.Where("project_id=? AND `default` = ?", p.ID, true).OrderBy("sorting").Find(&boards); err != nil {
+				return err
+			}
+
+			if len(boards) == 0 {
+				if _, err := sess.Insert(project.Board{
+					ProjectID: p.ID,
+					Default:   true,
+					Title:     "Uncategorized",
+					CreatorID: p.CreatorID,
+				}); err != nil {
+					return err
+				}
+				continue
+			}
+
+			var boardsToUpdate []int64
+			for id, b := range boards {
+				if id > 0 {
+					boardsToUpdate = append(boardsToUpdate, b.ID)
+				}
+			}
+
+			if _, err := sess.Where(builder.Eq{"project_id": p.ID}.And(builder.In("id", boardsToUpdate))).
+				Cols("`default`").Update(&project.Board{Default: false}); err != nil {
+				return err
+			}
+		}
+
+		if start%1000 == 0 {
+			if err := sess.Commit(); err != nil {
+				return err
+			}
+			if err := sess.Begin(); err != nil {
+				return err
+			}
+		}
+	}
+
+	return sess.Commit()
+}
diff --git a/models/migrations/v1_22/v292_test.go b/models/migrations/v1_22/v292_test.go
new file mode 100644
index 0000000000..5e32e0220f
--- /dev/null
+++ b/models/migrations/v1_22/v292_test.go
@@ -0,0 +1,44 @@
+// Copyright 2024 The Gitea Authors. All rights reserved.
+// SPDX-License-Identifier: MIT
+
+package v1_22 //nolint
+
+import (
+	"testing"
+
+	"code.gitea.io/gitea/models/db"
+	"code.gitea.io/gitea/models/migrations/base"
+	"code.gitea.io/gitea/models/project"
+
+	"github.com/stretchr/testify/assert"
+)
+
+func Test_CheckProjectColumnsConsistency(t *testing.T) {
+	// Prepare and load the testing database
+	x, deferable := base.PrepareTestEnv(t, 0, new(project.Project), new(project.Board))
+	defer deferable()
+	if x == nil || t.Failed() {
+		return
+	}
+
+	assert.NoError(t, CheckProjectColumnsConsistency(x))
+
+	// check if default board was added
+	var defaultBoard project.Board
+	has, err := x.Where("project_id=? AND `default` = ?", 1, true).Get(&defaultBoard)
+	assert.NoError(t, err)
+	assert.True(t, has)
+	assert.Equal(t, int64(1), defaultBoard.ProjectID)
+	assert.True(t, defaultBoard.Default)
+
+	// check if multiple defaults were removed
+	expectDefaultBoard, err := project.GetBoard(db.DefaultContext, 2)
+	assert.NoError(t, err)
+	assert.Equal(t, int64(2), expectDefaultBoard.ProjectID)
+	assert.True(t, expectDefaultBoard.Default)
+
+	expectNonDefaultBoard, err := project.GetBoard(db.DefaultContext, 3)
+	assert.NoError(t, err)
+	assert.Equal(t, int64(2), expectNonDefaultBoard.ProjectID)
+	assert.False(t, expectNonDefaultBoard.Default)
+}