Thread: ODBC Rewrite
I have been thinking about the ODBC driver, I know you guys know a lot more about it than I do, but I gather that the problem with it is that it does not rely on the core PostgreSQL libraries. Which means that it needs a lot of work when the PostgreSQL project makes a protocol change. Looking at both ODBC and libpq and I think that the difference is that libpq aims to make PostgreSQL interfacing easy, while ODBC strives to be a comprehensive SQL interface. 99% of what will be done with the ODBC interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute. This difference, I think anyways, makes the libpq API a poor choice for the foundation of the ODBC driver. What is needed is a lower level PostgreSQL interface library. Maybe it is based on libpq, making internal routines public, maybe it is a new development, either way, I think both ODBC and HACKERS should cooperate on a low level interface on which the ODBC driver can be based. I also think with a solid defined project with a tangible outcome will be more attrictive to new developers than does being a bugbitch. What do you think?
* markw@mohawksoft.com (markw@mohawksoft.com) wrote:
> Looking at both ODBC and libpq and I think that the difference is that
> libpq aims to make PostgreSQL interfacing easy, while ODBC strives to be a
> comprehensive SQL interface. 99% of what will be done with the ODBC
> interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute.
Saying what 99% of what the ODBC driver does isn't terribly useful-
What's that 1% that libpq can't do? Having looked at the code, can you
give us an idea on that?
Stephen
Attachment
> * markw@mohawksoft.com (markw@mohawksoft.com) wrote: >> Looking at both ODBC and libpq and I think that the difference is that >> libpq aims to make PostgreSQL interfacing easy, while ODBC strives to be >> a >> comprehensive SQL interface. 99% of what will be done with the ODBC >> interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute. > > Saying what 99% of what the ODBC driver does isn't terribly useful- > What's that 1% that libpq can't do? Having looked at the code, can you > give us an idea on that? > > Stephen > Off the top of my head, binding data to the SQL statement, reusing statements, stuff like that. The ODBC API isn't designed to talk to any one database, so there are no short cuts. Unfortunately, applications based on ODBC assume there are no short cuts, and rely on that behavior.
* markw@mohawksoft.com (markw@mohawksoft.com) wrote:
> > * markw@mohawksoft.com (markw@mohawksoft.com) wrote:
> >> Looking at both ODBC and libpq and I think that the difference is that
> >> libpq aims to make PostgreSQL interfacing easy, while ODBC strives to be
> >> a
> >> comprehensive SQL interface. 99% of what will be done with the ODBC
> >> interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute.
> >
> > Saying what 99% of what the ODBC driver does isn't terribly useful-
> > What's that 1% that libpq can't do? Having looked at the code, can you
> > give us an idea on that?
>
> Off the top of my head, binding data to the SQL statement, reusing
> statements, stuff like that.
Isn't this handled by PQexecParams and PQexecPrepared?
> The ODBC API isn't designed to talk to any one database, so there are no
> short cuts. Unfortunately, applications based on ODBC assume there are no
> short cuts, and rely on that behavior.
That's not particularly relevant- the question is if the existing libpq
API is sufficient for the ODBC driver or not.
Stephen
Attachment
> * markw@mohawksoft.com (markw@mohawksoft.com) wrote: >> > * markw@mohawksoft.com (markw@mohawksoft.com) wrote: >> >> Looking at both ODBC and libpq and I think that the difference is >> that >> >> libpq aims to make PostgreSQL interfacing easy, while ODBC strives to >> be >> >> a >> >> comprehensive SQL interface. 99% of what will be done with the ODBC >> >> interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute. >> > >> > Saying what 99% of what the ODBC driver does isn't terribly useful- >> > What's that 1% that libpq can't do? Having looked at the code, can >> you >> > give us an idea on that? >> >> Off the top of my head, binding data to the SQL statement, reusing >> statements, stuff like that. > > Isn't this handled by PQexecParams and PQexecPrepared? I had forgotten about those, but I was thinking more along the lines of SQLBindCol. > >> The ODBC API isn't designed to talk to any one database, so there are no >> short cuts. Unfortunately, applications based on ODBC assume there are >> no >> short cuts, and rely on that behavior. > > That's not particularly relevant- the question is if the existing libpq > API is sufficient for the ODBC driver or not. You should take a look at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_api_reference.asp There are a lot of things that would have to be synthisized using libpq, but could be handled better using some of the primitives. There were some things that Dave mentioned, but they escape me at the moment.
* markw@mohawksoft.com (markw@mohawksoft.com) wrote: > > * markw@mohawksoft.com (markw@mohawksoft.com) wrote: > >> > * markw@mohawksoft.com (markw@mohawksoft.com) wrote: > >> >> Looking at both ODBC and libpq and I think that the difference is > >> that > >> >> libpq aims to make PostgreSQL interfacing easy, while ODBC strives to > >> be > >> >> a > >> >> comprehensive SQL interface. 99% of what will be done with the ODBC > >> >> interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute. > >> > > >> > Saying what 99% of what the ODBC driver does isn't terribly useful- > >> > What's that 1% that libpq can't do? Having looked at the code, can > >> you > >> > give us an idea on that? > >> > >> Off the top of my head, binding data to the SQL statement, reusing > >> statements, stuff like that. > > > > Isn't this handled by PQexecParams and PQexecPrepared? > > I had forgotten about those, but I was thinking more along the lines of > SQLBindCol. Ah, yes, that's not cleanly handled by the current libpq API, and is actually something I'd like to see added. Basically, a method to allow a query to go directly to a user-supplied memory area instead of the result being stored in memory by libpq. Perhaps this would lend some more weight to getting that added. > >> The ODBC API isn't designed to talk to any one database, so there are no > >> short cuts. Unfortunately, applications based on ODBC assume there are > >> no > >> short cuts, and rely on that behavior. > > > > That's not particularly relevant- the question is if the existing libpq > > API is sufficient for the ODBC driver or not. > > You should take a look at: > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_api_reference.asp > > There are a lot of things that would have to be synthisized using libpq, > but could be handled better using some of the primitives. I'm not sure that I agree... I'd rather see libpq flushed out a bit than have a large ODBC driver which requires alot of updating whenever libpq internals change. I'm also not sure that it's quite as bad as you seem to think.. Looking at the API from that link it really doesn't look all that large and many of the things seem to have correlation to existing libpq functions. > There were some things that Dave mentioned, but they escape me at the moment. It seems to me like it'd be better to add the capabilities necessary to libpq so that others (such as myself) could benefit from them rather than write those capabilities solely into the ODBC driver. Thanks for your interest, by the way. I think there's a very good opportunity here to have a slim, fast and easily maintained ODBC driver and an improved libpq API. Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> That's not particularly relevant- the question is if the existing libpq
> API is sufficient for the ODBC driver or not.
And even more to the point, whether extending it wouldn't be a better
answer than writing a whole new API (and new library?). There are
definitely features of the V3 protocol that are not accessible through
libpq at the moment, but that is due to lack of time to add appropriate
API to libpq, not any fundamental objection to extending libpq.
regards, tom lane
> * markw@mohawksoft.com (markw@mohawksoft.com) wrote: >> > * markw@mohawksoft.com (markw@mohawksoft.com) wrote: >> >> > * markw@mohawksoft.com (markw@mohawksoft.com) wrote: >> >> >> Looking at both ODBC and libpq and I think that the difference is >> >> that >> >> >> libpq aims to make PostgreSQL interfacing easy, while ODBC strives >> to >> >> be >> >> >> a >> >> >> comprehensive SQL interface. 99% of what will be done with the >> ODBC >> >> >> interface will be simple SQLAllocStmt, SQLPrepare, and SQLExecute. >> >> > >> >> > Saying what 99% of what the ODBC driver does isn't terribly useful- >> >> > What's that 1% that libpq can't do? Having looked at the code, can >> >> you >> >> > give us an idea on that? >> >> >> >> Off the top of my head, binding data to the SQL statement, reusing >> >> statements, stuff like that. >> > >> > Isn't this handled by PQexecParams and PQexecPrepared? >> >> I had forgotten about those, but I was thinking more along the lines of >> SQLBindCol. > > Ah, yes, that's not cleanly handled by the current libpq API, and is > actually something I'd like to see added. Basically, a method to allow > a query to go directly to a user-supplied memory area instead of the > result being stored in memory by libpq. Perhaps this would lend some > more weight to getting that added. > >> >> The ODBC API isn't designed to talk to any one database, so there are >> no >> >> short cuts. Unfortunately, applications based on ODBC assume there >> are >> >> no >> >> short cuts, and rely on that behavior. >> > >> > That's not particularly relevant- the question is if the existing >> libpq >> > API is sufficient for the ODBC driver or not. >> >> You should take a look at: >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcodbc_api_reference.asp >> >> There are a lot of things that would have to be synthisized using libpq, >> but could be handled better using some of the primitives. > > I'm not sure that I agree... I'd rather see libpq flushed out a bit > than have a large ODBC driver which requires alot of updating whenever > libpq internals change. That's what we have now. In my original email, I suggested that we either create a low-level driver on which ODBC and libpq would be written, or re-work libpq a bit and expose some primitives. I'm also not sure that it's quite as bad as you > seem to think.. Looking at the API from that link it really doesn't > look all that large and many of the things seem to have correlation to > existing libpq functions. > >> There were some things that Dave mentioned, but they escape me at the >> moment. > > It seems to me like it'd be better to add the capabilities necessary to > libpq so that others (such as myself) could benefit from them rather > than write those capabilities solely into the ODBC driver. > > Thanks for your interest, by the way. I think there's a very good > opportunity here to have a slim, fast and easily maintained ODBC driver > and an improved libpq API. > > Stephen >
Tom Lane wrote:
>Stephen Frost <sfrost@snowman.net> writes:
>
>
>>That's not particularly relevant- the question is if the existing libpq
>>API is sufficient for the ODBC driver or not.
>>
>>
>
>And even more to the point, whether extending it wouldn't be a better
>answer than writing a whole new API (and new library?). There are
>definitely features of the V3 protocol that are not accessible through
>libpq at the moment, but that is due to lack of time to add appropriate
>API to libpq, not any fundamental objection to extending libpq.
>
> regards, tom lane
>
>
>
Can you list them, please?
Having based OLE DB on libpq, it would be nice to find that instead of
working hard to achieve some stuff via different SQL queries, that I can
just extend libpq to support easier handling.
In other words, I may find that it is easier to implement certain stuff
in libpq rather than OLE DB. ODBC may be in the same position. This way,
everybody win.
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/
While you all are doing this thinking, can I put in am idea?
I, like many others, am working on doing auditing in Postgres.
Something that would be very useful for auditing in a web application
environment would be to be able to pass various identifiers to the db
before any SQL statement is executed. In particular, I'm thinking of
something like Oracle's
SET_IDENTIFIER('string') feature (e.g.:
http://www.quest-pipelines.com/newsletter-v5/0604_C.htm), only
implemented through a driver hook, so that you could set the driver to
always pass this kind of identifier before executing any SQL. This
would allow a very straightforward way to manage users and auditing in a
setting where creating db users is not feasible, and it would be immune
to the problems of connection pooling.
Thoughts? Is this a bad thing to build into the driver?
Cheers,
Eric
* markw@mohawksoft.com (markw@mohawksoft.com) wrote:
> This is why I suggested either creating a new low level API library on
> which the linkes of ODBC, libpq, and I guess an OLE DB driver would be
> built, or perform an amount of surgery the existing libpq and isolate and
> expose various operations to a normalized API strategy.
Again, it'd probably be better to add the necessary API to libpq so
everyone can benefit from it than to give access to low-level libpq
function externally and have all that code in the ODBC driver which it
wouldn't be available for anyone else...
Also, again I contend that there'd really be much 'surgery' required to
extend the current libpq API to meet the requirements of, at least, the
ODBC driver.
Stephen
Attachment
> Tom Lane wrote: > >>Stephen Frost <sfrost@snowman.net> writes: >> >> >>>That's not particularly relevant- the question is if the existing libpq >>>API is sufficient for the ODBC driver or not. >>> >>> >> >>And even more to the point, whether extending it wouldn't be a better >>answer than writing a whole new API (and new library?). There are >>definitely features of the V3 protocol that are not accessible through >>libpq at the moment, but that is due to lack of time to add appropriate >>API to libpq, not any fundamental objection to extending libpq. >> >> regards, tom lane >> >> >> > Can you list them, please? > > Having based OLE DB on libpq, it would be nice to find that instead of > working hard to achieve some stuff via different SQL queries, that I can > just extend libpq to support easier handling. > > In other words, I may find that it is easier to implement certain stuff > in libpq rather than OLE DB. ODBC may be in the same position. This way, > everybody win. This is why I suggested either creating a new low level API library on which the linkes of ODBC, libpq, and I guess an OLE DB driver would be built, or perform an amount of surgery the existing libpq and isolate and expose various operations to a normalized API strategy.
Stephen Frost wrote: -- Start of PGP signed section. > * markw@mohawksoft.com (markw@mohawksoft.com) wrote: > > This is why I suggested either creating a new low level API library on > > which the linkes of ODBC, libpq, and I guess an OLE DB driver would be > > built, or perform an amount of surgery the existing libpq and isolate and > > expose various operations to a normalized API strategy. > > Again, it'd probably be better to add the necessary API to libpq so > everyone can benefit from it than to give access to low-level libpq > function externally and have all that code in the ODBC driver which it > wouldn't be available for anyone else... > > Also, again I contend that there'd really be much 'surgery' required to > extend the current libpq API to meet the requirements of, at least, the > ODBC driver. FYI, DBDpg needs similar libpq extensions so other languages can clearly benefit from extending the libpq API. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073