How to align DDL lines of an SQL file?

[ysvsr1]

Hi all,
I am newbie. So please bear with me.

I have a txt file having DDL (Data Definition Language) in the following format:

Code: Select all

CREATE MULTISET TABLE IVFF_CALL_SEG
(
CALLER_ID CHAR(26) NOT NULL ,
S_ID SMALLINT NOT NULL ,
S_SEQ_NUM INTEGER NOT NULL ,
CHK_PNT_MENU_TYPE_CD CHAR(2) NOT NULL ,
PTNRQW_ID CHAR(3) NULL
)

I need to format about as follows:

Code: Select all

CREATE MULTISET TABLE IVFF_CALL_SEG
(
CALLER_ID              CHAR(26)   NOT NULL ,
S_ID                   SMALLINT   NOT NULL ,
S_SEQ_NUM              INTEGER    NOT NULL ,
CHK_PNT_MENU_TYPE_CD   CHAR(2)    NOT NULL ,
PTNRQW_ID              CHAR(3)    NULL
)

What is the easiest way to do it?

I deal with lot of sql which have 1000+ columns, manually formatting them is a tedious task

All the columns need to be left aligned. The data types should be line perfectly and the NULL and NOT NULL needs to be aligned properly.

[Mofi]

How to determine which sequence of bytes belong together? Does your input source contain a tab character between the strings to align or is really the space character the separating character?

There is the command Column - Convert to Fixed Column which can be used to convert an entire CSV file to a fixed column text file. It would be possible to copy the lines to align into a new file and use this command if there are tabs in your text file.

But if really the space character is the separator, there is a problem. The first 5 lines contain 4 spaces while the last line has only 2 spaces.

To align the lines in your file an UltraEdit script would be necessary. The problem is that a program like an UltraEdit script can be coded only with clear rules what to do and how. A pattern recognition based on figure drawn from past experience as your brain uses to split up the characters into groups of strings can't be done with a program.

It would be possible to write an UltraEdit script which does in a loop from top to bottom of a file

1. Search for an opening round bracket at beginning of a line.
2. Select everything from start of next line up to end of the line above the line with a closing round bracket at start of the line.
3. Load the selection as array of strings with using \r\n as criteria to split the selected string up into several strings (= list of lines).
4. Split each each array element (=line) up into 4 strings using the first 2 spaces and the last comma as split criterias. It must be taken into account here that last line has no comma.
5. Find out for all first, all second and all third strings of a line which one is the longest over all lines.
6. Merge for every line the 4 strings again together with inserting spaces to get equal length for all first, second and third strings of all lines. Again a special handling is necessary for last line with no comma.
7. Join the lines together to a single string using \r\n as connection link and overwrite the selection with this string.

A further information required for coding such a script would be: How many spaces do you want between first and second and between second and third string?

And is the format of the lines always

ID TYPE ??? ,

or do other variants with more "columns", less or more spaces, etc. exist in the file?

[ysvsr1]

"The first 5 lines contain 4 spaces while the last line has only 2 spaces."

Yes the number of spaces varies for each columns.

------------------------------------------------

A further information required for coding such a script would be: How many spaces do you want between first and second and between second and third string?

I need five spaces.

-----------------------------------------------

And is the format of the lines always

ID TYPE ??? ,

Yes, I currently want the script to handle this scenario.

But there is one more scenario where we can have one more column, but the script for this might be more complicated.

Code: Select all

CHK_PNT_MENU_TYPE_CD     CHAR(2)     NOT NULL     COMPRESS('B','C'),
PTNRQW_ID                CHAR(3)     NULL         COMPRESS('A','B'),

Regardless of whether the column has NULL or NOT NULL at the end, Compress should be aligned. If you could create one generic script that could handle both the scenario's it would be really great. If it is too complicated, then a script that handles scenario one would be sufficient.

[Mofi]

Okay, the following script should do the alignment of the DDL code blocks for both variants. It worked on both of your examples copied into an ASCII file with DOS line terminators with UE v17.20.0.1016 (and inserting a line with ( above and a line with ) below your second example).

Code: Select all

