NYCPHP Meetup

[Mark Armendariz]

I'm a huge fan of SQL, and I've been using it for over 10 years.  its a 
solid and reliable friend.  But it seems to be far too wordy and gets 
hairy to maintain, which is why we tend to look for ways to modularize 
it within our programming languages.  When using SQL, we're just working 
with strings.  mysql_query('SELECT * FROM customers') is as painful as 
using innerHTML in javascript.  In some instances, you just have to, but 
it 'feels right' to use the DOM, and the DOM allows so much more power 
from a javascript perspective.

This might seem completely ridiculous and tear the idea to shreds if you 
must, but I've been working on a library with it's own query language 
that creates both the SQL and the PHP Objects to reference everything 
returned.  Though I've been working on it for quite some time, it's 
still too early (read: messy) to offer it to the masses (I'm 
successfully using it in 4 of my current small to medium sized projects). 

Over the past 10 years, every time I try to keep all my queries in one 
place, whether it be with SQL or with some objects representing my 
Database Tables, i end up with a bunch of functions named getCustomers, 
getCustomer($id), getCustomersInNy, getCustomersInTristate, so instead I 
made a language that allows something like:

get customers
means
SELECT * FROM customers

get customers 425
means
SELECT * FROM customers WHERE customer_id = 425

get customer last_name d, first_name d, full_address where customer_id = 1-5
means
SELECT c.first_name, c.last_name, a.address_1, a.address_2, a.city, 
a.province, co.country_title FROM customers c LEFT JOIN addresses a on 
c.customer_id = a.customer_id LEFT JOIN c.countries ON a.country_id = 
c.country_id WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 3 
OR customer_id = 4 OR customer_id = 5 ORDER BY c.last_name DESC 
c.first_name DESC

and
find mark in customers, addresses, countries
means
SELECT * FROM customers c LEFT JOIN addresses a on c.customer_id = 
a.customer_id FROM customers c LEFT JOIN addresses a on c.customer_id = 
a.customer_id LEFT JOIN c.countries ON a.country_id = c.country_id WHERE 
c.first_name LIKE '%mark%' OR c.last_name LIKE '%mark%' OR c.username 
LIKE '%mark%' OR a.address_1 LIKE '%mark%' OR a.address_2 LIKE '%mark%' 
OR a.city LIKE '%mark%' OR a.province LIKE '%mark%'
OR co.country_title LIKE '%mark%'
(all the string fields in all pertaining tables)

All the joins are dynamically generated, and those generations are 
cached, so it only needs to figure out the joins once.  All the queries 
are dynamically generated as well (obviously) and cached, but if a new 
field is added, the cache is cleared and all those selects are re-cached 
with the new fields (or removed fields removed from queries)

The return can be an array of results, an iterator or an object with 
access to the selected tables and fields (with values set and more 
detailed properties for formatting and manipulation).  The returned 
array can have the data formatted as well.

I haven't done the inserts and updates language yet, but all the fields 
have types (url, email, USPhone, textile, textileComment, alpha, 
alphaWithPunctuation, Integer, DateTime etc), so all the proper 
filtering and escaping is done automatically as is.  Even though the 
language doesn't have inserts and updates, I've been using the type 
objects for proper field handling and filtering (rather than 
add-slashing everything)

Anyways, to me this seems to be the way to go.  If we're dealing with 
strings anyways, we might as well keep those strings short and sweet and 
most importantly dynamic.  It should be portable (any database or any 
model, really) and simple.

The biggest argument I tend to hear is that restricting fields in php is 
silly, but I largely disagree.  By filtering inputted data, we're 
already typing these fields, and usually incorrectly.  Strict typing and 
a dynamic language fit together well as you are allowed the choice of 
either.

Ideally I'd be able to get this into an extension at some point, but I'm 
still getting things organized.  I'm currently in the process of 
rewriting the type classes to make the more modular / portable (if 
anyone has experience in the realm of creating a type system, please 
give me a shout off-list)

Anyways, that's my take on SQL and ORM.  Both - with an easily 
manipulated and portable meta language.

Mark Armendariz