OO wrapper for SQL where clause like Roguewave
Shane
delete rows in a theoretical movie reservation system one can write:
// C++ code
int videoID = 1234;
std::string lastName = "Miller";
RWDBTable purchases = myDbase.table("purchase");
RWDBDeleter deleter = purchases.deleter();
deleter.where(purchases["videoID"] == videoID && purchases["last_name"]
==lastName);
deleter.execute(myConnection);
// above does delete from purchase where videoID==123 and
last_name='Miller';
Question: are there any papers which might explain how the C++
expression:
purchases["videoID"] == videoID && purchases["last_name"]
==lastName
can be mapped to the string where videoID==123 and
last_name='Miller' ? Note that the operator == could have been != and
the C++ operator && could have been || to give:
delete from purchase where videoID!=123 or last_name='Miller';
Thank you in advance.
jason.c...@gmail.com
> Question: are there any papers which might explain how the C++
> expression:
>
> purchases["videoID"] == videoID && purchases["last_name"]
> ==lastName
>
> can be mapped to the string where videoID==123 and
> last_name='Miller' ?
What are you asking? Is the following not what you are looking for?
purchases["videoID"] == 123 && purchases["last_name"] == "Miller"
This is assuming purchases.operator[] returns an object with an ==
operator defined to take integers and char *'s.
> Note that the operator == could have been != and
> the C++ operator && could have been || to give:
>
> delete from purchase where videoID!=123 or last_name='Miller';
This isn't equivalent to anything that you've mentioned, and in any
case, probably isn't what you want. (a && b) is certainly not
equivalent to (!a || !b). It isn't the inverse of it either. The two
have very different meanings.
Can you clarify you question? Is this related to C++?
Jason
Shane
In a nutshell: are there papers discussing how to encapsulate a SQL
where clause inside an object oriented language? The above example is
how the C++ roguewave library does it. Assuming you had Table, Column,
Deleter, and Where classes what interface on those classes (and why)
could generate the std::string
where videoID==123 and last_name='Miller';
from the C++ code
deleter.where(purchases["videoID"] == videoID && purchases
["last_name"]==lastName);
jason.c...@gmail.com
Hmm, that is a good question. I've cross-posted this to comp.object
(although I wonder if comp.software-eng would have been appropriate),
perhaps somebody there has seen some research. Apologies if this is
off-topic in comp.object; it seems relevant, though.
Personally, I don't know of any such papers. There is more than one
way to do it. Take a look at SQLAPI++ for example (http://
www.sqlapi.com/) (note for comp.object -- the original post cited
RogueWave), it does things in a slightly different way.
Also take a look at technologies like LINQ:
http://msdn.microsoft.com/en-us/vcsharp/aa904594.aspx
LINQ can be used, for example, to write code that directly operates on
databases (e.g. a for loop that iterates over rows in a database, but
is part of the actual programming language).
It kind of depends on the level of mapping that you want. An API like
SQLAPI++ does not provide a wrapper around SQL, it lets you use
arbitrary SQL but provides a wrapper around the results. In a case
like that, it's up to your application to wrap database entities
specifically.
Personally, I don't particularly care for LINQ or APIs that attempt to
map queries directly; generally I hide all of that behind a layer of
abstraction anyways. I'm not comfortable with the interdependence of
code and queries that, say, LINQ encourages, although I can see its
uses.
> The above example is
> how the C++ roguewave library does it. Assuming you had Table, Column,
> Deleter, and Where classes what interface on those classes (and why)
> could generate the std::string
> where videoID==123 and last_name='Miller';
> from the C++ code
> deleter.where(purchases["videoID"] == videoID && purchases
> ["last_name"]==lastName);
I have seen APIs that do it similar to this (just an example):
deleteQuery
.from("mytable")
.constrain("videoID", videoID)
.constrain("last_name", lastName)
.execute();
In a language like C++ that allows operator overloading, you could
theoretically come up with a query builder based on operator
overloading:
Query q = Delete << (Where("videoID") == videoID && Where
("last_name") == lastName);
q.Execute();
That is somewhat ugly but I did not put much thought into the
interface there. There, "Delete" and "Where" would be some objects
that are combined with the use of operators and, when assigned to a
Query, construct a representative SQL query.
Jason
frebe
Embedded SQL products like Pro*C is another way to go.
//frebe
James Kanze
<jason.cipri...@gmail.com> wrote:
> On Jan 5, 6:11 pm, Shane <gshanemil...@verizon.net> wrote:
> > In a nutshell: are there papers discussing how to
> > encapsulate a SQL where clause inside an object oriented
> > language?
> Hmm, that is a good question. I've cross-posted this to
> comp.object (although I wonder if comp.software-eng would have
> been appropriate), perhaps somebody there has seen some
> research. Apologies if this is off-topic in comp.object; it
> seems relevant, though.
> Personally, I don't know of any such papers. There is more
> than one way to do it. Take a look at SQLAPI++ for example
> (http://www.sqlapi.com/) (note for comp.object -- the original
> post cited RogueWave), it does things in a slightly different
> way.
I don't know of any general papers either, but each
implementation should be documented. I've used OTL in the
past, and it's concept is considerably different than that of
RogueWave.
I'm not sure, however, whether the OP was asking about such
concepts, or whether he was more interested about the mechanisms
behind them, e.g. how one would go about implementing something
like the RogueWave example he posted. While the obvious key for
this is operator overloading, I'm not aware of any papers which
really explain the fundamentals of creating a new language using
it. The trick here, of course, is that RWDBTable::operator[]
is overloaded to return a special type, and all of the other
operators in the expression are overloaded on that type; an
operator== on the type doesn't do a comparison, but links in the
necessary information to generate the SQL command. And the
RWDBDeleter::where function takes a reference to the type,
follows the links, and uses the necessary information to build
the SQL command.
I'm not too sure I like the concept. I'm not really happy with
the idea that == doesn't do a comparison, and that && doesn't
short circuit; in other words, that `` purchases[ "videoID" ] ==
videoID '' can't be used as a condition, and that the second
operand of && will always be evaluated. Carefully designed,
however, it's possible to ensure that such an expression can
only be used as an argument to something like
RWDBDeleter::where, so it might not be as bad as that.
> > The above example is how the C++ roguewave library does it.
> > Assuming you had Table, Column, Deleter, and Where classes
> > what interface on those classes (and why) could generate the
> > std::string
> > where videoID==123 and last_name='Miller';
> > from the C++ code
> > deleter.where(purchases["videoID"] == videoID && purchases
> > ["last_name"]==lastName);
> I have seen APIs that do it similar to this (just an example):
> deleteQuery
> .from("mytable")
> .constrain("videoID", videoID)
> .constrain("last_name", lastName)
> .execute();
> In a language like C++ that allows operator overloading, you
> could theoretically come up with a query builder based on
> operator overloading:
That's exactly what the RogueWave interface is doing.
> Query q = Delete << (Where("videoID") == videoID && Where
> ("last_name") == lastName);
> q.Execute();
> That is somewhat ugly but I did not put much thought into the
> interface there. There, "Delete" and "Where" would be some
> objects that are combined with the use of operators and, when
> assigned to a Query, construct a representative SQL query.
The trick in the RogueWave implementation seems to be having
RWDBTable::operator[] return a special type, on which the other
operators are defined. There are several ways to do this; I'd
probably use an expression node base class, with each of the
nodes derived classes. So ``purchases[ "videoID" ]'' might
return a ColumnReferenceNode (which contains the string
"videoID", and derived from ExpressionNode), there will be an
IntConstantNode with a converting constructor from int, and an
operator==( ExpressionNode const&, ExpressionNode const& ) which
returns an IsEqualNode containing pointers to the two nodes.
The RWDBDeleter::where function would take an ExpressionNode,
and "execute" it to generate the string.
The same thing could also be done with expression templates. I
find the virtual function version easier to understand, however.
--
James Kanze (GABI Software) email:james...@gmail.com
Conseils en informatique orientée objet/
Beratung in objektorientierter Datenverarbeitung
9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34
topmind
May I ask why you'd want to wrap most of your SQL? I agree there are
some places where it makes sense for at least parts of SQL, such as
repetitious UPDATE and INSERT column lists, but it can also hinder the
readability of the queries, setting us back to the assembler language
days.
For a math analogy, why have this:
adder = new Adder(new FloatManager())
multiplier = new Multiplier(new FloatManager())
temp = new Float()
result = new Float()
temp.setValue(multipler.multiply(C, D))
result.setValue(adder.add(B, temp))
When you can have this:
A = B + C * D
?
Some of the wrapper proponents are overly dogmatic in my opinion.
-T-
James Kanze
[...]
> May I ask why you'd want to wrap most of your SQL?
Static type checking and parsing. More or less, but the Rogue
Wave solution will give you an error at compile time for many
errors; building the SQL request up as a string won't.
frebe
> On Jan 6, 8:16 pm, topmind <topm...@technologist.com> wrote:
> [...]
>
> > May I ask why you'd want to wrap most of your SQL?
>
> Static type checking and parsing. More or less, but the Rogue
> Wave solution will give you an error at compile time for many
> errors; building the SQL request up as a string won't.
>
> --
> Conseils en informatique orientée objet/
> Beratung in objektorientierter Datenverarbeitung
> 9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34
Embedded SQL products like Pro*C solves the same problem. Using stored
procedures would also solve the problem.
//frebe
Peter Morris
I'm not too sure I like the concept. I'm not really happy with
the idea that == doesn't do a comparison, and that && doesn't
short circuit; in other words, that `` purchases[ "videoID" ] ==
videoID '' can't be used as a condition, and that the second
operand of && will always be evaluated.
<<
In what context? I am unaware of any circumstances where this is the case
(that I can think of at the moment at least.)
--
Pete
====
http://mrpmorris.blogspot.com
http://www.capableobjects.com - Think domain, not database
Roland Pibinger
>The trick in the RogueWave implementation seems to be having
>RWDBTable::operator[] return a special type, on which the other
>operators are defined.
It's a RWDBColumn:
http://www2.roguewave.com/support/docs/hppdocs/dbtref/rwdbtable.html#idx1562
See also the docs:
http://www2.roguewave.com/support/docs/hppdocs/dbtug/booktoc.html
DBTools.h uses a special variant of 'reference semantics': "With rare
exceptions, DBTools.h++ classes obey reference semantics. Copy
constructors and assignment operators result in interface objects that
share a common implementation."
http://www2.roguewave.com/support/docs/hppdocs/dbtug/2-4.html
--
Roland Pibinger
"The best software is simple, elegant, and full of drama" - Grady Booch
James Kanze
> > I'm not too sure I like the concept. I'm not really happy with
> > the idea that == doesn't do a comparison, and that && doesn't
> > short circuit; in other words, that `` purchases[ "videoID" ] ==
> > videoID '' can't be used as a condition, and that the second
> > operand of && will always be evaluated.
> In what context? I am unaware of any circumstances where this
> is the case (that I can think of at the moment at least.)
In what context what? In the example code we were looking at
(using the RogueWave SQL library), == didn't do a comparison,
and && didn't short circuit. In general, I would classify that
as operator overload abuse. (In this case, the criticism is
somewhat mitigated because these operators are only used in a
very special context.)
bors...@roguewave.com
> On Jan 8, 11:04 am, "Peter Morris" <mrpmorri...@SPAMgmail.com> wrote:
>
> > > I'm not too sure I like the concept. I'm not really happy with
> > > the idea that == doesn't do a comparison, and that && doesn't
> > > short circuit; in other words, that `` purchases[ "videoID" ] ==
> > > videoID '' can't be used as a condition, and that the second
> > > operand of && will always be evaluated.
> > In what context? I am unaware of any circumstances where this
> > is the case (that I can think of at the moment at least.)
>
> In what context what? In the example code we were looking at
> (using the RogueWave SQL library), == didn't do a comparison,
> and && didn't short circuit. In general, I would classify that
> as operator overload abuse. (In this case, the criticism is
> somewhat mitigated because these operators are only used in a
> very special context.)
>
> --
> Conseils en informatique orientée objet/
> Beratung in objektorientierter Datenverarbeitung
> 9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34
Yes, I can see your point about operator overload abuse although I
disagree that it is a problem.
To continue with Rogue Wave's tutorial code snippet, this code:
purchases["videoID"] == videoID && purchases["last_name"] == lastName
is being used as an argument to the method RWDBDeleter::where which
takes an RWDBExpr. So, the operators are defined on RWDBExpr. RWDBExpr
is used to build an SQL expression to be sent to a database. The SQL
statement produced would be:
purchases.videoID == videoID && purchases.last_name == lastName
What kind of an alternative would you suggest that would be intuitive
and easy to use?
James Kanze
> On Jan 9, 1:35 am, James Kanze <james.ka...@gmail.com> wrote:
> > On Jan 8, 11:04 am, "Peter Morris" <mrpmorri...@SPAMgmail.com> wrote:
> > > > I'm not too sure I like the concept. I'm not really
> > > > happy with the idea that == doesn't do a comparison, and
> > > > that && doesn't short circuit; in other words, that ``
> > > > purchases[ "videoID" ] == videoID '' can't be used as a
> > > > condition, and that the second operand of && will always
> > > > be evaluated.
> > > In what context? I am unaware of any circumstances where
> > > this is the case (that I can think of at the moment at
> > > least.)
> > In what context what? In the example code we were looking
> > at (using the RogueWave SQL library), == didn't do a
> > comparison, and && didn't short circuit. In general, I
> > would classify that as operator overload abuse. (In this
> > case, the criticism is somewhat mitigated because these
> > operators are only used in a very special context.)
> Yes, I can see your point about operator overload abuse
> although I disagree that it is a problem.
As I said, the criticism is mitigated because (if?) the
operators can only be used in this very special context.
What you're doing, fundamentally, is defining a new language,
which isn't C++ (but is modeled very closely on SQL). I'm
somewhat sceptical of mixing two languages in the same source
file. Of course, we already do it with C style macros and C++
template meta-programming, but both are reknown for resulting in
unreadable code, so I'm not sure they're good models to follow.
The important aspect here is to be able to distinguish which
language is "active" at any given time. If the operators are
designed so that it is impossible to use them outside of certain
clearly marked contexts, it's less of a problem. But the
solution still isn't perfect.
> To continue with Rogue Wave's tutorial code snippet, this
> code:
> purchases["videoID"] == videoID && purchases["last_name"] == lastName
> is being used as an argument to the method RWDBDeleter::where
> which takes an RWDBExpr.
And what happens if someone uses it in an if?
> So, the operators are defined on RWDBExpr. RWDBExpr is used to
> build an SQL expression to be sent to a database. The SQL
> statement produced would be:
> purchases.videoID == videoID && purchases.last_name == lastName
You mean "purchases.videaID = videoId and purchases.last_name =
last_name", I suppose. The above is C++.
> What kind of an alternative would you suggest that would be
> intuitive and easy to use?
The most intuitive and easiest to use is just to have the user
provide a string. Of course, if you do this, you loose
compile-time checking. There are a number of tradeoffs
involved; in my experience, there's not too much problem
differing the checking until runtime, and using a string. But I
don't have that much experience with data bases, and most of my
requests are fairly simple. (Almost all of my use of data bases
from C++ is for persistence, which generally means that I'm
never doing anything very complicated.)
--
James Kanze (GABI Software) email:james...@gmail.com