B4J Tutorial Using MiniORMUtils in Web API Server 3

Introduction
At the time of writing, MiniORMUtils is version 1.14 and Web API Server 3 is version 3.00.
MiniORMUtils support SQLite and MySQL databases.
We don't need to write different SQL queries to work with SQLite and later migrate to MySQL.

How to Use MiniORMUtils

1. When we run Web API Server 3 for the first time, a new SQLite database is created by default.
2. The creation of database is achieved using CreateDatabase sub in Main module.
3. The project checks whether a database is already existed every time the server is started through CreateConnection sub.
4. Before we call this sub, the project needs to load configurations from config.ini file through InitDatabase sub.
   
   Private Sub InitDatabase
       cnn.Initialize
       cnn.DBDir = ctx.GetDefault("DbDir", "")
       cnn.DBFile = ctx.GetDefault("DbFile", "")
       cnn.DBType = ctx.GetDefault("DbType", "")
       ...
   End Sub
5. No modification is needed on this sub. The correct way to modify the values is through editing the config.ini file.
6. Let's take a look at CreateConnection sub.
   
   Private Sub CreateConnection
       Try
           Log("Checking database...")
           #If MySQL
           Dim DBType As String = "MySQL"
           #Else
           Dim DBType As String = "SQLite"
           #End If
           If cnn.DBType.EqualsIgnoreCase(DBType) = False Then
               ShowBuildConfigurationNotMatch(cnn.DBType)
               Return
           End If
           DBConnector.Initialize(cnn)
           ...
   End Sub
7. The selection of DBType is determined by Build Configurations.
   
   
   
   The "Default" build configuration is selected means the project will utilize SQLite as database.
   To use MySQL, select MySQL from the dropdown list.
   
   
8. If the selected DBType in Build Configuration does not match the configurations in config.ini, an error message will show and the server will be terminated.
   
   
9. If the configuration is matched, the project proceeds to initialize the DBConnector object.
   
   DBConnector.Initialize(cnn)
10. Depending on DBType, the project uses DBExist or DBExist2 sub for checking the database is existed.
    
    #If MySQL
    Wait For (DBConnector.DBExist2) Complete (DBFound As Boolean)
    #Else
    Dim DBFound As Boolean = DBConnector.DBExist
    #End If
11. If the return value for DBFound is True, a message log will be showed.
    
    If DBFound Then
        LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
    Else
        LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
        CreateDatabase
    End If
    DBFound = True
    
    
    
    
    DBFound = False
    
    

Create Database

1. Depending on DBType, we call DBCreate for creating a new database.
   
   Private Sub CreateDatabase
       Log("Creating database...")
       Select cnn.DBType.ToUpperCase
           Case "MYSQL"
               Wait For (DBConnector.DBCreateMySQL) Complete (Success As Boolean)
           Case "SQLITE"
               Wait For (DBConnector.DBCreateSQLite) Complete (Success As Boolean)
       End Select
2. The database will be created. If it is failed then the sub will be exited.
   
   
3. To use MiniORMUtils, we must first initialize it.
   We need to pass 2 parameters to the Initialize method.
   
   Dim MDB As MiniORM
   MDB.Initialize(DBOpen, DBEngine)
4. If you want to add timestamps fields in your tables, we can set UseTimestamps to True.
   
   MDB.UseTimestamps = True
   This will add 3 columns (created_date, modified_date and deleted_date) at the last columns to all of the following tables to be created.
   If you don't need these columns, omit this line of code.
   This setting can be set to True or False between creation of tables.
5. We will use a batch to execute the queries. Hence we will add every CREATE table query to a batch and execute them by batch.
   This is set using the following code:
   
   MDB.AddAfterCreate = True
6. We also want to add some dummy data to the table.
   We need to set the INSERT query as batch.
   
   MDB.AddAfterInsert = True

Create Table

1. We tell MiniORM the table name we want to create.
   
   MDB.Table = "tbl_categories"
2. We can add each column by passing an ORMColumn to the Columns list of the MiniORM.
   
   MDB.Columns.Add( <ORMColumn> )
3. We can use CreateORMColumn2 method to create ORMColumn by passing a Map.
   
   MDB.CreateORMColumn2( <Map> )
4. The Map must contains at least a keyvalue pair with the key "Name".
   
   CreateMap("Name": "category_name")
