fork download 
  1. -- Normalized httpd vhost log
  2. -- Section 0. Decent defaults
  3. PRAGMA page_size=4096;
  4. PRAGMA default_cache_size=2048; -- = 8 MByte
  5. PRAGMA cache_size=512;
  6. PRAGMA foreign_keys=ON;
  7. PRAGMA recursive_triggers=ON;
  8. --    The table that would have been used for a 1:1 mapping of logfile to table
  9. --		CREATE TABLE Loglines (
  10. --			id         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  11. --		,	id_log     INTEGER
  12. --		,	linenr     INTEGER
  13. --		,	vhost      TEXT
  14. --		,	hhost      TEXT
  15. --		,	client     TEXT
  16. --		,	dtevent    REAL
  17. --		,	method     TEXT
  18. --		,	lpath      TEXT
  19. --		,	lpage      TEXT
  20. --		,	args       TEXT
  21. --		,	rc         INTEGER
  22. --		,	rsz        INTEGER
  23. --		,	isz        INTEGER
  24. --		,	osz        INTEGER
  25. --		);
  26.  
  27. -- Section 1. Referenced tables
  28. -- 1.0
  29. CREATE TABLE Categories (
  30. 	id         INTEGER PRIMARY KEY NOT NULL
  31. ,	desc       TEXT UNIQUE NOT NULL
  32. );
  33.  
  34. INSERT INTO Categories (id,desc) VALUES (0,'unknown');
  35. INSERT INTO Categories (id,desc) VALUES (1,'self');
  36. INSERT INTO Categories (id,desc) VALUES (2,'meatwork');
  37. INSERT INTO Categories (id,desc) VALUES (3,'family');
  38. INSERT INTO Categories (id,desc) VALUES (4,'friend');
  39. INSERT INTO Categories (id,desc) VALUES (5,'known');
  40. INSERT INTO Categories (id,desc) VALUES (6,'hacker');
  41.  
  42. -- 1.1
  43. CREATE TABLE Logs (
  44. 	id INTEGER PRIMARY KEY NOT NULL
  45. ,	filename   TEXT UNIQUE NOT NULL
  46. ,	dtimport   REAL DEFAULT (julianday('now'))
  47. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  48. -- ,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  49. );
  50. -- 1.2
  51. CREATE TABLE Vhosts (
  52. 	id INTEGER PRIMARY KEY NOT NULL
  53. ,	vh         TEXT UNIQUE NOT NULL        -- abbreviation
  54. ,	vhost      TEXT UNIQUE NOT NULL        -- full name_or_ip
  55. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  56. -- ,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  57. );
  58. -- 1.3
  59. CREATE TABLE Hhosts (
  60. 	id INTEGER PRIMARY KEY NOT NULL
  61. ,	hh         TEXT        NOT NULL        -- abbreviation, may not be unique
  62. ,	hhost      TEXT UNIQUE NOT NULL        -- full name_or_ip
  63. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  64. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  65. );
  66. -- 1.4
  67. CREATE TABLE Clients (
  68. 	id INTEGER PRIMARY KEY NOT NULL
  69. ,	client     TEXT UNIQUE NOT NULL        -- ipdotted
  70. ,	ciip       INTEGER
  71. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  72. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  73. );
  74. -- 1.5
  75. CREATE TABLE Users (
  76. 	id INTEGER PRIMARY KEY NOT NULL
  77. ,	user       TEXT UNIQUE NOT NULL
  78. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  79. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  80. );
  81. -- 1.6
  82. CREATE TABLE Methods (
  83. 	id INTEGER PRIMARY KEY NOT NULL
  84. ,	method     TEXT UNIQUE NOT NULL
  85. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  86. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  87. );
  88. -- 1.7
  89. CREATE TABLE Lpaths (
  90. 	id INTEGER PRIMARY KEY NOT NULL
  91. ,	lpath      TEXT UNIQUE NOT NULL
  92. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  93. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  94. );
  95. -- 1.8
  96. CREATE TABLE Lpages (
  97. 	id INTEGER PRIMARY KEY NOT NULL
  98. ,	lpage      TEXT UNIQUE NOT NULL
  99. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  100. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  101. );
  102. -- 1.9
  103. CREATE TABLE Argss (
  104. 	id INTEGER PRIMARY KEY NOT NULL
  105. ,	args       TEXT UNIQUE NOT NULL
  106. ,	refcnt     INTEGER DEFAULT 0 NOT NULL  -- denormalized, maintained by trigger
  107. --	,	refchk     INTEGER DEFAULT 0           -- denormalized, maintained by procedure
  108. );
  109.  
  110. -- prepopulate with empty strings
  111. INSERT INTO Vhosts  (vh,vhost)    VALUES ('','');
  112. INSERT INTO Hhosts  (hh,hhost)    VALUES ('','');
  113. INSERT INTO Hhosts  (hh,hhost)    VALUES ('_lo0','localhost');
  114. INSERT INTO Hhosts  (hh,hhost)    VALUES ('_lo4','127.0.0.1');
  115. INSERT INTO Hhosts  (hh,hhost)    VALUES ('_lo6','::1');
  116. INSERT INTO Hhosts  (hh,hhost)    VALUES ('_wan','1.2.3.4');
  117. INSERT INTO Hhosts  (hh,hhost)    VALUES ('_lan','192.168.1.55');
  118. INSERT INTO Clients (client,ciip) VALUES ('',0);
  119. INSERT INTO Users   (user)        VALUES ('');
  120. INSERT INTO Users   (user)        VALUES ('-');
  121. INSERT INTO Methods (method)      VALUES ('');
  122. INSERT INTO Lpaths  (lpath)       VALUES ('');
  123. INSERT INTO Lpages  (lpage)       VALUES ('');
  124. INSERT INTO Argss   (args)        VALUES ('');
  125.  
  126. -- Section 2. Referencing tables
  127. --    The main log table mostly references the details.
  128. CREATE TABLE T_Events (
  129. 	id           INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  130. ,	id_log       INTEGER NOT NULL REFERENCES Logs    (id) ON DELETE CASCADE ON UPDATE CASCADE
  131. ,	linenr       INTEGER NOT NULL
  132. ,	id_vhost     INTEGER NOT NULL REFERENCES Vhosts  (id) ON DELETE CASCADE ON UPDATE CASCADE
  133. ,	id_hhost     INTEGER NOT NULL REFERENCES Hhosts  (id) ON DELETE CASCADE ON UPDATE CASCADE
  134. ,	id_client    INTEGER NOT NULL REFERENCES Clients (id) ON DELETE CASCADE ON UPDATE CASCADE
  135. ,	id_user      INTEGER NOT NULL REFERENCES Users   (id) ON DELETE CASCADE ON UPDATE CASCADE
  136. ,	dtevent      REAL
  137. ,	id_method    INTEGER NOT NULL REFERENCES Methods (id) ON DELETE CASCADE ON UPDATE CASCADE
  138. ,	id_lpath     INTEGER NOT NULL REFERENCES Lpaths  (id) ON DELETE CASCADE ON UPDATE CASCADE
  139. ,	id_lpage     INTEGER NOT NULL REFERENCES Lpages  (id) ON DELETE CASCADE ON UPDATE CASCADE
  140. ,	id_args      INTEGER NOT NULL REFERENCES Argss   (id) ON DELETE CASCADE ON UPDATE CASCADE
  141. ,	rc           INTEGER
  142. ,	rsz          INTEGER
  143. ,	isz          INTEGER
  144. ,	osz          INTEGER
  145. );
  146.  
  147. CREATE INDEX idx_te_dt on T_Events(dtevent);
  148.  
  149. -- Section 3. Updatable view that replaces that table
  150. --    The view itself
  151. CREATE VIEW Loglines AS 
  152.   SELECT 
  153. 	E.id
  154. ,	id_log
  155. ,	linenr
  156. ,	V.vhost
  157. ,	H.hhost
  158. ,	C.client
  159. ,	C.ciip
  160. ,	U.user
  161. ,	dtevent
  162. ,	M.method
  163. ,	L.lpath
  164. ,	P.lpage
  165. ,	A.args
  166. ,	rc
  167. ,	rsz
  168. ,	isz
  169. ,	osz
  170. 	FROM T_Events AS E
  171. 	INNER JOIN Vhosts    AS V ON (V.id==E.id_vhost)
  172. 	INNER JOIN Hhosts    AS H ON (H.id==E.id_hhost)
  173. 	INNER JOIN Clients   AS C ON (C.id==E.id_client)
  174. 	INNER JOIN Users     AS U ON (U.id==E.id_user)
  175. 	INNER JOIN Methods   AS M ON (M.id==E.id_method)
  176. 	INNER JOIN Lpaths    AS L ON (L.id==E.id_lpath)
  177. 	INNER JOIN Lpages    AS P ON (P.id==E.id_lpage)
  178. 	INNER JOIN Argss     AS A ON (A.id==E.id_args);
  179.  
  180. --    The trigger that defers the insert into view to insert in events, with all pointers looked up and filled in.
  181. CREATE TRIGGER io_ins_Loglines INSTEAD OF INSERT ON Loglines
  182. FOR EACH ROW
  183. BEGIN
  184. 	INSERT INTO T_Events (
  185. 	id_log
  186. ,	linenr
  187. ,	id_vhost
  188. ,	id_hhost
  189. ,	id_client
  190. ,	id_user
  191. ,	dtevent
  192. ,	id_method
  193. ,	id_lpath
  194. ,	id_lpage
  195. ,	id_args
  196. ,	rc
  197. ,	rsz
  198. ,	isz
  199. ,	osz) VALUES (
  200. 	NEW.id_log
  201. ,	NEW.linenr
  202. ,	(SELECT id FROM Vhosts  WHERE  vhost==NEW.vhost)
  203. ,	(SELECT id FROM Hhosts  WHERE  hhost==NEW.hhost)
  204. ,	(SELECT id FROM Clients WHERE client==NEW.client)
  205. ,	(SELECT id FROM Users   WHERE   user==NEW.user)
  206. ,	NEW.dtevent
  207. ,	(SELECT id FROM Methods WHERE method==NEW.method)
  208. ,	(SELECT id FROM Lpaths  WHERE  lpath==NEW.lpath)
  209. ,	(SELECT id FROM Lpages  WHERE  lpage==NEW.lpage)
  210. ,	(SELECT id FROM Argss   WHERE   args==NEW.args)
  211. ,	NEW.rc
  212. ,	NEW.rsz
  213. ,	NEW.isz
  214. ,	NEW.osz);
  215. END;
  216.  
  217. -- Triggers for updating reference counters
  218. -- speeds up reporting, slows down updates
  219.  
  220. CREATE TRIGGER a_ins_events AFTER INSERT ON T_Events
  221. FOR EACH ROW
  222. BEGIN
  223. 	UPDATE Logs    SET refcnt = refcnt + 1 WHERE id = NEW.id_log;
  224. 	UPDATE Vhosts  SET refcnt = refcnt + 1 WHERE id = NEW.id_vhost;
  225. 	UPDATE Hhosts  SET refcnt = refcnt + 1 WHERE id = NEW.id_hhost;
  226. 	UPDATE Clients SET refcnt = refcnt + 1 WHERE id = NEW.id_client;
  227. 	UPDATE Users   SET refcnt = refcnt + 1 WHERE id = NEW.id_user;
  228. 	UPDATE Methods SET refcnt = refcnt + 1 WHERE id = NEW.id_method;
  229. 	UPDATE Lpaths  SET refcnt = refcnt + 1 WHERE id = NEW.id_lpath;
  230. 	UPDATE Lpages  SET refcnt = refcnt + 1 WHERE id = NEW.id_lpage;
  231. 	UPDATE Argss   SET refcnt = refcnt + 1 WHERE id = NEW.id_args;
  232. END;
  233.  
  234. CREATE TRIGGER a_del_events AFTER DELETE ON T_Events
  235. FOR EACH ROW
  236. BEGIN
  237. 	UPDATE Logs    SET refcnt = refcnt - 1 WHERE id = OLD.id_log;
  238. 	UPDATE Vhosts  SET refcnt = refcnt - 1 WHERE id = OLD.id_vhost;
  239. 	UPDATE Hhosts  SET refcnt = refcnt - 1 WHERE id = OLD.id_hhost;
  240. 	UPDATE Clients SET refcnt = refcnt - 1 WHERE id = OLD.id_client;
  241. 	UPDATE Users   SET refcnt = refcnt - 1 WHERE id = OLD.id_user;
  242. 	UPDATE Methods SET refcnt = refcnt - 1 WHERE id = OLD.id_method;
  243. 	UPDATE Lpaths  SET refcnt = refcnt - 1 WHERE id = OLD.id_lpath;
  244. 	UPDATE Lpages  SET refcnt = refcnt - 1 WHERE id = OLD.id_lpage;
  245. 	UPDATE Argss   SET refcnt = refcnt - 1 WHERE id = OLD.id_args;
  246. END;
  247.  
  248. CREATE VIEW Abbrevloglines AS 
  249.   SELECT 
  250. 	E.id
  251. ,	id_log
  252. ,	linenr
  253. ,	V.vh
  254. ,	H.hh
  255. ,	H.hhost
  256. ,	C.client
  257. ,	U.user
  258. ,	dtevent
  259. ,	M.method
  260. ,	L.lpath
  261. ,	rc
  262. ,	rsz
  263. ,	isz
  264. ,	osz
  265. 	FROM T_Events AS E
  266. 	INNER JOIN Vhosts    AS V ON (V.id==E.id_vhost)
  267. 	INNER JOIN Hhosts    AS H ON (H.id==E.id_hhost)
  268. 	INNER JOIN Clients   AS C ON (C.id==E.id_client)
  269. 	INNER JOIN Users     AS U ON (U.id==E.id_user)
  270. 	INNER JOIN Methods   AS M ON (M.id==E.id_method)
  271. 	INNER JOIN Lpaths    AS L ON (L.id==E.id_lpath);
  272.  
  273. -- Section 4. Any other tables
  274. --    Dates, can be used as Integers as well
  275. CREATE TABLE Dates (
  276. 	id INTEGER PRIMARY KEY  NOT NULL
  277. ,	d          REAL
  278. );
  279.  
  280. CREATE TABLE KnownIPs (
  281. 	ipdotted   TEXT PRIMARY KEY NOT NULL
  282. ,	owner      TEXT DEFAULT 'knuyt'
  283. ,	id_cat     INTEGER NOT NULL DEFAULT 0 REFERENCES Categories (id) ON DELETE CASCADE ON UPDATE CASCADE
  284. );
  285.  
  286. -- EOF
Success #stdin #stdout 0s 3008KB
comments (?)
stdin 
Standard input is empty
stdout 
Standard output is empty
https://ideone.com/bTOre
Space-optimize an Events table by referring to unique strings in separate tables.
language:
SQLite (sqlite 3.27.2)
created:
13 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!