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.
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;
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.
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.
- 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.
Matt Diephouse <matt@diephouse.com> |
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 */ | [reply]
[d/l]
[select] |
|
|
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 | [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 = ?
you just need some whitespace in your SQL =P that and sticking to plural/non-plural for your table names. | [reply]
[d/l] |
|
|
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 | [reply]
[d/l]
[select] |
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.
| [reply]
[d/l]
[select] |
|
|
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)
| [reply]
[d/l]
[select] |
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
| [reply] |
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 | [reply] |
|
|
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.
| [reply] |
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. | [reply] |
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.
| [reply] |
Re: Module Design Review -- DB mini language
by extremely (Priest) on Feb 11, 2003 at 05:25 UTC
|
| [reply] |
|
|
