Is there a One True Postgres interface? Is Ruby-postgresmaintained?

[Thomas Kellerer]

Phillip Gawlowski, 16.04.2008 16:39:

Thomas Kellerer wrote:

| The Postgres JDBC driver is 100% Java and definitely does not rely on
| any external DLLs or libraries, it speaks the native PG protocol (AFAIK
| there is no such thing as a "Java interface"). So why shouldn't this be
| possible with Ruby as well?

Because Ruby doesn't really compare to Java in terms of infrastructure
and APIs?

Both Ruby and the database you want to speak to have to provide an API
to do that (JDBC is a Java API, and still needs the proper connectors to
actually talk to the database, similar to ODBC).

This is not want I meant. It's the driver that provides the JDBC API to the application. This is true for all 100% Java JDBC drivers. (e.g. the Oracle, SQL Server, MySQL).

All of them have to use the core functions available in the JDK. The JDK itself does not contain any implementation of the JDBC API (apart from the DriverManager maybe), so all those drivers basically use the standard Java IO and network classes to communicate with their backend (and of course their own classes that support the DBMS protocol)

I'm a complete newbie with Ruby, but from what I have seen, I would be very surprised if it wasn't possible to implement a pure Ruby implementation to communicate with a Postgres backend.
How much work that would be, is probably another question though.

Thomas

 

[Phillip Gawlowski]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thomas Kellerer wrote:

| This is not want I meant. It's the driver that provides the JDBC API to
| the application. This is true for all 100% Java JDBC drivers. (e.g. the
| Oracle, SQL Server, MySQL).

http://java.sun.com/products/jdbc/overview.html

"JDBC Architecture
The JDBC API contains two major sets of interfaces: the first is the
JDBC API for application writers, and the second is the lower-level JDBC
driver API for driver writers."

Later on:

"Included in the Java Platform
As a core part of the Java 2 Platform, the JDBC API is available
anywhere that the platform is. This means that your applications can
truly write database applications once and access data anywhere. The
JDBC API is included in both the Java 2 Platform, Standard Edition
(J2SE) and the Java 2 Platform, Enterprise Edition (J2EE), providing
server-side functionality for industrial strength scalability."

In short: The driver talks to the DB, hooking Java and the DB together
via the JDBC API. ODBC works similar (much less well, though, IME).

|
| I'm a complete newbie with Ruby, but from what I have seen, I would be
| very surprised if it wasn't possible to implement a pure Ruby
| implementation to communicate with a Postgres backend. How much work
| that would be, is probably another question though.

Needs an API, though. And that needs working with Ruby's C code first,
to "teach" the Ruby language basic functionality (or to enable adapters
in a coherent manner).

JDBC allows agnostic access to Databases. Theoretically, any way.
Similar to Sequel, DataMapper, or ActiveRecord in the Ruby space.

It does work, but it is not unified, much less standardized.

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan

YUPPIES: <Y>es, <U>nder <P>eer <P>ressure <I>'ll
~ <E>at <S>hit
~ -- Chris Squire
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkgGIeQACgkQbtAgaoJTgL9HhQCbBIXfYBh4SvnrWX8vU93MlycH
JR4An2I46T0Vx2BxbwvRvzwbdjBVMaSA
=9aq0
-----END PGP SIGNATURE-----

 

[Christopher Dicely]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thomas Kellerer wrote:

| The Postgres JDBC driver is 100% Java and definitely does not rely on
| any external DLLs or libraries, it speaks the native PG protocol (AFAIK
| there is no such thing as a "Java interface"). So why shouldn't this be
| possible with Ruby as well?

Because Ruby doesn't really compare to Java in terms of infrastructure
and APIs?

While this may be true in some general sense, I don't think its really all that
relevant here. PostgreSQL's protocol is documented and supported over
TCP/IP, and Ruby certainly has TCP/IP support, so there is no reason you
couldn't write a pure Ruby PostgreSQL driver.

