PostgreSQL, drag and swap

Question

I have successfully solved situation regarding swapping rows by value here what still work excellent.
But by using this function I see some lacks of functionality in mean of drag and drop rows.
I try to get solution here where discussion goes wrong way and offered solution is not adequate since they need additional columns.

Here is well known table:

DROP TABLE IF EXISTS kalksad1;

CREATE TABLE kalksad1(
kalk_id     int PRIMARY KEY,
brkalk      integer, 
brred       integer, 
description text);

INSERT INTO kalksad1 VALUES
(12, 2, 5, 'text index 12 doc 2 row 5'),
(26, 2, 1, 'text index 26 doc 2 row 1'),
(30, 2, 2, 'text index 30 doc 2 row 2'),
(32, 4, 1, 'text index 32 doc 4 row 1'),
(36, 1, 1, 'text index 36 doc 1 row 1'),
(37, 1, 2, 'text index 37 doc 1 row 2'),
(38, 5, 1, 'text index 38 doc 5 row 1'),
(39, 5, 2, 'text index 39 doc 5 row 2'),
(42, 2, 3, 'text index 42 doc 2 row 3'),
(43, 2, 4, 'text index 43 doc 2 row 4'),
(46, 3, 1, 'text index 46 doc 3 row 1'),
(47, 3, 2, 'text index 47 doc 3 row 2');

Object of manipulation is reordering values of column "brred"(row) under same "brkalk"(doc).
Let "brkalk" be 2.

Now I would like to get reordering/swapping according to drag and drop needs where swapping just one row look unnatural. I have datagrid binded to kalksad1 table so I will describe situation looking in my datagrid filled with query "... ORDERED by brred".

If I could explain query by words that would be...
Example 1:
Under doc 2 I will drag row 4 and drop it to position of row 2.
For that is needed following steps:
1) Remember data of row 4.
2) In row 3 replace value of "brred" from 3 to 4.
3) In row 2 replace value of "brred" from 2 to 3.
4) In remembered data from step 1) change value of "brred" from 4 to 2.

Example 2:
Under doc 2 I will drag row 1 and drop it to position 3.
That can go like this:
1) Remember data of row 1.
2) In row 2 replace value of "brred" from 2 to 1.
3) In row 3 replace value of "brred" from 3 to 2.
4) In remembered data from step 1. change value of "brred" from 1 to 3.

Idea that this may be possible comes from elegant solution with swapping and SO questions like this, this and this. I make examples based on my thinkings but that shouldn't go that way if better exist.

Please if someone can write described query at way similar to those for swapping of user Roman Pekar.

EDIT: Solution based on Example1 from Tometzky  

Imports Npgsql

Public Class Form1
Dim dServer As String = "127.0.0.1"
Dim dPort As String = "5432"
Dim dUser As String = "postgres"
Dim dPass As String = yourpass
Dim ddatabase As String = yourdatabase
Private dragrect As Rectangle
Private dragindex, dropindex As Integer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim conn As New NpgsqlConnection(String.Format( _
            "Server={0};Port={1};User Id={2};Password={3};Database={4};", _
             dServer, dPort, dUser, dPass, ddatabase))

    conn.Open()
    Using t As NpgsqlTransaction = conn.BeginTransaction()
        Using cmd As New NpgsqlCommand( _
            "DROP TABLE IF EXISTS kalksad1;", conn)
            cmd.ExecuteNonQuery()
        End Using

        Using cmd As New NpgsqlCommand( _
            "CREATE TABLE kalksad1(" & _
            "kalk_id     int PRIMARY KEY, " & _
            "brkalk      integer, " & _
            "brred       integer, " & _
            "description text);", conn)
            cmd.ExecuteScalar()
        End Using

        Using cmd As New NpgsqlCommand( _
            "INSERT INTO kalksad1 VALUES" & _
            "(12, 2, 5, 'text index 12 doc 2 row 5'), " & _
            "(26, 2, 1, 'text index 26 doc 2 row 1'), " & _
            "(30, 2, 2, 'text index 30 doc 2 row 2'), " & _
            "(32, 4, 1, 'text index 32 doc 4 row 1'), " & _
            "(36, 1, 1, 'text index 36 doc 1 row 1'), " & _
            "(37, 1, 2, 'text index 37 doc 1 row 2'), " & _
            "(38, 5, 1, 'text index 38 doc 5 row 1'), " & _
            "(39, 5, 2, 'text index 39 doc 5 row 2'), " & _
            "(42, 2, 3, 'text index 42 doc 2 row 3'), " & _
            "(43, 2, 4, 'text index 43 doc 2 row 4'), " & _
            "(46, 3, 1, 'text index 46 doc 3 row 1'), " & _
            "(47, 3, 2, 'text index 47 doc 3 row 2');", conn)
            cmd.ExecuteNonQuery()
        End Using
        t.Commit()
    End Using

    With DataGridView1
        .AllowDrop = True
        .MultiSelect = False
        .Dock = DockStyle.Fill
        .SelectionMode = DataGridViewSelectionMode.FullRowSelect
        .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        .Columns.Add("col1", "ID")
        .Columns.Add("col2", "Doc")
        .Columns.Add("col3", "Row")
        .Columns.Add("col4", "Description")
    End With

    FillData(0)
