PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

multi-user SQlite (cubeSQL) with security/performance

https://www.purebasic.fr/english/viewtopic.php?t=48182

Page 1 of 1

multi-user SQlite (cubeSQL) with security/performance

Posted: Fri Nov 11, 2011 11:32 am
by mskuma
I had an interest to maintain a small, secure database to be shared amongst a small user base. I came across cubeSQL which seems to be promising. Some main points about it (paraphrased from the web site):

- SQlite-based + multi-user
- support for encrypted db + encrypted client/server channel
- multi-platform server (Mac/Linux/Windows) with high performance & stability
- reasonable price (3 connection server is free)
- good developer support

After initially connecting to the server via JSON, I really wanted the encrypted channel + better performance for large uploads, so I contacted the developer and I could convince him to provide a DLL interface.

I provide the DLL interface code I've put together so far, which includes a couple of simple demos to show the core functions. The DLL is now part of the latest server download, and there is an API ref in there also for more tips. BTW I'm not affiliated with the developer - just sharing something that might be useful. Any updates or improvements to the below is welcome, please post it back to this topic.

Code: Select all

; cubesql interface code
; by mskuma (Nov 2011)
; refer to: http://www.sqlabs.com/cubesql.php

; ---------
; constants 
; ---------

#kTRUE = #True
#kFALSE = #False

; default values

#kDEFAULT_PORT = 4430
#kDEFAULT_TIMEOUT =	12
#kNOERR	= 0
#kERR	= -1

; client side errors

#kMEMORY_ERROR = -2
#kPARAMETER_ERROR	= -3
#kPROTOCOL_ERROR = -4
#kZLIB_ERROR = -5

; encryption flags used in cubesql_connect

#kAESNONE = 0
#kAES128 = 2
#kAES192 = 3
#kAES256 = 4

; flag used in cubesql_cursor_getfield

#kCUBESQL_COLNAME = 0
#kCUBESQL_CURROW = -1
#kCUBESQL_COLTABLE = -2
#kCUBESQL_ROWID = -666

; flag used in cubesql_cursor_seek

#kCUBESQL_SEEKNEXT = -2
#kCUBESQL_SEEKFIRST = -3
#kCUBESQL_SEEKLAST = -4
#kCUBESQL_SEEKPREV = -5

; column types to specify in the cubesql_bind command (coltype)

#kBIND_INTEGER = 1
#kBIND_DOUBLE = 2
#kBIND_TEXT = 3
#kBIND_BLOB = 4
#kBIND_NULL = 5
#kBIND_INT64 = 8
#kBIND_ZEROBLOB = 9

; column types coming from the server

Enumeration
  #TYPE_None		= 0
	#TYPE_Integer	= 1
	#TYPE_Float		= 2
	#TYPE_Text		= 3
	#TYPE_Blob		= 4
	#TYPE_Boolean	= 5
	#TYPE_Date		= 6
	#TYPE_Time		= 7
	#TYPE_Timestamp	= 8
	#TYPE_Currency	= 9
EndEnumeration

; -------------------
; function prototypes
; -------------------

PrototypeC cubesql_connect(db, host.s, port, username.s, password.s, timeout, encryptionMode)  ; passed-in db must be the address of the *db ptr
PrototypeC cubesql_disconnect(*db, gracefullyFlag)
PrototypeC cubesql_select(*db, sql.s, unused) ; returns pointer to a cursor structure
PrototypeC cubesql_execute(*db, sql.s) ; returns int (success/failure)
PrototypeC cubesql_commit(*db)
PrototypeC cubesql_rollback(*db)
PrototypeC cubesql_bind(*db, sql.s, *colvalue, *colsize, *coltype, ncols)
PrototypeC cubesql_ping(*db)
PrototypeC cubesql_errcode(*db)
PrototypeC cubesql_errmsg(*db)  ; returns pointer to a string

