WIP NetDB (Database) Module: Need Feedback

[scurtyParticipant]

Taking influence from PHP and using the amazingness of MX2 I’ve updated my previous WIP SQLite3 Abstraction Layer into using JSON instead of a Stack< StringMap< String > >

Again still, the goal here is to execute very simple SQL Queries and get the Results from native MX2. (Now Using Json for the Return Values)

The following Code Sample shows how it’s done as of now. (Subject to change due to possible future enhancements)

Monkey

#Import "<std>" #Import "netdb" Using std.. Using netdb.. Function Main() Local DB:Database = New Database("newdb.sqlite3") If(DB.Open()) Local results:JsonObject = DB.Query( AppArgs()[1] ) ' Print First Row Local first_row := results.GetObject("1") ' Get the row object Print first_row.GetString("b") ' get the needed value from said row Print results.ToJson() ' print the whole json result as a string DB.Close() Endif End

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

#Import "<std>" #Import "netdb"   Using std.. Using netdb..   Function Main() Local DB:Database = New Database("newdb.sqlite3") If(DB.Open()) Local results:JsonObject = DB.Query( AppArgs()[1] ) ' Print First Row Local first_row := results.GetObject("1") ' Get the row object Print first_row.GetString("b") ' get the needed value from said row Print results.ToJson() ' print the whole json result as a string DB.Close() Endif      End

The “netdb.monkey2” Module is Here, you should be able to just be plug and play into your projects.
(Tested only on Windows 10 & Linux Kernel 4+)
I wrote this with barely any documentation very quickly so forgive any memory leaks or flat-out wrong usage of the sqlite3 lib. x’D Please you’re welcome to suggest any improvements.

Monkey

#Import "<std>" #Import "<sqlite>" Namespace netdb Using sqlite.. Using std.. ' --- Abstraction layer for a Simple Database --- Class Database Field db:sqlite3 Ptr Field res:sqlite3_stmt Ptr Field Name:String Method New(name:String) Self.Name = name End ' If a database exists, load it, otherwise, create it. Returns if the database was loaded or not Method Open:Bool() Local success:Bool = False If( sqlite3_open( Self.Name, Varptr db ) = SQLITE_OK ) success = True Else Print "Failed to Open Database - "+sqlite3_errmsg( db ) sqlite3_close( db ) Endif Return success End ' Closes the database Method Close() sqlite3_close( db ) End ' A simple way of sending queries to the databases and getting the results Method Query:JsonObject(data:String) Local rowsobj:JsonObject = New JsonObject ' - Check Query String Print "Validating Query..." If(sqlite3_prepare_v2( db, data, -1, Varptr res, Null) = SQLITE_OK) Local colnum:Int = sqlite3_column_count(res) ' Get Result Count Local rownum:Int = 0 While(True) ' - Loop If(sqlite3_step( res ) = SQLITE_ROW ) ' If there's a Row Local print_string:String = "" ' Debug Output ' Create New Row in Json Local row_id:String = Cast<String>(rownum) ' Get Row String ID rowsobj[row_id] = New JsonObject ' Create New Row from String ID Local newrow := rowsobj.GetObject(row_id) ' Get New Row Ref ' Generate Rows For Local i:Int = 0 Until colnum Local typeint := sqlite3_column_type( res, i ) ' Get Type of Entry Local colname := sqlite3_column_name( res, i ) ' Get Column Name Select typeint ' Determine the Correct Data Type Case 1 ' ADD INTEGER COLUMN Local colval:Int = sqlite3_column_int( res, i ) newrow.SetNumber(colname, colval) print_string += "INTEGER:" + colname + " " + colval + " | " Case 2 ' ADD FLOAT/REAL COLUMN Local colval:Double = sqlite3_column_double( res, i ) newrow.SetNumber(colname, colval) print_string += "REAL:" + colname + " " + colval + " | " Case 3 ' ADD STRING/TEXT COLUMN Local colval := sqlite3_column_text( res, i ) newrow.SetString(colname, colval) print_string += "TEXT:" + colname + " " + colval + " | " Case 4 ' ADD BLOB/ARRAY? COLUMN (Probably coming soon) 'Local colval := sqlite3_column_blob( res, i ) 'print_string += "BLOB:" + colname + " " + colval + " | " Default ' NOT AN ACCEPTABLE TYPE FOR NOW print_string += "UNKNOWN TYPE" End Select Next Print print_string rownum += 1 Else Exit ' - Exit Read Row Loop End Wend Print "Query Finished." Else ' - On Failed Query Preparation Print "Failed to Prepare Query - "+sqlite3_errmsg( db ) sqlite3_close( db ) ' Close sql file Endif sqlite3_finalize( res ) ' Clean up stmt? Return rowsobj End End Class

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130

