MemberJunction / MemberJunction/MJ
Build Utility to Reorder SQL Table Columns via Migration Script
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 308
Description
SQL Server does not natively support the ability to change column order for an existing table via an `ALTER TABLE` statement. As a result, our migrations, when new columns are added, end up having those columns all at the end of the table instead of wherever the developer intended them to be in the table's column order. Generally speaking this shouldn't affect functionality in any way as people aren't using column order to drive behavior. However, this is "messy" and it is important that we be able to control column order in our tables.
For this reason, we will introduce a new utility method within the `CodeGen` tool that is capable of generating just such a migration script. The idea is that:
1. Using SQL Server Management Studio (SSMS) a developer will order the columns in their table as desired - this can be done in the SSMS UI.
2. After this, the `CodeGen` tool will be run with a specific option where we will provide the table name(s) that we want column reordering scripts built for. There could be better ways to do this ranging from somehow auto-detecting changes to also doing this in another tool outside of CodeGen, but this seems like a logical place to do it, at least to start.
3. The CodeGen tool will generate a SQL script to a file in a location specified in the `config.json` file in a new section to be called "table_column_reordering". This new section will have the following properties:
- `table_names` - list of table names to generate scripts for - can be a single table name or comma delimited. Each table name is assumed to be prefixed with a schema name so the string would look something like `__mj.Entity, __mj.EntityField` etc
- `output_directory` a fully qualified directory path that resolves from the `cwd` that CodeGen runs from. For example we could provide a relative path to the `migrations` folder
- `file_prefix` - a string to be prepended onto each file generated by the routine. If omitted the output files will be just `column_reorder_tablename.sql` where tablename is replaced with the actual table name. Developers will change these files to merge them into one, or into other migration scripts etc and rename those files as desired for migration script purposes.
@jordanfanapour the first draft of the code that does the heavy lifting on this is here: https://github.com/MemberJunction/MJ/pull/631
Contributor guide
Assessment
This issue has not been assessed yet.