Virtual listviews for huge number of rows

[Iczer]

i somewhat confused about switching ListView filling between 2+ different arrays... can i have some light on this case?

[LarsJ]

In each example, I start with a relatively low number of rows to avoid too long waiting time, if you do not have a new and fast PC. I have tested all examples on my old XP. It certainly takes longer time to fill the arrays, than it does on a new Windows 7/8.

In most examples the listview is filled with data from a global array $aItems. When a new tab item e.g. tab 2 is clicked these commands are executed:

 

$iRows = $iRows2   ; $iRows is set to the new number of rows
$aItems = $aItems2 ; $aItems is set to the new array
GUICtrlSendMsg( $idLV, $LVM_SETITEMCOUNT, $iRows, 0 )

The last command does two things: It updates the number of rows, and it updates (repaints) all visible rows in the listview (about 20 rows).

In the sorting example (LvVirtArraySort.au3) you can see, that all rows really gets updated.

Because a virtual listview only contains information about the visible rows, switching from one array to another is very fast. Information about all other rows (except for the number of rows) exists only in the array.

In the example with a standard listview (LvStandard.au3) you can see, that it takes quite some time to delete the old rows and fill in the new rows.

[Iczer]

Thanks!, i think i understand now

[Zedna]

@LarsJ

Absolutely awesome examples!! :thumbsup:

EDIT:

Just one very important note: All these LVN_ODxxx notifications are available only on Vista+ systems so this will not work on Windows XP.

EDIT2: 

This presumption is based only on MSDN documentation where is mentioned minimal requirements Vista+

but it seems that Microsoft removed XP from MSDN completely

Edited April 13, 2015 by Zedna

[argumentum]

EDIT:

Just one very important note: All these LVN_ODxxx notifications are available only on Vista+ systems so this will not work on Windows XP.

I just tried in a XP and I don't see a problem with the examples but I also don't know what you mean

Edited April 7, 2015 by argumentum

[LarsJ]

Zedna, That's simply wrong. I'm using LVN_ODCACHEHINT and LVN_ODFINDITEM in the examples. These notifications certainly works on my own XP SP3 32 bit.

I'm aware that the current official MicroSoft documentation on the Internet says, that it only works on Vista and later. If you look in the documentation on an old MSDN DVD (before support on XP stopped), you will see that the notifications works on Windows 2000 and later.

Test the examples on a XP, and you'll see that they are working.

argumentum, I'm glad to read, that they works for you too.

[Zedna]

LarsJ

Thanks for info.

I only checked online MSDN documentation (there is mentioned Vista+) and tested your examples only on Win7 at work.

I will definitely test it also on my XP at home later ...

Good news it will work also on XP :-)

Edited April 7, 2015 by Zedna

[BrewManNH]

I've noticed that a lot of the MSDN pages are now saying Vista+ for some functions. I noticed it the other day on EM_SETCUEBANNER, which does work in XP, where the page for it says it's only for Vista+.

[LarsJ]

It may be hard to find good examples for something like virtual listviews. Probably not many are messing around with more than 10.000 rows in a listview in an AutoIt script. But virtual listviews can be useful for a much smaller number of rows.

Here is an example with a search field and a virtual listview. Initially the listview is filled out with 1000 rows of random strings with a length between 10 and 30 characters.

When you type in a search string in the search field (edit control), the listview is updated with the rows, that matches the search string. The listview is updated dynamically while the search string is typed in. The search string can be a regular expression.

This is the code:

expandcollapse popup

#include <GUIConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <Array.au3>

Opt( "MustDeclareVars", 1 )

Global $hGui, $hEdit, $idEditSearch, $hLV, $iItems = 1000, $aItems[$iItems], $aSearch[$iItems], $iSearch = 0

Example()