End Sub

Private Sub FillData(ByVal dropindex As Integer)

    DataGridView1.Rows.Clear()

    Try
        Using mCon As New NpgsqlConnection(String.Format( _
                      "Server={0};Port={1};User Id={2};Password={3};Database={4};", _
                      dServer, dPort, dUser, dPass, ddatabase))

            mCon.Open()
            Using mCmd = New NpgsqlCommand( _
                      "SELECT kalk_id, brkalk, brred, description " & _
                      "FROM kalksad1 " & _
                      "WHERE brkalk='2' ORDER BY brred", mCon)

                Using reader As NpgsqlDataReader = mCmd.ExecuteReader()
                    While (reader.Read())
                        DataGridView1.Rows.Add(New String() _
                        {CStr(reader("kalk_id")), _
                         CStr(reader("brkalk")), _
                         CStr(reader("brred")), _
                         CStr(reader("description"))})
                    End While
                End Using
            End Using
        End Using
    Catch ex As Exception
        Debug.Print(ex.Message)
    End Try

    ''selecting a row
    If dropindex < 0 Then dropindex = 0
    With DataGridView1
        .Rows(dropindex).Selected = True
        .CurrentCell = .Item(0, dropindex)
    End With
End Sub

#Region "dragdrop"
Private Sub DataGridView1_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView1.DragDrop
    Dim p As Point = Me.PointToClient(New Point(e.X, e.Y))
    dropindex = DataGridView1.HitTest(p.X, p.Y).RowIndex

    If (e.Effect = DragDropEffects.Move) Then
        Dim dragRow As DataGridViewRow = CType(e.Data.GetData(GetType(DataGridViewRow)), DataGridViewRow)

        Dim _from As Integer = dragindex + 1 ''grid is zero based, document is 1 based
        Dim _to As Integer = dropindex + 1

        Dim updown As String = ""
        If _from < _to Then    ''correction for up
            _to = _to + 1
            updown = "!"
        End If

        '' PROCEDURE HERE -----------------------------------------------------------------
        Dim affected As Integer = 0
        Try
            Using conn As New NpgsqlConnection(String.Format( _
                          "Server={0};Port={1};User Id={2};Password={3};Database={4};", _
                          dServer, dPort, dUser, dPass, ddatabase))

                conn.Open()
                Using t As NpgsqlTransaction = conn.BeginTransaction()
                    Using cmd As New NpgsqlCommand( _
                          "UPDATE kalksad1 SET brred=_brred " & _
                              "FROM (" & _
                              "  SELECT " & _
                              "    row_number() OVER (" & _
                              "      ORDER BY brred<" & _to.ToString & " DESC, brred" & updown & "=" & _from.ToString & " DESC, brred>=" & _to.ToString & " DESC, brred" & _
                              "    ) AS _brred," & _
                              "    kalk_id AS _kalk_id " & _
                              "FROM kalksad1 " & _
                              "WHERE brkalk=2 " & _
                              "ORDER BY _kalk_id" & _
                              ") AS _ " & _
                              "WHERE kalk_id=_kalk_id AND brred!=_brred;", conn)

                        affected = CInt(cmd.ExecuteNonQuery())
                    End Using
                    If affected > 0 Then t.Commit()
                End Using
            End Using
        Catch ex As Exception
            Debug.Print(ex.Message)
        End Try
        ''---------------------------------------------------------------------------------
        FillData(dropindex) ''clear, fill and select dropped row
    End If
