Posted by --CELKO-- on 10/28/06 18:17

>> there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>> But I really would love to hear how you would keep a list of links in an arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr > 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr)); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference_nbr INTEGER, @new_preference_nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference_nbr
THEN @new_preference_nbr
ELSE preference_nbr + SIGN(@old_preference_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference_nbr AND
@new_preference_nbr
OR preference_nbr BETWEEN @new_preference_nbr AND
@old_preference_nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksGaps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_nbr)
FROM UserLinks AS U1
WHERE U1.preference_nbr <= UserLinks.preference_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_id);

[Back to original message]