1 # export-to-sqlite.py: export perf data to a sqlite3 database
2 # Copyright (c) 2017, Intel Corporation.
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.
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
13 from __future__ import print_function
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.
24 # An example of using this script with Intel PT:
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
33 # To browse the database, sqlite3 can be used e.g.
35 # $ sqlite3 pt_example
37 # sqlite> select * from samples_view where id < 10;
38 # sqlite> .mode column
39 # sqlite> select * from samples_view where id < 10;
41 # sqlite> .schema samples_view
44 # An example of using the database is provided by the script
45 # exported-sql-viewer.py. Refer to that script for details.
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.
52 from PySide.QtSql import *
54 sys.path.append(os.environ['PERF_EXEC_PATH'] + \
55 '/scripts/python/Perf-Trace-Util/lib/Perf/Trace')
57 # These perf imports are not used at present
58 #from perf_trace_context import *
61 perf_db_export_mode = True
62 perf_db_export_calls = False
63 perf_db_export_callchains = False
65 def printerr(*args, **keyword_args):
66 print(*args, file=sys.stderr, **keyword_args)
68 def printdate(*args, **kw_args):
69 print(datetime.datetime.today(), *args, sep=' ', **kw_args)
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")
78 if (len(sys.argv) < 2):
83 if (len(sys.argv) >= 3):
88 if columns not in ("all", "branches"):
91 branches = (columns == "branches")
93 for 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
104 raise Exception("Query failed: " + q.lastError().text())
109 raise Exception("Query failed: " + q.lastError().text())
111 printdate("Creating database ...")
122 raise Exception(dbname + " already exists")
124 db = QSqlDatabase.addDatabase('QSQLITE')
125 db.setDatabaseName(dbname)
128 query = QSqlQuery(db)
130 do_query(query, 'PRAGMA journal_mode = OFF')
131 do_query(query, 'BEGIN TRANSACTION')
133 do_query(query, 'CREATE TABLE selected_events ('
134 'id integer NOT NULL PRIMARY KEY,'
136 do_query(query, 'CREATE TABLE machines ('
137 'id integer NOT NULL PRIMARY KEY,'
139 'root_dir varchar(4096))')
140 do_query(query, 'CREATE TABLE threads ('
141 'id integer NOT NULL PRIMARY KEY,'
146 do_query(query, 'CREATE TABLE comms ('
147 'id integer NOT NULL PRIMARY KEY,'
149 do_query(query, 'CREATE TABLE comm_threads ('
150 'id integer NOT NULL PRIMARY KEY,'
153 do_query(query, 'CREATE TABLE dsos ('
154 'id integer NOT NULL PRIMARY KEY,'
156 'short_name varchar(256),'
157 'long_name varchar(4096),'
158 'build_id varchar(64))')
159 do_query(query, 'CREATE TABLE symbols ('
160 'id integer NOT NULL PRIMARY KEY,'
165 'name varchar(2048))')
166 do_query(query, 'CREATE TABLE branch_types ('
167 'id integer NOT NULL PRIMARY KEY,'
171 do_query(query, 'CREATE TABLE samples ('
172 'id integer NOT NULL PRIMARY KEY,'
184 'to_symbol_id bigint,'
185 'to_sym_offset bigint,'
187 'branch_type integer,'
189 'call_path_id bigint)')
191 do_query(query, 'CREATE TABLE samples ('
192 'id integer NOT NULL PRIMARY KEY,'
204 'to_symbol_id bigint,'
205 'to_sym_offset bigint,'
209 'transaction_ bigint,'
211 'branch_type integer,'
213 'call_path_id bigint)')
215 if perf_db_export_calls or perf_db_export_callchains:
216 do_query(query, 'CREATE TABLE call_paths ('
217 'id integer NOT NULL PRIMARY KEY,'
221 if perf_db_export_calls:
222 do_query(query, 'CREATE TABLE calls ('
223 'id integer NOT NULL PRIMARY KEY,'
226 'call_path_id bigint,'
228 'return_time bigint,'
229 'branch_count bigint,'
232 'parent_call_path_id bigint,'
236 # printf was added to sqlite in version 3.8.3
237 sqlite_has_printf = False
239 do_query(query, 'SELECT printf("") FROM machines')
240 sqlite_has_printf = True
245 if sqlite_has_printf:
246 return 'printf("%x", ' + x + ')'
250 do_query(query, 'CREATE VIEW machines_view AS '
255 'CASE WHEN id=0 THEN \'unknown\' WHEN pid=-1 THEN \'host\' ELSE \'guest\' END AS host_or_guest'
258 do_query(query, 'CREATE VIEW dsos_view AS '
262 '(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
268 do_query(query, 'CREATE VIEW symbols_view AS '
272 '(SELECT short_name FROM dsos WHERE id=dso_id) AS dso,'
276 'CASE WHEN binding=0 THEN \'local\' WHEN binding=1 THEN \'global\' ELSE \'weak\' END AS binding'
279 do_query(query, 'CREATE VIEW threads_view AS '
283 '(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
289 do_query(query, 'CREATE VIEW comm_threads_view AS '
292 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
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')
298 if perf_db_export_calls or perf_db_export_callchains:
299 do_query(query, 'CREATE VIEW call_paths_view AS '
302 + emit_to_hex('c.ip') + ' AS ip,'
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,'
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')
314 if perf_db_export_calls:
315 do_query(query, 'CREATE VIEW calls_view AS '
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,'
323 + emit_to_hex('ip') + ' AS ip,'
325 '(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
328 'return_time - call_time AS elapsed_time,'
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,'
335 ' FROM calls INNER JOIN call_paths ON call_paths.id = call_path_id')
337 do_query(query, 'CREATE VIEW samples_view AS '
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,'
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,'
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,'
358 do_query(query, 'END TRANSACTION')
360 evsel_query = QSqlQuery(db)
361 evsel_query.prepare("INSERT INTO selected_events VALUES (?, ?)")
362 machine_query = QSqlQuery(db)
363 machine_query.prepare("INSERT INTO machines VALUES (?, ?, ?)")
364 thread_query = QSqlQuery(db)
365 thread_query.prepare("INSERT INTO threads VALUES (?, ?, ?, ?, ?)")
366 comm_query = QSqlQuery(db)
367 comm_query.prepare("INSERT INTO comms VALUES (?, ?)")
368 comm_thread_query = QSqlQuery(db)
369 comm_thread_query.prepare("INSERT INTO comm_threads VALUES (?, ?, ?)")
370 dso_query = QSqlQuery(db)
371 dso_query.prepare("INSERT INTO dsos VALUES (?, ?, ?, ?, ?)")
372 symbol_query = QSqlQuery(db)
373 symbol_query.prepare("INSERT INTO symbols VALUES (?, ?, ?, ?, ?, ?)")
374 branch_type_query = QSqlQuery(db)
375 branch_type_query.prepare("INSERT INTO branch_types VALUES (?, ?)")
376 sample_query = QSqlQuery(db)
378 sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
380 sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
381 if 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 (?, ?, ?, ?)")
384 if perf_db_export_calls:
385 call_query = QSqlQuery(db)
386 call_query.prepare("INSERT INTO calls VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
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)
406 do_query(query, 'END TRANSACTION')
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)')
413 if (unhandled_count):
414 printdate("Warning: ", unhandled_count, " unhandled events")
417 def trace_unhandled(event_name, context, event_fields_dict):
418 global unhandled_count
421 def sched__sched_switch(*x):
424 def bind_exec(q, n, x):
426 q.addBindValue(str(xx))
430 bind_exec(evsel_query, 2, x)
432 def machine_table(*x):
433 bind_exec(machine_query, 3, x)
435 def thread_table(*x):
436 bind_exec(thread_query, 5, x)
439 bind_exec(comm_query, 2, x)
441 def comm_thread_table(*x):
442 bind_exec(comm_thread_query, 3, x)
445 bind_exec(dso_query, 5, x)
447 def symbol_table(*x):
448 bind_exec(symbol_query, 6, x)
450 def branch_type_table(*x):
451 bind_exec(branch_type_query, 2, x)
453 def sample_table(*x):
456 sample_query.addBindValue(str(xx))
458 sample_query.addBindValue(str(xx))
459 do_query_(sample_query)
461 bind_exec(sample_query, 22, x)
463 def call_path_table(*x):
464 bind_exec(call_path_query, 4, x)
466 def call_return_table(*x):
467 bind_exec(call_query, 12, x)