Category Archives: Database

What are ORM Frameworks?

ORM is a short form of Object Relational Mapping, means as ORM framework is written specifically in OOP (object-oriented programming) language (like PHP, C#, Java, etc…) and it is like a wrapper around a relational database (like MySQL, PostgreSQL, Oracle, etc…). So, ORM is basically mapping objects to relational tables.

What does an ORM framework do?

The ORM framework generates objects (as in OOP) that virtually map the tables in a database. So, any programmer could use these objects to interact with the database without writing an optimized SQL code.

For example:

We have 2 tables in a database:

  • Products
  • Orders

The ORM framework would create 2 objects corresponding to above tables (like products_object and orders_object) with little configuration, which will handle all the database interaction. So, if you want to add a new product in products table, you would have to use products_object and save() method like below,

You can see that, how much easier an ORM framework can make things. No need to write any SQL syntax. And application code would be very clean.

Some other advantages of using ORM frameworks

1. Syncing between OOP language and the relational database data types is always creating a problem. Sometimes variable data types have to be converted properly to insert into the database. A good ORM framework will take care of these conversions.

2. Using an ORM will create a consistent code base for your application since no SQL statements are written in the code. This makes it easier to write and debug any application, especially if more programmers are using same code base.

3. ORM frameworks will shield your application from SQL injection attacks since the framework will be filtering the data before any operation in the database.

4. Database Abstraction; Switching databases for the application is easier as, ORM will take care of writing all the SQL code, data type conversions etc …

When to use an ORM framework?

An ORM framework becomes more useful as the size and complexity of the project increases. An ORM framework may be overkilling an application on a simple database with 5 tables and 5-6 queries to be used for the application.

Consider the use of ORM when:

  • 3 or more programmers are working on an application.
  • Application database consists of 10+ tables.
  • The application is using 10+ queries.

About 80-90% of application queries can be handled by the ORM generated objects. It is inevitable that at some point straight SQL query is required, which can’t be handled by ORM generated objects.

In fact, ORM frameworks often have their own *QL query language that looks a lot like SQL. Doctrine, a popular PHP based ORM framework has DQL (Doctrine Query Language) and the very popular Hibernate (used in the Java and .Net world) has HQL. Going even further, Hibernate allows writing straight SQL if need be.

ORM Frameworks for PHP programmers

  • CakePHP, ORM, and framework for PHP 5
  • CodeIgniter, a framework that includes an ActiveRecord implementation
  • Doctrine, open source ORM for PHP 5.3.X
  • FuelPHP, ORM, and framework for PHP 5.3. Based on the ActiveRecord pattern.
  • Laravel, a framework that contains an ORM called “Eloquent” an ActiveRecord implementation.
  • Maghead, a database framework designed for PHP7 includes ORM, Sharding, DBAL, SQL Builder tools etc.
  • Propel, ORM and query-toolkit for PHP 5, inspired by Apache Torque
  • Qcodo, ORM, and framework for PHP 5
  • QCubed, A community-driven fork of Qcodo
  • Redbean, ORM layer for PHP 5, creates and maintains tables on the fly
  • Yii, ORM, and framework for PHP 5. Based on the ActiveRecord pattern.
  • Zend Framework, a framework that includes a table data gateway and row data gateway implementations. ZendDb

phpMyAdmin gives 404 error in localhost

To run phpmyadmin without getting 404 error, configure apache.conf for phpmyadmin.

Then add the following line to the end of the file.

And restart apache

Please read this section in Ubuntu wiki describes the 404 error problem for phpmyadmin.

MySQL database difference with MySQL Workbench

mysql-workbenchAs a DB Manager, there is always a need for database synchronization or difference tool to upload the latest changes to the server.

For uploading latest files, there are so many tools freely available like SVN. But when there is a time to upload the database changes, there are very few options available for free. MySQL Workbench is one of the free tools provided by Oracle which provides database synchronization as well as database difference facility.

MySQL Workbench is one of the best tools to manage databases. You can connect multiple databases and query each database separately. And most importantly, it also synchronizes and find difference of online as well as offline database. In both cases, you have to manage models of offline database.

It is a unified visual tool which provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, and much more. MySQL Workbench is available for free on Windows, Linux and Mac OS.

MySQL Workbench is used to visually design, model, generate, and manage databases, which includes creating complex ER models, forward and reverse engineering. It also delivers visual tools for creating, executing, and optimizing SQL queries. SQL editor of MySQL Workbench provides color.

MySQL Workbench also delivers key features like performing difficult change management and documentation tasks, which normally requires much time and effort.

New version of MySQL Workbench also provide database migration from popular database servers to MySQL, which includes Microsoft SQL Server, Sybase ASE, PostreSQL, and other RDBMS. It’s easy to use solution for migrating can migrate tables, objects and data to MySQL to quickly and easily convert existing applications to run on windows and other platforms.

For more information about MySQL Workbench, visit http://www.mysql.com/products/workbench/
MySQL Workbench is freely available at http://www.mysql.com/downloads/workbench/.