Re: Re: Module Design Review -- DB mini language

[elusion]

Apparently everyone else likes SQL. *shrug*

My dislike comes when I'm using several tables. Take this as an example from an NLP (Natural Language Processing) application I'm working on.

SELECT w.id, w.definition
FROM words w, stock s, languages l, stockmatch m, types t
WHERE s.word = ? AND l.name = ? AND s.languageID = l.id AND
m.stockID = s.id AND w.id = m.wordID AND t.name = ? AND
w.typeID = t.id;

That's horribly complex and I have to look at it a pretty long time before I know what's going on. I find this easier to understand because I can tell what I'm extracting and why I care if stuff matches.

words[
    id = stockmatch[
        stockID = stock[
            word = ?,
            languageID = languages[name = ?](id)
        ](id)
    ](wordID),
    typeID = types[name = ?](id)
](id, definition)

elusion : http://matt.diephouse.com

[zengargoyle]

SELECT
  words.id, words.definition
FROM
  words, stock, languages, stockmatch, types

WHERE
  words.typeID = types.id
AND
  stock.languageID = languages.id
AND
  stockmatch.stockID = stock.id
AND
  stockmatch.wordID = words.id

AND stock.word = ?
AND languages.name = ?
AND types.name = ?

you just need some whitespace in your SQL =P that and sticking to plural/non-plural for your table names.

[Jenda]

Maybe if you were not so eager to shorten the code and forgot you can "rename" a table for the query, and if you used JOIN:

SELECT words.id, words.definition
  FROM words 
  JOIN types ON words.typeID = types.id
  JOIN stockmatch ON words.id = stockmatch.wordID
  JOIN stock ON stockmatch.stockID = stock.id 
  JOIN languages ON stock.languageID = languages.id
WHERE stock.word = ? 
  AND languages.name = ? 
  AND types.name = ?

or

SELECT words.id, words.definition
  FROM words 
  JOIN types ON words.typeID = types.id AND types.name = ?
  JOIN stockmatch ON words.id = stockmatch.wordID
  JOIN stock ON stockmatch.stockID = stock.id AND stock.word = ?
  JOIN languages ON stock.languageID = languages.id AND languages.name
+ = ?

(Now, maybe I'm making a fool of myself. I've worked with Oracle and MS SQL, never with mysql, so I don't know it it allows this syntax.)

On the other hand ... once one would get used to your "language" he might be able to put the query together quicker than in SQL. (Even if only thanks to the fact it's fewer characters). But what if I wanted to use some "advanced" feature of the SQL server? Any Column in (SELECT Column FROM Table WHERE ...) or  WHERE exists (SELECT * FROM Table WHERE ...) or ...

If I stay with SQL then it's much easier to start using those.

Also (again possibly nonsense, does mysql allow stored procedures?) it's generaly considered better to put especialy the complex queries into stored procedures. That way you do not force the server to recompile the query and regenerate the execution plan every time. Yes $dbh->prepare() helps, but still isn't perfect. Each process has to submit the query for compilation at least once.

Jenda