
I know it's been a while since we last discussed a possible rewrite of
the C++ API but I now have some time to devote to it.

The following are my ideas for implementing the C++ API:

I need suggestions, additions, comments etc!

All classes will be defined in postgres namespace.

The desired usage would be as follows

//create a database object
postgres::db = database("host = somewhere.com user=someone
password=something dbname=mydb");

//synchronous connection returns TRUE upon success FALSE upon error
if( db.connect() ) {
  string sql("SELECT * FROM foo");
  postgres::result res = db.exec(sql);

  if( !res.error() ) {
    int numrows = res.numrows(); //used to obtain number of rows returned
                                //by previous sql statement
    int numcols = res.numcols(); //used to obtain number of columns
                                //returned by previous sql statement
    int field1;
    char field2[size],field3[size];
    long field4;


//results can be obtained within a for loop using numrows, numcols or as
//below
    while( res.getrow() ) {  //increment row

//result object has insert operator and array operator overloaded
      res >> field1 >> field2;  //result object will return datatypes not
                                //just chars
      field3 = res[0];
      field4 = res["fieldname"];
      // .. do something with values ..
    }

  }
  else {
    cerr << res.display_error();
  }

}
else {
  cerr << db.display_error();
}


Alternatively one could access db asynchronously

//create a database object
postgres::db = database("host = somewhere.com user=someone
password=something dbname=mydb");

db.setasync();  //set asyncrhonous conection with back-end
		//setsync does the opposite
while( !db.connect() && !db.error() ) {

  //..do something

}
if( db.error() ) {
  cerr << db.display_error();
  exit(1);
}

string sql("SELECT * FROM foo");
postgres::result res = db.exec(sql);

while( !res.ready() && !res.error() ) {

  //..do something

}


One could also set exceptions with

//create a database object
postgres::db = database("host = somewhere.com user=someone
password=something dbname=mydb");

db.setexception();

try {

  db.connect();
  string sql("SELECT * FROM foo");
  postgres::result res = db.exec(sql);

}
catch( postgres::error& err ) {

  //..do something
  cerr << err.display_error();
}

The above examples make use of embedded sql being passed to the db object
via a string object. ( exec will be overloaded to accept a const char * as well).
I also envision a higher level which might prove usefull.

//create a database object
postgres::db = database("host = somewhere.com user=someone
password=something dbname=mydb");

postgres::table mytable = db.gettable("tablename");
//table can now be queried about characteristics of table
uint64_t numcols = mytable.numcols();  //need to find the max values and return an appropriate type
uint64_t numrows = mytable.numrows();
size_t colsize = mytable.colsize("column");

//obtain an inserter

postgres::inserter myinsert mytable.getinsert();
inserter.setcolumn("colname");

ifstream infile;
infile.open("myfile");
char data[32];

while (infile.getline(line,sizeof(data),'\t')) {
      inserter << data;
}

the above can be extended to include update and delete functions as well

postgres::updater myupdate mytable.getupdate();
myupdate.setcolumn("colname");
myupdate.setcond("WHERE something = something");

ifstream infile;
infile.open("myfile");
char data[32];

while (infile.getline(line,sizeof(data),'\t')) {
      myupdate << data;
}



Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle



Wed, 6 Dec 2000 17:09:31 -0500 (EST)
Randy Jonasz <rjonasz@click2net.com>
RFC C++ Interface
On Wed, Dec 06, 2000 at 05:09:31PM -0500, Randy Jonasz wrote:
> 
> I know it's been a while since we last discussed a possible rewrite of
> the C++ API but I now have some time to devote to it.
> 
> The following are my ideas for implementing the C++ API:
> 
> I need suggestions, additions, comments etc!

It would be helpful if the interface elements were to satisfy the STL 
requirements on iterators and collections.  Those specify a minimum 
interface, which may be extended as needed.

The one caveat is, don't try to "shoehorn" any semantics into the 
interface; anything that doesn't fit precisely should be treated 
as an extension instead, and the corresponding standard interface 
left unimplemented.

Nathan Myers
ncm@zembu.com


Wed, 6 Dec 2000 17:11:41 -0800
ncm@zembu.com (Nathan Myers)
Re: RFC C++ Interface
Randy Jonasz writes:

> The following are my ideas for implementing the C++ API:

My feeling is that if you want to create a new API, don't.  Instead
immitate an existing one.  For you ambitions you could either take the C++
API of another RDBMS product, try to shoehorn the SQL CLI onto C++, write
a C++ version of JDBC, or something of that nature.  Designing a complete
and good API from scratch is a really painful job when done well, and
given that the market for C++ combined with PostgreSQL traditionally
hasn't exactly been huge you need all the head starts you can get.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Thu, 7 Dec 2000 17:15:07 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: RFC C++ Interface
I appreciate your comments and would like to respond to your concerns.
The API I sketched in my earlier e-mail is borrowed heavily from
Rogue Wave's dbtools.h++ library.  I think it can be a very clean and
elegant way of accessing a database.

I realize the job is not a small one nor will it be easy to implement
efficiently but I am excited about taking on the responsibility and I have
the support of my employer to proceed with the project.

In comparison with the current C++ API, I think a more object oriented
approach might encourage the use of C++ with postgreSQL for software
solutions.

Having said all of this, can I count on your support to proceed?

Cheers,

Randy


Having said all of this, can I count on support

On Thu, 7 Dec 2000, Peter Eisentraut wrote:

> Randy Jonasz writes:
>
> > The following are my ideas for implementing the C++ API:
>
> My feeling is that if you want to create a new API, don't.  Instead
> immitate an existing one.  For you ambitions you could either take the C++
> API of another RDBMS product, try to shoehorn the SQL CLI onto C++, write
> a C++ version of JDBC, or something of that nature.  Designing a complete
> and good API from scratch is a really painful job when done well, and
> given that the market for C++ combined with PostgreSQL traditionally
> hasn't exactly been huge you need all the head starts you can get.
>
> --
> Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/
>
>
>

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle


Thu, 7 Dec 2000 12:44:33 -0500 (EST)
Randy Jonasz <rjonasz@click2net.com>
Re: RFC C++ Interface
Thanks for responding.  I will definitely kepp your comments in mind.

Cheers,

Randy


On Wed, 6 Dec 2000, Nathan Myers wrote:

> On Wed, Dec 06, 2000 at 05:09:31PM -0500, Randy Jonasz wrote:
> >
> > I know it's been a while since we last discussed a possible rewrite of
> > the C++ API but I now have some time to devote to it.
> >
> > The following are my ideas for implementing the C++ API:
> >
> > I need suggestions, additions, comments etc!
>
> It would be helpful if the interface elements were to satisfy the STL
> requirements on iterators and collections.  Those specify a minimum
> interface, which may be extended as needed.
>
> The one caveat is, don't try to "shoehorn" any semantics into the
> interface; anything that doesn't fit precisely should be treated
> as an extension instead, and the corresponding standard interface
> left unimplemented.
>
> Nathan Myers
> ncm@zembu.com
>
>
>

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle


Thu, 7 Dec 2000 12:46:11 -0500 (EST)
Randy Jonasz <rjonasz@click2net.com>
Re: RFC C++ Interface
> I appreciate your comments and would like to respond to your concerns.
> The API I sketched in my earlier e-mail is borrowed heavily from
> Rogue Wave's dbtools.h++ library.  I think it can be a very clean and
> elegant way of accessing a database.

Rogue Wave's API is quite interesting.  It would be a challenge to
implement.  If you think you can do it, I think it would be a real win,
and a real object-oriented API to PostgreSQL.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Dec 2000 18:53:11 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RFC C++ Interface
Randy Jonasz wrote:
> 
> I appreciate your comments and would like to respond to your concerns.
> The API I sketched in my earlier e-mail is borrowed heavily from
> Rogue Wave's dbtools.h++ library.  I think it can be a very clean and
> elegant way of accessing a database.

Yes, this looks neat. At least it is an API design that has been
properly tested. We've been thinking along the same lines, and were
thinking of faking up a roguewave type API for postgres.

One thing I would like to see, which we have built into our own,
primitive, C++ interface, is support for binary data retrieval. For some
applications the savings are huge. 

I haven't thought very hard about how to do this: we do it by having a
perl script generate structures from the table definitions at compile
time, which works well in our case, but is not necessarily suitable for
a library. Code to copy the data into these structures is similarly
generated. Not sure whether roguewave have a better solution.

Good luck with it.

Adriaan

Mon, 11 Dec 2000 20:01:31 +0200
Adriaan Joubert <a.joubert@albourne.com>
Re: RFC C++ Interface
> One thing I would like to see, which we have built into our own,
> primitive, C++ interface, is support for binary data retrieval. For some
> applications the savings are huge.
> I haven't thought very hard about how to do this: we do it by having a
> perl script generate structures from the table definitions at compile
> time, which works well in our case, but is not necessarily suitable for
> a library. Code to copy the data into these structures is similarly
> generated. Not sure whether roguewave have a better solution.

This is what CORBA is designed to do. No point in reinventing the wheel
for an on-the-wire protocol.

I'm not sure what the integration path would be for a CORBA-based
interface onto the server.

                    - Thomas

Tue, 12 Dec 2000 03:30:48 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: RFC C++ Interface

Thanks for the vote of confidence.  I'm looking forward to doing it.  I've
got most of the classes needed laid out.  Once I'm done this step, I'll
post what I have for more comments, crticism, suggestions.

Cheers,

Randy

On Sun, 10 Dec 2000, Bruce Momjian wrote:

> > I appreciate your comments and would like to respond to your concerns.
> > The API I sketched in my earlier e-mail is borrowed heavily from
> > Rogue Wave's dbtools.h++ library.  I think it can be a very clean and
> > elegant way of accessing a database.
>
> Rogue Wave's API is quite interesting.  It would be a challenge to
> implement.  If you think you can do it, I think it would be a real win,
> and a real object-oriented API to PostgreSQL.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
>

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle


Tue, 12 Dec 2000 13:53:09 -0500 (EST)
Randy Jonasz <rjonasz@click2net.com>
Re: RFC C++ Interface
On Sun, Dec 10, 2000 at 06:53:11PM -0500, Bruce Momjian wrote:
> > I appreciate your comments and would like to respond to your concerns.
> > The API I sketched in my earlier e-mail is borrowed heavily from
> > Rogue Wave's dbtools.h++ library.  I think it can be a very clean and
> > elegant way of accessing a database.
> 
> Rogue Wave's API is quite interesting.  It would be a challenge to
> implement.  If you think you can do it, I think it would be a real win,
> and a real object-oriented API to PostgreSQL.

I was co-architect of the Rogue Wave Dbtools.h++ interface design (along 
with somebody who actually knew something about databases, Stan Sulsky) in 
the early 90's.  We really tried to make the "Datum" type unnecessary in 
normal programs.  To my disgrace, I didn't participate in implementation;
it was implemented mainly by Lars Lohn, who went on to a stellar career 
as a consultant to users of the library.

At the time, ODBC was just beginning to be used.  Oracle was already
a bully, actually moreso than today; we had to buy a full production 
license just to develop on it.  Ingres was much better; they sent two
engineers to do the port themselves.  

The design is really showing its age.  SQL92 and SQL3 didn't exist then,
and neither did the STL or the ISO 14882 C++ Language standard.

Nathan Myers
ncm@zembu.com

Tue, 12 Dec 2000 13:25:03 -0800
ncm@zembu.com (Nathan Myers)
Re: RFC C++ Interface
> On Sun, Dec 10, 2000 at 06:53:11PM -0500, Bruce Momjian wrote:
> > > I appreciate your comments and would like to respond to your concerns.
> > > The API I sketched in my earlier e-mail is borrowed heavily from
> > > Rogue Wave's dbtools.h++ library.  I think it can be a very clean and
> > > elegant way of accessing a database.
> > 
> > Rogue Wave's API is quite interesting.  It would be a challenge to
> > implement.  If you think you can do it, I think it would be a real win,
> > and a real object-oriented API to PostgreSQL.
> 
> I was co-architect of the Rogue Wave Dbtools.h++ interface design (along 
> with somebody who actually knew something about databases, Stan Sulsky) in 
> the early 90's.  We really tried to make the "Datum" type unnecessary in 
> normal programs.  To my disgrace, I didn't participate in implementation;
> it was implemented mainly by Lars Lohn, who went on to a stellar career 
> as a consultant to users of the library.
> 
> At the time, ODBC was just beginning to be used.  Oracle was already
> a bully, actually moreso than today; we had to buy a full production 
> license just to develop on it.  Ingres was much better; they sent two
> engineers to do the port themselves.  
> 
> The design is really showing its age.  SQL92 and SQL3 didn't exist then,
> and neither did the STL or the ISO 14882 C++ Language standard.

Can you suggest areas that should be changed?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 12 Dec 2000 17:28:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RFC C++ Interface
On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > On Sun, Dec 10, 2000 at 06:53:11PM -0500, Bruce Momjian wrote:
> > > > I appreciate your comments and would like to respond to your
> > > > concerns. The API I sketched in my earlier e-mail is borrowed
> > > > heavily from Rogue Wave's dbtools.h++ library. I think it can be
> > > > a very clean and elegant way of accessing a database.
> > >
> > > Rogue Wave's API is quite interesting. It would be a challenge to
> > > implement. If you think you can do it, I think it would be a real
> > > win, and a real object-oriented API to PostgreSQL.
> >
> > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > ... The design is really showing its age. SQL92 and SQL3 didn't
> > exist then, and neither did the STL or the ISO 14882 C++ Language
> > standard.
>
> Can you suggest areas that should be changed?

As I recall, we were much more fond of operator overloading then than is
considered tasteful or wise today. Also, there was no standard for how
iterators ought to work, then, whereas today one needs unusually good
reasons to depart from the STL style.

Nathan Myers 
ncm@zembu.com

Tue, 12 Dec 2000 15:50:31 -0800
ncm@zembu.com (Nathan Myers)
Re: RFC C++ Interface
Interesting comments.  I can see using the STL framework for iterating
through result sets being one way to go.  Would something like:

vector<pgrows>table = pgdb.exec("Select * from foo");
vector<pgrows>::iterator row;
vector<pgrows>::iterator end = table.end();

for( row = table.begin(); row != end; ++row ) {

  *row >> field1 >> field2;

  //do something with fields

}

be along the lines you were thinking?

Cheers,

Randy

On Tue, 12 Dec 2000, Nathan Myers wrote:

> On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > > On Sun, Dec 10, 2000 at 06:53:11PM -0500, Bruce Momjian wrote:
> > > > > I appreciate your comments and would like to respond to your
> > > > > concerns. The API I sketched in my earlier e-mail is borrowed
> > > > > heavily from Rogue Wave's dbtools.h++ library. I think it can be
> > > > > a very clean and elegant way of accessing a database.
> > > >
> > > > Rogue Wave's API is quite interesting. It would be a challenge to
> > > > implement. If you think you can do it, I think it would be a real
> > > > win, and a real object-oriented API to PostgreSQL.
> > >
> > > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > > ... The design is really showing its age. SQL92 and SQL3 didn't
> > > exist then, and neither did the STL or the ISO 14882 C++ Language
> > > standard.
> >
> > Can you suggest areas that should be changed?
>
> As I recall, we were much more fond of operator overloading then than is
> considered tasteful or wise today. Also, there was no standard for how
> iterators ought to work, then, whereas today one needs unusually good
> reasons to depart from the STL style.
>
> Nathan Myers
> ncm@zembu.com
>
>

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle


Wed, 13 Dec 2000 15:16:28 -0500 (EST)
Randy Jonasz <rjonasz@click2net.com>
Re: RFC C++ Interface
On Wed, Dec 13, 2000 at 03:16:28PM -0500, Randy Jonasz wrote:
> On Tue, 12 Dec 2000, Nathan Myers wrote:
> > On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > > > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > > > ... The design is really showing its age.
> > > Can you suggest areas that should be changed?
> > As I recall, we were much more fond of operator overloading then than is
> > considered tasteful or wise today. Also, there was no standard for how
> > iterators ought to work, then, whereas today one needs unusually good
> > reasons to depart from the STL style.
> 
> Interesting comments.  I can see using the STL framework for iterating
> through result sets being one way to go.  Would something like:
> 
> vector<pgrows>table = pgdb.exec("Select * from foo");
> vector<pgrows>::iterator row;
> vector<pgrows>::iterator end = table.end();
> 
> for( row = table.begin(); row != end; ++row ) {
>   *row >> field1 >> field2;
>   //do something with fields
> }
> 
> be along the lines you were thinking?

No.  The essence of STL is its iterator interface framework.  
(The containers and algorithms that come with STL should be seen
merely as examples of how to use the iterators.)  A better
example would be

  Postgres::Result_iterator end;
  for (Postgres::Result_iterator it = pgdb.exec("Select * from foo");
       it != end; ++it) {
    int field1;
    string field2;
    *it >> field1 >> field2;
    // do something with fields
  }
 
(although this still involves overloading ">>").  
The points illustrated above are:

1. Shoehorning the results of a query into an actual STL container is
   probably a Bad Thing.  Users who want that can do it themselves
   with std::copy().

2. Lazy extraction of query results is almost always better than
   aggressive extraction.  Often you don't actually care about
   the later results, and you may not have room for them anyhow.

Rather than the generic result iterator type illustrated above, with 
conversion to C++ types on extraction via ">>", I would prefer to use 
a templated iterator type so that the body of the loop would look more 
like

  // do something with it->field1 and it->field2

or

  // do something with it->field1() and it->field2()

However, it can be tricky to reconcile compile-time type-safety with 
the entirely runtime-determined result of a "select".  Probably you 
could put in conformance checking where the result of exec() gets 
converted to the iterator, and throw an exception if the types don't 
match. 

Nathan Myers
ncm@zembu.com


Wed, 13 Dec 2000 14:21:22 -0800
ncm@zembu.com (Nathan Myers)
Re: RFC C++ Interface
Thanks for responding.  I've made some comments below.

On Wed, 13 Dec 2000, Nathan Myers wrote:

> On Wed, Dec 13, 2000 at 03:16:28PM -0500, Randy Jonasz wrote:
> > On Tue, 12 Dec 2000, Nathan Myers wrote:
> > > On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > > > > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > > > > ... The design is really showing its age.
> > > > Can you suggest areas that should be changed?
> > > As I recall, we were much more fond of operator overloading then than is
> > > considered tasteful or wise today. Also, there was no standard for how
> > > iterators ought to work, then, whereas today one needs unusually good
> > > reasons to depart from the STL style.
> >
> > Interesting comments.  I can see using the STL framework for iterating
> > through result sets being one way to go.  Would something like:
> >
> > vector<pgrows>table = pgdb.exec("Select * from foo");
> > vector<pgrows>::iterator row;
> > vector<pgrows>::iterator end = table.end();
> >
> > for( row = table.begin(); row != end; ++row ) {
> >   *row >> field1 >> field2;
> >   //do something with fields
> > }
> >
> > be along the lines you were thinking?
>
> No.  The essence of STL is its iterator interface framework.
> (The containers and algorithms that come with STL should be seen
> merely as examples of how to use the iterators.)  A better
> example would be
>
>   Postgres::Result_iterator end;
>   for (Postgres::Result_iterator it = pgdb.exec("Select * from foo");
>        it != end; ++it) {
>     int field1;
>     string field2;
>     *it >> field1 >> field2;
>     // do something with fields
>   }
>
> (although this still involves overloading ">>").
> The points illustrated above are:
>
The above I like very much although it implies a synchronous connection to
the back end.  This can be worked around though by providing both a
synchronous and an asynchronous interface rather than using just one.  I
don't see any problems with overloading ">>" or "[]" to obtain the value
of a column.

> 1. Shoehorning the results of a query into an actual STL container is
>    probably a Bad Thing.  Users who want that can do it themselves
>    with std::copy().

On further thought I agree with you here.  Returning an iterator to a
result container would be much more efficient than what I originally
proposed.
>
> 2. Lazy extraction of query results is almost always better than
>    aggressive extraction.  Often you don't actually care about
>    the later results, and you may not have room for them anyhow.
>
> Rather than the generic result iterator type illustrated above, with
> conversion to C++ types on extraction via ">>", I would prefer to use
> a templated iterator type so that the body of the loop would look more
> like
>
>   // do something with it->field1 and it->field2
>
> or
>
>   // do something with it->field1() and it->field2()
>
This creates the problem of having public member variables and/or having a
mechanism to clone enough variables as there were columns returned in an
SQL statement.  I much prefer the earlier methods of accessing these
values.

> However, it can be tricky to reconcile compile-time type-safety with
> the entirely runtime-determined result of a "select".  Probably you
> could put in conformance checking where the result of exec() gets
> converted to the iterator, and throw an exception if the types don't
> match.
>
> Nathan Myers
> ncm@zembu.com
>
>
>

Cheers,

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle


Wed, 13 Dec 2000 20:35:32 -0500 (EST)
Randy Jonasz <rjonasz@click2net.com>
Re: RFC C++ Interface