Manual for Pl/Sql Tidy version 1.2

Its purpose

This software allows you to tidy/beautify/format your Pl\Sql code in a batch file or Dos prompt.
This is a command line tool that gives you the freedom to build it in your programming environment. You can use it in your favorite editor easily.

How to use it

Goto to the installation directory and enter in dos:
psti -h
for the options and help,

How to use it in an editor

In a regular editor, there are user definable tools. Set a tool to execute psti.
Make sure that the working directory is the same as the installation directory. Otherwise, it won't work.

E.g. Ultraedit
Command line : psti -io %F
Working directory: d:\psti

About the use of switches

Incorrect switches are simply ignored. You are informed about that.
Incompatible switches are allowed. The switches that come later will override earlier ones.

There are 3 types of switches:
-with no value (-h,-v)
-with 2 possible value (-rs+,-rs-)
-with many possible values (-i,-o..)

Many switches are already "on" by default. Therefore things may not work out as you expect.
Take these into account when using switches.

Here is the list:
-in+ -uk+ -c+ -rsaob+ -co+ -ncb+ -rs+ -iaew+ -iacw+ -iac+ -clb+

List of switches


Switch/option

Meaning

-i filename/stdin

use filename as input file or use standard input as source

-o filename/stdout

use filename output file (tidied up) or use standard output as destination

-io

filename same input and output file

-sl lineno

tidy lines from this lineno on

-se lineno

tidy lines up to that lineno

-t size

tab is equal to size spaces

-is size

indent size in spaces (generally)

-in[+,-]

indent lines yes/no

-il[Level]

size indent size at Level(specific)

-uk[+,-]

uppercase keywords yes/no

-ck[+,-]

capital keywords yes/no

-lk[+,-] lowercase keywords yes/no
-ui[+,-] uppercase identifiers yes/no
-ci[+,-] capital identifiers yes/no
-li[+,-] lowercase identifiers yes/no

-clb[+,-]

column like lists inside brackets

-c[+,-]

compactify, remove redundant spaces/keep

-co[+,-]

remove spaces around operations (+,- etc/do nothing/)

-sao[+,-]

add space around operations/do nothing

-rsaob[+,-]

remove spaces after opening brackets/keep

-iic[+,-]

indent inside comments/do nothing

-ncb[+,-]

don't remove spaces around brackets/do nothing

-rs[+,-]

keep the relative identation of a select/do nothing

-iaew[+,-]

indent after exception when yes/no

-iacw[+,-] indent after case when yes/no

-iac[+,-]

indent after cursor yes/no

-acav[+,-]
align consecutive assignments (:=) vertically
-isc[+,-]
indent standalone comments

-x

does not execute anything, just prints info about the configuration given by command line

-v

verbose , same as -x but it will tidy

-b maxvers

number of backup versions to keep

-bdir directory
set backup directory

-h

help

Switches and examples

-h

lists you all possible switches you can use.

-v

shows you the settings.

-i filename input file

This is the filename of scripts to tidy.
If the filename is stdin, the program uses the standard input as a source

E.g. psti -i=h.sql
E.g psti -i stdin

-o filename output file (tidied up)

Default value: input filename + ".tdy"

This is the resultant file of the formatting.
If filename is stdout, the program uses the standard output as a destination.

E.g. psti -i=h.sql -o=h2.sql
E.g psti -i stdin -o stdout

-sl lineno tidy lines from this lineno on

The resultant fiile will not contain lines before line lineno of the original file.

E.g. psti -i=h.sql -sl 4

-sr lineno tidy lines up to that lineno

The resultant file will not contain lines after line lineno of the original file.

E.g. psti -i=h.sql -sl 2000

-io filename same input and output file

This is a shortcut for -i and -o.

E.g. psti -io=h.sql

-t size tab is equal to size spaces

Default value: 8 spaces.
Specify the size of tab character in spaces.


E.g. psti -io=h.sql -t 4


-is size indent size in spaces (generally)

Default value: 2 spaces

Probably it is one of the most important switches.

E.g. psti -io=h.sql -is 3

-il[Level] size indent size at Level(specific)

Level refers to how many spaces of indentation precedes a given line of code. The code inside a loop is more indented than the code before the loop.
Statements that make the level of code higher/more indented :
packages, functions, procedures,loops, if , begin, when(optional), cursor (optional),
The size is in spaces. Use more of this switch, if you want to set more than 1 level.

E.g. psti -io=h.sql -is 3 -il1 2 -il2 4

-uk[+,-] uppercase keywords yes/no

Default state:on.
All keywords will be converted into uppercase letters. (BEGIN IF VARCHAR2)


E.g. psti -io=h.sql -uk+

-ck[+,-] capital keywords yes/no

Default state:off.
All keywords will be capitalized. (Begin If Varchar2)

E.g. psti -io=h.sql -ck+

-lk[+,-] lowercase keywords yes/no

Default state:off.
All keywords will be converted into lowercase letters. (begin if varchar2)


E.g. psti -io=h.sql -lk+

-ui[+,-] uppercase identifiers yes/no

Default state:off.
All identifiers will be converted into uppercase letters. (TERRA5:=3;)


E.g. psti -io=h.sql -ui+

-ci[+,-] capital identifiers yes/no

Default state:off.
All identifiers will be capitalized. (Terra5:=3;)

E.g. psti -io=h.sql -ci+

-li[+,-] lowercase identifiers yes/no

Default state:off.
All keywords will be converted into lowercase letters. (terra5:=3;)

E.g. psti -io=h.sql -li+

-clb[+,-] column like lists inside brackets

Default state:on

E.g. psti -io=h.sql -clb+

this:

