SQL Code Formatting

Find Tips and tricks on how to better use the Zeus IDE. Feel free to post your own tips but please do not post bug reports, feature requests or questions here.
Post Reply
jussij
Site Admin
Posts: 2512
Joined: Fri Aug 13, 2004 5:10 pm

SQL Code Formatting

Post by jussij » Fri Jan 13, 2012 6:09 am

The macro below uses SqlFormatter utility to reformat SQL code.

To use this macro you will need to download the SqlFormatter utility and place it in the Zeus install folder.

Note:
The macro below works with the entire file but with a bit of tweaking it is would also be possible to make the macro work with just a marked area.

For an example on how to do this refer to this macro.

Code: Select all

--
--        Name: SqlFormatter Code Reformating Macro
--
--    Language: Lua Macro
--
-- Description: This macro will excute the SqlFormatter.exe utility to reformat
--              the current file. For more details on how to use SqlFormatter
--              visit the following web page:
--
--     http://www.architectshack.com/PoorMansTSqlFormatter.ashx

function make_backup(source, destination)
    local input = io.open(source, "r")
    local output = io.open(destination, "w+")
    output:write(input:read("*all"))
    output:close()
    input:close()
end

function key_macro()
  -- dialog box caption
  local caption = "SqlFormatter Utility"

  -- macro only works for documents
  local document = is_document()

  -- macro only works for read/write documents.
  local locked = is_read_only()

  -- macro only works for named documents.
  local named = is_named()

  if (locked == 1) or (document == 0) or (named == 0) then
    -- can't run astyle.exe on current document
    message("This macro can only be run with a named, writable document file.")
    beep()
    return
  end

  -- the directory of the current document
  local dir = macro_tag("$fdd")

  -- the name of the current document
  local file_name = macro_tag("$f")

  -- the name of the backup document
  local file_backup = file_name .. ".orig"

  local message1 = "SqlFormatter is ready to re-style the current file, while the following backup file will also be created:\n\n         '" .. file_backup .."'\n"
  local message2 = "NOTE: If the styling does not work as expected use backup file to undo the changes made.\n\nDo you want to continue with the styling?"

  local message_text = message1 .. "\n" .. message2

  -- uncomment this code if you want confirmation
  local result = message_box(4, message_text, caption)

  -- comment this line if you want user confirmation
  --local result = 6

  -- check for the IDYES return value
  if (result == 6) then
      message("SqlFormatter is formatting the code....")
  else
      message("Operation canceled by user.")
      return
  end

  -- make a backup copy of the file
  make_backup(dir .. file_name, dir .. file_backup)

  -- ===================================================================
  -- Poor Man's T-SQL Formatter - a small free Transact-SQL formatting
  -- library for .Net 2.0, written in C#. Distributed under AGPL v3.
  -- Copyright (C) 2011 Tao Klerks
  -- v1.0.1.23412
  --
  -- Usage notes:
  --
  -- SqlFormatter <filename or pattern> <options>
  --
  --    /is  Indent string (default: \t)
  --    /st  Spaces per tab (default: 4)
  --    /mw  Maximum line width (default: 999)
  --    /tc  Trailing commas (default: false)
  --    /sac Add space after leading comma (default: false)
  --    /ebc Expand 'BETWEEN' conditions (default: true)
  --    /ecs Expand 'CASE' statements (default: true)
  --    /ebe Expand boolean expressions (default: true)
  --    /ecl Expand comma lists (default: true)
  --    /uk  Uppercase keywords (default: true)
  --    /sk  Standardize keywords (default: false)
  --    /e   Extensions (default: sql)
  --    /r   Recursive (default: false)
  --    /b   Backups (default: true)
  --    /b   OutputFileOrFolder (default: none; if set, overrides the backup option)
  --    /h ? Help
  --
  -- Disable boolean options with a trailing minus, enable by just specifying them or with a trailing plus.
  --
  -- eg:
  --
  -- SqlFormatter TestFiles\* /is:"  " /tc /uc-
  --
  -- or
  --
  -- SqlFormatter test*.sql /o:resultfile.sql
  -- ===================================================================

  -- turn of the back as we already have one
  local cmd_options = "/b- /sac "

  -- get the tab size for the current document
  local tab_size = macro_tag("$TabSize")

  -- see if real tabs are to be used
  local use_tabs = macro_tag("$UseTabs")

  if (use_tabs == "true") then
    -- use tabs as white space
    cmd_options = cmd_options .. "/is:\\t /st " .. tab_size
  else
    -- the white space string
    local space = string.sub("                           ", 1, tab_size)

    -- use space as white space
    cmd_options = cmd_options .. "/is:\"" .. space .. "\" /st " .. tab_size
  end

  -- build up the final styling command line
  local cmd = "SqlFormatter.exe" .. " " ..  cmd_options  .. " \"" .. file_name .. "\""

  -- for debugging only
  --message_box(1, cmd, "Command Line", caption)

  -- System control values
  --  1 - save the document before running the program
  --  2 - capture any standard output generated by the program
  --  4 - capture any standard error generated by the program
  --  8 - ask for additional arguments
  -- 16 - the program will use the MS-DOS command interpreter (ie. dir *.* etc)
  -- 32 - wait for the program to complete (the ESC key will cancel the wait)
  -- 64 - run the program in a visible DOS session (otherwise runs hidden)

  -- run command using the 'save' and 'wait for complete' options
  local flags = 1+32

  -- run the SqlFormatter.exe command
  if (system(cmd, dir, flags) == 0) then
    -- reload the newly styled document
    FileReloadCurrent()

   -- Uncomment this code if you want to see the backup file
   -- -- get the current window id
   -- local id = get_window_id()
   --
   -- -- build the name of the backup file
   -- local backup_file = dir .. file_backup
   --
   -- -- load the backup file as well
   -- if file_open(backup_file) == 1 then
   --   -- re-activate the reformated file
   --   window_activate(id)
   -- end

    -- some feedback
    message("The styling of the document is now complete.")
  else
    -- had problems running the command
    local error_msg = cmd .. "\n\n" .. "Error executing the 'SqlFormatter.exe' command!"
    message_box(1, error_msg, caption)
  end
end

key_macro() -- run the macro

neumannadre
Posts: 1
Joined: Wed Aug 15, 2018 8:40 am

Re: SQL Code Formatting

Post by neumannadre » Wed Aug 15, 2018 8:53 am

Thanks for the information! I want to add one more tool to this list - I use SQL Formatter to make my code better. This is an online service http://www.sql-format.com/

Post Reply
kuchnie warszawa opinie

Who is online

Users browsing this forum: No registered users and 1 guest