Wizard of Orm

[aeric]

Project is still in early development.
Here is a teaser.

 

[aeric]

{
    "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`)"
        }
    ]
}

 

[PaulMeuris]

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?

 

[aeric]

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?

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:

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:

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.

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:

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.

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.