8 files changed, 40 insertions, 19 deletions
diff --git a/tools/sql/data-300-ew.txt b/tools/sql/data-300-ew.txt
deleted file mode 100644
index 0f6cb16..0000000
--- a/tools/sql/data-300-ew.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-INSERT OR REPLACE INTO titles VALUES ( '300-earth-and-water', '300: Earth & Water', 267058 );
-INSERT OR REPLACE INTO roles VALUES ( '300-earth-and-water', 'Persia' );
-INSERT OR REPLACE INTO roles VALUES ( '300-earth-and-water', 'Greece' );
diff --git a/tools/sql/data-caesar.txt b/tools/sql/data-caesar.txt
deleted file mode 100644
index 1e11df8..0000000
--- a/tools/sql/data-caesar.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-INSERT OR REPLACE INTO titles VALUES ( 'julius-caesar', 'Julius Caesar', 37836 );
-INSERT OR REPLACE INTO roles VALUES ( 'julius-caesar', 'Caesar' );
-INSERT OR REPLACE INTO roles VALUES ( 'julius-caesar', 'Pompeius' );
diff --git a/tools/sql/data-crusader.txt b/tools/sql/data-crusader.txt
deleted file mode 100644
index 4f565b9..0000000
--- a/tools/sql/data-crusader.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-INSERT OR REPLACE INTO titles VALUES ( 'crusader-rex', 'Crusader Rex', 8481 );
-INSERT OR REPLACE INTO roles VALUES ( 'crusader-rex', 'Franks' );
-INSERT OR REPLACE INTO roles VALUES ( 'crusader-rex', 'Saracens' );
diff --git a/tools/sql/data-hammer.txt b/tools/sql/data-hammer.txt
deleted file mode 100644
index 853bed4..0000000
--- a/tools/sql/data-hammer.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-INSERT OR REPLACE INTO titles VALUES ( 'hammer-of-the-scots', 'Hammer of the Scots', 3685 );
-INSERT OR REPLACE INTO roles VALUES ( 'hammer-of-the-scots', 'England' );
-INSERT OR REPLACE INTO roles VALUES ( 'hammer-of-the-scots', 'Scotland' );
diff --git a/tools/sql/data-richard.txt b/tools/sql/data-richard.txt
deleted file mode 100644
index ff0787c..0000000
--- a/tools/sql/data-richard.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-INSERT OR REPLACE INTO titles VALUES ( 'richard-iii', 'Richard III', 25277 );
-INSERT OR REPLACE INTO roles VALUES ( 'richard-iii', 'York' );
-INSERT OR REPLACE INTO roles VALUES ( 'richard-iii', 'Lancaster' );
diff --git a/tools/sql/data-tripoli.txt b/tools/sql/data-tripoli.txt
deleted file mode 100644
index 0addd6d..0000000
--- a/tools/sql/data-tripoli.txt
+++ /dev/null
@@ -1,3 +0,0 @@
-INSERT OR REPLACE INTO titles VALUES ( 'shores-of-tripoli', 'The Shores of Tripoli', 237860 );
-INSERT OR REPLACE INTO roles VALUES ( 'shores-of-tripoli', 'Tripolitania' );
-INSERT OR REPLACE INTO roles VALUES ( 'shores-of-tripoli', 'United States' );
diff --git a/tools/sql/data.txt b/tools/sql/data.txt
new file mode 100644
index 0000000..7268acc
--- /dev/null
+++ b/tools/sql/data.txt
@@ -0,0 +1,29 @@
+PRAGMA foreign_keys=0;
+
+INSERT OR REPLACE INTO titles VALUES ( '300-earth-and-water', '300: Earth & Water', 267058, 0 );
+INSERT OR REPLACE INTO roles VALUES ( '300-earth-and-water', 'Persia' );
+INSERT OR REPLACE INTO roles VALUES ( '300-earth-and-water', 'Greece' );
+
+INSERT OR REPLACE INTO titles VALUES ( 'crusader-rex', 'Crusader Rex', 8481, 0 );
+INSERT OR REPLACE INTO roles VALUES ( 'crusader-rex', 'Franks' );
+INSERT OR REPLACE INTO roles VALUES ( 'crusader-rex', 'Saracens' );
+
+INSERT OR REPLACE INTO titles VALUES ( 'julius-caesar', 'Julius Caesar', 37836, 0 );
+INSERT OR REPLACE INTO roles VALUES ( 'julius-caesar', 'Caesar' );
+INSERT OR REPLACE INTO roles VALUES ( 'julius-caesar', 'Pompeius' );
+
+INSERT OR REPLACE INTO titles VALUES ( 'hammer-of-the-scots', 'Hammer of the Scots', 3685, 0 );
+INSERT OR REPLACE INTO roles VALUES ( 'hammer-of-the-scots', 'England' );
+INSERT OR REPLACE INTO roles VALUES ( 'hammer-of-the-scots', 'Scotland' );
+
+INSERT OR REPLACE INTO titles VALUES ( 'richard-iii', 'Richard III', 25277, 0 );
+INSERT OR REPLACE INTO roles VALUES ( 'richard-iii', 'York' );
+INSERT OR REPLACE INTO roles VALUES ( 'richard-iii', 'Lancaster' );
+
+INSERT OR REPLACE INTO titles VALUES ( 'shores-of-tripoli', 'The Shores of Tripoli', 237860, 0 );
+INSERT OR REPLACE INTO roles VALUES ( 'shores-of-tripoli', 'Tripolitania' );
+INSERT OR REPLACE INTO roles VALUES ( 'shores-of-tripoli', 'United States' );
+
+INSERT OR REPLACE INTO titles VALUES ( 'wilderness-war', 'Wilderness War', 1822, 1 );
+INSERT OR REPLACE INTO roles VALUES ( 'wilderness-war', 'French' );
+INSERT OR REPLACE INTO roles VALUES ( 'wilderness-war', 'British' );
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index 5e80ec6..dc2bcc6 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -4,7 +4,8 @@ CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT NOT NULL PRIMARY KEY ) WIT
 CREATE TABLE IF NOT EXISTS titles (
 	title_id TEXT NOT NULL PRIMARY KEY,
 	title_name TEXT NOT NULL,
-	bgg INTEGER
+	bgg INTEGER,
+	hidden BOOLEAN
 ) WITHOUT ROWID;
 
 CREATE TABLE IF NOT EXISTS roles (
@@ -290,3 +291,12 @@ BEGIN
 	DELETE FROM last_notified WHERE game_id = old.game_id;
 	DELETE FROM players WHERE game_id = old.game_id;
 END;
+
+DROP VIEW IF EXISTS game_stat_view;
+CREATE VIEW game_stat_view AS
+	SELECT title_id, scenario, result, count(*) AS count
+	FROM game_full_view
+	NATURAL JOIN titles
+	WHERE hidden=0 AND is_solo=0 AND status=2
+	GROUP BY title_name, scenario, result
+	;