Autogenerate CRUD Apps with Phreeze

Taylor Ren
Tweet

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!

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • AlexGemmell

    Interesting tool. For my Symfony projects I ended up extending the Doctrine CLI crud generator tool (./app/console doctrine:generate:crud) to build me the necessary CRUD Controllers to manage my Models. This relies on me having the schema written out either as YML or as annotated Model class files (which I prefer).

    It’s pretty easy to override the Twig templates in SensioGeneratorBundle by putting them in app/Resources/SensioGeneratorBundle/skeleton/crud.

    Sweeeet :)

    • Taylor Ren

      Good to learn that you have extended the built-in tool. Schema in YML should not be too difficult to obtain. I always use doctrine:mapping:import to generate those.

      Cheers!

  • CTN

    I checked this tool out tonight and I found it to be junk and useless.

    • Taylor Ren

      Mind to share why you think it sucks?

      • CTN

        first problem i had was a field naming conflict with theirs. so I had to rename fields before I could try it out. this obviously broke my php code, which would have to be fixed.

        trying out the built front end revealed many issues. navigation was rubbish. i could not re-order the list, nor could i quickly jump to the end. some of my fields were html text, but there was no html editor for them, only a standard text editor.

        total waste of time. i’m going to use angular js to help build my front ends, not this piece of garbage.

        • bliksempie1975

          Yes, a lot of apps/tools require too much on convention. CakePHP (at least at the time I checked it out initially) was rife with it, and made life very complex. Since then, I used CodeIgniter, but thinking of switching to Laravel.

          • CTN

            check out phalcon

          • bliksempie1975

            Phalcon is a compiled source, right? Not quite for me – unless I am missing something?

  • bliksempie1975

    Hi Taylor,

    Nice article! Thanks for sharing!

    How does Phreeze deal with many:many relationships? Let table1 be “class” and table2 be
    “teacher”. So, table1_table2 would be “class_teacher” with “class_id” and
    “teacher_id” fields, and also fields for “date” and “time”. The date and
    time can not be added to the “class” table, because a class might be
    taught multiple in days. Can this be circumvented? Most crud systems I checked out has this problem. In GroceryCRUD (my current crud of choice) it can be circumvented by coding an “action” in the list overview, that opens a dialog that sets these parameters, however, it is a bit tedious.

    Another problem with crud systems I have used is that there “where” clause does not work well on tables where many:many relations are present. A specific crud here that fails is GroceryCRUD. Any info on this?

    Furthermore, I see in your screenshot that it appears that relationships can not be linked to a text field. This is where GroceryCRUD excels. For example, in your Book Taglist screenshot, It’d be wonderful if the dropdown could display the name of the book and not the book’s ID. Of course, the ID still needs to be used to save the relation, but the display value should not be that restricted.

    Hope to hear from you soon!

    Kind regards,

    Kobus

    • Taylor Ren

      Hi @bliksempie1975:disqus

      Thanks for the sharing.

      Absolutely it is a MUST to support a more meaningful link when FK/PK is in place, like the user case you quoted for Book Taglist.

      I have not tried m-m relationship very much in my db. But I do have one in this scheme: book_book and book_taglist do have a m-m relationship. But of course, in this case, the tag table is so simple and it does not contain many fields so it may not apply to your case.

      My understanding of your case will be like this:

      1. The key object in your app is a “class”. A “class” is defined by its identifier, subject, entry level requirement, its location/time, etc.

      So I would in a way create a more completed object for “class”, to include the above properties as well.

      By doing this, we can create a 1-m relationship: 1 teacher can only teach 1 class at a specific time and location but many other classes on a different time/location.

      Hope this helps.

      • bliksempie1975

        Thanks, Taylor. I will rather try and work with M;N relations. if I try this, and get it figured out, I will post here. My reasoning is that there are two distinct objects here: teacher and class. reason why I say M:N and not 1:M, is because “Salsa dance” can be held on Thursdays and Fridays, so, I want to define “Salsa dance” as an item, and link it to “Teacher”. Using 1:M, I’d have to create a class called “Salsa dance Thursdays” and “Salsa dance Fridays”, which in my opinion is going to be too much admin. There are hundreds of different dances that this studio teaches, six days a week, and what makes it even worse, is on this Thursday, it may be teacher 1 and next Thursday it may be Teacher 2 presenting. So M:N is absolute necessary here :-)

        • Taylor Ren

          Points taken.