Mercurial > lbo > hg > analyrics
changeset 35:7a64e348786d
Move schema
| author | Lewin Bormann <lbo@spheniscida.de> |
|---|---|
| date | Sat, 16 Jul 2022 10:45:28 -0700 |
| parents | 761e4438e0dd |
| children | 0c5f8caf6736 |
| files | config_schema_sqlite.sql devdata.sql log_sqlite.sql queries.sql schema/config_schema_sqlite.sql schema/devdata.sql schema/log_sqlite.sql schema/queries.sql |
| diffstat | 8 files changed, 69 insertions(+), 66 deletions(-) [+] |
line wrap: on
line diff
--- a/config_schema_sqlite.sql Sat Jul 16 10:14:41 2022 -0700 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,19 +0,0 @@ - -DROP TABLE IF EXISTS users; -CREATE TABLE users ( - id INTEGER PRIMARY KEY, - username TEXT NOT NULL UNIQUE, - name TEXT, - salt TEXT, - password_hash TEXT, - tz_offset INTEGER, -); - -DROP TABLE IF EXISTS domainpermissions; -CREATE TABLE domainpermissions ( - id INTEGER PRIMARY KEY, - username TEXT NOT NULL, - domain TEXT NOT NULL, - - FOREIGN KEY (username) REFERENCES users (username) -);
--- a/devdata.sql Sat Jul 16 10:14:41 2022 -0700 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,2 +0,0 @@ - -INSERT INTO users (id, username, password_hash, name) VALUES (null, "lbo", "5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8", "Lewin");
--- a/log_sqlite.sql Sat Jul 16 10:14:41 2022 -0700 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,41 +0,0 @@ - -DROP TABLE IF EXISTS Sessions; -CREATE TABLE Sessions ( - id INTEGER PRIMARY KEY, - start INTEGER NOT NULL, - last INTEGER NOT NULL - domain TEXT, -); - -DROP TABLE IF EXISTS RequestLog; -CREATE TABLE RequestLog ( - id INTEGER PRIMARY KEY, - session INTEGER, - ip TEXT NOT NULL, - atime INTEGER NOT NULL, - domain TEXT, - path TEXT, - status INTEGER, - pagename TEXT, - refer TEXT, - ua TEXT, - ntags INTEGER, - - FOREIGN KEY (session) REFERENCES Sessions (id) -); - -DROP INDEX IF EXISTS RequestLogBySession; -CREATE INDEX RequestLogBySession ON RequestLog (session); - -DROP TABLE IF EXISTS RequestTags; -CREATE TABLE RequestTags ( - id INTEGER PRIMARY KEY, - requestid INTEGER NOT NULL, - key TEXT, - value TEXT, - - FOREIGN KEY (requestid) REFERENCES RequestLog (id) -); - -DROP INDEX IF EXISTS RequestTagsByRequest; -CREATE INDEX RequestTagsByRequest ON RequestTags (requestid);
--- a/queries.sql Sat Jul 16 10:14:41 2022 -0700 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,4 +0,0 @@ - --- Find all sessions and sort by request count -select sessions.id, start, count(*) from sessions join requestlog on -(sessions.id = requestlog.session) group by sessions.id order by 3;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/config_schema_sqlite.sql Sat Jul 16 10:45:28 2022 -0700 @@ -0,0 +1,19 @@ + +DROP TABLE IF EXISTS users; +CREATE TABLE users ( + id INTEGER PRIMARY KEY, + username TEXT NOT NULL UNIQUE, + name TEXT, + salt TEXT, + password_hash TEXT, + tz_offset INTEGER, +); + +DROP TABLE IF EXISTS domainpermissions; +CREATE TABLE domainpermissions ( + id INTEGER PRIMARY KEY, + username TEXT NOT NULL, + domain TEXT NOT NULL, + + FOREIGN KEY (username) REFERENCES users (username) +);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/devdata.sql Sat Jul 16 10:45:28 2022 -0700 @@ -0,0 +1,2 @@ + +INSERT INTO users (id, username, password_hash, name) VALUES (null, "lbo", "5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8", "Lewin");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/log_sqlite.sql Sat Jul 16 10:45:28 2022 -0700 @@ -0,0 +1,44 @@ + +DROP TABLE IF EXISTS Sessions; +CREATE TABLE Sessions ( + id INTEGER PRIMARY KEY, + start INTEGER NOT NULL, + last INTEGER NOT NULL + domain TEXT, + + origin_country TEXT, + origin_city TEXT, +); + +DROP TABLE IF EXISTS RequestLog; +CREATE TABLE RequestLog ( + id INTEGER PRIMARY KEY, + session INTEGER, + ip TEXT NOT NULL, + atime INTEGER NOT NULL, + domain TEXT, + path TEXT, + status INTEGER, + pagename TEXT, + refer TEXT, + ua TEXT, + ntags INTEGER, + + FOREIGN KEY (session) REFERENCES Sessions (id) +); + +DROP INDEX IF EXISTS RequestLogBySession; +CREATE INDEX RequestLogBySession ON RequestLog (session); + +DROP TABLE IF EXISTS RequestTags; +CREATE TABLE RequestTags ( + id INTEGER PRIMARY KEY, + requestid INTEGER NOT NULL, + key TEXT, + value TEXT, + + FOREIGN KEY (requestid) REFERENCES RequestLog (id) +); + +DROP INDEX IF EXISTS RequestTagsByRequest; +CREATE INDEX RequestTagsByRequest ON RequestTags (requestid);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/queries.sql Sat Jul 16 10:45:28 2022 -0700 @@ -0,0 +1,4 @@ + +-- Find all sessions and sort by request count +select sessions.id, start, count(*) from sessions join requestlog on +(sessions.id = requestlog.session) group by sessions.id order by 3;