app

Local-first trade for farms and co-ops
git clone https://radroots.dev/git/app.git
Log | Files | Refs | README | LICENSE

0027_local_interop_validation_receipt_projection_kind.sql (2732B)

      1 DROP INDEX IF EXISTS idx_local_interop_imports_seq;
      2 DROP INDEX IF EXISTS idx_local_interop_imports_owner_status;
      3 DROP INDEX IF EXISTS idx_local_interop_imports_projected;
      4 
      5 ALTER TABLE local_interop_imports RENAME TO local_interop_imports_validation_receipt_projection_kind_legacy;
      6 
      7 CREATE TABLE local_interop_imports (
      8     record_id TEXT PRIMARY KEY NOT NULL,
      9     local_seq INTEGER NOT NULL CHECK (local_seq >= 0),
     10     record_family TEXT NOT NULL CHECK (record_family IN ('local_work', 'signed_event')),
     11     local_status TEXT NOT NULL CHECK (
     12         local_status IN (
     13             'local_draft',
     14             'local_saved',
     15             'pending_publish',
     16             'published',
     17             'failed',
     18             'conflict'
     19         )
     20     ),
     21     source_runtime TEXT NOT NULL,
     22     owner_account_id TEXT,
     23     owner_pubkey TEXT,
     24     farm_key TEXT,
     25     listing_addr TEXT,
     26     projected_kind TEXT NOT NULL CHECK (
     27         projected_kind IN ('farm', 'listing', 'signed_event', 'validation_receipt', 'unsupported')
     28     ),
     29     projected_id TEXT,
     30     event_id TEXT,
     31     event_kind INTEGER,
     32     outbox_status TEXT NOT NULL CHECK (
     33         outbox_status IN ('none', 'pending', 'acknowledged', 'failed')
     34     ),
     35     relay_delivery_json TEXT,
     36     imported_at TEXT NOT NULL,
     37     event_pubkey TEXT,
     38     event_created_at INTEGER,
     39     event_tags_json TEXT,
     40     event_content TEXT,
     41     event_sig TEXT,
     42     raw_event_json TEXT
     43 );
     44 
     45 INSERT INTO local_interop_imports (
     46     record_id,
     47     local_seq,
     48     record_family,
     49     local_status,
     50     source_runtime,
     51     owner_account_id,
     52     owner_pubkey,
     53     farm_key,
     54     listing_addr,
     55     projected_kind,
     56     projected_id,
     57     event_id,
     58     event_kind,
     59     outbox_status,
     60     relay_delivery_json,
     61     imported_at,
     62     event_pubkey,
     63     event_created_at,
     64     event_tags_json,
     65     event_content,
     66     event_sig,
     67     raw_event_json
     68 )
     69 SELECT
     70     record_id,
     71     local_seq,
     72     record_family,
     73     local_status,
     74     source_runtime,
     75     owner_account_id,
     76     owner_pubkey,
     77     farm_key,
     78     listing_addr,
     79     projected_kind,
     80     projected_id,
     81     event_id,
     82     event_kind,
     83     outbox_status,
     84     relay_delivery_json,
     85     imported_at,
     86     event_pubkey,
     87     event_created_at,
     88     event_tags_json,
     89     event_content,
     90     event_sig,
     91     raw_event_json
     92 FROM local_interop_imports_validation_receipt_projection_kind_legacy;
     93 
     94 CREATE INDEX idx_local_interop_imports_seq
     95     ON local_interop_imports(local_seq);
     96 
     97 CREATE INDEX idx_local_interop_imports_owner_status
     98     ON local_interop_imports(owner_account_id, local_status, local_seq DESC);
     99 
    100 CREATE INDEX idx_local_interop_imports_projected
    101     ON local_interop_imports(projected_kind, projected_id);
    102 
    103 DROP TABLE local_interop_imports_validation_receipt_projection_kind_legacy;