Continuing my voyage of discovery with mySQL, I decided to see how much of the logic of the application could be encoded in stored functions and procedures, to limit the complexity of the AJAX and GUI components. First of all, I decided to try to write a stored procedure which would handle the addition of items to the relational table that stores associations of keywords with inscriptions. The idea here is that, when an Inscription record is updated, a list of keyword associations are submitted; some may pre-exist, in which case they don't need to be re-created, but others may be new. I thought it would be handy to get back the id of the old or new record in either case, so I tried creating a stored function, like this:
CREATE FUNCTION AddkeywordToInscRec(inKeywordId INT, inInscId INT)
RETURNS INT
BEGIN
DECLARE retId INT;
IF EXISTS(SELECT * FROM keywordToInsc WHERE (keywordId = inKeywordId) AND (inscId = inInscId)) THEN
SET retId = (SELECT 'id' FROM keywordToInsc WHERE (keywordId = inKeywordId) AND (inscId = inInscId) LIMIT 1);
ELSE
INSERT INTO keywordToInsc(keywordId, inscId) VALUES (inKeywordId, inInscId);
SET retId = LAST_INSERT_ID();
END IF;
RETURN retId;
END; $$
However, mySQL refuses to accept this, returning the following error:
#1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled.
The function is, of course, not deterministic -- it may return different values in different circumstances -- and so it's not really on to add the DETERMINISTIC keyword; it does contain SQL and does more than reading; and we don't want to, and can't, turn off binary logging, since this is required for backup/restore. So we're stuck.
Switching to a simpler stored procedure gets around the problem, though:
[Set delimiter to $$]
CREATE PROCEDURE spAddkeywordToInscRec(inKeywordId INT, inInscId INT)
BEGIN
IF NOT EXISTS(SELECT * FROM keywordToInsc WHERE (keywordId = inKeywordId) AND (inscId = inInscId)) THEN
INSERT INTO keywordToInsc(keywordId, inscId) VALUES (inKeywordId, inInscId);
END IF;
END; $$
This doesn't return the value of the new record, but this was only a little bonus I fancied having, and isn't absolutely necessary. I can now create corresponding procedures for the other two correspondence tables.