We have done the same thing that you are doing - but we don't read the database metadata at all, we just create an xml file when we create the table that describes the datatype and html display type so we can have custom types such as email address type, etc. I thought about doing this the way you have, but ultimately I decided that depending on the database metadata would be too limited for my needs. Attached is an xml file that describes one of our tables. We have a custom application that automatically build interfaces to add, edit, list, view, and delete data based on this xml file with validation. -r On Thu, 2003-11-20 at 14:36, Tom Hebbron wrote: > Hi group, > > I'm interested to see that other people have hit this problem. In the last > few months I've been writing and re-writing PHP classes to access a > postgreSQL database for a CMS. > Here is a rough overview of the way I've dealt with the validation problem. > > tuple class > array of attribute class objects > > initialisation fetches metadata about the table/view the tuple (row) > belongs to (attribute types, domains & built in CHECK constraints, foriegn > keys, not null) > this data comes from a set of attribute metadata views (built from > system catalogs and optimised - further optimisation is envisaged for PG > 7.4) > the tuple / attribute metadata allows us to do the following things > > foreign key attributes can auto-build a list of options for a <select> > dropdown (either multiple selection using a join table, or single selection) > the options in the list come from following the foreign key definitions. > Specially named views (schema.table/view.attribute) are used to get labels > for meaningful display, > so you don't have drop down lists with your externally meaningless > primary keys, but nice values (e.g. value = doc_id and label = doc_title) > if no view is found for the table/column, then just the values are used > as labels. > > validation is doen using the folowing procedure > for each attribute in the tuple > "SELECT 'newvalue'::datatype" > if this fails, then it is a parse error, and the message "invalid > [attribute name]" is registered with the object against that attribute > > then for each check constraint (both single and multiple column) defined > against the table (including where domains are used as attribute data types) > we run the following query > "SELECT [check constraint definition] AS [check constraint name] FROM > ([for each attribute in the CHECK, output 'value'::type AS name,]) AS > values" > the result will be true or false, and the name of the only reutrned > column is the name of the check that passed/failed > if you have named your check contstraints sensibly, then the name is > used (if it failed) as an error message against the attribute(s) involved in > the check. > > if all the values parse correctly, and all the check constraints are > passed - THEN we try to do the INSERT/UPDATE > > other jobs the objects can do is insert parsed default values (from the > metadata) to display initially in the form. > > Sadly, all this 'niceness' comes at a cost, because of all the extra queries > that must be executed in order to get back full validation failure messages > from PG. > Once we have released, then it might be possible to try and go back and make > some of this functionality available inside PHP, by adding to the pg_xxx > commands > available in PHP (particularly pg_meta_data()) > > This tuple object is then renderable as XHTML form elements (with > session/db/table/attirbute unique element names md5'd) so that multiple > tables can be exposed on a single form. > > I'd be interested to hear from any other people who've built similar > systems, or about any database abstraction projects which attempt to do the > 'possible value lookup' and full validation > descripbed above. Our code was working with 7.3.4 fairly well, and should be > back up and finished soon with 7.4. Once the project is compelted, I'll see > about releasig a more generic set > of classes (our are currently tied to a project-specific inheritance layer) > or expanding this into a gBorg project or similar. > > > -- > -- > Tom Hebbron > www.hebbron.com > +39 0444540626 (Vicenza, Italy) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Ryan Mahoney ryan@xxxxxxxxxxxx http://www.flowlabs.com (updated)
<XML> <TABLE NAME="pa_discounts" DISPLAY_NAME="Discounts"> <key>discount_id</key> <field> <field_name>discount_id</field_name> <field_desc></field_desc> <display_name>Discount ID</display_name> <data_type> <type>serial_type</type> <size>4</size> <null>false</null> </data_type> <html_form_parameters> <size>8</size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only>true</read_only> <data></data> </field> <field> <field_name>discount_name</field_name> <field_desc></field_desc> <display_name>Discount Name</display_name> <data_type> <type>varchar_type</type> <size>200</size> <null>true</null> </data_type> <html_form_parameters> <size>50</size> <selected></selected> <checked></checked> <maxlength>200</maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only>false</read_only> <data></data> </field> <field> <field_name>discount_code</field_name> <field_desc></field_desc> <display_name>Discount Code</display_name> <data_type> <type>varchar_type</type> <size>32</size> <null>false</null> </data_type> <html_form_parameters> <size>32</size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only></read_only> <data></data> </field> <field> <field_name>discount_expiration</field_name> <field_desc></field_desc> <display_name>Expiration Date</display_name> <data_type> <type>calendar_type</type> <size></size> <null>false</null> </data_type> <html_form_parameters> <size></size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_calendar_form</html_display_function> <display_class>admin_text_form</display_class> <read_only></read_only> <data></data> </field> <field> <field_name>discount_amount</field_name> <display_name>Discount Amount</display_name> <data_type> <type>double_type</type> <size></size> <null>false</null> </data_type> <html_form_parameters> <size>5</size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only></read_only> <data></data> </field> <field> <field_name>last_modified</field_name> <field_desc></field_desc> <display_name>Last Modified</display_name> <data_type> <type>timestamp_type</type> <size></size> <null>false</null> </data_type> <html_form_parameters> <size></size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_date_form</html_display_function> <display_class>admin_text_form</display_class> <read_only>true</read_only> <data>_POSTGRES.CURRENT_TIMESTAMP</data> </field> <field> <field_name>last_modifying_user</field_name> <field_desc></field_desc> <display_name>Last Modifying User</display_name> <data_type> <type>int_type</type> <size>4</size> <null>false</null> </data_type> <html_form_parameters> <size></size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only>true</read_only> <data>_SESSION.user_id</data> </field> <field> <field_name>modified_ip_address</field_name> <field_desc></field_desc> <display_name>Modified IP Address</display_name> <data_type> <type>varchar_type</type> <size>20</size> <null>false</null> </data_type> <html_form_parameters> <size></size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only>true</read_only> <data>_SERVER.REMOTE_ADDR</data> </field> <field> <field_name>discount_minimum</field_name> <field_desc></field_desc> <display_name>Discount Mininum</display_name> <data_type> <type>double_type</type> <size></size> <null>false</null> </data_type> <html_form_parameters> <size>5</size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> </options> </html_form_parameters> <html_display_function>display_html_text_form</html_display_function> <display_class>admin_text_form</display_class> <read_only></read_only> <data></data> </field> <field> <field_name>discount_type</field_name> <field_desc></field_desc> <display_name>Discount Type</display_name> <data_type> <type>varchar_type</type> <size>10</size> <null>true</null> </data_type> <html_form_parameters> <size></size> <selected></selected> <checked></checked> <maxlength></maxlength> <default></default> <references></references> <foreign_key></foreign_key> <display_field></display_field> <orderby></orderby> <options> <option name="Percent" value="percent"/> <option name="Value" value="value"/> </options> </html_form_parameters> <html_display_function>display_html_select_form</html_display_function> <display_class>admin_text_form</display_class> <read_only></read_only> <data></data> </field> <list name="list_all" display_name="Discounts"> <limit></limit> <offset></offset> <fields> <field>discount_id</field> <field>discount_name</field> <field>discount_type</field> <field>discount_amount</field> </fields> <tables> <table>pa_discounts</table> </tables> <joins> <join></join> </joins> <orderbys> <orderby>lower(discount_name)</orderby> </orderbys> <groupbys> <groupby></groupby> </groupbys> </list> </TABLE> </XML>
