CODEWITHSUNDEEP
javascriptnode.jssqlitesql-update
Kbi
Kbi

Reputation: 384

SQLITE3 UPDATE CASE Node.js

I'm using the sqllite3 npm for node.js and I want to do the following:

A player can play the game 10 times. 24 Hours later the counter resets and he can play 10 games again. My thought was doing it with an update case.

 db.run("UPDATE user_sessions SET games_played = CASE WHEN((CAST(strftime('%s', CURRENT_TIMESTAMP) as integer) - CAST(strftime('%s', last_game) as integer))>86400 THEN "1" ELSE  ?1, last_game = CURRENT_TIMESTAMP WHERE ip_address = ?2 AND user_agent = ?3", {
        1:gamesPlayed,
        2:handshake.address,
        3:handshake.headers['user-agent']
    });

He updates and sets counter 1 if between last game and now are 24 hours different (86400 seconds).

(CAST(strftime('%s', CURRENT_TIMESTAMP) as integer) - CAST(strftime('%s', last_game) as integer)

Should give the time difference in seconds. It works for me.

The complete statement doesnt work for me it says : error near "THEN": syntax error

Is this kind of update statement possible? If yes what im doing wrong?

I also tried this in the sqlite browser command line:

UPDATE user_sessions SET games_played = CASE WHEN(((CAST(strftime('%s', CURRENT_TIMESTAMP) as integer) - CAST(strftime('%s', last_game) as integer))>86400) THEN 1 ELSE  5, last_game = CURRENT_TIMESTAMP

Same error.

Upvotes: 3

Views: 659

Answers (1)

Mureinik
Mureinik

Reputation: 311073

You have a redundant ( before the first cast, and are missing the end clause of the case expression:

UPDATE user_sessions SET games_played = CASE WHEN(CAST(strftime('%s', CURRENT_TIMESTAMP) as integer) - CAST(strftime('%s', last_game) as integer))>86400 THEN "1" ELSE  ?1 END, last_game = CURRENT_TIMESTAMP WHERE ip_address = ?2 AND user_agent = ?3"

Upvotes: 2

Related Questions