Query cookbook¶
Open the database with sqlite3 db/premal.sqlite (or any SQLite client) and try:
-- Papers per collection
SELECT collection, COUNT(DISTINCT paper_id) FROM files WHERE missing = 0 GROUP BY collection;
-- Systems that run on a GPU and report a latency
SELECT name, year, hardware, latency FROM systems WHERE hardware LIKE '%GPU%' AND latency IS NOT NULL ORDER BY year;
-- Papers that evaluate on MNIST according to the extracted details
SELECT p.key, p.year, p.title FROM papers p JOIN paper_details d ON d.paper_id = p.id
WHERE d.field = 'datasets' AND d.value LIKE '%MNIST%' ORDER BY p.year;
-- Papers mentioning TFHE at least 10 times
SELECT key, count FROM v_mentions WHERE category = 'scheme' AND term = 'TFHE' AND count >= 10 ORDER BY count DESC;
-- Everything known about one paper
SELECT field, value, source FROM paper_details WHERE paper_id = (SELECT id FROM papers WHERE key = 'thor-secure-transformer-inference-with-homomorphic-encryption');
-- Papers without a local PDF
SELECT key, title, url FROM v_papers WHERE n_files = 0;
-- Follow-up papers per scheme
SELECT s.name, p.title FROM scheme_followups f JOIN schemes s ON s.id = f.scheme_id JOIN papers p ON p.id = f.paper_id ORDER BY s.id;
-- Most cited papers (Semantic Scholar counts)
SELECT citation_count, year, title FROM papers WHERE citation_count IS NOT NULL ORDER BY citation_count DESC LIMIT 20;
-- Federated-learning papers in the PPML collection
SELECT p.key, p.year, p.title FROM files f JOIN papers p ON p.id = f.paper_id WHERE f.topic = 'federated-learning' GROUP BY p.id;
Full-text search uses the second database:
ATTACH 'db/fulltext.sqlite' AS ft;
SELECT p.key, pg.page_no, snippet(ft.pages_fts, 0, '[', ']', '…', 10)
FROM ft.pages_fts JOIN ft.pages pg ON pg.id = ft.pages_fts.rowid
JOIN files f ON f.sha256 = pg.sha256 JOIN papers p ON p.id = f.paper_id
WHERE ft.pages_fts MATCH 'noise flooding' GROUP BY p.id ORDER BY rank LIMIT 20;
From Python:
import sqlite3, pandas as pd
con = sqlite3.connect('db/premal.sqlite')
df = pd.read_sql('SELECT * FROM v_papers', con)