Virtual listviews for huge number of rows

LarsJ · August 31, 2017

Just test it. Then you can see what happens.

kklee69 · September 1, 2017

OK!! I SEE

I have tested it!!
Nothing happens
That is why I will ask the question for the reason

3Q !! for your Reply

pvnn · December 6, 2017

Larsj, please, help

If I delete several records from the table (item_id> 5 And item_id <= 10), then the primary key is not sequential.
How correctly to show records in ListView?

Spoiler

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView


_SQLite_Startup()
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
    $Row=500000
    $hStarttime=_Timer_Init()
    $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
    $sExec = 'BEGIN; Create Table '&$TableName&' (' & _
        '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
        '[f1] TEXT,' & _
        '[f2] TEXT,' & _
        '[f3] TEXT,' & _
        '[f4] TEXT,' & _
        '[f5] TEXT,' & _
        '[f6] TEXT,' & _
        '[f7] TEXT,' & _
        '[f8] TEXT,' & _
        '[f9] TEXT );'
    ; Create string
    For $i=0 To $Row-1
        $ind=Random(5,25,1)
        $sData=StringMid($Line,$ind)
        $sExec &= 'INSERT INTO '&$TableName&' (f1,f2,f3,f4,f5,f6,f7,f8,f9) VALUES (' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&');'
    Next
    $sExec &= 'COMMIT;'
    ; Create Table
    $hStarttime=_Timer_Init()
    $hDB = _SQLite_Open($sFileDB)
    _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )
    _SQLite_Exec($hDB, $sExec)
    _SQLite_Close($hDB)
    ConsoleWrite(@CRLF&'Add records in DB: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf


; Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')



; Check databases
Local $iRows2 = CheckDB( @ScriptDir&"\Data.db" ) ; Count of records
Local $iRows

; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, $LVS_OWNERDATA, BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ) )
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


$iRows = $iRows2
If $iRows Then _SQLite_Open( $sFileDB )


; Send message to $ListView1, $iRows - Count of records
GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    Local $sSQL = "SELECT * FROM lvdata WHERE item_id >= " & $iFrom & _
                        " AND item_id <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"
;~                  Local $sSQL = "SELECT * FROM lvdata WHERE rowid >= " & $iFrom & _
;~                      " AND rowid <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" )+1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func CheckDB( $sDBname )
    Local $aRow, $iRows = 0
    If FileExists( $sDBname ) Then
        _SQLite_Open( $sDBname )
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
        _SQLite_Close( -1 )
    EndIf
    If IsArray( $aRow ) Then _
        $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

Edited December 6, 2017 by pvnn

LarsJ · December 6, 2017

You must establish a unique relationship between the rows in the listview and the rows in the table. If the table is not too big, it can be done in a memory database this way:

; Create memory database
_SQLite_Exec(-1, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")

; Create table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.RowRelation AS SELECT item_id FROM lvdata;")

And then you need to update your SQL SELECT statement:

Local $sSQL = "SELECT lvdata.* FROM lvdata " & _
  "INNER JOIN RowRelation ON RowRelation.item_id = lvdata.item_id " & _
  "WHERE RowRelation.rowid BETWEEN " & $iFrom & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView


_SQLite_Startup()
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
    $Row=500000
    $hStarttime=_Timer_Init()
    $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
    $sExec = 'BEGIN; Create Table '&$TableName&' (' & _
        '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
        '[f1] TEXT,' & _
        '[f2] TEXT,' & _
        '[f3] TEXT,' & _
        '[f4] TEXT,' & _
        '[f5] TEXT,' & _
        '[f6] TEXT,' & _
        '[f7] TEXT,' & _
        '[f8] TEXT,' & _
        '[f9] TEXT );'
    ; Create string
    For $i=0 To $Row-1
        $ind=Random(5,25,1)
        $sData=StringMid($Line,$ind)
        $sExec &= 'INSERT INTO '&$TableName&' (f1,f2,f3,f4,f5,f6,f7,f8,f9) VALUES (' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&',' & _
        _SQLite_FastEscape($sData)&');'
    Next
    $sExec &= 'COMMIT;'
    ; Create Table
    $hStarttime=_Timer_Init()
    $hDB = _SQLite_Open($sFileDB)
    _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )
    _SQLite_Exec($hDB, $sExec)
    _SQLite_Close($hDB)
    ConsoleWrite(@CRLF&'Add records in DB: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf


; Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')



; Check databases
Local $iRows2 = CheckDB( @ScriptDir&"\Data.db" ) ; Count of records
Local $iRows

; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, $LVS_OWNERDATA, BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ) )
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


$iRows = $iRows2
If $iRows Then _SQLite_Open( $sFileDB )

; Create memory database
_SQLite_Exec(-1, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")

; Create table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.RowRelation AS SELECT item_id FROM lvdata;")


; Send message to $ListView1, $iRows - Count of records
GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    Local $sSQL = "SELECT lvdata.* FROM lvdata " & _
                      "INNER JOIN RowRelation ON RowRelation.item_id = lvdata.item_id " & _
                      "WHERE RowRelation.rowid BETWEEN " & $iFrom & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"
                    ;Local $sSQL = "SELECT * FROM lvdata WHERE item_id >= " & $iFrom & _
                    ;    " AND item_id <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"
;~                  Local $sSQL = "SELECT * FROM lvdata WHERE rowid >= " & $iFrom & _
;~                      " AND rowid <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" )+1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func CheckDB( $sDBname )
    Local $aRow, $iRows = 0
    If FileExists( $sDBname ) Then
        _SQLite_Open( $sDBname )
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
        _SQLite_Close( -1 )
    EndIf
    If IsArray( $aRow ) Then _
        $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

There is an issue with an almost blank line in the listview. Make sure the height of the listview exactly matches an entire number of rows.

pvnn · December 7, 2017

Larsj, Thank you very much for the above example.

For the correct display, added +1

Local $sSQL = "SELECT lvdata.* FROM lvdata " & _
                      "INNER JOIN RowRelation ON RowRelation.item_id = lvdata.item_id " & _
                      "WHERE RowRelation.rowid BETWEEN " & $iFrom+1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" )+1 & ";"

Larsj, in order to do sorting by clicking on the title, I must:
1. Make a CREATE VIEW
2. Make Order By

Or it is possible to make somehow easier?

Edited December 7, 2017 by pvnn

LarsJ · December 7, 2017

Of course +1 must be added to $iFrom and $iTo. Because your item_id's starts at item_id = 1 and my item_id's starts at item_id = 0.

Sorting columns by clicking the column header. I would do it in the same way as the deletions:

; Create sorting table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY f1;")

; Create sorting table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort2 AS SELECT item_id FROM lvdata ORDER BY f2;")

; Create sorting table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort3 AS SELECT item_id FROM lvdata ORDER BY f3;")

Detect column header clicks in the listview:

Case $LVN_COLUMNCLICK
  Local $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" )
  Switch $iCol
    Case 1,2,3
      $iSort = $iCol
    Case Else
      $iSort = 0
  EndSwitch
  GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, 500000-5, 0 )

SQL SELECT statements:

Case $LVN_ODCACHEHINT
  Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
  $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
  Switch $iSort
    Case 1
      $sSQL = "SELECT lvdata.* FROM lvdata " & _
        "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
        "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
    Case 2
      $sSQL = "SELECT lvdata.* FROM lvdata " & _
        "INNER JOIN Sort2 ON Sort2.item_id = lvdata.item_id " & _
        "WHERE Sort2.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
    Case 3
      $sSQL = "SELECT lvdata.* FROM lvdata " & _
        "INNER JOIN Sort3 ON Sort3.item_id = lvdata.item_id " & _
        "WHERE Sort3.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
    Case Else
      Local $sSQL = "SELECT lvdata.* FROM lvdata " & _
        "INNER JOIN RowRelation ON RowRelation.item_id = lvdata.item_id " & _
        "WHERE RowRelation.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
  EndSwitch
  _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
  ConsoleWrite($sSQL&@CRLF)

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView


_SQLite_Startup()
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
  $hStarttime=_Timer_Init()

  _SQLite_Open( $sFileDB )

  _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )

  $sExec = 'Create Table lvdata (' & _
      '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
      '[f1] TEXT,' & _
      '[f2] TEXT,' & _
      '[f3] TEXT,' & _
      '[f4] TEXT,' & _
      '[f5] TEXT,' & _
      '[f6] TEXT,' & _
      '[f7] TEXT,' & _
      '[f8] TEXT,' & _
      '[f9] TEXT );'
  _SQLite_Exec( -1, $sExec )

  _SQLite_Exec( -1, "BEGIN TRANSACTION;" )

  $Row=500000
  $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
  For $i=0 To $Row-1
    $ind=Random(5,25,1)
    $sData=StringMid($Line,$ind)
    $sValues = "(" & $i + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "')"
    For $j = 1 To 99
      $sValues &= ",(" & $i + $j + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "')"
    Next
    _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
    $i += 99
  Next

  _SQLite_Exec( -1, "COMMIT TRANSACTION;" )
  _SQLite_Close( -1 )

  ConsoleWrite(@CRLF&'Create DB, create strings, fill table: '&_Timer_Diff($hStarttime)/1000&@CRLF) ; 7.9948113493733
EndIf


; Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')



; Check databases
Local $iRows2 = CheckDB( @ScriptDir&"\Data.db" ) ; Count of records
Local $iRows

; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, $LVS_OWNERDATA, BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ) )
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


$iRows = $iRows2
If $iRows Then _SQLite_Open( $sFileDB )

; Create memory database
_SQLite_Exec(-1, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")

; Create table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.RowRelation AS SELECT item_id FROM lvdata;")

; Create sorting table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY f1;")

; Create sorting table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort2 AS SELECT item_id FROM lvdata ORDER BY f2;")

; Create sorting table in memory database
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort3 AS SELECT item_id FROM lvdata ORDER BY f3;")


; Send message to $ListView1, $iRows - Count of records
GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom, $iSort = 0

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    Switch $iSort
                      Case 1
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
                          "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                      Case 2
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort2 ON Sort2.item_id = lvdata.item_id " & _
                          "WHERE Sort2.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                      Case 3
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort3 ON Sort3.item_id = lvdata.item_id " & _
                          "WHERE Sort3.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                      Case Else
                        Local $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN RowRelation ON RowRelation.item_id = lvdata.item_id " & _
                          "WHERE RowRelation.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    EndSwitch
                    ;Local $sSQL = "SELECT * FROM lvdata WHERE item_id >= " & $iFrom & _
                    ;    " AND item_id <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"
;~                  Local $sSQL = "SELECT * FROM lvdata WHERE rowid >= " & $iFrom & _
;~                      " AND rowid <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" )+1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                Case $LVN_COLUMNCLICK
                  Local $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" )
                  Switch $iCol
                    Case 1,2,3
                      $iSort = $iCol
                    Case Else
                      $iSort = 0
                  EndSwitch
                  GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, 500000-5, 0 )
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func CheckDB( $sDBname )
    Local $aRow, $iRows = 0
    If FileExists( $sDBname ) Then
        _SQLite_Open( $sDBname )
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
        _SQLite_Close( -1 )
    EndIf
    If IsArray( $aRow ) Then _
        $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

I've updated the code to create "lvdata" table so that column 1-3 gets sorted differently. And I've updated the code to use bulk inserts:

$Row=500000
$Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
For $i=0 To $Row-1
  $ind=Random(5,25,1)
  $sData=StringMid($Line,$ind)
  $sValues = "(" & $i + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
             ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
             ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
             ",'" & $sData & "'" & _
             ",'" & $sData & "'" & _
             ",'" & $sData & "'" & _
             ",'" & $sData & "'" & _
             ",'" & $sData & "'" & _
             ",'" & $sData & "')"
  For $j = 1 To 99
    $sValues &= ",(" & $i + $j + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                ",'" & $sData & "'" & _
                ",'" & $sData & "'" & _
                ",'" & $sData & "'" & _
                ",'" & $sData & "'" & _
                ",'" & $sData & "'" & _
                ",'" & $sData & "')"
  Next
  _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
  $i += 99
Next

This means that the SQL INSERT statement looks like this:

INSERT INTO lvdata VALUES (...),(...), ..., (...);

This reduces the total time to create database and table, generate strings and insert strings in the table from 25 seconds to 8 seconds on my PC.

I've also placed Case statements below Switch $iCode in proper order. The rule is that Case statements with most events should be in top of the list.

You must delete and recreate the database to see the effects of the sorting in Col1 - Col3.

Skysnake · December 8, 2017

@LarsJ, as an aside, please see this: case statement structure ?

pvnn · December 8, 2017

Larsj, Thanks to you I managed to do:
-Table in memory is generated when you click on the selected column
-Add sort in ascending and descending order
-Add arrows
How do you decide? Can you optimize it?

Add Global

Global $iCol=0          ; Column Click Number
Global $StateHeader=0   ; Arrow format
Global $SortAlert=0     ; Sorting  Alert

Add function SortColumn()

Spoiler

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView

Global $iCol=0          ; Column Click Number
Global $StateHeader=0   ; Arrow format
Global $SortAlert=0     ; Sorting  Alert

