X-Git-Url: http://git.squeep.com/?a=blobdiff_plain;f=src%2Fdb%2Fpostgres%2Fsql%2Fschema%2F1.0.2%2Fapply.sql;fp=src%2Fdb%2Fpostgres%2Fsql%2Fschema%2F1.0.2%2Fapply.sql;h=6da0924510d99a57362b358e23cff17880724016;hb=d5e7908d3e60ee0cb3149163d4749563cdfafeb3;hp=0000000000000000000000000000000000000000;hpb=a03352195c7ecfde25a8cf9aba6dd9666e1a3fea;p=websub-hub

diff --git a/src/db/postgres/sql/schema/1.0.2/apply.sql b/src/db/postgres/sql/schema/1.0.2/apply.sql
new file mode 100644
index 0000000..6da0924
--- /dev/null
+++ b/src/db/postgres/sql/schema/1.0.2/apply.sql
@@ -0,0 +1,28 @@
+BEGIN;
+	-- track when content was delivered as separate from latest content delivered
+	-- content_delivered continues to be the time the content was delivered, but becomes only informational
+	-- latest_content_delivered is date on topic content delivered
+	ALTER TABLE subscription
+		ADD COLUMN latest_content_delivered TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz
+	;
+	CREATE INDEX subscription_latest_content_delivered_idx ON subscription(latest_content_delivered);
+	-- migrate existing values
+	UPDATE subscription SET latest_content_delivered = content_delivered;
+	-- no need for this index
+	DROP INDEX subscription_content_delivered_idx;
+	-- update the view to use latest_cotnent_delivered
+	CREATE OR REPLACE VIEW subscription_delivery_needed AS
+		SELECT s.*
+		FROM subscription s JOIN topic t ON s.topic_id = t.id
+		WHERE
+			s.expires > now()
+		AND
+			s.latest_content_delivered < t.content_updated
+		AND
+			s.delivery_next_attempt < now()
+		AND
+			s.id NOT IN (SELECT id FROM subscription_delivery_in_progress_active)
+	;
+
+	INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 2);
+COMMIT;