36 lines
No EOL
1.3 KiB
SQL
36 lines
No EOL
1.3 KiB
SQL
CREATE TABLE events (
|
|
id TEXT NOT NULL, -- Event ID (32-byte hex)
|
|
original_id TEXT, -- Original (encrypted) event ID (32-byte hex)
|
|
pubkey TEXT NOT NULL, -- Author's public key (32-byte hex)
|
|
created_at INTEGER NOT NULL,-- Unix timestamp in seconds
|
|
kind INTEGER NOT NULL, -- Event kind number
|
|
content TEXT NOT NULL,
|
|
sig TEXT NOT NULL, -- Event signature (64-byte hex)
|
|
first_seen INTEGER,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE event_tags (
|
|
tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_id TEXT NOT NULL,
|
|
tag_name TEXT NOT NULL,
|
|
tag_index INTEGER NOT NULL,
|
|
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE event_tags_values (
|
|
tag_id INTEGER NOT NULL,
|
|
value_position INTEGER NOT NULL,
|
|
value TEXT NOT NULL,
|
|
FOREIGN KEY (tag_id) REFERENCES event_tags(tag_id)
|
|
);
|
|
|
|
CREATE INDEX idx_events_id ON events(id);
|
|
CREATE INDEX idx_events_pubkey ON events(pubkey);
|
|
CREATE INDEX idx_events_created_at ON events(created_at);
|
|
CREATE INDEX idx_events_kind ON events(kind);
|
|
CREATE INDEX idx_events_original_id ON events(original_id);
|
|
|
|
CREATE INDEX idx_event_tags_event_id ON event_tags(event_id);
|
|
CREATE INDEX idx_event_tags_name ON event_tags(tag_name, tag_index);
|
|
CREATE INDEX idx_event_tags_values ON event_tags_values(tag_id, value_position, value); |