AMPLE Scaffolder 2.1.0 Now Available
MySQL PHPUPDATE: The software package formerly known as MBG Scaffolder has now been officially renamed to “AMPLE Scaffolder”.
Even though the last release was less than a week ago, I’ve been using AMPLE Scaffolder recently on another project and I discovered a few things that were lacking. The Insert Plus feature allows users to duplicate data from a record insertion and use that data to repopulate a new set of form fields. This is a very handy feature if you want to insert multiple records where the majority of the fields have the same value. But I recently found that this wasn’t enough.
My current project had me wanting the option to duplicate data for an existing record (vs. a new record). So the 2.1.0 release now includes a Record Duplication feature that makes this possible. Just click the pencil icon to modify the record you wish to duplicate and click the “copy” button at the bottom of the form. This will automatically generate a new record creation form with all the fields pre-populated with the previous record’s data.
There were also a few minor bugs that I discovered from several releases ago that have now been fixed in this latest release.
- Fixed a bug where users were returned to page one of the results, regardless of where the original record was listed (page two or higher)
- Fixed a bug that wouldn’t allow users to deselect all options under Advanced > SQL Field Select in order to show all fields again
- Fixed a bug where multi-select form fields were not pre-populated after an Insert Plus or a Record Copy
Also, SQL queries are no longer displayed by default. I found this to be a bit annoying when using the utility for longer sessions since some of the queries were long and I was forced to scroll down after after update. So I set this option to disabled by default so it doesn’t display after a session reset. It can easily be re-enabled under the Options tab though if you prefer.
Download the latest release here!
Hey,
Just wanting to let you know that the link is not referring to the right address.
I haven’t tested your code yet, but any thought about permission and access control? You can holler me directly on my email. Who knows maybe we can bring this project forward.
Oops, sorry about that Wendy. I fixed the link so it should now point to the software download page. Thanks for the tip off.
Regarding your questions on permission and access control, would you mind elaborating please? Currently the code relies on database permissions to determine levels of access. This way, access can be controlled by the DB administrators which helps to isolate any unwanted incidents. It also eliminates the need for additional configuration files on a server somewhere. Beyond db permissions, are there additional levels of control you wish to see implemented?
To be honest, I just checked your documentation and demo after posting that comment. Now I can see your point of view towards this tiny but SUPER-B implementation for RAD needs.
I’m really glad to find this stuff while searching on Google for scaffolding.
Would you mind if I use it on a commercial project that I do? Is there any restriction or legal stuff that I need to know before hand? I used to be involved with several Open Source project, but licensing is definitely not my strong point.
Thanks for the comments. I do hope you find it useful and feel free to post back with any questions.
As far as commercial use goes, if you plan to modify or integrate the original code into a new commercial program, just keep in mind that your new program must comply with the GPL v.3 license requirements. A quick excerpt from the GPL FAQ summarizes it best:
You can find more information here if you’re interested.
Thanks again for your feedback!
Jeff
Ehmm… Can I ask specific needs of mine?
CASE STUDY 1: If I use this as is for website backend system, in which my client’s corporate website database being manage, does it mean that I have to make the source code for my client’s website frontend under GPL as well?
CASE STUDY 2: If I use this with a little modification on the interface to manage a simple system, which will be used internally in my organization, do I need to make the interface modification under GPL or it also includes the extended system to displat the data for the system as a GPL as well?
Thanks…
Wendy,
From what you described in both of those instances, there would be no need to release your client’s site code or your internal system code under GPL (unless of course you wanted to). The license covers instances where you will be integrating the code from my application directly into an app of your own design and then re-releasing that entire bundle of code to the public for free or commercial use. The scenarios you describe above are actually the main reasons I designed MBG Scaffolder. I needed a drop-in-place tool that I could utilize for accessing and modifying MySQL data that would be easy to use and require as little configuration requirements as possible. And for those purposes, MBG Scaffolder works very well. So please feel free to make use of it and let me know if you have any additional questions. Also, don’t forget to view the Help documentation included with the tool. It contains a lot of information regarding setup and configuration of some of the more advanced features.
Noted!
Thanks for the information and your contribution to the open source community Jeff… Looking forward for more great things…
Hey Jeff,
I have a few questions and suggestions and correct me if I’m wrong.
1. It doesn’t seems to me that it support n-n relationship, is that correct?
2. Do you have the non-compressed code, so I can take a look what is under the hood, that if you don’t mind?
3. I think it will be best if you include downloadable SQL sample in which newbie like me able to get started faster, because all the comments tagging and stuff is integrated, so we can go back and forth between the documentation and the example sql, through the use of phpMyAdmin or other popular MySql editor?
4. Wouldn’t it will be great if you have an alternative view for lookup table, by showing them on a subgrid on item view/edit interface with support for adding a new one, deleting reference with the currently view item id, or deleting lookup table entry entirely?
Some of my suggestions/request might not be appropriate, but let me know your thought on this.
Thanks.
Wow, lots of feedback. I’ll try to address each comment individually.
1. By n-n I assume you mean a many-to-many relationship? I don’t actually have any databases that utilize this design but that’s not to say support couldn’t be added. Can you give me an example of your table structure so I have a better idea of what you’re trying to achieve?
2. I have a script that I’ve written that I plan to release soon that will auto-expand the code to make it readable. Maybe this weekend I’ll get some time to add that into the download.
3. I completely agree. This is a great suggestion! I’ll add a sample build file into the download as well that you can run on your MySQL server. It will be the same build file for the data seen on the demo site. That way you can go into the sample database and see examples of table structure and comments.
4. This one would be a bit more difficult to implement so I’m not sure it will happen any time soon. The tool is built with simplicity in mind in terms of adding and modifying content. This is not a bad idea though so it’s possible it may make it into future releases. For now, if you need to modify records in a lookup table, you just select that table from the drop list (under the Connect tab).
Just to capture my other thought:
On n-n relationship, there is definitely a way to add/edit extra field located on the link/bridging table connecting both tables, which usually consist of the primary id from both tables with optional fields for extra information.
I can see this implemented with a plus and minus arrow to add row during detail mode or edit mode.
PS: What kind of date plugin are you using? Is it home made?
I’m afraid this comment left me a bit confused. If you could provide an example of your table structure it might help me to visualize your end goal.
My date plug-in actually originates from older code that I’ve slowly modified over the years. For MySQL specifically, I needed a way to add datetime values to a read-only field so that users couldn’t accidentally enter bad values. So I added the javascript calendar plug-in as a means to do this.
Another awesome features:
1. Have each individual table declare its own options which can be override by global options.
2. Have an external PHP file that can be included to change language and view for bringing this mbgScaffolder towards the end user, though I’m not sure whether you’ve plan for this or you’re only targetting this as a backend of development only.
What types of options are you looking to apply/override? I actually like the concept of local vs global options but if you could be more specific as to the options you need it might help. I’ve also been considering the use of an external file for awhile now. I’ve avoided it thus far because I was trying to keep things simple. But I think moving some of the basic setup parameters into an external file might not be a bad idea. That way, I could separate control over default parameters, override local settings with global settings, etc. This would be especially helpful when it comes to controlling access to the basic configuration settings. I’ll see what I can do with this.
BUG REPORT:
This is more to the flow of the application.
If I choose 100 rows to show for a table with less than 100 rows, then I click on page 2, which is not support to exists, it will display an empty table with headers, but without pagination. The only way for me to go back to page 1 is to change the database table to other and get back to the intended table again.
Okay, I’ll take a look at this too. Thanks.
For n-n table structure example, which is definitely only use for example:
ClassRoom: (Primary ID: classroom_floor & classroom_block)
- classroom_floor
- classroom_block
- classroom_name
- classroom_capacity
Student: (Primary ID: student_id)
- student_id
- student_name
- student_grade
ClassRoom_Student_Rel
- classroom_floor
- classroom_block
- student_id
- seating_number
As you can see that ClassRoom & Student tables are in a n-n relationship with table ClassRoom_Student_Rel connecting them. This structure shows multiple primary key on ClassRoom table. It also support additional column for extra information regarding the seating_number of student on each classes they are in, which is tight into the connecting table ClassRoom_Student_Rel.
This will be shown and edited by default just like “Referential Field Types” / “CSV” / “ENUM” / “SET”, so we have to determine the column that will be concatenated when viewed on table listing or detail view.
But those can also be show as a subgrid, just like the one shown on ExtJS usage or other Javascript UI library out there, with a plus sign to open/close the grid during table listing view. This will be reflected on the edit/detail view, in which you can see the related subgrid in it with pagination and sorting as well. You can also add/edit individual related items on the edit/detail view as well.
It’s complicated but I think it will be awesome to have it in place, but definitely it’s up to your opinion whether it is useful or not.
Another awesome thing is to have a tree/hierarchical data support. It will support both adjacent tree model or nested set model, check this out for reference: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
All in all, it will still fit into a single php file for fast db administration, just like a swiss army for database application development and prototyping. Add that with a few custom JS library for most used form fields type that will improve usability and experience (not too bloated definitely).
Check this out: http://www.adminer.org/
Another great example of single script file with support for extension or extra config, theming, and locale support.
Okay, your table descriptions make a bit more sense now. I’ll be honest, I did not account for multiple primary keys in my design. I’ve seen it done before but never felt the need for this practice in any of my databases. If I’m understanding your correctly, you want to display both primary key fields from the lookup table, in the main table as a drop list option, yes? As it sits right now, you can tie an unlimited number of lookup tables to a single table. But there is a limit of one field to display in place of the primary key. I can look into this though and see if there’s a semi-quick solution.
As far as subgrids go, I agree that this would be a nice feature but it will require a great deal more time to implement. I’ve considered adding support for Javascript frameworks such as jQuery, Prototype or ExtJS. But so far I’ve avoided doing so because I wanted this app to remain completely independent of any external libraries. No dependencies means no disappointment when you go to use it.
On the opposite side of the spectrum, I’ve gotten to the point where I loathe traditional form posting and the resubmittal prompts that go with that approach. I would much prefer to utilize AJAX calls for database updates since they’re quicker, cleaner and utilize less bandwidth if done properly. But again, this would require additional JS code to make it work and I was concerned that the app might become overly bloated as a result.
It is highly possible that somewhere down the road I will completely revamp the app to utilize AJAX calls exclusively. This would allow me to easily send separate calls back to the database for subgrid queries and updates. But I don’t want to give you the impression that this will occur any time soon. Such a revision would require some time to implement.
Nested sets are handy, I’ll give you that. This feature I actually DO support. That’s the CSV field type under the Help docs. Essentially, you just add [CSV=myLookupTableName] to the comment area (under the table properties) for the field you want to tie back to the parent. Not only does this support a 1-to-1 relationship, it actually supports a 1-to-many relationship as well. The values in the field are based on primary keys from any table in the database, included the same table in which you are placing the data. Values are added to the field in a comma separated format, thus the “CSV” field type. Hopefully this makes sense. If not, let me know and I’ll do my best to provide more detail.
I checked out the Adminer page as well. It looks similar to PHPMyAdmin in some respects, but I couldn’t get it to do much in the demo area so I’m not entirely clear on its function. I will say this though… MBG Scaffolder is not designed as a replacement for PHPMyAdmin. It was never intended to modify database structure – just the data within. So anyone using MBG Scaffolder will still need to have access to command line or GUI tools to make changes to structure within the database, including comments within table properties. I may add more support for db structure manipulation down the road but at this time, I have no plans to do so.
Wendy,
I’ve added two additional files to the MBG Scaffolder download. They are included in the zip file now. The “index_max.php” file should be placed in the same directory as the “index.php” file. Simply run the “index_max.php” file from any web browser and it will output the contents of the “index.php” file in a more readable format. Just select the text within the browser window and paste into a blank php file for use. Or feel free to modify the code and have it output into another file entirely. It’s up to you.
The second file is a SQL dump of the demo database that you can use for testing. Just import the file into MySQL and connect to it via MGB Scaffolder. Upon importing, the “sampledb.sql” file should generate all the necessary tables and fully populate the fields with sample data as well. Hopefully this will help you in your testing and give you a better idea of how some of the internal configuration settings for MGB Scaffolder are designed to operate.
Jeff,
Adminer itself is a replicate of phpMyAdmin in a single PHP file, but Adminer Editor is almost similar with your approach, except that both Adminer and MBG Scaffolder has its pros & cons. Here’s the direct link: http://www.adminer.org/en/editor/
As for Hierarchical data, I can see how it’s being implemented with CSV approach, but as a good database designer, we should avoid non-normalize approach, because the integrity of the data cannot be enforced natively by database. It’s kinda missing your MBG Scaffolder characteristic, in which most features are implemented by using database native features, such as the privilege and form field type.
I like the column level permission provided by MySql native privilege system and I can see that MySql able to do row level permission through the use of view and trigger. As for me personally, I will look into both of this features further and we’ll see how it goes from there.
I’ll download your latest code right now and thanks for listening to my nonsense…
Regarding about the hierarchical model, I think your CSV approach is similar to “Adjacent Tree” model in a backward kind of way. The adjacent tree contains the parent id, yours contains the list of children id. So on “Adjacent Tree” model, we usually seek from the parent, yours will be form the child, because there is now way we can know which one the root parent through the data alone. Am I correct or I’m not getting it at all?
I am all for normalization but I can also see instances where it’s more convenient to capture data in a single record without the need for additional lookup tables. I realize this goes against the grain for some people. But good design isn’t always about taking the traditional approach. I created the CSV field type for those folks that prefer to trap multiple values in a single field. But just because it’s available does not mean you are obligated to use it.
That being said, the CSV field type is not meant to serve as an adjacent tree solution. I was simply saying that it could possibly be used to simulate the same data structure. I’m actually looking through the code to see if I can’t implement the adjacent tree option. As it stands right now, any attempts to link back to the same table will result in zero records being returned because there’s no aliasing for table names and it doesn’t distinguish between the table as a prime and the table as a lookup. I’ll see if I can’t get something working in the next couple of days and release a new version for you to test.
Okay, I made some changes to the code so that it now supports “same table” lookups. I added three additional columns to the Patterns table in the demo database and set up referential rules linking each column back to the same table (Patterns) as its parent. The code should support an unlimited number of joins but I wouldn’t suggest adding too many as performance will most likely suffer as a result.
So the correct values are now being returned from the parent table. However, I’m still not sure this is what you are looking for because the standard query is not designed to show hierarchy. So you would still need to run a custom query in another tool to see all the parent child relationships in their hierarchical format. At one point, I had a third text field under the Advanced tab that allowed users to input their own custom queries. But I ended up removing it because I was worried that novice users might accidentally (or intentionally) end up deleting critical data. Maybe it’s time I add that option back in, yeah?
Anyway, if you want to download the updated version (v2.1.1), it’s now available. I’ve also updated the example database file so that it contains the updated Patterns table where you can test adjacent tree scenarios. I renamed this file to “scaffolder_database.sql” btw. Let me know if this works for you or if you run into any bugs.
Good news! I added in support for linking to lookup tables from within a record. I noticed that the Adminer tool used a similar approach but their version only took you to a select list for some reason. I decided it might be more convenient to take the user directly to the record editing form instead. I even got it working for CSV fields so that each individual id is linked separately to its own editing page. I pushed the latest changes out to the download page if you’re interested in testing out the new features. And again, let me know if you run into any glaring bugs.