Recommended Orm Relational Access

nemesis567 commented 5 years ago

I'd like to know what's the recommended way to access pivot tables and related records with the ORM, if there is one and if not the recommended way of doing so in the most performant way as intended by drogon.

an-tao commented 5 years ago

Drogon has no particular abstraction about PivotTables, but I think it might be a good idea to implement PivotTables with Views + ORM. Of course, considering performance, writing SQL directly and executing it will be faster.

nemesis567 commented 5 years ago

Yes, but I'm wondering, how would I possibly do this on my tables, when they have such clear dependencies on others, particularly pivot tables, for instance every product may belong to several carts in a many-to-many relationship.

If I do use the orm, I found I'll be using multiple queries anytime I need joins, meaning I'd need to use pure SQL, and it does not seem consistent to use that for relations and the orm for joinless queries.

What would be your recommendation to keep consistency in this scenario?

Also, is there any plan to automatically generate REST controllers for the generated models, because this would save SOOOO much time, I mean if you had this, which is not particularly hard, this would be a really competitive framework as it'd be as full featured as laravel, except that so fast.

I favour the current no migrations approach, as it means you can deal with your databases in their own language and do the migration workflow separate from the rest of the project, being able to take full advantage of the existing workflows and tools for creating databases and the model generation is beautiful. I also like that it uses modern C++, which in my own opinion beats everything else both is expressivity and in completeness. I am currently building a e-commerce platform meant to compete with current offerings in the market, as none are fast.(free ones are slowpokes running on PHP, and the average ones are ASP.NET Core based with very constraining licenses)

To build the database and have the cli generate the entire REST for the application would mean that all the work I'd be doing is relative to the specifics of my application, there would be no more wasting time on boilerplate. This would become my number one go to tool for any web project and that's amazing as I finally could use C++ without the resulting productivity loss.

I'm more than certain I'm not alone in this thought, and regardless am very thankful for the work already done here, it's exquisite.

an-tao commented 5 years ago

Yes, but I'm wondering, how would I possibly do this on my tables, when they have such clear dependencies on others, particularly pivot tables, for instance every product may belong to several carts in a many-to-many relationship.

If I do use the orm, I found I'll be using multiple queries anytime I need joins, meaning I'd need to use pure SQL, and it does not seem consistent to use that for relations and the orm for joinless queries.

I can't understand what the inconsistency means, can you explain it in more detail or give an example? In fact, I am thinking about how to elegantly support join in ORM. Do you have any suggestions for this?

What would be your recommendation to keep consistency in this scenario?

Also, is there any plan to automatically generate REST controllers for the generated models, because this would save SOOOO much time, I mean if you had this, which is not particularly hard, this would be a really competitive framework as it'd be as full featured as laravel, except that so fast.

This sounds good, I will implement this feature. There are two options:

All REST controllers are generated by dg_ctl automatically;
Provide a REST controller class template that is instantiated by the model class; The first option looks more flexible, what do you think? There is another problem, if we map the database tables to the REST APIs, this means any changes to the database will affect the API, which seems to sound less decoupling. what do you think about this?

I favour the current no migrations approach, as it means you can deal with your databases in their own language and do the migration workflow separate from the rest of the project, being able to take full advantage of the existing workflows and tools for creating databases and the model generation is beautiful. I also like that it uses modern C++, which in my own opinion beats everything else both is expressivity and in completeness. I am currently building a e-commerce platform meant to compete with current offerings in the market, as none are fast.(free ones are slowpokes running on PHP, and the average ones are ASP.NET Core based with very constraining licenses)

To build the database and have the cli generate the entire REST for the application would mean that all the work I'd be doing is relative to the specifics of my application, there would be no more wasting time on boilerplate. This would become my number one go to tool for any web project and that's amazing as I finally could use C++ without the resulting productivity loss.

I'm more than certain I'm not alone in this thought, and regardless am very thankful for the work already done here, it's exquisite.

Thank you for your encouragement, I will continue to improve it.

nemesis567 commented 5 years ago

Regarding the first I'm not sure, it's an hard subject. Most systems use a query builder, please check what this means(definitely something easier to do in a more functional oriented language):

https://laravel.com/docs/5.7/eloquent-relationships //ease of use approach

https://codesynthesis.com/products/odb/ // in c++

