Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

12.7 KB
Raw Blame
package main

import (
	"database/sql/driver"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"testing"

	gokb "gitea.com/kingbase/gokb"
)

// Return the requested projection, so the reproduction also runs against the
// old query without failing merely because its column count is different.
func generationMetadataRows(query, identity, generated string, expression driver.Value) driver.Rows {
	if strings.Contains(query, "FROM information_schema.columns c") {
		columns := []string{"column_name", "data_type", "column_type", "is_nullable", "column_default", "column_comment", "numeric_precision", "numeric_scale", "character_maximum_length"}
		values := []driver.Value{"value", "integer", "integer", "YES", expression, nil, int64(32), int64(0), nil}
		if generated == "s" {
			values[4] = nil
		}
		if strings.Contains(query, "AS generation_expression") {
			columns = append(columns, "attidentity", "attgenerated", "generation_expression")
			values = append(values, identity, generated, expression)
		}
		return &valueRows{columns: columns, rows: [][]driver.Value{values}}
	}
	columns := []string{"resolved_schema", "column_name", "data_type", "is_nullable", "column_default", "column_comment", "numeric_precision", "numeric_scale", "character_maximum_length", "attidentity"}
	values := []driver.Value{"public", "value", "integer", true, expression, nil, int64(32), int64(0), nil, identity}
	if strings.Contains(query, "a.attgenerated") || strings.Contains(query, "AS attgenerated") {
		columns = append(columns, "attgenerated")
		values = append(values, generated)
	}
	return &valueRows{columns: columns, rows: [][]driver.Value{values}}
}

func TestGenerationMetadataPreservesColumnSemanticsAcrossQueryPaths(t *testing.T) {
	for _, mode := range []string{"oracle", "mysql"} {
		for _, pgCatalog := range []bool{false, true} {
			for _, test := range []struct {
				name, identity, generated, expression, wantExtra string
				wantDefault                                      bool
			}{
				{name: "ordinary default", expression: "42", wantDefault: true},
				{name: "serial default", expression: "nextval('public.value_seq'::regclass)", wantDefault: true},
				{name: "stored generated", generated: "s", expression: `(quantity * price)`, wantExtra: `GENERATED ALWAYS AS ((quantity * price)) STORED`},
				{name: "always identity", identity: "a", expression: "nextval('public.value_seq'::regclass)", wantExtra: "GENERATED ALWAYS AS IDENTITY"},
				{name: "default identity", identity: "d", expression: "nextval('public.value_seq'::regclass)", wantExtra: "GENERATED BY DEFAULT AS IDENTITY"},
				{name: "mysql-compat identity i", identity: "i", expression: "nextval('public.value_seq'::regclass)", wantDefault: true},
			} {
				t.Run(fmt.Sprintf("%s/pgCatalog=%v/%s", mode, pgCatalog, test.name), func(t *testing.T) {
					catalog, prefix := "sys_catalog", "sys"
					if pgCatalog {
						catalog, prefix = "pg_catalog", "pg"
					}
					state := &metadataDriverState{query: func(query string) (driver.Rows, error) {
						if strings.Contains(query, "information_schema.table_constraints") || strings.Contains(query, "sys.identity_columns") {
							return &valueRows{columns: []string{"column_name"}}, nil
						}
						if !strings.Contains(query, catalog+"."+prefix+"_attribute") {
							return nil, errors.New("column query used the unavailable catalog: " + query)
						}
						return generationMetadataRows(query, test.identity, test.generated, test.expression), nil
					}}
					srv := newServer()
					srv.db = openMetadataDB(t, state)
					srv.mode = kingbaseMode{compatibilityMode: mode, postgresCatalog: pgCatalog, mysqlCompat: mode == "mysql"}
					columns, err := srv.getColumns("public", "orders")
					if err != nil {
						t.Fatal(err)
					}
					if len(columns) != 1 {
						t.Fatalf("unexpected columns: %#v", columns)
					}
					column := columns[0]
					if test.wantDefault {
						if column.ColumnDefault == nil || *column.ColumnDefault != test.expression || column.Extra != nil {
							t.Fatalf("ordinary/serial default was changed: %#v", column)
						}
					} else if column.ColumnDefault != nil || column.Extra == nil || *column.Extra != test.wantExtra {
						t.Fatalf("generation must not be represented as DEFAULT: %#v", column)
					}
					ddl := srv.renderTableDDL("public", "orders", columns, nil)
					if test.wantDefault && !strings.Contains(ddl, " DEFAULT "+test.expression) || !test.wantDefault && (!strings.Contains(ddl, test.wantExtra) || strings.Contains(ddl, " DEFAULT nextval") || strings.Contains(ddl, " DEFAULT (")) {
						t.Fatalf("column semantics lost in DDL: %s", ddl)
					}
					payload, err := json.Marshal(column)
					if err != nil {
						t.Fatal(err)
					}
					var decoded columnInfo
					if err := json.Unmarshal(payload, &decoded); err != nil || srv.columnDDLDefinition(decoded) != srv.columnDDLDefinition(column) {
						t.Fatalf("generation must survive the existing column wire protocol: %s (%v)", payload, err)
					}
				})
			}
		}
	}
}