Func Example()
  ; Create GUI
  $hGui = GUICreate( "Virtual ListView search", 300, 230 )

  ; Create Edit control
  Local $idEdit = GUICtrlCreateEdit( "", 10, 10, 300-20, 20, BitXOR( $GUI_SS_DEFAULT_EDIT, $WS_HSCROLL, $WS_VSCROLL ) )
  $hEdit = GUICtrlGetHandle( $idEdit )
  $idEditSearch = GUICtrlCreateDummy()

  ; Handle $WM_COMMAND messages from Edit control
  ; To be able to read the search string dynamically while it's typed in
  GUIRegisterMsg( $WM_COMMAND, "WM_COMMAND" )

  ; Create ListView
  Local $idLV = GUICtrlCreateListView( "", 10, 40, 300-20, 200-20, $LVS_OWNERDATA, BitOR( $WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT ) )
  $hLV = GUICtrlGetHandle( $idLV ) ;                               Virtual listview                          Reduces flicker
  _GUICtrlListView_AddColumn( $hLV, "Items",  250 )

  ; Handle $WM_NOTIFY messages from ListView
  ; Necessary to display the rows in a virtual ListView
  GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )

  ; Show GUI
  GUISetState( @SW_SHOW )

  ; Fill array
  FillArray( $aItems, $iItems )
  _ArraySort( $aItems, 0, 0, 0, 0, 1 )

  ; Set search array to include all items
  For $i = 0 To $iItems - 1
    $aSearch[$i] = $i
  Next
  $iSearch = $iItems

  ; Display items
  GUICtrlSendMsg( $idLV, $LVM_SETITEMCOUNT, $iSearch, 0 )

  ; Message loop
  While 1
    Switch GUIGetMsg()
      Case $idEditSearch
        Local $sSearch = GUICtrlRead( $idEdit )
        If $sSearch = "" Then
          ; Empty search string, display all rows
          For $i = 0 To $iItems - 1
            $aSearch[$i] = $i
          Next
          $iSearch = $iItems
        Else
          ; Find rows matching the search string
          $iSearch = 0
          For $i = 0 To $iItems - 1
            ;If StringInStr( $aItems[$i], $sSearch ) Then ; Normal search
            If StringRegExp( $aItems[$i], $sSearch ) Then ; Reg. exp. search
              $aSearch[$iSearch] = $i
              $iSearch += 1
            EndIf
          Next
        EndIf
        GUICtrlSendMsg( $idLV, $LVM_SETITEMCOUNT, $iSearch, 0 )
        ConsoleWrite( StringFormat( "%4d", $iSearch ) & " rows matching """ & $sSearch & """" & @CRLF )

      Case $GUI_EVENT_CLOSE
        ExitLoop
    EndSwitch
  WEnd

  GUIDelete()
EndFunc

Func FillArray( ByRef $aItems, $iRows )
  Local $aLetters[26] = [ 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', _
                          'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z' ]
  Local $s
  For $i = 0 To $iRows - 1
    $s = $aLetters[Random(0,25,1)]
    For $j = 1 To Random(10,30,1)
      $s &= $aLetters[Random(0,25,1)]
    Next
    $aItems[$i] = $s
  Next
EndFunc

Func WM_COMMAND( $hWnd, $iMsg, $wParam, $lParam )
  Local $hWndFrom = $lParam
  Local $iCode = BitShift( $wParam, 16 ) ; High word
  Switch $hWndFrom
    Case $hEdit
      Switch $iCode
        Case $EN_CHANGE
          GUICtrlSendToDummy( $idEditSearch )
      EndSwitch
  EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc

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

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

  Switch $hWndFrom
    Case $hLV
      Switch $iCode
        Case $LVN_GETDISPINFOW
          Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
          If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
            Local $sItem = $aItems[$aSearch[DllStructGetData($tNMLVDISPINFO,"Item")]]
            DllStructSetData( $tText, 1, $sItem )
            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
          EndIf
      EndSwitch
  EndSwitch

  Return $GUI_RUNDEFMSG
EndFunc

This is the output in SciTE console (I have added the comments):

566 rows matching "a"            ; normal search for a substring
  36 rows matching "ab"
   5 rows matching "aba"
   2 rows matching "aban"
   1 rows matching "abanj"
   0 rows matching "abanja"

  40 rows matching "^a"           ; reg. exp. search for rows which starts with an "a"
  41 rows matching "a$"           ; ends with an "a"
   1 rows matching "^a.*a$"       ; starts and ends with an "a"
 211 rows matching "a.*a"         ; contains at least two a's

 805 rows matching "[ab]"         ; contains "a" or "b"
  71 rows matching "^[ab]"        ; starts with "a" or "b"
  91 rows matching "[ab]$"        ; ends with "a" or "b"
   6 rows matching "^[ab].*[ab]$" ; starts and ends with "a" or "b"

1000 rows matching ""             ; all rows matches an empty search string
1000 rows matching "^"            ; all rows starts somewhere
1000 rows matching "$"            ; all rows ends somewhere

You'll get other results. The random strings are generated every time you run the script.

Because the listview is updated all the time with completely new rows while you type in the search string, we need a listview which can be updated very fast. A virtual listview is definitely fast. That's why a virtual listview is useful here. Even if it's only 1000 rows.

I got the inspiration for the example from this question in the General Help and Support forum.

Edited April 26, 2015 by LarsJ

[Inpho]

Great work on this, but I've run into a problem. I'm trying to fill with data from  a 2D Array (from IniReadSection). I have .ini file with format:

[Current]

HTC=123|456

ZTE=789|101

This format works fine in another script and behaves as expected. Search works fine but I can't populate the 3 columns here with the correct data; it will only display the key in all columns.

expandcollapse popup

Opt("MustDeclareVars", 1)
Global $aItems, $data[54]
Global $aItems = IniReadSection("local.swm", "New")
Global $hGui, $hEdit, $idEditSearch, $hLV, $iItems = $aItems[0][0] + 1, $aSearch[$iItems], $iSearch = 0, $i = 1, $iRows = 0
Global $icolumns= 0


Example()


Func Example()
    $i = 1
    ; Create GUI
    $hGui = GUICreate("Virtual ListView search", 300, 600)

    ; Create Edit control
    Local $idEdit = GUICtrlCreateEdit("", 10, 10, 300 - 20, 20, BitXOR($GUI_SS_DEFAULT_EDIT, $WS_HSCROLL, $WS_VSCROLL))
    $hEdit = GUICtrlGetHandle($idEdit)
    $idEditSearch = GUICtrlCreateDummy()

    ; Handle $WM_COMMAND messages from Edit control
    ; To be able to read the search string dynamically while it's typed in
    GUIRegisterMsg($WM_COMMAND, "WM_COMMAND")
    ; Create ListView
    Local $idLV = GUICtrlCreateListView("", 10, 40, 300 - 20, 570 - 20, $LVS_OWNERDATA, BitOR($WS_EX_CLIENTEDGE, $LVS_EX_DOUBLEBUFFER, $LVS_EX_FULLROWSELECT))
    $hLV = GUICtrlGetHandle($idLV) ;                               Virtual listview                          Reduces flicker
;"Model         |Ext  |Int  "
    _GUICtrlListView_AddColumn( $hLV, "Items",  150 )
    _GUICtrlListView_AddColumn( $hLV, "Ext",  50 )
    _GUICtrlListView_AddColumn( $hLV, "Int",  50 )

    ; Handle $WM_NOTIFY messages from ListView
    ; Necessary to display the rows in a virtual ListView
    GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")

    ; Show GUI
    GUISetState(@SW_SHOW)

    ; Fill array
    ;FillArray( $aItems, $iItems )
    ;_ArraySort( $aItems, 0, 0, 0, 0, 1 )

    ; Set search array to include all items
    For $i = 0 To $iItems - 1
        $aSearch[$i] = $i
    Next
    $iSearch = $iItems
    $i = 1
    ; Display items
    ;GUICtrlSendMsg($idLV, $LVM_SETITEMCOUNT, $iSearch, 0)

    For $i = 1 To $aItems[0][0]
        $data[$i] = GUICtrlCreateListViewItem($aItems[$i][0] & "|" & $aItems[$i][1], $idLV)
    Next
    ;_ArrayDisplay($aItems)
    ;_ArrayDisplay($data)
    $i = 1
    ;for $i = 1 to $aItems[0][0]
    ;   GUICtrlSetData($hLV, $aItems[$i][0] & "|" & $aItems[$i][1])
    ;Next



    ; Message loop
    While 1
        Switch GUIGetMsg()
            Case $idEditSearch
                Local $sSearch = GUICtrlRead($idEdit)
                If $sSearch = "" Then
                    ; Empty search string, display all rows
                    For $i = 0 To $iItems - 1
                        $aSearch[$i] = $i
                    Next
                    $iSearch = $iItems
                Else
                    ; Find rows matching the search string
                    $iSearch = 0
                    For $i = 0 To $iItems - 1
                        ;If StringInStr( $aItems[$i], $sSearch ) Then ; Normal search
                        If StringRegExp($aItems[$i][0], $sSearch) Then ; Reg. exp. search
                            $aSearch[$iSearch] = $i
                            $iSearch += 1
                        EndIf
                    Next
                EndIf
                GUICtrlSendMsg($idLV, $LVM_SETITEMCOUNT, $iSearch, 0)
                ConsoleWrite(StringFormat("%4d", $iSearch) & " rows matching """ & $sSearch & """" & @CRLF)

            Case $GUI_EVENT_CLOSE
                ExitLoop
        EndSwitch
    WEnd

    GUIDelete()
EndFunc   ;==>Example

 

I've messed about a bit, please ignore the mess.

How do I populate 3 columns with data from a 2D array returned from IniReadSection? The (searchable) key in column 1, and returned data separated by | in other 2 columns. I'm really stuck on this one!

It works fine here with .ini that uses 7 separator characters, although I couldn't get search to work as efficiently as in a virtual listview:

 

expandcollapse popup

GUIRegisterMsg($WM_GETMINMAXINFO, "_WM_GETMINMAXINFO") ; Monitors resizing of the GUI
AdlibRegister("Refresh", 60000)
GUI()

Func GUI()
    Global $form1 = GUICreate($guitext, 696, 590, -1, -1, $ws_sizebox + $ws_minimizebox + $ws_maximizebox)
    ;GUISetBkColor(0xC5D1FE)
    Global $filemenu = GUICtrlCreateMenu("&File")
    Global $refresh = GUICtrlCreateMenuItem("&Refresh (F5)", $filemenu)
    Global $sendopt = GUICtrlCreateMenuItem("&Send bug report", $filemenu)
    Global $exitopt = GUICtrlCreateMenuItem("&Exit", $filemenu)
    Global $edit1 = GUICtrlCreateListView("Model      |BTU/XEU |EVR       |H3G        |VOD        |O2U        |VIR        |ORA        |TMU        ", 10, 10, 673, 493, $LVS_SORTASCENDING, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_DOUBLEBUFFER))

    Local $accel[1][2] = [["{F5}", $refresh]]
    GUISetAccelerators($accel)
    GUISetState(@SW_SHOW)
    Populate()
EndFunc   ;==>GUI

Func Populate()
    Local $data[999]
    Global $asearch[0]
    Global $iitems[0]
    Global $aItems
    $i = 1
    Global $content = IniReadSection($swmx, "Current")
    For $i = $i To $content[0][0]
        $data[$i] = GUICtrlCreateListViewItem($content[$i][0] & "|" & $content[$i][1], $edit1)
    Next
    
    Loop()
EndFunc   ;==>Populate

Func Loop()
    While 1
        $Msg = GUIGetMsg()
        Switch $Msg
            Case $gui_event_close
                Exit
            Case $refresh
                RefreshMan()
            Case $exitopt
                Exit
            Case $sendopt
                bugreport()
        EndSwitch
    WEnd
EndFunc   ;==>Loop

Func RefreshMan()
    $ctrlhgt = ControlGetPos($guitext, "", $edit1)
    GUICtrlDelete($edit1)
    $edit1 = GUICtrlCreateListView("Model      |BTU/XEU |EVR       |H3G        |VOD        |O2U        |VIR        |ORA        |TMU        ", $ctrlhgt[0], $ctrlhgt[1], $ctrlhgt[2], $ctrlhgt[3], $LVS_SORTASCENDING, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_DOUBLEBUFFER))
    Local $data[999]
    Local $content[999][1]
    $i = 1
    $content = IniReadSection($swmx, "Current")
    For $i = $i To $content[0][0]
        $data[$i] = GUICtrlCreateListViewItem($content[$i][0] & "|" & $content[$i][1], $edit1)
    Next
    Local $data = 0
    Local $content = 0
    Return
EndFunc   ;==>RefreshMan

Func _WM_GETMINMAXINFO($hWnd, $Msg, $wParam, $lParam) ; used to limit the minimum size of the GUI
    #forceref $hWnd, $Msg, $wParam, $lParam
    Local $iWidth = 710 ; minimum width setting
    Local $iHeight = 150 ; minimum height setting
    If $hWnd = $form1 Then
        Local $tagMaxinfo = DllStructCreate("int;int;int;int;int;int;int;int;int;int", $lParam)
        DllStructSetData($tagMaxinfo, 7, $iWidth) ; min width
        DllStructSetData($tagMaxinfo, 8, $iHeight) ; min height
        DllStructSetData($tagMaxinfo, 9, $iWidth) ; max width
        DllStructSetData($tagMaxinfo, 10, 99999) ; max height
        Return $GUI_RUNDEFMSG
    EndIf
EndFunc   ;==>_WM_GETMINMAXINFO

It's rough, but I've abandoned it to go with virtual listview, hopefully.

Edited September 5, 2015 by Inpho

[LarsJ]

I would not recommend using a virtual list view to display the contents of an inifile unless you have some specific reasons. An inifil is per definition a small file and can not contain that many rows. A virtual list view has limited functionality compared to a normal list view. You have to implement a lot of the functionality yourself. Therefore, a virtual list view should only be used if there are as many rows that it excludes a normal list view.

Of course it's possible to use a virtual list view, and I can add some code to create the list view. But I need a little more information on what functionality you need. And I need some more data from the inifile.

[Inpho]

I would not recommend using a virtual list view to display the contents of an inifile unless you have some specific reasons. An inifil is per definition a small file and can not contain that many rows. A virtual list view has limited functionality compared to a normal list view. You have to implement a lot of the functionality yourself. Therefore, a virtual list view should only be used if there are as many rows that it excludes a normal list view.

Of course it's possible to use a virtual list view, and I can add some code to create the list view. But I need a little more information on what functionality you need. And I need some more data from the inifile.

No specific reason as such, I just liked the dynamic updating and am trying to learn as much as possible. Althought that means I end up bambi'ing around for 2 days on something mad that could be solved in 5 minutes with someone else's UDF. I get proficient in one area, get a confidence boost then move onto another and feel stupid again

 

I ended up going with a standard listview, with an accelerator. One press of Enter is as close to dynamic as I'll get for now and the search is working perfectly. I won't be near a large database for a while yet but I will be popping back here when the time comes

[LarsJ]

Inpho, You are welcome. I am still working on this project, and expects to have an update ready within a month that deals with some of the issues associated with sorting of data when data is stored in arrays, and also adding and deleting of rows.

[Iczer]

Something wrong with my understanding of "Data stored in databases" case...

Basically i want to fill ListView (3 column) from database (8 column) by selecting data-rows where "title" column is equal to currently selected name in TreeView controll

it seems "Case $LVN_ODCACHEHINT" wont happen and so - listview wont get filled

can i have some input on this?

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <TreeViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiTreeView.au3>
#include <SQLite.au3>
#include <Math.au3>

Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo"
Global $aListView, $hListView, $TreeView_SelTitle

Opt("GUIOnEventMode", 1)
#Region ### START Koda GUI section ### Form=
Global $Form1 = GUICreate("Form1", 881, 516, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "Form1Close")
Global $TreeView = GUICtrlCreateTreeView(8, 16, 319, 489, BitOR($GUI_SS_DEFAULT_TREEVIEW,$TVS_FULLROWSELECT,$WS_HSCROLL,$WS_VSCROLL,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE))
Global $TreeView_Root_1 = GUICtrlCreateTreeViewItem("root - 1", $TreeView)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_1 = GUICtrlCreateTreeViewItem("111", $TreeView_Root_1)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_2 = GUICtrlCreateTreeViewItem("222", $TreeView_Root_1)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_3 = GUICtrlCreateTreeViewItem("333", $TreeView_Root_1)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_Root_2 = GUICtrlCreateTreeViewItem("root - 2", $TreeView)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_4 = GUICtrlCreateTreeViewItem("444", $TreeView_Root_2)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_5 = GUICtrlCreateTreeViewItem("555", $TreeView_Root_2)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $ListView = GUICtrlCreateListView("Name|Time|Category", 336, 16, 538, 486, BitOR($GUI_SS_DEFAULT_LISTVIEW,$LVS_ALIGNLEFT,$WS_HSCROLL,$WS_VSCROLL,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE,$LVS_EX_FULLROWSELECT))
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 333)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 88)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 112)
GUICtrlSetOnEvent(-1, "ListViewClick")
GUIRegisterMsg($WM_NOTIFY, "LWS_WM_NOTIFY")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
DBInit()
$hListView = GUICtrlGetHandle($ListView)
_GUICtrlTreeView_Expand($TreeView, $TreeView_Root_1, True)
While 1
    Sleep(100)
WEnd

Func Form1Close()
    GUIRegisterMsg( $WM_NOTIFY, "" )
    ;
    Exit
EndFunc
;==========================================================================================
Func ListViewClick()

EndFunc
;==========================================================================================
Func TreeView1Click()
    Local $sQuery, $aResult, $iRows, $iColumns
    Static Local $TreeView_SelTitlePRE = ""

    If $TreeView_SelTitlePRE == "" Then
        $TreeView_Selected = _GUICtrlTreeView_GetFirstChild($TreeView, $TreeView_Root_1)
        _GUICtrlTreeView_ClickItem($TreeView, $TreeView_Selected, "left")
    Else
        $TreeView_Selected = _GUICtrlTreeView_GetSelection($TreeView)
    EndIf
    $TreeView_SelTitle = _GUICtrlTreeView_GetText($TreeView, $TreeView_Selected)
    If $TreeView_SelTitle == $TreeView_SelTitlePRE Then
        Return
    Else
        $TreeView_SelTitlePRE = $TreeView_SelTitle
    EndIf

    ConsoleWrite($TreeView_SelTitle & @CRLF)

    $sQuery = "SELECT count(Title) FROM SSDB WHERE Title IS '" & $TreeView_SelTitle & "'"
    _SQLite_GetTable(-1, $sQuery, $aResult, $iRows, $iColumns)
    GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $aResult[2], 0 )
    ConsoleWrite("Rows = " & $aResult[2] & @CRLF)
EndFunc
;==========================================================================================
Func DBInit()
    Local $aResult, $iRows, $iColumns

;------------------------------------------------------------------------------------------
    Local $sPathTo_SSDB = @ScriptDir & "\SSDB.sqlite"
    Local $sPathTo_SQLiteDLL = @ScriptDir & "\sqlite3.dll"
    Global $sSqlBuild_Table = "(Title, Name, Time, Category, column_05, column_06, column_07, column_08)"
;------------------------------------------------------------------------------------------
    If FileExists($sPathTo_SSDB) Then FileDelete($sPathTo_SSDB)
    _SQLite_Startup($sPathTo_SQLiteDLL)
    $hSSDB = _SQLite_Open($sPathTo_SSDB)
;------------------------------------------------------------------------------------------
    _SQLite_GetTable($hSSDB, "PRAGMA page_size;", $aResult, $iRows, $iColumns)
    If Not @error And IsArray($aResult) Then
        $DBCacheSize = _Max(2*FileGetSize($sPathTo_SSDB)/$aResult[2],64*1024)
        _SQLite_Exec($hSSDB, "PRAGMA cache_size = " & $DBCacheSize & ";" )
        ConsoleWrite("...SQLite DB Set Cache Size = 2 x DB file size = " & $DBCacheSize & " Pages of " & $aResult[2] & " Bytes" & @CRLF)
    EndIf
;------------------------------------------------------------------------------------------
    _SQLite_Exec($hSSDB, "CREATE TABLE IF NOT EXISTS SSDB " & $sSqlBuild_Table & ";")
;------------------------------------------------------------------------------------------
    _SQLite_Exec($hSSDB, "CREATE INDEX IF NOT EXISTS index_Title ON SSDB (Title);" )
    _SQLite_Exec($hSSDB, "CREATE INDEX IF NOT EXISTS index_Name ON SSDB (Name);" )
    _SQLite_Exec($hSSDB, "CREATE INDEX IF NOT EXISTS index_Time ON SSDB (Time);" )
;------------------------------------------------------------------------------------------
    $sQuery = ""
    For $i = 1 To 5
        $sQuery &= 'INSERT INTO SSDB ' & $sSqlBuild_Table & ' VALUES '
        $sTitle = String($i) & String($i) & String($i)
        For $j = 0 To 62
            $sQuery &= "('" & StringRegExpReplace($sTitle, "'", "''") & "'"
            $sQuery &= ",'" & StringRegExpReplace($sTitle & "_" & $j, "'", "''") & "'"
            $sQuery &= ",'" & StringRegExpReplace("12345", "'", "''") & "'"
            $sQuery &= ",'" & StringRegExpReplace("123", "'", "''") & "'"
            $sQuery &= ",'','','',''),"
        Next
        $sQuery &= "('" & StringRegExpReplace($sTitle, "'", "''") & "'"
        $sQuery &= ",'" & StringRegExpReplace($sTitle & "_" & $j, "'", "''") & "'"
        $sQuery &= ",'" & StringRegExpReplace("12345", "'", "''") & "'"
        $sQuery &= ",'" & StringRegExpReplace("123", "'", "''") & "'"
        $sQuery &= ",'','','','');"
    Next
    If Not (_SQLite_Exec(-1, $sQuery) = $SQLITE_OK) Then Return SetError(2)

EndFunc
;==========================================================================================
Func LWS_WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[512]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

    Local $tNMHDR, $hWndFrom, $iCode, $tInfo, $VKey
    $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 Name,Time,Category FROM SSDB WHERE Title IS '" & $TreeView_SelTitle & "' AND item_id >= " & $iFrom & " AND item_id <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )

                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1
                        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 $NM_CUSTOMDRAW
                    Local $tNMLVCUSTOMDRAW = DllStructCreate($tagNMLVCUSTOMDRAW, $lParam)
                    Local $dwDrawStage = DllStructGetData($tNMLVCUSTOMDRAW, "dwDrawStage")
                    Local $dwItemSpec  = DllStructGetData($tNMLVCUSTOMDRAW, "dwItemSpec")

                    Switch $dwDrawStage                ; Holds a value that specifies the drawing stage

                        Case $CDDS_PREPAINT
                            ; Before the paint cycle begins
                            Return $CDRF_NOTIFYITEMDRAW    ; Notify the parent window of any item-related drawing operations

                        Case $CDDS_ITEMPREPAINT
                            ; Before painting an item
                            If Mod( $dwItemSpec, 2 ) = 0 Then
                                DllStructSetData( $tNMLVCUSTOMDRAW, "ClrTextBk", 0xFFFFFF )
                            Else
                                DllStructSetData( $tNMLVCUSTOMDRAW, "ClrTextBk", 0xF8FFF8 )
                            EndIf
                            Return $CDRF_NEWFONT           ; $CDRF_NEWFONT must be returned after changing font or colors
                    EndSwitch

            EndSwitch
    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc

 

[LarsJ]

You certainly can. Your code is well structured. Easy to read and understand. I like that.

There are a few minor issues and one major issue. A virtual listview must include the $LVS_OWNERDATA style. GUICtrlSendMsg works only with control IDs. Not handles. The parameters for GUICtrlSendMsg must be of the proper type.  $aResult[2] is a string. It must an integer. When it's a string the listview gets really confused and thinks that there are millions of rows.

The major issue is about the caching technique. The iFrom and iTo fields in the $tagNMLVCACHEHINT structure are simply row indices in the listview. When the listview is filled with rows from the SQL select statement the first time immediately after a click in the treeview, the value of iFrom is 0 and the value of iTo is 29 (the height of the listview matches aproximately 30 rows). This means that the first 30 rows from the select statement is loaded into the $aResult array. This is done in the $LVN_ODCACHEHINT section. $aResult is the cache.

When the rows are shown in the listview they are extracted from the cache, $aResult, and displayed. This is done on $LVN_GETDISPINFO events. There are 30 events to display an entire page on the listview.

If you select the last visible row on the first page in the listview and press Page Down, a new $LVN_ODCACHEHINT event is generated with iFrom = 30 and iTo = 59. This means that the next 30 rows from the select statement is loaded into $aResult. And these 30 rows are displayed on the next page of the listview with 30 $LVN_GETDISPINFO events.

For all this to work the result rows of the SQL select statement must be numbered in an uninterrupted sequence from first to last row in such a way that it's possible to create a 1-1 relation between the result row numbers and listview row indices. This 1-1 relation must ensure that it's possible to extract a result row with a specific number.

In my databases in the examples the tables are extremely simple. The item_id field can simply be used to establish this relation. The result rows of your SQL select statements are not that simple. We have to establish this relation manually.

Schematically it looks like this.

My simple tables:

Your more complicated select statements:

The relation can be established with a few SQL statements:

; Create memory database
ATTACH DATABASE ':memory:' AS DisplayMemDb;

; Create temp view
CREATE TEMP VIEW DisplayView AS SELECT Name,Time,Category FROM SSDB WHERE Title IS $TreeView_SelTitle;

; Create table in memory database
CREATE TABLE DisplayMemDb.RowRelation AS SELECT Name FROM DisplayView;

; The $LVN_ODCACHEHINT select statement
SELECT RowRelation.rowid,DisplayView.Name,Time,Category FROM DisplayView
  INNER JOIN RowRelation ON DisplayView.Name = RowRelation.Name
  WHERE RowRelation.rowid BETWEEN $iFrom + 1 And $iTo + 1;

 

Deprecated code because of missing primary key. See post 43.

expandcollapse popup

#include <GuiListView.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <TreeViewConstants.au3>
#include <WindowsConstants.au3>
#include <GuiTreeView.au3>
#include <SQLite.au3>
#include <Math.au3>

Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom;int iTo"
Global $aListView, $hListView, $TreeView_SelTitle

Opt("GUIOnEventMode", 1)
#Region ### START Koda GUI section ### Form=
Global $Form1 = GUICreate("Form1", 881, 516, -1, -1)
GUISetOnEvent($GUI_EVENT_CLOSE, "Form1Close")
Global $TreeView = GUICtrlCreateTreeView(8, 16, 319, 489, BitOR($GUI_SS_DEFAULT_TREEVIEW,$TVS_FULLROWSELECT,$WS_HSCROLL,$WS_VSCROLL,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE))
Global $TreeView_Root_1 = GUICtrlCreateTreeViewItem("root - 1", $TreeView)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_1 = GUICtrlCreateTreeViewItem("111", $TreeView_Root_1)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_2 = GUICtrlCreateTreeViewItem("222", $TreeView_Root_1)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_3 = GUICtrlCreateTreeViewItem("333", $TreeView_Root_1)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_Root_2 = GUICtrlCreateTreeViewItem("root - 2", $TreeView)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_4 = GUICtrlCreateTreeViewItem("444", $TreeView_Root_2)
GUICtrlSetOnEvent(-1, "TreeView1Click")
Global $TreeView_5 = GUICtrlCreateTreeViewItem("555", $TreeView_Root_2)
GUICtrlSetOnEvent(-1, "TreeView1Click")
;Global $ListView = GUICtrlCreateListView("Rowid|Name|Time|Category", 336, 16, 538, 486, BitOR($GUI_SS_DEFAULT_LISTVIEW,$LVS_ALIGNLEFT,$WS_HSCROLL,$WS_VSCROLL,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE,$LVS_EX_FULLROWSELECT))
Global $ListView = GUICtrlCreateListView("Rowid|Name|Time|Category", 336, 16, 538, 486, $LVS_OWNERDATA, BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE)) ; $LVS_OWNERDATA must be included for a virtual listview
_GUICtrlListView_SetExtendedListViewStyle( $ListView, $LVS_EX_DOUBLEBUFFER+$LVS_EX_FULLROWSELECT )
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 100)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 200)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 88)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 112)
GUICtrlSetOnEvent(-1, "ListViewClick")
GUIRegisterMsg($WM_NOTIFY, "LWS_WM_NOTIFY")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
DBInit()
$hListView = GUICtrlGetHandle($ListView)
_GUICtrlTreeView_Expand($TreeView, $TreeView_Root_1, True)
While 1
    Sleep(100)
WEnd

Func Form1Close()
    GUIRegisterMsg( $WM_NOTIFY, "" )
    ;
    Exit
EndFunc
;==========================================================================================
Func ListViewClick()

EndFunc
;==========================================================================================
Func TreeView1Click()
    Local $sQuery, $aResult, $iRows, $iColumns
    Static Local $TreeView_SelTitlePRE = "", $bFirst = True

    If $TreeView_SelTitlePRE == "" Then
        $TreeView_Selected = _GUICtrlTreeView_GetFirstChild($TreeView, $TreeView_Root_1)
        _GUICtrlTreeView_ClickItem($TreeView, $TreeView_Selected, "left")
    Else
        $TreeView_Selected = _GUICtrlTreeView_GetSelection($TreeView)
    EndIf
    $TreeView_SelTitle = _GUICtrlTreeView_GetText($TreeView, $TreeView_Selected)
    If $TreeView_SelTitle == $TreeView_SelTitlePRE Then
        Return
    Else
        $TreeView_SelTitlePRE = $TreeView_SelTitle
    EndIf

    ConsoleWrite($TreeView_SelTitle & @CRLF)

    $sQuery = "SELECT count(Title) FROM SSDB WHERE Title IS '" & $TreeView_SelTitle & "'"
    _SQLite_GetTable(-1, $sQuery, $aResult, $iRows, $iColumns)
    ;GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $aResult[2], 0 )
    ConsoleWrite("Rows = " & $aResult[2] & @CRLF)

    If Not $bFirst Then
      _SQLite_Exec( -1, "DROP VIEW DisplayView;" )
      _SQLite_Exec( -1, "DROP TABLE DisplayMemDb.RowRelation;" )
    Else
      $bFirst = False
    EndIf

    _SQLite_Exec( -1, "CREATE TEMP VIEW DisplayView AS SELECT Name,Time,Category FROM SSDB WHERE Title IS '" & $TreeView_SelTitle & "';" )
    _SQLite_Exec( -1, "CREATE TABLE DisplayMemDb.RowRelation AS SELECT Name FROM DisplayView;" )

    GUICtrlSendMsg( $ListView, $LVM_SETITEMCOUNT, Int($aResult[2]), 0 ) ; GUICtrlSendMsg works with control IDs only, $aResult[2] (string) must be an integer

EndFunc
;==========================================================================================
Func DBInit()
    Local $aResult, $iRows, $iColumns

;------------------------------------------------------------------------------------------
    Local $sPathTo_SSDB = @ScriptDir & "\SSDB.sqlite"
    Local $sPathTo_SQLiteDLL = @ScriptDir & "\sqlite3.dll"
    Global $sSqlBuild_Table = "(Title, Name, Time, Category, column_05, column_06, column_07, column_08)"
;------------------------------------------------------------------------------------------
    If FileExists($sPathTo_SSDB) Then FileDelete($sPathTo_SSDB)
    _SQLite_Startup($sPathTo_SQLiteDLL)
    $hSSDB = _SQLite_Open($sPathTo_SSDB)
;------------------------------------------------------------------------------------------
    _SQLite_GetTable($hSSDB, "PRAGMA page_size;", $aResult, $iRows, $iColumns)
    If Not @error And IsArray($aResult) Then
        $DBCacheSize = _Max(2*FileGetSize($sPathTo_SSDB)/$aResult[2],64*1024)
        _SQLite_Exec($hSSDB, "PRAGMA cache_size = " & $DBCacheSize & ";" )
        ConsoleWrite("...SQLite DB Set Cache Size = 2 x DB file size = " & $DBCacheSize & " Pages of " & $aResult[2] & " Bytes" & @CRLF)
    EndIf
;------------------------------------------------------------------------------------------
    _SQLite_Exec($hSSDB, "CREATE TABLE IF NOT EXISTS SSDB " & $sSqlBuild_Table & ";")
;------------------------------------------------------------------------------------------
    _SQLite_Exec($hSSDB, "CREATE INDEX IF NOT EXISTS index_Title ON SSDB (Title);" )
    _SQLite_Exec($hSSDB, "CREATE INDEX IF NOT EXISTS index_Name ON SSDB (Name);" )
    _SQLite_Exec($hSSDB, "CREATE INDEX IF NOT EXISTS index_Time ON SSDB (Time);" )
;------------------------------------------------------------------------------------------
    $sQuery = ""
    For $i = 1 To 5
        $sQuery &= 'INSERT INTO SSDB ' & $sSqlBuild_Table & ' VALUES '
        $sTitle = String($i) & String($i) & String($i)
        For $j = 0 To 6200
            $sQuery &= "('" & StringRegExpReplace($sTitle, "'", "''") & "'"
            $sQuery &= ",'" & StringRegExpReplace($sTitle & "_" & $j, "'", "''") & "'"
            $sQuery &= ",'" & StringRegExpReplace("12345", "'", "''") & "'"
            $sQuery &= ",'" & StringRegExpReplace("123", "'", "''") & "'"
            $sQuery &= ",'','','',''),"
        Next
        $sQuery &= "('" & StringRegExpReplace($sTitle, "'", "''") & "'"
        $sQuery &= ",'" & StringRegExpReplace($sTitle & "_" & $j, "'", "''") & "'"
        $sQuery &= ",'" & StringRegExpReplace("12345", "'", "''") & "'"
        $sQuery &= ",'" & StringRegExpReplace("123", "'", "''") & "'"
        $sQuery &= ",'','','','');"
    Next
    If Not (_SQLite_Exec(-1, $sQuery) = $SQLITE_OK) Then Return SetError(2)

    _SQLite_Exec( -1, "ATTACH DATABASE ':memory:' AS DisplayMemDb;" ) ; <<<<<<<<<<<<<<<<<<<
EndFunc
;==========================================================================================
Func LWS_WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
    Local Static $tText = DllStructCreate( "wchar[512]" )
    Local Static $pText = DllStructGetPtr( $tText )
    Local Static $aResult, $iRows, $iFrom

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

    Switch $hWndFrom
        Case $hListView
            Switch $iCode

                Case $NM_CUSTOMDRAW
                    Local $tNMLVCUSTOMDRAW = DllStructCreate($tagNMLVCUSTOMDRAW, $lParam)
                    Local $dwDrawStage = DllStructGetData($tNMLVCUSTOMDRAW, "dwDrawStage")
                    Local $dwItemSpec  = DllStructGetData($tNMLVCUSTOMDRAW, "dwItemSpec")

                    Switch $dwDrawStage                ; Holds a value that specifies the drawing stage

                        Case $CDDS_PREPAINT
                            ; Before the paint cycle begins
                            Return $CDRF_NOTIFYITEMDRAW    ; Notify the parent window of any item-related drawing operations

                        Case $CDDS_ITEMPREPAINT
                            ; Before painting an item
                            If Mod( $dwItemSpec, 2 ) = 0 Then
                                DllStructSetData( $tNMLVCUSTOMDRAW, "ClrTextBk", 0xFFFFFF )
                            Else
                                DllStructSetData( $tNMLVCUSTOMDRAW, "ClrTextBk", 0xF8FFF8 )
                            EndIf
                            Return $CDRF_NEWFONT           ; $CDRF_NEWFONT must be returned after changing font or colors
                    EndSwitch

                Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) - $iFrom + 1
                        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 ), $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    ;Local $sSQL = "SELECT Name,Time,Category FROM SSDB WHERE Title IS '" & $TreeView_SelTitle & "' AND item_id >= " & $iFrom & " AND item_id <= " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) & ";"
                    Local $sSQL = "SELECT RowRelation.rowid,DisplayView.Name,Time,Category FROM DisplayView INNER JOIN RowRelation ON DisplayView.Name = RowRelation.Name WHERE RowRelation.rowid BETWEEN " & $iFrom + 1 & " And " & DllStructGetData( $tNMLVCACHEHINT, "iTo" ) + 1 & ";"
                    _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )

            EndSwitch
    EndSwitch

    Return $GUI_RUNDEFMSG
EndFunc

Deprecated code because of missing primary key. See post 43.

Edited September 15, 2017 by LarsJ
New image links

[pvnn]

Hi
Why do so impedes the movement of the Virtual ListView?
For example, I type 5555, and try to move up and down arrow keys

 

expandcollapse popup

#include <Timers.au3>
#include <SQLite.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>

#include <ButtonConstants.au3>
#include <EditConstants.au3>


_SQLite_Startup()
If @error Then Exit MsgBox(16, "Error SQLite", "SQLite.dll")

$sFileDB = @ScriptDir & '\Data.db'
$TableName='table1'
$TableView='View1'
Global $sSQL
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom"



; Create DB
;~  FileDelete(@ScriptDir & '\Data.db') ; recreate data base
If Not FileExists($sFileDB) Then
    $Row=100000
    $hStarttime=_Timer_Init()
    $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
    $sExec = 'BEGIN; Create Table table1 ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [f_1] TEXT,[f_2] TEXT,[f_3] TEXT,[f_4] TEXT,[f_5] TEXT );'
    ; Create string
    For $i=0 To $Row-1
        $ind=Random(5,25,1)
        $sData=StringMid($Line,$ind)
        $sExec &= 'INSERT INTO '&$TableName&' (f_1,f_2,f_3,f_4,f_5) VALUES ("' & $sData&'","'&$sData&'","'&$sData&'","'&$sData&'","'&$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&'Добавление записей в БД: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf




; GUI
$Form1 = GUICreate("Form1", 803, 457, -1, -1)
Global $hListView= GUICtrlCreateListView("", 8, 24, 785, 383, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS))
_GUICtrlListView_SetExtendedListViewStyle(-1, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
  $hLV = GUICtrlGetHandle( $hListView ) ;    Virtual listview    Reduces flicker
  _GUICtrlListView_AddColumn( $hLV, "Column1",  50 )
  _GUICtrlListView_AddColumn( $hLV, "Column2",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column3",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column4",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column5",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column6",  100 )

; Поиск данных
    $InpRecord = GUICtrlCreateInput("", 8, 416, 249, 21)
    GUICtrlSetState(-1,$GUI_FOCUS)
    $hInput = GUICtrlGetHandle( $InpRecord )
    $InputChange = GUICtrlCreateDummy()
    GUIRegisterMsg( $WM_COMMAND, "ON_Change")


$hStarttime=_Timer_Init()
GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)




 ; Выполнить SQL-запрос
Query('SELECT * FROM  '&$TableName)



While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
         Exit
    Case $InputChange
         $sInput = GUICtrlRead( $InpRecord ) ; Get control data
         ConsoleWrite(@CRLF&'Input = '&$sInput)
         Query('SELECT * FROM  '&$TableName&' WHERE UPPER(id) like UPPER("'&$sInput&'%")')
        _GUICtrlListView_SetItemSelected($hListView,0 ,True)    ; highlight row
    EndSwitch
WEnd



; Change $hInput
Func ON_Change( $hWnd, $iMsg, $wParam, $lParam )
  Local $hWndFrom = $lParam
  Local $iCode = BitShift( $wParam, 16 ) ; High word
  Switch $hWndFrom
    ; Input
   Case $hInput
      Switch $iCode
          Case $EN_CHANGE
          GUICtrlSendToDummy( $InputChange ) ; => Case $InputChange
      EndSwitch
  EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc



Func Query($sQuery)
    Local $aRow, $iRows = 0

    _SQLite_Open($sFileDB)
    _SQLite_QuerySingleRow (-1, 'SELECT COUNT(*) FROM ('&$sQuery&')',$aRow) ; Item count
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1

    $sSQL=$sQuery

    ConsoleWrite(@CRLF&$sSQL)
    ConsoleWrite(@CRLF&$iRows)

    ; Update Virtual ListView
    GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $iRows, 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 $VisibleRow

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

  Switch $hWndFrom
    Case $hLV
      Switch $iCode

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    $VisibleRow=_GUICtrlListView_GetCounterPage($hListView)
                    Local $sSQL2=$sSQL&' limit '&$VisibleRow&' OFFSET '&$iFrom& ';'
                    _SQLite_GetTable2d( -1, $sSQL2, $aResult, $iRows, $iColumns )


                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][$nCol]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

      EndSwitch
 EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc

 

[pvnn]

16 minutes ago, pvnn said:

Hi
Why do so impedes the movement of the Virtual ListView?
For example, I type 5555, and try to move up and down arrow keys

 

expandcollapse popup

#include <Timers.au3>
#include <SQLite.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>

#include <ButtonConstants.au3>
#include <EditConstants.au3>


_SQLite_Startup()
If @error Then Exit MsgBox(16, "Error SQLite", "SQLite.dll")

$sFileDB = @ScriptDir & '\Data.db'
$TableName='table1'
$TableView='View1'
Global $sSQL
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom"



; Create DB
;~  FileDelete(@ScriptDir & '\Data.db') ; recreate data base
If Not FileExists($sFileDB) Then
    $Row=100000
    $hStarttime=_Timer_Init()
    $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
    $sExec = 'BEGIN; Create Table table1 ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [f_1] TEXT,[f_2] TEXT,[f_3] TEXT,[f_4] TEXT,[f_5] TEXT );'
    ; Create string
    For $i=0 To $Row-1
        $ind=Random(5,25,1)
        $sData=StringMid($Line,$ind)
        $sExec &= 'INSERT INTO '&$TableName&' (f_1,f_2,f_3,f_4,f_5) VALUES ("' & $sData&'","'&$sData&'","'&$sData&'","'&$sData&'","'&$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&'Добавление записей в БД: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf




; GUI
$Form1 = GUICreate("Form1", 803, 457, -1, -1)
Global $hListView= GUICtrlCreateListView("", 8, 24, 785, 383, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS))
_GUICtrlListView_SetExtendedListViewStyle(-1, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
  $hLV = GUICtrlGetHandle( $hListView ) ;    Virtual listview    Reduces flicker
  _GUICtrlListView_AddColumn( $hLV, "Column1",  50 )
  _GUICtrlListView_AddColumn( $hLV, "Column2",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column3",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column4",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column5",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column6",  100 )


    $InpRecord = GUICtrlCreateInput("", 8, 416, 249, 21)
    GUICtrlSetState(-1,$GUI_FOCUS)
    $hInput = GUICtrlGetHandle( $InpRecord )
    $InputChange = GUICtrlCreateDummy()
    GUIRegisterMsg( $WM_COMMAND, "ON_Change")


$hStarttime=_Timer_Init()
GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)




 ; Exsec SQL
Query('SELECT * FROM  '&$TableName)



While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
         Exit
    Case $InputChange
         $sInput = GUICtrlRead( $InpRecord ) ; Get control data
         ConsoleWrite(@CRLF&'Input = '&$sInput)
         Query('SELECT * FROM  '&$TableName&' WHERE UPPER(id) like UPPER("'&$sInput&'%")')
        _GUICtrlListView_SetItemSelected($hListView,0 ,True)    ; highlight row
    EndSwitch
WEnd



; Change $hInput
Func ON_Change( $hWnd, $iMsg, $wParam, $lParam )
  Local $hWndFrom = $lParam
  Local $iCode = BitShift( $wParam, 16 ) ; High word
  Switch $hWndFrom
    ; Input
   Case $hInput
      Switch $iCode
          Case $EN_CHANGE
          GUICtrlSendToDummy( $InputChange ) ; => Case $InputChange
      EndSwitch
  EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc



Func Query($sQuery)
    Local $aRow, $iRows = 0

    _SQLite_Open($sFileDB)
    _SQLite_QuerySingleRow (-1, 'SELECT COUNT(*) FROM ('&$sQuery&')',$aRow) ; Item count
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1

    $sSQL=$sQuery

    ConsoleWrite(@CRLF&$sSQL)
    ConsoleWrite(@CRLF&$iRows)

    ; Update Virtual ListView
    GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $iRows, 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 $VisibleRow

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

  Switch $hWndFrom
    Case $hLV
      Switch $iCode

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    $VisibleRow=_GUICtrlListView_GetCounterPage($hListView)
                    Local $sSQL2=$sSQL&' limit '&$VisibleRow&' OFFSET '&$iFrom& ';'
                    _SQLite_GetTable2d( -1, $sSQL2, $aResult, $iRows, $iColumns )


                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][$nCol]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

      EndSwitch
 EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc

 

 

[LarsJ]

Add this line

ConsoleWrite( "$sSQL2 = " & $sSQL2 & @CRLF )

as the last line in the $LVN_ODCACHEHINT section and you'll see why.

[Iczer]

WOW! Many Thanks! It was great help - very educationally and simple

[pvnn]

Think slows due to "like UPPER (" 5555% ")"?
I created a View and also slows down
Help me please. How can I find the right?

expandcollapse popup

#include <Timers.au3>
#include <SQLite.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>

#include <ButtonConstants.au3>
#include <EditConstants.au3>


_SQLite_Startup()
If @error Then Exit MsgBox(16, "Error SQLite", "SQLite.dll")

$sFileDB = @ScriptDir & '\Data.db'
$TableName='table1'
$TableView='View1'
Global $sSQL
Global Const $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom"



; Create DB
;~  FileDelete(@ScriptDir & '\Data.db') ; recreate data base
If Not FileExists($sFileDB) Then
    $Row=100000
    $hStarttime=_Timer_Init()
    $Line='tuysdufysdfsduyfiusydfisdyfiusfdsdf'
    $sExec = 'BEGIN; Create Table table1 ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [f_1] TEXT,[f_2] TEXT,[f_3] TEXT,[f_4] TEXT,[f_5] TEXT );'
    ; Create string
    For $i=0 To $Row-1
        $ind=Random(5,25,1)
        $sData=StringMid($Line,$ind)
        $sExec &= 'INSERT INTO '&$TableName&' (f_1,f_2,f_3,f_4,f_5) VALUES ("' & $sData&'","'&$sData&'","'&$sData&'","'&$sData&'","'&$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&'Добавление записей в БД: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf




; GUI
$Form1 = GUICreate("Form1", 803, 457, -1, -1)
Global $hListView= GUICtrlCreateListView("", 8, 24, 785, 383, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS))
_GUICtrlListView_SetExtendedListViewStyle(-1, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
  $hLV = GUICtrlGetHandle( $hListView ) ;    Virtual listview    Reduces flicker
  _GUICtrlListView_AddColumn( $hLV, "Column1",  50 )
  _GUICtrlListView_AddColumn( $hLV, "Column2",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column3",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column4",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column5",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column6",  100 )

; Поиск данных
    $InpRecord = GUICtrlCreateInput("", 8, 416, 249, 21)
    GUICtrlSetState(-1,$GUI_FOCUS)
    $hInput = GUICtrlGetHandle( $InpRecord )
    $InputChange = GUICtrlCreateDummy()
    GUIRegisterMsg( $WM_COMMAND, "ON_Change")


$hStarttime=_Timer_Init()
GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)




 ; Выполнить SQL-запрос
Query('SELECT * FROM '&$TableName)



While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
         Exit
    Case $InputChange
         $sInput = GUICtrlRead( $InpRecord ) ; Get control data
         ConsoleWrite(@CRLF&'Input = '&$sInput)
         Query('SELECT * FROM  '&$TableName&' WHERE UPPER(id) like UPPER("'&$sInput&'%")')
;~      _GUICtrlListView_SetItemSelected($hListView,0 ,True)    ; highlight row
    EndSwitch
WEnd



; Change $hInput
Func ON_Change( $hWnd, $iMsg, $wParam, $lParam )
  Local $hWndFrom = $lParam
  Local $iCode = BitShift( $wParam, 16 ) ; High word
  Switch $hWndFrom
    ; Input
   Case $hInput
      Switch $iCode
          Case $EN_CHANGE
          GUICtrlSendToDummy( $InputChange ) ; => Case $InputChange
      EndSwitch
  EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc



Func Query($sQuery)
    Local $aRow, $iRows = 0
    _SQLite_Open($sFileDB)
    ConsoleWrite(@CRLF&'DROP VIEW IF EXISTS '&$TableView&'; CREATE VIEW '&$TableView&' AS '&$sQuery)
    _SQLite_Exec (-1, 'DROP VIEW IF EXISTS '&$TableView&'; CREATE VIEW '&$TableView&' AS '&$sQuery) ; Create View

    _SQLite_QuerySingleRow( -1, 'SELECT COUNT(*) FROM '&$TableView&' LIMIT 1;',$aRow ) ; Item count
    If IsArray( $aRow ) Then $iRows = $aRow[0] + 1
    ConsoleWrite(@CRLF&$iRows)

    $sSQL='SELECT * FROM '&$TableView

    ; Update Virtual ListView
    GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $iRows, 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 $VisibleRow

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

  Switch $hWndFrom
    Case $hLV
      Switch $iCode

                Case $LVN_ODCACHEHINT
                    Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $iColumns
                    $iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
                    $VisibleRow=_GUICtrlListView_GetCounterPage($hListView)
                    Local $sSQL2=$sSQL&' limit '&$VisibleRow&' OFFSET '&$iFrom& ';'
                    ConsoleWrite( @CRLF&"$sSQL2 = " & $sSQL2 & @CRLF )
                    _SQLite_GetTable2d( -1, $sSQL2, $aResult, $iRows, $iColumns )


                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][$nCol]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
                        EndIf
                    EndIf

      EndSwitch
 EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc