botpress / solutions

List of Botpress templates / examples for common problems. Kick start your chatbot development with these ready-to-go custom modules, actions, hooks, and tools!
https://botpress.com
56 stars 25 forks source link

Increment Custom Metric throwing errors when updating the database #63

Open Naroh091 opened 1 year ago

Naroh091 commented 1 year ago

Environment details:

Botpress 12.30.6 Storage: PostgreSQL

I just added Increment Custom Metric to my environment and upon executing the action the system throws an error:

Sun Dec 11 2022 11:14:49 GMT+0000 (Coordinated Universal Time) ActionService (error) (bot: pruebas) An error occurred while executing the action "increment-custom-metric [error, SELECT * FROM bot_analytics WHERE 
     botId='pruebas' AND
     date = '2022-12-11' AND
     channel = 'api' AND
     metric = 'found' AND
     subMetric = 'n/a' - column "botid" does not exist]
STACK TRACE
error: column "botid" does not exist
    at Parser.parseErrorMessage (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/snapshot/botpress/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:482:12)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)
    at Socket.Readable.push (_stream_readable.js:212:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

This has to do with using PostgreSQL as storage since every column name without quotes is interpreted as lowercase:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other.

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

Also, after fixing it a new error comes up:

Sun Dec 11 2022 11:17:28 GMT+0000 (Coordinated Universal Time) ActionService (error) (bot: pruebas) An error occurred while executing the action "increment-custom-metric [error, insert into "bot_analytics" ("botId", "channel", "date", "metric", "subMetric", "value") values ($1, $2, $3, $4, $5, $6) returning "id" - column "id" does not exist]
STACK TRACE
error: column "id" does not exist
    at Parser.parseErrorMessage (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/snapshot/botpress/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/snapshot/botpress/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:482:12)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)
    at Socket.Readable.push (_stream_readable.js:212:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

The code omits the returnColumn or idColumn at insertAndRetrieve, so id is used as default - but there's no id column for this table.