NS3-DB - an open source NodeJS+SQLite based server

Developed or developing a new product in PureBasic? Tell the world about it.
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

NS3-DB - an open source NodeJS+SQLite based server

Post by the.weavster »

Details and source code available here

Here's a PB module for the client side (mod-ns3.pb):

Code: Select all

;
;THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
;EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
;OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
;IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
;CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
;TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
;OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
;
;Apart from the above use it however you like ;-)

DeclareModule NS3
  #NS3_BEGIN       = 0
  #NS3_COMMIT      = 1
  #NS3_ROLLBACK    = 2
  #NS3_SELECT      = 3
  #NS3_EXECUTE     = 4
  #NS3_EXECUTEMANY = 5
  
  Declare Bail(cnx.i,jsReq.i,jsRes.i,withRollback=#False)
  Declare BindBoolean(js.i,n.i)
  Declare BindDouble(js.i,n.d)
  Declare BindFloat(js.i,n.f)
  Declare BindInteger(js.i,n.i)
  Declare BindNull(js.i)
  Declare BindQuad(js.i,n.q)
  Declare BindString(js.i,s$)
  Declare Close(cnx.i)
  Declare.i Connect(host$,port)
  Declare.s ErrorText(js.i)
  Declare.i HasError(js.i)
  Declare.i NewRequest(method.i,sql$="",ejs=0)
  Declare.i SubmitRequest(cnx.i,js.i,timeoutInterval.i=10000)
EndDeclareModule

Module NS3
  
  ;_send #True as the fourth parameter to save having to
  ; call rollback as a seperate exercise
  Procedure Bail(cnx.i,jsReq.i,jsRes.i,withRollback=#False)
    Protected txt$ = ""
    If IsJSON(jsReq) : FreeJSON(jsReq) : EndIf
    If IsJSON(jsRes)
      txt$ = ErrorText(jsRes)
      FreeJSON(jsRes)
    EndIf
    If withRollback
      Protected jsRequest = NewRequest(#NS3_ROLLBACK)
      Protected jsResponse = SubmitRequest(cnx,jsRequest)
      If HasError(jsResponse)
        If txt$ <> "" : txt$ = txt$ + " | " : EndIf
        txt$ = txt$ + ErrorText(jsResponse)
      EndIf
      FreeJSON(jsRequest)
      FreeJSON(jsResponse)
    EndIf
    Debug(txt$)
    ;MessageRequester("Error",txt$,0)
  EndProcedure
  
  ; you wont call this procedure directly
  Procedure.s NonDatabaseError(msg$)
    Protected js.i = CreateJSON(#PB_Any)
    If Not js : ProcedureReturn "" : EndIf
    Protected req = SetJSONObject(JSONValue(js))
    SetJSONNull(AddJSONMember(req,"result"))
    nErr = SetJSONObject(AddJSONMember(req,"error"))
    SetJSONString(AddJSONMember(nErr,"code"),"")
    SetJSONString(AddJSONMember(nErr,"message"),msg$)
    SetJSONInteger(AddJSONMember(nErr,"origin"),0)
    ProcedureReturn ComposeJSON(js)    
  EndProcedure
  
  ; you wont call this procedure directly
  Procedure.i GetNewParamForBinding(js.i)
    Protected idx = GetJSONMember(JSONValue(js),"params")
    Protected itm = AddJSONElement(idx)
    ProcedureReturn itm
  EndProcedure
  
  Procedure BindBoolean(js.i,n.i)
    Protected idx = GetNewParamForBinding(js)
    SetJSONBoolean(idx, n)
  EndProcedure
  
  Procedure BindDouble(js.i,n.d)
    Protected idx = GetNewParamForBinding(js)
    SetJSONDouble(idx, n)  
  EndProcedure
  
  Procedure BindFloat(js.i,n.f)
    Protected idx = GetNewParamForBinding(js)
    SetJSONFloat(idx, n)  
  EndProcedure
  
  Procedure BindInteger(js.i,n.i)
    Protected idx = GetNewParamForBinding(js)
    SetJSONInteger(idx, n)  
  EndProcedure
  
  Procedure BindNull(js.i)
    Protected idx = GetNewParamForBinding(js)
    SetJSONNull(idx)  
  EndProcedure
  
  Procedure BindQuad(js.i,n.q)
    Protected idx = GetNewParamForBinding(js)
    SetJSONQuad(idx, n)  
  EndProcedure
  
  Procedure BindString(js.i,s$)
    Protected idx = GetNewParamForBinding(js)
    SetJSONString(idx, s$)  
  EndProcedure
  
  Procedure Close(cnx.i)
    CloseNetworkConnection(cnx)
  EndProcedure
  
  Procedure.i Connect(host$,port)
    ProcedureReturn OpenNetworkConnection(host$, port)
  EndProcedure
  
  Procedure.s ErrorText(js.i)
    If js = #False
      ProcedureReturn "Network error"
    EndIf
    Protected idx = GetJSONMember(JSONValue(js),"error")
    Protected nMsg = GetJSONMember(idx,"message")
    Protected msg.s = GetJSONString(nMsg)
    ProcedureReturn msg
  EndProcedure
  
  Procedure.i HasError(js.i)
    Protected idx = GetJSONMember(JSONValue(js),"error")
    If JSONType(idx) = #PB_JSON_Null
      ProcedureReturn #False
    EndIf
    ProcedureReturn #True
  EndProcedure
  
  ; method options are begin,commit,rollback,execute,executeMany and select.
  ; the ejs parameter just helps with housekeeping, you can free your last
  ; request at the same time as starting a new one
  Procedure.i NewRequest(method.i,sql$="",ejs=0)
    If ejs > 0 And IsJSON(ejs) : FreeJSON(ejs) : EndIf
    Protected js.i = CreateJSON(#PB_Any)
    If Not js : ProcedureReturn #False : EndIf
    Protected req = SetJSONObject(JSONValue(js))
    SetJSONInteger(AddJSONMember(req, "method"), method.i)
    SetJSONString(AddJSONMember(req, "sql"), sql$)
    Protected p.i = AddJSONMember(req, "params")
    SetJSONArray(p)
    ProcedureReturn js
  EndProcedure
  
  Procedure.i SubmitRequest(cnx.i,js.i,timeoutInterval.i=10000)
    Protected req$ = ComposeJSON(js)
    Protected responseChunk$
    Protected responseText$
    Protected nTimeoutStart.i
    #bufferLength = 1024
    Protected *buffer = AllocateMemory(#bufferLength)
    Protected res.i
    If SendNetworkString(cnx,req$,#PB_UTF8) = 0
      FreeMemory(*buffer)
      ProcedureReturn ParseJSON(#PB_Any,NonDatabaseError("REQUEST_FAILED:Failed to send request"))
    EndIf
    nTimeoutStart = ElapsedMilliseconds() + timeoutInterval
    Repeat
      Select NetworkClientEvent(cnx)
        Case #PB_NetworkEvent_None
          If nTimeoutStart < ElapsedMilliseconds()
            responseText$ = NonDatabaseError("TIMEOUT:The request timed out")
            Break
          EndIf
        Case #PB_NetworkEvent_Data
          res = ReceiveNetworkData(cnx,*buffer,#bufferLength)
          If res = -1
            responseText$ = NonDatabaseError("DISCONNECT:The connection to the server was lost")
            Break
          EndIf
          responseChunk$ = PeekS(*buffer,res,#PB_UTF8)
          responseText$ + responseChunk$
          If PeekA(*buffer + res - 1) = '}' And res < #bufferLength
            Break
          EndIf
        Case #PB_NetworkEvent_Disconnect
          responseText$ = NonDatabaseError("DISCONNECT:The connection to the server was lost")
          Break
      EndSelect
    ForEver
    FreeMemory(*buffer)
    ProcedureReturn ParseJSON(#PB_Any,responseText$)
  EndProcedure
  
EndModule
And an example of use:

Code: Select all

EnableExplicit

If InitNetwork() = 0 : End : EndIf
XIncludeFile "mod-ns3.pb"

Define h$ = "127.0.0.1"
Define pt = 2015

Define cnx = NS3::Connect(h$,pt)
If Not cnx
  Debug "Failed to connect"
  End
EndIf

Define jsRequest  = NS3::NewRequest(NS3::#NS3_BEGIN)
Define jsResponse = NS3::SubmitRequest(cnx,jsRequest)
If NS3::HasError(jsResponse)
  NS3::Bail(cnx,jsRequest,jsResponse)
  End
EndIf

jsRequest  = NS3::NewRequest(NS3::#NS3_EXECUTE,"DELETE FROM t1",jsRequest)
jsResponse = NS3::SubmitRequest(cnx,jsRequest)
If NS3::HasError(jsResponse)
  NS3::Bail(cnx,jsRequest,jsResponse,#True)
  End
EndIf

jsRequest  = NS3::NewRequest(NS3::#NS3_COMMIT,"",jsRequest)
jsResponse = NS3::SubmitRequest(cnx,jsRequest)
If NS3::HasError(jsResponse)
  NS3::Bail(cnx,jsRequest,jsResponse)
  End
EndIf

jsRequest  = NS3::NewRequest(NS3::#NS3_BEGIN,"",jsRequest)
jsResponse = NS3::SubmitRequest(cnx,jsRequest)
If NS3::HasError(jsResponse)
  NS3::Bail(cnx,jsRequest,jsResponse)
  End
EndIf

Define cntr = 0
While cntr < 1000
  jsRequest = NS3::NewRequest(NS3::#NS3_EXECUTE,"INSERT INTO t1 (f1,f2) VALUES (?,?)",jsRequest)
  NS3::BindInteger(jsRequest,cntr)
  NS3::BindInteger(jsRequest,cntr)
  jsResponse = NS3::SubmitRequest(cnx,jsRequest)
  If NS3::HasError(jsResponse)
    NS3::Bail(cnx,jsRequest,jsResponse,#True)
    End
  Else
    If cntr = 0 : Debug ComposeJSON(jsResponse) : EndIf
  EndIf
  cntr = cntr + 1
Wend

jsRequest  = NS3::NewRequest(NS3::#NS3_COMMIT,"",jsRequest)
jsResponse = NS3::SubmitRequest(cnx,jsRequest)
If NS3::HasError(jsResponse)
  NS3::Bail(cnx,jsRequest,jsResponse)
  End
EndIf

jsRequest = NS3::NewRequest(NS3::#NS3_SELECT,"SELECT * FROM t1 WHERE f1 <= ?",jsRequest)
NS3::BindInteger(jsRequest,500)
jsResponse = NS3::SubmitRequest(cnx,jsRequest)
If NS3::HasError(jsResponse)
  NS3::Bail(cnx,jsRequest,jsResponse)
Else
  Debug ComposeJSON(jsResponse,#PB_JSON_PrettyPrint)
EndIf

NS3::Close(cnx)
  
End
Testing for both server and client so far has been minimal so watch out! :D
Last edited by the.weavster on Mon Oct 19, 2015 9:14 pm, edited 1 time in total.
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by thanos »

Amazing idea! :shock:
Your server is amazingly fast!
Inserting 25.000 records with the debugger on it took only 41 seconds!
If someone estimate that there are only two commercial high priced SQLite servers (ValentinaDB and cubeSQL) and one public domain (SQLitening) and the need for such a product, I strongly believe that if you improve it, it might be a great potential for you.
Best regards and thank you very much for share it.

Thanos
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by the.weavster »

I'm glad you like it, Thanos
thanos wrote:Your server is amazingly fast!
Inserting 25.000 records with the debugger on it took only 41 seconds!
If there's any possibility you could use execMany I'd bet you could slash that time to < 5 seconds.

thanos wrote:I strongly believe that if you improve it, it might be a great potential for you.
I'll continue to refine it if it proves popular but it's < 200 lines of JS code and it's provided under the MIT license so anybody's free to do what they like with it. What I've shared today is very much a first draft so I don't doubt they'll be an issue or two to be resolved.
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by the.weavster »

The client module above has now had a tweak thanks to a tip from a helpful forum member.
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by thanos »

Keep up the good work!
The idea in amazing!
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by thanos »

@the.weavster
Hello.
I would like to "play" with NodeJs.
Is there any posibility to find your code?
The link https://oneledger.co.uk/ns3db.html is dead.
Regards
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by the.weavster »

Hi thanos,
Sorry I didn't spot your post sooner.

You can now find the code here
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by thanos »

the.weavster wrote:Hi thanos,
Sorry I didn't spot your post sooner.

You can now find the code here
Thank you very much for your response!
Regards

Thanos
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
loulou2522
Enthusiast
Enthusiast
Posts: 495
Joined: Tue Oct 14, 2014 12:09 pm

Re: NS3-DB - an open source NodeJS+SQLite based server

Post by loulou2522 »

Hi,
I would want to implement your solution but i thinks it fault me something. I Try the two programm and the server says me fault to connect
Thanks
Post Reply