Wizard of Orm

aeric

aeric

Expert
Licensed User
Longtime User
Project is still in early development.
Here is a teaser.

Wizard of Orm
 
table

Code: Copy 
{
    "charset": "CHARSET=utf8mb4",
    "dialect": "mysql",
    "engine": "InnoDB",
    "columns": [
        {
            "full_type": "int(11)",
            "nullable": false,
            "auto_increment": true,
            "precision": "",
            "scale": "",
            "default_value": "",
            "primary_key": false,
            "raw_definition": "`id` int(11) NOT NULL AUTO_INCREMENT",
            "type_parameters": "11",
            "collate": "",
            "size": "11",
            "unique": false,
            "name": "id",
            "data_type": "int"
        },
        {
            "full_type": "varchar(255)",
            "nullable": true,
            "auto_increment": false,
            "precision": "",
            "scale": "",
            "default_value": "NULL",
            "primary_key": false,
            "raw_definition": "`category_name` varchar(255) DEFAULT NULL",
            "type_parameters": "255",
            "collate": "",
            "size": "255",
            "unique": false,
            "name": "category_name",
            "data_type": "varchar"
        },
        {
            "full_type": "timestamp",
            "nullable": true,
            "auto_increment": false,
            "precision": "",
            "scale": "",
            "default_value": "current_timestamp()",
            "primary_key": false,
            "raw_definition": "`created_date` timestamp NULL DEFAULT current_timestamp()",
            "type_parameters": "",
            "collate": "",
            "size": "",
            "unique": false,
            "name": "created_date",
            "data_type": "timestamp"
        },
        {
            "full_type": "datetime",
            "nullable": true,
            "auto_increment": false,
            "precision": "",
            "scale": "",
            "default_value": "NULL",
            "primary_key": false,
            "raw_definition": "`modified_date` datetime DEFAULT NULL ON UPDATE current_timestamp()",
            "type_parameters": "",
            "collate": "",
            "size": "",
            "unique": false,
            "name": "modified_date",
            "data_type": "datetime"
        },
        {
            "full_type": "datetime",
            "nullable": true,
            "auto_increment": false,
            "precision": "",
            "scale": "",
            "default_value": "NULL",
            "primary_key": false,
            "raw_definition": "`deleted_date` datetime DEFAULT NULL",
            "type_parameters": "",
            "collate": "",
            "size": "",
            "unique": false,
            "name": "deleted_date",
            "data_type": "datetime"
        }
    ],
    "table_name": "TBL_CATEGORIES",
    "constraints": [
        {
            "columns": [
                "id"
            ],
            "type": "PRIMARY_KEY",
            "raw_definition": "PRIMARY KEY (`id`)"
        }
    ]
}
 
Last edited:
Some "early" remarks on this table definition:
- id field: primary_key setting is false? Is this the primary key value? ; unique setting is false? for a primary key this should be true, right?
- category_name field: nullable setting is true? This is the only field in this table with actual values. Can you insert a record with no category name?
- if this table contains a lot of records then the category_name field should be indexed. There is no setting for it yet.
- in a more complex database structure the foreign key constraints play an important role when updating or deleting records. Maybe there could be a foreign_key setting?
 
PaulMeuris said:
Some "early" remarks on this table definition:
- id field: primary_key setting is false? Is this the primary key value? ; unique setting is false? for a primary key this should be true, right?
- category_name field: nullable setting is true? This is the only field in this table with actual values. Can you insert a record with no category name?
- if this table contains a lot of records then the category_name field should be indexed. There is no setting for it yet.
- in a more complex database structure the foreign key constraints play an important role when updating or deleting records. Maybe there could be a foreign_key setting?
Click to expand...
Thanks Paul!

One of the features of this "wizard" library is to parse SQL from existing table schema.

I just show an example from a MariaDB/MySQL database generated using MiniORMUtils in Pakai framework.
The latest MiniORMUtils library (v3.80) has added new function to get the table schema and pass to this library.
The original query I use to create the table doesn't specify the primary key in first column but specified as a CONSTRAINT in the last line.

MySQL/MariaDB:
SQL: Copy 
CREATE TABLE `tbl_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) DEFAULT NULL,
  `created_date` timestamp NULL DEFAULT current_timestamp(),
  `modified_date` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQLite:
SQL: Copy 
CREATE TABLE tbl_categories (id INTEGER,
category_name TEXT NULL,
created_date TEXT DEFAULT (datetime('now')),
modified_date TEXT,
deleted_date TEXT,
PRIMARY KEY(id AUTOINCREMENT))

For both cases, they are generated using the same code with MiniORMUtils.
Code: Copy 
Dim DB As MiniORM
DB.Initialize(Engine, Open)
DB.UseTimestamps = True
DB.Table = "tbl_categories"
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "category_name")))
DB.Create
The table Column Definitions are handled by default.
More attributes can be specified, such as NOT NULL, UNIQUE, DEFAULT and so on.
If the developer wants to add NOT NULL, then the code can be updated to:
B4X: Copy 
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "category_name", "Nullable": False)))

For the Constraint like Foreign Key, it is also supported.
Since this "categories" table is consider a "Master" table, it doesn't need a foreign key.
However, the "products" table as a "Detail" table has a foreign key referenced to the categories table id.
Code: Copy 
DB.Table = "tbl_products"
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "category_id", "Type": DB.INTEGER)))
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "product_code", "Length": "12")))
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "product_name")))
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "product_price", "Type": DB.DECIMAL, "Length": "10,2", "Default": "0.00")))
DB.Columns.Add(DB.CreateColumn2(CreateMap("Name": "product_image", "Type": DB.BLOB)))
DB.Foreign("category_id", "id", "tbl_categories", "", "")
DB.Create

All these keywords are already supported in MiniORMUtils library.
It all depends to the developer.

MiniORMUtils is use for quick scaffolding or prototyping a new app.
The generated table can be modified further using other database management tool such as DB Browser for SQLite or PHPMyAdmin/Adminer for MySQL.
This library doesn't support indexing for the moment because I just want to make it as simple as possible.

Back to the main point.
This "wizard" library retrieves the information such list of all column names and types from a database table so it can be use to generate API endpoints or create a web form.

Sure the library need more improvement.
Maybe not all the info inside the generated json file is useful or more keys will be added or removed in the future.
I think I am going to use only certain key-values from it to create a "wizard".

This library should also work without using MiniORMUtils.
 
First UI concept.
List of tables in the database is populated automatically into a dropdown list.
Developer can select which columns to show and click Generate Code button.
We can generate the code for building the frontend and CRUD operation.
The UI and code for each column or field is depending on the column type TEXT, INT, DOUBLE or DATE if column name contains "date".
Required fields can be default to true if the column is NOT NULL.
More column settings or validation can also be added.

1767021026645.png
 
Trace back previous discussion:
aeric

Post in thread '[Project Template] Web API Server NEXT'

Mashiane said:
Build a wizard
Click to expand...
I have been thinking of the similar idea for a while as a plug-in library or tool.

I always unable to decide on which frontend UI and library to use.

Even though I am not good in jquery but it is what I feel more comfortable to work with.

I am thinking to create version 1 which is still based on boostrap4 + jquery and version 2 will be based on bootstrap5 + htmx.

The wizard will be in a web interface.
  • Like
 
You must log in or register to reply here.
Back
Top