Exemplo n.º 1
0
 public static string GetCode(Node node, bool resolvedNames)
 {
     var sw = new StringWriter();
     var cg = new SqlServerCodeGenerator();
     cg.ResolveNames = resolvedNames;
     cg.Execute(sw, node);
     return sw.ToString();
 }
Exemplo n.º 2
0
        public static string GetCode(Node node, bool resolvedNames)
        {
            var sw = new StringWriter();
            var cg = new SqlServerCodeGenerator();

            cg.ResolveNames = resolvedNames;
            cg.Execute(sw, node);
            return(sw.ToString());
        }
Exemplo n.º 3
0
        public string GenerateTableStatisticsQuery(TableReference table)
        {
            if (table.Statistics == null)
            {
                throw new InvalidOperationException();
            }

            // Build table specific where clause
            var cnr = new SearchConditionNormalizer();

            cnr.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
            var wh = cnr.GenerateWhereClauseSpecificToTable(table);

            var where = new StringWriter();
            if (wh != null)
            {
                var cg = new SqlServerCodeGenerator();
                cg.Execute(where, wh);
            }
            ;

            //*** TODO: move into resource
            string sql = String.Format(@"
IF OBJECT_ID('tempdb..##keys_{4}') IS NOT NULL
DROP TABLE ##keys_{4}

SELECT CAST({2} AS float) AS __key
INTO ##keys_{4}
FROM {0} {1}
{3};

DECLARE @count bigint = @@ROWCOUNT;
DECLARE @step bigint = @count / @bincount;

IF (@step = 0) SET @step = NULL;

WITH q AS
(
	SELECT __key, ROW_NUMBER() OVER (ORDER BY __key) __rn
	FROM ##keys_{4}
)
SELECT __key, __rn
FROM q
WHERE __rn % @step = 1 OR __rn = @count;

DROP TABLE ##keys_{4};
",
                                       GetResolvedTableName(table),
                                       table.Alias == null ? "" : String.Format(" AS {0} ", QuoteIdentifier(table.Alias)),
                                       QuoteIdentifier(table.Statistics.KeyColumn),
                                       where.ToString(),
                                       Guid.NewGuid().ToString().Replace('-', '_'));

            return(sql);
        }
        public void GenerateCreateDestinationTableQueryTest()
        {
            DataTable schema;

            using (var cn = new SqlConnection(Jhu.Graywulf.Test.AppSettings.IOTestConnectionString))
            {
                cn.Open();

                var sql = "SELECT * FROM SampleData";
                using (var cmd = new SqlCommand(sql, cn))
                {
                    using (var dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
                    {
                        schema = dr.GetSchemaTable();
                    }
                }
            }

            var dest = new Table()
            {
                SchemaName = "dbo",
                TableName = "destination"
            };

            var cg = new SqlServerCodeGenerator();
            var res = cg.GenerateCreateDestinationTableQuery(schema, dest);

            Assert.AreEqual(@"CREATE TABLE [dbo].[destination] ([float] real  NULL,
            [double] float  NULL,
            [decimal] money  NULL,
            [nvarchar(50)] nvarchar(50)  NULL,
            [bigint] bigint  NULL,
            [int] int NOT NULL,
            [tinyint] tinyint  NULL,
            [smallint] smallint  NULL,
            [bit] bit  NULL,
            [ntext] nvarchar(max)  NULL,
            [char] char(1)  NULL,
            [datetime] datetime  NULL,
            [guid] uniqueidentifier  NULL)", res);
        }
        private string[] GenerateMostRestrictiveTableQueryTestHelper(string sql, bool includePrimaryKey, int top)
        {
            var cg = new SqlServerCodeGenerator();
            cg.ResolveNames = true;

            var ss = CreateSelect(sql);

            var res = new List<string>();

            foreach (var qs in ss.EnumerateQuerySpecifications())
            {
                // TODO: use qs.SourceTableReferences
                foreach (var tr in qs.EnumerateSourceTableReferences(true))
                {
                    res.Add(cg.GenerateMostRestrictiveTableQuery(tr, includePrimaryKey, top));
                }
            }

            return res.ToArray();
        }
        private string GenerateCode(string query, bool resolveAliases, bool resolveNames, bool substituteStars)
        {
            var ss = CreateSelect(query);
            var w = new StringWriter();

            var cg = new SqlServerCodeGenerator();
            cg.ResolveNames = resolveNames;
            cg.Execute(w, ss);

            return w.ToString();
        }
Exemplo n.º 7
0
        /// <summary>
        /// Creates the destination table
        /// </summary>
        /// <param name="schemaTable"></param>
        protected void CreateDestinationTable(DataTable schemaTable)
        {
            // Generate create table SQL
            var cg = new SqlServerCodeGenerator();
            var sql = cg.GenerateCreateDestinationTableQuery(schemaTable, destination.Table);

            // Execute CREATE TABLE query on destination
            using (var cn = new SqlConnection(destination.Table.Dataset.ConnectionString))
            {
                cn.Open();

                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
Exemplo n.º 8
0
        protected override string GetExecuteQueryText()
        {
            // strip off order by
            var orderby = SelectStatement.FindDescendant<OrderByClause>();
            if (orderby != null)
            {
                SelectStatement.Stack.Remove(orderby);
            }

            // strip off partition on
            foreach (var qs in SelectStatement.EnumerateQuerySpecifications())
            {
                // strip off select into
                var into = qs.FindDescendant<IntoClause>();
                if (into != null)
                {
                    qs.Stack.Remove(into);
                }

                foreach (var ts in qs.EnumerateDescendantsRecursive<SimpleTableSource>())
                {
                    var pc = ts.FindDescendant<TablePartitionClause>();

                    if (pc != null)
                    {
                        pc.Parent.Stack.Remove(pc);
                    }
                }
            }

            // Generate code
            var sw = new StringWriter();
            var cg = new SqlServerCodeGenerator()
            {
                ResolveNames = true
            };

            cg.Execute(sw, SelectStatement);

            return sw.ToString();
        }
Exemplo n.º 9
0
        private void AppendPartitioningConditions(QuerySpecification qs, SimpleTableSource ts)
        {
            if (!double.IsInfinity(PartitioningKeyFrom) || !double.IsInfinity(PartitioningKeyTo))
            {
                var cg = new SqlServerCodeGenerator();

                string format;
                if (double.IsInfinity(PartitioningKeyFrom) && double.IsInfinity(PartitioningKeyTo))
                {
                    format = "{1} <= {0} AND {0} < {2}";
                }
                else if (double.IsInfinity(PartitioningKeyFrom))
                {
                    format = "{0} < {2}";
                }
                else
                {
                    format = "{1} <= {0}";
                }

                string sql = String.Format(format,
                    cg.GetResolvedColumnName(ts.PartitioningColumnReference),
                    PartitioningKeyFrom.ToString(System.Globalization.CultureInfo.InvariantCulture),
                    PartitioningKeyTo.ToString(System.Globalization.CultureInfo.InvariantCulture));

                var parser = new Jhu.Graywulf.SqlParser.SqlParser();
                var sc = (SearchCondition)parser.Execute(new SearchCondition(), sql);

                var where = qs.FindDescendant<WhereClause>();
                if (where == null)
                {
                    where = WhereClause.Create(sc);
                    var ws = Whitespace.Create();

                    var wsn = qs.Stack.AddAfter(qs.Stack.Find(qs.FindDescendant<FromClause>()), ws);
                    qs.Stack.AddAfter(wsn, where);
                }
                else
                {
                    where.AppendCondition(sc, "AND");
                }
            }

            // --- remove partition clause
            ts.Stack.Remove(ts.FindDescendant<TablePartitionClause>());
        }
Exemplo n.º 10
0
        public void PrepareComputeTableStatistics(Context context, TableReference tr, out string connectionString, out string sql)
        {
            // Assign a database server to the query
            // TODO: maybe make this function generic
            // TODO: check this part to use appropriate server and database
            var sm = GetSchemaManager(false);
            var ds = sm.Datasets[tr.DatasetName];

            if (ds is GraywulfDataset && !((GraywulfDataset)ds).IsSpecificInstanceRequired)
            {
                var gds = (GraywulfDataset)ds;
                var dd = new DatabaseDefinition(context);
                dd.Guid = gds.DatabaseDefinition.Guid;
                dd.Load();

                // Get a server from the scheduler
                var si = new ServerInstance(Context);
                si.Guid = Scheduler.GetNextServerInstance(new Guid[] { dd.Guid }, StatDatabaseVersionName, null);
                si.Load();

                connectionString = si.GetConnectionString().ConnectionString;

                SubstituteDatabaseName(tr, si.Guid, StatDatabaseVersionName);
                tr.DatabaseObject = null;
            }
            else
            {
                // Run it on the specific database
                connectionString = ds.ConnectionString;
            }

            // Generate statistics query
            var cg = new SqlServerCodeGenerator();
            cg.ResolveNames = true;
            sql = cg.GenerateTableStatisticsQuery(tr);
        }
Exemplo n.º 11
0
        public virtual string GetDestinationTableSchemaSourceQuery()
        {
            // strip off order by
            OrderByClause orderby = SelectStatement.FindDescendant<OrderByClause>();
            if (orderby != null)
            {
                SelectStatement.Stack.Remove(orderby);
            }

            // strip off partition on
            foreach (QuerySpecification qs in SelectStatement.EnumerateQuerySpecifications())
            {
                // strip off select into
                IntoClause into = qs.FindDescendant<IntoClause>();
                if (into != null)
                {
                    qs.Stack.Remove(into);
                }

                foreach (var ts in qs.EnumerateDescendantsRecursive<SimpleTableSource>())
                {
                    var pc = ts.FindDescendant<TablePartitionClause>();

                    if (pc != null)
                    {
                        pc.Parent.Stack.Remove(pc);
                    }
                }
            }

            var cg = new Jhu.Graywulf.SqlParser.SqlCodeGen.SqlServerCodeGenerator();
            cg.ResolveNames = true;

            var sw = new StringWriter();
            cg.Execute(sw, SelectStatement);
            return sw.ToString();
        }
Exemplo n.º 12
0
        public string GenerateTableStatisticsQuery(TableReference table)
        {
            if (table.Statistics == null)
            {
                throw new InvalidOperationException();
            }

            // Build table specific where clause
            var cnr = new SearchConditionNormalizer();
            cnr.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
            var wh = cnr.GenerateWhereClauseSpecificToTable(table);

            var where = new StringWriter();
            if (wh != null)
            {
                var cg = new SqlServerCodeGenerator();
                cg.Execute(where, wh);
            };

            //*** TODO: move into resource
            string sql = String.Format(@"
            IF OBJECT_ID('tempdb..##keys_{4}') IS NOT NULL
            DROP TABLE ##keys_{4}

            SELECT CAST({2} AS float) AS __key
            INTO ##keys_{4}
            FROM {0} {1}
            {3};

            DECLARE @count bigint = @@ROWCOUNT;
            DECLARE @step bigint = @count / @bincount;

            IF (@step = 0) SET @step = NULL;

            WITH q AS
            (
            SELECT __key, ROW_NUMBER() OVER (ORDER BY __key) __rn
            FROM ##keys_{4}
            )
            SELECT __key, __rn
            FROM q
            WHERE __rn % @step = 1 OR __rn = @count;

            DROP TABLE ##keys_{4};
            ",
             GetResolvedTableName(table),
             table.Alias == null ? "" : String.Format(" AS {0} ", QuoteIdentifier(table.Alias)),
             QuoteIdentifier(table.Statistics.KeyColumn),
             where.ToString(),
             Guid.NewGuid().ToString().Replace('-', '_'));

            return sql;
        }
        private string[] GetWhereClauses(string query)
        {
            SearchConditionNormalizer cn = new SearchConditionNormalizer();

            var select = CreateSelect(query);
            var res = new List<string>();

            foreach (var qs in select.EnumerateQuerySpecifications())
            {
                cn.NormalizeQuerySpecification(qs);

                // TODO use qs.SourceTableReferences ???
                foreach (var tr in qs.EnumerateSourceTableReferences(true))
                {
                    WhereClause where = cn.GenerateWhereClauseSpecificToTable(tr);

                    if (where != null)
                    {
                        var cg = new SqlServerCodeGenerator();
                        cg.ResolveNames = true;

                        var sw = new StringWriter();
                        cg.Execute(sw, where);

                        res.Add(sw.ToString());
                    }
                    else
                    {
                        res.Add("");
                    }
                }
            }

            return res.ToArray();
        }
Exemplo n.º 14
0
        private void RenderResults(JobInstance ji)
        {
            var q = (QueryBase)ji.Parameters["Query"].Value;

            var codegen = new SqlServerCodeGenerator();

            string sql = codegen.GenerateSelectStarQuery(q.Destination.GetTable(), 100);

            using (var cn = new SqlConnection())
            {
                cn.ConnectionString = MyDBDataset.ConnectionString;
                cn.Open();

                using (var cmd = cn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;

                    using (var dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        RenderTable(dr);
                    }
                }
            }
        }
Exemplo n.º 15
0
        /// <summary>
        /// Executest bulk copy to ingest data from the DataReader
        /// </summary>
        /// <param name="dr"></param>
        protected void ExecuteBulkCopy(IDataReader dr, Table destination)
        {
            // TODO: it can only import the first resultset from dr
            var cg = new SqlServerCodeGenerator();

            isBulkCopyCanceled = false;
            bulkCopyFinishedEvent = new AutoResetEvent(false);

            // Initialize bulk copy
            var sbc = new System.Data.SqlClient.SqlBulkCopy(destination.Dataset.ConnectionString);
            sbc.DestinationTableName = cg.GetResolvedTableName(destination);
            sbc.BatchSize = batchSize;
            sbc.BulkCopyTimeout = timeout;

            // Initialize events
            sbc.NotifyAfter = batchSize;
            sbc.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e)
            {
                //RowsAffected = e.RowsCopied;  // TODO: delete if not used
                e.Abort = isBulkCopyCanceled;
            };

            try
            {
                sbc.WriteToServer(dr);
            }
            finally
            {
                bulkCopyFinishedEvent.Set();
            }
        }
Exemplo n.º 16
0
        protected override string GetOutputSelectQuery()
        {
            // Generate code
            var sw = new StringWriter();
            var cg = new SqlServerCodeGenerator();
            cg.ResolveNames = true;

            cg.Execute(sw, SelectStatement);

            return sw.ToString();
        }