-- 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:
--
--   cockroach sql --insecure --host=localhost < ./migrations/cdb.sql
--
-- Make sure to apply the migrations the first time:
--
--   cockroach sql --insecure --host=localhost < ./migrations/cockroachdb/0001_create_initial_tables.up.sql
--
-- You should be able to run this file against the same database more than once
-- w/o error.

UPSERT 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 INTO SourceFiles (source_file, source_file_id)
VALUES
  ('gs://perf-bucket/2020/02/08/11/testdata.json',1),
  ('gs://perf-bucket/2020/02/08/12/testdata.json',2),
  ('gs://perf-bucket/2020/02/08/13/testdata.json',3),
  ('gs://perf-bucket/2020/02/08/14/testdata.json',4)
ON CONFLICT
DO NOTHING;

-- INSERT ON CONFLICT RETURNING doesn't work as needed
-- because the below query doesnt' return anything because it
-- stops at DO NOTHING.
--
-- INSERT INTO SourceFiles (source_file)
-- VALUES
--   ('gs://perf-bucket/2020/02/08/11/testdata.json')
-- ON CONFLICT
-- DO NOTHING
-- RETURNING source_file_id;

INSERT INTO TraceIDs (trace_name, trace_id)
VALUES
 (',arch=x86,config=8888,', 1),
 (',arch=x86,config=565,', 2),
 (',arch=arm,config=8888,',3),
 (',arch=arm,config=565,', 4)
ON CONFLICT
DO NOTHING;

SELECT trace_id, trace_name FROM TraceIDs;

UPSERT 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);

UPSERT 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;