Autogenerate CRUD Apps with Phreeze

Back in the times of Symfony 1.x, the framework had a powerful backend module to help the developers and site administrators create a good enough app and provide necessary CRUD features to manage the database (and save us from using PHPMyAdmin).

Since Symfony 2, this has been taken out from the core and the developers either have to rely on their own to start from scratch or rely on some other third party Symfony 2 bundle when such a feature is needed – and in many circumstances, it is.

In this article, we will take a look at Phreeze, a simple and easy to use PHP framework that can help us generate a MySQL CRUD backend app. I will use my book collection test site as the underlying database. Please refer to my Data Fixtures in Symfony2 article for more details on the database structure.

Installation and bootstrapping

Phreeze is distributed as standalone. You can clone a copy of its latest version from Github.

In my environment, I have created a new virtual host test and cloned the repository to its phreeze folder so that I can start the backend generation with Phreeze using: http://test/phreeze/builder.

To facilitate the generation of a backend, Phreeze introduces two step wizard-like screens to guide us.

In the first screen, we will provide the necessary database connection information (server, database/schema, user, password):

On MySQL Driver options, Phreeze provides raw mysql_connect, mysqli_connect and also PDO. PDO is recommended as it is more popular nowadays and mysql_* is deprecated.

Next, we can click “Analyze Database” and it will bring us to the second and final screen:

All the tables in the database are identified and selected by default. If we don’t want to include a particular table in our CRUD backend, we shall deselect it. In my case, I have deselected the table book_visit as this table stores the visit information of book detail pages and is populated inside my app.

Phreeze also suggests the names of the generated entities for each table. The name in the single form (BookBook) reflects one entity (a book) of a table (book_book), while that in the plural form (BookBooks) reflects the collection class (all books) of that table (book_book).

Phreeze is quite smart when suggesting the plural forms (as can been seen in Lakers/Lakerses). So normally I will just leave the suggestions untouched.

We can safely leave “Column Prefix” blank. It is used to strip (NOT append) that column prefix from your table’s field names during the entities’ generation for the backend. For example, if your table has a field called fld_firstname, you can provide a Column Prefix as fld_ and thus in the generated classes, that field will be mapped to a member named as firstname instead of fld_firstname.

Next, in the “Application Options”, Phreeze offers a few dropdowns to let us tweak the generated backend app.

Packages To Generate

It offers two types of packages: one is the running app with different template engines (Savant, Laraval/Blade, Smarty). The Twig template is not supported yet so in my Symfony 2 site, I　have　to use Smarty for my backend views. All templates will use the Bootstrap CSS boiler plate. This will help us to further customize the layout, style and so on of the backend view in a later stage.

Another type of package is called the test package. If supports PHPUnit and QUnit. We will not cover this in this article.

Application Name and Root URL

For application name, just give it a meaningful one of your choice.

We need to be careful when setting the application root url. For now, we just put a name for it and let’s come back later to this setting.

Path to lib and Self-Contained

Leave the “Path to libs” as it is.

I will choose “Yes” to make the generated backend application self-contained.

Long Polling

Choose “Disabled”, which is also the default option. Long polling makes sure the interface is refreshed via ajax, which allows you to use the app in several tabs and instantly see changes made in one happen in others, without having to reload them. This is eye candy for the most part, and is not essential.

Now, let’s click “Generate Application”. Phreeze will start to generate all the necessary files and zip them into one archive (in this case, this file is named “backend.zip“).

The generated zip file is quite big (almost 1M in my case) but most of it is contributed by Bootstrap files (CSS, image, js, font, etc) and the PHAR archive for Phreeze (we selected “Self-Contained” and included the Phreeze framework for our app).

Put it under the Symfony 2 project

The backend.zip file created above is everything we need to launch the backend. To integrate those unzipped files into Symfony 2, my approach will be as follows:

• Recall what we have provided for “Application Root URL”. In this case, it is http://servername/backend.
• My site that will use this backend has server name rsywx so the URL to call my backend should be something like http://rsywx/backend.
• As Symfony 2 puts everything “static” into its web directory, I created the backend directory under that web directory and unzipped all files in backend.zip into that directory.

To launch the backend, we just visit http://rsywx/backend and the backend welcoming screen will be like this:

