Edit SQL schema to use string IDs not ints. Use token as ID. Update tests.

2 files changed, 17 insertions, 13 deletions

diff --git a/synapse/storage/appservice.py b/synapse/storage/appservice.py

index a520a859d3..a8780eca1e 100644
--- a/synapse/storage/appservice.py
+++ b/synapse/storage/appservice.py
@@ -231,7 +231,8 @@ class ApplicationServiceStore(SQLBaseStore):
             url=as_info["url"],
             namespaces=as_info["namespaces"],
             hs_token=as_info["hs_token"],
-            sender=as_info["sender"]
+            sender=as_info["sender"],
+            id=as_info["as_token"]  # the token is the only unique thing here
         )
 
     def _populate_appservice_cache(self, config_files):
@@ -268,16 +269,20 @@ class ApplicationServiceTransactionStore(SQLBaseStore):
             A Deferred which resolves to a list of ApplicationServices, which
             may be empty.
         """
-        sql = (
-            "SELECT r.*, a.* FROM application_services_state AS s LEFT JOIN"
-            " application_services AS a ON a.id=s.as_id LEFT JOIN"
-            " application_services_regex AS r ON r.as_id=a.id WHERE state = ?"
-        )
-        results = yield self._execute_and_decode(
-            "get_appservices_by_state", sql, state
+        results = yield self._simple_select_list(
+            "application_services_state",
+            dict(state=state),
+            ["as_id"]
         )
         # NB: This assumes this class is linked with ApplicationServiceStore
-        defer.returnValue(self._parse_services_dict(results))
+        as_list = yield self.get_app_services()
+        services = []
+
+        for res in results:
+            for service in as_list:
+                if service.id == res["as_id"]:
+                    services.append(service)
+        defer.returnValue(services)
 
     @defer.inlineCallbacks
     def get_appservice_state(self, service):
diff --git a/synapse/storage/schema/delta/15/appservice_txns.sql b/synapse/storage/schema/delta/15/appservice_txns.sql

index 13bbb2de2e..2b27e2a429 100644
--- a/synapse/storage/schema/delta/15/appservice_txns.sql
+++ b/synapse/storage/schema/delta/15/appservice_txns.sql
@@ -14,14 +14,13 @@
  */
 
 CREATE TABLE IF NOT EXISTS application_services_state(
-    as_id INTEGER PRIMARY KEY,
+    as_id TEXT PRIMARY KEY,
     state TEXT,
-    last_txn TEXT,
-    FOREIGN KEY(as_id) REFERENCES application_services(id)
+    last_txn TEXT
 );
 
 CREATE TABLE IF NOT EXISTS application_services_txns(
-    as_id INTEGER NOT NULL,
+    as_id TEXT NOT NULL,
     txn_id INTEGER NOT NULL,
     event_ids TEXT NOT NULL,
     UNIQUE(as_id, txn_id) ON CONFLICT ROLLBACK