;-- TIME_STAMP 2019-09-19 16:08:29 #include "AutoitObject.au3" ; https://www.autoitscript.com/forum/topic/110379-autoitobject-udf/ #include #include Opt('MustDeclareVars', 1) _AutoItObject_StartUp() _SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, 1) ; Dll im angegebenen Pfad - hier im Skriptordner OnAutoItExitRegister ("_OnExit_objFilter") Global $g_hSQL Global $g_oFilter ; DON'T USE ANY OTHER VARIABLE for the filtering object! It's required in exit function. ; assign your database and table Global $dbPLZ = "C:\CODE\AutoIt\FIRMA\PLZ\plz_brd.db" Global $sTable = 'plz' ; create the global filter object Global $g_oFilter = _objFilter_Create($dbPLZ, $sTable) ; create a new instance for filtering Global $oPLZ = $g_oFilter.new() ; filter for one condition in each process ; the next filter process will uses only the results from previous filtering ; filter operators: == = != <> > < >= <= !< !> LIKE IN Global $aFilter ; you can filter one by one and show results between as array: ;~ $oPLZ.filter('ort', 'in', 'Halle, Halle (Saale)') $aFilter = $oPLZ.filter('ort', 'in', 'Halle, Halle (Saale)').result If IsArray($aFilter) Then _ArrayDisplay($aFilter) $aFilter = $oPLZ.filter('plz', 'like', '0611%').result If IsArray($aFilter) Then _ArrayDisplay($aFilter) ; ...or combine the calls ;~ $oPLZ.filter('ort', 'in', 'Halle, Halle (Saale)').filter('plz', 'like', '0611%') ; get the result as array ;~ $aFilter = $oPLZ.result ;~ If IsArray($aFilter) Then _ArrayDisplay($aFilter) ;============================== functions ========================================================== ; Opens the database and references to the named table ($g_hSQL) ; Creates temporary tables with the same structure to storing/switching results ($g_oFilter.tmptable1 /.tmptable2) ; The return must store in "$g_oFilter" for deleting the temporary tables when the script ends Func _objFilter_Create($_dbToFilter, $_sTable) Local $self = _AutoItObject_Create() _AutoItObject_AddProperty($self, 'sqlcreate', $ELSCOPE_PUBLIC) ;-- the table create string _AutoItObject_AddProperty($self, 'table', $ELSCOPE_PUBLIC) ;-- name of the referenced table _AutoItObject_AddProperty($self, 'fields', $ELSCOPE_PUBLIC) ;-- dictionary for storing the tables fields names _AutoItObject_AddProperty($self, 'tmptable1', $ELSCOPE_PUBLIC) ;-- temporary table for storing the filtered records _AutoItObject_AddProperty($self, 'tmptable2', $ELSCOPE_PUBLIC) ;-- temporary table for switching the filtered records _AutoItObject_AddMethod($self, 'new', '_objFilter_New') _AutoItObject_AddMethod($self, 'filter', '_objFilter_Filter') _AutoItObject_AddMethod($self, 'result', '_objFilter_Result') $self.table = $_sTable Local $aResult, $iRows, $iColumns ; ask for sql create string Local $sSQL = StringFormat("SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)" & _ "WHERE tbl_name LIKE '%s' AND type!='meta' ORDER BY type DESC, name;", $_sTable) $g_hSQL = _SQLite_Open($_dbToFilter) _SQLite_GetTable($g_hSQL, $sSQL, $aResult, $iRows, $iColumns) $self.sqlcreate = $aResult[$aResult[0]] ; get the field names and store them Local $aFields = StringRegExp($self.sqlcreate, '\[([^\]]+)\] ', 3) $self.fields = ObjCreate('Scripting.Dictionary') For $i = 0 To UBound($aFields) -1 $self.fields.add($aFields[$i], $i) Next ; create temporary tables with unique name $self.tmptable1 = __GUID() $self.tmptable2 = __GUID() Local $sCreateTmp = StringReplace($self.sqlcreate, 'CREATE TABLE ' & $_sTable, 'CREATE TABLE ' & $self.tmptable1) _SQLite_Exec($g_hSQL, $sCreateTmp) $sCreateTmp = StringReplace($self.sqlcreate, 'CREATE TABLE ' & $_sTable, 'CREATE TABLE ' & $self.tmptable2) _SQLite_Exec($g_hSQL, $sCreateTmp) Return $self EndFunc Func _OnExit_objFilter() If $g_hSQL > 0 Then _SQLite_Exec($g_hSQL, "DROP TABLE " & $g_oFilter.tmptable1) _SQLite_Exec($g_hSQL, "DROP TABLE " & $g_oFilter.tmptable2) _SQLite_Close($g_hSQL) EndIf _SQLite_Shutdown() _AutoItObject_Shutdown() Exit EndFunc ;==>_OnExit_objAU3 ;=================================================================================================== ;============================== methodes =========================================================== Func _objFilter_New($self, $_obj=Null) If IsObj($_obj) Then Return _AutoItObject_Create($_obj) Else Return _AutoItObject_Create($self) EndIf EndFunc Func _objFilter_Filter($self, $_CompareField, $_CompareOp, $_CompareValue) ; check the field name If Not $self.fields.exists($_CompareField) Then Return $self ; check for right operator Local $sListOp = " == = != <> > < >= <= !< !> LIKE IN " If Not StringInStr($sListOp, ' ' & $_CompareOp & ' ') Then Return $self ; from which table get results - on first call: "$self.table", otherwise "$self.tmptable1" Local $sAskTable = __TableCountRows($self.tmptable1) = 0 ? $self.table : $self.tmptable1 ; temporary the result will stored in "$self.tmptable2", so it need to be empty __TableEmpty($self.tmptable2) ; sql-query, store result in temp table2 Local $sSQL If $_CompareOp = 'IN' Then Local $sIN, $aTmp = StringSplit($_CompareValue, ',') For $i = 1 To $aTmp[0] $sIN &= '"' & StringStripWS($aTmp[$i], 3) & '"' & ',' Next $sSQL = StringFormat("INSERT INTO %s SELECT * FROM %s WHERE %s IN (%s);", $self.tmptable2, $sAskTable, $_CompareField, StringTrimRight($sIN, 1)) Else $sSQL = StringFormat("INSERT INTO %s SELECT * FROM %s WHERE %s %s '%s';", $self.tmptable2, $sAskTable, $_CompareField, $_CompareOp, $_CompareValue) EndIf _SQLite_Exec($g_hSQL, $sSQL) ; for further use: empty .tmptable1 and store there the result __TableEmpty($self.tmptable1) __TableCopyAll($self.tmptable2, $self.tmptable1) Return $self EndFunc Func _objFilter_Result($self) Local $aResult, $iRows, $iColumns Local $sSQL = StringFormat("SELECT * FROM %s;", $self.tmptable1) _SQLite_GetTable2d($g_hSQL, $sSQL, $aResult, $iRows, $iColumns) Return $aResult EndFunc ;=================================================================================================== ;============================== internal functions ================================================= Func __GUID() Local $oTypeLib = ObjCreate("Scriptlet.TypeLib") Local $GUID = 'tmp' & StringReplace(StringReplace(StringReplace($oTypeLib.GUID, '}', ''), '{', ''), '-', '_') Return $GUID EndFunc Func __TableCountRows($_sTable) Local $aResult, $iRows, $iColumns Local $sSQL = StringFormat("SELECT COUNT(*) FROM %s", $_sTable) _SQLite_GetTable($g_hSQL, $sSQL, $aResult, $iRows, $iColumns) Return $aResult[$aResult[0]] EndFunc Func __TableCopyAll($_sTableFrom, $_sTableTo) Local $sSQL = StringFormat("INSERT INTO %s SELECT * FROM %s;", $_sTableTo, $_sTableFrom) _SQLite_Exec($g_hSQL, $sSQL) EndFunc Func __TableEmpty($_sTable) Local $sSQL = StringFormat("DELETE FROM %s;", $_sTable) _SQLite_Exec($g_hSQL, $sSQL) EndFunc ;===================================================================================================