Passing around an array

[bourny]

I am working on a way to have multiple users share data with each other. All users are working  on the same data which is presented to them as a list view in a gui,  As the users change the data in the list view I simply capture the change and the location of the change and stamp this into a file that all users can read and write to.  There begins my problem,

Problem 1.  Not all users will be able to access the central file to stamp in their changes as another user may have the file already open for editing.

Problem 2. Creating a queuing mechanism is easy to do but will slow the program down and put users into locks in the event the user writing to the file does not release the write handle on the file

For this reason i am abandoning the concept of using files to carry this data.  I have though about using Registry keys which will not present this issue however it is messy.  So my next plan sounds really good but i have no clue of it is even possible and cannot find any record of this being done.

Idea : Each user already has a copy of the same array just with possibly different data in it.  Can I simply advertise the contents of the array to an external program or another user on a different P.C (DNS name will be known to the program of all users contributing).  This way user 2 can simply get a copy of user 1`s array and carry out a function to compare etc which is all straight forward to do.

What do you think.  Am I reaching levels of craziness here or is this idea actually workable.

Any help appreciated 

 

[JohnOne]

This is what a database is for.

[bourny]

Yes it had factored in on my options however that is a whole load of new code and many of the required skillets to pull this off beyond my abilities.  

[water]

I would try a database as well. Everything else soon gets too complex and unreliable.
There are many tutorials and example scripts on the forum telling you how to use SQLite for example.

[bourny]

OK thanks for you comments. My suspicions it it could not be done as I am trying to do it.  My reservation against using a DB is due to knowledge and limited time to complete this project so adding on DB, SQLlite studying will push me back significantly.

 

 

[JohnOne]

SQLite will not be much good because because of the writing conflict you will encounter, it would need to be MySQL or something like that.

That will handle race conditions you see, any other way you will need to handle race conditions yourself which in itself will become a projects of its own.

I do not believe there is any "simple" way to achieve what you desire.

[jchd]

Plain SQLite isn't the right choice in your case, not because it can't handle concurrency as J1 said, but because it actually is an embedded engine designed to work with local databases. I.E. it won't work reliably over a network, only due to issues with all existing file locking protocols (whatever OS you target).

Depending on the volume of the data (I guess rather small since you present it in a listview) and frequency of use (concurrency) you can still go by with a relatively simple IPC protocol suitable for network use, à la MailSlot. Nevertheless, you're going to have to decide whether or not to lock the data while the list is displayed on machine A, in the event that the user would change something. If the list is displayed on machine A and B at the same time, and B decide to change it, machine A won't know and still display obsolete data. This is the transactional machinery that database engines offers you.

Finally it all boils down to either write code to manage data from a remote DB or write remote IPC code to insure data integrity over machines. It might well be that the former solution is both simpler, faster and more reliable than the latter. Whatever solution you use, you'll still have to use some isolation barrier to avoid inconsistancies.

[ViciousXUSMC]

Im no DB admin or anything but I was able to build a DB for inventory with MySql and then write GUI scripts to use it pretty easy.

Great thing is no cost, free product, used an old computer for the server, and I have my tech pc running backups of the DB hourly incase the computer catches on fire or something.

If your data is as flat as it sounds, building the database with something like MySql Workbench should be easy for you, and AutoIt has some good UDF's to use for the queries/writes.

If it helps here are a couple of snippets of code I used.

Change Status of a Computer in the DB:

expandcollapse popup

#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#include <mysql.au3>
#include <WinAPI.au3>

AdlibRegister("TimeExit", 300000)

#Region ### START Koda GUI section ### Form=c:\users\it022565\desktop\database\change status.kxf
$Form1_1 = GUICreate("Black Magic Automation", 405, 207, 253, 149)
$Label1 = GUICtrlCreateLabel("Change Location or Status of Public Safety Toughpads", 16, 0, 380, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$Combo1 = GUICtrlCreateCombo("TabletName", 24, 56, 145, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL, $WS_VSCROLL))
;GUICtrlSetTip(-1, "Select Tablet Name From Dropdown", "How to use:",1, 1)
$Label2 = GUICtrlCreateLabel("Select Tablet", 56, 32, 67, 17)
$Label3 = GUICtrlCreateLabel("Select Status", 256, 32, 67, 17)
$Label4 = GUICtrlCreateLabel("Type New Location", 240, 88, 97, 17)
$Combo2 = GUICtrlCreateCombo("Status", 208, 56, 145, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
;GUICtrlSetTip(-1, "Select Status: Active, Spare, Repair", "How to use:", 1, 1)
$Input2 = GUICtrlCreateInput("Input Your Name", 24, 112, 145, 21)
;GUICtrlSetTip(-1, "Enter your Full Name or User ID", "How to use:", 1, 1)
$Input1 = GUICtrlCreateInput("Location", 208, 112, 145, 21)
GUICtrlSetTip(-1, "Fire Admin, MD73, BAT2 Truck", "Examples:", 1, 1)
$Button1 = GUICtrlCreateButton("Submit", 112, 160, 171, 25)
$Label5 = GUICtrlCreateLabel("Your Name", 60, 88, 83, 17)
GUIRegisterMsg($WM_COMMAND, "_WM_COMMAND")

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###


Dim $ret[1][1], $rs

$sql = _MySQLConnect("snip")
If @Error Then
    MsgBox(0, "", "Can Not Connect to the Database.  Error Code " & @Error)
    Exit
EndIf
$rs=_Query($sql,"Select TabletName From toughpads.status where TabletName Like 'Toughpad%';")
With $rs
    $nLaenge = $rs.Fields.Count ; get count of fields

; add fields value to 2 dimension array
    While Not .EOF
        ReDim $ret[UBound($ret, 1) + 1][$nLaenge]
        For $i = 0 To $nLaenge - 1
            $ret[UBound($ret, 1) - 1][$i] = $rs.Fields ($i).value
        Next
        .MoveNext
     WEnd
     ; add fields name to first row of array, must run below fields value because the redim is there
For $i = 0 To $rs.Fields.Count -1
    $ret[0][$i]=$rs.Fields($i).name
 Next

EndWith

 ;_ArrayDisplay($ret, "Check Status", "", 64)

For $i=0 To Ubound($ret)-1
    GUICtrlSetData($Combo1, $ret[$i][0], $ret[0][0])
Next

GUICtrlSetData($Combo2, "Active|Spare|Repair", "Status")

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            ExitLoop

    EndSwitch
WEnd


DIM $ColumName[5]
DIM $NewData[5]

$ColumName[0] = "TabletName"
$ColumName[1] = "Location"
$ColumName[2] = "Status"
$ColumName[3] = "LastOwner"
$ColumName[4] = ""
$NewData[0] = GUICtrlRead($Combo1)
$NewData[1] = GUICtrlRead($Input1)
$NewData[2] = GUICtrlRead($Combo2)
$NewData[3] = GUICtrlRead($Input2)
$NewDAta[4] = ""

_DeleteRecord($sql, "toughpads.status", "tabletname", GUICtrlRead($Combo1))
_AddRecord($sql, "toughpads.status", $ColumName, $NewData)


Dim $ret2[1][1], $rs2
$rs2=_Query($sql,"Select * From toughpads.status where status.tabletname ='" & GUICtrlRead($Combo1) & "';")
With $rs2
    $nLaenge = $rs2.Fields.Count ; get count of fields

; add fields value to 2 dimension array
    While Not .EOF
        ReDim $ret2[UBound($ret2, 1) + 1][$nLaenge]
        For $i = 0 To $nLaenge - 1
            $ret2[UBound($ret2, 1) - 1][$i] = $rs2.Fields ($i).value
        Next
        .MoveNext
     WEnd
     ; add fields name to first row of array, must run below fields value because the redim is there
For $i = 0 To $rs2.Fields.Count -1
    $ret2[0][$i]=$rs2.Fields($i).name
 Next

EndWith
_MySQLEnd($sql)

_ArrayDisplay($ret2, "New Toughpad Status", Default, 96)

Func TimeExit()
    _MySQLEnd($sql)
    Exit
EndFunc


Func _WM_COMMAND($hWHnd, $iMsg, $wParam, $lParam)

    ; If it was an update message from our input
    If _WinAPI_HiWord($wParam) = $EN_CHANGE And _WinAPI_LoWord($wParam) = $Input2 Then

        ; Read content
        $sContent = GUICtrlRead($Input2)
        ; check if any non-letters
        If StringRegExp($sContent, "[^A-Za-z ]") Then
            ; Replace any non-letters
            $sContent = StringRegExpReplace($sContent, "[^A-Za-z ]", "")
            ; Colour input
            GUICtrlSetBkColor($Input2, 0xFFCCCC)
            ; Create tootip

            $aPos = WinGetPos($Form1_1)
            ToolTip("Letters Only", $aPos[0] + 30, $aPos[1] + 100, "Error", 3)
            ; Register function to clear tooltip and reset backcolour

            AdlibRegister("_ResetBkColor", 1000)
        EndIf

        ; Set the label to the new data
        GUICtrlSetData($Input2, $sContent)

    EndIf



EndFunc   ;==>_WM_COMMAND

Func _ResetBkColor()
    AdlibUnRegister("_ResetBkColor")
    GUICtrlSetBkColor($Input2, 0xFEFEFE)
    ToolTip("")
EndFunc

Read Current Status of all Computers in the DB:

expandcollapse popup

#include <Array.au3>
#include <mysql.au3>

AdlibRegister("TimeExit", 300000)

Dim $ret[1][1], $rs

$sql = _MySQLConnect("snip")
If @Error Then
    MsgBox(0, "", "Can Not Connect to the Database.  Error Code " & @Error)
    Exit
EndIf
$rs=_Query($sql,"Select * From toughpads.status Where tabletname like 'toughpad%';")
With $rs
    $nLaenge = $rs.Fields.Count ; get count of fields

; add fields value to 2 dimension array
    While Not .EOF
        ReDim $ret[UBound($ret, 1) + 1][$nLaenge]
        For $i = 0 To $nLaenge - 1
            $ret[UBound($ret, 1) - 1][$i] = $rs.Fields ($i).value
        Next
        .MoveNext
     WEnd
     ; add fields name to first row of array, must run below fields value because the redim is there
For $i = 0 To $rs.Fields.Count -1
    $ret[0][$i]=$rs.Fields($i).name
 Next

EndWith

_MySQLEnd($sql)

 _ArrayDisplay($ret, "All Tablet Status", DEFAULT, 96)

Func TimeExit()
    _MySQLEnd($sql)
    Exit
EndFunc

 

[bourny]

JCHD : My volume of data is relatively small you are correct.  The list view itself will hold around 200 rows with around 6 columns.  Of the 6 columns only a select few will be editable by the users.  The actual rate of change to this data will not be intense as the users will have to process certain marked items in the list view.  Clearly the program will be working at a much faster rate to what the users can input so this may change the stance slightly on the right approach for this.  Maybe using flat files and queuing system will work for this level of workload. 

ViciousXUSMC;  Lots of thanks your way for this excellent pointer on how to use MYSQL.  I am still trying to avoid going down that route however if it looks like I will need to then you have given me an excellent start here with this example.  

[bourny]

Ok.  This is what I am going with.

- Use flat txt files to record the changes.  Each time a user makes a change this results in the following action.

 -- Control file that contains all the changes is renamed from "control.txt" to "control.Username"

-- Make the edits with function to detect if file is being edited and highlight on the GUI who is locking the file by obtaining the username suffix of the file.

--rename the file back to indicate the user has completed editing the file.

Meanwhile any user who needs to edit at the same time will invoke an adlib function (or similar may not use adlib as I Dont really like them for race condition and performance issues) to track the status of the file and have any changes they are making piped into a temporary array which will then be fed in once the file is available.

I will use time stamps to ensure any conflicts on the same data changing are resolved correctly once the information is merged.

 

I am also looking at the following UDF which will allow me to introduce extra functionality for correctly handling the locks on the file and possibly removing looks under certain scenarios.  https://www.autoitscript.com/forum/topic/138951-lockfile-lock-a-file-to-the-current-process-only/?page=1

 

From what I expect of the amount and rate of data change this method will suffice.  Clearly I will explore other avenues suggested above if it turns out this method is proving too difficult to control.

 

[bourny]

Scratch the above post I am using the registry as my deposit for informational changes to avoid all the locking of files which is heavier and riskier than file edits.  On the plus side if the uses machine BSODs , reboots the program can simply pull out its last workable list from its own registry without having to contact the network.  Other users would simply poll other machines registry keys for changes and pull out the data that has changed and simply have some conflict resolution code if the same data has changed on 2 users machines.

At least this way it reduces the time that the changes to the data remain volatile in memory as it is much quicker to place them into a string in the registry tan navigate to a UNC then open a file etc.  

 

 

[jchd]

I believe you're just adding more complexity and unsolvable issues to the problem by multiplying data repositories. Locking a centralized file (or better, a DB) is way easier than locking multiple registries, some being unreachable due to machines being turned off. And once a machine (re)boots, it has to re-sync with all machines to determine the "correct" data, including those which are offline! This is equivalent to multiple databases (or data sources, if you prefer this term) sync and this is an unsolved problem in the general case, whatever you can read here and there.

simply have some conflict resolution code if the same data has changed on 2 users machines

Unfortunately, that's also where the devil lives.

To illustrate, imagine we deal with a simple inventory list, with item number (all from the same supplier) and quantity in stock:

Item #1 Qty 17
Item #2 Qty 23

Say this is the list which is displayed at the same time to a warehouseman W and a salesperson S.

W modifies #1 qty to 37 for he just received a delivery of 20 and at the same time changes #2 qty to 0 because the tech department told him the items are defective and must be sent back to the manufacturer.

During this operation, S sells #1 3 pieces and #2 5 pieces to a client and changes the quantities to 14 and 18, respectively.

What the actual stock will look like from W, S or other users' point of view?

The correct way to handle this is to have the stock listing centralized in one place, displayed as requested, but before any change is actually enforced to mark the items to be changed as "locked for pending modification", change the data and only after release it for other users. You can't decently avoid displaying a list with now obsolete data, but at least no-one will be able to make conflicting changes to the same data without others being aware of the change if they need to modify the data themselves.

The main advantage of using a database engine is that it offers robust machinery to ensure that the transactional part complies with ACID properties and you can trust me it isn't the easiest job. Now regardless of your use of a database engine, a proprietary file locking or a clever messaging system to propagate the changes, you'll have to use or build some form of ACID transaction for locking the data in the initial state where it is, displaying it afresh, allowing changes, recording them, releasing the data for other uses.

[bourny]

Completely understand your point and cannot disagree in any way.  My method is crude to say the least however is a means to get this program off the ground. I can and will pursue the MySQL option as this sounds like the best solution to be implemented.  

 

This application will simply be recording basic items as a workflow engine which users contribute information.  The aim of the workflow engine is to ensure all the relevant missing bits of data is collected and in putted into the list view.  there should be no scope for scenarios as you mention above to occur however if the program develops then I should really be looking towards the next stop of placing it all in a database.

[JohnOne]

You could look at designing your your script a little differently. For example, one machine (server) distributes certain tasks to the other machines to be processed and another is not sent until the last is completed.

You could communicate over TCP or something.

[bourny]

I have spent this afternoon following the advice of going down the route of MySQL. When more than one person is steering you in a direction its best to listen  So now I have the following 

- New MySqL server (Downloaded and installed )

http://dev.mysql.com/downloads/windows/installer/5.6.html

Had to run bug fix during installer http://bugs.mysql.com/bug.php?id=71474

I then spent quite a bit of time looking thru forum threads and using Prog@ndy `s MySql UDF to get me up and running with MySQL.  (Thanks Prog@ndy for the nice UDF)

 

