PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: SQLite Client Server Classes  (Read 388 times)

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
SQLite Client Server Classes
« on: May 18, 2017, 12:41:17 AM »

The server and client classes are application support classes supporting features for the developer to pull in as necessary. Here are my thoughts...

Server Class Functions

1. ServerStartup - Create the TCP/UDP listener messaging window to receive connection notifications, Start the UDP and TCP listeners and verify that supporting classes loaded without errors
2. Create non TCP/UDP message window to be used for other purposes, i.e. tracing, logging, backup progress, etc
3. Receive/Reply to UDP broadcast
4. Receive a TCP message
5. TCP Send response
6. SQLite database access
7. Client authorization handshake
8. Set shared encryption key
9. Disconnect session
10. Compress message
11. Uncompress message
12. Connection Pool Statistics
13. Database backup
14. Manage thread pool
15. Set server ID tag
16. Database registration
17. Encryption
18. Decryption

Client Class Functions

1. FindServer - broadcast and receive UDP
2. Authorization handshake
3. Get Server Status
4. Set shared encryption key
5. Disconnect session
6. Compress message
7. Uncompress message
8. Set Server ID tag
9. TCP Send
10. TCP Receive
11. Database registration
12. Encryption
13. Decryption

Each application developer designs their specific send/response sequences. The server/client have control with a predefined message flow during FindServer (UDP broadcast), database registration and the handshake authorization.

The messaging window is a normal windows message pump that receives two specific messages to act upon.

Code: [Select]
Function ListenerProc(ByVal HWnd as HWnd, _
                      ByVal uMsg as uLong, _
                      ByVal wParam as Long, _
                      ByVal lParam as Long) as uLong
                   
' Connection request messages are posted here

    Select Case uMsg

      Case SQLITE_SERVER_TCP_CONNECT

'wParam is the handle of the socket

         Select Case LoWord(lParam)

            Case FD_ACCEPT
           
' optionally call for handshake
               
            End Select
           
        Case SQLITE_SERVER_UDP_BROADCAST
 
            Select Case LoWord(lParam)

            Case FD_READ
           
' call server class to handle broadcast               

            End Select

        Case WM_DESTROY

            PostQuitMessage 0

    End Select

    Function = DefWindowProc(HWnd,uMsg,wParam,lParam)       ' Windows handles the rest
   
End Function



As an application developer, is there anything else you might see that would be useful?

Rick
 
« Last Edit: May 18, 2017, 12:43:21 AM by Richard Kelly »
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8090
  • Windows 10
    • PlanetSquires Software
Re: SQLite Client Server Classes
« Reply #1 on: May 18, 2017, 08:35:30 AM »

I think you would need a method to shut down the server and also a way to retrieve a list of connected users although you might have that in "Connection pool statistics"? Also, you might have to build in functionality to keep connections alive. Kind of a ping to the server after so many seconds of inactivity (should the programmer/client wish this type of functionality for their specific case).
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
Re: SQLite Client Server Classes
« Reply #2 on: May 18, 2017, 10:29:33 AM »

I think you would need a method to shut down the server and also a way to retrieve a list of connected users although you might have that in "Connection pool statistics"? Also, you might have to build in functionality to keep connections alive. Kind of a ping to the server after so many seconds of inactivity (should the programmer/client wish this type of functionality for their specific case).

The FindServer client class function is your "ping". The application developer can run that function as often as they want. If the server is online, you'll get a response that includes the TCP IP and port the server is listening on. The listening is for the local subnet. If the server is on the "other" side of the router somewhere, you'll need port forwarding rules. I'm using UDP as the discovery so each client doesn't need to know where the server is ahead of time. Only the UDP port needs to be known.

The server shutdown process is all in the class deconstructor. The developer takes care of their end (and stops further calls/threads) and when the class goes out of scope, both the server and client classes will clean up their end of the deal. Closing message window, disconnecting the listeners, etc. The key to getting all of this well synchronized is the ThreadPool where the thread handle of launched threads is stored and closed when it is detected the thread has completed. During shutdown, I think it is best to wait for all outstanding threads to complete (blocking new ones from starting). The ThreadPool will have a function to tell you how many active threads are running as well as cleaning up completed thread handles. The server class will reserve ":MEMORY:" tables to manage all of this.

For TCP in Windows, the default keep-alive timeout is 2 hours and the keep-alive interval is 1 second, and, I'm not expecting any request/response cycle to exceed that.

Rick
« Last Edit: May 18, 2017, 05:01:48 PM by Richard Kelly »
Logged

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
Re: SQLite Client Server Classes
« Reply #3 on: May 22, 2017, 12:47:36 PM »

Have all the UDP/TCP functions tested and am starting on the SQLite piece of the server class. To keep the server class "application neutral", I think i'll use a registration function.

RegisterDatabase

Input:

Database ID (> 0)
Database Path (full path to use, i.e. common application data)
Database Name
Pragma's Array
Attachments Array

Output:

TRUE/FALSE

Each database is opened, added to connection pool, and, released during server startup.

Rick
« Last Edit: May 22, 2017, 03:58:37 PM by Richard Kelly »
Logged

Richard Kelly

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 318
Re: SQLite Client Server Classes
« Reply #4 on: May 23, 2017, 04:07:49 PM »

Database Registration

During server startup, I plan on creating some temp tables to hold the results of database registrations.

Code: [Select]
   sSQL = "CREATE TEMP TABLE DATABASE (DATABASEID INTEGER PRIMARY KEY," _
        + "DATABASEPATH TEXT," _
        + "DATABASENAME TEXT);" _
        + CHAR(13) + CHAR(10) _
        + "CREATE TEMP TABLE PRAGMA (PRAGMAID INTEGER PRIMARY KEY," _
        + "DATABASEID INTEGER,"
        + "PRAGMANAME TEXT," _
        + "PRAGMAVALUE TEXT;" _
        + CHAR(13) + CHAR(10) _
        + "CREATE TEMP TABLE ATTACH (ATTACHID INTEGER PRIMARY KEY," _
        + "DATABASEID INTEGER,"
        + "DATABASEPATH TEXT," _
        + "DATABASENAME TEXT);"

When the server class needs to add a connection to the pool, I think below should give me the information.

Code: [Select]
   sSQL = "SELECT DATABASE.DATABASEPATH,DATABASE.DATABASENAME," _
        + "PRAGMA.PRAGMANAME,PRAGMA.PRAGMAVALUE,ATTACH.DATABASEPATH," _
        + "ATTACH.DATABASENAME " _
        + "LEFT JOIN PRAGMA ON PRAGMA.DATABASEID = DATABASE.DATABASEID " _
        + "LEFT JOIN ATTACH ON ATTACH.DATABASEID = DATABASE.DATABASEID " _
        + "WHERE DATABASE.DATABASEID = ?;"

Updates will be a group of inserts into all three tables inside of a transaction. The server class will close the temp connection during class deconstructor which will remove the temp database from the file system. The developer, knowing their application, registers all the databases needed immediately after server startup.

Rick
« Last Edit: May 23, 2017, 04:09:34 PM by Richard Kelly »
Logged