Comms HUB

[storme]

G'day all

I've had this idea running around for a while and I keep getting stuck in loops in my head with all the "what-ifs" so I'm hoping someone more knowledgeable then myself can lend me a hand.

I am writing a client-server system where each program (AutoIT) I install on clients computers use a central hub program (AutoIT) to communicate with the server (also AutoIT). My clients are ALL remote so this will all be over the internet. The system will be a “store and forward” system taking messages from the client scripts and passing them on to the server and passing received messages from the server onto the client scripts. The client hub will initiate all communication removing the need to set up router tunnels or UPnP.

There are a few advantages to the system :

1. The server and clients hubs don’t have to be on constantly as each end will store messages until communication can be established (Especially good for wireless/dialup/etc)

2. Because of the central hub all the encryption/handshaking/etc is centralised and any changes are restricted to this one program.

At the moment I'm thinking of using MailSlot for communications between the scripts and the HUB. Of course there will be a UDF for the scripts to add to communicate with the HUB. BUT that is the next stage..

The interscript communication script may even be a separate service that drops the messages into an SQLite database for the ClientHUB to sent to the SeverHub.

Can 2 scripts access the same SQLite Database at the same time? Anyone know?

{As you may have guessed already, everything is in flux at the moment... }

I'm using KIPs TCP UDF, Event driven on both the server and client ends as it makes the "actual" TCP a breeze.

OK now to the bits that are making me run around in circles.

1. Size of packets

a. Should I restrict the packet size?

b. Is there a min/max restriction on packets I can send?

2. CRC/MD5Hash

a. Should I implement a checksum to ensure that the data wasn’t corrupted.

b. Which is the quickest to do in AutoIT.

3. There were other questions BUT the time it took me to get all this out I forgot what they were.

Also if anyone has any advice or suggestions outside of what I’ve asked please speak up.

I’d be especially impressed if someone said “why are you doing that I have the perfect solution, here is the code!” but I’m SURE that isn’t going to happen. But we do live in hope.

Thanks in advance for any and all help you can offer.

John Morrison

Aka

Storm-E

[jchd]

I can answer some of your questions.

SQLite is an embedded RDBMS library, which means that it isn't safe to share an SQLite database on a network. This is due to bugs in almost all NFS implementations, regardless of the OS maker. There are client-server add-ons available, some of them listed on the SQLite website.

Now if you database is to be shared by more than one single processes on the same machine, things are much better. You still have to be a little aware about the locking scheme that SQLite uses. Because it relies on the underlying filesystem (an SQLite database is one single file) locking is made on the whole file. Except in more advanced contexts, there is no possibility to lock a table or a recordset. Anyway, two or more separate processes (e.g. AutoIt scripts) can share a base. SQLite (as compiled in the standard Windows distribution) is also threadsafe, but since AutoIt isn't, there is nothing relevant here.

Set an ample timeout for every DB connection. For instance, I use as much as 60s, to be safe.

Then wrap any Read/Modify/Write operation in "Begin immediate;" .. "Commit;" statements to avoid possible deadlocks.

Keep such statements as short as possible. Example pseudo-SQL of bad idea:

begin immediate;

select * from T where <condition>;

-- display rows to the user in a Listview

-- <-- here, user XYZ falls asleep or goes to lunch (the table is still "reseved-locked")

-- <-- user XYZ decides to perform some change in the data

update T set ....; -- rewrite changed data

commit; <-- other users can only access the base after the commit, maybe hours later!

Except limitations like the above, there is little that you need to care of.

WRT TCP: hopefully, the protocol and transport layers are hidden to you. You don't have to select a route yourself, nor have to care that data could be distorded in transit. These poins are covered by low levels in the OSI model!

[storme]

I can answer some of your questions.

That is all I ask.

Now if you database is to be shared by more than one single processes on the same machine

Anyway, two or more separate processes (e.g. AutoIt scripts) can share a base.

That will be the situation. One database, one TCP HUB, X client scripts dumping messages or collecting messages.

Set an ample timeout for every DB connection. For instance, I use as much as 60s, to be safe.

Then wrap any Read/Modify/Write operation in "Begin immediate;" .. "Commit;" statements to avoid possible deadlocks.

Thank you

Keep such statements as short as possible. Example pseudo-SQL of bad idea:

Understood!

The clients will have the data ready and just dump it in. In one movement.

The hub will be a bit busier so I will have to be more carefull with it....

BTW I like your example... I wonder how many programs out there do exactly that.

WRT TCP: hopefully, the protocol and transport layers are hidden to you. You don't have to select a route yourself, nor have to care that data could be distorded in transit. These poins are covered by low levels in the OSI model!

