You need to be clear about what sort (and what amount) of extendability you really want to add:
- more flexibility in operations that affect existing fields and values (e.g. allowing more dynamic construction of sql statements, "where" clauses, etc)
- ability to add columns to existing tables
- ability to add tables to an existing database
- ability to create a new database
- ability to create a new user account on a given database server
The last two points, IMHO, are not things you would want to do via a web/cgi process -- they should be mediated (authorized) by someone with adminstrative authority on the particular machine where the DB is going to run.
But once a user account and database are established for a cgi script to connect to, it does seem like a pretty straight-forward exercise to design a web interface that allows for the creation of tables, data input methods (web forms, uploaded data files or both), and definition and storage of commonly used queries, updates or deletions.
One thing to think about will be what amount of expertise you can expect on the part of the web user. Is this being set up for people who already know SQL, or for inexperienced users? This involves deciding whether you grant a lot of power and flexibility, or offer a relatively limited range of options for defining tables and operations on them.
All database engines provide a means to query for table names and definitions (e.g. "show tables" and "describe some_table" in mysql), so putting this information dynamically into a web page is trivial. Providing an effective means for dynamically building queries and other operations will involve some cleverness and art (and details will depend on your particular DB server and expected users), but it shouldn't be overly complicated. | [reply] |
I think that it depends on many other informations:
What is it web based script?
Which SQL engine? (I guess you use MySQL) Via DBI?
How should be html stored? Serialized? Or some pieces?
Imho, you have too general quest.
| [reply] |
It is a web based script, with limited administrative access based on LDAP group authentication using apache. Mysql accessed via DBI. Only a couple of tables. Mostly I wish to make it easy for me to deploy this for different groups who have different storage requirements. In the DB we store information about people who are on call. The script is mostly used to provide a single integrated front end to 2 different paging systems. The main issue is that I would like to define this in one place to effect, DB fields, html forms, and other stuff. Instead of having to edit a whole series of subroutines to make s
| [reply] |
If I understand you correctly, you wish the universal CGI script, which is able to update (and insert or delete?) records into different db tables and is able to provide various html GUI (forms with inputs logically related with db columns)?
I am not experienced with CGI and MySQL, so I don't know, if some free codes are available on the Net - I work under pure mod_perl against PostgreSQL. But I can imagine some generic script using configuration file. I guess that the configuration must be very declarative, to describe tables, their columns, what can be edited under some conditions, etc. And I don't know, if
it is effective for you to develop generic script and write amounts of configurations after that...
I typically write one subroutine for one editor (== one html page), which calls generic subroutines, symbolically sub getHTTPQuery($request), sub doSql($dbh, $sql, $binds), sub sql2xml($dbh, $sql, $binds), sub responseFromXslt($xml,$xsl). This editor's subroutine realizes application logic: analyzes query pieces, does proper sql's, chooses proper xsl templates, does xslt transformation and returns it's html output. Some of them can be stored in configuration file, some of them can be hardcoded. My experience is that real requirements are too various and it is not effective to develop generalized editor subroutine.
| [reply] |
I have seen references to what I believe you are asking for using terms such as "data-driven programming." I cannot lay hands on an article ATM, but will poke around a bit further and revisit this node if I come up with something. In the meantime, don't forget about using PM's Super Search or another search engine to attempt searching on that phrase. You might also try searching Dr Dobb's Journal, as that tickles my memory...
| [reply] |
I found a few things about data driven programing but nothing inspiring, probably didn't find the right stuff. I have had to get the scripts tested and usable as they are so have not been able to do much work yet on making them more easily adaptable. I think I have what I need now, I will use a cfg file for most user changable parts of the program. Hashes in the cfg will determine valid db, web page form structures, and form inputs. Lastly, I will build a perl module to house commonly used functions. These improvements should make it easier to maintain and update.
| [reply] |
| [reply] |