Jump to content
Adrian123

virtual ListView sqlite

Recommended Posts

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.

 

#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 by Adrian123
Forgot

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.
  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by Adrian123

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.   

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • ScriptJunky
      By ScriptJunky
      I noticed a lack of a constants file for _WinAPI_GetSystemMetrics() so I made this for anyone who wants to add it to their library. Enjoy!  (file attached below)
      #include-once ; #INDEX# ======================================================================================================================= ; Title .........: WinAPI GetSystemMetrics Constants ; AutoIt Version : 3.3.14.5 ; Language ......: English ; Description ...: Constants for _WinAPI_GetSystemMetrics(). ; Author(s) .....: ScriptJunky ; =============================================================================================================================== ; #CONSTANTS# =================================================================================================================== ; _WinAPI_GetSystemMetrics() Global Const $ARRANGE = 56 Global Const $CLEANBOOT = 67 Global Const $CMONITORS = 80 Global Const $CMOUSEBUTTONS = 43 Global Const $CONVERTIBLESLATEMODE = 0x2003 Global Const $CXBORDER = 5 Global Const $CXCURSOR = 13 Global Const $CXDLGFRAME = 7 Global Const $CXDOUBLECLK = 36 Global Const $CXDRAG = 68 Global Const $CXEDGE = 45 Global Const $CXFIXEDFRAME = 7 Global Const $CXFOCUSBORDER = 83 Global Const $CXFRAME = 32 Global Const $CXFULLSCREEN = 16 Global Const $CXHSCROLL = 21 Global Const $CXHTHUMB = 10 Global Const $CXICON = 11 Global Const $CXICONSPACING = 38 Global Const $CXMAXIMIZED = 61 Global Const $CXMAXTRACK = 59 Global Const $CXMENUCHECK = 71 Global Const $CXMENUSIZE = 54 Global Const $CXMIN = 28 Global Const $CXMINIMIZED = 57 Global Const $CXMINSPACING = 47 Global Const $CXMINTRACK = 34 Global Const $CXPADDEDBORDER = 92 Global Const $CXSCREEN = 0 Global Const $CXSIZE = 30 Global Const $CXSIZEFRAME = 32 Global Const $CXSMICON = 49 Global Const $CXSMSIZE = 52 Global Const $CXVIRTUALSCREEN = 78 Global Const $CXVSCROLL = 2 Global Const $CYBORDER = 6 Global Const $CYCAPTION = 4 Global Const $CYCURSOR = 14 Global Const $CYDLGFRAME = 8 Global Const $CYDOUBLECLK = 37 Global Const $CYDRAG = 69 Global Const $CYEDGE = 46 Global Const $CYFIXEDFRAME = 8 Global Const $CYFOCUSBORDER = 84 Global Const $CYFRAME = 33 Global Const $CYFULLSCREEN = 17 Global Const $CYHSCROLL = 3 Global Const $CYICON = 12 Global Const $CYICONSPACING = 39 Global Const $CYKANJIWINDOW = 18 Global Const $CYMAXIMIZED = 62 Global Const $CYMAXTRACK = 60 Global Const $CYMENU = 15 Global Const $CYMENUCHECK = 72 Global Const $CYMENUSIZE = 55 Global Const $CYMIN = 29 Global Const $CYMINIMIZED = 58 Global Const $CYMINSPACING = 48 Global Const $CYMINTRACK = 35 Global Const $CYSCREEN = 1 Global Const $CYSIZE = 31 Global Const $CYSIZEFRAME = 33 Global Const $CYSMCAPTION = 51 Global Const $CYSMICON = 50 Global Const $CYSMSIZE = 53 Global Const $CYVIRTUALSCREEN = 79 Global Const $CYVSCROLL = 20 Global Const $CYVTHUMB = 9 Global Const $DBCSENABLED = 42 Global Const $DEBUG = 22 Global Const $DIGITIZER = 94 Global Const $IMMENABLED = 82 Global Const $MAXIMUMTOUCHES = 95 Global Const $MEDIACENTER = 87 Global Const $MENUDROPALIGNMENT = 40 Global Const $MIDEASTENABLED = 74 Global Const $MOUSEPRESENT = 19 Global Const $MOUSEHORIZONTALWHEELPRESENT = 91 Global Const $MOUSEWHEELPRESENT = 75 Global Const $NETWORK = 63 Global Const $PENWINDOWS = 41 Global Const $REMOTECONTROL = 0x2001 Global Const $REMOTESESSION = 0x1000 Global Const $SAMEDISPLAYFORMAT = 81 Global Const $SECURE = 44 Global Const $SERVERR = 289 Global Const $SHOWSOUNDS = 70 Global Const $SHUTTINGDOWN = 0x2000 Global Const $SLOWMACHINE = 73 Global Const $STARTER = 88 Global Const $SWAPBUTTON = 23 Global Const $TABLETPC = 86 Global Const $XVIRTUALSCREEN = 76 Global Const $YVIRTUALSCREEN = 77  
      WinAPISystemMetricsConstants.au3
    • chacoya121
      By chacoya121
      can someone plz explain how WinAPI work and some example script plz
    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Eminence
      By Eminence
      Hello,
      I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.
      This is my current code:
      Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.
       
      *EDIT
      The date format in the database is by MM/DD/YYYY HH:MM:SS.
    • Skysnake
      By Skysnake
      I am tracking this topic by @LarsJ.  It is very advanced and overkill for what I am currently trying to do.
       
      Problem is this.
      Listview contains columns, one of which is right aligned and gets populated by float values, such as 123.99.  Some do not have decimals ie 124.00 and on sort gets truncated to 124.  Its obviously still the same value, but the display has reset.
      ; line below is for list VIEW ;..................................0.........1......2............ $cListView = GUICtrlCreateListView("CUSTOMER|AMOUNT|DESCRIPTION", 8, 152, 764, 279) GUICtrlSetBkColor($cListView, $GUI_BKCOLOR_LV_ALTERNATE) ; alternate between the listview background color and the listview item background color GUICtrlSetBkColor($cListView, $LVStdClr) ; Set the background color for the listview _GUICtrlListView_SetColumnWidth($cListView, 0, 120) ; -- the client name _GUICtrlListView_SetColumnWidth($cListView, 1, 90) ;-- the amount _GUICtrlListView_JustifyColumn($cListView, 1, 1) ; 1 - Text is right aligned _GUICtrlListView_SetColumnWidth($cListView, 2, 200) ; the description What I am looking for is something native and simple like a 
          _GUICtrlListView_SetColumnFormat($cListView, 1, "%.2f") ;  1 - column is stringformatted to "%.2f"
      So that after each sort it will appear as it was in the original rendering.
      Is there something like this? I have not been able to find a simple solution.

      Thanks.
      Skysnake
×