Mercurial > urweb

comparison src/mysql.sml @ 874:3c7b48040dcf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
MySQL demo/sql succeeds in reading no rows
author Adam Chlipala <adamc@hcoop.net>
date Sun, 12 Jul 2009 15:05:40 -0400
parents 41971801b62d
children c50101ddf7fa
comparison
equal deleted inserted replaced
873:41971801b62d 874:3c7b48040dcf
53 | Blob => "MYSQL_TYPE_BLOB" 53 | Blob => "MYSQL_TYPE_BLOB"
54 | Channel => "MYSQL_TYPE_LONGLONG" 54 | Channel => "MYSQL_TYPE_LONGLONG"
55 | Client => "MYSQL_TYPE_LONG" 55 | Client => "MYSQL_TYPE_LONG"
56 | Nullable t => p_buffer_type t 56 | Nullable t => p_buffer_type t
57 57
58 fun p_sql_type_base t =
59 case t of
60 Int => "bigint"
61 | Float => "double"
62 | String => "longtext"
63 | Bool => "tinyint"
64 | Time => "timestamp"
65 | Blob => "longblob"
66 | Channel => "bigint"
67 | Client => "int"
68 | Nullable t => p_sql_type_base t
69
70 val ident = String.translate (fn #"'" => "PRIME"
71 | ch => str ch)
72
73 fun checkRel (table, checkNullable) (s, xts) =
74 let
75 val sl = CharVector.map Char.toLower s
76
77 val q = "SELECT COUNT(*) FROM information_schema." ^ table ^ " WHERE table_name = '"
78 ^ sl ^ "'"
79
80 val q' = String.concat ["SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '",
81 sl,
82 "' AND (",
83 String.concatWith " OR "
84 (map (fn (x, t) =>
85 String.concat ["(column_name = 'uw_",
86 CharVector.map
87 Char.toLower (ident x),
88 "' AND data_type = '",
89 p_sql_type_base t,
90 "'",
91 if checkNullable then
92 (" AND is_nullable = '"
93 ^ (if isNotNull t then
94 "NO"
95 else
96 "YES")
97 ^ "'")
98 else
99 "",
100 ")"]) xts),
101 ")"]
102
103 val q'' = String.concat ["SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '",
104 sl,
105 "' AND column_name LIKE 'uw_%'"]
106 in
107 box [string "if (mysql_query(conn->conn, \"",
108 string q,
109 string "\")) {",
110 newline,
111 box [string "mysql_close(conn->conn);",
112 newline,
113 string "uw_error(ctx, FATAL, \"Query failed:\\n",
114 string q,
115 string "\");",
116 newline],
117 string "}",
118 newline,
119 newline,
120
121 string "if ((res = mysql_store_result(conn->conn)) == NULL) {",
122 newline,
123 box [string "mysql_free_result(res);",
124 newline,
125 string "mysql_close(conn->conn);",
126 newline,
127 string "uw_error(ctx, FATAL, \"Result store failed:\\n",
128 string q,
129 string "\");",
130 newline],
131 string "}",
132 newline,
133 newline,
134
135 string "if (mysql_num_fields(res) != 1) {",
136 newline,
137 box [string "mysql_free_result(res);",
138 newline,
139 string "mysql_close(conn->conn);",
140 newline,
141 string "uw_error(ctx, FATAL, \"Bad column count:\\n",
142 string q,
143 string "\");",
144 newline],
145 string "}",
146 newline,
147 newline,
148
149 string "if ((row = mysql_fetch_row(res)) == NULL) {",
150 newline,
151 box [string "mysql_free_result(res);",
152 newline,
153 string "mysql_close(conn->conn);",
154 newline,
155 string "uw_error(ctx, FATAL, \"Row fetch failed:\\n",
156 string q,
157 string "\");",
158 newline],
159 string "}",
160 newline,
161 newline,
162
163 string "if (strcmp(row[0], \"1\")) {",
164 newline,
165 box [string "mysql_free_result(res);",
166 newline,
167 string "mysql_close(conn->conn);",
168 newline,
169 string "uw_error(ctx, FATAL, \"Table '",
170 string s,
171 string "' does not exist.\");",
172 newline],
173 string "}",
174 newline,
175 newline,
176 string "mysql_free_result(res);",
177 newline,
178 newline,
179
180 string "if (mysql_query(conn->conn, \"",
181 string q',
182 string "\")) {",
183 newline,
184 box [string "mysql_close(conn->conn);",
185 newline,
186 string "uw_error(ctx, FATAL, \"Query failed:\\n",
187 string q',
188 string "\");",
189 newline],
190 string "}",
191 newline,
192 newline,
193
194 string "if ((res = mysql_store_result(conn->conn)) == NULL) {",
195 newline,
196 box [string "mysql_free_result(res);",
197 newline,
198 string "mysql_close(conn->conn);",
199 newline,
200 string "uw_error(ctx, FATAL, \"Result store failed:\\n",
201 string q',
202 string "\");",
203 newline],
204 string "}",
205 newline,
206 newline,
207
208 string "if (mysql_num_fields(res) != 1) {",
209 newline,
210 box [string "mysql_free_result(res);",
211 newline,
212 string "mysql_close(conn->conn);",
213 newline,
214 string "uw_error(ctx, FATAL, \"Bad column count:\\n",
215 string q',
216 string "\");",
217 newline],
218 string "}",
219 newline,
220 newline,
221
222 string "if ((row = mysql_fetch_row(res)) == NULL) {",
223 newline,
224 box [string "mysql_free_result(res);",
225 newline,
226 string "mysql_close(conn->conn);",
227 newline,
228 string "uw_error(ctx, FATAL, \"Row fetch failed:\\n",
229 string q',
230 string "\");",
231 newline],
232 string "}",
233 newline,
234 newline,
235
236 string "if (strcmp(row[0], \"",
237 string (Int.toString (length xts)),
238 string "\")) {",
239 newline,
240 box [string "mysql_free_result(res);",
241 newline,
242 string "mysql_close(conn->conn);",
243 newline,
244 string "uw_error(ctx, FATAL, \"Table '",
245 string s,
246 string "' has the wrong column types.\");",
247 newline],
248 string "}",
249 newline,
250 newline,
251 string "mysql_free_result(res);",
252 newline,
253 newline,
254
255 string "if (mysql_query(conn->conn, \"",
256 string q'',
257 string "\")) {",
258 newline,
259 box [string "mysql_close(conn->conn);",
260 newline,
261 string "uw_error(ctx, FATAL, \"Query failed:\\n",
262 string q'',
263 string "\");",
264 newline],
265 string "}",
266 newline,
267 newline,
268
269 string "if ((res = mysql_store_result(conn->conn)) == NULL) {",
270 newline,
271 box [string "mysql_free_result(res);",
272 newline,
273 string "mysql_close(conn->conn);",
274 newline,
275 string "uw_error(ctx, FATAL, \"Result store failed:\\n",
276 string q'',
277 string "\");",
278 newline],
279 string "}",
280 newline,
281 newline,
282
283 string "if (mysql_num_fields(res) != 1) {",
284 newline,
285 box [string "mysql_free_result(res);",
286 newline,
287 string "mysql_close(conn->conn);",
288 newline,
289 string "uw_error(ctx, FATAL, \"Bad column count:\\n",
290 string q'',
291 string "\");",
292 newline],
293 string "}",
294 newline,
295 newline,
296
297 string "if ((row = mysql_fetch_row(res)) == NULL) {",
298 newline,
299 box [string "mysql_free_result(res);",
300 newline,
301 string "mysql_close(conn->conn);",
302 newline,
303 string "uw_error(ctx, FATAL, \"Row fetch failed:\\n",
304 string q'',
305 string "\");",
306 newline],
307 string "}",
308 newline,
309 newline,
310
311 string "if (strcmp(row[0], \"",
312 string (Int.toString (length xts)),
313 string "\")) {",
314 newline,
315 box [string "mysql_free_result(res);",
316 newline,
317 string "mysql_close(conn->conn);",
318 newline,
319 string "uw_error(ctx, FATAL, \"Table '",
320 string s,
321 string "' has extra columns.\");",
322 newline],
323 string "}",
324 newline,
325 newline,
326 string "mysql_free_result(res);",
327 newline]
328 end
329
58 fun init {dbstring, prepared = ss, tables, views, sequences} = 330 fun init {dbstring, prepared = ss, tables, views, sequences} =
59 let 331 let
60 val host = ref NONE 332 val host = ref NONE
61 val user = ref NONE 333 val user = ref NONE
62 val passwd = ref NONE 334 val passwd = ref NONE
100 ss], 372 ss],
101 string "} uw_conn;", 373 string "} uw_conn;",
102 newline, 374 newline,
103 newline, 375 newline,
104 376
377 string "void uw_client_init(void) {",
378 newline,
379 box [string "if (mysql_library_init(0, NULL, NULL)) {",
380 newline,
381 box [string "fprintf(stderr, \"Could not initialize MySQL library\\n\");",
382 newline,
383 string "exit(1);",
384 newline],
385 string "}",
386 newline],
387 string "}",
388 newline,
389 newline,
390
105 if #persistent (currentProtocol ()) then 391 if #persistent (currentProtocol ()) then
106 box [string "static void uw_db_prepare(uw_context ctx) {", 392 box [string "static void uw_db_validate(uw_context ctx) {",
393 newline,
394 string "uw_conn *conn = uw_get_db(ctx);",
395 newline,
396 string "MYSQL_RES *res;",
397 newline,
398 string "MYSQL_ROW row;",
399 newline,
400 newline,
401 p_list_sep newline (checkRel ("tables", true)) tables,
402 p_list_sep newline (checkRel ("views", false)) views,
403 string "}",
404 newline,
405 newline,
406
407 string "static void uw_db_prepare(uw_context ctx) {",
107 newline, 408 newline,
108 string "uw_conn *conn = uw_get_db(ctx);", 409 string "uw_conn *conn = uw_get_db(ctx);",
109 newline, 410 newline,
110 string "MYSQL_STMT *stmt;", 411 string "MYSQL_STMT *stmt;",
111 newline, 412 newline,
145 string "if (stmt == NULL) {", 446 string "if (stmt == NULL) {",
146 newline, 447 newline,
147 uhoh false "Out of memory allocating prepared statement" [], 448 uhoh false "Out of memory allocating prepared statement" [],
148 string "}", 449 string "}",
149 newline, 450 newline,
451 string "conn->p",
452 string (Int.toString i),
453 string " = stmt;",
454 newline,
150 455
151 string "if (mysql_stmt_prepare(stmt, \"", 456 string "if (mysql_stmt_prepare(stmt, \"",
152 string (String.toString s), 457 string (String.toString s),
153 string "\", ", 458 string "\", ",
154 string (Int.toString (size s)), 459 string (Int.toString (size s)),
160 newline, 465 newline,
161 string "msg[1023] = 0;", 466 string "msg[1023] = 0;",
162 newline, 467 newline,
163 uhoh true "Error preparing statement: %s" ["msg"]], 468 uhoh true "Error preparing statement: %s" ["msg"]],
164 string "}", 469 string "}",
165 newline,
166 string "conn->p",
167 string (Int.toString i),
168 string " = stmt;",
169 newline] 470 newline]
170 end) 471 end)
171 ss, 472 ss,
172 473
173 string "}"] 474 string "}"]
197 case !port of 498 case !port of
198 NONE => string "0" 499 NONE => string "0"
199 | SOME n => string (Int.toString n), 500 | SOME n => string (Int.toString n),
200 string ", ", 501 string ", ",
201 stringOf unix_socket, 502 stringOf unix_socket,
202 string ", 0)) {", 503 string ", 0) == NULL) {",
203 newline, 504 newline,
204 box [string "char msg[1024];", 505 box [string "char msg[1024];",
205 newline, 506 newline,
206 string "strncpy(msg, mysql_error(mysql), 1024);", 507 string "strncpy(msg, mysql_error(mysql), 1024);",
207 newline, 508 newline,
212 string "uw_error(ctx, BOUNDED_RETRY, ", 513 string "uw_error(ctx, BOUNDED_RETRY, ",
213 string "\"Connection to MySQL server failed: %s\", msg);"], 514 string "\"Connection to MySQL server failed: %s\", msg);"],
214 newline, 515 newline,
215 string "}", 516 string "}",
216 newline, 517 newline,
217 string "conn = calloc(1, sizeof(conn));", 518 string "conn = calloc(1, sizeof(uw_conn));",
218 newline, 519 newline,
219 string "conn->conn = mysql;", 520 string "conn->conn = mysql;",
220 newline, 521 newline,
221 string "uw_set_db(ctx, conn);", 522 string "uw_set_db(ctx, conn);",
222 newline, 523 newline,
469 end) cols, 770 end) cols,
470 newline, 771 newline,
471 772
472 string "if (mysql_stmt_execute(stmt)) uw_error(ctx, FATAL, \"", 773 string "if (mysql_stmt_execute(stmt)) uw_error(ctx, FATAL, \"",
473 string (ErrorMsg.spanToString loc), 774 string (ErrorMsg.spanToString loc),
474 string ": Error executing query\");", 775 string ": Error executing query: %s\", mysql_error(conn->conn));",
475 newline, 776 newline,
476 newline, 777 newline,
477 778
478 string "if (mysql_stmt_store_result(stmt)) uw_error(ctx, FATAL, \"", 779 string "if (mysql_stmt_store_result(stmt)) uw_error(ctx, FATAL, \"",
479 string (ErrorMsg.spanToString loc), 780 string (ErrorMsg.spanToString loc),
480 string ": Error storing query result\");", 781 string ": Error storing query result: %s\", mysql_error(conn->conn));",
481 newline, 782 newline,
482 newline, 783 newline,
483 784
484 string "if (mysql_stmt_bind_result(stmt, out)) uw_error(ctx, FATAL, \"", 785 string "if (mysql_stmt_bind_result(stmt, out)) uw_error(ctx, FATAL, \"",
485 string (ErrorMsg.spanToString loc), 786 string (ErrorMsg.spanToString loc),
486 string ": Error binding query result\");", 787 string ": Error binding query result: %s\", mysql_error(conn->conn));",
487 newline, 788 newline,
488 newline, 789 newline,
489 790
490 string "uw_end_region(ctx);", 791 string "uw_end_region(ctx);",
491 newline, 792 newline,
494 doCols p_getcol, 795 doCols p_getcol,
495 string "}", 796 string "}",
496 newline, 797 newline,
497 newline, 798 newline,
498 799
499 string "if (r != MYSQL_NO_DATA) uw_error(ctx, FATAL, \"", 800 string "if (r == 1) uw_error(ctx, FATAL, \"",
500 string (ErrorMsg.spanToString loc), 801 string (ErrorMsg.spanToString loc),
501 string ": query result fetching failed\");", 802 string ": query result fetching failed (%d): %s\", r, mysql_error(conn->conn));",
502 newline] 803 newline]
503 804
504 fun query {loc, cols, doCols} = 805 fun query {loc, cols, doCols} =
505 box [string "uw_conn *conn = uw_get_db(ctx);", 806 box [string "uw_conn *conn = uw_get_db(ctx);",
506 newline, 807 newline,
512 newline, 813 newline,
513 string "uw_push_cleanup(ctx, (void (*)(void *))mysql_stmt_close, stmt);", 814 string "uw_push_cleanup(ctx, (void (*)(void *))mysql_stmt_close, stmt);",
514 newline, 815 newline,
515 string "if (mysql_stmt_prepare(stmt, query, strlen(query))) uw_error(ctx, FATAL, \"", 816 string "if (mysql_stmt_prepare(stmt, query, strlen(query))) uw_error(ctx, FATAL, \"",
516 string (ErrorMsg.spanToString loc), 817 string (ErrorMsg.spanToString loc),
517 string "\");", 818 string ": error preparing statement: %s\", mysql_error(conn->conn));",
518 newline, 819 newline,
519 newline, 820 newline,
520 821
521 p_list_sepi (box []) (fn i => fn t => 822 p_list_sepi (box []) (fn i => fn t =>
522 let 823 let
758 fun dml _ = box [] 1059 fun dml _ = box []
759 fun dmlPrepared _ = box [] 1060 fun dmlPrepared _ = box []
760 fun nextval _ = box [] 1061 fun nextval _ = box []
761 fun nextvalPrepared _ = box [] 1062 fun nextvalPrepared _ = box []
762 1063
1064 fun sqlifyString s = "CAST('" ^ String.translate (fn #"'" => "\\'"
1065 | #"\\" => "\\\\"
1066 | ch =>
1067 if Char.isPrint ch then
1068 str ch
1069 else
1070 (ErrorMsg.error
1071 "Non-printing character found in SQL string literal";
1072 ""))
1073 (String.toString s) ^ "' AS longtext)"
1074
1075 fun p_cast (s, t) = "CAST(" ^ s ^ " AS " ^ p_sql_type t ^ ")"
1076
1077 fun p_blank _ = "?"
1078
763 val () = addDbms {name = "mysql", 1079 val () = addDbms {name = "mysql",
764 header = "mysql/mysql.h", 1080 header = "mysql/mysql.h",
765 link = "-lmysqlclient", 1081 link = "-lmysqlclient",
766 global_init = box [string "void uw_client_init() {",
767 newline,
768 box [string "if (mysql_library_init(0, NULL, NULL)) {",
769 newline,
770 box [string "fprintf(stderr, \"Could not initialize MySQL library\\n\");",
771 newline,
772 string "exit(1);",
773 newline],
774 string "}",
775 newline],
776 string "}",
777 newline],
778 init = init, 1082 init = init,
779 p_sql_type = p_sql_type, 1083 p_sql_type = p_sql_type,
780 query = query, 1084 query = query,
781 queryPrepared = queryPrepared, 1085 queryPrepared = queryPrepared,
782 dml = dml, 1086 dml = dml,
783 dmlPrepared = dmlPrepared, 1087 dmlPrepared = dmlPrepared,
784 nextval = nextval, 1088 nextval = nextval,
785 nextvalPrepared = nextvalPrepared} 1089 nextvalPrepared = nextvalPrepared,
1090 sqlifyString = sqlifyString,
1091 p_cast = p_cast,
1092 p_blank = p_blank,
1093 supportsDeleteAs = false}
786 1094
787 end 1095 end