Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Module Design Review -- DB mini language

by elusion (Curate)
on Feb 10, 2003 at 20:30 UTC ( #234231=perlmeditation: print w/replies, xml ) Need Help??

In an attempt to rid my code of SQL, which I find generally distasteful, I've designed a mini language (but have not implemented it yet) for accessing databases. I've submitted it here for review. Also, I'm not sure about the name, so I'd like some thoughts on that as well.

elusion : http://matt.diephouse.com

Update: I apologize for the huge font. A runaway h1 tag was to blame. My browser (safari) did not render it, so I didn't know.




NAME

DBIx::DAL -- Database Abstraction Language


use DBIx::DAL; my $f = 3; dal->connect("mysql:dbname:localhost", "user", "pass"); my $id = query "foo[blah = 1 | 2, baz = ?](id)", $f; dal->disconnect;

ABSTRACT

Let's face it: SQL is ugly, especially when a query uses data from multiple tables. DBIx::DAL is an attempt to solve this problem. This module implements a mini language that is converted to SQL. All SQL is tailored to MySQL, so it may or may not work with other databases. There is no support for database creation, only for accessing, storing, and changing data.


DESCRIPTION

LANGUAGE

To demonstrate how DAL works, we will use a table of colors that have an id and a rating (1-10). All queries will be done from this table.

+----Table: colors-----+ | id | name | rating | +----+--------+--------+ | 1 | blue | 9 | | 2 | green | 10 | | 3 | red | 7 | | 4 | orange | 10 | | 5 | brown | 3 | +----+--------+--------+

First off you want to want to select the rating of the color green. That would be done using this command:

my $color = "green"; my ($ans) = query "colors[name = ?](rating)", $color; print "Rating: ", $ans->[0], "\n"; # prints "Rating: 10\n"

As you can see, the table name comes first, followed by the where clause and, finally, what it is that you want to select. We used a binding for the value of the color (the ?) that allows us to pass the variable we want to use in order to make sure it's quoted right. To select all the columns from the table, don't specify any: table[]().

All results from the query are returned, as an array of anonymous arrays, which correspond to the different rows returned.

If you want to alter the data in the table, the command is very similar. Someone has decided that they don't like green so much, so we'll change its rating to 8.

my ($color, $new_rating) = ("green", 8); query "colors[name = ?](rating) = ?", $color, $new_rating;

That has the same order: table name, where clause, and column selection. In fact it is identical to the above command, except that here we do an assignment, using =. We even use a binding for the value, as we did before.

Statements in the where clause are seperated by commas, which mean 'and'. If you want an 'or', use the keyword or. Junctions, like those coming in perl 6, are also supported -- 'or' as | and 'and' as &.

It's important to note here that we can nest calls. In other words, say we have a table of items which each have a color.

+-----Table: items------+ | id | name | color_id | +----+-------+----------+ | 1 | sky | 1 | | 2 | brick | 3 | | 3 | tree | 5 | | 4 | water | 1 | +----+-------+----------+

Now we decide we want to know the rating of the color of the sky. Instead of making the query much more complex by using identifiers for each table name, we do this:

my ($item) = ("sky"); my ($ans) = query "colors[id = items[name = ?](color_id)](rating)", +$item; print "Rating: ", $ans->[0], "\n"; # prints "Rating: 9\n"

Now someone wants to add a leaf to the items table.

my ($item, $color) = ("leaf", "green"); query "items(name, color_id) = ?, colors[name = ?](id)";

To insert items, leave out a where clause and do an assignment, similar to an alter. DAL will automatically seperate the values into the different rows (if you specify a column, you must pass a value for it, even if that value is ''). Optionally, you can surround the different rows with square brackets.

METHODS

connect('type:database:host', username, password)
Opens a connection to the specified database with the given username and password. Call using dal.

dal
This method returns 'DBIx::DAL'. This is done so that methods like connect and disconnect don't need to be imported. Instead, you can call them using dal. dal->connect

disconnect
Closes the connection to the current database. Call using dal.

query('query string' [, vars])
This is the query interface. Pass variables along with it for any bindings in the query string.

The return value is a list of all rows that match the query. The row is represented as an anonymous array that contains the values for the columns that were requested.


AUTHOR

Matt Diephouse <matt@diephouse.com>

Replies are listed 'Best First'.
Re: Module Design Review -- DB mini language
by l2kashe (Deacon) on Feb 10, 2003 at 21:20 UTC
    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]
    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";
    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 */
      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

        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.

        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

Re: Module Design Review -- DB mini language
by BazB (Priest) on Feb 10, 2003 at 21:21 UTC

    I personally can't quite see why this is any better than SQL.

    Call me crazy, but I rather like SQL - it's pretty clear given enough whitespace.
    Aliases make selecting from multiple tables and/or subselects tidy too.

    Vanilla SQL* is going to be more portable than DAL - learn SQL and you can use it from Perl, PHP, $fave_language, whereas DAL is only being to be useful with your module to hand.

    You're not providing anything clever (i.e. database independent SQL).

    It'd be really cool if DAL spat out SQL that attempted to do The Right Thing, depending on the database it's talking too.

    Interesting idea, but I can't see any practical use for it.

    Cheers.

    BazB

    * OK, I know that Oracle, MySQL, PostGreSQL etc all have their own specific SQL extensions/mutations.

    Update:
    When I use SQL, I tend to go totally overkill on the whitespace.
    Taking your example SQL, I have to admit the way you've formatted it is pretty nasty.

    I'd lay it out as follows:

    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;

    I'd possibly group the where clauses together by table too, if it made it easier to figure out which tables/columns where being used without having to scan up and down the list.

    You'd line everything up in Perl, why not do it in SQL?


    If the information in this post is inaccurate, or just plain wrong, don't just downvote - please post explaining what's wrong.
    That way everyone learns.

      I agree with you on the whitespace, but I think that, while it may increase typing, spelling out your joins is much cleaner and easier to read (if the db that you are using support it).
      SELECT words.id, words.definition FROM stock JOIN languages ON (stock.languageID = languages.id) JOIN stockmatch ON (stockmatch.stockID = stocks.id) JOIN words ON (stockmatch.wordID = words.word_id) JOIN types ON (words.typeID = types.id) WHERE stock.word = :stock_word AND language.name = :language_name AND type.name = :type_name
      And if you are joining two tables on a column that has the same name in both tables you can:
      SELECT * FROM table_one JOIN table_two USING (join_column)
