virtual ListView sqlite

[Adrian123]

Hi I need help with the code bellow, my goal is to make faster sqlite queries to Sqlite because _SQLite_GetTable2d seams to be slow on big tables.

Thank you :

 

https://www.autoitscript.com/forum/topic/182469-using-controls-to-edit-cells-in-a-listview/

 

Well my code is faster but a I have 2 issues :

1 Memory leaks

2 Listview ( virtual) a little slow when scrolling (column headers delay )

Please help I am not a WinApi specialist. (The hugetable its actuality much bigger I'm limited by upload size)

Thank You.

 

expandcollapse popup

#comments-start

C++ Source of Dll

#include <vector>
#include <string>
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"
using namespace std;



extern "C"
{
    // A function adding two integers and returning the result
    char * SqliteSelect(char *  path ,char  * sSql , char * rowsep, char * tabsep  )
    {
        std::string rez="";
        bool  once=true;
        sqlite3 *db;

        if ( sqlite3_open_v2(path, &db,SQLITE_OPEN_READONLY, NULL) == SQLITE_OK)
    {
        sqlite3_stmt *stmt;
        if (sqlite3_prepare_v2(db, sSql, -1, &stmt, 0) == SQLITE_OK)
            {
            int nCols = sqlite3_column_count(stmt);
            while (sqlite3_step(stmt) == SQLITE_ROW)
            {
            for (int nCol = 0; nCol < nCols; nCol++)
                    {
                    if (once==true)
                                {
                                    rez+=(char *) sqlite3_column_name(stmt, nCol);
                                     if (nCol < nCols-1 )
                                     {
                                        rez+=tabsep;
                                     }

                                                     else {
                                         rez+=rowsep;
                                            once=false;
                                           // sqlite3_reset(stmt);
                                            nCol=0;
                                                     }
                                }

                            if (once==false){

                                        if ( sqlite3_column_text(stmt, nCol)!=nullptr)
                                        {
                                            rez+=(char *) sqlite3_column_text(stmt, nCol);
                                        }
                                        if (nCol < nCols-1 )
                                             rez+=tabsep;
                                    }
                        }
                    rez+=rowsep;
            }

        }
        else {

return (char *)sqlite3_errmsg(db);

        sqlite3_close(db);


    }


        sqlite3_close(db);
    }


 char *cstr = new char[rez.length() + 1];
    strcpy(cstr, rez.c_str());
    return cstr;
    }



char * SqliteExec(char *  path ,char  * sSql  )
    {
std::string rez="Error: ";
            sqlite3 *db;
           char *error;
        if ( sqlite3_open_v2(path, &db,SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK)
            {

        sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL,&error);
               if( sqlite3_exec(db, sSql, NULL, NULL, &error) != SQLITE_OK )
               {
                rez+= (char *)sqlite3_errmsg(db);
                  sqlite3_exec(db, "ROLLBACK", NULL, NULL, &error);
                sqlite3_close(db);
               }
              sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &error);
             sqlite3_close(db);
if (rez=="Error: " )
{
  rez="OK";
}
char *cstr = new char[rez.length() + 1];
    strcpy(cstr, rez.c_str());
    return cstr;

            }

    }

}




#comments-end


#include <GuiListView.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPIvkeysConstants.au3>
#include <WinAPIShellEx.au3>


#include <GuiEdit.au3>

Opt( "MustDeclareVars", 1 )


Global $hListView, $iItem = -1, $iSubItem = 0, $aRect
Global  $idComboOpen, $idComboClose, $bComboOpen = False, $bComboDoNotOpen = False
Global $bEditEscape = True, $bEditUpDown = False, $bListboxOpen = False
Global $bComboOpenOnEnter = True
Global $bComboOpenOnSpace = False
Global $bComboOpenOnDoubleClick = True
Global $bListboxAcceptClickEnter = True
Global $hEdit
;Global $Table



Func __SQLite_StringToUtf8Struct($sString)
    Local $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sString, "int", -1, _
            "ptr", 0, "int", 0, "ptr", 0, "ptr", 0)
    If @error Then Return SetError(1, @error, "") ; DllCall error
    Local $tText = DllStructCreate("char[" & $aResult[0] & "]")
    $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sString, "int", -1, _
            "struct*", $tText, "int", $aResult[0], "ptr", 0, "ptr", 0)
    If @error Then Return SetError(2, @error, "") ; DllCall error
    Return $tText
EndFunc   ;==>__SQLite_StringToUtf8Struct

    Global $Headers ,$RowsArray
Global $TabSep =@TAB
Example()

Func Compare_Headers($ArrayIni,$Array_)

    If   UBound($ArrayIni)<> UBound($Array_) Then
    Return 1
    EndIf

    For $R=0 to UBound($ArrayIni)-1
        if $ArrayIni[$R] <> $Array_[$R] Then
        Return 1
        EndIf
    Next

Return 0
EndFunc

#include <File.au3>
Func nCheckDB( $sDBname , $SSQL , $idListView )

    Local $iniH =$Headers;

    GUICtrlSendMsg( $idListView, $LVM_SETITEMCOUNT,0, 0)

    Local $RowSep =@LF
    Local $tFilename = __SQLite_StringToUtf8Struct($sDBname)
    Local $tSsql = __SQLite_StringToUtf8Struct($SSQL )

    Local $hDLL = DllOpen("libSlqiteFaster.dll")
    Local $Arrx = DllCall($hDLL,"STR" ,"SqliteSelect",  "struct*", $tFilename ,"struct*" ,$tSsql ,"struct*" ,__SQLite_StringToUtf8Struct($RowSep) ,"struct*" ,__SQLite_StringToUtf8Struct($TabSep) )

    DllClose($hDLL)

     $RowsArray= StringSplit($Arrx[0],$RowSep)
     $Headers=StringSplit($RowsArray[1],$TabSep)
      Local $iCols = $Headers[0]
    Local $iRows = $RowsArray[0]

    If Compare_Headers( $iniH,$Headers)=1 Then

        While _GUICtrlListView_GetColumnCount($idListView)>0
            _GUICtrlListView_DeleteColumn ( $idListView, 0 )
        WEnd


                For $i = 1 To $iCols
                            _GUICtrlListView_AddColumn( $idListView,$Headers[ $i ], 75 )
                        Next

    EndIf

    GUICtrlSendMsg( $idListView, $LVM_SETITEMCOUNT, $iRows-2, 0 )

;$RowsArray=0
;$Headers=0
$iRows=0
$iCols=0
$tFilename=0
$tSsql=0

EndFunc




Func Example()

Local $hGui = GUICreate( "LV_", @DesktopWidth-20, @DesktopHeight-40,-1,-1, $WS_SIZEBOX+$WS_MAXIMIZEBOX+ $WS_MINIMIZEBOX )

Global $Button3 = GUICtrlCreateButton("Ne3", 400, 0, 75, 25)
Global $Button1 = GUICtrlCreateButton("NeSmler", 100, 0, 75, 25)

    Local $idListView
    Local $pListViewCallback
    Local $pEditCallback
    Local $pListCommand
    Local $pGuiCallback


    ;MsgBox(0,"","STOP")
    $idListView= GUICtrlCreateListView( "", 10, 40, @DesktopWidth-20-20, @DesktopHeight-80-100,$LVS_OWNERDATA ) ;$LVS_NOCOLUMNHEADER
    _GUICtrlListView_SetExtendedListViewStyle( $idListView, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_BORDERSELECT,$LVS_EX_DOUBLEBUFFER, $LVS_EX_GRIDLINES) ) ; $LVS_EX_DOUBLEBUFFER+$LVS_EX_FULLROWSELECT +
    GUICtrlSetResizing($hGui, $GUI_DOCKBORDERS)
    $hListView = GUICtrlGetHandle( $idListView )
    ; Subclass ListView to handle messages related to ComboBox


    ; ComboBox open and close events
    $idComboOpen = GUICtrlCreateDummy()
    $idComboClose = GUICtrlCreateDummy()

    GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )

    ; Subclass callback functions
    $pListViewCallback= DllCallbackGetPtr( DllCallbackRegister( "ListViewCallback", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) )
    $pEditCallback = DllCallbackGetPtr( DllCallbackRegister( "EditCallback", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) )
     $pListCommand = DllCallbackGetPtr( DllCallbackRegister( "ListCommand", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) )
     $pGuiCallback = DllCallbackGetPtr( DllCallbackRegister( "GuiCallback", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) )