For ease I have written a script which I can use to create the Database structure.  

------------------------------------------------------------------------------------------------------

-This will help me automate the column layouts I need and now starting to feed information into the first table from one of my CSV`s.  

For the benefit of those who are like myself apprehensive of using MySQL I am creating an Autoit script which i will once complete attach it.  I have attached what i have so far which will create the DB, Tables and populate in the information using TSQL.

As I find more functions to do the more advanced queries i will update this topic so others can get a head start.  I will use the code in this script to recreate the database structure with then enough knowledge to embed these functions into my core GUI.

expandcollapse popup

#include <MySQL.au3>
#include <Array.au3>
#include <File.au3>

Global $MySQLServer="localhost"
Global $MySQLDatabase="Service_Database"

Global $MySQLUser="AutoIT_AI"
Global $MySQLPassword="password"
Global $MysqlConn
Global $MysqlConnected


$aMasterList =  ReadInMasterListFromCSV()
InitializeLibrary()
Create_Structure($aMasterServerList)

Func Create_Structure($aMasterServerList)

    ;Create the Database Structure

    Create_Database("Service_Workflow")
    Create_Table("Service_Workflow", "Master_List")
    SQL_Create_Columns("Service_Workflow", "Server_List", "ServerName,IP,OS,PhysicalorVirtual,ServerRole")
    PopulateMasterServerListTable("Service_Workflow",$aMasterServerList)

    _MySQL_EndLibrary()

EndFunc

;-------------------------------------------------------------------------------------
;FUNCTIONS - >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
;-------------------------------------------------------------------------------------

Func Create_Database($DBName)

    ;$Qry = "CREATE DATABASE IF NOT EXISTS EzMySqlTest"
    ;The DB can easily be dropped and recreated as the code will hook into the MYSQL Default DB


    $Qry = "CREATE DATABASE IF NOT EXISTS " & $DBName
    $ReturnVal = _mysql_query($MysqlConn,$qry)
    Select
        Case $ReturnVal = 0 ; success
            ConsoleWrite("Create new Database Success:" & $DBName & @CRLF)

        Case $ReturnVal = 1 ;failed
            ConsoleWrite("Create New Database Failed: " & $DBName & @CRLF)

    EndSelect

EndFunc

;-------------------------------------------------------------------------------------

Func Create_Table( $DBName, $TableName)

    ;TSQL
    ;CREATE TABLE MasterList (ServerName int PRIMARY KEY NOT NULL)  -- this will simply create the Table named "MasterList" and create the first column called Servername as an ID


    $Qry = "USE " & $DBName
    $ReturnVal = _mysql_query($MysqlConn,$qry)

    $Qry = "CREATE TABLE " & $TableName & "(ServerName VARCHAR(50) PRIMARY KEY NOT NULL)"
    $ReturnVal = _mysql_query($MysqlConn,$qry)
    Select
        Case $ReturnVal = 0 ; success
            ConsoleWrite("Create new Table Success:" & $TableName & @CRLF)

        Case $ReturnVal = 1 ;failed
            ConsoleWrite("Create New Table Failed: " & $TableName & @CRLF)

    EndSelect

EndFunc

;-------------------------------------------------------------------------------------

Func SQL_Create_Columns( $DBName,$Table,$StringOfColumns)

    ;$qry = "ALTER TABLE master_list ADD Company VARCHAR(20) NULL"


    $Qry = "USE " & $DBName
    $ReturnVal = _mysql_query($MysqlConn,$qry)

    $aListOfColums = Stringsplit($StringOfColumns, ",")
    If IsArray($aListOfColums) Then

        for $element in $aListOfColums
            If $element = Ubound($aListOfColums) -1 then ContinueLoop

            $Qry = "ALTER TABLE " & $Table & " ADD " & $element & " VARCHAR(20) NULL"
            $ReturnVal = _mysql_query($MysqlConn,$qry)
            Select
                Case $ReturnVal = 0 ; success
                    ConsoleWrite("Create new Column Success: " & $Element & @CRLF)

                Case $ReturnVal = 1 ;failed
                    ConsoleWrite("Create New Column Failed: " & $Element & @CRLF)

            EndSelect

        Next

    EndIf


    ;Return $NumSuccessQry, $NumFailedQry

EndFunc

;-------------------------------------------------------------------------------------

Func PopulateMasterServerListTable($DBName, $aMasterList)  ;;;;;INCOMPLETE - WORKING ON



    $Qry = "USE " & $DBName
    $ReturnVal = _mysql_query($MysqlConn,$qry)

    ;Run the below 2 lines to run a manual entry into the database as a test.
    ;$Qry = "INSERT INTO Master_List (ServerName,IP,OS,PhysicalorVirtual,ServerRole") VALUES ('MyServer','192.168.0.5','2012','Virtual','Exchange')"
    ;$ReturnVal = _mysql_query($MysqlConn,$qry)


    ;_ArrayDisplay($aMasterList)
    For $Element in $aMasterList

        If $Element = Ubound($aMasterServerList) -1 Then ContinueLoop
        $aCSVLine = StringSplit($Element, ",")
        For $CSVLineElement in $aCSVLine
            
            ; - ASSEMBLE THE QUERY WITH DYNAMIC INPUT FROM VARIABLES.
            
            $aSplitLine = StringSplit($CSVLineElement, ",")
            ;;;havent finished this bit yet !!!!!!!!!!!!!!!!!!!!!!!!!!!
            ;-----need to wrap in single quotes and substitute the below with the dynanmic looped entries
                
            
            
            $Qry = "INSERT INTO Master_List (ServerName,IP,OS,PhysicalorVirtual,ServerRole") VALUES ('MyServer','192.168.0.5','2012','Virtual','Exchange')"
            $ReturnVal = _mysql_query($MysqlConn,$qry)
            
        Next


    Next


EndFunc

;-------------------------------------------------------------------------------------

Func InitializeLibrary()

 _MySQL_InitLibrary()
    If @error Then
        Exit MsgBox(0, "debug", "_MySQL_InitLibrary() failed")
    Else
        $SQLloaded =True
        $MysqlConn = _MySQL_Init()
        $MysqlConnected = _MySQL_Real_Connect($MysqlConn, $MySQLServer, $MySQLUser, $MySQLPassword, $MySQLDatabase)
        If $MysqlConnected = 0 Then Exit MsgBox(0, "debug", "_MySQL_Real_Connect() failed")
    EndIf

EndFunc

;-------------------------------------------------------------------------------------

Func ReadInMasterListFromCSV()

    Local $a_csv
    $s_Path = "c:\au3\MasterList.csv"
    _FileReadToArray($s_Path, $a_csv)

    Return $a_csv

EndFunc



























#cs
    ;https://www.autoitscript.com/forum/topic/116072-ezmysql-udf-use-mysql-databases-with-autoit/
    ;$sMySqlStatement = "CREATE TABLE IF NOT EXISTS TestTable (" & _
                   "RowID INT NOT NULL AUTO_INCREMENT," & _
                   "Name TEXT NOT NULL ," & _
                   "Age INT NOT NULL ," & _
                   "EyeColour TEXT NOT NULL ," & _
                   "HairColour TEXT NOT NULL ," & _
                   "PRIMARY KEY (`RowID`) ," & _
                   "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"


    $returnval = _mysql_query($MysqlConn,$sMySqlStatement)
    msgbox(0, "", $returnval)
    #ce

    ;$query = "REPLACE INTO " & $MySQLTable & " ( dataa, datab, datac ) VALUES ( 'valuea', 'valueb', 'valuec' )"
    ;$res = _MySQL_Real_Query($MysqlConn, $query)
    ;If $res <> 0 Then MsgBox(0,"debug", "_MySQL_Real_Query() failed with " & $res)