html2sql rfc - experimental

There once was a poor, green, consultant who was faced with writing a giant schema on an underbid contract. He needed to code 1000 fields into 200 tables by hand. But wait, the customer was hip enough to make his own internal static prototype in HTML to communicate what the system should look like. Yipee! So the contractor began an experiment to write a few quick perl scripts to grok the HTML for inputs and produce an SQL schema, which then would snarfed by DB_DataObjects. I'll include the moderately customized experimental files in this message. The HTML grokkage mapping proceeded as follows. * Each HTML file F.html mapped to one CREATE TABLE F (...); * <input type="checkbox" name="foo"> --> foo BIT, * <input name="bar"> --> bar VARCHAR(255), * <textarea name="yoo"> --> yoo TEXT, * <select name="baz"> <option>aaa</option> <option>bbb</option> <option>ccc</option> </select> --> plan 1. an sql table, or --> plan 2. some DB_DataObject methods Enum Plan 1: Generate a table for each <select>, with each <option> inserted as one row there. CREATE TABLE F_baz_enum ( id INT NOT NULL auto_increment, name VARCHAR(80), PRIMARY KEY (id)); INSERT INTO F_baz_enum VALUES (0,'aaa'),(0,'bbb'),(0,'cccc'); Enum Plan 2. Generate a SMALLINT SQL field for each enum, generate some php crud into a text file that implements the enums, then override the DB_DataObject_Generator method in the DB_DataObjects package to scarf those text files when generating classes from the SQL and included them in the classes. [NB: you cannot use PHP's include or require function inside a class, for eg, to bring in new methods at runtime. See php.net]. The php crud: /** BEGIN enum access methods from F.htm **/ // array of int=>str function baz_getAll() { return array('aaa', 'bbbb', 'cccc'); } // str of enum $n, or $this->units if null function baz_toString($n=null) { $a = DataObjects_F::units_getAll(); return is_null($n) ? $a[$this->units] : $a[$n]; } /** END enum access methods from F.htm **/ Enclosed in attached zip are: * getinputs-table-enums - which does Enum plan 1, outputs just SQL. * getinputs-method-enums - which does Enum plan 2, outputs the SQL and writes one .enums file. * myGenerator.php - subclasses the DB_DataObject Generator and overrides one method. * myCreateTables.php - Like original but s/Generator/myGenerator/g. * example-getinputs-Makefile - How I call the HTML scanners - very customized, but maybe an idea for you * example-dataobjects-Makefile - How I call the table generator I hope someone can benefit from these things in this messy stage. They were meant to get my HTML converted one time, so are heavily customized in parts. No error checking, no help strings, no warranty. The next logical step to this hairbrained scheme would be to convert the HTML into some kind of template, so when it again meets the fields it spawned, it will have a place for them: from <input name="F_foo" size="20" /> to maybe <input name="F_foo" size="20" value="{f_foo}" /> References: * Alan wrote earlier today Re: [PEAR] Re: DataObject and enums * Simon Willison posted this week about getting form validation info from extra HTML tags: http://www.codingtheweb.com/projects/newslog/article/1_75047.htm Have a nice day, -- Mitchell Perilstein mitch at enetis dot net www.enetis.net/~mitch

Attachment: [application/zip] html2sql.zip