ServQLite - SQLite Server for PureBasic 0.0.5

[srod]

You just have to build in a system of reader/writer locks similar to SQLite's internal mechanisms, but of course at the server level (and preferably targetting individual database files). With suitable timeouts and providing those writing to the database in question are quick about their business then I see no reason why this should not work and be suitable for many uses.

[idle]

I'd look at using a linked list based queue in the server, as you need to ensure that writes within an over lapped time request takes priority over the reads, so if you have an over lapped request from two or more clients you just need to insert the reads after the writes in the list. Then you can simply pop the head of the queue in a thread.

[Karbon]

@Karbon: so you say, if several clients read simultaneously, it works. If several clients write simultaneously to the database, it may result in data loss? *shudder* Have to sleep about it, and will get back here on tuesday!

Well, sort of

SQLite locks the entire database during *any* write operation (any INSERT/UPDATE/DELETE query). While the lock time of one query might only be measured in milliseconds, during that time SQLite can't do any other write operations and *should* return SQLITE_BUSY when you issue the query commands. Unfortunately I've seen plenty of cases when that doesn't happen or it happens for far too long. That's mostly due to network file systems when you're accessing the SQLite database on a shared drive (like SMB).

The TCP/IP server idea is a good one for SQLite but you need to write a crazy amount of error checking into it and build a queue system so that write queries are buffered and performed when they can be (when the database isn't busy). Unfortunately there are many drawbacks with that and it just doesn't scale very well.

I love SQLite but you have to be very cognizant of what it was designed for and when it is a good choice.

Good luck!

[jamirokwai]

Hi,

after a few days of delay, I present you the pb-include to access ServQLite 0.0.5 and a testclient...
Not much commented, but a beginning... Just use the standard Port and IP-Address to test...
You may use the .zip from the first post. Start ServQLite prior this testclient.
The function-names are similar to those of PureBasics-SQLite-functions.

If you are on a second computer in the same network, please adjust the IP here to the IP of the computer running ServQLite.
Keep in mind, that the blob-functions are experimental at this moment, and there is no internal buffering... Two clients may interfere => possible data-loss.

Have fun

Code: Select all

EnableExplicit

#TimeOut               = 2500 ; TimeOut für jede Anfrage ist 1000 ms
Global ServQL_BuffSize = 1000 ; Buffergröße

Global ServQL_Port     = 6832 ; Standard-Port
Global ConnectionID    = 0    ; Connection-ID
Global ServQL_Server$  = "127.0.0.1" ; ServQLite-Server-Adresse
Global DatabaseString$ = ""   ; Puffer für eine Abfrage
Global DatabaseInfo$   = ""   ; Puffer für Namen, Größe und Typ der Tabelle
Global Use_ServQLite   = 0    ; 1 = Server benutzen
Global ServerQL_Ready     = 0 ; 0 = nur Lokal, 1 = Netzwerk nicht verfügbar, nur lokal, 2 = Netzwerk verfügbar, Server kann gesucht werden
Global ServerQL_LastError = 0 ; Letzter Fehler
Global *ServerQL_BlobBuff     ; Puffer für den Blob
Global ServerQL_BlobSize = 0  ; Größe des erhaltenen Puffers

Enumeration
 #ServerQL_Lokal
 #ServerQL_Lokal_No_Network
 #ServerQL_Network
EndEnumeration

Enumeration
 #ServerQL_OK
 #ServerQL_DB_Created
 #ServerQL_DB_Exists
 #ServerQL_DB_Not_Found
 #ServerQL_DB_Opened
 #ServerQL_DB_Creation_Error
 #ServerQL_DB_Opened_Error
 #ServerQL_DB_Query_Success
 #ServerQL_DB_Query_Fault
 #ServerQL_Last_Row
 #ServerQL_First_Row
 #ServerQL_No_Network
 #ServerQL_Mem_Error
 #ServerQL_Port_Error
EndEnumeration

Procedure.s ServQL_WaitClientEvent(Connection, TimeOut = #TimeOut) ; - fehler zurückgeben, wenn TimeOut!
 Define *Buffer, TimeOutBeginn.l, Exit.l, CEvent.l, Temp$
 
 *Buffer       = AllocateMemory(ServQL_BuffSize)
 TimeOutBeginn = 0
 Exit          = 0
 Repeat
  TimeOutBeginn + 10
  CEvent    = NetworkClientEvent(Connection)
  If CEvent = #PB_NetworkEvent_Data
   ReceiveNetworkData(Connection, *Buffer, ServQL_BuffSize)
   Temp$ = PeekS(*Buffer)
   Debug "server said: " + Temp$
   Exit = 1
  EndIf
  If TimeOutBeginn >= TimeOut
   Debug "TIMEOUT!"
   Exit = 1
  EndIf
  Delay(10)
 Until Exit = 1
 FreeMemory(*Buffer)
 ProcedureReturn Temp$
EndProcedure

Procedure.l ServQL_Connect_Server(IP$, Port)
 Define Antwort.l
 
 If Use_ServQLite = 1
  Antwort = OpenNetworkConnection(IP$, Port)
  If Antwort = 0
   Use_ServQLite = 0
  EndIf
  ProcedureReturn Antwort
 Else
  ProcedureReturn 1
 EndIf
EndProcedure

Procedure.s ServQL_DatabaseError(Connection)
 If Use_ServQLite = 1
  SendNetworkString(Connection, "DatabaseError")
  ProcedureReturn ServQL_WaitClientEvent(Connection)
 Else
  ProcedureReturn DatabaseError()
 EndIf
EndProcedure

Procedure.l ServQL_OpenDatabase(Connection, Database$, User$, Password$, Make = 1)
 Define DBFile.l
 
 If Use_ServQLite = 1
  If Make = 1
   SendNetworkString(Connection, "CreateDatabase" + "°" + Database$ + "°" + User$ + "°" + Password$)
   ServQL_WaitClientEvent(Connection) 
  EndIf
  SendNetworkString(Connection, "OpenDatabase" + "°" + Database$ + "°" + User$ + "°" + Password$)
  ServQL_WaitClientEvent(Connection)
  ProcedureReturn Connection
 EndIf
 If Make = 1
  DBFile = ReadFile(#PB_Any,  Database$)
  If DBFile <> 0
   If IsFile(DBFile)
    CloseFile(DBFile)
   EndIf
   ServerQL_LastError = #ServerQL_DB_Exists
  Else
   DBFile = CreateFile(#PB_Any,  Database$)
   If IsFile(DBFile)
    CloseFile(DBFile)
   EndIf
   If DBFile <> 0
    ServerQL_LastError = #ServerQL_DB_Created
   Else
    ServerQL_LastError = #ServerQL_DB_Creation_Error
   EndIf  
  EndIf
  ProcedureReturn OpenDatabase(Connection, Database$, User$, Password$)
 EndIf 
EndProcedure

Procedure.l ServQL_CloseDatabase(Connection)
 If Use_ServQLite = 1
  SendNetworkString(Connection, "CloseDatabase")
  ProcedureReturn Val(ServQL_WaitClientEvent(Connection))
 Else
  ProcedureReturn CloseDatabase(Connection)
 EndIf 
EndProcedure

Procedure.l ServQL_DatabaseQuery(Connection, Request$)
 Define TemP$
 
 If Use_ServQLite = 1
  If FindString(UCase(Request$), "SELECT", 0) <> 0
   DatabaseInfo$ = ""
  EndIf
  SendNetworkString(Connection, "DatabaseQuery" + "°" + Request$)
  Temp$ = ServQL_WaitClientEvent(Connection)
  If StringField(Temp$, 2, "°") <> "0"
;    Debug temp$
   If FindString(Temp$, "Time-Out", 0)
    ProcedureReturn -1
   EndIf
   DatabaseInfo$ = Temp$
   ProcedureReturn 1
  EndIf
 Else
  ProcedureReturn DatabaseQuery(Connection, Request$)
 EndIf
EndProcedure

Procedure.s ServQL_DatabaseColumnName(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn StringField(DatabaseInfo$, 4 + Column * 3, "°")
 Else
  ProcedureReturn DatabaseColumnName(Connection, Column)
 EndIf
EndProcedure

Procedure.l ServQL_DatabaseColumnSize(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn Val(StringField(DatabaseInfo$, 5 + Column * 3, "°"))
 Else
  ProcedureReturn DatabaseColumnSize(Connection, Column)
 EndIf
EndProcedure

Procedure.l ServQL_DatabaseColumnType(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn Val(StringField(DatabaseInfo$, 6 + Column * 3, "°"))
 Else
  ProcedureReturn DatabaseColumnType(Connection, Column)
 EndIf
EndProcedure

Procedure.l ServQL_DatabaseColums(Connection)
 If Use_ServQLite = 1
  ProcedureReturn Val(StringField(DatabaseInfo$, 3, "°"))
 Else
  ProcedureReturn DatabaseColumns(Connection)
 EndIf
EndProcedure

Procedure.l ServQL_NextDatabaseRow(Connection)
 Define Temp$
 
 If Use_ServQLite = 1
  DataBaseString$ = ""
  SendNetworkString(Connection, "NextDatabaseRow")
  Temp$ = ServQL_WaitClientEvent(Connection)
  If StringField(Temp$, 2, "°") <> "0"
   ProcedureReturn 1
  EndIf
 Else
  ProcedureReturn NextDatabaseRow(Connection)
 EndIf
EndProcedure

Procedure.l ServQL_PreviousDatabaseRow(Connection)
 If Use_ServQLite = 1
  DataBaseString$ = ""
  SendNetworkString(Connection, "PreviousDatabaseRow")
  ServQL_WaitClientEvent(Connection) 
 Else
  ProcedureReturn PreviousDatabaseRow(Connection)
 EndIf
EndProcedure

Procedure.l ServQL_DatabaseUpdate(Connection, Request$)
 If Use_ServQLite = 1
  SendNetworkString(Connection, "DatabaseUpdate" + "°" + Request$)
  ProcedureReturn Val(ServQL_WaitClientEvent(Connection))
 Else
  ProcedureReturn DatabaseUpdate(Connection, Request$)
 EndIf
EndProcedure

Procedure.l ServQL_CloseNetworkConnection(Connection)
 If Use_ServQLite = 1
  CloseNetworkConnection(Connection)
 EndIf
EndProcedure

Procedure.s ServQL_RefreshDatabaseString(Connection)
 If DataBaseString$ = ""
  SendNetworkString(Connection, "GetDatabaseString")
  DataBaseString$ = ServQL_WaitClientEvent(Connection)
  DataBaseString$ = Right(DataBaseString$, Len(DataBaseString$) - FindString(DataBaseString$, "°", 0))
 EndIf
 ProcedureReturn DataBasestring$
EndProcedure

Procedure.s ServQL_GetDatabaseString(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn StringField(ServQL_RefreshDatabaseString(Connection), Column + 1, "°")
 Else
  ProcedureReturn GetDatabaseString(Connection, Column)
 EndIf
EndProcedure

Procedure.l ServQL_GetDatabaseLong(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn Val(StringField(ServQL_RefreshDatabaseString(Connection), Column + 1, "°"))
 Else
  ProcedureReturn GetDatabaseLong(Connection, Column)
 EndIf
EndProcedure

Procedure.q ServQL_GetDatabaseQuad(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn ValF(StringField(ServQL_RefreshDatabaseString(Connection), Column, "°"))
 Else
  ProcedureReturn GetDatabaseQuad(Connection, Column)
 EndIf
EndProcedure

Procedure.f ServQL_GetDatabaseFloat(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn ValF(StringField(ServQL_RefreshDatabaseString(Connection), Column, "°"))
 Else
  ProcedureReturn GetDatabaseFloat(Connection, Column)
 EndIf
EndProcedure

Procedure.d ServQL_GetDatabaseDouble(Connection, Column)
 If Use_ServQLite = 1
  ProcedureReturn ValD(StringField(ServQL_RefreshDatabaseString(Connection), Column, "°"))
 Else
  ProcedureReturn GetDatabaseDouble(Connection, Column)
 EndIf
EndProcedure

Procedure.l ServQL_SetDatabaseBlob(Connection, Statementindex, *Buffer, Bufferlength)
 If Use_ServQLite = 1
  SendNetworkString(Connection, "SetDatabaseBlob°" + Str(Statementindex) + "°" + Str(Bufferlength))
  ServQL_WaitClientEvent(Connection)    ; Abfrage, ob ich schicken kann
  SendNetworkData(Connection, *Buffer, Bufferlength)
  ServQL_WaitClientEvent(Connection)    ; Abfrage, ob alles in Ordnung
 Else
  ProcedureReturn SetDatabaseBlob(Connection, Statementindex, *Buffer, Bufferlength)
 EndIf
EndProcedure

Procedure.l ServQL_GetDatabaseBlob(Connection, Column)
Define Temp$

 If Use_ServQLite = 1
  SendNetworkString(Connection, "GetDatabaseBlob°" + Str(Column))
  Temp$ = ServQL_WaitClientEvent(Connection)
  ServerQL_BlobSize = Val(StringField(Temp$, 3, "°"))
 Else
  ServerQL_BlobSize = DatabaseColumnSize(Connection, Column)
 EndIf

 If *ServerQL_BlobBuff <> 0
  FreeMemory(*ServerQL_BlobBuff)
 EndIf
 *ServerQL_BlobBuff = AllocateMemory(ServerQL_BlobSize)
 
 If Use_ServQLite = 1
  SendNetworkString(Connection, "GetDatabaseBlobRec") 
  ReceiveNetworkData(Connection, *ServerQL_BlobBuff, ServerQL_BlobSize)
 Else
   GetDatabaseBlob(Connection, Column, *ServerQL_BlobBuff, ServerQL_BlobSize)
  ProcedureReturn 1
 EndIf
EndProcedure
 
Procedure.l ServQL_FinishDatabaseQuery(Connection)
 If Use_ServQLite = 1
  SendNetworkString(Connection, "FinishDatabaseQuery")
  ProcedureReturn Val(ServQL_WaitClientEvent(Connection)) 
 Else
  ProcedureReturn FinishDatabaseQuery(Connection)
 EndIf
EndProcedure

Procedure.l ServQL_Quit_Server(Connection)
 If Use_ServQLite = 1
  SendNetworkString(Connection, "Quit Server")
  ServQL_WaitClientEvent(Connection)
 EndIf
EndProcedure

And a simple Client

Code: Select all

Define x$

If InitNetwork() = 0
 ServerQL_Ready =  #ServerQL_Lokal_No_Network
Else
 ServerQL_Ready =  #ServerQL_Network
EndIf

use_servqlite = 1

ConnectionID = ServQL_Connect_Server(ServQL_Server$, ServQL_Port)
ServQL_OpenDatabase(ConnectionID, "test.sqlite", "", "")

If ConnectionID = 0
  MessageRequester("Error", "Can't connect to Server")
  End
EndIf

ServQL_DatabaseUpdate(ConnectionID, "CREATE TABLE food (name CHAR(50), amount INT)")
ServQL_DatabaseUpdate(ConnectionID, "INSERT INTO food (name, amount) VALUES ('apple', '10')")
ServQL_DatabaseUpdate(ConnectionID, "INSERT INTO food (name, amount) VALUES ('pear', '5')")
ServQL_DatabaseUpdate(ConnectionID, "INSERT INTO food (name, amount) VALUES ('banana', '20')")
ServQL_DatabaseUpdate(ConnectionID, "INSERT INTO food (name, amount) VALUES ('coconuts', '3')")

MessageRequester("Info", "Created a table food with 10 apples, 5 pears, 20 bananas, 3 coconuts")

ServQL_DatabaseUpdate(ConnectionID, "UPDATE food SET amount = '12' WHERE name = 'coconuts'")
ServQL_DatabaseUpdate(ConnectionID, "DELETE FROM food WHERE name = 'banana'")

If ServQL_DatabaseQuery(ConnectionID, "SELECT * FROM food WHERE amount > 7") = -1
 Debug "you've been kicked"
EndIf

MessageRequester("Info", "Added 9 coconuts to a total of 12 and remove the whole bananas.")

x$ = "We looked for food of more than 7 pieces each..." + #CR$ + "There is/are "
While ServQL_NextDatabaseRow(ConnectionID)
 x$ + Str(ServQL_GetDatabaseLong(ConnectionID, 1)) + " "
 x$ + ServQL_GetDatabaseString(ConnectionID, 0) + "(s) and "
Wend
x$ + " that's it..."

MessageRequester("Info", x$)

ServQL_FinishDatabaseQuery(ConnectionID)

ServQL_Quit_Server(ConnectionID)
ServQL_CloseNetworkConnection(ConnectionID)

[jamirokwai]

You will find the full Source-Code of ServQLite 0.0.6 here:
http://www.purebasic.fr/english/viewtop ... 03&start=0

[loadstone]

very good~~~

give you a link：
http://www.planetsquires.com/sqlite_client_server.htm

[infratec]

@loadstone

the original site is:
http://www.sqlitening.com/support/index.php

The disadvantage:

not for Linux, not for MAC

[loadstone]

Expect your new edition ~~~~~