1# export-to-sqlite.py: export perf data to a sqlite3 database
2# Copyright (c) 2017, Intel Corporation.
3#
4# This program is free software; you can redistribute it and/or modify it
5# under the terms and conditions of the GNU General Public License,
6# version 2, as published by the Free Software Foundation.
7#
8# This program is distributed in the hope it will be useful, but WITHOUT
9# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
10# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
11# more details.
12
13from __future__ import print_function
14
15import os
16import sys
17import struct
18import datetime
19
20# To use this script you will need to have installed package python-pyside which
21# provides LGPL-licensed Python bindings for Qt.  You will also need the package
22# libqt4-sql-sqlite for Qt sqlite3 support.
23#
24# An example of using this script with Intel PT:
25#
26#	$ perf record -e intel_pt//u ls
27#	$ perf script -s ~/libexec/perf-core/scripts/python/export-to-sqlite.py pt_example branches calls
28#	2017-07-31 14:26:07.326913 Creating database...
29#	2017-07-31 14:26:07.538097 Writing records...
30#	2017-07-31 14:26:09.889292 Adding indexes
31#	2017-07-31 14:26:09.958746 Done
32#
33# To browse the database, sqlite3 can be used e.g.
34#
35#	$ sqlite3 pt_example
36#	sqlite> .header on
37#	sqlite> select * from samples_view where id < 10;
38#	sqlite> .mode column
39#	sqlite> select * from samples_view where id < 10;
40#	sqlite> .tables
41#	sqlite> .schema samples_view
42#	sqlite> .quit
43#
44# An example of using the database is provided by the script
45# exported-sql-viewer.py.  Refer to that script for details.
46#
47# The database structure is practically the same as created by the script
48# export-to-postgresql.py. Refer to that script for details.  A notable
49# difference is  the 'transaction' column of the 'samples' table which is
50# renamed 'transaction_' in sqlite because 'transaction' is a reserved word.
51
52from PySide.QtSql import *
53
54sys.path.append(os.environ['PERF_EXEC_PATH'] + \
55	'/scripts/python/Perf-Trace-Util/lib/Perf/Trace')
56
57# These perf imports are not used at present
58#from perf_trace_context import *
59#from Core import *
60
61perf_db_export_mode = True
62perf_db_export_calls = False
63perf_db_export_callchains = False
64
65def printerr(*args, **keyword_args):
66	print(*args, file=sys.stderr, **keyword_args)
67
68def printdate(*args, **kw_args):
69        print(datetime.datetime.today(), *args, sep=' ', **kw_args)
70
71def usage():
72	printerr("Usage is: export-to-sqlite.py <database name> [<columns>] [<calls>] [<callchains>]");
73	printerr("where:	columns		'all' or 'branches'");
74	printerr("		calls		'calls' => create calls and call_paths table");
75	printerr("		callchains	'callchains' => create call_paths table");
76	raise Exception("Too few arguments")
77
78if (len(sys.argv) < 2):
79	usage()
80
81dbname = sys.argv[1]
82
83if (len(sys.argv) >= 3):
84	columns = sys.argv[2]
85else:
86	columns = "all"
87
88if columns not in ("all", "branches"):
89	usage()
90
91branches = (columns == "branches")
92
93for i in range(3,len(sys.argv)):
94	if (sys.argv[i] == "calls"):
95		perf_db_export_calls = True
96	elif (sys.argv[i] == "callchains"):
97		perf_db_export_callchains = True
98	else:
99		usage()
100
101def do_query(q, s):
102	if (q.exec_(s)):
103		return
104	raise Exception("Query failed: " + q.lastError().text())
105
106def do_query_(q):
107	if (q.exec_()):
108		return
109	raise Exception("Query failed: " + q.lastError().text())
110
111printdate("Creating database ...")
112
113db_exists = False
114try:
115	f = open(dbname)
116	f.close()
117	db_exists = True
118except:
119	pass
120
121if db_exists:
122	raise Exception(dbname + " already exists")
123
124db = QSqlDatabase.addDatabase('QSQLITE')
125db.setDatabaseName(dbname)
126db.open()
127
128query = QSqlQuery(db)
129
130do_query(query, 'PRAGMA journal_mode = OFF')
131do_query(query, 'BEGIN TRANSACTION')
132
133do_query(query, 'CREATE TABLE selected_events ('
134		'id		integer		NOT NULL	PRIMARY KEY,'
135		'name		varchar(80))')
136do_query(query, 'CREATE TABLE machines ('
137		'id		integer		NOT NULL	PRIMARY KEY,'
138		'pid		integer,'
139		'root_dir 	varchar(4096))')
140do_query(query, 'CREATE TABLE threads ('
141		'id		integer		NOT NULL	PRIMARY KEY,'
142		'machine_id	bigint,'
143		'process_id	bigint,'
144		'pid		integer,'
145		'tid		integer)')
146do_query(query, 'CREATE TABLE comms ('
147		'id		integer		NOT NULL	PRIMARY KEY,'
148		'comm		varchar(16))')
149do_query(query, 'CREATE TABLE comm_threads ('
150		'id		integer		NOT NULL	PRIMARY KEY,'
151		'comm_id	bigint,'
152		'thread_id	bigint)')
153do_query(query, 'CREATE TABLE dsos ('
154		'id		integer		NOT NULL	PRIMARY KEY,'
155		'machine_id	bigint,'
156		'short_name	varchar(256),'
157		'long_name	varchar(4096),'
158		'build_id	varchar(64))')
159do_query(query, 'CREATE TABLE symbols ('
160		'id		integer		NOT NULL	PRIMARY KEY,'
161		'dso_id		bigint,'
162		'sym_start	bigint,'
163		'sym_end	bigint,'
164		'binding	integer,'
165		'name		varchar(2048))')
166do_query(query, 'CREATE TABLE branch_types ('
167		'id		integer		NOT NULL	PRIMARY KEY,'
168		'name		varchar(80))')
169
170if branches:
171	do_query(query, 'CREATE TABLE samples ('
172		'id		integer		NOT NULL	PRIMARY KEY,'
173		'evsel_id	bigint,'
174		'machine_id	bigint,'
175		'thread_id	bigint,'
176		'comm_id	bigint,'
177		'dso_id		bigint,'
178		'symbol_id	bigint,'
179		'sym_offset	bigint,'
180		'ip		bigint,'
181		'time		bigint,'
182		'cpu		integer,'
183		'to_dso_id	bigint,'
184		'to_symbol_id	bigint,'
185		'to_sym_offset	bigint,'
186		'to_ip		bigint,'
187		'branch_type	integer,'
188		'in_tx		boolean,'
189		'call_path_id	bigint)')
190else:
191	do_query(query, 'CREATE TABLE samples ('
192		'id		integer		NOT NULL	PRIMARY KEY,'
193		'evsel_id	bigint,'
194		'machine_id	bigint,'
195		'thread_id	bigint,'
196		'comm_id	bigint,'
197		'dso_id		bigint,'
198		'symbol_id	bigint,'
199		'sym_offset	bigint,'
200		'ip		bigint,'
201		'time		bigint,'
202		'cpu		integer,'
203		'to_dso_id	bigint,'
204		'to_symbol_id	bigint,'
205		'to_sym_offset	bigint,'
206		'to_ip		bigint,'
207		'period		bigint,'
208		'weight		bigint,'
209		'transaction_	bigint,'
210		'data_src	bigint,'
211		'branch_type	integer,'
212		'in_tx		boolean,'
213		'call_path_id	bigint)')
214
215if perf_db_export_calls or perf_db_export_callchains:
216	do_query(query, 'CREATE TABLE call_paths ('
217		'id		integer		NOT NULL	PRIMARY KEY,'
218		'parent_id	bigint,'
219		'symbol_id	bigint,'
220		'ip		bigint)')
221if perf_db_export_calls:
222	do_query(query, 'CREATE TABLE calls ('
223		'id		integer		NOT NULL	PRIMARY KEY,'
224		'thread_id	bigint,'
225		'comm_id	bigint,'
226		'call_path_id	bigint,'
227		'call_time	bigint,'
228		'return_time	bigint,'
229		'branch_count	bigint,'
230		'call_id	bigint,'
231		'return_id	bigint,'
232		'parent_call_path_id	bigint,'
233		'flags		integer,'
234		'parent_id	bigint)')
235
236# printf was added to sqlite in version 3.8.3
237sqlite_has_printf = False
238try:
239	do_query(query, 'SELECT printf("") FROM machines')
240	sqlite_has_printf = True
241except:
242	pass
243
244def emit_to_hex(x):
245	if sqlite_has_printf:
246		return 'printf("%x", ' + x + ')'
247	else:
248		return x
249
250do_query(query, 'CREATE VIEW machines_view AS '
251	'SELECT '
252		'id,'
253		'pid,'
254		'root_dir,'
255		'CASE WHEN id=0 THEN \'unknown\' WHEN pid=-1 THEN \'host\' ELSE \'guest\' END AS host_or_guest'
256	' FROM machines')
257
258do_query(query, 'CREATE VIEW dsos_view AS '
259	'SELECT '
260		'id,'
261		'machine_id,'
262		'(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
263		'short_name,'
264		'long_name,'
265		'build_id'
266	' FROM dsos')
267
268do_query(query, 'CREATE VIEW symbols_view AS '
269	'SELECT '
270		'id,'
271		'name,'
272		'(SELECT short_name FROM dsos WHERE id=dso_id) AS dso,'
273		'dso_id,'
274		'sym_start,'
275		'sym_end,'
276		'CASE WHEN binding=0 THEN \'local\' WHEN binding=1 THEN \'global\' ELSE \'weak\' END AS binding'
277	' FROM symbols')
278
279do_query(query, 'CREATE VIEW threads_view AS '
280	'SELECT '
281		'id,'
282		'machine_id,'
283		'(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
284		'process_id,'
285		'pid,'
286		'tid'
287	' FROM threads')
288
289do_query(query, 'CREATE VIEW comm_threads_view AS '
290	'SELECT '
291		'comm_id,'
292		'(SELECT comm FROM comms WHERE id = comm_id) AS command,'
293		'thread_id,'
294		'(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
295		'(SELECT tid FROM threads WHERE id = thread_id) AS tid'
296	' FROM comm_threads')
297
298if perf_db_export_calls or perf_db_export_callchains:
299	do_query(query, 'CREATE VIEW call_paths_view AS '
300		'SELECT '
301			'c.id,'
302			+ emit_to_hex('c.ip') + ' AS ip,'
303			'c.symbol_id,'
304			'(SELECT name FROM symbols WHERE id = c.symbol_id) AS symbol,'
305			'(SELECT dso_id FROM symbols WHERE id = c.symbol_id) AS dso_id,'
306			'(SELECT dso FROM symbols_view  WHERE id = c.symbol_id) AS dso_short_name,'
307			'c.parent_id,'
308			+ emit_to_hex('p.ip') + ' AS parent_ip,'
309			'p.symbol_id AS parent_symbol_id,'
310			'(SELECT name FROM symbols WHERE id = p.symbol_id) AS parent_symbol,'
311			'(SELECT dso_id FROM symbols WHERE id = p.symbol_id) AS parent_dso_id,'
312			'(SELECT dso FROM symbols_view  WHERE id = p.symbol_id) AS parent_dso_short_name'
313		' FROM call_paths c INNER JOIN call_paths p ON p.id = c.parent_id')
314if perf_db_export_calls:
315	do_query(query, 'CREATE VIEW calls_view AS '
316		'SELECT '
317			'calls.id,'
318			'thread_id,'
319			'(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
320			'(SELECT tid FROM threads WHERE id = thread_id) AS tid,'
321			'(SELECT comm FROM comms WHERE id = comm_id) AS command,'
322			'call_path_id,'
323			+ emit_to_hex('ip') + ' AS ip,'
324			'symbol_id,'
325			'(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
326			'call_time,'
327			'return_time,'
328			'return_time - call_time AS elapsed_time,'
329			'branch_count,'
330			'call_id,'
331			'return_id,'
332			'CASE WHEN flags=0 THEN \'\' WHEN flags=1 THEN \'no call\' WHEN flags=2 THEN \'no return\' WHEN flags=3 THEN \'no call/return\' WHEN flags=6 THEN \'jump\' ELSE flags END AS flags,'
333			'parent_call_path_id,'
334			'calls.parent_id'
335		' FROM calls INNER JOIN call_paths ON call_paths.id = call_path_id')
336
337do_query(query, 'CREATE VIEW samples_view AS '
338	'SELECT '
339		'id,'
340		'time,'
341		'cpu,'
342		'(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
343		'(SELECT tid FROM threads WHERE id = thread_id) AS tid,'
344		'(SELECT comm FROM comms WHERE id = comm_id) AS command,'
345		'(SELECT name FROM selected_events WHERE id = evsel_id) AS event,'
346		+ emit_to_hex('ip') + ' AS ip_hex,'
347		'(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
348		'sym_offset,'
349		'(SELECT short_name FROM dsos WHERE id = dso_id) AS dso_short_name,'
350		+ emit_to_hex('to_ip') + ' AS to_ip_hex,'
351		'(SELECT name FROM symbols WHERE id = to_symbol_id) AS to_symbol,'
352		'to_sym_offset,'
353		'(SELECT short_name FROM dsos WHERE id = to_dso_id) AS to_dso_short_name,'
354		'(SELECT name FROM branch_types WHERE id = branch_type) AS branch_type_name,'
355		'in_tx'
356	' FROM samples')
357
358do_query(query, 'END TRANSACTION')
359
360evsel_query = QSqlQuery(db)
361evsel_query.prepare("INSERT INTO selected_events VALUES (?, ?)")
362machine_query = QSqlQuery(db)
363machine_query.prepare("INSERT INTO machines VALUES (?, ?, ?)")
364thread_query = QSqlQuery(db)
365thread_query.prepare("INSERT INTO threads VALUES (?, ?, ?, ?, ?)")
366comm_query = QSqlQuery(db)
367comm_query.prepare("INSERT INTO comms VALUES (?, ?)")
368comm_thread_query = QSqlQuery(db)
369comm_thread_query.prepare("INSERT INTO comm_threads VALUES (?, ?, ?)")
370dso_query = QSqlQuery(db)
371dso_query.prepare("INSERT INTO dsos VALUES (?, ?, ?, ?, ?)")
372symbol_query = QSqlQuery(db)
373symbol_query.prepare("INSERT INTO symbols VALUES (?, ?, ?, ?, ?, ?)")
374branch_type_query = QSqlQuery(db)
375branch_type_query.prepare("INSERT INTO branch_types VALUES (?, ?)")
376sample_query = QSqlQuery(db)
377if branches:
378	sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
379else:
380	sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
381if perf_db_export_calls or perf_db_export_callchains:
382	call_path_query = QSqlQuery(db)
383	call_path_query.prepare("INSERT INTO call_paths VALUES (?, ?, ?, ?)")
384if perf_db_export_calls:
385	call_query = QSqlQuery(db)
386	call_query.prepare("INSERT INTO calls VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
387
388def trace_begin():
389	printdate("Writing records...")
390	do_query(query, 'BEGIN TRANSACTION')
391	# id == 0 means unknown.  It is easier to create records for them than replace the zeroes with NULLs
392	evsel_table(0, "unknown")
393	machine_table(0, 0, "unknown")
394	thread_table(0, 0, 0, -1, -1)
395	comm_table(0, "unknown")
396	dso_table(0, 0, "unknown", "unknown", "")
397	symbol_table(0, 0, 0, 0, 0, "unknown")
398	sample_table(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
399	if perf_db_export_calls or perf_db_export_callchains:
400		call_path_table(0, 0, 0, 0)
401		call_return_table(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
402
403unhandled_count = 0
404
405def trace_end():
406	do_query(query, 'END TRANSACTION')
407
408	printdate("Adding indexes")
409	if perf_db_export_calls:
410		do_query(query, 'CREATE INDEX pcpid_idx ON calls (parent_call_path_id)')
411		do_query(query, 'CREATE INDEX pid_idx ON calls (parent_id)')
412
413	if (unhandled_count):
414		printdate("Warning: ", unhandled_count, " unhandled events")
415	printdate("Done")
416
417def trace_unhandled(event_name, context, event_fields_dict):
418	global unhandled_count
419	unhandled_count += 1
420
421def sched__sched_switch(*x):
422	pass
423
424def bind_exec(q, n, x):
425	for xx in x[0:n]:
426		q.addBindValue(str(xx))
427	do_query_(q)
428
429def evsel_table(*x):
430	bind_exec(evsel_query, 2, x)
431
432def machine_table(*x):
433	bind_exec(machine_query, 3, x)
434
435def thread_table(*x):
436	bind_exec(thread_query, 5, x)
437
438def comm_table(*x):
439	bind_exec(comm_query, 2, x)
440
441def comm_thread_table(*x):
442	bind_exec(comm_thread_query, 3, x)
443
444def dso_table(*x):
445	bind_exec(dso_query, 5, x)
446
447def symbol_table(*x):
448	bind_exec(symbol_query, 6, x)
449
450def branch_type_table(*x):
451	bind_exec(branch_type_query, 2, x)
452
453def sample_table(*x):
454	if branches:
455		for xx in x[0:15]:
456			sample_query.addBindValue(str(xx))
457		for xx in x[19:22]:
458			sample_query.addBindValue(str(xx))
459		do_query_(sample_query)
460	else:
461		bind_exec(sample_query, 22, x)
462
463def call_path_table(*x):
464	bind_exec(call_path_query, 4, x)
465
466def call_return_table(*x):
467	bind_exec(call_query, 12, x)
468