_SQLite_Startup()
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
  $hStarttime=_Timer_Init()

  _SQLite_Open( $sFileDB )

  _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )

  $sExec = 'Create Table lvdata (' & _
      '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
      '[f1] TEXT,' & _
      '[f2] TEXT,' & _
      '[f3] TEXT,' & _
      '[f4] TEXT,' & _
      '[f5] TEXT,' & _
      '[f6] TEXT,' & _
      '[f7] TEXT,' & _
      '[f8] TEXT,' & _
      '[f9] TEXT );'
  _SQLite_Exec( -1, $sExec )

  _SQLite_Exec( -1, "BEGIN TRANSACTION;" )

  $Row=500000
  $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
  For $i=0 To $Row-1
    $ind=Random(5,25,1)
    $sData=StringMid($Line,$ind)
    $sValues = "(" & $i + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "')"
    For $j = 1 To 99
      $sValues &= ",(" & $i + $j + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "')"
    Next
    _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
    $i += 99
  Next

  _SQLite_Exec( -1, "COMMIT TRANSACTION;" )
  _SQLite_Close( -1 )

  ConsoleWrite(@CRLF&'Create DB, create strings, fill table: '&_Timer_Diff($hStarttime)/1000&@CRLF) ; 7.9948113493733
EndIf


; Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')



; Check databases
Local $iRows2 = CheckDB( @ScriptDir&"\Data.db" ) ; Count of records
Local $iRows

; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, $LVS_OWNERDATA, BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ) )
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


$iRows = $iRows2
If $iRows Then _SQLite_Open( $sFileDB )

; Create memory database
_SQLite_Exec(-1, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")

; Create table in memory database
;_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.RowRelation AS SELECT item_id FROM lvdata;")
_SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY item_id;")

;~ ; Create sorting table in memory database
;~ _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY f1;")

;~ ; Create sorting table in memory database
;~ _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort2 AS SELECT item_id FROM lvdata ORDER BY f2;")

;~ ; Create sorting table in memory database
;~ _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort3 AS SELECT item_id FROM lvdata ORDER BY f3;")


; Send message to $ListView1, $iRows - Count of records
GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )


While 1
    SortColumn()
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


Func SortColumn()
    If $SortAlert=0 Then Return

;~  Local $IndexListView=Number(_GUICtrlListView_GetSelectedIndices($hListView))  ; Index Selected
;~  $ItemName=_GUICtrlListView_GetItemText($hListView,$IndexListView,0)           ; Primary Key

    Local $FieldName
    ; Fields Name
    Switch $iCol
        Case 0
            $FieldName='item_id'
        Case Else
            $FieldName='f'&$iCol
    EndSwitch

    ; Sort ASC or DESC
    GUICtrlSetState($ListView1, $GUI_DISABLE)
    _SQLite_Exec(-1,"DROP TABLE DisplayMemDb.Sort1;") ; Delete Table
    Switch $StateHeader
        Case 17408 ; Up arrow
            _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY "&$FieldName&" DESC;")
        Case Else
            _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY "&$FieldName&";")
    EndSwitch
    GUICtrlSetState($ListView1, $GUI_ENABLE)

    ConsoleWrite('$iCol = '&$iCol&'; '&'$StateHeader = '&$StateHeader&'; $FieldName = '&$FieldName &  @CRLF)
    $SortAlert=0
    GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 ) ; Update ListView

EndFunc


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
                          "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                Case $LVN_COLUMNCLICK
                   $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" ) ; Column Click Number
                    ; Draw the arrow
                    Local $hHdr = _GUICtrlListView_GetHeader($hListView)        ; ListView Header ID
                    $StateHeader    =_GUICtrlHeader_GetItemFormat($hHdr, $iCol) ; Arrow format
                    ; Cycle for all titles
                    For $i=0 To  _GUICtrlHeader_GetItemCount($hHdr)
                         If _GUICtrlHeader_GetItemOrder($hHdr,$i)=$iCol Then ; If the current title matches the click
                            ; No arrow = 16384
                            If $StateHeader=16384 Then _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            ; Up arrow = 17408
                            If $StateHeader=17408 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTDOWN)) ; Show arrow
                            EndIf
                            ; Down arrow = 16896
                            If $StateHeader=16896 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            EndIf
                         Else
                            _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; Remove the sorting arrow in all other columns
                         EndIf
                    Next

                    $SortAlert=1 ; Sorting  Alert

                  ;GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, 500000-5, 0 )
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func CheckDB( $sDBname )
    Local $aRow, $iRows = 0
    If FileExists( $sDBname ) Then
        _SQLite_Open( $sDBname )
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
        _SQLite_Close( -1 )
    EndIf
    If IsArray( $aRow ) Then _
        $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

Help implement the following:
1. I have selected a row in the list
2. Performed sorting
3. After sorting, select the same row

Functions are executed slowly:

Local $IndexListView=Number(_GUICtrlListView_GetSelectedIndices($hListView))  ; Index Selected
 $ItemName=_GUICtrlListView_GetItemText($hListView,$IndexListView,0)           ; Primary Key

LarsJ · December 10, 2017

First part:
For the current database and code, I would save all memory tables directly in the database to avoid recreating the tables each time a new sorting is needed. This will remove the delay associated with a new sorting.

Second part:
_GUICtrlListView_GetSelectedIndices is slow if there are many rows. But I don't think that _GUICtrlListView_GetItemText is slow.

Use this procedure:

Add a Case $NM_CLICK section to your message handler and get the index of selected row this way. See the help file. This is much faster than _GUICtrlListView_GetSelectedIndices.
Get item_id of selected row with _GUICtrlListView_GetItemText($hListView,$IndexListView,0).
Perform sorting.
Get the rowid from the new sorting table that matches item_id. rowid is the new listview index for the selected row.
Use _GUICtrlListView_ClickItem( $hListView, rowid ) to select the row,

pvnn · December 11, 2017

Add a Case $NM_CLICK works only with a mouse. But I can go up or down the entries with the cursor keys. Then the code does not work. We need a fast analog _GUICtrlListView_GetSelectedIndices.

Quote

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView

Global $iCol=0          ; Column Click Number
Global $StateHeader=0   ; Arrow format
Global $SortAlert=0     ; Sorting  Alert
Global $IndexItemClick  ; Primary Key of the selected record

; DB
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Load SQLite3.dll
_SQLite_Startup()
If @error Then Exit MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded")

; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
  $hStarttime=_Timer_Init()
  _SQLite_Open( $sFileDB )
  _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )

  $sExec = 'Create Table lvdata (' & _
      '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
      '[f1] TEXT,' & _
      '[f2] TEXT,' & _
      '[f3] TEXT,' & _
      '[f4] TEXT,' & _
      '[f5] TEXT,' & _
      '[f6] TEXT,' & _
      '[f7] TEXT,' & _
      '[f8] TEXT,' & _
      '[f9] TEXT );'
  _SQLite_Exec( -1, $sExec )
  _SQLite_Exec( -1, "BEGIN TRANSACTION;" )

  $Row=500000
  $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
  For $i=0 To $Row-1
    $ind=Random(5,25,1)
    $sData=StringMid($Line,$ind)
    $sValues = "(" & $i + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "')"
    For $j = 1 To 99
      $sValues &= ",(" & $i + $j + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "')"
    Next
    _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
    $i += 99
  Next

  _SQLite_Exec( -1, "COMMIT TRANSACTION;" )
  _SQLite_Close( -1 )

  ConsoleWrite(@CRLF&'Create DB, create strings, fill table: '&_Timer_Diff($hStarttime)/1000&@CRLF) ; 7.9948113493733
EndIf

; Open DB
$hDB=_SQLite_Open($sFileDB)
If @error Then Exit MsgBox(16, "SQLite Error", "Can't create a memory Database!")

; Count of records
Local $iRows = DB_Count()

; Test - Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')

; Create memory database
_SQLite_Exec($hDB, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")
; Create table in memory database
_SQLite_Exec($hDB, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY item_id;")



; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS), BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ))
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Add", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)

; Send message to $ListView1, $iRows - Count of records
GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )


While 1
    SortColumn()
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd



Func SortColumn()
    If $SortAlert=0 Then Return
    Local $FieldName, $aRow

 ; Primary Key of the selected record
    $SelectedPrimaryKey=_GUICtrlListView_GetItemText($hListView,$IndexItemClick,0) ; Item Name

    ; Fields Name
    Switch $iCol
        Case 0
            $FieldName='item_id'
        Case Else
            $FieldName='f'&$iCol
    EndSwitch
    ; Sort ASC or DESC
    GUICtrlSetState($ListView1, $GUI_DISABLE)
    _SQLite_Exec(-1,"DROP TABLE DisplayMemDb.Sort1;") ; Delete Table
    Switch $StateHeader
        Case 17408 ; Up arrow
            _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY "&$FieldName&" DESC;")
        Case Else
            _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY "&$FieldName&";")
    EndSwitch
    GUICtrlSetState($ListView1, $GUI_ENABLE)
    ConsoleWrite('$iCol = '&$iCol&'; '&'$StateHeader = '&$StateHeader&'; $FieldName = '&$FieldName &  @CRLF)
    $SortAlert=0
    ; Update Virtual ListView
    GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )

    ; Go to record
    _SQLite_QuerySingleRow( -1, "SELECT rowid FROM DisplayMemDb.Sort1 WHERE item_id="&$SelectedPrimaryKey&";", $aRow )
    ConsoleWrite($SelectedPrimaryKey&' -=- '&$aRow[0]& @CRLF)
    _GUICtrlListView_ClickItem($hListView,$aRow[0]-1) ; -1 because the numbering from 0

EndFunc


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
                          "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                Case $LVN_COLUMNCLICK
                   $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" ) ; Column Click Number
                    ; Draw the arrow
                    Local $hHdr = _GUICtrlListView_GetHeader($hListView)        ; ListView Header ID
                    $StateHeader    =_GUICtrlHeader_GetItemFormat($hHdr, $iCol) ; Arrow format
                    ; Cycle for all titles
                    For $i=0 To  _GUICtrlHeader_GetItemCount($hHdr)
                         If _GUICtrlHeader_GetItemOrder($hHdr,$i)=$iCol Then ; If the current title matches the click
                            ; No arrow = 16384
                            If $StateHeader=16384 Then _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            ; Up arrow = 17408
                            If $StateHeader=17408 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTDOWN)) ; Show arrow
                            EndIf
                            ; Down arrow = 16896
                            If $StateHeader=16896 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            EndIf
                         Else
                            _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; Remove the sorting arrow in all other columns
                         EndIf
                    Next

                    $SortAlert=1 ; Sorting  Alert

                Case $NM_CLICK
                     $tInfo = DllStructCreate($tagNMITEMACTIVATE, $lParam)
                     $IndexItemClick = DllStructGetData($tInfo, "Index")
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func DB_Count()
    Local $aRow, $iRows = 0
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

Edited December 11, 2017 by pvnn

LarsJ · December 11, 2017

Then you can use $LVN_ITEMCHANGED notifications. They are fired everytime an item is changed eg. when it's selected. Normally 3 $LVN_ITEMCHANGED notifications are fired when an item is changed. The last one contains the index of the selected item. You can use code similar to the code for $NM_CLICK.

pvnn · December 13, 2017

$LVN_ITEMCHANGED - good decision, but why the _GUICtrlListView_GetItemText() function does not work

Case $LVN_ITEMCHANGED
                     If Not DllStructGetData($tNMITEMACTIVATE, 'OldState') Then ; 'Changed', 'NewState', 'OldState'
                        $IndexItemSelected = DllStructGetData($tNMITEMACTIVATE, 'Index')
                        $SelectedPrimaryKey=_GUICtrlListView_GetItemText($hListView,$IndexItemSelected) ; Item Name
                        ConsoleWrite($IndexItemSelected  &@TAB&StringLen($SelectedPrimaryKey)& @LF)
                     EndIf

I moved the _GUICtrlListView_GetItemText() function to SortColumn (), but even there it does not always work. Sometimes _GUICtrlListView_GetItemText() returns an empty string. How stably you can get the name of the record

Quote

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView

Global $iCol=0              ; Column Click Number
Global $StateHeader=0       ; Arrow format
Global $SortAlert=0         ; Sorting  Alert
Global $IndexItemSelected   ; Index of the selected record

; DB
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Load SQLite3.dll
_SQLite_Startup()
If @error Then Exit MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded")

; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
  $hStarttime=_Timer_Init()
  _SQLite_Open( $sFileDB )
  _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )

  $sExec = 'Create Table lvdata (' & _
      '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
      '[f1] TEXT,' & _
      '[f2] TEXT,' & _
      '[f3] TEXT,' & _
      '[f4] TEXT,' & _
      '[f5] TEXT,' & _
      '[f6] TEXT,' & _
      '[f7] TEXT,' & _
      '[f8] TEXT,' & _
      '[f9] TEXT );'
  _SQLite_Exec( -1, $sExec )
  _SQLite_Exec( -1, "BEGIN TRANSACTION;" )

  $Row=500000
  $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
  For $i=0 To $Row-1
    $ind=Random(5,25,1)
    $sData=StringMid($Line,$ind)
    $sValues = "(" & $i + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "')"
    For $j = 1 To 99
      $sValues &= ",(" & $i + $j + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "')"
    Next
    _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
    $i += 99
  Next

  _SQLite_Exec( -1, "COMMIT TRANSACTION;" )
  _SQLite_Close( -1 )

  ConsoleWrite(@CRLF&'Create DB, create strings, fill table: '&_Timer_Diff($hStarttime)/1000&@CRLF) ; 7.9948113493733
