B4J Library [B4X] MiniORMUtils

MiniORMUtils
Version: 4.03

---

This library can be use for creating database tables and performing CRUD operations.
It is suitable for B4X apps, non-UI web app or REST API servers.
Currently it supports SQLite (for B4A, B4i, B4J), MariaDB and MySQL (for B4J only).

Project Template:
[B4X] [Project Template] MiniORM

Examples:
Initialize MiniORM

Private DB As MiniORM
Private MS As ORMSettings

DB.Initialize
MS.Initialize
MS.DBType = "SQLite"
MS.DBFile = "data.db"
MS.DBDir = File.DirApp
DB.Settings = MS

Check if database exist

If DB.Exist Then
    LogColor($"${MS.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

Create Database

Dim Success As Boolean = DB.InitializeSQLite

Create Table

DB.Table = "tbl_category"
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "category_name")))
DB.Create

Insert Rows

DB.Columns = Array("category_name")
DB.Insert2(Array("Hardwares"))
DB.Insert2(Array("Toys"))

Execute NonQuery Batch

Wait For (DB.ExecuteBatch) Complete (Success As Boolean)
If Success Then
    Log("Database is created successfully!")
Else
    Log("Database creation failed!")
End If
DB.Close

Select All Rows

DB.Table = "tbl_category"
DB.Query
Dim Items As List = DB.Results

Update Row

DB.Table = "tbl_products"
DB.Columns = Array("category_id", "product_code", "product_name", "product_price")
DB.Id = 2
DB.Save2(Array(Category_Id, Product_Code, Product_Name, Product_Price))

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

Spoiler: Version 1

[B4X] MiniORMUtils - SQL Query Builder

Updates Version 2.00 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...

www.b4x.com

 

[aeric]

Migration from version 1.x

1. DatabaseConnector is now ORMConnector
2. ORMConnector requires a ConnectionInfo object to pass as a parameter for initialization.
   
   Dim Info As ConnectionInfo
   Info.Initialize
   Info.DBType = "SQLite"
   Info.DBFile = "test.db"
3. Then we can initialize ORMConnector as follow:
   
   Dim Con As ORMConnector
   Con.Initialize(Info)
4. To initialize MiniORM object, we need to specify the database type as first parameter and an SQL object as second parameter.
5. We can use DBType and DBOpen from ORMConnector object.
   
   Dim DB As MiniORM
   DB.Initialize(DBType, DBOpen)
6. Some methods name have been renamed.
7. CreateORMColumn and CreateORMColumn2 renamed to CreateColumn and CreateColumn2.
8. CreateORMFilter renamed to CreateFilter.
9. CreateORMJoin renamed to CreateJoin.
10. setRawSQL changed to setStatement
11. ToString changed to getStatement
12. Need to specify journal mode for SQLite when use in server. Default is "DELETE".
    
    Info.JournalMode = "WAL"
13. When using MySQL, connect to main schema using InitSchema before calling DBExist2 or DBCreate.
14. AddQuery replaced by AddNonQueryToBatch
15. BlnAddAfterCreate and BlnAddAfterInsert replaced by setQueryAddToBatch
16. BlnExecuteAfterCreate and BlnExecuteAfterInsert replaced by setQueryExecute

 

[aeric]

Version: 2.10
Size: 14KB

What's New

1. Fixed bug in setParameters sub for B4J
2. Replaced Where3 sub with WhereParam and WhereParams
3. Show logs when ShowExtraLogs = True with LogQuery, LogQuery2 and LogQueryWithArg

 

[aeric]

Version: 2.11
Size: 14KB

What's New

1. Update WhereParam sub to accept mCondition instead of mColumn as first parameter to avoid confusion with WhereParams

 

[aeric]

Version: 2.20
Size: 14KB

What's New

1. Fixed bugs for B4A and B4i

 

[aeric]

Version: 2.21
Size: 14KB

What's New

1. Updated AddParameters sub
2. Updated ORMConnector Initialize sub for B4A and B4i
3. Removed ByteConverter from manifest for B4A and B4i
4. Removed Merge sub
5. Fixed Find sub
6. Fixed Find2 sub
7. Fixed setId sub
8. Updated Query sub
9. Added setShowDBUtilsJson sub for B4A and B4i

 

[aeric]

Version: 2.30
Size: 14KB

What's New

1. Added View property
2. Added ResetParameters sub
3. Added IfNull sub
4. Added Create2 sub
5. Added setSelect2 sub
6. Added SelectFromView sub
7. Added SelectFromTable sub
8. Added ViewExists sub
9. Added ViewExists2 sub
10. Updated setSelect sub

