MySQL ORM

Introduction

Object-relational mapping (ORM) is a mechanism that makes it possible to address, access and manipulate objects without having to consider how those objects relate to their database tables.
WebMVC provides you with a useful tool for the Object Relation Mapping of MySQL. This tool generates automatically Model classes for any tables of a given database schema.

WebMVC Model and ORM - Object Relational Mapping

To generate Model classes to map database tables you need to:

1. Use lowercase with the underscore, which is the widely used MySQL naming notation (formerly named as snake case notation), on your database tables and field names.

2. Configure your database schema by modifying util\mysqlreflection\mysqlreflection.config.php file and, assigning appropriate values to DBHOST, DBNAME, DBUSER, DBPASSWORD, and DBPORT PHP constants, according to your MySQL settings.

3. Then, launch the tool by typing:
   http/localhost/util/app_create_beans.php Note that, the GUI of the utility uses Bootstrap and jQuery from CDN. So you also need an internet connection alive before running it.

4. Once the utility is started, click the "Generate classes" button.

The following figure shows you the startup screen of the utility:

Utility GUI

After running the generation of classes you can close the utility. You will find all the generated classes under models\beans folder.

Notice that:

• You can find a class for each table of your MySQL schema.

• Each auto-generated class name is prefixed with "Bean" followed by the table name in a PascalCase format. E.g, for the table name users_roles you will find a class named BeanUsersRoles.

• Each auto-generated class extends framework\Model.php, so you can relate it to a Controller

Each auto-generated Model, widely known as a Database Bean, provides you with the following:

• A constructor for managing a fetched data row from a table or for adding a new one on it

• Management for both single or composite Primary Keys

• Automatic mapping of the different date formats which may occur between the web application and database

• It defines a set of attributes corresponding to the table fields

• Setter and Getter methods for each attribute

• Setter methods automatically sanitize data against SQL injection

• OOP methods for simplifying the DML operations of SELECT, INSERT, UPDATE, and DELETE.

• A facility for quickly updating a previously fetched row

• Useful methods to obtain table DDL and the last executed SQL statement

• Error handling of SQL statements

• It uses camelCase and PascalCase naming convention on class, attributes, and methods that were generated to map table and fields

• PHPDOC on table, fields, class, attributes, and usage of methods

Notice that ORM doesn't relieve you from a good DB design. This means you must design a good relational database schema, before using the ORM engine provided by WebMVC.

What's next

You can use autogenerated database bean classes as a Model or in conjunction with it when coding your MVC applications. On the next page, we explain a a fully functioning DB application and consuming, Model, View ad an autogenerate database bean class.