if (UltraEdit.document.length > 0)  // Is any file open?
{
   // Define the environment for the script, set caret to beginning
   // of file and trim all trailing whitespaces from all lines.
   UltraEdit.perlReOn();
   UltraEdit.insertMode();
   UltraEdit.columnModeOff();
   UltraEdit.activeDocument.top();
   UltraEdit.activeDocument.trimTrailingSpaces();

   // The following string variable containing only spaces is used for
   // aligning the data columns by appending spaces from this variable.
   var sSpaces = "                                        ";
   var nSpaceCount = 4;  // Defines the additional number of spaces between
                         // the columns. 1 space is always added additionally.
   var nMaxColumms = 3;  // Defines the maximum number of data columns to align.
   var nBlockCount = 0;  // Used to count the number of modified blocks.
   // The following array is used to hold the lengths of the data columns.
   var anLengths = new Array(nMaxColumms);

   // Determine the type of line termiator used in the file.
   var sLineTerm = "\r\n";    // The default is DOS.
   var sPerlTerm = "\\r\\n";  // This string is for the Perl regexp search.
   if (typeof(UltraEdit.activeDocument.lineTerminator) == "number")
   {
      if (UltraEdit.activeDocument.lineTerminator == 1)
      {
         sLineTerm = "\n";   // UNIX
         sPerlTerm = "\\n";
      }
      else if (UltraEdit.activeDocument.lineTerminator == 2)
      {
         sLineTerm = "\r";   // MAC
         sPerlTerm = "\\r";
      }
   }
   // Run in a loop a Perl regular expression find searching for a block
   // starting with an opening round bracket at start of a line and ending
   // on closing round bracket at start of a line. But first define once
   // all the options for the find command.
   UltraEdit.activeDocument.findReplace.mode=0;
   UltraEdit.activeDocument.findReplace.matchCase=false;
   UltraEdit.activeDocument.findReplace.matchWord=false;
   UltraEdit.activeDocument.findReplace.regExp=true;
   UltraEdit.activeDocument.findReplace.searchDown=true;
   if (typeof(UltraEdit.activeDocument.findReplace.searchInColumn) == "boolean")
   {   // This find option is not supported by all UE with script support.
      UltraEdit.activeDocument.findReplace.searchInColumn=false;
   }

   var sFindExp = "^\\((?:.*" + sPerlTerm + ")+\\)";
   while (UltraEdit.activeDocument.findReplace.find(sFindExp))
   {
      // Get the found and selected block into an array of strings with
      // each string containing one line. The first string is the line
      // with ( and the last string is the line with ). But first replace
      // every "NOT NULL" by "NOT#NULL" and then any sequence of spaces
      // by a single space for splitting the lines later more easily.
      var sFoundBlock = UltraEdit.activeDocument.selection;
      var sBlock = sFoundBlock.replace(/NOT NULL/g,"NOT#NULL");
      sBlock = sBlock.replace(/ +/g," ");
      var asLines = sBlock.split(sLineTerm);

      // Initialize length information for the data columns to align.
      for (var nIndex = 0; nIndex < nMaxColumms; nIndex++) anLengths[nIndex] = 0;

      // Create a new array holding the data of all lines except first
      // and last line only containing opening and closing round bracket.
      var asData = new Array(asLines.length-2);
      var bLastColumnOnlyCommas = false;
      var nLastColIndex = 0;

      // Split the lines into data columns using space character as separator.
      for (var nLine = 0; nLine < asData.length; nLine++)
      {
         asData[nLine] = asLines[nLine+1].split(" ");
         // And determine the longest string for the data columns of all
         // lines. The number of data columns to analyze is either the
         // maximum number of columns to align, or in case the line has
         // less data columns, the number of data columns the line has.
         var nMaxDataCols = (asData[nLine].length >= nMaxColumms) ? nMaxColumms : asData[nLine].length;
         for (var nColumn = 0; nColumn < nMaxDataCols; nColumn++)
         {
            if (asData[nLine][nColumn].length > anLengths[nColumn])
            {
               anLengths[nColumn] = asData[nLine][nColumn].length;
            }
         }
         // Check for the need of special aligning of last data column when
         // this data column is not really a data column because it contains
         // only a comma. The last data line not having a comma and therefore
         // less columns as the other lines must be ignored.
         if ((asData[nLine].length - 1) > nLastColIndex)
         {
            nLastColIndex = asData[nLine].length - 1;
            bLastColumnOnlyCommas = (asData[nLine][nLastColIndex] == ",") ? true : false;
         }
         else if ((asData[nLine].length - 1) == nLastColIndex)
         {
            if (asData[nLine][nLastColIndex] != ",") bLastColumnOnlyCommas = false;
         }
      }
      // Verify that the string variable for spaces contains enough spaces
      // and append spaces in case the predefined amount is not enough. And
      // increase every length by the predefined number of separating spaces.
      for (var nColumn = 0; nColumn < nMaxColumms; nColumn++)
      {
         anLengths[nColumn] += nSpaceCount;
         while (sSpaces.length < anLengths[nColumn]) sSpaces += ' ';
      }
      // If the last column contains only commas, and this column is also
      // taken into account for aligning because spaces are appended to
      // column left the column with the commas, then remove the number
      // of additional spaces from the column left the comma column to
      // have finally just 1 space left the comma.
      if (bLastColumnOnlyCommas && (nMaxColumms >= nLastColIndex))
      {
         anLengths[nLastColIndex-1] -= nSpaceCount;
      }
      // Append the required number of spaces for aligning the data
      // columns and join them together to build again the lines.
      for (var nLine = 0; nLine < asData.length; nLine++)
      {
         // Never append spaces on last data column of line.
         var nMaxColsAlign = asData[nLine].length - 1;
         if (nMaxColsAlign > nMaxColumms) nMaxColsAlign = nMaxColumms;
         for (var nColumn = 0; nColumn < nMaxColsAlign; nColumn++)
         {
            var nSpacesToAppend = anLengths[nColumn] - asData[nLine][nColumn].length;
            asData[nLine][nColumn] += sSpaces.substr(0,nSpacesToAppend);
         }
         asLines[nLine+1] = asData[nLine].join(" ");
      }

      // Rebuild the entire block with now aligned data columns.
      sBlock = asLines.join(sLineTerm);
      sBlock = sBlock.replace(/NOT#NULL/g,"NOT NULL");
      // Was the block already perfectly aligned?
      if (sBlock != sFoundBlock)
      {
         nBlockCount++;   // No, overwrite selected block.
         UltraEdit.activeDocument.write(sBlock);
      }
   }
   UltraEdit.activeDocument.top();
   UltraEdit.outputWindow.write("Number of aligned blocks: " + nBlockCount);
}

[ysvsr1]

Thanks much. Works like a charm !