Mercurial > urweb

annotate src/postgres.sml @ 880:8e9f2d247dba

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Testing nested queries
author Adam Chlipala <adamc@hcoop.net>
date Fri, 17 Jul 2009 12:25:34 -0400
parents b2a175a0f2ef
children 9c1b7e46eed2
rev   line source
adamc@866 1 (* Copyright (c) 2008-2009, Adam Chlipala
adamc@866 2 * All rights reserved.
adamc@866 3 *
adamc@866 4 * Redistribution and use in source and binary forms, with or without
adamc@866 5 * modification, are permitted provided that the following conditions are met:
adamc@866 6 *
adamc@866 7 * - Redistributions of source code must retain the above copyright notice,
adamc@866 8 * this list of conditions and the following disclaimer.
adamc@866 9 * - Redistributions in binary form must reproduce the above copyright notice,
adamc@866 10 * this list of conditions and the following disclaimer in the documentation
adamc@866 11 * and/or other materials provided with the distribution.
adamc@866 12 * - The names of contributors may not be used to endorse or promote products
adamc@866 13 * derived from this software without specific prior written permission.
adamc@866 14 *
adamc@866 15 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
adamc@866 16 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
adamc@866 17 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
adamc@866 18 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
adamc@866 19 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
adamc@866 20 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
adamc@866 21 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
adamc@866 22 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
adamc@866 23 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
adamc@866 24 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
adamc@866 25 * POSSIBILITY OF SUCH DAMAGE.
adamc@866 26 *)
adamc@866 27
adamc@866 28 structure Postgres :> POSTGRES = struct
adamc@866 29
adamc@866 30 open Settings
adamc@866 31 open Print.PD
adamc@866 32 open Print
adamc@866 33
adamc@870 34 val ident = String.translate (fn #"'" => "PRIME"
adamc@870 35 | ch => str ch)
adamc@870 36
adamc@873 37 fun p_sql_type t =
adamc@873 38 case t of
adamc@873 39 Int => "int8"
adamc@873 40 | Float => "float8"
adamc@873 41 | String => "text"
adamc@873 42 | Bool => "bool"
adamc@873 43 | Time => "timestamp"
adamc@873 44 | Blob => "bytea"
adamc@873 45 | Channel => "int8"
adamc@873 46 | Client => "int4"
adamc@873 47 | Nullable t => p_sql_type t
adamc@873 48
adamc@870 49 fun p_sql_type_base t =
adamc@870 50 case t of
adamc@871 51 Int => "bigint"
adamc@871 52 | Float => "double precision"
adamc@870 53 | String => "text"
adamc@871 54 | Bool => "boolean"
adamc@871 55 | Time => "timestamp without time zone"
adamc@870 56 | Blob => "bytea"
adamc@871 57 | Channel => "bigint"
adamc@871 58 | Client => "integer"
adamc@870 59 | Nullable t => p_sql_type_base t
adamc@870 60
adamc@872 61 fun checkRel (table, checkNullable) (s, xts) =
adamc@871 62 let
adamc@871 63 val sl = CharVector.map Char.toLower s
adamc@871 64
adamc@872 65 val q = "SELECT COUNT(*) FROM information_schema." ^ table ^ " WHERE table_name = '"
adamc@871 66 ^ sl ^ "'"
adamc@871 67
adamc@871 68 val q' = String.concat ["SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '",
adamc@871 69 sl,
adamc@871 70 "' AND (",
adamc@871 71 String.concatWith " OR "
adamc@871 72 (map (fn (x, t) =>
adamc@871 73 String.concat ["(column_name = 'uw_",
adamc@871 74 CharVector.map
adamc@871 75 Char.toLower (ident x),
adamc@871 76 "' AND data_type = '",
adamc@871 77 p_sql_type_base t,
adamc@872 78 "'",
adamc@872 79 if checkNullable then
adamc@872 80 (" AND is_nullable = '"
adamc@872 81 ^ (if isNotNull t then
adamc@872 82 "NO"
adamc@872 83 else
adamc@872 84 "YES")
adamc@872 85 ^ "'")
adamc@871 86 else
adamc@872 87 "",
adamc@872 88 ")"]) xts),
adamc@871 89 ")"]
adamc@871 90
adamc@871 91 val q'' = String.concat ["SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '",
adamc@871 92 sl,
adamc@871 93 "' AND column_name LIKE 'uw_%'"]
adamc@871 94 in
adamc@871 95 box [string "res = PQexec(conn, \"",
adamc@871 96 string q,
adamc@871 97 string "\");",
adamc@871 98 newline,
adamc@871 99 newline,
adamc@871 100 string "if (res == NULL) {",
adamc@871 101 newline,
adamc@871 102 box [string "PQfinish(conn);",
adamc@871 103 newline,
adamc@871 104 string "uw_error(ctx, FATAL, \"Out of memory allocating query result.\");",
adamc@871 105 newline],
adamc@871 106 string "}",
adamc@871 107 newline,
adamc@871 108 newline,
adamc@871 109 string "if (PQresultStatus(res) != PGRES_TUPLES_OK) {",
adamc@871 110 newline,
adamc@871 111 box [string "char msg[1024];",
adamc@871 112 newline,
adamc@871 113 string "strncpy(msg, PQerrorMessage(conn), 1024);",
adamc@871 114 newline,
adamc@871 115 string "msg[1023] = 0;",
adamc@871 116 newline,
adamc@871 117 string "PQclear(res);",
adamc@871 118 newline,
adamc@871 119 string "PQfinish(conn);",
adamc@871 120 newline,
adamc@871 121 string "uw_error(ctx, FATAL, \"Query failed:\\n",
adamc@871 122 string q,
adamc@871 123 string "\\n%s\", msg);",
adamc@871 124 newline],
adamc@871 125 string "}",
adamc@871 126 newline,
adamc@871 127 newline,
adamc@871 128 string "if (strcmp(PQgetvalue(res, 0, 0), \"1\")) {",
adamc@871 129 newline,
adamc@871 130 box [string "PQclear(res);",
adamc@871 131 newline,
adamc@871 132 string "PQfinish(conn);",
adamc@871 133 newline,
adamc@871 134 string "uw_error(ctx, FATAL, \"Table '",
adamc@871 135 string s,
adamc@871 136 string "' does not exist.\");",
adamc@871 137 newline],
adamc@871 138 string "}",
adamc@871 139 newline,
adamc@871 140 newline,
adamc@871 141 string "PQclear(res);",
adamc@871 142 newline,
adamc@871 143
adamc@871 144 string "res = PQexec(conn, \"",
adamc@871 145 string q',
adamc@871 146 string "\");",
adamc@871 147 newline,
adamc@871 148 newline,
adamc@871 149 string "if (res == NULL) {",
adamc@871 150 newline,
adamc@871 151 box [string "PQfinish(conn);",
adamc@871 152 newline,
adamc@871 153 string "uw_error(ctx, FATAL, \"Out of memory allocating query result.\");",
adamc@871 154 newline],
adamc@871 155 string "}",
adamc@871 156 newline,
adamc@871 157 newline,
adamc@871 158 string "if (PQresultStatus(res) != PGRES_TUPLES_OK) {",
adamc@871 159 newline,
adamc@871 160 box [string "char msg[1024];",
adamc@871 161 newline,
adamc@871 162 string "strncpy(msg, PQerrorMessage(conn), 1024);",
adamc@871 163 newline,
adamc@871 164 string "msg[1023] = 0;",
adamc@871 165 newline,
adamc@871 166 string "PQclear(res);",
adamc@871 167 newline,
adamc@871 168 string "PQfinish(conn);",
adamc@871 169 newline,
adamc@871 170 string "uw_error(ctx, FATAL, \"Query failed:\\n",
adamc@871 171 string q',
adamc@871 172 string "\\n%s\", msg);",
adamc@871 173 newline],
adamc@871 174 string "}",
adamc@871 175 newline,
adamc@871 176 newline,
adamc@871 177 string "if (strcmp(PQgetvalue(res, 0, 0), \"",
adamc@871 178 string (Int.toString (length xts)),
adamc@871 179 string "\")) {",
adamc@871 180 newline,
adamc@871 181 box [string "PQclear(res);",
adamc@871 182 newline,
adamc@871 183 string "PQfinish(conn);",
adamc@871 184 newline,
adamc@871 185 string "uw_error(ctx, FATAL, \"Table '",
adamc@871 186 string s,
adamc@871 187 string "' has the wrong column types.\");",
adamc@871 188 newline],
adamc@871 189 string "}",
adamc@871 190 newline,
adamc@871 191 newline,
adamc@871 192 string "PQclear(res);",
adamc@871 193 newline,
adamc@871 194 newline,
adamc@871 195
adamc@871 196 string "res = PQexec(conn, \"",
adamc@871 197 string q'',
adamc@871 198 string "\");",
adamc@871 199 newline,
adamc@871 200 newline,
adamc@871 201 string "if (res == NULL) {",
adamc@871 202 newline,
adamc@871 203 box [string "PQfinish(conn);",
adamc@871 204 newline,
adamc@871 205 string "uw_error(ctx, FATAL, \"Out of memory allocating query result.\");",
adamc@871 206 newline],
adamc@871 207 string "}",
adamc@871 208 newline,
adamc@871 209 newline,
adamc@871 210 string "if (PQresultStatus(res) != PGRES_TUPLES_OK) {",
adamc@871 211 newline,
adamc@871 212 box [string "char msg[1024];",
adamc@871 213 newline,
adamc@871 214 string "strncpy(msg, PQerrorMessage(conn), 1024);",
adamc@871 215 newline,
adamc@871 216 string "msg[1023] = 0;",
adamc@871 217 newline,
adamc@871 218 string "PQclear(res);",
adamc@871 219 newline,
adamc@871 220 string "PQfinish(conn);",
adamc@871 221 newline,
adamc@871 222 string "uw_error(ctx, FATAL, \"Query failed:\\n",
adamc@871 223 string q'',
adamc@871 224 string "\\n%s\", msg);",
adamc@871 225 newline],
adamc@871 226 string "}",
adamc@871 227 newline,
adamc@871 228 newline,
adamc@871 229 string "if (strcmp(PQgetvalue(res, 0, 0), \"",
adamc@871 230 string (Int.toString (length xts)),
adamc@871 231 string "\")) {",
adamc@871 232 newline,
adamc@871 233 box [string "PQclear(res);",
adamc@871 234 newline,
adamc@871 235 string "PQfinish(conn);",
adamc@871 236 newline,
adamc@871 237 string "uw_error(ctx, FATAL, \"Table '",
adamc@871 238 string s,
adamc@871 239 string "' has extra columns.\");",
adamc@871 240 newline],
adamc@871 241 string "}",
adamc@871 242 newline,
adamc@871 243 newline,
adamc@871 244 string "PQclear(res);",
adamc@871 245 newline]
adamc@871 246 end
adamc@871 247
adamc@872 248 fun init {dbstring, prepared = ss, tables, views, sequences} =
adamc@866 249 box [if #persistent (currentProtocol ()) then
adamc@879 250 box [string "void uw_client_init(void) {",
adamc@879 251 newline,
adamc@879 252 box [string "uw_sqlfmtInt = \"%lld::int8%n\";",
adamc@879 253 newline,
adamc@879 254 string "uw_sqlfmtFloat = \"%g::float8%n\";",
adamc@879 255 newline,
adamc@879 256 string "uw_Estrings = 1;",
adamc@879 257 newline,
adamc@879 258 string "uw_sqlsuffixString = \"::text\";",
adamc@879 259 newline,
adamc@879 260 string "uw_sqlsuffixBlob = \"::bytea\";",
adamc@879 261 newline,
adamc@879 262 string "uw_sqlfmtUint4 = \"%u::int4%n\";",
adamc@879 263 newline],
adamc@879 264 string "}",
adamc@874 265 newline,
adamc@874 266 newline,
adamc@874 267
adamc@874 268 string "static void uw_db_validate(uw_context ctx) {",
adamc@870 269 newline,
adamc@870 270 string "PGconn *conn = uw_get_db(ctx);",
adamc@870 271 newline,
adamc@870 272 string "PGresult *res;",
adamc@870 273 newline,
adamc@870 274 newline,
adamc@872 275 p_list_sep newline (checkRel ("tables", true)) tables,
adamc@872 276 p_list_sep newline (checkRel ("views", false)) views,
adamc@870 277
adamc@870 278 p_list_sep newline
adamc@870 279 (fn s =>
adamc@870 280 let
adamc@870 281 val sl = CharVector.map Char.toLower s
adamc@870 282
adamc@870 283 val q = "SELECT COUNT(*) FROM pg_class WHERE relname = '"
adamc@870 284 ^ sl ^ "' AND relkind = 'S'"
adamc@870 285 in
adamc@870 286 box [string "res = PQexec(conn, \"",
adamc@870 287 string q,
adamc@870 288 string "\");",
adamc@870 289 newline,
adamc@870 290 newline,
adamc@870 291 string "if (res == NULL) {",
adamc@870 292 newline,
adamc@870 293 box [string "PQfinish(conn);",
adamc@870 294 newline,
adamc@870 295 string "uw_error(ctx, FATAL, \"Out of memory allocating query result.\");",
adamc@870 296 newline],
adamc@870 297 string "}",
adamc@870 298 newline,
adamc@870 299 newline,
adamc@870 300 string "if (PQresultStatus(res) != PGRES_TUPLES_OK) {",
adamc@870 301 newline,
adamc@870 302 box [string "char msg[1024];",
adamc@870 303 newline,
adamc@870 304 string "strncpy(msg, PQerrorMessage(conn), 1024);",
adamc@870 305 newline,
adamc@870 306 string "msg[1023] = 0;",
adamc@870 307 newline,
adamc@870 308 string "PQclear(res);",
adamc@870 309 newline,
adamc@870 310 string "PQfinish(conn);",
adamc@870 311 newline,
adamc@870 312 string "uw_error(ctx, FATAL, \"Query failed:\\n",
adamc@870 313 string q,
adamc@870 314 string "\\n%s\", msg);",
adamc@870 315 newline],
adamc@870 316 string "}",
adamc@870 317 newline,
adamc@870 318 newline,
adamc@870 319 string "if (strcmp(PQgetvalue(res, 0, 0), \"1\")) {",
adamc@870 320 newline,
adamc@870 321 box [string "PQclear(res);",
adamc@870 322 newline,
adamc@870 323 string "PQfinish(conn);",
adamc@870 324 newline,
adamc@870 325 string "uw_error(ctx, FATAL, \"Sequence '",
adamc@870 326 string s,
adamc@870 327 string "' does not exist.\");",
adamc@870 328 newline],
adamc@870 329 string "}",
adamc@870 330 newline,
adamc@870 331 newline,
adamc@870 332 string "PQclear(res);",
adamc@870 333 newline]
adamc@870 334 end) sequences,
adamc@870 335
adamc@870 336 string "}",
adamc@870 337
adamc@870 338 string "static void uw_db_prepare(uw_context ctx) {",
adamc@866 339 newline,
adamc@866 340 string "PGconn *conn = uw_get_db(ctx);",
adamc@866 341 newline,
adamc@866 342 string "PGresult *res;",
adamc@866 343 newline,
adamc@866 344 newline,
adamc@866 345
adamc@866 346 p_list_sepi newline (fn i => fn (s, n) =>
adamc@866 347 box [string "res = PQprepare(conn, \"uw",
adamc@866 348 string (Int.toString i),
adamc@866 349 string "\", \"",
adamc@866 350 string (String.toString s),
adamc@866 351 string "\", ",
adamc@866 352 string (Int.toString n),
adamc@866 353 string ", NULL);",
adamc@866 354 newline,
adamc@866 355 string "if (PQresultStatus(res) != PGRES_COMMAND_OK) {",
adamc@866 356 newline,
adamc@866 357 box [string "char msg[1024];",
adamc@866 358 newline,
adamc@866 359 string "strncpy(msg, PQerrorMessage(conn), 1024);",
adamc@866 360 newline,
adamc@866 361 string "msg[1023] = 0;",
adamc@866 362 newline,
adamc@866 363 string "PQclear(res);",
adamc@866 364 newline,
adamc@866 365 string "PQfinish(conn);",
adamc@866 366 newline,
adamc@866 367 string "uw_error(ctx, FATAL, \"Unable to create prepared statement:\\n",
adamc@866 368 string (String.toString s),
adamc@866 369 string "\\n%s\", msg);",
adamc@866 370 newline],
adamc@866 371 string "}",
adamc@866 372 newline,
adamc@866 373 string "PQclear(res);",
adamc@866 374 newline])
adamc@866 375 ss,
adamc@866 376
adamc@866 377 string "}",
adamc@866 378 newline,
adamc@866 379 newline,
adamc@866 380
adamc@866 381 string "void uw_db_close(uw_context ctx) {",
adamc@866 382 newline,
adamc@866 383 string "PQfinish(uw_get_db(ctx));",
adamc@866 384 newline,
adamc@866 385 string "}",
adamc@866 386 newline,
adamc@866 387 newline,
adamc@866 388
adamc@866 389 string "int uw_db_begin(uw_context ctx) {",
adamc@866 390 newline,
adamc@866 391 string "PGconn *conn = uw_get_db(ctx);",
adamc@866 392 newline,
adamc@866 393 string "PGresult *res = PQexec(conn, \"BEGIN ISOLATION LEVEL SERIALIZABLE\");",
adamc@866 394 newline,
adamc@866 395 newline,
adamc@866 396 string "if (res == NULL) return 1;",
adamc@866 397 newline,
adamc@866 398 newline,
adamc@866 399 string "if (PQresultStatus(res) != PGRES_COMMAND_OK) {",
adamc@866 400 box [string "PQclear(res);",
adamc@866 401 newline,
adamc@866 402 string "return 1;",
adamc@866 403 newline],
adamc@866 404 string "}",
adamc@866 405 newline,
adamc@866 406 string "return 0;",
adamc@866 407 newline,
adamc@866 408 string "}",
adamc@866 409 newline,
adamc@866 410 newline,
adamc@866 411
adamc@866 412 string "int uw_db_commit(uw_context ctx) {",
adamc@866 413 newline,
adamc@866 414 string "PGconn *conn = uw_get_db(ctx);",
adamc@866 415 newline,
adamc@866 416 string "PGresult *res = PQexec(conn, \"COMMIT\");",
adamc@866 417 newline,
adamc@866 418 newline,
adamc@866 419 string "if (res == NULL) return 1;",
adamc@866 420 newline,
adamc@866 421 newline,
adamc@866 422 string "if (PQresultStatus(res) != PGRES_COMMAND_OK) {",
adamc@866 423 box [string "PQclear(res);",
adamc@866 424 newline,
adamc@866 425 string "return 1;",
adamc@866 426 newline],
adamc@866 427 string "}",
adamc@866 428 newline,
adamc@866 429 string "return 0;",
adamc@866 430 newline,
adamc@866 431 string "}",
adamc@866 432 newline,
adamc@866 433 newline,
adamc@866 434
adamc@866 435 string "int uw_db_rollback(uw_context ctx) {",
adamc@866 436 newline,
adamc@866 437 string "PGconn *conn = uw_get_db(ctx);",
adamc@866 438 newline,
adamc@866 439 string "PGresult *res = PQexec(conn, \"ROLLBACK\");",
adamc@866 440 newline,
adamc@866 441 newline,
adamc@866 442 string "if (res == NULL) return 1;",
adamc@866 443 newline,
adamc@866 444 newline,
adamc@866 445 string "if (PQresultStatus(res) != PGRES_COMMAND_OK) {",
adamc@866 446 box [string "PQclear(res);",
adamc@866 447 newline,
adamc@866 448 string "return 1;",
adamc@866 449 newline],
adamc@866 450 string "}",
adamc@866 451 newline,
adamc@866 452 string "return 0;",
adamc@866 453 newline,
adamc@866 454 string "}",
adamc@866 455 newline,
adamc@866 456 newline]
adamc@866 457 else
adamc@870 458 box [string "static void uw_db_validate(uw_context ctx) { }",
adamc@870 459 newline,
adamc@870 460 string "static void uw_db_prepare(uw_context ctx) { }"],
adamc@870 461
adamc@866 462 newline,
adamc@866 463 newline,
adamc@866 464
adamc@866 465 string "void uw_db_init(uw_context ctx) {",
adamc@866 466 newline,
adamc@866 467 string "PGconn *conn = PQconnectdb(\"",
adamc@866 468 string (String.toString dbstring),
adamc@866 469 string "\");",
adamc@866 470 newline,
adamc@866 471 string "if (conn == NULL) uw_error(ctx, FATAL, ",
adamc@866 472 string "\"libpq can't allocate a connection.\");",
adamc@866 473 newline,
adamc@866 474 string "if (PQstatus(conn) != CONNECTION_OK) {",
adamc@866 475 newline,
adamc@866 476 box [string "char msg[1024];",
adamc@866 477 newline,
adamc@866 478 string "strncpy(msg, PQerrorMessage(conn), 1024);",
adamc@866 479 newline,
adamc@866 480 string "msg[1023] = 0;",
adamc@866 481 newline,
adamc@866 482 string "PQfinish(conn);",
adamc@866 483 newline,
adamc@866 484 string "uw_error(ctx, BOUNDED_RETRY, ",
adamc@866 485 string "\"Connection to Postgres server failed: %s\", msg);"],
adamc@866 486 newline,
adamc@866 487 string "}",
adamc@866 488 newline,
adamc@866 489 string "uw_set_db(ctx, conn);",
adamc@866 490 newline,
adamc@866 491 string "uw_db_validate(ctx);",
adamc@866 492 newline,
adamc@866 493 string "uw_db_prepare(ctx);",
adamc@866 494 newline,
adamc@866 495 string "}"]
adamc@866 496
adamc@880 497 fun p_getcol {loc, wontLeakStrings, col = i, typ = t} =
adamc@867 498 let
adamc@867 499 fun p_unsql t e eLen =
adamc@867 500 case t of
adamc@867 501 Int => box [string "uw_Basis_stringToInt_error(ctx, ", e, string ")"]
adamc@867 502 | Float => box [string "uw_Basis_stringToFloat_error(ctx, ", e, string ")"]
adamc@867 503 | String =>
adamc@867 504 if wontLeakStrings then
adamc@867 505 e
adamc@867 506 else
adamc@867 507 box [string "uw_strdup(ctx, ", e, string ")"]
adamc@867 508 | Bool => box [string "uw_Basis_stringToBool_error(ctx, ", e, string ")"]
adamc@867 509 | Time => box [string "uw_Basis_stringToTime_error(ctx, ", e, string ")"]
adamc@867 510 | Blob => box [string "uw_Basis_stringToBlob_error(ctx, ",
adamc@867 511 e,
adamc@867 512 string ", ",
adamc@867 513 eLen,
adamc@867 514 string ")"]
adamc@867 515 | Channel => box [string "uw_Basis_stringToChannel_error(ctx, ", e, string ")"]
adamc@867 516 | Client => box [string "uw_Basis_stringToClient_error(ctx, ", e, string ")"]
adamc@867 517
adamc@867 518 | Nullable _ => raise Fail "Postgres: Recursive Nullable"
adamc@867 519
adamc@867 520 fun getter t =
adamc@867 521 case t of
adamc@867 522 Nullable t =>
adamc@867 523 box [string "(PQgetisnull(res, i, ",
adamc@867 524 string (Int.toString i),
adamc@867 525 string ") ? NULL : ",
adamc@867 526 case t of
adamc@867 527 String => getter t
adamc@867 528 | _ => box [string "({",
adamc@867 529 newline,
adamc@874 530 string (p_sql_ctype t),
adamc@867 531 space,
adamc@867 532 string "*tmp = uw_malloc(ctx, sizeof(",
adamc@874 533 string (p_sql_ctype t),
adamc@867 534 string "));",
adamc@867 535 newline,
adamc@867 536 string "*tmp = ",
adamc@867 537 getter t,
adamc@867 538 string ";",
adamc@867 539 newline,
adamc@867 540 string "tmp;",
adamc@867 541 newline,
adamc@867 542 string "})"],
adamc@867 543 string ")"]
adamc@867 544 | _ =>
adamc@867 545 box [string "(PQgetisnull(res, i, ",
adamc@867 546 string (Int.toString i),
adamc@867 547 string ") ? ",
adamc@867 548 box [string "({",
adamc@874 549 string (p_sql_ctype t),
adamc@867 550 space,
adamc@867 551 string "tmp;",
adamc@867 552 newline,
adamc@880 553 string "uw_error(ctx, FATAL, \"",
adamc@880 554 string (ErrorMsg.spanToString loc),
adamc@880 555 string ": Unexpectedly NULL field #",
adamc@867 556 string (Int.toString i),
adamc@867 557 string "\");",
adamc@867 558 newline,
adamc@867 559 string "tmp;",
adamc@867 560 newline,
adamc@867 561 string "})"],
adamc@867 562 string " : ",
adamc@867 563 p_unsql t
adamc@867 564 (box [string "PQgetvalue(res, i, ",
adamc@867 565 string (Int.toString i),
adamc@867 566 string ")"])
adamc@867 567 (box [string "PQgetlength(res, i, ",
adamc@867 568 string (Int.toString i),
adamc@867 569 string ")"]),
adamc@867 570 string ")"]
adamc@867 571 in
adamc@867 572 getter t
adamc@867 573 end
adamc@867 574
adamc@873 575 fun queryCommon {loc, query, cols, doCols} =
adamc@867 576 box [string "int n, i;",
adamc@867 577 newline,
adamc@867 578 newline,
adamc@867 579
adamc@867 580 string "if (res == NULL) uw_error(ctx, FATAL, \"Out of memory allocating query result.\");",
adamc@867 581 newline,
adamc@867 582 newline,
adamc@867 583
adamc@867 584 string "if (PQresultStatus(res) != PGRES_TUPLES_OK) {",
adamc@867 585 newline,
adamc@867 586 box [string "PQclear(res);",
adamc@867 587 newline,
adamc@867 588 string "uw_error(ctx, FATAL, \"",
adamc@867 589 string (ErrorMsg.spanToString loc),
adamc@867 590 string ": Query failed:\\n%s\\n%s\", ",
adamc@867 591 query,
adamc@867 592 string ", PQerrorMessage(conn));",
adamc@867 593 newline],
adamc@867 594 string "}",
adamc@867 595 newline,
adamc@867 596 newline,
adamc@867 597
adamc@867 598 string "if (PQnfields(res) != ",
adamc@873 599 string (Int.toString (length cols)),
adamc@867 600 string ") {",
adamc@867 601 newline,
adamc@867 602 box [string "int nf = PQnfields(res);",
adamc@867 603 newline,
adamc@867 604 string "PQclear(res);",
adamc@867 605 newline,
adamc@867 606 string "uw_error(ctx, FATAL, \"",
adamc@867 607 string (ErrorMsg.spanToString loc),
adamc@867 608 string ": Query returned %d columns instead of ",
adamc@873 609 string (Int.toString (length cols)),
adamc@867 610 string ":\\n%s\\n%s\", nf, ",
adamc@867 611 query,
adamc@867 612 string ", PQerrorMessage(conn));",
adamc@867 613 newline],
adamc@867 614 string "}",
adamc@867 615 newline,
adamc@867 616 newline,
adamc@867 617
adamc@867 618 string "uw_end_region(ctx);",
adamc@867 619 newline,
adamc@867 620 string "uw_push_cleanup(ctx, (void (*)(void *))PQclear, res);",
adamc@867 621 newline,
adamc@867 622 string "n = PQntuples(res);",
adamc@867 623 newline,
adamc@867 624 string "for (i = 0; i < n; ++i) {",
adamc@867 625 newline,
adamc@867 626 doCols p_getcol,
adamc@867 627 string "}",
adamc@867 628 newline,
adamc@867 629 newline,
adamc@867 630 string "uw_pop_cleanup(ctx);",
adamc@867 631 newline]
adamc@867 632
adamc@873 633 fun query {loc, cols, doCols} =
adamc@867 634 box [string "PGconn *conn = uw_get_db(ctx);",
adamc@867 635 newline,
adamc@867 636 string "PGresult *res = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);",
adamc@867 637 newline,
adamc@867 638 newline,
adamc@873 639 queryCommon {loc = loc, cols = cols, doCols = doCols, query = string "query"}]
adamc@867 640
adamc@867 641 fun p_ensql t e =
adamc@867 642 case t of
adamc@867 643 Int => box [string "uw_Basis_attrifyInt(ctx, ", e, string ")"]
adamc@867 644 | Float => box [string "uw_Basis_attrifyFloat(ctx, ", e, string ")"]
adamc@867 645 | String => e
adamc@867 646 | Bool => box [string "(", e, string " ? \"TRUE\" : \"FALSE\")"]
adamc@867 647 | Time => box [string "uw_Basis_attrifyTime(ctx, ", e, string ")"]
adamc@867 648 | Blob => box [e, string ".data"]
adamc@867 649 | Channel => box [string "uw_Basis_attrifyChannel(ctx, ", e, string ")"]
adamc@867 650 | Client => box [string "uw_Basis_attrifyClient(ctx, ", e, string ")"]
adamc@867 651 | Nullable String => e
adamc@867 652 | Nullable t => box [string "(",
adamc@867 653 e,
adamc@867 654 string " == NULL ? NULL : ",
adamc@867 655 p_ensql t (box [string "(*", e, string ")"]),
adamc@867 656 string ")"]
adamc@867 657
adamc@879 658 fun queryPrepared {loc, id, query, inputs, cols, doCols, nested = _} =
adamc@867 659 box [string "PGconn *conn = uw_get_db(ctx);",
adamc@867 660 newline,
adamc@867 661 string "const int paramFormats[] = { ",
adamc@867 662 p_list_sep (box [string ",", space])
adamc@867 663 (fn t => if isBlob t then string "1" else string "0") inputs,
adamc@867 664 string " };",
adamc@867 665 newline,
adamc@867 666 string "const int paramLengths[] = { ",
adamc@867 667 p_list_sepi (box [string ",", space])
adamc@867 668 (fn i => fn Blob => string ("arg" ^ Int.toString (i + 1) ^ ".size")
adamc@867 669 | Nullable Blob => string ("arg" ^ Int.toString (i + 1)
adamc@867 670 ^ "?arg" ^ Int.toString (i + 1) ^ "->size:0")
adamc@867 671 | _ => string "0") inputs,
adamc@867 672 string " };",
adamc@867 673 newline,
adamc@867 674 string "const char *paramValues[] = { ",
adamc@867 675 p_list_sepi (box [string ",", space])
adamc@867 676 (fn i => fn t => p_ensql t (box [string "arg",
adamc@867 677 string (Int.toString (i + 1))]))
adamc@867 678 inputs,
adamc@867 679 string " };",
adamc@867 680 newline,
adamc@867 681 newline,
adamc@867 682 string "PGresult *res = ",
adamc@867 683 if #persistent (Settings.currentProtocol ()) then
adamc@867 684 box [string "PQexecPrepared(conn, \"uw",
adamc@867 685 string (Int.toString id),
adamc@867 686 string "\", ",
adamc@867 687 string (Int.toString (length inputs)),
adamc@867 688 string ", paramValues, paramLengths, paramFormats, 0);"]
adamc@867 689 else
adamc@867 690 box [string "PQexecParams(conn, \"",
adamc@867 691 string (String.toString query),
adamc@867 692 string "\", ",
adamc@867 693 string (Int.toString (length inputs)),
adamc@867 694 string ", NULL, paramValues, paramLengths, paramFormats, 0);"],
adamc@867 695 newline,
adamc@867 696 newline,
adamc@873 697 queryCommon {loc = loc, cols = cols, doCols = doCols, query = box [string "\"",
adamc@873 698 string (String.toString query),
adamc@873 699 string "\""]}]
adamc@867 700
adamc@868 701 fun dmlCommon {loc, dml} =
adamc@868 702 box [string "if (res == NULL) uw_error(ctx, FATAL, \"Out of memory allocating DML result.\");",
adamc@868 703 newline,
adamc@868 704 newline,
adamc@868 705
adamc@868 706 string "if (PQresultStatus(res) != PGRES_COMMAND_OK) {",
adamc@868 707 newline,
adamc@868 708 box [string "if (!strcmp(PQresultErrorField(res, PG_DIAG_SQLSTATE), \"40001\")) {",
adamc@868 709 box [newline,
adamc@868 710 string "PQclear(res);",
adamc@868 711 newline,
adamc@868 712 string "uw_error(ctx, UNLIMITED_RETRY, \"Serialization failure\");",
adamc@868 713 newline],
adamc@868 714 string "}",
adamc@868 715 newline,
adamc@868 716 string "PQclear(res);",
adamc@868 717 newline,
adamc@868 718 string "uw_error(ctx, FATAL, \"",
adamc@868 719 string (ErrorMsg.spanToString loc),
adamc@868 720 string ": DML failed:\\n%s\\n%s\", ",
adamc@868 721 dml,
adamc@868 722 string ", PQerrorMessage(conn));",
adamc@868 723 newline],
adamc@868 724 string "}",
adamc@868 725 newline,
adamc@868 726 newline,
adamc@868 727
adamc@868 728 string "PQclear(res);",
adamc@868 729 newline]
adamc@868 730
adamc@868 731 fun dml loc =
adamc@868 732 box [string "PGconn *conn = uw_get_db(ctx);",
adamc@868 733 newline,
adamc@868 734 string "PGresult *res = PQexecParams(conn, dml, 0, NULL, NULL, NULL, NULL, 0);",
adamc@868 735 newline,
adamc@868 736 newline,
adamc@868 737 dmlCommon {loc = loc, dml = string "dml"}]
adamc@868 738
adamc@868 739 fun dmlPrepared {loc, id, dml, inputs} =
adamc@868 740 box [string "PGconn *conn = uw_get_db(ctx);",
adamc@868 741 newline,
adamc@868 742 string "const int paramFormats[] = { ",
adamc@868 743 p_list_sep (box [string ",", space])
adamc@868 744 (fn t => if isBlob t then string "1" else string "0") inputs,
adamc@868 745 string " };",
adamc@868 746 newline,
adamc@868 747 string "const int paramLengths[] = { ",
adamc@868 748 p_list_sepi (box [string ",", space])
adamc@868 749 (fn i => fn Blob => string ("arg" ^ Int.toString (i + 1) ^ ".size")
adamc@868 750 | Nullable Blob => string ("arg" ^ Int.toString (i + 1)
adamc@868 751 ^ "?arg" ^ Int.toString (i + 1) ^ "->size:0")
adamc@868 752 | _ => string "0") inputs,
adamc@868 753 string " };",
adamc@868 754 newline,
adamc@868 755 string "const char *paramValues[] = { ",
adamc@868 756 p_list_sepi (box [string ",", space])
adamc@868 757 (fn i => fn t => p_ensql t (box [string "arg",
adamc@868 758 string (Int.toString (i + 1))]))
adamc@868 759 inputs,
adamc@868 760 string " };",
adamc@868 761 newline,
adamc@868 762 newline,
adamc@868 763 string "PGresult *res = ",
adamc@868 764 if #persistent (Settings.currentProtocol ()) then
adamc@868 765 box [string "PQexecPrepared(conn, \"uw",
adamc@868 766 string (Int.toString id),
adamc@868 767 string "\", ",
adamc@868 768 string (Int.toString (length inputs)),
adamc@868 769 string ", paramValues, paramLengths, paramFormats, 0);"]
adamc@868 770 else
adamc@868 771 box [string "PQexecParams(conn, \"",
adamc@868 772 string (String.toString dml),
adamc@868 773 string "\", ",
adamc@868 774 string (Int.toString (length inputs)),
adamc@868 775 string ", NULL, paramValues, paramLengths, paramFormats, 0);"],
adamc@868 776 newline,
adamc@868 777 newline,
adamc@868 778 dmlCommon {loc = loc, dml = box [string "\"",
adamc@868 779 string (String.toString dml),
adamc@868 780 string "\""]}]
adamc@868 781
adamc@869 782 fun nextvalCommon {loc, query} =
adamc@869 783 box [string "if (res == NULL) uw_error(ctx, FATAL, \"Out of memory allocating nextval result.\");",
adamc@869 784 newline,
adamc@869 785 newline,
adamc@869 786
adamc@869 787 string "if (PQresultStatus(res) != PGRES_TUPLES_OK) {",
adamc@869 788 newline,
adamc@869 789 box [string "PQclear(res);",
adamc@869 790 newline,
adamc@869 791 string "uw_error(ctx, FATAL, \"",
adamc@869 792 string (ErrorMsg.spanToString loc),
adamc@869 793 string ": Query failed:\\n%s\\n%s\", ",
adamc@869 794 query,
adamc@869 795 string ", PQerrorMessage(conn));",
adamc@869 796 newline],
adamc@869 797 string "}",
adamc@869 798 newline,
adamc@869 799 newline,
adamc@869 800
adamc@869 801 string "n = PQntuples(res);",
adamc@869 802 newline,
adamc@869 803 string "if (n != 1) {",
adamc@869 804 newline,
adamc@869 805 box [string "PQclear(res);",
adamc@869 806 newline,
adamc@869 807 string "uw_error(ctx, FATAL, \"",
adamc@869 808 string (ErrorMsg.spanToString loc),
adamc@869 809 string ": Wrong number of result rows:\\n%s\\n%s\", ",
adamc@869 810 query,
adamc@869 811 string ", PQerrorMessage(conn));",
adamc@869 812 newline],
adamc@869 813 string "}",
adamc@869 814 newline,
adamc@869 815 newline,
adamc@869 816
adamc@869 817 string "n = uw_Basis_stringToInt_error(ctx, PQgetvalue(res, 0, 0));",
adamc@869 818 newline,
adamc@869 819 string "PQclear(res);",
adamc@869 820 newline]
adamc@869 821
adamc@878 822 open Cjr
adamc@878 823
adamc@878 824 fun nextval {loc, seqE, seqName} =
adamc@878 825 let
adamc@878 826 val query = case seqName of
adamc@878 827 SOME s =>
adamc@879 828 string ("\"SELECT NEXTVAL('" ^ s ^ "')\"")
adamc@878 829 | _ => box [string "uw_Basis_strcat(ctx, \"SELECT NEXTVAL('\", uw_Basis_strcat(ctx, ",
adamc@878 830 seqE,
adamc@878 831 string ", \"')\"))"]
adamc@878 832 in
adamc@878 833 box [string "char *query = ",
adamc@878 834 query,
adamc@878 835 string ";",
adamc@878 836 newline,
adamc@878 837 string "PGconn *conn = uw_get_db(ctx);",
adamc@878 838 newline,
adamc@878 839 string "PGresult *res = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);",
adamc@878 840 newline,
adamc@878 841 newline,
adamc@878 842 nextvalCommon {loc = loc, query = string "query"}]
adamc@878 843 end
adamc@869 844
adamc@869 845 fun nextvalPrepared {loc, id, query} =
adamc@869 846 box [string "PGconn *conn = uw_get_db(ctx);",
adamc@869 847 newline,
adamc@869 848 newline,
adamc@869 849 string "PGresult *res = ",
adamc@869 850 if #persistent (Settings.currentProtocol ()) then
adamc@869 851 box [string "PQexecPrepared(conn, \"uw",
adamc@869 852 string (Int.toString id),
adamc@869 853 string "\", 0, NULL, NULL, NULL, 0);"]
adamc@869 854 else
adamc@869 855 box [string "PQexecParams(conn, \"",
adamc@869 856 string (String.toString query),
adamc@869 857 string "\", 0, NULL, NULL, NULL, NULL, 0);"],
adamc@869 858 newline,
adamc@869 859 newline,
adamc@869 860 nextvalCommon {loc = loc, query = box [string "\"",
adamc@869 861 string (String.toString query),
adamc@869 862 string "\""]}]
adamc@869 863
adamc@874 864 fun sqlifyString s = "E'" ^ String.translate (fn #"'" => "\\'"
adamc@874 865 | #"\\" => "\\\\"
adamc@874 866 | ch =>
adamc@874 867 if Char.isPrint ch then
adamc@874 868 str ch
adamc@874 869 else
adamc@874 870 "\\" ^ StringCvt.padLeft #"0" 3
adamc@874 871 (Int.fmt StringCvt.OCT (ord ch)))
adamc@874 872 (String.toString s) ^ "'::text"
adamc@874 873
adamc@874 874 fun p_cast (s, t) = s ^ "::" ^ p_sql_type t
adamc@874 875
adamc@874 876 fun p_blank (n, t) = p_cast ("$" ^ Int.toString n, t)
adamc@874 877
adamc@866 878 val () = addDbms {name = "postgres",
adamc@866 879 header = "postgresql/libpq-fe.h",
adamc@866 880 link = "-lpq",
adamc@873 881 p_sql_type = p_sql_type,
adamc@867 882 init = init,
adamc@867 883 query = query,
adamc@868 884 queryPrepared = queryPrepared,
adamc@868 885 dml = dml,
adamc@869 886 dmlPrepared = dmlPrepared,
adamc@869 887 nextval = nextval,
adamc@874 888 nextvalPrepared = nextvalPrepared,
adamc@874 889 sqlifyString = sqlifyString,
adamc@874 890 p_cast = p_cast,
adamc@874 891 p_blank = p_blank,
adamc@877 892 supportsDeleteAs = true,
adamc@877 893 createSequence = fn s => "CREATE SEQUENCE " ^ s,
adamc@878 894 textKeysNeedLengths = false,
adamc@879 895 supportsNextval = true,
adamc@879 896 supportsNestedPrepared = true}
adamc@874 897
adamc@866 898 val () = setDbms "postgres"
adamc@866 899
adamc@866 900 end