PrototypeC cubesql_cursor_numrows(*cursor)
PrototypeC cubesql_cursor_numcolumns(*cursor)
PrototypeC cubesql_cursor_currentrow(*cursor)
PrototypeC cubesql_cursor_seek(*cursor, index)
PrototypeC cubesql_cursor_iseof(*cursor)
PrototypeC cubesql_cursor_columntype(*cursor, index)
PrototypeC cubesql_cursor_field(*cursor, row, column, *len)
PrototypeC cubesql_cursor_cstring(*cursor, row, column)  ; returns a string pointer
PrototypeC cubesql_cursor_cstring_static(*cursor, row, column, *static_buffer, bufferlen) ; returns a string pointer
PrototypeC cubesql_cursor_free(*cursor)

PrototypeC cubesql_vmprepare(*db, sql.s) ; returns pointer to a vm structure
PrototypeC cubesql_vmbind_int(*vm, index, intValue) ; returns int (success/failure)
PrototypeC cubesql_vmbind_double(*vm, index, doubleValue) ; returns int (success/failure)
PrototypeC cubesql_vmbind_text(*vm, index, *value, len) ; returns int (success/failure)
PrototypeC cubesql_vmbind_blob(*vm, index, *value, len) ; returns int (success/failure)
PrototypeC cubesql_vmbind_null(*vm, index) ; returns int (success/failure)
PrototypeC cubesql_vmbind_int64(*vm, index, int64Value) ; returns int (success/failure)
PrototypeC cubesql_vmbind_zeroblob(*vm, index, len) ; returns int (success/failure)
PrototypeC cubesql_vmexecute(*vm) ; returns int (success/failure)
PrototypeC cubesql_vmselect(*vm)  ; returns pointer to a cursor structure
PrototypeC cubesql_vmclose(*vm)   ; returns int (success/failure)

; global function pointers

Global cubesql_connect.cubesql_connect
Global cubesql_disconnect.cubesql_disconnect
Global cubesql_select.cubesql_select
Global cubesql_execute.cubesql_execute
Global cubesql_commit.cubesql_commit
Global cubesql_rollback.cubesql_rollback
Global cubesql_bind.cubesql_bind
Global cubesql_ping.cubesql_ping
Global cubesql_errcode.cubesql_errcode
Global cubesql_errmsg.cubesql_errmsg

Global cubesql_cursor_numrows.cubesql_cursor_numrows
Global cubesql_cursor_numcolumns.cubesql_cursor_numcolumns
Global cubesql_cursor_currentrow.cubesql_cursor_currentrow
Global cubesql_cursor_seek.cubesql_cursor_seek
Global cubesql_cursor_iseof.cubesql_cursor_iseof
Global cubesql_cursor_columntype.cubesql_cursor_columntype
Global cubesql_cursor_field.cubesql_cursor_field
Global cubesql_cursor_cstring.cubesql_cursor_cstring
Global cubesql_cursor_cstring_static.cubesql_cursor_cstring_static
Global cubesql_cursor_free.cubesql_cursor_free

Global cubesql_vmprepare.cubesql_vmprepare
Global cubesql_vmbind_int.cubesql_vmbind_int
Global cubesql_vmbind_double.cubesql_vmbind_double
Global cubesql_vmbind_text.cubesql_vmbind_text
Global cubesql_vmbind_blob.cubesql_vmbind_blob
Global cubesql_vmbind_null.cubesql_vmbind_null
Global cubesql_vmbind_int64.cubesql_vmbind_int64
Global cubesql_vmbind_zeroblob.cubesql_vmbind_zeroblob
Global cubesql_vmexecute.cubesql_vmexecute
Global cubesql_vmselect.cubesql_vmselect
Global cubesql_vmclose.cubesql_vmclose

; ---------------
; library handler
; ---------------