Hmm I'll move forward with that in mind but I'm a little more parnoid. Yes the OSI does promise to take care of all of that BUT I've seen corruption before on downloaded files that were fine on the second doenwload. It coudl have been the FTP program or the TCP protocol both should have stopped any corruption...Hmm

Thanks for your help.

John Morrison

aka

Storm-E

[jchd]

From what you say, you should be fine in this context.

There are indeed programs doing that, as some DBMS don't lock the whole DB, but only concerned rows and even concerned columns. There is no provision for such fine-grain locking in SQLite, due to Lite precisely and reliance on underlying filesystem and locking mechanisms. Anyway, it won't be an issue for you.

TCP: there are many opportunities for corruption, but stable protocol stacks are today's rule. I mean for instance: we don't "often" receive, due to corruption in transit, a webpage full of rogue data, nor missing blocks in the middle of mails fetched from a POP server. I wouldn't care too much, except in mission-critical situations. As a tribute to your paranoïa, you can still append an MD5 hash to every data sent, but then, you're running into inventing some application-layer protocol to handle handshaking, errors, retries ... Really worth it?

[storme]

From what you say, you should be fine in this context.

There are indeed programs doing that, as some DBMS don't lock the whole DB, but only concerned rows and even concerned columns. There is no provision for such fine-grain locking in SQLite, due to Lite precisely and reliance on underlying filesystem and locking mechanisms. Anyway, it won't be an issue for you.

Lite will be fine for me. NO need to install extra software and my requirements are minimal. I'll be good to do some Dbase work again haven't touched it since uni.

TCP: there are many opportunities for corruption, but stable protocol stacks are today's rule. I mean for instance: we don't "often" receive, due to corruption in transit, a webpage full of rogue data, nor missing blocks in the middle of mails fetched from a POP server. I wouldn't care too much, except in mission-critical situations. As a tribute to your paranoïa, you can still append an MD5 hash to every data sent, but then, you're running into inventing some application-layer protocol to handle handshaking, errors, retries ... Really worth it?

Ok you've convinced me Or maybe it's just because it's easier to NOT add extra layers when they probably arn't (most likely) needed.

As I siad in the first Email the system in still in flux and with these few messages I've gone from using mailslots to probably having the clinet scripts accessing the database directly. I'll just have to see how much "weight" the scripts gain by adding SQL UDF over using mailslot. It will definatly me easyier adn simpler to access them direct.... hmm

It will be interesting

Thanks for the input.

John Morrison

aka

Storm-E

[jchd]

As I siad in the first Email the system in still in flux and with these few messages I've gone from using mailslots to probably having the clinet scripts accessing the database directly. I'll just have to see how much "weight" the scripts gain by adding SQL UDF over using mailslot. It will definatly me easyier adn simpler to access them direct.... hmm

I think we misunderstood each other. I insisted that:

it isn't safe to share an SQLite database on a network. ... Now if you database is to be shared by more than one single processes on the same machine, things are much better.

You're in the situation of 1 single DB with N remote clients accessing the DB over SMB. And that's the situation where SMB (NFS, and other protocols) locking is known to fail on most systems, if not almost all systems.

I'm in a similar situation where I have K databases (1 or more doesn't in fact make any difference here) and wish I could have several remote clients accessing them over my LAN. Currently only the machine hosting the base is using it and I have plans to (understatement for "I badly need to move my ass to be able ASAP to") adapt a client-server layer to my software.

[storme]

You're in the situation of 1 single DB with N remote clients accessing the DB over SMB. And that's the situation where SMB (NFS, and other protocols) locking is known to fail on most systems, if not almost all systems.

No.... you were right in your previous Email. ALL of the database activity will be within the same computer

The Client programs will drop or pickup their messages from the SQLite database (on the client computer).

The clientHUB then sends the messages to the serverHUB over TCP (internet).

The serverHUB drops the recieved messages into another SQLite database (On the server Computer) for the server programs to pickup.

Of course the reverse happens as well (from server to client).

Looking at the current state of the design it looks like at this level it's just a database sync program.

Of course there will be more functions added after this core is working.

John Morrison

[jchd]

OK I get it now. Sorry for misreading, but better warn twice rather than drive people in mousetrap!

Have fun.

[storme]

OK I get it now. Sorry for misreading, but better warn twice rather than drive people in mousetrap!

Have fun.

I agree and appreciate the warnings!

Thanks for all the help!

Yep should be fun....I've never used SQLite before.....TCP I know (doesn't everyone ) BUT I've never written anything using it directly so that will be fun as well.

When I have something working I'll be back to share.

[jchd]

You know you're welcome. If you have issues with SQL[ite] and / or DB design, you also know where to chime!