pgddl_test.go

package pgddl

import (
	"strings"
	"testing"

	"github.com/pgplex/pgddl/catalog"
)

func TestLoadSQL_Basic(t *testing.T) {
	sql := `
CREATE SCHEMA s1;
CREATE TABLE s1.users (
    id integer PRIMARY KEY,
    name text NOT NULL
);
CREATE INDEX users_name_idx ON s1.users (name);
`
	c, err := LoadSQL(sql)
	if err != nil {
		t.Fatalf("LoadSQL: %v", err)
	}
	if c.GetRelation("s1", "users") == nil {
		t.Error("expected table s1.users to exist")
	}
}

func TestLoadSQL_ParseError(t *testing.T) {
	sql := `CREATE TABLE t1 (id int; INVALID SYNTAX`
	c, err := LoadSQL(sql)
	if err == nil {
		t.Fatal("expected parse error")
	}
	if c != nil {
		t.Error("expected nil catalog on parse error")
	}
}

func TestLoadSQL_DDLError(t *testing.T) {
	sql := `
CREATE TABLE t1 (id integer);
CREATE TABLE t1 (id integer);
CREATE TABLE t2 (id integer);
`
	c, err := LoadSQL(sql)
	if err == nil {
		t.Fatal("expected DDL error for duplicate table")
	}

	// Catalog should reflect the first successful statement.
	if c == nil {
		t.Fatal("expected non-nil catalog on DDL error")
	}
	if c.GetRelation("", "t1") == nil {
		t.Error("expected t1 to exist (created before error)")
	}
	if c.GetRelation("", "t2") != nil {
		t.Error("expected t2 to NOT exist (after error)")
	}
}

func TestLoadSQL_PgDumpStyle(t *testing.T) {
	sql := `
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
BEGIN;
CREATE TABLE users (id integer NOT NULL);
COMMIT;
SELECT pg_catalog.set_config('search_path', '', false);
`
	c, err := LoadSQL(sql)
	if err != nil {
		t.Fatalf("LoadSQL: %v", err)
	}
	if c.GetRelation("", "users") == nil {
		t.Error("expected table users to exist")
	}
}

func TestExec_LineNumbers(t *testing.T) {
	sql := "CREATE SCHEMA s1;\nCREATE TABLE s1.t1 (id int);\n\nCREATE INDEX idx ON s1.t1 (id);"

	c := catalog.New()
	results, err := c.Exec(sql, nil)
	if err != nil {
		t.Fatalf("Exec: %v", err)
	}

	if len(results) != 3 {
		t.Fatalf("expected 3 results, got %d", len(results))
	}

	// Line numbers: "CREATE SCHEMA s1;" starts at line 1,
	// "CREATE TABLE ..." at line 2, "CREATE INDEX ..." at line 4.
	wantLines := []int{1, 2, 4}
	for i, r := range results {
		if r.Line != wantLines[i] {
			t.Errorf("stmt %d: Line=%d, want %d", i, r.Line, wantLines[i])
		}
	}

	// SQL text should be extracted.
	if !strings.HasPrefix(results[0].SQL, "CREATE SCHEMA") {
		t.Errorf("stmt 0: SQL=%q, want prefix 'CREATE SCHEMA'", results[0].SQL)
	}
	if !strings.HasPrefix(results[1].SQL, "CREATE TABLE") {
		t.Errorf("stmt 1: SQL=%q, want prefix 'CREATE TABLE'", results[1].SQL)
	}
	if !strings.HasPrefix(results[2].SQL, "CREATE INDEX") {
		t.Errorf("stmt 2: SQL=%q, want prefix 'CREATE INDEX'", results[2].SQL)
	}
}

func TestExec_WithWarnings(t *testing.T) {
	sql := `
CREATE TABLE t1 (id integer);
CREATE TABLE IF NOT EXISTS t1 (id integer);
CREATE TABLE t2 (id integer);
`
	c := catalog.New()
	results, err := c.Exec(sql, nil)
	if err != nil {
		t.Fatalf("Exec: %v", err)
	}

	if len(results) != 3 {
		t.Fatalf("expected 3 results, got %d", len(results))
	}

	// Statement 0: success, no warnings.
	if results[0].Error != nil {
		t.Errorf("stmt 0: %v", results[0].Error)
	}
	if len(results[0].Warnings) != 0 {
		t.Errorf("stmt 0: expected 0 warnings, got %d", len(results[0].Warnings))
	}

	// Statement 1: success with warning.
	if results[1].Error != nil {
		t.Errorf("stmt 1: %v", results[1].Error)
	}
	if len(results[1].Warnings) != 1 {
		t.Fatalf("stmt 1: expected 1 warning, got %d", len(results[1].Warnings))
	}
	w := results[1].Warnings[0]
	if w.Code != catalog.CodeWarningSkip {
		t.Errorf("warning code=%q, want %q", w.Code, catalog.CodeWarningSkip)
	}
	if !strings.Contains(w.Message, "already exists") {
		t.Errorf("warning message=%q, want substring 'already exists'", w.Message)
	}

	// Statement 1 should have SQL and line number.
	if results[1].SQL == "" {
		t.Error("stmt 1: SQL should not be empty")
	}
	if results[1].Line == 0 {
		t.Error("stmt 1: Line should not be 0")
	}

	// Statement 2: success, no warnings.
	if results[2].Error != nil {
		t.Errorf("stmt 2: %v", results[2].Error)
	}
}

func TestLoadSQL_Empty(t *testing.T) {
	c, err := LoadSQL("")
	if err != nil {
		t.Fatalf("LoadSQL: %v", err)
	}
	if c == nil {
		t.Error("expected non-nil catalog")
	}
}