_WinAPI_SetWindowSubclass( $hListView, $pListViewCallback, 9998, 0 )

    ; Show GUI
    GUISetState( @SW_SHOW )
    ; Message loop
    While 1
        Switch GUIGetMsg()

            Case $Button3
                GuiSetState(@SW_LOCK, $hGui)
            nCheckDB("bigtable.sqlite" ,"SELECT * FROM hugetable;",$idListView )
            GuiSetState(@SW_UNLOCK, $hGui)
            Case $Button1
                GuiSetState(@SW_LOCK, $hGui)
    For $i = 0 To _GUICtrlListView_GetColumnCount($idListView)-1
                            _GUICtrlListView_SetColumnWidth ( $hListView, $i,  $LVSCW_AUTOSIZE_USEHEADER  ) ;$LVSCW_AUTOSIZE
                        Next

GuiSetState(@SW_UNLOCK, $hGui)

#cs
    Local $S_PH = "C:\\Users\\achis\\Desktop\\PP2C 24.11.2015_OK\\Prod.sqlite"
    Local $SSQLi ="UPDATE sumbomLVL_Static Set part='10000611' WHERE rowid=1 ;"
    Local $hDLL = DllOpen("libSlqiteFaster.dll")
    Local $Arrx = DllCall($hDLL,"STR" ,"SqliteExec",  "struct*",  __SQLite_StringToUtf8Struct($S_PH) ,"struct*" ,__SQLite_StringToUtf8Struct($SSQLi)  )
    MsgBox ( 0,"",$Arrx[0])

    DllClose($hDLL)


#ce


            Case $idComboOpen
                If $bComboOpen Then
                    ; If another ComboBox is open then delete it
                    _WinAPI_RemoveWindowSubclass( $hEdit, $pEditCallback, 9999 )
                    ;_WinAPI_RemoveWindowSubclass( $hListView, $pListCommand, 9999 )
                    _WinAPI_RemoveWindowSubclass( $hGui, $pGuiCallback, 9999 )
                    _GUICtrlEdit_Destroy( $hEdit )
                EndIf

