|Author:||Jeff Todnem @ Step41 Software|
|Updated:||February 23, 2015|
|Languages:||PHP 5.2.x or higher|
|Databases:||MySQL 5.0.x or higher|
Table of Contents:
- 1. Database:
- 1.1 .................... Accessing Foreign Keys with SHOW CREATE TABLE
- 1.2 .................... Creating Rules to Support Advanced Features
- 1.3 .................... Database Listing
- 1.4 .................... Establishing Connections
- 1.5 .................... Field Listing
- 1.6 .................... Primary Keys and Unique Indexing
- 1.7 .................... Schema Access
- 1.8 .................... Server Name
- 1.9 .................... Table Listing
- 1.10 ................... User Access
- 1.11 ................... User Name
- 1.12 ................... User Password
- 2. Database Fields:
- 2.1 .................... Booleans and Tiny Integers
- 2.2 .................... Comma-Separated Values
- 2.3 .................... Data Type Restrictions
- 2.4 .................... Date and Time Field Types
- 2.5 .................... Enum Field Types
- 2.6 .................... File Field Types
- 2.7 .................... Referential Field Types
- 2.8 .................... Set Field Types
- 2.9 .................... Unique Identifier Field Types
- 3. Form Fields:
- 4. Scaffolder Class:
- 5. SQL Queries:
- 5.1 .................... Conditional Clauses
- 5.2 .................... Display Minimized Data
- 5.3 .................... Display Records After POST
- 5.4 .................... Displaying Queries
- 5.5 .................... Exporting Data
- 5.6 .................... Inserting Bulk Records
- 5.7 .................... Inserting Repetitive Records
- 5.8 .................... Records Per Page
- 5.9 .................... Referential Lookup Tables
- 5.10 ................... Syntax Highlighting
- 6. Uploading Files:
- 6.1 .................... File Previews and Downloads
- 6.2 .................... Length Limit
- 6.3 .................... Size Limit
- 6.4 .................... Thumbnail Generator
- 6.5 .................... Types Allowed
- 6.6 .................... Types Forbidden
- 6.7 .................... Unique Naming
- 6.8 .................... Upload Directory
- 7. Web Interface:
- 8. Web Server:
- 8.1 .................... Maximum POST Size
Accessing Foreign Keys with SHOW CREATE TABLE:
In order to determine table relationships automatically behind the scenes, a "SHOW CREATE TABLE yourTableName" query is run to access the necessary foreign key assignments. If you experience an error related to this query, it indicates a lack of permissions to the table you selected. As always, check with your database administrators to ensure you have the necessary access required to run this query.
Creating Rules to Support Advanced Features:
End users now have the ability to add or modify custom rules through the AMPLE Scaffolder Database Utility's web interface. Prior releases required a 3rd party tool like PHPMyAdmin to make these modifications but as of version 2.2.0 of the AMPLE Scaffolder Database Utility, 3rd party software is no longer required to add or modify a rule within a table. Simply select a table for viewing and after the records display table has loaded, click the "Rules" button at the bottom of the tabbed UI. This will open the selected table in the Rules Editor where you can add or modify rules to your heart's content. Editing these rules is as simple as left-clicking any field on the right-hand side of the edit form, adding or modifying the rules within this area and then once you're done, click the "Update" button to save the changes or the "Cancel" button to disregard any recent modifications to the table structure.
In general, the rules used within the AMPLE Scaffolder Database Utility are really simple to use, consisting of a flag set equal to a value, with two square brackets surrounding the entire string. There are specific examples outlined in other sections of the Help documentation but the general idea is to look like the following example:
One of the most commonly asked questions is how to set up options for viewing data from a secondary lookup table. Another popular request is how to set up a specific table to work with file uploads. We admit that this one is a bit more complex but thankfully we've already outlined all the required details so you don't have to waste time guessing how it's done. If you're planning on using the AMPLE Scaffolder Database Utility to upload files to a web server, you might find some of the additional Help areas useful such as setting up an upload directory, troubleshooting file size limitations during an upload, and learning how to prevent files from being overwritten once they are uploaded.
Once you have successfully connected to the MySQL database server with the necessary credentials, a drop list will auto-populate with a list of all the databases that reside on that server. This not only makes it much simpler to move from one database to another, it also prevents any errors as a result of typos or memory lapses. Selecting an item from this list will cause the form to resubmit so that a list of tables can be generated based on the database selected.
In order to properly manipulate data within a database, a connection must first be established using credentials. This connection is comprised of a database server name, user name and password. If any of these items is incorrect, the connection will fail. If you receive an error indicating that access was denied, check the values entered in any of the above fields to determine if a mistake was made. If you are using "localhost" for your server name, try replacing this with the fully qualified domain name of your database server. If the connection still fails, check with your database administrators to determine whether the user credentials (username and password) are correct and have the required level of access.
Once a database table is selected, the database fields list is automatically populated with all the fields that correspond to the selected table. By default, all fields are selected and displayed. However, individual fields can be included or excluded by simply selecting one or more fields from the list and clicking the Update button at the bottom of the form. This is especially handy for those tables with an inordinately large amount of fields. To avoid excessive horizontal scrolling on the page simply reduce the total number of fields displayed by selecting one or more specific fields from this list.
Primary Keys and Unique Indexing:
Field order within a table makes no difference as long as there is at least one Primary Key field available in the table. If a Primary Key is not available, a Unique index on any field within the table will also work. But either a Primary Key or Unique designator is required for any table you wish to modify using AMPLE Scaffolder Database Utility. Tables without a unique or primary key are still available through the AMPLE Scaffolder Database Utility but they will be displayed in a read-only format. Please note that the current iteration of AMPLE Scaffolder Database Utility does not support composite unique keys. Unique keys must exist on a single field only for AMPLE Scaffolder Database Utility to properly index and edit records within that table.
In order for AMPLE Scaffolder Database Utility to properly function, user permissions are compared against the information schema table to determine if the specified user has proper access. Without the proper level of access, users will be unable to manipulate the data within the database. If an error occurs indicating that the specified user does not have access, contact your database administrators to determine whether or not the user's access can be modified appropriately. At a minimum, Scaffolding users must have "USAGE" access within the information_schema.USER_PRIVILEGES table, or individual levels of access for the following commands within the information_schema.SCHEMA_PRIVILEGES table: "SELECT","INSERT","UPDATE","DELETE","CREATE". These are the minimum options required for AMPLE Scaffolder Database Utility to properly access the database and perform its functions.
A server name is required for AMPLE Scaffolder Database Utility to be able to connect successfully. If you are running this file on the same server as your MySQL database resides, you can normally use "localhost" in the server name field. For remote locations, a fully qualified domain name is typically required. If you experience an error indicating that the host server name or IP address cannot be reached, check the value entered in the respective field under the Connect tab and use a DNS lookup utility to ensure the value specified can be successfully resolved in DNS. Naming resolution problems are often the result of restrictive firewall policies, so check with your local network technicians if this problem persists.
Like the database list, the database table list is automatically generated based on user selection. Once a database is selected, all available tables within that database will show up in the database table list. Once a table is selected from this list, the form will once again resubmit, automatically selecting all the records from within that table and displaying them at the top of the form in the records display table. Selecting an option from the database table list will also result in an additional list of table fields that correspond to the table selected. These individual fields are available for selection under the Advanced tab, within the SQL Field Select area.
The username specified within the Database Username field under the Connect tab, must have full access to the selected table to perform the basic AMPLE Scaffolder Database Utility functions. If you receive an error indicating the user does not have the proper database access, you should contact your database administrators to either increase the user's level of permission or to select an alternate user who does have the necessary access. Use the Schema Access section of the help documentation for further details on what permissions are required within the database.
Access to MySQL requires a valid account that has the proper access to connect to the database server. At a minimum, "USAGE" access is required on both the Information Schema database as well as the primary database to which you wish to connect. This permits the user in question to successfully manage and modify data within the database. For more information on configuration details and troubleshooting see the User Access section and the Schema Access section within the help documentation.
Like any other access credentials, a password is also required along with the username, to successfully connect and access the database server. Like the Database Username, this password is set within the database itself. If you are having trouble connecting to the database with a specific set of credentials, contact your database administrator to verify that the user exists, that the password is correct and that those credentials have the required level of access to the database you're attempting to access.
Booleans and Tiny Integers:
In addition to the auto-joining drop lists for lookup tables and Enum fields, there is also an option to automatically generate drop lists that correspond to Boolean field types. MySQL will typically utilize a TinyInt field with a length of one to similate a bit or boolean field. But since the TinyInt data type can also be used to track smaller numbers, enabling these boolean drop lists is optional. Enabled by default, all Boolean and TinyInt(1) field types are converted to a Yes/No drop list making it that much easier to add and modify records with these field types. If you wish to disable this feature, select "No" from the "Auto-Join Boolean Fields" drop list under the Options tab.
The CSV field type, also known as an Extended Set field type, is one of the handier options available in AMPLE Scaffolder Database Utility. Enum Field Types and Set Field Types are typically used when you need to restrict users to a pre-established list of comma-separated values (CSV) without using a separate lookup table. These field types allow users to insert one (Enum) or multiple (Set) values into a field as long as all of the values match items within the pre-established list. The primary drawback to an Enum or Set field type is that the combined length of all the values cannot exceed 255 characters. Enter the CSV field type, a simulated Set field without the 255 character length limitation.
Requirements for this option are similar to those of a lookup table. A Database Lookup Flag is required in the lookup table that will be used to populate the CSV field drop list. Any field being used as a CSV field must support string values. The two most common options are a Varchar or Text field type. The type selected is normally based on the length (total number of characters)of the values you expect to store in the field. Finally, since the built-in referential integrity rules will obviously fail due to a data-type mismatch, a pseudo-referential rule is established by adding a flag in the Comment area of the table properties. Under the table properties, select the field you wish to designate as a CSV field and add a CSV flag to the Comment area for that field. The format for a CSV flag is [CSV=myTableName], where myTableName is the name of the table you wish to join to that particular field. For data integrity reasons, it is highly recommended that this option remain enabled. However, the default behavior for each individual Auto-Join field type can be easily disabled under the Options tab if necessary.
Data Type Restrictions:
Due to integrity rules within most databases, it is not possible to insert just any type of data into any field. Fields within databases are "type specific", meaning that each field has parameters to know what it can and cannot accept when it comes to data values. Numeric fields, in particular, are not designed to accept string values and will alert users if they see this occur. If you receive an error related to numeric field values, check the form field in question to ensure that the value specified within it is a valid number. As of version 2.0.1, the following datatypes are supported within AMPLE Scaffolder Database Utility:
bigint • bit • bool • boolean • char • datetime • decimal • double • enum • float • int • longtext • mediumint • numeric • set • smallint • text • timestamp • tinyint • tinytext • varchar
The latest version of AMPLE Scaffolder Database Utility does not offer support for Blob, Date and Time field types but it's possible that support for these data types may be added in future revisions.
Date and Time Field Types:
Enum Field Types:
The Enum field type is quite handy for those times when you want to limit values in a field to a specific list of options. The main problem with using an Enum or Set Field Type is that these preset values are not available to the end user. Since Enum and Set field types will only accept values that match the pre-established list of options, AMPLE Scaffolder Database Utility makes life easier by automatically providing the end user with a drop list containing all the acceptable options. For data integrity reasons, it is highly recommended that this option remain enabled. However, the default behavior for each individual Auto-Join field type can be easily disabled under the Options tab if necessary.
File Field Types:
The latest version of AMPLE Scaffolder Database Utility is now capable of supporting file uploads. This feature works by adding flags to the Comment area within a table. There are a number of flag options available, but only the FILENAME flag is required for file uploads to function properly. All other file-type flags are optional but can be added to the table Comment area for each respective field if you choose to capture additional file-related data. Although not required, it is recommended that a FILEPATH attribute be assigned to a field as well. This allows original paths to be captured so the files can be successfully removed at a later date. If this value is not captured, a path based on the current uploads directory will be used instead. This may result in files not being properly removed from the file system when file references are deleted within a record.
The myFileFieldName reference within each flag, corresponds to a single field name within the table that will store the full name of the file being uploaded. The same field name will be assigned to each separate flag so that the respective attributes can be captured and stored for the file being uploaded. The available flag options are as follows:
|[FILENAME=myFileFieldName]||This flag is assigned to the field that will capture the full file name once it has been uploaded.|
|[FILEPATH=myFileFieldName]||This flag is assigned to the field that will capture the full file path as specified under the Files options.|
|[FILESHORT=myFileFieldName]||This flag is assigned to the field that will capture the file name minus the extension.|
|[FILETYPE=myFileFieldName]||This flag is assigned to the field that will capture the file mime-type.|
|[FILESIZE=myFileFieldName]||This flag is assigned to the field that will capture the file size in bytes.|
|[FILEEXT=myFileFieldName]||This flag is assigned to the field that will capture the file extension if present.|
Referential Field Types:
The referential or lookup field is not an official field type within MySQL. It's simply a field that contains, and is referentially tied to, ids that exist in a secondary table. One of the benefits to using AMPLE Scaffolder Database Utility is that it attempts to automatically join any secondary lookup tables to the primary table based on any pre-established referential integrity rules. This is a great benefit since it not only enforces the integrity rules, but also gives users a user-friendly list of options from which to select. For data integrity reasons, it is highly recommended that this option remain enabled. However, the default behavior for each individual Auto-Join field type can be easily disabled under the Options tab if necessary.
To get the benefit of this feature, a lookup flag or LUF designator is required to properly join and display data from any lookup tables within the database. In addition to the lookup flag, referential integrity rules must also be utilized within the database. The lookup flag designator must be added to the Comment field within each of the respective lookup tables. To do this, edit the lookup table properties and select the field whose values you wish to display (rather than the lookup id itself). Simply type the lookup flag designator into the Comment area for that particular field, and save the table changes back to the database. The format for a lookup flag is [LUF=myFlagDesignator], where myFlagDesignator is the term you wish to use for the designator itself. The default lookup flag designator is "scaffold", but you can select any term if you wish to override this value. To override the default lookup flag designator, specify a new field value under the Connect tab, next to Database Lookup Flag.
Set Field Types:
The Set field type is similar to the Enum Field Type in that it allows you to limit values in a field to a specific list of options. The difference between the Set and Enum field types is that the Set field type allows a user to select one or more options from a list, while the Enum is limited to a single selection. The main problem with using either an Enum or Set field type is that these preset values are not available to the end user. Since Enum and Set field types will only accept values that match the pre-established list of options, AMPLE Scaffolder Database Utility makes life easier by automatically providing the end user with a drop list containing all the acceptable options. For data integrity reasons, it is highly recommended that this option remain enabled. However, the default behavior for each individual Auto-Join field type can be easily disabled under the Options tab if necessary.
Unique Identifier Field Types:
|[UID=UNIQUE]||10-15 character random string.|
|[UID=RANDOM]||15-20 character random string.|
|[UID=MD5]||32 character random string.|
Client-Side and Server-Side Validation:
By default, the AMPLE Scaffolder Database Utility supports both client-side and server-side validation. These options do not require any additional rules to be established in the database. They are built automatically based on the data types specified in the table structure itself, along with settings for the allowance (or not) of NULL values in a particular field.
For instance, if a datetime field existed in a table, the AMPLE Scaffolder Database Utility would check to ensure any data positioned to be placed into this field matched the format required for a datetime field. This generally looks like "2013-05-20 13:45:00" so if the value passed doesn't match this structure, it would be considered a bad value. If this field was set to allow NULL values than a blank string would be fine. However a numeric value or otherwise ill-formatted date string would not conform and would therefore generate an error as a result. Setting this field to forbid NULL values means that a blank string would no longer suffice and an error would be generated in this case as well.
These are the types of issues that the AMPLE Scaffolder Database Utility handles with ease, preventing end users from making mistakes whenever possible and presenting useful troubleshooting information to help them determine where things went wrong. And since it works both on the client side as well as the server side, AMPLE Scaffolder Database Utility provides double the protection and peace of mind that data entered will meet or exceed the established database requirements.
HTML Tagging Support:
By default form fields are designed to accept HTML syntax. However, allowing users to enter HTML tags directly into a form field can lead to potential problems if the data is being displayed through a web interface and erroneous code is entered. To prevent this possibility, developers can easily enable or disable HTML tag support within the form fields. By disabling this option, all HTML tags are automatically stripped from any data entered through the AMPLE Scaffolder Database Utility form.
|Active Record Highlighting||Visually identifies the last record modified by a user|
|API Interface||Easy class instantiation makes for quick access to all public variables and functions|
|Auto-Generated Form Fields||Builds complete entry forms based on database specifications and field types|
|Auto-Generated Select Lists||Converts all reference tables, enums, sets, and boolean field types to user-friendly drop lists|
|Auto-Join Disabler||Provides users with the ability to enable / disable individual auto-join policies based on field type|
|Bulk-Insert Option||Allows developers to test database or front-end performance using randomly generated type-consistent values|
|Client-Side Validation||Enforces data type matching and non-null field types - alerts and highlights any form fields with errors|
|Controlled Access||Uses built-in MySQL permissions to control access to databases and tables|
|CSV Field Type Support||Provides users with the ability to automatically join a character field with comma-separated-values to corresponding records within a lookup table|
|Data Exporting||Capture all records from a table based on specific query criteria and save them to a Microsoft Excel spreadsheet for further analysis.|
|Data Type Matching||Ensures that values entered are correct based on the field type selected|
|Date Time Calendar||Eliminates formatting errors during date time field insertion and modification|
|Error Reporting||Tracks and reports on any internal object errors as well as MySQL errors and warnings|
|Extensive Field Types||Supports bigint, bit, bool, boolean, char, datetime, decimal, double, enum, float, int, longtext, mediumint, numeric, set, smallint, text, timestamp, tinyint, tinytext, and varchar|
|External Configuration Overrides||Allows administrators to lock down specific settings in the application based on individual security requirements|
|File Upload Support||Allows users to upload one or more files within a single database table while capturing all the standard file data such as path, name, type, size, and file extension.|
|Flexible and Intuitive||Works "auto-magically" out-of-the-box with most standard MySQL configurations.|
|Friendly GUI||Simple 3-tab interface makes option selection quick and easy|
|Full Paging and Sorting||Allows users to more easily work within larger tables and find the records they need|
|Help Docs||Helpful documentation is automatically compiled into the tool to help acclimate new users|
|HTML Tag Stripper||Allows or denies HTML tag support within field values during record inserts and updates|
|Individual Field Selection||Gives users the ability to individually select one or more specific fields from a table|
|Location Independent||Capable of accessing any local MySQL server with "localhost" or any remote server with a fully qualified domain name|
|Minimized Data Option||Enforces min/max row height in record display to assist users in accessing individual records and data|
|Multi-Referential Lookup Support||Allows multiple fields within a primary table to point to the same lookup table|
|No AJAX Complications||Relies on standard HTML form submissions to reduce complexity and file footprint required|
|Query Generator||Dynamic SQL queries based on user selections|
|Query Viewer||Allows users to view the final SQL output|
|Record Duplication||Duplicate an existing record in mere seconds using this feature.|
|Record Row Resizer||Allows users to expand or contract records displayed through the web interface to more easily view the data within.|
|Records Per Page Option||Allows users to select anywhere from 10 to 100 records at a time|
|Server-Side Validation||Provides a fall-back with default null, blank or numeric values, in the event of client-side validation failure|
|Single File Format||Releasable version is compiled and minified to reduce file size and make for easy deployment|
|Small Footprint||Total disk space usage of less than 150 kilobytes|
|Software Update Notification||Automatically checks for the latest version of the software and notifies developers when a newer version is available|
|Syntax Highlighting||Color-coded SQL syntax makes it easier to distinguish reserved words within queries and field data|
|Table and Field List Generator||Automatically provides users with a list of available resources on the server based on database permissions|
|User-Friendly Labels||Converts upper case, lower case, camel case and underscores to human readable headers and select options|
|Where Clause Generator||Allows users to specify an unlimited number of SQL query conditions using pre-validated values for all lookup tables, enum, set and datetime field types|
AMPLE Scaffolder Database Utility was designed with "user-friendly" in mind. In traditional scaffolding classes, a standard text input field is provided for editing values pulled from lookup tables, Enum and TinyInt database field types. Since these database fields are both data type-specific but also data-specific, this design approach is relatively useless to the end user unless they happen to know the acceptable values and data types to insert into these areas. The goal of AMPLE Scaffolder Database Utility was to eliminate this problem by automatically and intuitively joining all lookup tables and retrieving all CSV, Enum, Set and Boolean field values, and providing these values to the end user as standard HTML select list.
AMPLE Scaffolder Database Utility was originally created and tested using Apache version 2.2.8, PHP version 5.2.6 and MySQL version 5.0.5. While it has not been fully tested with earlier versions of these applications, that is not to say it will not function correctly on legacy platforms. If you run into difficulties or problems with this package, try upgrading your server components to the latest version. For those folks using the latest versions of Apache, PHP and MySQL, we are continuously testing AMPLE Scaffolder Database Utility to ensure its continued and stable operation within these environments.
As of version 2.0.5, AMPLE Scaffolder Database Utility has a built-in software update utility to keep developers informed of new releases. This option is enabled by default and will automatically display a message at the top of the web interface if the current version in use is out of date. This feature can be easily disabled under the Options tab if developers do not wish to be notified of new releases.
One of the greatest features of AMPLE Scaffolder Database Utility is that it generates all the required SQL statements automatically behind the scenes. This makes it quick and easy for developers to get to records held within the selected table. By default, all records are selected. But if you happen to be looking for a particular record, the advanced where clause generator (under the Advanced tab) can be extremely handy. Selecting a field from the first drop list will automatically update the second (operator) and third (value) options based on the field type selected. This prevents any SQL errors due to data type mismatching. Entering a value is even easier with built-in features like the datetime calendar selector and auto-generated drop lists based on any lookup tables or Enum/Set/Boolean type fields. Plus, there's no limit to the number of custom clauses. Just click the add button (the plus button immediately to the right of the clause fields) to generate a new clause and select your next set of options. Keep in mind that by default all searches are exclusive which means each conditional clause that is added will serve to narrow the list of results even further.
Display Minimized Data:
When a database table contains a large number of fields or large quantities of data, displaying the data itself can be cumbersome because it requires a great deal of scrolling to view the output from every field. To assist developers in viewing the record output, record data is displayed in a minimized format by default. For those tables plagued with a large number of fields or data, this minimized view can make it much easier to get to individual records for modification. For those developers who wish to view all the fields and data up front, you can simply set this option to "No" under the Options tab.
A secondary option, and perhaps a more useful one at that, is the record row toggler. This function is built into the main record display table and allows you to expand or contract any row to more easily view the data. To use this option, simply click the toggle button next to the row you wish to resize. If it's not already expanded, it will do so, allowing you to view all of the data within each of the fields. Clicking this button again will return the record to its minimized state. To reset any custom row heights (toggled on or off), simply refresh your browser window or click the Update button and all the records will return to the previously saved state (either minimized or maximized).
Display Records After POST:
By default the main records list from the selected table is automatically displayed after any major transactions such as inserting a new record or modifying or deleting an existing record. However for remote calls to AMPLE Scaffolder Database Utility, this option may not always be required. Developers can easily disable this option by selecting No and clicking the Update button at the bottom of the form.
This option has been temporarily removed from the package.
All SQL queries are automatically generated behind the scenes based on front-end user selections. But that doesn't mean developers have to "fly blind". This particular option assists developers by making all major record transactions visible. Users who are new to SQL (in general) and want to get more familiar with the syntax can display the queries to help them learn. The query display option can be turned on or off (under the Options tab) as needed by selecting "Yes" or "No" and clicking the Update button at the bottom of the form.
The Export feature allows users to capture all records matching the current query criteria in an excel spreadsheet. This can be quite useful for management, meetings, data distribution or other types of offline metrics analysis. Plus, with the built-in auto-joining features, the data retrieved is user-friendly, making it much easier for management and metrics to sort and make sense of the data provided. If the actual data stored within a table is required, just disable all the auto-join options and resubmit your current query, before clicking the Export button. Lastly, it should be known that exported data is not limited by the Records Per Page option (under the Options tab) so use caution if you use the Export feature on tables with a large amount of records. The larger the record count, the more time the export process will require.
Inserting Bulk Records:
Another major benefit to AMPLE Scaffolder Database Utility is that it offers a very cool feature known as Bulk Insert. Oftentimes, developers need to get a feel for how a database performs with large amounts of data or they may just need to visualize the output in a specific fashion. For smaller databases or brand new databases that have yet to be populated with copious amounts of data, this can present a problem. In the past, developers were left with the labor-intensive task of manually inserting hundreds of records in order to have data with which to work. With the Bulk Insert feature, those days are history.
Developers can now utilize the Bulk Insert feature to automatically and intuitively populate a database with anywhere from 5 to 10,000 records at a time. Strictly enforced data type matching ensures that field types and data are fully compatible. Plus, a randomization algorithm is used to ensure that each record being inserted differs as much as possible from other records that exist.
Not only is this feature extremely quick and easy to use, it's also fully reversible. All records created using this option will be tracked making their removal as quick and easy as their creation. Once records have been added using Bulk Insert, look for a Purge button next to the Update button on the main tabbed interface. Clicking the Purge button and confirming will result in the immediate removal of all Bulk Inserted records.
To use the Bulk Insert feature, select the table to which you wish to add records. Then click the plus button in the top-left-hand corner of the records display table to add a new record. At the bottom left-hand side of the Edit form there is an option for Bulk Insert with a drop-list that is used to select the total number of records to be inserted.
As of version 2.0.8 of AMPLE Scaffolder Database Utility, developers now have the option of enabling one or more fields within the form in order to force the Bulk Insert feature to use a value specified or selected instead of a randomly generated value. The default behavior during a Bulk Insert is to disable all fields, resulting in 100 percent randomly-generated, data type-specific values. This means that the form fields themselves do not need to be filled out with data. The Bulk Insert feature does all of this behind the scenes.
To disable random values for a particular field, simply select the checkbox next to that field and add or select a value for this field. This new value will now be assigned to this field for all records inserted using the Bulk Insert option. De-selecting the same checkbox returns the field back to a state where it will use randomly assigned values instead. Depending on the size of the table and how many records were selected for insert, the Bulk Insert process can range from a few seconds up to a few minutes. Once the Bulk Insert process is complete, you will be returned to the main records display page where the new records should now be displayed.
Inserting Repetitive Records:
Insert Plus One is another handy little feature built into AMPLE Scaffolder Database Utility. There are times when data must be manually entered on a record by record basis, but the bulk of the data is the same for each record. These minor differences in data are a nuisance because they require the user to enter the same data over and over for different records. The Insert Plus One feature allows all of the previously entered data to repopulate into a newly created form, allowing you to modify only the small portions that differ for each record instead of filling out the entire form all over again.
To provide an example, picture entering personnel data into a Human Resources database. For a smaller office, the state, city, zip code and business phone remain the same. Only the name of the individual is different for each record. These records can be easily created by clicking the Add Record button located in the top left-hand corner of the records display table, entering all the required data into the form fields, and then clicking the "Insert + 1" button. This action will save your current record back to the database (behind the scenes) and then follow-up immediately by presenting you with a new form that is pre-popluated with all the same data you just entered. You can continue this process for as long as necessary until you reach the last record you need to enter. Once you reach this last record, simply click the "Insert" button instead to complete this process and return to the records display table. Note that the "Enable Bulk Insert" feature must be enabled under the Options tab to utilize this feature.
Records Per Page:
This one is pretty self-explanatory. Just select a different option from the drop down list under the Options tab to change this value. Clicking the Update button at the bottom left of the form will update this value along with the total number of records displayed each time data is retrieved and displayed from within the database table.
Referential Lookup Tables:
Based on referential integrity rules established within the database, AMPLE Scaffolder Database Utility will attempt to automatically join any lookup tables with the primary table being viewed. There is one caveat to making this work, however. The lookup table must have at least one record within it to ensure that there is something to choose within a drop list. This typically won't be a problem in most cases because with integrity rules in place, the lookup table would never be allowed to be empty. If you do see an error related to "no records found for lookup table", it most likely means that a lookup table was recently created or otherwise modified, resulting in a lookup table that is now empty of records. To fix this problem, simply add one or more records to the lookup table in question and then refresh the AMPLE Scaffolder Database Utility web page.
Reserved words in MySQL are automatically highlighted in the query display. This option gives you the ability to highlight reserved words within the records as well. This is especially useful when viewing the information schema table, making it much easier to determine data types and other SQL options. Syntax highlighting is currently only available on string fields with values less than 255 characters. Because of the overhead required to match terms, strings longer than 255 characters can cause record retrieval to run extremely slowly. If you still notice excessive wait times during record retrieval, try disabling this feature altogether.
This feature has been temporarily disabled due to the performance overhead required for larger data sets. We are currently working on methods to optimize this feature so we can re-enable it in future releases.
File Previews and Downloads:
As of version 2.1.7, the AMPLE Scaffolder Database Utility now offers file previewing and downloading for any files that were uploaded and saved to a table record. This allows users to verify that the correct file was attached to the record as well as making these files available to other users and developers. For image uploads specifically, a small preview thumbnail is now shown when editing any records with images attached. For more information on uploading and storing files you might want to see the Help sections on Upload Directory, File Field Types, and Thumbnail Generation.
File names and other common attributes related to File Field Types are normally saved in a character-type field. Sometimes these fields have string-length limitations where the field only accepts values that are "X" number of characters in length. If this is the case and the maximum length is known ahead of time, the File Limit Length field under the Files tab can be used to specify the maximum allowable length. The Length Limit option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.
Web servers are typically configured with a default size limit for all files being uploaded. The settings as configured on the web server and within PHP cannot be overridden in terms of maximum size. However, a lesser value can be added to this field to enforce a file size smaller than that of the server configuration. The File Limit Size field accepts straight integer values which are then calculated as Bytes. The field also accepts PHP's standard for size parameters in the form of (K)ilobytes, (M)egabytes or (G)igabytes. So, for example, if you wished to limit the upload size to four megabytes, you could simply enter "4M" in this field. The Size Limit option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.
As of version 2.0.7, AMPLE Scaffolder Database Utility comes equipped with the option for on-the-fly thumbnail generation for any JPEG, GIF or PNG image types. The current version offers up to twelve different thumbnail sizes ranging from 75x75 pixel boxes to 1600x1200 proportional images.
Like file uploads, image files, and their corresponding thumbnail counterparts, are saved to the directory path specified in the Upload Directory field under the Files tab. Note that this tab will only be visible if the required flags have been set in the database. See File Field Types for more information on these configuration requirements. Thumbnail images maintain the same name as the original file with an added underscore and extension based on the size selected. For example, if the image "myphoto.jpg" was uploaded and "1024" was selected under the thumbnail options list, the new file would be named "myphoto_1024.jpg". If no size options are selected from the thumbnail option list then only the original file will be uploaded and no additional thumbnails will be created. The Thumbnail Generator option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.
Limits uploaded file types based on a specific list of comma-separated file type extensions. For example, if you wanted only images to be allowed during upload, the value of this field might look as follows: jpg,gif,png,jpeg,bmp,tiff. File types are checked based on both extension and mime-type for additional verification. Note that the Types Allowed rule will override the default or specified value assigned to Types Forbidden. The Types Allowed option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.
By default a variety of executable, batch, script and other dangerous file types are blacklisted, effectively blocking them from being uploaded. But for some applications it may be necessary to override the default "danger" list so an option was added so that this parameter can be disabled. For security reasons we recommend leaving this option enabled unless you absolutely need to change it. The Types Forbidden option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.The default list of forbidden file types is below.
|386||Windows i386 File Type|
|ade||Microsoft Access Project Extension|
|adp||Microsoft Access project|
|app||Visual FoxPro Application|
|asx||Advanced Stream Redirector File|
|bas||Visual Basic Class Module|
|chm||Compiled HTML Help File|
|cmd||Windows NT Command Script|
|cpl||Control Panel Extension|
|dll||Windows Dynamic Link Library Extension|
|Outlook Express message|
|emf||Enhanced Windows Metafile (Graphic format)|
|eml||Outlook Express message|
|hlp||Windows Help File|
|hta||HTML Applications (Microsoft HTML archive?)|
|inf||Setup Information File|
|ins||Internet Naming Service|
|isp||Internet Communication Settings|
|jse||JScript Encoded Script File|
|mda||Microsoft Access Add-in:Microsoft Access Project|
|mdb||Microsoft Access Application|
|mde||Microsoft Access MDE Database|
|mdt||Microsoft Access workgroup information|
|mdw||Microsoft Access workgroup information|
|mdz||Microsoft Access Wizard Template|
|mht||Web Archive File|
|mhtml||Possible Eudora meta-refresh attack|
|msc||Microsoft Common Console Document|
|msi||Windows Installer Package|
|msp||Windows Installer Patch|
|mst||Windows Installer transform / Visual Test Source File|
|nch||Outlook Express Folder File|
|ops||Office XP settings|
|pcd||Microsoft Visual Test compiled script (P-Code compiled test scripts) / Photo CD Image|
|pif||Shortcut to MS-DOS program|
|prf||Microsoft Outlook Profile Settings|
|scf||Windows Explorer command|
|sct||Windows Script Component|
|shb||Shell Scrap object (Document shortcut)|
|shs||Shell Scrap Object|
|sys||Windows System File Extension|
|url||Internet Shortcut (Uniform Resource Locator)|
|vbe||VBScript Encoded Script File|
|vbs||VBScript Script File|
|vxd||Virtual Device Driver Extension|
|wms||Windows Media Skin|
|wsc||Windows Script Component|
|wsf||Windows Script File|
|wsh||Windows Scripting Host Settings File|
When utilizing a central directory for all uploaded items, users are quite capable of overwriting eachother's files if the names match that of an existing file on the server. Enabled by default, the File Names Unique option prevents the overwriting of existing files because each new file is checked against the upload directory prior to being saved. If another file with the same name exists, the newer file will be renamed to a serialized version of the original name. For example, if the old file was named BUDGETACCOUNTING.pdf, then the new file would be named BUDGETACCOUNTING1.pdf. Disabling this option means that files with the same name will be overwritten each time. See the Upload Directory option for more details on how and where to specify a path for saving files during upload. The Unique Naming option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.
For those tables with records that expect files or attachments, a file uploading feature is now available within AMPLE Scaffolder Database Utility. In order to properly save the files on the server, an upload directory must be specified ahead of time. The File Upload Directory field under the Files tab is where you specify a relative or absolute path to a directory where all uploads can be saved. The Upload Directory option can be found under the Files tab. If you don't currently see a tab labeled Files you will need to first configure the appropriate options within the database to support this option. See the File Field Types section for more details.
External Configuration Overrides:
As of version 2.1.9, the AMPLE Scaffolder Database Utility now offers the ability to override many of the application settings normally available to the end user. It provides web and database server administrators with an option for locking down a subset of options within the application, based on their individual security requirements. There is no requirement to actually apply settings through the file. It is simply provided as a tool to assist administrators. The file provided is currently named "amplescaffolder_config.php" and needs to reside in the same directory as the main "index.php" file included with the software package.
Any settings you wish to override within the app must be added as override values in the configuration file. In addition, the ENABLE_OVERRIDES constant (near the top of the file) must be set to TRUE to enforce any of the override settings. This makes it quick and easy to enable or disable all of the override settings at once. Please keep in mind that the end users must not have the ability to modify either of these two files. Place them in a directory with a READ/EXECUTE level of permission so they cannot make changes directly to the code used behind the scenes. The configuration file conforms to PHPDocs standards and includes full comments for each item to assist administrators with applying the correct settings.
AMPLE Scaffolder Database Utility offers the ability to duplicate an existing record in mere seconds using the built-in Record Copy option. On the left-hand side of the records display table, simply click the Modify icon next to the record you wish to copy. Once the record is open for editing, simply click the "Copy" button at the bottom of the form. This will copy all of the existing data for that record into a brand new form, preserving all of the values from the previous record in the process. This makes it extremely easy to create similar records that only have a few minor differences. For quick duplication of records see the Help section on Inserting Repetitive Records.
Maximum POST Size:
Apache's web server and PHP are both configured with default settings that control the total amount of data being sent to the server. Depending on your server's configuration, it's very possible to exceed these settings when posting large amounts of data or uploading large files. Unlike some of the other options available, the post_max_size and upload_max_filesize directives cannot be overridden during run time because these values are parsed prior to the PHP itself.
Luckily, AMPLE Scaffolder Database Utility was designed with user-friendly in mind. If your post data happens to exceed to pre-configured maximum of one or both of these settings, a warning will occur indicating which parameter was triggered. One of the most common culprits in these type of violations is one or more excessively large files being uploaded. If you experience an error related to max upload or max post size, try reducing the size of any files attached to your form. If you absolutely must upload larger files you'll need to contact your local web server support team and see if they can't increase the maximum settings.