Add user agent indexed by sha1

1 files changed, 11 insertions, 2 deletions

diff --git a/sql/sqlite3/02_hits.sql b/sql/sqlite3/02_hits.sql
index 2fd5539..ca76f0b 100644
--- a/sql/sqlite3/02_hits.sql
+++ b/sql/sqlite3/02_hits.sql
@@ -1,3 +1,11 @@
+pragma foreign_keys = on;
+
+create table user_agents (
+	hash varchar not null primary key,
+	name varchar not null,
+	last_seen_at timestamp not null
+);
+
 create table hits (
 	id integer primary key autoincrement,
 	domain_id integer check(domain_id >0),
@@ -8,8 +16,9 @@ create table hits (
 	query varchar null,
 	title varchar null,
 	referrer varchar null,
-	user_agent varchar null,
+	user_agent_hash varchar null,
 	view_port varchar null,
-	created_at timestamp not null
+	created_at timestamp not null,
+	foreign key(user_agent_hash) references user_agents(hash)
 );
 create index "hits#domain_id#created" on hits(domain_id, created_at);