5. The complete code to add a table column for table tbl_categories with the name "category_name" is per the following:
   
   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_name")))
   In SQLite, it will add a column of the type TEXT for category_name.
   In MySQL, it will add a column of the type VARCHAR for category_name with length 255.
6. Then we call Create method to tell MiniORM to generate a CREATE query.

Insert Row

1. We can proceed to use MiniORM to add row to the table.
2. Since we have specified the table name before, we don't need to specify the table name again.
3. We just need to specify the column name as array to pass to the Columns property.
   
   MDB.Columns = Array("category_name")
4. Then we can add the value using the method Insert2.
   
   MDB.Insert2(Array("Hardwares"))
   MDB.Insert2(Array("Toys"))

Execute Batch Query

1. Previously we have add CREATE and INSERT queries to the MiniORM object using methods such as Create and Insert2.
2. To execute all the queries, we use ExecuteBatch method.
   
   Wait For (MDB.ExecuteBatch) Complete (Success As Boolean)
   If Success Then
       LogColor("Database is created successfully!", COLOR_BLUE)
   Else
       LogColor("Database creation failed!", COLOR_RED)
   End If
3. We will see a log message showing the execution success or failed.
4. Finally, we close the database connection using the Close method.
   
   MDB.Close
   The Close method only applies to MySQL internally. SQLite has no effect but we left the code for compatibility.

This is the end of tutorial. Thanks for taking your time to read it.

Please do not post any question on this thread.
Always start a new question with the name Web API Server 3 or post your question here : [Q&A] Web Api Server 3

 

[aeric]

Using MiniORMUtils inside Server Handler

1. Referring to the generated code using Code Snippets in the Part #5: Add Boilerplate Code to Handler Using Code Snippets tutorial.
2. On top of the code, we see there are some variables declared as Private.
   
   Sub Class_Globals
           Private Request As ServletRequest
           Private Response As ServletResponse
           Private HRM As HttpResponseMessage
           Private DB As MiniORM
           Private Method As String
           Private Elements() As String
           Private ElementId As Int
   End Sub
3. Request and Response are passing from Handle sub so we can use them through out the entire handler class.
   
   Sub Handle (req As ServletRequest, resp As ServletResponse)
       Request = req
       Response = resp
       ...
   End Sub
4. We declared a variable HRM as HttpResponseMessage for generating the JSON output or response.
   
   Private HRM As HttpResponseMessage
   HttpResponseMessage is a custom type defined in WebApiUtils library.
5. We declared DB as MiniORM for working with our database.
   
   Private DB As MiniORM
   MiniORM is a class from MiniORMUtils library.
6. We declared a String array Elements() for storing the elements from the request URL.
   
   Private Elements() As String
7. Last variable is ElementId for storing the Id element.
   
   Private ElementId As Int

Initialization

1. We use the Initialize sub to initialize 2 variables, HRM and DB from the public variables we declared in Main module.
   
   Public Sub Initialize
       HRM.Initialize
       HRM.SimpleResponse = Main.Config.SimpleResponse
       DB.Initialize(Main.DBOpen, Main.DBEngine)
   End Sub

Handle sub

1. The Handle sub look like following:
   
   Sub Handle (req As ServletRequest, resp As ServletResponse)
       Request = req
       Response = resp
       Method = Request.Method.ToUpperCase
       Dim FullElements() As String = WebApiUtils.GetUriElements(Request.RequestURI)
       Elements = WebApiUtils.CropElements(FullElements, 3) ' 3 For Api handler
       Select Method
           Case "GET"
               ...
           Case "POST"
               ...
           Case "PUT"
               ...
           Case "DELETE"
               ...
           Case Else
               Log("Unsupported method: " & Method)
               ReturnMethodNotAllow
               Return
       End Select
       ReturnBadRequest
   End Sub
2. The Method variable read from the Request variable will be use for further routing.
3. We can use GetUriElements method of WebApiUtils to fill the FullElements array.
   
   Dim FullElements() As String = WebApiUtils.GetUriElements(Request.RequestURI)
4. Instead of counting the elements from 0 for the root element from the URL, what we are interested are the elements after the /api (for Api Handler).
5. Therefore we use CropElements method to "slice" or crop out the first elements.
   
   Elements = WebApiUtils.CropElements(FullElements, 3) ' 3 For Api handler
6. From this point, we can think that the first Element e.g Id is accessed using the index 0.
   
   ElementId = Elements(0)
