federationlib/database/query_documents.sql

59 lines
3.5 KiB
MySQL
Raw Permalink Normal View History

2023-06-04 14:23:51 -04:00
create table query_documents
(
document_id varchar(26) not null comment 'The unique reference ID of the document (UUID v4)'
primary key,
subject_type varchar(32) default 'RECON' not null comment 'The subject type of the query document',
client_id varchar(36) not null comment 'The ID of the client that submitted the document',
client_signature varchar(36) null comment 'The authorization token signed with the document by the client',
2023-06-23 03:53:47 -04:00
event_type tinytext not null comment 'The event type that occured',
2023-06-04 14:23:51 -04:00
channel_peer varchar(256) null comment 'Optional. The channel that the content was sent on',
2023-06-23 03:53:47 -04:00
resent_from_peer varchar(256) null comment 'Optional. The original peer that constructed the message, this indicates "from_peer" is resending the content',
2023-06-04 14:23:51 -04:00
from_peer varchar(256) null comment 'Optional. The peer that sent the content',
to_peer varchar(256) null comment 'Optional. The peer that is the intended reciever of the content',
2023-06-23 03:53:47 -04:00
reply_to_peer varchar(256) null comment 'Optional. This indicates the message is a reply to the peer',
2023-06-04 14:23:51 -04:00
proxy_peer varchar(256) null comment 'Optional. The proxy peer used to send the content, may indicates "from_peer" is the original sender.',
retain tinyint default 0 not null comment 'Indicates if this record should be retained or not',
timestamp bigint default unix_timestamp() not null comment 'The Unix Timestamp of the document''s submission (based off the authorization token)',
constraint query_documents_document_id_uindex
unique (document_id),
constraint query_documents_discovered_peers_federated_address_fk
foreign key (channel_peer) references peers (federated_address),
constraint query_documents_discovered_peers_federated_address_fk2
foreign key (from_peer) references peers (federated_address),
constraint query_documents_discovered_peers_federated_address_fk3
foreign key (to_peer) references peers (federated_address),
constraint query_documents_peers_federated_address_fk
2023-06-23 03:53:47 -04:00
foreign key (proxy_peer) references peers (federated_address),
constraint query_documents_peers_federated_address_fk2
foreign key (resent_from_peer) references peers (federated_address),
constraint query_documents_peers_federated_address_fk3
foreign key (reply_to_peer) references peers (federated_address)
2023-06-04 14:23:51 -04:00
)
comment 'An archive of all the submitted query documents the database received';
create index query_documents_channel_peer_index
on query_documents (channel_peer);
create index query_documents_client_id_index
on query_documents (client_id);
create index query_documents_from_peer_index
on query_documents (from_peer);
create index query_documents_proxy_peer_index
on query_documents (proxy_peer);
2023-06-23 03:53:47 -04:00
create index query_documents_reply_to_peer_index
on query_documents (reply_to_peer);
create index query_documents_resent_from_peer_index
on query_documents (resent_from_peer);
2023-06-04 14:23:51 -04:00
create index query_documents_retain_index
on query_documents (retain);
create index query_documents_subject_type_index
on query_documents (subject_type);
create index query_documents_to_peer_index
2023-06-23 03:53:47 -04:00
on query_documents (to_peer);