B4J Code Snippet (Deprecated) MiniORMUtils v1

Updates
Version 4.03 is available
https://www.b4x.com/android/forum/threads/b4x-miniormutils.166030/

MiniORMUtils
Version: 1.17
This library can be use for creating db schema and performing CRUD operations.
It is suitable for Web API Template or any database system.
Currently it supports SQLite and MySQL (B4J).

B4X project template
Version: 1.06

[Project Template] [B4X] MiniORM

Version 6.00 A simple app to demonstrate the use of MiniORMUtils library. Platforms supported: B4A, B4i, B4J GitHub: https://github.com/pyhoon/MiniORM-B4X Depends on: B4XPages B4XPreferencesDialog MiniORMUtils (depends on SQL) XUI Views
www.b4x.com www.b4x.com

Examples:

Initialization
B4X: 
Dim MDB As MiniORM
MDB.Initialize(Main.DBOpen, Main.DBEngine)
MDB.UseTimestamps = True
MDB.AddAfterCreate = True
MDB.AddAfterInsert = True
Take note: Before calling MDB.Create and MDB.Insert, set AddAfterCreate and AddAfterInsert to True.
Click to expand...

Create Table
B4X: 
MDB.Table = "tbl_category"
MDB.Columns.Add(MDB.CreateORMColumn2(CreateMap("Name": "category_name")))
MDB.Create

Insert Data
B4X: 
MDB.Columns = Array("category_name")
MDB.Insert2(Array As String("Hardwares"))
MDB.Insert2(Array As String("Toys"))

Execute Batch NonQuery
B4X: 
Wait For (MDB.ExecuteBatch) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
    Log(LastException)
End If
MDB.Close

Select All Rows
B4X: 
MDB.Table = "tbl_category"
MDB.Query
Dim Items As List = MDB.Results

Joining Table
B4X: 
MDB.Table = "tbl_products p"
MDB.Select = Array("p.*", "c.category_name")
MDB.Join = MDB.CreateORMJoin("tbl_category c", "p.category_id = c.id", "")
MDB.WhereValue(Array("c.id = ?"), Array As String(CategoryId))
MDB.Query
Dim Items As List = MDB.Results

GitHub:
https://github.com/pyhoon/MiniORMUtils-B4X
https://github.com/pyhoon/MiniORM-B4X
https://github.com/pyhoon/MiniORM-Demo-B4X
 

Attachments

Last edited:
Click to expand...
aeric said:
Code: 
' INSERT
db1.DataColumn = CreateMap("category_id": 2, "product_code": "T003", "product_name": "YoYo", "product_price": 9.25)
db1.Save
Click to expand...

We can also write using Map.Put("ColumnName", "Value")
B4X: 
' INSERT
Dim rec As Map
rec.Initialize
rec.Put("category_id", cid)
rec.Put("product_code", data.Get("code"))
rec.Put("product_name", data.Get("name"))
rec.Put("product_price", data.Get("price"))
Dim db1 As ORM
db1.Initialize(con)
db1.Table = "tbl_products"
db1.DataColumn = rec
db1.Save

Example of modified PostProductByCategory from Web API template.
[CODE lang="b4x" title="ProductHandler.bas" highlight="20-31"]Sub PostProductByCategory (cid As Int) As HttpResponseMessage
#region Documentation
' #Body = {<br>&nbsp; "code": "product_code",<br>&nbsp; "name": "product_name",<br>&nbsp; "price": "product_price"<br>}
' #Desc1 = (N/A)
' #Desc2 = Add a new product by category id
' #Elems = 4
#End region
Dim con As SQL = Main.DB.GetConnection
Dim strSQL As String
Try
strSQL = Main.queries.Get("GET_CATEGORY_BY_ID")
Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(cid))
If res.NextRow Then
Dim data As Map = Utility.RequestData(Request)
If data.IsInitialized Then
'strSQL = Main.queries.Get("ADD_NEW_PRODUCT_BY_CATEGORY")
con.BeginTransaction
'con.ExecNonQuery2(strSQL, Array As String(cid, data.Get("code"), data.Get("name"), data.Get("price")))

