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

    • VADemon
      By VADemon
      I've encountered a problem with a single file where I cannot retrieve it's Date-time. So far my code has worked well for over 30 files, but this one is a mystery I cannot debug myself due to insufficient Au3 knowledge.
      In line 11 "_Date_Time_FileTimeToArray" is called and for this particular file it sets the @error to 10. I don't know what that error code means, but it's not set by the _Date functions themselves I think.
      Overall, it could be a problem caused by any of the functions below, how can I properly debug this? / Does anybody know a what's causing this?
      _WinAPI_CreateFile() / _Date_Time_GetFileTime() / _Date_Time_FileTimeToArray()
      Func _SetFileTimes($sFilePath) Local $monthNumber[13] = ["", "January", "February", "March", "April", "May", "Juny", "July", "August", "September", "October", "November", "December"] Local $dayNumber[7] = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"] Local $fHandle = _WinAPI_CreateFile($sFilePath, 2, 2) ; read-only ; may NOT return a valid date for some reason! TODO Local $fTagFILETIME = _Date_Time_GetFileTime($fHandle) _WinAPI_CloseHandle($fHandle) ; This will return an empty array if theres no valid date $fModTime = _Date_Time_FileTimeToArray($fTagFILETIME[2]) ; last Modified if @error <> 10 then Local $year = $fModTime[2] Local $month = $fModTime[0] Local $day = $fModTime[1] Local $hour = $fModTime[3] Local $min = $fModTime[4] Local $sec = $fModTime[5] Local $ms = $fModTime[6] Local $weekday = $fModTime[7] Global $prettyTimestamp = StringFormat("%s, %s %d, %04d %02d:%02d:%02d", $dayNumber[$weekday], $monthNumber[$month], $day, $year, $hour, $min, $sec) Global $uploadDate = StringFormat("%04d-%02d-%02d", $year, $month, $day) $fModTime = _Date_Time_FileTimeToArray(_Date_Time_FileTimeToLocalFileTime($fTagFILETIME[2])) ; last Modified Local $year = $fModTime[2] Local $month = $fModTime[0] Local $day = $fModTime[1] Local $hour = $fModTime[3] Local $min = $fModTime[4] Local $sec = $fModTime[5] Local $ms = $fModTime[6] Local $weekday = $fModTime[7] ; GetUnixTime accounts for Local time, hence feed it local time Global $unixTimestamp = _GetUnixTime($year &"/"& $month &"/"& $day &" "& $hour&":"& $min &":"& $sec) else Global $prettyTimestamp = "N/A" Global $uploadDate = "" Global $unixTimestamp = "N/A" endif endfunc  
      _GetUnixTime returned the year 1601 start date, showing that $fModTime is probably equal 0. (But Why?)
      The file reports these dates in Explorer, it's on local NTFS drive:
      Created: ‎‎Wednesday, ‎31. ‎Januar ‎2018, ‏‎18:55:02
      Modified: ‎Wednesday, ‎10. ‎Januar ‎2018, ‏‎12:39:23
      Accessed: ‎Wednesday, ‎10. ‎Januar ‎2018, ‏‎12:39:23
    • astrionn
      By astrionn
      Hi Guys,
       
      I`m trying to record with my webcam in 5s intervalls and do some stuff between starting to record and stoping.
      I took code from here: https://www.autoitscript.com/forum/topic/27925-webcam-example/
      and took what looked relevant to me.
      I use 3 different files:
       Is called WMS.au3 and contains all the constants for the dll calls is called rec.au3 and does all necessary dll calls to start a webcam recording to a file Is called stopcam.exe and does the dll calls to stop recording into a file WMS.au3  :
      $WM_CAP_START = 0x400 $WM_CAP_UNICODE_START = $WM_CAP_START +100 $WM_CAP_PAL_SAVEA = $WM_CAP_START + 81 $WM_CAP_PAL_SAVEW = $WM_CAP_UNICODE_START + 81 $WM_CAP_UNICODE_END = $WM_CAP_PAL_SAVEW $WM_CAP_ABORT = $WM_CAP_START + 69 $WM_CAP_DLG_VIDEOCOMPRESSION = $WM_CAP_START + 46 $WM_CAP_DLG_VIDEODISPLAY = $WM_CAP_START + 43 $WM_CAP_DLG_VIDEOFORMAT = $WM_CAP_START + 41 $WM_CAP_DLG_VIDEOSOURCE = $WM_CAP_START + 42 $WM_CAP_DRIVER_CONNECT = $WM_CAP_START + 10 $WM_CAP_DRIVER_DISCONNECT = $WM_CAP_START + 11 $WM_CAP_DRIVER_GET_CAPS = $WM_CAP_START + 14 $WM_CAP_DRIVER_GET_NAMEA = $WM_CAP_START + 12 $WM_CAP_DRIVER_GET_NAMEW = $WM_CAP_UNICODE_START + 12 $WM_CAP_DRIVER_GET_VERSIONA = $WM_CAP_START + 13 $WM_CAP_DRIVER_GET_VERSIONW = $WM_CAP_UNICODE_START + 13 $WM_CAP_EDIT_COPY = $WM_CAP_START + 30 $WM_CAP_END = $WM_CAP_UNICODE_END $WM_CAP_FILE_ALLOCATE = $WM_CAP_START + 22 $WM_CAP_FILE_GET_CAPTURE_FILEA = $WM_CAP_START + 21 $WM_CAP_FILE_GET_CAPTURE_FILEW = $WM_CAP_UNICODE_START + 21 $WM_CAP_FILE_SAVEASA = $WM_CAP_START + 23 $WM_CAP_FILE_SAVEASW = $WM_CAP_UNICODE_START + 23 $WM_CAP_FILE_SAVEDIBA = $WM_CAP_START + 25 $WM_CAP_FILE_SAVEDIBW = $WM_CAP_UNICODE_START + 25 $WM_CAP_FILE_SET_CAPTURE_FILEA = $WM_CAP_START + 20 $WM_CAP_FILE_SET_CAPTURE_FILEW = $WM_CAP_UNICODE_START + 20 $WM_CAP_FILE_SET_INFOCHUNK = $WM_CAP_START + 24 $WM_CAP_GET_AUDIOFORMAT = $WM_CAP_START + 36 $WM_CAP_GET_CAPSTREAMPTR = $WM_CAP_START + 1 $WM_CAP_GET_MCI_DEVICEA = $WM_CAP_START + 67 $WM_CAP_GET_MCI_DEVICEW = $WM_CAP_UNICODE_START + 67 $WM_CAP_GET_SEQUENCE_SETUP = $WM_CAP_START + 65 $WM_CAP_GET_STATUS = $WM_CAP_START + 54 $WM_CAP_GET_USER_DATA = $WM_CAP_START + 8 $WM_CAP_GET_VIDEOFORMAT = $WM_CAP_START + 44 $WM_CAP_GRAB_FRAME = $WM_CAP_START + 60 $WM_CAP_GRAB_FRAME_NOSTOP = $WM_CAP_START + 61 $WM_CAP_PAL_AUTOCREATE = $WM_CAP_START + 83 $WM_CAP_PAL_MANUALCREATE = $WM_CAP_START + 84 $WM_CAP_PAL_OPENA = $WM_CAP_START + 80 $WM_CAP_PAL_OPENW = $WM_CAP_UNICODE_START + 80 $WM_CAP_PAL_PASTE = $WM_CAP_START + 82 $WM_CAP_SEQUENCE = $WM_CAP_START + 62 $WM_CAP_SEQUENCE_NOFILE = $WM_CAP_START + 63 $WM_CAP_SET_AUDIOFORMAT = $WM_CAP_START + 35 $WM_CAP_SET_CALLBACK_CAPCONTROL = $WM_CAP_START + 85 $WM_CAP_SET_CALLBACK_ERRORA = $WM_CAP_START + 2 $WM_CAP_SET_CALLBACK_ERRORW = $WM_CAP_UNICODE_START + 2 $WM_CAP_SET_CALLBACK_FRAME = $WM_CAP_START + 5 $WM_CAP_SET_CALLBACK_STATUSA = $WM_CAP_START + 3 $WM_CAP_SET_CALLBACK_STATUSW = $WM_CAP_UNICODE_START + 3 $WM_CAP_SET_CALLBACK_VIDEOSTREAM = $WM_CAP_START + 6 $WM_CAP_SET_CALLBACK_WAVESTREAM = $WM_CAP_START + 7 $WM_CAP_SET_CALLBACK_YIELD = $WM_CAP_START + 4 $WM_CAP_SET_MCI_DEVICEA = $WM_CAP_START + 66 $WM_CAP_SET_MCI_DEVICEW = $WM_CAP_UNICODE_START + 66 $WM_CAP_SET_OVERLAY = $WM_CAP_START + 51 $WM_CAP_SET_PREVIEW = $WM_CAP_START + 50 $WM_CAP_SET_PREVIEWRATE = $WM_CAP_START + 52 $WM_CAP_SET_SCALE = $WM_CAP_START + 53 $WM_CAP_SET_SCROLL = $WM_CAP_START + 55 $WM_CAP_SET_SEQUENCE_SETUP = $WM_CAP_START + 64 $WM_CAP_SET_USER_DATA = $WM_CAP_START + 9 $WM_CAP_SET_VIDEOFORMAT = $WM_CAP_START + 45 $WM_CAP_SINGLE_FRAME = $WM_CAP_START + 72 $WM_CAP_SINGLE_FRAME_CLOSE = $WM_CAP_START + 71 $WM_CAP_SINGLE_FRAME_OPEN = $WM_CAP_START + 70 $WM_CAP_STOP = $WM_CAP_START + 68 rec.au3  :
       
      #include <WMS.au3> #include <GUIConstants.au3> if not FileExists(@ScriptDir&"\vid\"&string(@MON)&"\"&string(@mday)) Then DirCreate(@ScriptDir&"\vid\"&"\"&string(@MON)&"\"&string(@mday)) EndIf $moviefile = @ScriptDir&"\vid"&"\"&string(@MON)&"\"&string(@mday)&"\test.avi" $avi = DllOpen("avicap32.dll") $user = DllOpen("user32.dll") FileWrite(@scriptdir&"\clop.clop",$user) $Main = GUICreate("Camera",350,270) $moviefile=@ScriptDir&"\test.avi" $cap = DllCall($avi, "int", "capCreateCaptureWindow", "str", "cap", "int", BitOR($WS_CHILD,$WS_VISIBLE), "int", 15, "int", 15, "int", 320, "int", 240, "hwnd", $Main, "int", 1) FileWrite(@scriptdir&"\clip.clip",$cap[0]) run(@scriptdir&"\stopcam.exe") DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_DRIVER_CONNECT, "int", 0, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_SET_SCALE, "int", 1, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_SET_OVERLAY, "int", 1, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_SET_PREVIEW, "int", 1, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_SET_PREVIEWRATE, "int", 1, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_FILE_SET_CAPTURE_FILEA, "int", 0, "str", $moviefile) DllCall($user, "int", "SendMessage", "hWnd", $cap[0], "int", $WM_CAP_SEQUENCE, "int", 0, "int", 0) Stopcam.exe is the compiled version of this:
      #include "WMS.au3" #include <GUIConstants.au3> $cap = Int(FileRead(@scriptdir&"\clip.clip")) $user = int(fileread(@scriptdir&"\clop.clop")) Sleep(10000) DllCall($user, "int", "SendMessage", "hWnd", $cap, "int", $WM_CAP_STOP, "int", 0, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap, "int", $WM_CAP_END, "int", 0, "int", 0) DllCall($user, "int", "SendMessage", "hWnd", $cap, "int", $WM_CAP_DRIVER_DISCONNECT, "int", 0, "int", 0) DllClose($user) FileDelete(@scriptdir&"\clip.clip") FileDelete(@scriptdir&"\clop.clop")  
      Unfortunately this is not working as I thought it would be working.
      Does someone see what I´m doing wrong?
      It should record the webcam for about 10 seconds ( a little less) and save that to an .avi file.
      It does not stop the recording , I have to stop the process manually...
      Secondly, which confuses me more is that it creates the test.avi not in the location is specified in the script... its stored in the ScriptDir for whatever reason haha
      I hope you can help me
       
      ~HappyCoding
    • badcoder123
      By badcoder123
      I was looking around the help file and I came across _GDIPlus_GraphicsDrawRect and _WinAPI_DrawLine and I seem to be having the same "problem" where every time it draws into a window and you lose or gain focus it has to redraw itself. Is there anyway around this? 
      #include <WindowsConstants.au3> #include <WinAPI.au3> #Include <GDIPlus.au3> _GDIPlus_Startup() Run("notepad.exe") $hWnd = WinWait("Untitled") $hGraphic = _GDIPlus_GraphicsCreateFromHWND($hWnd) $Color = 0xFFFF0000 $hPen = _GDIPlus_PenCreate($Color, 2) For $i = 1 To 10 _GDIPlus_GraphicsDrawRect($hGraphic, 200, 200, 25 ,25, $hPen) ToolTip($i) Sleep(1000) Next _WinAPI_RedrawWindow($hWnd, 0, 0, $RDW_INVALIDATE + $RDW_ALLCHILDREN) _GDIPlus_GraphicsDispose($hGraphic) _GDIPlus_PenDispose($hPen) _GDIPlus_Shutdown()  
    • Eminence
      By Eminence
      Hello,
      Using SQLite, I was trying to select a specific column with having duplicate entries removed by using the DISTINCT function. 
      Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) FROM data_db What the above snippet does is just list only one supervisor but with the total count of unique employee names in the whole database when it should be listing out all supervisor entries from the database and list the total count of employees per each supervisors. If I remove the COUNT function, it does list out all unique entries of supervisor names from the list. Attached is a screenshot of an example database as well. 
      Any help will be much appreciated. Thank you!

    • Seminko
      By Seminko
      Hey,
      I have a script that uses SQLite. It worked without a hiccup on my W7 system.
      However, last week I bought a new machine, installed W10 and autoit, and now I'm getting an error during _SQLite_Startup.
      Can anyone advise on how to find what's wrong? Tried checking $__g_hPrintCallback_SQLite but that returns nothing.
       
      Tried downloading the latest version of the dll from the link in the function page and I also replaced the default SQLite.dll.au3 that came with AutoIt with the one that came with the latest SQLite version zip.
       
      Relevant parts of my script:
      #include <SQLite.au3> #include <SQLite.dll.au3> _SQLite_Startup(@ScriptDir & "\Config\sqlite3_x64.dll", False, 1) If @error Then MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded! - " & $__g_hPrintCallback_SQLite & @CRLF & @CRLF & "Exiting application / Zavolej Honzovi") Exit -1 EndIf  
      Thanks
      S.
×