Nitrite/Java: Possibility to have unique nullable indexes

[magicDGS]

Sometimes it would be nice to have a nullable unique field. Currently the way to ensure a unique constraint on indexed field(s) should be implemented in two nitrite calls:

1. query with a search for the unique field (in case of multiples, with an OR)
2. if not, insert the document/entity into nitrite

I believe that the unique-constraint of SQL databases supports this use case, and it would be nice to have this included in this library. Maybe a new type of index should be introduce to support this use-case.

[anidotnet]

Not all SQL database supports this feature. PostgreSQL support this by considering two null values are not equals/same. But in Nitrite, all null values are considered to be same.

[anidotnet]

If you really want to create a separate type of index for this, may be you can roll your own by extending the NitriteIndexer interface. One such example is already available as SpatialIndex. You can even raise a PR and introduce it the nitrite-support module, I'll be happy to accept the contribution.

[magicDGS]

I would be happy to contribute if I had some extra time from my personal project. To be honest, I would need to dig deeper in the library to do so, and I am not sure if I have the time.

I found the SingleFieldIndex implementation and I believe that at the moment won't be easy to extend to support nulls, as the DBNull already takes into account that null are equal, and thus the NitriteIndex::addNitriteIds already throws.

This would either require a refactor on those classes and thus possibilities to destabilize the library, or write the index from scratch without relying on already existing almost-identical features (which means that it will require more time to implement).

I will reconsider in case that I see a performance issue in my current use-case for nullable-unique-fields.