sending full database table with images through tcp socket

JonB

@rhx9 said in sending full database table with images through tcp socket:

the mysql server lives in a remote machine, currently i want to replace the mysql server with an sqlite database and have a server app that sends and recieve data from the main application.

Separately from @StarterKit's comments.

Your desire seems strange to me. If you have a multi-user database on a remote server then MySQL --- or other server-based RDBMS --- seems like the correct choice. I don't know why you would want to change that to a SQLite system. How do you think your new server code handles transactions and multiple simultaneous clients, for example? Seems likely to be most non-robust to me.....

Also your (multi-connection) server is an application with a UI. This too is very strange for a server. I don't know what/why you are trying to achieve.

rhx9

@StarterKit said in sending full database table with images through tcp socket:

As you named it - "message is sent in chunks" - so the answer will be - you need first to collect all chunks from the network and then decode the message.

And how might i achieve that ? care to provide a simple code example ?
I'm not even sure i should be sending the data as json, maybe there is a better way/format to send the data?

Regards

rhx9

@JonB said in sending full database table with images through tcp socket:

@rhx9 said in sending full database table with images through tcp socket:

the mysql server lives in a remote machine, currently i want to replace the mysql server with an sqlite database and have a server app that sends and recieve data from the main application.

Separately from @StarterKit's comments.

Your desire seems strange to me. If you have a multi-user database on a remote server then MySQL --- or other server-based RDBMS --- seems like the correct choice. I don't know why you would want to change that to a SQLite system. How do you think your new server code handles transactions and multiple simultaneous clients, for example? Seems likely to be most non-robust to me.....

Also your (multi-connection) server is an application with a UI. This too is very strange for a server. I don't know what/why you are trying to achieve.

Well, this is a turnstile control program, the server app has a ui(not the one in my example, i removed it for simplicity) that provides info about the member trying to enter the building, another application is run on a control pc on the same network, the control app is used to add/remove/update the members info and permissions, the mysql server is on the same machine as the server app (both are on a raspberrypi 3).
we noticed that the mysql server uses a bit much resources from the raspberry pi than we like, also we are trying to replace the raspberry pi 3 with a raspberry pi zero which has much less CPU and GPU power, but is a much better choice in our case due to low power consumption and heating issues, also we want to run the raspberry pi in a read only mode and have only the database file on a different drive to reduce SD card wear, which could be achieved with mysql but is easier with SQLite.

Although, the system we run is only controlled by one client, the example code i wrote handles multiple clients.

Regards

JonB

@rhx9
All that is fine for your usage of SQLite instead of MySQL, so long as you know what you are doing.

And how might i achieve that ? care to provide a simple code example ?

I'm not even sure i should be sending the data as json, maybe there is a better way/format to send the data?

The JSON encoding seems not unreasonable. For the coding you need to use Qt's signals and slots.

Try not to use any waitFor... calls, as they block.
message = self.tcpSocket.readAll(): This does not guarantee to read anything and everything already sent to you. readAll() just reads whatever is available at the instant it is called, and the readyRead() signal can and will be raised multiple times, calling readmessage() slot each time. So to be robust you must buffer the bytes received so far, appending the new stuff each time. You cannot know that it has received all of your JSON message until either you implement a protocol which can recognise this (e.g. message-length byte count exchanged, or special "marker" at end of JSON message) or you keep retrying JSON parse of what you have received until it no longer says "premature end of data". One possible way of handling the buffering is by using QDataStream transactions around your sockets.

SGaist

Hi,

How many "terminals" are you going to have in your system ?

rhx9

@SGaist said in sending full database table with images through tcp socket:

Hi,

How many "terminals" are you going to have in your system ?

I'm not sure what do you mean by "Terminal".
The system will eventually consist of two programs:
1- a server program in PyQT5 that runs on the raspberrypi, it reads the rfid cards and decides whether to let the person in or not by controlling a relay, it also has the function of replying to messages sent by a the control program and takes actions on these messages that alter the database.
2- a control program in PyQT5 also running on a different machine, the program connects to the server program though tcp and can add/remove/alter the users and their permissions.

SGaist

By terminal I mean the number of devices that are going to connect to your main server.