$hEdit = _GUICtrlEdit_Create( $hListView,_GUICtrlListView_GetItemText( $hListView, $iItem, $iSubItem ), $aRect[0], $aRect[1], $aRect[2] - $aRect[0],20,$ES_AUTOHSCROLL )
_GUICtrlEdit_SetSel( $hEdit, 0, -1 )
                ; Create subclasses to handle Windows messages
                _WinAPI_SetWindowSubclass( $hEdit, $pEditCallback, 9999, 0 )    ; Messages from the Edit control of the ComboBox
                ;_WinAPI_SetWindowSubclass( $hListView, $pListCommand, 9999, 0 ) ; WM_COMMAND messages from Listbox part of ComboBox
                _WinAPI_SetWindowSubclass( $hGui, $pGuiCallback, 9999, 0 )      ; Handle GUI messages related to ComboBox control
                ; Set focus to ComboBox                                         ; Subclasses are used only when ComboBox is open
                _WinAPI_SetFocus( $hEdit )
                $bComboOpen = True


            Case $idComboClose

                If Not $bComboOpen Then ContinueLoop
                If GUICtrlRead( $idComboClose ) Then
                    Local $c_item = StringSplit($RowsArray[$iItem+2],$TabSep)[$iSubItem+1]

                Local $T = StringSplit(  $RowsArray[$iItem+2],$TabSep)


                $T[$iSubItem] =_GUICtrlEdit_GetText( $hEdit )
                $RowsArray[$iItem+2]=_ArrayToString($T,$TabSep)


                    _WinAPI_SetFocus( $hListView ) ; Set focus to ListView
                EndIf
                ; Delete ComboBox control
                _WinAPI_RemoveWindowSubclass( $hEdit, $pEditCallback, 9999 )
                ;_WinAPI_RemoveWindowSubclass( $hListView, $pListCommand, 9999 )
                _WinAPI_RemoveWindowSubclass( $hGui, $pGuiCallback, 9999 )
                _GUICtrlEdit_Destroy( $hEdit )
                $bComboOpen = False


            Case $GUI_EVENT_PRIMARYDOWN, $GUI_EVENT_SECONDARYDOWN
                If Not $bComboOpen Then ContinueLoop
                ; Clicks in Listbox part of ComboBox should not delete it
                If $bListboxOpen Then ContinueLoop
                ; Clicks in Edit part of ComboBox should not delete it
                Local $aPos = MouseGetWindowPos( $hListView )
                If Not ( $aPos[0] > $aRect[0] And $aPos[0] < $aRect[2] And $aPos[1] > $aRect[1] And $aPos[1] < $aRect[1] + 20 ) Then
                    GUICtrlSendToDummy( $idComboClose ) ; Delete ComboBox
                    Local $aSize = WinGetPos( $hListView )
                    If $aPos[0] > 0 And $aPos[1] > 0 And $aPos[0] < $aSize[2] And $aPos[1] < $aSize[3] Then _
                        _WinAPI_SetFocus( $hListView ) ; Set focus to ListView if mouse click is inside ListView
                EndIf

            Case $GUI_EVENT_CLOSE
                  Exit

                If Not $bEditEscape Then ExitLoop
                $bEditEscape = False
        EndSwitch
    WEnd

    ; Cleanup
    GUIDelete()
EndFunc



Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )

    Local Static $bNotXP = Not ( @OSVersion = "WIN_XP" )
    Local Static $tRect = DllStructCreate( $tagRECT )
    Local Static $hBrush = _WinAPI_CreateSolidBrush( 0xFFFF00 )
    Local Static $tText = DllStructCreate( "wchar[50]" )
    Local Static $pText = DllStructGetPtr( $tText )

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

    Switch $hWndFrom
        Case $hListView
            Switch $iCode


            Case $LVN_GETDISPINFOW
                    Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )

                ;   $LV_DISPINFO *lpdi = ($LV_DISPINFO *)$lParam;

                    If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                        Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" )
                        Local $Subindex = DllStructGetData($tNMLVDISPINFO,"SubItem")
                            Local $sItem =  StringSplit($RowsArray[$iIndex+2],$TabSep)[$Subindex+1] ;  $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")]
                            DllStructSetData( $tText, 1, $sItem )
                            DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
                            DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )

                    EndIf


                Case $NM_CUSTOMDRAW

                    Local $tNMLVCustomDraw = DllStructCreate( $tagNMLVCUSTOMDRAW, $lParam )
                    Local $dwDrawStage = DllStructGetData( $tNMLVCustomDraw, "dwDrawStage" )
                    Switch $dwDrawStage                                ; Specifies the drawing stage
                        ; Stage 1
                        Case $CDDS_PREPAINT                              ; Before the paint cycle begins
                            Return $CDRF_NOTIFYITEMDRAW                    ; Stage 2 will be carried out

                        ; Stage 2
                        Case $CDDS_ITEMPREPAINT                          ; Before an item is painted
                            If Not _GUICtrlListView_GetItemState( $hListView, DllStructGetData( $tNMLVCUSTOMDRAW, "dwItemSpec" ), $LVIS_FOCUSED ) Then Return $CDRF_NEWFONT ; Default drawing of item
                            $iItem = DllStructGetData( $tNMLVCUSTOMDRAW, "dwItemSpec" )
                            Return $CDRF_NOTIFYSUBITEMDRAW                 ; Stage 3 will be carried out

                        ; Stage 3
                        Case BitOR( $CDDS_ITEMPREPAINT, $CDDS_SUBITEM )  ; Before a subitem is painted

                            Return $CDRF_NOTIFYPOSTPAINT                   ; Stage 4 will be carried out


                        ; Stage 4
                        Case BitOR( $CDDS_ITEMPOSTPAINT, $CDDS_SUBITEM ) ; After a subitem has been painted

                            If DllStructGetData( $tNMLVCustomDraw, "iSubItem" ) = $iSubItem Then
                                Local $hDC = DllStructGetData( $tNMLVCUSTOMDRAW, "hdc" )                   ; Device context
                                $aRect = _GUICtrlListView_GetSubItemRect( $hListView, $iItem, $iSubItem )  ; Subitem rectangle
                                $aRect[2] = $aRect[0] + _GUICtrlListView_GetColumnWidth( $hListView, $iSubItem )

                                DllStructSetData( $tRect, "Left",   $aRect[0]+4 )
                                DllStructSetData( $tRect, "Top",    $aRect[1] )
                                DllStructSetData( $tRect, "Right",  $aRect[2] )
                                DllStructSetData( $tRect, "Bottom", $aRect[3] )
                                _WinAPI_FillRect( $hDC, $tRect, $hBrush )                                  ; Fill subitem background
                                _WinAPI_SetTextColor( $hDC, 0x000000 )                                     ; Set black text color

                                DllStructSetData( $tRect, "Left", DllStructGetData( $tRect, "Left" ) + 2 ) ; Adjust rectangle
                                If $bNotXP Then DllStructSetData( $tRect, "Top", DllStructGetData( $tRect, "Top" ) + 2 )

                                _WinAPI_DrawText( $hDC, StringSplit($RowsArray[$iItem+2],$TabSep)[$iSubItem+1], $tRect, $DT_WORD_ELLIPSIS ) ; Draw text
                            EndIf
                            Return $CDRF_NEWFONT                           ; $CDRF_NEWFONT must be returned after changing font or colors
                    EndSwitch
                Case $NM_CLICK
                    If Not $bComboDoNotOpen And Not $bComboOpenOnDoubleClick And $iItem > -1 And $iSubItem > -1 Then _
                        GUICtrlSendToDummy( $idComboOpen ) ; Send message to open ComboBox
                Case $NM_DBLCLK
                    If $bComboOpenOnDoubleClick And $iItem > -1 And $iSubItem > -1 Then _
                        GUICtrlSendToDummy( $idComboOpen ) ; Send message to open ComboBox
            EndSwitch
    EndSwitch
    Return $GUI_RUNDEFMSG
