The best way to control Claude Code, Codex and more from any computer or phone
| 1 | import { scanSqlScript, splitSqlScript } from "@alchemy.run/sql/Migrations"; |
| 2 | import { describe, expect, it } from "@alchemy.run/test"; |
| 3 | import * as Effect from "effect/Effect"; |
| 4 | |
| 5 | const tags = { tags: ["unit", "local"] }; |
| 6 | |
| 7 | const split = (sql: string, dialect: "postgres" | "mysql" | "googlesql") => |
| 8 | Effect.sync(() => splitSqlScript(sql, dialect)); |
| 9 | |
| 10 | describe("splitSqlScript", () => { |
| 11 | it.effect( |
| 12 | "splits on semicolons and drops terminators and blank statements", |
| 13 | () => |
| 14 | Effect.gen(function* () { |
| 15 | for (const dialect of ["postgres", "mysql", "googlesql"] as const) { |
| 16 | expect(yield* split("SELECT 1;\n\n;SELECT 2; ", dialect)).toEqual([ |
| 17 | "SELECT 1", |
| 18 | "SELECT 2", |
| 19 | ]); |
| 20 | } |
| 21 | }), |
| 22 | tags, |
| 23 | ); |
| 24 | |
| 25 | it.effect( |
| 26 | "removes comments, including `;` inside them", |
| 27 | () => |
| 28 | Effect.gen(function* () { |
| 29 | expect( |
| 30 | yield* split( |
| 31 | "-- header; still a comment\nCREATE TABLE a (id int) /* x; y */; SELECT 1", |
| 32 | "postgres", |
| 33 | ), |
| 34 | ).toEqual(["CREATE TABLE a (id int)", "SELECT 1"]); |
| 35 | }), |
| 36 | tags, |
| 37 | ); |
| 38 | |
| 39 | it.effect( |
| 40 | "treats `#` as a comment in MySQL and GoogleSQL, an operator in PostgreSQL", |
| 41 | () => |
| 42 | Effect.gen(function* () { |
| 43 | expect(yield* split("SELECT 1 # c; d\n+ 1; SELECT 2", "googlesql")).toEqual([ |
| 44 | "SELECT 1 \n+ 1", |
| 45 | "SELECT 2", |
| 46 | ]); |
| 47 | expect(yield* split("SELECT 1 # c; d\n+ 1; SELECT 2", "mysql")).toEqual([ |
| 48 | "SELECT 1 \n+ 1", |
| 49 | "SELECT 2", |
| 50 | ]); |
| 51 | expect(yield* split("SELECT '{}'::jsonb #> '{}'; SELECT 2", "postgres")).toEqual([ |
| 52 | "SELECT '{}'::jsonb #> '{}'", |
| 53 | "SELECT 2", |
| 54 | ]); |
| 55 | }), |
| 56 | tags, |
| 57 | ); |
| 58 | |
| 59 | it.effect( |
| 60 | "needs whitespace after `--` for a MySQL comment", |
| 61 | () => |
| 62 | Effect.gen(function* () { |
| 63 | expect(yield* split("SELECT 1--1; SELECT 2", "mysql")).toEqual(["SELECT 1--1", "SELECT 2"]); |
| 64 | expect(yield* split("SELECT 1 -- c; d\n; SELECT 2", "mysql")).toEqual([ |
| 65 | "SELECT 1", |
| 66 | "SELECT 2", |
| 67 | ]); |
| 68 | }), |
| 69 | tags, |
| 70 | ); |
| 71 | |
| 72 | it.effect( |
| 73 | "nests block comments only in PostgreSQL", |
| 74 | () => |
| 75 | Effect.gen(function* () { |
| 76 | expect(yield* split("/* a /* b; */ c; */ SELECT 1; SELECT 2", "postgres")).toEqual([ |
| 77 | "SELECT 1", |
| 78 | "SELECT 2", |
| 79 | ]); |
| 80 | expect(yield* split("/* a /* b */ SELECT 1; SELECT 2", "googlesql")).toEqual([ |
| 81 | "SELECT 1", |
| 82 | "SELECT 2", |
| 83 | ]); |
| 84 | }), |
| 85 | tags, |
| 86 | ); |
| 87 | |
| 88 | it.effect( |
| 89 | "keeps `;` inside PostgreSQL strings, E-strings, identifiers, and dollar quotes", |
| 90 | () => |
| 91 | Effect.gen(function* () { |
| 92 | expect( |
| 93 | yield* split( |
| 94 | "INSERT INTO t VALUES ('it''s; me', E'esc\\'; still', \"we\"\"ird;\"); SELECT $$a;b$$, $tag$c;$$d$tag$", |
| 95 | "postgres", |
| 96 | ), |
| 97 | ).toEqual([ |
| 98 | "INSERT INTO t VALUES ('it''s; me', E'esc\\'; still', \"we\"\"ird;\")", |
| 99 | "SELECT $$a;b$$, $tag$c;$$d$tag$", |
| 100 | ]); |
| 101 | // A word ending in `e` before a quote is not an E-string prefix. |
| 102 | expect(yield* split("SELECT CASE WHEN x THEN 'a\\' ELSE'b'; SELECT 2", "postgres")).toEqual( |
| 103 | ["SELECT CASE WHEN x THEN 'a\\' ELSE'b'", "SELECT 2"], |
| 104 | ); |
| 105 | }), |
| 106 | tags, |
| 107 | ); |
| 108 | |
| 109 | it.effect( |
| 110 | "keeps `;` inside MySQL strings with backslash and doubled-quote escapes", |
| 111 | () => |
| 112 | Effect.gen(function* () { |
| 113 | expect( |
| 114 | yield* split("INSERT INTO `t;x` VALUES ('a\\';b', 'c'';d', \"e;f\"); SELECT 2", "mysql"), |
| 115 | ).toEqual(["INSERT INTO `t;x` VALUES ('a\\';b', 'c'';d', \"e;f\")", "SELECT 2"]); |
| 116 | }), |
| 117 | tags, |
| 118 | ); |
| 119 | |
| 120 | it.effect( |
| 121 | "keeps `;` inside GoogleSQL strings, triple quotes, raw strings, and identifiers", |
| 122 | () => |
| 123 | Effect.gen(function* () { |
| 124 | expect( |
| 125 | yield* split( |
| 126 | "INSERT INTO `t;x` (a) VALUES ('it\\'s; -- no comment'); SELECT '''tri;ple ''q'' ''', \"\"\"x;\"\"\"", |
| 127 | "googlesql", |
| 128 | ), |
| 129 | ).toEqual([ |
| 130 | "INSERT INTO `t;x` (a) VALUES ('it\\'s; -- no comment')", |
| 131 | "SELECT '''tri;ple ''q'' ''', \"\"\"x;\"\"\"", |
| 132 | ]); |
| 133 | // In a raw string a backslash doesn't escape, so `r'a\'` ends there. |
| 134 | expect(yield* split("SELECT r'a\\'; SELECT 2", "googlesql")).toEqual([ |
| 135 | "SELECT r'a\\'", |
| 136 | "SELECT 2", |
| 137 | ]); |
| 138 | }), |
| 139 | tags, |
| 140 | ); |
| 141 | }); |
| 142 | |
| 143 | describe("scanSqlScript", () => { |
| 144 | it.effect( |
| 145 | "masks literals and comments at the same offsets as the source", |
| 146 | () => |
| 147 | Effect.sync(() => { |
| 148 | const [statement] = scanSqlScript("CREATE INDEX \"i;x\" /* c */ ON t ('lit')", "postgres"); |
| 149 | expect(statement!.code.length).toEqual(statement!.sql.length); |
| 150 | // `"i;x"` (5) masks to `_` + 4 spaces; ` /* c */ ` (9) blanks out. |
| 151 | expect(statement!.code).toEqual(`CREATE INDEX _${" ".repeat(4 + 9)}ON t (_ )`); |
| 152 | const on = statement!.code.indexOf(" ON "); |
| 153 | expect(statement!.sql.slice(on)).toEqual(" ON t ('lit')"); |
| 154 | }), |
| 155 | tags, |
| 156 | ); |
| 157 | }); |
| 158 | |
| 159 | describe("scanSqlScript alignment", () => { |
| 160 | it.effect( |
| 161 | "keeps `code` aligned with `sql` across leading whitespace and comments", |
| 162 | () => |
| 163 | Effect.sync(() => { |
| 164 | const statements = scanSqlScript( |
| 165 | "SELECT 1;\n -- next\n CREATE INDEX i ON t (a)", |
| 166 | "postgres", |
| 167 | ); |
| 168 | const second = statements[1]!; |
| 169 | expect(second.code.length).toEqual(second.sql.length); |
| 170 | const at = second.code.indexOf("CREATE"); |
| 171 | expect(second.sql.slice(at, at + 6)).toEqual("CREATE"); |
| 172 | expect(second.text).toEqual("CREATE INDEX i ON t (a)"); |
| 173 | }), |
| 174 | tags, |
| 175 | ); |
| 176 | |
| 177 | it.effect( |
| 178 | "flags a quote or comment that never closes", |
| 179 | () => |
| 180 | Effect.sync(() => { |
| 181 | expect(scanSqlScript("SELECT 'oops", "postgres")[0]!.unterminated).toEqual("quote"); |
| 182 | expect(scanSqlScript("SELECT $$oops", "postgres")[0]!.unterminated).toEqual("quote"); |
| 183 | expect(scanSqlScript("SELECT 1 /* oops", "googlesql")[0]!.unterminated).toEqual("comment"); |
| 184 | expect(scanSqlScript("SELECT 'ok'", "postgres")[0]!.unterminated).toBeUndefined(); |
| 185 | }), |
| 186 | tags, |
| 187 | ); |
| 188 | }); |