2
0

sqlstate_test.go 11 KB

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