EndIf

; Open DB
$hDB=_SQLite_Open($sFileDB)
If @error Then Exit MsgBox(16, "SQLite Error", "Can't create a memory Database!")

; Count of records
Local $iRows = DB_Count()

; Test - Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')

; Create memory database
_SQLite_Exec($hDB, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")
; Create table in memory database
_SQLite_Exec($hDB, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY item_id;")



; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS), BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ))
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)

; Send message to $ListView1, $iRows - Count of records
GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )


While 1
    SortColumn()
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd



Func SortColumn()
    If $SortAlert=0 Then Return
    Local $FieldName, $aRow

 ; Primary Key of the selected record
    $SelectedPrimaryKey=_GUICtrlListView_GetItemText($hListView,$IndexItemSelected,0) ; Item Name

    ; Fields Name
    Switch $iCol
        Case 0
            $FieldName='item_id'
        Case Else
            $FieldName='f'&$iCol
    EndSwitch
    ; Sort ASC or DESC
    GUICtrlSetState($ListView1, $GUI_DISABLE)
    _SQLite_Exec(-1,"DROP TABLE DisplayMemDb.Sort1;") ; Delete Table
    Switch $StateHeader
        Case 17408 ; Up arrow
            _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY "&$FieldName&" DESC;")
        Case Else
            _SQLite_Exec(-1, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY "&$FieldName&";")
    EndSwitch
    GUICtrlSetState($ListView1, $GUI_ENABLE)
    ConsoleWrite('$iCol = '&$iCol&'; '&'$StateHeader = '&$StateHeader&'; $FieldName = '&$FieldName &  @CRLF)
    $SortAlert=0
    ; Update Virtual ListView
    GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, $iRows, 0 )

    ; Go to record
    _SQLite_QuerySingleRow( -1, "SELECT rowid FROM DisplayMemDb.Sort1 WHERE item_id="&$SelectedPrimaryKey&";", $aRow )
    ConsoleWrite($SelectedPrimaryKey&' -=- '&$aRow[0]& @CRLF)
    _GUICtrlListView_ClickItem($hListView,$aRow[0]-1) ; -1 because the numbering from 0

EndFunc


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )
    $tNMITEMACTIVATE = DllStructCreate($tagNMITEMACTIVATE, $lParam)

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
                          "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                Case $LVN_COLUMNCLICK
                   $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" ) ; Column Click Number
                    ; Draw the arrow
                    Local $hHdr = _GUICtrlListView_GetHeader($hListView)        ; ListView Header ID
                    $StateHeader    =_GUICtrlHeader_GetItemFormat($hHdr, $iCol) ; Arrow format
                    ; Cycle for all titles
                    For $i=0 To  _GUICtrlHeader_GetItemCount($hHdr)
                         If _GUICtrlHeader_GetItemOrder($hHdr,$i)=$iCol Then ; If the current title matches the click
                            ; No arrow = 16384
                            If $StateHeader=16384 Then _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            ; Up arrow = 17408
                            If $StateHeader=17408 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTDOWN)) ; Show arrow
                            EndIf
                            ; Down arrow = 16896
                            If $StateHeader=16896 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            EndIf
                         Else
                            _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; Remove the sorting arrow in all other columns
                         EndIf
                    Next

                    $SortAlert=1 ; Sorting  Alert

                Case $LVN_ITEMCHANGED
                     If Not DllStructGetData($tNMITEMACTIVATE, 'OldState') Then ; 'Changed', 'NewState', 'OldState'
                        $IndexItemSelected = DllStructGetData($tNMITEMACTIVATE, 'Index')
                        $SelectedPrimaryKey=_GUICtrlListView_GetItemText($hListView,$IndexItemSelected) ; Item Name
                        ConsoleWrite($IndexItemSelected  &@TAB&StringLen($SelectedPrimaryKey)& @LF)
                     EndIf

;~              Case $NM_CLICK
;~                      $tInfo = DllStructCreate($tagNMITEMACTIVATE, $lParam)
;~                      $IndexItemSelected = DllStructGetData($tInfo, "Index")
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func DB_Count()
    Local $aRow, $iRows = 0
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

Edited December 13, 2017 by pvnn

LarsJ · December 14, 2017

Usually you don't need an If-statement. Do it this way:

Case $LVN_ITEMCHANGED
  $IndexItemSelected = DllStructGetData($tNMITEMACTIVATE, 'Index')
  $SelectedPrimaryKey = _GUICtrlListView_GetItemText($hListView,$IndexItemSelected) ; Item Name
  ConsoleWrite( $IndexItemSelected & @TAB & $SelectedPrimaryKey & @LF )

pvnn · December 14, 2017

Why the function _GUICtrlListView_GetItemText() returns empty? How can I fix it?

$SelectedPrimaryKey = _GUICtrlListView_GetItemText($hListView,$IndexItemSelected) ; Item Name

Edited December 14, 2017 by pvnn

LarsJ · December 15, 2017

_GUICtrlListView_GetItemText() can return empty output if the item index is -1. And this can be the case for $LVN_ITEMCHANGED events.

pvnn · December 18, 2017

LarsJ, I'm at a dead end.
Could you please give an example of how I can get the text of the item in WM_NOTIFY (), for example, at the event $ LVN_ITEMCHANGED
_GUICtrlListView_GetItemText () does not work.
I really need help.

Quote

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView

Global $iCol=0              ; Column Click Number
Global $FieldNameSort=''    ; Field sorting Name
Global $FieldTypeSort=1     ; 1- Ascending, 0 - Descending

Global $StateHeader=0       ; Arrow format
Global $SortAlert=0         ; Sorting  Alert
Global $IndexItemSelected   ; Index of the selected record






; DB
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Load SQLite3.dll
_SQLite_Startup()
If @error Then Exit MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded")

; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
  $hStarttime=_Timer_Init()
  _SQLite_Open( $sFileDB )
  _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )

  $sExec = 'Create Table lvdata (' & _
      '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
      '[f1] TEXT,' & _
      '[f2] TEXT,' & _
      '[f3] TEXT,' & _
      '[f4] TEXT,' & _
      '[f5] TEXT,' & _
      '[f6] TEXT,' & _
      '[f7] TEXT,' & _
      '[f8] TEXT,' & _
      '[f9] TEXT );'
  _SQLite_Exec( -1, $sExec )
  _SQLite_Exec( -1, "BEGIN TRANSACTION;" )

  $Row=500000
  $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
  For $i=0 To $Row-1
    $ind=Random(5,25,1)
    $sData=StringMid($Line,$ind)
    $sValues = "(" & $i + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "')"
    For $j = 1 To 99
      $sValues &= ",(" & $i + $j + 1 & ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & StringMid($Line,Random(5,25,1)) & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "')"
    Next
    _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
    $i += 99
  Next

  _SQLite_Exec( -1, "COMMIT TRANSACTION;" )
  _SQLite_Close( -1 )

  ConsoleWrite(@CRLF&'Create DB, create strings, fill table: '&_Timer_Diff($hStarttime)/1000&@CRLF) ; 7.9948113493733
EndIf

; Open DB
$hDB=_SQLite_Open($sFileDB)
If @error Then Exit MsgBox(16, "SQLite Error", "Can't create a memory Database!")

; Count of records
Local $iRows = DB_Count()

; Test - Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')

; Create memory database
_SQLite_Exec($hDB, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")
; Create table in memory database
_SQLite_Exec($hDB, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY item_id;")



; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS), BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ))
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


$FieldNameSort='f1'
$ColumnWithArrow=1
 _GUICtrlHeader_SetItemFormat(_GUICtrlListView_GetHeader($hListView), _
 $ColumnWithArrow, _
 BitOR(_GUICtrlHeader_GetItemFormat(_GUICtrlListView_GetHeader($hListView), $ColumnWithArrow), $HDF_SORTUP)) ; Show arrow
UpdateVirtualListView()


While 1
    SortColumn()
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


; Update Virtual ListView
Func UpdateVirtualListView($SelectedPrimaryKey='')

    Local $aRow, $sExec=''

    GUICtrlSetState($ListView1, $GUI_DISABLE)
    _SQLite_Exec(-1,"DROP TABLE IF EXISTS DisplayMemDb.Sort1;") ; Delete Table

    ; If Sorting
    If $FieldNameSort='' Then
        $sExec='CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata;'  ; Create table in memory database
    Else
        $sExec='CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY '&$FieldNameSort
        If $FieldTypeSort=0 Then $sExec&=' DESC;'
    EndIf
    _SQLite_Exec(-1,$sExec)
    GUICtrlSetState($ListView1, $GUI_ENABLE)

    ; Update Virtual ListView
    GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, DB_Count(), 0 )

    ; If Go to record
    If  $SelectedPrimaryKey<>'' Then
        _SQLite_QuerySingleRow( -1, "SELECT rowid FROM DisplayMemDb.Sort1 WHERE item_id="&$SelectedPrimaryKey&";", $aRow )
        _GUICtrlListView_ClickItem($hListView,$aRow[0]-1) ; -1 because the numbering from 0
    Else
        _GUICtrlListView_ClickItem($hListView,0) ; Click First item
    EndIf


EndFunc


Func SortColumn()
    If $SortAlert=0 Then Return
    $SortAlert=0
    Local $aRow

 ; Primary Key of the selected record
    $SelectedPrimaryKey=_GUICtrlListView_GetItemText($hListView,$IndexItemSelected) ; Item Name

 ; Can not always identify a name $SelectedPrimaryKey
  If $SelectedPrimaryKey='' Then
      MsgBox(16,'Error',$IndexItemSelected)
      _GUICtrlListView_ClickItem($hListView,$IndexItemSelected)
      $SelectedPrimaryKey   =_GUICtrlListView_GetItemText($hListView, $IndexItemSelected) ; Primary Key (Item Name) of the selected record
  EndIf

    ; Fields Name
    Switch $iCol
        Case 0
            $FieldNameSort='item_id'
        Case Else
            $FieldNameSort='f'&$iCol
    EndSwitch
    ; Sort ASC or DESC
    Switch $StateHeader
        Case 17408 ; Up arrow
            $FieldTypeSort=0 ; Sort Ascending
        Case Else
            $FieldTypeSort=1 ; Sort Descending
    EndSwitch

    ; Update Virtual ListView
    UpdateVirtualListView($SelectedPrimaryKey)
    ConsoleWrite('$iCol = '&$iCol&'; '&'$StateHeader = '&$StateHeader&'; $FieldName = '&$FieldNameSort &  @CRLF)
EndFunc


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )
    $tNMITEMACTIVATE = DllStructCreate($tagNMITEMACTIVATE, $lParam)

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
                          "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
                    ConsoleWrite($sSQL&@CRLF)

                Case $LVN_COLUMNCLICK
                   $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" ) ; Column Click Number
                    ; Draw the arrow
                    Local $hHdr = _GUICtrlListView_GetHeader($hListView)        ; ListView Header ID
                    $StateHeader    =_GUICtrlHeader_GetItemFormat($hHdr, $iCol) ; Arrow format
                    ; Cycle for all titles
                    For $i=0 To  _GUICtrlHeader_GetItemCount($hHdr)
                         If _GUICtrlHeader_GetItemOrder($hHdr,$i)=$iCol Then ; If the current title matches the click
                            ; No arrow = 16384
                            If $StateHeader=16384 Then _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            ; Up arrow = 17408
                            If $StateHeader=17408 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTDOWN)) ; Show arrow
                            EndIf
                            ; Down arrow = 16896
                            If $StateHeader=16896 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            EndIf
                         Else
                            _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; Remove the sorting arrow in all other columns
                         EndIf
                    Next

                    $SortAlert=1 ; Sorting  Alert

                Case $LVN_ITEMCHANGED
                      $IndexItemSelected = DllStructGetData($tNMITEMACTIVATE, 'Index')
;~                    $SelectedPrimaryKey = _GUICtrlListView_GetItemText($hListView,0,1) ; Item Name
;~                    ConsoleWrite( $IndexItemSelected & @TAB &'-=- '& $SelectedPrimaryKey & @LF )

            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func DB_Count()
    Local $aRow, $iRows = 0
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

Edited December 20, 2017 by pvnn

Iczer · January 25, 2018

Is there a right way to delete item/entry from virtual listview after it was loaded and shown? I'm deleting it from main db, from rowrelation db and even from listview itself, I decrease listview item count, but after some scroll other items get messed up numbers...

pvnn

According msdn : The following messages are not supported under the LVS_OWNERDATA style: LVM_ENABLEGROUPVIEW, LVM_GETITEMTEXT, LVM_SETTILEINFO, and LVM_MAPIDTOINDEX.

So GUICtrlListView_GetItemText () will not work. But actually it may return some results for selected/focused item. In general you should retrieve data/text directly from database in case of using virtual listview.

LarsJ · January 25, 2018