' INSERT
Dim rec As Map
rec.Initialize
rec.Put("category_id", cid)
rec.Put("product_code", data.Get("code"))
rec.Put("product_name", data.Get("name"))
rec.Put("product_price", data.Get("price"))
Dim db1 As ORM
db1.Initialize(con)
db1.Table = "tbl_products"
db1.DataColumn = rec
db1.Save

strSQL = Main.queries.Get("GET_LAST_INSERT_ID")
Dim NewId As Int = con.ExecQuerySingleResult(strSQL)
strSQL = Main.queries.Get("GET_PRODUCT_BY_CATEGORY_AND_ID")
Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(cid, NewId))
con.TransactionSuccessful
Dim List1 As List
List1.Initialize
Do While res.NextRow
Dim Map2 As Map
Map2.Initialize
For i = 0 To res.ColumnCount - 1
If res.GetColumnName(i) = "product_price" Then
Map2.Put(res.GetColumnName(i), res.GetDouble2(i))
Else If res.GetColumnName(i) = "category_id" Or res.GetColumnName(i) = "id" Then
Map2.Put(res.GetColumnName(i), res.GetInt2(i))
Else
Map2.Put(res.GetColumnName(i), res.GetString2(i))
End If
Next
List1.Add(Map2)
Loop
HRM.ResponseCode = 201
HRM.ResponseMessage = "Created"
HRM.ResponseData = List1
End If
Else
HRM.ResponseCode = 404
HRM.ResponseError = "Category Not Found"
End If
Catch
LogError(LastException)
HRM.ResponseCode = 422
HRM.ResponseError = "Error Execute Query"
End Try
Main.DB.CloseDB(con)
Return HRM
End Sub[/CODE]
 
Last edited:
Teaser of project "EndsMeet" - a B4J MVC web framework using this Mini ORM
This is an ambitious project that I don't know when I have time to create. ?
 

Attachments

  • 1656584449556.png
    1656584449556.png
    115.1 KB · Views: 1,363
  • 1656584523311.png
    1656584523311.png
    123.3 KB · Views: 1,283
Last edited:
Mashiane said:
Q. For the filters, how do you add multiple filters e.g. other fields?
Q. How do you perform an "or" filter on some column (same / different)

Thanks.
Click to expand...
This is an old thread.
MiniORM latest version 0.03 is attached in post #2 of Web API Template 2.

A quick one.
Actually I seldom use MiniORM and it is in beta development. ?

Let say the SQL I want to create is:
SQL: 
SELECT *
FROM
tbl_users
WHERE
role = 'cashier' AND status = 1 AND deleted_at IS Null
I can write:
B4X: 
DB.Table = "tbl_users"
DB.Where = Array("role = ?", "status = ?", "deleted_at IS Null")
DB.Parameters = Array("cashier", 1)
DB.Query

I also seldom use OR in my SQL query.
Let say the query is:
SQL: 
SELECT
user_name, user_email
FROM
tbl_users
WHERE
role = 'sales' AND status = 1 OR status = 2

I can write:
B4X: 
DB.Table = "tbl_users"
DB.Select = Array("user_name", "user_email")
DB.Where = Array("role = ?", "status = ? OR status = ?")
DB.Parameters = Array("sales", 1, 2)
DB.Query
 
aeric said:
' CREATE TABLE Dim fields As List fields.Initialize Dim Col1 As ORMColumn = db1.CreateORMColumn("category_id", db1.INTEGER, "", "", False, False) Dim Col2 As ORMColumn = db1.CreateORMColumn("product_code", db1.VARCHAR, "", "", True, False) Dim Col3 As ORMColumn = db1.CreateORMColumn("product_name", db1.VARCHAR, "", "", True, False) Dim Col4 As ORMColumn = db1.CreateORMColumn("product_price", db1.DECIMAL, "", "'0.00'", True, False) fields.AddAll(Array(Col1, Col2, Col3, Col4)) db1.Table = "tbl_products" db1.Create(fields, True) db1.Foreign("category_id", "id", "tbl_category", "", "") db1.Execute
Click to expand...
I welcome you.