Depending on that, you device could also be simply thin client that work directly on the server.

JoeCFD

Is it possible to get the data through a web server on the server side and download it by polling on the client side?
It is easy to set-up a web-server.

rhx9

@JoeCFD part of why we are moving to sqlite is to minimize resource usage, running a web server would defeat that purpose

rhx9

@JonB so I've used datastreams to send messages, and although it helps it also has problems

the main problem i have is it's not possible to queue messages, so if the client pushes 2 buttons that sends different commands very quickly it would not send the message.
the following example illustrate the problem:

the server:

import sys
import json
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg

from PyQt5.QtNetwork import QHostAddress, QTcpServer
from PyQt5 import QtSvg
from PyQt5 import QtSql
from PyQt5.QtMultimedia import QSound

class Server(qtw.QDialog):
    def __init__(self):
        super().__init__()

        self.tcp_server = QTcpServer()
        self.connectedclients = []
        self.connectedclients_datastreams = []

        if not self.tcp_server.listen(QHostAddress.AnyIPv4, 8000):
            print("can't listen!")
        self.tcp_server.newConnection.connect(self.on_new_connection)
        self.blockSize = 0

    @qtc.pyqtSlot(int)
    def readmessage(self, clientid):
        client = self.connectedclients[clientid]
        print("reading data from client")
        print("reading incoming data")
    

        instr = self.connectedclients_datastreams[clientid]
        instr.startTransaction()
        if self.blockSize == 0:
            print("self.blockSize == 0")
            if client.bytesAvailable() < 4:
                print("self.tcpSocket.bytesAvailable() < 4")
                return
            self.blockSize = instr.readUInt32()
            print("blocksize: ", self.blockSize)
            print("self.tcpSocket.bytesAvailable(): ",client.bytesAvailable())
        if client.bytesAvailable() < self.blockSize:
            print("client.bytesAvailable() < self.blockSize:", client.bytesAvailable() < self.blockSize)
            return
        
        self.blockSize = 0
        recievedmsg = str(instr.readString(), encoding='utf-8')
        
        print("transaction started")


        if (not instr.commitTransaction()):
            print("wait for more data")
            return

        print("recieved command: '",recievedmsg,"'")
        try:
            messagejson = json.loads(recievedmsg)
            if messagejson['type'] == "open_gate":
                print("opening gate")
            elif messagejson['type'] == "settime":
                print("setting os time")
            elif messagejson['type'] == "shutdown_gate":
                print("shutting down the system")
        except json.decoder.JSONDecodeError:
            print("failed to parse json message")
    
    @qtc.pyqtSlot()
    def on_new_connection(self):
        print("got connection")
        self.connectedclients.append(self.tcp_server.nextPendingConnection())
        
        clientid = len(self.connectedclients)-1
        self.connectedclients_datastreams.append(qtc.QDataStream(self.connectedclients[clientid]))
                        
        self.connectedclients_datastreams[clientid].setVersion(qtc.QDataStream.Qt_5_15)
        self.connectedclients[clientid].readyRead.connect(
            lambda: self.readmessage(clientid))

if __name__ == '__main__':
    app = qtw.QApplication(sys.argv)
    server = Server()
    sys.exit(server.exec_())

the client :

import time
import json
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5 import QtSvg
from PyQt5.QtNetwork import QTcpSocket, QAbstractSocket

