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