OO wrapper for SQL where clause like Roguewave

[Shane]

Roguewave makes a well known C++ wrapper for SQL. For example to
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.cipriani]

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]

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

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.cipriani]

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

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.

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]

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

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.

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

Embedded SQL products like Pro*C is another way to go.

//frebe

 

[James Kanze]

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:perator[]
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:perator[] 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.

 

[topmind]

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

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.

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

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]

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.

--
James Kanze (GABI Software)             email:[email protected]
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.)

 

[Roland Pibinger]

The trick in the RogueWave implementation seems to be having
RWDBTable:perator[] 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

 

[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.)

 

[borschow]

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.)

--
James Kanze (GABI Software)             email:[email protected]
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]

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.)