End Sub

Private Sub DataGridView1_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView1.DragOver
    e.Effect = DragDropEffects.Move
End Sub

Private Sub DataGridView1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown

    dragindex = DataGridView1.HitTest(e.X, e.Y).RowIndex
    If dragindex > -1 Then
        Dim dragSize As Size = SystemInformation.DragSize
        dragrect = New Rectangle(New Point(CInt(e.X - (dragSize.Width / 2)), CInt(e.Y - (dragSize.Height / 2))), dragSize)
    Else
        dragrect = Rectangle.Empty
    End If
End Sub

Private Sub DataGridView1_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseMove

    If (e.Button And MouseButtons.Left) = MouseButtons.Left Then
        If (dragrect <> Rectangle.Empty AndAlso Not dragrect.Contains(e.X, e.Y)) Then
            Me.DoDragDrop(DataGridView1.Rows(dragindex), DragDropEffects.Move)
        End If
    End If
End Sub
#End Region

End Class

So, basically you want to _rotate_ the value for column `brred` for a certain range of rows? — wildplasser, Oct 19 '13 at 12:26
I dont' know for "rotate". Basically I would like to TAKE, MAKE PLACE and then to INSERT to this place, I don't know to explain more precise... And yes, all manipulation should be only on value of brred. — Wine Too, Oct 19 '13 at 12:38
Yes, after making a place values between _from and _to should be renumbered. If you can use NET and Npgsql here is workable example. It work good for dragging to down. — Wine Too, Oct 19 '13 at 12:45
Well, you could add the intended result to your question. I am unable to read your script language, and it seems rather verbose and procedural. If you actually want to _rotate_, I think my flipflag trigger (in your second link) could be adapted to suit your needs. — wildplasser, Oct 19 '13 at 12:45
Well, it is partially some kind of rotation plus little more. That example is a bit complicated for me since I never use triggers and functions. But this one can be adapted for working properly in both directions. In worst case I can make conditional code... Thank you for helping with PostgreSQL! — Wine Too, Oct 19 '13 at 12:51
BTW: the flipflag trigger is about 1/2 the LOC of your procedural code, plus it would even work irrespective of the front end code that uses the DB. It might be more difficult, but at least it is smaller. — wildplasser, Oct 19 '13 at 13:03
Of course. I am very sorry for not be able (don't know) to use all those nice features of PostgreSQL. But I will learn some day! — Wine Too, Oct 19 '13 at 13:19

Tometzky · Accepted Answer · 2013-10-18T23:15:20.710

Example 1:

update kalksad1 set brred=_brred
from (
  select
    row_number() over (
      order by brred<2 desc, brred=4 desc, brred>=2 desc, brred
    ) as _brred,
    kalk_id as _kalk_id
  from kalksad1
  where brkalk=2
  order by _kalk_id
) as _
where kalk_id=_kalk_id and brred!=_brred;

Example 2:

update kalksad1 set brred=_brred
from (
  select
    row_number() over (
      order by brred<4 desc, brred!=1 desc, brred>=4 desc, brred
    ) as _brred,
    kalk_id as _kalk_id
  from kalksad1
  where brkalk=2
  order by _kalk_id
) as _
where kalk_id=_kalk_id and brred!=_brred;

If you have unique index on (brkalk,brred) then it would be more complicated, as during renumbering there'll be duplicate brred.

But for many rows I'd recommend using something which was very useful in the days of BASIC language on 8bit computers - number your rows with gaps.

So instead of:

(26, 2, 1, 'text index 26 doc 2 row 1'),
(30, 2, 2, 'text index 30 doc 2 row 2'),
(42, 2, 3, 'text index 42 doc 2 row 3'),
(43, 2, 4, 'text index 43 doc 2 row 4'),
(12, 2, 5, 'text index 12 doc 2 row 5'),

use:

(26, 2, 1024, 'text index 26 doc 2 row 1'),
(30, 2, 2048, 'text index 30 doc 2 row 2'),
(42, 2, 3072, 'text index 42 doc 2 row 3'),
(43, 2, 4096, 'text index 43 doc 2 row 4'),
(12, 2, 5120, 'text index 12 doc 2 row 5'),

Then your examples would just look like:

Example 1: update kalksad1 set brred=(2048+1024)/2 where kalk_id=43, which would change it to:

(26, 2, 1024, 'text index 26 doc 2 row 1'),
(43, 2, 1536, 'text index 43 doc 2 row 4'),
(30, 2, 2048, 'text index 30 doc 2 row 2'),
(42, 2, 3072, 'text index 42 doc 2 row 3'),
(12, 2, 5120, 'text index 12 doc 2 row 5'),

Example 2: update kalksad1 set brred=(4096+3072)/2 where kalk_id=43, which would change it to:

(30, 2, 2048, 'text index 30 doc 2 row 2'),
(42, 2, 3072, 'text index 42 doc 2 row 3'),
(26, 2, 3584, 'text index 26 doc 2 row 1'),
(43, 2, 4096, 'text index 43 doc 2 row 4'),
(12, 2, 5120, 'text index 12 doc 2 row 5'),

Only when there's no gap between rows where the target should be, you'd need to first renumber rows using for example:

update kalksad1 set brred=_brred*1024
from (
  select row_number() over (order by brred) as _brred, kalk_id as _kalk_id
  from kalksad1
  where brkalk=2
  order by _brred desc
) as _
where kalk_id=_kalk_id;

This would be much aster than changing every row between source and target. But this'll only matter when there may be many rows to change.

Hi Tometzky, that is really unacceptable because of many reasons. Hope better (more elegant) solution exist in mighty PostgreSQL. — Wine Too, Oct 18 '13 at 22:31
WOW Tometzky, that seem's really work as expected. Thank you! I try through PGAdmin. Tomorrow I will try to apply that on real data and program. There are very little differences between example1 and example2. Could same query be used for drag/drop to up and drag drop to down? — Wine Too, Oct 19 '13 at 00:00
Didn't try yet on real data but here I add VB.NET code to see of those example table. Work excellent when dragging to down but improperly to up. Maybe this can be repaired? For try Npgsql should be accessible, Form1 should contain one datagridview. — Wine Too, Oct 19 '13 at 11:42
After many testing and trying I can say that second example definitely don't work good. First example do. — Wine Too, Oct 19 '13 at 18:53
Could you provide an example which does not work? We could debug this then. — Tometzky, Oct 19 '13 at 19:33
Sory Tometzky, I get it working now. Not elegant but workable, I updated my code. Only, can you put in code limitation like IF dropindex < 1 or dropindex > MaxCountOfDocRows THEN SKIP. I can do it from outside but is not fully reliable. — Wine Too, Oct 19 '13 at 20:24

score 1 · Answer 2 · answered Oct 19 '13 at 14:07

This handles the tabbing order by a set of triggers+ associated functions.

UPDATING the brred for a particular brkalk will cause all values between the old and new values to be rotated, either up or down.
DELETE will cause all the brred values above the OLD value (for the same brkalk) to be decremented (shifted down)
INSERT will cause all the brred values above the NEW value (for the same brkalk) to be incremented (shifted up)

To avoid recursively updating forever, one extra bit of information per row is needed: the flipflag (which should only be touched by the triggers, not by the application code. It could be hidden from the application by means of a view)

ALTER TABLE kalksad1 ADD COLUMN flipflag boolean DEFAULT false;

        -- This should be an UNIQUE constraint
        -- , but that would need to be deferrable.
CREATE INDEX ON kalksad1 (brkalk,brred);


        -- Trigger functions for Insert/update/delete
CREATE function rotate_brred()
RETURNS TRIGGER AS $body$

BEGIN
        UPDATE kalksad1 fr
        SET brred = brred +1
        , flipflag = NOT flipflag       -- alternating bit protocol ;-)
        WHERE NEW.brred < OLD.brred
        -- AND OLD.flipflag = NEW.flipflag -- redundant condition
        -- AND OLD.brkalk = NEW.brkalk
        AND fr.brkalk = NEW.brkalk
        AND fr.brred >= NEW.brred
        AND fr.brred < OLD.brred
        AND fr.kalk_id <> NEW.kalk_id             -- exlude the initiating row
                ;
        UPDATE kalksad1 fr
        SET brred = brred -1
        , flipflag = NOT flipflag
        WHERE NEW.brred > OLD.brred
        -- AND OLD.flipflag = NEW.flipflag
        -- AND OLD.brkalk = NEW.brkalk
        AND fr.brkalk = NEW.brkalk
        AND fr.brred <= NEW.brred
        AND fr.brred > OLD.brred
        AND fr.kalk_id <> NEW.kalk_id
        ;
        RETURN NEW;