EndFunc

; Handle ListView messages related to ComboBox
Func ListViewCallback( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData )
    Switch $iMsg



        ; Open ComboBox on Enter key
        Case $WM_GETDLGCODE
            Switch $wParam
                Case $VK_RETURN
                    If Not $bComboDoNotOpen And $bComboOpenOnEnter And $iItem > -1 And $iSubItem > -1 Then
                        _GUICtrlListView_SetItemSelected( $hListView, -1, False )
                        _GUICtrlListView_SetItemSelected( $hListView, $iItem )
                        GUICtrlSendToDummy( $idComboOpen )
                    EndIf
            EndSwitch

        ; Manage multiple selections
        ; Prevent ComboBox from opening
        ; Manage keyboard events
        Case $WM_KEYDOWN
            Switch $wParam
                Case $VK_SHIFT, $VK_CONTROL
                    $bComboDoNotOpen = True
                ; Manage keyboard events
                Case $VK_LEFT
                    If Not $bComboDoNotOpen And $iSubItem > 0 Then $iSubItem -= 1
                    _GUICtrlListView_RedrawItems( $hListView, $iItem, $iItem )
                Case $VK_RIGHT
                    If Not $bComboDoNotOpen And $iSubItem < _GUICtrlListView_GetColumnCount( $hListView ) - 1 Then $iSubItem += 1
                    _GUICtrlListView_RedrawItems( $hListView, $iItem, $iItem )
                Case $VK_SPACE
                    If Not $bComboDoNotOpen And $bComboOpenOnSpace And $iItem > -1 And $iSubItem > -1 Then
                        _GUICtrlListView_SetItemSelected( $hListView, -1, False )
                        GUICtrlSendToDummy( $idComboOpen )
                    EndIf
            EndSwitch
        Case $WM_KEYUP
            Switch $wParam
                Case $VK_SHIFT, $VK_CONTROL
                    $bComboDoNotOpen = False
            EndSwitch

        ; Left click in ListView
        ; Sent on single and double click
        ; Determines item/subitem of the cell that's clicked
        Case $WM_LBUTTONDOWN
            Local $aHit = _GUICtrlListView_SubItemHitTest( $hListView )
            If $bComboOpen Then
                ; If another ComboBox is open then delete it
                _GUICtrlEDIT_Destroy( $hEdit )
                GUICtrlSendToDummy( $idComboClose )
                _WinAPI_SetFocus( $hListView )
            EndIf
            If $aHit[0] > -1 And $aHit[1] > -1 Then
                $iItem = $aHit[0]
                $iSubItem = $aHit[1]
                _GUICtrlListView_RedrawItems( $hListView, $iItem, $iItem )
            EndIf

        ; Delete ComboBox on right click in ListView and on left
        ; or right click in non-client ListView area (Scrollbars).
        Case $WM_RBUTTONDOWN, $WM_NCLBUTTONDOWN, $WM_NCRBUTTONDOWN
            _GUICtrlEDIT_Destroy( $hEdit )
            GUICtrlSendToDummy( $idComboClose )
            _WinAPI_SetFocus( $hListView )
    EndSwitch

    ; Call next function in subclass chain
    Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0]
    #forceref $iSubclassId, $pData