(NOTE: Due to the Symfony 2 .htaccess rewrite rules and also your LAMP/WAMP configuration, you may not see anything or just see a directory listing. In that case, try visiting http://rsywx/backend/index.php. The latter URL will always work.)

At the top of the page, we see a few navigation links. Each will bring us to the respective table’s CRUD page. Let’s take a look at a few of them and try to CRUD a few records.

(NOTE: CRUD operations can drastically change your database/tables, so for this demonstration, I will only apply these operations on a test database. The test database can always be dumped with the same set of data using DataFixtures discussed in my previous article.)

The following few screenshots show what will be displayed for book_publishers, book_places, and book_taglist (doing an insert operation):

Notice that as book_taglist has an FK linked to book_book, the bid field is in a dropdown box so that we can select a valid book ID to avoid consistency check failures.

Also, in each page shown above, there is a “Search” function with which we can further filter the results displayed.

Clicking on table’s head will sort the result in that particular field, switching from ascending and descending. Quite handy, right?

Now let’s take a look at the display of the book_book table. We click the “BookBooks” link in the top navigation bar and expect to see a total of 101 books. Oops! Why is there only one book displayed?

After some discussion with the author of Phreeze, we managed to figure out the issue: in my book_book table setup, there is a field named page. Phreeze’s internal route/controllers also use a page parameter to paginate the result set (like page=1 to display the first page). But as page is also a valid field name of my table, Phreeze thinks that I am trying to filter the result set to display only those records whose page field has a value of 1.

Well, in my test data, there is exactly only one record whose page is of value 1. That explains why there is only one record displayed.

The author (thanks, Jason!) provides two solutions to get out of this.

One option is to rename that field to something else, say pages. But this will involve a lot in our Symfony 2 setup, including re-import the database mappings and re-generate the entities. So I just denied it.

The second, which is what I use, is to hack a bit on the generated code. Here is how:

• Locate and open the BookBookController.php file. It should reside in our Symfony 2 project under web/backend/libs/Controller if you follow the setup instructions above.
• Somewhere around line 60-70, inside the function called Query(), there should be these few lines:

if (property_exists($criteria, $prop_normal))
{
    $criteria->$prop_normal = RequestUtil::Get($prop);
}
elseif (property_exists($criteria, $prop_equals))
{
    // this is a convenience so that the _Equals suffix is not needed
    $criteria->$prop_equals = RequestUtil::Get($prop);
}

• In our particular case where our table field page conflicts with Phreeze query parameter page, we modify the above code to grant one exception:

if ($prop != 'page' && property_exists($criteria, $prop_normal))
{
    $criteria->$prop_normal = RequestUtil::Get($prop);
}
elseif ($prop != 'page' && property_exists($criteria, $prop_equals))
{
    // this is a convenience so that the _Equals suffix is not needed
    $criteria->$prop_equals = RequestUtil::Get($prop);
} 

• Save and reload.

Bingo! Here is what is displayed for our book CRUD page:

The book list is displayed correctly and the pagination is working fine. (The above screenshot shows we are in page 4.)

Changing the app configurations

Phreeze relies on three configuration files to run correctly.

• _machine_config.php: This file holds the the database connection parameters and also the Application Root URL settings. We need to change the settings when we deploy.
• _global_config.php: This file defines many application specific settings and fundamental functions. Normally, we will leave it as it is.
• _app_config.php: This file defines all the routes and other settings. Normally, it should not be modified.

APIs

The most brilliant feature of Phreeze is that it provides an API interface. To call the API directly and see its JSON output, we add api in the URI we call to display the data.

For example, to display the book data, the URI is: http://rsywx/backend/bookbooks; to get the JSON output from API, we use this: http://rsywx/backend/api/bookbooks.

This API interface has two shining points:

1. When we customize the views, we do have a way to get the data directly from the respective APIs. Then we can use JSON decode to parse the returned data and use them in our template engine. This will save us a lot of time and effort.
2. It actually becomes an out-of-the-box RESTful API to provide data to 3rd parties.

And of course, with the raw data grabbed by Phreeze, we may also be able to find some other not so obvious issues and bugs.

Secure the access

Right now, this backend URL (http://rsywx/backend/index.php) is accessible to everyone. In most cases this is not a good idea so let’s secure the access to this page, or more precisely, to this directory (web/backend).

Phreeze is not a bundle in Symfony 2’s terms. So Symfony’s built-in authorization does not apply and the access to /backend will let anyone through.

In this case, I am using the combination of .htaccess and .htpasswd to provide a quite entry-level security control on this directory using HTTP authentication.

NOTE: Phreeze comes with its own authentication too. It uses a table to store the user name and hashed password. But in this short entry tutorial, I am giving it a miss. Interested parties can download and read its authorization demo to get a better understanding.

Phreeze has come with its own .htaccess file and we just append a few lines to its end:

AuthName "RSYWX Backend"
AuthType Basic
AuthUserFile .htpasswd
require valid-user

Then we can create a .htpasswd file with the htpasswd utility under Linux. We copy that .htpasswd file to a proper location. In my Windows EasyPHP installation, it should be copied to f:\EasyPHP\binaries\apache\. Don’t panic if you can’t figure out the right location at first. If this file is in the wrong location, there will be a 500 error and the Apache error.log will always prompt you with the right location.

NOTE: In a Symfony 2 site, HTTP authentication not using Symfony’s built-in authorization method (security.yml) may fail. It works fine in my current local EasyPHP setup but fails in my production server setup and the difference is the Apache version. Apache 2.4 is recommended.

A few thoughts and conclusion

Phreeze comes in very handy for me. With its help, I managed to create my backend CRUD module for my site within 10 minutes without writing a single line of code (except the hacking stuff, of course).

Its features are not super rich but are sufficient. I like the API, filtering and sorting very much.

Its interface may still need to be enhanced. Currently, it relies purely on JavaScript and DOM manipulation to dynamically display new data (say, when we navigate to next page). The URL will remain the same but I would like to see something like http://rsywx/backend/bookbooks/page/2 so that I can quickly navigate to a page I want, instead of clicking the pagination links many times (it only has previous/next page link and total 10 page links).

I would yearn for Twig template support. Symfony 2 users use Twig a lot and Twig itself is quite mature and popular. I hope to see this in its future releases.

Missing other DB support may restrict its application scope. In my personal view, when MySQL is supported via PDO, it is not a mission impossible to support other popular DBs (SQLite, pgSQL, MongoDB, etc). I hope this is in the author’s roadmap.

It may not have those fancy interfaces like other admin generator bundles in Symfony 2 but it is enough for a simple and quick backend setup and usage. And after all, we can use our own creativity to give it a brand new look.

This also has the advantage that it is not tightly bound to Symfony 2. You can use it in any of your MySQL powered sites with other frameworks.

For a detailed documentation on Phreeze, please refer to its official site.

I would give Phreeze a 4 out of 5 score and recommend you use it in your own site.

Let me know your feedback and comments!

Frequently Asked Questions (FAQs) about Auto-generating CRUD Apps with Phreeze