Anyone else using DB-Schema-Validation for MVC projects?

Question

Ok, inspired in part by CakePHP's Model Validation I created a project which uses a separate database schema file. I didn't like that in CakePHP the model file and the db-schema are combined in the same php file. I like having them separate.

All of my sql updates, inserts, and deletes are first passed by this schema. I wrote update() insert() delete() functions to do this validation automatically.

Why? Because I can accept a post from a visitor and send the un-checked data to my database without having to check it. My schema filters for innocent and not so innocent violations.

Here is an example of a database schema:

<?php
$schemas    = array('database_name_hidden'=>array(
'assigned_weeks'=>array(
    'id'=>array('id'),
    'user_id'=>array('foreign_id','users'),
    'week_number'=>array('posint'),
    'year'=>array('posint'),
    'unit_id'=>array('foreign_id','units'),
    'claim_listing'=>array('posint'),
    'created'=>array('created'),
    'modified'=>array('modified'),
    'resort_id'=>array('foreign_id','resorts','required'),
),

'trade_listings'=>array(
    'id'=>array('id'),
    'assigned_week_id'=>array('foreign_id', 'assigned_weeks','required'),
    'listing_assigned_week_id'=>array('foreign_id', 'assigned_weeks'),
    'opposite_id'=>array('numeric'),
    'listed'=>array('bool'),
    'prev_id'=>array('foreign_id','trade_listings'),
    'next_id'=>array('foreign_id','trade_listings'),
    'listing_email'=>array('email'),
    'listing_description'=>array('text'),
    'trade_confirmation_number'=>array('text'),
    'external_resort_id'=>array('foreign_id','resorts'),
    'external_unit_number'=>array('text','size'=>array(1,6)),
    'external_start_time'=>array('time_future',),
    'external_end_time'=>array('time_future'),
    'admin_comment'=>array('text'),
    'comment'=>array('text'),
    'created'=>array('created'),
    'creator'=>array('creator'),
    'modified'=>array('modified'),
    'modifier'=>array('modifier'),
    'resort_id'=>array('foreign_id','resorts','required'),
),
);
?>

Anyone else doing something like this?

Answer 1

I am not sure I really understand the question, but speaking about DB, Validation, Schema, and PHP, I really like the ORM Framework called Doctrine.

(It's the default ORM stack of the PHP Framework symfony, btw ; but can be integrated quite easily with other frameworks -- I've already used it with Zend Framework, for instance)

It's website seems to be down right now (they've been experimenting some high-load-related troubles, those last weeks), but it provides classes/method to do validation of data, based on the YAML-files describing the schema, before inserting data into DB.

You might want to take a look at it...