func TestGeneratedCatalogCapabilityFallbackIsIndependentAndCached(t *testing.T) {
	for _, mysql := range []bool{false, true} {
		t.Run(fmt.Sprintf("mysql=%v", mysql), func(t *testing.T) {
			state := &metadataDriverState{query: func(query string) (driver.Rows, error) {
				if strings.Contains(query, "a.attidentity") {
					return nil, &gokb.Error{Code: "42703", Message: "column a.attidentity does not exist"}
				}
				if strings.Contains(query, "a.attgenerated") {
					return nil, &gokb.Error{Code: "42703", Message: "column a.attgenerated does not exist"}
				}
				return generationMetadataRows(query, "", "", "42"), nil
			}}
			srv := newServer()
			srv.db = openMetadataDB(t, state)
			srv.mode.mysqlCompat = mysql
			for call := 0; call < 2; call++ {
				var columns []columnInfo
				var err error
				if mysql {
					columns, err = srv.informationSchemaColumns("public", "orders", nil)
				} else {
					columns, err = srv.queryCatalogColumns("public", "orders", "sys_catalog", "sys", "sys_get_expr")
				}
				if err != nil || len(columns) != 1 || columns[0].ColumnDefault == nil || *columns[0].ColumnDefault != "42" || columns[0].Extra != nil {
					t.Fatalf("legacy column metadata failed: %#v (%v)", columns, err)
				}
			}
			queries := state.snapshotQueries()
			if len(queries) != 4 || strings.Contains(queries[3], "a.attidentity") || strings.Contains(queries[3], "a.attgenerated") {
				t.Fatalf("each missing capability must be probed only once: %v", queries)
			}
		})
	}
}

func TestIncompleteGenerationMetadataReportsError(t *testing.T) {
	for _, mysql := range []bool{false, true} {
		for _, test := range []struct {
			name, identity, generated string
			expression                driver.Value
		}{
			{name: "missing expression", generated: "s"},
			{name: "blank expression", generated: "s", expression: " "},
		} {
			t.Run(fmt.Sprintf("mysql=%v/%s", mysql, test.name), func(t *testing.T) {
				state := &metadataDriverState{query: func(query string) (driver.Rows, error) {
					return generationMetadataRows(query, test.identity, test.generated, test.expression), nil
				}}
				srv := newServer()
				srv.db = openMetadataDB(t, state)
				var err error
				if mysql {
					_, err = srv.informationSchemaColumns("public", "orders", nil)
				} else {
					_, err = srv.queryCatalogColumns("public", "orders", "sys_catalog", "sys", "sys_get_expr")
				}
				if err == nil || !strings.Contains(err.Error(), "value") {
					t.Fatalf("incomplete generation must report the affected column: %v", err)
				}
			})
		}
	}
}

func TestUnknownGenerationOrIdentityMetadataDegradesGracefully(t *testing.T) {
	for _, mysql := range []bool{false, true} {
		for _, test := range []struct {
			name, identity, generated string
			expression                driver.Value
		}{
			{name: "unknown generation", generated: "future", expression: "a * 2"},
			{name: "unknown identity", identity: "future", expression: "42"},
			{name: "mysql-compat identity", identity: "i", expression: "nextval('public.orders_id_seq'::regclass)"},
		} {
			t.Run(fmt.Sprintf("mysql=%v/%s", mysql, test.name), func(t *testing.T) {
				state := &metadataDriverState{query: func(query string) (driver.Rows, error) {
					return generationMetadataRows(query, test.identity, test.generated, test.expression), nil
				}}
				srv := newServer()
				srv.db = openMetadataDB(t, state)
				var columns []columnInfo
				var err error
				if mysql {
					columns, err = srv.informationSchemaColumns("public", "orders", nil)
				} else {
					columns, err = srv.queryCatalogColumns("public", "orders", "sys_catalog", "sys", "sys_get_expr")
				}
				if err != nil {
					t.Fatalf("unknown metadata must not error: %v", err)
				}
				if len(columns) != 1 {
					t.Fatalf("expected 1 column, got %d", len(columns))
				}
				col := columns[0]
				if col.Extra != nil {
					t.Fatalf("unknown metadata must leave Extra nil, got: %v", *col.Extra)
				}
				if col.ColumnDefault == nil || *col.ColumnDefault != test.expression {
					t.Fatalf("default was not preserved: %#v", col.ColumnDefault)
				}
			})
		}
	}
}

