Event table @index operations are not uniform across stores

[niveathika]

Description:

We have behaviours as below,

Example - @Index('a', 'b')

1. In-memory table - An element inside @Index is considered as seperate index i.e 2 indices, 1. a, 2. b
2. MongoDB - Same as in-memory, however, a.b has to be valid MongoDB indices
3. RDBMS - The elements are combined to create only one index. i.e 1 index of a and b

Moreover, @Index annotation functionality also differs from @PrimaryKey functionality as in @PrimaryKey we combine all elements to create one primary key however in @Index this is seperate

Affected Siddhi Version:
Siddhi 4.x and relevant extensions

[mohanvive]

In databases, if you want to create indexes for multiple columns then you have to create individual indexes multiple time. And if we want to add multiple columns for same index then it is considered as composite index. Then we can have below approach.

If you wanted to create individual indexes for column a, b and c then
@index (a)
@index (b)
@index (c)

If we wanted to create composite index key then we can go as @index(a, b, c). Then, RDBMS extension behavior is correct. We should correct the behavior of in-memory and mongodb. As the first step, we'll print an error log for in-memory if multiple columns are defined within @index annotation and ask the user to define it separately. And we could work on improving the composite index key support for in-memory and mongo db store in future.

[niveathika]

+1 for correcting it this way. However, for MongoDB, there is an issue as only getting the column name is not enough. since for index creation, we have to pass 3 params, column name, sorting order and options,

db.myColl.createIndex(
   { score: 1}, { collation: { locale: "fr" } } )

For mongo DB as of now, the index annotation will be as follows,

@IndexBy("volume 1 {background:true,unique:true}")

However, if we want to standardise this, we can go with

@Index('volume:1',  '{background:true,unique:true}')

As it will be easy for composite keys as follws,

@Index('volume:1', 'score:-1',  '{background:true,unique:true}')

Any other suggestions for this?

[suhothayan]

As long as MongoDB does not have a composite index this approach will be fine.

[niveathika]

The support is finished for InMemory, RDBMS and MongoDB.

I have opened the issue for siddhi-store-redis in extension repo. siddhi-io/siddhi-store-redis#30