#Import "<std>" #Import "<sqlite>"   Namespace netdb   Using sqlite.. Using std..   ' --- Abstraction layer for a Simple Database ---   Class Database Field db:sqlite3 Ptr Field res:sqlite3_stmt Ptr Field Name:String Method New(name:String) Self.Name = name End ' If a database exists, load it, otherwise, create it. Returns if the database was loaded or not Method Open:Bool() Local success:Bool = False If( sqlite3_open( Self.Name, Varptr db ) = SQLITE_OK )    success = True Else Print "Failed to Open Database - "+sqlite3_errmsg( db ) sqlite3_close( db ) Endif Return success End ' Closes the database Method Close() sqlite3_close( db ) End ' A simple way of sending queries to the databases and getting the results Method Query:JsonObject(data:String) Local rowsobj:JsonObject = New JsonObject ' - Check Query String Print "Validating Query..."     If(sqlite3_prepare_v2( db, data, -1, Varptr res, Null) = SQLITE_OK)           Local colnum:Int = sqlite3_column_count(res) ' Get Result Count     Local rownum:Int = 0          While(True) ' - Loop     If(sqlite3_step( res ) = SQLITE_ROW ) ' If there's a Row          Local print_string:String = "" ' Debug Output          ' Create New Row in Json     Local row_id:String = Cast<String>(rownum) ' Get Row String ID     rowsobj[row_id] = New JsonObject ' Create New Row from String ID      Local newrow := rowsobj.GetObject(row_id) ' Get New Row Ref           ' Generate Rows     For Local i:Int = 0 Until colnum          Local typeint := sqlite3_column_type( res, i ) ' Get Type of Entry     Local colname := sqlite3_column_name( res, i ) ' Get Column Name          Select typeint ' Determine the Correct Data Type          Case 1 ' ADD INTEGER COLUMN          Local colval:Int = sqlite3_column_int( res, i )     newrow.SetNumber(colname, colval)      print_string += "INTEGER:" + colname + " " + colval + " | " Case 2 ' ADD FLOAT/REAL COLUMN Local colval:Double = sqlite3_column_double( res, i )     newrow.SetNumber(colname, colval)      print_string += "REAL:" + colname + " " + colval + " | "      Case 3 ' ADD STRING/TEXT COLUMN Local colval := sqlite3_column_text( res, i ) newrow.SetString(colname, colval) print_string += "TEXT:" + colname + " " + colval + " | "      Case 4 ' ADD BLOB/ARRAY? COLUMN (Probably coming soon) 'Local colval := sqlite3_column_blob( res, i ) 'print_string += "BLOB:" + colname + " " + colval + " | " Default ' NOT AN ACCEPTABLE TYPE FOR NOW print_string += "UNKNOWN TYPE"      End Select         Next       Print print_string    rownum += 1    Else Exit ' - Exit Read Row Loop End Wend Print "Query Finished."     Else ' - On Failed Query Preparation           Print "Failed to Prepare Query - "+sqlite3_errmsg( db )         sqlite3_close( db ) ' Close sql file          Endif          sqlite3_finalize( res ) ' Clean up stmt?          Return rowsobj      End End Class

Now you can use the Json Library for transmitting query results over TCP/UDP protocols, to kinda make your own storage server if you wish.

Note: This Module doesn’t support arrays as database results just yet, A.K.A Blobs in SQLite3 “speak” I think.

The next improvement should be arrays, and maybe better access to structured table return types. But that should be simple to implement yourself if needed.

[Mark SiblyKeymaster]

Very nice!

[scurtyParticipant]

Thanks Mark!

Should I switch to JSON for the result Data instead of making the Alias?
I feel like it would be more flexible since you can use GetString/GetNumber instead of type casting.

Edit: The only issue I see with JSON is generating the results from the Database would probably require parsing the JSON from an assembled string, that might add too much overhead for response times. I’ll try and run a few tests. Update later.

[scurtyParticipant]

Updated to JSON. Works great! Probably faster than my own parsing functions! I still have both versions. I’ll compare, but I’m very sure that JSON is quicker and more reliable. (Sorry for the double post)

[Author]

Posts