EndFunc

; Messages from the Edit control of the ComboBox
Func EditCallback( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData )
    Switch $iMsg
        ; Dialog codes
        Case $WM_GETDLGCODE
            Switch $wParam
                Case $VK_TAB    ; Close

                    GUICtrlSendToDummy( $idComboClose, True )
                Case $VK_RETURN  ; Accept and close
                    GUICtrlSendToDummy( $idComboClose, True )
                Case $VK_ESCAPE ; Close
                GUICtrlSendToDummy( $idComboClose, True )

                    _WinAPI_SetFocus( $hListView )
                    $bEditEscape = True

            EndSwitch

        ; Double click in Edit part of ComboBox
        Case $WM_LBUTTONDBLCLK
            Local $aPos = MouseGetWindowPos( $hListView )
            If $aPos[0] > $aRect[0] And $aPos[0] < $aRect[2] And $aPos[1] > $aRect[1] And $aPos[1] < $aRect[1] + 20 Then _
                GUICtrlSendToDummy( $idComboClose, True ) ; Accept and close
    EndSwitch

    ; Call next function in subclass chain
    Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0]
    #forceref $iSubclassId, $pData
EndFunc


; Handle GUI messages related to ComboBox control
Func GuiCallback( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData )
    Switch $iMsg
        ; Delete ComboBox on left or right mouse click in non-client GUI area and on GUI deactivate
        Case $WM_NCLBUTTONDOWN, $WM_NCRBUTTONDOWN, $WM_ACTIVATE
        _GUICtrlEDIT_Destroy( $hEdit )
            GUICtrlSendToDummy( $idComboClose )
    EndSwitch

    ; Call next function in subclass chain
    Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0]
    #forceref $iSubclassId, $pData
EndFunc

; Get mouse pos relative to window
Func MouseGetWindowPos( $hWindow )
    Local $aPos = MouseGetPos()
    Local $tPoint = DllStructCreate( "int X;int Y" )
    DllStructSetData( $tPoint, "X", $aPos[0] )
    DllStructSetData( $tPoint, "Y", $aPos[1] )
    _WinAPI_ScreenToClient( $hWindow, $tPoint )
    $aPos[0] = DllStructGetData( $tPoint, "X" )
    $aPos[1] = DllStructGetData( $tPoint, "Y" )
    Return $aPos
EndFunc

 

 

 

 

OnefolderWorking.zip

Edited March 24, 2017 by Adrian123
Forgot

[LarsJ]

When I run your script I get an empty listview. And .schema returns an error:

sqlite> .schema
Error: file is encrypted or is not a database