Well done !!
I recently work in python with SQLAlchemy and there is that if I add a new column to the table, at the beginning of creation, the code checks if all the columns are already in the table. If not, it probably does 'ALTER TABLE' and adds the missing columns.
I don't know if your code does it, if you can't add it.
That's my little suggestion.
 
MichalK73 said:
I welcome you.

Well done !!
I recently work in python with SQLAlchemy and there is that if I add a new column to the table, at the beginning of creation, the code checks if all the columns are already in the table. If not, it probably does 'ALTER TABLE' and adds the missing columns.
I don't know if your code does it, if you can't add it.
That's my little suggestion.
Click to expand...
Thanks for your suggestion.
Usually I would ensure that I am clear that I need to CREATE or ALTER a table.
I know sometimes we need to create a new migration using framework like Django.
Although it is possible but I haven't encounter such situation in my projects. I seldom modify database tables which already in production.

In situation where I need to alter a table, I would :
For database on server:
Directly modify the table structure using database management tool like Adminer/PHPMyAdmin/SQLite DB Browser
For database on client:
Create a temporary table and insert the old data and drop the old table. Create the new table and copy the old data to the new table. Finally drop the temporary table.

So, I would not put this as priority.
 
aeric said:
Thanks for your suggestion.
Usually I would ensure that I am clear that I need to CREATE or ALTER a table.
I know sometimes we need to create a new migration using framework like Django.
Although it is possible but I haven't encounter such situation in my projects. I seldom modify database tables which already in production.

In situation where I need to alter a table, I would :
For database on server:
Directly modify the table structure using database management tool like Adminer/PHPMyAdmin/SQLite DB Browser
For database on client:
Create a temporary table and insert the old data and drop the old table. Create the new table and copy the old data to the new table. Finally drop the temporary table.

So, I would not put this as priority.
Click to expand...
I understand you. I just use it in python and don't think about the structure, it adds a new column or deletes the unnecessary one and the code does it for me. You don't have to do it on the database. This is what ORM is all about.
 
This class is a sub product of my Web API and Ticketing system projects.

It tries to simplify some code.

I am not lucky to work on complex project. Most projects I work with are too simple. ?
 
This class has been updated to version 0.08
It is now supporting MySQL database, batch non query, new CreateORMColumn2 sub and fixed some bugs.

CreateORMColumn
B4X: 
DB1.Table = "tbl_products"
DB1.Columns.Add(DB1.CreateORMColumn("category_id", DB1.INTEGER, "", "", True, False))
DB1.Columns.Add(DB1.CreateORMColumn("product_code", DB1.VARCHAR, "12", "", True, False))
DB1.Columns.Add(DB1.CreateORMColumn("product_name", DB1.VARCHAR, "200", "", True, False))
DB1.Columns.Add(DB1.CreateORMColumn("product_price", DB1.DECIMAL, "10,2", "0.00", True, False))
DB1.UseTimestamps = True
DB1.Foreign("category_id", "id", "tbl_category", "", "")
DB1.Create

CreateORMColumn2
B4X: 
DB1.Table = "tbl_products"
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_id", "Type": DB1.INTEGER)))
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "product_code", "Length": "12")))
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "product_name")))
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "product_price", "Type": DB1.DECIMAL, "Length": "10,2", "Default": "0.00")))
DB1.UseTimestamps = True
DB1.Foreign("category_id", "id", "tbl_category", "", "")
DB1.Create
Default column type is varchar with length 255 characters (or Text in SQLite), Nullable and AutoIncrement = False.
 
where is the error?

Code: 
#Region Project Attributes 
    #MainFormWidth: 600
    #MainFormHeight: 600 
    #AdditionalJar: sqlite-jdbc-3.7.2
#End Region
*
*
Code: 
Sub Process_Globals
    Dim SQL As SQL
