Can't Stress Enough -- Make Your 60% Admin CRUD Generator
Posted: Tue Feb 10, 2009 11:23 pm
Just wanted to share with you something neat I built. As a freelancer, I can't stress enough how critical it is to have one of these things. I mean, a lot of clients want to ask the world of you to put into your admin portions of a custom website, and often don't have the budget for the usual two months it would require to fulfill all their needs. Wouldn't you like to get an admin section of your site down to a week or two weeks of development, rather than a month or two months?
A couple days ago I made my first Admin CRUD Generator that I can hopefully reuse on multiple projects. It only does 60%-70% of the work because no batch script for such a thing can suit all needs. And then you finish the remaining 30-40% by hand. Anyway, it is a tremendous timesaver for me on this current big project I have.
The way it works is I point it at a given table. It then analyzes the table to determine how to convert field names into PHP vars, XHTML field vars, and labels. It also determines the data type and knows which default XHTML field type to use for that, as well as setting things like size and maxlength appropriately. The next thing it does is copy over templates into the MVC framework, and then does template phrase replacement for my table name in that framework. Sometimes the table name needs to be used as a folder name. Sometimes it's a front controller action. Sometimes the table name is used as a variable name. So, I prepare all that and do template phrase replacement. Next, I have "insert blocks" in the template where it iterates through my fields and creates a particular block of either PHP or XHTML or SQL based on those fields and data types. Last, the end result is an accordion menu on the left (tabbed so that I group accordions by a certain keyword to grab, like user* stuff going to the "Users" tab), and a workspace pane on the right. So, for a given table, I will have New X, View X..., Edit X..., and Delete X.... I also have Special Function 1... and Special Function 2.... When you choose View, Edit, or Delete, it will first show you a search page with a paginated grid and a generic search form where you choose field name, type some text and can specify stuff like >, <, >=, <=, and if the data is not integer or date, it will automatically use LIKE queries. If you add in a ! or NOT, it will automatically make it either a NOT LIKE or inverse the <, >, etc. result.
Now, one of the problems is that it doesn't know what the heck to do with the foreign keys. And I didn't want it to do anything. I just make it dump it out as a numerical ID field. So, that's an example where I must do the remaining 40%. I take the numerical ID field and introduce a "chooser" where you choose something like a first name or last name from another table and match it to this record. I make the chooser as an inline DIV with jQuery and AJAX.
The key things I learned in building these things are:
- Get your paginated grid down to a reusable object you can use multiple times with great ease.
- Get your search form SQL worked out and reusable.
- Do AJAX for form posting (except with search) so that you can do inline data validation on the form. And if successful, then redirect to some success page.
- Get the CRUD working for just one table, and then derive your template and batch script from that.
- Learn the API for MySQL or PostgreSQL or whatever database you are using so that you can run SQL statements to analyze a table and the fields in that table, and don't forget that when you delete a column in PostgreSQL, it leaves the orphan of that column still in the database and you have to find the way to skip over those columns.
- The batch script can be overwhelming to build at first. Just take a deep breath and only focus on one part at a time, stop, test your results, and move forward. Eventually you'll get it.
- I use crc32 on a given block of text I insert so that if I accidentally run a batch script more than once, it will be smart enough to know that nothing changed and therefore it won't insert that block of text. Thus, you'll see near the <-- INSERT xx --> blocks a <-- CRC 20023032 --> or something like that, letting the script know to not run that field insert more than once in that given file.
- After my batch script copies over the text files from the template folders for each part of the MVC, and I start modifying files by inserting text, I *always* backup that file so that I have a fallback plan.
- Think generically. Think, "What would be easily possible for me to get a script to automate this?" Don't do anything that requires special logic that only applies to certain tables. The remainder of custom work will go into the 40% of stuff you have to do to take what is automatically generated and convert it into the final product.
- A jQuery accordion menu, tabbed at the top, is an extremely efficient use of screen space and a great menu system on the left.
- I use ordinary frames, not iframes, for the menu on the left and the workspace on the right. I mean, this isn't something other than an admin would use. I also found that if I use AJAX to pull back a TinyMCE control, sometimes this doesn't work and is quirky. So, I don't do the frameless frames technique with AJAX, either. And frames are necessary because you don't want to have to repaint the entire menu system with each form post on the site. This is especially important when you have placed your admin system on a slower, shared hosting plan where bandwidth is a concern.
A couple days ago I made my first Admin CRUD Generator that I can hopefully reuse on multiple projects. It only does 60%-70% of the work because no batch script for such a thing can suit all needs. And then you finish the remaining 30-40% by hand. Anyway, it is a tremendous timesaver for me on this current big project I have.
The way it works is I point it at a given table. It then analyzes the table to determine how to convert field names into PHP vars, XHTML field vars, and labels. It also determines the data type and knows which default XHTML field type to use for that, as well as setting things like size and maxlength appropriately. The next thing it does is copy over templates into the MVC framework, and then does template phrase replacement for my table name in that framework. Sometimes the table name needs to be used as a folder name. Sometimes it's a front controller action. Sometimes the table name is used as a variable name. So, I prepare all that and do template phrase replacement. Next, I have "insert blocks" in the template where it iterates through my fields and creates a particular block of either PHP or XHTML or SQL based on those fields and data types. Last, the end result is an accordion menu on the left (tabbed so that I group accordions by a certain keyword to grab, like user* stuff going to the "Users" tab), and a workspace pane on the right. So, for a given table, I will have New X, View X..., Edit X..., and Delete X.... I also have Special Function 1... and Special Function 2.... When you choose View, Edit, or Delete, it will first show you a search page with a paginated grid and a generic search form where you choose field name, type some text and can specify stuff like >, <, >=, <=, and if the data is not integer or date, it will automatically use LIKE queries. If you add in a ! or NOT, it will automatically make it either a NOT LIKE or inverse the <, >, etc. result.
Now, one of the problems is that it doesn't know what the heck to do with the foreign keys. And I didn't want it to do anything. I just make it dump it out as a numerical ID field. So, that's an example where I must do the remaining 40%. I take the numerical ID field and introduce a "chooser" where you choose something like a first name or last name from another table and match it to this record. I make the chooser as an inline DIV with jQuery and AJAX.
The key things I learned in building these things are:
- Get your paginated grid down to a reusable object you can use multiple times with great ease.
- Get your search form SQL worked out and reusable.
- Do AJAX for form posting (except with search) so that you can do inline data validation on the form. And if successful, then redirect to some success page.
- Get the CRUD working for just one table, and then derive your template and batch script from that.
- Learn the API for MySQL or PostgreSQL or whatever database you are using so that you can run SQL statements to analyze a table and the fields in that table, and don't forget that when you delete a column in PostgreSQL, it leaves the orphan of that column still in the database and you have to find the way to skip over those columns.
- The batch script can be overwhelming to build at first. Just take a deep breath and only focus on one part at a time, stop, test your results, and move forward. Eventually you'll get it.
- I use crc32 on a given block of text I insert so that if I accidentally run a batch script more than once, it will be smart enough to know that nothing changed and therefore it won't insert that block of text. Thus, you'll see near the <-- INSERT xx --> blocks a <-- CRC 20023032 --> or something like that, letting the script know to not run that field insert more than once in that given file.
- After my batch script copies over the text files from the template folders for each part of the MVC, and I start modifying files by inserting text, I *always* backup that file so that I have a fallback plan.
- Think generically. Think, "What would be easily possible for me to get a script to automate this?" Don't do anything that requires special logic that only applies to certain tables. The remainder of custom work will go into the 40% of stuff you have to do to take what is automatically generated and convert it into the final product.
- A jQuery accordion menu, tabbed at the top, is an extremely efficient use of screen space and a great menu system on the left.
- I use ordinary frames, not iframes, for the menu on the left and the workspace on the right. I mean, this isn't something other than an admin would use. I also found that if I use AJAX to pull back a TinyMCE control, sometimes this doesn't work and is quirky. So, I don't do the frameless frames technique with AJAX, either. And frames are necessary because you don't want to have to repaint the entire menu system with each form post on the site. This is especially important when you have placed your admin system on a slower, shared hosting plan where bandwidth is a concern.