Jump to content

Names Aggregator

willichan

By willichan,
in AutoIt Example Scripts

 Share

Recommended Posts

willichan Universalist

willichan

Posted
Posted (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.

:ILA2:

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.

#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.

#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

#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

#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 by willichan

UserBarPurpleGeekNerd.GIF


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

 

Link to comment
Share on other sites
  • 3 weeks later...
willichan Universalist

willichan

Posted
  • Author
Posted

Updated to fix some minor issues with the SQL handling.  (See first post)

UserBarPurpleGeekNerd.GIF


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

 

Link to comment
Share on other sites
guinness Universalist

guinness

Posted
Posted

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

Link to comment
Share on other sites
  • 2 weeks later...
willichan Universalist

willichan

Posted
  • Author
Posted

Added case insensitive matching for better results. (see first post)

UserBarPurpleGeekNerd.GIF


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

 

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

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 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)

Link to comment
Share on other sites
willichan Universalist

willichan

Posted
  • Author
Posted

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.  :doh:

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 ... :mad2: ... 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.

UserBarPurpleGeekNerd.GIF


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

 

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

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 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)

Link to comment
Share on other sites
  • 11 months later...
willichan Universalist

willichan

Posted
  • Author
Posted

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.

UserBarPurpleGeekNerd.GIF


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

 

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...