/* sqlines(option1=value1, option2 = value2) */
Can be used for tests and in online tools. Maybe very convenient.
In report show source match and target tokens (in symbolic form to understand conversion after applying all options $options, $target; use symbols to represent $change, $delete)
Also output link to problem description and examples. Cool feature.
a) full pattern match
SELECT [DISTINCT] $list1 = { $ident } FROM ... $list2 = { MINUS SELECT ... $eost } => $foreach($list2) { $change(MINUS, $list1 NOT IN \( ); $prepend($eost, \) ); }
b) loose match (context)
first template [^MINUS] SELECT $list (save list) seconds MINUS SELECT → $list_from_stack NOT IN ()
Find columns that do not exist in tables Find CASE WHEN NULL issues Various recommendations on performance Use of old native join etc
Find missing columns (compare columns used in queries and existing in tables), wrong data types etc
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE index ALIAS FOR $2; user_id integer; v_string ALIAS FOR $1; BEGIN
{ DECLARE ... $alias = { $ident1 ALIAS FOR \$ident2; } ... BEGIN => pushAt("alias list", $ident2, $ident1); cut($alias); raise("alias list") } { CREATE FUNCTION $ident1($ident2.list.comma) => waitfor("alias list") { prepend($ident2, pop("alias list")); } }
a,b,c INTO v1, v2, v3 -> v1=a, v2=b, v3=c { $expv.list.comma INTO $ident.list.comma FROM => change($expv, $expv = $ident); cut(INTO); }
Always develop as if you deal with the simplest case
Develop as if you do by hands - one action at a time
a way to link variable with type, or column with table.
$exp - any in () including select statement, variable, identifier, function f() all separated by operands
$expv - expression that can be aliased (DB) or referenced through a variable such as sqlcon.Open, or func(sqlcon)
- convert NVL to ISNULL (simple copy)
{ nvl($exp1, $exp2) => isnull($exp1, $exp2) }
- CASE $exp WHEN NULL to CASE WHEN $exp IS NULL
{ CASE $exp ... WHEN NULL => mark("when null"); cut($exp); change(NULL, $exp IS NULL); } { [marked("when null") CASE] $exp ... WHEN $lit => change($lit, $exp = $lit); }
- Move subselect out of IN
{ from ... $in_clause = { and $ident in (select ...) } where $data = { ... } => cut($in_clause); append($data, $in_clause); }
- Outer join
// firstly, lets do transposition of tables in from { FROM ... $expv1 ... , $expv2 ... WHERE ... $expv2.member *= $expv1.member => cut($expv1); append($expv2, \, $expv1); } { FROM ... $expv1 ... $join = { , $expv2 } ... WHERE ...$join_condition = {$expv1.$ident1 *= $expv2.$ident2 } => change($join, \, , LEFT OUTER JOIN); append($join, ON $join_condition); cut($join_condition); } // second and further conditions for the same tables { FROM ... $join_pair = { $ident1 LEFT OUTER JOIN $ident2 ON $ident1.$ident12 = $ident2.$ident22 } ... WHERE ...$join_condition = {$ident1.$ident11 ... *= $ident2.$ident21 } => change($join_condition, *=, =); append($join_pair, AND $join_condition); cut($join_condition); } // constant equations for right tables { FROM ... $join_pair = { LEFT OUTER JOIN $ident2 } ... WHERE ...$const__condition = { $ident2 = $lit } => append($join_pair, AND $const_condition); cut($const_condition); } @noassess() { FROM $conditions = {...} WHERE => change($conditions, *=, =); } @noassess() { WHERE AND => cut(AND); }
How to put all internal Procedures to end and wrap file with CREATE PROC
{ ... $proc = { PROCEDURE ... END } ... => push("inline procs", $proc) cut($proc) } @file-start() { indent($file, \t); // indent all lines with tab !!! prepend($file, CREATE PROCEDURE $file.name AS) append($file, END); } @file-end() { append($file, pop("inline procs")) }
$lit - string or number literal
In assessment report, you can link conversion stats with actual doc (link by @name and namespace)
use 'exp' syntax to put code in italic exp in doku wiki
Single dir list of the left, always linked with Target dir (that does not have a separate list as in commander)
All highligting is done in one tree
But when you select directory, you can 2 panels on the right, with source and target lists
If conversion was not done, target list is empty
Script view is vertical (one under one)
Quick template at the bottom
Templates as a TAB in tree on the left!!!
The same is for assessment reports - one view is source, second in browser (even text file can be shown there)
By default, all string comparisons in ”” are case insensitive. But in grammar helper you can change default.
To override default, use ::CS, ::CI after string
{ [ {[SYSDATE | SYSTIMESTAMP] [+|-]} $num/24] => // convert to INTERVAL expression if($target(NonStop)) [INTERVAL '$num' HOUR] }
Part in context {} is not converted, it just checked for existence
file name - nvl.txt → default name is nvl
@name() - template name @id() - id of a template for further references in assessment reports and other templates.
If variable from OR or optional list requires further processing, define variable
{ // one of possible operators in LNNVL() function $operator = [ = | < | > ]; [LNNVL($exp1 $operator $exp2)] => // for all other databases convert to CASE expression syntax if(!$target(Oracle)) [((NOT $exp1 $operator $exp2) OR (1 = case when (exp1 IS NULL OR $exp2 IS NULL) then 1 else 0 end))] }
variable can be local or global (defined some where outside {} not only in grammar-helper.txt)
For each SQL statement, identify the DDL or DML operation (CREATE, DELETE, INSERT, UPDATE) and the target table(s).
@name("DDL statements", @ddl) { [CREATE $tok;] } @name("DML statements", $dml) { [[INSERT | UPDATE | DELETE ] $tok;] } @name("SQL SELECT statements", $select) { [SELECT $tok1 FROM $tok2;] }
Get list of tables in FROM
@name("Table name", $tab) { // match FROM ... WHERE clause [FROM $list ( // FROM list can either contain table names with optional aliases [$ident1 [^$ident2]] | // or sub-select statements with mandatory aliases [(SELECT $tok) $ident2] ) WHERE] => foreach($item in $list) { $assess($ident1, $tabname); } }
Assessment functions
@name(“DML statements”, $dml) {
[[INSERT | UPDATE | DELETE ] $tok;]
}
$dml.count() - return the total number of times this template was matched. $dml.name(name) - returns name
$templates.count() - count of all templates $templates.count(functions/string-functions) - total for string functions
How to count whether $dml matched INSERT or DELETE?
$dml must contain matched string!!!!
INSERT $tok, DELETE $tok etc
How to access them in abstract form??? This required for Group by, sort, having
$dml().lineNum() - line number for an occurence $dml().file() - filename $dml().fullFile() - file name with path $dml().data() - matched string $dml().match() - matched template (without | ), what exactly was matched
<?sqlines foreach($var in $dml) { $echo($dml().data()); } ?> <= $source() > // is also acceptable
Group by can be done on matched string (as a whole)
Assessment functions
$source(), $target() - returns name of source and target (already used in conversion templates)
$timestamp() - current timestamp (should accept PHP modifiers %Y %d etc)
-assessment-template = command line option
$company() - default registration name
$applicationName()
$projectName()
$author()
$sqlinesVersion()
Assessment report showing list of files that were changed, total number of files
10 total files
5 changed
XML Report that can imported to Excel and other tools
Calculating total number of lines containing SQL and API (summary report)