예제 #1
0
        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));
        }
예제 #2
0
        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);
        }
예제 #3
0
        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");
        }
예제 #4
0
        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
            });
        }
예제 #5
0
        /// <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);
        }
예제 #7
0
        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);
        }
예제 #8
0
        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);
            }
        }
예제 #9
0
        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);
        }
예제 #11
0
        /// <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");
        }
예제 #12
0
        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));
        }
예제 #13
0
        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);
        }
예제 #14
0
        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);
            }
        }
예제 #15
0
        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);
        }
예제 #16
0
        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));
        }
예제 #17
0
        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));
        }
예제 #18
0
        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);
        }
예제 #19
0
        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);
        }
예제 #20
0
        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);
        }
예제 #21
0
        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);
        }
예제 #22
0
        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);
        }
예제 #23
0
        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);
        }
예제 #24
0
        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);
        }
예제 #25
0
        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);
        }
예제 #26
0
        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);
        }
예제 #27
0
        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);
        }
예제 #28
0
        /// <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));
        }
예제 #29
0
        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);
        }
예제 #30
0
        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);
        }