7. You can see the logic inside ElementMatch sub.
8. Next, we will use Select-Case to control the flow of the request falls to the right sub.

Control Flow

1. Base on the Select-Case of variable Method, we can route to 2 API end points in this example.
2. The first end point is without Id where we pass an empty string pattern to the ElementMatch sub.
   e.g URL: http://127.0.0.1:8080/api/users
   
   Select Method
       Case "GET"
           If ElementMatch("") Then
               GetUsers
               Return
           End If
           ...
       ...
   End Select
3. If ElementMatch returns True then we can proceed to call GetUsers sub to return all the rows inside users table.
4. The second end point is with an Id where we pass the "id" pattern to the ElementMatch sub.
   e.g URL: http://127.0.0.1:8080/api/users/2
   
   If ElementMatch("id") Then
       GetUserById(ElementId)
       Return
   End If
5. Important: For every match, insert Return keyword before the End If keywords like the examples above.
   Missing this keyword will cause the response Api returns the Bad Request output too. In other word, a malformed JSON will be returned.
6. The same steps apply to other Methods such as POST, PUT and DELETE depending on the Endpoint pattern you want to create.
7. We also check if there are request by other Method (e.g PATCH) and we don't want to allow such request to proceed.
   
   Case Else
       Log("Unsupported method: " & Method)
       ReturnMethodNotAllow
       Return
8. This is also the case where you want to create an Api that only allow POST then the remaining request can be consider under the Case Else flow.
9. Any route that does not match the desired pattern will fall under Bad Request and handled by ReturnBadRequest sub.

Commonly Use Subs

1. We can shorten some of the long codes by using extra subs.
2. This is an example:
   
   Private Sub ReturnApiResponse
       WebApiUtils.ReturnHttpResponse(HRM, Response)
   End Sub
3. So instead of writing the full code, we can just use one word in our code.
4. You can see examples of using ReturnBadRequest, ReturnMethodNotAllow and ReturnErrorUnprocessableEntity.

 

[aeric]

Examples

GetUsers

Private Sub GetUsers
    ' #Desc = Read all Users
    DB.Table = "tbl_users"
    DB.Query
    HRM.ResponseCode = 200
    HRM.ResponseData = DB.Results
    ReturnApiResponse
    DB.Close
End Sub

1. This sub is called when the request URL matched an empty pattern with GET method.
2. It is use to get all the rows in users table.
3. Important: In order for HelpHandler to read this handler to generate the API Documentation, the name of the sub must be started with "Get".
4. The first line is a comment. We use #Desc to tell HelpHandler class that the text after the equals sign is the description of the API that will appear in the documentation.
   
   Spoiler: Setting description for API end point

   
5. Similar to how we create a table, we need to pass the table name using the Table property when we want to query rows from a table.
   
   DB.Table = "tbl_users"
6. Next, we just need to call Query method.
   
   DB.Query
7. We want to return a status code 200 to the response.
   
   HRM.ResponseCode = 200
8. Since we are expecting the rows are returned as a JSON array or list, we pass the query result DB.Results (which is a list) to HRM.ResponseData.
   
   HRM.ResponseData = DB.Results
9. Then we call ReturnApiResponse to write the JSON output to the server response.
10. Finally we close the MiniORM object which will close the SQL resultset object especially for MySQL database.
    
    DB.Close

GetUserById

Private Sub GetUserById (Id As Int)
    ' #Desc = Read one User by id
    ' #Elements = [":id"]
    DB.Table = "tbl_users"
    DB.Find(Id)
    If DB.Found Then
        HRM.ResponseCode = 200
        HRM.ResponseObject = DB.First
    Else
        HRM.ResponseCode = 404
        HRM.ResponseError = "User not found"
    End If
    ReturnApiResponse
    DB.Close
End Sub

1. This sub is called when the request URL matched an id pattern with GET method.
2. It is use to get a single row in users table if the id is existed.
3. Adding #Elements comment and parameter with type (e.g Id As Int) for the sub dictates how the documentation will appear.
   
   Spoiler: How code affects Api documentation

   
4. Similar to the GetUsers sub, we pass the table name to MiniORM.
   
   DB.Table = "tbl_users"
5. To query for a single row from users table by id, we can use the Find method.
   
   DB.Find(Id)
6. If a row is found then we return the row as a single object.
   
   If DB.Found Then
       HRM.ResponseCode = 200
       HRM.ResponseObject = DB.First
