Example #1
0
        private static int Main(string[] args)
        {
            var rootCommand = new RootCommand(ThisAssembly.AppName)
            {
                new Option <string>(new[] { "--connection-string", "-cs" }, "Contains the connection string to connect to the database"),
                new Option <string>(new[] { "--view-name", "-vn" }, "The name of the view to inline"),
                new Option <FileInfo>(new[] { "--view-path", "-vp" }, "The path of the view as a .sql file (including create statement)"),
                new Option <bool>(new[] { "--strip-unused-columns", "-suc" }, () => true),
                new Option <bool>(new[] { "--strip-unused-joins", "-suj" }),
                new Option <bool>("--generate-create-or-alter", () => true),
                // TODO: DatabaseView.parser (hardcoded to TSql150Parser)
            };

            rootCommand.Handler = CommandHandler.Create <string, string?, FileInfo?, bool, bool, bool>((connectionString, viewName, viewPath, stripUnusedColumns, stripUnusedJoins, generateCreateOrAlter) =>
            {
                var cs = new SqlConnectionStringBuilder(connectionString);
                if (!cs.ContainsKey(nameof(cs.ApplicationName)))
                {
                    cs.ApplicationName = ThisAssembly.AppName;
                    connectionString   = cs.ToString();
                }

                var connection = new DatabaseConnection(new SqlConnection(connectionString));

                string viewSql;
                if (!string.IsNullOrEmpty(viewName))
                {
                    viewSql = connection.GetViewDefinition(viewName);
                }
                else if (viewPath != null)
                {
                    viewSql = File.ReadAllText(viewPath.FullName);
                }
                else
                {
                    throw new InvalidOperationException("At least --view-name or --view-path is required.");
                }

                if (generateCreateOrAlter)
                {
                    viewSql = DatabaseView.CreateOrAlter(viewSql);
                }

                var inliner = new DatabaseViewInliner(connection, viewSql, new()
                {
                    StripUnusedColumns = stripUnusedColumns,
                    StripUnusedJoins   = stripUnusedJoins,
                });

                Console.WriteLine(inliner.Sql);
                return(inliner.Errors.Count > 0 ? -1 : 0);
            });

            return(rootCommand.Invoke(args));
        }
Example #2
0
        /// <summary>
        /// Creates a new instance of <see cref="DatabaseViewInliner"/>.
        /// </summary>
        public DatabaseViewInliner(DatabaseConnection connection, string viewSql, InlinerOptions?options = null)
        {
            this.connection = connection;
            this.options    = options ?? new();

            var sw = Stopwatch.StartNew();

            // Parse definition
            var(view, errors) = DatabaseView.FromSql(connection, viewSql);
            if (view == null)
            {
                // NOTE: Should not happen :)
                Sql = "/*\nFailed parsing query:\n" + string.Join("\n", errors.Select(e => e.Message)) + "\n\nOriginal query was kept:\n*/" + viewSql;
                return;
            }

            View = view;

            var tree       = view.Tree;
            var references = view.References;

#if DEBUG
            var treeVisualizer = new DomVisualizer();
            treeVisualizer.Walk(tree);
#endif

            // Recursively check nested views (and track functions for information purpose)
            var referencedViews = references.Views;
            if (referencedViews.Count == 0)
            {
                // No nested views, return original SQL
                Sql = viewSql;
                return;
            }

            var knownViews = new Dictionary <string, DatabaseView>
            {
                { view.ViewName, view },
            };

            void AddView(NamedTableReference viewReference)
            {
                var viewName = viewReference.SchemaObject.GetName();

                if (!knownViews.ContainsKey(viewName))
                {
                    var referencedDefinition = connection.GetViewDefinition(viewName);
                    var(referencedView, _) = DatabaseView.FromSql(connection, referencedDefinition);
                    if (referencedView == null)
                    {
                        return; // TODO: Handle as error?
                    }
                    knownViews[viewName] = referencedView;

                    foreach (var nestedViewReference in referencedView.References.Views)
                    {
                        AddView(nestedViewReference);
                    }
                }
            }

            foreach (var viewReference in referencedViews)
            {
                AddView(viewReference);
            }

            // Inline views
            Inline(view);

            // Output result, starting with original SQL as comment + extra information from checking code (e.g. list used nested views and functions)

            new Sql150ScriptGenerator(GetOptions()).GenerateScript(tree, out var formattedSql);

            sw.Stop();

            var result = new InlinerResult(this, sw.Elapsed, viewSql, knownViews, formattedSql);

            Sql    = result.Sql;
            Result = result;
        }