Project Setup: How to manage table definitions and stored Data

[B0Bftl]

Hello!

Iam currently "migrating" an old project of mine to Git using this add-in, to finally be able to cleanly track Changes. Thanks for that 👍!

I do have one question about the setup, that i have not yet seen addressed in the wiki, though i might have missed it.
My project consists of a Frontend DB (all Forms & Logic Components) and a backend DB (Only Tables, nothing else)

Getting the Frontend DB into git was quite straightforward. Currently, Iam struggling with the Backend DB.
Of course i do not want to store my data in VCS, i definitely want to store my table defintions though.
Now, getting the Definitions is easy, but what's the recommended way to handle the data?
When I export/import the Backend a new DB is built from scratch based on the source files, hence no data is stored there.
To be able to enable merge based Builds, i seem to have to start off from a clean full build.
Migrating the data by hand would be very painful, as its a lot of stuff across lots of tables.

So my question: How do i get the backend table definitions into VCS with a way, to import them into a Database that contains Data.
This process would also be required for any deployments i would do based on the source code.

Thanks for the help!
Lukas

[joyfullservice]

Hi Lukas,

That is a great question! As you have probably observed, the majority of database components can be merged into an existing database. Tables are a bit different in that they can contain both structure and data. I can certainly see the benefit of merging a change in table structure while preserving the table data. In the current functionality, merging a table would replace the existing table with one created from the source file. This would also clear any data in the table, unless that table's data was also stored in version control. (Which is usually not the ideal setup.) To describe this in SQL terms, the current functionality is more of a REPLACE TABLE than an ALTER TABLE.

I think this would be a great suggestion for an enhancement to the add-in. There are a couple ways that we could approach this on a technical side. One approach would be to rename the existing table, import the new table, then transfer the data from the old table to the new table. (Either directly, or via XML export/import.) For a simple structure like what you are currently using, this would probably work pretty well. In a more complicated scenario we might need to consider additional things like table relationships.

Another approach would be to import the new table with a temporary name, then update the properties and fields of the existing table to match the updated structure. This would preserve the original object ID, but would be a little more complicated looping through the fields, properties, indexes, etc... to make the existing table match the new definition.

Either way we do it, we would also want to handle scenarios where columns were added, removed, or renamed. Field sizes or data type changes could also cause truncation of data, Renaming a column without losing data would probably require some type of migration script.

Finally, one other potential approach that would work right now is the BeforeExport and AfterMerge/AfterBuild hooks. These call VBA code that you have in your database and give you the flexibility to do anything you want. (Export data to another location outside VCS, and import data back in after reloading the table definition.)

Feel free to open an issue for a suggested enhancement in this project, and we can use that to flesh out the details of exactly how a merge could update a table structure while preserving existing table data. Perhaps it could be as simple as exporting the table data to a temporary (XML) file, then importing it back into the table after updating the structure from source.

Thanks again for the feedback!

• Adam