For a large database you should use the example in "Data stored in databases" in the thread about virtual listviews.

[Adrian123]

20 hours ago, LarsJ said:

When I run your script I get an empty listview. And .schema returns an error:

sqlite> .schema
Error: file is encrypted or is not a database

For a large database you should use the example in "Data stored in databases" in the thread about virtual listviews.

Sorry I put everything in one zip should work now

OnefolderWorking.zip

[jchd]

I only have pretty little time to dig into this but here are what strikes me.

• You shouldn't open / close the DB at every SQL statement. Open it at program start and close it at program termination.
• Test and handle SQLite error codes, not only SQLITE_OK.
• Use a large SQLite cache. It won't make a diference first time but subsequent requests will run much faster.
• You don't have to convert AutoIt (UTF16-LE) strings back and forth to/from UTF8. Use UTF16 SQLite primitives instead.
• I understand the sample DB is only a toy but its schema lives me with questions about replicated columns.
• You select * which is very bad. If ever your DB design changes for some good reasons someday, changing the order of columns, you'll have to change many things in the code. Select named columns and only those which are useful for the task at hand.
• Your app design seems to load the whole DB in a huge listview, say with 180K rows, and let the user painfully navigate in this ocean. It's way more efficient and user-friendly to let the user select the criterions he needs for what he wants to view/change and, from that, build a query to select and load, in a manageable listview, only what he actually needs.
• Create indices for speeding up the various specific queries mentionned above.
• I have only little insight about the actual goals, but if you're dealing with automotive parts, you might find it useful and efficient to create columns (and indices) for manufacturer (car brand), item familly and create separate tables with foreign keys from there to ease queries. Generic items (light bulbs, tyres, accessories, ...) can populate a "Generic" brand.

[Adrian123]

Thank You

My goal is to make the fastest way to use sqlite and listview because I have an application in 

C++ combined with various exes built with Autoit,freePascal,exel VBA etc ,and I was thinking to gather everything in one application built with Autoit and some minimal dlls (data in DB is only for testing to see which method is the best, I will never have more than 1000 rows/30 colums but I need to make sure I don't have memory leaking since user don't close the application 😊 bad users)

I would like Autoit since the 'IDE' is small and portable and I also use Sap scripting from Autoit.(quite powerful)

I will take in consideration your advice.

 

Edited March 25, 2017 by Adrian123

[LarsJ]

There are no memory leaks in the code in OnefolderWorking.zip. When you start scrolling a virtual listview it'll allocate working storage. It'll continue allocating memory until a sufficient large number of new listview cells has been displayed on the screen. In this example it can probably allocate up to around 1 MB of additional memory. If you don't scroll much you can see this memory allocation for a long time. When it has sufficient working storage it'll stop allocating more memory. This is completely normal behaviour of a virtual listview.

The delay in scrolling is caused by the fact that it's a virtual listview. The listview is updated through code in Windows DLL-files. This is very very fast code. Because it's a virtual listview the code is constantly sending messages ($LVN_GETDISPINFOW notifications contained in WM_NOTIFY messages) to your AutoIt code to get data for the next listview cell. The AutoIt code is slow compared to code in DLL-files. This makes the entire update process slow.

When you scroll horizontally the repositioning of the header is done after the listview cells has been updated. If 45 rows are visible on the screen 45 * 30 cells has to be updated (note that non-visible columns on left or right side are updated too). This takes time. And this is the reason for the delay in the header update.

When you are using a virtual listview you are executing much more AutoIt code for updating the listview than in a normal listview. This costs on performance. You should only use a virtual listview if you need to. That means if you have more than 100.000 cells. Or if you have other special reasons.

If you need only 1000 rows and 30 columns = 30,000 cells you should definitely use a normal listvew. Then the scrolling will be much smoother because the update is done entirely through the very fast code in the DLL-files. See "Using standard listviews" in the example about Virtual ListViews for code to fill out a normal listview in a fast way.

[Adrian123]

Thank You for your feedback, 

I will make a code with  normal listview to see its behavior.

I guess there is not a good reason fro extreme speed in my case.