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
- Code: Select all
-- [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;
The 3rd line is where the compile blows up as a syntax error.
type ALIAS FOR $2;- The fix is to change: Show Spoiler
type ALIAS FOR $2;
name ALIAS FOR $3;
to
xtype ALIAS FOR $2;
xname ALIAS FOR $3;
and change
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;
to
IF (SELECT COUNT(*) FROM auth."Scores" WHERE "OwnerIdx"=ownerId AND "Name"=xname) < 1 THEN
INSERT INTO auth."Scores" ("OwnerIdx", "CreateTime", "Type", "Name", "Points")
VALUES (ownerId, EXTRACT(EPOCH FROM NOW()), xtype, xname, points)
RETURNING idx INTO scoreId;
END IF;
This is apllicable to PostgreSQL 8.4
