PostgreSQL，拖动和交换 [英] PostgreSQL, drag and swap
问题描述
我已经成功解决了有关按值交换行的情况此处仍然有效的方法。
但是通过使用此功能，我发现拖放行的功能有些欠缺。
我尝试获取解决方案此处其中讨论方式错误且提供的解决方案不是足够，因为他们需要其他列。
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.
以下是众所周知的表：
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');
操作对象是在同一 brkalk下重新排列 brred（行）列的值（doc ）。
让 brkalk为2。
Object of manipulation is reordering values of column "brred"(row) under same "brkalk"(doc).
Let "brkalk" be 2.
现在，我想根据拖放需求进行重新排序/交换，其中交换只是一个行看起来不自然。我已经将datagrid绑定到kalksad1表，所以我将描述在充满查询 ... ORDERED by brred的datagrid中查找的情况。
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".
如果我可以用以下单词来解释查询，则......
示例1：
在文档下2我将第4行拖放到第2行的位置。
为此，需要执行以下步骤：
1）记住第4行的数据。 >
2）在第3行中，将 brred的值从3替换为4。
3）在第2行中，将 brred的值从2替换为3。
4）在步骤1）的记忆数据中，将 brred的值从4更改为2。
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.
示例2：
在下面doc 2我将把第1行拖放到位置3。
可以这样：
1）记住第1行的数据。
2）在第2行中，将 brred的值从2替换为1。
3）在第3行中，将 brred的值从3替换为2。
4 ）在第1步的记忆数据中，将 brred的值从1更改为3。
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.
这种想法可能来自于带有交换和SO问题的优雅解决方案< a href = https://stackoverflow.com/questions/6037045/resequen像这样，此和此。我根据自己的想法举了个例子，但是如果存在更好的话就不应该这样。
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.
请问有人可以用与交换。
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
推荐答案
示例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;
示例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;
如果（brkalk，brred）则会更加复杂，因为在重新编号期间，会重复出现 bred 。
If you have unique index on (brkalk,brred) then it would be more complicated, as during renumbering there'll be duplicate brred.
但是对于很多行，我建议您使用在8位计算机上使用BASIC语言时非常有用的东西-用空格对行进行编号。
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.
因此，代替：
(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'),
使用：
(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:
update kalksad1 set brred =（2048 + 1024）/ 2其中kalk_id = 43 ，它将更改为：
(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'),
更新kalksad1 set brred =（4096 + 3072）/ 2，其中kalk_id = 43 ，它将更改为：
(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'),
仅当目标行之间没有间隙时，才需要使用fo重新编号行例如：
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.
这篇关于PostgreSQL，拖动和交换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