hDLL = OpenLibrary(#PB_Any, "cubesql.dll")

If hDLL <> 0
  
  cubesql_connect = GetFunction(hDLL, "cubesql_connect")
  cubesql_disconnect = GetFunction(hDLL, "cubesql_disconnect")
  cubesql_select  = GetFunction(hDLL, "cubesql_select")
  cubesql_execute = GetFunction(hDLL, "cubesql_execute")
  cubesql_commit = GetFunction(hDLL, "cubesql_commit")
  cubesql_rollback = GetFunction(hDLL, "cubesql_rollback")
  cubesql_bind = GetFunction(hDLL, "cubesql_bind")
  cubesql_ping = GetFunction(hDLL, "cubesql_ping")
  cubesql_errcode = GetFunction(hDLL, "cubesql_errcode")
  cubesql_errmsg = GetFunction(hDLL, "cubesql_errmsg")
  
  cubesql_cursor_numrows = GetFunction(hDLL, "cubesql_cursor_numrows")
  cubesql_cursor_numcolumns = GetFunction(hDLL, "cubesql_cursor_numcolumns")
  cubesql_cursor_currentrow = GetFunction(hDLL, "cubesql_cursor_currentrow")
  cubesql_cursor_seek = GetFunction(hDLL, "cubesql_cursor_seek")
  cubesql_cursor_iseof = GetFunction(hDLL, "cubesql_cursor_iseof")
  cubesql_cursor_columntype = GetFunction(hDLL, "cubesql_cursor_columntype")  
  cubesql_cursor_field = GetFunction(hDLL, "cubesql_cursor_field") 
  cubesql_cursor_cstring = GetFunction(hDLL, "cubesql_cursor_cstring")
  cubesql_cursor_cstring_static = GetFunction(hDLL, "cubesql_cursor_cstring_static")  
  cubesql_cursor_free = GetFunction(hDLL, "cubesql_cursor_free") 
  
  cubesql_vmprepare = GetFunction(hDLL, "cubesql_vmprepare")
  cubesql_vmbind_int = GetFunction(hDLL, "cubesql_vmbind_int")
  cubesql_vmbind_double = GetFunction(hDLL, "cubesql_vmbind_double")
  cubesql_vmbind_text = GetFunction(hDLL, "cubesql_vmbind_text")
  cubesql_vmbind_blob = GetFunction(hDLL, "cubesql_vmbind_blob")
  cubesql_vmbind_null = GetFunction(hDLL, "cubesql_vmbind_null")  
  cubesql_vmbind_int64 = GetFunction(hDLL, "cubesql_vmbind_int64") 
  cubesql_vmbind_zeroblob = GetFunction(hDLL, "cubesql_vmbind_zeroblob")
  cubesql_vmexecute = GetFunction(hDLL, "cubesql_vmexecute")  
  cubesql_vmselect = GetFunction(hDLL, "cubesql_vmselect") 
  cubesql_vmclose = GetFunction(hDLL, "cubesql_vmclose") 
  
Else
  
  Debug "could not open dll lib"
  MessageRequester("Error", "cubesql.dll could not be found")
  End
  
EndIf

; ---------------
; demo procedures
; ---------------

Procedure ShowInfo()
  
  ; display server information - a good test that cubesql server is working
  ; demonstrates SQL select & cursor manipulation to display results
  
  *db = 0
  
  ; attempt connection with encryption
  
  rv = cubesql_connect(@*db, "localhost", #kDEFAULT_PORT, "admin", "admin", #kDEFAULT_TIMEOUT, #kAES256)

  If rv <> #kERR
    
    Debug "connect success"
      
    c = cubesql_select(*db, "show info;", 0)  ; run a select statement - returns a pointer representing a cursor (i.e. select result)
    
    If c
      
      ; collect some basic details about the cursor
      
      numRows = cubesql_cursor_numrows(c)
      numCols = cubesql_cursor_numcolumns(c)
      
      Debug "--- select details ---"
      Debug "num rows = " + Str(numRows) 
      Debug "num cols = " + Str(numCols) 
      
      ; print columns
      
      Debug "--- column details ---"
      
      For i = 1 To numCols
        strLen = 0
        stringPtr = cubesql_cursor_field(c, #kCUBESQL_COLNAME, i, @strLen);
        Debug "col = " + PeekS(stringPtr) + " (string length = " + Str(strLen) + ")"
      Next
      
      ; print data using the EOF property (safe for both server side and client side cursors)
      
      Debug "--- select data ---"      
            
      *buff = AllocateMemory(512)
      
      While cubesql_cursor_iseof(c) <> #kTRUE
        
        selectOutputStr.s = ""
        
        For i = 1 To numCols
          stringPtr = cubesql_cursor_cstring_static(c, #kCUBESQL_CURROW, i, *buff, 512)
          ;stringPtr = cubesql_cursor_cstring(c, #kCUBESQL_CURROW, i) ; this is an alternative method, not requiring pre-created buffer
          If stringPtr <> #Null
            selectOutputStr + PeekS(stringPtr) + ", "
          Else
            selectOutputStr + "<null>" + ", "
          EndIf
        Next
        
        ; print row output (trimmed due to clunky comma-separation method above :-)
        
        Debug "row: " + Left(selectOutputStr, Len(selectOutputStr)-2)
    		
    		cubesql_cursor_seek(c, #kCUBESQL_SEEKNEXT)
        
    	Wend
    	
    	cubesql_cursor_free(c)
    	
    	FreeMemory(*buff)
    	
    EndIf
    
    cubesql_disconnect(*db, #True)
    
    Debug "success!"
    
  Else
    
    ; show error in the event of inability to connect
      
    Debug "connect error: " + PeekS(cubesql_errmsg(*db)) + " (Error code: " + Str(cubesql_errcode(*db)) + ")"
    
    cubesql_disconnect(*db, #False)
    
  EndIf
  
EndProcedure


Procedure InsertDemo()
  
  ; insert some dummy records into a database
  ; demonstrates SQL functions via execute function
  
  *db = 0
  
  rv = cubesql_connect(@*db, "localhost", #kDEFAULT_PORT, "admin", "admin", #kDEFAULT_TIMEOUT, #kAES256)
  
  If rv <> #kERR
    
    Debug "connect success"
      
    ; select database
  
    rv = cubesql_execute(*db, "use database test;")
    
  	If rv <> #kERR 
  	  
  	  Debug "execute success (use database)"
  	  
  	  ; insert 200,000 records
    	
  	  For i = 1 To 100
   
    	  For j = 1 To 2000
    	    
    	    ; start insert transaction - by default the server is in transaction mode, so we have to commit it at the end (no need for begin)
    	    
      	  rv = cubesql_execute(*db, "insert into test values (" + Str(j) + ", " + Str(j+1) + ");")
      	  
      	  If rv = #kERR
      	    
      	    ; if error, display the issue
      	    
      	    errStr.s = PeekS(cubesql_errmsg(*db))
      	    Debug "error: " + errStr + " (Error code: " + Str(cubesql_errcode(*db)) + ") aborting.."
      	    
      	    cubesql_disconnect(*db, #True)
      	    
      	    ProcedureReturn #False
      	    
      	  EndIf  
      	  
      	Next
      	
      	Debug "finished iteration = " + Str(i)
      	
      Next
     	
    	Debug "finished insert"
    	
    	; commit the transaction
    	
    	cubesql_commit(*db)
    	
    	cubesql_disconnect(*db, #True)
    	
    Else
      
      ; could not connect
      
      Debug "execute error: " + PeekS(cubesql_errmsg(*db)) + " (Error code: " + Str(cubesql_errcode(*db)) + ") aborting.."
      ProcedureReturn #False
      
  	EndIf  
  	
  Else
    
    ; could not connect
    
    Debug "connect error: " + PeekS(cubesql_errmsg(*db)) + " (Error code: " + Str(cubesql_errcode(*db)) + ") aborting.."    
    ProcedureReturn #False
    
  EndIf
  
  Debug "success!" 
  ProcedureReturn #True
  	
EndProcedure

; ----
; main
; ----

ShowInfo()

InsertDemo()

Re: multi-user SQlite (cubeSQL) with security/performance

Posted: Fri Nov 11, 2011 8:50 pm
by Zach
Thanks for sharing :) is it Application embeddable like SQlite or do you need to run a server on the local machine?

Database encryption sounds temping for protecting important App/Game data from being fudged with, so it would be great if it is embeddable

Re: multi-user SQlite (cubeSQL) with security/performance

Posted: Fri Nov 11, 2011 10:50 pm
by mskuma
It's not embeddable - AFAIK it's meant to run as a service or background process.
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited