cheatsheet_en.md

[toc]

Useful Commands

Basic suggest

echo "select title from sakila.film" | ./soar -log-output=soar.log

Analyze SQL with test environment

vi soar.yaml
# yaml format config file
online-dsn:
    addr:     127.0.0.1:3306
    schema:   sakila
    user:     root
    password: "1t'sB1g3rt"
    disable:  false

test-dsn:
    addr:     127.0.0.1:3306
    schema:   sakila
    user:     root
    password: "1t'sB1g3rt"
    disable:  false

echo "select title from sakila.film" | ./soar -test-dsn="root:1t'[email protected]:3306/sakila" -allow-online-as-test -log-output=soar.log

List supported heuristic rules

$ soar -list-heuristic-rules

Ignore Rules

$ soar -ignore-rules "ALI.001,IDX.*"

List supported report-type

$ soar -list-report-types

Set report-type for output

$ soar -report-type json

Syntax Check

$ echo "select * from tb" | soar -only-syntax-check
$ echo $?
0

$ echo "select * fromtb" | soar -only-syntax-check
At SQL 0 : syntax error at position 16 near 'fromtb'
$ echo $?
1

Slow log analyzing

$ pt-query-digest slow.log > slow.log.digest
# parse pt-query-digest's output which example script
$ python2.7 doc/example/digest_pt.py slow.log.digest > slow.md

SQL FingerPrint

$ echo "select * from film where col='abc'" | soar -report-type=fingerprint

Output

select * from film where col=?

Convert UPDATE/DELETE/INSERT into SELECT

$ echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

Output

select * from film;

Merge ALTER SQLs

$ echo "alter table tb add column a int; alter table tb add column b int;" | soar -report-type rewrite -rewrite-rules mergealter

Output

ALTER TABLE `tb` add column a int, add column b int ;

SQL Pretty

$ echo "select * from tbl where col = 'val'" | ./soar -report-type=pretty

Output

SELECT
  *
FROM
  tbl
WHERE
  col  = 'val';

Convert markdown to HTML

md2html comes with other flags, such as -report-css, -report-javascript, -markdown-extensions, -markdown-html-flags, you can get more self control HTML report.

$ cat test.md | soar -report-type md2html > test.html