public void testSelectItemInNewLine()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT e.employee_id,\n" + " d.locatioin_id \n" + "FROM employees e,departments d \n" + "order by e.employee_id,d.locatioin_id,d.locatioin_id2 ";
sqlparser.parse();
option.selectItemInNewLine = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT \n" + " e.employee_id,\n" + " d.locatioin_id\n" + "FROM employees e,\n" + " departments d\n" + "ORDER BY\n" + " e.employee_id,\n" + " d.locatioin_id,\n" + " d.locatioin_id2", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testIndentLen()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "DECLARE @s VARCHAR(1000), @s2 VARCHAR(10)";
sqlparser.parse();
option.linebreakAfterDeclare = true;
option.indentLen = 3;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("DECLARE\n" + " @s VARCHAR(1000),\n" + " @s2 VARCHAR(10)", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testCTE_NewlineBeforeAs()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "WITH mycte(x) AS (SELECT x = Convert( VARCHAR(1000), 'hello' ) UNION ALL \n" + " SELECT Convert( VARCHAR(1000), x + 'a' ) FROM mycte \n" + " WHERE Len( x ) < 10 UNION ALL SELECT Convert( VARCHAR(1000), x + 'b' ) \n" + " FROM mycte WHERE Len( x ) < 10)\n" + "SELECT x FROM mycte ORDER BY Len( x ), x; ";
sqlparser.parse();
option.cteNewlineBeforeAs = false;
string result = FormatterFactory.pp(sqlparser, option);
//Console.WriteLine(result);
Assert.IsTrue(result.Trim().Equals("WITH mycte(x) AS (SELECT x = CONVERT(VARCHAR(1000), 'hello')\n UNION ALL\n SELECT CONVERT(VARCHAR(1000), x + 'a')\n FROM mycte\n WHERE len(x) < 10\n UNION ALL\n SELECT CONVERT(VARCHAR(1000), x + 'b')\n FROM mycte\n WHERE len(x) < 10) \n SELECT x\n FROM mycte\n ORDER BY len(x),\n x;", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testDefaultCommaOption()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "CREATE TABLE dept(deptno NUMBER(2),\n" + " dname VARCHAR2(14),\n" + " loc VARCHAR2(13)) ";
sqlparser.parse();
option.defaultAligntype = TAlignStyle.AsStacked;
option.defaultCommaOption = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("CREATE TABLE dept(deptno NUMBER(2)\n" + " , dname VARCHAR2(14)\n" + " , loc VARCHAR2(13))", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public static string formatSql(EDbVendor dbVendor, string inputQuery)
{
string Result = inputQuery;
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
sqlparser.sqltext = inputQuery;
int ret = sqlparser.parse();
if (ret == 0)
{
GFmtOpt option = GFmtOptFactory.newInstance();
option.caseFuncname = TCaseOption.CoNoChange;
Result = FormatterFactory.pp(sqlparser, option);
}
return(Result);
}
public void testEloPreserve()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE FUNCTION dbo.isoweek (@DATE datetime)\n" + "RETURNS INT\n" + "WITH EXECUTE AS caller\n" + "AS\n" + "BEGIN\n" + " DECLARE @ISOweek INT\n" + " \n" + " \n" + " SET @ISOweek= datepart(wk,@DATE)+1\n" + " -datepart(wk,CAST(datepart(yy,@DATE) AS CHAR(4))+'0104')\n" + " \n" + "--Special cases: Jan 1-3 may belong to the previous year\n" + " IF (@ISOweek=0)\n" + " SET @ISOweek=dbo.isoweek(CAST(datepart(yy,@DATE)-1\n" + " AS CHAR(4))+'12'+ CAST(24+datepart(DAY,@DATE) AS CHAR(2)))+1\n" + "--Special case: Dec 29-31 may belong to the next year\n" + " IF ((datepart(mm,@DATE)=12) AND\n" + " ((datepart(dd,@DATE)-datepart(dw,@DATE))>= 28))\n" + " SET @ISOweek=1\n" + " \n" + " \n" + " \n" + " \n" + " RETURN(@ISOweek)\n" + "END;\n" + "GO ";
sqlparser.parse();
option.emptyLines = TEmptyLinesOption.EloPreserve;
//option.insertBlankLineInBatchSqls = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION dbo.Isoweek (@DATE DATETIME\n" + ") \n" + "RETURNS INT WITH EXECUTE AS caller \n" + "AS \n" + " BEGIN \n" + " DECLARE @ISOweek INT \n" + "\n" + " SET @ISOweek= Datepart(wk,@DATE) + 1 - Datepart(wk,Cast(Datepart(yy,@DATE) AS CHAR(4))+'0104') \n" + "\n" + "--Special cases: Jan 1-3 may belong to the previous year\n" + " IF ( @ISOweek = 0 ) SET @ISOweek=dbo.Isoweek(Cast(Datepart(yy,@DATE)-1 AS CHAR(4))+'12'+ Cast(24+Datepart(DAY,@DATE) AS CHAR(2))) + 1 \n" + "--Special case: Dec 29-31 may belong to the next year\n" + " IF ( ( Datepart(mm,@DATE) = 12 )\n" + " AND ( ( Datepart(dd,@DATE) - Datepart(dw,@DATE) ) >= 28 ) ) SET @ISOweek=1 \n" + "\n" + "\n" + "\n" + "\n" + " RETURN(@ISOweek) \n" + " END;\n" + "GO", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
public void testSelect_Columnlist_Comma()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "select col1, col2,sum(col3) from table1";
sqlparser.parse();
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
option.selectColumnlistComma = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Equals("SELECT col1\n" + " , col2\n" + " , Sum(col3)\n" + "FROM table1", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
/// <summary>
/// don't use BEStyle_Function_BodyIndent, replace it by beStyleBlockIndentSize
/// </summary>
public void testBEStyle_Function_BodyIndent()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE PROCEDURE humanresources.Uspgetallemployees \n" + "AS \n" + "SELECT lastname,\n" + "firstname,\n" + "jobtitle,\n" + "department \n" + "FROM humanresources.vemployeedepartment; \n";
sqlparser.parse();
option.beStyleBlockIndentSize = 3;
string result = FormatterFactory.pp(sqlparser, option);
//System.out.println(result);
Assert.IsTrue(result.Trim().Equals("CREATE PROCEDURE humanresources.uspgetallemployees \n" + "AS \n" + " SELECT lastname,\n" + " firstname,\n" + " jobtitle,\n" + " department\n" + " FROM humanresources.vemployeedepartment;", StringComparison.OrdinalIgnoreCase));
}
public void testCreatetable_Fieldlist_Align_option()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "CREATE TABLE dept(deptno NUMBER(2),\n" + " dname VARCHAR2(14),\n" + " loc VARCHAR2(13)) ";
sqlparser.parse();
option.createtableFieldlistAlignOption = TAlignOption.AloRight;
option.beStyleCreatetableLeftBEOnNewline = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("CREATE TABLE dept\n" + " (deptno NUMBER(2),\n" + " dname VARCHAR2(14),\n" + " loc VARCHAR2(13))", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testDefaultCommaOption()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "INSERT INTO employees\n" + " (employee_id,\n" + " first_name,\n" + " department_id) VALUES (113, NULL, 100);";
option.defaultCommaOption = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
option.insertColumnlistStyle = TAlignStyle.AsStacked;
option.insertValuelistStyle = TAlignStyle.AsStacked;
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("INSERT INTO employees\n" + " (employee_id\n" + " , first_name\n" + " , department_id)\n" + "VALUES (113\n" + " , NULL\n" + " , 100);", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
static void outputPlainFormat(TGSqlParser parser)
{
int ret = parser.parse();
if (ret == 0)
{
GFmtOpt option = GFmtOptFactory.newInstance();
string result = FormatterFactory.pp(parser, option);
Console.WriteLine(result);
}
else
{
Console.WriteLine(parser.Errormessage);
}
}
public void testAlignJoinWithFromKeyword()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "SELECT p.name AS product,\n" + " p.listprice AS 'List Price',\n" + " p.discount AS 'discount' \n" + "FROM \n" + " production.product p \n" + " JOIN production.productsubcategory s \n" + " ON p.productsubcategoryid = s.productsubcategoryid \n" + "WHERE s.name LIKE @product \n" + " AND p.listprice < @maxprice;";
sqlparser.parse();
option.alignJoinWithFromKeyword = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT p.name AS product,\n" + " p.listprice AS 'List Price',\n" + " p.discount AS 'discount'\n" + "FROM production.product p\n" + "JOIN production.productsubcategory s\n" + " ON p.productsubcategoryid = s.productsubcategoryid\n" + "WHERE s.name LIKE @product\n" + " AND p.listprice < @maxprice;", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testFromClauseInNewLine()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT last_name,\n" + " department_name dept_name \n" + "FROM employees,\n" + " departments; ";
sqlparser.parse();
option.fromClauseInNewLine = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT last_name,\n" + " department_name dept_name\n" + "FROM \n" + " employees,\n" + " departments;", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testlinenumber_enabled()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "select department_id,\n" + " min( salary ) -- single line comment \n" + "from employees \n" + "group by department_id";
sqlparser.parse();
option.linenumberEnabled = true;
option.linenumberLeftMargin = 1;
option.linenumberRightMargin = 4;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Equals(" 1 SELECT department_id,\n" + " 2 Min(salary) -- single line comment \n" + " 3 FROM employees\n" + " 4 GROUP BY department_id", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
public void testEmptyLines2()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE FUNCTION dbo.isoweek (@DATE datetime)\n" + "RETURNS INT\n" + "WITH EXECUTE AS caller\n" + "AS\n" + "BEGIN\n" + " DECLARE @ISOweek INT\n" + " \n" + " \n" + " SET @ISOweek= datepart(wk,@DATE)+1\n" + " -datepart(wk,CAST(datepart(yy,@DATE) AS CHAR(4))+'0104')\n" + " \n" + "--Special cases: Jan 1-3 may belong to the previous year\n" + " IF (@ISOweek=0)\n" + " SET @ISOweek=dbo.isoweek(CAST(datepart(yy,@DATE)-1\n" + " AS CHAR(4))+'12'+ CAST(24+datepart(DAY,@DATE) AS CHAR(2)))+1\n" + "--Special case: Dec 29-31 may belong to the next year\n" + " IF ((datepart(mm,@DATE)=12) AND\n" + " ((datepart(dd,@DATE)-datepart(dw,@DATE))>= 28))\n" + " SET @ISOweek=1\n" + " \n" + " \n" + " \n" + " \n" + " RETURN(@ISOweek)\n" + "END;\n" + "GO ";
sqlparser.parse();
option.emptyLines = TEmptyLinesOption.EloMergeIntoOne;
// option.emptyLines = TEmptyLinesOption.EloPreserve;
option.insertBlankLineInBatchSqls = true;
string result = FormatterFactory.pp(sqlparser, option);
// Assert.IsTrue("EmptyLines,insertBlankLineInBatchSqls can't be tested due to format in stored procedure not work",false);
Console.WriteLine("EmptyLines,insertBlankLineInBatchSqls can't be tested due to format in stored procedure not work");
}
public void testIfElse()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "DECLARE @compareprice money, @cost money \n" + "EXECUTE Production.uspGetList '%Bikes%', 700, \n" + " @compareprice OUT, \n" + " @cost OUTPUT\n" + "IF @cost <= @compareprice \n" + "BEGIN\n" + " PRINT 'These products can be purchased for less than \n" + " $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'\n" + "END\n" + "ELSE\n" + " PRINT 'The prices for all products in this category exceed \n" + " $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'";
sqlparser.parse();
option.emptyLines = TEmptyLinesOption.EloMergeIntoOne;
option.insertBlankLineInBatchSqls = true;
//option.noEmptyLinesBetweenMultiSetStmts = false;
string result = FormatterFactory.pp(sqlparser, option);
// System.out.println(result);
Assert.IsTrue(result.Trim().Equals("DECLARE @compareprice MONEY,\n" + " @cost MONEY \n" + "\n" + "EXECUTE production.uspgetlist \n" + " '%Bikes%',\n" + " 700,\n" + " @compareprice OUT,\n" + " @cost OUTPUT \n" + "\n" + "IF @cost <= @compareprice \n" + " BEGIN \n" + " PRINT 'These products can be purchased for less than \n" + " $'+Rtrim(Cast(@compareprice AS VARCHAR(20)))+'.' \n" + " END \n" + "ELSE \n" + " PRINT 'The prices for all products in this category exceed \n" + " $'+ Rtrim(Cast(@compareprice AS VARCHAR(20)))+'.'", StringComparison.OrdinalIgnoreCase));
}
/// <summary>
/// turn sql into a single line, or multiple lines with a fixed line width
/// no need to format this sql
/// be careful when there are single line comment in SQL statement.
/// </summary>
public void testSingleLine()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "select department_id,\n" + " min( salary ) -- single line comment \n" + "from employees \n" + "group by department_id";
sqlparser.parse();
option.compactMode = TCompactMode.Cpmugly;
option.lineWidth = 60;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT department_id, Min( salary ) \n" + "/* -- single line comment */ FROM employees GROUP BY \n" + "department_id", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testDecode()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
option.functionCallParametersStyle = TAlignStyle.AsStacked;
option.functionCallParametersComma = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "SELECT last_name,\n" + " DECODE(job_id, 'It_prog', 1.10 * salary, 'st_clerk', 1.15 * salary, 'sa_rep', 1.20 * salary,\n" + " salary) revised_salary\n" + "FROM employees;";
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT last_name,\n" + " Decode(job_id\n" + " , 'It_prog'\n" + " , 1.10 * salary\n" + " , 'st_clerk'\n" + " , 1.15 * salary\n" + " , 'sa_rep'\n" + " , 1.20 * salary\n" + " , salary) revised_salary\n" + "FROM employees;", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
public void testParameters()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
option.functionCallParametersStyle = TAlignStyle.AsStacked;
option.functionCallParametersComma = TLinefeedsCommaOption.LfBeforeComma;
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "SET @a = dbo.Func1(@param1, @param2, @param3 + 1,\n" + " @param4) ";
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SET @a = dbo.Func1(@param1\n" + " ,@param2\n" + " ,@param3 + 1\n" + " ,@param4)", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testremove_comment()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
option.removeComment = true;
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "select department_id,\n" + " min( salary ) -- single line comment \n" + "from employees \n" + "group by department_id";
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT department_id,\n" + " Min(salary) \n" + "FROM employees\n" + "GROUP BY department_id", StringComparison.OrdinalIgnoreCase));
//Assert.IsTrue("remove_comment is not supported",false);
Console.WriteLine(result);
}
public void testIndent_CaseFromSwitch()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "SELECT productnumber,\n" + " name,\n" + " 'Price Range' = CASE WHEN listprice = 0 THEN 'Mfg item - not for resale' \n" + " WHEN listprice < 50 THEN 'Under $50' \n" + " WHEN listprice >= 50 AND listprice < 250 \n" + " THEN 'Under $250' WHEN listprice >= 250 \n" + " AND listprice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END \n" + "FROM production.product \n" + "ORDER BY productnumber; ";
sqlparser.parse();
option.caseWhenThenInSameLine = false;
option.indentCaseFromSwitch = 4;
option.indentCaseThen = 2;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT productnumber,\n" + " name,\n" + " 'Price Range' = CASE\n" + " WHEN listprice = 0\n" + " THEN 'Mfg item - not for resale'\n" + " WHEN listprice < 50\n" + " THEN 'Under $50'\n" + " WHEN listprice >= 50\n" + " AND listprice < 250\n" + " THEN 'Under $250'\n" + " WHEN listprice >= 250\n" + " AND listprice < 1000\n" + " THEN 'Under $1000'\n" + " ELSE 'Over $1000'\n" + " END\n" + "FROM production.product\n" + "ORDER BY productnumber;", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testParameters_Style()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE FUNCTION dbo.Fn_gettoporders(@custid AS INT, @n AS INT, @test AS CHAR) RETURNS TABLE \n" + "AS \n" + " RETURN SELECT TOP(@n) * FROM sales.salesorderheader WHERE customerid = @custid \n" + " ORDER BY totaldue DESC ";
sqlparser.parse();
option.parametersStyle = TAlignStyle.AsWrapped;
option.beStyleFunctionRightBEOnNewline = false;
string result = FormatterFactory.pp(sqlparser, option);
//System.out.println(result);
Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION dbo.Fn_gettoporders(@custid AS INT, @n AS INT, @test AS CHAR) \n" + "RETURNS TABLE \n" + "AS \n" + " RETURN \n" + " SELECT TOP(@n) *\n" + " FROM sales.salesorderheader\n" + " WHERE customerid = @custid\n" + " ORDER BY totaldue DESC", StringComparison.OrdinalIgnoreCase));
}
public void testMultiStatements()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)\n" + "RETURNS @retFindReports TABLE \n" + "(\n" + " EmployeeID int primary key NOT NULL,\n" + " Name nvarchar(255) NOT NULL,\n" + " Title nvarchar(50) NOT NULL,\n" + " EmployeeLevel int NOT NULL,\n" + " Sort nvarchar (255) NOT NULL\n" + ")\n" + "--Returns a result set that lists all the employees who report to the \n" + "--specific employee directly or indirectly.*/\n" + "AS\n" + "BEGIN\n" + " WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS\n" + " (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),\n" + " e.Title,\n" + " e.EmployeeID,\n" + " 1,\n" + " CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)\n" + " FROM HumanResources.Employee AS e\n" + " JOIN Person.Contact AS c ON e.ContactID = c.ContactID \n" + " WHERE e.EmployeeID = @InEmpID\n" + " UNION ALL\n" + " SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +\n" + " c.FirstName + ' ' + c.LastName),\n" + " e.Title,\n" + " e.EmployeeID,\n" + " EmployeeLevel + 1,\n" + " CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + \n" + " LastName)\n" + " FROM HumanResources.Employee as e\n" + " JOIN Person.Contact AS c ON e.ContactID = c.ContactID\n" + " JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID\n" + " )\n" + "-- copy the required columns to the result of the function \n" + " INSERT @retFindReports\n" + " SELECT EmployeeID, Name, Title, EmployeeLevel, Sort\n" + " FROM DirectReports \n" + " RETURN\n" + "END;\n" + "GO\n" + "-- Example invocation\n" + "SELECT EmployeeID, Name, Title, EmployeeLevel\n" + "FROM dbo.fn_FindReports(109)\n" + "ORDER BY Sort;";
sqlparser.parse();
option.parametersStyle = TAlignStyle.AsStacked;
option.parametersComma = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
option.beStyleFunctionRightBEOnNewline = false;
string result = FormatterFactory.pp(sqlparser, option);
Console.WriteLine("sql statements inside stored procedure not format correctly");
//Assert.IsTrue("sql statements inside stored procedure not format correctly",false);
}
public void testCreateProcedure_includes_create_index()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE PROCEDURE [dbo].[GenerateTempBondDetail]\n" + "AS\n" + "BEGIN\n" + " CREATE CLUSTERED INDEX ##PK_BondDetail2\n" + " ON dbo.##BondDetail2( Index )\n" + " ON [PRIMARY]\n" + "END";
sqlparser.parse();
option.parametersStyle = TAlignStyle.AsStacked;
option.parametersComma = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
option.beStyleFunctionRightBEOnNewline = false;
string result = FormatterFactory.pp(sqlparser, option);
//System.out.println(result);
Assert.IsTrue(result.Trim().Equals("CREATE PROCEDURE [dbo].[GenerateTempBondDetail] \n" + "AS \n" + " BEGIN \n" + " CREATE CLUSTERED INDEX ##pk_bonddetail2 ON dbo.##bonddetail2( index ) ON [PRIMARY] \n" + " END", StringComparison.OrdinalIgnoreCase));
}
public void testSelect_Columnlist_Comma()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT e.employee_id,\n" + " d.locatioin_id \n" + "FROM employees e,departments d \n" + "group by e.employee_id,d.locatioin_id,d.locatioin_id2 ";
sqlparser.parse();
option.selectColumnlistStyle = TAlignStyle.AsStacked;
option.selectColumnlistComma = TLinefeedsCommaOption.LfbeforeCommaWithSpace;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT e.employee_id\n" + " , d.locatioin_id\n" + "FROM employees e,\n" + " departments d\n" + "GROUP BY e.employee_id\n" + " , d.locatioin_id\n" + " , d.locatioin_id2", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testNoEmptyLinesBetweenMultiSetStmts()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "SET @A = @B\n" + "SET @C = @D\n" + "SET @E = @F\n\n" + "SET @G = @H ";
sqlparser.parse();
option.emptyLines = TEmptyLinesOption.EloRemove;
option.insertBlankLineInBatchSqls = true;
option.noEmptyLinesBetweenMultiSetStmts = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SET @A = @B \n" + "SET @C = @D \n" + "SET @E = @F \n" + "SET @G = @H", StringComparison.OrdinalIgnoreCase));
// Assert.IsTrue("NoEmptyLinesBetweenMultiSetStmts not worked correctly",false);
// System.out.println(result);
}
public void testLinebreakBeforeParamInExec()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name+"."+ new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "EXEC Sptrackmember @p_member_id, '2.2', @p_weeknum ";
sqlparser.parse();
option.linebreakBeforeParamInExec = false;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("EXEC sptrackmember @p_member_id, '2.2', @p_weeknum", StringComparison.OrdinalIgnoreCase));
sqlparser.parse();
option.linebreakBeforeParamInExec = true;
result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("EXEC sptrackmember \n" + " @p_member_id,\n" + " '2.2',\n" + " @p_weeknum", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testMssqlCreateFunction_BlockStmt()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "create function infFKfrom(@tbID int, @colID smallint) returns varchar(2000) as \n" + "begin declare @r varchar(2000), @a varchar(200)\n" + "select @r='', @a=''\n" + "DECLARE cs CURSOR FOR\n" + " select FKfrom=convert(varchar(200),object_name(rkeyid)+'.'+r.[name])\n" + " from sysforeignkeys c join syscolumns f on c.fkeyid=f.[id] and c.fkey=f.colID\n" + "join syscolumns r on c.rkeyid=r.[id] and c.rkey=r.colID where fkeyID=@tbID and fkey=@colID\n" + " order by keyNo\n" + "\n" + "OPEN cs\n" + " FETCH NEXT FROM cs INTO @a\n" + "WHILE @@FETCH_STATUS = 0 BEGIN\n" + "select @r=@r+case when len(@r)>0 then ', ' else '' end+@a\n" + "FETCH NEXT FROM cs INTO @a\n" + "END\n" + " CLOSE cs\n" + " DEALLOCATE cs\n" + " return(@r)\n" + "end\n" + "GO";
sqlparser.parse();
option.beStyleBlockLeftBEOnNewline = true;
option.beStyleBlockLeftBEIndentSize = 2;
option.beStyleBlockIndentSize = 3;
string result = FormatterFactory.pp(sqlparser, option);
//Console.WriteLine(result);
//Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION Inffkfrom(@tbID INT,\n" + " @colID SMALLINT\n" + ") \n" + "RETURNS VARCHAR(2000) \n" + "AS \n" + " BEGIN \n" + " DECLARE @r VARCHAR(2000),\n" + " @a VARCHAR(200) \n" + " SELECT @r = '',\n" + " @a = '' \n" + " DECLARE cs CURSOR FOR SELECT fkfrom = Convert(VARCHAR(200),Object_name(rkeyid)+'.'+r.[name])\n" + " FROM sysforeignkeys c\n" + " JOIN syscolumns f\n" + " ON c.fkeyid=f.[id] AND c.fkey=f.colid\n" + " JOIN syscolumns r\n" + " ON c.rkeyid=r.[id] AND c.rkey=r.colid\n" + " WHERE fkeyid = @tbID\n" + " AND fkey = @colID\n" + " ORDER BY keyno \n" + "\n" + " OPEN cs \n" + " FETCH NEXT FROM cs INTO @a \n" + " WHILE @@FETCH_STATUS = 0 \n" + " BEGIN \n" + " SELECT @r = @r + CASE\n" + " WHEN Len(@r) > 0\n" + " THEN ', '\n" + " ELSE ''\n" + " END + @a \n" + " FETCH NEXT FROM cs INTO @a \n" + " END \n" + " CLOSE cs \n" + " DEALLOCATE cs \n" + " RETURN(@r) \n" + " END\n" + "GO", StringComparison.OrdinalIgnoreCase));
Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION INFFKFROM(@tbID INT,\n @colID SMALLINT\n) \nRETURNS VARCHAR(2000) \nAS \n BEGIN \n DECLARE @r VARCHAR(2000),\n @a VARCHAR(200) \n SELECT @r = '',\n @a = '' \n DECLARE cs CURSOR FOR SELECT fkfrom = CONVERT(VARCHAR(200),object_name(rkeyid) + '.' + r.[name])\n FROM sysforeignkeys c\n JOIN syscolumns f\n ON c.fkeyid = f.[id]\n AND c.fkey = f.colid\n JOIN syscolumns r\n ON c.rkeyid = r.[id]\n AND c.rkey = r.colid\n WHERE fkeyid = @tbID\n AND fkey = @colID\n ORDER BY keyno \n\n OPEN cs \n FETCH NEXT FROM cs INTO @a \n WHILE @@FETCH_STATUS = 0 \n BEGIN \n SELECT @r = @r + CASE\n WHEN len(@r) > 0\n THEN ', '\n ELSE ''\n END + @a \n FETCH NEXT FROM cs INTO @a \n END \n CLOSE cs \n DEALLOCATE cs \n RETURN(@r) \n END\nGO", StringComparison.OrdinalIgnoreCase));
}
public void testBEStyle_Block_leftBEIndentSize()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE PROCEDURE humanresources.Uspgetallemployees \n" + "AS \n" + "BEGIN \n" + "SELECT lastname,\n" + "firstname,\n" + "jobtitle,\n" + "department \n" + "FROM humanresources.vemployeedepartment; \n" + "END \n";
sqlparser.parse();
option.beStyleBlockLeftBEOnNewline = true; // begin keyword in new line
option.beStyleBlockLeftBEIndentSize = 2; // offset of begin keyword
option.beStyleBlockIndentSize = 1; // code indentation inside begin/end block
string result = FormatterFactory.pp(sqlparser, option);
//System.out.println(result);
Assert.IsTrue(result.Trim().Equals("CREATE PROCEDURE humanresources.uspgetallemployees \n" + "AS \n" + " BEGIN \n" + " SELECT lastname,\n" + " firstname,\n" + " jobtitle,\n" + " department\n" + " FROM humanresources.vemployeedepartment; \n" + " END", StringComparison.OrdinalIgnoreCase));
}
public void testAllUnchanged()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "Select department_id,\n" + " miN( Salary ) \n" + "from employees \n" + "GROUP by department_id";
sqlparser.parse();
option.caseDatatype = TCaseOption.CoNoChange;
option.caseFuncname = TCaseOption.CoNoChange;
option.caseIdentifier = TCaseOption.CoNoChange;
option.caseKeywords = TCaseOption.CoNoChange;
option.caseQuotedIdentifier = TCaseOption.CoNoChange;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("Select department_id,\n" + " miN(Salary)\n" + "from employees\n" + "GROUP by department_id", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}