sqlstate_test.go 9.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230
  1. package httpserver
  2. import (
  3. "encoding/json"
  4. "errors"
  5. "fmt"
  6. "net/http"
  7. "net/http/httptest"
  8. "strings"
  9. "testing"
  10. "github.com/danfragoso/pizzasql-next/pkg/pgerror"
  11. "github.com/danfragoso/pizzasql-next/pkg/storage"
  12. )
  13. type httpErrorBody struct {
  14. Error struct {
  15. Code string `json:"code"`
  16. Message string `json:"message"`
  17. Details map[string]interface{} `json:"details"`
  18. } `json:"error"`
  19. }
  20. func TestHTTPUniqueViolationReturns23505(t *testing.T) {
  21. server := setupKVTestServer(t)
  22. queryHTTP(t, server, "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)")
  23. queryHTTP(t, server, "INSERT INTO users (id, email) VALUES (1, 'a@x')")
  24. body, _ := json.Marshal(QueryRequest{SQL: "INSERT INTO users (id, email) VALUES (2, 'a@x')"})
  25. r := httptest.NewRequest(http.MethodPost, "/query", strings.NewReader(string(body)))
  26. w := httptest.NewRecorder()
  27. server.handleQuery(w, r)
  28. if w.Code != http.StatusConflict {
  29. t.Fatalf("status = %d, want 409; body %s", w.Code, w.Body.String())
  30. }
  31. var resp httpErrorBody
  32. if err := json.NewDecoder(w.Body).Decode(&resp); err != nil {
  33. t.Fatalf("decode: %v", err)
  34. }
  35. if resp.Error.Code != "23505" {
  36. t.Fatalf("code = %q, want 23505", resp.Error.Code)
  37. }
  38. constraint, _ := resp.Error.Details["constraint"].(string)
  39. if constraint == "" || !strings.Contains(resp.Error.Message, constraint) {
  40. t.Fatalf("details.constraint = %q, message = %q", constraint, resp.Error.Message)
  41. }
  42. }
  43. // A duplicate composite key over HTTP keeps SQLSTATE 23505 and reports the key
  44. // components instead of the length-prefixed durable encoding.
  45. func TestHTTPCompositePrimaryKeyViolationReportsComponents(t *testing.T) {
  46. server := setupKVTestServer(t)
  47. queryHTTP(t, server, "CREATE TABLE pairs (a INTEGER, b INTEGER, PRIMARY KEY (a, b))")
  48. queryHTTP(t, server, "INSERT INTO pairs (a, b) VALUES (1, 11)")
  49. body, _ := json.Marshal(QueryRequest{SQL: "INSERT INTO pairs (a, b) VALUES (1, 11)"})
  50. r := httptest.NewRequest(http.MethodPost, "/query", strings.NewReader(string(body)))
  51. w := httptest.NewRecorder()
  52. server.handleQuery(w, r)
  53. if w.Code != http.StatusConflict {
  54. t.Fatalf("status = %d, want 409; body %s", w.Code, w.Body.String())
  55. }
  56. var resp httpErrorBody
  57. if err := json.NewDecoder(w.Body).Decode(&resp); err != nil {
  58. t.Fatalf("decode: %v", err)
  59. }
  60. if resp.Error.Code != "23505" {
  61. t.Fatalf("code = %q, want 23505", resp.Error.Code)
  62. }
  63. if want := "duplicate primary key: (1,11)"; resp.Error.Message != want {
  64. t.Fatalf("message = %q, want %q", resp.Error.Message, want)
  65. }
  66. }
  67. // A constraint the commit discovers must report its SQLSTATE instead of the
  68. // generic TRANSACTION_ERROR the transaction endpoints use for other failures.
  69. func TestHTTPCommitCheckViolationReturns23514(t *testing.T) {
  70. s := newTestDBServer(t)
  71. if w := postQuery(t, s, "CREATE TABLE t (id INTEGER PRIMARY KEY)"); w.Code != http.StatusOK {
  72. t.Fatalf("create table: %d %s", w.Code, w.Body.String())
  73. }
  74. txID := beginTransaction(t, s, "")
  75. if w := postTransactionQuery(t, s, txID, "INSERT INTO t (id) VALUES (1)"); w.Code != http.StatusOK {
  76. t.Fatalf("staged insert: %d %s", w.Code, w.Body.String())
  77. }
  78. // A second session publishes the constraint the staged row will violate.
  79. if w := postQuery(t, s, "ALTER TABLE t ADD COLUMN qty INTEGER DEFAULT -1 CHECK (qty >= 0)"); w.Code != http.StatusOK {
  80. t.Fatalf("add column: %d %s", w.Code, w.Body.String())
  81. }
  82. body, _ := json.Marshal(TransactionRequest{TransactionID: txID})
  83. r := httptest.NewRequest(http.MethodPost, "/transaction/commit", strings.NewReader(string(body)))
  84. w := httptest.NewRecorder()
  85. s.handleTransactionCommit(w, r)
  86. if w.Code != http.StatusBadRequest {
  87. t.Fatalf("commit status = %d, want 400; body %s", w.Code, w.Body.String())
  88. }
  89. var resp httpErrorBody
  90. if err := json.NewDecoder(w.Body).Decode(&resp); err != nil {
  91. t.Fatalf("decode: %v", err)
  92. }
  93. if resp.Error.Code != "23514" {
  94. t.Fatalf("code = %q, want 23514", resp.Error.Code)
  95. }
  96. constraint, _ := resp.Error.Details["constraint"].(string)
  97. if constraint == "" || !strings.Contains(resp.Error.Message, constraint) {
  98. t.Fatalf("details.constraint = %q, message = %q", constraint, resp.Error.Message)
  99. }
  100. }
  101. // The transaction endpoints only replace TRANSACTION_ERROR for constraint
  102. // violations; other commit failures keep the generic code.
  103. func TestConstraintViolationHTTPErrorIgnoresOtherErrors(t *testing.T) {
  104. if got := constraintViolationHTTPError(errors.New("boom")); got != nil {
  105. t.Fatalf("generic error mapped to %+v, want nil", got)
  106. }
  107. }
  108. func TestHTTPCheckViolationReturns23514(t *testing.T) {
  109. server := setupKVTestServer(t)
  110. queryHTTP(t, server, "CREATE TABLE t (id INTEGER PRIMARY KEY, age INTEGER, CONSTRAINT positive_age CHECK (age > 0))")
  111. body, _ := json.Marshal(QueryRequest{SQL: "INSERT INTO t (id, age) VALUES (1, -1)"})
  112. r := httptest.NewRequest(http.MethodPost, "/query", strings.NewReader(string(body)))
  113. w := httptest.NewRecorder()
  114. server.handleQuery(w, r)
  115. if w.Code != http.StatusBadRequest {
  116. t.Fatalf("status = %d, want 400; body %s", w.Code, w.Body.String())
  117. }
  118. var resp httpErrorBody
  119. if err := json.NewDecoder(w.Body).Decode(&resp); err != nil {
  120. t.Fatalf("decode: %v", err)
  121. }
  122. if resp.Error.Code != "23514" {
  123. t.Fatalf("code = %q, want 23514", resp.Error.Code)
  124. }
  125. constraint, _ := resp.Error.Details["constraint"].(string)
  126. if constraint != "positive_age" {
  127. t.Fatalf("details.constraint = %q, want positive_age", constraint)
  128. }
  129. }
  130. func TestSQLStateHTTPErrorMapping(t *testing.T) {
  131. tests := []struct {
  132. name string
  133. err error
  134. status int
  135. code string
  136. }{
  137. {"unique", fmt.Errorf("UNIQUE constraint failed: uniq_users_email"), http.StatusConflict, "23505"},
  138. {"foreign key", fmt.Errorf("FOREIGN KEY constraint failed: parent_id -> parent"), http.StatusConflict, "23503"},
  139. {"typed foreign key", &storage.ForeignKeyViolationError{Table: "child", Constraint: "parent_id -> parent"}, http.StatusConflict, "23503"},
  140. {"not null", fmt.Errorf("NOT NULL constraint failed: t.v"), http.StatusBadRequest, "23502"},
  141. {"check", fmt.Errorf("CHECK constraint failed: positive_age"), http.StatusBadRequest, "23514"},
  142. {"typed check", &storage.CheckViolationError{Table: "t", Constraint: "positive_age"}, http.StatusBadRequest, "23514"},
  143. {"typed not null", &storage.NotNullViolationError{Table: "t", Column: "v"}, http.StatusBadRequest, "23502"},
  144. {"typed unique", &storage.UniqueViolationError{Constraint: "uq_email"}, http.StatusConflict, "23505"},
  145. {"typed primary key", &storage.PrimaryKeyViolationError{Table: "t", Key: "1"}, http.StatusConflict, "23505"},
  146. {"serialization", fmt.Errorf("commit: %w", storage.ErrSerialization), http.StatusConflict, "40001"},
  147. {"unsupported", fmt.Errorf("feature not supported: DEFAULT is not constant"), http.StatusBadRequest, "0A000"},
  148. {"undefined table", pgerror.UndefinedTable("relation %q does not exist", "missing"), http.StatusNotFound, "42P01"},
  149. {"other", errors.New("boom"), http.StatusInternalServerError, "EXECUTION_ERROR"},
  150. }
  151. for _, tt := range tests {
  152. t.Run(tt.name, func(t *testing.T) {
  153. got := sqlStateHTTPError(tt.err)
  154. if got.Status != tt.status || got.Code != tt.code {
  155. t.Fatalf("got status=%d code=%s, want status=%d code=%s", got.Status, got.Code, tt.status, tt.code)
  156. }
  157. })
  158. }
  159. }
  160. // The batch /execute endpoint commits its own transaction, so a constraint the
  161. // commit discovers must map to 23514 there too instead of TRANSACTION_ERROR.
  162. func TestHTTPExecuteCommitCheckViolationReturns23514(t *testing.T) {
  163. s := newTestDBServer(t)
  164. if w := postQuery(t, s, "CREATE TABLE t (id INTEGER PRIMARY KEY)"); w.Code != http.StatusOK {
  165. t.Fatalf("create table: %d %s", w.Code, w.Body.String())
  166. }
  167. w := postExecute(t, s, ExecuteRequest{
  168. Transaction: true,
  169. Statements: []QueryRequest{
  170. {SQL: "INSERT INTO t (id) VALUES (1)"},
  171. {SQL: "ALTER TABLE t ADD COLUMN qty INTEGER DEFAULT -1 CHECK (qty >= 0)"},
  172. },
  173. })
  174. if w.Code != http.StatusBadRequest {
  175. t.Fatalf("execute status = %d, want 400; body %s", w.Code, w.Body.String())
  176. }
  177. var resp httpErrorBody
  178. if err := json.NewDecoder(w.Body).Decode(&resp); err != nil {
  179. t.Fatalf("decode: %v", err)
  180. }
  181. if resp.Error.Code != "23514" {
  182. t.Fatalf("code = %q, want 23514", resp.Error.Code)
  183. }
  184. }
  185. // A dangling foreign key reports 23503 through the query endpoint, with the
  186. // constraint label in details.
  187. func TestHTTPForeignKeyViolationReturns23503(t *testing.T) {
  188. server := setupKVTestServer(t)
  189. queryHTTP(t, server, "CREATE TABLE parent (id INTEGER PRIMARY KEY)")
  190. queryHTTP(t, server, "CREATE TABLE child (id INTEGER PRIMARY KEY, parent_id INTEGER, CONSTRAINT child_parent FOREIGN KEY (parent_id) REFERENCES parent(id))")
  191. body, _ := json.Marshal(QueryRequest{SQL: "INSERT INTO child (id, parent_id) VALUES (1, 99)"})
  192. r := httptest.NewRequest(http.MethodPost, "/query", strings.NewReader(string(body)))
  193. w := httptest.NewRecorder()
  194. server.handleQuery(w, r)
  195. if w.Code != http.StatusConflict {
  196. t.Fatalf("status = %d, want 409; body %s", w.Code, w.Body.String())
  197. }
  198. var resp httpErrorBody
  199. if err := json.NewDecoder(w.Body).Decode(&resp); err != nil {
  200. t.Fatalf("decode: %v", err)
  201. }
  202. if resp.Error.Code != "23503" {
  203. t.Fatalf("code = %q, want 23503", resp.Error.Code)
  204. }
  205. if constraint, _ := resp.Error.Details["constraint"].(string); constraint != "child_parent" {
  206. t.Fatalf("details.constraint = %q, want child_parent", constraint)
  207. }
  208. }