I'm not entirely sure of how this was implemented so I can't really say much more. I understand this needs a lot of research as it would most definitely impact performance severally. Maybe instead of building the code to work around the dynamic objects generated by the join, there is a way to assist the process with something without a performance cost, like Macros or whatever else is applicable.

As for the second point and not sure there won't be issues with this but I'd go for option 1. You could generate two controllers per model, the basecontroller and another one that inherits from it, or just make best practice for the user to inherit from the generated controllers when making new changes. You could then have one cli command to generate both and another that just generates the basecontroller, so you'd have the post, delete, put, get functions(with maybe paginated overloads) and in the controller that inherits from it the developer would add any logic, or overload the functions necessary. If the model changes then the dev looses no progress and if necessary changes the inherited reference to the new generated model.

an-tao commented 5 years ago

@nemesis567 Thank you very much, I have to take the time to think carefully about these problems.

an-tao commented 5 years ago

@nemesis567 , For creating REST controllers for the generated models, I made a PR #244 . After some very simple tests, it seems to be feasible. Please take a look, thank you.

nemesis567 commented 5 years ago

Pulled the restful branch compiled but it doesn't seem to be generating the controllers with dg_ctl create model ./models

an-tao commented 5 years ago

You should modify the model.json.

    "restful_api_controllers": {
        "enabled": false,// set this option to true to generate controller source files.
        // resource_uri: The URI to access the resource, the default value 
        // is '/*' in which the asterisk represents the table name.
        // If this option is set to a empty string, the URI is composed of the namespaces and the class name.
        "resource_uri": "/*",
        // class_name: "Restful*Ctrl" by default, the asterisk represents the table name.
        // This option can contain namespaces.
        "class_name": "Restful*Ctrl",
        // filters: an array of filter names.
        "filters": [],
        // db_client: the database client used by the controller. this option must be consistent with
        // the configuration of the application.
        "db_client": {
            //name: Name of the client,'default' by default
            "name": "default",
            //is_fast: 
            "is_fast": false
        },

the option above can be obtained by creating a new project by the latest version of dg_ctl.

nemesis567 commented 5 years ago

{

    "rdbms": "mysql",
    "host": "127.0.0.1",
    "port": 3306,
    "dbname": "xx",
    "user": "xx",
    "passwd": "xxxxxxxxxx",
    "tables":[

    ],
    "restful_api_controllers": {
        "enabled": true,
        "resource_uri": "/*",

        "class_name": "URest*Ctrl",
        "filters": [],
        "db_client": {
            "name": "default",
            "is_fast": false
        },
        "directory": "controllers"
    }
}

an-tao commented 5 years ago

Did you run cmake ..? There are some new template files (for generating restful controllers) added to drogon. And don't forget to run make install.

nemesis567 commented 5 years ago

Yes, I did:

cmake -DCMAKE_BUILD_TYPE=Release ..
make && make install

I can confirm I'm building the right repo as well. I did not create a new project, just edited the models json. OUTPUT

root@PAR-****************:/usr/xx/xx/hempeno/hempeno# dg_ctl create model ./models
Create model
mysql
Connect to server...
Source files in the ./models folder will be overwritten, continue(y/n)?
y
table name:Options
table name:brands
table name:carts
(...)
table name:variations_images
table name:variations_values

an-tao commented 5 years ago

table name:array_test
create a http restful API controller:URestArrayTestCtrl
file name: URestArrayTestCtrl.h and URestArrayTestCtrl.cc
table name:camera_group
create a http restful API controller:URestCameraGroupCtrl
file name: URestCameraGroupCtrl.h and URestCameraGroupCtrl.cc
table name:camera_group_info
create a http restful API controller:URestCameraGroupInfoCtrl
file name: URestCameraGroupInfoCtrl.h and URestCameraGroupInfoCtrl.cc
table name:camera_info

The output in my system. run dg_ctl -v to check its version

nemesis567 commented 5 years ago

Version:1.0.0.beta7.1058
Git commit:6f75787083d3d3af52012503b4957b13828b5cbf

an-tao commented 5 years ago

It looks all right. Let me check. sorry for that.

nemesis567 commented 5 years ago

Sure no problem, let me know if you need more information.

an-tao commented 5 years ago

@nemesis567 Please check the latest commit. thanks. currently, I've not added subclasses of controllers yet.

nemesis567 commented 5 years ago

Very good work, no doubt. :clap: :clap: :clap: It generated all controllers without issue, I'll be using this and figure out if there any major issues persists. Did you have any idea yet regarding relationships?

an-tao commented 5 years ago

@nemesis567 Thank you ^.^ I think it is necessary to add subclasses for customization. I will add them today. Do you think if it is useful to add fields parameter when using GET method on a REST resouce? like this:

GET /cars?fields=manufacturer,model,id,color

Regarding relationships, I tried to create some Joiner (what name is appropriate?) class templates for them. There are some details to consider, and I think it will be done in a week or two. If you have any idea about drogon, please be free to let me know, thanks and welcome any PR on drogon :)

