I learned that the column type autoincrement is not compatible with Elastic DB.
Does that mean we can never use it or only in specific cases?
Here are already 3 examples where the autoincrement type is used today.
- add rows via QAction:
string rowKey = protocol.AddRowReturnKey(tablePid);
- add key on rows added via polling: e.g. WMI
- Service Overview Manager receiving alarm data via the "autoAdd" option:
If we can no longer use this, how can we achieve the same result for all the above cases?
For case 1: modify existing driver, new version range is not needed. Change the ColumnOption type from autoincrement to retrieved.
Simply add a single, saved, parameter that stores the highest PK value of the table.
-If the single parameter is empty then read out the table to determine the highest PK value and start from there and save the new PK in the single parameter (this covers existing elements with tables that had autoincrement, if table is empty then start with PK "1" -> this covers new created elements).
-If the single parameter is not empty, then the new PK = single parameter value + 1, and save the single parameter with the new PK value.
For case 2: this depends: for some cases a QAction could fill in the column, in other cases this column is the index (PK) column. In the latter case it's better to choose another PK column (such as the instance in case of SNMP polling) -> in that case a new version range will be needed to make it compatible with elastic.
For the WMI case: I'm wondering if this auto increment is still needed? This is coming from the Microsoft Platform driver and I can't see a case where :1 or higher is present, Windows adds # number to make it already unique, resulting in :0 for all cases. As the autoincrement column is the index PK column, there is no other option but to have a new version range to get rid of it. Alternative to avoid the range change is to make the original table fully retrieved, and poll the WMI data in a hidden table in the background, then copy the data to the original table, and PK value is then fixed ending with ":0". This way the range change is avoided, it will be compatible with Elastic, but it will cost some performance.
For case 3: I have no experience with that driver how the data is entering in that table.