End Sub
*
*
*
Code: 
SQL.InitializeSQLite(File.DirApp, "demo.db", True) 'ALWAYS EMPTY (no table created)
    Dim DB1 As MiniORM
    DB1.Initialize(SQL)
    DB1.Engine = "SQLite"
    '
    DB1.Table = "tbl_category"
    DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_name")))
    DB1.UseTimestamps = True
    DB1.Create
    '
    DB1.Columns = Array("category_name")
    DB1.Parameters = Array("Hardwares")
    DB1.Insert
    DB1.Parameters = Array("Toys")
    DB1.Insert
    '
    Wait For (DB1.ExecuteBatch) Complete (Success As Boolean)
    Log(Success)
    If Success Then
        Log("Database is created successfully!") 'always success
    End If
    DB1.Close
 
giannimaione said:
where is the error?

Code: 
#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: sqlite-jdbc-3.7.2
#End Region
*
*
Code: 
Sub Process_Globals
    Dim SQL As SQL
End Sub
*
*
*
Code: 
SQL.InitializeSQLite(File.DirApp, "demo.db", True) 'ALWAYS EMPTY (no table created)
    Dim DB1 As MiniORM
    DB1.Initialize(SQL)
    DB1.Engine = "SQLite"
    '
    DB1.Table = "tbl_category"
    DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_name")))
    DB1.UseTimestamps = True
    DB1.Create
    '
    DB1.Columns = Array("category_name")
    DB1.Parameters = Array("Hardwares")
    DB1.Insert
    DB1.Parameters = Array("Toys")
    DB1.Insert
    '
    Wait For (DB1.ExecuteBatch) Complete (Success As Boolean)
    Log(Success)
    If Success Then
        Log("Database is created successfully!") 'always success
    End If
    DB1.Close
Click to expand...
I haven't tested on UI app.
Can you upload it?
 
Sorry, I didn't mention about the following settings:

B4X: 
    ' These 2 lines are required
    DB1.AddAfterCreate = True
    DB1.AddAfterInsert = True

[CODE lang="b4x" highlight="8,9"]Sub runit
SQL.InitializeSQLite(File.DirApp, "demo.db", True) 'ALWAYS EMPTY
Dim DB1 As MiniORM
DB1.Initialize(SQL)
DB1.Engine = "SQLite"
'
' These 2 lines are required
DB1.AddAfterCreate = True
DB1.AddAfterInsert = True

DB1.Table = "tbl_category"
DB1.Columns.Add(DB1.CreateORMColumn2(CreateMap("Name": "category_name")))
DB1.UseTimestamps = True
DB1.Create
'
DB1.Columns = Array("category_name")
DB1.Parameters = Array("Hardwares")
DB1.Insert
DB1.Parameters = Array("Toys")
DB1.Insert
'
Wait For (DB1.ExecuteBatch) Complete (Success As Boolean)
Log(Success)
If Success Then
Log("Database is created successfully!")
End If
DB1.Close
End Sub[/CODE]

Edit: I have updated the remarks in post #1. Thanks @giannimaione for reporting.
 
Last edited:
MiniORMUtils
Version: 1.00
Size: 10KB

Introduction
  • B4X class for building SQL queries
  • Works with Web API Server Template (version 2.04)
  • Reduce number of code lines for Web API by outputting query results into Map and List for generating JSON
 
MiniORMUtils
Version: 1.01
Size: 11KB

What's New
  • (fix) Save sub returns map for Insert and Update using First sub
  • (new) Initialize sub now expects DBEngine as second parameter
  • (update) Support for Web API Server Template (version 2.05)
  • (update) Return ID with getID
  • (update) Code snippets
 
You must log in or register to reply here.

Similar Threads

Erel
Server software is being upgraded
Replies
2
Views
59
Erel
Erel
A
eMedNY
Replies
0
Views
89
Alex_197
A
Daestrum
When tech works for you. (minimum effort)
Replies
0
Views
122
Daestrum
Daestrum
Robert Valentino
  • Question Question
Android Question #R8 and B4XLib crashing method not found in: okhttp3.Request$Builder
Replies
3
Views
161
Alessandro71
Alessandro71
GMan
  • Article Article
B4J Code Snippet Daytime-depending event (here playing MP3-file)
Replies
0
Views
112
GMan
GMan
Back
Top