7. We use the First method to ensure that only one object is read.
8. WebApiUtils through ReturnHttpResponse method will determine which format to return as JSON output based on the SimpleResponse settings.
   It means the return value can be an array or an object.
9. If the id is not existed, we want to return a message to the API.
   We assign status code 404 to indicate the item is not found.
   
   Else
       HRM.ResponseCode = 404
       HRM.ResponseError = "User not found"
10. Finally, we write the JSON output to server response and close the MiniORM object.
    
    ReturnApiResponse
    DB.Close

PostUser

Private Sub PostUser
    ' #Desc = Add a new User
    ' #Body = {<br>&nbsp;"name": "User_name"<br>}
    Dim data As Map = WebApiUtils.RequestData(Request)
    If Not(data.IsInitialized) Then
        HRM.ResponseCode = 400
        HRM.ResponseError = "Invalid json object"
        ReturnApiResponse
        Return
    End If

    ' Check whether required keys are provided
    Dim RequiredKeys As List = Array As String("key1", "key2", "key3")
    For Each requiredkey As String In RequiredKeys
        If data.ContainsKey(requiredkey) = False Then
            HRM.ResponseCode = 400
            HRM.ResponseError = $"Key '${requiredkey}' not found"$
            ReturnApiResponse
            Return
        End If
    Next
   
    ' Check conflict User name
    DB.Table = "tbl_users"
    DB.Where = Array("User_name = ?")
    DB.Parameters = Array As String(data.Get("user_name"))
    DB.Query
    If DB.Found Then
        HRM.ResponseCode = 409
        HRM.ResponseError = "User already exist"
        ReturnApiResponse
        DB.Close
        Return
    End If
   
    ' Insert new row
    DB.Reset
    DB.Columns = Array("User_name", "created_date")
    DB.Parameters = Array(data.Get("User_name"), data.GetDefault("created_date", WebApiUtils.CurrentDateTime))
    DB.Save
   
    ' Retrieve new row
    HRM.ResponseCode = 201
    HRM.ResponseObject = DB.First
    HRM.ResponseMessage = "User created successfully"
    ReturnApiResponse
    DB.Close
End Sub

1. This sub is called when the request URL matched the empty pattern with POST method.
2. It is use to create a new resource in this case the users table.
3. Meaning it will execute an INSERT query to the database using MiniORM.
4. Notice that we use a comment ' #Body to display the sample body. (Similar for PUT end point)
   
   Spoiler: Construct body hint in API documentation

   
   Take note that we only can write the comment in a single line.
5. For POST request, we need to read the data submitted in the POST body.
   
   Dim data As Map = WebApiUtils.RequestData(Request)
6. If the submitted payload is a valid JSON string, it can be parse using RequestData method of WebApiUtils and assign the result into a Map.
   
   If Not(data.IsInitialized) Then
       HRM.ResponseCode = 400
       HRM.ResponseError = "Invalid json object"
       ReturnApiResponse
       Return
   End If
   If the string is invalid, we return a result with status code 400 and exit the process.
7. We also want to check if all the required keys are provided.
   
   ' Check whether required keys are provided
   Dim RequiredKeys As List = Array As String("key1", "key2", "key3")
   For Each requiredkey As String In RequiredKeys
       If data.ContainsKey(requiredkey) = False Then
           HRM.ResponseCode = 400
           HRM.ResponseError = $"Key '${requiredkey}' not found"$
           ReturnApiResponse
           Return
       End If
   Next
8. For e.g we can replace key1 with user_name, key2 with email and key3 with password.
9. Then we check for existing records to avoid duplicate.
   
   ' Check conflict User name
   DB.Table = "tbl_users"
   DB.Where = Array("User_name = ?")
   DB.Parameters = Array As String(data.Get("User_name"))
   DB.Query
   If DB.Found Then
       HRM.ResponseCode = 409
       HRM.ResponseError = "User already exist"
       ReturnApiResponse
       DB.Close
       Return
   End If
10. In the case of users, it is more useful if we check for unique emails. We can replace the above code to check for emails.
    
    ' Check conflict emails
    DB.Table = "tbl_users"
    'DB.Where = Array("user_email = ?")
    'DB.Parameters = Array As String(data.Get("user_email"))
    DB.WhereValue(Array("user_email = ?"), Array(data.Get("user_email")))
    DB.Query
    There is a new shortcut method WhereValue in MiniORMUtils to replace method Where and Parameters.