Iczer, You need to recalculate the entire rowrelation table. It's not enough just to delete a single row. If the virtual listview contains 100,000 rows, the rowrelation table must contain an uninterrupted sequence of numbers from 0 - 99,999 that properly matches the listview row indices. If you delete a row, the rowrelation table must contain a new uninterrupted sequence of numbers from 0 - 99,998 that properly matches the listview row indices. This requires a total recalculation of the table.

pvnn, I did not see the update of post #96 you made on December 20th before now. Just add a new post next time. A virtual listview does not contain any data. That's the reason why it's called virtual, why it's fast and why it can display millions of rows. Since it does not contain any data _GUICtrlListView_GetItemText() does generally not work, as Iczer has already mentioned. Just get the data directly from the source.

Iczer · January 26, 2018

Thanks! I was looking into sqlite forein key constrains, but it seems to just drop and then refill rowrelation table is more simple

pvnn · February 6, 2018

LarsJ and Iczer thanks for the clarification with _GUICtrlListView_GetItemText()

LarsJ, how can I highlight text in a virtual ListView?
For example, I need to "Yes" highlight green, and "No" - red
In a normal ListView, I would use NM_CUSTOMDRAW and _GUICtrlListView_GetItemText() to parse the text.
But _GUICtrlListView_GetItemText() does not work in the virtual ListView.
What is the solution?

Spoiler

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3> ; SQLite

#include <Timers.au3>

; Create structure $tagNMLVCACHEHINT
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo" ; #include <GuiListView.au3>
Global $hListView

Global $iCol=0              ; Column Click Number
Global $FieldNameSort=''    ; Field sorting Name
Global $FieldTypeSort=1     ; 1- Ascending, 0 - Descending

Global $StateHeader=0       ; Arrow format
Global $SortAlert=0         ; Sorting  Alert
Global $IndexItemSelected   ; Index of the selected record





; DB
$sFileDB = @ScriptDir & '\Data.db'
$TableName='lvdata'


; Load SQLite3.dll
_SQLite_Startup()
If @error Then Exit MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded")

; Create DB if not exist
If Not FileExists($sFileDB) Then ; Recreate data base
  $hStarttime=_Timer_Init()
  _SQLite_Open( $sFileDB )
  _SQLite_Exec( -1, "PRAGMA synchronous = OFF;" )

  $sExec = 'Create Table lvdata (' & _
      '[item_id] INTEGER PRIMARY KEY AUTOINCREMENT,' & _
      '[f1] TEXT,' & _
      '[f2] TEXT,' & _
      '[f3] TEXT,' & _
      '[f4] TEXT,' & _
      '[f5] TEXT,' & _
      '[f6] TEXT,' & _
      '[f7] TEXT,' & _
      '[f8] TEXT,' & _
      '[f9] TEXT );'
  _SQLite_Exec( -1, $sExec )
  _SQLite_Exec( -1, "BEGIN TRANSACTION;" )

  $Row=300000
  Local $aLine[2]=['Yes','No']

  $Line='Yes'
  For $i=0 To $Row-1
    $ind=Random(5,25,1)
    $sData=$aLine[Random(0,1,1)]
    $sValues = "(" & $i + 1 & ",'" & $aLine[Random(0,1,1)] & "'" & _
               ",'" & $aLine[Random(0,1,1)] & "'" & _
               ",'" & $aLine[Random(0,1,1)] & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "'" & _
               ",'" & $sData & "')"
    For $j = 1 To 99
      $sValues &= ",(" & $i + $j + 1 & ",'" & $aLine[Random(0,1,1)] & "'" & _
                  ",'" & $aLine[Random(0,1,1)] & "'" & _
                  ",'" & $aLine[Random(0,1,1)] & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "'" & _
                  ",'" & $sData & "')"
    Next
    _SQLite_Exec( -1, "INSERT INTO lvdata VALUES " & $sValues & ";" )
    $i += 99
  Next

  _SQLite_Exec( -1, "COMMIT TRANSACTION;" )
  _SQLite_Close( -1 )

  ConsoleWrite(@CRLF&'Create DB, create strings, fill table: '&_Timer_Diff($hStarttime)/1000&@CRLF) ; 7.9948113493733
EndIf

; Open DB
$hDB=_SQLite_Open($sFileDB)
If @error Then Exit MsgBox(16, "SQLite Error", "Can't create a memory Database!")

; Count of records
Local $iRows = DB_Count()

; Test - Specially delete 5 records
$hDB = _SQLite_Open($sFileDB)
_SQLite_Exec($hDB, 'Delete From '&$TableName&' Where item_id > 5 And item_id <= 10 ;')

; Create memory database
_SQLite_Exec($hDB, "ATTACH DATABASE ':memory:' AS DisplayMemDb;")
; Create table in memory database
_SQLite_Exec($hDB, "CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY item_id;")



; GUI
$Form1 = GUICreate("Virtual ListViews", 908, 524, 192, 114)

; Virtual ListViews - $LVS_OWNERDATA, Reduces flicker - $LVS_EX_DOUBLEBUFFER
$ListView1 = GUICtrlCreateListView("", 8, 16, 890, 462, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS), BitOR( $LVS_EX_GRIDLINES, $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ))
$hListView = GUICtrlGetHandle($ListView1) ; Handle
; Add Columns
For $i = 0 To 9
    _GUICtrlListView_AddColumn( $hListView, "Col" & $i, 75 )
Next

$Input1 = GUICtrlCreateInput("Input1", 8, 488, 185, 21)
$Button1 = GUICtrlCreateButton("Button1", 200, 488, 75, 25)
$Button2 = GUICtrlCreateButton("Button2", 680, 488, 75, 25)
$Button3 = GUICtrlCreateButton("Button3", 752, 488, 75, 25)
$Button4 = GUICtrlCreateButton("Button4", 824, 488, 75, 25)

GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


$FieldNameSort='f1'
$ColumnWithArrow=1
 _GUICtrlHeader_SetItemFormat(_GUICtrlListView_GetHeader($hListView), _
 $ColumnWithArrow, _
 BitOR(_GUICtrlHeader_GetItemFormat(_GUICtrlListView_GetHeader($hListView), $ColumnWithArrow), $HDF_SORTUP)) ; Show arrow
UpdateVirtualListView()


While 1
    SortColumn()
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