nemesis567 commented 5 years ago

For rest, yes, I agree that's important. It's also important:

Sorting by fields :heavy_check_mark:
Partial updates :heavy_check_mark:
Field validation for non null fields on create :interrobang:
Field filters(what you said) :interrobang:
Maximum, Minimum per field(does the orm already have this feature?) ⁉️
Between per field(basically WHERE v < max and v > min SQL selection) ⁉️

an-tao commented 5 years ago

For rest, yes, I agree that's important. It's also important:

Sorting by fields ✔️

Partial updates ✔️

Field validation for non null fields on create ⁉️

I'll add this right now.

Field filters(what you said) ⁉️

I'll add this right now.

Maximum, Minimum per field(does the orm already have this feature?) ⁉️

Currently, the meta data of this is not extracted into model classes, I'll add this later.

Between per field(basically WHERE v < max and v > min SQL selection) ⁉️

I'll add this. Thank you very much.

nemesis567 commented 5 years ago

Let's take the following tables:

Carts
CartsProducts
Product
Sku
Reviews

With the following relationships:

One product has one sku. One sku belongs to one product. The sku has a foreign key to product id. This is the hasOne example bellow.
One cart has many products and products belong to many carts. One CartProduct belongs to one cart and one product. The CartProduct has a foreign key to cart_id in the carts table and another to product_id in the Products table. This is the many-to-many example bellow.
One product has many reviews. Each review belongs to one product. The reviews table has one foreign key to the product id in the products table. This is the hasMany example bellow.

Regarding relationships, have you considered this: In the model.json add a new array called relationships. Possible syntax:

[std::string RELATIONSHIPNAME,std::string ORIGIN_TABLE, std::string FOREIGN_REFERENCED_TABLE, std::string RELATIONSHIP_ALIAS, std::string RELATIONSHIP_REVERSE_ALIAS, bool DECLARE_REVERSE]

//For each referenced table find it's foreign keys to determine to which relationship do they belong. With the origin and target table names you can figure out which key belongs to which relationship.

["hasOne", "table_name", "target_table_name",  "alias","reversealias", true],

//If the table name is present while generating models create a new method, get_target_table_name or get_alias, for table_name model returning a target_table_name model. 
//If DECLARE_REVERSE is true, declare a new method, get_table_name or get_reversealias, for target_table_name model returning a table_name model.

["hasMany", "table_name", "target_table_name", "alias", "reversealias",true],

//If the table name is present while generating models create a new method, get_target_table_names or get_alias, for table_name model returning a list of target_table_name model. 
//If DECLARE_REVERSE is true, declare a new method, get_table_name or get_reversealias, for target_table_name model returning a table_name model.

[std::string RELATIONSHIPNAME,std::string ORIGIN_TABLE, std::string PIVOT_TABLE_NAME, std::string FOREIGN_REFERENCED_TABLE, std::string RELATIONSHIP_PIVOT_ALIAS, std::string RELATIONSHIP_ALIAS, std::string RELATIONSHIP_REVERSE_ALIAS, bool DECLARE_REVERSE]

["ManyToMany", "table_name", "pivot_table_name", "target_table_name","pivot_alias", "alias","reversealias", true]

//If the table name is present while generating models create a new method, get_pivot_table_name or get_pivot_alias, for table_name model returning a list of pivot_table_name model.s 
//If DECLARE_REVERSE is true, declare a new method, get_pivot_table_name or get_pivot_alias, for target_table_name model returning a list of pivot_table_name models.
//If the table name is present while generating models create a new method, get_target_table_name or get_alias, for table_name model returning a list of target_table_name model. 
//If DECLARE_REVERSE is true, declare a new method, get_table_name or get_reversealias, for target_table_name model returning a list of table_name models.

//Polymorphic relationships don't seem like a very good thing as you either have two null fields and check which one is set, which would work with everything above, or you have to store the type.
//Being like this most of the time there is no real advantage to these at least I haven't encountered them yet, but if you want to implement them. I guess they only become reasonable when there are more than two different types.

So we should have:

product->getSku() automatically mapping to a select sku.
product->getReviews() getting the product reviews.
product->getProductCarts() getting the pivot table models.
product->getCarts() getting the carts where the product exists.
cart->getProducts(), getting the carts products.
cart->getProductCarts() getting the pivot model.
review->getProduct() getting the product the review belongs to.

There are benefits to having these all hard coded as there is no dynamic management overhead. Now there is a problem when we want to use complex queries to retrieve related models as these methods would be doing multiple queries.

As such I think it'd be relevant to define the relationships themselves in code, so that their role would be assembling the necessary queries when the intention is not to retrieve a related model but to use this relation in a more complex query.

For example product->reviews()->findBy().

By having some sort of definition of the reviews relationship, we could build a query at once that filters all the skus that belong to the given product. This query helper would not return any result until a GET type of query is applied, like findBy.

reviews() would return a static class that would have the job of binding the SQL together in a single query. You could do all above through such a class, but you'd need to figure out a way for it to work reversed.

Also for all above I favour written code, even if it becomes messy, results in more space or is harder to maintain, than performance loss related to dynamic typing or reflection.

Furthermore

[x] Add command to selectively recreate a single model or controller.

[x] Manual Field validation to controllers(allow to manually 'tag' fields with a validation function on setters). Say I have an e-mail fields, I want to make sure create and update methods validate the field with my function to make sure it's an e-mail.

[ ] Background service boilerplate class to be inherited from, with safe interface to the main app thread and associated services.

Please read whatever I say with a grain of salt, this is just a rough overview I came up with, definitely not with enough consideration, but in hopes that it fits somewhere along the right path to go.

an-tao commented 5 years ago

Thank you very much, there are many good ideas in your comments, I will implement them step by step.

So much work to do ;-) I am afraid that it will take a lot of time to implement all.

So, as what you said, we prefer to use model APIs for queries instead of using Mapper templates? For now model classes are just data abstractions (All queries are executed by Mappers), so there are a lot of work related to model generation.

Maybe we can provide some methods, such as Product::findBy(...., bool withSku=false) to avoid multiple queries, what do you think?

BTW: I value performance very much, all the reflections are done in the initial phase of drogon applications. Did you see the letast tfb benchmark result?

nemesis567 commented 5 years ago

I agree with the static methods for the model classes. I am talking on the model level, not questioning whether the underlying query building happens in a different class as long as it's low overhead. Basically, what the static relationship classes I was talking about earlier would do is provide an abstraction similar to the Mapper, basically a query building utility. Now I am not entirely sure how the mapper works yet so I'll have to look into it before going further.

Yes, I have seen the benchmark, and while interesting I'd rather see how this would behave in production.

kapilpipaliya commented 5 years ago

can you add this type of api? auto user = User.admin().first() will give first admin. on User model we defined a scope: scope :admin, -> { where(admin: true) }(not c++) this makes finding users by different scopes very easy.

you can use a function to read data part from file, before writing new content to the file. <%= get_part_comman(dest_file, "// PartScope") %>

std::string get_part_comman(std::string dest_file_name, std::string word){
//use the regex to read the part.
/(.*word(?m:.*?)word)/
// https://rubular.com/r/qINLri3ddLxlMP
}

an-tao commented 5 years ago

Now all operations are executed with the Mapper template, model classes haven't queries-related methods. You should do this by using findBy() method :

mapper.limit(1).findBy(Criteria(Users.Cols._admin, true) ... );

nemesis567 commented 5 years ago

@an-tao Please check e-mail.

an-tao commented 5 years ago

@nemesis567 I have added the request validations and field selections to the REST APIs.

an-tao commented 5 years ago

@nemesis567 I added hasOne relationship in ORM.

drogonframework / drogon

Recommended Orm Relational Access #241

For example product->reviews()->findBy().

Furthermore