a := (a + 1
+ 4
+ 5
+ 8);
will become that:
a := (a + 1
+ 4
+ 5
+ 8);

-c[+,-] compactify, remove redundant spaces/keep

Default state:on.

E.g. psti -io=h.sql -c+

Original

procedure h is

Tidied

procedure h is

-co[+,-] remove spaces around operations (+,- etcdo nothing/)

Default state:on.

E.g. psti -io=h.sql -co+

Original

d:= 5 + 7;

Tidied

d:=5+7;

-sao[+,-] add space around operations/do nothing

Default state:on.

E.g. psti -io=h.sql -co- -sao+

Original

d:=5+7;

Tidied

d := 5 + 7;


-rsaob[+,-] remove spaces after opening brackets/keep

Default state:on.

Original

FOR rec IN ( SELECT * FROM dual
WHERE g = 5)


Tidied
psti -io=h.sql -rsaob-

FOR rec IN ( SELECT * FROM dual
WHERE g = 5)


Tidied
psti -io=h.sql -rsaob+

FOR rec IN (SELECT * FROM dual
WHERE g = 5)


-iic[+,-] indent inside comments/do nothing

Default state:off.

If it is on, it will indent text inside /*..*/

-ncb[+,-] don't remove spaces around brackets/do nothing

Default state:on.

Original
psti -io=h.sql -ncb-

FOR rec IN(SELECT * FROM dual
WHERE g = 5)


Tidied
psti -io=h.sql -ncb+

FOR rec IN (SELECT * FROM dual
WHERE g = 5)


-rs[+,-] keep the relative identation of a select/do nothing

Default state:on.

Original
psti -io=h.sql -rs+

select *
from memory
where year>1786


Tidied
psti -io=h.sql -rs-

select *
from memory
where year>1786

-iaew[+-] indent after when in exception yes/no


Original
tpsf -io=h.sql -iaew-

EXCEPTION
WHEN no_data THEN
hello;


Tidied
tpsf -io=h.sql -iaew+

EXCEPTION
WHEN no_data THEN
  hello;


-iacw[+-] indent after when in case statement yes/no


Original
tpsf -io=h.sql -iacw-

CASE opinion
  WHEN '9' THEN
  dbms_output.put_line('Excellent');
  WHEN '5' THEN dbms_output.put_line('Poor');
  ELSE dbms_output.put_line('cannot decide');
END CASE;   


Tidied
tpsf -io=h.sql -iacw+

CASE opinion
  WHEN '9' THEN      

    dbms_output.put_line('Excellent');
  WHEN '5' THEN dbms_output.put_line('Poor');
  ELSE dbms_output.put_line('cannot decide');
END CASE;

-iac[+,-] indent after cursor yes/no

Default state:on.

Original
psti -io=h.sql -iac-

CURSOR b IS
SELECT * FROM dual;

Tidied
psti -io=h.sql -iac+

CURSOR b IS
SELECT * FROM dual;

-acav[+,-] align consecutive assignments (:=) vertically

Default state:off.

Original
psti -io=h.sql -acav-

derival:=0;
d:=1;

Tidied
psti -io=h.sql -acav+

derival:=0;
d :=1;

-isc[+,-] indent standalone comments

Default state:off.
Original
psti -io=h.sql -isc-
begin
/*hello  Sql*/
  low:=4+4
-- end of little block
end;
Tidied
psti -io=h.sql -isc+
begin
  /*hello  Sql*/
  low:=4+4
  -- end of little block
end;

-b maxvers number of backup versions to keep

Default value:40.

Number of old backup versions to keep +1 (including the current backup version that is being created)

-bdir directory

Default value: temp enviromental variable (usually C:\DOCUME~1\username\LOCALS~1\Temp)
Set backup directory

Backing up

The original source and its directory path are backed up. Even old backups are backed up.
The backup directory can be specified by -bdir switch. Otherwise, it is
the tmp directory.

Syntax checking

Some syntax checking is performed. If the check fails, the code is not tidied.
The following are checked:
-the balance of brackets ()
-runaway/unclosed '
-runaway/unclosed "
-blocks are closed properly (missing end if,end loop, end package detection)

Return code of the software

If the executable runs without error, it returns error code 0. 

Otherwise, it returns 2.

Safety measures

-Syntax checking. If it fails, the code is not formatted.
-The formatter checks itself. So it does not produce bad code easily. If self-check fails, it displays the message : Integrity error and the code is not touched.
-It backs up the original source in the temp directory. (designated by tmp enviroment variable). By default, 40 versions are backed up. It can be changed by -b flag.
-Unless you specify -io, the output file is the original filename plus .tdy. So the original one is not overwritten.


Limits

The software can handle/format a code up to a size that is maximum of 1/3 of the available memory.
(If you have 500 Mbyte memory, it can handle 166Mbyte code). This is an approximation.


License

Pl/Sql Tidy
Idea/programmed by Marton Papp ((C)2003-2008 )
End User License Agreement (EULA)
THIS IS FREEWARE AND IS TO BE DISTRIBUTED AS THAT ONLY
===========================================================
You will agree to these terms in order to continue:
1. If you distribute this software, you are to (a) Distribute it in it's original condition (b) You will send it with all and only the files that came with it.
2. You will not alter this file or any of the contained files
3. You will not use this application for any illegal activities
4. You will install this application at your own risk. Any damage done is the responsibility of the end-user
This software contains ABSOLUTELY NO ADWARE, SPYWARE, VIRUSES or other harmful applications.

Credits

Made in C++ with STL.
Compiled with Microsoft Compiler 
Html pages are edited with  NVU
The installer is Inno Setup
The rest is me.