Long story short: It's not impossible to define a standard API for Ruby,
but somebody has to do it yet (don't look at me, C makes me scream).

Ruby has a fairly standard DB API: Ruby-DBI. And there's no reason you
need to use C to write Ruby-DBI database drivers.

 

[Christopher Dicely]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thomas Kellerer wrote:

| This is not want I meant. It's the driver that provides the JDBC API to
| the application. This is true for all 100% Java JDBC drivers. (e.g. the
| Oracle, SQL Server, MySQL).

http://java.sun.com/products/jdbc/overview.html

"JDBC Architecture
The JDBC API contains two major sets of interfaces: the first is the
JDBC API for application writers, and the second is the lower-level JDBC
driver API for driver writers."

Later on:

"Included in the Java Platform
As a core part of the Java 2 Platform, the JDBC API is available
anywhere that the platform is. This means that your applications can
truly write database applications once and access data anywhere. The
JDBC API is included in both the Java 2 Platform, Standard Edition
(J2SE) and the Java 2 Platform, Enterprise Edition (J2EE), providing
server-side functionality for industrial strength scalability."

In short: The driver talks to the DB, hooking Java and the DB together
via the JDBC API. ODBC works similar (much less well, though, IME).


|
| I'm a complete newbie with Ruby, but from what I have seen, I would be
| very surprised if it wasn't possible to implement a pure Ruby
| implementation to communicate with a Postgres backend. How much work
| that would be, is probably another question though.

Needs an API, though. And that needs working with Ruby's C code first,
to "teach" the Ruby language basic functionality (or to enable adapters
in a coherent manner).

Even if Ruby didn't have a database API, why would it take working with Ruby's
C code to add it? All an API is is a defined interface.

JDBC allows agnostic access to Databases. Theoretically, any way.
Similar to Sequel, DataMapper, or ActiveRecord in the Ruby space.

ActiveRecord, DataMapper, and Sequel are ORM libraries, not really
comparable to JDBC. JDBC's closest Ruby equivalent is Ruby-DBI.

 

[Sam Smoot]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thomas Kellerer wrote:

| The Postgres JDBC driver is 100% Java and definitely does not rely on
| any external DLLs or libraries, it speaks the native PG protocol (AFAIK
| there is no such thing as a "Java interface"). So why shouldn't this be
| possible with Ruby as well?

Because Ruby doesn't really compare to Java in terms of infrastructure
and APIs?

Both Ruby and the database you want to speak to have to provide an API
to do that (JDBC is a Java API, and still needs the proper connectors to
actually talk to the database, similar to ODBC).

It is not yet possible with Ruby (except via Ruby/DL, I've heard), but
it certainly could be.

So, up to trying to do something like that? ;P

At the moment, you have to hook into C somehow, or wrap a C/C++
extension to pull that off. Which is certainly possible, as the various
DB-gems show.

However, since Ruby has no ready-made API for hooking up DB connectors
and all that stuff, the job has be done over and over again, for each DB
Ruby shall support.

And, IMO, adding such an API, while nice, would be beyond Ruby's scope.

And JRuby needs the JDBC libraries of, for example, MySQL to talk to a
MySQL server. Those aren't part of Java's distribution. :|

But the relative ease of installation makes JRuby very, very attractive
to me when deploying Rails apps.

Well, MySQL might become shipped as a standard, since Sun bought out
MySQL, but you never know..

Long story short: It's not impossible to define a standard API for Ruby,
but somebody has to do it yet (don't look at me, C makes me scream).

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan

~ - You know you've been hacking too long when...
..you want to retract something said in haste, and think C-a C-@ C-e C-w
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org

iEYEARECAAYFAkgGD48ACgkQbtAgaoJTgL+OmQCfbLO5l8x0cw+HhH8C6RClJ7VR
SWkAmwX2UxbzdJgNM25SOhEuB+OWDeC1
=4UzI
-----END PGP SIGNATURE-----

This is exactly the goal of the DataObjects project. It's a simple,
bare, unified API for Sqlite3, MySQL, and Postgres. ADO.NET and JDBC
bridges are planned as well...

 

[Phillip Gawlowski]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Christopher Dicely wrote:

|> Needs an API, though. And that needs working with Ruby's C code first,
|> to "teach" the Ruby language basic functionality (or to enable adapters
|> in a coherent manner).
|
| Even if Ruby didn't have a database API, why would it take working
with Ruby's
| C code to add it? All an API is is a defined interface.

And guess where the Ruby language is implemented? And for a more-or-less
coherent API similar to JDBC or ODBC, you'd have to dig a bit into Ruby.
Not a lot, I guess, but still a bit.

| ActiveRecord, DataMapper, and Sequel are ORM libraries, not really
| comparable to JDBC. JDBC's closest Ruby equivalent is Ruby-DBI.

Similar != equivalent. And Ruby:BI isn't all that agnostic in regard
to databases, since you still have to use SQL (which is true with JDBC,
too, I know). However, JDBC massages SQL a bit, to enable a somewhat
seamless integration of databases in applications.

Similar to what ORM libraries offer for Ruby. You can expect to get the
same type of result out of an ORM, similar to JDBC.

While Ruby:BI needs a bit of fine tuning in that area. That might be
different in newer releases than the one I looked at and used (that was,
what, a year and a half ago?).


- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan

~ Why do we drink cow's milk? Who was the first guy who first looked at
a cow
and said "I think I'll drink whatever comes out of these things when I
squeeze
'em!"? -- Calvin
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkgGKssACgkQbtAgaoJTgL8M0ACgmEKVnZT1hBH4mzPh+FO8sdVT
pOoAn0qVKJWrNwle1/+l0Id0n0puZadu
=26Fg
-----END PGP SIGNATURE-----

 

[Phillip Gawlowski]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Christopher Dicely wrote:

| While this may be true in some general sense, I don't think its really
all that
| relevant here. PostgreSQL's protocol is documented and supported over
| TCP/IP, and Ruby certainly has TCP/IP support, so there is no reason you
| couldn't write a pure Ruby PostgreSQL driver.

Specifically speaking, it is not.

|> Long story short: It's not impossible to define a standard API for Ruby,
|> but somebody has to do it yet (don't look at me, C makes me scream).
|
| Ruby has a fairly standard DB API: Ruby-DBI. And there's no reason you
| need to use C to write Ruby-DBI database drivers.

Ruby:BI used to use native adapter gems (at least for MySQL) and
SQLite3, providing a rather thin wrapper around the functions provided
by these native gems.

"The Ruby DBI module includes the code that implements the general DBI
layer, as well as a set of DBD-level drivers. Many of these drivers
require that you have additional software installed. For example, the
database driver for MySQL is written in Ruby and provides a binding to
the Ruby MySQL module, which itself is written in C and provides a
binding to the MySQL C client API. This means that if you want to write
DBI scripts to access MySQL databases, you'll need to have both the Ruby
MySQL module and the C API installed."

~From http://www.kitebird.com/articles/ruby-dbi.html#TOC_2 which is still
referenced by Ruby:BI.

Also, last time I checked, Ruby-DBI wasn't part of the Ruby STDLIB, but
is a third party tool, which doesn't make it Ruby's API, but a Ruby API
to databases.

Of course, ORMs solve different problems than the Ruby:BI attempts to
solve.

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan

You want to live forever? Don't die.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkgGLBUACgkQbtAgaoJTgL8qcgCggDQt/VvAApMMrUDLsbivFSXh
vqAAn3hs/WASPd6EXZxa3Es1YM0UipJV
=UYdl
-----END PGP SIGNATURE-----

 

[Luis Lavena]

Well ... don't say I didn't try. I installed PostgreSQL 8.3 and the
Rails add-on that comes from a third party. I was not able to get it to
function, however. What I know so far is that it is derived from the
One-Click Installer version 186-22. However, it installs in C:\pgRails,
but all of the "bat" files from the One-Click still are looking for
C:\Ruby, and it doesn't put the right path in the PATH environment
variable. So the "gem" console, which is usually how I access things
from the One-Click Ruby, doesn't work.

You should have tried at least 186-26... dunno what release are you
using :-$

The Rails piece of it may be hard coded to actually function, but I
couldn't find any documentation on how to start that up, so I didn't get
anywhere with it. A couple of months ago I tried it and posted some
comments on the creator's forum. They are looking for volunteers.

can't comment on that... didn't tried.

By the way, if it matters, the Windows PostgreSQL 8.3 installer loads a
copy of the Visual C++ 2005 run time.

The only pieces of PostgresSQL 8.2 or 8.3 that depends on latest
runtimes are the pgAdmin and GUI tools.

The libpq.lib only depends on MSVCRT, since it was build with MinGW
actually.

I've verified that when checking DataObjects.

Regards,

 

[Jeff Davis]

Jeff, I managed to get do_postgres (DataObject.rb project) working on
Windows with PostgreSQL 8.2.

Why you don't fill a Integration Request [1] at RubyInstaller tracker
and point with the instructions to get the code, the dependencies and
everything.

I'll be happy to provide patches to make it work properly under
Windows (both VC6 and MinGW). See my post on PostgreSQL [2]

[1] http://rubyforge.org/tracker/?atid=22895&group_id=167&func=browse
[2] http://rubyforge.org/pipermail/rubyinstaller-devel/2008-April/000284.html

Thank you for the response!

I have filed an integration request, please let me know if more
information is required.

I would welcome any patches that improve portability to any platform.

Regards,
Jeff Davis

 

[Jeff Davis]

Phillip Gawlowski, 16.04.2008 02:08:

The Postgres JDBC driver is 100% Java and definitely does not rely on any external DLLs or libraries, it speaks the native PG protocol (AFAIK there is no such thing as a "Java interface").
So why shouldn't this be possible with Ruby as well?

It is certainly possible. It would require a huge amount of effort,
however. Not just up-front effort, but lots of maintenance effort, as
well.

There is also a question of scope. What are the limits to this line of
reasoning? Are we going to re-implement every system library, or just
some of them?

Regards,
Jeff Davis

 

[Jeff Davis]

Nothing else works seamlessly on Windows. But seriously, there is an
add-on you can get when you install PostgreSQL 8.3 on Windows that
contains an ancient implementation of Ruby and Rails -- with PostgreSQL,
of course. I looked at it briefly, and I have no idea how it was talking
to PostgreSQL.
Interesting.

At this stage of the game, I think a pure Ruby PostgreSQL (and MySQL)
interface has some serious advantages over struggling with numerous C
compilers for Windows, Mac, Solaris, etc.

In my experience with ruby-pg, there were only minor issues with
platforms other than windows. I solved all the problems that I know
about on OS X, linux, solaris, and freebsd. It probably works on some
other platforms as well. Windows is the only difficult case, as far as I
can tell.

Trying to maintain all the code to implement the PostgreSQL protocol
would be a huge project, and not one that I have the time for.
Furthermore, people need to trust their database driver, and I can
provide a certain level of that trust by using the well-tested libpq
rather than trying to implement it myself.

Regards,
Jeff Davis

 

[Jeff Davis]

However, since Ruby has no ready-made API for hooking up DB connectors
and all that stuff, the job has be done over and over again, for each DB
Ruby shall support.

Huh? Each RDBMS uses a different wire protocol, and something needs to
speak that wire protocol. If you don't use the C or Java libraries which
the RDBMS provides, it needs to be implemented. Having a "ready-made
API" doesn't avoid having to implement the wire protocol.

Regards,
Jeff Davis

 

[Luis Lavena]

Jeff, I managed to get do_postgres (DataObject.rb project) working on
Windows with PostgreSQL 8.2.

Why you don't fill a Integration Request [1] at RubyInstaller tracker
and point with the instructions to get the code, the dependencies and
everything.

I'll be happy to provide patches to make it work properly under
Windows (both VC6 and MinGW). See my post on PostgreSQL [2]

[1]http://rubyforge.org/tracker/?atid=22895&group_id=167&func=browse
[2]http://rubyforge.org/pipermail/rubyinstaller-devel/2008-April/000284....

Thank you for the response!

I have filed an integration request, please let me know if more
information is required.

I would welcome any patches that improve portability to any platform.

Great!

Keep using mkmf for the time being. moving to mkrf will require users
previously have it installed to properly build the gem on every
platform, also dunno it's state regarding cross-platform
functionality.

I'll work on setting up monitoring for it and provide you patches.

Also, I'll build it against 8.2 since 8.3 as not been promoted to
stable.

Will be helpful if you can also provide me a mailing list where the
integration builder will output the building results...

Something like this:
http://rubyforge.org/pipermail/rubyinstaller-devel/2008-April/000263.html
Or this:
http://rubyforge.org/pipermail/rubyinstaller-devel/2008-April/000283.html

Regards,

Thank you for your time, I'll let you know how this end

Regards,

 

[Jeff Davis]

Even if Ruby didn't have a database API, why would it take working with Ruby's
C code to add it? All an API is is a defined interface.

Precisely. This thread is about database drivers, not "one true database
API". Either can be done in C, and either can be done in pure ruby.

Regards,
Jeff Davis

 

[Jeff Davis]

Is there good actual use documentation, rather than only basic class and
method documentation?

I consider the class and method documentation to be quite complete and
useful. Most users can browse that to find the functionality they are
looking for.

There could be some better tutorial documentation, but I haven't gotten
around to that yet.

Regards,
Jeff Davis

 

[Jeff Davis]

Also, I'll build it against 8.2 since 8.3 as not been promoted to
stable.

Promoted to stable by whom? PostgreSQL 8.3.1 is the latest stable
release on all platforms.

Will be helpful if you can also provide me a mailing list where the
integration builder will output the building results...

Something like this:
http://rubyforge.org/pipermail/rubyinstaller-devel/2008-April/000263.html
Or this:
http://rubyforge.org/pipermail/rubyinstaller-devel/2008-April/000283.html

I set up the mailing list ruby-pg-devel.

Regards,
Jeff Davis

 

[Jeff Davis]

. . . into this:

Ruby-pg provides the module "pg", a Ruby interface to the PostgreSQL
Relational Database Management System, which supersedes "postgres". The
ruby-pg project also maintains a fork of the "postgres" module from the
"ruby-postgres" project.

I get 238 characters for that with wc. There's enough room left out of
255 to s/which supersedes/meant to supersede/ if you don't want to speak
for the Ruby community at large in such strong terms, too. I don't know
if there might be any other concerns that would make you hesitant to make
this kind of statement, of course.

Thank you for the specific update. I had to edit it somewhat, because
the real limit seems to be actually lower than 255 (and lower than 238).

Regards,
Jeff Davis

 

[Stefan Lang]

While this may be true in some general sense, I don't think its really all that
relevant here. PostgreSQL's protocol is documented and supported over
TCP/IP, and Ruby certainly has TCP/IP support, so there is no reason you
couldn't write a pure Ruby PostgreSQL driver.

Yes, and to prove you right, someone has already done it.
There's the postgres-pr gem on RubyForge:
http://rubyforge.org/projects/ruby-dbi/

We use it for a Rails app and it works fine on MRI and JRuby.
Although I can't say how it compares performance wise,
this app has only a couple of users.

 

[Luis Lavena]

Promoted to stable by whom? PostgreSQL 8.3.1 is the latest stable
release on all platforms.

By me? :-D

I got several issues from 5.0 to 5.1 when was dealing with MySQL,
also, there is a bigger 8.2 userbase than 8.3 *and* we need to verify
API incompatibilities that those shared libraries (DLL) exposes and
make everything crash.

I set up the mailing list ruby-pg-devel.

Great, will make the bot subcribe in it and start dumping build
reports

Regards,

 

[Peter Booth]

I can see Jeff's point. If both libpq and the Postgres JDBC drivers
exist what is the value add and writing a third version that
duplicates their functionality? Certainly it could be a good research
project to see how complexity, bug count, and performance vary across
three implementations but not a great use of Jeff's time.

Does anyone know which of the Postgres JDBC driver or libpq is most
widely used today and most reliable?

Peter