While testing the modified scripts for Er'cana, dirtsand threw a string of SQL function syntax errors when the client tried to update the pellet and lake point scores. I hoped someone could tell me whether the problem is a missing node or two that I need to INSERT into the tables or if there might be another problem.
If it's just a missing node or two, does anyone have the SQL statements to insert them? (It would be nice to put in all the missing public nodes I mentioned in an
earlier post above).
EDIT: It appears the scores table was created but there are no rows at all in the DB. I'm thinking at this point that there is a logic error in ErcanaCitySilo.py that fails to create the players score record (ki points or lake points). If the msg is to update (which is what happens when you drop a pellet) then the script bombs out. If it is a list score msg, then it tries to create the row if it doesn't exist. I haven't found what causes a list rather than an update. (help!)
EDIT2: And I found the problem and fixed it. I now have KI points accumulating and displaying in the Neighborhood imager.
I read the error coming from dirtsand about a compilation error in create score function. I couldn't see anything wrong with the code but when I opened it up in pgAdmin, I noticed that a couple of words were colorized so I went looking for reserved words in Postgresql. Although
name and
type are supposed to not be reserved, they were the source of the problem in the following dirtsand function:
- from functions.sql Show Spoiler
[color=#00FF00][code]-- [Required] Create a new score--
-- This prevents score creation race conditions --
CREATE OR REPLACE FUNCTION auth.create_score(integer, integer, character varying, integer)
RETURNS integer AS
$BODY$
DECLARE
ownerId ALIAS FOR $1;
type ALIAS FOR $2;
name ALIAS FOR $3;
points ALIAS FOR $4;
scoreId integer DEFAULT -1;
BEGIN
IF (SELECT COUNT(*) FROM auth."Scores" WHERE "OwnerIdx"=ownerId AND "Name"=name) < 1 THEN
INSERT INTO auth."Scores" ("OwnerIdx", "CreateTime", "Type", "Name", "Points")
VALUES (ownerId, EXTRACT(EPOCH FROM NOW()), type, name, points)
RETURNING idx INTO scoreId;
END IF;
RETURN scoreId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION auth.create_score(IN integer, IN integer, IN character varying, IN integer) OWNER TO dirtsand;[/code][/color]
The 3rd line is where the compile blows up as a syntax error.
type ALIAS FOR $2;
- The fix is to change: Show Spoiler
[color=#00FF00] type ALIAS FOR $2;
name ALIAS FOR $3;[/color]
to
[color=#FF0000] xtype[/color][color=#00FF00] ALIAS FOR $2;[/color]
[color=#FF0000]xname[/color][color=#00FF00] ALIAS FOR $3;[/color]
and change
[color=#00FF00] IF (SELECT COUNT(*) FROM auth."Scores" WHERE "OwnerIdx"=ownerId AND "Name"=name) < 1 THEN
INSERT INTO auth."Scores" ("OwnerIdx", "CreateTime", "Type", "Name", "Points")
VALUES (ownerId, EXTRACT(EPOCH FROM NOW()), type, name, points)
RETURNING idx INTO scoreId;
END IF;[/color]
to
[color=#00FF00] IF (SELECT COUNT(*) FROM auth."Scores" WHERE "OwnerIdx"=ownerId AND "Name"=[/color][color=#FF0000]xname[/color][color=#00FF00]) < 1 THEN
INSERT INTO auth."Scores" ("OwnerIdx", "CreateTime", "Type", "Name", "Points")
VALUES (ownerId, EXTRACT(EPOCH FROM NOW()), [/color][color=#FF0000]xtype, xname[/color][color=#00FF00], points)
RETURNING idx INTO scoreId;
END IF;[/color]
This is apllicable to PostgreSQL 8.4
