| -- This is a useful file for playing around with SQL queries against a database |
| -- populated with Perf data. You can use this file by running: |
| -- |
| -- sqlite3 test.db < test.sql |
| -- |
| -- Where test.db is an empty sqlite3 database. You should be able to run this |
| -- file against the same sqlite3 database more than once w/o error. |
| CREATE TABLE IF NOT EXISTS TraceIDs ( |
| trace_id INTEGER PRIMARY KEY, |
| trace_name TEXT UNIQUE NOT NULL |
| ); |
| |
| CREATE TABLE IF NOT EXISTS Postings ( |
| tile_number INTEGER, |
| key_value text NOT NULL, |
| trace_id INTEGER, |
| PRIMARY KEY (tile_number, key_value, trace_id) |
| ); |
| |
| CREATE TABLE IF NOT EXISTS SourceFiles ( |
| source_file_id INTEGER PRIMARY KEY, |
| source_file TEXT UNIQUE NOT NULL |
| ); |
| |
| CREATE TABLE IF NOT EXISTS TraceValues ( |
| trace_id INTEGER, |
| commit_number INTEGER, |
| val REAL, |
| source_file_id INTEGER, |
| PRIMARY KEY (trace_id, commit_number) |
| ); |
| |
| CREATE TABLE IF NOT EXISTS Commits ( |
| commit_number INTEGER PRIMARY KEY, |
| git_hash TEXT UNIQUE NOT NULL, |
| commit_time INTEGER, -- And not author_time. |
| author TEXT, -- Name <email> |
| subject TEXT |
| ); |
| |
| INSERT OR IGNORE INTO Commits (commit_number, git_hash, commit_time, author, subject) |
| VALUES |
| (0, "586101c79b0490b50623e76c71a5fd67d8d92b08", 1158764756, "unknown@example.com", "initial directory structure"), |
| (1, "0f87cd842dd46205d5252c35da6d2c869f3d2e98", 1158767262, "unknown@example.com", "initial code checkin"), |
| (2, "48ede9b432a3c3d62835a1400a9ed347b4a93024", 1163013888, "unknown@example.org", "Add LICENSE"); |
| |
| -- Get most recent git hash. |
| SELECT git_hash FROM Commits |
| ORDER BY commit_number DESC |
| LIMIT 1; |
| |
| -- Get commit_number from git hash. |
| SELECT commit_number FROM Commits |
| WHERE git_hash='0f87cd842dd46205d5252c35da6d2c869f3d2e98'; |
| |
| -- Get commit_number from time. |
| SELECT commit_number FROM Commits |
| WHERE commit_time <= 1163013888 |
| ORDER BY commit_number DESC |
| LIMIT 1; |
| |
| INSERT OR IGNORE INTO SourceFiles (source_file) |
| VALUES |
| ("gs://perf-bucket/2020/02/08/11/testdata.json"), |
| ("gs://perf-bucket/2020/02/08/12/testdata.json"), |
| ("gs://perf-bucket/2020/02/08/13/testdata.json"), |
| ("gs://perf-bucket/2020/02/08/14/testdata.json"); |
| |
| INSERT OR IGNORE INTO TraceIDs (trace_name) |
| VALUES |
| (",arch=x86,config=8888,"), |
| (",arch=x86,config=565,"), |
| (",arch=arm,config=8888,"), |
| (",arch=arm,config=565,"); |
| |
| SELECT trace_id, trace_name FROM TraceIDs; |
| |
| INSERT OR REPLACE INTO TraceValues (trace_id, commit_number, val, source_file_id) |
| VALUES |
| (1, 1, 1.2, 1), |
| (1, 2, 1.3, 2), |
| (1, 3, 1.4, 3), |
| (1, 256, 1.1, 4), |
| (2, 1, 2.2, 1), |
| (2, 2, 2.3, 2), |
| (2, 3, 2.4, 3), |
| (2, 256, 2.1, 4); |
| |
| INSERT OR REPLACE INTO Postings (tile_number, key_value, trace_id) |
| VALUES |
| (2, "config=565", 4), |
| (0, "arch=x86", 1), |
| (0, "arch=x86", 2), |
| (0, "arch=arm", 3), |
| (0, "arch=arm", 4), |
| (0, "config=8888", 1), |
| (0, "config=8888", 3), |
| (0, "config=565", 2), |
| (0, "config=565", 4); |
| |
| -- All trace_ids that match a particular key=value. |
| SELECT tile_number, key_value, trace_id FROM Postings |
| WHERE tile_number=0 AND key_value="arch=x86" |
| ORDER BY trace_id; |
| |
| -- Retrieve matching values. Note that sqlite querys are limited to 1MB, |
| -- so we might need to break up the trace_ids if the query is too long. |
| SELECT trace_id, commit_number, val FROM TraceValues |
| WHERE commit_number>=0 AND commit_number<255 AND trace_id IN (1,2); |
| |
| -- Build traces using a JOIN. |
| SELECT TraceIDs.trace_name, TraceValues.commit_number, TraceValues.val FROM TraceIDs |
| INNER JOIN TraceValues ON TraceValues.trace_id = TraceIDs.trace_id |
| WHERE TraceIDs.trace_name=",arch=x86,config=8888," OR TraceIDs.trace_name=",arch=x86,config=565,"; |
| |
| -- Retrieve source file. |
| SELECT source_file_id, source_file from SourceFiles |
| WHERE source_file_id=1; |
| |
| SELECT DISTINCT key_value FROM Postings |
| WHERE tile_number=0; |
| |
| -- Most recent tile. |
| SELECT tile_number FROM Postings ORDER BY tile_number DESC LIMIT 1; |
| |
| -- Count indices for Tile. |
| SELECT COUNT(*) FROM Postings WHERE tile_number=0; |
| |
| -- GetSource by trace name. |
| SELECT SourceFiles.source_file FROM TraceIDs |
| INNER JOIN TraceValues ON TraceValues.trace_id = TraceIDs.trace_id |
| INNER JOIN SourceFiles ON SourceFiles.source_file_id = TraceValues.source_file_id |
| WHERE TraceIDs.trace_name=",arch=x86,config=8888," AND TraceValues.commit_number=256; |
| |
| -- Fully query traces from tile based on query plan. |
| SELECT TraceIDs.trace_name, TraceValues.commit_number, TraceValues.val FROM TraceIDs |
| INNER JOIN TraceValues ON TraceValues.trace_id = TraceIDs.trace_id |
| WHERE |
| TraceValues.trace_id IN ( |
| SELECT trace_id FROM Postings WHERE key_value IN ("arch=x86", "arch=arm") |
| AND tile_number=0 |
| ) |
| AND TraceValues.trace_id IN ( |
| SELECT trace_id FROM Postings WHERE key_value IN ("config=8888") |
| AND tile_number=0 |
| ); |
| |
| -- Count the number traces that are in a single tile. |
| SELECT COUNT(DISTINCT trace_id) FROM TraceValues |
| WHERE |
| commit_number > 0 |
| AND commit_number < 8; |