From a16099dfdd679398a90eefb3ed467974648b3b26 Mon Sep 17 00:00:00 2001
From: Cédric Bonhomme <cedric@cedricbonhomme.org>
Date: Fri, 20 Nov 2020 15:43:48 +0100
Subject: updated indexes

---
 newspipe/models/article.py | 18 ++++++++++++------
 newspipe/models/feed.py    | 16 ++++++++++++----
 2 files changed, 24 insertions(+), 10 deletions(-)

diff --git a/newspipe/models/article.py b/newspipe/models/article.py
index af190053..c26d2530 100644
--- a/newspipe/models/article.py
+++ b/newspipe/models/article.py
@@ -28,7 +28,7 @@ __license__ = "GPLv3"
 
 from datetime import datetime
 
-from sqlalchemy import Index
+from sqlalchemy import Index, ForeignKeyConstraint
 from sqlalchemy.ext.associationproxy import association_proxy
 
 from newspipe.bootstrap import db
@@ -63,12 +63,18 @@ class Article(db.Model, RightMixin):
     )
     tags = association_proxy("tag_objs", "text")
 
-    # indexes
     __table_args__ = (
-       Index('user_id'),
-       Index('user_id', 'category_id'),
-       Index('user_id', 'feed_id'),
-       Index('ix_article_uid_fid_eid', user_id, feed_id, entry_id)
+            ForeignKeyConstraint([user_id], ['user.id'], ondelete='CASCADE'),
+            ForeignKeyConstraint([feed_id], ['feed.id'], ondelete='CASCADE'),
+            ForeignKeyConstraint([category_id], ['category.id'],
+                                 ondelete='CASCADE'),
+            Index('ix_article_eid_cid_uid', user_id, category_id, entry_id),
+            Index('ix_article_retrdate', retrieved_date),
+
+            Index('user_id'),
+            Index('user_id', 'category_id'),
+            Index('user_id', 'feed_id'),
+            Index('ix_article_uid_fid_eid', user_id, feed_id, entry_id)
     )
 
     # api whitelists
diff --git a/newspipe/models/feed.py b/newspipe/models/feed.py
index 4d067163..95c5f340 100644
--- a/newspipe/models/feed.py
+++ b/newspipe/models/feed.py
@@ -28,7 +28,7 @@ __license__ = "GPLv3"
 
 from datetime import datetime
 
-from sqlalchemy import Index, desc
+from sqlalchemy import Index, desc, ForeignKeyConstraint
 from sqlalchemy.orm import validates
 
 from newspipe.bootstrap import db
@@ -72,9 +72,17 @@ class Feed(db.Model, RightMixin):
         order_by=desc(Article.date),
     )
 
-    # index
-    idx_feed_uid_cid = Index("user_id", "category_id")
-    idx_feed_uid = Index("user_id")
+    __table_args__ = (
+            ForeignKeyConstraint([user_id], ['user.id'], ondelete='CASCADE'),
+            ForeignKeyConstraint([category_id], ['category.id'],
+                                 ondelete='CASCADE'),
+            ForeignKeyConstraint([icon_url], ['icon.url']),
+            Index('ix_feed_uid', user_id),
+            Index('ix_feed_uid_cid', user_id, category_id),
+    )
+
+    # idx_feed_uid_cid = Index("user_id", "category_id")
+    # idx_feed_uid = Index("user_id")
 
     # api whitelists
     @staticmethod
-- 
cgit