Re: SQL Readability.. (was Re: most powerful php editor)

Larry Garfield wrote: > On Saturday 27 January 2007 7:43 am, Jochem Maas wrote: >> Larry Garfield wrote: >>> I have long since given up on raw insert/update/delete statements as the >>> syntax is all kinds nasty. These days I just do this, which is even >>> easier and more powerful: >>> >>> http://www.garfieldtech.com/blog/simplifying-sql >> a quick look at those funcs gives me the impression that they are woefully >> inadequate for any level of complex realworld use. > > That's interesting, because I've been using variants of that for a year now > with much success in a dozen projects. I was nitpicking - I'm quite sure they are useful within the bounds of the intended scope and wielded by a pair of hands that knows the code intimately (including any limitations). I run plenty of stuff that falls in the same category :-) > >> query builders are alot more fiddly to get 'right' than one might imagine, >> dealing with NULLs, booleans and dates for example (as Satyam pointed out) >> can be a right PITA. > > I actually almost never use native date types in the SQL database. I just > store unix timestamps and do the math in PHP. Dates are completely > unportable anyway. I also tend to use ints for booleans, too, although > beefing up the switch statements in the code to handle native booleans should > be trivial. mysql doesn't have booleans does it? at least not versions I have to use. with regard to date stuff, many people take the opposite approach and do most of the date math inside SQL - most DBs have kickass date calculation functions btw. and for the times when you need/want unix timestamps, mysql atleast, gives you UNIX_TIMSTAMP(). (just some loose thoughts) > >> perfect automated CRUD (it's an acronym!) is kind a holy grail - and >> that is, I think, the driving force behind most attempts to crteate query >> builders. > > Orthogonal persistence is, yes. The goal here was simply to make dealing with > arbitrary insert and update statements easier, which in practice I've found > to be a huge success. Full arbitrary CRUD and orthogonal persistence is much > harder. That's why there's a dozen ORMs out there, all of which have some > major flaw. :-) including mine :-) (not released because it, well, needs a big manual that only exists in my head - besides is firebird/ibase specific and I'm one of about 5 people who actually use php+firebird :-) > >> also I don't really agree with the sentiment that SQL syntax is nasty, >> personally I find it, mostly, very easy to read and powerful ... but as >> this thread shows there is no accounting for taste! :-) > > What bugs me most about SQL syntax is INSERT vs. UPDATE. I don't know the > underlying implementation details of the engine, but from the level I work at > (sending SQL to a database from a web app) I see no legitimate reason why > those two very-similar statements should have ridiculously different syntax. granted it's not perfect, somebody made a design 'fault' way back when and we're stuck with it. maybe someone else has some real info about why this is so. >