123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183 |
- CREATE TABLE IF NOT EXISTS seen_hosts (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- ip_address TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS seen_users (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- user TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS seen_routes (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- route TEXT NOT NULL,
- method TEXT NOT NULL,
- UNIQUE(route, method)
- );
- CREATE TABLE IF NOT EXISTS response_code (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- code INTEGER NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS referer (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- referer TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS ua (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- ua TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS requests (
- id INTEGER PRIMARY KEY,
- uuid TEXT NOT NULL UNIQUE,
- date INTEGER NOT NULL,
- host_id INTEGER NOT NULL REFERENCES seen_hosts(id) ON DELETE CASCADE,
- user_id INTEGER NOT NULL REFERENCES seen_users(id) ON DELETE CASCADE,
- route_id INTEGER NOT NULL REFERENCES seen_routes(id) ON DELETE CASCADE,
- referer_id INTEGER NOT NULL REFERENCES referer(id) ON DELETE CASCADE,
- ua_id INTEGER NOT NULL REFERENCES ua(id) ON DELETE CASCADE,
- response_code_id INTEGER NOT NULL REFERENCES response_code(id) ON DELETE RESTRICT
- );
- /* Urchin stuff - it's powerful to be able to do things in backend based on campaign, even if you use a JS frontend. */
- CREATE TABLE IF NOT EXISTS urchin_source (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- value TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS urchin_medium (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- value TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS urchin_campaign (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- value TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS urchin_term (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- value TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS urchin_content (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- value TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS urchin (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- request_id INTEGER NOT NULL UNIQUE REFERENCES requests(id) ON DELETE CASCADE,
- source_id INTEGER NOT NULL REFERENCES urchin_source(id) ON DELETE CASCADE,
- medium_id INTEGER REFERENCES urchin_medium(id) ON DELETE CASCADE,
- campaign_id INTEGER REFERENCES urchin_campaign(id) ON DELETE CASCADE,
- term_id INTEGER REFERENCES urchin_term(id) ON DELETE CASCADE,
- content_id INTEGER REFERENCES urchin_content(id) ON DELETE CASCADE
- );
- CREATE VIEW IF NOT EXISTS urchin_requests AS
- SELECT
- u.id,
- r.uuid AS request_uuid,
- us.value AS utm_source,
- um.value AS utm_medium,
- uc.value AS utm_campaign,
- ut.value AS utm_term,
- uo.value AS utm_content
- FROM
- urchin AS u
- JOIN
- requests AS r ON u.request_id = r.id
- JOIN
- urchin_source AS us ON us.id = u.source_id
- LEFT JOIN
- urchin_medium AS um ON um.id = u.medium_id
- LEFT JOIN
- urchin_campaign AS uc ON uc.id = u.campaign_id
- LEFT JOIN
- urchin_term AS ut ON ut.id = u.term_id
- LEFT JOIN
- urchin_content AS uo ON uo.id = u.content_id;
- /* Make urchin_requests a writable view via triggers. We will always stomp the main row, as the last update will be what we want. */
- CREATE TRIGGER IF NOT EXISTS insert_urchin_requests INSTEAD OF INSERT ON urchin_requests BEGIN
- INSERT OR IGNORE INTO urchin_source (value) VALUES (NEW.utm_source);
- INSERT OR IGNORE INTO urchin_medium (value) VALUES (NEW.utm_medium);
- INSERT OR IGNORE INTO urchin_campaign (value) VALUES (NEW.utm_campaign);
- INSERT OR IGNORE INTO urchin_term (value) VALUES (NEW.utm_term);
- INSERT OR IGNORE INTO urchin_content (value) VALUES (NEW.utm_content);
- INSERT OR REPLACE INTO urchin SELECT
- NEW.id,
- r.id AS request_id,
- us.id AS source_id,
- um.id AS medium_id,
- uc.id AS campaign_id,
- ut.id AS term_id,
- uo.id AS content_id
- FROM requests AS r
- JOIN urchin_source AS us ON us.value = NEW.utm_source
- LEFT JOIN urchin_medium AS um ON um.value = NEW.utm_medium
- LEFT JOIN urchin_campaign AS uc ON uc.value = NEW.utm_campaign
- LEFT JOIN urchin_term AS ut ON ut.value = NEW.utm_term
- LEFT JOIN urchin_content AS uo ON uo.value = NEW.utm_content
- WHERE r.uuid = NEW.request_uuid;
- END;
- CREATE VIEW IF NOT EXISTS all_requests AS
- SELECT
- q.id,
- q.uuid,
- q.date,
- h.ip_address,
- u.user,
- r.method,
- r.route,
- f.referer,
- ua.ua,
- c.code
- FROM
- requests AS q
- JOIN
- seen_hosts AS h ON q.host_id = h.id
- JOIN
- seen_users AS u ON q.user_id = u.id
- JOIN
- seen_routes AS r ON q.route_id = r.id
- JOIN
- referer AS f ON q.referer_id = f.id
- JOIN
- ua ON q.ua_id = ua.id
- JOIN
- response_code AS c on q.response_code_id = c.id;
- /* Make all_requests a writable view via triggers. We will always stomp the main row, as the last update will be what we want. */
- CREATE TRIGGER IF NOT EXISTS insert_all_requests INSTEAD OF INSERT ON all_requests BEGIN
- INSERT OR IGNORE INTO response_code (code) VALUES (NEW.code);
- INSERT OR IGNORE INTO seen_routes (route,method) VALUES (NEW.route, NEW.method);
- INSERT OR IGNORE INTO seen_users (user) VALUES (NEW.user);
- INSERT OR IGNORE INTO seen_hosts (ip_address) VALUES (NEW.ip_address);
- INSERT OR IGNORE INTO referer (referer) VALUES (NEW.referer);
- INSERT OR IGNORE INTO ua (ua) VALUES (NEW.ua);
- INSERT OR REPLACE INTO requests SELECT
- NEW.id,
- NEW.uuid,
- NEW.date,
- h.id AS host_id,
- u.id AS user_id,
- r.id AS route_id,
- f.id AS referer_id,
- ua.id AS ua_id,
- c.id AS response_code_id
- FROM seen_hosts AS h
- JOIN seen_users AS u ON u.user = NEW.user
- JOIN seen_routes AS r ON r.route = NEW.route AND r.method = NEW.method
- JOIN referer AS f ON f.referer = NEW.referer
- JOIN ua ON ua.ua = NEW.ua
- JOIN response_code AS c ON c.code = NEW.code
- WHERE h.ip_address = NEW.ip_address;
- END;
- /* This is just to store various messages associated with requests, which are usually errors. */
- CREATE TABLE IF NOT EXISTS messages (
- uuid TEXT NOT NULL REFERENCES requests(uuid) ON DELETE NO ACTION,
- message TEXT NOT NULL
- );
|