rilldata · grahamplata · Aug 6, 2025 · Aug 6, 2025 · Aug 6, 2025 · Aug 7, 2025
diff --git a/admin/provisioner/clickhousestatic/provisioner.go b/admin/provisioner/clickhousestatic/provisioner.go
@@ -9,6 +9,7 @@ import (
 	"io"
 	"net/url"
 	"os"
+	"regexp"
 	"strings"
 
 	"github.com/ClickHouse/clickhouse-go/v2"
@@ -17,6 +18,8 @@ import (
 	"go.uber.org/zap"
 )
 
+var nonAlphanumericRegexp = regexp.MustCompile(`[^a-zA-Z0-9]+`)
+
 func init() {
 	provisioner.Register("clickhouse-static", New)
 }
@@ -130,16 +133,8 @@ func (p *Provisioner) Provision(ctx context.Context, r *provisioner.Resource, op
 	}
 
 	// Prepare for creating the schema and user.
-	id := strings.ReplaceAll(r.ID, "-", "")
-	user := fmt.Sprintf("rill_%s", id)
-	dbName := fmt.Sprintf("rill_%s", id)
-
-	// Use org and project names to create a more human-readable database name.
-	orgName := sanitizeName(getAnnotationValue(opts.Annotations, "organization_name"))
-	projectName := sanitizeName(getAnnotationValue(opts.Annotations, "project_name"))
-	if orgName != "" && projectName != "" {
-		dbName = fmt.Sprintf("rill_%s_%s_%s", orgName, projectName, id)
-	}
+	user := fmt.Sprintf("rill_%s", nonAlphanumericRegexp.ReplaceAllString(r.ID, ""))
+	dbName := generateDatabaseName(r.ID, opts.Annotations)
 
 	password := newPassword()
 	annotationsJSON, err := json.Marshal(opts.Annotations)
@@ -329,31 +324,19 @@ func newPassword() string {
 	return fmt.Sprintf("1Rr!%x", b[:])
 }
 
-// Helper function to get annotation value
-func getAnnotationValue(annotations map[string]string, key string) string {
-	if annotations == nil {
-		return ""
+func generateDatabaseName(resourceID string, annotations map[string]string) string {
+	name := "rill"
+	if org, ok := annotations["organization_name"]; ok {
+		name += "_" + nonAlphanumericRegexp.ReplaceAllString(org, "")
 	}
-	return annotations[key]
-}
-
-// Helper function to sanitize names for ClickHouse identifiers
-func sanitizeName(name string) string {
-	if name == "" {
-		return ""
+	if proj, ok := annotations["project_name"]; ok {
+		name += "_" + nonAlphanumericRegexp.ReplaceAllString(proj, "")
 	}
-
-	// Replace invalid characters with underscores
-	name = strings.ReplaceAll(name, "-", "_")
-	name = strings.ReplaceAll(name, " ", "_")
-
-	// Remove any characters that aren't alphanumeric or underscore
-	var result strings.Builder
-	for _, r := range name {
-		if (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') || (r >= '0' && r <= '9') || r == '_' {
-			result.WriteRune(r)
-		}
+	name += "_" + nonAlphanumericRegexp.ReplaceAllString(resourceID, "")
+	// Optionally, trim to 63 chars and remove trailing underscores if needed
+	if len(name) > 63 {
+		name = name[:63]
 	}
-
-	return strings.ToLower(result.String())
+	name = strings.TrimRight(name, "_")
+	return strings.ToLower(name)
 }
diff --git a/admin/provisioner/clickhousestatic/provisioner_test.go b/admin/provisioner/clickhousestatic/provisioner_test.go
@@ -294,9 +294,8 @@ func TestClickHouseStaticHumanReadableNaming(t *testing.T) {
 	opts2, err := clickhouse.ParseDSN(cfg.DSN)
 	require.NoError(t, err)
 	// Check that the database name follows the expected format
-	expectedID := strings.ReplaceAll(resourceID, "-", "")
-	expectedDBName := fmt.Sprintf("rill_acme_corp_my_project_%s", expectedID)
-	expectedUser := fmt.Sprintf("rill_%s", expectedID) // User name remains UUID format
+	expectedUser := fmt.Sprintf("rill_%s", nonAlphanumericRegexp.ReplaceAllString(resourceID, ""))
+	expectedDBName := generateDatabaseName(resourceID, opts.Annotations)
 
 	require.Equal(t, expectedDBName, opts2.Auth.Database)
 	require.Equal(t, expectedUser, opts2.Auth.Username)
@@ -377,9 +376,8 @@ func TestClickHouseStaticFallbackNaming(t *testing.T) {
 	opts2, err := clickhouse.ParseDSN(cfg.DSN)
 	require.NoError(t, err)
 	// Check that the database name follows the fallback format
-	expectedID := strings.ReplaceAll(resourceID, "-", "")
-	expectedDBName := fmt.Sprintf("rill_%s", expectedID)
-	expectedUser := fmt.Sprintf("rill_%s", expectedID) // User name always uses UUID format
+	expectedUser := fmt.Sprintf("rill_%s", nonAlphanumericRegexp.ReplaceAllString(resourceID, ""))
+	expectedDBName := generateDatabaseName(resourceID, opts.Annotations)
 
 	require.Equal(t, expectedDBName, opts2.Auth.Database)
 	require.Equal(t, expectedUser, opts2.Auth.Username)
@@ -401,29 +399,56 @@ func TestClickHouseStaticFallbackNaming(t *testing.T) {
 	require.NoError(t, err)
 }
 
-func TestSanitizeName(t *testing.T) {
+func TestGenerateDatabaseName(t *testing.T) {
 	tests := []struct {
-		input    string
-		expected string
+		name        string
+		id          string
+		annotations map[string]string
+		expected    string
 	}{
-		{"", ""},
-		{"simple", "simple"},
-		{"Simple", "simple"},
-		{"UPPERCASE", "uppercase"},
-		{"with-dashes", "with_dashes"},
-		{"with spaces", "with_spaces"},
-		{"with@special!chars", "withspecialchars"},
-		{"mixed-Case_Name", "mixed_case_name"},
-		{"123numbers", "123numbers"},
-		{"_underscore_", "_underscore_"},
-		{"Acme-Corp", "acme_corp"},
-		{"My-Project", "my_project"},
+		{
+			name:        "with org and project",
+			id:          "77cf2b72_65ab_4bbe_a10e_627bcff4915e",
+			annotations: map[string]string{"organization_name": "rilldata", "project_name": "dev-project-1"},
+			expected:    "rill_rilldata_devproject1_77cf2b7265ab4bbea10e627bcff4915e",
+		},
+		{
+			name:        "with org only",
+			id:          "12345",
+			annotations: map[string]string{"organization_name": "acme-corp"},
+			expected:    "rill_acmecorp_12345",
+		},
+		{
+			name:        "with project only",
+			id:          "12345",
+			annotations: map[string]string{"project_name": "my-project"},
+			expected:    "rill_myproject_12345",
+		},
+		{
+			name:        "no annotations",
+			id:          "12345",
+			annotations: map[string]string{},
+			expected:    "rill_12345",
+		},
+		{
+			name:        "nil annotations",
+			id:          "12345",
+			annotations: nil,
+			expected:    "rill_12345",
+		},
+		{
+			name:        "long name truncated",
+			id:          "very_long_resource_id_that_will_cause_truncation_12345678",
+			annotations: map[string]string{"organization_name": "very_long_organization_name", "project_name": "very_long_project_name"},
+			expected:    "rill_verylongorganizationname_verylongprojectname_verylongresou",
+		},
 	}
 
 	for _, tt := range tests {
-		t.Run(tt.input, func(t *testing.T) {
-			result := sanitizeName(tt.input)
+		t.Run(tt.name, func(t *testing.T) {
+			result := generateDatabaseName(tt.id, tt.annotations)
 			require.Equal(t, tt.expected, result)
+			require.LessOrEqual(t, len(result), 63, "database name should not exceed 63 characters")
 		})
 	}
 }