From: Ant Zucaro <azucaro@gmail.com>
Date: Wed, 19 Nov 2014 02:17:01 +0000 (-0500)
Subject: Add indexes. Rename to reflect its purpose.
X-Git-Url: https://git.xonotic.org/?p=xonotic%2Fxonstatdb.git;a=commitdiff_plain;h=e807dc64763d12d9e141cda40f91e4d143b7aeaf

Add indexes. Rename to reflect its purpose.
---

diff --git a/scripts/gen_partitions.shl b/scripts/gen_partitions.shl
new file mode 100755
index 0000000..963f829
--- /dev/null
+++ b/scripts/gen_partitions.shl
@@ -0,0 +1,82 @@
+#!/bin/bash
+
+table=$1
+start_year=$2
+end_year=$3
+
+for year in `seq $start_year $end_year`
+do
+    for qtr in "q1" "q2" "q3" "q4"
+    do
+        printf "CREATE TABLE IF NOT EXISTS xonstat.%s_%s%s ( \n" $table $year $qtr
+        if [[ $qtr = "q1" ]]
+        then
+            printf "\tCHECK ( create_dt >= DATE '%s-01-01' AND create_dt < DATE '%s-04-01' ) \n" $year $year
+        elif [[ $qtr = "q2" ]]
+        then
+            printf "\tCHECK ( create_dt >= DATE '%s-04-01' AND create_dt < DATE '%s-07-01' ) \n" $year $year
+        elif [[ $qtr = "q3" ]]
+        then
+            printf "\tCHECK ( create_dt >= DATE '%s-07-01' AND create_dt < DATE '%s-10-01' ) \n" $year $year
+        elif [[ $qtr = "q4" ]]
+        then
+            next_year=$[year + 1]
+            printf "\tCHECK ( create_dt >= DATE '%s-10-01' AND create_dt < DATE '%s-01-01' ) \n" $year $next_year
+        fi
+
+        printf ") INHERITS (%s);\n\n" $table
+
+        # indexes
+        printf "CREATE INDEX %s_%s%s_ix001 on %s_%s%s(create_dt);\n" $table $year $qtr $table $year $qtr
+
+        # conditional indexes that depend on the table
+        if [[ $table = "player_game_stats" || $table = "player_weapon_stats" ]]
+        then
+            printf "CREATE INDEX %s_%s%s_ix002 on %s_%s%s(game_id);\n" $table $year $qtr $table $year $qtr
+            printf "CREATE INDEX %s_%s%s_ix003 on %s_%s%s(player_id);\n" $table $year $qtr $table $year $qtr
+        fi
+        printf "\n"
+    done
+done
+printf "\n"
+
+printf "CREATE OR REPLACE FUNCTION %s_ins()\n" $table
+printf "RETURNS TRIGGER AS \$\$\n"
+printf "BEGIN\n"
+
+for i in `seq $start_year $end_year`
+do
+
+    if [[ start_year -eq i ]]
+    then
+        printf "\tIF (NEW.create_dt >= DATE '%s-01-01' AND NEW.create_dt < DATE '%s-04-01') THEN\n" $i $i
+    else
+        printf "\tELSIF (NEW.create_dt >= DATE '%s-01-01' AND NEW.create_dt < DATE '%s-04-01') THEN\n" $i $i
+    fi
+    printf "\t\tINSERT INTO %s_%sQ1 VALUES (NEW.*);\n" $table $i
+
+    printf "\tELSIF (NEW.create_dt >= DATE '%s-04-01' AND NEW.create_dt < DATE '%s-07-01') THEN\n" $i $i
+    printf "\t\tINSERT INTO %s_%sQ2 VALUES (NEW.*);\n" $table $i
+
+    printf "\tELSIF (NEW.create_dt >= DATE '%s-07-01' AND NEW.create_dt < DATE '%s-10-01') THEN\n" $i $i
+    printf "\t\tINSERT INTO %s_%sQ3 VALUES (NEW.*);\n" $table $i
+
+    next_year=$[i + 1]
+    printf "\tELSIF (NEW.create_dt >= DATE '%s-10-01' AND NEW.create_dt < DATE '%s-01-01') THEN\n" $i $next_year
+    printf "\t\tINSERT INTO %s_%sQ4 VALUES (NEW.*);\n" $table $i
+
+done
+
+printf "\tELSE\n"
+printf "\t\tRAISE EXCEPTION 'Date out of range. Fix the %s_ins() trigger!';\n" $table
+printf "\tEND IF;\n"
+printf "\tRETURN NULL;\n"
+
+printf "END\n"
+printf "\$\$\n"
+printf "LANGUAGE plpgsql;\n\n"
+
+printf "DROP TRIGGER IF EXISTS %s_ins_trg ON xonstat.%s;\n" $table $table
+printf "CREATE TRIGGER %s_ins_trg\n" $table
+printf "BEFORE INSERT on xonstat.%s\n" $table
+printf "FOR EACH ROW EXECUTE PROCEDURE %s_ins();\n" $table
diff --git a/scripts/gen_trigger.shl b/scripts/gen_trigger.shl
deleted file mode 100755
index 3a6d2e8..0000000
--- a/scripts/gen_trigger.shl
+++ /dev/null
@@ -1,71 +0,0 @@
-#!/bin/bash
-
-table=$1
-start_year=$2
-end_year=$3
-
-for year in `seq $start_year $end_year`
-do
-    for qtr in "q1" "q2" "q3" "q4"
-    do
-        printf "CREATE TABLE IF NOT EXISTS xonstat.%s_%s%s ( \n" $table $year $qtr
-        if [[ $qtr = "q1" ]]
-        then
-            printf "\tCHECK ( create_dt >= DATE '%s-01-01' AND create_dt < DATE '%s-04-01' ) \n" $year $year
-        elif [[ $qtr = "q2" ]]
-        then
-            printf "\tCHECK ( create_dt >= DATE '%s-04-01' AND create_dt < DATE '%s-07-01' ) \n" $year $year
-        elif [[ $qtr = "q3" ]]
-        then
-            printf "\tCHECK ( create_dt >= DATE '%s-07-01' AND create_dt < DATE '%s-10-01' ) \n" $year $year
-        elif [[ $qtr = "q4" ]]
-        then
-            next_year=$[year + 1]
-            printf "\tCHECK ( create_dt >= DATE '%s-10-01' AND create_dt < DATE '%s-01-01' ) \n" $year $next_year
-        fi
-
-        printf ") INHERITS (%s);\n\n" $table
-    done
-done
-printf "\n"
-
-printf "CREATE OR REPLACE FUNCTION %s_ins()\n" $table
-printf "RETURNS TRIGGER AS \$\$\n"
-printf "BEGIN\n"
-
-for i in `seq $start_year $end_year`
-do
-
-    if [[ start_year -eq i ]]
-    then
-        printf "\tIF (NEW.create_dt >= DATE '%s-01-01' AND NEW.create_dt < DATE '%s-04-01') THEN\n" $i $i
-    else
-        printf "\tELSIF (NEW.create_dt >= DATE '%s-01-01' AND NEW.create_dt < DATE '%s-04-01') THEN\n" $i $i
-    fi
-    printf "\t\tINSERT INTO %s_%sQ1 VALUES (NEW.*);\n" $table $i
-
-    printf "\tELSIF (NEW.create_dt >= DATE '%s-04-01' AND NEW.create_dt < DATE '%s-07-01') THEN\n" $i $i
-    printf "\t\tINSERT INTO %s_%sQ2 VALUES (NEW.*);\n" $table $i
-
-    printf "\tELSIF (NEW.create_dt >= DATE '%s-07-01' AND NEW.create_dt < DATE '%s-10-01') THEN\n" $i $i
-    printf "\t\tINSERT INTO %s_%sQ3 VALUES (NEW.*);\n" $table $i
-
-    next_year=$[i + 1]
-    printf "\tELSIF (NEW.create_dt >= DATE '%s-10-01' AND NEW.create_dt < DATE '%s-01-01') THEN\n" $i $next_year
-    printf "\t\tINSERT INTO %s_%sQ4 VALUES (NEW.*);\n" $table $i
-
-done
-
-printf "\tELSE\n"
-printf "\t\tRAISE EXCEPTION 'Date out of range. Fix the %s_ins() trigger!';\n" $table
-printf "\tEND IF;\n"
-printf "\tRETURN NULL;\n"
-
-printf "END\n"
-printf "\$\$\n"
-printf "LANGUAGE plpgsql;\n\n"
-
-printf "DROP TRIGGER IF EXISTS %s_ins_trg ON xonstat.games;\n" $table
-printf "CREATE TRIGGER %s_ins_trg\n" $table
-printf "BEFORE INSERT on xonstat.%s\n" $table
-printf "FOR EACH ROW EXECUTE PROCEDURE %s_ins();\n" $table