Thanks to Support Ticketing System.

Many new functions in DatabaseBuilder class such as IfNull, Create2, setSelect2, SelectFromView, ViewExists and ViewExists2.

 

[aeric]

Version: 2.40
Size: 14KB

What's New

1. Added Save3 sub
2. Updated Create sub
3. Updated ORMConnector class

 

[Mashiane]

Hi, a kind request

In the readme, please include examples of doing executing conditions for updates & deletes

1. Update Where e.g. UPDATE Users Where ...
2. Delete Where e.g. DELETE Users Where...

Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution?

Thanks in advance.

PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need?

 

[aeric]

Hi, a kind request

In the readme, please include examples of doing executing conditions for updates & deletes

1. Update Where e.g. UPDATE Users Where ...
2. Delete Where e.g. DELETE Users Where...

Also, is there functionality to do a batch addition of records e.g. that will use transaction mode for faster execution?

Thanks in advance.

PS: In cases you have a large dataset, at times you dont want to return the updated / inserted record, how can one turn that off based on need?

Thanks for your suggestion. I will try to add more tutorials when I am free.
For any new questions, please post new threads.

 

[aeric]

Version: 2.50
Size: 14KB

What's New

1. Changed MiniORM class Initialize sub parameters switch order, SQL object is now second parameter which can be Null
2. Renamed Engine or DBEngine property to DBType
3. Added Error property
4. Updated ORMConnector class with DBType and Error property

This is a breaking update.

Dim DB As MiniORM
DB.Initialize(DBType, DBOpen)

 

[aeric]

Version: 2.51
Size: 14KB

What's New

1. updated setView

 

[amorosik]

The project seems very interesting
But it is really difficult to do something without following some working examples, at least for me it is impossible
I have tried several times to spend time to understand how it works, but so far I have not succeeded
It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it

 

[aeric]

The project seems very interesting
But it is really difficult to do something without following some working examples, at least for me it is impossible
I have tried several times to spend time to understand how it works, but so far I have not succeeded
It would be desirable to have some basic code examples so that we can start from an already working system, otherwise I think it is really difficult to use it

Check the B4X project template (B4A, B4i, B4J)
[B4X] [Project Template] MiniORM

There is also tutorial for using this library in server solution
[Tutorial] Using MiniORMUtils in Web API Server v3

Edit: Some examples added to first post

 

[aeric]

Version: 2.60
Size: 14KB

What's New

1. Fix unsupported function Initialized in B4i
2. Update DBOpen sub

 

[aeric]

Version: 2.61
Size: 14KB

What's New

1. Fix Close RS in Query sub (B4i)

Version: 2.62
Size: 14KB

What's New

1. Fix Close RS in Query sub (B4A)

 

[aeric]

Version: 2.63
Size: 14KB

What's New

1. Fix IfNull sub when setting default value and empty alias name

 

[aeric]

I hope this idea will work and solve a decade problem for generating ordered keys JSON

 

[aeric]

I hope this idea will work and solve a decade problem for generating ordered keys JSON

View attachment 164253

Unfortunately, I got a better idea from ChatGPT but I wrote most of the code.

 

[aeric]

Version: 3.00
Size: 14KB

This is a major update.
This may break compatibility with older app (especially not tested for B4A and B4i which will be fix in next version if there is any error).

What's New

1. Big update on Query sub
2. ORMTable type
   • Added Results2 property with "__order" key -> helps for generating ordered key JSON
   • Added Columns property as List (vs map as in ORMResults type)
   • Added Rows property (same as ORMResults type Rows)
   • Removed Row property
3. Added DBObject use in some cases instead of DBTable and DBView
4. Added Reset2 sub
5. Added Results2 sub returns the property from ORMTable
6. Added SelectAllFromDBObject sub
7. Added SelectFromTableOrView sub
8. Added JournalMode sub
9. Added ExecNonQuery sub (Private)
10. Added ExecQuery sub (Private) returns a ResultSet
11. Execute sub calls ExecNonQuery sub
12. Updated Create sub
13. Updated Insert sub
14. Updated Save sub
15. Updated Save3 sub
16. Updated Delete sub
17. Updated Destroy sub
18. Updated SoftDelete sub
19. Updated ViewExists sub
20. Updated ViewExists2 sub
21. Updated setId sub
22. Removed conditional symbol WAL
23. Removed SelectFromTable sub
24. Removed SelectFromView sub
25. Select2 sub removed - use Select instead
26. Some internal variables renamed