Thread: A thought about other open source projects

I happened across (yet) another open source project which supports
MySql and Derby (its a Java app) and is thinking about supporting
Oracle (they have actually bought a licence) but does not support
Postgresql.  This particular project is onehippo.org, but there are many
others.  Another perhaps more important project is the Akonadi
project in KDE, which is only gradually getting around to Postgresql.

These projects need help to realise that adding Postgresql is not a big
job, especially for those using JDBC which can already connect to all
DBs.  It strikes me that if the project could write a few pages gleaned
from other porting operations, then whenever a project like this is found
they can be pointed to these pages and shown how easy it is to do.

Then if someone spots a project that might use Postgresql they can
simply point them at the pages.

David

On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:
> These projects need help to realise that adding Postgresql is not a
> big
> job, especially for those using JDBC which can already connect to all
> DBs.  It strikes me that if the project could write a few pages
> gleaned
> from other porting operations, then whenever a project like this is
> found
> they can be pointed to these pages and shown how easy it is to do.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

On Sunday 20 June 2010, Peter Eisentraut wrote:
> On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:
> > These projects need help to realise that adding Postgresql is not a
> > big
> > job, especially for those using JDBC which can already connect to all
> > DBs.  It strikes me that if the project could write a few pages
> > gleaned
> > from other porting operations, then whenever a project like this is
> > found
> > they can be pointed to these pages and shown how easy it is to do.
>
>
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreS
> QL
>
Excellent, I had not realised this existed.  I will point any projects I meet
which have not found Postrgesql goodness at this page.  Thank you.

 I don't support anyone has written a "how to write database agnostic
code" guide?  That way its not a matter of porting, more a matter of
starting off right.

David

database agnostic code is theoretically a great idea. However, you lose
most of the advantages of the chosen database engine. For example, if
you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per database
(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.

On 6/20/2010 12:08 PM, David Goodenough wrote:
> On Sunday 20 June 2010, Peter Eisentraut wrote:
>
>> On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:
>>
>>> These projects need help to realise that adding Postgresql is not a
>>> big
>>> job, especially for those using JDBC which can already connect to all
>>> DBs.  It strikes me that if the project could write a few pages
>>> gleaned
>>> from other porting operations, then whenever a project like this is
>>> found
>>> they can be pointed to these pages and shown how easy it is to do.
>>>
>>
>>
> http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreS
>
>> QL
>>
>>
> Excellent, I had not realised this existed.  I will point any projects I meet
> which have not found Postrgesql goodness at this page.  Thank you.
>
>  I don't support anyone has written a "how to write database agnostic
> code" guide?  That way its not a matter of porting, more a matter of
> starting off right.
>
> David
>
>

On Sunday 20 June 2010, Sim Zacks wrote:
> database agnostic code is theoretically a great idea. However, you lose
> most of the advantages of the chosen database engine. For example, if
> you support an engine that does not support relational integrity you
> cannot use delete cascades.
> The most efficient way is to have a separate backend module per
database
> (or db version) supported. The quickest way is to write code that will
> work on any db but won't take advantage of db-specific features.
This is what I am trying to encourage.  I am asking about the best
way to encourage it.

David
>
> On 6/20/2010 12:08 PM, David Goodenough wrote:
> > On Sunday 20 June 2010, Peter Eisentraut wrote:
> >> On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:
> >>> These projects need help to realise that adding Postgresql is not a
> >>> big
> >>> job, especially for those using JDBC which can already connect to
all
> >>> DBs.  It strikes me that if the project could write a few pages
> >>> gleaned
> >>> from other porting operations, then whenever a project like this is
> >>> found
> >>> they can be pointed to these pages and shown how easy it is to
do.
> >
> >
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_Postgr
> >eS
> >
> >> QL
> >
> > Excellent, I had not realised this existed.  I will point any projects I
> > meet which have not found Postrgesql goodness at this page.  Thank
you.
> >
> >  I don't support anyone has written a "how to write database agnostic
> > code" guide?  That way its not a matter of porting, more a matter of
> > starting off right.
> >
> > David
>

David Goodenough wrote on 20.06.2010 11:08:
> I don't support anyone has written a "how to write database agnostic
> code" guide?  That way its not a matter of porting, more a matter of
> starting off right.

I don't believe in "database agnostic code".

In the end it basically means that the application will run equally slow on all platforms.

I'm not necessarily talking about syntax features/differences (e.g. hierarchical queries or other advanced features)
butabout behavioral features that stem from the way the engine works, e.g. due to different locking strategies or
differentoptimizers. 

Some engines don't like single large transactions, some don't like a lot of small transactions.
Then think about syntactically identical statements that will behave differently because each engine has different
optimizationstrategies. Some engines are better with complex joins and subqueries some are better with several small
queries.An index that might be used in one engine to speed up a select might be totally ignored by another. 

Thomas

On Sun, 20 Jun 2010 12:52:22 +0200
Thomas Kellerer <spam_eater@gmx.net> wrote:

> David Goodenough wrote on 20.06.2010 11:08:
> > I don't support anyone has written a "how to write database
> > agnostic code" guide?  That way its not a matter of porting,
> > more a matter of starting off right.
>
> I don't believe in "database agnostic code".

It depends on what you're after.

There are applications that really use a DB as a SQL interface to
the file system (sort of).
Coding non standard SQL for such kind of application (and there are
many out there) is just a different practice of BDSM.

Still even when you deal with application that can really take
advantage of the various special feature of some DB and their SQL
dialect... 80% of the written SQL could be standard.

Sometimes people write abstraction code to make it easier to write
in the only SQL dialect they know for a very small coding advantage
while they could write a better one that at least wouldn't make a
pain to post the application.

When thinking about portable code I generally find this documents
useful:
http://sql-info.de/postgresql/postgres-gotchas.html


--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Sun, Jun 20, 2010 at 10:08:34AM +0100, David Goodenough wrote:
> On Sunday 20 June 2010, Peter Eisentraut wrote:
> > On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote:
> > > These projects need help to realise that adding Postgresql is
> > > not a big job, especially for those using JDBC which can already
> > > connect to all DBs.  It strikes me that if the project could
> > > write a few pages gleaned from other porting operations, then
> > > whenever a project like this is found they can be pointed to
> > > these pages and shown how easy it is to do.
> >
> >
> http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreS
> > QL
> >
> Excellent, I had not realised this existed.  I will point any
> projects I meet which have not found Postrgesql goodness at this
> page.  Thank you.
>
> I don't support anyone has written a "how to write database agnostic
> code" guide?

I have. :)

