82 lines
5.3 KiB
SQL
82 lines
5.3 KiB
SQL
-- Synthetic fixture for the disposable, network-isolated CI database only.
|
|
-- Required columns/types follow Paper Atlas's fixed a3b677b migration.
|
|
CREATE SCHEMA research_fixture;
|
|
CREATE TABLE research_fixture.papers (
|
|
id bigint PRIMARY KEY, title text NOT NULL, abstract text, title_key text COLLATE "C" NOT NULL,
|
|
authors_json text NOT NULL, first_published text
|
|
);
|
|
CREATE INDEX papers_title_key ON research_fixture.papers(title_key);
|
|
CREATE INDEX papers_search_gin ON research_fixture.papers USING gin
|
|
(to_tsvector('english'::regconfig,coalesce(title,'') || ' ' || coalesce(abstract,'')));
|
|
CREATE TABLE research_fixture.sources (id bigint PRIMARY KEY,name text NOT NULL);
|
|
CREATE TABLE research_fixture.source_records (
|
|
id bigint PRIMARY KEY,source_id bigint REFERENCES research_fixture.sources(id),
|
|
paper_id bigint REFERENCES research_fixture.papers(id),external_id text,url text,metadata_json text
|
|
);
|
|
CREATE TABLE research_fixture.identifiers (scheme text,value text,paper_id bigint REFERENCES research_fixture.papers(id),PRIMARY KEY(scheme,value));
|
|
CREATE TABLE research_fixture.tags (id bigint PRIMARY KEY,namespace text,value text,UNIQUE(namespace,value));
|
|
CREATE TABLE research_fixture.paper_tags (
|
|
paper_id bigint REFERENCES research_fixture.papers(id),tag_id bigint REFERENCES research_fixture.tags(id),
|
|
evidence_key text,PRIMARY KEY(paper_id,tag_id,evidence_key)
|
|
);
|
|
CREATE TABLE research_fixture.venues (id bigint PRIMARY KEY,acronym text);
|
|
CREATE TABLE research_fixture.editions (id bigint PRIMARY KEY,venue_id bigint REFERENCES research_fixture.venues(id),year bigint,track text);
|
|
CREATE TABLE research_fixture.submissions (
|
|
id bigint PRIMARY KEY,paper_id bigint REFERENCES research_fixture.papers(id),edition_id bigint REFERENCES research_fixture.editions(id)
|
|
);
|
|
CREATE TABLE research_fixture.decisions (
|
|
id bigint PRIMARY KEY,submission_id bigint REFERENCES research_fixture.submissions(id),
|
|
status text,evidence_url text,observed_at text,decision_date text
|
|
);
|
|
CREATE INDEX decisions_submission_order ON research_fixture.decisions(submission_id,observed_at DESC,id DESC);
|
|
|
|
INSERT INTO research_fixture.papers VALUES
|
|
(1,'Graph & learning <original>','Neural graph models preserve original abstracts.','graph learning','[["Smith","Ada",""],"The Research Group"]','2024-01-02'),
|
|
(2,'Alpha topology',NULL,'alpha topology','["Bruno Park"]',NULL),
|
|
(3,'Graph systems','Graph systems and data.','graph systems','[["Network","Carla","","Fixture University"]]','2023-01-01'),
|
|
(4,'Zebra methods','Evidence and uncertainty.','zebra methods','[]','2022-01-01'),
|
|
(5,'Alpha Topology','Another independent record.','alpha topology','["Dana Other"]','2025-01-01');
|
|
INSERT INTO research_fixture.sources VALUES(10,'arxiv'),(20,'pmlr');
|
|
INSERT INTO research_fixture.source_records VALUES
|
|
(101,10,1,'fixture-1','https://example.test/papers/1','{"journal-ref":"Journal Fixture","comments":"Original comment"}'),
|
|
(202,20,3,'fixture-3','https://example.test/papers/3','{"doi":"10.fixture/other"}'),
|
|
(203,10,3,'fixture-3-arxiv','https://example.test/papers/3/arxiv','{}');
|
|
INSERT INTO research_fixture.identifiers VALUES('doi','10.fixture/canonical',1);
|
|
INSERT INTO research_fixture.tags VALUES(1,'arxiv:category','cs.LG'),(2,'arxiv:category','cs.AI'),(3,'arxiv:category','cs.DC'),(4,'different:namespace','cs.LG');
|
|
INSERT INTO research_fixture.paper_tags VALUES(1,1,'first'),(1,1,'second'),(1,2,'first'),(3,3,'first'),(2,4,'first');
|
|
INSERT INTO research_fixture.venues VALUES(1,'ICLR'),(2,'ICML');
|
|
INSERT INTO research_fixture.editions VALUES(7,1,2024,'main'),(8,2,2023,'main');
|
|
INSERT INTO research_fixture.submissions VALUES(11,1,7),(12,3,8);
|
|
INSERT INTO research_fixture.decisions VALUES
|
|
(1,11,'accepted','https://example.test/decision/1','2024-02-01','2024-01-30'),
|
|
(2,11,'rejected','https://example.test/decision/2','2024-02-01','2024-01-31'),
|
|
(3,11,'withdrawn','https://example.test/decision/3','2024-02-01','2024-01-31'),
|
|
(4,11,'accepted','https://example.test/decision/4','2024-01-01','2099-01-01');
|
|
|
|
CREATE ROLE foundry_research_fixture LOGIN;
|
|
REVOKE ALL ON SCHEMA research_fixture FROM PUBLIC;
|
|
GRANT USAGE ON SCHEMA research_fixture TO foundry_research_fixture;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA research_fixture TO foundry_research_fixture;
|
|
|
|
-- Separate encoded schema mirrors the published Paper Atlas nul-soh-v1 format.
|
|
CREATE SCHEMA research_encoded_fixture;
|
|
COMMENT ON SCHEMA research_encoded_fixture IS 'paper-atlas:text-codec=nul-soh-v1';
|
|
DO $$
|
|
DECLARE item text;
|
|
BEGIN
|
|
FOREACH item IN ARRAY ARRAY['papers','sources','source_records','identifiers','tags','paper_tags','venues','editions','submissions','decisions'] LOOP
|
|
EXECUTE format('CREATE TABLE research_encoded_fixture.%I (LIKE research_fixture.%I INCLUDING ALL)',item,item);
|
|
EXECUTE format('INSERT INTO research_encoded_fixture.%I SELECT * FROM research_fixture.%I',item,item);
|
|
END LOOP;
|
|
END $$;
|
|
UPDATE research_encoded_fixture.papers
|
|
SET title='Graph'||chr(1)||chr(1)||'original'||chr(1)||chr(2),
|
|
abstract='Text'||chr(1)||chr(1)||'tail',
|
|
authors_json='["Ada\u0000Smith","Group\u0001Name"]'
|
|
WHERE id=1;
|
|
UPDATE research_encoded_fixture.source_records
|
|
SET metadata_json='{"comments":"Original\u0000comment"}' WHERE id=101;
|
|
REVOKE ALL ON SCHEMA research_encoded_fixture FROM PUBLIC;
|
|
GRANT USAGE ON SCHEMA research_encoded_fixture TO foundry_research_fixture;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA research_encoded_fixture TO foundry_research_fixture;
|