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:
Code: Select all
$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'),
),
'realtor_listings'=>array(
'id'=>array('id'),
'photo1'=>array('filename'),
'photo2'=>array('filename'),
'photo3'=>array('filename'),
'photo4'=>array('filename'),
'photo5'=>array('filename'),
'photo6'=>array('filename'),
'price'=>array('posint','required'),
'description'=>array('text','required'),
'unit_id'=>array('foreign_id','units'),
'realtor_id'=>array('foreign_id','users'),
'created'=>array('created'),
'modified'=>array('modified'),
'resort_id'=>array('foreign_id','resorts','required'),
),
'resorts'=>array(
'id'=>array('id'),
'name'=>array('company','required','unique'),
'start_days'=>array('text'),
'created'=>array('created'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
'street'=>array('street'),
'city'=>array('city'),
'state'=>array('state_code'),
'country'=>array('country_code'),
'postal_code'=>array('text'),
'telephone_us'=>array('phone'),
'telephone_foreign'=>array('phone'),
'fax'=>array('phone'),
'email'=>array('email'),
'homepage'=>array('url'),
'contactpage'=>array('url'),
'enable_real_estate'=>array('bool'),
),
'connected_resorts'=>array(
'id'=>array('id'),
'resort_id'=>array('foreign_id','resorts'),
'connected_resort_id'=>array('foreign_id','resorts'),
'created'=>array('created'),
'creator'=>array('creator'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
),
'unit_descriptors'=>array(
'id'=>array('id'),
'resort_id'=>array('foreign_id','resorts'),
'descriptor'=>array('text','required','size'=>array(6,100)),
'created'=>array('created'),
'creator'=>array('creator'),
'modified'=>array('modified'),
'modifier'=>array('modifier'),
),
'units'=>array(
'id'=>array('id'),
'resort_id'=>array('foreign_id','resorts','required'),
'bathrooms'=>array('posint','required'),
'bedrooms'=>array('posint','required'),
'floors'=>array('posint','required'),
'unit_descriptor_id'=>array('foreign_id','unit_descriptors'),
'number'=>array('text','required'),
'description'=>array('text'),
'created'=>array('created'),
'modified'=>array('modified'),
),
'users'=>array(
'id'=>array('id'),
'username'=>array('text','size'=>array(4,32),'required'),
'password'=>array('hex','size'=>array(32),'required'),
'logins'=>array('posint'),
'last_login'=>array('time'),
'first_login'=>array('time'),
'salt'=>array('text','size'=>array(32),'required'),
'first_name'=>array('name'),
'last_name'=>array('name'),
'super_admin'=>array('bool'),
'admin'=>array('bool'),
'email'=>array('email'),
'unit_id'=>array('foreign_id','units'),
'resort_id'=>array('foreign_id','resorts','required'),
'realtor'=>array('bool'),
'company'=>array('company'),
'phone'=>array('phone'),
'homepage'=>array('url'),
'thumb'=>array('url'),
'created'=>array('created'),
'modified'=>array('modified'),
),
));
There are things I am leaving out, so as to not confuse you with all the options (Meaning, authorization of edit/delete/inserting for each visitor, and also auto-selecting one-to-many records from other tables). Has anyone else done something similar on their projects?