public void testSelectItemInNewLine()
{
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.selectItemInNewLine = true;
string result = FormatterFactory.pp(sqlparser, option);
// System.out.println(result);
Assert.IsTrue(result.Equals("SELECT\n" + " col1,\n" + " col2,\n" + " Sum(col3)\n" + "FROM table1", StringComparison.OrdinalIgnoreCase));
TGSqlParser sqlparser2 = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser2.sqltext = "select top 10 col1 as b, col2222 as c,sum(col3) as d from table1";
option.selectItemInNewLine = true;
option.alignAliasInSelectList = true;
sqlparser2.parse();
result = FormatterFactory.pp(sqlparser2, option);
//System.out.println(result);
Assert.IsTrue(result.Equals("SELECT top 10\n" + " col1 AS b,\n" + " col2222 AS c,\n" + " Sum(col3) AS d\n" + "FROM table1", StringComparison.OrdinalIgnoreCase));
}
public void testSelect_keywords_alignOption()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "DELETE FROM job_history jh \n" + "WHERE employee_id = (SELECT employee_id \n" + "FROM employee e \n" + "WHERE jh.employee_id = e.employee_id \n" + "AND start_date = (SELECT Min(start_date) \n" + "FROM job_history jh \n" + "WHERE jh.employee_id = e.employee_id) \n" + "AND 5 > (SELECT Count( * ) \n" + "FROM job_history jh \n" + "WHERE jh.employee_id = e.employee_id \n" + "GROUP BY employee_id \n" + "HAVING Count( * ) >= 4)); ";
sqlparser.parse();
option.selectKeywordsAlignOption = TAlignOption.AloRight;
string result = FormatterFactory.pp(sqlparser, option);
//Console.WriteLine(result);
//Console.WriteLine("DELETE FROM job_history jh\n" + " WHERE employee_id = (SELECT employee_id\n" + " FROM employee e\n" + " WHERE jh.employee_id = e.employee_id\n" + " AND start_date = (SELECT Min(start_date)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id)\n" + " AND 5 > ( SELECT Count(*)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id\n" + " GROUP BY employee_id\n" + " HAVING Count(*) >= 4));");
// Assert.IsTrue(result.Trim().Equals("DELETE FROM job_history jh\n" + " WHERE employee_id = (SELECT employee_id\n" + " FROM employee e\n" + " WHERE jh.employee_id = e.employee_id\n" + " AND start_date = (SELECT Min(start_date)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id)\n" + " AND 5 > ( SELECT Count(*)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id\n" + " GROUP BY employee_id\n" + " HAVING Count(*) >= 4));", StringComparison.OrdinalIgnoreCase));
// Assert.IsTrue(result.trim().equalsIgnoreCase("DELETE FROM job_history jh\n" +
// " WHERE employee_id = (SELECT employee_id\n" +
// " FROM employee e\n" +
// " WHERE jh.employee_id = e.employee_id\n" +
// " AND start_date = (SELECT Min(start_date)\n" +
// " FROM job_history jh\n" +
// " WHERE jh.employee_id = e.employee_id)\n" +
// " AND 5 > ( SELECT Count(*)\n" +
// " FROM job_history jh\n" +
// " WHERE jh.employee_id = e.employee_id\n" +
// " GROUP BY employee_id HAVING Count(*) >= 4));"));
// System.out.println(result);
}
public void testEmptyLines3()
{
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.EloRemove;
option.insertBlankLineInBatchSqls = false;
string result = FormatterFactory.pp(sqlparser, option);
// Assert.IsTrue(result.trim().equalsIgnoreCase("CREATE FUNCTION dbo.Isoweek (@DATE DATETIME\n" +
// ") \n" +
// "RETURNS INT WITH EXECUTE AS caller \n" +
// "AS \n" +
// " BEGIN \n" +
// " DECLARE @ISOweek INT \n" +
// " SET @ISOweek= Datepart(wk,@DATE) + 1 - Datepart(wk,Cast(Datepart(yy,@DATE) AS CHAR(4))+'0104') \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" +
// " RETURN(@ISOweek) \n" +
// " END;\n" +
// "GO"));
Console.WriteLine("this is a bug, need to be fixed");
}
public FormatSqlResponse FormatSQl([FromBody] FormatSqlRequest request)
{
string formattedText = _memoryCache.GetOrCreate("SQL_" + request.QueryId, entry => {
StoreKey(entry);
entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
string text = string.Empty;
_connectionProvider.GetConnection(c => {
text = c.QuerySingle <string>("SELECT NormalizedQuery FROM NormQueryTextHistory WHERE Id = @id", new
{
id = request.QueryId
});
});
try
{
var parser = new TGSqlParser(EDbVendor.dbvmssql)
{
sqltext = text
};
parser.parse();
GFmtOpt option = GFmtOptFactory.newInstance();
option.outputFmt = GOutputFmt.ofSql;
string result = FormatterFactory.pp(parser, option);
return(result);
}
catch (Exception)
{
return("Error while formatting sql" + text);
}
});
return(new FormatSqlResponse
{
Result = formattedText
});
}
/// <summary>
/// Not support option.treatDistinctAsVirtualColumn yet
/// </summary>
public void testTreatDistinctAsVirtualColumn()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "select distinct col1 as b, col2222 as c,sum(col3) as d from table1";
option.treatDistinctAsVirtualColumn = true;
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
// System.out.println(result);
Assert.IsTrue(result.Trim().Equals("SELECT DISTINCT \n" + " col1 AS b,\n" + " col2222 AS c,\n" + " Sum(col3) AS d\n" + "FROM table1", StringComparison.OrdinalIgnoreCase));
}
public void testBigScript()
{
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 MSupdatescdata\n" + " @logical_record_parent_nickname int,\n" + " @logical_record_parent_rowguid uniqueidentifier,\n" + " @replnick binary(6),\n" + " @parent_row_inserted bit output\n" + "as\n" + " declare @logical_record_parent_oldmaxversion int,\n" + " @logical_record_lineage varbinary(311),\n" + " @logical_record_parent_regular_lineage varbinary(311),\n" + " @logical_record_parent_gencur bigint,\n" + " @rows_updated int,\n" + " @error int,\n" + " @logical_record_parent_pubid uniqueidentifier,\n" + " @logical_record_parent_objid int\n" + "\n" + " select top 1 @logical_record_parent_pubid = pubid, @logical_record_parent_objid = objid\n" + " from dbo.sysmergearticles\n" + " where nickname = @logical_record_parent_nickname\n" + " if (isnull(permissions(@logical_record_parent_objid), 0) & 0x1b = 0 and {fn ISPALUSER(@logical_record_parent_pubid)} <> 1)\n" + " begin\n" + " raiserror(15247, 11, -1)\n" + " return 1\n" + " end\n" + " \n" + " select @parent_row_inserted = 0\n" + " \n" + " if @logical_record_parent_rowguid is null\n" + " return 0\n" + " \n" + " select top 1 @logical_record_parent_oldmaxversion = maxversion_at_cleanup\n" + " from dbo.sysmergearticles\n" + " where nickname = @logical_record_parent_nickname\n" + "\n" + " exec sys.sp_MSmerge_getgencur @logical_record_parent_nickname, 1, @logical_record_parent_gencur output\n" + " \n" + " update dbo.MSmerge_tombstone\n" + " set logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @logical_record_parent_oldmaxversion+1) }\n" + " where tablenick = @logical_record_parent_nickname\n" + " and rowguid = @logical_record_parent_rowguid\n" + " select @rows_updated = @@rowcount, @error = @@error\n" + " \n" + " if @error <> 0\n" + " return 1\n" + " \n" + " if @rows_updated = 1\n" + " return 0 -- no need to insert tombstone if rows_updated = 0. This proc is never called to insert a parent's tombstone.\n" + " \n" + " update dbo.MSmerge_contents\n" + " set logical_record_lineage = { fn UPDATELINEAGE(logical_record_lineage, @replnick, @logical_record_parent_oldmaxversion+1) }\n" + " where tablenick = @logical_record_parent_nickname\n" + " and rowguid = @logical_record_parent_rowguid\n" + " select @rows_updated = @@rowcount, @error = @@error\n" + " \n" + " if @error <> 0\n" + " return 1\n" + " \n" + " if @rows_updated = 0\n" + " begin\n" + " select @logical_record_lineage = { fn UPDATELINEAGE(0x0, @replnick, @logical_record_parent_oldmaxversion+1) }\n" + " \n" + " -- if no cleanup done yet, use 1 as the version.\n" + " if @logical_record_parent_oldmaxversion = 1\n" + " select @logical_record_parent_regular_lineage = { fn UPDATELINEAGE(0x0, @replnick, 1) }\n" + " else\n" + " select @logical_record_parent_regular_lineage = @logical_record_lineage\n" + " \n" + " insert into dbo.MSmerge_contents (tablenick, rowguid, lineage, colv1, generation, partchangegen, \n" + " logical_record_parent_rowguid, logical_record_lineage)\n" + " values (@logical_record_parent_nickname, @logical_record_parent_rowguid, @logical_record_parent_regular_lineage,\n" + " 0x00, @logical_record_parent_gencur, NULL, @logical_record_parent_rowguid, @logical_record_lineage)\n" + " if @@error <> 0\n" + " return 1\n" + " \n" + " select @parent_row_inserted = 1\n" + " end\n" + " \n" + " return 0\n" + "\n" + "\n" + "\n" + "create procedure cainfo\n" + " (@publisher sysname,\n" + " @publisher_db sysname,\n" + " @publication sysname,\n" + " @datasource_type int = 0, \n" + " @server_name sysname = NULL, \n" + " @db_name sysname = NULL, \n" + " @datasource_path nvarchar(255) = NULL, \n" + " @compatlevel int = 10) \n" + " \n" + "as\n" + " declare @retcode int\n" + " declare @repid uniqueidentifier\n" + " declare @pubid uniqueidentifier\n" + " declare @schemaguid uniqueidentifier\n" + " declare @replnick binary(6)\n" + " declare @subscription_type int\n" + " declare @validation_level int\n" + " declare @reptype int\n" + " declare @priority real\n" + " declare @schversion int\n" + " declare @status int\n" + " declare @resync_gen bigint\n" + " declare @replicastate uniqueidentifier\n" + " declare @sync_type tinyint\n" + " declare @description nvarchar(255)\n" + " declare @dynamic_snapshot_received int\n" + " declare @distributor sysname\n" + " declare @dometadata_cleanup int\n" + " declare @REPLICA_STATUS_Deleted tinyint\n" + " declare @REPLICA_STATUS_BeforeRestore tinyint\n" + " declare @REPLICA_STATUS_AttachFailed tinyint\n" + " declare @retention_period_unit tinyint\n" + " declare @islocalpubid bit, @islocalsubid bit, @cleanedup_unsent_changes bit\n" + " declare @last_sync_time datetime, @num_time_units_since_last_sync int\n" + " declare @supportability_mode int\n" + "\n" + " select @publisher_db = RTRIM(@publisher_db)\n" + " select @db_name = RTRIM(@db_name)\n" + " \n" + " exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, @publisher_db, @publisher, @pubid output\n" + " if @retcode <> 0 or @@error <> 0\n" + " return 1\n" + "\n" + " set @REPLICA_STATUS_Deleted= 2\n" + " set @REPLICA_STATUS_BeforeRestore= 7\n" + " set @REPLICA_STATUS_AttachFailed= 6\n" + "\n" + " if (@server_name is NULL)\n" + " SET @server_name = publishingservername()\n" + "\n" + " if (@db_name is NULL)\n" + " set @db_name = db_name()\n" + " \n" + " select @retention_period_unit = retention_period_unit from dbo.sysmergepublications\n" + " where pubid = @pubid\n" + "\n" + " SELECT @repid = subid, @replnick = replnickname, @priority = priority, @reptype = subscriber_type,\n" + " @subscription_type = subscription_type , @status = status, @replicastate = replicastate,\n" + " @schversion = schemaversion, @schemaguid = schemaguid,\n" + " @sync_type = sync_type, @description = description, @priority = priority,\n" + " @dometadata_cleanup = case when sys.fn_add_units_to_date(-1, @retention_period_unit, getdate()) > metadatacleanuptime then 1\n" + " else 0\n" + " end,\n" + " @last_sync_time = last_sync_date,\n" + " @supportability_mode = supportability_mode\n" + " FROM dbo.sysmergesubscriptions\n" + " WHERE UPPER(subscriber_server) collate database_default = UPPER(@server_name) collate database_default\n" + " and db_name = @db_name and pubid = @pubid\n" + " and status <> @REPLICA_STATUS_Deleted\n" + " and status <> @REPLICA_STATUS_BeforeRestore\n" + " and status <> @REPLICA_STATUS_AttachFailed\n" + " if @repid is NULL\n" + " begin\n" + " RAISERROR(20021, 16, -1)\n" + " return (1)\n" + " end\n" + "\n" + " select @validation_level=validation_level, @resync_gen=resync_gen\n" + " from dbo.MSmerge_replinfo\n" + " where repid = @repid\n" + "\n" + " select @distributor = NULL\n" + " select @publication = NULL\n" + " if @repid = @pubid\n" + " begin\n" + " select @publication = name, @distributor = distributor from dbo.sysmergepublications\n" + " where pubid = @pubid\n" + " end\n" + " \n" + " select @num_time_units_since_last_sync = sys.fn_datediff_units(@retention_period_unit, getdate(), @last_sync_time)\n" + "\n" + " if @repid <> @pubid\n" + " begin\n" + " update s\n" + " set s.application_name = p.program_name\n" + " from sys.dm_exec_sessions p, dbo.sysmergesubscriptions s where p.session_id = @@spid and s.subid = @repid\n" + " if @@error<>0\n" + " return 1\n" + " end\n" + "\n" + " if @compatlevel >= 90\n" + " begin\n" + " select @islocalpubid = sys.fn_MSmerge_islocalpubid(@pubid),\n" + " @islocalsubid = sys.fn_MSmerge_islocalsubid(@repid),\n" + " @cleanedup_unsent_changes = 0\n" + " \n" + " if @islocalsubid = 0\n" + " select @cleanedup_unsent_changes = cleanedup_unsent_changes\n" + " from dbo.sysmergesubscriptions\n" + " where subid = @repid -- right replica row\n" + " else\n" + " begin\n" + " if @islocalpubid = 0\n" + " select @cleanedup_unsent_changes = cleanedup_unsent_changes\n" + " from dbo.sysmergesubscriptions\n" + " where pubid = @pubid\n" + " and subid = @pubid -- we are interested in the cleanedup_unsent_changes bit from the publisher replica row\n" + " else\n" + " select @cleanedup_unsent_changes = 0 -- no way to tell which subscriber we are syncing with\n" + " end\n" + " \n" + " select @repid, @replnick,\n" + " @reptype, @subscription_type, @priority, @schversion, @schemaguid, @status, @replicastate,\n" + " @sync_type, @description, @publication, @distributor, @validation_level, @resync_gen, @dometadata_cleanup,\n" + " @pubid, @cleanedup_unsent_changes, @num_time_units_since_last_sync, @supportability_mode\n" + " end\n" + " else\n" + " begin\n" + " select @repid, {fn REPLNICK_90_TO_80(@replnick)},\n" + " @reptype, @subscription_type, @priority, @schversion, @schemaguid, @status, @pubid,\n" + " @sync_type, @description, @publication, @distributor, @validation_level, @resync_gen, @dometadata_cleanup\n" + "\n" + " end\n" + "\n" + " return (0)\n" + "\n" + "\n" + "create procedure MSgrdata\n" + " (@tablenick int,\n" + " @rowguid uniqueidentifier,\n" + " @lineage varbinary(311) output,\n" + " @colv varbinary(2953) output,\n" + " @pubid uniqueidentifier = NULL,\n" + " @compatlevel int = 10)\n" + "as\n" + " begin\n" + " if @compatlevel < 90\n" + " begin\n" + " declare @iscoltracked int\n" + " declare @cCols int\n" + " set @iscoltracked= sys.fn_fIsColTracked(@tablenick)\n" + " if @iscoltracked = 1\n" + " begin\n" + " set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)\n" + " end\n" + " end\n" + " select\n" + " @lineage= case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end,\n" + " @colv= case when @compatlevel >= 90 or @iscoltracked = 0 then colv1 else {fn COLV_90_TO_80(colv1, @cCols)} end\n" + " from dbo.MSmerge_contents\n" + " with (serializable)\n" + " where tablenick = @tablenick and rowguid = @rowguid\n" + " end\n" + "\n" + "create procedure sp_MSpttweight\n" + " @tablenick int,\n" + " @rowguid uniqueidentifier,\n" + " @acknowledge_only bit,\n" + " @rowvector varbinary(11)= null\n" + "as\n" + " set nocount on\n" + " \n" + " declare @METADATA_TYPE_DeleteLightweight tinyint\n" + " declare @METADATA_TYPE_DeleteLightweightProcessed tinyint\n" + " declare @pubnick tinyint \n" + " declare @removefrompartialvector binary(1)\n" + " declare @versionzerovector binary(11) \n" + " update dbo.MSmerge_rowtrack\n" + " set rowvector= case @acknowledge_only\n" + " when 1 then rowvector -- For a simple acknowledge, leave the vector alone.\n" + " else @rowvector\n" + " end,\n" + " changed= sys.fn_MSdayasnumber(getdate()) \n" + " where\n" + " tablenick = @tablenick and\n" + " rowguid = @rowguid and\n" + " sync_cookie = @pubnick and\n" + " (\n" + " 1=@acknowledge_only or\n" + " (\n" + " {fn GETMAXVERSION(\n" + " case\n" + " when rowvector is null then @versionzerovector\n" + " when rowvector = @removefrompartialvector then @versionzerovector\n" + " else rowvector\n" + " end)}\n" + " <= {fn GETMAXVERSION(isnull(@rowvector, @versionzerovector))}\n" + " )\n" + " )\n" + "\n" + " begin\n" + " update dbo.MSmerge_rowtrack\n" + " set sync_cookie= null,\n" + " changetype= case changetype\n" + " when @METADATA_TYPE_DeleteLightweight then @METADATA_TYPE_DeleteLightweightProcessed\n" + " else changetype\n" + " end,\n" + " changed= sys.fn_MSdayasnumber(getdate())\n" + " where\n" + " sync_cookie = @pubnick\n" + " begin\n" + " update dbo.MSmerge_rowtrack\n" + " set rowvector= @rowvector\n" + " where\n" + " tablenick = @tablenick and\n" + " (\n" + " {fn GETMAXVERSION(\n" + " case\n" + " when rowvector is null then @versionzerovector\n" + " else rowvector\n" + " end)}\n" + " <= {fn GETMAXVERSION(isnull(@rowvector, @versionzerovector))}\n" + " )\n" + "\n" + " end\n" + " end\n" + "\n" + "\n" + "create procedure sp_setap\n" + " @rolename sysname, -- name app role\n" + " @fCreateCookie bit = 0\n" + "as\n" + " set nocount on\n" + " if (@fCreateCookie = 1)\n" + " setuser @rolename \n" + " else\n" + " setuser @rolename \n" + " if (@@error <> 0)\n" + " return (1)\n" + " \n" + "return (0) \n" + "\n" + "\n" + "create procedure MSrepl_b_s\n" + "as\n" + " declare @sync_bit int\n" + " declare @dist_bit int\n" + " \n" + " begin\n" + " update MSrepl_backup_lsns set next_xact_id = t2.xact_id, next_xact_seqno = m.xact_seqno from\n" + " (select tm.publisher_database_id, max(tm.xact_seqno) from \n" + " ((select t.publisher_database_id, max(substring(t.xact_seqno, 1, 10)) from\n" + " MSrepl_transactions t where\n" + " not t.xact_id = 0x0\n" + " group by t.publisher_database_id, substring(t.xact_seqno, 1, 10)\n" + " having count(t.xact_seqno) < 2)\n" + " union \n" + " (select t.publisher_database_id, max(substring(xact_seqno, 1, 10)) from\n" + " MSrepl_transactions t where\n" + " not t.xact_id = 0x0\n" + " and t.xact_id = substring(t.xact_seqno, 1, 10)\n" + " group by t.publisher_database_id )\n" + " )as tm(publisher_database_id, xact_seqno)\n" + " group by tm.publisher_database_id\n" + " )as m(publisher_database_id, xact_seqno),\n" + " MSrepl_transactions t2 \n" + " \n" + " end\n" + " \n" + " return 0\n" + "\n" + "\n" + "create procedure MSrestorefk(\n" + " @program_name sysname = null\n" + ")as\n" + "begin\n" + " set nocount on\n" + " declare @retcode int,\n" + " @parent_name sysname,\n" + " @parent_schema sysname,\n" + " @referenced_object_id int\n" + " begin\n" + " select key_constraints.name\n" + " from sys.index_columns index_columns\n" + " inner join sys.key_constraints key_constraints\n" + " on indexes.name = key_constraints.name\n" + " where indexes.object_id = @referenced_object_id and indexes.index_id not in\n" + " (select index_columns.index_id\n" + " from sys.index_columns index_columns\n" + " left join\n" + " (select referenced_column_name, constraint_column_id\n" + " from dbo.MSsavedforeignkeycolumns\n" + " where program_name = @program_name\n" + " and parent_schema = @parent_schema) foreignkeycolumns (referenced_column_name, constraint_column_id)\n" + " on columns.name = foreignkeycolumns.referenced_column_name\n" + " and index_columns.key_ordinal = foreignkeycolumns.constraint_column_id\n" + " where index_columns.object_id = @referenced_object_id\n" + " and foreignkeycolumns.referenced_column_name is null\n" + " )\n" + " \n" + " \n" + " end\n" + "end\n" + "\n" + "\n" + "CREATE PROCEDURE MSindex\n" + " @tablename nvarchar(517), @index_name nvarchar(258) = NULL\n" + "AS\n" + "BEGIN\n" + " DECLARE @table_id int\n" + " DECLARE @pagesize int\n" + " \n" + " SELECT @table_id = id\n" + " FROM dbo.sysobjects\n" + " WHERE (id = object_id(@tablename))\n" + " AND ((OBJECTPROPERTY(id, N'IsTable') = 1) OR (OBJECTPROPERTY(id, N'IsView') = 1))\n" + " CHECKPOINT\n" + " \n" + "\n" + "END\n" + "\n" + "\n" + "create procedure vupgrade\n" + "as\n" + " begin\n" + " alter table dbo.sysmergearticles add default 0 for compensate_for_errors\n" + " end \n" + " begin\n" + " alter table dbo.MSmerge_errorlineage alter column lineage varbinary(311) not null\n" + " update dbo.MSmerge_errorlineage set lineage= {fn LINEAGE_80_TO_90(lineage)}\n" + " end\n" + "\n" + "CREATE UNIQUE CLUSTERED INDEX ucMSrepl_transactions ON dbo.MSrepl_transactions\n" + " (publisher_database_id, xact_seqno)\n" + " WITH STATISTICS_NORECOMPUTE\n" + " \n" + " ";
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
Console.WriteLine("not formatted correctly");
//Assert.IsTrue("not formatted correctly",false);
}
public void testInsert_Valuelist_Style()
{
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.insertValuelistStyle = TAlignStyle.AsWrapped;
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, NULL, 100);", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
static void outputHtmlFormat(TGSqlParser parser, GFmtOpt formatOption, bool rtf, bool custom)
{
int ret = parser.parse();
if (ret == 0)
{
if (formatOption != null)
{
formatOption.outputFmt = GOutputFmt.ofhtml;
}
else
{
formatOption = GFmtOptFactory.newInstance();
formatOption.outputFmt = GOutputFmt.ofhtml;
}
if (custom)
{
OutputConfig outputConfig = OutputConfigFactory.getOutputConfig(formatOption,
parser.DbVendor);
if (outputConfig is HtmlOutputConfig)
{
customOutputConfig((HtmlOutputConfig)outputConfig);
}
FormatterFactory.OutputConfig = outputConfig;
}
else
{
FormatterFactory.OutputConfig = new HtmlOutputConfig(formatOption, parser.DbVendor);
}
string result = FormatterFactory.pp(parser, formatOption);
if (rtf)
{
StringBuilder buffer = new StringBuilder(result.Replace("</br>", "<br>"));
ParameterizedThreadStart ps = new ParameterizedThreadStart(convertHtmlToRtf);
Thread t = new Thread(ps);
t.IsBackground = true;
t.SetApartmentState(ApartmentState.STA);
t.Start(buffer);
t.Join();
result = buffer.ToString();
}
Console.WriteLine(result);
}
else
{
Console.WriteLine(parser.Errormessage);
}
}
public void testWSPadding_OperatorArithmetic()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT * \n" + "FROM dual \n" + "WHERE 1=-1 \n" + " AND(1!=2 \n" + " OR 2^=3) \n" + " AND 3<>4 \n" + " AND 4>+5; ";
sqlparser.parse();
option.wsPaddingOperatorArithmetic = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT *\n" + "FROM dual\n" + "WHERE 1 = -1\n" + " AND ( 1 != 2\n" + " OR 2 ^= 3 )\n" + " AND 3 <> 4\n" + " AND 4 > +5;", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
public void testDefault()
{
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();
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);
}
/// <summary>
/// gFmtOpt.IntoClauseInNewline not implemented
/// No need to implement in this version as it not in document before.
/// </summary>
public void testSelectIntoClause()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "select col1, col2,sum(col3) INTO Persons_backup from table1";
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
//Assert.IsTrue("gFmtOpt.IntoClauseInNewline not implemented",false);
Console.WriteLine("gFmtOpt.IntoClauseInNewline not implemented");
}
public void testbegin_no_format()
{
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 uspnresults\n" + "AS\n" + "SELECT COUNT(contactid) FROM person.contact\n" + "--begin_no_format\n" + "SELECT COUNT(customerid) FROM \n" + "sales.customer;\n" + "--end_no_format\n" + "GO";
sqlparser.parse();
string result = FormatterFactory.pp(sqlparser, option);
//System.out.println(result);
Assert.IsTrue(result.Trim().Equals("CREATE PROCEDURE uspnresults \n" + "AS \n" + " SELECT Count(contactid)\n" + " FROM person.contact \n" + "--begin_no_format\n" + "SELECT Count(customerid) FROM \n" + "sales.customer;\n" + "--end_no_format \n" + "GO", StringComparison.OrdinalIgnoreCase));
}
public void testCaseWhenThenInSameLine()
{
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 = true;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("SELECT productnumber,\n" + " name,\n" + " 'Price Range' = CASE\n" + " WHEN listprice = 0 THEN 'Mfg item - not for resale'\n" + " WHEN listprice < 50 THEN 'Under $50'\n" + " WHEN listprice >= 50\n" + " AND listprice < 250 THEN 'Under $250'\n" + " WHEN listprice >= 250\n" + " AND listprice < 1000 THEN 'Under $1000'\n" + " ELSE 'Over $1000'\n" + " END\n" + "FROM production.product\n" + "ORDER BY productnumber;", 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 test1()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "CREATE OR REPLACE FORCE VIEW \"POTC_PO_LINES\" (\"A$CHART_OF_ACCOUNTS_NAME\", \"A$CONTRACT_NUMBER\", \"A$HAZARD_CLASS\", \"A$ITEM_DESCRIPTION\", \"A$JOB_NAME\", \"A$LEDGER_NAME\", \"A$LINE_CREATION_DATE\", \"A$LINE_TYPE\", \"A$OPERATING_UNIT_NAME\", \"A$PO_NUMBER\", \"A$TERMS\", \"A$UN_HAZARD_MATL_NUMBER\", \"A$VENDOR_NAME\", \"A$VENDOR_NUMBER\", \"A$VENDOR_PRODUCT_NUMBER\", \"A$ZZ__________________________\", \"ALLOW_PRICE_OVERRIDE_FLAG\", \"AMOUNT\", \"AUCTION_DISPLAY_NUMBER\", \"AUCTION_LINE_NUMBER\", \"AUTHORIZATION_STATUS\", \"BASE_CURRENCY_CODE\", \"BID_LINE_NUMBER\", \"BID_NUMBER\", \"CANCEL_DATE\", \"CANCEL_FLAG\", \"CANCEL_REASON\", \"CAPITAL_EXPENSE_FLAG\", \"CHART_OF_ACCOUNTS_NAME\", \"CLOSED_CODE\", \"CLOSED_DATE\", \"CLOSED_REASON\", \"CONSIGNED_CONSUMPTION_FLAG\", \"CONTRACT_NUMBER\", \"CONTRACTOR_ASSIGN_END_DATE\", \"CONTRACTOR_ASSIGN_START_DATE\", \"CONTRACTOR_FIRST_NAME\", \"CONTRACTOR_LAST_NAME\", \"CURRENCY\", \"HAZARD_CLASS\", \"INCOME_TAX_TYPE\", \"ITEM$INVENTORY_ITEM_ID\", \"ITEM$ORGANIZATION_ID\", \"ITEM$SV$ITEM\", \"ITEMCAT$CV$ITEM_CATEGORY_KFF\", \"ITEMCAT$CATEGORY_ID\", \"ITEM_DESCRIPTION\", \"ITEM_REVISION\", \"JOB_NAME\", \"LEDGER_NAME\", \"LINE_CREATION_DATE\", \"LINE_TYPE\", \"LIST_PRICE_PER_UNIT\", \"LIST_PRICE_PER_UNIT_BASE\", \"MANUAL_PRICE_CHANGE_FLAG\", \"MARKET_PRICE\", \"MARKET_PRICE_BASE\", \"MATCHING_BASIS\", \"NEGOTIATED_BY_PREPARER_FLAG\", \"NOTE_TO_VENDOR\", \"OPEN_FLAG\", \"OPERATING_UNIT_NAME\", \"OVER_TOLERANCE_ERROR_FLAG\", \"POLIN$ATTRIBUTE_CATEGORY\", \"POLIN$ATTRIBUTE_13\", \"POLIN$ORDER_SEQUENCE\", \"PO_LINE_NUMBER\", \"PO_NUMBER\", \"PRICE_TYPE\", \"PRICE_TYPE_LOOKUP_CODE\", \"PURCHASE_BASIS\", \"QTY_RECEIVED_TOLERANCE_PCT\", \"QUANTITY\", \"RATE\", \"RATE_DATE\", \"RATE_TYPE\", \"SET_OF_BOOKS_NAME\", \"SHIPMENT_LINE_NUMBER\", \"SHIPMENT_LINE_STATUS\", \"SHIPMENT_QUANTITY\", \"TAXABLE_FLAG\", \"TERMS\", \"TRANSACTION_REASON_CODE\", \"UN_HAZARD_MATL_NUMBER\", \"UNIT_OF_MEASURE\", \"UNIT_PRICE\", \"UNIT_PRICE_BASE\", \"UNORDERED_FLAG\", \"VEND$SUPPLIER_HOME_PAGE\", \"VENDOR_NAME\", \"VENDOR_NUMBER\", \"VENDOR_PRODUCT_NUMBER\", \"Z$$_________________________\", \"Z$POTC_PO_LINES\") AS SELECT XMAP.CHART_OF_ACCOUNTS_NAME A$Chart_Of_Accounts_Name, Contract_Number.SEGMENT1 A$Contract_Number, Hazard_Class.HAZARD_CLASS A$Hazard_Class, POLIN.ITEM_DESCRIPTION A$Item_Description, Job_Name.NAME A$Job_Name, XMAP.LEDGER_NAME A$Ledger_Name, POLIN.CREATION_DATE A$Line_Creation_Date, Line_Type.LINE_TYPE A$Line_Type, XMAP.OPERATING_UNIT_NAME A$Operating_Unit_Name, POHDR.SEGMENT1 A$PO_Number, Terms.NAME A$Terms, UN_Hazard_Matl_Number.UN_NUMBER A$UN_Hazard_Matl_Number, VEND.VENDOR_NAME A$Vendor_Name, VEND.SEGMENT1 A$Vendor_Number, POLIN.VENDOR_PRODUCT_NUM A$Vendor_Product_Number, 'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________, NVL(POLIN.ALLOW_PRICE_OVERRIDE_FLAG,'Y') Allow_Price_Override_Flag, NVL(POLIN.AMOUNT,0) Amount, POLIN.AUCTION_DISPLAY_NUMBER Auction_Display_Number, POLIN.AUCTION_LINE_NUMBER Auction_Line_Number, Authorization_Status.MEANING Authorization_Status, GLSOB.CURRENCY_CODE Base_Currency_Code, POLIN.BID_LINE_NUMBER Bid_Line_Number, POLIN.BID_NUMBER Bid_Number, POLIN.CANCEL_DATE Cancel_Date, NVL(POLIN.CANCEL_FLAG,'N') Cancel_Flag, POLIN.CANCEL_REASON Cancel_Reason, NVL(POLIN.CAPITAL_EXPENSE_FLAG,'N') Capital_Expense_Flag, XMAP.CHART_OF_ACCOUNTS_NAME Chart_Of_Accounts_Name, NVL(POLIN.CLOSED_CODE,'OPEN') Closed_Code, POLIN.CLOSED_DATE Closed_Date, POLIN.CLOSED_REASON Closed_Reason, NVL(POHDR.CONSIGNED_CONSUMPTION_FLAG,'N') Consigned_Consumption_Flag, Contract_Number.SEGMENT1 Contract_Number, POLIN.EXPIRATION_DATE Contractor_Assign_End_Date, POLIN.START_DATE Contractor_Assign_Start_Date, POLIN.CONTRACTOR_FIRST_NAME Contractor_First_Name, POLIN.CONTRACTOR_LAST_NAME Contractor_Last_Name, POHDR.CURRENCY_CODE Currency, Hazard_Class.HAZARD_CLASS Hazard_Class, POLIN.TYPE_1099 Income_Tax_Type, MSTK1.INVENTORY_ITEM_ID Item$Inventory_Item_Id, MSTK1.ORGANIZATION_ID Item$Organization_Id, MSTK1.SV$Item Item$SV$Item, ItemCat.CV$Item_Category_KFF ItemCat$CV$Item_Category_KFF, ItemCat.CATEGORY_ID ItemCat$Category_Id, POLIN.ITEM_DESCRIPTION Item_Description, POLIN.ITEM_REVISION Item_Revision, Job_Name.NAME Job_Name, XMAP.LEDGER_NAME Ledger_Name, POLIN.CREATION_DATE Line_Creation_Date, Line_Type.LINE_TYPE Line_Type, POLIN.LIST_PRICE_PER_UNIT List_Price_Per_Unit, POLIN.LIST_PRICE_PER_UNIT * NVL(POHDR.RATE, DECODE(POHDR.CURRENCY_CODE,GLSOB.CURRENCY_CODE,1,NULL)) List_Price_Per_Unit_Base, NVL(POLIN.MANUAL_PRICE_CHANGE_FLAG,'N') Manual_Price_Change_Flag, POLIN.MARKET_PRICE Market_Price, POLIN.MARKET_PRICE * NVL(POHDR.RATE, DECODE(POHDR.CURRENCY_CODE,GLSOB.CURRENCY_CODE,1,NULL)) Market_Price_Base, Matching_Basis.MEANING Matching_Basis, NVL(POLIN.NEGOTIATED_BY_PREPARER_FLAG,'N') Negotiated_By_Preparer_Flag, POLIN.NOTE_TO_VENDOR Note_To_Vendor, DECODE(NVL(POLIN.CLOSED_CODE,'OPEN')||NVL(POLIN.CANCEL_FLAG,'N'),'OPENN','Y','N') Open_Flag, XMAP.OPERATING_UNIT_NAME Operating_Unit_Name, NVL(POLIN.OVER_TOLERANCE_ERROR_FLAG,'N') Over_Tolerance_Error_Flag, POLIN.ATTRIBUTE_CATEGORY POLIN$ATTRIBUTE_CATEGORY, POLIN.ATTRIBUTE13 POLIN$Attribute_13, decode(POLIN.ATTRIBUTE_CATEGORY,'2052',POLIN.ATTRIBUTE1,null) POLIN$Order_Sequence, POLIN.LINE_NUM PO_Line_Number, POHDR.SEGMENT1 PO_Number, Price_Type.MEANING Price_Type, POLIN.PRICE_TYPE_LOOKUP_CODE Price_Type_Lookup_Code, Purchase_Basis.MEANING Purchase_Basis, POLIN.QTY_RCV_TOLERANCE Qty_Received_Tolerance_Pct, NVL(POLIN.QUANTITY,0) Quantity, POHDR.RATE Rate, POHDR.RATE_DATE Rate_Date, POHDR.RATE_TYPE Rate_Type, TO_CHAR(NULL) Set_Of_Books_Name, TO_NUMBER(NULL) Shipment_Line_Number, TO_CHAR(NULL) Shipment_Line_Status, TO_NUMBER(NULL) Shipment_Quantity, NVL(POLIN.TAXABLE_FLAG,'N') Taxable_Flag, Terms.NAME Terms, POLIN.TRANSACTION_REASON_CODE Transaction_Reason_Code, UN_Hazard_Matl_Number.UN_NUMBER UN_Hazard_Matl_Number, Unit_Of_Measure.DESCRIPTION Unit_Of_Measure, POLIN.UNIT_PRICE Unit_Price, POLIN.UNIT_PRICE * NVL(POHDR.RATE, DECODE(POHDR.CURRENCY_CODE,GLSOB.CURRENCY_CODE,1,NULL)) Unit_Price_Base, NVL(POLIN.UNORDERED_FLAG,'N') Unordered_Flag, VEND.ATTRIBUTE14 VEND$Supplier_Home_Page, VEND.VENDOR_NAME Vendor_Name, VEND.SEGMENT1 Vendor_Number, POLIN.VENDOR_PRODUCT_NUM Vendor_Product_Number, 'Z$$_________________________' Z$$_________________________, POLIN.rowid Z$POTC_PO_Lines FROM PO.PO_HEADERS_ALL Contract_Number, dev_602_medium_tc.XXK_Mtl_Cat_S2 ItemCat, HR.PER_JOBS Job_Name, PO.PO_UN_NUMBERS_TL UN_Hazard_Matl_Number, PO.PO_HAZARD_CLASSES_TL Hazard_Class, PO.PO_LINE_TYPES_TL Line_Type, INV.MTL_UNITS_OF_MEASURE_TL Unit_Of_Measure, AP.AP_TERMS_TL Terms, dev_602_medium_tc.N_PO_LOOKUPS_VL Matching_Basis, dev_602_medium_tc.N_PO_LOOKUPS_VL Purchase_Basis, dev_602_medium_tc.N_PO_LOOKUPS_VL Price_Type, dev_602_medium_tc.N_PO_LOOKUPS_VL Authorization_Status, dev_602_medium_tc.XXK_Sys_Item MSTK1, INV.MTL_SYSTEM_ITEMS_B ITEM, GL.GL_LEDGERS GLSOB, AP.FINANCIALS_SYSTEM_PARAMS_ALL FIN, AP.AP_SUPPLIERS VEND, PO.PO_HEADERS_ALL POHDR, dev_602_medium_tc.POTC_OU_ACL_Map_Base XMAP, PO.PO_LINES_ALL POLIN WHERE 'Copyright Noetix Corporation 1992-2010' is not null AND POLIN.PO_HEADER_ID = POHDR.PO_HEADER_ID AND NVL(POLIN.ORG_ID, -9999) = XMAP.ORG_ID AND POHDR.VENDOR_ID = VEND.VENDOR_ID AND POHDR.TYPE_LOOKUP_CODE = 'STANDARD' AND POLIN.ITEM_ID = ITEM.INVENTORY_ITEM_ID(+) AND NVL(ITEM.ORGANIZATION_ID,FIN.INVENTORY_ORGANIZATION_ID) = FIN.INVENTORY_ORGANIZATION_ID AND NVL(POHDR.ORG_ID,-9999) = NVL(FIN.ORG_ID,-9999) AND FIN.SET_OF_BOOKS_ID = GLSOB.LEDGER_ID AND POHDR.AUTHORIZATION_STATUS = Authorization_Status.LOOKUP_CODE(+) AND Authorization_Status.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS' AND Authorization_Status.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Authorization_Status.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Authorization_Status.LOOKUP_TYPE(+),Authorization_Status.VIEW_APPLICATION_ID(+)) AND POLIN.PRICE_TYPE_LOOKUP_CODE = Price_Type.LOOKUP_CODE(+) AND Price_Type.LOOKUP_TYPE(+) = 'PRICE TYPE' AND Price_Type.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Price_Type.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Price_Type.LOOKUP_TYPE(+),Price_Type.VIEW_APPLICATION_ID(+)) AND POLIN.PURCHASE_BASIS = Purchase_Basis.LOOKUP_CODE(+) AND Purchase_Basis.LOOKUP_TYPE(+) = 'PURCHASE BASIS' AND Purchase_Basis.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Purchase_Basis.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Purchase_Basis.LOOKUP_TYPE(+),Purchase_Basis.VIEW_APPLICATION_ID(+)) AND POLIN.MATCHING_BASIS = Matching_Basis.LOOKUP_CODE(+) AND Matching_Basis.LOOKUP_TYPE(+) = 'MATCHING BASIS' AND Matching_Basis.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Matching_Basis.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Matching_Basis.LOOKUP_TYPE(+),Matching_Basis.VIEW_APPLICATION_ID(+)) AND POHDR.TERMS_ID = Terms.TERM_ID(+) AND Terms.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.UNIT_MEAS_LOOKUP_CODE = Unit_Of_Measure.UNIT_OF_MEASURE(+) AND Unit_Of_Measure.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.LINE_TYPE_ID = Line_Type.LINE_TYPE_ID(+) AND Line_Type.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.HAZARD_CLASS_ID = Hazard_Class.HAZARD_CLASS_ID(+) AND Hazard_Class.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.UN_NUMBER_ID = UN_Hazard_Matl_Number.UN_NUMBER_ID(+) AND UN_Hazard_Matl_Number.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.JOB_ID = Job_Name.JOB_ID(+) AND POLIN.CATEGORY_ID = ItemCat.CATEGORY_ID(+) AND POLIN.CONTRACT_ID = Contract_Number.PO_HEADER_ID(+) AND ITEM.INVENTORY_ITEM_ID = MSTK1.INVENTORY_ITEM_ID (+) AND ITEM.ORGANIZATION_ID = MSTK1.ORGANIZATION_ID (+)";
// System.out.println(sqlparser.sqltext);
Assert.IsTrue(sqlparser.parse() == 0);
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("CREATE OR REPLACE FORCE VIEW \"POTC_PO_LINES\" (\"A$CHART_OF_ACCOUNTS_NAME\",\n" + " \"A$CONTRACT_NUMBER\",\n" + " \"A$HAZARD_CLASS\",\n" + " \"A$ITEM_DESCRIPTION\",\n" + " \"A$JOB_NAME\",\n" + " \"A$LEDGER_NAME\",\n" + " \"A$LINE_CREATION_DATE\",\n" + " \"A$LINE_TYPE\",\n" + " \"A$OPERATING_UNIT_NAME\",\n" + " \"A$PO_NUMBER\",\n" + " \"A$TERMS\",\n" + " \"A$UN_HAZARD_MATL_NUMBER\",\n" + " \"A$VENDOR_NAME\",\n" + " \"A$VENDOR_NUMBER\",\n" + " \"A$VENDOR_PRODUCT_NUMBER\",\n" + " \"A$ZZ__________________________\",\n" + " \"ALLOW_PRICE_OVERRIDE_FLAG\",\n" + " \"AMOUNT\",\n" + " \"AUCTION_DISPLAY_NUMBER\",\n" + " \"AUCTION_LINE_NUMBER\",\n" + " \"AUTHORIZATION_STATUS\",\n" + " \"BASE_CURRENCY_CODE\",\n" + " \"BID_LINE_NUMBER\",\n" + " \"BID_NUMBER\",\n" + " \"CANCEL_DATE\",\n" + " \"CANCEL_FLAG\",\n" + " \"CANCEL_REASON\",\n" + " \"CAPITAL_EXPENSE_FLAG\",\n" + " \"CHART_OF_ACCOUNTS_NAME\",\n" + " \"CLOSED_CODE\",\n" + " \"CLOSED_DATE\",\n" + " \"CLOSED_REASON\",\n" + " \"CONSIGNED_CONSUMPTION_FLAG\",\n" + " \"CONTRACT_NUMBER\",\n" + " \"CONTRACTOR_ASSIGN_END_DATE\",\n" + " \"CONTRACTOR_ASSIGN_START_DATE\",\n" + " \"CONTRACTOR_FIRST_NAME\",\n" + " \"CONTRACTOR_LAST_NAME\",\n" + " \"CURRENCY\",\n" + " \"HAZARD_CLASS\",\n" + " \"INCOME_TAX_TYPE\",\n" + " \"ITEM$INVENTORY_ITEM_ID\",\n" + " \"ITEM$ORGANIZATION_ID\",\n" + " \"ITEM$SV$ITEM\",\n" + " \"ITEMCAT$CV$ITEM_CATEGORY_KFF\",\n" + " \"ITEMCAT$CATEGORY_ID\",\n" + " \"ITEM_DESCRIPTION\",\n" + " \"ITEM_REVISION\",\n" + " \"JOB_NAME\",\n" + " \"LEDGER_NAME\",\n" + " \"LINE_CREATION_DATE\",\n" + " \"LINE_TYPE\",\n" + " \"LIST_PRICE_PER_UNIT\",\n" + " \"LIST_PRICE_PER_UNIT_BASE\",\n" + " \"MANUAL_PRICE_CHANGE_FLAG\",\n" + " \"MARKET_PRICE\",\n" + " \"MARKET_PRICE_BASE\",\n" + " \"MATCHING_BASIS\",\n" + " \"NEGOTIATED_BY_PREPARER_FLAG\",\n" + " \"NOTE_TO_VENDOR\",\n" + " \"OPEN_FLAG\",\n" + " \"OPERATING_UNIT_NAME\",\n" + " \"OVER_TOLERANCE_ERROR_FLAG\",\n" + " \"POLIN$ATTRIBUTE_CATEGORY\",\n" + " \"POLIN$ATTRIBUTE_13\",\n" + " \"POLIN$ORDER_SEQUENCE\",\n" + " \"PO_LINE_NUMBER\",\n" + " \"PO_NUMBER\",\n" + " \"PRICE_TYPE\",\n" + " \"PRICE_TYPE_LOOKUP_CODE\",\n" + " \"PURCHASE_BASIS\",\n" + " \"QTY_RECEIVED_TOLERANCE_PCT\",\n" + " \"QUANTITY\",\n" + " \"RATE\",\n" + " \"RATE_DATE\",\n" + " \"RATE_TYPE\",\n" + " \"SET_OF_BOOKS_NAME\",\n" + " \"SHIPMENT_LINE_NUMBER\",\n" + " \"SHIPMENT_LINE_STATUS\",\n" + " \"SHIPMENT_QUANTITY\",\n" + " \"TAXABLE_FLAG\",\n" + " \"TERMS\",\n" + " \"TRANSACTION_REASON_CODE\",\n" + " \"UN_HAZARD_MATL_NUMBER\",\n" + " \"UNIT_OF_MEASURE\",\n" + " \"UNIT_PRICE\",\n" + " \"UNIT_PRICE_BASE\",\n" + " \"UNORDERED_FLAG\",\n" + " \"VEND$SUPPLIER_HOME_PAGE\",\n" + " \"VENDOR_NAME\",\n" + " \"VENDOR_NUMBER\",\n" + " \"VENDOR_PRODUCT_NUMBER\",\n" + " \"Z$$_________________________\",\n" + " \"Z$POTC_PO_LINES\") \n" + "AS \n" + " SELECT xmap.chart_of_accounts_name a$chart_of_accounts_name,\n" + " contract_number.segment1 a$contract_number,\n" + " hazard_class.hazard_class a$hazard_class,\n" + " polin.item_description a$item_description,\n" + " job_name.NAME a$job_name,\n" + " xmap.ledger_name a$ledger_name,\n" + " polin.creation_date a$line_creation_date,\n" + " line_type.line_type a$line_type,\n" + " xmap.operating_unit_name a$operating_unit_name,\n" + " pohdr.segment1 a$po_number,\n" + " terms.NAME a$terms,\n" + " un_hazard_matl_number.un_number a$un_hazard_matl_number,\n" + " vend.vendor_name a$vendor_name,\n" + " vend.segment1 a$vendor_number,\n" + " polin.vendor_product_num a$vendor_product_number,\n" + " 'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' a$zz__________________________,\n" + " Nvl(polin.allow_price_override_flag,'Y') allow_price_override_flag,\n" + " Nvl(polin.amount,0) amount,\n" + " polin.auction_display_number auction_display_number,\n" + " polin.auction_line_number auction_line_number,\n" + " authorization_status.meaning authorization_status,\n" + " glsob.currency_code base_currency_code,\n" + " polin.bid_line_number bid_line_number,\n" + " polin.bid_number bid_number,\n" + " polin.cancel_date cancel_date,\n" + " Nvl(polin.cancel_flag,'N') cancel_flag,\n" + " polin.cancel_reason cancel_reason,\n" + " Nvl(polin.capital_expense_flag,'N') capital_expense_flag,\n" + " xmap.chart_of_accounts_name chart_of_accounts_name,\n" + " Nvl(polin.closed_code,'OPEN') closed_code,\n" + " polin.closed_date closed_date,\n" + " polin.closed_reason closed_reason,\n" + " Nvl(pohdr.consigned_consumption_flag,'N') consigned_consumption_flag,\n" + " contract_number.segment1 contract_number,\n" + " polin.expiration_date contractor_assign_end_date,\n" + " polin.start_date contractor_assign_start_date,\n" + " polin.contractor_first_name contractor_first_name,\n" + " polin.contractor_last_name contractor_last_name,\n" + " pohdr.currency_code currency,\n" + " hazard_class.hazard_class hazard_class,\n" + " polin.type_1099 income_tax_type,\n" + " mstk1.inventory_item_id item$inventory_item_id,\n" + " mstk1.organization_id item$organization_id,\n" + " mstk1.sv$item item$sv$item,\n" + " itemcat.cv$item_category_kff itemcat$cv$item_category_kff,\n" + " itemcat.category_id itemcat$category_id,\n" + " polin.item_description item_description,\n" + " polin.item_revision item_revision,\n" + " job_name.NAME job_name,\n" + " xmap.ledger_name ledger_name,\n" + " polin.creation_date line_creation_date,\n" + " line_type.line_type line_type,\n" + " polin.list_price_per_unit list_price_per_unit,\n" + " polin.list_price_per_unit * Nvl(pohdr.rate, Decode(pohdr.currency_code,glsob.currency_code,1,NULL)) list_price_per_unit_base,\n" + " Nvl(polin.manual_price_change_flag,'N') manual_price_change_flag,\n" + " polin.market_price market_price,\n" + " polin.market_price * Nvl(pohdr.rate, Decode(pohdr.currency_code,glsob.currency_code,1,NULL)) market_price_base,\n" + " matching_basis.meaning matching_basis,\n" + " Nvl(polin.negotiated_by_preparer_flag,'N') negotiated_by_preparer_flag,\n" + " polin.note_to_vendor note_to_vendor,\n" + " Decode(Nvl(polin.closed_code,'OPEN')||Nvl(polin.cancel_flag,'N'),'OPENN','Y','N') open_flag,\n" + " xmap.operating_unit_name operating_unit_name,\n" + " Nvl(polin.over_tolerance_error_flag,'N') over_tolerance_error_flag,\n" + " polin.attribute_category polin$attribute_category,\n" + " polin.attribute13 polin$attribute_13,\n" + " Decode(polin.attribute_category,'2052',polin.attribute1,NULL) polin$order_sequence,\n" + " polin.line_num po_line_number,\n" + " pohdr.segment1 po_number,\n" + " price_type.meaning price_type,\n" + " polin.price_type_lookup_code price_type_lookup_code,\n" + " purchase_basis.meaning purchase_basis,\n" + " polin.qty_rcv_tolerance qty_received_tolerance_pct,\n" + " Nvl(polin.quantity,0) quantity,\n" + " pohdr.rate rate,\n" + " pohdr.rate_date rate_date,\n" + " pohdr.rate_type rate_type,\n" + " To_char(NULL) set_of_books_name,\n" + " To_number(NULL) shipment_line_number,\n" + " To_char(NULL) shipment_line_status,\n" + " To_number(NULL) shipment_quantity,\n" + " Nvl(polin.taxable_flag,'N') taxable_flag,\n" + " terms.NAME terms,\n" + " polin.transaction_reason_code transaction_reason_code,\n" + " un_hazard_matl_number.un_number un_hazard_matl_number,\n" + " unit_of_measure.description unit_of_measure,\n" + " polin.unit_price unit_price,\n" + " polin.unit_price * Nvl(pohdr.rate, Decode(pohdr.currency_code,glsob.currency_code,1,NULL)) unit_price_base,\n" + " Nvl(polin.unordered_flag,'N') unordered_flag,\n" + " vend.attribute14 vend$supplier_home_page,\n" + " vend.vendor_name vendor_name,\n" + " vend.segment1 vendor_number,\n" + " polin.vendor_product_num vendor_product_number,\n" + " 'Z$$_________________________' z$$_________________________,\n" + " polin.ROWID z$potc_po_lines\n" + " FROM po.po_headers_all contract_number,\n" + " dev_602_medium_tc.xxk_mtl_cat_s2 itemcat,\n" + " hr.per_jobs job_name,\n" + " po.po_un_numbers_tl un_hazard_matl_number,\n" + " po.po_hazard_classes_tl hazard_class,\n" + " po.po_line_types_tl line_type,\n" + " inv.mtl_units_of_measure_tl unit_of_measure,\n" + " ap.ap_terms_tl terms,\n" + " dev_602_medium_tc.n_po_lookups_vl matching_basis,\n" + " dev_602_medium_tc.n_po_lookups_vl purchase_basis,\n" + " dev_602_medium_tc.n_po_lookups_vl price_type,\n" + " dev_602_medium_tc.n_po_lookups_vl authorization_status,\n" + " dev_602_medium_tc.xxk_sys_item mstk1,\n" + " inv.mtl_system_items_b item,\n" + " gl.gl_ledgers glsob,\n" + " ap.financials_system_params_all fin,\n" + " ap.ap_suppliers vend,\n" + " po.po_headers_all pohdr,\n" + " dev_602_medium_tc.potc_ou_acl_map_base xmap,\n" + " po.po_lines_all polin\n" + " WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL\n" + " AND polin.po_header_id = pohdr.po_header_id\n" + " AND Nvl(polin.org_id, -9999) = xmap.org_id\n" + " AND pohdr.vendor_id = vend.vendor_id\n" + " AND pohdr.type_lookup_code = 'STANDARD'\n" + " AND polin.item_id = item.inventory_item_id(+)\n" + " AND Nvl(item.organization_id,fin.inventory_organization_id) = fin.inventory_organization_id\n" + " AND Nvl(pohdr.org_id,-9999) = Nvl(fin.org_id,-9999)\n" + " AND fin.set_of_books_id = glsob.ledger_id\n" + " AND pohdr.authorization_status = authorization_status.lookup_code(+)\n" + " AND authorization_status.lookup_type(+) = 'AUTHORIZATION STATUS'\n" + " AND authorization_status.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND authorization_status.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (authorization_status.lookup_type(+),authorization_status.view_application_id(+))\n" + " AND polin.price_type_lookup_code = price_type.lookup_code(+)\n" + " AND price_type.lookup_type(+) = 'PRICE TYPE'\n" + " AND price_type.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND price_type.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (price_type.lookup_type(+),price_type.view_application_id(+))\n" + " AND polin.purchase_basis = purchase_basis.lookup_code(+)\n" + " AND purchase_basis.lookup_type(+) = 'PURCHASE BASIS'\n" + " AND purchase_basis.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND purchase_basis.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (purchase_basis.lookup_type(+),purchase_basis.view_application_id(+))\n" + " AND polin.matching_basis = matching_basis.lookup_code(+)\n" + " AND matching_basis.lookup_type(+) = 'MATCHING BASIS'\n" + " AND matching_basis.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND matching_basis.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (matching_basis.lookup_type(+),matching_basis.view_application_id(+))\n" + " AND pohdr.terms_id = terms.term_id(+)\n" + " AND terms.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.unit_meas_lookup_code = unit_of_measure.unit_of_measure(+)\n" + " AND unit_of_measure.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.line_type_id = line_type.line_type_id(+)\n" + " AND line_type.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.hazard_class_id = hazard_class.hazard_class_id(+)\n" + " AND hazard_class.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.un_number_id = un_hazard_matl_number.un_number_id(+)\n" + " AND un_hazard_matl_number.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.job_id = job_name.job_id(+)\n" + " AND polin.category_id = itemcat.category_id(+)\n" + " AND polin.contract_id = contract_number.po_header_id(+)\n" + " AND item.inventory_item_id = mstk1.inventory_item_id (+)\n" + " AND item.organization_id = mstk1.organization_id (+)", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
public void testMssqlCreateFunction_ReturnStmt()
{
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 Sales.fn_SalesByStore (@storeid int)\n" + "RETURNS TABLE\n" + "AS\n" + "RETURN \n" + "(\n" + " SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'\n" + " FROM Production.Product AS P \n" + " JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID\n" + " JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID\n" + " WHERE SH.CustomerID = @storeid\n" + " GROUP BY P.ProductID, P.Name\n" + "); ";
sqlparser.parse();
option.beStyleBlockIndentSize = 3;
string result = FormatterFactory.pp(sqlparser, option);
// System.out.println(result);
Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION sales.Fn_salesbystore (@storeid INT\n" + ") \n" + "RETURNS TABLE \n" + "AS \n" + " RETURN \n" + " (\n" + " SELECT p.productid,\n" + " p.name,\n" + " Sum(sd.linetotal) AS 'YTD Total'\n" + " FROM production.product AS p\n" + " JOIN sales.salesorderdetail AS sd\n" + " ON sd.productid = p.productid\n" + " JOIN sales.salesorderheader AS sh\n" + " ON sh.salesorderid = sd.salesorderid\n" + " WHERE sh.customerid = @storeid\n" + " GROUP BY p.productid,\n" + " p.name\n" + " );", StringComparison.OrdinalIgnoreCase));
}
public void testSelect_fromclause_Style()
{
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.selectFromclauseStyle = TAlignStyle.AsWrapped;
string result = FormatterFactory.pp(sqlparser, option);
Console.WriteLine(result);
Assert.IsTrue(result.Trim().Equals("SELECT last_name,\n" + " department_name dept_name\n" + "FROM employees, departments;", StringComparison.OrdinalIgnoreCase));
}
public void testAndOrUnderWhere()
{
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,\n" + " departments d\n" + "WHERE e.department_id = d.department_id\n" + " AND e.last_name = 'Matos' and exists(\n" + "\t\t\t\tSELECT e.employee_id\n" + "\t\t\t\tFROM employees e,\n" + "\t\t\t\t departments d\n" + "\t\t\t\tWHERE e.department_id = d.department_id\n" + "\t\t\t\t AND e.last_name \n" + " );";
sqlparser.parse();
option.andOrUnderWhere = true;
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" + "WHERE e.department_id = d.department_id\n" + " AND e.last_name = 'Matos'\n" + " AND EXISTS( SELECT e.employee_id\n" + " FROM employees e,\n" + " departments d\n" + " WHERE e.department_id = d.department_id\n" + " AND e.last_name );", StringComparison.OrdinalIgnoreCase));
// System.out.println(result);
}
public void testDefaultAligntype()
{
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.AsWrapped;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("CREATE TABLE dept(deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13))", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testSelect_keywords_alignOption_update()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "UPDATE employees \n" + "SET department_id = 70 \n" + "WHERE employee_id = 113";
sqlparser.parse();
option.selectKeywordsAlignOption = TAlignOption.AloRight;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("UPDATE employees\n" + " SET department_id = 70\n" + " WHERE employee_id = 113", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
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" + "group 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" + "GROUP BY\n" + " e.employee_id,\n" + " d.locatioin_id,\n" + " d.locatioin_id2", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
public void testSelect_keywords_alignOption_delete()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "INSERT INTO employees\n" + "(employee_id,\n" + " first_name,\n" + " last_name,\n" + " email,\n" + " phone_number,\n" + " hire_date,\n" + " job_id,\n" + " salary,\n" + " commission_pct,\n" + " manager_id,\n" + " department_id) \n" + "VALUES(113,\n" + "'Louis',\n" + "'Popp',\n" + "'Ldd',\n" + "'515.124.222',\n" + "sysdate,\n" + "'Ac_account',\n" + "8900,\n" + "NULL,\n" + "205,\n" + "100)\n" + " \n" + " ";
sqlparser.parse();
option.selectKeywordsAlignOption = TAlignOption.AloRight;
string result = FormatterFactory.pp(sqlparser, option);
Assert.IsTrue(result.Trim().Equals("INSERT INTO employees\n" + " (employee_id,\n" + " first_name,\n" + " last_name,\n" + " email,\n" + " phone_number,\n" + " hire_date,\n" + " job_id,\n" + " salary,\n" + " commission_pct,\n" + " manager_id,\n" + " department_id)\n" + " VALUES (113,\n" + " 'Louis',\n" + " 'Popp',\n" + " 'Ldd',\n" + " '515.124.222',\n" + " sysdate,\n" + " 'Ac_account',\n" + " 8900,\n" + " NULL,\n" + " 205,\n" + " 100)", StringComparison.OrdinalIgnoreCase));
//System.out.println(result);
}
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 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 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 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);
}
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);
}
/// <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 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 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);
}