func TestMissingGeneratedCatalogFieldPreservesIdentity(t *testing.T) {
	for _, mysql := range []bool{false, true} {
		t.Run(fmt.Sprintf("mysql=%v", mysql), func(t *testing.T) {
			state := &metadataDriverState{query: func(query string) (driver.Rows, error) {
				if strings.Contains(query, "a.attgenerated") {
					return nil, &gokb.Error{Code: "42703", Message: "column a.attgenerated does not exist"}
				}
				return generationMetadataRows(query, "d", "", nil), nil
			}}
			srv := newServer()
			srv.db = openMetadataDB(t, state)
			for call := 0; call < 2; call++ {
				var columns []columnInfo
				var err error
				if mysql {
					columns, err = srv.informationSchemaColumns("public", "orders", nil)
				} else {
					columns, err = srv.queryCatalogColumns("public", "orders", "sys_catalog", "sys", "sys_get_expr")
				}
				if err != nil || len(columns) != 1 || columns[0].Extra == nil || *columns[0].Extra != "GENERATED BY DEFAULT AS IDENTITY" {
					t.Fatalf("missing generated-column support must not disable identity: %#v (%v)", columns, err)
				}
			}
			if len(state.snapshotQueries()) != 3 {
				t.Fatalf("missing attgenerated must be cached: %v", state.snapshotQueries())
			}
		})
	}
}

func TestInformationSchemaGeneratedExpressionFunctionFallbackIsCached(t *testing.T) {
	state := &metadataDriverState{query: func(query string) (driver.Rows, error) {
		if strings.Contains(query, "sys_get_expr(") {
			return nil, &gokb.Error{Code: "42883", Message: "function sys_get_expr(pg_node_tree, oid) does not exist"}
		}
		if !strings.Contains(query, "pg_get_expr(ad.adbin, ad.adrelid)") {
			return nil, errors.New("missing compatible generation expression function")
		}
		return generationMetadataRows(query, "", "s", "quantity * price"), nil
	}}
	srv := newServer()
	srv.db = openMetadataDB(t, state)
	for call := 0; call < 2; call++ {
		columns, err := srv.informationSchemaColumns("public", "orders", nil)
		if err != nil || len(columns) != 1 || columns[0].Extra == nil || *columns[0].Extra != "GENERATED ALWAYS AS (quantity * price) STORED" || columns[0].ColumnDefault != nil {
			t.Fatalf("generated expression lost in function fallback: %#v (%v)", columns, err)
		}
	}
	if queries := state.snapshotQueries(); len(queries) != 3 || !strings.Contains(queries[0], "sys_get_expr(") || strings.Contains(queries[2], "sys_get_expr(") {
		t.Fatalf("function fallback must be cached: %v", queries)
	}
}

func TestGenerationMetadataErrorsDoNotDisableCapabilities(t *testing.T) {
	for _, mysql := range []bool{false, true} {
		for _, failure := range []error{
			&gokb.Error{Code: "42501", Message: "permission denied for column attgenerated"},
			&gokb.Error{Code: "42703", Message: "column a.other_column does not exist"},
			errors.New("metadata connection reset"),
		} {
			t.Run(fmt.Sprintf("mysql=%v/%s", mysql, failure), func(t *testing.T) {
				state := &metadataDriverState{query: func(string) (driver.Rows, error) { return nil, failure }}
				srv := newServer()
				srv.db = openMetadataDB(t, state)
				var err error
				if mysql {
					_, err = srv.informationSchemaColumns("public", "orders", nil)
				} else {
					_, err = srv.queryCatalogColumns("public", "orders", "sys_catalog", "sys", "sys_get_expr")
				}
				if !errors.Is(err, failure) || len(state.snapshotQueries()) != 1 || srv.catalogIdentityUnsupported || srv.catalogGeneratedUnsupported {
					t.Fatalf("unrelated failure must propagate without downgrading metadata: %v", err)
				}
			})
		}
	}
}

func TestDisconnectResetsGeneratedColumnCapability(t *testing.T) {
	srv := newServer()
	srv.catalogGeneratedUnsupported = true
	if err := srv.disconnect(); err != nil {
		t.Fatal(err)
	}
	_, projection := srv.catalogColumnGenerationExpressions()
	if srv.catalogGeneratedUnsupported || projection != "a.attgenerated" {
		t.Fatal("new connections must reprobe generated-column support")
	}
}