Auto increment ID key Datatable from Mashup

[tenegabi]

Hello,

I want to add information from Mashup and I want to have an ID incremental from Mashup that is start from 1.

I try to find something but anything use some service. I have some services already and I don't want to do this incremental from service, I want to do it from Mashup.

Thank you.

[Bikash_Panda]

@tenegabi  WIll GUID work for your requirement ?

[tenegabi]

Hello @Bikash_Panda ,

I don't know what to tell you, I have try already but it create this type of input: 9beb3c03-0af8-4642-82fa-1114dda72e10
I want to create only number and start from 1, it is simple to identify by users and by me. 

Thank you for the answer

[Bikash_Panda]

In that case, you may take a look at,

https://github.com/carlescm/ThingWorxConcurrencyExtension

This will help you generate unique number, but each time it will increment the value. So if you increment the value and dont insert to datatable, then your values will not be consecutives though.

[tenegabi]

I am not able to download any Extension, the Policy of the company, so I need to use the Thingworx standard version. 

[Bikash_Panda]

Then store the number in a property, persist it, increment it each time and insert to DataTable But you may have issues when mutiple users try to use that at the same time. You might get duplicate, need to test though

[tenegabi]

In this case is same like service, and is not helpful at all. That I don't want to do it. Because I have a lot of Things and services connected to Mashup and if exist something by default. I can use service, because I have already created. 

[Rocko]

Not sure what you're trying to do and why it is a problem to call a service. Mashups are executed on the client machine, so there is no way to create a unique ID client side when you have multiple people access the mashup. How should the different clients know what the others are doing?

Services are executed server-side and they can coordinate this. You have to call a service anyway to update the information you added, so why not letting this service take care of it?

[tenegabi]

I have expected to have already by default in Mashup.
I don't want to use the service because in Mashup, even if are more than 1 user already when the button is pressed ( for add new row ) can have a trigger that bring last ID number and add next one. Like GUID, but I want to know the numbers because I want to make another service that delete the data from Datatable if I have more than 100000 registration. 

Thank you. 

[Constantine]

Use GUIDs and add timestamps to your data. Delete data based on the timestamp, e.g. "everything older than 1 month". Also, it looks like Streams would be a good storage mechanism for that -- they are keyed and timestamped by default.

It would help if you explain the use case you are trying to implement.

/ Constantine

[mstarnaud]

Hi Tenegabi.

I'm guessing the data will go in a DataTable entity? If that's the case I'm not sure what the best way would be, What I would do is on the Add service's Thing, I would put a property (like LastCreatedId) of type number and persistent, add every time the service adds a record I would look at that value and add 1. But I'm not sure it would be robust against odd scenarios, like concurrency.

If the data is stored in a database, there's a much easier way to do it. You can give the Identity setting to a column, this will do it automatically for you. Here's how you can set it with the wizard in MSSQL : 

[tenegabi · Accepted solution]

I made a service separate that add automatically the number.

The service is only call in Mashup and don't need another specifies connexions except Clicked button trigger and Loaded Trigger from Mashup. 

try{

    var dtValues= Things["AuditItBOS_Schedule"].GetDataTableEntries({

maxItems: 99999 /* NUMBER {"defaultValue":500} */

    });

    var dtMaxParams = {

            t: dtValues, // INFOTABLE

            columns: "ID", // STRING

            aggregates: "MAX", // STRING

            groupByColumns: undefined // STRING

          };

        var dtMax = parseInt(Resources["InfoTableFunctions"].Aggregate(dtMaxParams).MAX_ID); //INFOTABLE

}

catch(err){

    result = "Service call error ["+me.name + "]: on line " + err.lineNumber + ". Error Name: " + err.name + ". Error Message: " + err.message;

}

var result = dtMax+1;

[Bikash_Panda]

If you want to adopt this approach, i woudl suggest query the data table, sorted by timestamp desc, and only query 1 row, rather then querying 9999 rows. That is performance intensive and also will not work if you have 100000+ rows.

I am not sure of but also check what happens when multiple users do the same operation, same time, 

[tenegabi]

The Thingworx work in a way first come first serv and this think can't happen in theory. 
And this case must not happen. 

For case that you thing I will have more than 100000 rows I make another service that is erase information older than 6 months and in an month create only 2-3 thousands rows, so it's everything is all right.

[Rocko]

For starting with "I don't want to do this incremental from service, I want to do it from Mashup." you ended up with a solution that uses a service AND is pretty wasteful with resources, as @Bikash_Panda mentioned. No offense, but that is not a good solution and you will pretty likely have collisions (as Bikash also mentioned), because you created a race condition.

You can try to open that mashup with two machines and click the button to execute the service at the same time. Multithreading means any service can be paused in execution so another thread can be serviced. Your service is not synchronized, therefore two threads could have same dtMax, but you do you.

[tenegabi]

Hello @Rocko 

And then witch is the best solution?

First of all I don't want to use a service, I want to make some solution from Mashup, but that was my solution. 

Thank you. 

[Rocko]

I just wanted to point out the issue on you marked here as solution, so people reading this will know this will not work in a multi-user environment. You have to come up with a solution yourself, there were multiple suggestions in this thread which you all rejected.

Another suggestion would be to use a db sequence.

[tenegabi]

Because for my point of view this is the best for what I need. That I can use for Default version of Thingworx, I can't use plugins. 

For use DB also is unavailable in my company, so I can't use because I am not an Administrator User, I can't develop a solution using SQL. 

I can use Mashup or Things with service. 

[Constantine]

What you implemented will be

1. Slow to add records due to the Aggregation call on EACH insert
2. Slow to query, because 100,000 rows is A LOT for a Data Table. Streams are much more efficient in this regard
3. Unreliable, because it DOES allow ID collisions if you execute your insert service twice at the same time

Since you asked -- the best solution in your case would be one of those two:

1. Use AuditSubsystem, which is specifically designed for this use case.
2. Follow what I wrote in my reply on the 20th of November: Use Streams and delete data based on the timestamp, e.g. "everything older than 6 months". Streams are keyed and timestamped by default, so you don't have to worry about generating IDs, etc. They are indexed in a way that allows efficient inserts and timestamp-based queries.

/ Constantine