contraints like unique keys

dmartz · January 5, 2023, 5:12pm

Is there the concept of a unique key (ex: mysql)?

I see the REPLACE statement which is based on a constraint conflict, but it appears based on an internally generated ID value. Is there a way to expand the constraint?

[update]
Being new to manticore, I’m attempting to import data from another system and was hoping for a replace call to handle insert/update on any changes. Even knowing what records have been updated, the replace statement is only triggered on an ID field conflict which the external system is unaware of.

Is there a “best practices” for bringing data and updating with changes into manticore?

Sergey · January 6, 2023, 6:48am

No, only document ID is a unique value in Manticore.

Is there a “best practices” for bringing data and updating with changes into manticore?

Some users make the ID meaningful when syncing from another storage, for example by making it a hash of a combination of the other fields that make the record unique.

dmartz · January 8, 2023, 10:36pm

Is there any roadmap feature to add a unique value constraint? I have been looking for a reasonable alternative to Elastic Search and this has been compareable in some areas. In my view, synchronization options go along way to adoption, but I’m not yet certain to the target market here.

Sergey · January 10, 2023, 4:05am

Let me check with the team on this.

Sergey · January 11, 2023, 7:13am

@dmartz We’ve discussed it and it looks doable. Feel free to make a feature request on GitHub.

dmartz · January 11, 2023, 4:01pm

Added. Thank you Sergey.

github.com/manticoresoftware/manticoresearch

Feature request: user defined constraints (unique key)

opened 03:45PM - 11 Jan 23 UTC

darren-martz

Ingesting data into manticore based on a changing dataset is challenging with th…e ID field being the only unique data constraint. Constraints are a powerful tool for identifying conflicts for insert vs update actions. The REPLACE statement already exists for this purpose but only has the ID field to work from. Adding the ability to identify any field as UNIQUE would allow the REPLACE statement to update a record based on user content. Example: both mykey and specialnumber have unique value constraints `CREATE TABLE ( mykey TEXT UNIQUE, author TEXT, body TEXT, created TIMESTAMP, specialnumber INT UNIQUE )` Example: unique value constrain on mykey author and a second value constraint on specialnumber `CREATE TABLE ( mykey TEXT, author TEXT, body TEXT, created TIMESTAMP, specialnumber INT UNIQUE, UNIQUE(mykey, author) )` The REPLACE, INSERT, and UPDATE statements would then respect all unique value constraints. **Alternatives/Workarounds** 1. generate a 64-bit integer hash value from content and assign it to the built-in ID field. 2. drop the entire table and reload **Additional context** As someone exploring to replace Elastic Search, having decent synchronizing tools available can go a long way to adoption. https://forum.manticoresearch.com/t/contraints-like-unique-keys/1262/3

Henzeb · September 25, 2024, 2:53pm

I was looking for something similar, and I stumbled upon the mathematical functions Cantor Pairing and Szudzik Pairing. Both are algorithms that calculates a unique integer based upon 2 different integers in a specific order. So is with Szudzik Pairing 2 and 3 the outcome 11, but 3 and 2 is 14. This behaviour also allows you to reverse the process.