http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html

> That way its not a matter of porting, more a matter of starting off
> right.

You're assuming that the goal of "database agnostic code" is
reasonable.  I'd take a hard look at the trade-offs first.  "Database
agnostic code" sounds like a reasonable idea until you've had to
maintain such code for a few years.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sunday 20 June 2010 12.52:22 Thomas Kellerer wrote:
> I don't believe in "database agnostic code".

Using a db abstraction may be the right way to write "database agnostic
code".

I have quite a good impression of SQLAlchemy, for example: it is quite
generic, and it is still possible to write direct SQL for those few queries
where I know that I can create better queries than SQLAlchemy would.

YMMV, of course.  When performance is an issue, optimized code for a certain
DB backend will probably beat abstractions like this every time.  But if
you're just writing a small-ish application, the option to support other db
with minimal effort is quite nice.

cheers
-- vbi


--
Today is Sweetmorn, the 25th day of Confusion in the YOLD 3176

David Goodenough wrote:
>   I don't support anyone has written a "how to write database agnostic
> code" guide?  That way its not a matter of porting, more a matter of
> starting off right.

There is no real way to write "database[-]agnostic" SQL, although of course
middleware code can and should be.

SQL dialects cannot even agree on simple things like the syntax for VARCHAR
(VARCHAR2 in Oracle) or the semantics of TIMESTAMP, or what is legal in a
SELECT.  As Sim Zacks said, "you lose most of the advantages of the chosen
database engine" if you write to the lowest common denominator, particularly
as such an LCD is nonexistent.

--
Lew

Sim Zacks wrote:
>> database agnostic code is theoretically a great idea. However, you lose
>> most of the advantages of the chosen database engine. For example, if
>> you support an engine that does not support relational integrity you
>> cannot use delete cascades.
>> The most efficient way is to have a separate backend module per
> database
>> (or db version) supported. The quickest way is to write code that will
>> work on any db but won't take advantage of db-specific features.

