parsing apache logs

1 files changed, 27 insertions, 0 deletions

diff --git a/webstats/logs.sql b/webstats/logs.sql
new file mode 100644
index 0000000..9b5146b
--- /dev/null
+++ b/webstats/logs.sql
@@ -0,0 +1,27 @@
+-- name: create-log-table
+CREATE TABLE IF NOT EXISTS logs (
+    ip text,
+    remote_log text,
+    userid text,
+    date integer,
+    request_method text,
+    path text,
+    version text,
+    status integer,
+    length integer,
+    referrer text,
+    user_agent text)
+
+-- name: create-agent-table
+CREATE TABLE IF NOT EXISTS agent (
+    id integer PRIMARY KEY,
+    user_agent text UNIQUE)
+
+-- name: create-ip-table
+CREATE TABLE IF NOT EXISTS ip (
+    id integer PRIMARY KEY,
+    addr text UNIQUE)
+
+-- name: insert-log
+INSERT INTO logs (ip, remote_log, userid, date, request_method, path, version, status, length, referrer, user_agent)
+    VALUES (?ip, ?remote_log, ?userid, ?date, ?request_method, ?path, ?version, ?status, ?length, ?referrer, ?user_agent)