in reply to Module Design Review -- DB mini language

I really am attempting to not be rude here, so please take this as strictly opinion with no bearing on you personally.

I didn't down vote you, but the syntax here simply makes my head hurt. Reinventing this particular wheel seems simply painful.

I personnally find SQL to be straight forward, and well defined. The functions in SQL are well named, and mnemonically resemble what they do. I would be more interested in what you were doing with SQL which lead to the feeling that SQL is ugly, and whether you were using the right tool for your particular job.

I mean if you need data from multiple tables is as simple as 
SELECT [LIST] from db1.table, db2.table WHERE [ARGS]

[download]
Simple, straight forward. The syntax of your module smacks me as "busy", there is alot going on even in a simple query. to pull from your example, to get the rating for green we could do
SELECT rating FROM colors WHERE name = "green";

[download]
So again, I guess I would like to know the motivation behind this move. Why attempt to fix something that isn't broken? Why add yet another layer on top of an already pretty abstracted layer?

/* And the Creator, against his better judgement, wrote man.c */

Replies are listed 'Best First'.
Re: Re: Module Design Review -- DB mini language
by elusion (Curate) on Feb 10, 2003 at 21:44 UTC
    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;

    [download]
    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)

    [download]

    elusion : http://matt.diephouse.com

[reply]
[d/l]
[select]
      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 = ?

      [download]

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

[reply]
[d/l]

      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 = ?

      [download]
      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
+ = ?

      [download]
      (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

[reply]
[d/l]
[select]

In Section Meditations