Re: Module Design Review -- DB mini language
by Elgon (Curate) on Feb 11, 2003 at 01:30 UTC

    Okay Matt, we've had the replies from people as to why you shouldn't reinvent this particular wheel. I'm not going to give you that kind of answer as lots of pople have already done that and generally more eloquently than I could. What I will do is ask you why you want to do this and perhaps give you a reason why you should consider implementing your idea. If I get flamed for this, as they say in the Phillipines - Bahala na.

    Q & A 1 - Why do you really want to do this? This may seem like a dumb question, but it isn't (I hope.) Is it because you think that SQL is a bit ugly, you can make a microlanguage which will provide a limited subset of functionality more efficiently or because you don't really grok SQL? Answer yourself honestly. If it is the last of these then you are guilty of false modesty, which is a Bad Thing (TM) because you have failed to take the time to nuderstand (Update, Def: Nuderstand, like 'understand' but much more fun) something which could save you hours of work on a potentially pointless exercise. If it is the first, then remember that Perl often appears externally ugly, however you learned to look at the beauty and efficiency it can provide when used correctly in the right situations (let us not forget that Perl is not always the Right Choice (also TM.)) If it is the middle one, then go ahead - prove the doubters wrong.

    Q & A 2 - Why should you do this? Well, apart from the reason highlighted above, it might be a good technical exercise. My two most recent additions to PM have both been just that - something that I will probably rarely, or never, use but from which I have learned something (Kudos to Tachyon re: help on scoping.)What could you learn from this? Shedloads of stuff: API and language design, coding bigger projects and the judgement of abstractions, possibly even a newfound sense of wellbeing and respect for the designers of SQL - you never know.

    My son, the only question which yet remains, is one you must answer for yourself: Is this a valuable and potentially enlightening use of your time?

    Elgon

    "What this book tells me is that goose-stepping morons, such as yourself, should read books instead of burning them."
           - Dr. Jones Snr, Indiana Jones and the Last Crusade

Re: Module Design Review -- DB mini language
by rdfield (Priest) on Feb 11, 2003 at 11:29 UTC
    And when your database gets beyond being toy-sized, how are you going to get the performance tuning done?

    For instance, you notice that a particular query takes an inordinate amount of time, how are you going to tell which of your queries generated the SQL? Easy is a single user, single page script, but what about a 45000 line application spread across 80+ CGI scripts, a dozen or so mod_perl handlers, 20+ modules, a clustered Apache enviroment and a SOAP server? (Did I mention that locking/contention issues are hard enough to find and fix without another layer of abstraction?)

    Interesting exercise, but remember that SQL has its foundations in relational theory and as such is a very solid base upon which to build.

    rdfield

      I have to agree here. The code I work on is very SQL heavy. We use Oracle for most of it. I've seen poorly written Oracle queries that take hours to run that are pared down to minutes or seconds when the SQL is rewritten.

      This is one of the big problems I have with most SQL abstractions: They don't allow you to tune the SQL. We typically hand off our poorly performing SQL to DBA's who are well versed in this area. Even if you could hand them off the resulting SQL here, how would you roll their changes back into the abstraction?

      One approach to how to handle SQL across multiple DB's and allow for tuning is the one OpenInteract uses. They provide a place to "hang" each piece of SQL that allow for per database tuning.

      SQL, unfortunately, is neither standard or predictable across different databases. I get the same angst when it's suggested we move from database A to database B. The people asking for that generally are not aware of how much work is involved rewriting the SQL when such a move is made.

      I think abstractions are always worth thinking about and this particular one may address your needs, so I don't think it's necessarily a wasted effort.

Re: Module Design Review -- DB mini language
by perrin (Chancellor) on Feb 10, 2003 at 22:40 UTC
    I agree with the others about preferring SQL, but if you're looking for alternatives you might find something appealing in Alzabo or one of the DBIx query building tools.
Re: Module Design Review -- DB mini language
by adrianh (Chancellor) on Feb 10, 2003 at 23:08 UTC

    I'm afraid it makes my head hurt considerably more than the raw SQL :-) Maybe showing some side-by-side comparisons between SQL & DAL might make you point a bit better, but I'm not convinced personally.

    It also doesn't seem to allow for sub-selects, grouping, unions, etc. - all things I can't cope without.

Re: Module Design Review -- DB mini language
by extremely (Priest) on Feb 11, 2003 at 05:25 UTC
    You should pop over to perl.com and read the article about Class::DBI. It might just be what you are looking for...

    --
    $you = new YOU;
    honk() if $you->love(perl)

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2019-07-16 06:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?