Android Question MQTT vs Direct SQL Solutions or Web Server/App Solution

[Magma]

Well,

I am trying MQTT solution and comparing it to other solutions....

figure that:

MQTT is very good when all clients want to see the same thing ! I need to say that this solution is very secure ! MQTT is not a storage solution + so need to storage with sql or something (VERY FAST SPEED/very easy)

Direct SQL Solutions is good when big data and you wanna retrieve some data with filtering / ordering - it can be secure if use SSL and encryption... (MEDIUM Speed/easy)

WEB Server/App Solution can be fast not like MQTT and can filter data.... SSL secure here i think can be stronger than sql / you can have more than user/passwords and encryption... but all the data must organized and retrieved carefully for web server side...is not a storage solution + so need to storage with sql or something (MEDIUM Speed to fast/med difficulty)

I want to go with MQTT for it's easy and the speed... but here the example i want to have help:

Let's say we have 100 clients.. every ~5 sec... all of them put at broker a message... is there a way 1 to 50 clients receive and show all messages and 51-100 clients receive only specific client's messages and show them... the problem for me is the receive to reduce bandwith / not filter...

Must go with two solutions MQTT + SQL?

Thanks in advance...

 

[Erel]

Worth reading: [B4X] The Networker's Guide To The Galaxy

Direct SQL Solutions is good when big data and you wanna retrieve some data with filtering / ordering - it can be secure if use SSL and encryption...

This option (remote database) should never be used outside of trusted networks. Use jRDC2 instead. SSL will not help.

Let's say we have 100 clients.. every ~5 sec... all of them put at broker a message... is there a way 1 to 50 clients receive and show all messages and 51-100 clients receive only specific client's messages and show them... the problem for me is the receive to reduce bandwith / not filter...

Yes. Clients can subscribe to different topics.

 

[Magma]

@Erel

 

[Magma]

do you think is it safe to use MQTT outside trusted networks or go with b4j server etc... ?

 

[BillMeyer]

Hi,

If you are going to use a b4j server, to me, that implies that you have a public IP address and a server online somewhere available - then I would not even hesitate to use jRDC2 - fast, stable and secure.

However, if you want to use the MQTT route then let me address one of your concerns at least.
When you look at the protocol, at first glance it seems as though you need to have all 50 clients (as an example) to subscribe to the same topic (because we tend to hard code this section). This worried me for some time. My scenario was that I have multiple users (Apps - both Android and iOS) wanting to access multiple PC's with local databases (116 PC users) - this is in a Pharmacy environment where the client can order refill medicine. These Pharmacies are situated all over the country.

My solution works like this:

On registration on the app, the client puts in a unique Id Number and chooses his favourite Pharmacy. (Remember - the Pharmacy has a dispensing PC with his software on a shop counter somewhere running on a local database with the clients information on it)

When the client requests to start a transaction, I send the Unique Id of the Client, a GUID, and the Pharmacies Name to the Server (Broker)

Now, my backend system has a MQTT Server (used to be called a broker) that has a list of all Pharmacies available to it and in my code, I can look up this Pharmacies name and publish to it. I do this by soft code (using a variable) when publishing. This will ensure that only that Pharmacy receives the message. The message I send to this Pharmacy contains the clients unique Id Number and their unique GUID number in a json. A small NON GUI software runs on this PC in the Pharmacy and responds to the request by looking up the available medication in the database, creating a json and sending it back to the broker, with the GUID also in the json.

The MQTT Server now extracts the GUID and publishes this information to the GUID as a topic, to which the client previously has subscribed by softcode. Just Note - when the client subscribes the first time with the GUID, I start a timer to give a timeout after a period of time and unsubscribe so that we minimize the possibility of an attack. If timeout - we start again with a new GUID and the process.

When I tested this process, I created a mySQL database, filled it with 100 000 records, took the Unique ID and randomly placed it in the table in 6 places. This entire process from when I pressed request on my App to the time that the returned list of medicine was displayed on my App took less than 1.2 seconds !!

I have included some code on how I created my "SecretKey" on my App - it's very similar on B4J - I hope this gives you some direction as per my experience.

Public Sub CreateSecretKey
    ' Unsubscribe to old number First
    client.Unsubscribe("all/users/MC/MasterDrive/NA/"&SNumber) 'Just in case during testing
    
    ' Now subscribe to new number - Thanks to Erel for this Code
    Dim sb As StringBuilder
    sb.Initialize
'    For Each stp As Int In Array(8, 4, 4, 12) 'Use this if you want a really complex number
        For Each stp As Int In Array(8)
'        If sb.Length > 0 Then sb.Append("-")
        For n = 1 To stp
            Dim c As Int = Rnd(0, 16)
            If c < 10 Then c = c + 48 Else c = c + 55
            sb.Append(Chr(c))
        Next
    Next
    SNumber = "MD"&sb.ToString
    
'    Return sb.ToString
        
    Dim Data As List
    Data.Initialize
    Data.Add(SNumber)
    Data.Add("Acknowledge")
    Data.Add(DateTime.Time(DateTime.Now))
    Data.Add("End")
    
    Dim JSONGenerator As JSONGenerator
    JSONGenerator.Initialize2(Data)
    LogColor(JSONGenerator.ToPrettyString(2),Colors.Blue)

    Dim m As Message
    m.Initialize
    m.Body = JSONGenerator.ToString' Body
    m.From = SNumber

    client.Publish2("all/users/MC/MasterDrive/NA/",serializator.ConvertObjectToBytes(m), 0, False) ' Send to the Server here
    ToastMessageShow("Sent: "&sb.ToString&" to Message Centre",False)
    
    Dim LocTop As String = "all/users/MC/MasterDrive/NA/"&SNumber ' This is where the trick is - only this client subscribes to this topic
    client.Subscribe(LocTop,1)
    Sleep(500)
End Sub

Now all you have to do is follow the normal tutorials as to how to connect, read the message and process the payload.

Here is a Graphic to help somewhat:

I trust I have not confused you too much !!

 

[Magma]

...Very nice !

...can i ask - or it;s silly? Do you believe that a "client" (or outside of your app?) of yours will hack your system ?... because before someone post with guid as you re saying / must log in to broker with user/pass at any ssl protected environment... do you think a mim (man in middle) ?

 

[BillMeyer]

Do you believe that a "client" (or outside of your app?) of yours will hack your system ?

I think that the above mentioned system is too quick for that (I could be wrong) but I'd rather be safe than sorry !!

PS. Thank you for the compliment.