# HG changeset patch
# User Goffi <goffi@goffi.org>
# Date 1624609054 -7200
# Node ID 84ea57a8d6b3b36058b3bbdee001eddedc84cd0f
# Parent  8dd5e1bac9c3e31377da96c297c5632bb53d649f
memory (storage): adjustements to SQLAlchemy mapping to match current state of database

diff -r 8dd5e1bac9c3 -r 84ea57a8d6b3 sat/memory/sqla_mapping.py
--- a/sat/memory/sqla_mapping.py	Fri Jun 18 18:19:23 2021 +0200
+++ b/sat/memory/sqla_mapping.py	Fri Jun 25 10:17:34 2021 +0200
@@ -106,7 +106,14 @@
 class Profile(Base):
     __tablename__ = "profiles"
 
-    id = Column(Integer, primary_key=True)
+    id = Column(
+        Integer,
+        primary_key=True,
+        nullable=True,
+        # settings autoincrement would have negative performance impact
+        # cf. https://sqlite.org/autoinc.html
+        autoincrement=False
+    )
     name = Column(Text, unique=True)
 
     params = relationship("ParamInd", back_populates="profile", passive_deletes=True)
@@ -121,7 +128,11 @@
 class Component(Base):
     __tablename__ = "components"
 
-    profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True)
+    profile_id = Column(
+        ForeignKey("profiles.id", ondelete="CASCADE"),
+        nullable=True,
+        primary_key=True
+    )
     entry_point = Column(Text, nullable=False)
     profile = relationship("Profile")
 
@@ -129,7 +140,7 @@
 class MessageType(Base):
     __tablename__ = "message_types"
 
-    type = Column(Text, primary_key=True)
+    type = Column(Text, primary_key=True, nullable=True)
 
 
 class History(Base):
@@ -142,7 +153,7 @@
         )
     )
 
-    uid = Column(Text, primary_key=True)
+    uid = Column(Text, primary_key=True, nullable=True)
     stanza_id = Column(Text)
     update_uid = Column(Text)
     profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"))
@@ -250,9 +261,18 @@
 
 class Message(Base):
     __tablename__ = "message"
+    __table_args__ = (
+        Index("message__history_uid", "history_uid"),
+    )
 
-    id = Column(Integer, primary_key=True)
-    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), index=True)
+    id = Column(
+        Integer,
+        primary_key=True,
+        nullable=True,
+        # cf. note for Profile.id
+        autoincrement=False
+    )
+    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
     message = Column(Text)
     language = Column(Text)
 
@@ -265,9 +285,18 @@
 
 class Subject(Base):
     __tablename__ = "subject"
+    __table_args__ = (
+        Index("subject__history_uid", "history_uid"),
+    )
 
-    id = Column(Integer, primary_key=True)
-    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), index=True)
+    id = Column(
+        Integer,
+        primary_key=True,
+        nullable=True,
+        # cf. note for Profile.id
+        autoincrement=False,
+    )
+    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
     subject = Column(Text)
     language = Column(Text)
 
@@ -280,9 +309,18 @@
 
 class Thread(Base):
     __tablename__ = "thread"
+    __table_args__ = (
+        Index("thread__history_uid", "history_uid"),
+    )
 
-    id = Column(Integer, primary_key=True)
-    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), index=True)
+    id = Column(
+        Integer,
+        primary_key=True,
+        nullable=True,
+        # cf. note for Profile.id
+        autoincrement=False,
+    )
+    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
     thread_id = Column(Text)
     parent_id = Column(Text)
 
@@ -295,18 +333,18 @@
 class ParamGen(Base):
     __tablename__ = "param_gen"
 
-    category = Column(Text, primary_key=True, nullable=False)
-    name = Column(Text, primary_key=True, nullable=False)
+    category = Column(Text, primary_key=True, nullable=True)
+    name = Column(Text, primary_key=True, nullable=True)
     value = Column(Text)
 
 
 class ParamInd(Base):
     __tablename__ = "param_ind"
 
-    category = Column(Text, primary_key=True, nullable=False)
-    name = Column(Text, primary_key=True, nullable=False)
+    category = Column(Text, primary_key=True, nullable=True)
+    name = Column(Text, primary_key=True, nullable=True)
     profile_id = Column(
-        ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=False
+        ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True
     )
     value = Column(Text)
 
@@ -316,18 +354,18 @@
 class PrivateGen(Base):
     __tablename__ = "private_gen"
 
-    namespace = Column(Text, primary_key=True, nullable=False)
-    key = Column(Text, primary_key=True, nullable=False)
+    namespace = Column(Text, primary_key=True, nullable=True)
+    key = Column(Text, primary_key=True, nullable=True)
     value = Column(Text)
 
 
 class PrivateInd(Base):
     __tablename__ = "private_ind"
 
-    namespace = Column(Text, primary_key=True, nullable=False)
-    key = Column(Text, primary_key=True, nullable=False)
+    namespace = Column(Text, primary_key=True, nullable=True)
+    key = Column(Text, primary_key=True, nullable=True)
     profile_id = Column(
-        ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=False
+        ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True
     )
     value = Column(Text)
 
@@ -337,18 +375,18 @@
 class PrivateGenBin(Base):
     __tablename__ = "private_gen_bin"
 
-    namespace = Column(Text, primary_key=True, nullable=False)
-    key = Column(Text, primary_key=True, nullable=False)
+    namespace = Column(Text, primary_key=True, nullable=True)
+    key = Column(Text, primary_key=True, nullable=True)
     value = Column(LegacyPickle)
 
 
 class PrivateIndBin(Base):
     __tablename__ = "private_ind_bin"
 
-    namespace = Column(Text, primary_key=True, nullable=False)
-    key = Column(Text, primary_key=True, nullable=False)
+    namespace = Column(Text, primary_key=True, nullable=True)
+    key = Column(Text, primary_key=True, nullable=True)
     profile_id = Column(
-        ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=False
+        ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=True
     )
     value = Column(LegacyPickle)