11. Next, we can insert the data.
12. Notice we use method Reset to ensure any conditions or parameters are cleared before we reuse the MiniORM object.
    
    ' Insert new row
    DB.Reset
13. We specify the column names in order inside an array and assign to Columns property of MiniORM.
    
    DB.Columns = Array("user_name", "user_email", "user_password", "created_date")
14. Next, we specify the values associated with the columns into Parameters property as an array of objects.
    
    DB.Parameters = Array(data.Get("user_name"), data.Get("user_email"), data.Get("user_password"), data.GetDefault("created_date", WebApiUtils.CurrentDateTime))
15. Then we call the method Save. This will create an INSERT command and MiniORM will execute it.
    
    DB.Save
16. If the command executed successfully, we return a result with status code 201 to indicate a new resource is created.
17. We pass the first object from MiniORM which is queried based on the newly created id.
18. We can return the object by assigning it to ResponseObject.
19. We can also assign a message using the ResponseMessage property.
20. Finally, we write the JSON output to server response and close the MiniORM object.
    
    ReturnApiResponse
    DB.Close
21. Take note of the key names inside the data map. For e.g use the lowercase data.Get("user_name") instead of data.Get("User_name").

 

[aeric]

Adding New Table

1. Often time we need to add a new table after the database is created.
2. To do it, we need to check whether a table is exist then we create the table.
3. For e.g we want to add a new table tbl_users.

TableExists sub

1. Let's create a sub for checking the table in Main module.
2. We will pass the table name into this sub.
3. The sub will return True if the table exist or otherwise False.
   
   Private Sub TableExists (TableName As String) As Boolean
       Dim MDB As MiniORM
       MDB.Initialize(DBOpen, DBEngine)
       #If MySQL
       Return MDB.TableExists2(TableName, ctx.Get("DbName"))
       #Else
       Return MDB.TableExists(TableName)
       #End If
   End Sub

Create Table

1. We will add the code for creating table inside CreateConnection sub.
   
   If DBFound Then
       LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
       ' Add code for checking table exist
       ...
   Else
       LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
       CreateDatabase
   End If
2. We use the sub we have created previously to check for the table name.
   
   If TableExists("tbl_users") = False Then
3. Then we can use MiniORM to create the table.
   
   Dim MDB As MiniORM
   MDB.Initialize(DBOpen, DBEngine)
   MDB.UseTimestamps = True
   MDB.Table = "tbl_users"
   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_name")))
   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_email")))
   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_password")))
   MDB.Create
   MDB.Execute
   LogColor($"Table tbl_users is created!"$, COLOR_BLUE)
4. After calling the Create method, we call Execute to execute the SQL command.
5. The new table will be created when we run the project again.
6. The complete code is as following:
   
   Private Sub CreateConnection
       Try
           LogColor("Checking database...", COLOR_BLUE)
           #If MySQL
           Dim DBType As String = "MySQL"
           #Else
           Dim DBType As String = "SQLite"
           #End If
           If cnn.DBType.EqualsIgnoreCase(DBType) = False Then
               ShowBuildConfigurationNotMatch(cnn.DBType)
               Return
           End If
           DBConnector.Initialize(cnn)
           #If MySQL
           Wait For (DBConnector.DBExist2) Complete (DBFound As Boolean)
           #Else
           Dim DBFound As Boolean = DBConnector.DBExist
           #End If
           If DBFound Then
               LogColor($"${cnn.DBType} database found!"$, COLOR_BLUE)
               ' Check table exist
               If TableExists("tbl_users") = False Then
                   Dim MDB As MiniORM
                   MDB.Initialize(DBOpen, DBEngine)
                   MDB.UseTimestamps = True
                   MDB.Table = "tbl_users"
                   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_name")))
                   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_email")))
                   MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "user_password")))
                   MDB.Create
                   MDB.Execute
                   LogColor($"Table tbl_users is created!"$, COLOR_BLUE)
               End If
           Else
               LogColor($"${cnn.DBType} database not found!"$, COLOR_RED)
               CreateDatabase
           End If
       Catch
           LogError(LastException.Message)
           LogColor("Error checking database!", COLOR_RED)
           Log("Application is terminated.")
           ExitApplication
       End Try
   End Sub

This is the end of tutorial. Thanks for taking your time to read it.