code.gitea.io/gitea@v1.22.3/tests/integration/db_collation_test.go (about)

     1  // Copyright 2023 The Gitea Authors. All rights reserved.
     2  // SPDX-License-Identifier: MIT
     3  
     4  package integration
     5  
     6  import (
     7  	"testing"
     8  
     9  	"code.gitea.io/gitea/models/db"
    10  	"code.gitea.io/gitea/modules/setting"
    11  	"code.gitea.io/gitea/modules/test"
    12  
    13  	"github.com/stretchr/testify/assert"
    14  	"xorm.io/xorm"
    15  )
    16  
    17  type TestCollationTbl struct {
    18  	ID  int64
    19  	Txt string `xorm:"VARCHAR(10) UNIQUE"`
    20  }
    21  
    22  func TestDatabaseCollation(t *testing.T) {
    23  	x := db.GetEngine(db.DefaultContext).(*xorm.Engine)
    24  
    25  	// all created tables should use case-sensitive collation by default
    26  	_, _ = x.Exec("DROP TABLE IF EXISTS test_collation_tbl")
    27  	err := x.Sync(&TestCollationTbl{})
    28  	assert.NoError(t, err)
    29  	_, _ = x.Exec("INSERT INTO test_collation_tbl (txt) VALUES ('main')")
    30  	_, _ = x.Exec("INSERT INTO test_collation_tbl (txt) VALUES ('Main')") // case-sensitive, so it inserts a new row
    31  	_, _ = x.Exec("INSERT INTO test_collation_tbl (txt) VALUES ('main')") // duplicate, so it doesn't insert
    32  	cnt, err := x.Count(&TestCollationTbl{})
    33  	assert.NoError(t, err)
    34  	assert.EqualValues(t, 2, cnt)
    35  	_, _ = x.Exec("DROP TABLE IF EXISTS test_collation_tbl")
    36  
    37  	// by default, SQLite3 and PostgreSQL are using case-sensitive collations, but MySQL and MSSQL are not
    38  	// the following tests are only for MySQL and MSSQL
    39  	if !setting.Database.Type.IsMySQL() && !setting.Database.Type.IsMSSQL() {
    40  		t.Skip("only MySQL and MSSQL requires the case-sensitive collation check at the moment")
    41  		return
    42  	}
    43  
    44  	t.Run("Default startup makes database collation case-sensitive", func(t *testing.T) {
    45  		r, err := db.CheckCollations(x)
    46  		assert.NoError(t, err)
    47  		assert.True(t, r.IsCollationCaseSensitive(r.DatabaseCollation))
    48  		assert.True(t, r.CollationEquals(r.ExpectedCollation, r.DatabaseCollation))
    49  		assert.NotEmpty(t, r.AvailableCollation)
    50  		assert.Empty(t, r.InconsistentCollationColumns)
    51  
    52  		// and by the way test the helper functions
    53  		if setting.Database.Type.IsMySQL() {
    54  			assert.True(t, r.IsCollationCaseSensitive("utf8mb4_bin"))
    55  			assert.True(t, r.IsCollationCaseSensitive("utf8mb4_xxx_as_cs"))
    56  			assert.False(t, r.IsCollationCaseSensitive("utf8mb4_general_ci"))
    57  			assert.True(t, r.CollationEquals("abc", "abc"))
    58  			assert.True(t, r.CollationEquals("abc", "utf8mb4_abc"))
    59  			assert.False(t, r.CollationEquals("utf8mb4_general_ci", "utf8mb4_unicode_ci"))
    60  		} else if setting.Database.Type.IsMSSQL() {
    61  			assert.True(t, r.IsCollationCaseSensitive("Latin1_General_CS_AS"))
    62  			assert.False(t, r.IsCollationCaseSensitive("Latin1_General_CI_AS"))
    63  			assert.True(t, r.CollationEquals("abc", "abc"))
    64  			assert.False(t, r.CollationEquals("Latin1_General_CS_AS", "SQL_Latin1_General_CP1_CS_AS"))
    65  		} else {
    66  			assert.Fail(t, "unexpected database type")
    67  		}
    68  	})
    69  
    70  	if setting.Database.Type.IsMSSQL() {
    71  		return // skip table converting tests because MSSQL doesn't have a simple solution at the moment
    72  	}
    73  
    74  	t.Run("Convert tables to utf8mb4_bin", func(t *testing.T) {
    75  		defer test.MockVariableValue(&setting.Database.CharsetCollation, "utf8mb4_bin")()
    76  		assert.NoError(t, db.ConvertDatabaseTable())
    77  		r, err := db.CheckCollations(x)
    78  		assert.NoError(t, err)
    79  		assert.Equal(t, "utf8mb4_bin", r.DatabaseCollation)
    80  		assert.True(t, r.CollationEquals(r.ExpectedCollation, r.DatabaseCollation))
    81  		assert.Empty(t, r.InconsistentCollationColumns)
    82  
    83  		_, _ = x.Exec("DROP TABLE IF EXISTS test_tbl")
    84  		_, err = x.Exec("CREATE TABLE test_tbl (txt varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL)")
    85  		assert.NoError(t, err)
    86  		r, err = db.CheckCollations(x)
    87  		assert.NoError(t, err)
    88  		assert.Contains(t, r.InconsistentCollationColumns, "test_tbl.txt")
    89  	})
    90  
    91  	t.Run("Convert tables to utf8mb4_general_ci", func(t *testing.T) {
    92  		defer test.MockVariableValue(&setting.Database.CharsetCollation, "utf8mb4_general_ci")()
    93  		assert.NoError(t, db.ConvertDatabaseTable())
    94  		r, err := db.CheckCollations(x)
    95  		assert.NoError(t, err)
    96  		assert.Equal(t, "utf8mb4_general_ci", r.DatabaseCollation)
    97  		assert.True(t, r.CollationEquals(r.ExpectedCollation, r.DatabaseCollation))
    98  		assert.Empty(t, r.InconsistentCollationColumns)
    99  
   100  		_, _ = x.Exec("DROP TABLE IF EXISTS test_tbl")
   101  		_, err = x.Exec("CREATE TABLE test_tbl (txt varchar(10) COLLATE utf8mb4_bin NOT NULL)")
   102  		assert.NoError(t, err)
   103  		r, err = db.CheckCollations(x)
   104  		assert.NoError(t, err)
   105  		assert.Contains(t, r.InconsistentCollationColumns, "test_tbl.txt")
   106  	})
   107  
   108  	t.Run("Convert tables to default case-sensitive collation", func(t *testing.T) {
   109  		defer test.MockVariableValue(&setting.Database.CharsetCollation, "")()
   110  		assert.NoError(t, db.ConvertDatabaseTable())
   111  		r, err := db.CheckCollations(x)
   112  		assert.NoError(t, err)
   113  		assert.True(t, r.IsCollationCaseSensitive(r.DatabaseCollation))
   114  		assert.True(t, r.CollationEquals(r.ExpectedCollation, r.DatabaseCollation))
   115  		assert.Empty(t, r.InconsistentCollationColumns)
   116  	})
   117  }