END;

$body$
language plpgsql;

CREATE function shift_down()
RETURNS TRIGGER AS $body$

BEGIN

        UPDATE kalksad1 fr
        SET brred = brred -1
        , flipflag = NOT flipflag       -- alternating bit protocol ;-)
        WHERE fr.brred > OLD.brred
        AND fr.brkalk = OLD.brkalk
                ;
        RETURN NEW;
END;

$body$
language plpgsql;

CREATE function shift_up()
RETURNS TRIGGER AS $body$

BEGIN
        UPDATE kalksad1 fr
        SET brred = brred +1
        , flipflag = NOT flipflag       -- alternating bit protocol ;-)
        WHERE fr.brred >= NEW.brred
        AND fr.brkalk = NEW.brkalk
                ;
        RETURN NEW;
END;

$body$
language plpgsql;


        -- Triggers for Insert/Update/Delete
        -- ONLY for the case where brkalk is NOT CHANGED
CREATE TRIGGER shift_brred_u
        AFTER UPDATE OF brred ON kalksad1
        FOR EACH ROW
        WHEN (OLD.flipflag = NEW.flipflag AND OLD.brkalk = NEW.brkalk AND OLD.brred <> NEW.brred)
        EXECUTE PROCEDURE rotate_brred()
        ;
CREATE TRIGGER shift_brred_d
        AFTER DELETE ON kalksad1
        FOR EACH ROW
        EXECUTE PROCEDURE shift_down()
        ;
