Features to Do

Options in source as hints

/* sqlines(option1=value1, option2 = value2) */

Can be used for tests and in online tools. Maybe very convenient.

Discussion Forums

Report Feature - Target tokens

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.

SELECT MINUS to NOT IN

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 ()

Assessment - Static Analyzer

Find columns that do not exist in tables Find CASE WHEN NULL issues Various recommendations on performance Use of old native join etc

Assessment

Find missing columns (compare columns used in queries and existing in tables), wrong data types etc

(a,b,c) alias for $1 (linked list by values )

  1. CREATE FUNCTION instr(varchar, INTEGER) RETURNS INTEGER AS $$
  2. DECLARE
  3. INDEX ALIAS FOR $2;
  4. user_id INTEGER;
  5. v_string ALIAS FOR $1;
  6. BEGIN
  1. {
  2. DECLARE
  3. ...
  4. $alias = { $ident1 ALIAS FOR \$ident2; }
  5. ...
  6. BEGIN
  7. =>
  8. pushAt("alias list", $ident2, $ident1);
  9. cut($alias);
  10. raise("alias list")
  11. }
  12.  
  13. {
  14. CREATE FUNCTION $ident1($ident2.list.comma)
  15. =>
  16. waitfor("alias list")
  17. {
  18. prepend($ident2, pop("alias list"));
  19. }
  20. }

SELECT INTO into var=value (2 lists linked by order)

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

Concept

Always develop as if you deal with the simplest case

Develop as if you do by hands - one action at a time

$expv1.member

a way to link variable with type, or column with table.

$exp

$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)

Copy and Paste Model

- convert NVL to ISNULL (simple copy)

  1. {
  2. nvl($exp1, $exp2)
  3. =>
  4. isnull($exp1, $exp2)
  5. }

- CASE $exp WHEN NULL to CASE WHEN $exp IS NULL

  1. {
  2. CASE $exp ...
  3. WHEN NULL
  4. =>
  5. mark("when null");
  6. cut($exp);
  7. change(NULL, $exp IS NULL);
  8. }
  9.  
  10. {
  11. [marked("when null") CASE] $exp ...
  12. WHEN $lit
  13. =>
  14. change($lit, $exp = $lit);
  15. }

- Move subselect out of IN

{
   from ... $in_clause =  { and $ident in (select ...) }
   where $data = { ... }
=>
   cut($in_clause);
   append($data, $in_clause);
}

- Outer join

  1. // firstly, lets do transposition of tables in from
  2. {
  3. FROM ... $expv1 ... , $expv2 ...
  4. WHERE ... $expv2.member *= $expv1.member
  5. =>
  6. cut($expv1);
  7. append($expv2, \, $expv1);
  8. }
  9.  
  10. {
  11. FROM ... $expv1 ... $join = { , $expv2 } ...
  12. WHERE ...$join_condition = {$expv1.$ident1 *= $expv2.$ident2 }
  13. =>
  14. change($join, \, , LEFT OUTER JOIN);
  15. append($join, ON $join_condition);
  16. cut($join_condition);
  17. }
  18.  
  19. // second and further conditions for the same tables
  20. {
  21. FROM ... $join_pair = { $ident1 LEFT OUTER JOIN $ident2 ON $ident1.$ident12 = $ident2.$ident22 } ...
  22. WHERE ...$join_condition = {$ident1.$ident11 ... *= $ident2.$ident21 }
  23. =>
  24. change($join_condition, *=, =);
  25. append($join_pair, AND $join_condition);
  26. cut($join_condition);
  27. }
  28.  
  29. // constant equations for right tables
  30. {
  31. FROM ... $join_pair = { LEFT OUTER JOIN $ident2 } ...
  32. WHERE ...$const__condition = { $ident2 = $lit }
  33. =>
  34. append($join_pair, AND $const_condition);
  35. cut($const_condition);
  36. }
  37.  
  38. @noassess()
  39. {
  40. FROM $conditions = {...} WHERE
  41. =>
  42. change($conditions, *=, =);
  43. }
  44.  
  45. @noassess()
  46. {
  47. WHERE AND
  48. =>
  49. cut(AND);
  50. }

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"))
}

Built in variables

$lit - string or number literal

Link Assessment with Docs

In assessment report, you can link conversion stats with actual doc (link by @name and namespace)

Documentation

use 'exp' syntax to put code in italic exp in doku wiki

Studio

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)

Case Insensitivity By Default

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

Specify Context

  1. {
  2. [ {[SYSDATE | SYSTIMESTAMP] [+|-]} $num/24]
  3. =>
  4. // convert to INTERVAL expression
  5. if($target(NonStop))
  6. [INTERVAL '$num' HOUR]
  7. }

Part in context {} is not converted, it just checked for existence

Default Template name

file name - nvl.txt → default name is nvl

Annotations

@name() - template name @id() - id of a template for further references in assessment reports and other templates.

Variables

If variable from OR or optional list requires further processing, define variable

  1. {
  2. // one of possible operators in LNNVL() function
  3. $operator = [ = | < | > ];
  4.  
  5. [LNNVL($exp1 $operator $exp2)]
  6. =>
  7. // for all other databases convert to CASE expression syntax
  8. if(!$target(Oracle))
  9.  
  10. [((NOT $exp1 $operator $exp2) OR (1 = case when (exp1 IS
  11. NULL OR $exp2 IS NULL) then 1 else 0 end))]
  12. }

variable can be local or global (defined some where outside {} not only in grammar-helper.txt)

DDL and DML

For each SQL statement, identify the DDL or DML operation (CREATE, DELETE, INSERT, UPDATE) and the target table(s).

  1. @name("DDL statements", @ddl)
  2. {
  3. [CREATE $tok;]
  4. }
  5.  
  6.  
  7. @name("DML statements", $dml)
  8. {
  9. [[INSERT | UPDATE | DELETE ] $tok;]
  10. }
  11.  
  12. @name("SQL SELECT statements", $select)
  13. {
  14. [SELECT $tok1 FROM $tok2;]
  15. }

Get list of tables in FROM

  1. @name("Table name", $tab)
  2. {
  3. // match FROM ... WHERE clause
  4. [FROM
  5. $list
  6. (
  7. // FROM list can either contain table names with optional aliases
  8. [$ident1 [^$ident2]]
  9. |
  10. // or sub-select statements with mandatory aliases
  11. [(SELECT $tok) $ident2]
  12. )
  13. WHERE]
  14. =>
  15. foreach($item in $list)
  16. {
  17. $assess($ident1, $tabname);
  18. }
  19. }

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

  1. <?sqlines
  2. foreach($var in $dml)
  3. {
  4. $echo($dml().data());
  5. }
  6. ?>
  7.  
  8. <= $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)