; Update Virtual ListView
Func UpdateVirtualListView($SelectedPrimaryKey='')
;~ _GUICtrlListView_BeginUpdate($hListView)
    Local $aRow, $sExec=''

    GUICtrlSetState($ListView1, $GUI_DISABLE)
    _SQLite_Exec(-1,"DROP TABLE IF EXISTS DisplayMemDb.Sort1;") ; Delete Table

    ; If Sorting
    If $FieldNameSort='' Then
        $sExec='CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata;'  ; Create table in memory database
    Else
        $sExec='CREATE TABLE DisplayMemDb.Sort1 AS SELECT item_id FROM lvdata ORDER BY '&$FieldNameSort
        If $FieldTypeSort=0 Then $sExec&=' DESC;'
    EndIf
    _SQLite_Exec(-1,$sExec)
    GUICtrlSetState($ListView1, $GUI_ENABLE)

    ; Update Virtual ListView
    GUICtrlSendMsg( $ListView1, $LVM_SETITEMCOUNT, DB_Count(), 0 )

    ; If Go to record
    If  $SelectedPrimaryKey<>'' Then
        _SQLite_QuerySingleRow( -1, "SELECT rowid FROM DisplayMemDb.Sort1 WHERE item_id="&$SelectedPrimaryKey&";", $aRow )
        _GUICtrlListView_ClickItem($hListView,$aRow[0]-1) ; -1 because the numbering from 0
    Else
        _GUICtrlListView_ClickItem($hListView,0) ; Click First item
    EndIf

;~ _GUICtrlListView_EndUpdate($hListView)
EndFunc


Func SortColumn()
    If $SortAlert=0 Then Return
    $SortAlert=0
    Local $aRow

 ; Primary Key of the selected record
    $SelectedPrimaryKey=_GUICtrlListView_GetItemText($hListView,$IndexItemSelected) ; Item Name

 ; Can not always identify a name $SelectedPrimaryKey
  If $SelectedPrimaryKey='' Then
      ;MsgBox(16,'Error',$IndexItemSelected)
      _GUICtrlListView_ClickItem($hListView,$IndexItemSelected)
      $SelectedPrimaryKey   =_GUICtrlListView_GetItemText($hListView, $IndexItemSelected) ; Primary Key (Item Name) of the selected record
  EndIf

    ; Fields Name
    Switch $iCol
        Case 0
            $FieldNameSort='item_id'
        Case Else
            $FieldNameSort='f'&$iCol
    EndSwitch
    ; Sort ASC or DESC
    Switch $StateHeader
        Case 17408 ; Up arrow
            $FieldTypeSort=0 ; Sort Ascending
        Case Else
            $FieldTypeSort=1 ; Sort Descending
    EndSwitch

    ; Update Virtual ListView
    UpdateVirtualListView($SelectedPrimaryKey)
    ConsoleWrite('$iCol = '&$iCol&'; '&'$StateHeader = '&$StateHeader&'; $FieldName = '&$FieldNameSort &  @CRLF)
EndFunc


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[250]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom


    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
    $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
    $iCode = DllStructGetData( $tNMHDR, "Code" )
    $tNMITEMACTIVATE = DllStructCreate($tagNMITEMACTIVATE, $lParam)

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                ; View Records
                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1 ; number row
                        Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem")               ; number column
                        If $iIndex > 0 And $iIndex < $iRows + 1 Then
                            Local $sItem = $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf


                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $sSQL, $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                        $sSQL = "SELECT lvdata.* FROM lvdata " & _
                          "INNER JOIN Sort1 ON Sort1.item_id = lvdata.item_id " & _
                          "WHERE Sort1.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
;~                     ConsoleWrite($sSQL&@CRLF)

                Case $NM_CUSTOMDRAW
                    Local $tNMLVCD = DllStructCreate($tagNMLVCUSTOMDRAW, $lParam)
                    Local $iDrawStage = DllStructGetData($tNMLVCD, 'dwDrawStage')
                    Local $iItem = DllStructGetData($tNMLVCD, 'dwItemSpec')     ; Index item
                    Local $iSubItem = DllStructGetData($tNMLVCD, 'iSubItem')    ; Index subitem

                    Switch $iDrawStage
                        Case $CDDS_PREPAINT
                            Return $CDRF_NOTIFYITEMDRAW
                        Case $CDDS_ITEMPREPAINT
                            Return $CDRF_NOTIFYSUBITEMDRAW
                        Case BitOR($CDDS_ITEMPREPAINT, $CDDS_SUBITEM)
                            If $iItem=1 Then
                                DllStructSetData($tNMLVCD, 'clrText', 0x000FF)      ; Color Font
                                DllStructSetData($tNMLVCD, 'clrTextBk', 0x08fff7)   ; Color Background
                            EndIf

                            Return $CDRF_NEWFONT
                    EndSwitch



                Case $LVN_COLUMNCLICK
                   $iCol = DllStructGetData( DllStructCreate( $tagNMLISTVIEW, $lParam ), "SubItem" ) ; Column Click Number
                    ; Draw the arrow
                    Local $hHdr = _GUICtrlListView_GetHeader($hListView)        ; ListView Header ID
                    $StateHeader    =_GUICtrlHeader_GetItemFormat($hHdr, $iCol) ; Arrow format
                    ; Cycle for all titles
                    For $i=0 To  _GUICtrlHeader_GetItemCount($hHdr)
                         If _GUICtrlHeader_GetItemOrder($hHdr,$i)=$iCol Then ; If the current title matches the click
                            ; No arrow = 16384
                            If $StateHeader=16384 Then _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            ; Up arrow = 17408
                            If $StateHeader=17408 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTDOWN)) ; Show arrow
                            EndIf
                            ; Down arrow = 16896
                            If $StateHeader=16896 Then
                                _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; clear
                                _GUICtrlHeader_SetItemFormat($hHdr, $iCol, BitOR(_GUICtrlHeader_GetItemFormat($hHdr, $iCol), $HDF_SORTUP)) ; Show arrow
                            EndIf
                         Else
                            _GUICtrlHeader_SetItemFormat($hHdr, $i, $HDF_STRING) ; Remove the sorting arrow in all other columns
                         EndIf
                    Next

                    $SortAlert=1 ; Sorting  Alert

                Case $LVN_ITEMCHANGED
                     If Not DllStructGetData($tNMITEMACTIVATE, 'OldState') Then ; 'Changed', 'NewState', 'OldState'
                        $IndexItemSelected = DllStructGetData($tNMITEMACTIVATE, 'Index')
                     EndIf
            EndSwitch

    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc


Func DB_Count()
    Local $aRow, $iRows = 0
        _SQLite_QuerySingleRow( -1, "SELECT COUNT(*) FROM lvdata LIMIT 1;", $aRow )
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1
    Return $iRows
EndFunc

Sign In

Virtual listviews for huge number of rows

Recommended Posts

LarsJ

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

LarsJ

LarsJ

LarsJ

Posted Images

kklee69

pvnn

LarsJ

pvnn

LarsJ

Skysnake

pvnn

LarsJ

pvnn

LarsJ

pvnn

LarsJ

pvnn

LarsJ

pvnn

Iczer

LarsJ

Iczer

pvnn

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta