4 ms·

How does your query builder work? Is it an API that you use to build queries during runtime or do you generate database access code from queries?

hparadiz 5y ago
The framework has 3 layers, Query Builder -> ActiveRecord based ORM -> CRUD Controllers The CRUD controllers are then mapped with a router to different paths. When you create a CRUD controller you extend the base class which has everything you need and then you set a single parameter, className which sets the object for which this CRUD controller is for. The base class works out of the box with front ends like agGrid. The query builder itself is only part of the puzzle. Every single ORM in existence is converting the object's data from the native types of the language into the types of the database and vice versa. There's almost always some configuration for each object type in whatever format from native code to yaml to xml to plain text. Every ORM has a toDatabaseValue() normalization function that also goes in reverse when pulling data from the database. Some ORMs will call this "data mapper" or "mapping" or "normalization". When this becomes evident the queries themselves become suuuuper simple because what you send into your INSERT or UPDATE queries is a complete array of strings with field values already predetermined. This is necessary because a database engine might have dozens of types like enum, string, text, longtext,smalltext,bigtext,varchar, char but all of them in your language could be "string". Advanced ORMs will know the database engine you use and even warn you if you try to save a string over 255 chars to a (255) char database engine but most don't. My code is available here. https://github.com/Divergence/framework/blob/6af1b6b0e56b25cbcfc4e9e13aea1f77477da972/src/Models/ActiveRecord.php#L716-L735 https://github.com/Divergence/framework/blob/6af1b6b0e56b25c... Where people go wrong is trying to do this field normalization logic inside their query builder. That leads to all sorts of problems. The query builder shouldn't know what is going on with your ORM at all. It's just gluing strings together to form a query. You can see my dead simple query builder here: https://github.com/Divergence/framework/tree/develop/src/IO/Database/Query https://github.com/Divergence/framework/tree/develop/src/IO/... I honestly used to not have one as it's so simple but decided it was a good abstraction to assimilate from other frameworks. My query builder does not attempt to sanitize anything. The ActiveRecord class takes care of that through the data mapping conversion functions for all the basic HTTP CRUD functionality.
- deleted 5y ago
  [deleted]
- unclebucknasty 5y ago
  Interesting. Thanks for sharing! I wrote something for Java roughly 20 years ago (before ORM was really a thing). Of course I enhanced as needed over the years, but have used it heavily. It supports any DB with a JDBC driver and has easily saved me more time than anything else. There's not even a close second. It's a pure code generator with a UI on top. You write an SQL select stub that references columns/tables for one or more tables, then provide the class name you want to generate. You then add any number of where clauses (including join conditions and parameterized placeholders). For each where clause, you use checkboxes to indicate what you want generated: select, update, insert, upsert, delete. When the code is generated, all column-to-object field mappings, including types, are resolved through inspection of metadata (provided by the JDBC driver). Property names for the resulting domain objects are also generated using snake to camel case translations and, of course, methods resulting from parameterized statements include parameters of the correct types. So, instead of having, say, a User class with a Profile class, which has an Image class, each with its own properties representing the full set of columns for the underlying table, you frequently need a limited subset of each combined into one object. For instance, a ProfileListView might need username, email, first and last from user; profile_pic_id, slug, and short_bio from profile; and thumb_filename from image. There's more to it, but I took this join approach vs the "one table to one object" model because I found early that you frequently need different views of the data, based on different use cases. So, it's a way to flatten the underlying relational model when needed. Not perfect, of course, but the best thing going when I built it. And since, I never found an ORM that was worth the switch.

hparadiz 5y ago

The framework has 3 layers, Query Builder -> ActiveRecord based ORM -> CRUD Controllers The CRUD controllers are then mapped with a router to different paths. When you create a CRUD controller you extend the base class which has everything you need and then you set a single parameter, className which sets the object for which this CRUD controller is for. The base class works out of the box with front ends like agGrid. The query builder itself is only part of the puzzle. Every single ORM in existence is converting the object's data from the native types of the language into the types of the database and vice versa. There's almost always some configuration for each object type in whatever format from native code to yaml to xml to plain text. Every ORM has a toDatabaseValue() normalization function that also goes in reverse when pulling data from the database. Some ORMs will call this "data mapper" or "mapping" or "normalization". When this becomes evident the queries themselves become suuuuper simple because what you send into your INSERT or UPDATE queries is a complete array of strings with field values already predetermined. This is necessary because a database engine might have dozens of types like enum, string, text, longtext,smalltext,bigtext,varchar, char but all of them in your language could be "string". Advanced ORMs will know the database engine you use and even warn you if you try to save a string over 255 chars to a (255) char database engine but most don't. My code is available here. https://github.com/Divergence/framework/blob/6af1b6b0e56b25cbcfc4e9e13aea1f77477da972/src/Models/ActiveRecord.php#L716-L735 https://github.com/Divergence/framework/blob/6af1b6b0e56b25c... Where people go wrong is trying to do this field normalization logic inside their query builder. That leads to all sorts of problems. The query builder shouldn't know what is going on with your ORM at all. It's just gluing strings together to form a query. You can see my dead simple query builder here: https://github.com/Divergence/framework/tree/develop/src/IO/Database/Query https://github.com/Divergence/framework/tree/develop/src/IO/... I honestly used to not have one as it's so simple but decided it was a good abstraction to assimilate from other frameworks. My query builder does not attempt to sanitize anything. The ActiveRecord class takes care of that through the data mapping conversion functions for all the basic HTTP CRUD functionality.

deleted 5y ago
[deleted]
unclebucknasty 5y ago
Interesting. Thanks for sharing! I wrote something for Java roughly 20 years ago (before ORM was really a thing). Of course I enhanced as needed over the years, but have used it heavily. It supports any DB with a JDBC driver and has easily saved me more time than anything else. There's not even a close second. It's a pure code generator with a UI on top. You write an SQL select stub that references columns/tables for one or more tables, then provide the class name you want to generate. You then add any number of where clauses (including join conditions and parameterized placeholders). For each where clause, you use checkboxes to indicate what you want generated: select, update, insert, upsert, delete. When the code is generated, all column-to-object field mappings, including types, are resolved through inspection of metadata (provided by the JDBC driver). Property names for the resulting domain objects are also generated using snake to camel case translations and, of course, methods resulting from parameterized statements include parameters of the correct types. So, instead of having, say, a User class with a Profile class, which has an Image class, each with its own properties representing the full set of columns for the underlying table, you frequently need a limited subset of each combined into one object. For instance, a ProfileListView might need username, email, first and last from user; profile_pic_id, slug, and short_bio from profile; and thumb_filename from image. There's more to it, but I took this join approach vs the "one table to one object" model because I found early that you frequently need different views of the data, based on different use cases. So, it's a way to flatten the underlying relational model when needed. Not perfect, of course, but the best thing going when I built it. And since, I never found an ORM that was worth the switch.