Subversion Repositories phpLibraryV2

Problem:

When creating database applications, need a simple way of creating and "edit any table" functionality from a web site. Optimum solution would be:

1. Programmer creates a database definition structure
2. In code, programmer passes database definition to a class method
3. Class handles the following:
   1. Viewing tables in tabular format, allowing selection of individual rows
   2. Viewing rows of tables in pleasing format
   3. Editing individual rows from tables
   4. Adding new rows to a table
   5. Deleting rows from a table
4. Display and editing/adding should take into account type of data being edited. Examples:
   1. Some field types may require validation
   2. Some field types may require "helper code" (ie, a date widget) added to html
   3. Lookup tables should be displayed correctly on view, and selectable on edit (ie, <select>)
   4. many-to-many relationships involving intermediate tables should be handled seamlessly
5. Display/editing should be programmer modifiable without requiring a modification of class itself
   1. Use of css tags for all column types
   2. Ability to override default elements used for various functions
6. Would also like helper class that encapsulate some common functions related to queries. These are already written in procedural form in library.php, and include things like doSQL (generic SQL executer), makeSafeSQLValue (escapes SQL values for safety)
7. There are some generic DB to HTML functions in library.php that do things like take a query and generate HTML elements like tables, select, etc...
   
   
Discussion:


1.x   I currently have direct input from the array structure used in library.php. However, with a little clean up, I could also read an SQL file that creates the table structure such as that coming from mysqldump. Currently a perl script exists that does a semi-decent job of this, but it is definitely not infallible.

2.x   Note that the structure once created rarely changes, so it could be serialized. In this form, it could be maintained in memory by Apache, decreasing resource requirements.

3.x   Eventually, it would be good to have per-session variables to allow/deny adds, edits and deletions.

3.5   It would be nice to have flag in class that would disable deletions based on a criteria

4.x   For this, it would be very nice to allow form validation via javascript. There are some very, very nice libraries that do this, and I would like to be able to use them if desired. While this is not required for the initial project, it should be considered.

4.1   This can be done with the gen_form validator (http://www.javascript-coder.com/html-form/javascript-form-validation.phtml). It sets up a validation scheme that is quite flexible.

4.2   One thing I'm thinking of is the date selector widget used in timetracker. Also, in the code we can use the PHP strtotime function:
 if ( $result = strtotime( $value ) ) {
   $value = Date( 'Y-m-d H:i:s', $result);
 } else {
   $value = '0000-00-00';
 }
 which allows interpretation of "dates" such as "tomorrow", "next month", and 5 Mar 2009


5.x    Display/editing should be programmer modifiable without requiring a modification of class itself
   Some form of templating should be used. The default would be to simply display everything using tables. 3.1 would be displayed as the results of a query in a tabular format, with controls to view/edit/delete individual rows. 3.2-3.4 would display a two column table with column names on left <td> and values in right <td>.

5.1   For display, it should be sufficient to include a "class=" for each element returned (column name, value), which would allow, for example, right justification of numbers, etc. by like token, the overall table should have a class.

5.2   Obviously, some type of templating process would work well here. Question is, do we have to re-invent the wheel, or does one of the available template processors already do what we want. Hopefully the latter. We need something that will be able to do a call-back into the class, at the very least, ie when a value is inserted, it should call the class to get the format and values to be used. Not sure if something like that exists but, if not, would be fairly easy to create.