Declared dependencies

[RachelAmbler]

@erikbra : I had the need to provide for declared file dependencies in grate so I put one together at https://codeberg.org/RachelAmbler/grate/src/branch/Add_Sql_Code_Dependency_Functionality

It's only used if specifically called for and includes all tests etc. I'm still doing some final code cleanup etc. so it's not ready for primetime, but if you're interested in this forming part of grate then let me know and I'll issue a pull request for it. (It is all fully documented with a new docs page for it)

This is the first of a few 'value add' pieces I need to add to bend grate to my will - however they may be more niche than you're looking for in some cases - but let me know your thunks and we can discuss accordingly.

[RachelAmbler]

Cc @wokket

[wokket]

Thanks for the invite mate, I'd missed this.

Can I ask what the situation is that you're hitting here to need this? Migrating from another tool or something?

I'll have a proper look through the code soonish, but from the quick first glance I'm interested, but not convinced this adds enough value (vs managing the dependencies with filename, rather than header comments) to be worth the extra complexity.

I am a bit scarred atm with the outstanding bugs we need to work through though so a bit gunshy on the new features atm :)

[RachelAmbler]

In my world, the object name (as in SQL Server) is a first class citizen. This is practical for two reasons:

1. If other folk look at the repo then they can easily find the object based upon object type then schema + table name. I have none developers who'll be looking at the repo and it needs to be clear to them what they're looking at.

2. Refactoring names isn't then an issue. Let me explain further here:
   In the current grate world if I created a table called say MySchema.MyName that had a foreign key constraint on MySchema.ATable then I'd need to name the filenames according, e.g.

001-MySchema.ATable.sql
002-MySchema.MyTable.sql

If then I needed to make MySchema.MyTable also dependent on Bobs.Table then I'd have to make it

001-Bobs.Table.Sql
001-MySchema.ATable.sql
002-MySchema.MyTable.sql

One can argue that since B comes before M why do this? Well, What if we then decide to move Bobs.Table to a new schema - Sue?

001-Sue.Table.Sql
001-MySchema.ATable.sql
002-MySchema.MyTable.sql

If later on you refactor MySchema.MyTable to remove the dependency to Sue.MyTable well know you need to look at the file names and potentially refactor again. And thus you get into manual refactoring hell.

My solution bypasses all of this - but only if requested by the user on the command line. Without them passing the correct flag in, none of the dependency code kicks in.

The beauty of this solution for me though is that the dependencies are handled in one location: The file itself. When modifying the script you can simply add or remove the associated line to instantly refactor the dependencies.

This also handles when a table structure changes.

Right now you may need to have a name like 001-091522-MySchema.MyTable.sql filename - that can get very hard to look at very quickly with a lot of objects. More so when it comes to factoring.

In my World My table folder has subfolders for each table: Objects/Tables/dim.Currency/001.sql for the first base version

My script header for dim.Warehouse that requires this table looks like this:

/***********************************************************************************************************************
Table:                                  Warehouse
Schema:                                 dim
Created:                                09/06/2022
Author:                                 Rachel Ambler
Use:
Requires:                               Tables\dim.Currency\*.sql

Notes
========================================================================================================================


Change History
Date            Initials    WI          Details
========================================================================================================================
09/06/2022      RHA         0023        Created
***********************************************************************************************************************/

And that's it. That totally defines everything I need to know. In addition anyone else looking at it can see the dependencies without needing to look at the definition.

If I ever get to the state where I no longer need that dependency then simply removing the entry performs all my refactoring. No other work needs to happen in the repository and therefore no risk of extra script suddenly executed simply because of a name change.

That help clarify this approach?

[RachelAmbler]

To answer your other question: We're moving off Redgate's Sql Source control for a few reasons:

1. It's slow because it does so much for you.
2. Because it tries to do so much for you, it's prone to bugs which can delay deployments (often by weeks).
3. Its state driven so one has to be really careful to ensure objects are excluded if they exist only on the destination.
4. It's expensive (because it does so much!)
5. Its SQL Server only - we'll quite possibly be moving over to Greenplum next year.

However the one thing which I do like about it is that the repository is clear: The table has a script and that's it. Every object is named exactly as it appears on Sql.

I'm a power user at Pure Romance - we automated a huge amount (including building SSIS packages on the fly because they're too large to do manually with a huge number of dependencies). For me grate is a terrific starting point and I've been cogitating a change in direction for the last year. However it doesn't do everything I need, but being Open Source that's fine. I can do that myself.

I totally understand if y'all prefer to keep grate simple - that's the beauty of forks. That said, all the changes I've put in are all new files, and in total only about 5 lines of current grate source were changed to handle this (I used #def flags to handle if the compiled code uses the current version of parsing the file system, or my version). The only other change was for the command line arguments which can be handled differently.

[erikbra]

Hi, @RachelAmbler !

Thanks a lot for starting on this! This feature is really, really interesting! I have looked at a codebase using Redgate tools as well, and tried to use grate instead, but I got stuck pretty quick, due to exactly this, dependency management.

I'm really keen to add the feature, I'd just take some time to discuss it, so that we can come of with the simplest/best solution together :)

In experience, handling dependencies "manually", via files, comments, etc, is a bit tedious. So, if we could create a way to "magically" handle this, it would be even better (if it is at all possible).

When it comes to programmability, I think we could have handled it by just trying to run scripts in the order they are, and save the ones that don't work, due to dependencies, for later (in a list of sorts).

The up folder is a bit of a challenge, I think, as it might contain both table/object definitions, and e.g. data insertions, deleting of columns, etc (especially after having a solution live in prod for some years, with structural changes, etc).

Since you have experience with the Redgate tools, do you know how they handle inter-script dependencies? Is there a service installed on the DB which handles it, do you know?

[erikbra]

I haven't had time to read through your work on dependencies thoroughly yet, I'll spend some more time on it as well. It looks really interesting!

[RachelAmbler]

Hi, @RachelAmbler !

Thanks a lot for starting on this! This feature is really, really interesting! I have looked at a codebase using Redgate tools as well, and tried to use grate instead, but I got stuck pretty quick, due to exactly this, dependency management.

I'm really keen to add the feature, I'd just take some time to discuss it, so that we can come of with the simplest/best solution together :)

In experience, handling dependencies "manually", via files, comments, etc, is a bit tedious. So, if we could create a way to "magically" handle this, it would be even better (if it is at all possible).

When it comes to programmability, I think we could have handled it by just trying to run scripts in the order they are, and save the ones that don't work, due to dependencies, for later (in a list of sorts).

The up folder is a bit of a challenge, I think, as it might contain both table/object definitions, and e.g. data insertions, deleting of columns, etc (especially after having a solution live in prod for some years, with structural changes, etc).

Since you have experience with the Redgate tools, do you know how they handle inter-script dependencies? Is there a service installed on the DB which handles it, do you know?

The only way to have a 'pure' implementation of dependency handling is if you implement some sort of Sql Parser handling. Which is neither easy, nor an exact science.

Redgate handles it by dedicating itself to just SQL Server. It uses the Redgate Sql Compare engine under the hood (Sql Compare and Sql Source Control share a huge number of similarities). This can be a blessing and a curse. I'm not 100% sure exactly how it handles the dependencies, but it works most of the time, just not 100% of the time.

When it works well, it's great, when it fails it can cause no end of delays and workarounds. So even after a decade or more of experience, this is something even Redgate struggles with.

The other issue with Redgate is that is can be very slow. Our current Warehouse environment has 4 intertwined databases which is handled by Sql Source Control, built by Sql DLM automation via Azure, and deployed using Octopus Deploy using the Sql DLM deployment tasks. Which is great for effectiveness but can take upwards of 30 minutes to do a single deployment...! Even builds can take upwards of 10 minutes.

Hence my solution. It doesn't do any parsing of the script, but simply requires the user annotate their script with whatever dependencies they feel are required.

In our new mart, I have all our fact tables dependent on Tables\dim.*.sql which solves any number of issues for me. A few of my dimension tables have very specific dependencies, so I declare them explicitly.

The alternative to this was file naming which has already created an absolute nightmare in a previous repo (that I'm ripping out) and this enables me to create a cleaner, purer environment.

I'm also using JetBrains DataGrip to do all our development - this works very nicely because I can do raw base level 'kick the tires' development on a localized container, but then issue a grate command to then try my code out on the first level development environment.

This has already paid off humungous dividends and I've been able to get into the process of some very rapid deployments as a result.

My first tests have shown we could do a warehouse deployment using grate and my dependency code is just 1 minute...

I didn't half ass the code either. It employs a proper topological analyzer to do it's work so it creates a Directed acyclic graph to handle the process to ensure it doesn't miss anything. It also has some extra checks to look for cyclic dependencies. All the tests are in there as well so from all my raw, and real-life testing, it looks somewhat decent for a first hit!

I should add, I'm no developer so you'll have to forgive any code mistakes - my role at PR is as a Manager of Database & BI Systems - but needs must and these needs most certainly must for our environment!

[erikbra]

@RachelAmbler , Thanks a lot for your work here. I have had the time to look through it a bit more thoroughly now, and I like the feature!

I still have some questions on how this will work in a long-lived sources code base, if you change dependencies of tables, etc, over time, but that is perhaps a different question.

I'd really like to merge this into grate, as I think it would benefit the whole of grate's user base if we could keep features in the main repo, and not having to have forks all over because I'm a grumpy old guy, and don't want to merge stuff :)

That said, I think some cleanup of the code would be good before I merge this into main. How would you prefer to work on this? I have some suggestions, firstly regarding the reading of files in the DependencyHandler, which could be done easier. And, I think I would prefer to separate out the file sorting as a "first-class citizen" of its own (interface/abstract class), where the current sorting behaviour (by filename) could be the default, and the "sort-by-dependencies" strategy that you have created could be an alternate one :)

And, in addition, I would appreciate some unit tests on the topological sort as a separate unit, as it is quite complex.

I think it would be easier to work together on these fint tunings together if you make a pull request on the changes (I can't see that you have?). I'm not sure if you can do this from Codeberg (I haven't used that previously), or if you need to push it to a Github account.

Do you think you can please create a PR, so we can get closer to getting this merged? And again, I appreciate your work!

[RachelAmbler]

@erikbra Regarding the Unit tests - they're all in there - I did a copious number of them. Let me get to creating a PR in the next week or so - I'm deep into it at work ATM so my time is limited.

As a couple of asides - we've been using this in earnest in two of our projects at it's working terrifically - especially where we have some nasty complexities in terms of dependencies. In addition since we use JetBrains DataGrip for our development work, its refactoring handles the renaming quite nicely as I discovered last last when I renamed on of my table scripts and the deployment still worked. The moment I fired off a deployment I winced expecting it to fail and it was only after I went to inspect the procedure that I was expecting to fail, only to see that DataGrip had also handled that change for me. So I guess it kinda depends on the tooling you use.

There's another feature that I'm going to be exploring next - which is expanding grate to run executables - scripts or binaries - reason being that some of our deployments will need to be able to load static data. The data itself will be zipped in a git-lfs folder but I'll need to call other items to then unzip and bulk load. I'll add a new discussion here once I'm on that though.

[wokket]

I'll wait for the next discussion to see the details, but advance warning that I'm (probably) going to have reservations about adding the ability to run executables via grate on security grounds.

[theiam79]

I know this discussion is pretty stale, but I'm curious if it ever made any progress? We have many functions/procedures with dependencies, and I think a feature like this would be welcome. I considered using subfolders to group scripts so that they can be run after any required scripts, but would prefer to avoid the discoverability hit of nested folders.

[erikbra]

Hi, @theiam79 - thanks for joining the discussion. No, I'm sorry, this kind of stopped. I do love the idea of being able to control the dependencies in the scripts, and stating them, and make grate handle the ordering. But I feel that declaring dependencies in a comment in the files will be hard to maintain. Others might have different opinions, and I feel a bit bad that I never probably followed this PR through.

I don't have a lot of experience in developing large database-based applications. My applications are largely built in the application layer (using dotnet, Java, whatever, usually dotnet), and I'm using databases mostly for storage. So my use of stored procedures is rather limited. This might mean that I haven't seen the same need to be able to declare dependencies between stored procedures, as my stored procedures tend to be self-contained, and only perform some data manipulation (typically CRUD).

What do you think, @theiam79 - if you have a lot of functions and procedures with dependencies, do you think @RachelAmbler s suggestion in this thread would be a good way for you to handle it?

My dream scenario is that grate just figures this out itself. But I have thought a lot about it, and, of course you can perform some statical analysis beforehand, to try to figure out the dependencies, but it's difficult to get it right without actually trying. A solution could be to run grate in "figure out the order of dependencies in the stored procedures" mode, which ran in a loop, tried to apply the functions and stored procedures, and when it fails, reorder them, try again (in a structured manner, of course). But I'm not sure whether doing this would work for people, whether we run it "offline" (in a containerized environment e.g), to sort out the depenedencies, or against your live database.

Do people have some opinions on either the current suggestion by @RachelAmbler , or my "auto" suggestions? Which one do you think would work best? Is my dream scenario even possible? Or is it too ambitious?