Al: Question : Autoincrement and upgrade of an extension V2.0

[gudrunleos]

Not quite the same as described in #1079.

In our extension, we use an integer with property autoincrement=True to index a table and this integer field is used to mark data in another table that belongs to an entry in it.
The table entry is then processed, its data copied to another table and the entry finally deleted. We can have more than one entry in the table at each time, but the linked data can only be linked to one entry.

If this table is empty upon upgrade of the extension, the table is deleted during unpublish and created again during publish of the new version but no data obviously restored. This causes the autoincrement field to be reset - the first entry after upgrade becomes no. 1 instead of n + 1 where n is the last used number.

To reproduce:
create a table with an autoincrement field (EntryNo).
insert an entry into the table (EntryNo = 1)
insert an entry into the table (EntryNo = 2)
delete the inserted entries
unpublish the extension
publish the extension again
insert an entry into the table => (EntryNo = 1)

This is a problem in V1 Extension - will this still be a problem in V2 Extensions?

[HunterFeltman-MSFT]

In short: No, this shouldn't be a 'problem' in V2 extensions.

In not so short:
For V1, this was a consequence of the 'archive' table approach to retaining data across extension uninstalls/reinstalls.
When you 'uninstall' a V1 extension, we do 2 things:

1. 'Rolloff' data from extension tables into 'archives' that can be restored later
2. Synchronize the SQL schema to remove tables added by the extension

Upon subsequent reinstall or 'upgrade', the table is reconstructed and the developer's upgrade code dictates the restoration of data

This of course results in the deletion of the original table and thus the 'problem' you've laid out.
But you already knew all that.

For V2, we have changed the way that data is stored and handled during the lifetime of an extension:

1. Instead of actually adding new extension fields to existing base tables, we now use 'companion' tables (full tables with links to the base tables they 'extend') to house new fields. You'll never work with them in AL code though because we abstract this away and let AL code work as if we had actually added the fields to the original tables.
   The advantage of this arrangement means that we no longer need to use 'archives' to store data between extension install/uninstalls. Instead it can just 'remain' in the companion tables and we don't have to touch SQL.
   Even better, this means that you no longer have to write 'restoration' logic if there are no changes in your data structures. The companion tables just become 'enabled' and so any data that was in them is now available.

1. We split off the 'synchronization' from install/uninstall and made it into an explicit step required before a given version of a V2 extension can be installed. I.e. The Sync-NavApp cmdlet.
   This cmdlet is responsible for adding or removing the schema elements that are included in your extension (tables & table extensions). It is what invokes the SQL commands to actually create/modify/delete your tables. It comes in two colors modes of operation:

   • The default 'add' mode, which creates new tables/companion tables
     Sync-NavApp -ServerInstance <instance> -Name <ExtName> -Version <ExtVer>

   • The 'clean' mode which deletes all schema elements from ALL VERSIONS of a target extension
     Sync-NavApp -ServerInstance <instance> -Name <ExtName> -Version <ExtVer> -Mode Clean

     This mode is only meant to be used when you are truly 'done' with an extension family. When you no longer care for any data held within the extension an you want to remove it completely from the system. Important to note that 'unpublishing' an extension does NOT do this for you. Even if you unpublish a V2 extension the schema elements will remain if you have not called Sync in the 'clean' mode. Also also important to note that you can still call Sync even after the extension is unpublished

These fundamental changes to the way that data is structured/stored in V2 mean that unless you explicitly do so with the Sync cmdlet, your tables will remain 'intact' across install/uninstall/upgrade, meaning that your AutoIncrement should continue to increment automatically. (Unless I'm just horribly horribly wrong.)

[hougaard]

So why does updating an extension v2 currently delete all data in its tables?

[HunterFeltman-MSFT]

That is only a consequence of how VSCode 'publishes' extension via the F5-like commands today.

The gist is that the VSCode publish wants to assert that you have a 'clean' environment when you deploy your extension. To accomplish this, it performs a full 'removal' of all version of the extension before publishing your new one. I.e. Uninstall, Sync -Mode Clean, Unpublish.
This 'clears out your environment and allows you start fresh', which is great for initial rapid development that might mean breaking changes. Unfortunately it also 'clears out your environment and allows you to start fresh', which isn't so great for testing/observing upgrades.

At current, the only real way to test an upgrade is via the cmdlets:

1. Publish/Sync/Install 'old' version of extension with cmdlets or VSCode
2. Develop 'new' version of extension
   
   
   
   • Bump the version number in the app.json
   
   
   • Use Ctrl+Shift+B to 'compile' it without publishing
   
   
3. Publish/Sync new version of your extension with cmdlets
4. Run the 'Start-NavAppDataUpgrade' cmdlet to run the upgrade from old to new
5. Validate/Verify stuffs
6. Lather, rinse, repeat.

There is ongoing work to provide a better 'upgrade' scenario from within VSCode.
See Issue #62.

[StanislawStempin]

62 has been implemented and will be available in the January 2018 update. It will let you preserve data in development non-upgrade scenarios. To perform upgrade testing you will for now still need to follow the steps presented in the comment above.