Note that "index" and "transaction" are reserved in sqlite and alter table cannot be used to add a primary key.
With the below changes to the default schema and sequencer schemas, the schemas can at least be applied successfully with sqlite3 test.db < X.sql
diff --git a/migrations/V10__init_schema.sql b/migrations/V10__init_schema.sql
index 0ed12c67..bd3f3bec 100644
--- a/migrations/V10__init_schema.sql
+++ b/migrations/V10__init_schema.sql
@@ -47,7 +47,7 @@ CREATE TABLE leaf
qc JSONB NOT NULL
);
-CREATE TABLE transaction
+CREATE TABLE "transaction"
(
hash VARCHAR NOT NULL,
-- Block containing this transaction.
@@ -55,11 +55,11 @@ CREATE TABLE transaction
-- Position within the block. Transaction indices are an application-specific type, so we store
-- it as a serialized blob. We use JSON instead of a binary format so that the application can
-- make use of the transaction index in its own SQL queries.
- index JSONB NOT NULL
+ "index" JSONB NOT NULL,
+ PRIMARY KEY (block_height, "index")
);
-ALTER TABLE transaction ADD CONSTRAINT transaction_pk PRIMARY KEY (block_height, index);
-- This index is not unique, because nothing stops HotShot from sequencing duplicate transactions.
-CREATE INDEX transaction_hash ON transaction (hash);
+CREATE INDEX transaction_hash ON "transaction" (hash);
CREATE TABLE pruned_height (
id SERIAL PRIMARY KEY,
It would be great for the dev-node and maybe also otherwise if we could use all features of the query service without using postgres.
Since late last year sqlite has JSONB support: https://sqlite.org/jsonb.html
Note that "index" and "transaction" are reserved in sqlite and alter table cannot be used to add a primary key.
With the below changes to the default schema and sequencer schemas, the schemas can at least be applied successfully with
sqlite3 test.db < X.sql