[WIP] Support for AutoMigrate SQL

[reski-rukmantiyo]

To complete about SQL CRUD journey, i think it's better to create similiar like AutoMigrate in Gorm.
The reason is that to make consistent values between golang struct and sql table without manual interference.

The Automigrate function that I propose

• Table and column name in JSON will be converted into SnakeCase
• Support for drop table options if options true
• Suport to convert multiple structs at one time
• support for primary_key, auto_increment, not_null, unique, index, and unique_index,check,comment in gofr tags
• support for int, float, bool, string and time.Time in JSON type
• support foreign key as well

Example golang struct

type Order struct {
	ID          int       `json:"id" gofr:"primary_key,auto_increment"`
	CustomerID  int       `json:"customer_id" gofr:"not_null,index,fk(Customers:ID)"`
	OrderDate   time.Time `json:"order_date" gofr:"not_null"`
	OrderAmount float64   `json:"order_amount" gofr:"not_null,check(order_amount > 0)"`
	Notes       string    `json:"notes" gofr:"size(1000),comment(This is a note field)"`
}

Output of create table SQL statement

CREATE TABLE Order (
	ID INT PRIMARY KEY AUTO_INCREMENT,
	CustomerID INTEGER NOT NULL,
	OrderDate DATETIME NOT NULL,
	OrderAmount REAL NOT NULL CHECK (order_amount > 0),
	Notes VARCHAR(1000) COMMENT 'This is a note field',
	FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

CREATE INDEX idx_Order_CustomerID ON Order (CustomerID);

Code: #724

Please comments for these features

[aryanmehrotra]

Support for Drop table options if options True can you describe the use case for this?

[reski-rukmantiyo]

Support for Drop table options if options True can you describe the use case for this?

Sometimes we need to override existing table if necessary especially when we're in development stage.
Just need to pass true in dropIfExists parameter

func GenerateCreateTableSQL(structType interface{}, dbType string, dropIfExists bool) (string, error) {
}

[reski-rukmantiyo]

Update by today,

• remove check constraint and change it into trigger statetement
• foreign table and column will convert into snake case name

Example golang struct

type Charge struct {
	ID             uint64    `json:"id" gofr:"primary_key,auto_increment"`
	OrganizationID int       `json:"organizationid" gofr:"fk(Organization:ID),not_null"`
	Period         time.Time `json:"period" gofr:"not_null"`
	Count          uint64    `json:"count" gofr:"unique,not_null,check(Count >= 0)"`
	ModelID        int       `json:"modelid" gofr:"fk(Model:ID),not_null,comment(this is a model ID for charge table)"`
	Comment        string    `json:"comment" gofr:"size(128),unique"`
}

will be

DROP TABLE IF EXISTS charge;

CREATE TABLE charge (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INTEGER NOT NULL,
    period DATETIME NOT NULL,
    count INTEGER UNIQUE NOT NULL,
    model_id INTEGER NOT NULL COMMENT 'this is a model ID for charge table',
    comment VARCHAR(128) UNIQUE,
    FOREIGN KEY (organization_id) REFERENCES organization(id),
    FOREIGN KEY (model_id) REFERENCES model(id)
);

CREATE TRIGGER check_count_before_update
BEFORE UPDATE ON charge
FOR EACH ROW
BEGIN
    IF Count >= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Count >= 0';
    END IF;
END;

[aryanmehrotra]

Support for Drop table options if options True can you describe the use case for this?

Sometimes we need to override existing table if necessary especially when we're in development stage. Just need to pass true in dropIfExists parameter

func GenerateCreateTableSQL(structType interface{}, dbType string, dropIfExists bool) (string, error) {
}

How will the user use this?

[reski-rukmantiyo]

Support for Drop table options if options True can you describe the use case for this?

Sometimes we need to override existing table if necessary especially when we're in development stage. Just need to pass true in dropIfExists parameter

func GenerateCreateTableSQL(structType interface{}, dbType string, dropIfExists bool) (string, error) {
}

How will the user use this?

I revise the function into

func (a *App) AutoMigrate(dropIfExists bool, structs ...interface{}) error {
}

therefore user can access dropIfExists parameter to access the feature

[Umang01-hash]

@reski-rukmantiyo after some discussion with the team we have came to a point that at a particular time we don't want to deep dive into all the supported functionalities for SQL CRUD feature but we should first completely implement some basic necessary features and then we will keep updating and upgrading it with next advanced features.

For now please refer to below points :

• We currently don't need Support for drop table options if options true as the user can change the db schema or drop the table when in development process without much hassle. We will see if we will require it for advanced features.

• Also the need for Suport to convert multiple structs at one time is not required as user can call the method CRUDFromStruct multiple time with different structs.

• support for primary_key, auto_increment, not_null, unique, index, and unique_index,check,comment in gofr tags yes we will need the support for these sql constraints in our tags but we prefer sql instead of gofr as tag name.

• support for int, float, bool, string and time.Time in JSON type this is required. (keep this in a different PR)

• support foreign key as well this is also a valid requirement.

Please work on adding the necessary things first and after that you can raise separate PR's for other advanced features.

[reski-rukmantiyo]

@reski-rukmantiyo after some discussion with the team we have came to a point that at a particular time we don't want to deep dive into all the supported functionalities for SQL CRUD feature but we should first completely implement some basic necessary features and then we will keep updating and upgrading it with next advanced features.

For now please refer to below points :

• We currently don't need Support for drop table options if options true as the user can change the db schema or drop the table when in development process without much hassle. We will see if we will require it for advanced features.
• Also the need for Suport to convert multiple structs at one time is not required as user can call the method CRUDFromStruct multiple time with different structs.
• support for primary_key, auto_increment, not_null, unique, index, and unique_index,check,comment in gofr tags yes we will need the support for these sql constraints in our tags but we prefer sql instead of gofr as tag name.
• support for int, float, bool, string and time.Time in JSON type this is required. (keep this in a different PR)
• support foreign key as well this is also a valid requirement.

Please work on adding the necessary things first and after that you can raise separate PR's for other advanced features.

Noted @Umang01-hash , by the end of this week i will separate

• Support for drop table options if options true
• Suport to convert multiple structs at one time

While keep

• support for primary_key, auto_increment, not_null, unique, index, and unique_index,check,comment in gofr tags : change into sql meta tag
• support for int, float, bool, string and time.Time in JSON type
• support foreign key as well

Please confirm

[Umang01-hash]

Sure @reski-rukmantiyo moreover i will suggest let's do the second one first:

• support for primary_key, auto_increment, not_null, unique, index, and unique_index,check,comment in gofr tags : change into sql meta tag
• support for int, float, bool, string and time.Time in JSON type
• support foreign key as well

and merge it and then you can pick up the first