willichan Posted June 9, 2013 Posted June 9, 2013 (edited) At the office lately, I seem to have become the 'reports' guy. Any time some kind of report is needed, that compiles information from several disparate sources, I tend to be the one asked to do it. The biggest problem I run into is that there is not a consistent name for each person accross all systems. (A problem with multinational families of companies, I guess.) One source may use one logon account name, with one version of a real name. Another system may use just the real name, but the legal version. Yet another uses a different account name, and the person's nickname. Anyway, manually matching all of these names up each and every time is getting to be a problem. After the third report, taking an extra week just for this one step, I got tired of it, and wrote a Names Aggregator. It is fully functional, but need a lot of polishing. The names database is stored using SQLite. I will eventually make a version with file locking so that the database can be shared on a network, but that is for another time. ------------ HOW IT WORKS ------------ You call an aggregated lookup, passing a delimited string of names that your current source has in it for the current person. The aggregator will search the database for any matching names, then return the preferred name known for that person. This then becomes the common name for correlating data. Any names provided that are not currently known for that person are then added to the database to make finding that person easier in the future. If the person is entirely unknown, a new person record is created, and populated with the names you provided. It really is a niche tool, but can be a real time saver. For one major annual set of reports I do, this has reduced a three week long project down to about six hours. v2.0 - Now able to do lookups without adding names to the database. Added _NameAggregator_Realign() function for doing some database cleanup. The new function supports using a callback function for customizing the database cleanup or creating your own utility for manually maintaining the names database. expandcollapse popup#include-once #include <SQLite.au3> #include <SQLite.dll.au3> ; #INDEX# ======================================================================================================================= ; Title .........: NameAggregator ; AutoIt Version : 3.3.8.1 ; Description ...: Collects account names and real names from various sources, and attempts to link them so that one preferred ; name can be used for reporting purposes ; Author(s) .....: David Williams (willichan) ; Dll ...........: ; =============================================================================================================================== ; #CURRENT# ===================================================================================================================== ;_NameAggregator_Close ;_NameAggregator_Lookup ;_NameAggregator_Open ;_NameAggregator_Realign ; =============================================================================================================================== ; #INTERNAL_USE_ONLY# =========================================================================================================== ;__NameAggregator_FetchAllPersonNames ;__NameAggregator_RealignCallback ; =============================================================================================================================== ; #VARIABLES# =================================================================================================================== ; =============================================================================================================================== ; #CONSTANTS# =================================================================================================================== ; =============================================================================================================================== ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Close ; Description ...: Closes the names database and unloades SQLite DLL (if opened by _NameAggregator_Open) ; Syntax ........: _NameAggregator_Close(Byref $vAggregatorObject) ; Parameters ....: $vAggregatorObject - Returned by _NameAggregator_Open ; Return values .: Success - returns 0 ; Failure - returns 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAgregator_Open, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Close(ByRef $vAggregatorObject) Local $iErr = _SQLite_Close($vAggregatorObject[2]) If $iErr <> $SQLITE_OK Then Return 1 $vAggregatorObject = 0 Return 0 EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Lookup ; Description ...: Look up the given names in the known names database, adding any that does not currently exist ; Syntax ........: _NameAggregator_Lookup($sLookupName, Byref $vAggregatorObject[, $bCaseSensitive = False]) ; Parameters ....: $sLookupNames - A pipe (|) delimeted list of known names for a person to lookup ; $vAggregatorObject - Returned by _NameAggregator_Open ; $bIsCaseSensitive - Use case sensitive matching (not recommended, does not work well) ; Preferred name will still retain capitalization ; Default is False ; $bAddNewNames - Add unknown names to the database ; Default is True ; Return values .: Success - Returns the preferred name for this person. If the name is encountered for more than one ; person, the person with the lower record ID is returned. ; *NOTE* Also sets @error to 1 to indicate that the names database may be 'dirty'. This happens ; when a supplied name has been found for more than one person record. This flag is not ; persistent. When you get this @error result, it is recommended that you use ; _NameAggregator_Realign to fix it. ; Failure - returns "" ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: If a name is preceded by an exclamation point (!), then set it as the preferred name for this person. If a ; name in the list contains a pipe, exclamation point or carot (^), precede it with a carot. For example, a ; person's preferred name is John Smith, and has a login name of !jsmith, and another account j^smith, then ; $sLookupNames would be "!John Smith|^!jsmith|j^^smith". If he already had an entry in the names database, ; his current preferred name would be unflagged, and John Smith would be flagged as preferred. If no name is ; flagged, and no record exists for this person in the names database, the first name in the list will be ; assumed to be the preferred name when adding the person to the database. If no preferred name is flagged in ; $sLookupNames, but a matching name is found in the database, the preferred name from the database is ; returned, and is unchanged. Null names are not allowed. Space ( ) only names, double pipes (||) or ; exclamation pipe (!|) may cause unexpected results. ; Related .......: _NamesAgregator_Open, _NamesAggregator_Close ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Lookup($sLookupNames, ByRef $vAggregatorObject, $bIsCaseSensitive = Default, $bAddNewNames = Default) Local $bCaseSensitive = False Local $bAddEntries = True Local $sScanChar, $iRet, $hQuery, $aRow, $iLoop Local $sReturn = "" If StringStripWS($sLookupNames, 7) = "" Then Return "" Local $iScanpoint = 0 Local $asNames[1] = [0] Local $abAddNames[1] = [0] Local $iPreferred = 0 Local $vPerson = "none" If $bIsCaseSensitive <> Default Then $bCaseSensitive = $bIsCaseSensitive If $bAddNewNames <> Default Then $bAddEntries = $bAddNewNames ;parse $sLookupNames Do $iScanpoint += 1 $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) Switch $sScanChar Case "|" If $iScanpoint < StringLen($sLookupNames) And $iScanpoint > 1 Then ; ignore if first or last char If $asNames[$asNames[0]] <> "" Then ; no blank names $asNames[$asNames[0]] = StringStripWS($asNames[$asNames[0]], 7) ; clean up before starting new ;is it already in the list (duplicate)? If $asNames[0] > 1 Then For $iLoop = 1 to ($asNames[0] - 1) If $asNames[$asNames[0]] = $asNames[$iLoop] Then $asNames[$asNames[0]] = "" Next EndIf If $asNames[$asNames[0]] <> "" Then ;advance to next name $asNames[0] += 1 ReDim $asNames[$asNames[0] + 1] $asNames[$asNames[0]] = "" EndIf EndIf EndIf $sScanChar = "" Case "!" If $asNames[0] = 0 Then $iPreferred = 1 ; the first name has not been created yet, but will be Else $iPreferred = $asNames[0] EndIf $sScanChar = "" Case "^" $iScanpoint += 1 If $iScanpoint < StringLen($sLookupNames) Then $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) ; treat next char as a literal Else $sScanChar = "" ; end of the line. Nothing to do here. EndIf EndSwitch If $sScanChar <> "" Then ;add character to current name If $asNames[0] = 0 Then ;Is this the first name in the list ReDim $asNames[2] $asNames[0] = 1 $asNames[1] = "" EndIf $asNames[$asNames[0]] &= $sScanChar EndIf Until $iScanpoint >= StringLen($sLookupNames) If $asNames[0] > 0 And $asNames[$asNames[0]] = "" Then $asNames[0] -= 1 If $asNames[0] = 0 Then Return "" If $asNames[$iPreferred] = "" Then $iPreferred = 0 ; Eliminate blank preferred name ;check each name for matches ReDim $abAddNames[$asNames[0]+1] For $iLoop = 1 To $asNames[0] If $bCaseSensitive Then $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & ";", $hQuery) Else $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & " COLLATE NOCASE;", $hQuery) EndIf If $iRet = $SQLITE_OK Then If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then $vPerson = $aRow[0] $abAddNames[$iLoop] = False Else $abAddNames[$iLoop] = True EndIf Else $abAddNames[$iLoop] = True EndIf _SQLite_QueryFinalize($hQuery) Next ;Create new person if not found If ($vPerson = "none") And ($bAddEntries = True) Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO persons (preferred) values (" & _SQLite_FastEscape($asNames[1]) & ");") If $iRet <> $SQLITE_OK Then Return "" If $bCaseSensitive Then $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT id FROM persons WHERE preferred = " & _SQLite_FastEscape($asNames[1]) & ";", $hQuery) Else $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT id FROM persons WHERE preferred = " & _SQLite_FastEscape($asNames[1]) & " COLLATE NOCASE;", $hQuery) EndIf If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" $vPerson = $aRow[0] _SQLite_QueryFinalize($hQuery) EndIf ;Update preferred if provided If $iPreferred > 0 Then $iRet = _SQLite_Exec($vAggregatorObject[2], "UPDATE persons SET preferred = " & _SQLite_FastEscape($asNames[$iPreferred]) & " WHERE id = " & _SQLite_FastEscape($vPerson) & ";") If $iRet <> $SQLITE_OK Then Return "" EndIf ;Add names not found If $bAddEntries Then For $iLoop = 1 to $asNames[0] If $abAddNames[$iLoop] Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO names (person, name) VALUES (" & $vperson & ", " & _SQLite_FastEscape($asNames[$iLoop]) & ");") If $iRet <> $SQLITE_OK Then Return "" EndIf Next EndIf ;Return results If $iPreferred > 0 Then Return $asNames[$iPreferred] ; Preferred was supplied. Return it. ; Look up preferred. $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT preferred FROM persons WHERE id = " & _SQLite_FastEscape($vPerson) & ";", $hQuery) If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" _SQLite_QueryFinalize($hQuery) Return $aRow[0] EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Open ; Description ...: Loads the SQLite DLL and opens the names database ; Syntax ........: _NameAggregator_Open([$sDatabasePath = Default]) ; Parameters ....: $sDatabasePath - [optional] Path and file name for the names database to use. Defaults to ; @ScriptDir & "\names.db" ; Return values .: Success - returns a variant containing all info needed to access the database ; Failure - returns "" and sets @error to 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAggregator_Close, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Open($sDatabasePath = Default) Local $avReturn[3], $hDatabaseHandle, $sDLLpath If $sDatabasePath = Default Then $sDatabasePath = @ScriptDir & "\names.db" $sDLLpath = _SQLite_Startup() If @error Then SetError(1, 0, "") $hDatabaseHandle = _SQLite_Open($sDatabasePath) If @error Then _SQLite_Shutdown() SetError(1, 0, "") EndIf _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS persons( id INTEGER PRIMARY KEY, preferred TEXT);") _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS names( id INTEGER PRIMARY KEY, person INTEGER, name TEXT);") $avReturn[0] = $sDatabasePath $avReturn[1] = $sDLLpath $avReturn[2] = $hDatabaseHandle Return $avReturn EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Realign ; Description ...: Look for name records that should be linked together, and link them, cleaning extra records ; Syntax ........: _NameAggregator_Realign(Byref $vAggregatorObject, [, $sCallback = Default]) ; Parameters ....: $vAggregatorObject - Returned by _NameAggregator_Open ; $sCallback - Function to call when a match is found (see __NameAggregator_RealignCallback). If ; $sCallback is set to "", then _NameAggregator_Realign will run in 'fully automatic' ; mode, using the record with the lower ID as the master. ; Return values .: Success - returns number of records cleaned ; Failure - returns "" and sets @error to 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: Depending on the order in which names are submitted to the aggregator, is it possible for a name that is ; already associated with one person record to become associated with a second person record. From that point ; forward, that name will return results for the person record with the lower ID. This function has been added ; to facillitate repairing this problem. This can be handled using the internal 'fully automatic' mode, or ; using your own callback function to implement your own rules for correcting the records, or for creating an ; interactive script for human intervention. ; Related .......: _NamesAgregator_Open, _NamesAggregator_Close ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Realign(ByRef $vAggregatorObject, $sCallback = Default) Local $res, $hQuery1, $hQuery2, $aRow1, $aRow2, $count, $persons[2], $personIDs[2] Local $mastername = "", $isfirst, $i, $j, $t Local $vCallbackData[3] = [0, "", 0] If $sCallback = Default Then $sCallback = "" ;'fully automatic' mode #Region - look for person records with duplacate preferred names ;matching preferred names can be cleaned up in 'fully automatic' mode regardless of $sCallback $res = _SQLite_Query($vAggregatorObject[2], "SELECT preferred, COUNT(preferred) FROM persons GROUP BY preferred HAVING ( COUNT(preferred) > 1 );", $hQuery1) If $res = $SQLITE_OK Then While _SQLite_FetchData($hQuery1, $aRow1) = $SQLITE_OK $mastername = "!" & $aRow1[0] ;make sure this name stays the preferred name $res = _SQLite_Query($vAggregatorObject[2], "SELECT id FROM persons WHERE preferred=" & _SQLite_FastEscape($aRow1[0]) & ";", $hQuery2) If $res <> $SQLITE_OK Then ContinueLoop $isfirst = True While _SQLite_FetchData($hQuery2, $aRow2) = $SQLITE_OK ;get each person record with this name ;First record will be updated with these names $res = _NameAggregator_Lookup($mastername & "|" & __NameAggregator_FetchAllPersonNames($vAggregatorObject, $aRow2[0]), $vAggregatorObject) ;Delete record if not first one If $isfirst Then $isfirst = False Else __NameAggregator_DeletePerson($vAggregatorObject, $aRow2[0]) EndIf WEnd WEnd EndIf #EndRegion - look for person records with duplacate preferred names #Region - look for duplicate name records ;;;SELECT person, name, COUNT(name) FROM names GROUP BY name HAVING ( COUNT(name) > 1 ); $res = _SQLite_Query($vAggregatorObject[2], "SELECT name, COUNT(name) FROM names GROUP BY name HAVING ( COUNT(name) > 1 );", $hQuery1) If $res = $SQLITE_OK Then While _SQLite_FetchData($hQuery1, $aRow1) = $SQLITE_OK $count = 0 ReDim $persons[$aRow1[1]+1] $persons[0] = $aRow1[1] ReDim $personIDs[$aRow1[1]+1] $personIDs[0] = $aRow1[1] $res = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name="& _SQLite_FastEscape($aRow1[0]) & ";", $hQuery2) While _SQLite_FetchData($hQuery2, $aRow2) = $SQLITE_OK $count += 1 $persons[$count] = __NameAggregator_FetchAllPersonNames($vAggregatorObject, $aRow2[0]) $PersonIDs[$count] = $aRow2[0] WEnd If $sCallback <> "" Then ;prep for the callback function $vCallbackData[0] = 0 $vCallbackData[1] = $persons $vCallbackData[2] = 0 Call($sCallback, $vCallbackData) Switch $vCallbackData[0] Case 1 ;Do not combine. Apply changes in vCallbackData[1]. If vCallbackData[1][x] = "" then that person record will be deleted $persons = $vCallbackData[1] For $i = 1 to $persons[0] If $persons[$i] = "" Then ;Delete person __NameAggregator_DeletePerson($vAggregatorObject, $personids[$i]) Else ;remove associated names only __NameAggregator_DeletePerson($vAggregatorObject, $personids[$i], False) ;create new names records $t = StringSplit($persons[$i], "|") _SQLite_Exec($vAggregatorObject[2], "UPDATE names SET name=" & _SQLite_FastEscape(StringStripWS($t[1], 7)) & " WHERE id=" & $personids[$i] & ";") For $j = 1 to $t[0] _SQLite_Exec($vAggregatorObject[2], "INSERT INTO names (person, name) VALUES (" & $personIDs[$i],", " & _SQLite_FastEscape(StringStripWS($t[$j], 7)) & ");") Next EndIf Next Case 2 ;Combine using first name in $vCallbackData[1] record specified by $vCallbackData[2] $res = __NameAggregator_CombineNames($vCallbackData[1], $vCallbackData[2]) For $i = 1 to $count __NameAggregator_DeletePerson($vAggregatorObject, $PersonIDs[$i]) Next _NameAggregator_Lookup($res, $vAggregatorObject) Case 3 ;Automatically combine using priorities set in _NameAggregator_Realign() $res = __NameAggregator_CombineNames($persons, 1) For $i = 1 to $count __NameAggregator_DeletePerson($vAggregatorObject, $PersonIDs[$i]) Next _NameAggregator_Lookup($res, $vAggregatorObject) Case 4 ;Stop using Callback function, and run in 'fully automatic' mode $sCallback = "" ;Case 0 ;Do nothing. Ignore this matching. ;Case Else ;same as Case 0 EndSwitch EndIf If ($sCallback = "") Then ;handled last, in case the callback switched to 'fully automatic' mode $res = __NameAggregator_CombineNames($persons, 1) For $i = 1 to $count __NameAggregator_DeletePerson($vAggregatorObject, $PersonIDs[$i]) Next _NameAggregator_Lookup($res, $vAggregatorObject) EndIf WEnd EndIf #EndRegion - look for duplicate name records EndFunc ; #INTERNAL_USE_ONLY# =========================================================================================================== ; Name ..........: __NameAggregator_CombineNames ; Description ...: ; Syntax ........: __NameAggregator_CombineNames($persons, $selected) ; Parameters ....: $persons - Array of pipe delimited name lists. ; $selected - Array element to use as master. ; Return values .: Pipe delimited string with names for specified person ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func __NameAggregator_CombineNames($persons, $selected) Local $namelist, $split, $i, $j ;Get the preferred name from the 'master' list $split = StringSplit($persons[$selected], "|") If (stringleft($persons[$selected], 1) = "!") Or StringInStr($persons[$selected], "|!") Then For $i = 1 to $split[0] If StringLeft($split[$i], 1) = "!" Then $namelist = "|" & StringRight($split[$i], StringLen($split[$i]) - 1) & "|" ExitLoop EndIf Next Else $namelist = "|" & $split[1] & "|" EndIf ;Add the other names without duplicates For $i = 1 to $persons[0] $split = StringSplit($persons[$i], "|") For $j = 1 to $split[0] If StringLeft($split[$j], 1) = "!" Then $split[$j] = StringRight($split[$j], StringLen($split[$j]) - 1) If Not StringInStr($namelist, "|" & $split[$j] & "|") Then $namelist &= $split[$j] & "|" Next Next ;replace openning | with ! and trim off ending | Return "!" & StringMid($namelist, 2, StringLen($namelist) - 2) EndFunc ; #INTERNAL_USE_ONLY# =========================================================================================================== ; Name ..........: __NameAggregator_FetchAllPersonNames ; Description ...: Retrieve pipe delimited list of names belonging to person ; Syntax ........: __NameAggregator_FetchAllPersonNames($vAggregatorObject, $PersonID) ; Parameters ....: $vAggregatorObject - Returned by _NameAggregator_Open ; $PersonID - ID of person record to get names for ; Return values .: Pipe delimited string with names for specified person ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func __NameAggregator_FetchAllPersonNames($vAggregatorObject, $PersonID) Local $res, $hQuery, $aRow Local $names = "" $res = _SQLite_Query($vAggregatorObject[2], "SELECT name FROM names WHERE person='" & $PersonID & "';", $hQuery) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK If $names <> "" Then $names &= "|" $names &= $aRow[0] WEnd Return $names EndFunc ; #INTERNAL_USE_ONLY# =========================================================================================================== ; Name ..........: __NameAggregator_DeletePerson ; Description ...: Delete person record and all associated name records ; Syntax ........: __NameAggregator_DeletePerson($vAggregatorObject, $PersonID[, $delpersonrec = True]) ; Parameters ....: $vCallbackData - Variant (array) containing data passed to and from callback ; $PersonID - ID of person record to get names for ; $delpersonrec - Should the person record be deleted along with name records ; Return values .: none ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func __NameAggregator_DeletePerson($vAggregatorObject, $PersonID, $delpersonrec = True) _SQLite_Exec($vAggregatorObject[2], "DELETE FROM names WHERE person='" & $PersonID & "';") ;delete associated names If $delpersonrec Then _SQLite_Exec($vAggregatorObject[2], "DELETE FROM persons WHERE id='" & $PersonID & "';") ;delete person record EndFunc ; #INTERNAL_USE_ONLY# =========================================================================================================== ; Name ..........: __NameAggregator_RealignCallback ; Description ...: ; Syntax ........: __NameAggregator_RealignCallback(Byref $vCallbackData) ; Parameters ....: $vCallbackData - Variant (array) containing data for callback (both directions) ; $vCallbackData[0] = Action for _NameAggregator_Realign to take ; 0 = Do nothing. Ignore this matching. ; 1 = Do not combine. Apply changes in vCallbackData[1]. ; - if vCallbackData[x] = "" then that person record will be deleted. ; 2 = Combine using first name in $vCallbackData[1] record specified by ; $vCallbackData[2]. ; 3 = Automatically combine using priorities set in _NameAggregator_Realign(). ; 4 = Stop using Callback function, and run in 'fully automatic' mode. ; $vCallbackData[1] = Array of Pipe delimited strings with names for persons found ; - element 0 will contain the count of persons found ; $vCallbackData[2] = misc data ; Return values .: None ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NameAggregator_Realign ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func __NameAggregator_RealignCallback(ByRef $vCallbackData) ;You shouldn't ever really use this one. Just use the comments above for creating your own. $vCallbackData[0]=4 ;Just switch over to 'fully automatic' mode. EndFunc v1.3 - improvements thanks to jchd. expandcollapse popup#include-once #include <SQLite.au3> #include <SQLite.dll.au3> ; #INDEX# ======================================================================================================================= ; Title .........: NameAggregator ; AutoIt Version : 3.3.8.1 ; Description ...: Collects account names and real names from various sources, and attempts to link them so that one preferred ; name can be used for reporting purposes ; Author(s) .....: David Williams (willichan) ; Dll ...........: ; =============================================================================================================================== ; #CURRENT# ===================================================================================================================== ;_NameAggregator_Close ;_NameAggregator_Lookup ;_NameAggregator_Open ; =============================================================================================================================== ; #INTERNAL_USE_ONLY# =========================================================================================================== ; =============================================================================================================================== ; #VARIABLES# =================================================================================================================== ; =============================================================================================================================== ; #CONSTANTS# =================================================================================================================== ; =============================================================================================================================== ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Close ; Description ...: Closes the names database and unloades SQLite DLL (if opened by _NameAggregator_Open) ; Syntax ........: _NameAggregator_Close(Byref $vAggregatorObject) ; Parameters ....: $vAggregatorObject - Returned by _NameAggregator_Open ; Return values .: Success - returns 0 ; Failure - returns 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAgregator_Open, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Close(ByRef $vAggregatorObject) Local $iErr = _SQLite_Close($vAggregatorObject[2]) If $iErr <> $SQLITE_OK Then Return 1 $vAggregatorObject = 0 Return 0 EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Lookup ; Description ...: Look up the given names in the known names database, adding any that does not currently exist ; Syntax ........: _NameAggregator_Lookup($sLookupName, Byref $vAggregatorObject[, $bCaseSensitive = False]) ; Parameters ....: $sLookupNames - A pipe (|) delimeted list of known names for a person to lookup ; $vAggregatorObject - Returned by _NameAggregator_Open ; $bCaseSensitive - Use case sensitive matching (not recommended, does not work well) ; Preferred name will still retain capitalization ; Return values .: Success - returns the preferred name for this person ; Failure - returns "" ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: If a name is preceded by an exclamation point (!), then set it as the preferred name for this person. If a ; name in the list contains a pipe, exclamation point or carot (^), precede it with a carot. For example, a ; person's preferred name is John Smith, and has a login name of !jsmith, and another account j^smith, then ; $sLookupNames would be "!John Smith|^!jsmith|j^^smith". If he already had an entry in the names database, ; his current preferred name would be unflagged, and John Smith would be flagged as preferred. If no name is ; flagged, and no record exists for this person in the names database, the first name in the list will be ; assumed to be the preferred name when adding the person to the database. If no preferred name is flagged in ; $sLookupNames, but a matching name is found in the database, the preferred name from the database is ; returned, and is unchanged. Null names are not allowed. Space ( ) only names, double pipes (||) or ; exclamation pipe (!|) may cause unexpected results. ; Related .......: _NamesAgregator_Open, _NamesAggregator_Close ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Lookup($sLookupNames, ByRef $vAggregatorObject, $bCaseSensitive = False) Local $sScanChar, $iRet, $hQuery, $aRow, $iLoop Local $sReturn = "" If StringStripWS($sLookupNames, 7) = "" Then Return "" Local $iScanpoint = 0 Local $asNames[1] = [0] Local $abAddNames[1] = [0] Local $iPreferred = 0 Local $vPerson = "none" ;parse $sLookupNames Do $iScanpoint += 1 $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) Switch $sScanChar Case "|" If $iScanpoint < StringLen($sLookupNames) And $iScanpoint > 1 Then ; ignore if first or last char If $asNames[$asNames[0]] <> "" Then ; no blank names $asNames[$asNames[0]] = StringStripWS($asNames[$asNames[0]], 7) ; clean up before starting new ;is it already in the list (duplicate)? If $asNames[0] > 1 Then For $iLoop = 1 to ($asNames[0] - 1) If $asNames[$asNames[0]] = $asNames[$iLoop] Then $asNames[$asNames[0]] = "" Next EndIf If $asNames[$asNames[0]] <> "" Then ;advance to next name $asNames[0] += 1 ReDim $asNames[$asNames[0] + 1] $asNames[$asNames[0]] = "" EndIf EndIf EndIf $sScanChar = "" Case "!" If $asNames[0] = 0 Then $iPreferred = 1 ; the first name has not been created yet, but will be Else $iPreferred = $asNames[0] EndIf $sScanChar = "" Case "^" $iScanpoint += 1 If $iScanpoint < StringLen($sLookupNames) Then $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) ; treat next char as a literal Else $sScanChar = "" ; end of the line. Nothing to do here. EndIf EndSwitch If $sScanChar <> "" Then ;add character to current name If $asNames[0] = 0 Then ;Is this the first name in the list ReDim $asNames[2] $asNames[0] = 1 $asNames[1] = "" EndIf $asNames[$asNames[0]] &= $sScanChar EndIf Until $iScanpoint >= StringLen($sLookupNames) If $asNames[0] > 0 And $asNames[$asNames[0]] = "" Then $asNames[0] -= 1 If $asNames[0] = 0 Then Return "" If $asNames[$iPreferred] = "" Then $iPreferred = 0 ; Eliminate blank preferred name ;check each name for matches ReDim $abAddNames[$asNames[0]+1] For $iLoop = 1 To $asNames[0] If $bCaseSensitive Then $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & ";", $hQuery) Else $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & " COLLATE NOCASE;", $hQuery) EndIf If $iRet = $SQLITE_OK Then If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then $vPerson = $aRow[0] $abAddNames[$iLoop] = False Else $abAddNames[$iLoop] = True EndIf Else $abAddNames[$iLoop] = True EndIf _SQLite_QueryFinalize($hQuery) Next ;Create new person if not found If $vPerson = "none" Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO persons (preferred) values (" & _SQLite_FastEscape($asNames[1]) & ");") If $iRet <> $SQLITE_OK Then Return "" $vperson = _SQLite_LastInsertRowID($vAggregatorObject[2]) If $vperson = 0 Then Return "" EndIf ;Update preferred if provided If $iPreferred > 0 Then $iRet = _SQLite_Exec($vAggregatorObject[2], "UPDATE persons SET preferred = " & _SQLite_FastEscape($asNames[$iPreferred]) & " WHERE id = " & $vPerson & ";") If $iRet <> $SQLITE_OK Then Return "" EndIf ;Add names not found For $iLoop = 1 to $asNames[0] If $abAddNames[$iLoop] Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO names (person, name) VALUES (" & $vperson & ", " & _SQLite_FastEscape($asNames[$iLoop]) & ");") If $iRet <> $SQLITE_OK Then Return "" EndIf Next ;Return results If $iPreferred > 0 Then Return $asNames[$iPreferred] ; Preferred was supplied. Return it. ; Look up preferred. $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT preferred FROM persons WHERE id = " & $vPerson & ";", $hQuery) If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" _SQLite_QueryFinalize($hQuery) Return $aRow[0] EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Open ; Description ...: Loads the SQLite DLL and opens the names database ; Syntax ........: _NameAggregator_Open([$sDatabasePath = Default]) ; Parameters ....: $sDatabasePath - [optional] Path and file name for the names database to use. Defaults to ; @ScriptDir & "\names.db" ; Return values .: Success - returns a variant containing all info needed to access the database ; Failure - returns "" and sets @error to 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAggregator_Close, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Open($sDatabasePath = Default) Local $avReturn[3], $hDatabaseHandle, $sDLLpath If $sDatabasePath = Default Then $sDatabasePath = @ScriptDir & "\names.db" $sDLLpath = _SQLite_Startup() If @error Then SetError(1, 0, "") $hDatabaseHandle = _SQLite_Open($sDatabasePath) If @error Then _SQLite_Shutdown() SetError(1, 0, "") EndIf _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS persons( id INTEGER PRIMARY KEY, preferred TEXT);") _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS names( id INTEGER PRIMARY KEY, person INTEGER, name TEXT);") $avReturn[0] = $sDatabasePath $avReturn[1] = $sDLLpath $avReturn[2] = $hDatabaseHandle Return $avReturn EndFunc v1.2 - adds case insensitive matching expandcollapse popup#include-once #include <SQLite.au3> #include <SQLite.dll.au3> ; #INDEX# ======================================================================================================================= ; Title .........: NameAggregator ; AutoIt Version : 3.3.8.1 ; Description ...: Collects account names and real names from various sources, and attempts to link them so that one preferred ; name can be used for reporting purposes ; Author(s) .....: David Williams (willichan) ; Dll ...........: ; =============================================================================================================================== ; #CURRENT# ===================================================================================================================== ;_NameAggregator_Close ;_NameAggregator_Lookup ;_NameAggregator_Open ; =============================================================================================================================== ; #INTERNAL_USE_ONLY# =========================================================================================================== ; =============================================================================================================================== ; #VARIABLES# =================================================================================================================== ; =============================================================================================================================== ; #CONSTANTS# =================================================================================================================== ; =============================================================================================================================== ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Close ; Description ...: Closes the names database and unloades SQLite DLL (if opened by _NameAggregator_Open) ; Syntax ........: _NameAggregator_Close(Byref $vAggregatorObject) ; Parameters ....: $vAggregatorObject - Returned by _NameAggregator_Open ; Return values .: Success - returns 0 ; Failure - returns 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAgregator_Open, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Close(ByRef $vAggregatorObject) Local $iErr = _SQLite_Close($vAggregatorObject[2]) If $iErr <> $SQLITE_OK Then Return 1 $vAggregatorObject = 0 Return 0 EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Lookup ; Description ...: Look up the given names in the known names database, adding any that does not currently exist ; Syntax ........: _NameAggregator_Lookup($sLookupName, Byref $vAggregatorObject[, $bCaseSensitive = False]) ; Parameters ....: $sLookupNames - A pipe (|) delimeted list of known names for a person to lookup ; $vAggregatorObject - Returned by _NameAggregator_Open ; $bCaseSensitive - Use case sensitive matching (not recommended, does not work well) ; Preferred name will still retain capitalization ; Return values .: Success - returns the preferred name for this person ; Failure - returns "" ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: If a name is preceded by an exclamation point (!), then set it as the preferred name for this person. If a ; name in the list contains a pipe, exclamation point or carot (^), precede it with a carot. For example, a ; person's preferred name is John Smith, and has a login name of !jsmith, and another account j^smith, then ; $sLookupNames would be "!John Smith|^!jsmith|j^^smith". If he already had an entry in the names database, ; his current preferred name would be unflagged, and John Smith would be flagged as preferred. If no name is ; flagged, and no record exists for this person in the names database, the first name in the list will be ; assumed to be the preferred name when adding the person to the database. If no preferred name is flagged in ; $sLookupNames, but a matching name is found in the database, the preferred name from the database is ; returned, and is unchanged. Null names are not allowed. Space ( ) only names, double pipes (||) or ; exclamation pipe (!|) may cause unexpected results. ; Related .......: _NamesAgregator_Open, _NamesAggregator_Close ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Lookup($sLookupNames, ByRef $vAggregatorObject, $bCaseSensitive = False) Local $sScanChar, $iRet, $hQuery, $aRow, $iLoop Local $sReturn = "" If StringStripWS($sLookupNames, 7) = "" Then Return "" Local $iScanpoint = 0 Local $asNames[1] = [0] Local $abAddNames[1] = [0] Local $iPreferred = 0 Local $vPerson = "none" ;parse $sLookupNames Do $iScanpoint += 1 $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) Switch $sScanChar Case "|" If $iScanpoint < StringLen($sLookupNames) And $iScanpoint > 1 Then ; ignore if first or last char If $asNames[$asNames[0]] <> "" Then ; no blank names $asNames[$asNames[0]] = StringStripWS($asNames[$asNames[0]], 7) ; clean up before starting new ;is it already in the list (duplicate)? If $asNames[0] > 1 Then For $iLoop = 1 to ($asNames[0] - 1) If $asNames[$asNames[0]] = $asNames[$iLoop] Then $asNames[$asNames[0]] = "" Next EndIf If $asNames[$asNames[0]] <> "" Then ;advance to next name $asNames[0] += 1 ReDim $asNames[$asNames[0] + 1] $asNames[$asNames[0]] = "" EndIf EndIf EndIf $sScanChar = "" Case "!" If $asNames[0] = 0 Then $iPreferred = 1 ; the first name has not been created yet, but will be Else $iPreferred = $asNames[0] EndIf $sScanChar = "" Case "^" $iScanpoint += 1 If $iScanpoint < StringLen($sLookupNames) Then $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) ; treat next char as a literal Else $sScanChar = "" ; end of the line. Nothing to do here. EndIf EndSwitch If $sScanChar <> "" Then ;add character to current name If $asNames[0] = 0 Then ;Is this the first name in the list ReDim $asNames[2] $asNames[0] = 1 $asNames[1] = "" EndIf $asNames[$asNames[0]] &= $sScanChar EndIf Until $iScanpoint >= StringLen($sLookupNames) If $asNames[0] > 0 And $asNames[$asNames[0]] = "" Then $asNames[0] -= 1 If $asNames[0] = 0 Then Return "" If $asNames[$iPreferred] = "" Then $iPreferred = 0 ; Eliminate blank preferred name ;check each name for matches ReDim $abAddNames[$asNames[0]+1] For $iLoop = 1 To $asNames[0] If $bCaseSensitive Then $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & ";", $hQuery) Else $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & " COLLATE NOCASE;", $hQuery) EndIf If $iRet = $SQLITE_OK Then If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then $vPerson = $aRow[0] $abAddNames[$iLoop] = False Else $abAddNames[$iLoop] = True EndIf Else $abAddNames[$iLoop] = True EndIf _SQLite_QueryFinalize($hQuery) Next ;Create new person if not found If $vPerson = "none" Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO persons (preferred) values (" & _SQLite_FastEscape($asNames[1]) & ");") If $iRet <> $SQLITE_OK Then Return "" If $bCaseSensitive Then $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT id FROM persons WHERE preferred = " & _SQLite_FastEscape($asNames[1]) & ";", $hQuery) Else $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT id FROM persons WHERE preferred = " & _SQLite_FastEscape($asNames[1]) & " COLLATE NOCASE;", $hQuery) EndIf If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" $vPerson = $aRow[0] _SQLite_QueryFinalize($hQuery) EndIf ;Update preferred if provided If $iPreferred > 0 Then $iRet = _SQLite_Exec($vAggregatorObject[2], "UPDATE persons SET preferred = " & _SQLite_FastEscape($asNames[$iPreferred]) & " WHERE id = " & _SQLite_FastEscape($vPerson) & ";") If $iRet <> $SQLITE_OK Then Return "" EndIf ;Add names not found For $iLoop = 1 to $asNames[0] If $abAddNames[$iLoop] Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO names (person, name) VALUES (" & $vperson & ", " & _SQLite_FastEscape($asNames[$iLoop]) & ");") If $iRet <> $SQLITE_OK Then Return "" EndIf Next ;Return results If $iPreferred > 0 Then Return $asNames[$iPreferred] ; Preferred was supplied. Return it. ; Look up preferred. $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT preferred FROM persons WHERE id = " & _SQLite_FastEscape($vPerson) & ";", $hQuery) If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" _SQLite_QueryFinalize($hQuery) Return $aRow[0] EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Open ; Description ...: Loads the SQLite DLL and opens the names database ; Syntax ........: _NameAggregator_Open([$sDatabasePath = Default]) ; Parameters ....: $sDatabasePath - [optional] Path and file name for the names database to use. Defaults to ; @ScriptDir & "\names.db" ; Return values .: Success - returns a variant containing all info needed to access the database ; Failure - returns "" and sets @error to 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAggregator_Close, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Open($sDatabasePath = Default) Local $avReturn[3], $hDatabaseHandle, $sDLLpath If $sDatabasePath = Default Then $sDatabasePath = @ScriptDir & "\names.db" $sDLLpath = _SQLite_Startup() If @error Then SetError(1, 0, "") $hDatabaseHandle = _SQLite_Open($sDatabasePath) If @error Then _SQLite_Shutdown() SetError(1, 0, "") EndIf _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS persons( id INTEGER PRIMARY KEY, preferred TEXT);") _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS names( id INTEGER PRIMARY KEY, person INTEGER, name TEXT);") $avReturn[0] = $sDatabasePath $avReturn[1] = $sDLLpath $avReturn[2] = $hDatabaseHandle Return $avReturn EndFunc v1.1 - includes SQLite fix expandcollapse popup#include-once #include <SQLite.au3> #include <SQLite.dll.au3> ; #INDEX# ======================================================================================================================= ; Title .........: NameAggregator ; AutoIt Version : 3.3.8.1 ; Description ...: Collects account names and real names from various sources, and attempts to link them so that one preferred ; name can be used for reporting purposes ; Author(s) .....: David Williams (willichan) ; Dll ...........: ; =============================================================================================================================== ; #CURRENT# ===================================================================================================================== ;_NameAggregator_Close ;_NameAggregator_Lookup ;_NameAggregator_Open ; =============================================================================================================================== ; #INTERNAL_USE_ONLY# =========================================================================================================== ; =============================================================================================================================== ; #VARIABLES# =================================================================================================================== ; =============================================================================================================================== ; #CONSTANTS# =================================================================================================================== ; =============================================================================================================================== ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Close ; Description ...: Closes the names database and unloades SQLite DLL (if opened by _NameAggregator_Open) ; Syntax ........: _NameAggregator_Close(Byref $vAggregatorObject) ; Parameters ....: $vAggregatorObject - Returned by _NameAggregator_Open ; Return values .: Success - returns 0 ; Failure - returns 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAgregator_Open, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Close(ByRef $vAggregatorObject) Local $iErr = _SQLite_Close($vAggregatorObject[2]) If $iErr <> $SQLITE_OK Then Return 1 $vAggregatorObject = 0 Return 0 EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Lookup ; Description ...: Look up the given names in the known names database, adding any that does not currently exist ; Syntax ........: _NameAggregator_Lookup($sLookupName, Byref $vAggregatorObject) ; Parameters ....: $sLookupNames - A pipe (|) delimeted list of known names for a person to lookup ; $vAggregatorObject - Returned by _NameAggregator_Open ; Return values .: Success - returns the preferred name for this person ; Failure - returns "" ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: If a name is preceded by an exclamation point (!), then set it as the preferred name for this person. If a ; name in the list contains a pipe, exclamation point or carot (^), precede it with a carot. For example, a ; person's preferred name is John Smith, and has a login name of !jsmith, and another account j^smith, then ; $sLookupNames would be "!John Smith|^!jsmith|j^^smith". If he already had an entry in the names database, ; his current preferred name would be unflagged, and John Smith would be flagged as preferred. If no name is ; flagged, and no record exists for this person in the names database, the first name in the list will be ; assumed to be the preferred name when adding the person to the database. If no preferred name is flagged in ; $sLookupNames, but a matching name is found in the database, the preferred name from the database is ; returned, and is unchanged. Null names are not allowed. Space ( ) only names, double pipes (||) or ; exclamation pipe (!|) may cause unexpected results. ; Related .......: _NamesAgregator_Open, _NamesAggregator_Close ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Lookup($sLookupNames, ByRef $vAggregatorObject) Local $sScanChar, $iRet, $hQuery, $aRow, $iLoop Local $sReturn = "" If StringStripWS($sLookupNames, 7) = "" Then Return "" Local $iScanpoint = 0 Local $asNames[1] = [0] Local $abAddNames[1] = [0] Local $iPreferred = 0 Local $vPerson = "none" ;parse $sLookupNames Do $iScanpoint += 1 $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) Switch $sScanChar Case "|" If $iScanpoint < StringLen($sLookupNames) And $iScanpoint > 1 Then ; ignore if first or last char If $asNames[$asNames[0]] <> "" Then ; no blank names $asNames[$asNames[0]] = StringStripWS($asNames[$asNames[0]], 7) ; clean up before starting new $asNames[0] += 1 ReDim $asNames[$asNames[0] + 1] $asNames[$asNames[0]] = "" EndIf EndIf $sScanChar = "" Case "!" If $asNames[0] = 0 Then $iPreferred = 1 ; the first name has not been created yet, but will be Else $iPreferred = $asNames[0] EndIf $sScanChar = "" Case "^" $iScanpoint += 1 If $iScanpoint < StringLen($sLookupNames) Then $sScanChar = StringMid($sLookupNames, $iScanpoint, 1) ; treat next char as a literal Else $sScanChar = "" ; end of the line. Nothing to do here. EndIf EndSwitch If $sScanChar <> "" Then ;add character to current name If $asNames[0] = 0 Then ;Is this the first name in the list ReDim $asNames[2] $asNames[0] = 1 $asNames[1] = "" EndIf $asNames[$asNames[0]] &= $sScanChar EndIf Until $iScanpoint >= StringLen($sLookupNames) If $asNames[0] > 0 And $asNames[$asNames[0]] = "" Then $asNames[0] -= 1 If $asNames[0] = 0 Then Return "" If $asNames[$iPreferred] = "" Then $iPreferred = 0 ; Eliminate blank preferred name ;check each name for matches ReDim $abAddNames[$asNames[0]+1] For $iLoop = 1 To $asNames[0] $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT person FROM names WHERE name = " & _SQLite_FastEscape($asNames[$iLoop]) & ";", $hQuery) If $iRet = $SQLITE_OK Then If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then $vPerson = $aRow[0] $abAddNames[$iLoop] = False Else $abAddNames[$iLoop] = True EndIf Else $abAddNames[$iLoop] = True EndIf _SQLite_QueryFinalize($hQuery) Next ;Create new person if not found If $vPerson = "none" Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO persons (preferred) values (" & _SQLite_FastEscape($asNames[1]) & ");") If $iRet <> $SQLITE_OK Then Return "" $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT id FROM persons WHERE preferred = " & _SQLite_FastEscape($asNames[1]) & ";", $hQuery) If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" $vPerson = $aRow[0] _SQLite_QueryFinalize($hQuery) EndIf ;Update preferred if provided If $iPreferred > 0 Then $iRet = _SQLite_Exec($vAggregatorObject[2], "UPDATE persons SET preferred = " & _SQLite_FastEscape($asNames[$iPreferred]) & " WHERE id = " & _SQLite_FastEscape($vPerson) & ";") If $iRet <> $SQLITE_OK Then Return "" EndIf ;Add names not found For $iLoop = 1 to $asNames[0] If $abAddNames[$iLoop] Then $iRet = _SQLite_Exec($vAggregatorObject[2], "INSERT INTO names (person, name) VALUES (" & $vperson & ", " & _SQLite_FastEscape($asNames[$iLoop]) & ");") If $iRet <> $SQLITE_OK Then Return "" EndIf Next ;Return results If $iPreferred > 0 Then Return $asNames[$iPreferred] ; Preferred was supplied. Return it. ; Look up preferred. $iRet = _SQLite_Query($vAggregatorObject[2], "SELECT preferred FROM persons WHERE id = " & _SQLite_FastEscape($vPerson) & ";", $hQuery) If $iRet <> $SQLITE_OK Then Return "" If _SQLite_FetchData($hQuery, $aRow) <> $SQLITE_OK Then Return "" _SQLite_QueryFinalize($hQuery) Return $aRow[0] EndFunc ; #FUNCTION# ==================================================================================================================== ; Name ..........: _NameAggregator_Open ; Description ...: Loads the SQLite DLL and opens the names database ; Syntax ........: _NameAggregator_Open([$sDatabasePath = Default]) ; Parameters ....: $sDatabasePath - [optional] Path and file name for the names database to use. Defaults to ; @ScriptDir & "\names.db" ; Return values .: Success - returns a variant containing all info needed to access the database ; Failure - returns "" and sets @error to 1 ; Author ........: David Williams (willichan) ; Modified ......: ; Remarks .......: ; Related .......: _NamesAggregator_Close, _NameAggregator_Lookup ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _NameAggregator_Open($sDatabasePath = Default) Local $avReturn[3], $hDatabaseHandle, $sDLLpath If $sDatabasePath = Default Then $sDatabasePath = @ScriptDir & "\names.db" $sDLLpath = _SQLite_Startup() If @error Then SetError(1, 0, "") $hDatabaseHandle = _SQLite_Open($sDatabasePath) If @error Then _SQLite_Shutdown() SetError(1, 0, "") EndIf _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS persons( id INTEGER PRIMARY KEY, preferred TEXT);") _SQLite_Exec($hDatabaseHandle, "CREATE TABLE IF NOT EXISTS names( id INTEGER PRIMARY KEY, person INTEGER, name TEXT);") $avReturn[0] = $sDatabasePath $avReturn[1] = $sDLLpath $avReturn[2] = $hDatabaseHandle Return $avReturn EndFunc and here's a simple example script #include "NameAggregator.au3" $NAobj = _NameAggregator_Open() MsgBox(0, "test", _NameAggregator_Lookup("!John Q Public|jpublic", $NAobj)) MsgBox(0, "test", _NameAggregator_Lookup("jpublic|Jonny Public", $NAobj)) MsgBox(0, "test", _NameAggregator_Lookup("jpublic|Jonnathan Public", $NAobj)) MsgBox(0, "test", _NameAggregator_Lookup("Jonny Public|jqpublic", $NAobj)) MsgBox(0, "test", _NameAggregator_Lookup("jqpublic", $NAobj)) _NameAggregator_Close($NAobj) ; If all goes well, you should get "Jonh Q Public" returned 5 times Edited July 2, 2014 by willichan My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash
willichan Posted June 29, 2013 Author Posted June 29, 2013 Updated to fix some minor issues with the SQL handling. (See first post) My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash
guinness Posted June 29, 2013 Posted June 29, 2013 Nice. I like it that you're not using Global variables. UDF List: _AdapterConnections() • _AlwaysRun() • _AppMon() • _AppMonEx() • _ArrayFilter/_ArrayReduce • _BinaryBin() • _CheckMsgBox() • _CmdLineRaw() • _ContextMenu() • _ConvertLHWebColor()/_ConvertSHWebColor() • _DesktopDimensions() • _DisplayPassword() • _DotNet_Load()/_DotNet_Unload() • _Fibonacci() • _FileCompare() • _FileCompareContents() • _FileNameByHandle() • _FilePrefix/SRE() • _FindInFile() • _GetBackgroundColor()/_SetBackgroundColor() • _GetConrolID() • _GetCtrlClass() • _GetDirectoryFormat() • _GetDriveMediaType() • _GetFilename()/_GetFilenameExt() • _GetHardwareID() • _GetIP() • _GetIP_Country() • _GetOSLanguage() • _GetSavedSource() • _GetStringSize() • _GetSystemPaths() • _GetURLImage() • _GIFImage() • _GoogleWeather() • _GUICtrlCreateGroup() • _GUICtrlListBox_CreateArray() • _GUICtrlListView_CreateArray() • _GUICtrlListView_SaveCSV() • _GUICtrlListView_SaveHTML() • _GUICtrlListView_SaveTxt() • _GUICtrlListView_SaveXML() • _GUICtrlMenu_Recent() • _GUICtrlMenu_SetItemImage() • _GUICtrlTreeView_CreateArray() • _GUIDisable() • _GUIImageList_SetIconFromHandle() • _GUIRegisterMsg() • _GUISetIcon() • _Icon_Clear()/_Icon_Set() • _IdleTime() • _InetGet() • _InetGetGUI() • _InetGetProgress() • _IPDetails() • _IsFileOlder() • _IsGUID() • _IsHex() • _IsPalindrome() • _IsRegKey() • _IsStringRegExp() • _IsSystemDrive() • _IsUPX() • _IsValidType() • _IsWebColor() • _Language() • _Log() • _MicrosoftInternetConnectivity() • _MSDNDataType() • _PathFull/GetRelative/Split() • _PathSplitEx() • _PrintFromArray() • _ProgressSetMarquee() • _ReDim() • _RockPaperScissors()/_RockPaperScissorsLizardSpock() • _ScrollingCredits • _SelfDelete() • _SelfRename() • _SelfUpdate() • _SendTo() • _ShellAll() • _ShellFile() • _ShellFolder() • _SingletonHWID() • _SingletonPID() • _Startup() • _StringCompact() • _StringIsValid() • _StringRegExpMetaCharacters() • _StringReplaceWholeWord() • _StringStripChars() • _Temperature() • _TrialPeriod() • _UKToUSDate()/_USToUKDate() • _WinAPI_Create_CTL_CODE() • _WinAPI_CreateGUID() • _WMIDateStringToDate()/_DateToWMIDateString() • Au3 script parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018
willichan Posted July 12, 2013 Author Posted July 12, 2013 Added case insensitive matching for better results. (see first post) My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash
jchd Posted July 12, 2013 Posted July 12, 2013 Don't escape column Id and person since they are integer literals (e.g. line 186). It might bite you some day. You're lucky that SQLite is smart enough to convert back integers as strings to integers when the column has type integer but realize that those two conversions are useless. Allow me to add a few remarks: Shouldn't both ID columns be declared autoincrement? person shouldn't it be a foreign key for persons.id ? after inserting a new row in names, simply use _SQLite_LastInsertRowid to retrieve the ID used. All in all, why not use a single self-joined table (unless you have other developments in mind) 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 hereRegExp tutorial: enough to get startedPCRE 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)
willichan Posted July 15, 2013 Author Posted July 15, 2013 Don't escape column Id and person since they are integer literals (e.g. line 186). It might bite you some day. You're lucky that SQLite is smart enough to convert back integers as strings to integers when the column has type integer but realize that those two conversions are useless. Thanks. When I encountered a problem with an unescaped name, I guess I rushed implementing the fix, and started coloring outside the lines. Shouldn't both ID columns be declared autoincrement? I thought SQLite automatically increments if the primary key is an integer, and not provided in the INSERT statement. Is it better to explicitly declare it? after inserting a new row in names, simply use _SQLite_LastInsertRowid to retrieve the ID used. Thank you. That is a lot simpler. person shouldn't it be a foreign key for persons.id ? All in all, why not use a single self-joined table (unless you have other developments in mind) This was a larger project for creating some security auditing reports that spanned multiple systems, in multiple locations, administered by groups with different naming standards, with different ... ... you get the picture. I just yanked the names portion out, since I could see some use for it elsewhere. There are originally other tables with security access data that point back to the person record. If it can be done in one table, I am in favor of it. I will have to admit that, though I use databases in alot of solutions, I am not much of a database developer. How would you go about making this into a self-joined table? Do you mean pointing each name back to a record in the same table that is the preferred name? If the preferred name were never to be updated, then I would definately see that as more efficient, but since the preferred name can be changed at any time, I think updating all of the related name records to point to the new name would loose that efficiency. Thank you for the help. I am updating the code in post#1 with the fixes you mentioned above. My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash
jchd Posted July 16, 2013 Posted July 16, 2013 Just for the record (I'm not there to drive your ideas into anything residing in my own head!), you can simplify the current schema into that: CREATE TABLE [People] ( [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Name] CHAR NOT NULL COLLATE NOCASE, [PreferredId] INTEGER REFERENCES iD ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED); Have PreferredId = Id to denote the preferred ID of someone. That's pretty easy to manage, at least for a simple design as the one you posted. Now if the project evolves into a much more ambitious/complex development, all bets are off. But in such case it's fairly normal to have to reconsider the DB design, just like the need to evolve an application may imply deep changes in its OOP class hierarchy. Insert a new person with PreferredId = Id. Insert a new alias with PreferredId = Null then (in the same transaction) obtain the Id of the preferred name and update the newly inserted row. Deleting a preferred name is similar, changing one as well. Experimentation will tell if a compound index helps in practice; unless pressed to do so, leave that alone. Explicitely declaring a PK autoincrement makes it clear of your intention of considering this column as an internal-only, meaningless, identifier. It also eases porting to a possible future other engine, if ever you evolve this into a shareable, wider used audit/management tool. 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 hereRegExp tutorial: enough to get startedPCRE 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)
willichan Posted July 2, 2014 Author Posted July 2, 2014 Posted v2.0 in the first post above. This version adds an optional parameter to _NameAggregator_Lookup() to allow you to do a name lookup without adding any new name entries into the names database. The major improvement is the _NameAggregator_Realign() function. Depending on the order in which names are submitted to the aggregator, is it possible for a name that is already associated with one person record to become associated with a second person record. From that point forward, that name will return results for the person record with the lower ID. This function has been added to facillitate repairing this problem. This can be handled using the internal 'fully automatic' mode, or using your own callback function to implement your own rules for correcting the records, or for creating an interactive script for human intervention. My UDFs: Barcode Libraries, Automate creation of any type of project folder, File Locking with Cooperative Semaphores, Inline binary files, Continue script after reboot, WinWaitMulti, Name Aggregator, Enigma, CornedBeef Hash
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now