CREATE TRIGGER shift_brred_i
        BEFORE INSERT ON kalksad1
        FOR EACH ROW
        EXECUTE PROCEDURE shift_up()
        ;

        -- Test it
UPDATE kalksad1
SET brred = 2
WHERE brkalk = 2
AND brred = 4;

SELECT * FROM kalksad1
ORDER BY brkalk, brred;

RESULT:

UPDATE 1
 kalk_id | brkalk | brred |        description        | flipflag 
---------+--------+-------+---------------------------+----------
      36 |      1 |     1 | text index 36 doc 1 row 1 | f
      37 |      1 |     2 | text index 37 doc 1 row 2 | f
      26 |      2 |     1 | text index 26 doc 2 row 1 | f
      43 |      2 |     2 | text index 43 doc 2 row 4 | f
      30 |      2 |     3 | text index 30 doc 2 row 2 | t
      42 |      2 |     4 | text index 42 doc 2 row 3 | t
      12 |      2 |     5 | text index 12 doc 2 row 5 | f
      46 |      3 |     1 | text index 46 doc 3 row 1 | f
      47 |      3 |     2 | text index 47 doc 3 row 2 | f
      32 |      4 |     1 | text index 32 doc 4 row 1 | f
      38 |      5 |     1 | text index 38 doc 5 row 1 | f
      39 |      5 |     2 | text index 39 doc 5 row 2 | f
(12 rows)

Why do you call "alternating bit protocol" on something what is not a bit? I do need insert and delete options but not with triggers and aditional columns because I have many tables where I should add it and take further care for it. Can you improve your code to be similar to those one of Tometzky so I can use it through .NET reasonable easy? — Wine Too, Oct 19 '13 at 17:54
I cannot improve the code, since it already is perfect ;-) And doing it in .net would only need the update part (`UPDATE kalksad1 SET brred = 2 WHERE brkalk = 2 AND brred = 4;`) plus refetching of the resulting table. (which is probably _less_ costly than your row_at_a_time processing) — wildplasser, Oct 19 '13 at 18:09

PostgreSQL, drag and swap

2 Answers2

Linked