D3 - How to prevent duplicate index keys?

[Kevin Powick]

Unless I'm missing something, there doesn't seem to be any built-in functionality of D3 Indexes, or BASIC statements, to prevent duplicate index keys. i.e. A unique index.

A scenario would be an index on a file attribute updated by an end-user application, where one wants to ensure that the value of the user-updated attribute is unique for all items in the file.

For item IDs this is trivial, as D3 does not allow duplicate item IDs and one can use BASIC statements (e.g. readu / then / else) to not only check for existing item IDs at the application level, but prevent other processes from using them.

However, with indexes, it seems that all D3 has in BASIC is the "key" statement.  While helpful to interrogate the index, and a carefully written program can *almost* guarantee that index keys will not be duplicated, there doesn't seem to be a way to get a 100% guarantee.

Am I overlooking something, or has TigerLogic? ;-)

--

Kevin Powick

[Tony Gravagno]

The Key statement just helps us to find what's in the index, not to manage what goes in there. If the same value is in more than one item, the value and the ID are indexed. That's just the way it works.

 

If you want to enforce a unique value, I don't have the documentation open but the options in the Key statement help to identify what's there, so execute through it once and if you get an ID for a key value, it's not unique, loop  until the user provides something unique.

 

I have the feeling you've already been there, done that. What am I missing? Sorry.

T

[Anthony Youngman]

UV has the NO.DUPS keyword iirc.

When you create an index on UV you can specify that duplicate keys are
not permitted, so any attempt to save such a record will cause a write
error. Sounds like D3 doesn't have the equivalent, which is strange, or
am I missing something, too ... ?

Cheers,
Wol

On 11/04/13 08:37, Tony Gravagno wrote:
> The Key statement just helps us to find what's in the index, not to
> manage what goes in there. If the same value is in more than one item,
> the value and the ID are indexed. That's just the way it works.
>
>
>
> If you want to enforce a unique value, I don't have the documentation
> open but the options in the Key statement help to identify what's there,
> so execute through it once and if you get an ID for a key value, it's
> not unique, loop until the user provides something unique.
>
>
>
> I have the feeling you've already been there, done that. What am I
> missing? Sorry.
>
> T
>
>
>
>
>

> *From:*Kevin Powick

>
> Unless I'm missing something, there doesn't seem to be any built-in
> functionality of D3 Indexes, or BASIC statements, to prevent duplicate
> index keys. i.e. A unique index.
>
>
>
> A scenario would be an index on a file attribute updated by an end-user
> application, where one wants to ensure that the value of the
> user-updated attribute is unique for all items in the file.
>
>
>
> For item IDs this is trivial, as D3 does not allow duplicate item IDs
> and one can use BASIC statements (e.g. readu / then / else) to not only
> check for existing item IDs at the application level, but prevent other
> processes from using them.
>
>
>
> However, with indexes, it seems that all D3 has in BASIC is the "key"
> statement. While helpful to interrogate the index, and a carefully
> written program can *almost* guarantee that index keys will not be
> duplicated, there doesn't seem to be a way to get a 100% guarantee.
>
>
>
> Am I overlooking something, or has TigerLogic? ;-)
>

> --
> You received this message because you are subscribed to
> the "Pick and MultiValue Databases" group.
> To post, email to: mvd...@googlegroups.com
> To unsubscribe, email to: mvdbms+un...@googlegroups.com
> For more options, visit http://groups.google.com/group/mvdbms
>
>

[Brian Speirs]

Well, I've never used D3 but ...

I'm sure UniData had a unique clause for its indices. I've just looked at the OpenQM documentation, and that doesn't have anything.

Presumably, you can put a write trigger on the file, and check the index for the value. Of course, you'd need to code to catch the disallowed write.

Alternatively, you'd need to check the index for the value during the data entry and either get the user to enter a unique value, or create a new unique reference. For that matter, if it only needs to be unique (and not meaningful), it could all be done in the file trigger or general write process for the file: Just before the write, check the index for the value. If found, then create a new unique reference (sequential key). Test again if necessary, then do the write.

Cheers,

Brian

[Kevin Powick]

On Thursday, 11 April 2013 04:36:52 UTC-4, Wol wrote:

UV has the NO.DUPS keyword iirc.

D3 doesn't seem to have this, which is strange indeed.

--

Kevin Powick 

[Kevin Powick]

On Thursday, 11 April 2013 03:37:18 UTC-4, Tony Gravagno wrote:

I have the feeling you've already been there, done that. What am I missing? Sorry.

Yes, I've been there.  I understand the KEY statement.  I'm just saying that there does not appear to be any D3 built-in functionality to ensure unique index keys.  You can use the KEY statement to check for the existence of a key before writing the record, but does not *guarantee* that the key will not exist when you actually do write the record.

IOW, you can craft your program to *almost* guarantee no duplicate index keys, but that might not be good enough.

--

Kevin Powick 

[Kevin Powick]

On Thursday, 11 April 2013 06:07:12 UTC-4, Brian Speirs wrote:

 

Presumably, you can put a write trigger on the file, and check the index for the value. Of course, you'd need to code to catch the disallowed write.

I think that's pretty much as close as you can get to ensuring unique index keys.  IMO, it's a bit of a hack for functionality that should exist within D3 itself. 

I know we're supposed to consider triggers as the MV world's answer to referential integrity (RI), and they can help a lot, but MV products need to come a long way to match the RI functionality of RDBMS products.  Leaving RI at the application level, or within triggers, leaves a lot to be desired.

I believe that in D3, the only way to guarantee no duplicate index keys is to maintain your own secondary cross-reference file, where the item IDs of this xref file are the index keys in the primary file.  This way, you could use READU else/then on the xref file to lock index keys (item IDs).