David Goodenough wrote:
> This is what I am trying to encourage.  I am asking about the best
> way to encourage it.

You want to encourage the use of databases that don't support relational
integrity?

Really?

I think that is a simply terrible idea.

--
Lew

On Monday 21 June 2010, Lew wrote:
> Sim Zacks wrote:
> >> database agnostic code is theoretically a great idea. However, you
lose
> >> most of the advantages of the chosen database engine. For
example, if
> >> you support an engine that does not support relational integrity you
> >> cannot use delete cascades.
> >> The most efficient way is to have a separate backend module per
> >
> > database
> >
> >> (or db version) supported. The quickest way is to write code that will
> >> work on any db but won't take advantage of db-specific features.
>
> David Goodenough wrote:
> > This is what I am trying to encourage.  I am asking about the best
> > way to encourage it.
>
> You want to encourage the use of databases that don't support
relational
> integrity?
no, I want to encourage "The quickest way is to write code that will
work on any db but won't take advantage of db-specific features."

David
>
> Really?
>
> I think that is a simply terrible idea.
>

On Mon, Jun 21, 2010 at 08:35:02AM -0400, Lew wrote:
> David Goodenough wrote:
> >I don't support anyone has written a "how to write database
> >agnostic code" guide?  That way its not a matter of porting, more a
> >matter of starting off right.
>
> There is no real way to write "database[-]agnostic" SQL, although of
> course middleware code can and should be.

"Database-agnostic middleware" is not a practical or desirable goal
for the same reason that "database-agnostic SQL" isn't.  The original
reasoning behind the radical experiment of "database-agnostic" was an
attempt to defend against the depredations of vendors of proprietary
RDBMSs, who tended to use strong-arm tactics any time they felt they
could get away with it.

As a strategy, "database-agnostic" has failed because the only two
(combinable) ways to implement it are enormously expensive even to
create, and super-linearly expensive to maintain.  I've covered these
below:

    http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
    http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html

Fortunately, another strategy whose effect is to defend against the
above-mentioned strong-arm tactics--making a wide selection of
non-proprietary RDBMSs--has succeeded.  Just pick an RDBMS and max out
its capabilities.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

then you want your code to call stub functions (with DBSpecific stack parameters)
Insert
Update
Delete(-with-cascade)
Select
 
I ran into a problem recently where i wanted to LOCK table MySQL which of course is a no-op in MySQL so I carried the driver string as a stack param e.g.
public int Lock(String driver_string_stack_param)
if(driver_string_stack_param.equalsIgnoreCase("com.mysql.jdbc.Driver") ; //noop
else{
//      do Lock Logic
     }

hth
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

 
> From: david.goodenough@btconnect.com
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] A thought about other open source projects
> Date: Mon, 21 Jun 2010 16:14:10 +0100
>
> On Monday 21 June 2010, Lew wrote:
> > Sim Zacks wrote:
> > >> database agnostic code is theoretically a great idea. However, you
> lose
> > >> most of the advantages of the chosen database engine. For
> example, if
> > >> you support an engine that does not support relational integrity you
> > >> cannot use delete cascades.
> > >> The most efficient way is to have a separate backend module per
> > >
> > > database
> > >
> > >> (or db version) supported. The quickest way is to write code that will
> > >> work on any db but won't take advantage of db-specific features.
> >
> > David Goodenough wrote:
> > > This is what I am trying to encourage. I am asking about the best
> > > way to encourage it.
> >
> > You want to encourage the use of databases that don't support
> relational
> > integrity?
> no, I want to encourage "The quickest way is to write code that will
> work on any db but won't take advantage of db-specific features."
>
> David
> >
> > Really?
> >
> > I think that is a simply terrible idea.
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail. Get busy.

