From 1ab38e7d96610b441e99c1b0cd56e878a377e00d Mon Sep 17 00:00:00 2001
From: Dan White <dan@whiteaudio.com>
Date: Mon, 2 Jan 2023 22:05:19 -0600
Subject: [PATCH] update

---
 observations.sqbpro | 79 ++++++++++++++++++++++++++++++++++++---------
 1 file changed, 64 insertions(+), 15 deletions(-)

diff --git a/observations.sqbpro b/observations.sqbpro
index 2f9398f..c63189c 100644
--- a/observations.sqbpro
+++ b/observations.sqbpro
@@ -1,4 +1,4 @@
-<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="observations.db" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><current_tab id="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="7736"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="observations"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="observations" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_node" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_parent" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_rowid" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 6">-- Indexes make the world go 'round ... faster.
+<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="observations.db" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><current_tab id="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="6286"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="observations"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="observations" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_node" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_parent" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table><table schema="main" name="times_index_rowid" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk=""><sort/><column_widths/><filter_values/><display_formats/><hidden_columns/><plot_y_axes/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 6">-- Indexes make the world go 'round ... faster.
 CREATE INDEX IF NOT EXISTS gs_norad_index
 ON observations(ground_station, norad_cat_id);
 
@@ -16,23 +16,50 @@ ON observations(observer, ground_station);
 
 CREATE INDEX IF NOT EXISTS gs_observer_index
 ON observations(ground_station, observer);
-</sql><sql name="SQL 2">-- Special index for speeding up time range queries.
+</sql><sql name="SQL 2">
+-- Special index for speeding up time range queries.
 CREATE VIRTUAL TABLE IF NOT EXISTS times_index
-USING rtree(id, start, end);
+USING rtree(
+        id,
+        start,
+        end,
+        +ground_station INT,
+        +observer INT,
+        +norad_cat_id INT);
 
 INSERT OR REPLACE INTO times_index
-SELECT id, strftime(&quot;%s&quot;, start), strftime(&quot;%s&quot;, end)
+SELECT
+    id,
+    strftime(&quot;%s&quot;, start),
+    strftime(&quot;%s&quot;, end),
+    ground_station,
+    observer,
+    norad_cat_id
 FROM observations
 WHERE strftime(&quot;%s&quot;, start) &lt;= strftime(&quot;%s&quot;, end);
-</sql><sql name="SQL 10">-- Add triggers to keep the times Rtree updated
+
+-- (commented out since it takes a long time, and is only needed once anyway)
+</sql><sql name="SQL 10">
+-- Add triggers to keep the times Rtree updated
 
 CREATE TRIGGER IF NOT EXISTS times_insert_trigger
 AFTER INSERT
 ON observations
-WHEN strftime(&quot;%s&quot;, NEW.start) &lt;= strftime(&quot;%s&quot;, NEW.end)
+WHEN strftime(&quot;%s&quot;, NEW.start) &lt;= strftime(&quot;%s&quot;, NEW.end)  -- sanity check
 BEGIN
-	INSERT OR REPLACE INTO times_index(id, start, end)
-	VALUES (NEW.id, strftime(&quot;%s&quot;, NEW.start), strftime(&quot;%s&quot;, NEW.end));
+    INSERT OR REPLACE INTO times_index(
+            id,
+            start,
+            end,
+            ground_station,
+            observer,
+            norad_cat_id)
+    VALUES (NEW.id,
+            strftime(&quot;%s&quot;, NEW.start),
+            strftime(&quot;%s&quot;, NEW.end),
+            NEW.ground_station,
+            NEW.observer,
+            NEW.norad_cat_id);
 END;
 
 
@@ -41,18 +68,28 @@ AFTER UPDATE
 ON observations
 WHEN strftime(&quot;%s&quot;, NEW.start) &lt;= strftime(&quot;%s&quot;, NEW.end)
 BEGIN
-	INSERT OR REPLACE INTO times_index(id, start, end)
-	VALUES (NEW.id, strftime(&quot;%s&quot;, NEW.start), strftime(&quot;%s&quot;, NEW.end));
+    INSERT OR REPLACE INTO times_index(
+            id,
+            start,
+            end,
+            ground_station,
+            observer,
+            norad_cat_id)
+    VALUES (NEW.id,
+            strftime(&quot;%s&quot;, NEW.start),
+            strftime(&quot;%s&quot;, NEW.end),
+            NEW.ground_station,
+            NEW.observer,
+            NEW.norad_cat_id);
 END;
 
 CREATE TRIGGER IF NOT EXISTS times_delete_trigger
 AFTER DELETE
 ON observations
 BEGIN
-	DELETE FROM times_index
-	WHERE id = OLD.id;
-END;
-</sql><sql name="SQL 4">-- Observers with un-vetted observations
+    DELETE FROM times_index
+    WHERE id = OLD.id;
+END;</sql><sql name="SQL 4">-- Observers with un-vetted observations
 -- LONG
 SELECT
 	observer,
@@ -105,4 +142,16 @@ SELECT
 FROM observations
 GROUP BY observer
 ORDER BY num_obs DESC;
-</sql><sql name="SQL 11"></sql><current_tab id="7"/></tab_sql></sqlb_project>
+</sql><sql name="SQL 11">
+-- EXPLAIN QUERY PLAN
+SELECT
+    observer,
+    count(distinct ground_station) AS num_gs,
+    count(id) AS num_obs,
+    ground_station
+FROM times_index
+WHERE start &gt;= strftime(&quot;%s&quot;, &quot;2022-01-01&quot;)
+    AND start &lt;  strftime(&quot;%s&quot;, &quot;2023-01-01&quot;)
+GROUP BY observer
+ORDER BY num_obs DESC
+LIMIT 10;</sql><current_tab id="2"/></tab_sql></sqlb_project>
-- 
2.25.1