Showing content from https://webtransparency.cs.princeton.edu/webcensus/data/schema.sql below:
/*** Define table schema ***/ /* Crawler Tables */ CREATE TABLE IF NOT EXISTS task ( task_id INTEGER PRIMARY KEY, start_time TIMESTAMPTZ DEFAULT (now() at time zone 'utc'), manager_params TEXT NOT NULL, openwpm_version TEXT NOT NULL, browser_version TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS crawl ( id INTEGER PRIMARY KEY, task_id SMALLINT, start_time TIMESTAMPTZ DEFAULT (now() at time zone 'utc'), browser_params TEXT NOT NULL, screen_res TEXT, ua_string TEXT, finished SMALLINT, FOREIGN KEY(task_id) REFERENCES task(task_id) ); CREATE TABLE IF NOT EXISTS crawl_history ( crawl_id SMALLINT, command TEXT, arguments TEXT, success SMALLINT, dtg TIMESTAMPTZ DEFAULT (now() at time zone 'utc'), FOREIGN KEY(crawl_id) REFERENCES crawl(id) ); CREATE TABLE IF NOT EXISTS site_visits ( id INTEGER PRIMARY KEY, crawl_id SMALLINT NOT NULL, top_url TEXT NOT NULL ); /* HTTP Data */ CREATE TABLE IF NOT EXISTS urls ( id INTEGER PRIMARY KEY, public_suffix TEXT, url TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS http_requests ( id INTEGER PRIMARY KEY, visit_id INTEGER NOT NULL, url_id INTEGER NOT NULL, method TEXT NOT NULL, referrer_id INTEGER, time_stamp TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS http_responses ( id INTEGER PRIMARY KEY, visit_id INTEGER NOT NULL, url_id INTEGER NOT NULL, method TEXT NOT NULL, referrer_id INTEGER, response_status SMALLINT NOT NULL, location_id INTEGER, time_stamp TEXT NOT NULL, content_hash TEXT ); /* HTTP Headers */ CREATE TABLE IF NOT EXISTS http_header_names ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS http_header_values ( id INTEGER PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS http_request_headers ( id INTEGER PRIMARY KEY, request_id INTEGER NOT NULL, name_id INTEGER NOT NULL, value_id INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS http_response_headers ( id INTEGER PRIMARY KEY, response_id INTEGER NOT NULL, name_id INTEGER NOT NULL, value_id INTEGER NOT NULL ); /* HTTP Cookies */ CREATE TABLE IF NOT EXISTS http_cookie_names ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS http_cookie_values ( id INTEGER PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS http_request_cookies ( id INTEGER PRIMARY KEY, request_id INTEGER NOT NULL, name_id INTEGER NOT NULL, value_id INTEGER NOT NULL, accessed TIMESTAMPTZ ); CREATE TABLE IF NOT EXISTS http_response_cookies ( id INTEGER PRIMARY KEY, response_id INTEGER NOT NULL, name_id INTEGER NOT NULL, value_id INTEGER NOT NULL, domain TEXT, path TEXT, expires TIMESTAMPTZ, max_age REAL, httponly BOOLEAN, secure BOOLEAN, comment TEXT, version TEXT, accessed TIMESTAMPTZ ); /* Javascript Instrumentation */ CREATE TABLE IF NOT EXISTS javascript_values ( id INTEGER PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS javascript_symbols ( id INTEGER PRIMARY KEY, symbol TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS javascript_parameters ( id INTEGER PRIMARY KEY, pindex INTEGER NOT NULL, pvalue TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS javascript ( id INTEGER PRIMARY KEY, visit_id INTEGER NOT NULL, url_id INTEGER, symbol_id INTEGER NOT NULL, operation TEXT NOT NULL, value_id INTEGER, parameter_id INTEGER ); /* Disk Instrumentation */ CREATE TABLE IF NOT EXISTS flash_cookie_content ( id INTEGER PRIMARY KEY, key TEXT NOT NULL, content TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS flash_cookie_path ( id INTEGER PRIMARY KEY, filename TEXT NOT NULL, local_path TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS flash_cookies ( id INTEGER PRIMARY KEY, visit_id INTEGER NOT NULL, domain TEXT NOT NULL, path_id INTEGER NOT NULL, content_id INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS javascript_cookie_values ( id INTEGER PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS javascript_cookie_names ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS javascript_cookies( id INTEGER PRIMARY KEY, visit_id INTEGER NOT NULL, host TEXT, raw_host TEXT, change TEXT, name_id INTEGER NOT NULL, value_id INTEGER NOT NULL, path TEXT, expires BIGINT, is_http_only BOOLEAN, is_session BOOLEAN, is_domain BOOLEAN, is_secure BOOLEAN, policy INTEGER, status INTEGER, creationTime TIMESTAMPTZ, expiry TIMESTAMPTZ, last_accessed TIMESTAMPTZ ); /*** Creates and validates all foreign keys ****/ /* HTTP Requests */ ALTER TABLE http_requests ADD CONSTRAINT requests_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id); ALTER TABLE http_requests ADD CONSTRAINT requests_urls_fk FOREIGN KEY(url_id) REFERENCES urls(id); ALTER TABLE http_requests ADD CONSTRAINT requests_refs_fk FOREIGN KEY(referrer_id) REFERENCES urls(id); ALTER TABLE http_requests VALIDATE CONSTRAINT requests_visit_fk; ALTER TABLE http_requests VALIDATE CONSTRAINT requests_urls_fk; ALTER TABLE http_requests VALIDATE CONSTRAINT requests_refs_fk; /* HTTP Responses */ ALTER TABLE http_responses ADD CONSTRAINT responses_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id); ALTER TABLE http_responses ADD CONSTRAINT responses_urls_fk FOREIGN KEY(url_id) REFERENCES urls(id); ALTER TABLE http_responses ADD CONSTRAINT responses_locs_fk FOREIGN KEY(location_id) REFERENCES urls(id); ALTER TABLE http_responses VALIDATE CONSTRAINT responses_visit_fk; ALTER TABLE http_responses VALIDATE CONSTRAINT responses_urls_fk; ALTER TABLE http_responses VALIDATE CONSTRAINT responses_locs_fk; /* HTTP Request Headers */ ALTER TABLE http_request_headers ADD CONSTRAINT reqh_req_fk FOREIGN KEY(request_id) REFERENCES http_requests(id); ALTER TABLE http_request_headers ADD CONSTRAINT reqh_name_fk FOREIGN KEY(name_id) REFERENCES http_header_names(id); ALTER TABLE http_request_headers ADD CONSTRAINT reqh_value_fk FOREIGN KEY(value_id) REFERENCES http_header_values(id); ALTER TABLE http_request_headers VALIDATE CONSTRAINT reqh_req_fk; ALTER TABLE http_request_headers VALIDATE CONSTRAINT reqh_name_fk; ALTER TABLE http_request_headers VALIDATE CONSTRAINT reqh_value_fk; /* HTTP Response Headers */ ALTER TABLE http_response_headers ADD CONSTRAINT resph_resp_fk FOREIGN KEY(response_id) REFERENCES http_responses(id); ALTER TABLE http_response_headers ADD CONSTRAINT resph_name_fk FOREIGN KEY(name_id) REFERENCES http_header_names(id); ALTER TABLE http_response_headers ADD CONSTRAINT resph_value_fk FOREIGN KEY(value_id) REFERENCES http_header_values(id); ALTER TABLE http_response_headers VALIDATE CONSTRAINT resph_resp_fk; ALTER TABLE http_response_headers VALIDATE CONSTRAINT resph_name_fk; ALTER TABLE http_response_headers VALIDATE CONSTRAINT resph_value_fk; /* HTTP Request Cookies */ ALTER TABLE http_request_cookies ADD CONSTRAINT reqc_header_fk FOREIGN KEY(request_id) REFERENCES http_requests(id); ALTER TABLE http_request_cookies ADD CONSTRAINT reqc_name_fk FOREIGN KEY(name_id) REFERENCES http_cookie_names(id); ALTER TABLE http_request_cookies ADD CONSTRAINT reqc_value_fk FOREIGN KEY(value_id) REFERENCES http_cookie_values(id); ALTER TABLE http_request_cookies VALIDATE CONSTRAINT reqc_header_fk; ALTER TABLE http_request_cookies VALIDATE CONSTRAINT reqc_name_fk; ALTER TABLE http_request_cookies VALIDATE CONSTRAINT reqc_value_fk; /* HTTP Response Cookies */ ALTER TABLE http_response_cookies ADD CONSTRAINT respc_header_fk FOREIGN KEY(response_id) REFERENCES http_requests(id); ALTER TABLE http_response_cookies ADD CONSTRAINT respc_name_fk FOREIGN KEY(name_id) REFERENCES http_cookie_names(id); ALTER TABLE http_response_cookies ADD CONSTRAINT respc_value_fk FOREIGN KEY(value_id) REFERENCES http_cookie_values(id); ALTER TABLE http_response_cookies VALIDATE CONSTRAINT respc_header_fk; ALTER TABLE http_response_cookies VALIDATE CONSTRAINT respc_name_fk; ALTER TABLE http_response_cookies VALIDATE CONSTRAINT respc_value_fk; /* Javascript */ ALTER TABLE javascript ADD CONSTRAINT javascript_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id); ALTER TABLE javascript ADD CONSTRAINT javascript_url_fk FOREIGN KEY(url_id) REFERENCES urls(id); ALTER TABLE javascript ADD CONSTRAINT javascript_symbol_fk FOREIGN KEY(symbol_id) REFERENCES javascript_symbols(id); ALTER TABLE javascript ADD CONSTRAINT javascript_value_fk FOREIGN KEY(value_id) REFERENCES javascript_values(id); ALTER TABLE javascript ADD CONSTRAINT javascript_parameter_fk FOREIGN KEY(parameter_id) REFERENCES javascript_parameters(id); ALTER TABLE javascript VALIDATE CONSTRAINT javascript_visit_fk; ALTER TABLE javascript VALIDATE CONSTRAINT javascript_url_fk; ALTER TABLE javascript VALIDATE CONSTRAINT javascript_symbol_fk; ALTER TABLE javascript VALIDATE CONSTRAINT javascript_value_fk; ALTER TABLE javascript VALIDATE CONSTRAINT javascript_parameter_fk; /* Flash Cookies */ ALTER TABLE flash_cookies ADD CONSTRAINT flash_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id); ALTER TABLE flash_cookies ADD CONSTRAINT flash_path_fk FOREIGN KEY(path_id) REFERENCES flash_cookie_path(id); ALTER TABLE flash_cookies ADD CONSTRAINT flash_content_fk FOREIGN KEY(content_id) REFERENCES flash_cookie_content(id); ALTER TABLE flash_cookies VALIDATE CONSTRAINT flash_visit_fk; ALTER TABLE flash_cookies VALIDATE CONSTRAINT flash_path_fk; ALTER TABLE flash_cookies VALIDATE CONSTRAINT flash_content_fk; /* Javascript Cookies */ ALTER TABLE javascript_cookies ADD CONSTRAINT jscookies_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id); ALTER TABLE javascript_cookies ADD CONSTRAINT jscookies_name_fk FOREIGN KEY(name_id) REFERENCES javascript_cookie_names(id); ALTER TABLE javascript_cookies ADD CONSTRAINT jscookies_value_fk FOREIGN KEY(value_id) REFERENCES javascript_cookie_values(id); ALTER TABLE javascript_cookies VALIDATE CONSTRAINT jscookies_visit_fk; ALTER TABLE javascript_cookies VALIDATE CONSTRAINT jscookies_name_fk; ALTER TABLE javascript_cookies VALIDATE CONSTRAINT jscookies_value_fk; /*** Creates indexes on all necessary columns ***/ /* HTTP Requests */ CREATE INDEX requests_visit_id ON http_requests(visit_id); CREATE INDEX requests_url_id ON http_requests(url_id); CREATE INDEX requests_referrer_id ON http_requests(referrer_id); /* HTTP Request Headers */ CREATE INDEX request_headers_request_id ON http_request_headers(request_id); CREATE INDEX request_headers_name_id ON http_request_headers(name_id); CREATE INDEX request_headers_value_id ON http_request_headers(value_id); /* HTTP Request Cookies */ CREATE INDEX request_cookies_header_id ON http_request_cookies(request_id); CREATE INDEX request_cookies_name_id ON http_request_cookies(name_id); CREATE INDEX request_cookies_value_id ON http_request_cookies(value_id); /* HTTP Responses */ CREATE INDEX responses_visit_id ON http_responses(visit_id); CREATE INDEX responses_url_id ON http_responses(url_id); CREATE INDEX responses_referrer_id ON http_responses(referrer_id); CREATE INDEX responses_location_id ON http_responses(location_id); /* HTTP Response Headers */ CREATE INDEX response_headers_request_id ON http_response_headers(response_id); CREATE INDEX response_headers_name_id ON http_response_headers(name_id); CREATE INDEX response_headers_value_id ON http_response_headers(value_id); /* HTTP Response Cookies */ CREATE INDEX response_cookies_header_id ON http_response_cookies(response_id); CREATE INDEX response_cookies_name_id ON http_response_cookies(name_id); CREATE INDEX response_cookies_value_id ON http_response_cookies(value_id); /* Javascript */ CREATE INDEX javascript_visit_id ON javascript(visit_id); CREATE INDEX javascript_url_id ON javascript(url_id); CREATE INDEX javascript_symbol_id ON javascript(symbol_id); CREATE INDEX javascript_value_id ON javascript(value_id); CREATE INDEX javascript_parameter_id ON javascript(parameter_id); /* Javascript Cookies */ CREATE INDEX flash_cookies_visit_id ON flash_cookies(visit_id); CREATE INDEX flash_cookies_path_id ON flash_cookies(path_id); CREATE INDEX flash_cookies_content_id ON flash_cookies(content_id); /* Flash Cookies */ CREATE INDEX javascript_cookies_visit_id ON javascript_cookies(visit_id); CREATE INDEX javascript_cookies_name_id ON javascript_cookies(name_id); CREATE INDEX javascript_cookies_value_id ON javascript_cookies(value_id); /*** Create views to make tables easier to query Note: These views should be similar to OpenWPM's sqlite schema but may not always match exactly. ***/ CREATE OR REPLACE VIEW javascript_view (id, top_url, script_url, symbol, value, pindex, pvalue) AS SELECT j.id, sv.top_url, u.url, s.symbol, v.value, p.pindex, p.pvalue FROM javascript AS j LEFT JOIN javascript_values as v ON v.id = j.value_id LEFT JOIN javascript_symbols as s ON s.id = j.symbol_id LEFT JOIN javascript_parameters as p ON p.id = j.parameter_id LEFT JOIN site_visits as sv ON sv.id = j.visit_id LEFT JOIN urls as u ON u.id = j.url_id; CREATE OR REPLACE VIEW http_requests_view (id, top_url, url, method, referrer, time_stamp) AS SELECT r.id, sv.top_url, u1.url, r.method, u2.url, r.time_stamp FROM http_requests as r LEFT JOIN urls as u1 ON r.url_id = u1.id LEFT JOIN urls as u2 ON r.referrer_id = u2.id LEFT JOIN site_visits as sv ON r.visit_id = sv.id; CREATE OR REPLACE VIEW http_request_headers_view (id, request_id, name, value) AS SELECT h.id, h.request_id, n.name, v.value FROM http_request_headers as h LEFT JOIN http_header_names as n ON h.name_id = n.id LEFT JOIN http_header_values as v ON h.value_id = v.id; CREATE OR REPLACE VIEW http_responses_view (id, top_url, url, method, referrer, response_status, location, time_stamp, content_hash) AS SELECT r.id, sv.top_url, u1.url, r.method, u2.url, r.response_status, u3.url, r.time_stamp, r.content_hash FROM http_responses as r LEFT JOIN urls as u1 ON r.url_id = u1.id LEFT JOIN urls as u2 ON r.referrer_id = u2.id LEFT JOIN urls as u3 ON r.location_id = u3.id LEFT JOIN site_visits as sv ON r.visit_id = sv.id; CREATE OR REPLACE VIEW http_response_headers_view (id, response_id, name, value) AS SELECT h.id, h.response_id, n.name, v.value FROM http_response_headers as h LEFT JOIN http_header_names as n ON h.name_id = n.id LEFT JOIN http_header_values as v ON h.value_id = v.id; CREATE OR REPLACE VIEW http_request_cookies_view (id, request_id, name, value, accessed) AS SELECT rc.id, rc.request_id, n.name, v.value, rc.accessed FROM http_request_cookies AS rc LEFT JOIN http_cookie_names AS n ON n.id = rc.name_id LEFT JOIN http_cookie_values AS v ON v.id = rc.value_id; CREATE OR REPLACE VIEW http_response_cookies_view (id, response_id, name, value, domain, path, expires, max_age, httponly, secure, comment, version, accessed) AS SELECT rc.id, rc.response_id, n.name, v.value, rc.domain, rc.path, rc.expires, rc.max_age, rc.httponly, rc.secure, rc.comment, rc.version, rc.accessed FROM http_response_cookies AS rc LEFT JOIN http_cookie_names AS n ON n.id = rc.name_id LEFT JOIN http_cookie_values AS v ON v.id = rc.value_id;
RetroSearch is an open source project built by @garambo
| Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.3