On Mon, Jun 21, 2010 at 04:14:10PM +0100, David Goodenough wrote:
> On Monday 21 June 2010, Lew wrote:
> > Sim Zacks wrote:
> > >> database agnostic code is theoretically a great idea. However, you
> lose
> > >> most of the advantages of the chosen database engine. For
> example, if
> > >> you support an engine that does not support relational integrity you
> > >> cannot use delete cascades.
> > >> The most efficient way is to have a separate backend module per
> > >
> > > database
> > >
> > >> (or db version) supported. The quickest way is to write code that will
> > >> work on any db but won't take advantage of db-specific features.
> >
> > David Goodenough wrote:
> > > This is what I am trying to encourage.  I am asking about the best
> > > way to encourage it.
> >
> > You want to encourage the use of databases that don't support relational
> > integrity?
> no, I want to encourage "The quickest way is to write code that will
> work on any db but won't take advantage of db-specific features."

As with phrases like, "the quickest way to grill a unicorn steak,"
that it can be stated in a few words does not make in possible.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

> As with phrases like, "the quickest way to grill a unicorn steak,"
> that it can be stated in a few words does not make in possible.

Exactly.  The big issue here is that nobody's saying what kind of app
they want to write.

If it's a simple web content management system, the possibility of
having > 1 db being supported is better than if you need a
transactional real time securities trading system, it's a lot less
likely to work on > 1 db.

Scott Marlowe wrote:
>> As with phrases like, "the quickest way to grill a unicorn steak,"
>> that it can be stated in a few words does not make in possible.
>>
>
> Exactly.  The big issue here is that nobody's saying what kind of app
> they want to write.
>
Or what sort of performance requirements are tied to that app.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote:
> Scott Marlowe wrote:
> >>As with phrases like, "the quickest way to grill a unicorn steak,"
> >>that it can be stated in a few words does not make in possible.
> >
> >Exactly.  The big issue here is that nobody's saying what kind of
> >app they want to write.
> Or what sort of performance requirements are tied to that app.

It's not performance requirements that tend to tank such projects, but
the amount of maintenance involved.  Extending the app gets
quadratically painful.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, 21 Jun 2010 11:27:20 -0700
David Fetter <david@fetter.org> wrote:

> On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote:
> > Scott Marlowe wrote:
> > >>As with phrases like, "the quickest way to grill a unicorn
> > >>steak," that it can be stated in a few words does not make in
> > >>possible.
> > >
> > >Exactly.  The big issue here is that nobody's saying what kind
> > >of app they want to write.
> > Or what sort of performance requirements are tied to that app.
>
> It's not performance requirements that tend to tank such projects,
> but the amount of maintenance involved.  Extending the app gets
> quadratically painful.

I perfectly agree. But maybe the number of hackers involved in a
project could grow faster if the project serves more purposes.

Reuse lower maintenance costs too.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

bnichols@ca.afilias.info (Brad Nicholson) writes:
> Scott Marlowe wrote:
>>> As with phrases like, "the quickest way to grill a unicorn steak,"
>>> that it can be stated in a few words does not make in possible.
>>
>> Exactly.  The big issue here is that nobody's saying what kind of app
>> they want to write.
>>
> Or what sort of performance requirements are tied to that app.

And that's only one form of "quality of service," too.

- Sometimes you need fast...

- Mighty frequently, "fast enough" is good enough.

- Sometimes you need excruciating correctness (to the point of needing
  2PC!).

- Sometimes you need to not need to respond to all errors.  (e.g. - in
  an embedded application, there may never be a way for an
  "administrator" to get at instances of the system - consider an
  appliance like a router)

- Very frequently, making sure the system "isn't too fragile" is an
  important aspect of QoS.  [1]

- I've really liked the recent comments about the philosophy behind
  NoSQL, where it is suggested to be "postmodern" in the sense that:

    - SQL traditionally has been about storing facts of some solidity,
      and, in contrast...

    - The postmodernist approach to philosophy, and, to a great extent,
      NoSQL systems involves an acceptance of:
      - The absence of objective truth
      - Queries return opinions, not facts

  In effect, this involves a more-or-less philosophical shift that is,
  all the same, an expression of "quality of service."

- The vigorously expressed unfriendliness of the MythTV folk is somewhat
  curious, and I'm not quite sure how to classify it.  I don't think it's
  any of the above.

[1]  <http://www.databasejournal.com/features/db2/article.php/3888026>

[2]  <http://www.xaprb.com/blog/2010/06/12/postmodern-databases/>
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow <aa126@DELETE.fan.nb.ca>