public static PoorMansTSqlFormatterLib.SqlFormattingManager GetFormattingManager(ISqlSettings settings)
{
var formatter = new PoorMansTSqlFormatterLib.Formatters.TSqlStandardFormatter(
settings.IndentString.Replace("\\t", "\t").Replace("\\s", " "),
settings.SpacesPerTab,
settings.MaxLineWidth,
settings.ExpandCommaLists,
settings.TrailingCommas,
settings.SpaceAfterExpandedComma,
settings.ExpandBooleanExpressions,
settings.ExpandCaseStatements,
settings.ExpandBetweenConditions,
settings.BreakJoinOnSections,
settings.UppercaseKeywords,
false,
settings.KeywordStandardization
);
ResourceManager _generalResourceManager = new ResourceManager("PoorMansTSqlFormatterPluginShared.GeneralLanguageContent", Assembly.GetExecutingAssembly());
formatter.ErrorOutputPrefix = _generalResourceManager.GetString("ParseErrorWarningPrefix") + System.Environment.NewLine;
var formattingManager = new PoorMansTSqlFormatterLib.SqlFormattingManager(formatter);
return(formattingManager);
}
public static PoorMansTSqlFormatterLib.SqlFormattingManager GetFormattingManager(ISqlSettings settings)
{
var formatter = new PoorMansTSqlFormatterLib.Formatters.TSqlStandardFormatter(settings.Options);
ResourceManager _generalResourceManager = new ResourceManager("PoorMansTSqlFormatterPluginShared.GeneralLanguageContent", Assembly.GetExecutingAssembly());
formatter.ErrorOutputPrefix = _generalResourceManager.GetString("ParseErrorWarningPrefix") + System.Environment.NewLine;
var formattingManager = new PoorMansTSqlFormatterLib.SqlFormattingManager(formatter);
return formattingManager;
}
public string FormatSql(SqlTiming timing)
{
var sqlFormatter = new SqlServerFormatter();
var sqlFormat = sqlFormatter.FormatSql(timing);
var poorMansFormatter = new TSqlStandardFormatter();
var fullFormatter = new SqlFormattingManager(poorMansFormatter);
return fullFormatter.Format(sqlFormat);
}
public static PoorMansTSqlFormatterLib.SqlFormattingManager GetFormattingManager(ISqlSettings settings)
{
var formatter = new PoorMansTSqlFormatterLib.Formatters.TSqlStandardFormatter(settings.Options);
ResourceManager _generalResourceManager = new ResourceManager("PoorMansTSqlFormatterPluginShared.GeneralLanguageContent", Assembly.GetExecutingAssembly());
formatter.ErrorOutputPrefix = _generalResourceManager.GetString("ParseErrorWarningPrefix") + System.Environment.NewLine;
var formattingManager = new PoorMansTSqlFormatterLib.SqlFormattingManager(formatter);
return(formattingManager);
}
protected override void WndProc(ref Message m)
{
switch (m.Msg)
{
case WM_DRAWCLIPBOARD:
if (Clipboard.ContainsText())
{
string lSelect = "select";
string lFrom = "from";
string lInsert = "insert";
string lInto = "into";
string lUpdate = "update";
string lSet = "set";
string clipText = Clipboard.GetText();
if ((clipText.ToUpper().Contains(lSelect.ToUpper()) && clipText.ToUpper().Contains(lFrom.ToUpper())) ||
(clipText.ToUpper().Contains(lInsert.ToUpper()) && clipText.ToUpper().Contains(lInto.ToUpper())) ||
(clipText.ToUpper().Contains(lUpdate.ToUpper()) && clipText.ToUpper().Contains(lSet.ToUpper()))
)
{
SqlFormattingManager format = new SqlFormattingManager();
format.Formatter.ErrorOutputPrefix = "";
string sqlFormatado = format.Format(clipText);
sqlFormatado = sqlFormatado.Replace(": ", ":");
Clipboard.SetText(sqlFormatado);
if (!Clipboard.ContainsText())
{
Clipboard.SetText(clipText);
}
}
}
SendMessage(proxjanela, WM_DRAWCLIPBOARD, m.WParam, m.LParam);
break;
case WM_CHANGECBCHAIN:
if (m.WParam == proxjanela)
{
proxjanela = m.LParam;
}
else
{
SendMessage(proxjanela, WM_DRAWCLIPBOARD, m.WParam, m.LParam);
}
break;
default:
base.WndProc(ref m);
break;
}
}
public static PoorMansTSqlFormatterLib.SqlFormattingManager GetFormattingManager(ISqlSettings settings)
{
var formatter = new PoorMansTSqlFormatterLib.Formatters.TSqlStandardFormatter(
settings.IndentString.Replace("\\t", "\t").Replace("\\s", " "),
settings.SpacesPerTab,
settings.MaxLineWidth,
settings.ExpandCommaLists,
settings.TrailingCommas,
settings.SpaceAfterExpandedComma,
settings.ExpandBooleanExpressions,
settings.ExpandCaseStatements,
settings.ExpandBetweenConditions,
settings.BreakJoinOnSections,
settings.UppercaseKeywords,
false,
settings.KeywordStandardization
);
ResourceManager _generalResourceManager = new ResourceManager("PoorMansTSqlFormatterPluginShared.GeneralLanguageContent", Assembly.GetExecutingAssembly());
formatter.ErrorOutputPrefix = _generalResourceManager.GetString("ParseErrorWarningPrefix") + System.Environment.NewLine;
var formattingManager = new PoorMansTSqlFormatterLib.SqlFormattingManager(formatter);
return formattingManager;
}
public void InventoryQuery() {
const string expected = @"SELECT [InventoryKey]
,[Id]
,[Timestamp]
,[StatusChangeTimestamp]
,[PartKey]
,[StorageLocationKey]
,[SerialNo1]
,[SerialNo2]
,[SerialNo3]
,[SerialNo4]
,[Pallet]
,[Lot]
,[ShipmentOrder]
,[DateReceived]
,[DateInstalled]
,[LocationInstalled]
,[Notes]
,[InventoryStatusId]
,[Hide]
,[SS_RowVersion]
FROM [Inventory]
WHERE ([InventoryStatusId] = 80)
";
var builder = new ContainerBuilder();
builder.RegisterModule(new ConfigurationModule(@"C:\temp\Inventory.xml", @"Files\Shorthand.xml"));
var container = builder.Build();
var root = container.Resolve<Root>();
var process = root.Processes[0];
var context = new PipelineContext(new ConsoleLogger(), process, process.Entities[0]);
process.Entities[0].Filter.Add(new Filter { Left = "InventoryStatusId", Right = "80" });
var sql = new SqlFormattingManager().Format(context.SqlSelectInput(process.Entities[0].GetAllFields().Where(f => f.Input).ToArray()));
Assert.AreEqual(expected, sql);
}
public void StarSql() {
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\Northwind.xml");
Assert.AreEqual(0, composer.Root.Errors().Length);
var pipe = new PipelineContext(new TraceLogger(), composer.Process);
var actual = new SqlFormattingManager().Format(pipe.SqlCreateStarView());
Assert.IsNotNull(controller);
const string expected = @"CREATE VIEW [NorthWindStar]
AS
SELECT A.[A1] AS [OrderDetailsDiscount]
,A.[A2] AS [OrderDetailsOrderID]
,A.[A3] AS [OrderDetailsProductID]
,A.[A4] AS [OrderDetailsQuantity]
,A.[A5] AS [OrderDetailsRowVersion]
,A.[A6] AS [OrderDetailsUnitPrice]
,A.[A7] AS [OrderDetailsExtendedPrice]
,A.[A8] AS [TflHashCode]
,A.[A9] AS [CountryExchange]
,A.[B1] AS [OrdersCustomerID]
,A.[C1] AS [CustomersAddress]
,A.[C2] AS [CustomersCity]
,A.[C3] AS [CustomersCompanyName]
,A.[C4] AS [CustomersContactName]
,A.[C5] AS [CustomersContactTitle]
,A.[C6] AS [CustomersCountry]
,A.[C8] AS [CustomersFax]
,A.[C9] AS [CustomersPhone]
,A.[C10] AS [CustomersPostalCode]
,A.[C11] AS [CustomersRegion]
,A.[C12] AS [CustomersRowVersion]
,A.[B2] AS [OrdersEmployeeID]
,A.[E8] AS [ProductsSupplierID]
,A.[E1] AS [ProductsCategoryID]
,A.[B15] AS [OrdersShipVia]
,ISNULL(B.[B3], 0.0) AS [OrdersFreight]
,ISNULL(B.[B4], '12/31/9999 11:59:59 PM') AS [OrdersOrderDate]
,ISNULL(B.[B6], '12/31/9999 11:59:59 PM') AS [OrdersRequiredDate]
,ISNULL(B.[B8], '') AS [OrdersShipAddress]
,ISNULL(B.[B9], '') AS [OrdersShipCity]
,ISNULL(B.[B10], '') AS [OrdersShipCountry]
,ISNULL(B.[B11], '') AS [OrdersShipName]
,ISNULL(B.[B12], '12/31/9999 11:59:59 PM') AS [OrdersShippedDate]
,ISNULL(B.[B13], '') AS [OrdersShipPostalCode]
,ISNULL(B.[B14], '') AS [OrdersShipRegion]
,ISNULL(B.[B16], '12-DEC') AS [TimeOrderMonth]
,ISNULL(B.[B17], '9999-12-31') AS [TimeOrderDate]
,ISNULL(B.[B18], '9999') AS [TimeOrderYear]
,ISNULL(D.[D1], '') AS [EmployeesAddress]
,ISNULL(D.[D2], '12/31/9999 11:59:59 PM') AS [EmployeesBirthDate]
,ISNULL(D.[D3], '') AS [EmployeesCity]
,ISNULL(D.[D4], '') AS [EmployeesCountry]
,ISNULL(D.[D6], '') AS [EmployeesExtension]
,ISNULL(D.[D7], '') AS [EmployeesFirstName]
,ISNULL(D.[D8], '12/31/9999 11:59:59 PM') AS [EmployeesHireDate]
,ISNULL(D.[D9], '') AS [EmployeesHomePhone]
,ISNULL(D.[D10], '') AS [EmployeesLastName]
,ISNULL(D.[D11], '') AS [EmployeesNotes]
,ISNULL(D.[D14], '') AS [EmployeesPostalCode]
,ISNULL(D.[D15], '') AS [EmployeesRegion]
,ISNULL(D.[D17], '') AS [EmployeesTitle]
,ISNULL(D.[D18], '') AS [EmployeesTitleOfCourtesy]
,ISNULL(D.[D19], 0) AS [EmployeesReportsTo]
,ISNULL(D.[D20], '') AS [EmployeesManager]
,ISNULL(D.[D21], '') AS [Employee]
,ISNULL(E.[E2], 0) AS [ProductsDiscontinued]
,ISNULL(E.[E4], '') AS [ProductsProductName]
,ISNULL(E.[E5], '') AS [ProductsQuantityPerUnit]
,ISNULL(E.[E6], 0) AS [ProductsReorderLevel]
,ISNULL(E.[E9], 0.0) AS [ProductsUnitPrice]
,ISNULL(E.[E10], 0) AS [ProductsUnitsInStock]
,ISNULL(E.[E11], 0) AS [ProductsUnitsOnOrder]
,ISNULL(F.[F1], '') AS [SuppliersAddress]
,ISNULL(F.[F2], '') AS [SuppliersCity]
,ISNULL(F.[F3], '') AS [SuppliersCompanyName]
,ISNULL(F.[F4], '') AS [SuppliersContactName]
,ISNULL(F.[F5], '') AS [SuppliersContactTitle]
,ISNULL(F.[F6], '') AS [SuppliersCountry]
,ISNULL(F.[F7], '') AS [SuppliersFax]
,ISNULL(F.[F8], '') AS [SuppliersHomePage]
,ISNULL(F.[F9], '') AS [SuppliersPhone]
,ISNULL(F.[F10], '') AS [SuppliersPostalCode]
,ISNULL(F.[F11], '') AS [SuppliersRegion]
,ISNULL(G.[G2], '') AS [CategoriesCategoryName]
,ISNULL(G.[G3], '') AS [CategoriesDescription]
,ISNULL(H.[H1], '') AS [ShippersCompanyName]
,ISNULL(H.[H2], '') AS [ShippersPhone]
,A.TflBatchId
,A.TflKey
FROM [NorthWindOrder DetailsTable] A WITH (NOLOCK)
LEFT OUTER JOIN [NorthWindOrdersTable] B WITH (NOLOCK) ON (A.A2 = B.B5)
LEFT OUTER JOIN [NorthWindCustomersTable] C WITH (NOLOCK) ON (A.B1 = C.C7)
LEFT OUTER JOIN [NorthWindEmployeesTable] D WITH (NOLOCK) ON (A.B2 = D.D5)
LEFT OUTER JOIN [NorthWindProductsTable] E WITH (NOLOCK) ON (A.A3 = E.E3)
LEFT OUTER JOIN [NorthWindSuppliersTable] F WITH (NOLOCK) ON (A.E8 = F.F13)
LEFT OUTER JOIN [NorthWindCategoriesTable] G WITH (NOLOCK) ON (A.E1 = G.G1)
LEFT OUTER JOIN [NorthWindShippersTable] H WITH (NOLOCK) ON (A.B15 = H.H4);
";
Assert.AreEqual(expected, actual);
}
