Alright, I've done just this, reimplementing sequences for MySQL only
by patching "mysql.sml". Hopefully it functions as intended.
On 06/14/2013 02:13 PM, Istvan Chung wrote:
> Well, another option could be to just maintain our own counter and
> increment it manually, forgoing AUTO_INCREMENT entirely.
>
> On 06/14/2013 01:07 PM, Adam Chlipala wrote:
>> Wow, that's some grungy behavior on MySQL's part, which somehow
>> doesn't surprise me. :D
>>
>> It would certainly be an easy "fix" to remove the 'DELETE'
>> command from the Ur/Web compiler's handling of sequences in
>> MySQL. However, there could be non-trivial performance
>> consequences, as now an extra table would exist with a row for
>> every allocated sequence element. Maybe "real" rows using IDs
>> are deleted periodically, so that the asymptotic space usage of
>> a database would increase under the proposed change.
>>
>> Does anyone have any ideas for a better way to implement
>> Postgres-style sequences in MySQL? Alternatively, we could
>> contemplate a compiler flag to toggle MySQL behavior, giving an
>> option to increase space use to make the semantics nicer. (It's
>> not clear whether that option should be on or off by default.)
>>
>> On 06/11/2013 11:23 PM, Istvan Chung wrote:
>>> It appears that when using MySQL with InnoDB, the
>>> AUTO_INCREMENT counter which Ur/Web uses to maintain sequences
>>> is reset when the database is restarted. This is because of a
>>> [bug in InnoDB](http://bugs.mysql.com/bug.php?id=199). In
>>> essence, whenever the database is restarted, InnoDB determines
>>> where the counter was left off by finding the first available
>>> number in each table which uses AUTO_INCREMENT, and uses that
>>> as the new initial value for the counter.
>>>
>>> However, since Ur/Web erases each entry in the table used to
>>> maintain the sequence as soon as it is done using that entry,
>>> InnoDB doesn't know which numbers have been used, and starts
>>> over at 1. Although this is a bug in InnoDB, it might be
>>> considered to simply remove (if only for MySQL+InnoDB) the
>>> behavior of erasing each entry as it is used, so that InnoDB
>>> can continue the AUTO_INCREMENT counter correctly.
>>>
>>
>>
>> _______________________________________________ Ur mailing list
>> [email protected]
>> http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
>
>
--
Istvan Chung
diff -r 3683d1a8c1c8 src/mysql.sml
--- a/src/mysql.sml Sat Jun 15 08:18:47 2013 -0400
+++ b/src/mysql.sml Tue Jul 09 19:14:39 2013 -0400
@@ -1485,29 +1485,78 @@
fun nextval {loc, seqE, seqName} =
box [string "uw_conn *conn = uw_get_db(ctx);",
newline,
- string "char *insert = ",
+
+ string "char *read = ",
case seqName of
- SOME s => string ("\"INSERT INTO " ^ s ^ " VALUES ()\"")
- | NONE => box [string "uw_Basis_strcat(ctx, \"INSERT INTO \", uw_Basis_strcat(ctx, ",
- seqE,
- string ", \" VALUES ()\"))"],
- string ";",
- newline,
- string "char *delete = ",
- case seqName of
- SOME s => string ("\"DELETE FROM " ^ s ^ "\"")
- | NONE => box [string "uw_Basis_strcat(ctx, \"DELETE FROM \", ",
+ SOME s => string ("\"SELECT uw_id FROM " ^ s ^ "\"")
+ | NONE => box [string "uw_Basis_strcat(ctx, \"SELECT uw_id FROM \", ",
seqE,
string ")"],
string ";",
newline,
newline,
+ string "if (mysql_query(conn->conn, read)) uw_error(ctx, FATAL, \"'nextval' SELECT failed\");",
+ newline,
+
+ string "MYSQL_RES *res;",
+ newline,
+ string "if (!(res = mysql_store_result(conn->conn))) {",
+ newline,
+ string "mysql_free_result(res);",
+ newline,
+ string "uw_error(ctx, FATAL, \"'nextval' mysql_store_result failed\");",
+ newline,
+ string "}",
+ newline,
+
+ string "MYSQL_ROW row;",
+ newline,
+ string "if (!(row = mysql_fetch_row(res))) {",
+ newline,
+ string "mysql_free_result(res);",
+ newline,
+ string "n = 1;",
+ newline,
+ string "char *insert = ",
+ case seqName of
+ SOME s => string ("\"INSERT INTO " ^ s ^ " VALUES (2)\"")
+ | NONE => box [string "uw_Basis_strcat(ctx, \"INSERT INTO \", uw_Basis_strcat(ctx, ",
+ seqE,
+ string ", \" VALUES (1)\"))"],
+ string ";",
+ newline,
string "if (mysql_query(conn->conn, insert)) uw_error(ctx, FATAL, \"'nextval' INSERT failed\");",
newline,
- string "n = mysql_insert_id(conn->conn);",
+ string "}",
newline,
- string "if (mysql_query(conn->conn, delete)) uw_error(ctx, FATAL, \"'nextval' DELETE failed\");",
+
+ string "else {",
+ newline,
+
+ string "mysql_free_result(res);",
+ newline,
+
+ string "n = atoll(row[0]);",
+ newline,
+
+ string "char buf[strlen(row[0])+2];", (* n + 1 cannot be more than 1 digit longer than n *)
+ newline,
+ string "sprintf(buf, \"%llu\", n+1);",
+ newline,
+
+ string "char *update = ",
+ case seqName of
+ SOME s => string ("uw_Basis_strcat(ctx, \"UPDATE " ^ s ^ " SET uw_id=\\\"\", uw_Basis_strcat(ctx, buf, \"\\\"\"))")
+ | NONE => box [string "uw_Basis_strcat(ctx, \"UPDATE \", uw_Basis_strcat(ctx, ",
+ seqE,
+ string ", uw_Basis_strcat(ctx, \" SET uw_id=\\\"\", uw_Basis_strcat(ctx, buf, \"\\\"\"))))"],
+ string ";",
+ newline,
+
+ string "if (mysql_query(conn->conn, update)) uw_error(ctx, FATAL, \"'nextval' UPDATE failed\");",
+ newline,
+ string "}",
newline]
fun nextvalPrepared _ = raise Fail "MySQL.nextvalPrepared called"
@@ -1547,7 +1596,7 @@
p_blank = p_blank,
supportsDeleteAs = false,
supportsUpdateAs = false,
- createSequence = fn s => "CREATE TABLE " ^ s ^ " (uw_id INTEGER PRIMARY KEY AUTO_INCREMENT)",
+ createSequence = fn s => "CREATE TABLE " ^ s ^ " (uw_id INTEGER PRIMARY KEY)",
textKeysNeedLengths = true,
supportsNextval = false,
supportsNestedPrepared = false,
_______________________________________________
Ur mailing list
[email protected]
http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
