diff options
| author | Oscar Najera <hi@oscarnajera.com> | 2025-02-10 13:29:17 +0100 |
|---|---|---|
| committer | Oscar Najera <hi@oscarnajera.com> | 2025-02-10 13:29:17 +0100 |
| commit | b9901f220007bda328427db8dcbe822bb89c99bc (patch) | |
| tree | 01bed2509e917201ab4b023d7e9532826d37485d /webstats/queries.sql | |
| parent | 30627e57593c4533b7d1cd49168e22f05a91d759 (diff) | |
| download | scratch-webstats.tar.gz scratch-webstats.tar.bz2 scratch-webstats.zip | |
add new visit row with foreign key previewwebstats
Diffstat (limited to 'webstats/queries.sql')
| -rw-r--r-- | webstats/queries.sql | 15 |
1 files changed, 12 insertions, 3 deletions
diff --git a/webstats/queries.sql b/webstats/queries.sql index 5a8a0ae..383ae22 100644 --- a/webstats/queries.sql +++ b/webstats/queries.sql @@ -15,11 +15,20 @@ CREATE TABLE IF NOT EXISTS STATS ( -- Removes table tracking visits DROP TABLE IF EXISTS STATS +-- name: add-ip +INSERT OR IGNORE INTO source (ip) + VALUES (:ip) + +-- name: add-user-agent +INSERT OR IGNORE INTO useragents (name) + VALUES (:user_agent) + -- name: insert @last-id -- Insert a data point -INSERT INTO STATS (click, page, referrer, ip, user_agent, title) - VALUES (:click, :page, :referer, :ip, :user_agent, :title) - +INSERT INTO STATS (click, page, referrer, title, ipid, agentid) + VALUES (:click, :page, :referer, :title, + (SELECT id FROM source WHERE ip = :ip), + (SELECT id FROM useragents WHERE name = :user_agent)) -- name: activity-stats -- timeseries of activity |