class Client(qtw.QWidget):
    def __init__(self):
        super().__init__()
        self.gridLayout = qtw.QGridLayout(self)
        self.open_gate_btn = qtw.QPushButton("open gate")
        self.open_gate_btn.clicked.connect(self.open_gate)
        self.gridLayout.addWidget(self.open_gate_btn)
        self.tcpSocket = QTcpSocket()
        self.tcpSocket.setSocketOption(QTcpSocket.KeepAliveOption, 1)
        self.blockSize = 0
        self.tcp_block = qtc.QByteArray()
        self.tcp_out_stream = qtc.QDataStream(self.tcp_block, qtc.QIODevice.ReadWrite)
        self.tcp_out_stream.setVersion(qtc.QDataStream.Qt_5_15)

        self.tcpSocket.readyRead.connect(self.on_read)
        self.tcpSocket.error.connect(self.displayError)
        self.tcpSocket.connected.connect(self.on_connected)
        self.tcpSocket.disconnected.connect(self.socket_disconnected)

        self.socket_connect()
    @qtc.pyqtSlot()
    def socket_connect(self):
        print("socket state: ", self.tcpSocket.state())
        if self.tcpSocket.state() == 0:
            print("tcpsocket connecting")
            try:
                self.tcpSocket.connectToHost('127.0.0.1', 8000, qtc.QIODevice.ReadWrite)
            except Exception as e:
                print("exception in socket connecting")
                print(e)
            else:
                if self.tcpSocket.state() == 3:
                    print("socket connected successfully")
 
    @qtc.pyqtSlot()
    def on_connected(self):
        current_time = time.time()
        self.send_tcp_command({"type": "settime", "data": int(current_time)})
        self.send_tcp_command({"type": "open_gate", "data": None})
    
    @qtc.pyqtSlot()
    def open_gate(self):
        self.send_tcp_command({"type": "open_gate", "data": None})
    @qtc.pyqtSlot()
    def socket_disconnected(self):
        print("socket got disconnected")

    def on_read(self):
        print("reading message from server")
        instr = qtc.QDataStream(self.tcpSocket)
        instr.setVersion(qtc.QDataStream.Qt_5_15)
        if self.blockSize == 0:
            print("self.blockSize == 0")
            if self.tcpSocket.bytesAvailable() < 4:
                # print("self.tcpSocket.bytesAvailable() < 2")
                return
            self.blockSize = instr.readUInt32()
            # print("blocksize: ", self.blockSize)
            # print("self.tcpSocket.bytesAvailable(): ",self.tcpSocket.bytesAvailable())
        if self.tcpSocket.bytesAvailable() < self.blockSize:
            print(self.tcpSocket.bytesAvailable() < self.blockSize)
            return
        # Print response to terminal, we could use it anywhere else we wanted.
        message = str(instr.readString(), encoding='utf-8')

        messagejson = json.loads(message)

    
    @qtc.pyqtSlot(dict)
    def send_tcp_command(self, message_json):
        print("func:send_tcp_command","sending the following message: ",message_json)
        message = bytes(json.dumps(message_json), encoding="utf-8")
        self.tcp_out_stream.device().seek(0)
        self.tcp_out_stream.writeUInt32(0)
        self.tcp_out_stream.writeString(message)
        self.tcp_out_stream.device().seek(0)
        self.tcp_out_stream.writeUInt32(self.tcp_block.size() - 4)
        self.tcpSocket.write(self.tcp_block)

    @qtc.pyqtSlot(QAbstractSocket.SocketError)
    def displayError(self, socketError):
        print("socket errrrr")
        if socketError == QAbstractSocket.RemoteHostClosedError:
            pass
        else:
            print("The following error occurred: %s." %
                  self.tcpSocket.errorString())

if __name__ == '__main__':
    import sys

    app = qtw.QApplication(sys.argv)
    client = Client()
    client.show()
    sys.exit(app.exec_())

note that when the connection is established (on_connected) the client immediately sends two commands , but on the server we notice that only the first message have arrived. after that if the user presses the open gate button the message would be sent fine since the socket is not busy

Regards

JonB

@rhx9
I don't know what the logic of your code implements. Messages written are always sent, and TCP does not lose any bytes. There is no difference whether a socket is "busy".

Verify that the total number of bytes sent is the same as the total number of bytes received at the other end.

I'm not sure what your protocol implements. I do not see evidence of the receiver splitting the input into separate messages.

Understand that TCP is a continuous stream of bytes. It is not record based. readyRead() signal can be called multiple times (even within one sent "message"): the only guarantee is that it will report anywhere between just 1 and the total number of bytes sent. Do not assume you will get separate calls for each message.

I have a feeling you do not allow for two messages arriving in one readyRead()? When you have picked out the first message from the stream, if more bytes than that have (already) arrived it is up to you to "buffer" those bytes beyond the first message so that they will be read for (as the start of) the second message. I believe you are not doing that, so you do not see the second message. This is more likely to happen when the messages are sent close together, which is doubtless the case when you click two sends rapidly.