Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

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

by elusion (Curate)
on Feb 10, 2003 at 21:44 UTC ( #234249=note: print w/replies, xml ) Need Help??


in reply to Re: Module Design Review -- DB mini language
in thread Module Design Review -- DB mini language

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

Replies are listed 'Best First'.
Re: Re: Re: Module Design Review -- DB mini language
by zengargoyle (Deacon) on Feb 10, 2003 at 22:35 UTC
    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.

Re: Re: Re: Module Design Review -- DB mini language
by Jenda (Abbot) on Feb 11, 2003 at 00:40 UTC

    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://234249]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2019-07-18 10:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?