Repository: p2panda/aquadoggo (language: rust)

Issue:
Add casted index for u64 fields that are stored as strings
Some u64 values are stored as strings because SQLite doesn't store u64. In some queries such as `SqlStorage::get_paginated_log_entries` these values are cast as numeric values.

We should add an index on those casted values so they don't have to be generated for each query.


Discussion / hints:

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 8ee833c22..d918fbf05 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -22,6 +22,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Move to `tokio` async runtime [#75](https://github.com/p2panda/aquadoggo/pull/75)
 - Implement SQL storage using `p2panda_rs` storage provider traits [#80](https://github.com/p2panda/aquadoggo/pull/80)
 - Improve `Signal` efficiency in `ServiceManager` [#95](https://github.com/p2panda/aquadoggo/pull/95)
+- Improvements for log and entry table layout [#124](https://github.com/p2panda/aquadoggo/issues/122)
 
 ## [0.2.0]
 
diff --git a/aquadoggo/migrations/20201229215646_create-entries.sql b/aquadoggo/migrations/20201229215646_create-entries.sql
index 741494fff..6508a0185 100644
--- a/aquadoggo/migrations/20201229215646_create-entries.sql
+++ b/aquadoggo/migrations/20201229215646_create-entries.sql
@@ -1,14 +1,17 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 
 CREATE TABLE IF NOT EXISTS entries (
-    author            VARCHAR(64)       NOT NULL,
-    entry_bytes       TEXT              NOT NULL,
-    entry_hash        VARCHAR(68)       NOT NULL UNIQUE,
-    -- Store u64 integer as 20 character string
-    log_id            VARCHAR(20)       NOT NULL,
+    author            TEXT      NOT NULL,
+    entry_bytes       TEXT      NOT NULL,
+    entry_hash        TEXT      NOT NULL UNIQUE,
+    -- Store u64 integer as character string
+    log_id            TEXT      NOT NULL,
     payload_bytes     TEXT,
-    payload_hash      VARCHAR(68)       NOT NULL,
-    -- Store u64 integer as 20 character string
-    seq_num           VARCHAR(20)       NOT NULL,
+    payload_hash      TEXT      NOT NULL,
+    -- Store u64 integer as character string
+    seq_num           TEXT      NOT NULL,
     PRIMARY KEY (author, log_id, seq_num)
 );
+
+-- Create an index for sorting by sequence number
+CREATE INDEX idx_entries_by_seq_num ON entries (CAST(seq_num AS NUMERIC));
diff --git a/aquadoggo/migrations/20201230002752_create-logs.sql b/aquadoggo/migrations/20201230002752_create-logs.sql
index 9412b2fe8..88e2672ec 100644
--- a/aquadoggo/migrations/20201230002752_create-logs.sql
+++ b/aquadoggo/migrations/20201230002752_create-logs.sql
@@ -1,14 +1,17 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 
 CREATE TABLE IF NOT EXISTS logs (
-    author            VARCHAR(64)       NOT NULL,
-    document          VARCHAR(68)       NOT NULL,
-    -- Store u64 integer as 20 character string
-    log_id            VARCHAR(20)       NOT NULL,
-    schema            VARCHAR(68)       NOT NULL,
+    author            TEXT      NOT NULL,
+    document          TEXT      NOT NULL,
+    -- Store u64 integer as text
+    log_id            TEXT      NOT NULL,
+    schema            TEXT      NOT NULL,
     PRIMARY KEY (author, document, log_id),
     UNIQUE(author, log_id)
 );
 
 -- Create an index for querying by schema
 CREATE INDEX idx_logs_schema ON logs (author, log_id, schema);
+
+-- Create an index for sorting by log id
+CREATE INDEX idx_logs_by_id ON logs (CAST(log_id AS NUMERIC));
diff --git a/aquadoggo/src/db/stores/entry.rs b/aquadoggo/src/db/stores/entry.rs
index 5b45fbd3b..65207801c 100644
--- a/aquadoggo/src/db/stores/entry.rs
+++ b/aquadoggo/src/db/stores/entry.rs
@@ -257,7 +257,7 @@ impl EntryStore<StorageEntry> for SqlStorage {
                 author = $1
                 AND log_id = $2
             ORDER BY
-                CAST(seq_num AS INTEGER) DESC
+                CAST(seq_num AS NUMERIC) DESC
             LIMIT
                 1
             ",
@@ -335,9 +335,9 @@ impl EntryStore<StorageEntry> for SqlStorage {
             WHERE
                 author = $1
                 AND log_id = $2
-                AND CAST(seq_num AS INTEGER) BETWEEN $3 and $4
+                AND CAST(seq_num AS NUMERIC) BETWEEN $3 and $4
             ORDER BY
-                CAST(seq_num AS INTEGER)
+                CAST(seq_num AS NUMERIC)
             ",
         )
         .bind(author.as_str())
@@ -388,9 +388,9 @@ impl EntryStore<StorageEntry> for SqlStorage {
             WHERE
                 author = $1
                 AND log_id = $2
-                AND CAST(seq_num AS INTEGER) IN ({})
+                AND CAST(seq_num AS NUMERIC) IN ({})
             ORDER BY
-                CAST(seq_num AS INTEGER) DESC
+                CAST(seq_num AS NUMERIC) DESC
             ",
             cert_pool_seq_nums
         );


Test patch: