figure out repo organization to facilitate migration between versions about lter-core-metabase HOT 5 CLOSED

Here's a proposal:

1. We put SQL scripts in a sql folder.
2. sql/core-metabase.sql is the latest released version.
3. A migration folder stores scripts to migrate from one version to the next. It also includes a script to set up the database initially (v1.0.1.sql in the example), upon which the other scripts build.
4. Since we'll eventually make breaking changes, migration is a parent folder containing subfolders for each non-breaking sequence of scripts.
5. We follow SemVer rules and only break at the first number in a version number.
6. A brief README.md is included in the sql folder to remind people what the contents of this folder are.

Example:

project
│   CONTRIBUTING.md
│   LICENSE
│   README.md
│
└───sql
│   │   README.md
│   │   core-metabase.sql
│   │
│   └───migration
│      └───v1
│         │   v1.0.0.sql
│         │   v1.0.0-to-v1.0.1.sql
│         │   v1.0.1-to-v1.1.0.sql
│         │   ...
│      └───v2
│         │   v2.0.0.sql
│         │   v2.0.0-to-v2.0.1.sql
│         │   ...

We may want to include leading zeroes in things named by version number. You want to run scripts in order, and a user's file system might sort v1.2 BELOW v1.19 for example.

from lter-core-metabase.

@mobb , @lkuiucsb What do you think of this repo organization? Let's nail this down so @mobb can upload whatever starting schema she has in mind.

from lter-core-metabase.

that's a good start. probably every directory should have a readme.
couple comments:

First, what is the difference between migration/V1/v1.0.0.sql and sql/core-metabase.sql
right now, my thought is that they are the same.

Second, once an instance is populated, in reality, you don't go back - ie, every change (major or minor) is an ALTER. A v2 might be so radically different from v1 that it means that you start over (with an empty db) - in which case a migration path would have to include content export>reload too.

third, we should assume that there will be local forks that are never re-integrated with the core. We can't control local modifications, but should suggest a place to keep them. In the main project, that dir is empty except for the readme (which contains a list of best practices and lotsa warnings).

So it might look more like the below.

project
│   CONTRIBUTING.md
│   LICENSE
│   README.md
│
└───sql
│   │   README.md
│   │
│   └───core
│      │   README.md
 |         |
│      └───v1
│              │   v1.0.0.sql 
│              │   v1.0.0-to-v1.0.1.sql
│              │   v1.0.1-to-v1.1.0.sql
│              │   ...
│      └───v2
│              │   v1.1.0-to-v2.0.0.sql
│              │   v2.0.0-to-v2.0.1.sql
│              │   ...
│   └───local
│      │   README.md
│      

from lter-core-metabase.

migration/V1/v1.0.0.sql is the very first version of the core metabase. Once migration/V1/v1.0.0.sql is committed, that file should never change. We keep in forever for history, but once we reach v2, no one should be bothering with v1 any more (unless they prefer v1 over v2).

sql/core-metabase.sql is whatever the current version is, e.g., maybe two years from now it will be v3.2.4. This is the file that folks will get if they are coming to this repo for the first time. It's there for convenience, so they don't have to get v3.0 and then apply all the v3 updates. Once they install it, they can find the version number in the version table, and then apply additional updates as they are committed to the repo in the future.

I like having a readme in every folder unless the folder contents are easily understood.

I agree with your "Second" point, about ALTER and such.

The readme for the local folder can instruct users to put their modifications in here such that our fancy .gitignore will ignore them. Does anyone know how to ignore all subfolders within a folder but not the files (e.g., readme.md) directly within the folder? I don't, so if we can't figure that out, then we can put the instructions about local additions in sql/readme.md and those instructions can say to put local additions in a folder named local and then we can include /sql/local/ in our gitignore.

from lter-core-metabase.

For now, I am going to put the new script (to create vw_associatedparty) in the same sql dir. Two reasons:

1. we are still at version .9
2. as we make these tweaks, we will see some patterns develop, which may inform the organization

from lter-core-metabase.