0002_network_source_runtime.up.sql (2809B)
1 create table local_event_record_network_source_next ( 2 seq integer primary key autoincrement, 3 change_seq integer not null unique, 4 record_id text not null unique, 5 family text not null check (family in ('local_work', 'signed_event')), 6 status text not null check (status in ('local_draft', 'local_saved', 'pending_publish', 'published', 'failed', 'conflict')), 7 source_runtime text not null check (source_runtime in ('cli', 'app', 'network', 'service', 'worker', 'test')), 8 created_at_ms integer not null, 9 inserted_at_ms integer not null, 10 updated_at_ms integer not null, 11 owner_account_id text, 12 owner_pubkey text, 13 farm_id text, 14 listing_addr text, 15 local_work_json text, 16 event_id text, 17 event_kind integer, 18 event_pubkey text, 19 event_created_at integer, 20 event_tags_json text, 21 event_content text, 22 event_sig text, 23 raw_event_json text, 24 outbox_status text not null check (outbox_status in ('none', 'pending', 'acknowledged', 'failed')), 25 relay_set_fingerprint text, 26 relay_delivery_json text, 27 check (change_seq >= 1), 28 check (trim(record_id) <> ''), 29 check (family <> 'local_work' or local_work_json is not null), 30 check (family <> 'local_work' or outbox_status = 'none'), 31 check (family <> 'signed_event' or (event_id is not null and event_kind is not null and event_pubkey is not null and event_sig is not null and raw_event_json is not null)) 32 ); 33 34 insert into local_event_record_network_source_next( 35 seq, 36 change_seq, 37 record_id, 38 family, 39 status, 40 source_runtime, 41 created_at_ms, 42 inserted_at_ms, 43 updated_at_ms, 44 owner_account_id, 45 owner_pubkey, 46 farm_id, 47 listing_addr, 48 local_work_json, 49 event_id, 50 event_kind, 51 event_pubkey, 52 event_created_at, 53 event_tags_json, 54 event_content, 55 event_sig, 56 raw_event_json, 57 outbox_status, 58 relay_set_fingerprint, 59 relay_delivery_json 60 ) 61 select 62 seq, 63 change_seq, 64 record_id, 65 family, 66 status, 67 source_runtime, 68 created_at_ms, 69 inserted_at_ms, 70 updated_at_ms, 71 owner_account_id, 72 owner_pubkey, 73 farm_id, 74 listing_addr, 75 local_work_json, 76 event_id, 77 event_kind, 78 event_pubkey, 79 event_created_at, 80 event_tags_json, 81 event_content, 82 event_sig, 83 raw_event_json, 84 outbox_status, 85 relay_set_fingerprint, 86 relay_delivery_json 87 from local_event_record 88 order by seq asc; 89 90 drop table local_event_record; 91 alter table local_event_record_network_source_next rename to local_event_record; 92 93 create index local_event_record_change_seq_idx on local_event_record(change_seq); 94 create index local_event_record_event_id_idx on local_event_record(event_id); 95 create index local_event_record_listing_addr_idx on local_event_record(listing_addr); 96 create index local_event_record_owner_pubkey_idx on local_event_record(owner_pubkey); 97 create index local_event_record_status_idx on local_event_record(status);