B4J Library [B4X] MiniORMUtils

MiniORMUtils
Version: 4.00

---

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 (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]

Testing this version 3 on MiniORM B4X template but getting error when creating database using ExecuteBatch.
Not sure why it used to work but not now.
I will use a different approach to solve this issue instead of troubleshooting the root cause.

 

[aeric]

Testing this version 3 on MiniORM B4X template but getting error when creating database using ExecuteBatch.
Not sure why it used to work but not now.
I will use a different approach to solve this issue instead of troubleshooting the root cause.

Found the issue.
I can't use DBParameters straight away. This can cause an issue of pass by reference.

Public Sub AddNonQueryToBatch
    Dim paramsize As Int = ParametersCount
    Dim Args(paramsize) As Object
    Dim i As Int
    For Each Param In DBParameters
        Args(i) = Param
        i = i + 1
    Next
    DBSQL.AddNonQueryToBatch(DBStatement, Args)
    'DBSQL.AddNonQueryToBatch(DBStatement, DBParameters) ' Wrong
End Sub

 

[aeric]

Version: 3.20
Size: 14KB

This version should fix some bugs when use for B4A and B4i projects.
It has been tested to use on [B4X] [Project Template] MiniORM.

 

[aeric]

Have you ever working with SQLite BLOB in B4i, B4A or B4J?

Next version will support BLOB.

 

[aeric]

Version: 3.30
Size: 14KB

Another major update!

What's New

1. BLOB column is now supported
2. ColumnsType property is added as Map of column name (or alias) as key and column type as value.
3. Specifying the ColumnsType is highly recommended especially when working with BLOB. It will fail in B4A if attempt to convert BLOB to String.
4. Parameters variable is now array of Object. It is no longer a List in B4J nor array of String in B4A and B4i.
5. Most subs are affected by #4 and updated
6. Query sub updated
7. DBUtils and JSON libraries are no longer required for B4A and B4i. These libraries have been removed in manifest.txt
8. First property is not returning a default Map
9. Execute2 sub added

 

[aeric]

Version: 3.40

What's New

1. MariaDB is supported
2. BLOB column is default to mediumblob for MariaDB and MySQL
3. Update ExecQuery sub
4. Update code example in B4XMainPage
5. Added example for testing MariaDB
6. Added InitSchema2
7. Added LogQuery3 sub to print out DBStatements and parameters in DBBatch
8. ShowExtraLogs in ExecuteBatch using LogQuery3
9. Update README.md
10. Change comment links to #Macro tags

 

[aeric]

Version: 3.50

What's New

1. Added getFirst2 sub As Map
2. Updated Query sub

 

[aeric]

Version: 3.60

What's New

1. Changed Error property from String type to Exception type
   • You can now check Error with
     
     DB.Error.IsInitialized
   • and then get the description using
     
     DB.Error.Message
2. ExecQuery sub has added with Try-Catch
3. ExecNonQuery sub has added with Try-Catch
4. Added #Macro tags
   • #Macro: Title, Release, ide://run?File=%WINDIR%\SysWOW64\explorer.exe&Args=%PROJECT%\..\..\release
   • #Macro: Title, Sync, ide://run?File=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
   • #Macro: Title, Publish, ide://run?File=%JAVABIN%\jar.exe&WorkingDirectory=..\..\..\release&Args=-cMf&Args=%ADDITIONAL%\..\B4X\%PROJECT_NAME%.b4xlib&Args=*

 

[LucaMs]

More examples on GitHub README page

Hi @aeric,

I was wondering: "More examples," but where's an example here?
Then I downloaded the library and the project template and realized the template itself "is" the example.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

Two more little things.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Finally, a superfluous suggestion because it's obvious you're an expert:

'con.DBType = "MySQL"

Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants (Erel, are you reading this? Why don't you implement Enums? )

 

[aeric]

Hi @aeric,

I was wondering: "More examples," but where's an example here?
Then I downloaded the library and the project template and realized the template itself "is" the example.

It's both a mistake and a useful thing that the template contains an actual project.
It's useful because you immediately have a complete example;
it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.
This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

Two more little things.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Finally, a superfluous suggestion because it's obvious you're an expert:

'con.DBType = "MySQL"

Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants (Erel, are you reading this? Why don't you implement Enums? )

I am happy to see questions!
Let me answer them one by one.

I was wondering: "More examples," but where's an example here?

There are actually indeed more examples on GitHub Readme.
The extra examples are:

1. Soft delete row
2. Permanent delete row
3. Batch delete rows
4. Return number of rows in query results
5. Return single row
6. Return multiple rows
7. Join tables

Then I downloaded the library and the project template and realized the template itself "is" the example.

Yes! You are right.
This template [B4X] [Project Template] MiniORM is a complete working project.

It's both a mistake and a useful thing that the template contains an actual project.

It's useful because you immediately have a complete example;

Yes, because I think it can be a good starter project to extend further for many more purposes. e.g as a POC.

it's a mistake because a template should be almost entirely empty, otherwise it requires great care in eliminating anything you don't need.

My concern is developers new to MiniORM will face difficulty to understand the concept.
The project template can let them see an immediate result without starting to read the documentation. It is almost zero configuration.
In fact, my real world projects are much more complicated.

Another advantage of project template is you can use any name for the project and usually created in a frequently use development path (more organized).

There are more reasons and benefits of project template instead of a "zipped" project file but I stopped here.

By the way, the main purpose of this project template is to showcase one of the uses of this library; for creating B4X cross platforms client apps (B4A, B4i, B4J).
Whilst MiniORMUtils can be use for UI apps for B4X, it is also part of the dependencies of Pakai framework which is a non-ui web server solution.

MiniORMUtils has a great "history".
Actually there is a more powerful library (now call CapORM) which is not generally available.
It supports wider database type i.e MS SQL Server, PostgreSQL and Firebird instead of only SQLite and MySQL/MariaDB
MiniORM is just a sub library.

The project template is guaranteed in high quality, well tested and clean of redundant files.
The file size is very small.
If you want, you can modify the template or remove the parts you think you don't want.
Then create your own version. The freedom is on your hand.

This is the mistake I made recently when I thoughtlessly created the B4XPagesAndDrawer template, incorporating Erel's entire example into it.

I don't agree it is a mistake.
Project Template can also be use as DRY principle (Don't Repeat Yourself).
It is a good way to start a project from a clean but included with features that are ready to be used.
My Pakai Server template is the best example I can think of.
Everything is ready to run. I can extend the generated boilerplate to many types of server apps.
You help other developers saving their time looking for other piece of puzzle.

I haven't been on GitHub very often (a shortcoming I need to address); where can I find the other examples (although the template is enough for me, I'm asking you specifically to understand GitHub)?

Mentioned above, Pakai Server is the best mate for MiniORMUtils. You can just run it with SQLite backend. Zero configuration.
Another example is User Login Server but it is based on older version of libraries.

Instead of having to write the DBType as a literal (DBType doesn't suggest which ones), it would be better to provide constants

The constants are already existed in MiniORM class.

Public Const MYSQL As String = "MYSQL"
Public Const SQLITE As String = "SQLITE"
Public Const MARIADB As String = "MARIADB"

For other questions, I welcome you to start a new thread.

 

[aeric]

Version: 3.70

What's New

1. Handle error when ExecQuery failed in Query sub
2. Pass Exception to Error property in subs:
   • DBCreate
   • InitPool
   • InitSchema
   • DBExist2
   • DBOpen2
   • GetDate
   • GetDate2
   • GetDateTime
   • GetDateTime2
3. Add #Macro tag in B4XMainPage:
   • #Macro: Title, Copy, ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=copy&Args=%PROJECT%\..\*ORM*.bas&Args=%PROJECT%\..\..\release\

 

[aeric]

Version: 3.80

What's New​

1. Added Sub ListTables
2. Added Sub ListTables2
3. Added Sub ShowCreateTable
4. Added Sub ShowCreateTable2
5. Remove redundant code in "release" folder. It is now only keep compressed b4xlib.
6. Remove unused color constant (MiniORM)
7. Update build configuration (B4J)
8. Update #AdditionalJar based on conditional compilation (Main)
9. Sub ConfigureDatabase (B4XMainPage)
   • Update ConnectionInfo based on conditional compilation
   • Update Conn.InitPool based on DBType
   • Update #If B4J conditional compilation to exclude code for MySQL/MariaDB in B4A/B4i
   • Update targetSdkVersion = 35 for B4A
10. Fix default (datetime('now')) for SQLite in subs (MiniORM)
    • Create
    • Insert
    • Save
    • Save3
11. Type ORMColumn (MiniORM)
    • Added property UseFunction As Boolean
12. Sub Create (MiniORM)
    • Updated code for DefaultValue logic
    • Updated code for BlnAutoIncrement logic
13. Sub Save (MiniORM)
    • Do not return NewID if BlnAutoIncrement = False
14. Sub CreateColumn (MiniORM)
    • Added code for UseFunction logic
15. Sub CreateColumn2 (MiniORM)
    • Added code for UseFunction logic
16. Macro tags (B4XMainPage)
    • Update Version
    • Create B4xLib
    • Release folder
    • Copy to AddLibs

 

[aeric]

Version: 3.90

What's Updated​

1. setGroupBy sub
2. Save3 sub

What's Changed​

1. setColumns sub is replacing setSelect sub
2. SelectOnly sub renamed to FirstPick
3. SelectOnly marked as deprecated
4. setSelect marked as deprecated
5. Some variables name
6. Some logs message

 

[aeric]

Version: 4.00

This is a breaking changes!

To be updated

What's Updated​

1. ORMConnector is removed
2. -

What's Changed​

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

Create ConnectionInfo object

Dim info As ConnectionInfo
info.Initialize
info.DBType = "SQLite"
info.DBFile = "data.db"

Create ORMConnector object

Dim conn As ORMConnector
conn.Initialize(info)

Initialize MiniORM

Dim DB As MiniORM
DB.Initialize(DBType, DBOpen)
DB.QueryAddToBatch = True

Check if database exist

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

Dim DBFound As Boolean = conn.DBExist
If DBFound = False Then
    LogColor($"${conn.DBType} database not found!"$, COLOR_RED)
    CreateDatabase
End If

Create Database

Dim Success As Boolean = DB.InitializeSQLite

Dim Success As Boolean = conn.DBCreate