This is more of a technical exercise than real world problem one is likely to encounter.  I'm not overly worried that my current project will result in a file being updated with duplicate index keys, but unless I create a secondary xref file, as described above, the possibility of a duplicate index key does exist.

--

Kevin Powick

[Tony Gravagno]

I don't think triggers will help because (IIRC) these are secondary processes that are fire asynchronously - trigger failure doesn't affect the write process. I did a Lot of unusual intersystem work with triggers in D3v9 and at this point I simply can't remember the basic flow, sorry.

 

I see what you mean now -  you want to try to guarantee unique values in a multi-user "simultaneous update" environment. Aside from your READU/xref solution you could use the LOCK statement around the Key/Write combination. That could be a real performance issue in a high-volume environment where this block is hit frequently but a great many of these scenarios where people picture heavy concurrency really aren't, so millisecond locks like this go un-noticed.

 

A final but reluctant (overkill) suggestion would be to use the  XSUEX "user exit". This is a very cool, virtually unknown feature that allows multiple processes to share the same memory space. In short you can use it like a sort of private lock table, and with a couple statements achieve the same functionality as a Lock statement without hitting the system lock table. This feature wasn't well documented prior to v9.0 but it's more of a "first class citizen" now.

 

In short, you're looking for inter-process functionality and here are two ways of accomplishing that without resorting to a new xref file and related read/updates.

 

HTH

T

 

 

 

 

 

From: Kevin Powick

 

On Thursday, 11 April 2013 06:07:12 UTC-4, Brian Speirs wrote:

 

Presumably, you can put a write trigger on the file, and check the index for the value. Of course, you'd need to code to catch the disallowed write.

 

I think that's pretty much as close as you can get to ensuring unique index keys.  IMO, it's a bit of a hack for functionality that should exist within D3 itself. 

 

I know we're supposed to consider triggers as the MV world's answer to referential integrity (RI), and they can help a lot, but MV products need to come a long way to match the RI functionality of RDBMS products.  Leaving RI at the application level, or within triggers, leaves a lot to be desired..

 

I believe that in D3, the only way to guarantee no duplicate index keys is to maintain your own secondary cross-reference file, where the item IDs of this xref file are the index keys in the primary file.  This way, you could use READU else/then on the xref file to lock index keys (item IDs).

 

This is more of a technical exercise than real world problem one is likely to encounter.  I'm not overly worried that my current project will result in a file being updated with duplicate index keys, but unless I create a secondary xref file, as described above, the possibility of a duplicate index key does exist.

 

--

Kevin Powick

--

[Kevin Powick]

On Thursday, 11 April 2013 12:10:28 UTC-4, Tony Gravagno wrote:

>
> I don't think triggers will help because (IIRC) these are secondary processes that are fire asynchronously - trigger failure doesn't affect the write process.
>

Not sure what you mean. If you use a callx (write) trigger and the callx subroutine executes the INPUTERR statement, then the write operation will be abandoned. The "failure" of this abandoned write operation can be captured with the ON ERROR clause of the basic WRITE statement.

>
>A final but reluctant (overkill) suggestion would be to use the XSUEX "user exit".
>

I was unaware of that user exit and it is certainly looks interesting. Thanks, but in this case, I don't think I want to go down that road. However, it has me thinking about some other areas where it might be useful.

--
Kevin Powick

[Tony Gravagno]

You're right on the INPUTERR thing, my bad. My task was to get triggers to work between systems, with updates here triggering updates there, with no code changes to the original source. This ultimately resulted in read/write triggers for both D3 Linux and Windows - not currently supported functionality but achievable with some rigorous code.

 

About XSUEX (also internally the u5b exit), there are times when we want inter-process data to be available immediately and we usually resort to file IO and locks to achieve it. Think about how Common is only common to a given process, not all users. XSUEX changes that. In a non-business scenario think about how cross-user data is critical for playing multi-user games. We're not writing games with these systems anymore but there is frequently a need for one process to hand a task over to another, or for processes to exchange messages with real-time data without polling a common file or using the expensive lock tables. Consider a scenario where a report is processing a million items and you want to know where it is from another process. You don't need to write a counter on every N iterations (disk/resource issue) - save the counter with a string assignment with XSUEX in the report generator and retrieve it with another dashboard process. you can monitor a lot of system activity like this. I think of this as just another D3 hidden gem. I have not yet done any sort of performance analysis on this but would be interested if anyone else does. These days I still tend to use disk IO with control tables just to keep code more portable, and because disk access isn't as expensive as it used to be, but I'm still curious about file lock penalties  vs XSUEX memory locking … and for all I know XSUEX does do file IO internally somewhere anyway.

 

T

 

From: Kevin Powick

[Scott Ballinger]

On Thursday, April 11, 2013 1:36:52 AM UTC-7, Wol wrote:

UV has the NO.DUPS keyword iirc.

Um, I am familiar with the NO.NULLS parameter to the UV CREATE.INDEX command, but I can't find any reference in the UV docs to a NO.DUPS option? Perhaps NO.NULLS is UniData-specific?

/Scott Ballinger
Pareto Corporation
Edmonds WA USA
206 713 6006

[Scott Ballinger]

Arrrgh... meant perhaps NO.DUPS is UniData-specific.
/Scott

[Chuck Stevenson]

I don't think you can prevent it from being built.
But you can control how SELECTINDEX behaves with
$OPTIONS PIOPEN.SELIDX

UV BasicRef.pdf describes the option thusly:  "In the SELECTINDEX statement, removes multiple occurrences of the same record ID in an index with a multivalued field."
In other words, it makes Universe SELECTINDEX behave like us old primates would expect.